MySQL基础

SQL通用语法

• 注释：-- 注释内容
• Mysql数据库不区分大小写。
• sql语句可多行或者单行书写，但是每个语句要以分号结束。

SQL分类

• DDL（Data Definition Language）数据库模式定义语言
  用来定义数据库对象：数据库，表，列等。 关键字：create、drop、alter等
• DML（Data Manipulation Language）数据操纵语言
  用来对数据库中的数据进行增删改。 关键字：insert、delete、update等
• DQL（data query language）数据查询语言
  用来查询数据库表中的记录（数据）。关键字：select、where等
• DCL（data control language）数据库控制语言
  用来定义数据库访问权限和安全级别。

四类语句

DDL

操作数据库

(CRUD)

C(create)创建

• 普通创建一个数据库test1
  
  上面的普通创建数据库不太好，因为如果你要创建的数据库已经存在那么就会报错。所以下面这个是在创建数据前先判断有没有这个数据库。
  

R（retrieve）查询

• 查询所有数据库
  
• 查看对应数据库的字符集
  
  
• 创建数据库test3，并判断其是否存在，并指定字符集为gbk.
  create database if not exists test3 character set gbk;
  

U（update）修改

• 修改数据库的字符集

mysql> alter database test3 character set UTF8;

D（delete）删除

• drop删除数据库，这是一个危险的操作请谨慎使用。

mysql> drop database test3;

和创建一样如果本来没有test3而我们去删除就会报错，那么就有方法如果存在test3就删除，没有就不删除，让它不报错。
mysql> drop database if exists test3;

使用数据库

• 查询当前正在使用的数据库名称

mysql> select database();

• 使用数据库

mysql> use test1;

操作表

(CRUD)

C(create)创建

• 创建表

mysql> create table stu1(name varchar(10),age int);

• 复制表

mysql> create table 表名 like 被拷贝表名;

R（retrieve）查询

• 查询某个数据库中所有表名
  
• 查询表结构
  desc 表名

U（update）修改

修改表名

• 修改表字符集

mysql> alter table stu character set UTF8;

当我们要修改列有主键约束时时需要先删除主键，修改后再次添加主键。

• 修改列名称、类型

（改列名和类型一起）mysql> alter table stu change 老列名 新列名 新列类型;

• （只改列类型）

mysql> alter table stu modify 需要改类型的列名 类型;

• 填加一列

mysql> alter table stu add birthday date;

• 删除列

mysql> alter table 表名 drop 列名;

D（delete）删除

• drop table 表名；
• drop table if exist 表名；

DML

DML（Data Manipulation Language）数据操纵语言
用来对数据库中的数据进行增删改。 关键字：insert、delete、update等

添加数据

• insert into 表名（列名1，列名2，、、、） values（值1，值2，、、、）

注意：
列名和值要一一对应。
如果表名后不定义列名，则默认给所有的列明添加值。insert into 表名 values（值1，值2，、、、）
除了数字类型，其他类型需要使用引号""

删除数据

• DELETE FROM 表名 WHERE(NAME=“王”);

注意：
如果不加条件where那么会将表中记录全部删除。
如果想删除表中所有的数据，最好用TRUNCATE TABLE 表名;
因为用上面delete操作删除不指定条件方式删除表中所有的数据，表里有多少条记录就会执行多少次，而用TRUNCATE则是先将表删了，然后创建一个一模一样的空表。

修改数据

• update 表名 set 列名1=值1，列名2=值2，、、、（where 条件）；
  不加条件就所有都会被改

UPDATE people
SET
math = 26,
english = 23
WHERE
id = 111;

DQL（data query language）数据查询语言

DQL（data query language）数据查询语言
用来查询数据库表中的记录（数据）。关键字：select、where等

• 语法：
  select 字段集 from 表名 where 条件 group by 分组字段 having 分组之后的条件限定 order by 排序 limit 分页限定；

基础查询

• 多个字段查询

– 查询姓名和年龄
SELECT
name,
age
FROM
people;

• 去除重复 DISTINCT
  – 去除重复结果的查询

SELECT DISTINCT
NAME
FROM
people;
注意：去重要保证结果集字段要完全一样。

• 如果为空替换IFNULL(可能为空列名,替换值)

SELECT
id,
age,
IFNULL(name,“未命名”)
FROM
people;
这个替换是在显示时替换，并不是修改数据。

• 计算列 （可以使用四则运算进行运算）

SELECT
id,
age,
mathscore+englishscore
FROM
people;
这个替换是在显示时替换，并不是修改数据。

• 起别名 AS 新列名（新列名不用加引号）

SELECT
id,
age,
IFNULL(name,“未命名”) AS 新列名
FROM
people;、

条件查询

• 1：where子句后跟条件
• 2：运算符：

<,>,=,>=,<> (在sql中<>表示不等于，没有==运算符)
between…and（在什么之间包含边界）
IN（集合）（在这个集合里面）
like （模糊查询）（占位符：_代表单个字符，%任意多个字符）
IS NULL
与：and 或者 &&
或：or 或者 ||
非：not 或者 ！

使用运算符举例：

-- 查询年龄大于等于22人
SELECT 
	*
FROM
	people
WHERE
	age >= 22;

-- 查询年龄等于20岁
SELECT 
	*
FROM
	people
WHERE
	age = 20;

-- 查询年龄不等于20
SELECT 
	*
FROM
	people
WHERE
	age <> 20;

-- 查询年龄大于21小于24
SELECT
	*
FROM
	people
WHERE
	age > 21 AND age < 24; -- 用&&也行建议用AND

-- 年龄在21到24之间包含21和24
SELECT
	*
FROM
	people
WHERE
	age BETWEEN 21 AND 24;

-- 查询年龄22，和25岁人
SELECT
	*
FROM
	people
WHERE
	age = 22 OR age = 25; -- 也可以使用 age IN (22,25)

-- 查询name不为null
SELECT
	*
FROM
	people
WHERE
	`name`IS NOT NULL;
	-- 查询name里姓王的人（模糊查询like）
SELECT
	*
FROM
	people
WHERE
	name LIKE "王%";
	-- 查询姓名是两个字的人
SELECT
	*
FROM
	people
WHERE
	name LIKE "__";

-- 查询姓名中包含四的人
SELECT
	*
FROM
	people
WHERE
	name LIKE "%四%";

排序查询

• order by 子句

order by 排序字段1 排序方式1，排序字段2，排序方式2、、、；
排序方式：1：ASC升序排序（默认升序）2：DESCS降序排序
排序方式1和排序方式2的关系是：当排序方式1里数值相同时，这时按照排序方式2进行排序。多个排序条件当前面条件值相同时，才会用到后面的排序条件。

-- 所有人按照数学成绩进行降序排序
SELECT 
		*
FROM
		people
ORDER BY
		math
DESC;

-- 按照数学成绩排名，如果数学成绩一样则按照英语成绩排名
SELECT 
		*
FROM
		people
ORDER BY
		math
DESC,
		english
DESC;

聚合函数

将一列数据作为一个整体，进行纵向的计算

• count：计算个数（一般选择非空的列，主键）
• max：计算最大值
• min：计算最小值
• sum：求和
• avg：计算平均值
  聚合函数的计算是排除NULL值的！计算时不带NULL值。
  解决方案：1：使用不为NULL函数。2：使用IF NULL函数

-- 学生表里共有多少人
SELECT
		COUNT(NAME)
FROM
		people;

分组查询

• group by 分组字段
  分组字段：分组字段、聚合函数

-- 按照性别分组分别查看男女同学的平均分
SELECT
		sex,AVG(english)
FROM
		people
GROUP BY
		sex;
		-- 按照性别分组分别查看男女同学的平均分,要求英语分数低于40不参与分组
SELECT
		sex,AVG(english),COUNT(id)
FROM
		people
WHERE
		english > 40
GROUP BY
		sex;

• where和having的区别：
  1：where（分组之前进行限定，若不满足条件则不参与分组）/having（在分组之后进行限定，若不满足限定，则不会被查询出来）
  2：having后能跟聚合函数，而where后不可以有聚合函数。

分页查询

• limit 开始的索引，每页查询的条数
  开始的索引=（当前的页码-1）*每页显示的条数

-- 每页显示3条记录，显示第一页
-- 每页显示3条记录，显示第一页
SELECT 
		*
FROM
		people
LIMIT
		0,3;

-- 第二页
SELECT 
		*
FROM
		people
LIMIT
		3,3;

分页limit是一个“Mysql方言”,在sqlserver和Oracle里面不一样。

约束

对表中的数据进行限定，保证其正确性，有效性，完整性。

添加约束方法：
1：创建表时添加约束
2：创建表之后添加约束

非空约束：not null

非空约束的删除就是修改列ALTER

CREATE TABLE keytest1(
		id INT,
		name VARCHAR(5) NOT NULL -- 在创建表时添加非空约束
);
-- 删除非空约束，其实就是修改列
ALTER TABLE 
		keytest1
MODIFY
 id int NOT NULL;

唯一约束：unique

某一列的值不能有重复，可以有NULL，但是空值也只允许有一个。
删除唯一约束和上面删除非空约束不同，请看下面，不能用修改列方式来修改唯一约束。
创建表之后添加唯一约束和非空约束一样，通过修改列方式来添加唯一约束（但是创建表之后添加唯一约束，保证原本表中数据没有重复。）

CREATE TABLE keytest(
			id INT,
			phone VARCHAR(10) UNIQUE -- 设置电话号码为唯一约束
);
-- 删除唯一约束
ALTER TABLE keytest DROP INDEX phonenumber;

-- 在创建表之后添加唯一约束
ALTER TABLE keytest MODIFY phonenumber VARCHAR(19) UNIQUE;

主键约束：primar key

含义：

非空且唯一
一张表只能有一个主键
主键就是表中记录的唯一标识
请注意下面演示的删除主键

-- 设置id为主键
CREATE TABLE keytest(
			id INT PRIMARY KEY,
			name VARCHAR(10)
) CHARACTER SET
			UTF8;
			
-- 删除主键，无需指定index因为一个表里只有一个主键
ALTER TABLE
		keytest
DROP 
		PRIMARY KEY;
-- 创建表后添加主键
ALTER TABLE
		keytest
MODIFY
id INT PRIMARY KEY;

• 自动增长

如果某一列是数值类型的，可以使用 自动完成增长。
设置主键为自增时当要给添加数据，这时就可以写NULL添加进去自动自增（添加不是null）
添加数据是当然可以自己制定数据，但是当输入NULL这是判断添加是多少时根据的是列表的上一条数据（例如上一条是10那么这是添加NULL那么默认添加11），自动增长只跟上一条数据有关系。
第一行如果插入时填NULL，那么默认插入为1.

-- 设置主键id为自增
CREATE TABLE keytest(
			id INT PRIMARY KEY auto_increment,
			name VARCHAR(10)
) CHARACTER SET
			UTF8;
-- 根据上一行数据自加插入数据
INSERT INTO keytest(
			id,
			NAME
) VALUES(
		NULL,
		"w"
);
-- 删除自动增长
ALTER TABLE keytest MODIFY id INT;
-- 添加自动增长
ALTER TABLE keytest MODIFY id INT auto_increment;

外键约束：foreign key

外键约束是将两个表关联起来。
下面是将表employee的department_id关联department表的id。

CREATE TABLE employee(
			id INT PRIMARY KEY,
			name VARCHAR(10),
			department_id INT,
			CONSTRAINT emp_dep_id FOREIGN KEY (department_id) REFERENCES department(id) -- 外键约束
) CHARACTER SET
			UTF8;

一定要先有department才能有employee与它关联。
如果department没有的id那么employee就无法连接。外键可以为NULL，但不可以为关联表列不存在的值。
互相关联都不能修改

-- 删除表外键
ALTER TABLE employee DROP FOREIGN KEY emp_dep_id;

-- 创建表之后添加外键
ALTER TABLE 
		employee
ADD
CONSTRAINT emp_dep_id FOREIGN KEY (department_id) REFERENCES department(id);

级联操作

从上面我们知道如果两个表或者多个表关联后，那么设置外键约束那一列不能修改为关联那张表中那一列没有的数据，这时就需要级联操作，当修改其中一个表时，其他相关联的表都会跟着修改（一个里面删除其他里面都会删除，一个修改其他都会修改）。这里要说级联非常危险，要谨慎操作！

ALTER TABLE
表名
ADD CONSTRAINT
外键名
FOREIGN KEY
(外键字段名)
REFERENCES
主表名称(主表列名)
ON
UPDATE CASCADE – 级联更新
ON
DELETE CASCADE; – 级联删除
当然级联更新和删除可以选一个，也可以都要。更新是主表更新另一章表也更新，删除是主表删除键另外一张表全部删除（例如部门去除，部门里面的人也全部删除）

-- 添加级联操作
ALTER TABLE 
		employee 
ADD CONSTRAINT 
		emp_dep_id 
FOREIGN KEY 
		(department_id) 
REFERENCES 
		department(id) 
ON
UPDATE CASCADE -- 级联更新
ON
DELETE CASCADE; -- 级联删除

数据库的备份还原

• 命令行方式

多表查询

内连接查询

• 隐式内连接查询：利用where消除无用的数据
• 显式内连接：select 字段列表 from 表名1 inner join 表名2 on 条件;

SELECT 
		* 
FROM 
		route_all
INNER JOIN
		route_classify
ON
		route_all.cid = route_classify.cid;

• 内连接查询

1. 从哪些表查询数据
2. 条件是什么
3. 查询哪些字段

外连接查询

• 左外连接：select 字段列表 表名1 left
  左外连接查询的是左表所有数据以及其交集部分。

SELECT 
		route_all.*,route_classify.type 
FROM
		route_all
LEFT JOIN
		route_classify
ON
		route_all.cid = route_classify.cid;

• 右外连接
• 查询右边表的所有记录和左边表格它的交集

SELECT 
		route_all.*,route_classify.type 
FROM
		route_all
RIGHT JOIN 
		route_classify
ON
		route_all.cid = route_classify.cid;

• 左连接和一变右连接只要把左右位置一换，左外和右外是相对左右

子查询

• 查询中嵌套查询

SELECT 
		*
FROM
		route_all
WHERE
		price = (SELECT MAX(price) FROM route_all);

• 子查询结果单行单列

• 子查询结果单行单列

• 子查询是多行多列
  子查询可以作为一个虚拟表
  

事务

事务的基本概念

• 概念
  如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。
  假设要实现王一给王二站长500元

-- 王一给王二转账500
UPDATE account SET balance = balance - 500 WHERE `name`="王一";
UPDATE account SET balance = balance + 500 WHERE `name`="王二";

假设第一条语句执行成功但是第二条语句执行失败那么就会导致王一balance少500，但是王二balance没有增加5000

这是我们就需要讲两个语句当做事务处理。

• 开启事务
  START TRANSACTION; – 开启事务
• 回滚
  ROLLBACK; – 如果发现事务中有错则回滚事务
• 提交
  COMMIT; – 如果事务没有出错则提交，不提交当关闭窗口数据还是没有保存

START TRANSACTION; -- 开启事务 
-- 王一给王二转账500
UPDATE account SET balance = balance + 500 WHERE `name` = "王一";
UPDATE account SET balance = balance - 500 WHERE `name` = "王二";
-- ROLLBACK; -- 如果发现事务中有错则回滚事务
COMMIT; -- 如果事务没有出错则提交，不提交当关闭窗口数据还是没有保存

MySQL数据库中事务默认自动提交

• 事务提交的两种方式

1. 自动提交：MySQL中例如DML语句，每句事务都是自动提交的。
2. 手动提交：就是上面的方法，需要开启事务，然后commit提交。

• 修改默认提交方式

3. 查看默认提交方式（1是默认提交，0是手动提交）
4. 修改默认提交方式（修改后DML语句如果不写commit提交话，数据不会别修改）

 SET @@autocommit=0; -- 将事务提价方式设置为手动提交
UPDATE account SET balance = balance + 500 WHERE `name` = "王一"; 
COMMIT;

这里我们需要注意一点，不提交事务，数据没被修改，但是缓冲区数据已经修改，如果一直修改但是没有提交，当最后提交时会把前面修改多次全部提交，除非在提交之前关闭查询窗口，这样缓冲区数据清除。

隔离四大特征

• 原子性：是不可能分割的单位，要么同时成功，要么同时失败。
• 持久性：当事务在回滚之后，数据库会持久的保存数据。
• 隔离性：多个事务之间相互独立。
• 一致性：事务操作前后数据总量不变。

隔离级别

• 概念：多个事务之间是相互隔离，独立的。但是当多个事务操作同一个数据，则会引发一些问题，这个时候就需要事务的隔离级别。
• 存在的问题

1. 脏读：一个事务读取到另外一个事务中没有提交的数据
2. 不可重复读（虚读）：同一个事务中两次读取的数据不一样。（两个窗口里面分别两个事务，一个操作，另一个不提交，数据就不会变）
3. 幻读：一个事务操作数据表中所有数据，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。

• 事务的隔离级别

4. read uncommitted：读未提交的数据。（产生问题：脏读，幻读，不可重复读）
5. read committed：读已经提交（幻读，不可重复读）
6. repeatable read：可重复读（幻读）（MySQL默认隔离级别）
7. serializable：串行话（解决所有问题）（和多线程加锁一样，一个事务操作数据，另外一个事务无法操作！只有第一个事务提交后后面的事务才可以操作）

set global transaction isolation level 级别名；-- 设置隔离级别
select @@tx_isolation; -- 查询隔离级别

隔离级别从小到大安全级别越来越高，但效率却越来越低。
当设置了事务隔离级别只有重新打开的窗口设置才会生效！

DCL

DCL管理数据库，管理用户及授权。

• 管理用户

1. 添加用户：

CREATE USER "张三"@"localhost" IDENTIFIED BY "123456" 

2. 删除用户：

DROP USER "张三"@"localhost";

3. 查询用户：

use mysql;-- 切换到MySQL数据库
SELECT * FROM USER;-- 查询用户表

4. 修改用户密码：

UPDATE USER SET PASSWORD = `PASSWORD`("123456") WHERE USER = "张三":

• 权限的管理