#创建表，把id设为主键
mysql> create table test02(-> id int primary key, #----主键约束-> name varchar(50)-> );
Query OK, 0 rows affected (0.02 sec)
#插入数据测试
mysql> insert into test02 values(1,"成都");
Query OK, 1 row affected (0.01 sec)
mysql> insert into test02 values(1,"西安");
ERROR 1062 (23000): Duplicate entry '1' for key 'test02.PRIMARY'
mysql> insert into test02 values(null,"西安");
ERROR 1048 (23000): Column 'id' cannot be null
mysql> insert into test02 values(2,"西安");
Query OK, 1 row affected (0.00 sec)
#主键自动增加auto_increment
mysql> create table test02(-> id int primary key auto_increment,-> name varchar(50)-> );
Query OK, 0 rows affected (0.02 sec)
#插入数据测试
mysql> insert into test02 values(1,"成都");
Query OK, 1 row affected (0.00 sec)
mysql> insert into test02 values(1,"西安");
ERROR 1062 (23000): Duplicate entry '1' for key 'test02.PRIMARY'
mysql> insert into test02 values(null,"西安");
Query OK, 1 row affected (0.01 sec)
mysql> insert into test02 values(2,"西安");
ERROR 1062 (23000): Duplicate entry '2' for key 'test02.PRIMARY'
mysql> insert into test02 values(3,"西安");
Query OK, 1 row affected (0.01 sec)
mysql> select * from test02;
+----+--------+
| id | name |
+----+--------+
| 1 | 成都 |
| 2 | 西安 |
| 3 | 西安 |
+----+--------+
3 rows in set (0.00 sec)
#主键另外一种写法
mysql> create table test02(-> id int auto_increment,-> name varchar(50),-> primary key(id)-> );
Query OK, 0 rows affected (0.02 sec)唯一约束
mysql> create table test02(-> id int primary key auto_increment,-> name varchar(50) unique #---唯一约束-> );
Query OK, 0 rows affected (0.04 sec)
#插入数据测试
mysql> insert into test02 values(1,"成都");
Query OK, 1 row affected (0.01 sec)
mysql> insert into test02 values(2,"成都");
ERROR 1062 (23000): Duplicate entry '成都' for key 'test02.name'
mysql> insert into test02 values(2,"西安");
Query OK, 1 row affected (0.00 sec)
mysql> select * from test02;
+----+--------+
| id | name |
+----+--------+
| 1 | 成都 |
| 2 | 西安 |
+----+--------+
2 rows in set (0.00 sec)
# 另一种写法
create table tets02(-> id int auto_increment,-> name varchar(50),# ---唯一约束 -> primary key(id),-> unique(name)-> );
Query OK, 0 rows affected (0.02 sec)默认值约束
mysql> create table test02(-> id int primary key auto_increment,-> name varchar(50) unique,-> age int default 18-> );
Query OK, 0 rows affected (0.04 sec)
#插入数据测试
mysql> insert into test02 values(1,"小红",20);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test02 values(default,"小蓝",19); #---因为存在自增，所以可以写默认
Query OK, 1 row affected (0.00 sec)
mysql> insert into test02(name) values("小黄");
Query OK, 1 row affected (0.00 sec)
mysql> select * from test02;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 小红 | 20 |
| 2 | 小蓝 | 19 |
| 3 | 小黄 | 18 |
+----+--------+------+
3 rows in set (0.00 sec)非空约束
mysql> create table test02(-> id int primary key auto_increment,-> name varchar(50) unique not null,-> age int default 18,-> password varchar(255) not null-> );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test02 values(1,"小兰",23,"890890");
Query OK, 1 row affected (0.01 sec)
mysql> insert into test02 values(2,"小二",18,null);
ERROR 1048 (23000): Column 'password' cannot be null检查约束
mysql> create table test02(-> id int primary key auto_increment,-> name varchar(50) not null unique,-> age int check(age >= 18)-> );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test02 value(null,"张三",20);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test02 value(null,"李四",17);
ERROR 3819 (HY000): Check constraint 'test02_chk_1' is violated.
mysql>修改表结构
修改列类型ALTER TABLE 表名 MODIFY 列名 列类型; -- 注意存在值的情况，类型不一定能成功
增加列ALTER TABLE 表名 ADD 列名 列类型;
删除列ALTER TABLE 表名 DROP 列名;
列改名ALTER TABLE 表名 CHANGE 旧列名 新列名 列类型;
更改表名ALTER TABLE 表名 RENAME 新表名;RENAME TABLE 表名 TO 新表名;
mysql> alter table test02 modify age varchar(20);
Query OK, 1 row affected (0.07 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> alter table test02 add address varchar(50);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from test02;
+----+--------+------+---------+
| id | name | age | address |
+----+--------+------+---------+
| 1 | 张三 | 20 | NULL |
+----+--------+------+---------+
1 row in set (0.00 sec)
mysql> alter table test02 drop age;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from test02;
+----+--------+---------+
| id | name | address |
+----+--------+---------+
| 1 | 张三 | NULL |
+----+--------+---------+
1 row in set (0.00 sec)
mysql> alter table test02 change address age int;
Query OK, 1 row affected (0.07 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from test02;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 张三 | NULL |
+----+--------+------+
1 row in set (0.00 sec)
mysql> alter table test02 rename test03;
Query OK, 0 rows affected (0.02 sec)
mysql> rename table test03 to test02;
Query OK, 0 rows affected (0.01 sec)replace语句
该语句是集更新和插入为一体的一个语句。
如果表中没有这条数据，则执行插入，否则执行更新
mysql> replace into test02 values(1,"小红",100);
Query OK, 2 rows affected (0.00 sec)
mysql> select * from test02;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 小红 | 100 |
| 2 | 小蓝 | 30 |
| 3 | 小绿 | 31 |
| 4 | 小羊 | 44 |
| 5 | 小牛 | 32 |
+----+--------+------+
5 rows in set (0.00 sec)
select查询语句
在开发中，查询语句是使用最多，也是CRUD中，复杂度最高的sql语句。
查询的语法结构
select *|字段1 [, 字段2 ……] from 表名称 [, 表名称2……] [where 条件] [group by 字段 [having 分组后的筛选]] [order by 字段 [desc|asc] [字段2 [desc|asc] ……]] [limit 分页]
简单的sql查询
#查询所有数据
select * from test02;
#查询需要的字段信息
select id,name from test02;
# 查询一个字段，一个等值条件
select name from test02 where id=1;
补充：+ 说明
-- MySQL的+默认只有一个功能：运算符
SELECT 100+80; # 结果为180
SELECT '123'+80; # 只要其中一个为数值，则试图将字符型转换成数值，转换成功做预算，结果为203
SELECT 'abc'+80; # 转换不成功，则字符型数值为0，结果为80
SELECT 'This'+'is'; # 转换不成功，结果为0
SELECT 'This'+'30is'; # ？猜测下这个结果是多少？
SELECT NULL+80; # 只要其中一个为NULL，则结果为NULL
等值判断
条件中，出现了相等值的判断，一般采用=进行判断。
= 判断两次的值是否相等
is 判断空null
is not null来判断不为空
<=> 可以判断null或者普通值
不等判断
!= 不等于
<>也是不等于
逻辑运算符
逻辑运算符是多条件关联的一种方式。
与或非
and
or
not
注意：在sql中，如果要提升条件的运行顺序，或者提高条件的优先级别，则需要使用括号来提升。
结果排序
# 语法结构
order by 字段
order by 字段 asc|desc;
order by 字段 asc|desc, 字段2 ;
mysql> select * from test02;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 小红 | 100 |
| 2 | 小蓝 | 30 |
| 3 | 小绿 | 31 |
| 4 | 小羊 | 44 |
| 5 | 小牛 | 32 |
+----+--------+------+
5 rows in set (0.00 sec)
mysql> select * from test02 order by age;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 2 | 小蓝 | 30 |
| 3 | 小绿 | 31 |
| 5 | 小牛 | 32 |
| 4 | 小羊 | 44 |
| 1 | 小红 | 100 |
+----+--------+------+
5 rows in set (0.00 sec)
mysql> select * from test02 order by age asc;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 2 | 小蓝 | 30 |
| 3 | 小绿 | 31 |
| 5 | 小牛 | 32 |
| 4 | 小羊 | 44 |
| 1 | 小红 | 100 |
+----+--------+------+
5 rows in set (0.00 sec)
mysql> select * from test02 order by age desc;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 小红 | 100 |
| 4 | 小羊 | 44 |
| 5 | 小牛 | 32 |
| 3 | 小绿 | 31 |
| 2 | 小蓝 | 30 |
+----+--------+------+
5 rows in set (0.00 sec)
mysql> select * from test02 order by age,id desc;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 2 | 小蓝 | 30 |
| 3 | 小绿 | 31 |
| 5 | 小牛 | 32 |
| 4 | 小羊 | 44 |
| 1 | 小红 | 100 |
+----+--------+------+
5 rows in set (0.00 sec)
分页功能
select语句，查询数据时，可能结果会非常多，此时就不能直接展示，分页展示。
总数量（all_data）：查询 select count(*)
每页展示的数量(page_size)：程序员定
当前页(cur_page)：默认第一页，用户自己点击选择
总页数(all_page)：总数量 % 每页的数量 == 0 整除后的商 ： 商 + 1
mysql> select * from test02 limit 2;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 小红 | 100 |
| 2 | 小蓝 | 30 |
+----+--------+------+
2 rows in set (0.00 sec)
mysql> select * from test02 limit 3,2;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 4 | 小羊 | 44 |
| 5 | 小牛 | 32 |
+----+--------+------+
2 rows in set (0.00 sec)
为什么分表
数据直接都存储在一张表中：
如果数据很大，性能会出现问题
将不同的数据，如果放在同一个表中，可能数据冗余
数据冗余，会导致数据可能出错
将不同的类型，采用不同的数据表进行存储，如果两张表或者多张表之间存在关联关系，则可以采用外键来描述这种关联关系。
主表中，一般是一个字段，改字段一般是从表的主键。
mysql> create table grade(
-> id int auto_increment,
-> name varchar(50) unique,
-> primary key(id)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into grade(name) value("Java精品班"), ("python数据分析班"), ("网络安全班"), ("云原生高级班");
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> create table student(
-> id int primary key auto_increment,
-> name varchar(50) unique,
-> class_id int
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> desc student;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | UNI | NULL | |
| class_id | int | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> insert into student(name, class_id) values("张三", 1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into student(name, class_id) values("张三2", 3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into student(name, class_id) values("张三3", 2);
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+---------+----------+
| id | name | class_id |
+----+---------+----------+
| 1 | 张三 | 1 |
| 2 | 张三2 | 3 |
| 3 | 张三3 | 2 |
+----+---------+----------+
3 rows in set (0.00 sec)
mysql> select * from grade;
+----+-----------------------+
| id | name |
+----+-----------------------+
| 1 | Java精品班 |
| 2 | python数据分析班 |
| 4 | 云原生高级班 |
| 3 | 网络安全班 |
+----+-----------------------+
4 rows in set (0.00 sec)
外键和多表关联
外键：指的是两张或者多张表之间关联关系的字段。
外键约束：是表的约束，是约束表在插入外键数据时能够正确的插入。
如何添加约束：
# 在创建表的同时，将外键约束添加上去
# 首先保证班级表创建成功
# 插入正确的数据
create table grade(
id int auto_increment,
name varchar(50) unique,
primary key(id)
)
insert into grade(name) value("Java精品班"), ("python数据分析班"), ("网络安全班"), ("云原生高级班");
外键约束
外键是构建于一个表的两个字段或者两个表的两个字段之间的关系
外键确保了相关的两个字段的两个关系：
子(从)表外键列的值必须在主表参照列值的范围内，或者为空（也可以加非空约束，强制不允许为空）。
当主表的记录被子表参照时，主表记录不允许被删除。
外键参照的只能是主表主键或者唯一键，保证子表记录可以准确定位到被参照的记录。
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(外键字段名)
REFERENCES 外表表名(主键字段名)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
格式FOREIGN KEY (外键列名)REFERENCES 主表(参照列)
表与表之间的关联关系
当表与表之间存在了外键，这就意味着，这两张表之间存在某种关联关系。
一旦表存在了关联关系，则会进行外键设计，如果设计外键，将外键设计在哪张表中？
一对一 ：外键可以设计在任意一张表中
一对多 ：外键必须设计在多方
多对多 ：创建第三张表，来专门描述两张表的关联关系
多表关联查询
当两张或者多张表之间存在了关联关系，往往多表查询，如果查询。
交叉连接
内连接
外链接
左外连接
右外连接
自连接
全连接
select *|字段 [,……] from 表名称 [,表名称] ……
#-- 交叉连接(cross join)
#-- 在查询多表时，不指定表的关联关系，数据只能全部匹配
#-- 引发笛卡尔积现象
mysql> select * from student,grade;
+----+---------+----------+----+-----------------------+
| id | name | class_id | id | name |
+----+---------+----------+----+-----------------------+
| 3 | 张三3 | 2 | 1 | Java精品班 |
| 2 | 张三2 | 3 | 1 | Java精品班 |
| 1 | 张三 | 1 | 1 | Java精品班 |
| 3 | 张三3 | 2 | 2 | python数据分析班 |
| 2 | 张三2 | 3 | 2 | python数据分析班 |
| 1 | 张三 | 1 | 2 | python数据分析班 |
| 3 | 张三3 | 2 | 4 | 云原生高级班 |
| 2 | 张三2 | 3 | 4 | 云原生高级班 |
| 1 | 张三 | 1 | 4 | 云原生高级班 |
| 3 | 张三3 | 2 | 3 | 网络安全班 |
| 2 | 张三2 | 3 | 3 | 网络安全班 |
| 1 | 张三 | 1 | 3 | 网络安全班 |
+----+---------+----------+----+-----------------------+
12 rows in set (0.00 sec)
#-- sql98的标准写法
mysql> select * from student cross join grade;
+----+---------+----------+----+-----------------------+
| id | name | class_id | id | name |
+----+---------+----------+----+-----------------------+
| 3 | 张三3 | 2 | 1 | Java精品班 |
| 2 | 张三2 | 3 | 1 | Java精品班 |
| 1 | 张三 | 1 | 1 | Java精品班 |
| 3 | 张三3 | 2 | 2 | python数据分析班 |
| 2 | 张三2 | 3 | 2 | python数据分析班 |
| 1 | 张三 | 1 | 2 | python数据分析班 |
| 3 | 张三3 | 2 | 4 | 云原生高级班 |
| 2 | 张三2 | 3 | 4 | 云原生高级班 |
| 1 | 张三 | 1 | 4 | 云原生高级班 |
| 3 | 张三3 | 2 | 3 | 网络安全班 |
| 2 | 张三2 | 3 | 3 | 网络安全班 |
| 1 | 张三 | 1 | 3 | 网络安全班 |
+----+---------+----------+----+-----------------------+
12 rows in set (0.00 sec)
# 内连接
mysql> select * from student, grade where student.class_id = grade.id;
+----+---------+----------+----+-----------------------+
| id | name | class_id | id | name |
+----+---------+----------+----+-----------------------+
| 1 | 张三 | 1 | 1 | Java精品班 |
| 2 | 张三2 | 3 | 3 | 网络安全班 |
| 3 | 张三3 | 2 | 2 | python数据分析班 |
+----+---------+----------+----+-----------------------+
3 rows in set (0.00 sec)
mysql> select * from student, grade where student.class_id = grade.id and student.name = '张三';
+----+--------+----------+----+---------------+
| id | name | class_id | id | name |
+----+--------+----------+----+---------------+
| 1 | 张三 | 1 | 1 | Java精品班 |
+----+--------+----------+----+---------------+
1 row in set (0.00 sec)
# 注意：sql98的内连接方式时，如果不指定关联条件，不管怎么写，都是交叉连接
mysql> select * from student inner join grade;
+----+---------+----------+----+-----------------------+
| id | name | class_id | id | name |
+----+---------+----------+----+-----------------------+
| 3 | 张三3 | 2 | 1 | Java精品班 |
| 2 | 张三2 | 3 | 1 | Java精品班 |
| 1 | 张三 | 1 | 1 | Java精品班 |
| 3 | 张三3 | 2 | 2 | python数据分析班 |
| 2 | 张三2 | 3 | 2 | python数据分析班 |
| 1 | 张三 | 1 | 2 | python数据分析班 |
| 3 | 张三3 | 2 | 4 | 云原生高级班 |
| 2 | 张三2 | 3 | 4 | 云原生高级班 |
| 1 | 张三 | 1 | 4 | 云原生高级班 |
| 3 | 张三3 | 2 | 3 | 网络安全班 |
| 2 | 张三2 | 3 | 3 | 网络安全班 |
| 1 | 张三 | 1 | 3 | 网络安全班 |
+----+---------+----------+----+-----------------------+
12 rows in set (0.00 sec)
mysql> select * from student join grade;
+----+---------+----------+----+-----------------------+
| id | name | class_id | id | name |
+----+---------+----------+----+-----------------------+
| 3 | 张三3 | 2 | 1 | Java精品班 |
| 2 | 张三2 | 3 | 1 | Java精品班 |
| 1 | 张三 | 1 | 1 | Java精品班 |
| 3 | 张三3 | 2 | 2 | python数据分析班 |
| 2 | 张三2 | 3 | 2 | python数据分析班 |
| 1 | 张三 | 1 | 2 | python数据分析班 |
| 3 | 张三3 | 2 | 4 | 云原生高级班 |
| 2 | 张三2 | 3 | 4 | 云原生高级班 |
| 1 | 张三 | 1 | 4 | 云原生高级班 |
| 3 | 张三3 | 2 | 3 | 网络安全班 |
| 2 | 张三2 | 3 | 3 | 网络安全班 |
| 1 | 张三 | 1 | 3 | 网络安全班 |
+----+---------+----------+----+-----------------------+
12 rows in set (0.00 sec)
# 正确写法，必须写清楚关联条件
mysql> select * from student inner join grade on (student.class_id=grade.id);
+----+---------+----------+----+-----------------------+
| id | name | class_id | id | name |
+----+---------+----------+----+-----------------------+
| 1 | 张三 | 1 | 1 | Java精品班 |
| 2 | 张三2 | 3 | 3 | 网络安全班 |
| 3 | 张三3 | 2 | 2 | python数据分析班 |
+----+---------+----------+----+-----------------------+
3 rows in set (0.00 sec)
mysql> select * from student join grade on (student.class_id=grade.id) where student.name = "张三3";
+----+---------+----------+----+-----------------------+
| id | name | class_id | id | name |
+----+---------+----------+----+-----------------------+
| 3 | 张三3 | 2 | 2 | python数据分析班 |
+----+---------+----------+----+-----------------------+
1 row in set (0.00 sec)
# 内连接，只能查询出，存在关联关系的数据，如果不存在关联关系，如目前没有班级的学生，目前没有学生的班级
mysql> select * from student cross join grade on (student.class_id=grade.id) where student.name = "张三3";
+----+---------+----------+----+-----------------------+
| id | name | class_id | id | name |
+----+---------+----------+----+-----------------------+
| 3 | 张三3 | 2 | 2 | python数据分析班 |
+----+---------+----------+----+-----------------------+
1 row in set (0.00 sec)
# 如果要将这些没关联关系的数据查询出来，则需要使用外连接
mysql> select * from student left join grade on (student.class_id=grade.id);
+----+---------+----------+------+-----------------------+
| id | name | class_id | id | name |
+----+---------+----------+------+-----------------------+
| 1 | 张三 | 1 | 1 | Java精品班 |
| 2 | 张三2 | 3 | 3 | 网络安全班 |
| 3 | 张三3 | 2 | 2 | python数据分析班 |
+----+---------+----------+------+-----------------------+
3 rows in set (0.00 sec)
mysql> select * from student right join grade on (student.class_id=grade.id);
+------+---------+----------+----+-----------------------+
| id | name | class_id | id | name |
+------+---------+----------+----+-----------------------+
| 1 | 张三 | 1 | 1 | Java精品班 |
| 3 | 张三3 | 2 | 2 | python数据分析班 |
| NULL | NULL | NULL | 4 | 云原生高级班 |
| 2 | 张三2 | 3 | 3 | 网络安全班 |
+------+---------+----------+----+-----------------------+
4 rows in set (0.00 sec)
# 注意：mysql不支持全连接查询 full join
# 但是SQL存在联合查询 union 、union all
# 注意：联合查询，必须保证查询的多条SQL返回的结果 结构必须一致，所以联合查询常见于查询一张表
表单查询、多表查询
一.单表查询
(1)查询前的数据准备
1.创建用于数据查询的数据库表
CREATE TABLE worker (
`部门号` int(11) NOT NULL,
`职工号` int(11) NOT NULL,
`工作时间` date NOT NULL,
`工资` float(8,2) NOT NULL,
`政治面貌` varchar(10) NOT NULL DEFAULT '群众',
`姓名` varchar(20) NOT NULL,
`出生日期` date NOT NULL,
PRIMARY KEY (`职工号`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
2.向表worker中插入用于查询的数据
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (101, 1001, '2015-5-4', 3500.00, '群众', '张三', '1990-7-1');
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (101, 1002, '2017-2-6', 3200.00, '团员', '李四', '1997-2-8');
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (102, 1003, '2011-1-4', 8500.00, '党员', '王亮', '1983-6-8');
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (102, 1006, '2025-3-5', 14500.00, '党员', 'RenJiawei', '2003-10-1');
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (102, 1004, '2016-10-10', 5500.00, '群众', '赵六', '1994-9-5');
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (102, 1005, '2014-4-1', 4800.00, '党员', '钱七', '1992-12-30');
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (102, 1006, '2017-5-5', 4500.00, '党员', '孙八', '1996-9-2');
(2)单表查询相关示例
1、显示所有职工的基本信息。
SELECT * FROM worker;
1
2、查询所有职工所属部门的部门号，不显示重复的部门号。
SELECT DISTINCT 部门号 FROM worker;
1
3、求出所有职工的人数。
SELECT COUNT(*) AS 职工总数 FROM worker;
1
4、列出最高工和最低工资。
SELECT MAX(工资) AS 最高工资, MIN(工资) AS 最低工资 FROM worker;
1
5、列出职工的平均工资和总工资。
CREATE TABLE 工作日期表 AS SELECT 职工号, 姓名, 工作时间 FROM worker;
1
6、创建一个只有职工号、姓名和参加工作的新表，名为工作日期表。
CREATE TABLE 工作日期表 AS SELECT 职工号, 姓名, 工作时间 FROM worker;
1
7、列出所有姓李的职工的职工号、姓名和出生日期。
SELECT 职工号, 姓名, 出生日期 FROM worker WHERE 姓名 LIKE '李%';
1
8、列出1960年以后出生的职工的姓名、参加工作日期。
SELECT 姓名, 工作时间 FROM worker WHERE 出生日期 > '1960-01-01';
1
9、列出工资在5000－8000之间的所有职工姓名。
SELECT 姓名 FROM worker WHERE 工资 > 5000 AND 工资 <8000;
1
10、列出所有孙姓和李姓的职工姓名。
SELECT 姓名 FROM worker WHERE 姓名 LIKE '孙%' OR 姓名 LIKE '李%';
1
11、列出所有部门号为102和103的职工号、姓名、是否党员。
SELECT 职工号, 姓名, IF(政治面貌='党员', '是', '否') AS 党员否
-> FROM worker WHERE 部门号 IN (102, 103);
1
2
12、将职工表worker中的职工按出生的先后顺序排序。
SELECT * FROM worker ORDER BY 出生日期 ASC;
1
13、显示工资最高的前3名职工的职工号和姓名。
SELECT 部门号, COUNT(*) AS 党员人数
FROM worker WHERE 政治面貌 = '党员' GROUP BY 部门号;
1
2
14、求出各部门党员的人数。
SELECT 部门号, SUM(工资) AS 总工资, AVG(工资) AS 平均工资
FROM worker GROUP BY 部门号;
1
2
15、统计各部门的工资和平均工资。
SELECT 部门号, SUM(工资) AS 总工资, AVG(工资) AS 平均工资
FROM worker GROUP BY 部门号;
1
2
16、列出总人数大于或等于4的部门号和总人数。
SELECT 部门号, COUNT(*) AS 人数
FROM worker GROUP BY 部门号 HAVING 人数 >= 4;
二.多表查询
(1)查询前的数据准备
1.创建student表
CREATE TABLE student (
id INT(10) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
sex VARCHAR(4),
birth YEAR,
department VARCHAR(20),
address VARCHAR(50)
);
2.创建score表
CREATE TABLE score (
id INT(10) PRIMARY KEY AUTO_INCREMENT,
stu_id INT(10) NOT NULL,
c_name VARCHAR(20),
grade INT(10)
);
3.向表student中插入数据以用于查询
INSERT INTO student VALUES( 901,'张老大', '男',1985,'计算机系', '北京市海淀区');
INSERT INTO student VALUES( 902,'张老二', '男',1986,'中文系', '北京市昌平区');
INSERT INTO student VALUES( 903,'张三', '女',1990,'中文系', '湖南省永州市');
INSERT INTO student VALUES( 904,'李四', '男',1990,'英语系', '辽宁省阜新市');
INSERT INTO student VALUES( 905,'王五', '女',1991,'英语系', '福建省厦门市');
INSERT INTO student VALUES( 906,'王六', '男',1988,'计算机系', '湖南省衡阳市');
INSERT INTO student VALUES( 907,'RenJiawei', '男',2003,'计算机系', '四川省成都市');
4.向表score中插入数据以用于查询
INSERT INTO score VALUES(NULL,901, '计算机',98);
INSERT INTO score VALUES(NULL,901, '英语', 80);
INSERT INTO score VALUES(NULL,902, '计算机',65);
INSERT INTO score VALUES(NULL,902, '中文',88);
INSERT INTO score VALUES(NULL,903, '中文',95);
INSERT INTO score VALUES(NULL,904, '计算机',70);
INSERT INTO score VALUES(NULL,904, '英语',92);
INSERT INTO score VALUES(NULL,905, '英语',94);
INSERT INTO score VALUES(NULL,906, '计算机',90);
INSERT INTO score VALUES(NULL,906, '英语',85);
(2)多表查询相关示例
1.查询student表的所有记录
SELECT * FROM student;
1
2.查询student表的第2条到4条记录
SELECT * FROM student LIMIT 1, 3;
1
3.从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息
SELECT id, name, department FROM student;
1
4.从student表中查询计算机系和英语系的学生的信息
SELECT * FROM student
WHERE department IN ('计算机系', '英语系');
1
2
5.从student表中查询年龄18~45岁的学生信息
SELECT * FROM student
WHERE (YEAR(CURDATE()) - birth) BETWEEN 18 AND 45;
1
2
6.从student表中查询每个院系有多少人
SELECT department, COUNT(*) AS 人数
FROM student
GROUP BY department;
1
2
3
7.从score表中查询每个科目的最高分
SELECT c_name, MAX(grade) AS 最高分
FROM score
GROUP BY c_name;
1
2
3
8.查询李四的考试科目（c_name）和考试成绩（grade）
SELECT c_name, grade
FROM score
WHERE stu_id = (SELECT id FROM student WHERE name = '李四');
1
2
3
9.用连接的方式查询所有学生的信息和考试信息
SELECT student.*, score.c_name, score.grade
FROM student
LEFT JOIN score ON student.id = score.stu_id;
1
2
3
10.计算每个学生的总成绩
SELECT stu_id, SUM(grade) AS 总成绩
FROM score
GROUP BY stu_id;
1
2
3
11.计算每个考试科目的平均成绩
SELECT c_name, AVG(grade) AS 平均分
FROM score
GROUP BY c_name;
1
2
3
12.查询计算机成绩低于95的学生信息
SELECT student.*
FROM student
JOIN score ON student.id = score.stu_id
WHERE score.c_name = '计算机' AND score.grade < 95;
1
2
3
4
13.查询同时参加计算机和英语考试的学生的信息
SELECT * FROM student
WHERE id IN (
SELECT stu_id FROM score WHERE c_name = '计算机'
AND stu_id IN (SELECT stu_id FROM score WHERE c_name = '英语')
);
1
2
3
4
5
14.将计算机考试成绩按从高到低进行排序
SELECT * FROM score
WHERE c_name = '计算机'
ORDER BY grade DESC;
1
2
3
15.从student表和score表中查询出学生的学号，然后合并查询结果
SELECT id FROM student
UNION
SELECT stu_id FROM score;
1
2
3
16.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩
SELECT student.name, student.department, score.c_name, score.grade
FROM student
JOIN score ON student.id = score.stu_id
WHERE student.name LIKE '张%' OR student.name LIKE '王%';
1
2
3
4
17.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩
SELECT student.name,
(YEAR(CURDATE()) - student.birth) AS 年龄,
student.department,
score.c_name,
score.grade
FROM student
JOIN score ON student.id = score.stu_id
