-- drop table t_stu;
-- unique 唯一约束的列允许为null
-- 如果在之后的操作中。需要某列必须为key，才能做一些操作的情况下。也可以使用唯一约束代替主键约束
-- create table t_stu(
-- studId int,
-- tel varchar(11) unique,
-- sex varchar(1),
-- addr varchar(100),
-- idCard varchar(18),
-- createTime datetime default now(),
-- unique(studId,createTime)
-- );
-- drop table t_stu;
-- create table t_stu(
-- studId int auto_increment unique,
-- tel varchar(11) unique,
-- sex varchar(1),
-- addr varchar(100),
-- idCard varchar(18),
-- createTime datetime default now()
-- );
-- insert into t_stu(studId,tel,sex,addr,idCard) values(1,"13333333333",'男','北京',"133233190001012233");
-- insert into t_stu(studId,tel,sex,addr,idCard) values(3,null,'男','石家庄',"133233190001012234");
-- alter table t_stu add constraint `uk_idcard` unique(idCard);
-- 查看建表语句
-- show create table 表名
-- drop table t_stu;
-- create table t_stu(
-- studId int auto_increment unique,
-- tel varchar(11) check(length(tel) = 11),
-- sex varchar(1),
-- addr varchar(100),
-- idCard varchar(18),
-- createTime datetime default now(),
-- age int check(age > 15),
-- constraint `ck_idCard` check(length(idCard) = 18 or length(idCard)= 15)
-- );
-- insert into t_stu(studId,tel,sex,addr,idCard) values(1,"13333333333",'男','北京',"133233190001012233");
-- alter table t_stu add constraint `ck_sex` check(sex = '男' or sex = '女');
-- insert into t_stu(studId,tel,sex,addr,idCard) values(2,"13333333333",'女','北京',"133233190001012233");
-- insert into t_stu(studId,tel,sex,addr,idCard) values(2,"13333333333",'女','北京',"133233190001012233");
-- insert into t_stu(studId,tel,sex,addr,idCard) values(3,"13333333333",'女','北京',"133233190001012");
--
-- insert into t_stu(studId,tel,sex,addr,idCard,age) values(3,"13333333333",'女','北京',"133233190001012",16);
-- 学生表
create table t_student(
id int auto_increment primary key COMMENT "id",
studId varchar(30) unique comment "学号",
studName varchar(30) comment "学生姓名",
studSex varchar(2),
tel varchar(11),
createTime datetime default now()
);
create table t_course(
courseId int auto_increment primary key,
courseName varchar(30),
createTime datetime default now()
);
-- 学生选课表
drop table t_curricula;
/* 被参考的列必须有主键或者唯一约束
create table tableName(
studId int,
[constraint `约束名称`] foreign key(studId) references 主表名(列名)
)
*/
-- drop table t_curricula;
-- create table t_curricula(
-- id int auto_increment primary key,
-- studId int,
-- courseId int,
-- constraint `fk_studId_t_student_id` foreign key(studId) references t_student(id),
-- FOREIGN key(courseId) references t_course(courseId)
-- );
-- alter table t_curricula add constraint `fk_courseId_t_course_courseId` foreign key(courseId) references t_course(courseId)
-- 删除主表数据。 先删除从表，再删除主表
delete from t_curricula where courseId = 8;
delete from t_course where courseId = 8
-- 根据同一个条件，同时删除多张表数据。
delete t_curricula,t_course from t_curricula,t_course where t_course.courseId = 1;
-- update t_course set courseId = 8 where courseId = 3;
-- update t_curricula set courseId = 8 where courseId = 3;
-- update t_course,t_curricula set t_course.courseId = 8,t_curricula.courseId = 8 where t_course.courseId = 3
drop table t_curricula;
create table t_curricula(
id int auto_increment primary key,
studId int,
courseId int,
constraint `fk_studId_t_student_id` foreign key(studId) references t_student(id),
FOREIGN key(courseId) references t_course(courseId) on update cascade on delete set null
);
update t_course set courseId = 8 where courseId = 3;
delete from t_course where courseId = 8
create table t_del_constraint(
id int auto_increment primary key,
`name` varchar(30) unique,
idcard varchar(18) not null,
addr varchar(30),
createtime datetime default now(),
constraint `uk_idcard` unique(idcard),
check(length(idcard) = 18),
FOREIGN key(addr) references t_stu(addr)
)
CREATE TABLE `t_del_constraint` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(30) DEFAULT NULL,
`idcard` varchar(18) NOT NULL,
`addr` varchar(30) DEFAULT NULL,
`createtime` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_idcard` (`idcard`),
UNIQUE KEY `name` (`name`),
KEY `addr` (`addr`),
CONSTRAINT `t_del_constraint_ibfk_1` FOREIGN KEY (`addr`) REFERENCES `t_stu` (`addr`),
CONSTRAINT `t_del_constraint_chk_1` CHECK ((length(`idcard`) = 18))
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
-- 删除外键
alter table t_del_constraint drop foreign key `t_del_constraint_ibfk_1`
-- 删除check
alter table t_del_constraint drop check `t_del_constraint_chk_1`
-- 删除唯一约束
alter table t_del_constraint drop index `uk_idcard`;
alter table t_del_constraint drop index `name`
-- 删除主键
alter table t_del_constraint drop primary key
-- 非空约束和默认值约束都可以使用修改列的数据类型的方式进行删除
alter table t_del_constraint modify id int;
alter table t_del_constraint modify idcard varchar(18);
alter table t_del_constraint modify createTime datetime;
-- 数据库备份
-- 备份结构 mysqldump -u -p -d dbName > 路径/文件名.sql
-- 备份数据 mysqldump -u -p -t dbName > 路径/文件名.sql
-- 备份数据和结构 mysqldump -u -p dbName > 路径/文件名.sql
-- 执行外部的sql命令(txt) 进入mysql之后 通过source命令 source 文件名
-- 查询结果和表中数据没有联系，只是以表的形式进行了展示
-- 查询部门信息
select * from dept;
-- 查询语句
-- select *(列名) from 表名 [where 查询条件][分页][排序][分组][聚合]
-- * 是所有列
select * from emp;
-- 查询员工姓名，员工工资和奖金，每行数据的唯一标识
select empno,ename,sal,comm from emp;
-- 从dept中检索部门编号 部门名称
select deptno,dname from dept;
-- 排序默认是根据主键升序排序
select * from emp;
-- 查询员工姓名
select ename from emp;
-- order by 子句 desc降序 asc升序
-- 查询员工姓名，员工工资根据员工工资升序排序
select ename,sal from emp order by sal desc;
-- 查询员工信息根据入职日期进行降序排序
select * from emp order by hiredate desc;
-- 查询员工姓名，根据员工姓名升序排序
select ename from emp order by ename;
-- 查询员工姓名，员工工资+奖金 根据员工资和奖金的和进行降序排序
-- ifnull(列名,值)
select ename,sal+ifnull(comm,0) from emp order by sal+ifnull(comm,0) desc;
-- 排序列可以为多列,如果根据单列排序，那么如果排序列两个或多行数据相同，那么相同的行则以默认方式排序
-- 查询员工姓名和员工工资并以员工工资降序,员工姓名升序排序
select ename,sal from emp order by sal desc,ename;
-- 查询员工姓名，员工岗位，员工工资，奖金（如果奖金为null，则设置'没奖金'）
select ename,job,sal,ifnull(comm,0) from emp;
-- 查询员工姓名，岗位，工资，奖金（如果奖金不为null，为有奖金，否则为无奖金）
select ename,job,sal,
case
when comm is not null then comm
else "没奖金"
end COMM
from emp;
-- 查询员工姓名，员工岗位，入职时间，如果入职日期在6月份之前，则为上半年入职，否则为下半年
select ename,job,
case
when month(hiredate) <= 6 then "上半年"
else "下半年"
end HIREDATE
from emp;
-- 待支付 待发货 待收货 待评价 已完成 0 1 2 3 4
-- 微信支付 支付宝支付 银联支付 惠支付 1 2 3 4
-- limit 子句 查询指定条数 limit的位置从0开始
-- 查询员工编号，员工姓名，员工岗位，查询前5条
select empno,ename,job from emp limit 5
-- limit num1,num2 开始位置(页码-1) * 每页显示条数，显示条数
select empno,ename,job from emp limit 0,5;
select empno,ename,job from emp limit 5,5;
select empno,ename,job from emp limit 10,5;
-- limit子句 在整个查询语句的最后
-- 查询员工姓名，工资，部门编号 根据工资降序排序查询前5条
select ename,sal,deptno
from emp
order by sal desc
limit 5,5
-- where 子句 待条件的查询
-- 查询10号部门的员工信息....
select * from emp where 1 != 1;
select * from emp where deptno = 10;
-- 在emp表中查询工资高于3000的员工的员工编号、员工姓名和工资信息：
select empno,ename,sal from emp where sal > 3000;
-- 总工资高于3000的员工编号，员工姓名和总工资信息
select empno,ename,sal + ifnull(comm,0) from emp
where sal + ifnull(comm,0) > 2000
-- 在emp表中查询工资高于1600，并且部门是20部门的员工的部门编号、员工编号和员工姓名信息：
select deptno,empno,ename from emp where sal > 1600 and deptno = 20;
-- 在emp表中查询工资高于1600低于5000，并且部门是20部门的员工的部门编号、员工编号和员工姓名信息：
select deptno,empno,ename,sal from emp where sal > 1600 and sal < 3000 and deptno = 20
-- 在emp表中查询10或20部门的员工的部门编号、员工编号和员工姓名信息，并按部门编号升序排列：
select deptno,empno,ename from emp
where deptno = 20 or deptno = 10
order by deptno
limit 2
-- 范围测试(between and) 查询员工编号，员工姓名，员工工资 要求工资大于等于1600 小于等于3000
select empno,ename,sal from emp where sal >= 1600 and sal <= 3000;
select empno,ename,sal from emp where sal between 1600 and 3000;
-- 组成员测试 in 查询10号或20号或者30号部门的员工信息
select * from emp where deptno = 10 or deptno = 20 or deptno = 30
order by deptno desc;
select * from emp where deptno in(10,20,30)
select * from emp where deptno not in(10,20,30);
-- like _ %
-- 查询员工信息，名字当中包含A的
select * from emp where ename like 'A%';
-- 查询员工信息，名字中包含A但是A不是第一个
select * from emp where ename like '%A%' and ename not like 'A%';
-- 查询没有奖金的员工信息
select * from emp where comm is null;
-- 查询有奖金的
select * from emp where comm is not null and comm != 0;
-- 查询员工姓名，员工岗位，员工的总工资
select ename,job,sal + ifnull(comm,0) as sal
from emp
select ename,job,sal,
case
when comm is null then '没奖金'
else comm
end comm
from emp
select ename "员工姓名",sal "员工工资" from emp;
-- 聚合函数
-- 求和 求平均数 最小 最大 条数
-- 员工的工资总和
select sum(sal) total from emp;
-- 员工的总工资（奖金和工资）
select sum(sal + ifnull(comm,0)) from emp
-- 查询10号部门的员工的总工资
select sum(sal + ifnull(comm,0)) from emp where deptno = 10
select avg(sal) from emp where deptno = 10;
-- 查询10号部门的最大工资
select max(sal) from emp where deptno = 10;
select min(sal) from emp where deptno = 10;
-- 查询的员工数量
select count(*) from emp;
-- 查询有奖金的人数 count数量 不包含指定列为空的行
select count(comm) from emp where comm != 0;
-- 分组查询 group by 子句
-- 分组查询时 select 查询的列只能包含三种
-- 1、聚合函数 2、分组依据 3、常量
-- 各个 查询各个部门的最大工资
select max(sal),deptno from emp group by deptno
select deptno,min(sal),max(sal),sum(sal) from emp group by deptno
-- 查询各个部门的最大工资及这个人的员工的姓名 -- 不提倡
select max(sal),ename from emp group by deptno;
-- select ename,sal from emp where sal in (select max(sal) from emp group by deptno)
-- group by 可以存在多个分组一句
-- 查询各个部门中，各个岗位的最高工资
select max(sal),deptno,job from emp group by deptno,job
-- 各个年份入职的人数
select count(*),year(hiredate),"该死的查询" from emp group by year(hiredate)
-- having 条件检索 和where很像，但是执行时机不同
-- 查询员工姓名，员工的总工资，总工资高于2650
-- select ename,sal + ifnull(comm,0) total from emp having total >= 2650;
-- 查询10号部门的员工信息
select * from emp where deptno = 10 having sal > 1300;
-- having 常用于分组查询后的筛选
-- 各个部门的最大工资，部门编号。要求最大工资大于3000
select max(sal),deptno from emp where sal > 3000 group by deptno
select max(sal) max,deptno from emp group by deptno having max > 3000;
-- distinct 去重
-- 查询有多少个工种
select count(distinct job) from emp
-- 查询各个部门的工资总和(奖金不为空),部门编号,总和大于9000 并且根据总工资进行倒序排序，查看前两条例
select sum(sal + comm) total,deptno
from emp
where comm is not null
group by deptno
order by total desc
limit 2;
