MySQL
MySQl基本使用
1.数据库基础
1）数据库管理主要经历过程：
手工管理阶段
文件管理阶段
数据管理阶段
2）数据库特征
数据按一定的数据模型组织，描述和存储
较小的冗余度
较高的数据独立性
可扩展性
3）数据库类型（按数据模型特点分）：
网状型数据库
层次型数据库
关系型数据库
4）SQL语句主要分为：
DDL：数据定义语言，进行数据库、表的管理等，如create（创建）、alter（修改）、drop（删除） DML：数据操作语言，对数据进行增加、修改、删除，如insert、udpate、delete DQL：数据查询语言，用于对数据进行查询，如select DCL：数据控制语言，进行授权与权限回收，如grant、revoke TPL：事务处理语言，对事务进行处理，包括begin transaction、commit、rollback CCL：指针控制语言，通过控制指针完成表的操作，如declare cursor
5）MySQL下载安装
略
注意：
安装时默认用户名为root，密码最好也设置为root，端口号最好采用默认的3306。
6）MySQL启动与停止
①手动
此电脑（右键） -> 管理 -> 服务与应用程序 ->服务 -> mysql -> 开启或停止操作。
①命令
启动：net start mysql57
停止：net stop mysql57
注意：
Ⅰ）开启报错，说明需要设置环境变量。
Ⅱ）为了后面操作mysql，mysql服务必须处于启动状态。
7）在DOS下进入mysql环境
mysql -u用户名 -p密码 -h主机名 -p端口号
eg：
mysql -uroot -proot 或 mysql -uroot -p （好处可以避免密码暴露）
8）退出mysql环境
\q 或 Ctrl+c 或 exit
2.数据库相关操作
1）查看数据库版本
select version();
2）显示当前时间
select now();
3）查看所有数据库
show databases;
4）创建数据库
create database 数据库名 default character set utf8 collate utf8_general_ci;
（collate utf8_general_ci 设置排序规则）
6）切换数据库
use 数据库名;
7）查看当前正在使用哪个数据库
select database();
8）删除数据库
drop database 数据库名;
eg：
-- 查看数据库版本
select version();
-- 查看当前时间
select now();
-- 查看所有数据库
show databases;
-- 创建一个数据库
create database hi default character set utf8;
-- 使用该数据库
use hi;
-- 查看当前使用的是哪个数据库
select database();
-- 删除数据库
drop database hi;3.数据表设计
数据表设计包括ER图，表的主键，字段，数据类型，约束，表之间的设计。
E-R图：E->实体，R->联系，实体联系图。
实体用矩形框表示，联系用菱形框表示，属性用椭圆表示，连线用无方向的直线表示。
实体联系有三种情况：
1：1 一对一
1：n 一对多
m：n 多对多
1）主外键
主键：表中要有主键，它是用来表示唯一标识一个实体的。表中最好有且仅有一个主键，它的值必须是唯一且不能出现null值。主键可能对应的是一个字段，也可能是多个字段的组合（联合主键）。（一个表中的主键值必须唯一，且不可重复添加）
外键：在A表中，它是主键，在B表中它不是主键，那么在B表中这个键就是外键。作用：用来连接两张或多张表。
2）数据类型：
①整型
tinyint：极小整型
smallint：小整型
int或integer：整型
bigint：大整形
timestamp：时间戳
②浮点型
float：单精度（有效位7位）
double：双精度（有效位10位）
decimal（n）：10进制小数（可以指定小数位数）
③文本型
char（n）：定长字符型
varchar（n）：变长字符型（根据内容改变长度）
text：多文本
④日期时间型
date：日期型
time：时间型
datetime：日期时间型
3）约束：
not null：非空约束
primary key：主键约束（值唯一且不可出现空值）
foreign key：外键约束
check：检查约束
unique：唯一性约束（值唯一，可出现空值但只能有一个空值）
4.数据表操作
1）查看当前数据库中的表
show tables;
2）创建数据表
语法：
create table 表名（列1 类型 [宽度] 约束 comment '注释'，列2 类型 [宽度] 约束，........）;
eg：
create table student(sno char(10) primary key,sname varchar(20) not null,sex char(2),hometown varchar(30),tel char(11));3）查看表结构
desc 表名; 
4）查看创建数据表的代码
show create table 表名;
5）表结构修改
语法：
alter table 表名 add|modify|change|drop 列名 类型[宽度] [约束];
①添加：
一列：
alter table 表名 add 列名 类型[宽度] [约束];
alter table student add email varchar(20); 多列：
alter table 表名 add 列名1 [宽度] [约束] , add 列名2 [宽度] [约束] , .....;
alter table student add class varchar(10) not null ,add age int;②修改：
修改单列属性：
alter table 表名 modify 列名 类型[宽度] [约束];
alter table student modify tel int not null; 修改单列属性（包括列名）：
alter table 表名 change 原列名 新列名 类型[宽度] [约束];
alter table student change tel phone int; 注意：
modify不能修改列名，而change则可以。
修改多列属性：
alter table 表名 modify 列名1 [宽度] [约束]，modify 列名2 [宽度] [约束]，....;
修改多列属性（包括列名）：
alter table 表名 change 原列名, 新列名 [宽度] [约束]，change 原列名, 新列名 [宽度] [约束]，.......;
③删除
删除单列：
alter table 表名 drop 列名;
alter table student drop email; 删除多列：
alter table 表名 drop 列名1，drop 列名2，......;
alter table student drop class,drop age;6）删除表
drop table 表名;
eg：
-- 查看当前数据库中有哪些表
show tables;
-- 创建一个表
create table teacher (tno char(8) primary key,tname varchar(8) not null,tcourse char(11));
-- 查看表结构
desc teacher;
-- 查看创建表的代码
show create table teacher;
-- 为表添加列并设置该列的默认值
alter table teacher add tage char(4) default '40';
-- 为表添加多列
alter table teacher add tsex char(4) not null,add taddress varchar (20);
-- 修改某一列的属性
alter table teacher modify tage int not null;
-- 修改某一列的列名并设置属性
alter table teacher change taddress taddr varchar(20) not null;
-- 删除指定某一列
alter table teacher drop tsex;
-- 删除指定的多列
alter table teacher drop tage,drop taddr;
-- 删除表
drop table teacher;7）索引的建立与删除
①建立索引
create [unique] [cluster] index <索引名>
on <表名>(<列名>[<次序>] [,<列名>[<次序>]]......)
unique：建立唯一索引。
cluster：表示要建立的索引时聚簇索引。
# 为student，course，和sc三个表建立索引。其中student表按学生姓名升序建立唯一索引，
# courese表按照姓名升序和课程号降序建立唯一索引
create unique index idx_sname
on student (sname);
create unique index idx_cno_sno
on course(cname asc, cno desc); ②修改索引名称
5.7以上的版本：
alter table <表名> rename index < old_index_name> to < new_index_name>;
5.7以下的版本：
alter table < 表名> drop index < old_index_name>;
alter table < 表名> add index < new_index_name>;
# 将student表的索引idx_sname改为idx
alter table student rename index idx_sname to idx; ③删除索引
drop index < 索引名> on < 表名>;
# 删除courese中的idx_cno_sno索引
drop index idx_cno_sno on course;5.安装MySQL图形界面操作软件Navicat
6.创建学生管理数据库及所有数据表的结构
create database stumanger default character set utf8 collate utf8_general_ci;
use stumanger;
# 创建学生表
drop table if exists student;
create table student (
sno varchar(3) not null primary key comment '学号', -- 主键约束（值不能为空，且唯一）
-- comment 为注释
sname varchar(4) not null comment '姓名', -- 非空约束
ssex char(2) default "男", -- 默认值约束
sbirthday varchar(10) default null,
class varchar(5) not null
) engine=innodb default charset=utf8; -- 设置编码
# 创建教师表
drop table if exists teacher; #如果存在该表就删除再次创建，避免重复运行报错
create table teacher (
tno varchar(3) primary key,
tname varchar(4) not null,
tsex varchar(2) not null,
tbirthday datetime not null,
prof varchar(6) default null,
depart varchar(10) not null
) engine=innodb default charset=utf8;
# 创建课程表
drop table if exists course;
create table course (
cno varchar(5) primary key,
cname varchar(10) not null,
credit int not null check (credit>0 and credit<10), -- 检查约束
tno varchar(10) not null,
foreign key(tno) references teacher(tno) -- 外键约束
) engine=innodb default charset=utf8;
# 创建成绩表
drop table if exists score;
create table score(
sno varchar(3) not null,
cno varchar(5) not null,
grade int,
primary key(sno, cno),-- 联合主键
foreign key(sno) references student(sno),-- 外键约束
foreign key(cno) references course(cno)
)engine=innodb default charset=utf8;注意：
①创建表时，一般要设置主键（主键只能有一个）。
②创建表结构时，一定要先创建主表（只有主键，不含有外键的表），再创建子表（含有任意外键的表）。
③删除表时，要先删除子表，在删除主表。
7.基本查询
1）输出所有列
selec * from 表名； -- *表示所有列 （-- /**/ #注释）
eg：
SELECT * from student;2）输出指定列
select 列1, 列2, ........from 表名;
eg：
SELECT sno,sname,class FROM student;8.添加数据
1）全列插入
insert into 表名 values（值1，值2，......）；
eg：
insert into student values('001','张三','男','2000','2-344'); 注意：
值的个数要和列相同，且位置要对应。
在插入时最提前使用desc 表名；查看一下每列的数据类型。
2）部分列插入
insert into 表名（列1，列2，.......） values（值1，值2，......）；
eg：
insert into student(sno,sname,class) values('002','李四','2-344') 注意：
①值要和列一一对应。
②约束为非空的列必须要插入对应的数据。
3）全列多行插入
insert into 表名 value（值1，值2，........）,（值1，值2，........）,（值1，值2，........）,........
insert into student value('003','王五','男','2000','2-344'),('004','赵六','男','2000','2-344'),('005','李大麻','男','2000','2-344');4）全列部分插入
insert into 表名（列1，列2，.......） value（值1，值2，......），（值1，值2，......），（值1，值2，......），.........；
eg：
insert into student (sno,sname,class) values('005','小花','2-300'),('006','小明','2-310'); tips：s
插入单行数据时最好使用values，多行最好使用value，这是基于性能（速度）的考虑。
9.为所有表添加数据
-- 学生表
insert into student value('102', '李军', '男', '1999', '20033'),
('103', '陆君', '男', '1999', '20031'),
('105', '匡明', '男', '1998', '20031'),
('107', '王丽', '女', '1997', '20033'),
('108', '曾华', '男', '2000', '20033'),
('109', '王芳', '女', '2001', '20031');
-- 教师表
insert into teacher
value('804', '李诚', '男', '1958-12-02 00:00:00', '副教授', '计算机系'),
('825', '王萍', '女', '1972-05-05 00:00:00', '助教', '计算机系'),
('831', '刘冰', '女', '1977-08-14 00:00:00', '助教', '电子工程系'),
('856', '张旭', '男', '1969-03-12 00:00:00', '讲师', '电子工程系');
set foreign_key_checks=1; # 启动外键约束
-- 课程表
insert into course value ('6-161', '数据电路',4, '856'),
('9-880', '高等数学', 5,'831'),
('3-105', '计算机导论',3,'825'),
('3-245', '操作系统',3,'804'),
('6-166', '数据电路',4,'856'),
('9-888', '高等数学',5,'804');
-- 成绩表
insert into score value('103', '3-245', 86.0),
('105', '3-245', 75.0),
('109', '3-245', 68.0),
('103', '3-105', 92.0),
('105', '3-105', 88.0),
('109', '3-105', 76.0),
('103', '9-880', 64.0),
('107', '3-105', 91.0),
('108', '3-105', 78.0),
('108', '6-166', 85.0),
('108', '3-245', 81.0);10.修改数据
语法：
update 表名 set 列1 = 表达式1[，列2 = 表达式2] [where <条件>];
eg：
#将所有姓名添加一个前缀stu-
update student set sname = concat('stu-',sname);
#concat() 拼接函数
#将张三改为Tom，性别为女
update student set sname ='Tom',ssex = '女' where sname = 'stu-张三';11.删除数据
1）delete
可以删除指定的数据或所有数据，表结构还在。
delete from 表名 [where <条件>]；
-- 删除Tom
delete from student where sname='Tom';
-- 删除姓名以stu-开头的学生
delete from student where sname like 'stu-%';
-- %代表任意0-n个字符
-- 清空学生表中所有数据
-- 先复制表再进行删除测试,用student表内的数据创建一个新表
create table stu_bak as select * from student;
-- 清空stu_bak表中所有的数据
select * from stu_bak;
delete from stu_bak;tips:
一般操作时都时按指定条件进行删除操作。一旦删除后，没法还原的，一定要做好数据库或数据表的备份。
2）truncate
只能删除所有数据，表结构还存在
truncate table 表名; --等价于delete from 表名；
-- 如果表存在就删除再创建
drop table if exists stu_bak;
create table stu_bak as select * from student;
-- 清空表中的数据
truncate table stu_bak;
select * from stu_bak;12.删除表
删除时连同表结构和数据一块删除，且无法恢复（删除前要先进行备份）。
drop table 表名；
-- 删除stu_bak表
drop table stu_bak;13.备份与恢复
1）图形界面手动备份与恢复
备份：
恢复
2）命令
Ⅰ）备份
①备份所有表
mysqldump -u用户名 -p密码 -h主机名 -P端口号 数据库名 > [路径]文件名.sql|.txt|.doc|docx
（在DOS窗口下运行）
②备份数据库中指定表
mysqldump -u用户名 -p密码 -h主机名 -P端口号 数据库名 表名 > [路径]文件名.sql|.txt|.doc|docx
Ⅱ）恢复
①恢复备份所有表
mysql -u用户名 -p密码 -h主机名 -P端口号 数据库名 < [路径]文件名.sql|.txt|.doc|docx
②恢复数据库中备份的单表
mysql -u用户名 -p密码 -h主机名 -P端口号 数据库名 < [路径]文件名.sql|.txt|.doc|docx
MySQL数据查询
1.查询的基本语法：
select [distinct] * |列名表|聚合函数 [as <别名>] --distinct：去重 as：取别名
from <表名列表> [as <别名>]
where <条件>
group by <列名表>
having <分组条件>
order by 列1 [asc|desc]，列2[asc|desc]，..... asc：升序排序（默认）desc：降序排序
limit [起始下标，]<条数>
2.select语句
-- 查询所有学生情况(查询所有列)
select * from student;
-- 查询学生的姓名和性别(查询指定列)
select sname,ssex from student;
-- 查询有那些班级(去重)
select distinct class from student;
-- 如果没有指定distinct，则默认值为all保留重复行
-- 统计有多少学生 (使用聚合函数count(*),用as取别名 as可以缺省)
select count(*) as 学生人数 from student;
select count(*) 学生人数 from student;3.where子句（条件查询）
关系：
关系运算：>，>=，<，<=，<>（不等于），!=（不等于），=
逻辑运算：not（非），and（与），or（或）
模糊查询：like / rlike
通配符： _：代表单个任意字符， %：代表任意长度字符串
rlike用于正则匹配
. : 匹配任意单个字符
* ： 匹配0个或多个前一个得到的字符
[] : 匹配任意一个[]内的字符，[ab]*可匹配空串、a、b、或者由任意个a和b组成的字符串。
^ : 匹配开头，如^s匹配以s或者S开头的字符串。
$ : 匹配结尾，如s$匹配以s结尾的字符串。
{n} : 匹配前一个字符反复n次
包含：in/ont in（对字符串型操作）
介于......之间：between and / not betwwen and（对数值型操作）
是否为空：is null / is not null
/：转义字符 /_：转义为普通 _ 字符
-- 关系运算：>，>=，<，<=，<>（不等于），!=（不等于），=
# 查询考试成绩在90分以上的学生
select * from score where grade >=90;
-- 逻辑运算：not（非），and（与），or（或）
# 查询20031班的男生
SELECT * from student where class = '20031' and ssex = '男';
# 查询不是20031班的女生的学号和姓名
select sname,sno from student where not(class = '20031' and ssex = '女');
-- 模糊查询：like/rlike
-- 通配符： _：代表一个字符， %：代表0个，1个或多个字符
# 查询姓李的学生
select * from student where sname like '李%';
# 查询王姓或李姓的学生
select * from student where sname rlike '^王|李';
select * from student where sname rlike '^(王|李).{2,}$';
-- 包含：in/ont in
# 查询王芳或李军的情况
select * from student where sname in('王芳','李军');
select * from student where sname not in('王芳','李军');
-- 介于......之间：between and / not betwwen and
# 查询考试成绩在80-100分之间的学生。
select * from score where grade between 80 and 100;
select * from score where grade>= 80 and grade<=100;
-- 是否为空：is null / is not null
# 查询出生年份为空的学生
select * from student where sbirthday is null; -- 不能写sbirthday = null
select * from student where sbirthday is not null;4.分组
1）关键字：group by
将查询结果按某一列或多列的值分组，值相等的为一组。
分组目的：细化聚集函数的作用对象，若为对查询结果分组，聚集函数将作用于整个查询结果。
2）聚集函数
count（*）：统计元组个数（即行数于列无关）
count（[distinct|all]< 列名>）：统计一列中值的个数 （可能会出现空值）
sum（）：求和
avg（）：求平均值
max（）：求最大值
min（）：求最小值
round（）：四舍五入指定保留小数位数
注意：distinct表示取消指定列中的重复值，如果不指定distinct或all默认值为all，即不取消重复值。
当遇到空值时，count(*)的结果不会改变，其他函数都跳过空值处理非空值。
# 分别统计男女生人数
select count(*) from student group by ssex;
select ssex,count(*) as 人数 from student group by ssex;
#统计学生出生日期(去除重复值)
select distinct sbirthday 出生日期
from student;
# 统计每位学生的考试总分
select * from score;
select sno, sum(grade) 总分 from score group by sno;
# 统计每位学生的考试的最高分
select sno , cno , max(grade) 最高分 from score group by sno;
# 统计每位学生的考试的平均分
select sno , avg(grade) 平均分 from score group by sno;
# 统计每位学生的考试的平均分,保留一位小数
select sno , round(avg(grade),1)平均分 from score group by sno;5.having子句
带有聚集函数的条件，只能放在having后，而不能放在where后。（作用于where相同）
用having子句时必须先分组，也即是having必须和group by结合使用。
# 查询选修两门课以上的学生
select * from score group by sno having count(*)>2;
select * from score group by sno having count(cno)>2;
-- 若某个元组的cno出现null时，count(*) 会记录，count(cno)不会记录该元组6.order by子句
排序，默认是升序排序，也可以使用asc表示升序，降序要用desc实现。
空值序号最小（如果升序排序，空值元组在最前面，降序排序，空值元组在最后面）
# 对成绩进行降序排序
select * from score order by grade desc;
# 按班级进行升序排序，同班级的按出生日期降序排序
select * from student order by class asc,sbirthday desc;7.limit分页子句
用于限制select语句查询结果的元组数量
limit [起始下标] < 行数1>[offset< 行数2>]；
从起始下标开始（可以缺省，默认为0），取<行数1>，忽略<行数2>行作为查询结果，offset可省略代表不忽略任何行。
# 以分页的方式输出学生成绩，每页输出5条数据。
select * from score limit 0,5; -- 下标从0开始,往后取5条数据
select * from score limit 5; -- 下标为0时可以缺省
# 查询成绩表第3-6条记录
select * from score limit 2,4; -- 下标从2开始，往后取4行
# 查询考试前三名的学生
select * from score order by grade desc limit 0,3; -- 下标从0开始取3条数据
# 查询考试平均分(保留一位小数)前4名忽略前3名学生的学号
select sno ,round(avg(grade),1)
from score
group by sno
order by round(avg(grade),1) desc
limit 4,1;8.复杂查询（多表查询）
1）内部连接
[inner] join：实际上就是等值连接，通常把多张表通过外键两两连接起来，形成一张逻辑表
用来获取多张表的交集部分；
# 查询每位学生的每门课的考试成绩，及授课老师
select sname 学生,cname 课程名,grade 分数,tname 老师
from student stu inner join score sc on stu.sno = sc.sno
inner join course c on c.cno = sc.cno -- student stu：为student取一个别名stu
inner join teacher t on t.tno = c.tno;
-- inner可缺省
select sname 学生,cname 课程名,grade 分数,tname 老师
from student stu join score sc on stu.sno = sc.sno
join course c on c.cno = sc.cno
join teacher t on t.tno = c.tno;
# 用where子句实现连接多张表
select sname,cname,grade,tname
from student stu, course c, score sc ,teacher t
where stu.sno = sc.sno and sc.cno = c.cno and t.tno = c.tno;2）外部连接
①left outer join ：左外连接 （outer可缺省）
结果为左表的所有数据全部显示，右表满足条件的有数据，不匹配的置空值。
# 学生表和成绩表进行左外查询
select *
from student a left outer join score b
on a.sno = b.sno; ②right outer join ：右外连接（outer可缺省）
结果为右表所有数据全部显示，左表不匹配的不显示。
# 学生表和成绩表右外查询
select *
from student a right outer join score b
on a.sno = b.sno; ③full outer join ：全外连接（mysql不支持）
注意：自然连接（内部连接）不匹配的的元组悬空。
3）笛卡儿积
A表和B表作笛卡尔积时，结果为列相加，行相乘。
左表每一行对应右表全部行
# 学生表和教师表作笛卡尔积运算
select *
from student cross join teacher;9.嵌套查询（子查询）
实际上就是select语句嵌套。
①不相关子查询：子查询的条件不依赖于父查询
# 查询考试成绩高于平均分的学生的姓名
select sname from student where sno in(
select sno from score where grade>(
select avg(grade) from score)); ②相关子查询：子查询的查询条件依赖父查询
# 找出每个学生超过他自己选修课平均成绩的课程
select sno ,cno
from score x
where grade >(select avg(grade)
from score y
where y.sno = x.sno);10.联合查询
union：将两个查询的结果集（去重后）合并到一起。
# 查询男生和女生的相关信息。
select * from student
where ssex = '男'
union
select * from student
where ssex = '女'
# 查询老师和学生的相关信息
select * from student
union
select * from teacher;
-- 报错列数不同类型不同
select sno, sname from student -- 输出结果列名以第一个表为准
union
select tno, tname from teacher; union all：将两个查询的结果集（不去重）合并到一起。
#用联合查询实现学生信息的查询
select * from student
-- union -- 去重
union all -- 不去重
select * from student;11.保存查询结果
语法：
insert into 表名[(列名表)] select语句；
# 将学生查询到的每位学生的每门课的考试成绩，及授课老师的查询结果进行保存到stuinfo这张表中
-- 第一步：先创建数据存入的表
drop table if exists stuinfo;
create table stuinfo(
sname varchar(20),cname varchar(20),tname varchar(20),grade int);
select * from stuinfo;
-- 第二步：向创建的表中添加查询的结果
insert into stuinfo(
select sname,cname ,tname,grade
from student stu inner join score sc on stu.sno = sc.sno
inner join course c on c.cno = sc.cno
inner join teacher t on t.tno = c.tno);
select * from stuinfo;MySQL高级
1.用户与权限管理
1）查看mysql中所有用户信息
select host, user from mysql.user;2）添加用户
Ⅰ）MySQL中的用户分为两种：root用户和普通用户。使用root用户创建普通用户。
Ⅱ）使用create user’语句创建普通用户
语法：CREATE USER '用户名1'@'主机名' IDENTIFIED by '密码' [,'用户名2'@'主机名' IDENTIFIED by '密码'];可同时定义多个用户。
主机名，用于指定该用户在哪个主机上可以登录MySQL服务器，localhost表明该用户只能只能在本地登录，若想远程登录将主机名的值改为%或者具体的主机名，%表明在任何电脑上否可以登录。
CREATE USER 'test001'@'localhost' IDENTIFIED by 'test001'; 注意：使用‘create user’ 语句创建的用户没有任何权限，如果想要该用户拥有某些权限，需要使用授予权限的SQL语句来实现
3）删除用户
Ⅰ）使用drop user语句删除普通用户
语法：DROP uesr '用户名'@'主机名'[, '用户名'@'主机名'];
DROP USER 'test001'@'localhost'; Ⅱ）使用DELETE语句删除普通用户
语法：delete from mysql.user where user = '用户名' and host = '主机名';
delete from mysql.user where user = 'test001' and host = 'localhost';4）设置（添加）权限
Ⅰ）查看指定用户的权限，需要具有对MySQL数据库的select权限 ，show grants for '用户名'@'主机名';
CREATE USER 'u1'@'localhost' IDENTIFIED by 'u1';/*创建用户'u1'@'localhost'*/
SHOW grants for 'u1'@'localhost'; /*查看'u1'@'localhost'的权限，USAGE 是指没有任何权限*/ Ⅱ）使用root超级用户来授予其他已经存在的用户权限
grant 权限列表 on (* | 数据库) . (*|数据表名) to ‘用户名’@‘访问主机’ ；
权限列表：create alter drop insert update delete select等，分配所有权限：all privileges。
/*把查询student表的权限授给用户U1*/
GRANT select ON TABLE teststudent.student TO 'u1'@'localhost';
/*创建用户U2，U3*/
CREATE USER 'u2'@'localhost' IDENTIFIED by 'u2','u3'@'localhost' IDENTIFIED by 'u3';
/*把对student表和course表的全部操作权限授予用户U2和U3 */
GRANT ALL PRIVILEGES ON TABLE teststudent.student TO 'u2'@'localhost','u3'@'localhost';
GRANT ALL PRIVILEGES ON TABLE teststudent.course TO 'u2'@'localhost','u3'@'localhost';5）删除（回收权限）
语法：revoke 权限列表 on (* | 数据库) . (*|数据表名) from ‘用户名’@‘访问主机’ ；
/*收回u2，u3对student表的全部权限*/
REVOKE ALL PRIVILEGES ON TABLE student FROM 'u2'@'localhost';
REVOKE ALL PRIVILEGES ON TABLE student FROM 'u3'@'localhost';6）修改密码
7）创建和收回角色
/*为一组具有相同权限的用户创建一个角色，然后使用角色为为这些用户授权 */
/* 首先创建一个角色 r1*/
CREATE role r1;
/* 使用grant语句，使r1拥有student表的select、UPDATE、INSERT权限*/
GRANT SELECT,UPDATE,INSERT on TABLE teststudent.course TO r1;
/* 将r1这个角色授予用户u1、u2、u3*/
GRANT r1 TO 'u1'@'localhost','u2'@'localhost','u3'@'localhost';
SHOW grants for 'u1'@'localhost';
/* 可以一次性地通过r1收回用户u1、u2、u3的这三个权限*/
REVOKE r1 from 'u1'@'localhost';
/* 角色的权限修改，增加student表的delete 权限*/
GRANT UPDATE on TABLE teststudent.course TO r1;
/* 减少r1的的select权限*/
REVOKE SELECT on TABLE teststudent.course FROM r1;2.事务
1）概念
事务：也称工作单元，是由一个或多个SQL语句所组成的操作序列，这些SQL语句作为一个完整的工作单元，要么全部执行成功，要么全部执行失败。在数据库中，通过事务来确保数据的一致性。
2）引擎
使用Innodb引擎支持事务。
3）事务特征（ACID）：
原子性（A）、一致性（C）、隔离性（I）和持久性（D）。
4）事务处理
Ⅰ）手动
MySQL默认是自动提交的，也就是你提交一个sql，就直接执行。可以通过set autocommit=0禁止自动提交，set autocommit = 1开启自动提交，来实现事务处理
set autocommit = 0
事务处理过程：
①开启事务 begin；begin transaction；
②事务要执行的语句序列
③提交事务或回滚事务 提交事务commit； 回滚事务：rollback；
# 创建一个用来删除李大六学生的事务
begin;
delete from student where sname = '李大六';
select * from student;
commit; -- 提交事务
# 删除之间将student表进行备份
create table stu as select * from student;
select * from stu;
# 创建一个用来删除王姓学生的事务
begin;
delete from stu where sname like '李%';
rollback; -- 事务回滚，撤销之前的操作 2）自动
set autocommit = 1 开启自动提交（默认）
3.视图
视图也叫虚拟表，是由基表派生出来的数据，用来存储查询结果。
1）创建视图
语法：
create view 视图名 as select语句：
create view v_stu_score as
select sname,grade from student a,score b where a.sno = b.sno;
# 可以通过视图进行查看，也可以通过对基本表的操作语句进行查看
select * from v_stu_score;2）查看视图
show tables；
show table status;
3）删除视图
drop view 视图名称；
4）调用视图
可以像操作基表一样去操作视图
select * from 视图名；
5）更新视图
更新视图就是指通过视图来插入、删除和修改数据。由于视图是不实际存储数据的虚表，因此对视图的更新最终要转换为对基本表的更新。
注意：
修改视图中的数据会影响到原始数据。
5.索引
索引是为了优化查询速度，特别对大批量的数据的筛选或排查。
索引选择越小的数据类型越好。
索引的分类：主键索引，唯一索引，普通索引，全文索引，组合索引。
1）创建索引
语法：
create [unique|fulltext] index 索引名 on 表名（列名|列名表）
Ⅰ）主键索引（PRIMARY KEY）：
在创建表结构时，一旦为该表设置主键，就会自动创建主键索引。
eg：
① create table table1（id int primary key auto_increment，......）auto_increment自动编号
②alter table 表名 add primary key （列名）；
Ⅱ）唯一索引（UNIQUE）：
create unique index 索引名 on 表名(列名);
create unique index idx_sname on student(sname); Ⅲ）普通索引（INDEX）：
create index 索引名 on 表名（列名）；
create index idx_class on student(class); Ⅳ）全文索引（FULLTEXT）：
基于相似度的查询。
注意：
①每张表只能有一个全文索引；
②由多列组合组合而成的全文索引的列必须使用相同的字符聚或排序规则；
③如果字段中含有汉字，要使用mysql5.6以后的版本；
④只允许对char，varchar和text数据类型的列上创建全文索引；
⑤全文索引不能跨表查询；
⑥全文索引要将数据缓存到内存中，所以内存要求32M以上，可以通过innodb_ft_cache_size调整大小；
⑦全文索引是根据频词来查询的；
⑧mysql5.6+，MyISAM和InnoDB存储引擎都支持全文索引；
语法：
create fulltext index 索引名 on 表名（列名|列名表）；
create fulltext index idx_tname on teacher(tname); 使用全文索引：
select * from teacher where match(tname) against('张大仙'); 注意：
①match后的列必须要跟创建该索引的列完全一样，against后是要查询的文本内容；
②通过 show variables like "%ft%"; 测试，MyISAM查询的字符在4-84个之间，而InnoDB在3-84之间，如果低于3个，将查不到结果。
Ⅴ）组合索引：
语法：
create [unique] index 索引名 on 表名（列名1，列名2，........）
create unique index indx_a on student(sno,sname);
create index indx_b on teacher(tno,tname);2）查看索引
show index from 表名 [from <数据库名>]；
show index from student;3）删除索引
drop index 索引名 on 表名；
drop index indx_a on student;6.存储过程
存储过程就是对多条sql语句的封装。
1）语法：
delimter // -- 设置语句分隔符为//
create procedure 存储过程名称（参数列表）
begin
sql语句
end
//
delimter; tips：delimter用于设置sql语句分割符，默认为分号。 为了避免在dos命令中 输入；后敲回车语句会自动执行。在navicat中可以省略不写。
-- 创建一个查询学生信息的存储过程
delimiter $$ -- 设置sql语句分隔符为$$
create procedure proc_stuinfo()
begin
select * from student;
end
$$
delimiter;2）调用存储过程
call 存储过程名（实参）; --没有参数括号可以缺省
call proc_stuinfo();
或
call pro_stuinfo();3）删除存储过程
drop procedure 存储过程名；
drop procedure proc_stuinfo;4）存储过程中的变量
Ⅰ）定义
DECLARE 变量名 数据类型 [DEFAULT 默认值]；
未设置默认值，默认为null；
drop procedure if exists test;
create procedure test()
begin
declare str varchar(10) default 'abc'; -- 定义单个变量
select str; -- 输出str
end
call test(); Ⅱ）赋值
SET 变量名 = 表达式；
drop procedure if exists test;
create procedure test()
begin
declare addr,sname varchar(20); -- 同时定义多个变量
set addr = '中国上海'; -- 赋值
set sname = '张三';
select sname 姓名,addr 住址; -- 取别名
end
call test(); 通过查询表的结果赋值和传参赋值
drop procedure if exists test1;
create procedure test1(str varchar(10))
begin
declare num,sname varchar(20);
set sname = str; -- 传参赋值
select count(*) into num from student where ssex ='男'; -- 查询表结果赋值
select sname,num;
end
call test1("Harrison");7.存储过程中的条件语句
1）IF语句（单分支）
语法：
IF expression THEN
statements;
END IF; 如果表达式expression计算结果为TRUE，那么将执行statements语句，否则执行END IF以后的语句。
2）IF.....ELSE语句（双分支）
语法：
IF expression THEN
statements;
ELSE
else-statements;
END IF;drop procedure if exists proc_query_name;
create procedure proc_query_name(str varchar(10))
begin
if str = "王大头" then
select concat(str,'是一位很优秀的学生！'); -- concat(str1,str2,..) 字符串拼接
else
select concat(str,"是一位普通学生");
end if;
end
call proc_query_name('王大头');
call proc_query_name('AA');3）IF...ELSEIF...ELSE语句（多分支）
IF expression THEN
statements;
ELSEIF elseif—expression THEN
elseif-statements;
.......
ELSE
else-statements;
END IF;drop procedure if exists proc_query_name;
create procedure proc_query_name(str varchar(10))
begin
if str = "王大头" then
select concat(str,'是一位很优秀的学生！');
elseif str = "AA" then
select concat(str,"是一位不错的学生");
else
select concat(str,"是一位普通学生");
end if;
end
call proc_query_name('王大头');
call proc_query_name('AA');8.WHILE循环语句
语法：
while 条件 do
循环体
end while; 利用存储过程依次向temp表中添加5条数据
drop table if exists temp;
create table temp(id int auto_increment primary key,content varchar(20));
# auto_increment 自动编号
drop procedure if exists proc_adddater;
create procedure proc_adddater()
begin
declare i int default 1;
while i <= 5 do
insert into temp(content) values(concat('data-',i));
set i = i + 1;
end while;
end
call proc_adddater();
select * from temp;9.函数
1）内置函数（库函数）
Ⅰ）字符串函数
①查看字符的ascii码值
ascii（str），str是空串时返回0
select ascii('abc'); -- 97 只返回第一个字符的ascii值 ②查看ascii码值对应的字符
char（数字）
select char(65); -- A ③拼接字符串
concat（str1,str2.....）
select concat('str1','str2','str3','str4'); ④包含字符个数
length（str）
select length('qwerrttg') Ⅱ）截取字符串
①left（str，len）返回字符串str的左端len个字符
select left('abcd34456',3); -- abc ②right（str，len）返回字符串str的右端len个字符
select right('abcdef',4); -- cdef ③substring（str，pos，len）返回字符串str的位置pos起len个字符串
select substring('abcdef',3,4); -- cdef 下标从1开始 Ⅲ）替换字符串
replace（str，fromstr，tostr）
select replace('123abc','abc','456'); -- 123456Ⅳ）大小写转换
lower（str）
upper（str）
select upper('abc'); -- ABC
select lower('ABC'); -- abcⅤ）日期时间函数
year（date）返回date的年份
month（date）返回date中的月份值
day（date）返回date中的日期数值
hour（time）返回time的小时数（0-23）
minute（time）返回time的分钟数（0-59）
second（time）返回time的秒数（0-59）
日期格式化：date_format（date，format）
当前日期：current_date（）
当前时间：current_time（）
当前日期时间：now（）
2）用户自定义函数
Ⅰ）定义函数
语法：
delimiter $$
create function 函数名称(参数列表) returns 返回类型
begin
sql语句
end
$$
delimiter;create function py_trim (str varchar(100)) returns varchar(100)
begin
declare x varchar(100);
set x = trim(str); -- 删除左右空格
return x;
end Ⅱ）查看函数
select db,name from mysql.proc where name like 'py_%'; 在函数下，和mysql下的proc表中存在。
Ⅲ）调用函数
select 函数名称（参数列表）；
select py_trim (' a b b c d '); Ⅳ）删除函数
drop function 函数名；
drop function 函数名你;
或
delete from mysql.proc where name = '函数名';10.查询实战
1）数据准备
create table goods(
id int PRIMARY key auto_increment, -- auto_increment 自动编号
gname varchar(50) not null,
cate varchar(50) not null,
brand_name varchar(40) not null,
price decimal(10,3) not null default 0
);
insert into goods values(0,' Apple MacBook Air 13.3英寸笔记本电脑','笔记本','苹果','6588');
insert into goods values(0,'联想(Lenovo)拯救者R720 15.6英寸大屏','笔记本','联想','6099');
insert into goods values(0,'法国酒庄直采原瓶原装进口AOC级艾落干红葡萄酒','红酒','法国','499');
insert into goods values(0,'x550cc 15.6英寸笔记本','笔记本','华硕','2799');
insert into goods values(0,'清扬(CLEAR)洗发水','洗发水','清扬','35');
insert into goods values(0,'荣耀MagicBook 14英寸轻薄窄边框笔记本','笔记本','联想','4299');
insert into goods values(0,'svp13226scb 触控超极本','超级本','索尼','7999');
insert into goods values(0,'海飞丝洗发水清爽去油750ml','洗发水','海飞丝','98');
insert into goods values(0,'ipad air 9.7英寸平板电脑','平板电脑','苹果','3388');
insert into goods values(0,'轩尼诗（Hennessy）洋酒 新点干邑白兰地 200ml','白酒','轩尼
诗','199');
insert into goods values(0,'ideacentre c340 20英寸一体电脑 ','台式机','联想','3499');
insert into goods values(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899');
insert into goods values(0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188');
insert into goods values(0,'阿道夫（ADOLPH）轻柔丝滑洗护组合3件套','洗发水','阿道夫','3699');
insert into goods values(0,'z220sff f4f06pa工作站','服务器/工作站','惠普','4288');
insert into goods values(0,'poweredge ii服务器','服务器/工作站','戴尔','5388');
insert into goods values(0,' 三星（SAMSUNG）C27F390FHC 27英寸 1800R曲率 ','显示器','三
星','1300');
insert into goods values(0,'戴尔（DELL） U2417H 23.8英寸四边微边框旋转升降IPS屏','显示器','戴尔','1500');2）查询
①查询goods表中所有的商品。
select * from goods; ②查询所有产品的平均价格,并且保留两位小数。
select round(avg(price),2) as 所有产品平均价格 from goods;
-- round(,n)保留n位小数 avg() 求平均值 ③通过子查询来实现，查询所有价格大于平均价格的商品，并且按价格降序排序。
select * from goods where
price>(select avg(price) from goods)
order by price desc; ④查询所有 "联想" 的产品
select * from goods where brand_name = '联想'; ⑤查询价格大于或等于"联想"价格的商品，并且按价格降序排列
select * from goods where
price >= any( -- any 任意一个 all 全部
select price from goods where brand_name = '联想'
)
and brand_name <> '联想' -- 不包括联想产品在内
order by price desc; ⑥查询每个产品类型的最低价格的，通过cate字段进行分组
select cate, min(price) from goods group by cate; -- min() 求最小值 group by 分组 ⑦查询价格区间在4500-6500之间的笔记本
select * from goods where price between 4500 and 6500; -- between x and y 在x-y之间3）查询数据分表
创建一个商品表：
create table goods_cates(
cate_id int primary key auto_increment,
cate_name varchar(40)
); ①查询goods表中所有的商品，并且按"类别"分组。
select cate from goods group by cate; ②将分组后的结果写入到刚才创建的表中。
insert into goods_cates(cate_name) select cate from goods group by cate; ③通过goods中cates数据表来更新goods表，将goods表中的cate字段，修改成goods中cates的id字段。
update goods inner join goods_cates on goods.cate = goods_cates.cate_name
set cate = cate_id; -- inner join：内部(等值)连接 on 连接条件 ④字段 brand_name 进行分表。
create table brand (
brand_id int(11) primary key auto_increment,
brand_name varchar(40)
);
insert into brand(brand_name)
select brand_name from goods group by brand_name; ⑤通过goodsbrands数据表来更新goods表，将goods表中的barndname字段，修改成goods_brands的id字
段。
update goods as g inner join brand as b on g.brand_name=b.brand_name
set g.brand_name = brand_id; ⑥查看goods表结构，发现 cate，brand_name 两个字段都是varchar字段，需要修改成int类型字段。
desc goods; -- desc 查看表结构
alter table goods modify cate int,modify brand_name int; -- modify 修改表结构 ⑦通过左连接查询所有商品的信息。
select id,gname,b.cate_name,c.brand_name,price from goods a
left join goods_cates b on a.cate = b.cate_id
left join brand c on a.brand_name = c.brand_id; ⑧通过右连接查询所有商品的信息。
select id,gname,b.cate_name,c.brand_name,price from goods a
right join goods_cates b on a.cate = b.cate_id
right join brand c on a.brand_name = c.brand_id;