Mysql笔记

DDL　

（一）数据库的操作　

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

　　2.查询某个数据库的创建语句　　show create database 数据库名;

　　3.创建数据库

　　　　create database 数据库名;

　　　　create database if not exists 数据库名;

　　　　create database 数据库名 character set gbk;

　　　　create database if not exists 数据库名 character set gbk;

　　4.修改数据库字符集　　alter database 数据库名 character set 字符集名;

　　5.删除数据库

　　　　drop database 数据库名;

　　　　drop database if exists 数据库名;

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

　　7.使用数据库　　use 数据库名;

（二）表的操作

　　1.创建表　　create table 表名(

　　　　　　　　　　列名1 数据类型1，

　　　　　　　　　　列名2 数据类型2，

　　　　　　　　　　......

　　　　　　　　　　列名n 数据类型n

　　　　　　　　);　　//最后一个列没有逗号

　　2.查询所有表　　show tables;

　　3.查询表结构　　desc 表名;

　　4.表的数据类型　　int，double，date，datetime，timestamp，varchar

　　5.删除表　　drop table 表名;

　　6.复制表　　create table 表名1 like 表名2;

　　7.修改表名　　alter table 表名 rename to 新表名;

　　8.修改表的字符集　　alter table 表名 character set 字符集类型;

　　9.添加一列　　alter table 表名 add 列名 数据类型;　　

　　10.修改列名　　alter table 表名 change 列名 新列名 新数据类型;　　alter table 表名 modify 列名 新数据类型;

　　11.删除列　　alter table 表名 drop 列名;

DML

　　1.添加数据　　insert into 表名(列名1,列名2,......,列名n) values(值1,......,值n);

　　　　注意：列名与值一一对应；若表名后不定义列名，则默认给所有列添加值；除了数字类型，其他需要引号；

　　2.查询所有数据　　select * from 表名;

　　3.删除某记录　　delete from 表名 where 条件;

　　4.删除所有记录（表）　　truncate table 表名;

　　5.修改数据　　update 表名 set 列名1=值1,...[where 条件];

　　　　注意：若不加条件，所有记录都被修改。

DQL

（一）语法

　　select

　　　　字段列表

　　from

　　　　表名列表

　　where

　　　　条件列表

　　group by

　　　　分组字段

　　having

　　　　分组后的条件

　　order by

　　　　排序

　　limit

　　　　分页限定

（二）基础查询

　　1.多个字段查询　　select 字段1,字段2,..., from 表名；

　　2.去除重复　　distinct

　　3.计算列　　四则运算（数值型）

　　　　　　　　ifnull(表达式1,表达式2) 

　　4.起别名　　as （可省略）

（三）条件查询

　　1.where后跟条件

　　2.运算符

　　　　>，<，<=，>=，=，<>

　　　　between...and

　　　　in

　　　　like　　//模糊查询

　　　　　　占位符：

　　　　　　　　_：单个任意字符

　　　　　　　　%：多个任意字符

　　　　is null

　　　　and(&)

　　　　or(||)

　　　　not(!)

（四）排序查询

　　语法：order by 子句

　　　　order by 排序字段1 排序方式1,......;

　　排序方式（默认升序）ASC，DESC（降序）

　　注意：如果有多个排序条件，前面一样则判断第二个条件。

（五）聚合函数--将一列数据作为一个整体，进行纵向的计算。

　　count：计算个数

　　　　排除空的

　　　　*select count(ifnull(math,0)) from student;

　　　　一般选择非空列（主键），使用ifnull

　　max：计算最大值

　　min：计算最小值

　　sum：计算和

　　avg：计算平均值

（六）分组查询

　　语法：group by 分组字段;

　　注意：分组后查询的字段：分组字段，聚合函数

　　*select sex,avg(math) from student group by sex;

　　where和having的区别：

　　　　where在分组前进行限定，若不满足条件，则不参与分组；having在分组后进行限定，若不满足条件则不会被查询出来。

　　　　where不可跟聚合函数，having可以。

（七）分页查询

　　语法：limit 开始的索引，每页查询的条数;

　　公式：开始的索引 = （当前页码-1）*每页显示的条数

约束

主键约束　　primary key（非空且唯一，一张表只能有一个字段为主键）

　　删除　　alter table stu drop primary key；

非空约束　　not null

唯一约束　　unique　

外键约束　　foreign key （让表与表产生关系，保证数据正确性；

　　　　　　　　　　　　（一对多）在多的一方建立外键，指向一的一方的主键

　　　　　　　　　　　　（多对多）需要第三张中间表，中间表的字段作为外键指向两张表的主键）

　　删除　　alter table 表名 drop foreign key 外键名;

　　添加　　alter table 表名 add constraint 外键名 foreign key 外键字段名 references 主表名(主表字段);

　　级联　　更新　　alter table 表名 add constraint 外键名 foreign key 外键字段名 references 主表名(主表字段) on update cascade;

　　　　　　删除　　alter table 表名 add constraint 外键名 foreign key 外键字段名 references 主表名(主表字段) on delete cascade;

create table 表名(

　　id int primary key auto_increament,

　　phone_number varchar(20) unique,

　　name varchar(20) not null,

　　constraint 外键名 foreign key (外键列名) references 主表名（主表列名）

)

范式

概念　　设计数据库时遵循的规范

分类　　第一范式　　每一列都是不可分割的数据项（数据内容不可继续拆分）。

　　　　第二范式　　在第一范式基础上，非码属性必须完全依赖于候选码（一张表一个事）。

　　　　第三范式　　在第二范式基础上，消除传递依赖（表与表之间用主键进行关联）。

数据库备份还原

　　备份　　musqldump -u用户名 -p密码 > 保存路径

　　还原　　登录数据库-创建数据库-使用数据库-执行文件。source 文件路径

多表查询　　

　　笛卡尔积　　A，B两个集合的所有组合情况；夺多表查询需要消除笛卡尔积中的无用数据。

　　内连接查询：从哪些表中查询数据，条件是什么，需要查询哪些字段

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

　　　　　　select * from stu,dep where dept_id =dep_id;

　　　　　　select stu.name,dep.name from stu,dep where dept_id =dep_id;

　　　　　　正规写法：所有内容各占一行，方便写注释

　　　　　　select

　　　　　　from

　　　　　　where

　　　　显式内连接

　　　　　　select 字段列表 from 表名1 [inner] join 表名2 on 条件

　　　　　　例如：select * from stu  join dep on dept_id =dep_id;

　　外连接查询

　　　　左外连接　　select 字段列表 from 表1 left [outer] join 表2 on 条件

　　　　　　左表所有内容加右表交集

　　　　右外连接

　　子查询　　查询中嵌套查询，嵌套的查询称作子查询。

　　　　select * from emp where emp.salary = (select * max(salsry) from emp);

　　　　子查询的不同情况

　　　　　　1.结果是单行单列的

　　　　　　2.结果是多行单列的

　　　　　　3.结果是多行多列的

事务

（一） 事务的基本介绍

　　概念：如果一个包含多个步骤的业务操作，被事务管理，那这些操作要么同时成功，同时失败。

　　操作：1.开启事务、2.回滚、3.提交；

　　　　开始事务：start transaction;

　　　　没有问题提交事务：commit;

　　　　出现问题回滚事务：rollback;

　　select @@autocommit;等于1表示自动提交等于0表示手动提交

　　set @@commit = 0;

（二）事务的四大特征

　　1.原子性：是不可分割的最小操作单位，要么同时成功要么同时失败；

　　2.持久性：如果事务一旦提交或者回滚，数据库会持久化的保存数据；

　　3.隔离性：多个事务之间相互独立。

　　4.一致性：事务操作前后数据总量不变。

（三）事务的隔离级别

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

　　存在问题：

　　　1.脏读：一个事务读取到另一个事务未提交的数据；

　　　2.不可重复读：同一个事务中两次读取到的数据不一样；

　　　3.幻读：一个事务操作（DML）数据表中所有记录，另一个记录添加了一条数据，则第一个事务查询不到自己的修改。

　　隔离级别：

　　　1.read uncommitted：读未提交　　会产生三种问题

　　　2.read committed：读已提交　　会产生后两种问题

　　　3.repeatable read：可重复读　　会产生最后一种问题（mysql默认）

　　　4.serializable：串行化　　可解决所有问题

　　注意：隔离级别从小到大安全性越来越高，而效率越来越低

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

　　设置隔离级别：set global transaction isolation level;

 DCL（管理用户，授权）

　　1.管理用户

　　　　1.用户添加：

　　　　　　creat user "用户名"@"主机名" identified by "密码";

　　　　　　creat user "用户名"@"#" identified by "密码";

　　　　2.删除用户

　　　　　　drop user "用户名"@"主机名";

　　　　3.修改密码

　　　　　　update user ser password  = password("新密码") where user = "用户名";

　　　　　　set password for "用户名"@"主机名" = password("新密码");

　　　　　　*忘记root密码怎么办？

　　　　　　　　1.管理员运行cmd

　　　　　　　　2.net stop mysql

　　　　　　　　3.使用无验证方式启动mysql服务：mysql --skip-grant-tables

　　　　　　　　4.任务管理器关闭mysqld进程

　　　　　　　　5.管理员运行cmd net start mysql

　　　　4.查询用户：use musql; select * from user;

　　2.授权　　

　　　　1.查询权限

　　　　　　show grant for "用户名"@"主机名"；

　　　　2.授予权限

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

　　　　　　grant all on '*' to '用户名'@'主机名';

　　　　3.撤销权限

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