DDL-表操作，数据类型有很多，主要分为三类，数值类型、字符串类型、日期时间类型
什么是精度和标度
什么是精度和标度
123.45 精度是5；标度是小数点后几位，标度是2
二进制文件是视频音频和安装包等
char(10) 性能好
varchar(10) 性能差 性别 gender char(1) 用户名 username varchar(50)
删除表 drop table if exists 表名
删除表并创建表 truncate table 表名;
show create table 表名： 查询这张表的建表语句。
编写顺序
执行顺序
DCL:用来管理数据库 用户，控制数据库的访问权限
函数
1.字符串函数
2.数值函数
3.日期函数
4.流程函数
-- 更新员工号，补充前面0，总长5个数
update user set workno = lpad(workno,5,'0');
-- 连接
select concat('hello','word');
-- 全部小写
select lower('HellO');
-- 全部大写
select upper('Hello');
-- 去除尾部和头部空格
select trim(' hello word ');2. 数值函数
-- 向上取整
select ceil(1.1);
-- 向下取整
select floor(1.9);
-- 取余
select mod(3,4);
-- 0-1之间随机数
select rand();
-- 求参数四舍五入值，保留y位小数
select round(2.897,2);-- 向上取整
select ceil(1.1);
-- 向下取整
select floor(1.9);
-- 取余
select mod(3,4);
-- 0-1之间随机数
select rand();
-- 求参数四舍五入值，保留y位小数
select round(2.897,2);
-- 通过数据库函数，生成一个6位数验证码
select lpad(round(rand()*1000000,0),6,0);3.日期函数
-- 当前年份
select CURDATE();
-- 当前时间
select curtime();
-- 当前年份和时间
select now();
-- year month day 获取当前年份、月份和天
select year(now()); -- 2023
select month(now()); -- 7
select day(now()); -- 22
-- 返回当前时间往后70天
select date_add(now(),INTERVAL 70 day );
-- 计算两个日期之间天数之差
select datediff('2023-7-22','2021-7-22');
-- 查询所有员工入职天数按倒序排序
select name,datediff(now(),entrydate) as 'entrydays' from user order by entrydays desc ;4.流程函数
select if(true,'ok','error');
-- 如果不为空，返回第一个参数，如果为空返回第二个参数
select IFNULL('ok','notok');
-- 查询员工住址信息，北京/上海返回一线城市，其他返回二线城市
select name,
(case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市'
end) as '工作地址'
from user;
-- 判断员工年龄划分等级
select name,
(case when age<20 then '童年' when age=20 then '青年' else '老年' end) as '年龄'
from user;
约束
按要求创建表
create table student(
id int primary key auto_increment comment '主键',
name varchar(10) not null unique comment '姓名',
age int check ( age > 0 and age <=120) comment '年龄',
status char(1) default 1 comment '状态',
gender char(1) comment '性别'
)comment '学生表'外键约束
insert into dept(id,name) values (1,'研发部'),
(2,'运维组'),
(3,'hr'),
(4,'经理');
select * from dept;
create table emp(
id int primary key auto_increment comment '编号',
name varchar(50) not null comment '姓名',
age int comment '年龄',
job varchar(20) comment '职位',
entrydate date comment '入职时间',
dept_id int comment '部门'
) comment '员工表';
insert into emp (id, name, age, job, entrydate, dept_id) values
(1,'张飞',23,'工程师','2012-12-30',1),
(2,'关羽',25,'经理','2000-12-12',4),
(3,'吕布',27,'hr','2023-3-3',3),
(4,'松江',30,'运维','2003-2-4',2);添加外键
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);删除外键
alter table emp drop foreign key fk_emp_dept_id;
cascade演示
添加外键的时候在后面添加
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade ;如果更新父类dept中id的值，子类表emp中对应的dept_id 的值也会改变。
如果删除父类dept中id的值，子类表emp中对应的dept_id的值的行上所有信息都会被删除。
set null 演示
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update set null on delete set null;如果删除父类表中的id值，子类表中对应的dept_id的值会变成null
修改父类表dept中id的值，子类表中对应的dept_id的值也会变成null
外键约束，NO ACTION和RESTRICT都是默认行为。
多表查询
建立学生表，建立课程表，关键建立中间表，并设置外键关系。
create table student(
id int primary key auto_increment comment '主键id',
name varchar(10) not null ,
no varchar(20) not null unique
)comment '学生表' ;
insert into student values (null,'张飞','2010'),
(null,'刘备','2011'),
(null,'关羽','2012'),
(null,'李逵','2013'),
(null,'武松','2014');
select * from student;
create table course(id int primary key auto_increment comment '主键',
name varchar(20) comment '课程'
)comment '课程表';
insert into course (name) values ('语文'),('英语'),('数学');
select * from course;
-- 建立中间表
create table student_course(
id int primary key auto_increment comment '主键',
studentid int not null comment '学生id',
courseid int not null comment '课程id',
constraint fk_courseid foreign key (courseid) references course(id),
constraint fk_studentid foreign key (studentid) references student(id)
) comment '学生课程中间表';
insert into student_course values (null,1,1),(null,1,2),(null,1,3),
(null,2,1),(null,2,2),(null,2,3),
(null,3,1),(null,3,2),(null,3,3);
在设置外键的时候要加上unique
外表查询
多表查询会出现笛卡尔积效应
select * from emp,dept ;
如何改进
select * from emp,dept where emp.dept_id = dept.id;
内连接查询
-- 查询员工姓名和员工所属部门
-- 隐式内连接
select emp.name,dept.name from emp,dept where emp.dept_id = dept.id;
-- 可以给表起别名
select e.name,d.name from emp e,dept d where e.dept_id = d.id;
-- 显示内连接
select e.name,d.name from emp e inner join dept d on e.dept_id = d.id;
-- 可以省略inner
select e.name,d.name from emp e join dept d on e.dept_id = d.id;外连接
-- 查询emp表所有数据和部门信息 左外连接
select e.*,d.name from emp e left OUTER JOIN dept d on e.dept_id = d.id;
-- 查询dept表部门和emp表所有信息
select d.name,e.* from emp e right outer join dept d on e.dept_id = d.id;自连接
-- 查询员工及领导的名字 自连接内连接
select b.name,a.name from emp a,emp b where a.id = b.managerid;
-- 查询所有员工emp及其领导的名字emp，如果员工没有领导也需要查询出来。 自连接外连接
select a.*,b.name from emp a left join emp b on a.managerid = b.id;联合查询
select * from emp where age >25
union all
select * from emp where emp.salary = 2000;
内容拼接在一起但是有重复的，去掉重复，只需要把all去掉
select * from emp where age >25
union
select * from emp where emp.salary = 2000;
子查询
标量子查询
-- 查询经理部门的员工信息
-- 分为两步，先查询经理部门id，再根据部门id查询员工信息
select * from emp where dept_id = (select id from dept where name = '经理');
-- 查询松江入职之后的人员信息
-- 先查询松江入职时间，再查询信息
select * from emp where entrydate > (select entrydate from emp where name = '松江');列子查询
-- 查询运维组和销售的所有员工信息
select * from emp where dept_id in (select id from dept where name = '运维组' or name = '销售部');
-- 查询比运维组所有员工信息都高的，员工信息 比最高的高才可以
select * from emp where emp.salary > all(select emp.salary from emp where dept_id in (select id from dept where name = '运维组'));
-- 查询比运维组任意一人工资高的信息 比最低的高就行
select * from emp where emp.salary > some (select emp.salary from emp where dept_id in (select id from dept where name = '运维组'));行子查询返回一行或多列
-- 查询和关羽相同领导和相同部门的人员信息
select * from emp where (dept_id,emp.managerid) = (select dept_id,emp.managerid from emp where name = '关羽');
表子查询返回一个表，只能用in
-- 查询和 关羽和黄忠 部门和领导相同的员工信息
select * from emp where (dept_id,emp.managerid) in (select dept_id,emp.managerid from emp where name = '关羽' or name = '黄忠');
-- 查询入职时间是2008-12-03之后的员工信息及部门信息
select * from (select * from emp where entrydate > '2008-12-03') e left join dept d on e.dept_id = d.id;多表联查 案例
显示内连接就设计到多表查询，一定要去掉重复的信息，关键在于where和on后面的信息。
涉及到两张表，emp和dept
-- 查看员工的姓名，年龄，工作和部门
select e.name,e.age,e.job,d.name from emp e,dept d where e.dept_id= d.id; -- 隐式内连接
select e.name,e.age,e.job,d.name from emp e inner join dept d on e.dept_id = d.id; -- 显示内连接
-- 查询年龄小于30岁的员工年龄，姓名，职位，部门信息
select e.name,e.age,e.job,d.name from emp e inner join dept d on e.dept_id = d.id where e.age < 30;-- 查询拥有员工的部门id和部门名称 distinct 去重
select distinct d.id,d.name from dept d,emp e where d.id = e.dept_id;外连接，查询一张完整表，及这个表和其他表的交集
-- 查询年龄大于30的员工及其归属部门，如果没有归属部门，也展示出来。 外连接
select e.*,d.name from emp e left join dept d on e.dept_id = d.id where e.age > 30;产看一张无关的表，上面记录着工资等级，根据这个无关的表查看员工，工资等级
create table salgrade(
grade int,
losal int,
hisal int
) comment '薪资表';
insert into salgrade values (1,0,3000),
(2,3001,6000),
(3,6001,7000),
(4,7001,10000);
-- 查询所有员工的工资等级
select e.*,s.grade from emp e,salgrade s where e.salary between s.losal and s.hisal;
