MySQL使用入门

MySQL

---

登录：

1. mysql -uroot -p123
2. mysql -uroot -p密文显示密码
3. mysql -h127.0.0.1 -uroot -p123通过IP远程连接，这里是本地
4. mysql --host=127.0.0.1 --user=root --password=123

退出：

1. exit
2. quit

目录结构：
配置文件：my.ini
数据库：文件夹
表：文件，frm

通用语法：

1. 可以单行或多行书写，以；结尾
2. 可用空格和缩进来提高可读性
3. 不区分大小写，关键词建议大写
4. 单行注释： – 注释内容 or # 注释内容； --后必须加空格，#无所谓
5. 多行注释：/* */

SQL分类：

1. DDL(Data Definition Language)数据定义语言，用来定义数据库对象：数据库，表，列等。关键字：create，drop，alter等
2. DML(Data Manipulation Language)数据操作语言，用来对数据库中表的数据进行增删改。关键字：insert，delete，update等
3. DQL(Data Query Language)数据查询语言，用来查询数据库中表的记录。关键字：select，where等
4. DCL(Data Control Language)数据控制语言，用来定义数据库的访问权限和安全级别，及创建用户。关键字：grant，revoke等

基本数据类型：

• double(m, d): m是长度，d是多少个小数位
• timestamp：时间戳类型，如果不赋值或者赋值null，则默认使用当前的系统时间，来自动赋值。
• varchar(m)：m是最大字符个数，超出就会报错
  

---

DDL: CRUD

数据库的CRUD

创建

1. 创建自己的数据库：create database db1;，可以用同样的方法查看，同名数据库只能有一个

2. 创建的时候判断是否存在：create database if not exists db1;，这样即使重名了也不会报错，已经存在了就不会再创建了

3. 创建时设置字符集：create database db2 character set gbk;

4. 创建时判断是否存在并设置字符集：create database if not exists db3 character set gbk;

---

查询

1. 查看所有数据库名称：show databases;

2. 查看某个数据库的字符集：show create database 数据库名称;

---

修改

1. 修改字符集类型：alter database db3 character set utf8;，注意这里utf8没有-

---

删除

1. 删除数据库：drop database db3;

2. 判断是否存在再删除：drop database if exists db3;

---

使用数据库

1. 查询当前正在使用的数据库名称：select database();

2. 使用数据库：use db1;

---

数据库表的CURD

创建

create table 表名( 参数名，参数类型 )

 create table student(
     id int,
     name varchar(32),
     age int,
     score double(4,1),
     birthday date,
     insert_time timestamp
 );

复制表：create table 表名 like 被复制表名;

---

查询

展示所有表：show tables;

查看表结构：desc 表名;

查看表的字符集：show create table students;

---

修改

修改表名：alter table student rename to students;

修改字符集：alter table students character set gbk;

添加一列：alter table 表名 add 列名称 列类型;

修改列名称：alter table 表名 change 列名称 新列名称 新列类型;，类型也可以直接改掉

删除列：alter table 表名 drop 列名;

---

删除

删除： drop table 表名;

判断并删除：drop table if exist 表名;

---

DML: 增删表中数据

添加数据

添加输入：insert into 表名(列名1，...) values(值1，...);

注意：

1. 列名和值要一一对应
2. 如果表明后不定义列名，则默认给所有列添加值
3. 除了数字类型，其他类型需要用引号，如日期：“1000-2-1”

---

删除数据

删除表中数据：delete from 表名 where id=1;

删除表，并创建空表：truncate table 表名;

注意 ：如果不加where，就删除全部数据，但是不推荐用这个方法删除所有记录，因为是逐条删除，效率低。

---

修改数据

修改：update 表名 set 列名=新值,... where 条件;

注意：如果不加where就会修改全部数据

---

DQL: 查询表中的记录

基础查询

从表里查询所有：selesc * from 表名;

1. 多个字段的查询：select 字段名1，字段名2，... from 表名;，如果想查询所有，可以用*

2. 去除重复：dictinct 字段名1，字段名2，... from 表名;，如果是对各字段，全部重复才会被去除

3. 计算列：字段名1 + 字段名2，一般可以使用四则运算来计算，ifnull(a,b)可调整null，a是需要被判断的值，b是如果是null后替换的值

4. 起别名：字段名1 as 新名，字段名2 新名， 字段名1 + 字段名2 as 新名，可以使用as也可以使用空格

---

条件查询

1. 标准运算符：> < <= >= = <>, <>表示不等于，也可以用 !=

2. 在。。。之间: SELECT * FROM students WHERE age BETWEEN 11 AND 14;，包含边界

3. 在。。。里面：select * from students where age in (11,12,14, 16);

4. 是否为NULL：SELECT * FROM students WHERE birthday IS NULL;，还可用IS NOT NULL，但不能用=来判断

5. 与：SELECT * FROM students WHERE age >= 11 AND age <= 14;，也可以用&&

6. 或：SELECT * FROM students WHERE age=12 OR age=14;，也可以用|

7. 非：NOT或者!

8. 模糊查询：SELECT * FROM students WHERE NAME LIKE '刘_';，_是单个任意字符的模糊，%是零个或多个字符的模糊。

---

排序查询

语法：oreder by 字段1 排序方法1，字段2，排序方法2 … 先排序字段1，如果相同的话，再用后面的字段排序。ASC是升序，DESC是降序。

SELECT * FROM students ORDER BY age ASC, score DESC;

---

聚合函数

1. count：计算个数，出现null的话会不被计算个数，所以要么找一个不包含null的列，要么改变null

SELECT COUNT(NAME) FROM students;
SELECT COUNT(IFNULL(birthday,0)) FROM students;  # 改变null的方法
SELECT COUNT(id) FROM students;

2. max：SELECT MAX(age) FROM students;

3. min：SELECT MIN(age) FROM students;

4. avg：SELECT AVG(age) FROM students;，不考虑NULL，就当没有

注意：聚合函数的计算都会排除NULL值。

---

分组查询

语法：group by 分组字段;

SELECT NAME, AVG(age) Age FROM students WHERE age> 1 GROUP BY NAME HAVING COUNT(id)< 5;，这里也可以换名
where和having区别：

1. where在分组前，挑选符合条件的进行分组；having在分组后，对分成的组进行筛选
2. where不能加聚合函数，having可以加聚合函数（因为where的时候还有组，所以无法聚合，而having时已经有组了，所以可以聚合）

---

分页查询

语法：limit 开始的索引，每页查询的条数
SELECT * FROM students LIMIT 0,3;

注意：limit操作是mysql的一个"方言"

---

DCL管理用户

先切换到mysql工具库
查询user表，%表示任意用户可用

管理用户

添加用户：

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
CREATE USER 'zs'@'localhost' IDENTIFIED BY '123';
CREATE USER 'ls'@'%' IDENTIFIED BY '123';

修改密码：

UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');

---

授权

查询权限：

SHOW GRANTS FOR '用户名'@'主机名'

授予权限：

GRANT 授权列表 ON 数据库名.表名 TO '用户名'@'主机名';
GRANT SELECT, DELETE ,UPDATE ON db3.account TO 'lisi'@'%';
# 授予所有库所有表所有权限
GRANT ALL ON *.* TO 'lisi'@'%';

撤销权限：

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名'

---

约束

作用：对表中的数据进行限定，保证数据的正确性、有效性和完整性

非空约束

关键词：NOT NULL

声明为非空约束：

1. 可在创建的时候声明

 CREATE TABLE stua(
	id INT,
	NAME VARCHAR(20) NOT NULL -- name为非空
);

2. 可用修改来后期增加

ALTER TABLE stua MODIFY NAME VARCHAR(20) NOT NULL;

取消声明：

ALTER TABLE stua MODIFY NAME VARCHAR(20);

---

唯一约束

创建唯一约束：方法和非空约束相同

删除唯一约束：ALTER TABLE stu DROP INDEX phone_number;

关键词：UNIQUE

注意： NULL可以重复

---

主键约束

创建主键：

1. 创建时声明

CREATE TABLE stu(
	id INT PRIMARY KEY, -- 给id添加主键约束, 注意这里是两个关键词
	NAME VARCHAR(20)
)

2. 后期声明

ALTER TABLE stu MODIFY id INT PRIMARY KEY;

删除主键：ALTER TABLE stu DROP PRIMARY KEY;

创建主键自动增长：

CREATE TABLE stu(
	id INT PRIMARY KEY AUTO_INCREMENT, -- 给id添加主键约束, 注意这里是两个关键词
	NAME VARCHAR(20)
)

自动增长的使用：

INSERT INTO stu VALUE(NULL,'a'); # 从1开始，后面每次自动+1
INSERT INTO stu VALUE(10,'a');     # 从指定的值开始，后面每次自动+1

删除自动增长：

ALTER TABLE stu MODIFY id INT;  # 并不能删除主键

---

外键约束

作用：将两个表的某列关联到一起，节约空间，关联后不能随意改动，会受相互影响

创建外键约束：

create table 表名(
    ...
    外键列
    constraint 外键名称 foreign key（自身的外键列名称）references 主表名称（主表列名称）
);

删除外键：ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

创建表之后，添加外键

alter table 表名 add constraint 外键名称 foreign key（自身的外键列名称）references 主表名称（主表列名称）

级联更新和级联删除：
更新：值改变时两边一起边
删除：删除一个id时，另一个表里对应的所有都删除

alter table 表名 add constraint 外键名称 foreign key（自身的外键列名称）references 主表名称（主表列名称）ON UPDATE CASCADE # 更新
alter table 表名 add constraint 外键名称 foreign key（自身的外键列名称）references 主表名称（主表列名称）ON UPDATE DELETE # 删除

注意：外键可以为NULL，不可以为不存在的值

---

表之间的关系

一对多：在多的一方建立外键

多对多：建立一个中间表，分别放两个表的主键，两个主键作为新表的外键，可将两个主键一起做成一个复合主键

一对一：任意一方添加外键指向另一方的主键，但是要让外键唯一，加唯一约束

旅游案例：

# 一对多，旅游类型和线路
# 先创建少的
CREATE TABLE ly_tag (
	tag_id INT PRIMARY KEY AUTO_INCREMENT,
	tag_name VARCHAR(20) NOT NULL UNIQUE # 注意非空和唯一
)

DROP TABLE ly_tag;
DROP TABLE ly_route;
	
INSERT INTO ly_tag VALUES(NULL,'蜜月'), (NULL, '毕业');
# 多的	
CREATE TABLE ly_route(
	route_id INT PRIMARY KEY AUTO_INCREMENT,
	route_name VARCHAR(20),
	cid INT,
	FOREIGN KEY (cid) REFERENCES ly_tag(tag_id)
	)
	
INSERT INTO ly_route VALUES(NULL, '三亚', 1), (NULL, '夏威夷', 1), (NULL, '华山', 2), (NULL, '黄山', 2)

SELECT * FROM ly_tag;
SELECT * FROM ly_route;

# 用户表，和旅游路线是多对多的
CREATE TABLE t_user(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20) NOT NULL UNIQUE
	)
	
INSERT INTO t_user VALUES(NULL, '张三'), (NULL, '李四');

# 创建一个关联表，实现多对多
CREATE TABLE tag_favorite(
	rid INT,
	uid INT,
	PRIMARY KEY(rid,uid), # 建立复合主键
	FOREIGN KEY (rid) REFERENCES ly_route(route_id),
	FOREIGN KEY (uid) REFERENCES t_user(id)
)

---

三大范式

三大范式要求：

1. 第一范式（1NF）：每一列都是不可分割的原子数据项
2. 第二范式（2NF）：在第一范式基础上，非马属性必须完全依赖于候选码，即在1NF基础上消除非主属性对主码的部分函数依赖
3. 第三范式（3NF）：在第二范式基础上，任何非主属性不依赖于其他非主属性，即在2NF基础上消出传递依赖

函数依赖：属性组确定唯一值：学号，课程名称–>分数

完全依赖：A需要B属性组中全部的信息才能得出，称A完全依赖B

部分完全依赖：A需要B属性组中部分的信息就能得出，称A部分完全依赖B

传递参数依赖：A --> B, B --> C 通过A唯一确定B，通过B唯一确定C，则称C传递函数依赖于A。例如：学号–>系名，系名–>系主任

码：如果在一张表中一个属性或属性组，被其他所有属性所完全依赖(通过该确定唯一的其他)，则称这个属性为码。该表中码为(学号，课程)

主属性：码属性组中的所有属性

非主属性：除过码属性的属性

第二范式就是要消出部分依赖

---

备份和还原

备份

命令行：

mysqldump -uroot -proot db1 > d://a.sql

图形化工具：

---

还原

命令行：

create database db1;
use db1;
source d://a.sql;

图形化工具：

---

多表查询

内连接

基本思路：

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

隐式内连接：
使用where条件来消除无用的数据

SELECT
	t1.`name`, t1.`dep_id`
FROM
	employee t1, 
	depart t2
WHERE
	t2.`id` = t1.`dep_id`

显示内连接：
INNER可以省略

SELECT 字段列表 FROM 表1 INNER JOIN 表2 ON 表1.`字段1` = 表2.`字段2`
SELECT * FROM employee INNER JOIN depart ON employee.`dep_id` = depart.`id`
SELECT * FROM employee JOIN depart ON employee.`dep_id` = depart.`id`

---

外连接

左外连接：查询的是左表的所有数据和交集部分，如果左边有右边没有，那么也会显示

SELECT 字段列表 FROM 表1 LEFT JOIN 表2 ON 表1.`字段1` = 表2.`字段2`

右外连接：

SELECT 字段列表 FROM 表1 RIGHT JOIN 表2 ON 表1.`字段1` = 表2.`字段2`

---

子查询

就是查询中套着查询

SELECT * FROM employee WHERE employee.`dep_id` = (SELECT MAX(dep_id) FROM employee)
SELECT * FROM dept t1, (SELECT * FROM emp WHRER emp.`join_data` > '2011-11-11') t2 # 可作为虚拟表

---

事务

基本内容：
START TRANSACTION：事务开始
COMMIT：提交事务，改动生效
ROLLBACK：回滚，回到事务开始时的状态

注意：
DML默认提交

设置默认：
1代表自动提交，0代表手动提交

SELECT @@autocommit = 1; # 设置为自动，不需要commit
UPDATE money SET hava=20;

SELECT @@autocommit = 0;
UPDATE money SET hava=30;
COMMIT; # 不写的话窗口关掉就会回滚

四大特征：

1. 原子性：不可分割的最小单位，要么同时成功，要么同时失败
2. 持久性：当事务提交或回滚后，数据库会持久化的保存数据
3. 隔离性：多个事务之间相互独立
4. 一致性：事务操作前后数据总量不变

同时处理的问题：
如果多个事务同时处理一批数据，则会引发一些问题，存在的问题：

1. 脏读：一个事务，读取到另一个事务中还没有提交的数据
2. 不可重复读(虚读)：在同一事务中，两次读取到的数据不一样
3. 幻读：一个事务操作数据表中所有的记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改

隔离级别：

隔离语句	隔离能力	产生的问题
read uncommitted	读未提交	脏读、不可重复读、幻读
read committed（Oracle默认）	读已提交	不可重复读、幻读
repeatable read（MySQL默认）	可重复读	幻读
serializable	串行化	无

隔离级别从小到大安全性越来越高，但是效率越来越低

查询隔离级别：select @@tx_isolation;

设置隔离级别：SET GLOBAL TRANSACTION ISOLATION LEVEL 隔离语句，重启才会生效