文章目录

一、分页查询1.应用场景2.案例3. 公式 二.练习三、联合查询 union四、插入语句1.语法12.null值处理3.顺序可颠倒4.列数和值的个数必须一致5.可以省略列名，默认就给你加上所有名6.插入语法2【set】7.两个插入语法的对比 五、修改语句1.修改单表的记录2.修改多表记录 六、删除语句1.方法一：delete2.方法二：truncate3.对比 七、练习八、库的管理1.库的创建2.库的修改3.库的删除 九、表的管理1.表的创建2.表的修改1. 修改列名2.修改列的类型和约束3. 添加新的列4.删除列5.修改表名 3.表的删除4.表的复制1.仅复制表的结构2.复制表的结构和全部数据3.只复制部分数据和部分结构4.仅仅复制部分结构，且不导入数据 5.练习 十、约束1.六大约束2.添加约束时机，列级约束/表级约束3.primary key和unique对比4.外键foreign key5.修改表时添加约束6.删除表时删除约束 十一、标识列/自增长列1.创建表时，设置标识列2.修改表时设置标识列/自增长列3.修改表时删除标识列

一、分页查询

1.应用场景

一页显示不全，需要分页提交sql请求

select 查询列表 from 表 join 表2 on 连接条件 where group by having order by limit 起始索引(从0开始)，要显示的数量（不是结束索引！！！）

limit 语句放在最后

执行顺序：from→join→on→where→group by → having → order by → limit

2.案例

1：查询前五条员工信息

select * from employees limit 5;

2.查询第11条到第25条

select * from employees limit 10,15;

3.查询有奖金的员工信息，并且工资较高的前10名

select * from employees where commission_pct is not null order by salary desc limit 10;

3. 公式

要显示的页数page(第几页)，每页的条目数量size

limit (page-1)*size,size;

二.练习

1.查询工资最低的员工last_name,salary

select last_name,salary from employees where salary = ( select min(salary) from employees );

2.查询平均工资最低的部门信息,和平均工资

select d.*,avg(e.salary) as 平均工资 from departments d inner join employees e on d.department_id = e.department_id group by e.department_id having avg(e.salary) = ( select min(ag) from (select avg(salary) as ag from employees group by department_id)as ag_dep );

简单做法，利用排序，从小到大排，然后limit，取第一个，的department_id

select avg(e.salary) 平均工资,d.* from employees e join departments d on e.department_id=d.department_id group by e.department_id order by 平均工资 asc limit 1;

3.查询平均工资最高的job信息

select j.*,平均工资 from jobs j join (select avg(salary) 平均工资,job_id from employees group by job_id order by 平均工资 desc limit 1) ag on ag.job_id = j.job_id;

4.查询平均工资高于公司平均工资的部门

select department_id,avg(salary) from employees group by department_id having avg(salary) >( select avg(salary) from employees);

5.查询所有manager的详细信息

select * from employees where employee_id in ( select distinct manager_id from employees);

6.各个部门中最高工资中最低的那个部门id还有其最低工资是多少

select min(salary),department_id from employees where department_id = (select department_id from employees group by department_id order by max(salary) asc limit 1);

7.查询平均工资最高的部门的manager详细信息，

select last_name,department_id,email,salary from employees where employee_id = ( select manager_id from departments d join ( select avg(salary),department_id from employees group by department_id order by avg(salary) desc limit 1) m on d.department_id = m.department_id );

8.查询每个专业的学生人数 student表，studentno学生id，majorid专业

select count(*),majorid from student group by majorid;

9.参加考试的学生中，每个学生的平均分，最高分 成绩表：result，成绩：score

select max(score),avg(score),studentno from result group by studentno;

10.查询姓张的且最低分大于60的学生学号，姓名

select s.studentname,s.studentno from student s join result r on s.studentno = r.studentno where studentname like '张%' group by s.studentno having min(score) > 60;

11.查询生日在’19880-1-1’后的学生姓名，其专业名称，生日

select s.studentname,m.majorname,s.borndate from major m join student s on s.majorid = m.majorid where borndate>str_to_date('1988-1-1','%Y-%m-%d');

老师的答案

select s.studentname,m.majorname,s.borndate from major m join student s on s.majorid = m.majorid where datediff(borndate,'1988-1-1')>0;

12.查询每个专业的男生人数和女生人数

方法一:

select count(*),sex,majorid from student group by majorid,sex;

方法2：

select majorid, (select count(*) from student where sex='男' and majorid=s.majorid) as 男, (select count(*) from student where sex='女' and majorid=s.majorid) as 女 from student s group by majorid;

13.查询专业和张翠山一样的学生的最低分，姓名

select min(score),studentname from student s join result r on s.studentno = r.studentno where majorid = ( select majorid from student where studentname='张翠山');

14.查询大于60分的学生姓名、密码、专业名称

select studentname,loginpwd,majorname from student s join major m on s.majorid = m.majorid join result r on r.studentno = s.studentno where score>60;

15.查询哪个专业没有学生，分别用左连接和右连接实现

select m.majorid,m.majorname from major m left join student s on m.majorid = s.majorid where s.studentno is null;

右连接省略

三、联合查询 union

将多条查询语句的结果合并成一个结果

案例：查询部门编号>90或邮箱中包含’a’的员工信息

原始版本

select * from employees where department_id>90 or email like '%a%';

使用union

select * from employees where department_id>90 union select * from employees where email like '%a%';

使用场景：要查询的结果来自多个表且多个表没有直接的连接关系，但要查询的信息一致时，就要用联合查询。

注意事项： 1.联合查询每个表查询的列数要一致， 2.要求多条查询语句的查询的每一列的类型和顺序要一致 比如说要从多个表查询年龄、性别、出生地，可能表1、表2的列名不大一样（age，sex，location） 但顺序要一致 3.union会自动去重，如果不要去重要用[union all] 关键字1

四、插入语句

1.语法1

insert into 表名(列名...) values (值...) insert into beautry(id,Name,sex,borndate,phone,photo,boyfriend_id) values(13,'唐艺昕','女','1990-4-23','189888888',null,2), (14,'紫霞','女'................);

2.null值处理

注意不可以为空null的列必须插入值。可以为null的列，没有值时可以填入null，甚至列名都可以不用填

insert into beauty(id,name,sex,phone) values(15,'阿珍','女','138888888'), (16,'阿亮','女'............)

3.顺序可颠倒

插入时列的顺序可以颠倒，但值也要对应

4.列数和值的个数必须一致

5.可以省略列名，默认就给你加上所有名

insert into beauty values(18,'阿华','女'.......)

6.插入语法2【set】

insert into beauty set id=19,name='刘涛',phone='999';

7.两个插入语法的对比

1.用values的插入语法，可以支持插入多行，方法2不行 2.方法1支持子查询，把只查询的结果当作values值插入

insert into beauty (id,name,phone) select id2,name2,phone2 from beauty2;

五、修改语句

1.修改单表的记录

update 表名 set 列名1=新值(字符型和日期加单引号),列名2=新值... where 筛选条件

案例1:修改beauty表中姓唐女神的电话为12345 name,phone

update beauty set phone='12345' where name like '唐%';

案例2：修改boys表中id为2的名称boyname为张飞，userCP为10

update boys set userCP=10,boyname='张飞' where id =2;

2.修改多表记录

update 表1 别名 inner/left/right/full/cross join 表2 on 连接条件 set 列名1=新值，列名2=新值... where 筛选条件

案例1：修改张无忌的女朋友的手机号为114 boyfriend_id ， boys表里的id ， phone，boyname

update boys inner join beauty on boys.id = beauty.boyfriend_id set phone ='114' where boyname = '张无忌';

案例2：修改女神的男朋友编号不在男神表中出现的男朋友编号都为2

update beauty left join boys on boys.id = beauty.boyfriend_id set boyfriend_id = 2 where boys.id is null;

六、删除语句

1.方法一：delete

delete from 表名 where 筛选条件 [一删就是整行]

单表删除

案例1：删除beauty表中手机号以9结尾的样本

delete from beauty where phone like '%9';

多表删除 案例2：删除张无忌女朋友的信息

delete beauty from beauty inner join boys on boys.id = beauty.boyfriend_id where boyname = '张无忌';

案例3：删除黄晓明的信息和她女朋友的信息

delete beauty,boys from beauty inner join boys on boys.id = beauty.boyfriend_id where boyname='黄晓明';

2.方法二：truncate

整表删除！！！！

truncate table 表名

3.对比

如果用delete删除表格后，再插入数据，自增长列的值从断点开始delete from boys;

比如说：id：1，2，3，4的样本，被删除后，再插入，id就会从5开始

而truncate删除后，再插入数据，自增长列的值从1开始truncate table boys;

truncate删除后没有返回值，delete删除表后有返回值

truncate删除不能回滚，delee删除可以回滚

七、练习

1.运行以下脚本，创建表my_employees

create table my_employees( Id int, First_name varchar(10), Last_name varchar(10), Userid varchar(10), Salary double(10,2)); create table users( id int, userid varchar(10), department_id int);

插入数据

insert into my_employees values (1,'patel','Ralph','Rpatel',895), (2,'Dancs','Betty','Bdancs',860), (3,'Biri','Ben','Bbiri',1100), (4,'Newman','Chad','Cnewman',750), (5,'Ropeburn','Audrey','Aropebur',1550);

也可以这么写,用select和union

insert into my_employees select 1,'patel','Ralph','Rpatel',895 union select 2,'Dancs','Betty','Bdancs',860 union select 3,'Biri','Ben','Bbiri',1100 union select 4,'Newman','Chad','Cnewman',750 union select 5,'Ropeburn','Audrey','Aropebur',1550; insert into users select 1,'Rpatel',10 union select 2,'Bdancs',10 union select 3,'Bbiri',20 union select 4,'Cnewman',30 union select 5,'Aropebur',40;

将3号员工的last_name改为’drelxer’

update my_employees set Last_name = 'drelxer' where Id=3;

2.将所有工资少于900的员工工资改为1000

update my_employees set Salary=1000 where Salary<900;

3.将userid为Bbiri的user表和my_employees表里的记录都删了

delete m,u from my_employees m join users u on m.Userid=u.userid where u.userid = 'Bbiri';

4.删除所有数据

delete from my_employees; delete from users;

5.清空列表

truncate table my_employees;

八、库的管理

1.库的创建

create database database_name;

容错创建

create database if not exists database_name;

2.库的修改

更改字符集 gbk,utf8…

alter database database_name character set gbk;

3.库的删除

drop database database_name; drop database if exists database_name;

九、表的管理

1.表的创建

create table table_name ( 列名 列的类型【长度/约束】, 列名 列的类型【长度/约束】, 列名 列的类型【长度/约束】, 列名 列的类型【长度/约束】, ...); create table book ( id int, bName varchar(20), price double, authorId int, publishDate datetime); create table author( id int, au_name varchar(20), nation varchar(10));

2.表的修改

1. 修改列名

把上面创建的book表里的publishDate列名改成pubDate

alter table book change column publishDate pubDate datetime; #column可以省略 alter table 表名 change column 列名 新列名 类型;

2.修改列的类型和约束

将book表里的pubDate的类型改成timestamp

alter table book modify column pubDate timestamp;

3. 添加新的列

给author表添加一个salary列，double类型

alter table author add column salary double;

设置新添加列的位置（默认添加到最后一列）

alter table 表名 add column 列名 类型 【first（添加到第一列） | after 列名x（添加到列x后面）】;

4.删除列

删除author表的salary列

alter table author drop column salary;

5.修改表名

将author表的表名改成book_author

alter table author rename to book_author;

3.表的删除

drop table 表名; drop table if exists 表名;

4.表的复制

author表

insert into author values (1,'村上春树','日本'), (2,'莫言','中国'), (3,'冯唐','日本'), (4,'金庸','中国');

1.仅复制表的结构

create table author_copy like author;

2.复制表的结构和全部数据

create table author_copy2 select * from author;

3.只复制部分数据和部分结构

create table author_copy3 select id,au_name from author where nation = '中国';

4.仅仅复制部分结构，且不导入数据

create table author_copy4 select id,au_name from author where 0; #0就代表false，不复制任何数据

5.练习

1.创建表dept1，在test库里 列名 ：id，name 类型：int(7),varchar(25)

use test; create table dept1 ( id int(7), name varchar(25) );

。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。

2.创建一个新表dept2将departments表里的department_id,department_name数据复制到新表中,注意departments表在myemployees库里

create table dept2 select department_id,department_name from myemployees.departments;

。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。

3.创建表emp5 列名：id,first_name,last_name,dept_id 类型:int,varchar(25),varchar（25），int

create table emp5 ( id int, first_name varchar(25), last_name varchar(25), dept_id int );

。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。

4.将emp5里的last_name长度增加到50

alter table emp5 modify column last_name varchar(50);

。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。

5.根据myemployees库里的表employees创建employees2,只要结构不要数据

create table employees2 like myemployees.employees;

。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。 6.删除表emp5

drop table if exists emp5;

。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。

7.将表employees2重命名为emp5

alter table employees2 rename to emp5;

。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。

8.在表emp5中添加新列test_column,类型int

alter table emp5 add column test_column int;

。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。

9.删除表emp5里的test_column列

alter table emp5 drop column test_column;

十、约束

约束：限制表中的数据，为了保证插入表中数据的准确和可靠性，一致性。

create table 表名 字段名 类型 约束

1.六大约束

not null：非空，用于保证该列不能为空，比如姓名、学号default：默认值，用于保证该字段有默认值primary key：主键，保证该字段的值据有唯一性，并且非空foreign key：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值. 在从表添加外键约束，用于引用主表中某列的值，比如学生表的学生编号，员工表的员工编号都可以添加外键约束unique：唯一，保证该字段的值据有唯一性，但是可以为空check：mysql不支持【但不报错】，比如性别只能填男/女，但还能填其他字，这是就要用到check检查。还比如年龄应该在0-130之内，用check进行限制

一个列可以添加多个约束，没有顺序之分，用空格隔开·

2.添加约束时机，列级约束/表级约束

添加约束的时机： 1.创建表时 2.修改表时（数据还没添加前）

列级约束/表级约束

create table 表名 ( 列名1 类型 列级约束， 列名2 类型 列级约束， 列名3 类型 列级约束， 表级约束 )

列级约束：语法上都支持六大约束，但foreign key，外键约束没有效果 表级约束：除了default和not null约束，其他都支持

添加列级约束

create table stuinfo( id int primary key,#主键 stuname varchar(20) not null, #非空 gender char(1) check(gender='男' or gender = '女'),#检查约束，mysql中不支持 seat int unique,#唯一约束 age int default 18, majorid int ); create table major( id int primary key, majorname varchar(20) );

添加表级约束 表级约束不支持非空和默认值约束

create table stuinfo( id int, stuname varchar(20), gender char(1), seat int, age int, majorid int, constraint pk primary key(id), #主键，起别名pk constraint uq unique(seat),#唯一键，起别名uq constraint fk_stuinfo_major foreign key(majorid) references major(id) #外键 ，起别名fk.... );

或者不起别名

create table stuinfo( id int, stuname varchar(20), gender char(1), eat int, age int, majorid int, primary key(id), unique(seat), foreign_key(majorid) references major(id) );

两种约束结合的通用写法

create table stuinfo( id int primary key, stuname varchar（20） unique, gender char(1), age int default 18, seat int unique, majorid int, constraint fk_stuinfo_majorid foreign key(majorid) references major(id) );

3.primary key和unique对比

primary key ：保证唯一性，不能为空，主键在一个表中只能有一个！ unique：保证唯一性，可以为空（但也只能有1个null），一个表里可以有多个unique

组合主键

create table stuinfo( id int, stuname varchar(20), gender char(1), seat int, age int, majorid int, primary key(id,stuname),#组合主键 );

插入id=1,stuname=‘john’；id=2,stuname=‘lili’ 不会报错 只有同时插入两个 id=1,stuname=‘john’ 才会报错。

组合唯一键也是如此unique(id,stuname)

4.外键foreign key

1.是在从表设置外键关系 2.从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无所谓 例子：stuinfo的majorid列引用了major表的id列 3.主表的关联列必须是一个key (通常是主键primary key或unique) 4.插入数据时要先插入主表数据，再插入从表数据，删除数据时先删除从表，再删主表 5.级联删除可以在先删除主表数据的同时，删除从表数据，做法是在设置外键时添加on delete cascade

alter table stuinfo add foreign key(majorid) references major(id) on delete cascade;

6.级联置空可以在先删除主表数据的同时，将从表中外键引用主表的数据，变成null

alter table stuinfo add foreign key(majorid) references major(id) on delete set null;

5.修改表时添加约束

1.添加非空约束

alter table stuinfo modify column stuname varchar(20) not null;

去除非空约束

alter table stuinfo modify column stuname varchar(20) null; #去除null

2.添加默认值约束

alter table stuinfo modify column age int default 18;

3.添加主键约束

alter table stuinfo modify column id int primary key; #或者 alter table stuinfo add primary key(id);

4.unique约束

alter table stuinfo modify column seat int unique; #或者 alter table stuinfo add unique(seat);

5.添加外键约束

alter table stuinfo add foreign key(majorid) references major(id); alter table 表1 add foreign key(要添加外键的列名) references 表2(主键列名); #也可以重命名 alter table stuinfo add constraint fk_stuinfo_major foreign key(majorid) references major(id);

6.删除表时删除约束

1.删除非空 not null约束

alter table stuinfo modify column stuname varchar(20) null;

2.删除默认值default约束

alter table stuinfo modify column age int;

3.删除主键约束

alter table stuinfo drop primary key; #因为一个表就一个主键

4.删除唯一键unique用的是index

alter table stuinfo drop index seat;

5.删除外键约束

alter table stuinfo drop foreign key majorid;

十一、标识列/自增长列

自增长列：不用手动插入值，系统提供默认的序列值

1.创建表时，设置标识列

auto_increment

create table test_table( id int primary key auto_increment), name varchar(20) );

添加数据时两种方法

insert into test_table values(null,'john');

或

insert into test_table (name) values('john');

一般来说mysql不支持设置自增字的起始值，但可以取巧

insert into test_table values(10,'lily'); #这样就会从10开始自动增长

设置自增长的步长（注意设置完后，同一个库下的所有表都会受影响）

set auto_increment_increment = 3;

特点: 1.标识列（自增长列）不一定要跟主键primary key搭配，但要求是一个key，比如unique 2.一个表只能有一个标识列（自增长列） 3.标识列的类型只能是数值型，一般是int

2.修改表时设置标识列/自增长列

alter table test_table modify column id int primary key auto_increment;

3.修改表时删除标识列

alter table test_table modify column id int primary key;