如何一天学完SQL基本操作？看这里，这篇文章帮助你完成SQL语言快速入门；
里面夹杂很多SQL的查询练习，想通过这篇文章直接变成SQL高手是不可能的。
但是想要入门，唬唬外行人，是完全没问题的。
终端如何操作数据库？
登陆数据库
mysql -u"username" -p"password"
# 例如
mysql -uroot -p1234
# 退出数据库
exit;
查看数据库及查看数据表
# 数据库展示
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| Learn |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
# 数据表展示
mysql> show tables;
+-----------------+
| Tables_in_learn |
+-----------------+
| abc |
+-----------------+
1 row in set (0.00 sec)
选中数据库并查询数据
# 选中数据库
mysql> use Learn
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
# 查询数据
mysql> select * from abc where ID=1402;
+------+-------+------+--------+----------------+-----------------------------+------------+------------------+--------------+-----------------+---------------------+-----------------------------------------+
| ID | Label | AGE | GENDER | MARITAL_STATUS | MONTHLY_INCOME_WHITHOUT_TAX | LOANTYPE | GAGE_TOTLE_PRICE | APPLY_AMOUNT | APPLY_TERM_TIME | APPLY_INTEREST_RATE | PAYMENT_TYPE |
+------+-------+------+--------+----------------+-----------------------------+------------+------------------+--------------+-----------------+---------------------+-----------------------------------------+
| 1402 | 1 | 35 | Female | Single | 8500 | Frist-Hand | 426002 | 340000 | 360 | 4.158 | Average_Capital_Plus_Interest_Repayment |
| 1402 | 1 | 35 | Female | Single | 8500 | Frist-Hand | 426002 | 340000 | 360 | 4.158 | Average_Capital_Plus_Interest_Repayment |
+------+-------+------+--------+----------------+-----------------------------+------------+------------------+--------------+-----------------+---------------------+-----------------------------------------+
2 rows in set (0.00 sec)
如何在数据库服务器中创建数据
# create database "database_name";
# 例如创建一个test数据库；
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| Learn |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
6 rows in set (0.00 sec)
创建数据表
# create table if not exists table_name (column1 type1, column2 type2, ...) default charset=utf8;
# 例如创建一个叫做student的数据表
mysql> create table student (ID INT(20), name VARCHAR(20), birth DATE) default charset=utf8;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
删除数据库及数据表
# drop table table_name; drop database database_name;
mysql> drop table student;
Query OK, 0 rows affected (0.00 sec)
mysql> drop database test;
Query OK, 0 rows affected (0.01 sec)
查看创建好的数据表的结构
# describe table_name;
mysql> describe abc;
+-----------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------------+--------------+------+-----+---------+-------+
| ID | varchar(255) | YES | | NULL | |
| Label | varchar(255) | YES | | NULL | |
| AGE | varchar(255) | YES | | NULL | |
| GENDER | varchar(255) | YES | | NULL | |
| MARITAL_STATUS | varchar(255) | YES | | NULL | |
| MONTHLY_INCOME_WHITHOUT_TAX | varchar(255) | YES | | NULL | |
| LOANTYPE | varchar(255) | YES | | NULL | |
| GAGE_TOTLE_PRICE | varchar(255) | YES | | NULL | |
| APPLY_AMOUNT | varchar(255) | YES | | NULL | |
| APPLY_TERM_TIME | varchar(255) | YES | | NULL | |
| APPLY_INTEREST_RATE | varchar(255) | YES | | NULL | |
| PAYMENT_TYPE | varchar(255) | YES | | NULL | |
+-----------------------------+--------------+------+-----+---------+-------+
12 rows in set (0.01 sec)
mysql> describe student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| birth | date | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
往创建好的数据表中插入数据
# insert into table_name values ();
mysql> insert into student values (20, "Li Min", '2009-01-27');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values (18, "小智", '2004-01-27');
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+------+--------+------------+
| ID | name | birth |
+------+--------+------------+
| 20 | Li Min | 2009-01-27 |
| 18 | 小智 | 2004-01-27 |
+------+--------+------------+
2 rows in set (0.00 sec)
MySQL常用数据类型
# 大致分为三种 数值 日期/时间 字符串
数据记录的增删改查
# 当数据表里存在两条一样的数据时，该如何删除？
mysql> select * from student;
+------+--------+------------+
| ID | name | birth |
+------+--------+------------+
| 20 | Li Min | 2009-01-27 |
| 18 | 小智 | 2004-01-27 |
| 8 | tiny | 2029-01-27 |
| 30 | pony | 2029-04-23 |
| 30 | pony | 2029-04-23 |
+------+--------+------------+
5 rows in set (0.00 sec)
# 全部删除之后再重新添加一条？
mysql> delete from student where ID=30;
Query OK, 2 rows affected (0.01 sec)
mysql> insert into student values (30, "pony", '2029-04-23');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+------+--------+------------+
| ID | name | birth |
+------+--------+------------+
| 20 | Li Min | 2009-01-27 |
| 18 | 小智 | 2004-01-27 |
| 8 | tiny | 2029-01-27 |
| 30 | pony | 2029-04-23 |
+------+--------+------------+
4 rows in set (0.00 sec)
# 如何修改数据
mysql> update student set name='Zek Shawn' where ID=18;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+------+-----------+------------+
| ID | name | birth |
+------+-----------+------------+
| 20 | Li Min | 2009-01-27 |
| 18 | Zek Shawn | 2004-01-27 |
| 8 | tiny | 2029-01-27 |
| 30 | pony | 2029-04-23 |
+------+-----------+------------+
4 rows in set (0.00 sec)
MySQL 建表约束
- 主键约束，它可以为唯一确定一张表中的一条记录，通过给某个字段添加约束，就可以使得该字段不重复且不为空；
# 设立 ID 为主键
mysql> create table user (id int primary key, name VARCHAR(20)) default charset=utf8;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> insert into user values (1, "zhangsan");
Query OK, 1 row affected (0.00 sec)
# 主键不能重复
mysql> insert into user values (1, "zhangsan");
ERROR 1062 (23000): Duplicate entry '1' for key 'user.PRIMARY'
# 主键不能为空
mysql> insert into user values (NULL, "zhangsan");
ERROR 1048 (23000): Column 'id' cannot be null
# 联合主键
mysql> create table user2 (id int, name VARCHAR(20), password VARCHAR(20), primary key (name, ID)) default charset=utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> select * from user2;
+----+----------+----------+
| id | name | password |
+----+----------+----------+
| 1 | zhangsan | 1234 |
| 2 | zhangsan | 1234 |
+----+----------+----------+
2 rows in set (0.00 sec)-
自增约束，简单来说就是自动增加主键，自动生成主键，与主键约束配合使用
mysql> create table user3 (id int primary key auto_increment, name VARCHAR(20)) default charset=utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> insert into user3 (name) values ("zhangsan");
Query OK, 1 row affected (0.01 sec)
mysql> insert into user3 (name) values ("zhangsan");
Query OK, 1 row affected (0.00 sec)
mysql> select * from user3;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | zhangsan |
+----+----------+
2 rows in set (0.01 sec)
如何在表确定之后设定主键
mysql> describe Credit;
+-----------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------------+--------------+------+-----+---------+-------+
| ID | varchar(255) | YES | | NULL | |
| Label | varchar(255) | YES | | NULL | |
| AGE | varchar(255) | YES | | NULL | |
| GENDER | varchar(255) | YES | | NULL | |
| MARITAL_STATUS | varchar(255) | YES | | NULL | |
| MONTHLY_INCOME_WHITHOUT_TAX | varchar(255) | YES | | NULL | |
| LOANTYPE | varchar(255) | YES | | NULL | |
| GAGE_TOTLE_PRICE | varchar(255) | YES | | NULL | |
| APPLY_AMOUNT | varchar(255) | YES | | NULL | |
| APPLY_TERM_TIME | varchar(255) | YES | | NULL | |
| APPLY_INTEREST_RATE | varchar(255) | YES | | NULL | |
| PAYMENT_TYPE | varchar(255) | YES | | NULL | |
+-----------------------------+--------------+------+-----+---------+-------+
12 rows in set (0.00 sec)
# 将ID设置为主键，并且加上自增约束
mysql> alter table Credit add primary key auto_increment(ID);
Query OK, 0 rows affected (0.04 sec)Records: 0 Duplicates: 0 Warnings: 0
mysql> desc credit;
+-----------------------------+--------------+------+-----+---------+-------+
|Field | Type | Null | Key | Default | Extra |
+-----------------------------+--------------+------+-----+---------+-------+
| ID | varchar(255) | NO | PRI | NULL | |
| Label | varchar(255) | YES | | NULL | |
| AGE | varchar(255) | YES | | NULL | |
| GENDER | varchar(255) | YES | | NULL | |
| MARITAL_STATUS | varchar(255) | YES | | NULL | |
| MONTHLY_INCOME_WHITHOUT_TAX | varchar(255) | YES | | NULL | |
| LOANTYPE | varchar(255) | YES | | NULL | |
| GAGE_TOTLE_PRICE | varchar(255) | YES | | NULL | |
| APPLY_AMOUNT | varchar(255) | YES | | NULL | |
| APPLY_TERM_TIME | varchar(255) | YES | | NULL | |
| APPLY_INTEREST_RATE | varchar(255) | YES | | NULL | |
| PAYMENT_TYPE | varchar(255) | YES | | NULL | |
+-----------------------------+--------------+------+-----+---------+-------+
12 rows in set (0.01 sec)
# 删除主键
mysql> alter table Credit drop primary key;
Query OK, 1412 rows affected (0.04 sec)Records: 1412 Duplicates: 0 Warnings: 0
mysql> desc credit;
+-----------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------------+--------------+------+-----+---------+-------+
|ID | varchar(255) | NO | | NULL | |
| Label | varchar(255) | YES | | NULL | |
| AGE | varchar(255) | YES | | NULL | |
| GENDER | varchar(255) | YES | | NULL | |
| MARITAL_STATUS | varchar(255) | YES | | NULL | |
| MONTHLY_INCOME_WHITHOUT_TAX | varchar(255) | YES | | NULL | |
| LOANTYPE | varchar(255) | YES | | NULL | |
| GAGE_TOTLE_PRICE | varchar(255) | YES | | NULL | |
| APPLY_AMOUNT | varchar(255) | YES | | NULL | |
| APPLY_TERM_TIME | varchar(255) | YES | | NULL | |
| APPLY_INTEREST_RATE | varchar(255) | YES | | NULL | |
| PAYMENT_TYPE | varchar(255) | YES | | NULL | |
+-----------------------------+--------------+------+-----+---------+-------+
12 rows in set (0.00 sec)
# 第二种添加方式
mysql> alter table Credit modify ID INT primary key auto_increment;
Query OK, 1412 rows affected (0.04 sec)Records: 1412 Duplicates: 0 Warnings: 0
mysql> desc credit;
+-----------------------------+--------------+------+-----+---------+----------------+
|Field | Type | Null | Key | Default | Extra |
+-----------------------------+--------------+------+-----+---------+----------------+
|ID | int | NO | PRI | NULL | auto_increment |
| Label | varchar(255) | YES | | NULL | |
| AGE | varchar(255) | YES | | NULL | |
| GENDER | varchar(255) | YES | | NULL | |
| MARITAL_STATUS | varchar(255) | YES | | NULL | |
| MONTHLY_INCOME_WHITHOUT_TAX | varchar(255) | YES | | NULL | |
| LOANTYPE | varchar(255) | YES | | NULL | |
| GAGE_TOTLE_PRICE | varchar(255) | YES | | NULL | |
| APPLY_AMOUNT | varchar(255) | YES | | NULL | |
| APPLY_TERM_TIME | varchar(255) | YES | | NULL | |
| APPLY_INTEREST_RATE | varchar(255) | YES | | NULL | |
| PAYMENT_TYPE | varchar(255) | YES | | NULL | |
+-----------------------------+--------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)
唯一约束
# 约束该字段的值不能重复
mysql> alter table user add unique(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into user values (1, "name");
Query OK, 1 row affected (0.00 sec)
mysql> insert into user values (1, "name");
ERROR 1062 (23000): Duplicate entry 'name' for key 'user.name'
mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID | int | YES | | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
# 如果设定unique()多个，则表示多个组合在一起不重复即可
mysql> alter table user2 add unique(ID, name);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID | int | YES | MUL | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
# 如何删除约束 alter table 表名 drop index 数据列名;
mysql> alter table user2 drop index ID;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
非空约束
# 修饰的字段不能为空
mysql> create table user3 (ID INT not null, name VARCHAR(20)) default charset=utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> desc user3;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID | int | NO | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into user3 values (NULL, "abde");
ERROR 1048 (23000): Column 'ID' cannot be null# 同样的删除
mysql> alter table user3 modify ID INT NULL;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user3;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
默认约束
# 当插入字段值的时候，如果没有传值，就会使用默认值
mysql> create table user3 (ID INT not null, name VARCHAR(20) default "Unknow") default charset=utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> desc user3;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID | int | NO | | NULL | |
| name | varchar(20) | YES | | Unknow | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from user3;
+----+--------+
| ID | name |
+----+--------+
| 2 | Unknow |
+----+--------+
1 row in set (0.01 sec)
外键约束
# 牵涉到两张表，一个主表、一个副表
mysql> create table classes (id int primary key, name varchar(20)) default charset=utf8;
mysql> desc classes;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> create table students (id int primary key, name varchar(20), class_id int, foreign key (class_id) references classes(id)) default charset=utf8;
mysql> desc students;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| class_id | int | YES | MUL | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
# 插入数据
mysql> insert into classes values (1, "yiban");
Query OK, 1 row affected (0.00 sec)
mysql> insert into classes values (2, "erban");
Query OK, 1 row affected (0.01 sec)
mysql> insert into classes values (3, "sanban");
Query OK, 1 row affected (0.00 sec)
mysql> insert into classes values (4, "siban");
Query OK, 1 row affected (0.01 sec)
mysql> select * from classes;
+----+--------+
| id | name |
+----+--------+
| 1 | yiban |
| 2 | erban |
| 3 | sanban |
| 4 | siban |
+----+--------+
4 rows in set (0.00 sec)
# 不允许插入外键里没有的值，这里班级表为主表，学生表为副表；
# 主表里没有的数据，在副表中的数据不可以使用；
mysql> insert into students values (1001, "zhangsan", 1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into students values (1002, "zhangsan", 2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into students values (1003, "zhangsan", 3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into students values (1004, "zhangsan", 4);
Query OK, 1 row affected (0.00 sec)
mysql> insert into students values (1005, "lisi", 5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`learn`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))
# 主表中的记录被副表引用，是不可以被删除的；
mysql> delete from classes where ID=4;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`learn`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))
第一范式
# 数据中所有字段都是不可分割的原子值？
# 比如地址，“中国江苏省南京市鼓楼区湖南路街道汉口路22号”，可以拆分为Country、Province、City
第二范式
# 必须满足第一范式# 要求每一列除主键外，都必须依赖于主键，可以有联合主键# 如学生表，存在学号、家庭住址、姓名、身份证号等等，后面三者不依赖于学号，因此需要进行拆表
第三范式
# 必须先满足第二范式# 要求除开主键外的其他列，不允许出现传递依赖# 如主键是订单ID，顾客的号码依赖于顾客ID，顾客ID依赖于订单ID
MySQL查询练习
# 查看数据表结构及数据
mysql> select * from student;
+----+--------------+------+------------+---------+
| ID | name | sex | birth | classes |
+----+--------------+------+------------+---------+
| 1 | Li Min | M | 2020-01-23 | 21334 |
| 2 | Han Leilei | F | 1998-03-28 | 21335 |
| 3 | Wang Xiaolei | M | 2002-01-18 | 21334 |
| 4 | Qiu Xiaozhi | M | 2003-02-24 | 21336 |
| 5 | Liu Niuniu | F | 2012-12-24 | 21334 |
+----+--------------+------+------------+---------+
5 rows in set (0.00 sec)
mysql> select * from teachers;
+----+--------+------+------------+-----------+-----------------+
| ID | name | sex | birth | prof | depart |
+----+--------+------+------------+-----------+-----------------+
| 1 | 李城 | F | 1958-12-02 | 副教授 | 计算机系 |
| 2 | 张旭 | F | 1969-03-12 | 讲师 | 电子工程系 |
| 3 | 王斌 | F | 1977-05-27 | 助教 | 计算机系 |
+----+--------+------+------------+-----------+-----------------+
3 rows in set (0.00 sec)
mysql> select * from courses;
+----+--------------------+------------+
| ID | course | teacher_id |
+----+--------------------+------------+
| 1 | C语言 | 1 |
| 2 | 高等数学 | 3 |
| 3 | 线性代数 | 2 |
| 4 | 美国自然文学 | 2 |
+----+--------------------+------------+
4 rows in set (0.00 sec)
mysql> select * from grades;
+------------+-----------+-------+
| student_id | course_id | score |
+------------+-----------+-------+
| 1 | 1 | 90 |
| 1 | 2 | 88 |
| 1 | 3 | 87 |
| 1 | 4 | 59 |
| 2 | 1 | 90 |
| 2 | 2 | 16 |
| 2 | 3 | 72 |
| 2 | 4 | 100 |
| 3 | 1 | 75 |
| 3 | 2 | 79 |
| 3 | 3 | 35 |
| 3 | 4 | 98 |
| 4 | 1 | 84 |
| 4 | 2 | 85 |
| 4 | 3 | 58 |
| 4 | 4 | 60 |
| 5 | 1 | 89 |
| 5 | 2 | 92 |
| 5 | 3 | 89 |
| 5 | 4 | 80 |
+------------+-----------+-------+
20 rows in set (0.00 sec)
# 查询单个表
mysql> select ID, name, sex from student;
+----+--------------+------+
| ID | name | sex |
+----+--------------+------+
| 1 | Li Min | M |
| 2 | Han Leilei | F |
| 3 | Wang Xiaolei | M |
| 4 | Qiu Xiaozhi | M |
| 5 | Liu Niuniu | F |
+----+--------------+------+
5 rows in set (0.00 sec)
# 查询某一列，不含重复值
mysql> select distinct depart from teachers;
+-----------------+
| depart |
+-----------------+
| 计算机系 |
| 电子工程系 |
+-----------------+
2 rows in set (0.00 sec)
mysql> select depart from teachers;
+-----------------+
| depart |
+-----------------+
| 计算机系 |
| 电子工程系 |
| 计算机系 |
+-----------------+
3 rows in set (0.00 sec)
# 查询某个区间的值，注意区间值(between and)
mysql> select * from grades where score between 50 and 80;
+------------+-----------+-------+
| student_id | course_id | score |
+------------+-----------+-------+
| 1 | 4 | 59 |
| 2 | 3 | 72 |
| 3 | 1 | 75 |
| 3 | 2 | 79 |
| 4 | 3 | 58 |
| 4 | 4 | 60 |
| 5 | 4 | 80 |
+------------+-----------+-------+
7 rows in set (0.00 sec)
mysql> select * from grades where score>50 and score<80;
+------------+-----------+-------+
| student_id | course_id | score |
+------------+-----------+-------+
| 1 | 4 | 59 |
| 2 | 3 | 72 |
| 3 | 1 | 75 |
| 3 | 2 | 79 |
| 4 | 3 | 58 |
| 4 | 4 | 60 |
+------------+-----------+-------+
6 rows in set (0.00 sec)
# 查询某几个记录，或关系(in)
mysql> select * from grades where score in (72, 80, 75);
+------------+-----------+-------+
| student_id | course_id | score |
+------------+-----------+-------+
| 2 | 3 | 72 |
| 3 | 1 | 75 |
| 5 | 4 | 80 |
+------------+-----------+-------+
3 rows in set (0.00 sec)
# 查询几个列中或的关系
mysql> select * from student where classes=21334 or sex='F';
+----+--------------+------+------------+---------+
| ID | name | sex | birth | classes |
+----+--------------+------+------------+---------+
| 1 | Li Min | M | 2020-01-23 | 21334 |
| 2 | Han Leilei | F | 1998-03-28 | 21335 |
| 3 | Wang Xiaolei | M | 2002-01-18 | 21334 |
| 5 | Liu Niuniu | F | 2012-12-24 | 21334 |
+----+--------------+------+------------+---------+
4 rows in set (0.00 sec)
# 降序查询 - order by 字段 desc;
mysql> select * from student order by classes desc;
+----+--------------+------+------------+---------+
| ID | name | sex | birth | classes |
+----+--------------+------+------------+---------+
| 4 | Qiu Xiaozhi | M | 2003-02-24 | 21336 |
| 2 | Han Leilei | F | 1998-03-28 | 21335 |
| 1 | Li Min | M | 2020-01-23 | 21334 |
| 3 | Wang Xiaolei | M | 2002-01-18 | 21334 |
| 5 | Liu Niuniu | F | 2012-12-24 | 21334 |
+----+--------------+------+------------+---------+
5 rows in set (0.00 sec)
# 升序查询 - 默认升序
mysql> select * from student order by classes;
+----+--------------+------+------------+---------+
| ID | name | sex | birth | classes |
+----+--------------+------+------------+---------+
| 1 | Li Min | M | 2020-01-23 | 21334 |
| 3 | Wang Xiaolei | M | 2002-01-18 | 21334 |
| 5 | Liu Niuniu | F | 2012-12-24 | 21334 |
| 2 | Han Leilei | F | 1998-03-28 | 21335 |
| 4 | Qiu Xiaozhi | M | 2003-02-24 | 21336 |
+----+--------------+------+------------+---------+
5 rows in set (0.00 sec)
mysql> select * from student order by classes asc;
+----+--------------+------+------------+---------+
| ID | name | sex | birth | classes |
+----+--------------+------+------------+---------+
| 1 | Li Min | M | 2020-01-23 | 21334 |
| 3 | Wang Xiaolei | M | 2002-01-18 | 21334 |
| 5 | Liu Niuniu | F | 2012-12-24 | 21334 |
| 2 | Han Leilei | F | 1998-03-28 | 21335 |
| 4 | Qiu Xiaozhi | M | 2003-02-24 | 21336 |
+----+--------------+------+------------+---------+
5 rows in set (0.00 sec)
# 同时含有升序与降序
mysql> select * from student order by classes asc, ID desc;
+----+--------------+------+------------+---------+
| ID | name | sex | birth | classes |
+----+--------------+------+------------+---------+
| 5 | Liu Niuniu | F | 2012-12-24 | 21334 |
| 3 | Wang Xiaolei | M | 2002-01-18 | 21334 |
| 1 | Li Min | M | 2020-01-23 | 21334 |
| 2 | Han Leilei | F | 1998-03-28 | 21335 |
| 4 | Qiu Xiaozhi | M | 2003-02-24 | 21336 |
+----+--------------+------+------------+---------+
5 rows in set (0.00 sec)
# 统计某个班级的学生人数
mysql> select count(*) from student where classes='21334';
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)
# 找到成绩最高的学生学号与课程编号
mysql> select student_id, course_id from grades where score=(select max(score) from grades);
+------------+-----------+
| student_id | course_id |
+------------+-----------+
| 2 | 4 |
+------------+-----------+
1 row in set (0.00 sec)
mysql> select student_id, course_id from grades order by score desc limit 0,1;
+------------+-----------+
| student_id | course_id |
+------------+-----------+
| 2 | 4 |
+------------+-----------+
1 row in set (0.00 sec)
查询练习
# 查询每门课的平均成绩 - group by 分组
mysql> select avg(score) from grades where course_id=3;
+------------+
| avg(score) |
+------------+
| 68.2000 |
+------------+
1 row in set (0.00 sec)
mysql> select course_id, avg(score) from grades group by course_id;
+-----------+------------+
| course_id | avg(score) |
+-----------+------------+
| 1 | 85.6000 |
| 2 | 72.0000 |
| 3 | 68.2000 |
| 4 | 79.4000 |
+-----------+------------+
4 rows in set (0.00 sec)
# 查询score表中至少有2名学修的，且课程编号大于2的，或者包含2的
mysql> select course_id from grades group by course_id having count(course_id)>=2 and course_id like '2%';
+-----------+
| course_id |
+-----------+
| 2 |
+-----------+
1 row in set (0.00 sec)
mysql> select course_id from grades group by course_id having count(course_id)>=2;
+-----------+
| course_id |
+-----------+
| 1 |
| 2 |
| 3 |
| 4 |
+-----------+
4 rows in set (0.00 sec)
多表查询
# 查询所有学生的 姓名、课程、分数
mysql> select name from student;
+--------------+
| name |
+--------------+
| Li Min |
| Han Leilei |
| Wang Xiaolei |
| Qiu Xiaozhi |
| Liu Niuniu |
+--------------+
5 rows in set (0.00 sec)
mysql> select course_id, score from grades;
+-----------+-------+
| course_id | score |
+-----------+-------+
| 1 | 90 |
| 2 | 88 |
| 3 | 87 |
| 4 | 59 |
| 1 | 90 |
| 2 | 16 |
| 3 | 72 |
| 4 | 100 |
| 1 | 75 |
| 2 | 79 |
| 3 | 35 |
| 4 | 98 |
| 1 | 84 |
| 2 | 85 |
| 3 | 58 |
| 4 | 60 |
| 1 | 89 |
| 2 | 92 |
| 3 | 89 |
| 4 | 80 |
+-----------+-------+
20 rows in set (0.00 sec)
# 查询手段mysql> select name, course_id, score from student, grades where student.ID=grades.student_id;
+--------------+-----------+-------+
| name | course_id | score |
+--------------+-----------+-------+
| Li Min | 1 | 90 |
| Li Min | 2 | 88 |
| Li Min | 3 | 87 |
| Li Min | 4 | 59 |
| Han Leilei | 1 | 90 |
| Han Leilei | 2 | 16 |
| Han Leilei | 3 | 72 |
| Han Leilei | 4 | 100 |
| Wang Xiaolei | 1 | 75 |
| Wang Xiaolei | 2 | 79 |
| Wang Xiaolei | 3 | 35 |
| Wang Xiaolei | 4 | 98 |
| Qiu Xiaozhi | 1 | 84 |
| Qiu Xiaozhi | 2 | 85 |
| Qiu Xiaozhi | 3 | 58 |
| Qiu Xiaozhi | 4 | 60 |
| Liu Niuniu | 1 | 89 |
| Liu Niuniu | 2 | 92 |
| Liu Niuniu | 3 | 89 |
| Liu Niuniu | 4 | 80 |
+--------------+-----------+-------+
20 rows in set (0.00 sec)
三表关联查询
# 查询所有学生姓名、选修课程、成绩
mysql> select name, course, score from student, courses, grades where student.ID=grades.student_id and grades.course_ID=courses.ID;
+--------------+--------------------+-------+
| name | course | score |
+--------------+--------------------+-------+
| Liu Niuniu | C语言 | 89 |
| Qiu Xiaozhi | C语言 | 84 |
| Wang Xiaolei | C语言 | 75 |
| Han Leilei | C语言 | 90 |
| Li Min | C语言 | 90 |
| Liu Niuniu | 高等数学 | 92 |
| Qiu Xiaozhi | 高等数学 | 85 |
| Wang Xiaolei | 高等数学 | 79 |
| Han Leilei | 高等数学 | 16 |
| Li Min | 高等数学 | 88 |
| Liu Niuniu | 线性代数 | 89 |
| Qiu Xiaozhi | 线性代数 | 58 |
| Wang Xiaolei | 线性代数 | 35 |
| Han Leilei | 线性代数 | 72 |
| Li Min | 线性代数 | 87 |
| Liu Niuniu | 美国自然文学 | 80 |
| Qiu Xiaozhi | 美国自然文学 | 60 |
| Wang Xiaolei | 美国自然文学 | 98 |
| Han Leilei | 美国自然文学 | 100 |
| Li Min | 美国自然文学 | 59 |
+--------------+--------------------+-------+
20 rows in set (0.00 sec)
# 核心要点就是找到关联的字段
mysql> select name, course, score, student.sex, courses.ID from student, courses, grades where student.ID=grades.student_idd anand grades.course_ID=courses.ID;
+--------------+--------------------+-------+------+----+
| name | course | score | sex | ID |
+--------------+--------------------+-------+------+----+
| Liu Niuniu | C语言 | 89 | F | 1 |
| Qiu Xiaozhi | C语言 | 84 | M | 1 |
| Wang Xiaolei | C语言 | 75 | M | 1 |
| Han Leilei | C语言 | 90 | F | 1 |
| Li Min | C语言 | 90 | M | 1 |
| Liu Niuniu | 高等数学 | 92 | F | 2 |
| Qiu Xiaozhi | 高等数学 | 85 | M | 2 |
| Wang Xiaolei | 高等数学 | 79 | M | 2 |
| Han Leilei | 高等数学 | 16 | F | 2 |
| Li Min | 高等数学 | 88 | M | 2 |
| Liu Niuniu | 线性代数 | 89 | F | 3 |
| Qiu Xiaozhi | 线性代数 | 58 | M | 3 |
| Wang Xiaolei | 线性代数 | 35 | M | 3 |
| Han Leilei | 线性代数 | 72 | F | 3 |
| Li Min | 线性代数 | 87 | M | 3 |
| Liu Niuniu | 美国自然文学 | 80 | F | 4 |
| Qiu Xiaozhi | 美国自然文学 | 60 | M | 4 |
| Wang Xiaolei | 美国自然文学 | 98 | M | 4 |
| Han Leilei | 美国自然文学 | 100 | F | 4 |
| Li Min | 美国自然文学 | 59 | M | 4 |
+--------------+--------------------+-------+------+----+
20 rows in set (0.00 sec)
自查询加分组求平均分
# 查询某个班学生每门课的平均分
mysql> select avg(score) from grades where student_id in (select ID from student where classes=21334) group by course_id;
+------------+
| avg(score) |
+------------+
| 84.6667 |
| 86.3333 |
| 70.3333 |
| 79.0000 |
+------------+
4 rows in set (0.00 sec)
# 需要注意顺序，这里的group by要放在最后面
# 查询选修“3”课程的成绩高于“2”号同学“3”课程成绩的所有记录
mysql> select score from grades where course_id=3 and score>(select score from grades where student_id=2 and course_id=3);
+-------+
| score |
+-------+
| 87 |
| 89 |
+-------+
2 rows in set (0.00 sec)
year函数与带in函数的查询
# 查询学号为3/5号的学生出生年份相同的同学信息
mysql> select * from student where year(birth) in (select year(birth) from student where ID in (3,5));
+----+--------------+------+------------+---------+
| ID | name | sex | birth | classes |
+----+--------------+------+------------+---------+
| 3 | Wang Xiaolei | M | 2002-01-18 | 21334 |
| 5 | Liu Niuniu | F | 2012-12-24 | 21334 |
+----+--------------+------+------------+---------+
2 rows in set (0.00 sec)
多层嵌套自查询
# 查询张旭授课的学生成绩
mysql> select score from grades where course_id in (select ID from courses where teacher_id=(selectID from teachers where name="张旭"));
+-------+
| score |
+-------+
| 87 |
| 72 |
| 35 |
| 58 |
| 89 |
| 59 |
| 100 |
| 98 |
| 60 |
| 80 |
+-------+
10 rows in set (0.00 sec)
# 套娃，首先查询学生成绩，需要筛选课程ID；筛选课程ID，需要得到教师ID；得到教师ID，需要获取教师名字；
多表查询
# 查询选修某课程的人数多于3人的教师姓名# 套娃，查询姓名需要得到教师编号；得到教师编号，需要大于人学生编号；
# 得到选修课程人数大于3人的课程ID
mysql> select course_id from grades group by course_id having count(course_id)>3;
+-----------+
| course_id |
+-----------+
| 1 |
| 2 |
| 3 |
| 4 |
+-----------+
4 rows in set (0.00 sec)
# 由course_id获得teacher_id
mysql> select teacher_id from courses where ID in (select course_id from grades group by course_id having count(course_id)>3);
+------------+
| teacher_id |
+------------+
| 1 |
| 2 |
| 2 |
| 3 |
+------------+
4 rows in set (0.00 sec)
# 由得到的教师ID获取教师姓名
mysql> select name from teachers where ID in (select teacher_id from courses where ID in (select course_id from grades group by course_id having count(course_id)>3));
+--------+
| name |
+--------+
| 李城 |
| 张旭 |
| 王斌 |
+--------+
3 rows in set (0.01 sec)
Union 和 not in的使用
# union 可以链接两个查询语句# any() 表示任意，比如可以表示任意相等
mysql> select * from grades where score<any(select score from grades);
+------------+-----------+-------+
| student_id | course_id | score |
+------------+-----------+-------+
| 1 | 1 | 90 |
| 1 | 2 | 88 |
| 1 | 3 | 87 |
| 1 | 4 | 59 |
| 2 | 1 | 90 |
| 2 | 2 | 16 |
| 2 | 3 | 72 |
| 3 | 1 | 75 |
| 3 | 2 | 79 |
| 3 | 3 | 35 |
| 3 | 4 | 98 |
| 4 | 1 | 84 |
| 4 | 2 | 85 |
| 4 | 3 | 58 |
| 4 | 4 | 60 |
| 5 | 1 | 89 |
| 5 | 2 | 92 |
| 5 | 3 | 89 |
| 5 | 4 | 80 |
+------------+-----------+-------+
19 rows in set (0.00 sec)
# all() 表示所有# as取别名，但其实没什么用# union求并集
复制表数据做条件查询
# 查询低于平均成绩的记录，复制只需要加一个名称
mysql> select * from grades a where score < (select avg(score) from grades b where a.course_id=b.co rse_id);
+------------+-----------+-------+
| student_id | course_id | score |
+------------+-----------+-------+
| 1 | 4 | 59 |
| 2 | 2 | 16 |
| 3 | 1 | 75 |
| 3 | 3 | 35 |
| 4 | 1 | 84 |
| 4 | 3 | 58 |
| 4 | 4 | 60 |
+------------+-----------+-------+
7 rows in set (0.01 sec)
查询至少有两名男生的班号
mysql> select classes from student where sex="M" group by classes having count(*)>1;
+---------+
| classes |
+---------+
| 21334 |
+---------+
1 row in set (0.00 sec)
查询每个学生的姓名与年龄
# 重点在于如何获取当前年份 - now()函数
mysql> select name, year(now())-year(birth) as age from student;
+--------------+------+
| name | age |
+--------------+------+
| Li Min | 1 |
| Han Leilei | 23 |
| Wang Xiaolei | 19 |
| Qiu Xiaozhi | 18 |
| Liu Niuniu | 9 |
+--------------+------+
5 rows in set (0.00 sec)
SQL的四种连接查询
mysql> select * from person;
+------+--------+--------+
| id | name | cardID |
+------+--------+--------+
| 1 | 张三 | 1 |
| 2 | 莉丝 | 3 |
| 3 | 王武 | 1 |
+------+--------+--------+
3 rows in set (0.00 sec)
mysql> select * from card;
+------+-----------+
| id | name |
+------+-----------+
| 1 | 饭卡 |
| 2 | 健康卡 |
| 3 | 社保卡 |
| 4 | 建钢卡 |
| 5 | 邮政卡 |
+------+-----------+
5 rows in set (0.00 sec)
# 内连接inner join / join 下面三种查询方式是等价的
mysql> select * from person join card on person.cardID=card.id;
+------+--------+--------+------+-----------+
| id | name | cardID | id | name |
+------+--------+--------+------+-----------+
| 3 | 王武 | 1 | 1 | 饭卡 |
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 莉丝 | 3 | 3 | 社保卡 |
+------+--------+--------+------+-----------
+3 rows in set (0.00 sec)
mysql> select * from person inner join card on person.cardID=card.id;
+------+--------+--------+------+-----------+
| id | name | cardID | id | name |
+------+--------+--------+------+-----------+
| 3 | 王武 | 1 | 1 | 饭卡 |
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 莉丝 | 3 | 3 | 社保卡 |
+------+--------+--------+------+-----------+
3 rows in set (0.00 sec)
mysql> select * from person,card where person.cardID=card.id;
+------+--------+--------+------+-----------+
| id | name | cardID | id | name |
+------+--------+--------+------+-----------+
| 3 | 王武 | 1 | 1 | 饭卡 |
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 莉丝 | 3 | 3 | 社保卡 |
+------+--------+--------+------+-----------+
3 rows in set (0.00 sec)
# 外连接 - 左链接，左边的表全部取出来left join / left outer join
mysql> select * from person left join card on person.cardID=card.id;
+------+--------+--------+------+-----------+
| id | name | cardID | id | name |
+------+--------+--------+------+-----------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 莉丝 | 3 | 3 | 社保卡 |
| 3 | 王武 | 1 | 1 | 饭卡 |
| 1 | 张三 | 7 | NULL | NULL |
+------+--------+--------+------+-----------+
4 rows in set (0.00 sec)
# 外连接 - 右连接right join / right outer join
mysql> select * from person right join card on person.cardID=card.id;
+------+--------+--------+------+-----------+
| id | name | cardID | id | name |
+------+--------+--------+------+-----------+
| 3 | 王武 | 1 | 1 | 饭卡 |
| 1 | 张三 | 1 | 1 | 饭卡 |
| NULL | NULL | NULL | 2 | 健康卡 |
| 2 | 莉丝 | 3 | 3 | 社保卡 |
| NULL | NULL | NULL | 4 | 建钢卡 |
| NULL | NULL | NULL | 5 | 邮政卡 |
+------+--------+--------+------+-----------+
6 rows in set (0.00 sec)
# 完全外连接 没有 full join / full outer join
mysql> select * from person left join card on person.cardID=card.id union select * from person right join card on person.cardID=card.id;
+------+--------+--------+------+-----------+
| id | name | cardID | id | name |
+------+--------+--------+------+-----------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 莉丝 | 3 | 3 | 社保卡 |
| 3 | 王武 | 1 | 1 | 饭卡 |
| 1 | 张三 | 7 | NULL | NULL |
| NULL | NULL | NULL | 2 | 健康卡 |
| NULL | NULL | NULL | 4 | 建钢卡 |
| NULL | NULL | NULL | 5 | 邮政卡 |
+------+--------+--------+------+-----------+
7 rows in set (0.00 sec)
MySQL 事务
# 事务是一个最小的不可分割的工作单元，事务可以保证一个业务的完整性
# 比如银行转账，a转账100元给b
update user set money=money-100 where name=a;
update user set money=money+100 where name=b;
# 假如其中一条语句执行成功了，而另一条没有执行，出现数据前后不一致
# 所以多条sql语句，需要同时成功或者同时失败
# 查看MySQL的事务状态
select @@autocommit;
# 关闭MySQL默认事务提交；
set autocommit=0;
# 开启手动事务提交
begin; / start transaction;
# 事务回滚
rollback;
# 事务提交
commit;
ACID特征与使用
A 原子性：事务是最小的单位，不可再分割
C 一致性：事务要求，同一事务中的sql语句，必须保证同时成功或者同时失败
I 隔离性：事务1 与事务2 之间是具有隔离性的。
D 持久性：事务一旦结束（commit），就不可以返回。
手动事务开启：
1. 修改默认提交 set autocommit=0;
2. begin;
3. start transaction;
事务手动提交： commit;
事务手动回滚： rollback;
事务的隔离性（都是在两端同时登陆数据库进行操作的情况下）：
1. read uncommitted; 读取未提交；
如果有事务A与事务B，事务A对数据进行操作，在操作的过程中，事务没有被提交，但是B可以看见A操作的这一步；如果这个时候事务B计算或者使用了事务A的数据，那么在事务A撤回之后，事务B会出现问题，也就是脏读；
2. read committed; 读取已提交；
如果事务B读取到了事务A提交结束的数据，前后执行事务B出现的结果不同，这种情况被称为不可重复读；
3. repeatable read; 可以重复读；
如果事务B读取到的数据在事务A提交前后不变，但实际上事务A提交了数据，事务B看不见，数据出现了错误的现象，称为幻读；
4. serializable; 串行化；
如果事务A执行一个操作且未提交的时候，其他事务的写操作就必须等待事务A执行完毕，才能执行，称为串行化；也就是会进入排队状态（串行化），前提是没有等待超时；
事务的隔离级别越高，性能越差：
READ-UNCOMMITTED > READ-COMMITED > REPEATABLE-READ > SERIALIZABLE
mysql 默认隔离级别是 REPEATABLE-READ
如何查看数据库的隔离级别？mysql> select @@global.transaction_isolation;+--------------------------------+| @@global.transaction_isolation |+--------------------------------+| REPEATABLE-READ |+--------------------------------+1 row in set (0.00 sec)如何修改数据库的隔离级别？mysql> set global transaction isolation level read committed;Query OK, 0 rows affected (0.00 sec)mysql> select @@global.transaction_isolation;+--------------------------------+| @@global.transaction_isolation |+--------------------------------+| READ-COMMITTED |+--------------------------------+1 row in set (0.00 sec)
- 幻读：
MySQL中的case when语法
# 查询某个字段连续出现 30 次的值，难点在于连续
mysql> select distinct AGE from (select AGE, case when @prev = AGE then @count := @count + 1 when (@prev := AGE) is not null then @count :=1 end as CNT from Credit, (select @prev := null, @count := null) as t) as temp where temp.CNT >= 30;
+------+
| AGE |
+------+
| 26 |
| 27 |
| 30 |
| 32 |
| 33 |
+------+
5 rows in set, 5 warnings (0.01 sec)
# case 语法就像是其他语言中的switch，不同的是只执行一个case，并且不需要break。下面的@修饰用户变量，也就是自定义变量，':'表示的是赋值运算，此时下面的结构就很容易理解了。先构造一个目标列与目标列值所对应的连续出现次数，然后从这里筛选出现次数大于N的值。
select distinct AGE
from (
select AGE,
case when @prev = AGE then @count := @count + 1
when (@prev := AGE) is not null then @count :=1
end as CNT from Credit,
(select @prev := null, @count := null) as t
) as temp
where temp.CNT >= 30;
Delete用法补充
# 假如要删除表中重复的字段值，并且保留一行，该如何执行？
# 多表竟然可以做到，不可置信
mysql> select * from student;
+----+--------------+------+------------+---------+
| ID | name | sex | birth | classes |
+----+--------------+------+------------+---------+
| 1 | Li Min | M | 2020-01-23 | 21334 |
| 2 | Han Leilei | F | 1998-03-28 | 21335 |
| 3 | Wang Xiaolei | M | 2002-01-18 | 21334 |
| 4 | Qiu Xiaozhi | M | 2003-02-24 | 21336 |
| 5 | Liu Niuniu | F | 2012-12-24 | 21334 |
| 6 | Li Min | M | 2020-01-23 | 21334 |
+----+--------------+------+------------+---------+
6 rows in set (0.00 sec)
mysql> delete s1 from student s1, student s2 where s1.birth=s2.birth and s1.ID>s2.ID;
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+--------------+------+------------+---------+
| ID | name | sex | birth | classes |
+----+--------------+------+------------+---------+
| 1 | Li Min | M | 2020-01-23 | 21334 |
| 2 | Han Leilei | F | 1998-03-28 | 21335 |
| 3 | Wang Xiaolei | M | 2002-01-18 | 21334 |
| 4 | Qiu Xiaozhi | M | 2003-02-24 | 21336 |
| 5 | Liu Niuniu | F | 2012-12-24 | 21334 |
+----+--------------+------+------------+---------+
5 rows in set (0.00 sec)