数据库之MySQL

我们通常可以将SQL分为三类：DDL（数据定义语言），DML（数据操作语言），DCL（数据控制语言）。DDL主要用于创建（create）、删除（drop）和修改（alter）数据库中的对象；DML主要负责插入数据（insert）、删除数据（delete）、更新数据（update）和查询（select）；DCL一般用于授予权限（grant）和召回权限（revoke）。

 

DDL（数据定义语言 ） 

--如果存在名为‘school’的就删除它
drop database if exist 'school';
--创建名为‘school’对的数据库并设置默认的字符集和排列方式
create database 'school' dafault character set utf8mb4;
--切换到school数据库上下文环境
use 'school'
--创建学院表
create table 'tb_college'
('col_id' int unsigned auto_increment comment '编号',
'col_name' varchar(50) not null comment '名称',
'col_intro' varchar(50) dafault '' comment '介绍',
primary_key('col_id'）
) engine=innodb comment '学院表';
--创建学生表
create table 'tb_student'
('stu_id' int unsigned not null comment '学号',
'stu_name' var_char(20) not null comment '姓名',
'stu_sex' boolean default 1 comment '性别',
'stu_birth' date not null comment '出生日期'
'stu_addr' var_char(255)  default comment '籍贯',
'col _id' int unsigned not null comment'所属学院',
primary key ('stu_id'),
foreign key ('col_id') references 'tb_college' ('col_id')，
) engine=innodb comment '学生表';
--创建教师表
create table 'tb_teacher'
('tea_id' int unsigned not null comment '工号'，
‘tea_name' varchar(10) not null comment '姓名'
'tea_title' varchar(10) default '助教' comment '职称',
'col_id' int unsigned not null comment '所属学院',
primary key ('tea_id'),
foregin key ('col_id') references 'tb_colloge' ('col_id'),
) engine=innodb comment '教师表';
--创建课程表
create table 'tb_course'
('cou_id' int undigned not null comment '编号',
'cou_name' varchar(50) not null comment '课程名称',
'cou_credit' int unsigned noy null comment '学分',
'tea_id' int unsigned not null comment '授课老师',
primary key ('cou_id')
foregin key ('Tea_id') references 'tb_teacher' ('tea_id')
） engine=innodb comment '课程表';
--创建选课记录表
create table 'tb_record'
('rec_id' int unsigned auto_increment comment '选课记录号',
'sid' int unsigned not null comment '学号',
'cid' int unsigned not null comment '课程编号',
'select_date' date  not null comment '选课日期',
'score' decimal(4, 1) comment '成绩',
primary key ('rec_id'),
foregin key ('sid') references 'tb_student' ('stu_id'),
foregin key ('cid') references 'tb_course' ('cou_id'),
unique('sid', 'cid')
) engine=innodb comment '选课记录表';

需要注意的几点：1、创建数据库的时候，我们通过default charset utf8mb4指定了数据库默认使用的字符集。如果需要修改MySQL服务启动时默认使用的字符集可以修改配置添加如下命令：

[mysqld]

character-set-server=utf8

       2、在创建表的时候，我们可以在右圆括号的后面通过engine=innodb来指定表的存储引擎.推荐大家使用的引擎就是innodb，因为innodb更适合互联网应用对于高并发，性能以及事务需求方面的支持。

下表对MySQL常用的几种引擎做了对比：

在定义表结构为每个字段定义数据类型时，如果不知道哪个更合适，可以使用MySQL的帮助系统来了解每种数据结构的特性，数据的长度和精度等信息。

? data types
? varchar

在数据类型的选择上，保存字符串数据通常使用varchar和char两种类型，前者通常称变长字符串后者称定长字符串；对于innodb引擎，行存储格式没有区分固定长度和可变长度列，所以varchar和char没有本质区别后者不一定比前者性能更好，如果要保存很大的字符串可以使用text类型；如果要保存很大的字节串可以使用BLOB类型，在MySQL中TEXT和BLOB又分别包括TEXT、MEDIUMTEXT、LONGTEXT和BLOB、MEDIUMBLOB、LONGBLOB三种类型，它们最大的不同在于存储数据的最大大小不同。保存浮点数据可以使用FLOAT和DOUBLE类型，而保存定点数应该使用DECIMAL类型。如果保存日期优先使用DATETIME类型他的时间日期范围比较大。        

DML（数据操作语言）

use school
insert to 'tb_college'
    ('col_name', 'col_intro')
values
    ('计算机学院', '计算机学院1958年设立计算机专业，1981年建立计算机科学系，1998年设立计算机学            院，2005年5月，为了进一步整合教学和科研资源，学校决定，计算机学院和软件学院行政班子合并统一运作、实行教学和学生管理独立运行的模式。 学院下设三个系：计算机科学与技术系、物联网工程系、计算金融系；    两个研究所：图象图形研究所、网络空间安全研究院（2015年成立）；三个教学实验中心：计算机基础教学实验中心、IBM技术中心和计算机专业实验中心。'),(),();

insert to 'tb_student'
    ('stu_id', 'stu_name', 'stu_sex', 'stu_birth', 'stu_addr', 'col_id')
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 to 'tb_teacher'
    ('tea_id', 'tea_name', 'tea_title', 'col_id')
values
    (1122, '张三丰', '教授', 1),
    (1133, '宋远桥', '副教授', 1),
    (1144, '杨逍', '副教授', 1),
    (2255, '范遥', '副教授', 2),
    (3366, '韦一笑', default, 3);
insert to 'tb_course'
    ('cou_id', 'cou_name', 'cou_credit',  'tea_id')
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 to 'tb_record'
    ('sid', 'cid', 'sel_date', 'score')
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, '2019-09-02', null),
    (3755, 1111, '2019-09-02', null),
    (3755, 8888, '2019-09-02', null),
    (3755, 9999, '2017-09-01', 92);

DQL（数据查询语言）

-- 查询所有学生的所有信息
select * from tb_student;
select stu_id, stu_name, stu_sex, stu_birth, stu_addr, col_id from tb_student;

-- 查询所有课程名称及学分(投影和别名)
select cou_name as 课程名称, cou_credit as 学分 from tb_course;

-- 查询所有女学生的姓名和出生日期(筛选)
select stu_name, stu_birth from tb_student where stu_sex=0;

-- 查询所有80后学生的姓名、性别和出生日期(筛选)
select stu_name, stu_sex, stu_birth from tb_student 
where stu_birth>='1980-1-1' and stu_birth<='1989-12-31';

select stu_name, stu_sex, stu_birth from tb_student 
where stu_birth between '1980-1-1' and '1989-12-31';

-- 补充1：在查询时可以对列的值进行处理
select 
	stu_name as 姓名, 
    case stu_sex when 1 then '男' else '女' end as 性别, 
    stu_birth as 生日
from tb_student 
where stu_birth between '1980-1-1' and '1989-12-31';

-- 补充2：MySQL方言(使用数据库特有的函数)
-- 例如：Oracle中做同样事情的函数叫做decode
select 
	stu_name as 姓名, 
    if(stu_sex, '男', '女') as 性别, 
    stu_birth as 生日
from tb_student 
where stu_birth between '1980-1-1' and '1989-12-31';

-- 查询所有80后女学生的姓名和出生日期
select stu_name, stu_birth from tb_student 
where stu_birth between '1980-1-1' and '1989-12-31' and stu_sex=0;

-- 查询所有的80后学生或女学生的姓名和出生日期
select stu_name, stu_birth from tb_student 
where stu_birth between '1980-1-1' and '1989-12-31' or stu_sex=0;

-- 查询姓“杨”的学生姓名和性别(模糊)
-- 在SQL中通配符%可以匹配零个或任意多个字符
select stu_name, stu_sex from tb_student where stu_name like '杨%';

-- 查询姓“杨”名字两个字的学生姓名和性别(模糊)
-- 在SQL中通配符_可以刚刚好匹配一个字符
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 '%嫣%';

update tb_student set stu_name='岳不嫣' where stu_id=1572;

-- 并集运算
select stu_name from tb_student where stu_name like '%不%'
union 
select stu_name from tb_student where stu_name like '%嫣%';

select stu_name from tb_student where stu_name like '%不%'
union all
select stu_name from tb_student where stu_name like '%嫣%';

-- 正则表达式模糊查询
select stu_name, stu_sex from tb_student where stu_name regexp '^杨.{2}$';

-- 查询没有录入家庭住址的学生姓名(空值)
-- null作任何运算结果也是产生null，null相当于是条件不成立
select stu_name from tb_student where stu_addr is null;
select stu_name from tb_student where stu_addr<=>null;

-- 查询录入了家庭住址的学生姓名(空值)
select stu_name from tb_student where stu_addr is not null;

-- 查询学生选课的所有日期(去重)
select distinct sel_date from tb_record;

-- 查询学生的家庭住址(去重)
select distinct stu_addr from tb_student where stu_addr is not null;

-- 查询男学生的姓名和生日按年龄从大到小排列(排序)
-- asc - 升序（从小到大），desc - 降序（从大到小）
select stu_name, stu_birth from tb_student 
where stu_sex=1 order by stu_birth asc;

select stu_name, stu_birth from tb_student 
where stu_sex=1 order by stu_birth desc;

-- 查询年龄最大的学生的出生日期(聚合函数) ---> 找出最小的生日
select min(stu_birth) from tb_student;

select 
	min(stu_birth) as 生日,
    floor(datediff(curdate(), min(stu_birth))/365) as 年龄
from tb_student;

-- 查询年龄最小的学生的出生日期(聚合函数)
select 
	max(stu_birth) as 生日,
    floor(datediff(curdate(), max(stu_birth))/365) as 年龄
from tb_student;

-- 查询所有考试的平均成绩
-- 聚合函数在遇到null值会做忽略的处理
-- 如果做计数操作，建议使用count(*)，这样才不会漏掉空值
select avg(score) from tb_record;

select sum(score) / count(score) from tb_record;

select sum(score) / count(*) from tb_record;

-- 查询课程编号为1111的课程的平均成绩(筛选和聚合函数)
select avg(score) from tb_record where cid=1111;

-- 查询学号为1001的学生所有课程的平均分(筛选和聚合函数)
select avg(score) from tb_record where sid=1001;

select count(distinct stu_addr) from tb_student where stu_addr is not null;

-- 查询男女学生的人数(分组和聚合函数) 
-- SAC（Split - Aggregate - Combine）
select 
	if(stu_sex, '男', '女') as 性别, 
    count(*) as 人数 
from tb_student group by stu_sex;

-- 统计每个学院男女学生的人数
select 
	col_id as 学院, 
    if(stu_sex, '男', '女') as 性别, 
    count(*) as 人数 
from tb_student group by col_id, stu_sex;

-- 查询每个学生的学号和平均成绩(分组和聚合函数)
select 
	sid as 学号, 
    round(avg(score),1) as 平均分 
from tb_record group by sid;


-- 查询平均成绩大于等于90分的学生的学号和平均成绩
-- 分组以前的数据筛选使用where子句，分组以后的数据筛选使用having子句
select 
	sid as 学号, 
    round(avg(score),1) as 平均分 
from tb_record 
group by sid having 平均分>=90;
--查询年龄最大的学生的姓名（子查询）
--嵌套查询：把一个查询的结果作为另一个查询的一部分来使用。
select stu_name from tb_student where stu_birth=
(
    select min(stu_birth) from tb_student
);
--查询年龄最大的学生的姓名和年龄（子查询+运算）
select stu_name as 姓名，
select floor(datediff(curdate(), stu_birth)/365) as 年龄
from tb_student where stu_birth=(
    select min(stu_birth) from tb_student
);
--查询选了两门以上的课程的学生姓名（子查询/分组条件/集合运算）
select stu_name from tb_student
where stu_id in(
    select sid from tb_record group by having count(*)>2);
--查询课程的名称，学分和授课老师姓名（连接查询）
select cou_name, cou_credit, tea_name
from tb_course, tb_teacher
where tb_course.tea_id=tb_teacher.tea_id;
select cou_name, cou_credit, tea_name
from tb_courese t1
inner join tb_teacher t2 on t1.tea_id=t2.tea_id;
--查询学生姓名，课程名称和成绩（连接查询）
select stu_name，cou_name, score
from tb_stuendt, tb_course, tb_record
where stu_id=sid and cou_id = cid and score is not null;
select stu_name, cou_name, score
from tb_student
inner join tb_record on stu_id=sid,
inner join tb_course on cou_id=cid,
where score is not null;
--查询选课学生的姓名和平均成绩
select stu_name, avg_score
from tb_student,
(select sid, round(avg(score), 1) as avg_score
from tb_record group by sid
) tb_temp where stu_id=sid;
--查询每个学生的姓名和选课数量（左外连接）
select stu_name as 姓名,
    ifnull(total, 0) as 选课数量
from tb_student left outer join (
    select sid count(*) as total from tb_record group by sid
) tb_temp on stu_id=sid;

需要加以说明的地方有：1、MySQL支持多种运算符包括算术运算符（+,-,*,/,%）,比较运算符（=、<>、<=>、<、>、>=、<=、BETWEEN……AND……、IN、IS NULL、IS NOT NULL、LIKE、RLIKE、REGEXP)、逻辑运算符（NOT、AND、OR、XOR）和位运算符（&、|、~、^、<<、>>)

2、在查询数据时，可以在select语句及其字句如WHERE语句等中使用函数，这些函数包括字符串函数，数值函数等。

常用字符串函数

常用数值函数

常用时间日期函数

 

常用流程函数 

 

其他常用函数：

DCL（数据控制语言） 

 

--创建可以远程登录的root用户并指定口令
create user 'root'@'%' identified by '123456';
--为远程登录的root账号授权操作所有数据库所有对象的所有权限并允许其将权限再次授权其他用户
grant all privileges on *.* to 'root'@'%' with grant option;
--创建名为HelloKitty的用户并为其指定口令
create user 'hellokitty'@'%' identified by '1234567';
--将对school数据库所有对象的所有操作权限授予HelloKitty账户
grant all privileges on school.* to 'hellokitty'@'%';
--召回上述权力
revoke insert, delete, update on school.* from 'hellokitty'@'%';