MySQL

SET character_set_client = utf8;
SET character_set_results = gb2312;
SET character_set_connection = utf8;

CREATE DATABASE 数据库名;
drop database <数据库名>; --删除数据库
USE test;--使用数据库

SHOW TABLES;

CREATE TABLE student3(
	id INT,
	NAME VARCHAR(20),
	birth DATE,
	insert_time TIMESTAMP
	); -- 创建表
	
CREATE TABLE student1 LIKE student3;-- 复制表

DROP TABLE student3;-- 删除表

DROP TABLE IF  EXISTS student3;	


RENAME TABLE student1 TO person;  -- 修改表名

ALTER TABLE student ADD remark VARCHAR(20); -- 追加列

ALTER TABLE student MODIFY remark INT; -- 修改列属性

ALTER TABLE student CHANGE  insert_time VARCHAR(20);-- 改变列

ALTER TABLE student DROP mark;-- 删除列

ALTER TABLE student CHARACTER SET utf8;



-- DML:增删改表中数据

INSERT INTO person(id,NAME,birth,insert_time) VALUES (2,'cai','2020-10-10','2020-10-10 00:50:36');

INSERT INTO person VALUES(2,'qi',NULL,NULL);

INSERT INTO person (id,NAME,) VALUES(3,'lian',NULL, NULL);-- 插入部分必须写列名

DELETE FROM person WHERE NAME IS NULL;

DELETE FROM person WHERE id = 1;

TRUNCATE TABLE person; -- 删除所有记录

UPDATE person SET id=3,NAME='cai' WHERE id=1;-- 改数据


--  DQL：查询表中的记录

CREATE TABLE student(
	id INT PRIMARY KEY,
	NAME VARCHAR(20) ,
	age INT NOT NULL,
	sex VARCHAR(20),
	address VARCHAR(20),
	math INT,
	english INT 
	CONSTRAINT per_stu FOREIGN KEY (NAME) REFERENCES student(NAME)
	);

INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES (1,'马云',55,'男','
杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),(4,'柳岩
',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港
',99,99),(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);

SELECT * FROM student;

SELECT DISTINCT id,NAME FROM student;-- 去重查询

SELECT id  账号, age 年龄,id + IFNULL(age, 0)  aa FROM student; -- 运算和起别名

-- 条件查询
SELECT score FROM student WHERE score !=80 AND age = 1;
SELECT score FROM student WHERE score BETWEEN 20  AND 30;

SELECT score FROM student WHERE score  IN (80.5,80);

SELECT score FROM student WHERE birth IS NULL;

-- 模糊查询 _占一字符，%任意字符
SELECT NAME FROM student WHERE NAME LIKE '%c%';
 
-- 排序查询 如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。
SELECT id ,score FROM  student ORDER BY id DESC,score ASC;

-- 聚合函数：将一列数据作为一个整体，进行纵向的计算
-- 特点：排除null
SELECT COUNT(IFNULL(english,0)) FROM student ;
SELECT MAX(math) FROM student;
SELECT MIN(english) FROM student;
SELECT SUM(english) FROM student;
SELECT AVG(math) FROM student;

-- 分组查询,分组之后查询字段为：分组字段、聚合函数
-- where 和 having 的区别？
	1. WHERE 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来
	2. WHERE 后不可以跟聚合函数，having可以进行聚合函数的判断。一般会给聚合函数起别名
SELECT sex,COUNT(id),AVG(math) FROM student GROUP BY sex;

SELECT sex,COUNT(id) 人数,AVG(math) FROM student WHERE math>70 GROUP BY sex HAVING  人数 >2;

-- 分页查询：limit 是一个MySQL"方言"
SELECT * FROM student LIMIT 0,3;
SELECT * FROM student LIMIT 6,3;

-- 约束：

-- not null
DELETE FROM student WHERE english IS NULL;
ALTER TABLE student MODIFY english VARCHAR(20) NOT NULL;
ALTER TABLE student MODIFY english VARCHAR(20);

-- unique:mysql中，唯一约束限定的列的值可以有多个null
ALTER TABLE student MODIFY id INT UNIQUE;

-- 删除唯一性（删除唯一索引）
ALTER TABLE student DROP INDEX id;

-- 主键：非空且唯一，一张表只能有一个字段为主键
ALTER TABLE student MODIFY id INT PRIMARY KEY;
ALTER TABLE sutdent2 DROP PRIMARY KEY;
-- 自动增长
ALTER TABLE student MODIFY id INT AUTO_INCREMENT;
ALTER TABLE student MODIFY id INT;



-- 外键：
SELECT * FROM  person;

ALTER TABLE student ADD CONSTRAINT stu_per FOREIGN KEY (NAME) REFERENCES person(NAME);

alter table sutdent drop foreign key stu_per;
-- 级联
ALTER TABLE student ADD CONSTRAINT stu_per FOREIGN KEY (NAME) REFERENCES person(NAME) ON UPDATE CASCADE ON DELETE


-- 多表之间的关系：
-- 一对一：一个表中的一条记录对应另一个表的一条记录，如身份证表和人表
-- 一对多：一个表中的一条记录可以对应另一个表中的多条纪录，如员工和部门表，一个部门表记录对应多个员工表中的记录
-- 多对多：两个表中的一条记录互相对应另一个表的多条记录，如课程表和学生表，学生可以选多个课程，课程也可以对应多个学生


-- 数据库设计范式：
-- 1NF：每一列都是不可分割的原子数据项
-- 2NF：在1NF基础上消除非主属性对主码的部分函数依赖
几个概念：
	1. 函数依赖：A-->B,如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖于A
		例如：学号-->姓名。  （学号，课程名称） --> 分数
	2. 完全函数依赖：A-->B， 如果A是一个属性组，则B属性值得确定需要依赖于A属性组中所有的属性值。
		例如：（学号，课程名称） --> 分数
	3. 部分函数依赖：A-->B， 如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中某一些值即可。
		例如：（学号，课程名称） --> 姓名
	4. 传递函数依赖：A-->B, B -->C . 如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递函数依赖于A
		例如：学号-->系名，系名-->系主任
	5. 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码
		* 主属性：码属性组中的所有属性
		* 非主属性：除过码属性组的属性
-- 3NF:在2NF基础上消除传递依赖

-- 数据库备份 
mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
source 文件路径


-- 多表查询

-- 隐式内连接：使用where条件消除无用数据
select * from person,student;

select 
	t1.`birth`,
	t2.`math`
from
	person t1, 
	student t2
where
	t1.`birth`=t2.`address`;

--  显示内连接
select * from student inner join person on student.`address` = person.`birth`;



-- 外连接
-- 左外连接：左表所有数据以及其交集部分。
SELECT * FROM student left JOIN person ON student.`address` = person.`birth`;

-- 左外连接：右表所有数据以及其交集部分。
SELECT * FROM student right JOIN person ON student.`address` = person.`birth`;



-- 子查询：查询中嵌套查询，称嵌套查询为子查询。

-- 单行单列子查询：子查询可以作为条件，使用运算符去判断 运算符： > >= < <= =

-- 单行多列子查询 ：子查询可以作为条件，使用运算符in来判断
SELECT * FROM student where address <(select avg(birth) from person);
-- 多行多列子查询
select * from student WHERE student.`id` in ( select id from person where id in (1, 2));
-- 多行多列子查询：子查询可以作为一张虚拟表参与查询

-- 事务：
CREATE TABLE account (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	balance DOUBLE
);
-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);

-- 开启事务：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。
START TRANSACTION;
语句块
commit;

rollback;

-- 查看事务默认提交,1为默认提交,mysql默认自动提交，一条dml语句执行后会自动提交
select @@autocommit;
set @@autocommit = 0;

-- 事物的四大特征：
1. 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
2. 持久性：当事务提交或回滚后，数据库会持久化的保存数据。
3. 隔离性：多个事务之间。相互独立。
4. 一致性：事务操作前后，数据总量不变
-- 3. 事务的隔离级别（了解）
* 概念：多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。
* 存在问题：
	1. 脏读：一个事务，读取到另一个事务中没有提交的数据
	2. 不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样。
	3. 幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改
* 隔离级别：
	1. read uncommitted：读未提交
		* 产生的问题：脏读、不可重复读、幻读
	2. read committed：读已提交 （Oracle）
		* 产生的问题：不可重复读、幻读
	3. repeatable read：可重复读 （MySQL默认）
		* 产生的问题：幻读
	4. serializable：串行化
		* 可以解决所有的问题

	* 注意：隔离级别从小到大安全性越来越高，但是效率越来越低
	* 数据库查询隔离级别：
		* select @@tx_isolation;
	* 数据库设置隔离级别：
		* set global transaction isolation level  级别字符串;	
		
-- DCL
-- 查询用户
USE mysql;
SELECT * FROM USER;

-- 创建用户：% 表示可以在任意主机使用用户登录数据库
create user 'name'@'host' identified by 'password';

CREATE USER 'name'@'%' IDENTIFIED BY 'root';

-- 删除用户
drop user 'name'@'host';

-- 修改密码 5
set password for 'name'@'host' = password('new password');
update user set Password = password('new passwodr') where user = 'name';
-- 修改密码 8
use mysql;
update user set authentication_string='' where user='root';
ALTER user 'root'@'localhost' IDENTIFIED BY 'JOhydhLfMsWyBcn#';
-- 修改root用户密码
1. cmd -- > net stop mysql 停止mysql服务
	* 需要管理员运行该cmd

2. 使用无验证方式启动mysql服务： mysqld --skip-grant-tables
3. 打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功
4. use mysql;
5. update user set password = password('你的新密码') where user = 'root';
6. 关闭两个窗口
7. 打开任务管理器，手动结束mysqld.exe 的进程
8. 启动mysql服务
9. 使用新密码登录。

-- 权限管理
-- 查询权限
show grants for 'root'@'localhost';
-- 授予选项
grant select update delete  insert on 数据库名.表名 to '用户名'@'主机名'; 
GRANT ALL ON *.* TO 'zhangsan'@'localhost';
-- 撤销权限
revoke SELECT UPDATE DELETE  INSERT ON 数据库名.表名 from '用户名'@'主机名';