1, Create
1. Create database
- View the original database of the system
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec)
Create your own database
mysql> create database if not exists huashanzhizai; Query OK, 1 row affected (0.00 sec)
2. View the database you created
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | huashanzhizai | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec)
Now that your database has been created, you should create your own tables in the database. It's equivalent to buying a cup. Now you can add water to the cup!
3. Select the database you created
mysql> use huashanzhizai; Database changed
4. Check whether the new table exists in the original database
mysql> drop table if exists stu_test; Query OK, 0 rows affected, 1 warning (0.00 sec)
5. Create a new table and assign attributes to it
mysql> create table stu_test( id INT, name varchar(10), password varchar(10), age int , sex varchar(1), birthday timestamp, amout decimal(6,2), resume text ); Query OK, 0 rows affected (0.04 sec)
6. View the table you created
mysql> show tables; +-------------------------+ | Tables_in_huashanzhizai | +-------------------------+ | stu_test | +-------------------------+ 1 row in set (0.00 sec)
- Add comments
mysql> drop table if exists stu_test; Query OK, 0 rows affected (0.02 sec) mysql> create table stu_test( id INT, name varchar(10) comment 'full name', password varchar(10) comment 'password', age int comment 'Age', sex varchar(1), birthday timestamp, amout decimal(6,2), resume text ); Query OK, 0 rows affected (0.04 sec)
7. Display fields
mysql> desc stu_test; +---------+---------------+----------+---------+---------------------+-------------------+ | Field | Type | Null | Key | Default | Extra | +---------+---------------+----------+---------+---------------------+-------------------+ | id | int(11) | YES | | NULL | | | name |varchar(10) | YES | | NULL | | | password|varchar(10) | YES | | NULL | | | age | int(11) | YES | | NULL | | | sex |varchar(1) | YES | | NULL | | | birthday| timestamp | NO | | CURRENT_TIMESTAMP |on update CURRENT_TIMESTAMP | | amout |decimal(6,2) | YES | | NULL | | | resume | text | YES | | NULL | | +---------+---------------+----------+---------+---------------------+-------------------+ 8 rows in set (0.01 sec)
2, CRUD (addition, deletion, query, modification)
1. Increase
- preparation
mysql> drop table if exists student; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> create table student( id int, sn int , name varchar(20), qq_mail varchar(20) ); Query OK, 0 rows affected (0.04 sec)
1. Full column insertion
//Single row full column insertion mysql> insert into student values(1,101,'bit','1963599369@qq.com'); Query OK, 1 row affected (0.01 sec) mysql> insert into student values(2,102,'yanghua','123@qq.com'); Query OK, 1 row affected (0.01 sec) //Only one row can be inserted at a time, and each field should correspond to the database
2. View
mysql> select *from student; +------+------+---------+-------------------+ | id | sn | name | qq_mail | +------+------+---------+-------------------+ | 1 | 101 | bit | 1963599369@qq.com | | 2 | 102 | yanghua | 123@qq.com | +------+------+---------+-------------------+ 2 rows in set (0.00 sec)
3. Insert specified column
mysql> insert into student (id,name) values(3,'kangxiangkun'),(4,'cuiyulu'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from student; +------+------+--------------+-------------------+ | id | sn | name | qq_mail | +------+------+--------------+-------------------+ | 1 | 101 | bit | 1963599369@qq.com | | 2 | 102 | yanghua | 123@qq.com | | 3 | NULL | kangxiangkun | NULL | | 4 | NULL | cuiyulu | NULL | +------+------+--------------+-------------------+ 4 rows in set (0.00 sec)
4. Delete the specified line
mysql> delete from student where id = 2; Query OK, 1 row affected (0.01 sec) mysql> select * from student; +------+------+--------------+-------------------+ | id | sn | name | qq_mail | +------+------+--------------+-------------------+ | 1 | 101 | bit | 1963599369@qq.com | | 3 | NULL | kangxiangkun | NULL | | 4 | NULL | cuiyulu | NULL | +------+------+--------------+-------------------+ 3 rows in set (0.00 sec)
5. Specify column query
mysql> select id,name from student; +------+--------------+ | id | name | +------+--------------+ | 1 | bit | | 3 | kangxiangkun | | 4 | cuiyulu | +------+--------------+ 3 rows in set (0.00 sec)
6. Add all numbers in id column + 10
mysql> select id+10,name from student; +-------+--------------+ | id+10 | name | +-------+--------------+ | 11 | bit | | 13 | kangxiangkun | | 14 | cuiyulu | +-------+--------------+ 3 rows in set (0.00 sec)
7. The query field is an expression
mysql> select id,name,10 from student; +------+--------------+----+ | id | name | 10 | +------+--------------+----+ | 1 | bit | 10 | | 3 | kangxiangkun | 10 | | 4 | cuiyulu | 10 | +------+--------------+----+ 3 rows in set (0.00 sec)
8. Alias
- Alias field
mysql> select id,name as full name from student; +------+--------------+ | id | full name | +------+--------------+ | 1 | bit | | 3 | kangxiangkun | | 4 | cuiyulu | +------+--------------+ 3 rows in set (0.00 sec)
- Table alias
mysql> select id,name from student as teacher; +------+--------------+ | id | name | +------+--------------+ | 1 | bit | | 3 | kangxiangkun | | 4 | cuiyulu | +------+--------------+ 3 rows in set (0.00 sec)
2. Check
- preparation
//Delete an existing table with the same name mysql> DROP TABLE IF EXISTS exam; Query OK, 0 rows affected, 1 warning (0.00 sec) //new table mysql> CREATE TABLE exam ( id INT, name VARCHAR(20), chinese DECIMAL(3,1), math DECIMAL(3,1), english DECIMAL(3,1) ); Query OK, 0 rows affected (0.05 sec) //Full column insertion mysql> INSERT INTO exam (id,name, chinese, math, english) VALUES (1,'Tang Sanzang', 67, 98, 56), (2,'Sun WuKong', 87.5, 78, 77), (3,'Pig Wuneng', 88, 98.5, 90), (4,'Cao mengde', 82, 84, 67), (5,'xuande ', 55.5, 85, 45), (6,'Sun Quan', 70, 73, 78.5), (7,'Song Gongming', 75, 65, 30); Query OK, 7 rows affected (0.01 sec) Records: 7 Duplicates: 0 Warnings: 0 //View field mysql> select * from exam; +------+-----------+---------+------+---------+ | id | name | chinese | math | english | +------+-----------+---------+------+---------+ | 1 | Tang Sanzang | 67.0 | 98.0 | 56.0 | | 2 | Sun WuKong | 87.5 | 78.0 | 77.0 | | 3 | Pig Wuneng | 88.0 | 98.5 | 90.0 | | 4 | Cao mengde | 82.0 | 84.0 | 67.0 | | 5 | xuande | 55.5 | 85.0 | 45.0 | | 6 | Sun Quan | 70.0 | 73.0 | 78.5 | | 7 | Song Gongming | 75.0 | 65.0 | 30.0 | +------+-----------+---------+------+---------+ 7 rows in set (0.00 sec)
1. Weight removal distinct
Remove the repetition of math scores and display them
mysql> select distinct math from exam_result; +------+ | math | +------+ | 98.0 | | 78.0 | | 84.0 | | 85.0 | | 73.0 | | 65.0 | +------+ 6 rows in set (0.00 sec)
2. Sort order by
The default is ascending
- In ascending order of math scores asc
mysql> select * from exam order by math asc; +------+-----------+---------+------+---------+ | id | name | chinese | math | english | +------+-----------+---------+------+---------+ | 7 | Song Gongming | 75.0 | 65.0 | 30.0 | | 6 | Sun Quan | 70.0 | 73.0 | 78.5 | | 2 | Sun WuKong | 87.5 | 78.0 | 77.0 | | 4 | Cao mengde | 82.0 | 84.0 | 67.0 | | 5 | xuande | 55.5 | 85.0 | 45.0 | | 1 | Tang Sanzang | 67.0 | 98.0 | 56.0 | | 3 | Pig Wuneng | 88.0 | 98.5 | 90.0 | +------+-----------+---------+------+---------+ 7 rows in set (0.00 sec)
- Descending desc
mysql> select * from exam order by math desc; +------+-----------+---------+------+---------+ | id | name | chinese | math | english | +------+-----------+---------+------+---------+ | 3 | Pig Wuneng | 88.0 | 98.5 | 90.0 | | 1 | Tang Sanzang | 67.0 | 98.0 | 56.0 | | 5 | xuande | 55.5 | 85.0 | 45.0 | | 4 | Cao mengde | 82.0 | 84.0 | 67.0 | | 2 | Sun WuKong | 87.5 | 78.0 | 77.0 | | 6 | Sun Quan | 70.0 | 73.0 | 78.5 | | 7 | Song Gongming | 75.0 | 65.0 | 30.0 | +------+-----------+---------+------+---------+ 7 rows in set (0.00 sec)
Note: do not use keywords as table names, such as DESC; If not, change the keyword table name to desc (the key under esc)
- Ascending when NULL exists in the sort field
//Insert a field mysql> insert into exam (id,name, chinese, math, english) values (8,'lu zhishen', 70, null, 38); Query OK, 1 row affected (0.01 sec) //display mysql> select * from exam; +------+-----------+---------+------+---------+ | id | name | chinese | math | english | +------+-----------+---------+------+---------+ | 1 | Tang Sanzang | 67.0 | 98.0 | 56.0 | | 2 | Sun WuKong | 87.5 | 78.0 | 77.0 | | 3 | Pig Wuneng | 88.0 | 98.5 | 90.0 | | 4 | Cao mengde | 82.0 | 84.0 | 67.0 | | 5 | xuande | 55.5 | 85.0 | 45.0 | | 6 | Sun Quan | 70.0 | 73.0 | 78.5 | | 7 | Song Gongming | 75.0 | 65.0 | 30.0 | | 8 | lu zhishen | 70.0 | NULL | 38.0 | +------+-----------+---------+------+---------+ 8 rows in set (0.00 sec) //Arrange in ascending mathematical order mysql> select * from exam order by math asc; +------+-----------+---------+------+---------+ | id | name | chinese | math | english | +------+-----------+---------+------+---------+ | 8 | lu zhishen | 70.0 | NULL | 38.0 | | 7 | Song Gongming | 75.0 | 65.0 | 30.0 | | 6 | Sun Quan | 70.0 | 73.0 | 78.5 | | 2 | Sun WuKong | 87.5 | 78.0 | 77.0 | | 4 | Cao mengde | 82.0 | 84.0 | 67.0 | | 5 | xuande | 55.5 | 85.0 | 45.0 | | 1 | Tang Sanzang | 67.0 | 98.0 | 56.0 | | 3 | Pig Wuneng | 88.0 | 98.5 | 90.0 | +------+-----------+---------+------+---------+ 8 rows in set (0.00 sec)
- Sort fields in descending order when NULL exists
mysql> select * from exam order by math desc; +------+-----------+---------+------+---------+ | id | name | chinese | math | english | +------+-----------+---------+------+---------+ | 3 | Pig Wuneng | 88.0 | 98.5 | 90.0 | | 1 | Tang Sanzang | 67.0 | 98.0 | 56.0 | | 5 | xuande | 55.5 | 85.0 | 45.0 | | 4 | Cao mengde | 82.0 | 84.0 | 67.0 | | 2 | Sun WuKong | 87.5 | 78.0 | 77.0 | | 6 | Sun Quan | 70.0 | 73.0 | 78.5 | | 7 | Song Gongming | 75.0 | 65.0 | 30.0 | | 8 | lu zhishen | 70.0 | NULL | 38.0 | +------+-----------+---------+------+---------+ 8 rows in set (0.00 sec)
- Sort multiple fields at the same time (chinese descending, math, english ascending)
mysql> select name,chinese,math, english from exam order by chinese desc,math,english asc; +-----------+---------+------+---------+ | name | chinese | math | english | +-----------+---------+------+---------+ | Pig Wuneng | 88.0 | 98.5 | 90.0 | | Sun WuKong | 87.5 | 78.0 | 77.0 | | Cao mengde | 82.0 | 84.0 | 67.0 | | Song Gongming | 75.0 | 65.0 | 30.0 | | lu zhishen | 70.0 | NULL | 38.0 | | Sun Quan | 70.0 | 73.0 | 78.5 | | Tang Sanzang | 67.0 | 98.0 | 56.0 | | xuande | 55.5 | 85.0 | 45.0 | +-----------+---------+------+---------+ 8 rows in set (0.00 sec)
3. Cumulative query+
mysql> select id,name,chinese+math+english from exam; +------+-----------+----------------------+ | id | name | chinese+math+english | +------+-----------+----------------------+ | 1 | Tang Sanzang | 221.0 | | 2 | Sun WuKong | 242.5 | | 3 | Pig Wuneng | 276.5 | | 4 | Cao mengde | 233.0 | | 5 | xuande | 185.5 | | 6 | Sun Quan | 221.5 | | 7 | Song Gongming | 170.0 | | 8 | lu zhishen | NULL | +------+-----------+----------------------+ 8 rows in set (0.00 sec)
4. Query criteria where
- where
- Query the id,name,math of students whose math scores are greater than 80
mysql> select id,name,math from exam where math > 80; +------+-----------+------+ | id | name | math | +------+-----------+------+ | 1 | Tang Sanzang | 98.0 | | 3 | Pig Wuneng | 98.5 | | 4 | Cao mengde | 84.0 | | 5 | xuande | 85.0 | +------+-----------+------+ 4 rows in set (0.00 sec)
- Query the ID, name and math of students whose math scores are greater than 80, and then sort them in ascending order according to their math scores
mysql> select id,name,math from exam where math > 80 order by math; +------+-----------+------+ | id | name | math | +------+-----------+------+ | 4 | Cao mengde | 84.0 | | 5 | xuande | 85.0 | | 1 | Tang Sanzang | 98.0 | | 3 | Pig Wuneng | 98.5 | +------+-----------+------+ 4 rows in set (0.00 sec)
- Query the id,name,math of students whose math scores are equal to 98
mysql> select id,name,math from exam where math = 98; +------+-----------+------+ | id | name | math | +------+-----------+------+ | 1 | Tang Sanzang | 98.0 | +------+-----------+------+ 1 row in set (0.00 sec)
- Note NULL: = unsafe. Cannot find NULL
mysql> select id,name,math from exam where math = NULL; Empty set (0.00 sec)
- Solution: use the < = > operator to find
mysql> select id,name,math from exam where math <=> NULL; +------+-----------+------+ | id | name | math | +------+-----------+------+ | 8 | lu zhishen | NULL | +------+-----------+------+ 1 row in set (0.00 sec)
- Query the id,name,math of students whose math scores are not equal to 98
mysql> select id,name,math from exam where math != 98; +------+-----------+------+ | id | name | math | +------+-----------+------+ | 2 | Sun WuKong | 78.0 | | 3 | Pig Wuneng | 98.5 | | 4 | Cao mengde | 84.0 | | 5 | xuande | 85.0 | | 6 | Sun Quan | 73.0 | | 7 | Song Gongming | 65.0 | +------+-----------+------+ 6 rows in set (0.00 sec)
- Values other than this value, and= Same effect < >
- It is not recommended to use < >, the index cannot be used, and the query efficiency is low
mysql> select id,name,math from exam where math <> 98; +------+-----------+------+ | id | name | math | +------+-----------+------+ | 2 | Sun WuKong | 78.0 | | 3 | Pig Wuneng | 98.5 | | 4 | Cao mengde | 84.0 | | 5 | xuande | 85.0 | | 6 | Sun Quan | 73.0 | | 7 | Song Gongming | 65.0 | +------+-----------+------+ 6 rows in set (0.00 sec)
- Query students with math scores between 70-90 = = id,name,math == A and B
mysql> select id,name,math from exam where math between 70 and 90; +------+-----------+------+ | id | name | math | +------+-----------+------+ | 2 | Sun WuKong | 78.0 | | 4 | Cao mengde | 84.0 | | 5 | xuande | 85.0 | | 6 | Sun Quan | 73.0 | +------+-----------+------+ 4 rows in set (0.00 sec)
- Query the = = id,name,math == in (x, y, z,...) of all students with math scores of 98, 85 and 73
mysql> select id,name,math from exam where math in(98,85,73); +------+-----------+------+ | id | name | math | +------+-----------+------+ | 1 | Tang Sanzang | 98.0 | | 5 | xuande | 85.0 | | 6 | Sun Quan | 73.0 | +------+-----------+------+ 3 rows in set (0.00 sec)
- or can also achieve the effect of accurate query
mysql> select id,name,math from exam where math = 98 or math = 85 or math = 73; +------+-----------+------+ | id | name | math | +------+-----------+------+ | 1 | Tang Sanzang | 98.0 | | 5 | xuande | 85.0 | | 6 | Sun Quan | 73.0 | +------+-----------+------+ 3 rows in set (0.01 sec)
- 6.NULL and NOT NULL
mysql> select id,name,math from exam where math is NULL; +------+-----------+------+ | id | name | math | +------+-----------+------+ | 8 | lu zhishen | NULL | +------+-----------+------+ 1 row in set (0.00 sec) mysql> select id,name,math from exam where math is NOT NULL; +------+-----------+------+ | id | name | math | +------+-----------+------+ | 1 | Tang Sanzang | 98.0 | | 2 | Sun WuKong | 78.0 | | 3 | Pig Wuneng | 98.5 | | 4 | Cao mengde | 84.0 | | 5 | xuande | 85.0 | | 6 | Sun Quan | 73.0 | | 7 | Song Gongming | 65.0 | +------+-----------+------+ 7 rows in set (0.00 sec)
5. Fuzzy query: like
preparation
//Add field mysql> insert into exam(id,name,chinese,math,english) values(9,'Zhang Sanfeng',10,11,90); Query OK, 1 row affected (0.01 sec) mysql> insert into exam values(10,'Zhang San',80,61,50); Query OK, 1 row affected (0.00 sec) mysql> select * from exam; +------+-----------+---------+------+---------+ | id | name | chinese | math | english | +------+-----------+---------+------+---------+ | 1 | Tang Sanzang | 67.0 | 98.0 | 56.0 | | 2 | Sun WuKong | 87.5 | 78.0 | 77.0 | | 3 | Pig Wuneng | 88.0 | 98.5 | 90.0 | | 4 | Cao mengde | 82.0 | 84.0 | 67.0 | | 5 | xuande | 55.5 | 85.0 | 45.0 | | 6 | Sun Quan | 70.0 | 73.0 | 78.5 | | 7 | Song Gongming | 75.0 | 65.0 | 30.0 | | 8 | lu zhishen | 70.0 | NULL | 38.0 | | 9 | Zhang Sanfeng | 10.0 | 11.0 | 90.0 | | 10 | Zhang San | 80.0 | 61.0 | 50.0 | +------+-----------+---------+------+---------+ 10 rows in set (0.00 sec)
- Query the information of students with = = = in name
mysql> select * from exam where name like '%three%'; +------+-----------+---------+------+---------+ | id | name | chinese | math | english | +------+-----------+---------+------+---------+ | 1 | Tang Sanzang | 67.0 | 98.0 | 56.0 | | 9 | Zhang Sanfeng | 10.0 | 11.0 | 90.0 | | 10 | Zhang San | 80.0 | 61.0 | 50.0 | +------+-----------+---------+------+---------+ 3 rows in set (0.00 sec)
- Query the information of students ending with "three" in name%
mysql> select * from exam where name like '%three'; +------+--------+---------+------+---------+ | id | name | chinese | math | english | +------+--------+---------+------+---------+ | 10 | Zhang San | 80.0 | 61.0 | 50.0 | +------+--------+---------+------+---------+ 1 row in set (0.00 sec)
- Query the information of students starting with "three" in name%
mysql> select * from exam where name like 'three%'; Empty set (0.00 sec)
Another fuzzy matching mechanism
- ’Sun Represents sun + a fuzzy matching word
mysql> select * from exam where name like 'Sun_'; +------+--------+---------+------+---------+ | id | name | chinese | math | english | +------+--------+---------+------+---------+ | 6 | Sun Quan | 70.0 | 73.0 | 78.5 | +------+--------+---------+------+---------+ 1 row in set (0.00 sec)
- ’Sun Represents sun + two fuzzy matching words
Insert code slice here
6. Paging query Limit
- Why pagination? Because loading too much data on a web page will affect the efficiency of cpu. If paging, the system will only load the rated data, which will improve the efficiency
mysql> select * from exam limit 0,5; +------+-----------+---------+------+---------+ | id | name | chinese | math | english | +------+-----------+---------+------+---------+ | 1 | Tang Sanzang | 67.0 | 98.0 | 56.0 | | 2 | Sun WuKong | 87.5 | 78.0 | 77.0 | | 3 | Pig Wuneng | 88.0 | 98.5 | 90.0 | | 4 | Cao mengde | 82.0 | 84.0 | 67.0 | | 5 | xuande | 55.5 | 85.0 | 45.0 | +------+-----------+---------+------+---------+ 5 rows in set (0.00 sec) mysql> select * from exam limit 1,4; +------+-----------+---------+---------+---------+ | id | name | chinese | math | english | +------+-----------+---------+---------+---------+ | 2 | Sun WuKong | 87.5 | 78.0 | 77.0 | | 3 | Pig Wuneng | 88.0 | 98.5 | 90.0 | | 4 | Cao mengde | 82.0 | 84.0 | 67.0 | | 5 | xuande | 55.5 | 85.0 | 45.0 | +------+-----------+---------+------+------------+ 4 rows in set (0.00 sec) //If the offset is too large, only a limited number of data in the table can be found mysql> select * from exam limit 5,10; +------+-----------+---------+------+---------+ | id | name | chinese | math | english | +------+-----------+---------+------+---------+ | 6 | Sun Quan | 70.0 | 73.0 | 78.5 | | 7 | Song Gongming | 75.0 | 65.0 | 30.0 | | 8 | lu zhishen | 70.0 | NULL | 38.0 | | 9 | Zhang Sanfeng | 10.0 | 11.0 | 90.0 | | 10 | Zhang San | 80.0 | 61.0 | 50.0 | +------+-----------+---------+------+---------+ 5 rows in set (0.00 sec) //If the starting number is too large, no data can be found mysql> select * from exam limit 12,5; Empty set (0.00 sec) //If the start data is not written, it starts from 0 by default mysql> select * from exam limit 5; +------+-----------+---------+------+---------+ | id | name | chinese | math | english | +------+-----------+---------+------+---------+ | 1 | Tang Sanzang | 67.0 | 98.0 | 56.0 | | 2 | Sun WuKong | 87.5 | 78.0 | 77.0 | | 3 | Pig Wuneng | 88.0 | 98.5 | 90.0 | | 4 | Cao mengde | 82.0 | 84.0 | 67.0 | | 5 | xuande | 55.5 | 85.0 | 45.0 | +------+-----------+---------+------+---------+ 5 rows in set (0.00 sec)
3. Change
1. Data update
- Update Sun Quan's math score to 99 points
//Before update: mysql> select * from exam; +------+-----------+---------+------+---------+ | id | name | chinese | math | english | +------+-----------+---------+------+---------+ | 1 | Tang Sanzang | 67.0 | 98.0 | 56.0 | | 2 | Sun WuKong | 87.5 | 78.0 | 77.0 | | 3 | Pig Wuneng | 88.0 | 98.5 | 90.0 | | 4 | Cao mengde | 82.0 | 84.0 | 67.0 | | 5 | xuande | 55.5 | 85.0 | 45.0 | | 6 | Sun Quan | 70.0 | 73.0 | 78.5 | | 7 | Song Gongming | 75.0 | 65.0 | 30.0 | | 8 | lu zhishen | 70.0 | NULL | 38.0 | | 9 | Zhang Sanfeng | 10.0 |11.0 | 90.0 | | 10 | Zhang San | 80.0 | 61.0 | 50.0 | +------+-----------+---------+------+---------+ 10 rows in set (0.00 sec) //Update code: mysql> update exam set math = 99 where name = 'Sun Quan'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 //After update: mysql> select *from exam; +------+-----------+---------+------+---------+ | id | name | chinese | math | english | +------+-----------+---------+------+---------+ | 1 | Tang Sanzang | 67.0 | 98.0 | 56.0 | | 2 | Sun WuKong | 87.5 | 78.0 | 77.0 | | 3 | Pig Wuneng | 88.0 | 98.5 | 90.0 | | 4 | Cao mengde | 82.0 | 84.0 | 67.0 | | 5 | xuande | 55.5 | 85.0 | 45.0 | | 6 | Sun Quan | 70.0 | 99.0 | 78.5 | | 7 | Song Gongming | 75.0 | 65.0 | 30.0 | | 8 | lu zhishen | 70.0 | NULL | 38.0 | | 9 | Zhang Sanfeng | 10.0 | 11.0 | 90.0 | | 10 | Zhang San | 80.0 | 61.0 | 50.0 | +------+-----------+---------+------+---------+ 10 rows in set (0.00 sec)
- Change Cao mengde's math score to = = 60 points = =, and his Chinese score to 70 points
//Before update: mysql> select * from exam; +------+-----------+---------+------+---------+ | id | name | chinese | math | english | +------+-----------+---------+------+---------+ | 1 | Tang Sanzang | 67.0 | 98.0 | 56.0 | | 2 | Sun WuKong | 87.5 | 78.0 | 77.0 | | 3 | Pig Wuneng | 88.0 | 98.5 | 90.0 | | 4 | Cao mengde | 82.0 | 84.0 | 67.0 | | 5 | xuande | 55.5 | 85.0 | 45.0 | | 6 | Sun Quan | 70.0 | 99.0 | 78.5 | | 7 | Song Gongming | 75.0 | 65.0 | 30.0 | | 8 | lu zhishen | 70.0 | NULL | 38.0 | | 9 | Zhang Sanfeng | 10.0 | 11.0 | 90.0 | | 10 | Zhang San | 80.0 | 61.0 | 50.0 | +------+-----------+---------+------+---------+ 10 rows in set (0.00 sec) //Update code: mysql> update exam set math = 60,chinese = 70 where name = 'Cao mengde'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 //After update: mysql> select * from exam; +------+-----------+---------+------+---------+ | id | name | chinese | math | english | +------+-----------+---------+------+---------+ | 1 | Tang Sanzang | 67.0 | 98.0 | 56.0 | | 2 | Sun WuKong | 87.5 | 78.0 | 77.0 | | 3 | Pig Wuneng | 88.0 | 98.5 | 90.0 | | 4 | Cao mengde | 70.0 | 60.0 | 67.0 | | 5 | xuande | 55.5 | 85.0 | 45.0 | | 6 | Sun Quan | 70.0 | 99.0 | 78.5 | | 7 | Song Gongming | 75.0 | 65.0 | 30.0 | | 8 | lu zhishen | 70.0 | NULL | 38.0 | | 9 | Zhang Sanfeng | 10.0 | 11.0 | 90.0 | | 10 | Zhang San | 80.0 | 61.0 | 50.0 | +------+-----------+---------+------+---------+ 10 rows in set (0.00 sec)
2. Comprehensive operation
- Add 30 points to the math scores of the top three students from the bottom of the total score
//Before update: reverse the order first, and then take the top three to find these three students mysql> select id ,name,chinese+english+math as total from exam order by chinese+english+math asc limit 1,3; +------+-----------+-------+ | id | name | total | +------+-----------+-------+ | 9 | Zhang Sanfeng | 111.0 | | 7 | Song Gongming | 170.0 | | 5 | xuande | 185.5 | +------+-----------+-------+ 3 rows in set (0.00 sec) //Update code: mysql> update exam set math = math - 30 where chinese + math + english is not NULL order by chinese + math + english limit 3; Query OK, 3 rows affected (0.01 sec) Rows matched: 3 Changed: 3 Warnings: 0 //After update: mysql> select * from exam; +------+-----------+---------+-------+---------+ | id | name | chinese | math | english | +------+-----------+---------+-------+---------+ | 1 | Tang Sanzang | 67.0 | 98.0 | 56.0 | | 2 | Sun WuKong | 87.5 | 78.0 | 77.0 | | 3 | Pig Wuneng | 88.0 | 98.5 | 90.0 | | 4 | Cao mengde | 70.0 | 60.0 | 67.0 | | 5 | xuande | 55.5 | 55.0 | 45.0 | | 6 | Sun Quan | 70.0 | 99.0 | 78.5 | | 7 | Song Gongming | 75.0 | 35.0 | 30.0 | | 8 | lu zhishen | 70.0 | NULL | 38.0 | | 9 | Zhang Sanfeng | 10.0 | -19.0| 90.0 | | 10 | Zhang San | 80.0 | 61.0 | 50.0 | +------+-----------+---------+-------+---------+ 10 rows in set (0.00 sec)
4. Delete
1. delete data in the table
- Delete Monkey King's test scores
//Original table code: mysql> select * from exam; +------+-----------+---------+------+---------+ | id | name | chinese | math | english | +------+-----------+---------+------+---------+ | 1 | Tang Sanzang | 67.0 | 98.0 | 56.0 | | 2 | Sun WuKong | 87.5 | 78.0 | 77.0 | | 3 | Pig Wuneng | 88.0 | 98.5 | 90.0 | | 4 | Cao mengde | 70.0 | 60.0 | 67.0 | | 5 | xuande | 55.5 | 85.0 | 45.0 | | 6 | Sun Quan | 70.0 | 99.0 | 78.5 | | 7 | Song Gongming | 75.0 | 65.0 | 30.0 | | 8 | lu zhishen | 70.0 | NULL | 38.0 | | 9 | Zhang Sanfeng | 10.0 | 11.0 | 90.0 | | 10 | Zhang San | 80.0 | 61.0 | 50.0 | +------+-----------+---------+------+---------+ 10 rows in set (0.00 sec) //Delete code: mysql> delete from exam where name = 'Sun WuKong'; Query OK, 1 row affected (0.01 sec) //After deletion: mysql> select * from exam; +------+-----------+---------+------+---------+ | id | name | chinese | math | english | +------+-----------+---------+------+---------+ | 1 | Tang Sanzang | 67.0 | 98.0 | 56.0 | | 3 | Pig Wuneng | 88.0 | 98.5 | 90.0 | | 4 | Cao mengde | 70.0 | 60.0 | 67.0 | | 5 | xuande | 55.5 | 85.0 | 45.0 | | 6 | Sun Quan | 70.0 | 99.0 | 78.5 | | 7 | Song Gongming | 75.0 | 65.0 | 30.0 | | 8 | lu zhishen | 70.0 | NULL | 38.0 | | 9 | Zhang Sanfeng | 10.0 | 11.0 | 90.0 | | 10 | Zhang San | 80.0 | 61.0 | 50.0 | +------+-----------+---------+------+---------+ 9 rows in set (0.00 sec) //Delete table directly: all data in the table will be deleted, but this table still exists, but there is no data in it mysql> delete from exam; Query OK, 9 rows affected (0.01 sec) //There is no data in the table mysql> select * from exam; Empty set (0.00 sec) //But the table is still there, but there is no data in the table mysql> show tables; +-------------------------+ | Tables_in_huashanzhizai | +-------------------------+ | exam | | student | +-------------------------+ 2 rows in set (0.00 sec)
2. Delete the table itself
mysql> drop table exam; Query OK, 0 rows affected (0.03 sec) //The table no longer exists mysql> show tables; +-------------------------+ | Tables_in_huashanzhizai | +-------------------------+ | student | +-------------------------+ 1 row in set (0.00 sec)
3. Delete database
- This operation is very dangerous. Don't try it easily
mysql> drop database if exists Library name;
3, Advanced query
1. Database Constraints
1.NULL constraint
The statement specifying the property cannot be NULL
//Table building and preparation mysql> drop table if exists student; Query OK, 0 rows affected (0.05 sec) //The specified name field cannot be NULL mysql> create table student( id int, sn int, name varchar(20) not NULL, qq_mail varchar(20) ); Query OK, 0 rows affected (0.15 sec) mysql> show tables; +-------------------------+ | Tables_in_huashanzhizai | +-------------------------+ | student | +-------------------------+ 1 row in set (0.00 sec) //At this point, you are ready to insert elements into the table mysql> insert into student(id,sn,name,qq_mail) values(1,101,NULL,'123@qq.com'); ERROR 1048 (23000): Column 'name' cannot be NULL //But the report is wrong for the following reasons mysql> desc student; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | sn | int(11) | YES | | NULL | | | name | varchar(20) | NO | | NULL | |//The name column cannot be null, otherwise an error is reported, which is a NULL constraint | qq_mail | varchar(20) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 4 rows in set (0.01 sec)
2. unique constraint
The statement specifying the attribute cannot be inserted twice
//Table building and preparation mysql> drop table if exists student; Query OK, 0 rows affected (0.02 sec) //The value of the specified sn field can only be unique mysql> create table student( id int, sn int unique, name varchar(20), qq_mail varchar(20) ); Query OK, 0 rows affected (0.04 sec) //Add first statement mysql> insert into student(id,sn,name,qq_mail) values(1,101,'bit','123@qq.com'); Query OK, 1 row affected (0.03 sec) //Effect after adding mysql> select * from student; +------+------+------+------------+ | id | sn | name | qq_mail | +------+------+------+------------+ | 1 | 101 | bit | 123@qq.com | +------+------+------+------------+ 1 row in set (0.00 sec) //When adding sn a second statement that is the same as the first statement: mysql> insert into student(id,sn,name,qq_mail) values(2,101,'bit2','1232@qq.com'); ERROR 1062 (23000): Duplicate entry '101' for key 'sn'
3. Default value constraint default
The value is assigned when the attribute is declared. If a member variable is added later, the system will assign the value by default
//Table building and preparation mysql> drop table if exists student; Query OK, 0 rows affected (0.03 sec) //Specify QQ_ The default value of mail is 110@qq.com mysql> create table student( id int, sn int, name varchar(20), qq_mail varchar(20) default '110@qq.com' ); Query OK, 0 rows affected (0.05 sec) //First time to qq_mail assignment: mysql> insert into student(id,sn,name,qq_mail) values(1,101,'bit','123@qq.com'); Query OK, 1 row affected (0.01 sec) mysql> select * from student; +------+------+------+------------+ | id | sn | name | qq_mail | +------+------+------+------------+ | 1 | 101 | bit | 123@qq.com | +------+------+------+------------+ 1 row in set (0.00 sec) //The second time to QQ_ When mail is assigned NULL: mysql> insert into student(id,sn,name,qq_mail) values(2,102,'bit2',NULL); Query OK, 1 row affected (0.01 sec) mysql> select * from student; +------+------+------+------------+ | id | sn | name | qq_mail | +------+------+------+------------+ | 1 | 101 | bit | 123@qq.com | | 2 | 102 | bit2 | NULL | +------+------+------+------------+ 2 rows in set (0.00 sec) //Don't give QQ for the third time_ Mail assignment: mysql> insert into student(id,sn,name) values(3,103,'bit3'); Query OK, 1 row affected (0.02 sec) mysql> select * from student; +------+------+------+------------+ | id | sn | name | qq_mail | +------+------+------+------------+ | 1 | 101 | bit | 123@qq.com | | 2 | 102 | bit2 | NULL | | 3 | 103 | bit3 | 110@qq.com | +------+------+------+------------+ 3 rows in set (0.00 sec) //Found not to QQ_ When mail is assigned a value, the system will automatically assign qq_mail with the default values we set at the beginning
4. primary key constraint
The combination of NOT NULL and unique, that is, when a field is modified by primary key, then the field cannot be empty and is unique!!! General Collocation:
auto_increment, self increasing function
auto_decrement function
//Table building and preparation mysql> drop table if exists student; Query OK, 0 rows affected (0.02 sec) //Add a primary key constraint to the id field mysql> create table student( id int primary key auto_increment, sn int unique, name varchar(20) NOT NULL, qq_mail varchar(20) DEFAULT '110@qq.com' ); Query OK, 0 rows affected (0.04 sec) //After the table is created, there is no data in the table. When the insertion is performed for the first time, the current primary key, that is, ID, will automatically start from 1 mysql> insert into student(sn,name,qq_mail) values(101,'bit','123@qq.com'); Query OK, 1 row affected (0.01 sec) mysql> select * from student; +----+------+------+------------+ | id | sn | name | qq_mail | +----+------+------+------------+ | 1 | 101 | bit | 123@qq.com | +----+------+------+------------+ 1 row in set (0.00 sec) //Clear data mysql> delete from student where id = 1; Query OK, 1 row affected (0.01 sec) mysql> select * from student; Empty set (0.00 sec) //When I delete the data I just inserted and insert it again, I will add 1 to the original basis, that is, the ID of the last inserted statement mysql> insert into student(sn,name,qq_mail) values(101,'bit','123@qq.com'); Query OK, 1 row affected (0.01 sec) mysql> select * from student; +----+------+------+------------+ | id | sn | name | qq_mail | +----+------+------+------------+ | 2 | 101 | bit | 123@qq.com | +----+------+------+------------+ 1 row in set (0.00 sec) //If you want id=1 again, you need to delete the table and start again!!!
5. foreign key constraint
Foreign keys are used to associate primary keys or unique keys of other tables
For example, Xiong Er is under the jurisdiction of Xiong da
Syntax: foreign key references main table (column)
//1. Build table
mysql> drop table if exists classes;
Query OK, 0 rows affected, 1 warning (0.00 sec)
//Create classes class table with id as primary key
mysql> create table classes(
id int primary key auto_increment,
name varchar(20),
`desc` varchar(30)
);
Query OK, 0 rows affected (0.04 sec)
mysql> drop table if exists student;
Query OK, 0 rows affected, 1 warning (0.00 sec)
//Then create a student table. One student corresponds to one class and one class corresponds to multiple students. Use id as the primary key and classes_id is a foreign key, associated with class table id
mysql> create table student(
id int primary key auto_increment,
sn int unique,
name varchar(20) NOT NULL,
qq_mail varchar(20) default '110@qq.com',
classes_id int,
foreign key (classes_id) references classes(id)
);
Query OK, 0 rows affected (0.05 sec)
//View table creation succeeded
mysql> show tables;
+-------------------------+
| Tables_in_huashanzhizai |
+-------------------------+
| classes |
| student |
+-------------------------+
2 rows in set (0.00 sec)
//2. Insert (can grow by itself)
//Insert main table first
mysql> insert into classes (name,`desc`)
values('java18','Refueling duck today!');
Query OK, 1 row affected (0.00 sec)
mysql> select * from classes;
+----+--------+--------------------------+
| id | name | desc |
+----+--------+--------------------------+
| 1 | java18 | Refueling duck today! |
+----+--------+--------------------------+
1 row in set (0.00 sec)
//Insert schedule again
mysql> insert into student (sn,name,qq_mail,classes_id)
values(101,'bit','123@qq.com',1);
mysql> insert into student (sn,name,qq_mail,classes_id) values
(102,'bit','365@qq.com',1);
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+------+------+------------+------------+
| id | sn | name | qq_mail | classes_id |
+----+------+------+------------+------------+
| 1 | 101 | bit | 123@qq.com | 1 |
| 2 | 102 | bit | 365@qq.com | 1 |
+----+------+------+------------+------------+
2 rows in set (0.00 sec)
//Where classes_id is related to class id, that is, these students are all students of this class
//3. Delete rule
//First delete the primary table associated with the child table or delete the primary table not associated with the child table, otherwise an error will be reported
//If the class is gone, there will be no place for the students to have classes On the contrary, students go first, but it will not affect the existence of the class
mysql> delete from student
where id = 1 or id = 2;
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
Empty set (0.00 sec)
//Then delete the main table
mysql> delete from classes
where id = 1;
Query OK, 1 row affected (0.01 sec)
mysql> select * from classes;
Empty set (0.00 sec)
6.check constraints
Check constraints can be used to enforce simple rules, such as column values must be within a certain range.
mysql> drop table if exists test_user; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> create table test_user ( id int, name varchar(20), sex varchar(1), check (sex ='male' or sex='female') //It means that only 'male' or 'female' can be inserted in sex, otherwise the system will report an error ); Query OK, 0 rows affected (0.04 sec) mysql> show tables; +-------------------------+ | Tables_in_huashanzhizai | +-------------------------+ | classes | | student | | test_user | +-------------------------+ 3 rows in set (0.00 sec) mysql> insert into test_user values(1,'bit','Ha'); Query OK, 1 row affected (0.01 sec) //But there will be no error on MySQL!!!
2. Advanced query
- Table building and preparation
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | huashanzhizai | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> use huashanzhizai; Database changed mysql> show tables; +-------------------------+ | Tables_in_huashanzhizai | +-------------------------+ | classes | | exam | | student | | test_user | +-------------------------+ 4 rows in set (0.00 sec) mysql> select * from exam; Empty set (0.00 sec) mysql> drop table if exists exam; Query OK, 0 rows affected (0.03 sec) mysql> create table exam ( id int, name varchar(20), chinese decimal(3,1), math decimal(3,1), english decimal(3,1) ); Query OK, 0 rows affected (0.10 sec) mysql> insert into exam (id,name, chinese, math, english) values (1,'Tang Sanzang', 67, 98, 56), (2,'Sun WuKong', 87.5, 78, 77), (3,'Pig Wuneng', 88, 98.5, 90), (4,'Cao mengde', 82, 84, 67), (5,'xuande ', 55.5, 85, 45), (6,'Sun Quan', 70, 73, 78.5), (7,'Song Gongming', 75, 65, 30); Query OK, 7 rows affected (0.01 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> select * from exam; +------+-----------+---------+------+---------+ | id | name | chinese | math | english | +------+-----------+---------+------+---------+ | 1 | Tang Sanzang | 67.0 | 98.0 | 56.0 | | 2 | Sun WuKong | 87.5 | 78.0 | 77.0 | | 3 | Pig Wuneng | 88.0 | 98.5 | 90.0 | | 4 | Cao mengde | 82.0 | 84.0 | 67.0 | | 5 | xuande | 55.5 | 85.0 | 45.0 | | 6 | Sun Quan | 70.0 | 73.0 | 78.5 | | 7 | Song Gongming | 75.0 | 65.0 | 30.0 | +------+-----------+---------+------+---------+ 7 rows in set (0.00 sec)
1. Aggregate function
| function | explain |
|---|---|
| count (column) | Find the quantity of data |
| sum (numeric column) | Total score |
| avg (numeric column) | Find the average of a column |
| max (column) | Find the maximum value |
| min (column) | Find the minimum value |
//1. Quantity of query data mysql> select count(*) from exam; +----------+ | count(*) | +----------+ | 7 | +----------+ 1 row in set (0.01 sec) //2. sum weighted summation mysql> select sum(math) from exam; +-----------+ | sum(math) | +-----------+ | 679.5 | +-----------+ 1 row in set (0.00 sec) mysql> select sum(math)from exam where math < 70; +-----------+ | sum(math) | +-----------+ | 65.0 | +-----------+ 1 row in set (0.00 sec) //3. avg query average score mysql> select avg(math) from exam; +-----------+ | avg(math) | +-----------+ | 84.93750 | +-----------+ 1 row in set (0.00 sec) //4. max query maximum mysql> select max(math) from exam; +-----------+ | max(math) | +-----------+ | 98.5 | +-----------+ 1 row in set (0.00 sec) //5. min query minimum mysql> select min(math) from exam; +-----------+ | min(math) | +-----------+ | 65.0 | +-----------+ 1 row in set (0.00 sec)
Note: aggregate functions should not appear after where
mysql> select id,name from exam where max(math) > 60; ERROR 1111 (HY000): Invalid use of group function
2. group by
- Prepare data
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| huashanzhizai |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use huashanzhizai;
Database changed
mysql> drop tables if exists emp;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> create table emp(
id int primary key auto_increment,
name varchar(20) not null,
role varchar(20) not null,
salary numeric(11,2)
);
Query OK, 0 rows affected (0.08 sec)
mysql> insert into emp(name, role, salary) values
('Peng Ge','lecturer', 1000.20),
('Gao Bo','lecturer', 2000.99),
('Old soup','lecturer', 999.11),
('Quiet','headmaster', 333.5),
('Sister Sasha','headmaster', 700.33),
('Lao Wang next door','market', 12000.66);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from emp;
+----+--------------+-----------+
| id | name | role | salary |
+----+--------------+-----------+
| 1 | Peng Ge | lecturer | 1000.20 |
| 2 | Gao Bo | lecturer | 2000.99 |
| 3 | Old soup | lecturer | 999.11 |
| 4 | Quiet | headmaster | 333.50 |
| 5 | Sister Sasha | headmaster | 700.33 |
| 6 | Lao Wang next door | market | 12000.66 |
+----+--------------+----------+
6 rows in set (0.00 sec)
- Query the maximum wage, minimum wage and average wage of each role
mysql> select role, max(salary), min(salary), avg(salary) from emp group by role; +-----------+-------------+-------------+--------------+ | role | max(salary) | min(salary) | avg(salary) | +-----------+-------------+-------------+--------------+ | market | 12000.66 | 12000.66 | 12000.660000 | | headmaster | 700.33 | 333.50 | 516.915000 | //As a head teacher, this class is the highest, the lowest and the average | lecturer | 2000.99 | 999.11 | 1333.433333 | +-----------+-------------+-------------+--------------+ 3 rows in set (0.03 sec) //Incorrect expression //difference mysql> select max(salary), min(salary), avg(salary) from emp; +-------------+-------------+-------------+ | max(salary) | min(salary) | avg(salary) | +-------------+-------------+-------------+ | 12000.66 | 333.50 | 2839.131667 | //This is the highest, lowest and average in a table +-------------+-------------+-------------+ 1 row in set (0.00 sec)
- Filtering condition
mysql> select role, max(salary), min(salary), avg(salary) from emp group by role having avg(salary) < 1500; +-----------+-------------+-------------+-------------+ | role | max(salary) | min(salary) | avg(salary) | +-----------+-------------+-------------+-------------+ | headmaster | 700.33 | 333.50 | 516.915000 | | lecturer | 2000.99 | 999.11 | 1333.433333 | +-----------+-------------+-------------+-------------+ 2 rows in set (0.00 sec)
3. Joint query
(two or more tables can be connected for query)
- preparation
1.//New library test0311
mysql> drop database if exists test0311;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create database test0311;
Query OK, 1 row affected (0.01 sec)
mysql> use test0311;
Database changed
mysql> drop table if exists classes;
Query OK, 0 rows affected, 1 warning (0.00 sec)
2.//Create classes class table in test0311 Library
mysql> create table classes(
id int primary key auto_increment,
name varchar(50),
`desc` varchar(50)
);
Query OK, 0 rows affected (0.07 sec)
mysql> insert into classes(name, `desc`) values
('Class 1, grade 2019, computer department', 'Learned the principle of computer C and Java Language, data structure and algorithm'),
('Class 3, grade 2019, Chinese Department','Studied Chinese traditional literature'),
('Class 5 of automation 2019','Learned mechanical automation');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
//Create student table in test0311 Library
mysql> drop table if exists student;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table student(
id int primary key auto_increment,
sn int,
name varchar(30),
qq_mail varchar(30),
classes_id int
);
Query OK, 0 rows affected (0.09 sec)
mysql> insert into student(sn, name, qq_mail, classes_id) values
('09982','Black Whirlwind Li Kui','xuanfeng@qq.com',1),
('00835','The Grapes ',null,1),
('00391','Bai Suzhen',null,1),
('00031','Xu Xian','xuxian@qq.com',1),
('00054','Don't want to graduate',null,1),
('51234','Speak in a normal way','say@qq.com',2),
('83223','tellme',null,2),
('09527','Foreigners learn Chinese','foreigner@qq.com',2);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
//Create course timetable in test0311 Library
mysql> drop table if exists course;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table course(
id int primary key auto_increment,
name varchar(20)
);
Query OK, 0 rows affected (0.05 sec)
mysql> insert into course(name) values
('Java'),('Chinese traditional culture'),('Computer principle'),('language'),('Higher order mathematics'),('english');
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
//Create score table in test0311 Library
mysql> drop table if exists score;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table score(
id int primary key auto_increment,
score DECIMAL,
student_id int,
course_id int
);
Query OK, 0 rows affected (0.04 sec)
mysql> insert into score(score, student_id, course_id) values
-- Black Whirlwind Li Kui
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
-- The Grapes -> (60, 2, 1),(59.5, 2, 5),
-- Bai Suzhen
(33, 3, 1),(68, 3, 3),(99, 3, 5),
-- Xu Xian
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
-- Don't want to graduate
(81, 5, 1),(37, 5, 5),
-- Speak in a normal way
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- tellme
(80, 7, 2),(92, 7, 6);
Query OK, 20 rows affected, 3 warnings (0.01 sec)
Records: 20 Duplicates: 0 Warnings: 3
3.//View various tables that have been built
mysql> show tables;
+--------------------+
| Tables_in_test0311 |
+--------------------+
| classes |
| course |
| score |
| student |
+--------------------+
4 rows in set (0.00 sec)
//Query classes class table
mysql> select * from classes;
+----+-------------------------+-------------------------------------------------------------------+
| id | name |desc |
+----+-------------------------+-------------------------------------------------------------------+
| 1 | Class 1, grade 2019, computer department | Learned the principle of computer C and Java Language, data structure and algorithm |
| 2 | Class 3, grade 2019, Chinese Department | Studied Chinese traditional literature |
| 3 | Class 5 of automation 2019 | Learned mechanical automation |
+----+-------------------------+-------------------------------------------------------------------+
3 rows in set (0.00 sec)
//Query course schedule
mysql> select * from course;
+----+--------------------+
| id | name |
+----+--------------------+
| 1 | Java |
| 2 | Chinese traditional culture |
| 3 | Computer principle |
| 4 | language |
| 5 | Higher order mathematics |
| 6 | english |
+----+--------------------+
6 rows in set (0.00 sec)
//Query score table
mysql> select * from score;
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
| 1 | 71 | 1 | 1 |
| 2 | 99 | 1 | 3 |
| 3 | 33 | 1 | 5 |
| 4 | 98 | 1 | 6 |
| 5 | 60 | 2 | 1 |
| 6 | 60 | 2 | 5 |
| 7 | 33 | 3 | 1 |
| 8 | 68 | 3 | 3 |
| 9 | 99 | 3 | 5 |
| 10 | 67 | 4 | 1 |
| 11 | 23 | 4 | 3 |
| 12 | 56 | 4 | 5 |
| 13 | 72 | 4 | 6 |
| 14 | 81 | 5 | 1 |
| 15 | 37 | 5 | 5 |
| 16 | 56 | 6 | 2 |
| 17 | 43 | 6 | 4 |
| 18 | 79 | 6 | 6 |
| 19 | 80 | 7 | 2 |
| 20 | 92 | 7 | 6 |
+----+-------+------------+-----------+
20 rows in set (0.00 sec)
//Query student table
mysql> select * from student;
+----+-------+-----------------+------------------+------------+
| id | sn | name | qq_mail | classes_id |
+----+-------+-----------------+------------------+------------+
| 1 | 9982 | Black Whirlwind Li Kui | xuanfeng@qq.com | 1 |
| 2 | 835 | The Grapes | NULL | 1 |
| 3 | 391 | Bai Suzhen | NULL | 1 |
| 4 | 31 | Xu Xian | xuxian@qq.com | 1 |
| 5 | 54 | Don't want to graduate | NULL | 1 |
| 6 | 51234 | Speak in a normal way | say@qq.com | 2 |
| 7 | 83223 | tellme | NULL | 2 |
| 8 | 9527 | Foreigners learn Chinese | foreigner@qq.com | 2 |
+----+-------+-----------------+------------------+------------+
8 rows in set (0.00 sec)
Inner connection
Syntax 1: select field from table 1 alias 1 inner join table 2 alias 2 on connection condition and other conditions;
Syntax 2: select field from table 1 alias 1, table 2 alias 2 where connection conditions and other conditions;
- Query the results of "Xu Xian" (gradually increase constraints)
Syntax 1 presentation
- Query all Cartesian products of student and score tables (this method is used with caution and affects performance)
mysql> select * from student inner join score; ...//Too many, we won't show them one by one ... ... 160 rows in set (0.01 sec)
- Displays the id in the student table and the student in the score table_ id two equal corresponding values
mysql> select * from student inner join score on student.id = score.student_id; +----+-------+-----------------+-----------------+------------+----+-------+------------+-----------+ | id | sn | name | qq_mail | classes_id | id | score |student_id | course_id | +----+-------+-----------------+-----------------+------------+----+-------+------------+-----------+ | 1 | 9982 | Black Whirlwind Li Kui | xuanfeng@qq.com | 1 | 1 | 71 | 1 | 1 | | 1 | 9982 | Black Whirlwind Li Kui | xuanfeng@qq.com | 1 | 2 | 99 | 1 | 3 | | 1 | 9982 | Black Whirlwind Li Kui | xuanfeng@qq.com | 1 | 3 | 33 | 1 | 5 | | 1 | 9982 | Black Whirlwind Li Kui | xuanfeng@qq.com | 1 | 4 | 98 | 1 | 6 | | 2 | 835 | The Grapes | NULL | 1 | 5 | 60 | 2 | 1 | | 2 | 835 | The Grapes | NULL | 1 | 6 | 60 | 2 | 5 | | 3 | 391 | Bai Suzhen | NULL | 1 | 7 | 33 | 3 | 1 | | 3 | 391 | Bai Suzhen | NULL | 1 | 8 | 68 | 3 | 3 | | 3 | 391 | Bai Suzhen | NULL | 1 | 9 | 99 | 3 | 5 | | 4 | 31 | Xu Xian | xuxian@qq.com | 1 | 10 | 67 | 4 | 1 | | 4 | 31 | Xu Xian | xuxian@qq.com | 1 | 11 | 23 | 4 | 3 | | 4 | 31 | Xu Xian | xuxian@qq.com | 1 | 12 | 56 | 4 | 5 | | 4 | 31 | Xu Xian | xuxian@qq.com | 1 | 13 | 72 | 4 | 6 | | 5 | 54 | Don't want to graduate | NULL | 1 | 14 | 81 | 5 | 1 | | 5 | 54 | Don't want to graduate | NULL | 1 | 15 | 37 | 5 | 5 | | 6 | 51234 | Speak in a normal way | say@qq.com | 2 | 16 | 56 | 6 | 2 | | 6 | 51234 | Speak in a normal way | say@qq.com | 2 | 17 | 43 | 6 | 4 | | 6 | 51234 | Speak in a normal way | say@qq.com | 2 | 18 | 79 | 6 | 6 | | 7 | 83223 | tellme | NULL | 2 | 19 | 80 | 7 | 2 | | 7 | 83223 | tellme | NULL | 2 | 20 | 92 | 7 | 6 | +----+-------+-----------------+-----------------+------------+----+-------+------------+-----------+ 20 rows in set (0.01 sec)
- Displays all the contents of the student and score tables
mysql> select * from student inner join score on student.id = score.student_id and student.id = 4; +----+------+--------+---------------+------------+----+-------+------------+-----------+ | id | sn | name | qq_mail | classes_id | id | score | student_id |course_id | +----+------+--------+---------------+------------+----+-------+------------+-----------+ | 4 | 31 | Xu Xian | xuxian@qq.com | 1 | 10 | 67 | 4 | 1 | | 4 | 31 | Xu Xian | xuxian@qq.com | 1 | 11 | 23 | 4 | 3 | | 4 | 31 | Xu Xian | xuxian@qq.com | 1 | 12 | 56 | 4 | 5 | | 4 | 31 | Xu Xian | xuxian@qq.com | 1 | 13 | 72 | 4 | 6 | +----+------+--------+---------------+------------+----+-------+------------+-----------+ 4 rows in set (0.01 sec)
- Displays the selected contents of the student and score tables
mysql> select student.id,student.name,score.score from student inner join score on student.id = score.student_id and student.id = 4; +----+--------+-------+ | id | name | score | +----+--------+-------+ | 4 | Xu Xian | 67 | | 4 | Xu Xian | 23 | | 4 | Xu Xian | 56 | | 4 | Xu Xian | 72 | +----+--------+-------+ 4 rows in set (0.00 sec)
- Course with score table_ ID joins the course table as well
mysql> select student.id,student.name,score.score,course.name from student inner join score on student.id = score.student_id inner join course on score.course_id = course.id and student.id = 4; +----+--------+-------+-----------------+ | id | name | score | name | +----+--------+-------+-----------------+ | 4 | Xu Xian | 67 | Java | | 4 | Xu Xian | 23 | Computer principle | | 4 | Xu Xian | 56 | Higher order mathematics | | 4 | Xu Xian | 72 | english | +----+--------+-------+-----------------+ 4 rows in set (0.00 sec)
- Change name
mysql> select student.id,student.name as full name,score.score as fraction,course.name as Class Schedule Card from student inner join score on student.id = score.student_id inner join course on score.course_id = course.id and student.id = 4; +----+--------+--------+-----------------+ | id | full name | fraction | Class Schedule Card | +----+--------+--------+-----------------+ | 4 | Xu Xian | 67 | Java | | 4 | Xu Xian | 23 | Computer principle | | 4 | Xu Xian | 56 | Higher order mathematics | | 4 | Xu Xian | 72 | english | +----+--------+--------+-----------------+ 4 rows in set (0.00 sec)
Syntax 2 Demo:
mysql> select student.id,student.name as full name,score.score as fraction,course.name as Class Schedule Card from student,score,course where student.id = score.student_id and score.course_id = course.id and student.id = 4; +----+--------+--------+-----------------+ | id | full name | fraction | Class Schedule Card | +----+--------+--------+-----------------+ | 4 | Xu Xian | 67 | Java | | 4 | Xu Xian | 23 | Computer principle | | 4 | Xu Xian | 56 | Higher order mathematics | | 4 | Xu Xian | 72 | english | +----+--------+--------+-----------------+ 4 rows in set (0.00 sec)
External connection
(external connections are divided into left external connections and right external connections. If the table on the left is fully displayed, we say it is a left external connection; if the table on the right is fully displayed, we say it is a right external connection)
Query the grades of all students and their personal information. If the student has no grades, it also needs to be displayed
- Previously created table
//View various tables that have been built mysql> show tables; +--------------------+ | Tables_in_test0311 | +--------------------+ | classes | | course | | score | | student | +--------------------+ 4 rows in set (0.00 sec) //Query classes class table mysql> select * from classes; +----+-------------------------+-------------------------------------------------------------------+ | id | name |desc | +----+-------------------------+-------------------------------------------------------------------+ | 1 | Class 1, grade 2019, computer department | Learned the principle of computer C and Java Language, data structure and algorithm | | 2 | Class 3, grade 2019, Chinese Department | Studied Chinese traditional literature | | 3 | Class 5 of automation 2019 | Learned mechanical automation | +----+-------------------------+-------------------------------------------------------------------+ 3 rows in set (0.00 sec) //Query course schedule mysql> select * from course; +----+--------------------+ | id | name | +----+--------------------+ | 1 | Java | | 2 | Chinese traditional culture | | 3 | Computer principle | | 4 | language | | 5 | Higher order mathematics | | 6 | english | +----+--------------------+ 6 rows in set (0.00 sec) //Query score table mysql> select * from score; +----+-------+------------+-----------+ | id | score | student_id | course_id | +----+-------+------------+-----------+ | 1 | 71 | 1 | 1 | | 2 | 99 | 1 | 3 | | 3 | 33 | 1 | 5 | | 4 | 98 | 1 | 6 | | 5 | 60 | 2 | 1 | | 6 | 60 | 2 | 5 | | 7 | 33 | 3 | 1 | | 8 | 68 | 3 | 3 | | 9 | 99 | 3 | 5 | | 10 | 67 | 4 | 1 | | 11 | 23 | 4 | 3 | | 12 | 56 | 4 | 5 | | 13 | 72 | 4 | 6 | | 14 | 81 | 5 | 1 | | 15 | 37 | 5 | 5 | | 16 | 56 | 6 | 2 | | 17 | 43 | 6 | 4 | | 18 | 79 | 6 | 6 | | 19 | 80 | 7 | 2 | | 20 | 92 | 7 | 6 | +----+-------+------------+-----------+ 20 rows in set (0.00 sec) //Query student table mysql> select * from student; +----+-------+-----------------+------------------+------------+ | id | sn | name | qq_mail | classes_id | +----+-------+-----------------+------------------+------------+ | 1 | 9982 | Black Whirlwind Li Kui | xuanfeng@qq.com | 1 | | 2 | 835 | The Grapes | NULL | 1 | | 3 | 391 | Bai Suzhen | NULL | 1 | | 4 | 31 | Xu Xian | xuxian@qq.com | 1 | | 5 | 54 | Don't want to graduate | NULL | 1 | | 6 | 51234 | Speak in a normal way | say@qq.com | 2 | | 7 | 83223 | tellme | NULL | 2 | | 8 | 9527 | Foreigners learn Chinese | foreigner@qq.com | 2 | +----+-------+-----------------+------------------+------------+ 8 rows in set (0.00 sec)
- Left outer connection
mysql> select * from student,score; ... ...//There are too many results to show one by one ... 160 rows in set (0.01 sec) //Sort by id in student table mysql> select * from student,score group by student.id; +----+-------+-----------------+------------------+------------+----+-------+------------+-----------+ | id | sn | name | qq_mail | classes_id | id | score |student_id | course_id | +----+-------+-----------------+------------------+------------+----+-------+------------+-----------+ | 1 | 9982 | Black Whirlwind Li Kui | xuanfeng@qq.com | 1 | 1 | 71 | 1 | 1 | | 2 | 835 | The Grapes | NULL | 1 | 1 | 71 | 1 | 1 | | 3 | 391 | Bai Suzhen | NULL | 1 | 1 | 71 | 1 | 1 | | 4 | 31 | Xu Xian | xuxian@qq.com | 1 | 1 | 71 | 1 | 1 | | 5 | 54 | Don't want to graduate | NULL | 1 | 1 | 71 | 1 | 1 | | 6 | 51234 | Speak in a normal way | say@qq.com | 2 | 1 | 71 | 1 | 1 | | 7 | 83223 | tellme | NULL | 2 | 1 | 71 | 1 | 1 | | 8 | 9527 | Foreigners learn Chinese | foreigner@qq.com | 2 | 1 | 71 | 1 | 1 | +----+-------+-----------------+------------------+------------+----+-------+------------+-----------+ 8 rows in set (0.01 sec) mysql> select * from student,score where student.id = score.student_id group by student.id; +----+-------+-----------------+-----------------+------------+----+-------+------------+-----------+ | id | sn | name | qq_mail | classes_id | id | score |student_id | course_id | +----+-------+-----------------+-----------------+------------+----+-------+------------+-----------+ | 1 | 9982 | Black Whirlwind Li Kui | xuanfeng@qq.com | 1 | 1 | 71 | 1 | 1 | | 2 | 835 | The Grapes | NULL | 1 | 5 | 60 | 2 | 1 | | 3 | 391 | Bai Suzhen | NULL | 1 | 7 | 33 | 3 | 1 | | 4 | 31 | Xu Xian | xuxian@qq.com | 1 | 10 | 67 | 4 | 1 | | 5 | 54 | Don't want to graduate | NULL | 1 | 14 | 81 | 5 | 1 | | 6 | 51234 | Speak in a normal way | say@qq.com | 2 | 16 | 56 | 6 | 2 | | 7 | 83223 | tellme | NULL | 2 | 19 | 80 | 7 | 2 | +----+-------+-----------------+-----------------+------------+----+-------+------------+-----------+ 7 rows in set (0.00 sec)//It can be displayed by using the left outer connection //At this time, it is found that there is no name in the student table, that is, there is no score student_ ID matches it mysql> select * from student left join score on student.id = score.student_id group by student.id; +----+-------+-----------------+------------------+------------+------+-------+------------+-----------+ | id | sn | name | qq_mail | classes_id | id | score |student_id | course_id | +----+-------+-----------------+------------------+------------+------+-------+------------+-----------+ | 1 | 9982 | Black Whirlwind Li Kui | xuanfeng@qq.com | 1 | 1 | 71 | 1 | 1 | | 2 | 835 | The Grapes | NULL | 1 | 5 | 60 | 2 | 1 | | 3 | 391 | Bai Suzhen | NULL | 1 | 7 | 33 | 3 | 1 | | 4 | 31 | Xu Xian | xuxian@qq.com | 1 | 10 | 67 | 4 | 1 | | 5 | 54 | Don't want to graduate | NULL | 1 | 14 | 81 | 5 | 1 | | 6 | 51234 | Speak in a normal way | say@qq.com | 2 | 16 | 56 | 6 | 2 | | 7 | 83223 | tellme | NULL | 2 | 19 | 80 | 7 | 2 | | 8 | 9527 | Foreigners learn Chinese | foreigner@qq.com | 2 | NULL | NULL | NULL | NULL | +----+-------+-----------------+------------------+------------+------+-------+------------+-----------+ 8 rows in set (0.00 sec)
- Right outer connection
mysql> select * from score right join student on student.id = score.student_id group by student.id; +------+-------+------------+-----------+----+-------+-----------------+------------------+------------+ | id | score | student_id | course_id | id | sn | name |qq_mail | classes_id | +------+-------+------------+-----------+----+-------+-----------------+------------------+------------+ | 1 | 71 | 1 | 1 | 1 | 9982 | Black Whirlwind Li Kui |xuanfeng@qq.com | 1 | | 5 | 60 | 2 | 1 | 2 | 835 | The Grapes |NULL | 1 | | 7 | 33 | 3 | 1 | 3 | 391 | Bai Suzhen |NULL | 1 | | 10 | 67 | 4 | 1 | 4 | 31 | Xu Xian |xuxian@qq.com | 1 | | 14 | 81 | 5 | 1 | 5 | 54 | Don't want to graduate |NULL | 1 | | 16 | 56 | 6 | 2 | 6 | 51234 | Speak in a normal way |say@qq.com | 2 | | 19 | 80 | 7 | 2 | 7 | 83223 | tellme |NULL | 2 | | NULL | NULL | NULL | NULL | 8 | 9527 | Foreigners learn Chinese |foreigner@qq.com | 2 | +------+-------+------------+-----------+----+-------+-----------------+------------------+------------+ 8 rows in set (0.00 sec)
Self connection
Self join refers to connecting itself in the same table for query
Display all the information that the score of "computer principle" is higher than that of "Java"
mysql> select * from score as s1,score as s2 //At this time, because the names of the two tables are the same, they need to be aliased where s1.student_id = 1 and s2.student_id = 3 and s1.score < s2.score; +----+-------+------------+-----------+----+-------+------------+-----------+ | id | score | student_id | course_id | id | score | student_id | course_id | +----+-------+------------+-----------+----+-------+------------+-----------+ | 3 | 33 | 1 | 5 | 8 | 68 | 3 | 3 | | 1 | 71 | 1 | 1 | 9 | 99 | 3 | 5 | | 3 | 33 | 1 | 5 | 9 | 99 | 3 | 5 | | 4 | 98 | 1 | 6 | 9 | 99 | 3 | 5 | +----+-------+------------+-----------+----+-------+------------+-----------+ 4 rows in set (0.00 sec) //improvement mysql> select s2.* from score as s1,score as s2 where s1.student_id = 1 and s2.student_id = 3 and s1.score < s2.score; +----+-------+------------+-----------+ | id | score | student_id | course_id | +----+-------+------------+-----------+ | 8 | 68 | 3 | 3 | | 9 | 99 | 3 | 5 | | 9 | 99 | 3 | 5 | | 9 | 99 | 3 | 5 | +----+-------+------------+-----------+ 4 rows in set (0.00 sec)
Subquery
Subquery refers to the select statement embedded in other sql statements, also known as nested query
Inquire about classmates who don't want to graduate
mysql> select *
from student
where classes_id = (select classes_id from student where name = 'Don't want to graduate');
+----+------+-----------------+-----------------+------------+
| id | sn | name | qq_mail | classes_id |
+----+------+-----------------+-----------------+------------+
| 1 | 9982 | Black Whirlwind Li Kui | xuanfeng@qq.com | 1 |
| 2 | 835 | The Grapes | NULL | 1 |
| 3 | 391 | Bai Suzhen | NULL | 1 |
| 4 | 31 | Xu Xian | xuxian@qq.com | 1 |
| 5 | 54 | Don't want to graduate | NULL | 1 |
+----+------+-----------------+-----------------+------------+
5 rows in set (0.01 sec)
//This sentence is equivalent to
mysql> select *
from student
where classes_id = 1;
+----+------+-----------------+-----------------+------------+
| id | sn | name | qq_mail | classes_id |
+----+------+-----------------+-----------------+------------+
| 1 | 9982 | Black Whirlwind Li Kui | xuanfeng@qq.com | 1 |
| 2 | 835 | The Grapes | NULL | 1 |
| 3 | 391 | Bai Suzhen | NULL | 1 |
| 4 | 31 | Xu Xian | xuxian@qq.com | 1 |
| 5 | 54 | Don't want to graduate | NULL | 1 |
+----+------+-----------------+-----------------+------------+
5 rows in set (0.00 sec)
//Multi line sub query: return the sub query of multi line records to query the score information of "Chinese" or "English" courses. Use in
mysql> select *
from score
where course_id
in(select id from course where name =('language' or name='english');
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
| 4 | 98 | 1 | 6 |
| 13 | 72 | 4 | 6 |
| 17 | 43 | 6 | 4 |
| 18 | 79 | 6 | 6 |
| 20 | 92 | 7 | 6 |
+----+-------+------------+-----------+
5 rows in set (0.00 sec)
//Use not in
mysql> select *
from score
where course_id
not in(select id from course where name != 'language' and name !='english');
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
| 4 | 98 | 1 | 6 |
| 13 | 72 | 4 | 6 |
| 17 | 43 | 6 | 4 |
| 18 | 79 | 6 | 6 |
| 20 | 92 | 7 | 6 |
+----+-------+------------+-----------+
5 rows in set (0.00 sec)
//Use not exists (as long as the expression in parentheses is true, the statement outside parentheses will be executed)
mysql> select *
from student
where exists(select id from student where id = 1);
+----+-------+-----------------+------------------+------------+
| id | sn | name | qq_mail | classes_id |
+----+-------+-----------------+------------------+------------+
| 1 | 9982 | Black Whirlwind Li Kui | xuanfeng@qq.com | 1 |
| 2 | 835 | The Grapes | NULL | 1 |
| 3 | 391 | Bai Suzhen | NULL | 1 |
| 4 | 31 | Xu Xian | xuxian@qq.com | 1 |
| 5 | 54 | Don't want to graduate | NULL | 1 |
| 6 | 51234 | Speak in a normal way | say@qq.com | 2 |
| 7 | 83223 | tellme | NULL | 2 |
| 8 | 9527 | Foreigners learn Chinese | foreigner@qq.com | 2 |
+----+-------+-----------------+------------------+------------+
8 rows in set (0.02 sec)
//Joint query (Union will automatically remove duplicate rows in the result set)
mysql> select *
from student
where id <= 3
union
select * from student where name = 'Bai Suzhen';
+----+------+-----------------+-----------------+------------+
| id | sn | name | qq_mail | classes_id |
+----+------+-----------------+-----------------+------------+
| 1 | 9982 | Black Whirlwind Li Kui | xuanfeng@qq.com | 1 |
| 2 | 835 | The Grapes | NULL | 1 |
| 3 | 391 | Bai Suzhen | NULL | 1 |
+----+------+-----------------+-----------------+------------+
3 rows in set (0.01 sec)
mysql> select *
from student
where id <= 3
union
select * from student where name = 'Speak in a normal way';
+----+-------+-----------------+-----------------+------------+
| id | sn | name | qq_mail | classes_id |
+----+-------+-----------------+-----------------+------------+
| 1 | 9982 | Black Whirlwind Li Kui | xuanfeng@qq.com | 1 |
| 2 | 835 | The Grapes | NULL | 1 |
| 3 | 391 | Bai Suzhen | NULL | 1 |
| 6 | 51234 | Speak in a normal way | say@qq.com | 2 |
+----+-------+-----------------+-----------------+------------+
4 rows in set (0.00 sec)
4, Database index
1. What is an index?
An index is a data structure. Database index is a sort data structure in database management system to help quickly query and update the data in database tables.
To put it more popularly: an index is equivalent to a directory.
2. Why use index?
for example
In the case of no index, the database is required to search the name of a student in the list of 10000 students. When the database finds one, it will not stop searching, but traverse the whole database. In this way, it will lose performance.
3. Advantages of index
- It can greatly speed up the speed of retrieving data.
- By creating a unique index, you can ensure the uniqueness of each row of data in the database table.
- In the process of grouping and sorting clauses, the time of grouping and sorting in the query process can be reduced.
- In the query process, we can use the optimization hidden device to improve the performance of the system.
4. Disadvantages of index
- Space: the index needs to occupy a certain amount of data space.
- Time: it takes time to add, delete and modify database tables (equivalent to inserting and deleting on B tree / B + tree), which reduces the performance.
5. Scenario of index use
- Columns that often need to be searched.
- The column that is the primary key.
- It is often used in connected columns. These columns are mainly foreign keys, which can speed up the connection.
- Columns that often need to be searched by range.
- Columns that often need to be sorted.
- It is often used on the column above the where clause.
6. Avoid scenarios where indexes are used
- Columns are rarely used in the query process.
- For columns with few data values. For example, the gender column of personnel table and the column of bit data type.
- For those columns defined as text,image. Because the amount of data in these columns is quite large.
- When the requirements for modification performance are much greater than the search performance. Because when the index is added, the search performance will be improved, but the modification performance will be reduced (equivalent to inserting and deleting on the B tree / B + tree).
7. Type of index
- General index
The basic index type has no uniqueness. In order to speed up the use of queries. Specify directly when creating a table.
create table mytable( ID int not NULL, username varchar(16) not NULL, index [indexName] (username(length)) );
Delete index
drop index [indexName] on mytable;
- unique index
Duplicate data columns are not allowed. NULL values are allowed. A table allows multiple columns to create unique indexes.
create table mytable( ID int not NULL, username varchar(16) not NULL, unique [indexName] (username(length)) );
- primary key
Data columns cannot be duplicate or NULL. A table has only one primary key. It is usually specified directly when creating a table.
mysql> create table user (
id int unsigned not NULL auto_increment,
name varchar(50) not NULL,
email varchar(50) not NULL,
primary key(id)
);
mysql> insert into user(name,email)values
('Zhang San','111111@qq.com'),
('Li Si','22222@qq.com');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from user;
+----+--------+---------------+
| id | name | email |
+----+--------+---------------+
| 1 | Zhang San | 111111@qq.com |
| 2 | Li Si | 22222@qq.com |
+----+--------+---------------+
2 rows in set (0.00 sec)
- Full text index
It is a key technology used by search engines at present.
8. Wildcard invalid
- The index fails when the wildcard appears at the beginning of the search term.
select * from student where name like '%huasahnzhizai';
- If not is used on the index column, a full table scan will be used.
select * from student where not (score=100); select * from student where score <> 100; select * from student where score != 100; select * from student where score not in (80,90,100); //not exist does not take the index
- When the data type is string type, if the conditional data is not enclosed in quotation marks, the index is invalid.
select * from student where dept_id = 1
//Should use:
select * from student where dept_id = '1'
- When or is used, if the conditions of each column do not have an index, the index will become invalid.
//name in the student table is indexed, but sex is not indexed select * from student where name = 'zhangsan' or sex = 1
- Combined index, instead of using the first column index, the index is invalid.
//Establish a composite index (key1, key2); select * from key1 = 1;--The combined index is valid; select * from key1 = 1 and key2= 2;--The combined index is valid; select * from key2 = 2;--Composite index is invalid; Does not conform to the leftmost prefix principle
- When the full table scanning speed is faster than the index speed, the database will use the full table scanning, and the index will become invalid.
9. Data structure used for index
In order to minimize I/O operations, the computer system generally adopts the way of disk pre reading, and the length of pre reading is generally an integral multiple of the page. Each storage block is called a page.
- B tree (multiple balanced search tree)
Each node in the B tree contains the key value and the address pointer of the data object for which the key value is stored, so the successful search of an object can not reach the leaf node of the tree.
If we want to find the user information with id=28, the search process in the B tree above is as follows:
First find the root node page 1, judge that 28 is between the key values 17 and 35, and find page 3 according to the pointer p2 in page 1. (1 IO)
Compare 28 with the key value in page 3. When 28 is between 26 and 30, find page 8 according to the pointer p2 in page 3. (1 IO)
Compare the key value 28 with the key value in page 8 and find the matching key value 28. The user information corresponding to the key value 28 is (28, zx). (1 IO)
- B + tree
The difference between B tree and B + tree
(1) The non leaf nodes of the B + tree do not store data and only store key values, while the B tree nodes store not only key values but also data.
(2) All data of the B + tree index is stored in the leaf node, and the data is arranged in order. Each page in the B + tree is connected through a two-way linked list, and the data in the leaf node is connected through a one-way linked list.
5, Business
1. Why use transactions?
Prepare test sheet
drop table if exists accout;
create table accout(
id int primary key auto_increment,
name varchar(20) comment 'title of account',
money decimal(11,2) comment 'amount of money'
);
insert into accout(name, money) values
('Alibaba', 5000),
('The Forty Thieves', 1000);
For example, forty thieves stole 2000 yuan from Alibaba's account
//Alibaba account decreased by 2000 update accout set money=money-2000 where name = 'Alibaba'; //Forty thieves account increased by 2000 update accout set money=money+2000 where name = 'The Forty Thieves'
If there is a network error or the database hangs up when executing the first sentence of SQL above, Alibaba's account will be reduced by 2000, but there will be no increased amount on the account of the forty thieves.
Solution: use transaction control to ensure that all the above two SQL sentences are executed successfully or fail.
2. Concept of affairs
A transaction is a logical set of operations. The units that make up this set of operations either succeed or fail.
There can be transactions in different environments. The corresponding in the database is the database transaction.
3. Transaction characteristics (ACID)
- Atomicity: refers to that all operations contained in a transaction either succeed or fail.
- Consistency: the collection of States after data submission is called consistency, that is, the database only contains the state of transaction submission.
- Isolation: concurrency. For any two concurrent transactions A and B, in the view of transaction A, B either ends before A starts or starts after A ends, so that each transaction does not feel that other transactions are executing concurrently.
- Durability: once a transaction is committed, it is saved to the hard disk. The changes to the data in the database are permanent.
4. Use of transactions
-
Start transaction: start transaction;
-
Execute multiple SQL statements
-
Rollback or commit: rollback/commit;
rollback means all failures, and commit means all successes
start transaction; -- Alibaba account decreased by 2000 update accout set money=money-2000 where name = 'Alibaba'; -- Forty thieves account increased by 2000 update accout set money=money+2000 where name = 'The Forty Thieves'; commit;
Over!