Review of Mysql at the end of junior year

Junior year final review

The following questions need to be written proficiently

What are the characteristics of the database?

Data is structured, with connections between data and oriented to the entire system; data has high sharing, low redundancy, and is easy to expand; data independence is high.

What databases are there?

MySQL, SQL Server, Oracle, Hadoop, etc.

Related free learning recommendations: mysql video tutorial

Types of databases include?

Early daysThere are three popular database models, namely

• Hierarchical database
• Network Relational database
• Relational database

In today's Internet, there are mainly two most commonly used database models, namely

• Relational database
• Non-relational database.

What are DDL, DQL and DML?

1. DDL (Data Definition Language) Data Definition Language
   Used to define database objects: databases, tables, columns, etc. Keywords: create, drop, alter, etc.

2. DQL (Data Query Language) Data Query Language
   Used to query records (data) in tables in the database . Keywords: select, etc.

3. DML (Data Manipulation Language)Data Manipulation Language
   is used to add, delete, and modify data in tables in the database. Keywords: insert, delete, update, etc.

What are the constraints? What are their respective meanings?

1. Primary key constraint: primary key
2. Non-null constraint: not null
3. Unique constraint: unique
4. Foreign key constraint: foreign key

Composition of database system?

• Database
• Computer hardware
• Computer software
• Database user

What are the functions?

##AVG() Return the average value of a certain fieldCOUNT()Return the number of rows in a certain fieldMAX()Return the maximum value of a certain fieldMIN()Return the minimum value of a certain fieldSUM ()The sum of the returned fields

Name	Function

Paging query?

If only one parameter is given, it indicates the number of records.
• Two parameters, the first parameter represents offset, and the second parameter is the number of records.

limit m,n;

Filter duplicate data?

SELECT DISTINCT <字段名> FROM <表名>;

Fuzzy query?

[NOT] LIKE  '字符串

NOT: Optional parameter, the condition is met when the content in the field does not match the specified string.
• String: Specify the string to match. "String" can be a complete string, or it can contain wildcard characters.

The LIKE keyword supports percent sign "%" and underscore "_" wildcard characters.

What is the difference between varchar and char?

The length of char is

immutable, while the length of varchar is variable, that is, define a char[10] and varchar[10] , if what is saved is 'csdn', then the length occupied by char is still 10, except for the character 'csdn', followed by followed by six spaces , and varchar immediately changes the length to 4. When fetching data, use trim() to remove extra spaces for char type, but varchar is not needed.

Furthermore, the storage method of char is that each English character (ASCII) occupies 1 byte, and each Chinese character occupies two bytes; while the storage method of varchar is that each English character occupies 2 bytes. Bytes, Chinese characters also occupy 2 bytes.

New view, delete view statement?

New view:

create view 视图名字 as select 语句;

Delete view:

drop view 视图名字;

Focus on: database.md, data Notes on definition language (DDL).md

The last big question is 30 points

Test points: database building, table building, query

Reference: All homework exercises

Example:

-- 创建create table stuInfo(
	stuId int primary key auto_increment,
	stuName varchar(10) not null,
	stuAge int not null,
	stuSex int not null check(stuSex = 0 or stuSex = 1),
	time datetime)create table courseInf(
	courseId int primary key auto_increment,
	courseName varchar(10) not null,
	courseMarks int not null)create table scoreInfo(
	scoreId int primary key auto_increment,
	stuId int,
	courseId int,
	score int not null,
	constraint scoreInfo_stuInfo_fk foreign key(stuId) references stuInfo(stuId),
	constraint scoreInfo_courseInf_fk foreign key(courseId) references courseInf(courseId))-- 插入insert into stuInfo()values(default,'Tom',19,1,null),(default,'Jack',20,0,null),(default,'Rose',21,1,null),(default,'Lulu',19,1,null),(default,'Lili',21,0,null),(default,'adc',20,1,'2007-01-07 01:11:36.590');insert into courseInf()values(default,'JavaBase',4),(default,'HTML',2),(default,'JavaScript',2),(default,'SqlBase',2);insert into scoreInfo()values(default,1,1,80),(default,1,2,85),(default,1,4,50),(default,2,1,75),(default,2,3,45),(default,2,4,75),(default,3,1,45),(default,4,1,95),(default,4,2,75),(default,4,3,90),(default,4,4,45);-- 查询出每个学生所选修的课程的数量和所选修的课程的考试的平均分select count(courseId),avg(score)from scoreInfogroup by stuId;-- 修改UPDATE table_name SET field1=new-value1, field2=new-value2[WHERE Clause]-- 删除DELETE FROM table_name [WHERE Clause]

Reminder: If students carefully read and finish their homework, they will definitely get full marks for the last big question, dddd, I won’t say anymore

Related free learning recommendations: mysql database(Video)

The above is the detailed content of Review of Mysql at the end of junior year. For more information, please follow other related articles on the PHP Chinese website!