MySql_Note

-- DDL操作数据库

show databases;#查看所有数据库

show create database new1;#查询某个数据库的创建语句

create database if not exists new1 character set gbk ;#创建数据库(判断是否存在再创建)(设置默认字符集)

alter database new1 character set utf8;-- 修改数据库默认字符集

drop database if exists new1;#删除数据库(判断是否存在)

SELECT DATABASE();#查看当前使用数据库

show variables like 'character_set_database';#查看当前数据库字符集

use new;#使用数据库
/***********************************************************************************************************/
-- DDL操作表

show tables;-- 查询当前数据库中的表

show create table student;-- 查询某个表的创建语句

create table if not exists Student(
	id int,username varchar(10),gender char(1),
	birthday date,score double(5,2),
	email VARCHAR(15),tel VARCHAR(15),status TINYINT
)character set utf8;-- 字段名,类型(判断是否存在)(设置默认字符集)

alter TABLE student character set utf8;-- 修改表默认字符集

drop table if exists s,sd,sttu,stu;#删除表(判断是否存在)

desc stu;-- 查看表结构

CREATE table if not exists sttu like student;-- 复制表(判断是否存在)

ALTER table biao rename to stu;#更改某表名称
ALTER table stu add address varchar(50);#在某表添加一列
ALTER table student modify email VARCHAR(51) CHARACTER SET utf8  COLLATE utf8_general_ci DEFAULT NULL;#根据列名修改某一列数据类型，可修改字符集，可不用写排序规则
ALTER table student change name VARCHAR(51) CHARACTER SET utf8 DEFAULT NULL;#根据列名修改这列列名和数据类型
ALTER table stu drop addr;#删除列
/***********************************************************************************************************/
-- DML

Insert into Student(id,username,birthday,score)VALUES(1,"多说几句","1-1-1",8.1658),(1,"sd","1-1-0",1.36);-- 如果表后无列名则默认为全列，除了数字其他都要用引号。

DELETE from student where id=1;-- 删除列(可写筛选条件不然为清空)
TRUNCATE TABLE student;-- 删除此表后创建和原来一样的空表，等于清空
-- 另外如果执行清空用下者因为下者时间复杂度为O(1),上者要一条条删时间复杂度为O(n)，上者太慢

update student set id=2 where id=1;-- 更改数据（可写筛选条件不然全部行执行此更改）
/***********************************************************************************************************/
-- DQL

SELECT
	字段列表 
FROM
	表名列表 
WHERE
	条件列表 
GROUP BY
	分组字段 
HAVING
	分组之后的条件 
ORDER BY
	排序 
	LIMIT 分页限定;
/*************************************************************************************/
-- 基础查询

select *from 学生;-- 查询所有字段
select 姓名,年龄 from 学生;-- 查询指定字段
select DISTINCT 地址 from 学生;-- DISTINCT可去重
SELECT 姓名 AS name,数学+英语 AS 总分 FROM 学生;-- 查询可计算用AS可给字段显示赋名且AS可省略,有null参与的计算，结果为null
SELECT 姓名,数学+IFNULL(英语,0) AS 总分 FROM 学生;-- IFNULL(英语,0)函数可将字段中的null“最后”置换为所给值,“最后”意思为条件等操作后的最终显示结果
/*************************************************************************************/
-- 条件查询

>、<、<=、>=、=、<>、BETWEEN AND、IN、LIKE、IS NULL、and、or、not、&&、||、！推荐用字母与或非;

select *from 学生 where 年龄<>20;-- <>不等号
select *from 学生 where 年龄!=20;-- !=也为不等号
select *from 学生 where 年龄 IN (22,18,25);-- 筛选出该字段等于列表中的项
select *from 学生 where 英语 is null;-- null的判断用is null或is not null
select *from 学生 where 英语 is not null;-- .................
select *from 学生 where 姓名 like "%马%";-- _:单个任意字符，%:多个任意字符
/*************************************************************************************/
-- 排序查询

语法:order by 子句
order by 排序字段1 排序方式1，排序字段2，排序方式2...
排序方式:
ASC:升序，默认的
DESC:降序;

select *from 学生 where 年龄!=20 ORDER BY 数学 DESC, 英语 ASC;-- 排序优先级从前往后递减
/*************************************************************************************/
-- 聚合函数

将一列数据作为一个整体，进行纵向的计算:count:计算个数	max:最大值	min:最小值	sum:和	avg:平均值,计算对象为“最后”显示结果,比IFNULL(expr1,expr2)还后;
select IFNULL(英语,0)from 学生 where 英语<80;
select count(英语) from 学生;-- 会排除值为null的项
select count(IFNULL(英语,0)) from 学生;-- 通过ifnull避免排除值为null的项
select count(*) from 学生;-- *避免了单行null值影响计算
/*************************************************************************************/
-- 分组查询

语法:GROUP BY
注意:
分组之后查询的字段:分组字段、聚合函数,其他的无意义;
where和having的区别：
where在分组之前进行筛选，如果不满足条件，不参与分组。不能跟聚合函数
having在分组之后进行筛选，如果不满足结果，不会被查询出来。能跟聚合函数;

SELECT 性别,avg(数学) from 学生 WHERE 数学>70 GROUP BY 性别 HAVING count(编号)>2;-- 按照where条件筛选出来的进行分组，再根据HAVING条件进行筛选从而显示，having条件可根据前面的AS来写，一般having的条件是根据分组后的结果来写
/*************************************************************************************/
-- 分页查询

语法:limit开始的索引，每页查询的条数
limit是mysql专有方言;

select *from 学生 LIMIT 0,3;-- 根据索引,长度分页，既然是索引当然是从0开始
/*************************************************************************************/
-- 约束

概念:对表中的数据进行限定，保证数据的正确性、有效性和完整性
分类:
非空约束:not null
唯一约束:unique
主键约束:primary key
外键约束:foreign key;
注意删除：非空约束用MODIFY 字段 类型 (冇)，唯一约束用DROP INDEX 字段名，主键约束用DROP primary key，主键约束的自动增长用MODIFY 字段 类型 (冇)，外键约束用DROP FOREIGN key 外键名;
/*************************************************************************************/
-- 非空约束:not null

此列数据不能为空

CREATE table stu(id int,name varchar(20) not NULL);-- 创建表时加非空约束
ALTER table stu MODIFY name varchar(20);-- 删除非空约束
ALTER table stu MODIFY name varchar(20) not null;-- 创建表后加非空约束
/*************************************************************************************/
-- 唯一约束:unique

此列数据不能重复，包括null也只能有一个

CREATE table stu(id int,name varchar(20) unique);-- 创建表时加唯一约束
ALTER table stu DROP index id;-- 删除唯一约束
ALTER table stu MODIFY id varchar(20) unique;-- 创建表后加唯一约束
/*************************************************************************************/
-- 主键约束:primary key

非空且唯一，一张表只能有一个字段作为主键，主键就是表中记录的唯一标识

CREATE table stu(id int primary key,name varchar(20));-- 创建表时加主键约束
ALTER table stu DROP primary key;-- 删除主键约束
ALTER table stu MODIFY name varchar(20) primary key;-- 创建表后加主键约束
/*************************************************************************************/
-- 主键约束_自动增长(一般和主键一起使用)

如果某一列是数值类型的，使用aotu_increment 可以来完成值的自动增长，添加数据时可以不添加主键字段;

CREATE table stu(id int primary key auto_increment,name varchar(20));-- 创建表时加主键约束，并设置自动增长
ALTER table stu modify id int;-- 删除主键的自动增长
ALTER table stu modify id int auto_increment;
/*************************************************************************************/
-- 外键约束:foreign key

设置外键关联到另一个表的主键上，外键所设置的值必须是关联表主键有的值，关联表只能删除主键没被外键写上的项;

CREATE TABLE `emp` (
  `编号` int(11) PRIMARY key auto_increment ,
  `姓名` varchar(20) DEFAULT NULL,
  `年龄` int(11) DEFAULT NULL,
  `性别` varchar(5) DEFAULT NULL,
  `地址` varchar(100) DEFAULT NULL,
  `数学` int(11) DEFAULT NULL,
  `英语` int(11) ,
	constraint wjname foreign key (英语) REFERENCES stu (id) 
) ;-- 创建表时加外键约束
ALTER table emp DROP FOREIGN key wjname;-- 删除外键约束
ALTER table emp ADD constraint wjname foreign key (英语) REFERENCES stu (id);-- 创建表后加外键约束
/*************************************************************************************/
-- 外键_删除外键关联的某一主键

update emp set 英语 =null where 英语=1;-- 根据主键修改外键为空
update stu set id =10 where id=1;-- 根据主键修改外键为空
update emp set 英语 =10 where 英语 is null;-- 根据主键修改外键为空
/*************************************************************************************/
-- 外键_级联更新

创建时，后面加上on UPDATE cascade
创建后，删掉外键后，单独创建外键后面加上on UPDATE cascade
/*************************************************************************************/
-- 外键_级联删除

创建时：后面加上on delete cascade
创建后：要删掉外键后，再创建外键后面加上on delete cascade
/*************************************************************************************/
-- 多表关系

一对多：在多的一方建立外键，指向另一方的主键
多对多：需要借助第三张表，至少包含两个字段设联合主键，分别作为第三张表的外键指向另外两张表的主键
一对一：可以在任意一方添加唯一外键指向另一方的主键
/*************************************************************************************/
-- 范式

第一范式(1NF):每一列都是不可分割的原子数据项
第二范式(2NF):在第1NF基础上，非码属性必须完全依赖于候选码(在1NF基础上消除非主属性对主码的部分函数依赖)
第三范式(3NF):在2NF基础上，任何非主属性不依赖于其他非主属性(在2NF基础上消除传递依赖)
/*************************************************************************************/
-- 数据备份和还原

备份:mysqldump -u用户名 -p密码 >保存的路径
还原:登录数据库;创建数据库;使用数据库;执行文件。source 文件路径
/*************************************************************************************/
-- 隐式内连接查询

用WHERE来筛选笛卡尔积表;

SELECT 编号,name from 学生, stu where 编号=id;-- 两表若字段重复则加:表.，另前面的别名可直接用在where后
/*************************************************************************************/
-- 显式内连接

用inner join 表 on 条件,或join 表 on 条件;

SELECT*FROM 学生 inner join stu on 编号=id;
/*************************************************************************************/
-- 左外连接

用left join 表 on 条件,无论是否符合条件左表会全出现;

SELECT*FROM 学生 left join stu on 编号=id;
/*************************************************************************************/
-- 右外连接

用RIGHT join 表 on 条件,无论是否符合条件右表会全出现;

SELECT*FROM 学生 RIGHT join stu on 编号=id;
/*************************************************************************************/
-- 子查询,就是嵌套查询

看子查询的返回有不同的查询方式
单行单例>=、<=、=、<、>、<>、！=
多行单列in
多行多列作虚拟表做连接查询,注意子查询要用()括起来并且要有别名;

SELECT 编号,name from (SELECT*FROM 学生 where 编号 in (SELECT id FROM stu where id in(5,4))), stu where 编号=id;
(SELECT*FROM 学生 where 编号 in (SELECT id FROM stu where id in(5,4)))
/*************************************************************************************/
-- 事务

概念:多个操作被事务管理，要么同时成功，要么同时失败

操作:
1、开启事务:start transaction
2、回滚:rollback
3、提交:commit

事务分自动提交和手动提交，mysql默认自动一语句一提交,开启事务后则需要手动提交。oracle默认手动提交

查看事务的默认提交方式 SELECT @@autocommit;1:自动,0:手动
修改事务的默认提交方式 set @@autocommit =值;

事务四大特征:
原子性:不可分割，要么同时成功，要么同时失败
持久性:提交或回滚后，数据库会持久化的保存数据
隔离性:多个事物之间，相互独立
一致性:事务操作前后，数据总量不变

事务的隔离级别（了解）
概念：多个事务之间是隔离的、独立的。但是多个事务操作同一批数据则会引发一些问题，设置不同的隔离级别就可以解决这些问题

存在问题：
脏读：一个事务读取到另一个事务中没有提交的数据
不可重复读（虚读）：在同一个事务中，两次读取到的数据不一样
幻读：一个事务操作（DML）数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改

隔离级别：
1、read UNCOMMITTED：读未提交。产生的问题：脏读、不可重复读、幻读
2、read COMMITTED：读已提交。产生的问题:不可重复读、幻读（Oracle默认）
3、REPEATABLE read：可重复读。产生的问题：幻读（MySQL默认）
4、SERIALIZABLE：串行化。可以解决所有的问题
隔离级别从小到大安全性越来越高，但是效率越来越低

数据库查询隔离级别：
SELECT @@tx_isolation;
数据库设置隔离级别：
set global transaction ISOLATION level 级别字符串
/*************************************************************************************/
-- DCL

用户放在mysql数据库中
查询用户需要先切换到mysql数据库，查询user表
%表示可以在任何主机使用用户登录数据库;

CREATE user '用户名'@'主机名' IDENTIFIED by '密码';-- 创建用户

Drop USER '用户名'@'主机名';-- 删除用户

UPDATE user set passwprd =PASSWORD('新密码')where user ='用户名';-- 修改密码方式1
set PASSWORD for '用户名'@'主机名'=PASSWORD('新密码');-- 修改密码方式2

root密码的找回（cmd管理员模式操作）：
net stop mysql-- 停止服务
mysqld --skip-grant-tables-- 跳过验证登录
打开新的cmd
mysql-- 无验证登录
user mysql;-- 切换到mysql数据库
UPDATE user set passwprd =PASSWORD('新密码')where user ='root';-- 更改密码
关闭两个cmd窗口然后在任务管理器中关闭mysqld.exe进程
net START mysql-- 启动服务
mysqld -uroot -p'密码'-- 用新密码登录
/*************************************************************************************/
-- 权限管理

show grant for '用户名'@'主机名';-- 查询权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

权限列表：SELECT、DELETE、UPDATE、ALL
所有数据库的所有表*.*