MySQL数据库总结
一、入门介绍
MySQL是一种开放源代码的关系型数据库管理系统（RDBMS），使用常用的数据库管理语言-结构化查询语言（SQL）进行数据库管理。
二、sql语句分类
- 1、DDL(Data Definition Language):数据定义语言，用来定义数据库对象：库、表、列等。功能： 创建、删除、修改库和表结构。
- 2.DML(Data Manipulation Language):数据操作语言，用来定义数据库记录:增、删、改表记录。
- 3.DCL(Data Control Language):数据控制语言，用来定义访问权限和安全级别。
- 4.DQL(Data Query Language):数据查询语言，用来查询记录。也是本章学习的重点。
三、sql数据中的属性类型
- TINYINT:1字节，小整数值。
- SMALLINT:2字节，大整数值。
- MEDIUMINT:3字节，大整数值。
- INT或INTEGER:4字节，整型,大整数值。
- FLOAT:单精度浮点数值。
- DOUBLE(5,2):双精度浮点型数值，参数表示该浮点型数值多有5位，其中必须有2位小数。
- DECIMAL(M,D):小数值,参数表示该数值多有M位，其中必须有D位小数。
- CHAR:字符型，固定长度字符串类型:char(255)。你存入一个a字符，虽然a只占一个字符，但是它 会自动给你加254个空格凑成255个长度。即数据的长度不足指定长度，它会补足到指定长度。
- VARCHAR:可变长度字符串类型：varchar(65535),你存入的数据多长它就是多长。它会抽出几个 字节来记录数据的长度。
- TEXT(CLOB):mysql独有的数据类型，字符串类型。
- BLOB:字节类型。 例如：图片
- YEAR:年份值，格式为:YYYY
- DATA:日期类型，格式为:yyyy-MM-dd。
- TIME:时间类型，格式为:hh:mm:ss。
- TIMESTAMP:时间戳类型，格式为上面二者的综合。
- DATETIME:混合日期和时间值，格式为:YYYYMMDD
四、四种原因sql语句
1、DCL(数据库控制语言)
用来定义访问权限
4.1.1 创建用户
/*创建用户*/
-- 用户只能在指定ip地址上登录mysql
create user 用户名@IP地址 identified by ‘密码’;
-- 用户可以在任意ip地址上登录
create user 用户名@‘％’ identified by ‘密码’;
-- 示例
create user wjm@'%' identified by 'wjm123';
4.1.2 设置权限，给用户授权
/*设置权限*/
-- 语法：grant 权限1，…，权限n on 数据库.* to 用户名@IP地址；其中权限1、2、n可以直接用all关键字 代替
-- 权限例如:create,alter,drop,insert,update,delete,select。
-- 示例
grant all on test.* to wjm;
4.1.3 撤销授权
1、撤销指定用户在指定数据库上的 指定权限。
2、撤销例如: revoke create,delete on mydb1.* form user@localhost;表示的意思是撤 消user用户在数据库mydb1上的create、alter权限。
/*撤销权限*/
-- revoke 权限1，…，权限n on 数据库.* from 用户名@ ip地址
-- 示例
revoke all on test.* from wjm;
4.1.4 查看权限
/*查看权限*/
-- 语法：查看指定用户的权限：
show grants for 用户名@ip地址；
-- 示例
show grants for wjm;
4.1.5 删除用户
/*删除用户*/
drop user 用户名@ip地址
2、DDL(数据库定义语言)语法
该语言用来对数据库和表结构进行操作
4.2.1对数据库的操作(增删改查)
右击服务器 ---》命令列界面
/*对数据库的操作*/
-- 查看所有数据库
show databases;
-- 使用数据库
USE 数据库名
-- 创建数据库，要指定编码，否则会出现乱码问题
CREATE DATABASE 数据库名 DEFAULT CHARACTER SET utf8;
CREATE DATABASE '武三水' DEFAULT CHARACTER SET utf8;
-- 删除数据库
DROP DATABASE 数据库名
-- 切换数据库，进入某个数据库
USE 数据库名
4.2.2 对表结构的操作
/*对表的操作*/
-- 创建表
DROP TABLE IF EXISTS 创建的表名;
CREATE TABLE 表名(
字段名1 类型 (宽度) 约束条件,
字段名2 类型(宽度) 约束条件,
字段名3 类型(宽度) 约束条件,
.......
);
-- 创建表示例
CREATE TABLE student (
id INT(10) primary key,
name VARCHAR (10),
age INT (10) NOT NULL,
gander varchar(2)
);
-- 查看当前数据库中所有表
SHOW TABLES;
-- 查看表结构
DESC 表名;
-- 删除表
DROP TABLE 表名;
-- 修改表，有五个操作
-- 修改表之添加列语法与示例：
ALTER TABLE 表名 ADD(列名 列类型，...，列名 列类型);
ALTER TABLEstudent ADD(
address varchar(20),
hobby varchar(20)
);
-- 修改表之添加列类型
ALTER TABLE 表名 MODIFY 列名 新的列类型;
ALTER TABLE student MODIFY hobby INT;
-- 修改表之列名称列类型一起修改：
ALTER TABLE 表名 CHANGE 原列名 新列名 列名类型;
ALTER TABLE student CHANGE hobby newHobby varchar(15);
-- 修改表之删除列
ALTER TABLE 表名 DROP 列名;
ALTER TABLE student DROP newHobby;
-- 修改表之修改表名
ALTER TABLE 表名 RENAME TO 新表名
ALTER TABLE student RENAME TO stu;
常用数据类型：
int：整型
double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；
char：固定长度字符串类型； char(10)
varchar：可变长度字符串类型；varchar(10)
text：字符串类型;
blob：字节类型；
date：日期类型，格式为：yyyy-MM-dd；
time：时间类型，格式为：hh:mm:ss
timestamp：时间戳类型 yyyy-MM-dd hh:mm:ss 会自动赋值 datetime:日期时间类型 yyyy-MM-dd hh:mm:ss
3、DML(数据操作语言)增删改
该语言用来对表记录操作(增、删、改)。
4.3.1 插入数据(一次插入就是一行)
/*插入数据*/
-- 语法
INSERT INTO 表名(列名1，列名2，列名3...) VALUES (列值1，列值2，列值3...);
-- 示例
INSERT INTO stu(id，name，age，gander) VALUES(2,'李华',19,'男');
INSERT INTO stu VALUES(2,'李华',19,'男');
4.3.2 修改记录
/*修改记录*/
-- 语法
UPDATE 表名 SET 列名1 = 列值1(,列名2 = 列值2);
UPDATE 表名 SET 列名1 = 列值1(,列名2 = 列值2) WHERE 条件;
-- 示例
UPDATE stu set age=22;
UPDATE stu set age=18,name='武三水';
UPDATE stu set age=18,name='武三水' where id = 1;
-- 修改(某行或者多行记录的)列的指定值:
update 表名 set 列名1=列值1 where 列名2=列值2 or 列名3= 列值3;
4.3.3 删除数据(删除整行)
/*删除数据*/
-- 语法
DELETE FROM 表名 (WHERE 条件);
-- 示例
DELETE FROM student WHERE id = 1;
4、DQL查询记录
不会修改数据库和表结构
4.4.1 基本查询
/*基本查询*/
-- 查询所有列:*表示查询所有列，不是所有行
SELECT * FROM 表名;
-- 查询指定列
SELECT 列1，列2，列n FROM 表名;
-- 去重：在查询列之前添加distinct
SELECT DISTINCT age，name FROM student;
-- 查询特定的某条数据之精确查询 where 条件
SELECT id，name,gander FROM student WHERE id = 3;
-- 模糊查询: '_'代表匹配任意一个字符，'%'代表匹配0～n个任意字符
SELECT id,name,gander FROM student WHERE name like '%武%';
4.4.2 列运算
通过列运算，可以得到一个新值
注意：搜索出来的都是结果的列，不是存在原数据的列，每一列的数据都是从真实原表数据而来
/*列运算*/
-- 数量的列可以做加、减、乘、除
/**
* 1.遇到null加任何值都等于null的情况，需要用到ifnull()函数。
* 2.将字符串做加减乘除运算，会把字符串当作0
*/
SELECT age+1,score+10 FROM 表名;
-- 字符串类型可以做连续运算 (需要用到concat()函数)
select concat(列名1，列名2) from 表名 -- 其中列名是字符类型
-- 给列名起别名
select 列名1 (as) 别名1，列名2 (as) 别名2，列名(n) (as) 别名n
4.4.3 排序
所谓升序和降序都是从上到下排序
/*排序*/
/*默认的排序为正序*/
-- 升序, ()里面的内容为缺省值
SELECT * FROM 表名 ORDER BY 列名(ASC);
-- 降序
SELECT * FROM 表名 ORDER BY 列名 DESC;
-- 使用多列作为排序条件
select * from 表名 order by 列名1 ASC, 列名2 DESC;-- 意思是当列名1的值相同时按照列名2的值降序排
-- 后面可以跟两个字段：这时会先根据第一个字段排序，之后再根据第二个排序
4.4.4 聚合函数
/*聚合函数*/
-- count(汇总：记录行数)
SELECT COUNT(列名) FROM 表名;
-- max(最大值：该列的最大值)
SELECT MAX(列名) FROM 表名;
-- min(最小值：该列的最小值)
SELECT MIN(列名) FROM 表名;
-- sum(求和：该列的总和)
SELECT SUM(列名) FROM 表名;
-- avg(平均值：求该列的平均值)
SELECT AVG(列名) FROM 表名;
4.4.5 分组查询
注意：
查询的列必须是分组的列
分组查询的信息都是组的信息，不能查到个人的信息，其中查询的信息是通过聚合函数得到的
/*分组查询*/
-- 查询的列必须是分组的列
select 分组列名，聚合函数1,聚合函数2 from 表名 group by 该分组列名 select gander,avg(age) from stu group by gander;
-- 分组查询条件用 having
select 分组列名，聚合函数1,聚合函数2 from 表名 group by 该分组列名 having select gander,avg(age) from stu group by gander having gander='男';
4.4.6 limit
如果一个参数，则从开始到这个数的记录
例如limit 3 ，则查找三条记录
SELECT id,name,age,gander FROM student limit 3如果两个参数，则从这个数开始(不算这个数)
例如limit 3,3 ，则查找第4,5,6
SELECT id,name,age,gander FROM student limit 3,3
/*分组查询*/
SELECT id,name,age,gander FROM student limit 3
SELECT id,name,age,gander FROM student limit 3,4
五、多表查询
内连接
-- 内连接 inner join on
/** **内连接查询操作只列出与连接条件匹配的数据行，使用INNER JOIN或者直接使用JOIN 进行连接。
**内连接可以没有连接条件，没有条件之后的查询结果，会保留所有结果（笛卡尔积）。
**在连接条件中使用等于号（=）运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括 其中的重复列。
* 也就是根据条件，找到表 A 和 表 B 的数据的交集 */
SELECT * from teacher t inner JOIN course c on t.id = c.t_id
左外连接
-- 左外联接 left join on
/**
** 利用笛卡尔积原理：简单来说就是两个集合相乘的结果，集合A和集合B中任意两个元素结合在一起
** 左外连接不只列出与连接条件相匹配的行，而且还加上左表中所有符合搜索条件的数据行。
** 根据条件，用右表（B）匹配左表（A），能匹配，正确保留，不能匹配其他表的字段都置空 Null。
* 也就是，根据条件找到表 A 和 表 B 的数据的交集，再加上左表的数据集
*/
SELECT * from teacher t LEFT JOIN course c on t.id = c.t_id
右外连接
-- 右外连接 right join on
/**
** 右外连接不只列出与连接条件相匹配的行，而且还加上右表中所有符合搜索条件的数据行。
** 根据条件，用左表（A）匹配右表（B），能匹配，正确保留，不能匹配其他表的字段都置空 Null。
* 也就是，根据条件找到表 A 和 表 B 的数据的交集，再加上右表的数据集 */
SELECT * from teacher t right JOIN course c on t.id = c.t_id
子查询
-- 子查询
/**where型子查询： 将查询结果当条件 */
-- where 型子查询，如果是 where 列 =（内层 sql） 则内层 sql 返回的必须是单行单列，单个值。
-- where 型子查询，如果是 where 列 in（内层 sql） 则内层 sql 返回的必须是单列，可以多行。
select * from student where id in (select distinct s_id from scores where score>90);
select * from student where id=(select s_id from scores GROUP BY s_id ORDER BY
sum(score) desc limit 1);
/**from型子查询： 查询结果集在结构上可以当成表看，那就可以当成临时表对他进行再次查询*/
select * from (select s.id id,s.name name,e.score score,c.`name` cname from student s LEFT JOIN scores e on s.id=e.s_id left join course c on e.c_id=c.id where c.`name`='数 学'ORDER BY e.score desc limit 5) t ORDER BY t.score;
其他查询
-- 查询空值字段
select 列名（*） from 表名 where 列名 is (not) null
select * from course where t_id is null;
-- 查询不为空值字段
select * from course where t_id is not null;
-- 查询字段特定行 in
select * from 表名 where 列名 （not） in （1，3，4）
select * from author where name in(1,3,5)
/**查询第一行、3行、5行*/
select * from author where name not in(1,3,5)
/**查询不是第一行、3行、5行*/
-- 查询不等于!= <>
select * from course where t_id !=5;
select * from course where t_id <>5;
六、建表约束
| 约束名称 | 描述 |
|---|---|
| NOT NULL | 非空约束 |
| UNIQUE | 唯一约束，取值不允许重复 |
| PRIMARY KEY | 主键约束(主关键字)，自带非空、唯一、索引 |
| FOREIGN KEY | 外键约束(外关键字) |
| DEFAULT | 默认值(缺省值) |
6.1.1 not null非空约束
6.1.2 unique 唯一约束
实现方法
1、表的定义最后施加
2、字段定义的最后施加
6.1.3 default 默认约束
如果里面为空，会有 一个默认值
6.1.4 primary key 主键约束
主键只能有一个，自带非空和唯一
但是可以由单个或者多个字段构成联合主键
实现方法
1、单个字段作为主键，写在字段后面
2、单个字段做主键，写在所有字段完成之后
3、多个字段做主键，写在所有字段完成之后
6.1.5 auto_increment 自增约束
需要配合主键使用，非主键不能自增
6.1.6 foreign key 外键约束
外键可以建立多个，多个外键接着写就行了
外键会产生的效果：
1、删除表时，如果不删除引用外键的表，被引用的表不能直接删除2、外键的值必须来源于引用的表的主键字段
语法
FOREIGN KEY [column list] REFERENCES [primary key table] ([column list]);
CREATE TABLE IF NOT EXISTS book(
book_id int PRIMARY key auto_increment, book_name varchar(50) ,
aut_id int FOREIGN KEY REFERENCES author(aut_id),
book_price decimal(8,2)
);
七、MySQL常用函数
7.1 数值型函数
- ABS(x)， 返回x的绝对值
- CEILING(x) ，返回大于x的小整数值，向上取整
- FLOOR(x) ，返回小于x的大整数值，向下取整
- ROUND(x,y) ，返回参数x的四舍五入的有y位小数的值 四舍五入
- TRUNCATE(x,y) ，返回数字x截短为y位小数的结果
- PI() ， 返回pi的值（圆周率）
- RAND() ，返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定 的值
- GRFATEST(x1,x2,…xn)，返回集合中最大的值
- LEAST(x1,x2,…xn)，返回集合中最小的值
/*数值函数*/
-- 向上取整
SELECT CEILING(3.15);
-- 向下取整
SELECT FLOOR(3.15);
-- 取最大值
SELECT GREATEST(1,2,3);
-- 取最小值
SELECT LEAST(1,2,3);
-- π
SELECT PI();
-- 截数
SELECT TRUNCATE(3.255555,2);
-- 四舍五入
SELECT ROUND(3.255555,2);
-- 随机数
SELECT RAND();
-- 如何使用？例如将年龄取整
SELECT NAME,FLOOR(age) FROM student;
7.2 聚合函数
- COUNT(col) 统计查询结果的行数
- MIN(col) 查询指定列的小值
- MAX(col) 查询指定列的大值
- SUM(col) 求和，返回指定列的总和
- AVG(col) 求平均值，返回指定列数据的平均值
7.3 字符串函数
- LENGTH(s) ，计算字符串长度函数，返回字符串的字节长度
- CONCAT(s1,s2…,sn) ，合并字符串函数，返回结果为连接参数产生的字符串，参数可以是一个或多 个
- LOWER(str) ，将字符串中的字母转换为小写
- UPPER(str) ，将字符串中的字母转换为大写
- LEFT(str,x) ，返回字符串str中左边的x个字符
- RIGHT(str,x) ，返回字符串str中右边的x个字符
- TRIM(str) ，删除字符串左右两侧的空格
- REPLACE ，字符串替换函数，返回替换后的新字符 REPLACE(name,‘白’,‘黑’)
- SUBSTRING 截取字符串，返回从指定位置开始的指定长度的字符换
- REVERSE(str) ，返回颠倒字符串str的结果
/*字符串函数*/
SELECT NAME,id FROM student WHERE LENGTH(name) = 9;
-- 合并字符串函数
SELECT CONCAT(name,'今年',age,'岁了！') FROM student;
-- 字符串转小写
SELECT LOWER(name) FROM student;
-- 字符串转大写
SELECT UPPER(name) FROM student;
-- 删除两边的空格
SELECT trim(name) FROM student;
-- 替换字符
SELECT replace(name,'白','黑') FROM student;
-- 截取字符串
SELECT SUBSTRING(name,1,1) FROM student;
-- 颠倒字符串
SELECT REVERSE(name) FROM student;
7.4 日期和时间函数
-
CURDATE 和 CURRENT_DATE 两个函数作用相同，返回当前系统的日期值
-
CURTIME 和 CURRENT_TIME 两个函数作用相同，返回当前系统的时间值
-
NOW 和 SYSDATE 两个函数作用相同，返回当前系统的日期和时间值
-
UNIX_TIMESTAMP 获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数
时间戳：是一个long型的整形，代表了从1970年零点零分零秒格林兰治时间到现在的毫秒数
时间戳的好处：在世界上的任何一个国家都一样
日期
-
FROM_UNIXTIME 将 UNIX 时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数
-
MONTH ，获取指定日期中的月份
-
MONTHNAME， 获取指定日期中的月份英文名称
-
DAYNAME ，获取指定曰期对应的星期几的英文名称
-
DAYOFWEEK ，获取指定日期对应的一周的索引位置值
-
WEEK ，获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53
-
DAYOFYEAR ，获取指定曰期是一年中的第几天，返回值范围是1~366
-
DAYOFMONTH ，获取指定日期是一个月中是第几天，返回值范围是1~31
-
YEAR 获取年份，返回值范围是 1970〜2069
-
DATE_ADD 和 ADDDATE ，两个函数功能相同，都是向日期添加指定的时间间隔
-
DATE_SUB 和 SUBDATE ，两个函数功能相同，都是向日期减去指定的时间间隔
-
ADDTIME 时间加法运算，在原始时间上添加指定的时间 SUBTIME 时间减法运算，在原始时间上减去指定的时间
-
DATEDIFF ，获取两个日期之间间隔，返回参数 1 减去参数 2 的值
-
DATE_FORMAT ，格式化指定的日期，根据参数返回指定格式的值
/*时间函数*/
-- 返回当前系统的日期
SELECT CURRENT_DATE();
SELECT CURDATE();-- 缩写
-- 返回当前的时间
SELECT CURRENT_TIME();
SELECT CURRENT_USER();
-- 既返回时间也返回日期
SELECT NOW();
SELECT SYSDATE();
-- 返回一个时间戳
SELECT UNIX_TIMESTAMP();
-- 获取月份
SELECT vip_name,MONTH(birthday) FROM vip;
-- 打印指定表中月份的英文名称
SELECT vip_name,MONTHNAME(birthday) FROM vip;
-- 获取指定日期对于星期几的英文
SELECT vip_name,DAYNAME(birthday) from vip;
-- 获取这一年的第几天
SELECT vip_name,DAYOFYEAR(birthday) FROM vip;
-- DAYOFMONTH ，获取指定日期是一个月中是第几天
SELECT vip_name,DAYOFMONTH(birthday) FROM vip;
-- YEAR 获取年份
SELECT vip_name,YEAR(birthday) FROM vip;
SELECT vip_name,YEAR(birthday) year,MONTH(birthday) month,DAYOFMONTH(birthday) day FROM vip;
SELECT vip_name,YEAR (birthday),MONTH (birthday),DAYOFMONTH(birthday) FROM vip
WHERE MONTH (birthday) = MONTH(NOW());
-- DATE_ADD往后加几天日期
SELECT vip_name,birthday,DATE_ADD(birthday,INTERVAL 1 day) FROM vip;
-- DATEDIFF()获取两个日期间隔的时间
SELECT vip_name,birthday,DATEDIFF(NOW(),birthday) FROM vip;
-- **DATE_FORMAT ，格式化指定的日期
SELECT vip_name,birthday,DATEDIFF(NOW(),birthday) FROM vip;
SELECT vip_name,birthday,DATE_FORMAT(birthday,"%Y") FROM vip;-- 年四位
SELECT vip_name,birthday,DATE_FORMAT(birthday,"%y") FROM vip;-- 年两位
SELECT vip_name,birthday,DATE_FORMAT(birthday,"%Y-%m-%d %H:%i:%s") FROM vip;-- 年月日拼起来
SELECT vip_name,birthday,DATE_FORMAT(birthday,"%Y-%m-%d %H时%i分%s秒") FROM vip;-- 年月日拼起来
7.5 流程控制函数
流程控制类函数可以进行条件操作，用来实现SQL的条件逻辑，允许开发者将一些应用程序业务逻辑转 换到数据库后台，列举如下：
- IF(test,t,f) 如果test是真，返回t；否则返回f
- IFNULL(arg1,arg2) 如果arg1不是空，返回arg1，否则返回arg2 NULLIF(arg1,arg2) 如果arg1=arg2返回NULL；否则返回arg1
- CASE WHEN[test1] THEN [result1]…ELSE [default] END 如果testN是真，则返回resultN，否则 返回default
- CASE [test] WHEN[val1] THEN [result]…ELSE [default]END 如果test和valN相等，则返回 resultN，否则返回default
7.6 加密函数
加密函数主要用于对字符串进行加密，常用的几个列举如下：
- ENCRYPT(str,salt) 使用UNIXcrypt()函数，用关键词salt(一个可以惟一确定口令的字符串，就像钥 匙一样)加密字符串str
- ENCODE(str,key) 使用key作为密钥加密字符串str，调用ENCODE()的结果是一个二进制字符串， 它以BLOB类型存储
- MD5() 计算字符串str的MD5校验和
- PASSWORD(str) 返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使 用不同的算法。
- SHA() 计算字符串str的安全散列算法(SHA)校验和
八、MySQL视图
概念
视图其相当于从原来的数据表中获取部分数据，然后新建一个只可创建、查询和删除的新表来存放这些数据（一般情况下），可以理解成把想要的数据部分截图下来保存，供以后查询用，此时视图只是为了满足某些数据查询而建立的对象。
其和表的区别如下：
1.举个例子，你们公司使用了别人的开票系统，开票需要一些信息，你能把用户信息，订单信息等等这些表的权限给那个开票公司吗，就算能给，私密信息比如密码能给吗？肯定不行
解决的方案就是使用视图，把他需要的数据搞成一张表，给他个权限，他只能看这个视图的数据，而不能看整个表的数据。
8.1 创建视图
基本格式：
create view 视图名(列名1, 列名2, ...) as select语句;
/*创建视图*/
-- 只查询年龄大于20的学生信息
CREATE VIEW otherstudent(id,name,age) as
SELECT id,name,age FROM student WHERE age > 20;
-- 查看这个视图
SELECT * FROM otherstudent;
-- 视图的数据会随着表的数据改变而改变,在表中插入新的数据
SELECT * FROM otherstudent;
8.2 显示视图
基本格式：
show create view 视图名;
在视图创建完成后，就可以把其当做一个只读的表来用，此时就可以正常的进行查询了，举例：
mysql> create VIEW temp(id,name,age) as SELECT id,name,age from student where age > 20
#创建视图temp，里面记录了student表的id，name，age
mysql> select * from temp;
#发现可以正常查询
-- 创建一个新用户
create user wjm@'%' identified by 'wjm123';
-- 给用户赋个权限，把test里面otherstudent的权限赋给新创建的用户
-- 创建一个新的连接，输入创建的用户名和密码就可查看这个视图
grant all on test.otherstudent to wjm;
revoke all on test.temp from zn;
drop user zn;
8.3 删除视图
基本格式：
drop view 视图名;
九、MySQL事务
(1)、在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
(2)、事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
(3)、事务用来管理 insert,update,delete 语句。
9.1 事务四大特征
9.1.1 原子性（Atomicity）
一个事务（transaction）中的所有操作，是一个整体操作，要么全部完成，要么全部失败，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
（2）一致性（Consistency）
在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。(比如：A向B转账，不可能A扣了钱，B却没有收到)
（3）隔离性（Isolation）
数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。（比u人：A正在从一张银行卡里面取钱，在A取钱的过程中，B不能向这张银行卡打钱）
（3）持久性（durability）
事务的执行使得数据库从一种正确状态转换成另外一种正确状态
隔离级别
（1）读未提交：read uncommitted
（2）读已提交：read committed
（3）可重复读：repeatable read
（4）串行化：serializable
| 脏读 | 不可重复读 | 幻读 | |
|---|---|---|---|
| Read uncommitted | √ | √ | √ |
| Read committed | × | √ | √ |
| Repeatable read | × | × | √ |
| Serializable | × | × | × |
A得数据未提交，B已经读取到，这种数据就是脏读。
A多次读取数据同一个数据，每次值都不一样，这种数据是不可重复读。
A多次读取数据同一个数据，第一次没有值，第二次出现了值， 这种数据是幻读。
AB事务操作数据，A完成之前，B再等待；直到A完成后，B再执行。这是串行化。（事务A与事务B串 行，而不并发）
SET transaction isolation level read uncommitted; -- 读未提交 隔离级别最低
SET transaction isolation level read committed; -- 读已提交 不可重复读
set transaction isolation level repeatable read; -- 可重复度 针对delete和insert
set transaction isolation level serializable; -- 串行化
（4）持久性（Durability）
事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
9.1.2 事务回滚、提交
-- UNSIGNED代表无符号数，不能是负数
create table user(
id int primary key auto_increment,
name VARCHAR(20),
balance DECIMAL(10,2) UNSIGNED
);
insert into user VALUES (1,'楠哥',200);
insert into user VALUES (2,'楠哥老婆',50000);
-- 转账业务，必须都成功，或者都失败，所以不能一句一句执行，万一执行了一半，断电了咋办
-- 所以要编程一个整体
-- 都成功
-- 启动事务用begin或者 start transaction都行;
start transaction;
-- 进行转账
UPDATE user set balance = balance - 200 where id = 1;
UPDATE user set balance = balance + 200 where id = 2;
-- 提交select语句
commit;
-- 都失败
start transaction;
UPDATE user set balance = balance - 200 where id = 1;
UPDATE user set balance = balance + 200 where id = 2;
rollback;
实现原理的简单介绍
mysql每执行一条语句记录一条日志， 1、start transaction，先记个日志，真正执行执行。
2、UPDATE user set balance = balance - 200 where id = 1，先记个日志，真正执行。 2.1 如果此时断电了，当然不能继续执行了，过了一会来电了，启动mysql会检查日志，发现有个 事务没有执行完毕，没有commit，就会安装反向的操作把他回滚了。
3、UPDATE user set balance = balance + 200 where id = 2，先记个日志，真正执行。 4、如commit，记个记录，执行，结束了，日志就能删除了。如果rollback，就会按照日志反向操作， 回滚。
十、MySQL索引
简介
- MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
- 索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引， 但这不是组合索引。组合索引，即一个索引包含多个列。
- 创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
- 实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
- 缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、 UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。 建立索引会占用磁盘空间的索引文件。
10.1 普通索引
创建索引
这是最基本的索引，它没有任何限制。它有以下几种创建方式：
CREATE INDEX indexName ON mytable(username(length));
create index myDeptIndex on detail(dept_id);
修改表结构(添加索引)
ALTER table tableName ADD INDEX indexName(columnName)
创建表的时候直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);
删除索引的语法
DROP INDEX [indexName] ON mytable;
10.2 唯一索引
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length))
修改表结构
ALTER table mytable ADD UNIQUE [indexName] (username(length))
创建表的时候直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username(length))
);
10.3 使用ALTER 命令添加和删除索引
有四种方式来添加数据表的索引：
-
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):
该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
-
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
-
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
-
**ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)😗*该语句指定了索引为 FULLTEXT ，用于全文索引。
以下实例为在表中添加索引。
mysql> ALTER TABLE testalter_tbl ADD INDEX (c);
你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:
mysql> ALTER TABLE testalter_tbl DROP INDEX c;
10.4 使用 ALTER 命令添加和删除主键
主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：
mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
你也可以使用 ALTER 命令删除主键
10.5 索引优化策略
-
索引列上不能使用表达式或者函数
-
联合索引，如何选择索引的顺序
-
经常会被使用到的列优先
-
选择性高的列优先
-
宽度小的列优先
-
索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这 一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
- 索引列排序
如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的
- like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会 使用索引而like “aaa%”可以使用索引
十一、数据库设计
三范式
三范式的作用：能极大减少数据冗余。
- 第一范式：要求有主键，并且要求每一个字段原子性不可再分。
譬如：联系方式只写手机号、微信、qq等多个方式，如果联系方式将这些全部都写在一个字段， 那么这就违背了第一范式正确写法应该是联系方式只写一个手机号或者其他，而其他联系方式再另 起一个字段。
- 第二范式：要求所有非主键字段完全依赖主键，不能产生部分依赖
譬如：学生表里有学生个人信息和上的课程相关信息。学生信息通过学生id都可找到，但学生上的 课程的相关信息，和学生id没有依赖关系，通过课程id找到相应的课程。一个同学多个课程，都被 显示出来出现数据冗余的现象，学生id和课程id没有任何联系，同时和这两者有关联的数据只有成 绩，那么这个成绩字段就是部分依赖。
- 第三范式：所有非主键字段和主键字段之间不能产生传递依赖。
譬如：学生表里：学生信息通过学生id都可找到，但学生上的课程名称，和学生id没有依赖关系可 以通过课程id找到相应的课程，那么学生表里的课程名称可以写到课程表里，通过其他手段让其有 关联。
十二、表关系
一对一
一个表和另一张表存在的关系是一对一，此种设计不常用，应为此种关系经常会将多张表合并为一张 表。
一对多
分两张表存储，在多的一方添加外键，这个外键字段引用另一方中的主键字段
多对多
分三张表存储，在学生表中存储学生信息，在课程表中存储课程信息，在成绩表中存储学生和课程的关 系信息
十三 、JDBC
由于JDBC内容略多，知识点放在了下期文章，尽请关注！！！
354
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
