数据库学习笔记02 SQL 增删改查

2，操作命令

SQL增删改

通过远程客户端Navicat链接到数据库，完成增、删、改操作

-- 创建数据库,指定默认字符集为utf-8
create database school default charset utf8;
-- 切换到数据库
use school;
-- 删除数据库
drop database school ;
-- 或
drop database if exists school ;
-- 创建学生表,()是指定表的列
create table tb_students
(
    stuid int not null comment '学号',
    stuname varchar(20) not null comment '姓名',
    stusex bit default 1 comment '性别',
    stubirth date comment '生日',
    primary key (stuid)
);
-- 修改表,添加列
alter table tb_students add column stuaddr varchar(255);
alter table tb_students modify column stuaddr varchar(200);
-- 修改表,删除列
alter table tb_students drop column stuaddr;
-- 向学生表插入数据
insert into tb_students values (1001,'张无忌', 1, '1988-09-18', '北京');
insert into tb_students values (1002,'赵敏', 0, '1988-09-18', '北京');
-- 如果不完全写入所有列，需要指定列，否则报错
insert into tb_students (stuid,stuname) values (1003,'小昭');
-- 一次插入多条数据，每个元祖是一条数据
insert into tb_students (stuid,stuname,stusex) values (1004,'宋远桥',1),(1005,'周芷若',0),(1006,'小龙女',0);
-- 删除学号为1003学生
delete from tb_students where stuid = 1003;
-- 删除所有女学生
-- delete from tb_students where stusex = 0;
-- 更新操作
update tb_students set stuaddr = '北京通州运河湾',stubirth = '1985-08-18' where stuid = 1001 or stuid = 1005;
update tb_students set stuaddr = '天津海河教育园' where stuid in (1004,1006);

-- 创建学院表
create table tb_college
(
colid int auto_increment comment '编号',
colname varchar(31) not null comment '名称',
website varchar(1024) comment '网站',
primary key (colid)
);

insert into tb_college (colname) value ('经济管理学院'),('外国语学院'),('计算机学院');

alter table tb_students add column colid int;
update tb_students set colid = 1 where stuid between 1001 and 1004;
update tb_students set colid = 2 where stuid in (1005,1006);
-- '
实体：学生、学院
关系：属于
重数：多对一
-- '
-- 添加学生表的外键约束 （参照完整性）
alter table tb_students add constraint fk_student_colid foreign key (colid) references tb_college (colid);
-- colid 值不在tb_college表中，即无效则更新失败
update tb_students set colid = 5 where stuid in (1005,1006);
-- Cannot add or update a child row: a foreign key constraint fails (`school`.`tb_students`, CONSTRAINT `fk_student_colid` FOREIGN KEY (`colid`) REFERENCES `tb_college` (`colid`))

-- 创建教师表
create table tb_teacher
(
    teaid int not null comment '学号',
    teaname varchar(20) not null comment '姓名',
    teasex bit default 1 comment '性别',
    teatitle varchar(20) not null comment '职称',
    primary key (teaid)
);

-- 创建课程表
create table tb_course
(
	couid int not null comment '编号',
    couname varchar(50) not null comment '名称',
    coucredit int not null comment '学分',
    teaid int not null comment '授课教师',
    primary key (couid),
    foreign key (teaid) references tb_teacher (teaid) 
);

-- 创建选课记录表
create table tb_score
(
	scid int auto_increment comment '选课记录编号',
    stuid int not null comment '选课学生',
    couid int not null comment '所选课程',
    scdate datetime comment '选课时间日期',
    scmark decimal(4,1) comment '考试成绩',
    primary key (scid),
    foreign key (stuid) references tb_students (stuid),
    foreign key (couid) references tb_course (couid)    
);

-- 添加唯一性约束
alter table tb_score add constraint uni_score_stuid_couid UNIQUE (stuid,couid);

-- 向每张表格插入相应内容
insert into tb_teacher values (2001, '张三丰',1,'高级教授'),(2002, '阳顶天',1,'高级教授'),(2003, '黄药师',1,'教授');
insert into tb_course values 
(501, '统计学',2,2001),
(502, '工程经济',3,2002),
(503, '谈判技巧',1,2003),
(504, '运筹学',2,2003),
(505, '招投标',1,2001),
(506, '大数据',3,2002),
(507, '马哲',1,2003),
(508, '智慧物流',2,2002),
(509, '智能交通',1,2003)
;

insert into tb_score (stuid,couid,scdate,scmark) values 
(1001,501,'2022-09-18',98.5),
(1002,501,'2022-09-18',92.5),
(1003,501,'2022-09-18',94.5),
(1004,501,'2022-09-18',93.5),
(1005,501,'2022-09-18',88.5),
(1006,501,'2022-09-18',98);

SQL查询操作

-- 查询所有学生信息
select * from tb_students;
-- 查询所有课程名称及学分（投影和别名）
select couname,coucredit from tb_course;
select couname as '课程名称',coucredit as '课程学分' from tb_course;
-- 查学生表里所有学生的姓名和性别，并且正常展示
select stuname as '姓名' , case stusex when 1 then '男' else '女' end as '性别' from tb_students; 
-- 查询所有女学生的姓名和出生日期(筛选)
select stuname, stubirth from tb_students where stusex=0;
-- 查询所有80后的学生姓名，性别和出生日期(筛选)
select stuname, stusex,stubirth from tb_students where stubirth >= '1980-01-01' and stubirth <= '1989-12-31';
select stuname, stusex,stubirth from tb_students where stubirth between '1980-1-1' and '1989-12-31';
-- 查所有姓张的学生的姓名和性别（模糊）
select stuname, stusex from tb_students where stuname like '张%';
-- 查所有姓杨且是2个字名字的学生（模糊）
select stuname, stusex from tb_students where stuname like '杨_';
-- 查所有姓杨且是3个字名字的学生（模糊）
select stuname, stusex from tb_students where stuname like '杨__';
-- 查所有姓名中包含不和嫣的学生（模糊）
select stuname, stusex from tb_students where stuname like '%不%' or stuname like '%嫣';
-- 查询没有录入家庭地址的学生姓名（空值）
select stuname, stuaddr from tb_students where stuaddr is null;

-- 查询录入家庭地址的学生姓名（空值）
select stuname, stuaddr from tb_students where stuaddr is not null;

-- 查询选课日期 （去重）
select distinct scdate from tb_score;

-- 查询学生家庭住址 （去重）
select distinct stuaddr from tb_students;

-- 查询男学生，按年龄从小到大排序 （排序）asc -- ascending 升序 desc -- descending 降序
select stuname,stubirth from tb_students where stusex = 1 order by stubirth desc;
select stuname,year(now()) - year(stubirth) as 年龄 from tb_students where stusex = 1 order by stubirth asc;
select stuname,year(now()) - year(stubirth) as 年龄 from tb_students where stusex = 1 order by 年龄 asc;
-- 常用的聚合函数： max / min / count / avg / sum， 不考虑NULL
-- 查询年龄最大的学生 （聚合函数）
select min(stubirth) from tb_students;

-- 查询年龄最小的学生 （聚合函数）
select max(stubirth) from tb_students;

-- 查询男学生和女学生的人数 （分组和聚合函数）
select stusex,count(stuid) from tb_students group by stusex;
select stusex,min(stubirth) from tb_students group by stusex;

-- 查询课程编号为501的课程平均成绩（筛选和聚合函数）
select avg(scmark) from tb_score where couid = 501;

-- 查询每个学生的学号和平均成绩（分组和聚合函数）
select stuid as 学号, avg(scmark) as 平均分 from tb_score group by stuid; 

-- 查询年龄最大学生的姓名 （子查询/嵌套查询）
select stuname,stubirth from tb_students where stubirth = (select min(stubirth) from tb_students);

-- 查询选了2门以上课程的学生姓名（子查询/分组条件/集合运算）
select stuname from tb_students where stuid in (
select stuid from tb_score group by stuid having count(stuid)>2
);
-- 连接查询 --> 内连接
-- 查询学生姓名、课程名称及考试成绩（连接查询）
-- 笛卡尔积
select stuname,couname, scmark from tb_students,tb_course,tb_score; 

-- 消除笛卡尔积
select stuname,couname, scmark from tb_students t1,tb_course t2,tb_score t3 where t1.stuid=t3.stuid and t2.couid=t3.couid;
-- limit 3 offset 5 分页查询
select stuname,couname, scmark from tb_students t1 inner join tb_score t3 on t1.stuid= t3.stuid  
inner join tb_course t2 on t2.couid=t3.couid order by scmark desc limit 3 offset 5;

-- 查询选课学生的姓名和平均成绩（子查询和连接查询）
select stuname,avgmark from tb_students t1,
(select stuid,avg(scmark) as avgmark from tb_score group by stuid) t2 where t1.stuid=t2.stuid;

-- 内连接 inner join
select stuname,avgmark from tb_students t1 inner join
(select stuid,avg(scmark) as avgmark from tb_score group by stuid) t2 on t1.stuid=t2.stuid;

-- 外连接 outer join ：左外连接 left outer join、右外连接right outer join、全外连接full outer join
-- 查询每个学生的姓名和选课数量（左外连接和子查询）
select stuname,ifnull(total,0) from tb_students t1 left outer join ( select stuid, count(stuid) as total from tb_score group by stuid) t2 on t1.stuid = t2.stuid ;

-- MySQL 单表： 65535 TB, 
-- 单列：4 GB - LONGBLOB --> Binary large object / LOGNTEXT

写SQL 时的注意事项

-- 1，给数据库命名时尽量使用全小写
-- 2,作为筛选条件的字符串是否区分大小写看设置的校对规则
-- 3,数据库中的对象通常会用前缀加以区分
-- table / view / index / function / procedure / trigger

索引视图及用户权限

-- 准备表
use company;
create table tb_emp
(
    eno int not null comment '员工编号',
    ename varchar(20) not null comment '员工姓名',
    job varchar(20) not null comment '员工职位',
    sex bit default 1 comment '性别',
    ebirth date comment '生日',
		mgr int not null comment '汇报经理编号',
		edpt int not null comment '所在部门编号',
    primary key (eno)
);

alter table tb_emp change column edpt dno int;
alter table tb_emp modify column dno int not null;
create table tb_dpt
(
    dno int not null comment '部门编号',
    dname varchar(20) not null comment '部门名称',
    dcom varchar(20) not null comment '归属公司',
    primary key (dno)
);

insert into tb_dpt values 
(101,'人事部', 'DXC'),
(102,'行政部', 'DXC'),
(103,'企划部', 'DXC'),
(104,'工程部', 'DXC'),
(105,'售后服务部', 'DXC'),
(106,'研发部', 'DXC')
;

insert into tb_emp values 
(0001,'张三丰', '人事主管',1,'1980-01-01',0001,101),
(0002,'张无忌', '首席设计师',1,'1988-02-15',0001,103),
(0003,'杨逍', '营销总监',1,'1980-1-1',0001,103),
(0004,'赵敏', '美术指导',1,'1989-01-24',0001,104),
(0005,'王宝宝', '军事顾问',1,'1980-1-1',0001,104),
(0006,'周芷若', '前台',1,'1988-1-1',0001,102)
;

-- explain 生成执行计划
explain select eno,ename from tb_emp where eno=0002;
explain select eno,ename from tb_emp where eno<>0002;
explain select eno,ename from tb_emp where ename = '张三丰';
explain select eno,ename from tb_emp where ename like '张%';

-- 索引 index
-- 索引可以加速查询，所以应该在用于查询筛选条件的列上建立索引
-- 索引会占用储存空间，而且会让增删改变得更慢，因此不能滥用索引
create index idx_emp_ename on tb_emp(ename);
drop index idx_emp_ename on tb_emp;

-- 视图：查询快照
-- 通过视图可以将用户的访问权限限制到某些指定的列上
create view vw_emp_dpt as 
select eno,ename,dname from tb_emp t1 inner join tb_dpt t2 on t1.dno = t2.dno;
select * from vw_emp_dpt;
drop view vw_emp_dpt;

-- 重新定义定界符为$$
delimiter $$
-- 创建存储过程
create procedure sp_dpt_avg_sal(dptno int, out avgsal float)
begin 
	select avg(sal) into avgsal from tb_emp where dno=dptno;
end$$
-- 将定界符还原回去
delimiter ;
-- 调用存储过程
call sp_dpt_avg_sal(20,@a);
-- 通过输出取出部门的平均工资
select @a;
-- 删除存储过程
drop procedure sp_dpt_avg_sal;

-- DCL: 授予权限（grant to） 和 召回权限（revoke from）
-- localhost 只能本机，% 通配符=任意主机， 也可以写固定IP
create user 'hellokitty'@'%' identified by '1234567';
-- 授权
grant all privileges on *.* to 'hellokitty'@'%';
-- 收回权限
revoke delete,insert,update on *.* from 'hellokitty'@'%';
-- 删除用户
drop user 'hellokitty'@'%';

-- 事务 transaction - 把多个增删改查的操作做成不可分割的原子操作
-- 要么全做，要么全不做
-- start transaction
-- 开启事务环境
begin ;
-- 操作
delete from tb_emp;
-- 提交，事务中所有操作全部生效
commit;
-- 回滚，事务中所有操作全部撤销
rollback;