MySQL数据库---Day 02

一、子查询

        子查询：（嵌套查询）一个select 语句中包含另一个 select 语句

-- 查询所有比张三年龄大的其他学生
select * from student where age > (select age from student where name = '张三')
-- 查询张三的年龄
select age from student where name = '张三'
-- 查询所有比11大的其他的学生
select * from student where age > 11

二、where常用的表达式

where 后常用的运算符
> < <= >= = <>	大于、小于、大于 ( 小于 ) 等于、不等于
BETWEEN ...AND...	显示在某一区间的值 ( 含头含尾 )
LIKE	模糊查询，与通配符搭配使用， % ， _
IS NULL	判断是否为空
and	多个条件同时成立
IN	显示在 in 列表中的值，例： in(100,200)
or	多个条件任一成立
not	不成立，例： where not(salary>100);

-- 查询比指定员工出生日期大的其他员工的姓名
select name from student where birth > (select birth from student where id = 2);
-- 查询所有电话不等于120的员工
select * from student where tel != 120;
-- 查询所有年龄大于等于18小于等于22的员工
select * from student where age between 18 and 22;
-- 查询姓名为张三并且性别为男并且年龄大于18的员工
select * from student where name = '张三' and sex = '男' and age > 18; 
-- 查询所有姓名为张三或者zhangsan的员工
select * from student where name = '张三' or name = 'zhangsan';
-- 查询所有出生日期不为空的员工
select * from student where birth is not null;
-- 查询所有年龄为10，年龄为11，年龄为20的员工
select * from student where age in (10,11,20);
-- 查询所有姓名包含a的员工
select * from student where name like '%a%';
-- 查询出所有姓张的员工
select * from student where name like '张%';
-- 查询出所有结尾为a的员工
select * from student where name like '%a';

三、常用的约束

约束	描述
非空约束	not null
唯一约束（可为空，并且重复）	unique
主键（非空且唯一）	primary key
外键	foreign key

        主键：确定一条记录的唯一标识，比如人的身份证号

        外键：用于与另外一张表进行关联，确定另一张表数据的字段，保持数据的一致性

-- 创建部门表，字段为部门编号，部门名称，部门人数，给部门编号添加主键，
-- 给部门名称添加唯一约束，给部门人数添加非空约束
create table bumen(
b_num int(11) primary key,
b_name varchar(20) unique,
b_count int(11) not null
);
-- 验证约束
insert into bumen values(null,null,null);
insert into bumen values(1,'人事部',null);
insert into bumen values(1,'人事部',12);
insert into bumen values(1,'人事部',11);
insert into bumen values(2,'人事部',10);
insert into bumen values(2,'策划部',5);
insert into bumen values(3,'宣传部',9);
insert into bumen values(4,'后勤保障部',5);
-- 给员工表添加主键约束
alter table emp add constraint pk_emp primary key emp(id);
-- 给员工表添加字段部门编号
alter table emp add column b_num int(11);
-- 给员工表和部门添加外键约束
alter table emp add constraint fk_emp_bumen foreign key emp(b_num) references bumen(b_num);
-- 验证外键约束
insert into emp(id,emp_name,b_num) values(7,'发生的',5);
-- 删除外键
alter table emp drop foreign key fk_emp_bumen;

四、排序查询

-- order by
select * from 表名 order by 字段名 DESC|ASC(默认)
eg:
select * from student;
-- 根据学生年龄进行降序排序
select * from student order by age DESC
-- 根据张三的年龄进行升序排序
select * from student where name = 'zhangsan' order by age ASC
-- 根据张三的年龄进行升序排序，只取一条
-- limit
select * from student where name = 'zhangsan' order by age asc limit 1
-- 查询出出生日期大于2023年三月的前两个学生，并且年龄要降序排序
-- 查询出出生日期大于2023年三月的所有学生
select * from student where birth > '2023-03-01'
-- 降序排序
select * from student where birth > '2023-03-01' order by age DESC;
-- 取前两个学生
select * from student where birth > '2023-03-01' order by age DESC limit 0,2
-- 查询出出生日期大于等于2023年4月1号，小于等于2023年4月30日的学生的第三到第五名学生，并且根据
id升序排序
-- 查询出出生日期大于等于2023年4月1号，小于等于2023年4月30日的学生
select * from student where birth between '2023-04-01' and '2023-04-30'
-- 并且根据id升序排序
select * from student where birth between '2023-04-01' and '2023-04-30' order by
id ASC;
-- 第三到第五名学生
select * from student where birth between '2023-04-01' and '2023-04-30' order by
id ASC limit 2,3
/*
limit 数字1,数字2
数字1代表索引，从0开始，数字2代表取几个
*/

五、自动增长

-- auto_increment
insert into student values (null,'chensen',21,'man','2023-05-01','120',1)
select * from student
-- 创建部门表
create table dept(
dept_no int(11) primary key auto_increment,
dept_name varchar(20) unique,
dept_num int(11)
)
insert into dept values(null,'行政部',10)
select * from dept
-- 创建课程表,要求自增的起始值从1000开始
create table class(
id int(11) primary key auto_increment,
class_name varchar(20)
)auto_increment=1000
insert into class values(null,'java')
selecauto_incrementt * from class

六、delete&truncate

delete from 表名
truncate table 表名

        在有自增的前提下，如果用delete清空表中数据，再插入时，主键从被删除的最大的值开始累加如果是用truncate清空表中数据，再插入时主键从起始值开始

七、分组查询

-- group by
-- 查询出每个老师名下带多少个学生
select count(*),tno from student group by tno
-- 查询出名下带学生的人数大于5的所有教师编号
select tno,count(*) from student group by tno having count(*) > 5
-- 查询出名下带学生人数大于等于2，并且学生姓zhang的教师编号
select tno,count(*) from student where name like 'zhang%' group by tno having
count(*) >= 2
-- 查询出名下带学生人数大于等于3，并且学生姓名包含a，并且出生日期在2022-2023年之间的前三个教师
编号，并且根据tno进行降序排序
select tno,count(*) from student where name like '%a%' and birth between '2022-
01-01' and '2023-12-31' group by tno having count(*) >= 3 order by tno desc
limit 3

八、常用的时间函数

时间函数	描述
curdate()	当前日期
curtime()	当前时间
now()	当前日期和时间
year()	返回指定时间的年份
week()	返回指定时间是一年中的第几周
hour()	返回指定时间的小时
minute()	返回指定时间的分钟
date_add( 时间，间隔 )	返回距离间隔时间的时间
date_format( 时间，格式 )	格式化时间

-- 查询出当前的日期
select curdate();
-- 查询出当前的时间
select curtime();
-- 查询出当前的日期和时间
select now();
-- 查询当前日期是一年中的第几周
select week(curdate());
-- 查询指定日期2023-06-11是一年中的第几周
select week('2023-06-11');
-- 返回当前的年份
select year(curdate());
-- 返回指定日期2023-06-11的年份
select year('2023-06-11');
-- 返回当前时间的小时
select hour(curtime());
-- 返回当前时间的分钟
select minute(curtime());
-- 查询出距离当前时间一个月后的时间
select date_add(curdate(),interval 1 month);
-- 查询距离2000-01-01一天前的时间
select date_add('2000-01-01',interval -1 day);
-- 查询距离当前时间7天前的时间
select date_add(curdate(),interval -1 week);
-- 将当前时间格式化为2023/4/14 16:51:00
select date_format('2023-4-14 16:50:00','%Y/%m/%d %H:%i:%s');
-- 查询当前时间的英文的月份名
select monthname(curdate());