MySQL基础

• 创建数据库

-- 创建数据库
drop database if exists test;
create database test charset utf8 collate utf8_bin;
use test;

• 创建学院表

create table tb_college
(
co_id int not null auto_increment comment'学院编号', -- auto_increment自增长字段
co_name varchar(50) not null comment'学院名称',
co_master varchar(50) not null comment'院长',
co_web varchar(1024) default'' comment'学院网站',
primary key(co_id)
);
-- 添加唯一约束,学院名字
alter table tb_college add constraint uni_college_collname unique (co_name);

• 创建学生表

create table tb_student
(
stu_id int not null comment'学号', -- 不允许为空,comment为注释
stu_sex bit default 1 comment'性别', -- 默认值为1
stu_name varchar(10) comment'姓名',
stu_age tinyint not null comment'年龄', -- tinyint整数型,值在-128-127之间
co_id int not null comment'所属学院编号',
primary key(stu_id) -- 设置学号为主键
);
-- 添加外键约束
alter table tb_student add constraint fk_student_collid foreign key (co_id) references tb_college (co_id);

• 创建教师表

create table tb_teacher
(
tea_id int not null comment '教师工号',
tea_name varchar(20) not null comment '教师姓名',
tea_ti varchar(10) default '' comment '职称',
co_id int not null comment '所属学院编号'
);
-- 添加主键约束
alter table tb_teacher add constraint pk_teacher primary key (tea_id);
-- 添加外键约束
alter table tb_teacher add constraint fk_teacher_collid foreign key (co_id) references tb_college (co_id);

• 创建课程表

create table tb_course
(
ce_id int not null comment'课程编号',
ce_name varchar(50) not null comment'课程名称',
ce_redit tinyint not null comment'学分',
tea_id int not null comment'教师工号',
primary key(ce_id)
);
-- 添加外键约束
alter table tb_course add constraint fk_course_tid foreign key (tea_id) references tb_teacher (tea_id);

• 创建选课表

create table tb_score
(
sc_id int not null auto_increment comment '选课编号',
st_id int not null comment '学号',
se_id int not null comment '课程编号',
sc_date date comment '选课时间日期',
mark decimal(4,1) comment '考试成绩',
primary key (sc_id)
);
-- 添加外键约束
alter table tb_score add constraint fk_score_sid foreign key (st_id) references tb_student (stu_id);
alter table tb_score add constraint fk_score_cid foreign key (se_id) references tb_course (ce_id);
-- 添加唯一约束,一个学生一种课程只能选则一次
alter table tb_score add constraint uni_score_sid_cid unique (st_id, se_id);

• 约束关系
  

---

• DML数据操作语言
• insert
• 插入学院数据

insert into tb_college (co_name, co_master, co_web) values 
('计算机学院', '左冷禅', 'http://www.abc.com'),
('外国语学院', '岳不群', 'http://www.xyz.com'),
('经济管理学院', '风清扬', 'http://www.foo.com');

• 插入学生数据

insert into tb_student (stu_id, stu_sex, stu_name, stu_age, co_id) values
(1001, 1,'杨逍','30',1),
(1002, null,'任我行','28',1),
(1033, 0,'王语嫣','16',1),
(1572, 1,'岳不群','43',1),
(1378, 0,'纪嫣然','21',1),
(1954, 1,'林平之','25',1),
(2035, 0,'东方不败','27',2),
(3011, null,'林震南','42',3),
(3755, 1,'项少龙','25',3),
(3923, 0,'杨不悔','30',3);

• 插入老师数据

insert into tb_teacher (tea_id, tea_name, tea_ti, co_id) values 
(1122, '李寻欢', '教授', 1),
(1133, '西门吹雪', '教授', 1),
(1144, '叶孤城', '副教授', 1),
(2255, '谢晓峰', '副教授', 2),
(3366, '韦一笑', '讲师', 3);

• 插入课程数据

insert into tb_course (ce_id, ce_name, ce_redit, tea_id) values 
(1111, '小李飞刀', 3, 1122),
(2222, '天外飞仙', 2, 1122),
(3333, '剑神一笑', 4, 1122),
(4444, '剑十五', 2, 1133),
(5555, '草上飞', 4, 1144),
(6666, '灵犀一指', 3, 1144),
(7777, '夺命十三剑', 3, 2255),
(8888, '高等数学', 2, 3366),
(9999, '数论', 3, 3366);

• 插入选课数据

• update

update tb_student set stu_name="谢晓峰" where stu_id=1033;
update tb_student set stu_ager=67,where stu_id=1057;
update tb_student set stu_age=0, stu_sex =1 where stu_id in (1003,1004,1005,1006);

• delete

delete from tb_student where stu_id = 1033;
delete from tb_student where stu_id in (1005,1007);
truncate table tb_student; -- 截断数据(删除全表数据)

• select

-- 查询所有学生信息
select * from tb_student;
-- 查询所有课程名称及学分(投影和别名)
select ce_name as 课程,ce_redit as 学分 from tb_course;
-- 查询所有女学生的姓名和年龄(筛选)
select stu_name,stu_age from tb_student where stu_sex=0;
-- 查询所有年龄小于26的学生与年龄在21-27之间的学生姓名与年龄(筛选)
select stu_name,stu_age from tb_student where stu_age<26;
select stu_name,stu_age from tb_student where stu_age between 21 and 27;
-- 查询姓”杨“的学生姓名和性别(模糊)
select stu_name,stu_sex from tb_student where stu_name like '杨%';
-- 查询姓”杨“名字两个字的学生姓名和性别(模糊)
select stu_name,stu_sex from tb_student where stu_name like '杨_';
-- 查询姓”杨“名字三个字的学生姓名和性别(模糊)
select stu_name,stu_sex from tb_student where stu_name like '杨__';
-- 查询名字中有”不“字或“嫣”字的学生的姓名(模糊)
select stu_name from tb_student where stu_name like "%不%" or stu_name like "%嫣%";
-- 查询没有录入性别的学生姓名(空值)
select stu_name from tb_student where stu_sex is null;
-- 查询录入了性别的学生姓名(空值)
select stu_name from tb_student where stu_sex is not null or stu_sex<>"";
-- 查询学生的学院id(去重)---distinct
select distinct co_id from tb_student;
-- 查询学生的姓名按年龄从大到小排列从小到大(排序)
select stu_name,stu_age from tb_student order by stu_age asc limit 2,3; -- 跳过前两条取后三条
select stu_name,stu_age from tb_student order by stu_age desc limit 5; -- 取前5条
-- 查询学生姓名和性别，性别按男女显示
select stu_name as 姓名,if(stu_sex,"男","女")as 性别 from tb_student;
-- 查询年龄最大的学生的姓名(聚合函数)
-- max() / min() / sum()/ avg() / count() 聚合函数不考虑空值，忽略不参与计算
select stu_name,stu_age as 年龄 from tb_student where stu_age=(select max(stu_age) from tb_student);
-- 查询年龄最小的学生的姓名性别(聚合函数)
select stu_name,stu_sex from tb_student where stu_age=(select min(stu_age) from tb_student);
-- 查询每个学院学生的人数(分组和聚合函数)
select co_id as 学院编号,count(co_id) as 人数 from tb_student group by co_id; 
-- 查询课程编号为1111的课程的平均成绩(筛选和聚合函数)
select avg(mark) as 平均分 from tb_score where se_id=1111; 
-- 查询学号为1001的学生所有课程的总成绩(筛选和聚合函数)
select sum(mark) as 总分 from tb_score where st_id=1001;
-- 查询每个学生的学号和平均成绩(分组和聚合函数)
select st_id,avg(mark) as 平均分 from tb_score group by st_id;
-- 查询平均成绩大于等于90分的学生的学号和平均成绩 -- 分组之后再筛选
select st_id,avg(mark) from tb_score group by st_id having  avg(mark)>=90;
-- 查询年龄最大的学生的姓名(子查询)
select stu_name,stu_age from tb_student where stu_age=(select max(stu_age) from tb_student);
-- 查询选了两门以上的课程的学生姓名(子查询/分组条件/集合运算)
select stu_name from tb_student where stu_id in (select st_id from tb_score group by st_id having count(st_id)>2);
-- 连接查询 查询学生姓名、所选课程名称和成绩
select stu_name,mark,ce_name from tb_student,tb_score,tb_course 
where stu_id=st_id and ce_id=se_id and mark is not null;

select stu_name,ce_name,mark from tb_student
inner join tb_score on stu_id=st_id
inner join tb_course on ce_id=se_id
where mark is not null;
-- 查询选课学生的姓名和平均成绩(子查询和连接查询)
select stu_name,avg(mark) from tb_student,tb_score
where stu_id=st_id and mark is not null group by st_id;
-- 查询每个学生的姓名和选课数量(左外连接和子查询)
select stu_name,if(total,total,0) as 数量 from tb_student t1 left join 
(select st_id,count(st_id)as total from tb_score group by st_id)t2
on t1.stu_id=t2.st_id;
-- 左外连接 -把左表(写在前面的表)不满足连接条件的记录也查出来对应记录补null
-- 右外连接 -把右表(写在后面的表)不满足条件的记录也查出来对应记录补null值

---

• DCL数据控制语言
• grant授权
• 创建用户

create user 'hellokitty'@'localhost'; -- 本地登录账户不能远程连接
create user 'hellokitty'@'ip'; -- 指定IP连接
create user 'hellokitty'@'%'; -- 任意地方登录
create user 'hellokitty'@'%' identified by '密码'; -- 指定登录口令

-- grant授权

grant select on srs.* to 'hellokitty'@'%'; 
grant insert,delete,update on srs.*to 'hellokitty'@'%';
grant create,drop.alter on srs.*to 'hellokitty'@'%';

grant all privileges on srs.*to 'hellokitty'@'%'; -- 所有权限
grant all privileges on srs.* 'hellokitty'@'%' with grant option;
-- 可以把权限给别人

• 召回权限

revoke all privileges on srs.* from 'hellokitty'@'%';

• 事务

begin; -- 开启事务环境
update tb_score set mark=mark-2 where sid=1001 and mark is not null;
update tb_score set mark=mark+2 where sid=1002 and mark is not null;
commit; -- 提交
rollback; -- 回滚撤销事务

通过begin或start transactio开启事务，事务的ACID特性，事务的ACID特性:
Atomicity 原子性-不可分割
Consistency 一致性 -事务前后数据状态一致
Isolation 隔离性 多个事务不能看到彼此的中间状态
Duration 持久性 事务完成后数据要持久化
-- 并发数据访问可能出现的5种问题
-- 第1类丢失更新，第2类丢失更新 脏读 不可重复读 幻读
-- 脏读-一个事务读取到另一个事务尚未提交的数据
-- 不可重复读 -一个事务在读取查询结果时发现其他事务更新了数据导致无法读取
-- 幻读-一个事务在执行查询时发现被其他事务提交了新的数据
-- set session transaction isolation level read committed; 设置当前事务的隔离级别
-- set session transaction isloation level serializable; 串行优化
-- select @@tx_isolation; 查看事务级别

• 视图

视图是查询的快照,可以通过控制视图的权限来控制用户的权限到字段上
通过视图可以将用户对表访问权限进一步加以限制
也就是说将来普通用户不能直接查询表的数据
通过指定的视图取查看允许他访问的内容

create view vw_select_boss as 
select ename,job from tbemp t1
where exists (select 'x' from tbemp t2 where t1.eno=t2.mgr);
select * from vw_select_boss

• 索引

索引(相当于一本书的目录)
为表创建索引可以加速查询(用空间换时间)
索引会让增删改变得更慢
因为增删改操作调整 数据所以可能会导致更新索引
那个列经常被用于查询的筛选条件，那么就应该在这个列上建立索引
主键上有默认的索引(唯一索引)
如果使用模糊查询，如果查询条件不以%开头，那么索引有效，反之，无效

create index idx_emp_ename on tb_student(sname); -- 建立索引
create unique index uni_emp_ename on tb_student(sname); -- 唯一索引
alter table tb_student drop index idx_emp_ename; -- 删除索引