-- SQL:Structured Query language - 结构化查询语言 - 不区分大小写 
-- 1. DDL :Data DEfinition Language - 数据定义语言- create（创建）/drop(删除)/alter(修改)
-- 2. DML: Data Manipulation Language - 数据操作语言 - insert(添加)/delete(删除)/update(改)/select(查)
-- 3. DCL: Data Control Language - grant/revokr/commit/rollback
-- 如果存在名为school的数据库就删除
drop database if exists school;
-- 创建school数据库并设置默认字符集为utf8
create database school default charset utf8;

-- 切换到school数据上下文环境
use school;

-- 如果存在名为tb_student的表就删除
drop table if exists tb_student;

-- 创建学生表tb_student
create table tb_student 
(
stuid int not null comment '学号',
stuname varchar(31) not null comment '姓名',
stusex  enum('男','女') default '男' comment '性别',
stubirth date comment '出生日期',
stuaddr varchar(255) comment '家庭地址',
primary key (stuid)
);

-- 修改tb_student 表的表结构
alter table tb_student add column stutel char(11) comment '联系电话';

-- 修改tb_student表结构删除一个列
alter table tb_student drop column stutel;

-- 修改tb_student 表结构修改一个列
alter table tb_student change column stuname stuname varchar(20) not null;


-- 如果存在名为tb_course的表就删除
drop table if exists tb_course;
-- 新建课程表 tb_course
create table tb_course
(
cid int unsigned not null comment '课程编号',
cname varchar(31) not null comment '课程名',
credit tinyint unsigned not null  comment '学分',
cdate timestamp default now() comment '开课日期',
primary key (cid)
);
-- select now();
-- select year(now());
-- select date(now());
-- select time(now());

-- 录入数据
insert into tb_student values
(1001, '小明', '男', '19980529','四川成都');
insert into tb_student values
(1002, '小花', '女', '19980415','四川绵阳');
insert into tb_student (stuid, stuname, stusex)
values(1003, '小华', '男');
-- 多行录入
insert into tb_student (stuid, stuname, stusex) values
(1004, '李元芳', '男'),
(1005, '狄仁杰', '男'),
(1006, '武则天', '女');


-- 添加课程
insert into tb_course values
(2001, '语文',1,'2017.0527'),
(2002, '数学',2,'2017.0527'),
(2003, '英语',3,'2017.0527');



-- 删除记录 <> - 不等号
-- delete from tb_student where stuid=1005;
-- delete from tb_student where stuid>1005;
-- delete from tb_student between 1003 and 1005;


-- 删全表

-- 更新记录  
update tb_student set stubirth='1995-5-5' where stuid=1004;
update tb_student set stubirth='1995-5-5', stuaddr = '河北保定' where stuid=1003;

-- 修改多个
-- update tb_course set credit='4' where cid=2001 or cid=2002;
-- update tb_course set credit='4' where cid in (2001,2003);
-- 修改单个字符
-- update tb_course set credit='4' where cid=2001;


-- 查询记录
select * from tb_student;
select * from tb_course;

-- 投影
select stuid, stuname, stusex from tb_student;

-- 别名
select stuid as 学号, stuname as 姓名, stusex as 性别 from tb_student;

-- 筛选
select stuid, stuname, stusex from tb_student where stusex='女'; 
select stuname, stusex, stubirth from tb_student where stubirth between '1990-1-1' and '1999-12-31';
select stuname, stusex, stubirth from tb_student where stubirth between '1990-1-1' and '1999-12-31' and stusex='男';
select stuname, stusex, stubirth from tb_student where stubirth between '1990-1-1' and '1999-12-31' or stusex='男';


  select stuid, stuname from tb_student where stuid in (1001,1004);

-- 判断空值（null）不能用=或<>,需要用is或者is not
select * from tb_student where stuaddr is null;
select * from tb_student where stuaddr is not null;


insert into tb_student values (1007, '李白', default, null, null);
insert into tb_student values (1008, '李白菜', '女', null, null);
-- 模糊查询  

select * from tb_student where stuname like '李%';
select * from tb_student where stuname like '小_';
select * from tb_student where stuname like '%白';
select * from tb_student where stuname like '%白%';

-- 排序   order by 排序
select * from tb_student order by stuid desc;     
select * from tb_student order by stuname asc;    

select * from tb_student order by stusex asc;
select * from tb_student order by stusex desc, stuid asc;
 
-- 分页查询
select * from tb_student order by stuid desc limit 3;
select * from tb_student order by stuid desc limit 3 offset 3; 
select * from tb_student order by stuid desc limit 3,3;
select * from tb_student order by stuid desc limit 3 offset 6;
select * from tb_student order by stuid desc limit 6,3;

-- 函数
-- distinct 去重
select distinct substr(stuname, 1, 1) from tb_student;

select substr(stuname, 2, length(stuname)) from tb_student;


-- 聚合函数（统计函数）
select max(credit) from  tb_course;
select min(credit) from  tb_course;

select max(stubirth) from tb_student;

select sum(credit) from tb_course;        -- 求和
select avg(credit) from tb_course;        -- 求平均
select count(stuid) from tb_student;       -- 求个数

-- 分组查询
 -- group by 分组
select stusex, count(stuid) from tb_student group by stusex;  



-- 创建用户 
create user 'wangdachui'@'%' identified by '123456';

-- 授权操作   with grant option --> 授权给别人
-- 把权限全部赋予用户并赋予用哦过户授权给他人的权限
grant all privileges on *.* to 'wangdachui'@'%' with grant option;
-- 召回权限
revoke all privileges on *.* from 'wangdachui'@'%';
-- 给用户赋查询的权限
grant select on school.* to 'wangdachui'@'%';

grant delete, create, insert, update,drop, alter on school.* to 'wangdachui'@'%';


-- 表与表之间的关系

-- 一对一  （人和身份证，用户和购物车） 
-- 一对多 / 多对一 （人和银行卡，用户和订单）要维持这种关系，需要在多的一方去添加外键关系
-- 多对多  （学生和课程，订单和商品）

-- 外键：外来的主键（其他表的主键）
-- 学生、 课程、学院、老师

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

-- 切换到SRS数据库
use SRS;

-- 创建学院表
create table tb_college
(
collid int not null auto_increment comment '学院编号',
collname varchar(50) not null comment '学院名称',
collmaster varchar(20) not null comment '院长姓名',
collweb varchar(511) default '' comment '学院网站',
primary key (collid)
);

-- 添加唯一约束
alter table tb_college add constraint uni_college_collname unique (collname);

-- 创建学生表
create table tb_student
(
stuid int not null comment '学号',
sname varchar(20) not null comment '学生姓名',
gender bit default 1 comment '性别',
birth date not null comment '出生日期',
addr varchar(255) default '' comment '籍贯',
collid int not null comment '所属学院编号',
primary key (stuid)
);

-- 添加外键约束
alter table tb_student add constraint fk_student_collid foreign key (collid) references tb_college (collid);

-- 创建教师表
create table tb_teacher
(
teaid int not null comment '教师工号',
tname varchar(20) not null comment '教师姓名',
title varchar(10) default '' comment '职称',
collid int not null comment '所属学院编号'
);

-- 添加主键约束
alter table tb_teacher add constraint pk_teacher primary key (teaid);

-- 添加外键约束
alter table tb_teacher add constraint fk_teacher_collid foreign key (collid) references tb_college (collid);

-- 创建课程表
create table tb_course
(
couid int not null comment '课程编号',
cname varchar(50) not null comment '课程名称',
credit tinyint not null comment '学分',
teaid int not null comment '教师工号',
primary key (couid)
);

-- 添加外键约束
alter table tb_course add constraint fk_course_tid foreign key (teaid) references tb_teacher (teaid);

-- 创建学生选课表
create table tb_score
(
scid int not null auto_increment comment '选课编号',
sid int not null comment '学号',
cid int not null comment '课程编号',
seldate date comment '选课时间日期',
mark decimal(4,1) comment '考试成绩',
primary key (scid)
);

-- 添加外键约束
alter table tb_score add constraint fk_score_sid foreign key (sid) references tb_student (stuid);
alter table tb_score add constraint fk_score_cid foreign key (cid) references tb_course (couid);


-- 插入学院数据
insert into tb_college (collname, collmaster, collweb) values 
('计算机学院', '左冷禅', 'http://www.abc.com'),
('外国语学院', '岳不群', 'http://www.xyz.com'),
('经济管理学院', '风清扬', 'http://www.foo.com');

-- 插入学生数据
insert into tb_student (stuid, sname, gender, birth, addr, collid) values
(1001, '杨逍', 1, '1990-3-4', '四川成都', 1),
(1002, '任我行', 1, '1992-2-2', '湖南长沙', 1),
(1033, '王语嫣', 0, '1989-12-3', '四川成都', 1),
(1572, '岳不群', 1, '1993-7-19', '陕西咸阳', 1),
(1378, '纪嫣然', 0, '1995-8-12', '四川绵阳', 1),
(1954, '林平之', 1, '1994-9-20', '福建莆田', 1),
(2035, '东方不败', 1, '1988-6-30', null, 2),
(3011, '林震南', 1, '1985-12-12', '福建莆田', 3),
(3755, '项少龙', 1, '1993-1-25', null, 3),
(3923, '杨不悔', 0, '1985-4-17', '四川成都', 3);

-- 插入老师数据
insert into tb_teacher (teaid, tname, title, collid) values 
(1122, '张三丰', '教授', 1),
(1133, '宋远桥', '副教授', 1),
(1144, '杨逍', '副教授', 1),
(2255, '范遥', '副教授', 2),
(3366, '韦一笑', '讲师', 3);

-- 插入课程数据
insert into tb_course (couid, cname, credit, teaid) values 
(1111, 'Python程序设计', 3, 1122),
(2222, 'Web前端开发', 2, 1122),
(3333, '操作系统', 4, 1122),
(4444, '计算机网络', 2, 1133),
(5555, '编译原理', 4, 1144),
(6666, '算法和数据结构', 3, 1144),
(7777, '经贸法语', 3, 2255),
(8888, '成本会计', 2, 3366),
(9999, '审计学', 3, 3366);

-- 插入选课数据
insert into tb_score (sid, cid, seldate, mark) values 
(1001, 1111, '2017-09-01', 95),
(1001, 2222, '2017-09-01', 87.5),
(1001, 3333, '2017-09-01', 100),
(1001, 4444, '2018-09-03', null),
(1001, 6666, '2017-09-02', 100),
(1002, 1111, '2017-09-03', 65),
(1002, 5555, '2017-09-01', 42),
(1033, 1111, '2017-09-03', 92.5),
(1033, 4444, '2017-09-01', 78),
(1033, 5555, '2017-09-01', 82.5),
(1572, 1111, '2017-09-02', 78),
(1378, 1111, '2017-09-05', 82),
(1378, 7777, '2017-09-02', 65.5),
(2035, 7777, '2018-09-03', 88),
(2035, 9999, date(now()), null),
(3755, 1111, date(now()), null),
(3755, 8888, date(now()), null),
(3755, 9999, '2017-09-01', 92);