2018-11-20mysql学习

-- SQL: Structured Query Language(结构化查询语言)
-- 1. DDL: 数据定义语言 - create / drop / alter(修改)
-- 2. DML： 数据操作语言 - insert / delete/ update/ select
-- 3. DCL： 数据控制语言 - grant / revoke/ commit / rollback

-- creat database school default charset utf8;

-- 如果存在名为school的数据库就删除
drop database if exists school;

-- 创建school数据库并设置默认字符集为utf8
create database school default charset utf8;

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

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

-- 修改表（添加字段）
alter table tb_student add column stutel char(11) comment '联系电话';

-- 删除字段
alter table tb_student drop column stutel;

alter table tb_student change column stuname stuname varchar(20) not null comment '姓名';

-- 新建课程表
drop table if exists tb_course;

create table tb_course
(
cid int not null comment '课程id',
cname varchar(31) not null comment '课程名',
credit tinyint unsigned not null comment '课程学分',
cdate timestamp default now() comment '开课时间',
primary key (cid)
);

-- tinyint 1字节
-- smallint 2字节
-- int 4字节
-- bigint
-- now() 获取系统当前时间
select now();
select year(now());
select date(now());
select time(now());

-- 在学生表中插入数据
-- into 可以省略
-- 录入一条完整的数据
insert into tb_student values (1001, '张三', default, '2001-12-23', '成都');
-- values 中的数据必须意义对应

-- 指定列插入对应的数据
insert into tb_student (stuid, stuname, stubirh) values (1002, '王大锤', '1990-1-1');

-- 一次录入多个值
insert into tb_student (stuid, stuname, stusex)
values
(1003, '李四', default),
(1004, '小明', '男'),
(1005, '小芳', '女'),
(1006, '白元芳', default);

-- 向课程表中插入数据

insert into tb_course
values
(1001, '语文', 2, default),
(1002, '数学', 3, '2018-10-2'),
(1003, '英语', 2, '2018-11-3');

insert into tb_course
values
(1004, 'python', 2, default),
(1005, 'java', 3, '2018-10-2'),
(1006, 'C', 2, '2018-11-3');

-- 删除记录

delete from tb_student where stuid=1006;
delete from tb_student where stuid>5;
delete from tb_student stuid between 1002 and 1005;

-- 删除全表
truncate table tb_student;

-- 更新数据

update tb_student set stubirh='1995-5-5' where stuid=1006;
-- 增删改的条件一般用主键

update tb_student set stubirh='1996-6-6', stuaddr='河北保定' where stuid=1004;

update tb_course set credit=4 where cid=1003;

update tb_course set credit=2 where cid=1002 or cid=1001;

update tb_course set credit=3 where cid in (1003, 1006);

-- 查询所有记录
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 from tb_student where stubirh between '1990-1-1' and '1999-12-31';

-- 集合运算
select stuname, stusex from tb_student where stuid in (1003, 1005, 1006);

select stuname, stusex from tb_student where stuid not in (1003, 1005, 1006);

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

select * from tb_student where stuaddr is not null;

insert into tb_student (stuid, stuname, stusex) values (1007, '李白', '女');

insert into tb_student (stuid, stuname, stusex) values (1008, '小小舒', '女');

-- 模糊查询
select * from tb_student where stuname like '小%';
-- % 支持匹配0个或多个字符

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 '%白%';

-- 查询数据并排序
select * from tb_student order by stuid desc;
-- asc --- 默认值升序
-- desc --- 降序

select * from tb_student order by stuname asc;

-- 指定多个排序字段进行
select * from tb_student order by stusex desc, stuid desc;

-- 先排列在看前3条
select * from tb_student order by stuid desc limit 3;

select * from tb_student order by stuid desc limit 3 offset 3;
-- offset 跳过指定的条数看指定条数（分页查询）
-- 分页查询可以用以下到的语句： limit 偏移量, 条数
select * from tb_student order by stuid desc limit 6, 3;

select substr(stuname, 1, 1) from tb_student;

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

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

-- 字符串连接
select concat(stuname, stubirh) from tb_student;

-- 聚合查询（聚合函数-- 统计函数）
-- max()
select max(credit) from tb_course;
select max(stubirh) from tb_student;

-- min()
select min(credit) from tb_course;
select min(stubirh) from tb_student;

-- sum()
select sum(credit) from tb_course;

-- avg()
select avg(credit) from tb_course;

-- count()
select count(stuid) from tb_student;
select count(cid) from tb_course;

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

-- DCL 创建用户
create user 'me'@'%' identified by '123456';
-- 'localhost' ---- 只能本地登录
-- 'ip地址' ---- 通过指定的ip登录
-- '%' ---- 任意行式登录

-- 修改用户权限
grant all privileges on . to 'me'@'%';
-- 把所有的权限都给指定用户（但是他不能将自己的所有的权限给别的用户）
-- 收回所有的权限
revoke all privileges on . from 'me'@'%';
-- with grant option
-- 加上以上语句me用户就能将自己的权限授予别的用户

grant select on school.* to 'me'@'%';

grant insert, delete, update on school.* to 'me'@'%';

grant create, drop, alter on school.* to 'me'@'%';

-- 表与表之间的关系（实体之间的关系）
-- 一对一
-- 一对多
-- 多对多

-- 通过外键进行表关联
-- 外键