MySQL表的约束和基本查询

一.表的约束

1.1空属性

当我们填写问卷的时候,经常会有不允许为空的问题,比如电话号,姓名等等.而mysql上我们可以在创建表的时候,如果想要某一列不允许为空,可以加上not null来加以限制:

mysql> create table myclass(
-> class_name varchar(20) not null,
-> class_room varchar(10) not null);
Query OK, 0 rows affected (0.02 sec)mysql> desc myclass;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| class_name | varchar(20) | NO | | NULL | |
| class_room | varchar(10) | NO | | NULL | |
+------------+-------------+------+-----+---------+-------+mysql> insert into myclass(class_name) values('class1');
ERROR 1364 (HY000): Field 'class_room' doesn't have a default value

1.2默认值

一般我们自己不手动设置默认值时,default默认为NULL,也就是什么都没有.那么我们如果设置的话,在插入数据时如果我们不特定值,那么数据库则会使用默认值为我们填充:

mysql> create table test_1(-> name varchar(20) not null,-> sex enum('男','女') default '男',-> phonenum varchar(20)-> );
Query OK, 0 rows affected (0.07 sec)mysql> insert into test_1 (name) values('张三');
mysql> select * from test_1;
+--------+------+----------+
| name   | sex  | phonenum |
+--------+------+----------+
| 张三   | 男   | NULL     |
+--------+------+----------+
1 row in set (0.00 sec)

1.3列描述

列描述类似于编程语言中的注释，用于说明表中每一列的具体含义和用途:

alter table test_1 change sex sex enum('男','女') default '男' comment '这一列描述的是人的性别';
mysql> desc test_1;
+----------+-------------------+------+-----+---------+-------+
| Field    | Type              | Null | Key | Default | Extra |
+----------+-------------------+------+-----+---------+-------+
| name     | varchar(20)       | NO   |     | NULL    |       |
| sex      | enum('男','女')   | YES  |     | 男      |       |
| phonenum | varchar(20)       | YES  |     | NULL    |       |
+----------+-------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)//desc无法查看注释
mysql> show create table test_1\G
*************************** 1. row ***************************Table: test_1
Create Table: CREATE TABLE `test_1` (`name` varchar(20) NOT NULL,`sex` enum('男','女') DEFAULT '男' COMMENT '这一列描述的是人的性别',//此时我们便可以看到注释`phonenum` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

1.4zerofill

mysql> create table test_2(-> name varchar(20) not null,-> id int-> );
Query OK, 0 rows affected (0.02 sec)mysql> desc test_2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | NO   |     | NULL    |       |
| id    | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

当我们使用数字类型的数据类型时,我们会发现他们在desc时后面都会带上一个数字,此时我们可能会想这是int类型的数据最大长度,你看符号位占一个,剩余位置是数据位.实际上没有关系.我们说这个11在当前列没有zerofill属性时是没有意义的.而添加了zerofill属性后如下:

mysql> alter table test_2 modify id int(7) zerofill;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> insert into test_2 values('张三',1),('李四',12345678);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> select * from test_2;
+--------+----------+
| name   | id       |
+--------+----------+
| 张三   |  0000001 |
| 李四   | 12345678 |
+--------+----------+
2 rows in set (0.00 sec)

我们发现，不足7位的部分会用0补足，而超过7位的数字则不会再进行补零填充。其实zerofill这种属性就是一种格式化输出的手段.

1.5主键(primary key)与自增长(auto_increment)

主键：primary key用来唯一的约束该字段里面的数据，不能重复，不能为空，一张表中最多只能有一个主键；主键所在的列通常是整数类型。
这就可以借助我们介绍ip时的一些理解来理解这部分.假如我们当前的表是一个存放着所有用户信息的表,那么用户之间如何进行区分呢,虽然每个人都有可以标识自身唯一性的东西比如身份证.但是为了解耦,我们需要使用主键来确保不同行用户信息的唯一性.当然这只是一种场景,辅助理解,主键除此之外还可以为索引提供帮助.

索引：

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息

我们后面再详细介绍索引,下面我们看一个例子:

mysql> create table test_3(-> id int primary key,-> username varchar(20) not null);
Query OK, 0 rows affected (0.01 sec)mysql> insert into test_3 values(1,'张三');
Query OK, 1 row affected (0.00 sec)mysql> insert into test_3 values(1,'李四');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'//主键约束,不允许重复

如果表原来没有主键,我们也可以后续添加,但是如果添加时对应列数据有重复行,则会直接报错不允许我们添加主键.

在创建表的时候，在所有字段之后，使用primary key(主键字段列表)来创建主键，如果有多个字段作为主键，可以使用复合主键。

mysql> alter table test_3 drop primary key;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0mysql> alter table test_3 add primary key(id,username);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc test_3;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| username | varchar(20) | NO   | PRI | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)//此时不是两个主键,而是id和username共同充当主键mysql> insert into test_3 values(1,'张三');
ERROR 1062 (23000): Duplicate entry '1-张三' for key 'PRIMARY'
mysql> insert into test_3 values(1,'李四');//只有二者完全相同时才会触发主键冲突
Query OK, 1 row affected (0.00 sec)mysql> select * from test_3;
+----+----------+
| id | username |
+----+----------+
|  1 | 张三     |
|  1 | 李四     |
+----+----------+
2 rows in set (0.00 sec)

自增长是隶属与主键的一种约束,当对应的字段，不给值，会自动的被系统触发，系统会从当前字段中已经有的最大值+1操作，得到一个新的不同的值。通常和主键搭配使用，作为逻辑主键。

自增长的特点:

任何一个字段要做自增长，前提是本身是一个索引（key一栏有值）
自增长字段必须是整数
一张表最多只能有一个自增长

下面我们来看一个例子:

mysql> create table if not exists test_4(-> id int primary key auto_increment,-> name varchar(20) not null);
Query OK, 0 rows affected (0.02 sec)mysql> show create table test_4\G
*************************** 1. row ***************************Table: test_4
Create Table: CREATE TABLE `test_4` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) NOT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)//此时表中没有数据mysql> insert into test_4 (name) values('张三');
Query OK, 1 row affected (0.01 sec)mysql> select * from test_3;
ERROR 1146 (42S02): Table 'd2.test_3' doesn't exist
mysql> select * from test_4;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
+----+--------+
1 row in set (0.00 sec)#默认从1开始mysql> show create table test_4;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                              |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_4 | CREATE TABLE `test_4` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) NOT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |#此时表中最大值为1,所以下次插入为2
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)mysql> insert into test_4 values(1000,'李四');
Query OK, 1 row affected (0.00 sec)mysql> show create table test_4;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                 |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_4 | CREATE TABLE `test_4` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) NOT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)//此时表中最大值为1000,所以下次插入为1001

1.6唯一键

一张表中有往往有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键：唯一键就可以解决表中有多个字段需要唯一性约束的问题。

唯一键的本质和主键差不多，唯一键允许为空，而且可以多个为空，空字段不做唯一性比较。

关于唯一键和主键的区别：

我们可以简单理解成，主键更多的是标识唯一性的。而唯一键更多的是保证在业务上，不要和别的信息出现重复。乍一听好像没啥区别，我们举一个例子:

假设一个场景(当然，具体可能并不是这样，仅仅为了帮助大家理解)

比如在公司，我们需要一个员工管理系统，系统中有一个员工表，员工表中有两列信息，一个身份证号码，一个是员工工号，我们可以选择身份号码作为主键。

而我们设计员工工号的时候，需要一种约束：而所有的员工工号都不能重复。

具体指的是在公司的业务上不能重复，我们设计表的时候，需要这个约束，那么就可以将员工工号设计成为唯一键。

一般而言，我们建议将主键设计成为和当前业务无关的字段，这样，当业务调整的时候，我们可以尽量不会对主键做过大的调整,下面是一个案例：

mysql> create table student (
-> id char(10) unique comment '学号，不能重复，但可以为空',
-> name varchar(10)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into student(id, name) values('01', 'aaa');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student(id, name) values('01', 'bbb'); --唯一约束不能重复
ERROR 1062 (23000): Duplicate entry '01' for key 'id'
mysql> insert into student(id, name) values(null, 'bbb'); -- 但可以为空
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+------+------+
| id | name |
+------+------+
| 01 | aaa |
| NULL | bbb |
+------+------+

需要注意的是,如果原本的列已经有唯一键属性了,我们想要修改当前列的属性,必须要先把原来的唯一键删除,再使用modify或者change进行修改,否则就会出现下图的情况:

这种情况出现的原因是

MySQL的行为特性：当使用MODIFY COLUMN并包含UNIQUE约束时，MySQL会添加新的索引而不是检查是否已存在相同索引。
索引命名规则：当不显式指定索引名称时，MySQL会自动命名。如果telephone已存在，它会创建telephone_2、telephone_3等。

1.7外键

外键用于定义主表和从表之间的关系：外键约束主要定义在从表上，主表则必须是有主键约束或unique约束。当定义外键后，要求外键列数据必须在主表的主键列存在或为null。

语法：

foreign key (字段名) references 主表(列)

案例:

对上面的示意图进行设计：

create table myclass (
id int primary key,
name varchar(30) not null comment'班级名'
);//创建主键表create table stu (
id int primary key,
name varchar(30) not null comment '学生名',
class_id int,
foreign key (class_id) references myclass(id)
);//创建从表mysql> insert into myclass values(10, 'C++'),(20, 'java');
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into stu values(100, '张三', 10),(101, '李四',20);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql> insert into stu values(102, 'wangwu',30);
ERROR 1452 (23000): Cannot add or update a child row:
a foreign key constraint fails (mytest.stu, CONSTRAINT stu_ibfk_1
FOREIGN KEY (class_id) REFERENCES myclass (id))//插入一个班级号为30的学生，因为没有这个班级，所以插入不成功mysql> insert into stu values(102, 'wangwu', null);//插入班级id为null，比如来了一个学生，目前还没有分配班级

使用外键会略微影响性能，因为每次操作都需要检查约束条件。需要确保关联字段的数据类型完全一致。 InnoDB引擎才支持完整的外键功能。

二.mysql表的基本操作(增删查改)

CRUD : Create(创建), Retrieve(读取)，Update(更新)，Delete（删除）

2.1Create

语法:

INSERT [INTO] table_name
[(column [, column] ...)]
VALUES (value_list) [, (value_list)] ...
value_list: value, [, value] ...

我们以一张学生表来介绍插入时的几种情况:

-- 创建一张学生表
CREATE TABLE students (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
sn INT NOT NULL UNIQUE COMMENT '学号',
name VARCHAR(20) NOT NULL,
qq VARCHAR(20)
);

2.1.1单行数据+全列插入

-- 插入两条记录，value_list 数量必须和定义表的列的数量及顺序一致
-- 注意，这里在插入的时候，也可以不用指定id(当然，那时候就需要明确插入数据到那些列了)，那么mysql会使用默认
的值进行自增。
INSERT INTO students VALUES (100, 10000, '唐三藏', NULL);
Query OK, 1 row affected (0.02 sec)
)
-- 查看插入结果
SELECT * FROM students;
+-----+-------+-----------+-------+
| id | sn | name | qq |
+-----+-------+-----------+-------+
| 100 | 10000 | 唐三藏 | NULL     |
| 101 | 10001 | 孙悟空 | 11111    |
+-----+-------+-----------+-------+
2 rows in set (0.00 sec)

2.1.2多行数据+指定列插入

INSERT INTO students (id, sn, name) VALUES
(102, 20001, '曹孟德'),
(103, 20002, '孙仲谋');
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
-- 查看插入结果
SELECT * FROM students;
+-----+-------+-----------+-------+
| id | sn | name | qq |
+-----+-------+-----------+-------+
| 100 | 10000 | 唐三藏 | NULL |
| 101 | 10001 | 孙悟空 | 11111 |
| 102 | 20001 | 曹孟德 | NULL |
| 103 | 20002 | 孙仲谋 | NULL |
+-----+-------+-----------+-------+
4 rows in set (0.00 sec)

2.1.3插入否则更新

由于主键或者唯一键对应的值已经存在而导致插入失败

-- 主键冲突
INSERT INTO students (id, sn, name) VALUES (100, 10010, '唐大师');
ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY'
-- 唯一键冲突
INSERT INTO students (sn, name) VALUES (20001, '曹阿瞒');
ERROR 1062 (23000): Duplicate entry '20001' for key 'sn'

可以选择性的进行同步更新操作语法：

INSERT ... ON DUPLICATE KEY UPDATE
column = value [, column = value] ...
INSERT INTO students (id, sn, name) VALUES (100, 10010, '唐大师')
ON DUPLICATE KEY UPDATE sn = 10010, name = '唐大师';
Query OK, 2 rows affected (0.47 sec)
-- 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等
-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据，并且数据已经被更新
-- 通过 MySQL 函数获取受到影响的数据行数
SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
| 2 |
+-------------+1 row in set (0.00 sec)
-- ON DUPLICATE KEY 当发生重复key的时候

2.1.4替换

-- 主键 或者 唯一键 没有冲突，则直接插入；
-- 主键 或者 唯一键 如果冲突，则删除后再插入
REPLACE INTO students (sn, name) VALUES (20001, '曹阿瞒');
Query OK, 2 rows affected (0.00 sec)
-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据，删除后重新插入

2.2Retrieve

基本语法:

SELECT
[DISTINCT] {* | {column [, column] ...}
[FROM table_name]
[WHERE ...]
[ORDER BY column [ASC | DESC], ...]
LIMIT ...

我们以下面的一个例子展开叙述:

-- 创建表结构
CREATE TABLE exam_result (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL COMMENT '同学姓名',
chinese float DEFAULT 0.0 COMMENT '语文成绩',
math float DEFAULT 0.0 COMMENT '数学成绩',
english float DEFAULT 0.0 COMMENT '英语成绩'
);
-- 插入测试数据
INSERT INTO exam_result (name, chinese, math, english) VALUES
('唐三藏', 67, 98, 56),
('孙悟空', 87, 78, 77),
('猪悟能', 88, 98, 90),
('曹孟德', 82, 84, 67),
('刘玄德', 55, 85, 45),
('孙权', 70, 73, 78),
('宋公明', 75, 65, 30);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0

2.2.1select列

最简单的方式-全列查询:

mysql> select * from exam_result;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |      67 |   98 |      56 |
|  2 | 孙悟空    |      87 |   78 |      77 |
|  3 | 猪悟能    |      88 |   98 |      90 |
|  4 | 曹孟德    |      82 |   84 |      67 |
|  5 | 刘玄德    |      55 |   85 |      45 |
|  6 | 孙权      |      70 |   73 |      78 |
|  7 | 宋公明    |      75 |   65 |      30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)

指定列查询(比如此时我只想查询每个同学的姓名和语文成绩)

mysql> select name,chinese from exam_result;
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 唐三藏    |      67 |
| 孙悟空    |      87 |
| 猪悟能    |      88 |
| 曹孟德    |      82 |
| 刘玄德    |      55 |
| 孙权      |      70 |
| 宋公明    |      75 |
+-----------+---------+
7 rows in set (0.00 sec)

查询字段为表达式:

mysql> select name,chinese + 10,10 from exam_result;
+-----------+--------------+----+
| name      | chinese + 10 | 10 |
+-----------+--------------+----+
| 唐三藏    |           77 | 10 |
| 孙悟空    |           97 | 10 |
| 猪悟能    |           98 | 10 |
| 曹孟德    |           92 | 10 |
| 刘玄德    |           65 | 10 |
| 孙权      |           80 | 10 |
| 宋公明    |           85 | 10 |
+-----------+--------------+----+
7 rows in set (0.00 sec)

为查询结果指定别名(as可以省略) :

mysql> select name as 名字,chinese 语文成绩 from exam_result;
+-----------+--------------+
| 名字      | 语文成绩     |
+-----------+--------------+
| 唐三藏    |           67 |
| 孙悟空    |           87 |
| 猪悟能    |           88 |
| 曹孟德    |           82 |
| 刘玄德    |           55 |
| 孙权      |           70 |
| 宋公明    |           75 |
+-----------+--------------+
7 rows in set (0.00 sec)

对查询结果进行去重(distinct):

mysql> insert into exam_result (name,math) values ('唐三藏',98);
Query OK, 1 row affected (0.00 sec)mysql> select math,name from exam_result;
+------+-----------+
| math | name      |
+------+-----------+
|   98 | 唐三藏    |
|   78 | 孙悟空    |
|   98 | 猪悟能    |
|   84 | 曹孟德    |
|   85 | 刘玄德    |
|   73 | 孙权      |
|   65 | 宋公明    |
|   98 | 唐三藏    |
+------+-----------+
8 rows in set (0.00 sec)mysql> select distinct math from exam_result;
+------+
| math |
+------+
|   98 |
|   78 |
|   84 |
|   85 |
|   73 |
|   65 |
+------+
6 rows in set (0.00 sec)mysql> select distinct math,name from exam_result;
+------+-----------+
| math | name      |
+------+-----------+
|   98 | 唐三藏    |
|   78 | 孙悟空    |
|   98 | 猪悟能    |
|   84 | 曹孟德    |
|   85 | 刘玄德    |
|   73 | 孙权      |
|   65 | 宋公明    |
+------+-----------+
7 rows in set (0.00 sec)mysql> delete from exam_result where id=8;
Query OK, 1 row affected (0.00 sec)

有些类似于复合主键那样.

2.2.2where条件

我们查询的时候有的时候会有一些条件限制,比如成绩大于90分或不及格的同学,此时我们便可以在上面的基础上添加where条件进行筛选,我们先来介绍mysql中的比较运算符和逻辑运算符:

比较运算符:

逻辑运算符:

下面我们以几个例子来熟悉下where的使用方法:

1.英语不及格的同学及英语成绩 ( < 60 )

mysql> select name,english from exam_result where english < 60;
+-----------+---------+
| name      | english |
+-----------+---------+
| 唐三藏    |      56 |
| 刘玄德    |      45 |
| 宋公明    |      30 |
+-----------+---------+
3 rows in set (0.00 sec)

2.语文成绩在 [80, 90] 分的同学及语文成绩

mysql> select name,chinese from exam_result where chinese between 80 and 90;
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 孙悟空    |      87 |
| 猪悟能    |      88 |
| 曹孟德    |      82 |
+-----------+---------+
3 rows in set (0.00 sec)mysql> select name,chinese from exam_result where chinese >= 80 and chinese <= 90;
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 孙悟空    |      87 |
| 猪悟能    |      88 |
| 曹孟德    |      82 |
+-----------+---------+
3 rows in set (0.00 sec)

3.数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

mysql> select name,math from exam_result where math in(58,59,98,99);
+-----------+------+
| name      | math |
+-----------+------+
| 唐三藏    |   98 |
| 猪悟能    |   98 |
+-----------+------+
2 rows in set (0.00 sec)mysql> select name,math from exam_result where math = 58 or math = 59 or math = 98 or math = 99;
+-----------+------+
| name      | math |
+-----------+------+
| 唐三藏    |   98 |
| 猪悟能    |   98 |
+-----------+------+
2 rows in set (0.00 sec)

4.姓孙的同学及孙某同学

mysql> select name,math,chinese,english from exam_result where name like '孙%';
+-----------+------+---------+---------+
| name      | math | chinese | english |
+-----------+------+---------+---------+
| 孙悟空    |   78 |      87 |      77 |
| 孙权      |   73 |      70 |      78 |
+-----------+------+---------+---------+
2 rows in set (0.00 sec)mysql> select name,math,chinese,english from exam_result where name like '孙_';
+--------+------+---------+---------+
| name   | math | chinese | english |
+--------+------+---------+---------+
| 孙权   |   73 |      70 |      78 |
+--------+------+---------+---------+
1 row in set (0.00 sec)

5.语文成绩好于英语成绩的同学

mysql> select name,chinese,english from exam_result where chinese > english;
+-----------+---------+---------+
| name      | chinese | english |
+-----------+---------+---------+
| 唐三藏    |      67 |      56 |
| 孙悟空    |      87 |      77 |
| 曹孟德    |      82 |      67 |
| 刘玄德    |      55 |      45 |
| 宋公明    |      75 |      30 |
+-----------+---------+---------+
5 rows in set (0.00 sec)

6.总分在 200 分以下的同学

mysql> select name,math+chinese+english total from exam_result where total < 200;
ERROR 1054 (42S22): Unknown column 'total' in 'where clause'//因为我们说过where是从源表中筛选数据,而select是将结果打印到屏幕上,所以执行where时select还没有执行,total别名自然就不会被识别mysql> select name,math+chinese+english total from exam_result where english+chinese+chinese < 200;
+-----------+-------+
| name      | total |
+-----------+-------+
| 唐三藏    |   221 |
| 刘玄德    |   185 |
| 宋公明    |   170 |
+-----------+-------+
3 rows in set (0.00 sec)

7.语文成绩 > 80 并且不姓孙的同学

mysql> select name,chinese from exam_result where chinese > 80 and name not like '孙%';
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 猪悟能    |      88 |
| 曹孟德    |      82 |
+-----------+---------+
2 rows in set (0.00 sec)

8.孙某同学，否则要求总成绩 > 200 并且语文成绩 < 数学成绩并且英语成绩 > 80

mysql> select name,math,chinese,english,math+chinese+english total from exam_result where (english+chinese+chinese > 200 and chinese < math and english > 80) or name like '孙_';
+-----------+------+---------+---------+-------+
| name      | math | chinese | english | total |
+-----------+------+---------+---------+-------+
| 猪悟能    |   98 |      88 |      90 |   276 |
| 孙权      |   73 |      70 |      78 |   221 |
+-----------+------+---------+---------+-------+
2 rows in set (0.00 sec)

9.NULL 的查询

-- 查询 students 表
+-----+-------+-----------+-------+
| id | sn | name | qq |
+-----+-------+-----------+-------+
| 100 | 10010 | 唐大师 | NULL |
| 101 | 10001 | 孙悟空 | 11111 |
| 103 | 20002 | 孙仲谋 | NULL |
| 104 | 20001 | 曹阿瞒 | NULL |
+-----+-------+-----------+-------+
4 rows in set (0.00 sec)
-- 查询 qq 号已知的同学姓名
SELECT name, qq FROM students WHERE qq IS NOT NULL;
+-----------+-------+
| name | qq |
+-----------+-------+
| 孙悟空 | 11111 |
+-----------+-------+
1 row in set (0.00 sec)
-- NULL 和 NULL 的比较，= 和 <=> 的区别
SELECT NULL = NULL, NULL = 1, NULL = 0;
+-------------+----------+----------+
| NULL = NULL | NULL = 1 | NULL = 0 |
+-------------+----------+----------+
| NULL | NULL | NULL |
+-------------+----------+----------+
1 row in set (0.00 sec)
SELECT NULL <=> NULL, NULL <=> 1, NULL <=> 0;
+---------------+------------+------------+
| NULL <=> NULL | NULL <=> 1 | NULL <=> 0 |
+---------------+------------+------------+
| 1 | 0 | 0 |
+---------------+------------+------------+
1 row in set (0.00 sec)

2.2.3结果排序order by

语法:

-- ASC 为升序（从小到大）
-- DESC 为降序（从大到小）
-- 默认为 ASC
SELECT ... FROM table_name [WHERE ...]
ORDER BY column [ASC|DESC], [...];

注意：没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序
案例:
1.同学及数学成绩，按数学成绩升序显示

mysql> select name,math from exam_result order by math;
+-----------+------+
| name      | math |
+-----------+------+
| 宋公明    |   65 |
| 孙权      |   73 |
| 孙悟空    |   78 |
| 曹孟德    |   84 |
| 刘玄德    |   85 |
| 唐三藏    |   98 |
| 猪悟能    |   98 |
+-----------+------+
7 rows in set (0.00 sec)

2.查询同学各门成绩，依次按数学降序，英语升序，语文升序的方式显示

mysql> select name,math,chinese,english from exam_result order by math desc,english,chinese;
+-----------+------+---------+---------+
| name      | math | chinese | english |
+-----------+------+---------+---------+
| 唐三藏    |   98 |      67 |      56 |
| 猪悟能    |   98 |      88 |      90 |
| 刘玄德    |   85 |      55 |      45 |
| 曹孟德    |   84 |      82 |      67 |
| 孙悟空    |   78 |      87 |      77 |
| 孙权      |   73 |      70 |      78 |
| 宋公明    |   65 |      75 |      30 |
+-----------+------+---------+---------+
7 rows in set (0.00 sec)

3.查询同学及总分，由高到低

mysql> select name,math,chinese,english,math+chinese+english total from exam_result order by total desc;
+-----------+------+---------+---------+-------+
| name      | math | chinese | english | total |
+-----------+------+---------+---------+-------+
| 猪悟能    |   98 |      88 |      90 |   276 |
| 孙悟空    |   78 |      87 |      77 |   242 |
| 曹孟德    |   84 |      82 |      67 |   233 |
| 唐三藏    |   98 |      67 |      56 |   221 |
| 孙权      |   73 |      70 |      78 |   221 |
| 刘玄德    |   85 |      55 |      45 |   185 |
| 宋公明    |   65 |      75 |      30 |   170 |
+-----------+------+---------+---------+-------+
7 rows in set (0.00 sec)
//注意因为order by是对查询结果进行排序,所以order by执行在select之后,此时order by也就认识total

4.查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示

mysql> select name,math from exam_result where name like '孙%' or name like '曹%' order by math desc;
+-----------+------+
| name      | math |
+-----------+------+
| 曹孟德    |   84 |
| 孙悟空    |   78 |
| 孙权      |   73 |
+-----------+------+
3 rows in set (0.00 sec)

2.2.4筛选分页结果

语法:

-- 起始下标为 0
-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n
-- 从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
;
-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;
//注意update不能与limit offset结合使用

建议：对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死按 id 进行分页，每页 3 条记录，分别显示第 1、2、3 页

-- 第 1 页
SELECT id, name, math, english, chinese FROM exam_result
ORDER BY id LIMIT 3 OFFSET 0;
+----+-----------+--------+--------+-------+
| id | name | math | english | chinese |
+----+-----------+--------+--------+-------+
| 1 | 唐三藏 | 98 | 56 | 67 |
| 2 | 孙悟空 | 78 | 77 | 87 |
| 3 | 猪悟能 | 98 | 90 | 88 |
+----+-----------+--------+--------+-------+
3 rows in set (0.02 sec)-- 第 2 页
SELECT id, name, math, english, chinese FROM exam_result
ORDER BY id LIMIT 3 OFFSET 3;
+----+-----------+--------+--------+-------+
| id | name | math | english | chinese |
+----+-----------+--------+--------+-------+
| 4 | 曹孟德 | 84 | 67 | 82 |
| 5 | 刘玄德 | 85 | 45 | 55 |
| 6 | 孙权 | 73 | 78 | 70 |
+----+-----------+--------+--------+-------+
3 rows in set (0.00 sec)
-- 第 3 页，如果结果不足 3 个，不会有影响
SELECT id, name, math, english, chinese FROM exam_result
ORDER BY id LIMIT 3 OFFSET 6;
+----+-----------+--------+--------+-------+
| id | name | math | english | chinese |
+----+-----------+--------+--------+-------+
| 7 | 宋公明 | 65 | 30 | 75 |
+----+-----------+--------+--------+-------+
1 row in set (0.00 sec)

2.3update

语法:

UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]

对查询到的结果进行列值更新,下面是几个案例:

1.将孙悟空同学的数学成绩变更为 80 分

mysql> update exam_result set math = 80 where name = '孙悟空';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

2.将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分

mysql> update exam_result set math = 60,chinese = 70 where name = '曹孟德';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

3.将总成绩倒数前三的 3 位同学的数学成绩加上 30 分

mysql> update exam_result set math=math+30 order by english+chinese+math limit 3;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

4.将所有同学的语文成绩更新为原来的 2 倍

注意：更新全表的语句慎用！

mysql> select name,chinese from exam_result;
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 唐三藏    |      67 |
| 孙悟空    |      87 |
| 猪悟能    |      88 |
| 曹孟德    |      70 |
| 刘玄德    |      55 |
| 孙权      |      70 |
| 宋公明    |      75 |
+-----------+---------+
7 rows in set (0.00 sec)mysql> update exam_result set chinese = chinese * 2;
Query OK, 7 rows affected (0.01 sec)
Rows matched: 7  Changed: 7  Warnings: 0mysql> select name,chinese from exam_result;
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 唐三藏    |     134 |
| 孙悟空    |     174 |
| 猪悟能    |     176 |
| 曹孟德    |     140 |
| 刘玄德    |     110 |
| 孙权      |     140 |
| 宋公明    |     150 |
+-----------+---------+
7 rows in set (0.00 sec)

2.4delete

语法:

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

案例:

1.删除孙悟空同学的考试成绩

-- 查看原数据
SELECT * FROM exam_result WHERE name = '孙悟空';
+----+-----------+-------+--------+--------+
| id | name | chinese | math | english |
+----+-----------+-------+--------+--------+
| 2 | 孙悟空 | 174 | 80 | 77 |
+----+-----------+-------+--------+--------+
1 row in set (0.00 sec)
-- 删除数据
DELETE FROM exam_result WHERE name = '孙悟空';
Query OK, 1 row affected (0.17 sec)
-- 查看删除结果
SELECT * FROM exam_result WHERE name = '孙悟空';
Empty set (0.00 sec)

以下两个操作慎用:

2. 删除整张表的数据

-- 准备测试表
CREATE TABLE for_delete (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
Query OK, 0 rows affected (0.16 sec)
-- 插入测试数据
INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');
Query OK, 3 rows affected (1.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
-- 查看测试数据
SELECT * FROM for_delete;
+----+------+
| id | name |
+----+------+
| 1 | A |
| 2 | B |
| 3 | C |
+----+------+
3 rows in set (0.00 sec)
-- 删除整表数据
DELETE FROM for_delete;
Query OK, 3 rows affected (0.00 sec)
-- 查看删除结果
SELECT * FROM for_delete;
Empty set (0.00 sec)
-- 再插入一条数据，自增 id 在原值上增长
INSERT INTO for_delete (name) VALUES ('D');
Query OK, 1 row affected (0.00 sec)
-- 查看数据
SELECT * FROM for_delete;
+----+------+
| id | name |
+----+------+
| 4 | D |
+----+------+
1 row in set (0.00 sec)
-- 查看表结构，会有 AUTO_INCREMENT=n 项
SHOW CREATE TABLE for_delete\G
*************************** 1. row ***************************
Table: for_delete
Create Table: CREATE TABLE `for_delete` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

3.截断表

语法:

TRUNCATE [TABLE] table_name

1. 只能对整表操作，不能像 DELETE 一样针对部分数据操作；

2. 实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事物，所以无法回滚

3. 会重置 AUTO_INCREMENT 项

-- 准备测试表
CREATE TABLE for_truncate (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
Query OK, 0 rows affected (0.16 sec)
-- 插入测试数据
INSERT INTO for_truncate (name) VALUES ('A'), ('B'), ('C');
Query OK, 3 rows affected (1.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
-- 查看测试数据
SELECT * FROM for_truncate;
+----+------+
| id | name |
+----+------+
| 1 | A |
| 2 | B |
| 3 | C |
+----+------+
3 rows in set (0.00 sec)
-- 截断整表数据，注意影响行数是 0，所以实际上没有对数据真正操作
TRUNCATE for_truncate;
Query OK, 0 rows affected (0.10 sec)
-- 查看删除结果
SELECT * FROM for_truncate;
Empty set (0.00 sec)
-- 再插入一条数据，自增 id 在重新增长
INSERT INTO for_truncate (name) VALUES ('D');
Query OK, 1 row affected (0.00 sec)
-- 查看数据
SELECT * FROM for_truncate;
+----+------+
| id | name |
+----+------+
| 1 | D |
+----+------+
1 row in set (0.00 sec)
-- 查看表结构，会有 AUTO_INCREMENT=2 项
SHOW CREATE TABLE for_truncate\G
*************************** 1. row ***************************
Table: for_truncate
Create Table: CREATE TABLE `for_truncate` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

2.5插入查询结果

如果我们想要去掉源表中的重复数据(针对某列或复合列),注意这里不是简单的对查询结果去重而是对源表中的数据去重:

语法:

INSERT INTO table_name [(column [, column ...])] SELECT ...

案例:
删除表中的的重复复记录，重复的数据只能有一份

-- 创建原数据表
CREATE TABLE duplicate_table (id int, name varchar(20));
Query OK, 0 rows affected (0.01 sec)
-- 插入测试数据
INSERT INTO duplicate_table VALUES
(100, 'aaa'),
(100, 'aaa'),
(200, 'bbb'),
(200, 'bbb'),
(200, 'bbb'),
(300, 'ccc');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

思路:

-- 创建一张空表 no_duplicate_table，结构和 duplicate_table 一样
CREATE TABLE no_duplicate_table LIKE duplicate_table;
Query OK, 0 rows affected (0.00 sec)-- 将 duplicate_table 的去重数据插入到 no_duplicate_table
INSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
-- 通过重命名表，实现原子的去重操作
RENAME TABLE duplicate_table TO old_duplicate_table,
no_duplicate_table TO duplicate_table;
Query OK, 0 rows affected (0.00 sec)
-- 查看最终结果
SELECT * FROM duplicate_table;
+------+------+
| id | name |
+------+------+
| 100 | aaa |
| 200 | bbb |
| 300 | ccc |
+------+------+
3 rows in set (0.00 sec)

2.6聚合函数

案例:

1.统计班级共有多少同学

mysql> select count(*) 班级人数 from exam_result;
+--------------+
| 班级人数     |
+--------------+
|            7 |
+--------------+
1 row in set (0.00 sec)

2.统计本次考试的数学成绩分数个数

mysql> select count(math) 参加数学考试的人数 from exam_result;
+-----------------------------+
| 参加数学考试的人数          |
+-----------------------------+
|                           7 |
+-----------------------------+
1 row in set (0.00 sec)

3.统计数学成绩总分

mysql> select sum(math) 所有人的数学成绩总和 from exam_result;
+--------------------------------+
| 所有人的数学成绩总和           |
+--------------------------------+
|                            649 |
+--------------------------------+
1 row in set (0.00 sec)

4.统计平均总分

mysql> select avg(chinese+math+english) 班级平均总分 from exam_result;
+--------------------+
| 班级平均总分       |
+--------------------+
|  302.2857142857143 |
+--------------------+
1 row in set (0.00 sec)

5.返回英语最高分

mysql> select max(english) from exam_result;
+--------------+
| max(english) |
+--------------+
|           90 |
+--------------+
1 row in set (0.00 sec)

6.返回 > 70 分以上的数学最低分

mysql> select min(math) from exam_result where math > 70;
+-----------+
| min(math) |
+-----------+
|        73 |
+-----------+
1 row in set (0.00 sec)

2.7group by子句

假设我们现在有这样一张工资表:

mysql> select * from emp;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)mysql> show create table emp\G
*************************** 1. row ***************************Table: emp
Create Table: CREATE TABLE `emp` (`empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',`ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',`job` varchar(9) DEFAULT NULL COMMENT '雇员职位',`mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',`hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',`sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',`comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',`deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

如果我们想要显示不同部门的平均工资,单单使用上面介绍的方法无法实现.此时我们就可以借助group by子句对表进行分组,分组简单的理解就是分表.为什么?

比如还是上面我们举的那个例子,不同的人员可能有着相同的部门号(deptno),那么对where子句筛选后的结果再进行分组,不就是将一张整表按照部门号分成了多个不同的子表,而这些子表中的deptno都是相同的.

下面我们来看几个例子来熟悉下group by的用法:
1.显示每个部门的平均工资和最高工资

mysql> select deptno 部门,avg(sal) 平均工资,max(sal) 最高工资 from emp group by deptno;
+--------+--------------+--------------+
| 部门   | 平均工资     | 最高工资     |
+--------+--------------+--------------+
|     10 |  2916.666667 |      5000.00 |
|     20 |  2175.000000 |      3000.00 |
|     30 |  1566.666667 |      2850.00 |
+--------+--------------+--------------+
3 rows in set (0.00 sec)

2.显示每个部门的每种岗位的平均工资和最低工资

mysql> select deptno 部门,job 岗位类别,avg(sal) 平均工资,min(sal) 最低工资 from emp group by deptno,job;
+--------+--------------+--------------+--------------+
| 部门   | 岗位类别     | 平均工资     | 最低工资     |
+--------+--------------+--------------+--------------+
|     10 | CLERK        |  1300.000000 |      1300.00 |
|     10 | MANAGER      |  2450.000000 |      2450.00 |
|     10 | PRESIDENT    |  5000.000000 |      5000.00 |
|     20 | ANALYST      |  3000.000000 |      3000.00 |
|     20 | CLERK        |   950.000000 |       800.00 |
|     20 | MANAGER      |  2975.000000 |      2975.00 |
|     30 | CLERK        |   950.000000 |       950.00 |
|     30 | MANAGER      |  2850.000000 |      2850.00 |
|     30 | SALESMAN     |  1400.000000 |      1250.00 |
+--------+--------------+--------------+--------------+
9 rows in set (0.00 sec)

3.显示平均工资低于2000的部门和它的平均工资(使用having作为group by的条件筛选子句)

mysql> select deptno 部门,avg(sal) 平均工资 from emp group by deptno having avg(sal) < 2000;
+--------+--------------+
| 部门   | 平均工资     |
+--------+--------------+
|     30 |  1566.666667 |
+--------+--------------+
1 row in set (0.00 sec)