Core overview: in the development, we use SQL commands to operate the database and the data in the database. The common operations include the addition, deletion, modification and query of the database and tables. In this article, we will first learn how to use SQL statements to realize the basic operations of the database and tables.
Chapter one: understanding SQL
1.1 – what is SQL (understanding)
Anguage structured queryLanguage is a kind of command specification for operating database.
The role of 1.2-sql (understanding)
- It is a query specification for all relational databases, which is supported by different databases.
- General database operation language can be used in different databases.
- Different database SQL statements have some differences
1.3-sql statement classification (understanding)
- Data definition language (DDL data definition language), such as: building database, building table
Data manipulation language (DML)For example, adding, deleting and modifying records in the table
Data query language (DQL)For example, query operations in tables
- Data control language (DCL), such as the setting of user permissions
1.4-sql syntax considerations (understanding)
1: Each statement ends with a semicolon if it is not required in sqlog.
2: SQL is not case sensitive, and the upper case and lower case are the same in keywords.
3: There are three kinds of notes:
||This is the unique annotation method of MySQL|
Chapter 2: DDL operation database
2.1 – create database (key)
Create database database name;
Judge whether the database already exists, and create the database if it does not exist
Create database if not exists database name;
Create a database and specify a character set
Create database database name character set;
--Create a database called db1 create database db1; --If not, create a database called DB2 create database if not exists db2; --Create database db3 with GBK character set create database db3 character set gbk;
2.2 – view database (key)
View all databases
View the specified database
Show create database name
--View all databases show databases; --View the database named db1 show create database db1;
2.3 – database modification (key points)
Modifying the character set of a database
Alter database name default character set character set;
ALTER DATABASE db1 character set utf8;
2.4 – delete database (key)
Drop database database name;
DROP DATABASE db1;
2.5 – using databases (key points)
View databases used
Switch databases used
Use database name;
Chapter 3: DDL operation table structure
The premise is to use a database first.
3.1 – creating tables (key points)
Create table name（ Field name 1, field type 1, Field name 2 field type 2 );
3.2-mysql data type (key)
create table student ( Id int, - integer Name varchar (20), -- string Birthday date -- there is no comma at the end );
3.3 – view table (key)
View all tables in a database
View table structure
Desc table name
View the SQL statement that created the table
Show create table name
3.4 – quickly create a table with the same structure as the table (emphasis)
Create table new table name like old table name;
CREATE TABLE student LINKE stu;
3.5 – delete table (key)
Delete table directly
Drop table name;
Determine whether the table exists, and delete the table if it exists
Drop table if exists table name
DROP TABLE student; DROP TABLE IF EXISTS student
3.6 – modify table structure (key points)
Add a column
Alter table name add column name type
Modify the type of the column
Alter table name modify column name new type
Modify column name
Alter table name change old column name new column name type;
Alter table name DROP column name;
Modify table name
Rename table name to new table name;
Modify table name character set
Alter table name character set character set;
Chapter 4: data in DML operation table
4.1 – insert records (key points)
All field names
Insert into table name (field name 1, field name 2, field name 3 ）Values (value 1, value 2, value 3...);
Do not write the field name
Insert into table name values (value 1, value 2, value 3 );
Insert partial data
Insert into table name (field name 1, field name 2,...) values (value 1, value 2,...);
matters needing attention
- The inserted data should be of the same data type as the field
- The size of the data should be within the specified range of the column. For example, a string of length 80 cannot be added to a column of length 40.
- The data position listed in values must correspond to the arrangement position of the added column.
- Value can be used in mysql, but it is not recommended. Its function is the same as values.
- Character and date data should be enclosed in single quotation marks. Double quotation marks can also be used as separators in MySQL.
- Do not specify a column or use null to insert a null value.
4.2 – update records (key points)
Update table name set column name = value [where conditional expression] #Update: the name of the table that needs to be updated #Set: modified column value #Where: only qualified records are updated #You can update one or more fields at the same time. #You can specify any condition in the where clause.
Modifying data without conditions
Update table name set field name = value; -- modify all rows
Modify data with conditions
Update table name set field name = value where field name = value;
--Modify the data without conditions, change all gender to female Update student set sex ='female '; --Modify the data with conditions, and change the gender of the student whose ID number is 2 to male Update student set sex ='male 'where id = 2; --Modify multiple columns at a time, change the age of students with ID 3 to 26, and change the address to Beijing Update student set age = 26, address ='Beijing 'where id = 3;
4.3 – delete records (key points)
Delete from table name [where conditional expression] #If the where clause is not specified, all records in the MySQL table will be deleted. #You can specify any condition in the where clause
Delete data without conditions
Delete from table name
Delete data with conditions
Delete from table name where field name = value;
Use truncate to delete all records in the table
Truncate table name;
The difference between truncate and delete
Truncate is equivalent to deleting the structure of a table and creating another table.
--Delete data with conditions and delete the record with ID 1 delete from student where id=1; --Delete data without conditions, delete all data in the table delete from student;
Chapter 5: data in DQL query table
Select column name from table name [where conditional expression];
5.1 – simple query (key)
Query the data of all rows and columns in a table
Select * from table name;
Query the specified column
Select field name 1, field name 2, field name 3,... From table name;
select * from student; select name,age,sex from student;
5.2 – specify the alias of the column (emphasis)
The advantage of using aliases: use a new name when displaying, and do not modify the structure of the table.
Why tables use aliases: for multi table query operations.
Assign aliases to columns
Select field name 1 as alias, field name 2 as alias... From table name;
Assign aliases to column and table names
Select field name 1 as alias, field name 2 as alias... From table name as table alias;
--Use alias Select name as name, age as age from student; --Table use alias select st.name As name, age as age from student as St
5.3 – remove duplicate values (emphasis)
Query the specified column without duplicate data
Select distinct field name from table name;
--Where do students come from select address from student; --Remove duplicate records select distinct address from student;
5.4 – query results participate in operation (key)
Note: the number type must be involved in the operation
A column of data and fixed value operation
Select column name 1 + fixed value from table name;
One column data and other column data participate in the operation
Select column name 1 + column name 2 from table name;
Demand – prepare data: add the math and English score columns, add the corresponding math and English scores to each record, and add the math and English scores when querying
select * from student; --Give five points to all the math select math+5 from student; --Query the sum of math + English select * from student; (math + English) as total score from student; --As can be omitted Select *, (math + English) total score from student;
5.5 – conditional query (key)
Why query by condition?
If there is no query condition, all rows are queried each time. In practical application, it is necessary to specify the query conditions. Filtering records
Select field name from table name where condition;
Process: take out each data in the table, return the records that meet the conditions, and do not return the records that do not meet the conditions
CREATE TABLE student3 ( Id int, - number Name varchar (20), -- name Age int, - age Sex varchar (5), -- sex Address varchar (100), -- Address Math int, -- Mathematics English int ); INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES (1), 'ma Yun', 55, 'male', 'Hangzhou', 66,78), (2), 'ma Huateng', 45, 'female', 'Shenzhen', 98,87), (3, 'ma Jingtao', 55, 'male', 'Hong Kong', 56,77), (4, 'Liuyan', 20, 'female', 'Hunan', 76,65), (5, 'Liu Qing', 20, 'male', 'Hunan', 86, null), (6, 'Andy Lau', 57, 'male', 'Hong Kong', 99,99), (7, 'Mulder', 22, 'female', 'Hong Kong', 99,99), (8, 'demacia', 18, 'male', 'Nanjing', 56,65);
||Within a certain range, for example, between 100 and 200 is equivalent to the condition between 100 and 200, including the head and the tail|
||Sets represent multiple values, separated by commas|
||Query the value of a column that is null. Note: cannot write = null|
--Query the students whose math score is more than 80 select * from student3 where math>80; --Query the students whose English score is less than or equal to 80 select * from student3 where english <=80; --I'm a 20-year-old student select * from student3 where age = 20; --Query age is not equal to 20 years old students, note: does not mean there are two ways to write select * from student3 where age <> 20; select * from student3 where age != 20;
||The former is recommended in SQL, while the latter is not universal.|
--Query the students whose age is greater than 35 and gender is male (both conditions are met) Select * from student3 where age > 35 and sex ='male '; --Query the students whose age is greater than 35 or whose gender is male (one of the two conditions is met) Select * from student3 where age > 35 or sex ='male '; --Query the students whose ID is 1 or 3 or 5 select * from student3 where id=1 or id=3 or id=5;
Select field name from table name where field in (data 1, data 2...);
In each data will be as a condition, as long as meet the conditions will be displayed.
--Query the students whose ID is 1 or 3 or 5 select * from student3 where id in(1,3,5); --Query students whose ID is not 1 or 3 or 5 select * from student3 where id not in(1,3,5);
The value of between 1 and 2 indicates the range from value 1 to value 2, including the beginning and the end
age BETWEEN 80 AND 100amount to:
age>=80 && age<=100
Query the students whose English score is greater than or equal to 75 and less than or equal to 90
select * from student3 where english between 75 and 90;
Select * from table name where field name like 'wildcard string';
Like stands for fuzzy query
||Matches any number of strings|
||Match one character|
--Search for students surnamed Ma Select * from student3 where name like 'ma%'; Select * from student3 where name like 'horse'; --Search for students whose names contain 'de' Select * from student3 where name like '% de%'; --Search for students with two characters surnamed ma Select * from student3 where name like '_ ';
5.6 – ranking (key points)
Through the order by clause, you can sort the query results (sorting is only the display mode, and will not affect the order of data in the database)
Select field name from table name where field = value order by field name [ASC | desc];
ASC: ascending, default desc: descending
Single column sorting
Sort by only one field, single column sort.
--Query all data, using age descending sort select * from student order by age desc;
Multiple fields are sorted at the same time. If the first field is equal, the second field is sorted, and so on.
Select field name from table name where field = value order by field name 1 [ASC | desc], field name 2 [ASC | desc]; --Query all the data, on the basis of descending order of age, if the age is the same, then sort it in ascending order of mathematics score select * from student order by age desc, math asc;
5.7-aggregate function (emphasis)
The previous queries we made are horizontal queries, which are judged line by line according to the conditions, while the aggregate function query is vertical query, which calculates the value of a column, and then returns a result value. Aggregate functions ignore null values.
|Aggregate function in SQL||effect|
|Max (column name)||Find the maximum in this column|
|Min (column name)||Find the minimum value of this column|
|AVG (column name)||Find the average of this column|
|Count (column name)||Count how many records there are in this column|
|Sum (column name)||Sum this column|
Select aggregate function (column name) from table name;
--Query the total number of students Select count (ID) as total number of students; Select count (*) as total number of students;
Null records will not be counted. It is recommended that if the number of records is counted, columns that may be null should not be used. But what about counting null?
Ifnull (column name, default)If the column name is not empty, return the value of the column. If NULL, the default value is returned.
--Query the ID field. If it is null, use 0 instead select ifnull(id,0) from student;
We can use the ifnull() function, if the record is null, give a default value, so that the statistical data will not be missed.
select count(ifnull(id,0)) from student; --Query the total number of people older than 20 select count(*) from student where age>20; --Query the total score of Mathematics Select sum (Math) total score from student; --Query math grade average The average score of select AVG (Math) was from student; --Query the highest math score Select max (Math) from student; --Query the lowest score in Mathematics The lowest score of select min (Math) was from student;
Group query refers to the use of group by statement to group query information, the same data as a group
Select field 1, field 2... From table name group by [having condition];
How to group by
Take the same content in the result of grouping field as a group, such as dividing students into two groups according to gender.
Group by takes the same content in the results of grouped fields as a group, and returns the first data of each group, so it is useless to group them separately. The purpose of grouping is for statistics. Generally, grouping is used with aggregate function.
--Divide them into groups according to gender and get the average score of boys and girls select sex, avg(math) from student3 group by sex;
The effect is as follows
In fact, the math of each group is averaged and the statistical results of each group are returned
Note: when we use a field group, we need to query this field when querying, otherwise we can’t see which group the data belongs to
How many people are male and female
Query all data,
- They were grouped by gender.
- Count the number of people in each group
select sex, count(*) from student3 group by sex;
Query the people over 25 years old, group them by gender, and count the number of each group
- Filter out people younger than 25.
- Group them again.
- Finally, count the number of each group
select sex, count(*) from student3 where age > 25 group by sex ;
Query the people over 25 years old, group them by gender, count the number of people in each group, and only display the data that the number of people in gender is more than 2
--Filter the result of group query SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex having COUNT(*) >2;
The difference between having and where
||1. Before grouping the query results, remove the rows that do not meet the where condition, that is, filter the data before grouping, that is, filter before grouping. 2. Aggregate function cannot be used after where|
||1. The function of having clause is to filter the groups that meet the conditions, that is, to filter the data after grouping, that is, to filter after grouping. 2. Aggregate function can be used after having|
5.9-limit statement (emphasis)
INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES (9, Tang Seng, 25, male, Chang'an, 87, 78), (10, Monkey King, 18, male, Huaguo Mountain, 100, 66), (11, 'Zhu Bajie', 22, 'male', 'Gao Laozhuang', 58,78), (12, 'Shaseng', 50, 'male', 'Liushahe', 77,88), (13, 'Baigujing', 22, 'Nu', 'Baihuling', 66,66), (14, 'spider sperm', 23, 'female', 'pansidong', 88,88);
Limit means limit, so the function of limit is to limit the number of query records.
Select * | field list [as alias] from table name [where clause] [group by clause] [having clause] [order by clause] [limit clause];
Offset: the number of starting lines, counting from 0. If omitted, it is 0 by default
Length: the number of rows returned
--Query the data in the student table, and display it from the third item, and display 6 items. select * from student3 limit 2,6;
paging: for example, if we log on to Jingdong and Taobao, we may return tens of thousands of product information, not all of them will be displayed at one time. Is a page display fixed number. Suppose we paginate by displaying 5 records per page
--If the first parameter is 0, you can omit writing: select * from student3 limit 5; --Finally, if there are not enough 5 items, how many are displayed select * from student3 limit 10,5;
Chapter 6: constraints of database tables
6.1 – overview of Database Constraints (key points)
The role of constraints
Limit the data in the table to ensure the correctness, validity and integrity of the data. If a constraint is added to a table, incorrect data cannot be inserted into the table. It is appropriate to add constraints when creating tables.
Types of constraints
|Constraint name||Constraint keywords|
|Check constraints||Check note: MySQL does not support|
6.2 – primary key constraint (key)
The role of primary key
Used to uniquely identify each record in the database
Which field should be the primary key of the table
Usually, instead of using the business field as the primary key, an ID field is designed for each table, and the ID is used as the primary key.
The primary key is for the database and program, not for the final customer。 So it doesn’t matter whether the primary key has meaning or not, as long as it doesn’t repeat and is not empty.
Such as: ID card, student number is not recommended to be made into primary key.
Create primary key
- Primary key keyword:
Features of primary key:
- Not null
How to create a primary key
- To add a primary key to a field when creating a table:
Field name field type primary key;
- To add a primary key to an existing table:
Alter table name add primary key;
- To add a primary key to a field when creating a table:
--Create a student table ST5, which contains fields (ID, name, age) and takes ID as the primary key create table st5 ( Id int primary key, - ID is the primary key name varchar(20), age int ) desc st5;
Delete primary key
--Delete the primary key of ST5 table alter table st5 drop primary key;
Add primary key
alter table st5 add primary key(id);
Primary key auto increment
If we add the primary key ourselves, it is likely to be repeated. We usually hope that the database will automatically generate the value of the primary key field every time we insert a new record
AUTO_ Increment indicates auto growth (field type must be integer type) --Insert data Insert into ST6 (name, age) values ('xiao Qiao ', 18); Insert into ST6 (name, age) values ('daqiao ', 20); --Another way of writing Insert into ST6 values (null, 'Zhou Yu', 35); select * from st6;
Modify the default starting value of self growth
Auto by default_ The start value of increment is 1. If you want to modify the start value, use the following SQL syntax.
Specify the starting value when creating
Create table name（ Column name int primary key auto_ INCREMENT ) AUTO_ Increment = starting value; --Specify a starting value of 1000 create table st4 ( id int primary key auto_increment, name varchar(20) ) auto_increment = 1000; Insert into ST4 values (null, 'Kongming');
Modify start value
Alter table name Auto_ Increment = starting value; alter table st4 auto_increment = 2000;
6.3 – unique constraints (key points)
What is a unique constraint: a column in a table cannot have duplicate values
Field name field type unique
--Create a student table ST7, which contains fields (ID, name). The name column is set with a unique constraint. Students with the same name cannot appear create table st7 ( id int, name varchar(20) unique ) --Add a student with the same name Insert into ST7 values (1, 'Zhang San'); select * from st7; --Duplicate entry 'Zhang San' for key 'name' Insert into ST7 values (2, 'Zhang San'); --What happens when multiple nulls are inserted repeatedly? insert into st7 values (2, null); insert into st7 values (3, null); --Null has no data, and there is no duplication problem
6.4 – nonempty constraints (emphasis)
What is a non NULL constraint: a column cannot be null.
Field name field type not null --Create a student table ST8, which contains fields (ID, name, gender), where name cannot be null create table st8 ( id int, name varchar(20) not null, gender char(1) )
Field name field type default --Create a student table ST9, which contains fields (ID, name, address). The default address is Guangzhou create table st9 ( id int, name varchar(20), Address varchar (20) default 'Guangzhou' ) --Add a record and use the default address Insert into ST9 values (1, 'Li Si', default); select * from st9; Insert into ST9 (ID, name) values (2, 'Li Bai'); --Add a record without using the default address Insert into ST9 values (3, 'Li Siguang', 'Shenzhen');
Question: if a field is set with non null and unique constraints, what is the difference between the field and the primary key?
- There can only be one primary key in a table. Cannot have more than one primary key. The primary key can be single column or multi column.
- Self growth can only be used on primary keys.
6.5 – foreign key constraints (key)
Disadvantages of single table
Create an employee table containing the following (ID, name, age, DEP_ name, dep_ Location), ID primary key and automatically grow, add 5 pieces of data
CREATE TABLE emp ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(30), age INT, dep_name VARCHAR(30), dep_location VARCHAR(30) ); --Add data INSERT INTO emp (NAME, age, dep_ name, dep_ Location) values (Zhang San, 20, R & D department, Guangzhou); INSERT INTO emp (NAME, age, dep_ name, dep_ Location) values (Li Si, 21, R & D department, Guangzhou); INSERT INTO emp (NAME, age, dep_ name, dep_ Location) values (Wang Wu, 20, R & D department, Guangzhou); INSERT INTO emp (NAME, age, dep_ name, dep_ Location) values ('lao Wang ', 20,' sales department ',' Shenzhen '); INSERT INTO emp (NAME, age, dep_ name, dep_ Location) values ('dawang ', 22,' sales department ','shenzhen'); INSERT INTO emp (NAME, age, dep_ name, dep_ Location) values ('xiao Wang ', 18,' sales department ','shenzhen');
Disadvantages of the above table:
- data redundancy
- Later, there will be the problem of addition, deletion and modification
--Solution: divide into 2 tables --Create department table (ID, DEP)_ name,dep_ location) --One side, main table create table department( id int primary key auto_increment, dep_name varchar(20), dep_location varchar(20) ); --Create employee table (ID, name, age, DEP)_ ID) --Multi party, from table create table employee( id int primary key auto_increment, name varchar(20), age int, dep_ Id int -- the primary key of the main table corresponding to the foreign key ) --Add 2 departments Insert into department values (null, 'R & D department', 'Guangzhou'), (null, 'sales department', 'Shenzhen'); select * from department; --Add employee, DEP_ ID indicates the Department of the employee INSERT INTO employee (NAME, age, dep_ ID) values ('zhang San ', 20, 1); INSERT INTO employee (NAME, age, dep_ ID) values ('li Si ', 21, 1); INSERT INTO employee (NAME, age, dep_ ID) values ('wang Wu ', 20, 1); INSERT INTO employee (NAME, age, dep_ ID) values ('lao Wang ', 20, 2); INSERT INTO employee (NAME, age, dep_ ID) values ('dawang ', 22, 2); INSERT INTO employee (NAME, age, dep_ ID) values ('xiao Wang ', 18, 2); select * from employee;
New problems: when we are in employee’s dep_ If you enter a non-existent department in the ID, the data can still be added. However, there is no corresponding department, which can’t happen in practical application. Dep of employee_ The data in ID can only be the ID that exists in the Department table
target: constraint dep is required_ ID can only be an ID that already exists in the Department table
Solutions: use foreign key constraints
What are foreign key constraints
What are foreign keys: the column corresponding to the primary key of the master table in the slave table, such as DEP in the employee table_ ID
Main tableOne side, a table used to constrain others
From tableA table that is constrained by others
Syntax for creating constraints
- Add foreign key when creating a new table:
[constraint] [foreign key constraint name] foreign key references main table name (primary key field name)
- Add foreign key to existing table:
Alter table slave table add [constraint] [foreign key constraint name] foreign key (foreign key field name) references main table (primary key field name);
--1) delete sub table / sub table employee drop table employee; 18 / 26 --2) create the slave table employee and add the foreign key constraint EMP_ depid_ FK --Multi party, from table create table employee( id int primary key auto_increment, name varchar(20), age int, dep_ ID, int, -- foreign key corresponds to the primary key of the main table --Creating foreign key constraints constraint emp_depid_fk foreign key (dep_id) references department(id) ) --3) add data normally INSERT INTO employee (NAME, age, dep_ ID) values ('zhang San ', 20, 1); INSERT INTO employee (NAME, age, dep_ ID) values ('li Si ', 21, 1); INSERT INTO employee (NAME, age, dep_ ID) values ('wang Wu ', 20, 1); INSERT INTO employee (NAME, age, dep_ ID) values ('lao Wang ', 20, 2); INSERT INTO employee (NAME, age, dep_ ID) values ('dawang ', 22, 2); INSERT INTO employee (NAME, age, dep_ ID) values ('xiao Wang ', 18, 2); select * from employee;
Delete foreign key
Alter table drop foreign key name from the table; --Delete EMP from employee table_ depid_ FK foreign key alter table employee drop foreign key emp_depid_fk;
select * from employee; select * from department; --If you want to change the ID value from 2 to 5 in the Department table, can you update it directly? -- Cannot delete or update a parent row: a foreign key constraint fails update department set id=5 where id=2; --If you want to delete a department whose department ID is equal to 1, can you delete it directly? -- Cannot delete or update a parent row: a foreign key constraint fails delete from department where id=1;
Cascade operation:When the primary key of the primary table is modified or deleted, the foreign key value of the secondary table is updated or deleted at the same time, which is called cascade operation
|Cascading operation syntax||describe|
||Cascade update can only create a cascade relationship when creating a table. The primary key in the master table is updated, and the foreign key columns in the slave table are also updated automatically|
--Delete employee table, re create employee table, add cascade update and cascade delete drop table employee; create table employee( id int primary key auto_increment, name varchar(20), age int, dep_ ID, int, -- foreign key corresponds to the primary key of the main table --Creating foreign key constraints constraint emp_depid_fk foreign key (dep_id) references department(id) on update cascade on delete cascade ) --Add data to employee table and department table again INSERT INTO employee (NAME, age, dep_ ID) values ('zhang San ', 20, 1); INSERT INTO employee (NAME, age, dep_ ID) values ('li Si ', 21, 1); INSERT INTO employee (NAME, age, dep_ ID) values ('wang Wu ', 20, 1); INSERT INTO employee (NAME, age, dep_ ID) values ('lao Wang ', 20, 2); INSERT INTO employee (NAME, age, dep_ ID) values ('dawang ', 22, 2); INSERT INTO employee (NAME, age, dep_ ID) values ('xiao Wang ', 18, 2); --Delete department table? Can you delete it directly? drop table department; --Change the Department ID equal to 1 in the Department table to ID equal to 10 update department set id=10 where id=1; select * from employee; select * from department; --Delete the Department whose department number is 2 delete from department where id=2;
6.6 – Summary of data constraints (key points)
||1) Unique, 2) nonempty|
||If a column has no value, the default value is used|
||This column must have a value|
||This column cannot have duplicate values|
||Primary key column in master table, external key column in slave table|