MySQL2【个人学习笔记】

一、分页查询

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.六大约束

1. not null：非空，用于保证该列不能为空，比如姓名、学号
2. default：默认值，用于保证该字段有默认值
3. primary key：主键，保证该字段的值据有唯一性，并且非空
4. foreign key：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值.
   在从表添加外键约束，用于引用主表中某列的值，比如学生表的学生编号，员工表的员工编号都可以添加外键约束
5. unique：唯一，保证该字段的值据有唯一性，但是可以为空
6. 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;