mysql便签（常用指令）

cmd（右键以管理员身份运行）
访问本地
cmd——
mysql -uroot -p
root（密码） 登录
exit 退出

net start mysql 启动
net stop mysql 停止

DDL操作库

1.创建库（data下的数据库文件夹）
create database 库名; 创建
create database if not exists 库名; 如果没有这个库名，再创建
create database 库名 character set gbk; 创建，并指定字符集GBK
create database if not exists 库名 character set gbk; 如果没有这个库名创建，并指定字符集GBK
2.查询库
show databases; 查看所有数据库名称
show create database 库名; 查看某个数据库创建语句
3.修改库
alter database 库名 character 字符集; 修改字符集
4.删除库
drop database 库名; 删库
drop database if exists 库名; 如果存在，则删库
5.使用库
use 库名; 使用库
select database(); 查询正在使用的库名

DDL操作表

1.创建表（USE 库名; 进入库再创建）
CREATE TABLE IF NOT EXISTSstudent (
sid INT(10) NOT NULL AUTO_INCREMENT COMMENT ‘学生编号’,
sname VARCHAR(20) COLLATE utf8_bin NOT NULL COMMENT ‘学生名字’,
ssex CHAR(1) COLLATE utf8_bin NOT NULL COMMENT ‘学生性别’,
age INT(2) NOT NULL COMMENT ‘学生年龄’,
address VARCHAR(200) COLLATE utf8_bin DEFAULT ‘南京’ COMMENT ‘学生地址’,
identify VARCHAR(20) COLLATE utf8_bin NOT NULL COMMENT ‘学生身份证号’,
PRIMARY KEY (sid)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

数据类型：
int 整数： age int;
double 小数： score double(5最多位,2小数位)
varchar 字符串类型： name varchar(255最大字符)
text 文本类型： 大文本串（>255）存放txt文件
date 日期： yyyy-MM-dd
datetime日期时间： yyyy-MM-dd HH:mm:ss
timestamp 时间戳（如果不赋值，或null，就自动赋值）： yyyy-MM-dd HH:mm:ss
SQL分类（DDL,DML,DQL,DCL）

主键约束（primary key）
create table 表名(列名1,数据类型1 primary key，。。。); 创建主键约束
alter table 表名 modify 列名 数据类型 primary key; 加主键
alter table 表名 drop primary key; 删主键
自增（auto_increment）写主键后面
alter table 表名 modify 列名 数据类型 auto_increment; 加自增
alter table 表名 modify 列名 数据类型; 删自增
非空约束（not null值不能为空）
create table 表名(列名1,数据类型1,列名2,数据类型2 not null。。。); 创建非空约束
alter table 表名 modify 列名 数据类型 not null; 加非空约束
alter table 表名 modify 列名 数据类型; 删非空约束
唯一约束（unique值不能重复）
create table 表名(列名1,数据类型1,列名2,数据类型2 unique。。。); 创建唯一约束
alter table 表名 modify 列名 数据类型 unique; 加唯一约束
alter table 表名 drop index 列名 数据类型; 删唯一约束
外键约束（foreign key）
表拆分（抽象成表）：创建表1时，列名换成表2的对应列名，值为表2的序号
create table 表名(列名1,数据类型1,外键列
constraint 外键名 foreign key (外键列) references 主表名(主表列名)
); 创建表时加外键（constraint 外键名 – 可省略，系统自动生成）
alter table 表名 add constraint 外键名 foreign key (外键列) references 主表名(主表列名); 创建表后加外键
alter table 表名 drop foreign key 外键名; 删外键

2.查询表
show tables; 查询一个库所有表
desc 表名; 查询表结构
show create table 表名; 查看表语句
3.修改表
alter table 表名 rename to 新表名; 改表名
alter table 表名 character set 字符集; 改字符集
alter table 表名 add 列名 数据类型; 添加列
alter table 表名 modify 列名 新数据类型; 修改列类型
alter table 表名 change 列名 新列名 新数据类型; 修改名称和类型
alter table 表名 drop 列名; 删除列
alter table 表名 engine MyISAM; 改表引擎
4.删除表
drop table 表名;
drop table if exists 表名; 如果有则删除表

DML 增删改

增数据
insert into 表名 values(值1,‘值2’。。。); 增加数据
insert into 表名[(列名1,列名2。。。)] values(值1,‘值2’。。。); 增加数据
insert into 表名[(列名1,列名2。。。)] values(值1,‘值2’。。。),(值1,‘值2’。。。); 增加多行数据
create table 新表 (select 列名1,列名2。。。 from 原表); 克隆出一个新表
insert into 表名 values(值1，值2，null); 给所有列增加数据
删数据
delete from 表名 where 列 内容; 删除具体内容的数据，where条件
truncate table 表名; 删除全部表数据，再创建空表
delete from 表名; 删除全部表数据
改数据
update 表名 set 列名 = 值 where 列 = 值; 改数据，where条件
update 表名 set 列名1 = 值1,列名2 = 值2 where 列 = 值; 改多个数据，where条件
update 表名 set 列名 = 值; 改列所有的值

DQL 查

通配符
select * from 表名; 查表中全部数据（一般不用，写全部列名，加注释）
基础查询
select 列名1 from 表名; 字段查询
select 列名1,列名2 from 表名; 多字段查询
select distinct 列名1 from 表名; 多字段去重查询（所有值一样才去重）
计算列之和
select 列名1,列名2,列名1+列名2 from 表名; 多字段相加
select 列名1,列名2,列名1+ifnull(列名2,0) from 表名; 多字段相加，如果值为空(列表值，换成0)
select 列名1,列名2,列名1+ifnull(列名2,0) 别名 from 表名; 多字段相加，判断null，并起别名
条件查询
selfct * from 表名 where 列名 > 20 and <30; 查询列表名数值>20的,并<30
selfct * from 表名 where 列名 between 20 and 30; 查询列表名数值之间 >=20的,并<=30
具体条件（in）
selfct * from 表名 where 列名 in(20,30); 查询列表名数值是（条件是20,30的）
selfct * from 表名 where 列名 is null; 查询列表名数值是null空的
selfct * from 表名 where 列名 is not null; 查询列表名数值不是null空的
模糊查询
selfct * from 表名 where 列名 is not null; 查询列表值不是null空的
selfct * from 表名 where 列名 like ‘马%’; 查询列表值是‘马什么’的，%
selfct * from 表名 where 列名 like ‘马’; 查询列表值是‘什么马’的
selfct * from 表名 where 列名 like ‘__’; 查询列表值是‘三个字的’的
selfct * from 表名 where 列名 like ‘%马%’; 查询列表值是‘包含马’的
排序查询（order by）
selfct * from 表名 order by 列名 排序方式; (ASC升序，DESC降序）不写默认ASC
selfct * from 表名 order by 列名1 排序方式1,列表名2 排序方式2; 第二排序条件，第一条件一样才执行
聚合函数（列的纵向计算）（count，mix，min，avg）
count 个数，max 最大，min 最小，sum 合计，avg 平均值
selfct count(ifnull(列名,0)) from 列表名; 计算个数算上null,算主键列
selfct count(*) from 表名; 计算个数算上null
selfct max(列名) from 表名; 计算最大
selfct min(列名) from 表名; 计算最小
selfct sum(列名) from 表名; 计算合计
selfct avg(列名) from 表名; 计算平均
分组查询（统计，计算列&列）
select count(列名1) 别名,列名2 别名 from 表名; 分组查询，两列

selfct 列名1,avg(列名2) from 表名 group by 列名1; 计算列名1的（列名2）的平均分
selfct 列名1,avg(列名2),count(列名3) from 表名 group by 列名1;
参照列名1，计算（列名2）的平均分，并计算（列名3）的合计

selfct 列名1,avg(列名2),count(列名3) from 表名 where 列名2>70 group by 列名1;
参照列名1，计算（列名2）的平均分，并计算（列名3）的合计,where过滤条件

selfct 列名1,avg(列名2),count(列名3) from 表名 where 列名2>70 group by 列名1 having count(列名3) > 2; 分组之后再having分组

selfct 列名1,avg(列名2),count(列名3) 人数 from 表名 where 列名2>70 group by 列名1 having 人数 > 2; 分组之后再having别名分组

分页查询（limit）
开始索引=（当前页面-1）*每页显示的数
selfct * from 表名 limit 0,3; 第1页，从0（开始索引）开始到第3（每页显示的数）
selfct * from 表名 limit 3,3; 第2页，从3开始到第3

多表查询
select 表名1.列名1,表名2.列名2 from 表名1,表名2 where 条件; 隐式
select 表名1.列名1,表名2.列名2 from 表名1 join 表名2 on 条件; 显示
select 表名1.*,表名2.列名2 from 表名1 left join 表名2 on 条件; 左外（左表所有数据，及交集部分，位置调换就是右外）

子查询（select查询嵌套select查询）
select * from 表名 where 列名 = (select 条件);

1.单行单列（子查询作为条件）

2.多行单列

3.多行多列

显示版本函数，直接输出
SELECT VERSION(),(36525/365);
数学函数
select floor(rand()*10); 0.0-9.0随机数，向下取整
select ceil(23,5); 向上取整
select round(23,5); 四舍五入
字符串函数
select concat(‘Hello’,‘SQL!!!’); 字符串
select insert(‘我爱你，中国，china’,3,4,‘动物’); 字符串索引从1开始
select substring(‘我爱你，中国，china’,8); 字符串截取
时间函数
select curdate(); 当前日期
select curtime(); 当前时间
select now(); 当前日期时间
select year(now()); 提取时间中的年份
selsct month(select now()); 提取时间中的月份
select day(now()); 提取时间中的月份
select dayofyear(now()); 一年已过天数
select datediff(now(),‘1997-3-14’); 距今天数
select datediff(‘2097-3-14’,‘1997-3-14’); 相隔天数
select datediff(now(),‘2097-3-14’); 距今多少天后日期
select max(concat(year(now())，month(now())，day(now()))); 提取最近年月日

DCL用户管理（增删改查）
use myql;
select * from user; 查询用户表

create user ‘用户名’@‘主机名’ identified by ‘密码’; 创建用户
drop user ‘用户名’@‘主机名’; 删除用户

set password for ‘用户名’@‘主机名’ = password(‘新密码’); 改密码
update user set password = password(‘新密码’) where user = ‘用户名’; 改密码

DCL授权管理（增删改查）
show grants for ‘用户名’@‘主机名’; 查看用户权限
grant 权限列表 on 数据库名.表名 to ‘用户名’@‘主机名’; 用户授权（哪个库，表，给谁，什么权限）
权限列表：select查询,delete,update
grant all on . to ‘用户名’@‘主机名’; 授予所有权限，在所有库，表（危险，一般不用）
revoke 权限列表 on 库名.表名 from ‘用户名’@‘主机名’;

事务
start transaction; 开启事务（不开启则自动提交）
rollback; 回滚
commit; 提交

select @@autocommit; 查看默认提交事务方式，1是自动，0是手动
select @@autocommit=0; 更改提交事务方式，为0手动
commit; 手动提交事务（持久化更新数据）

视图
Create view view_Infos视图名 as select 列名1 ‘别名’,列名2 ‘别名’ from 表名; 创建视图
select * from view_Infos视图名; 使用视图
drop view if exists view_Infos视图名; 删除视图
USE information_schema;
select * from views; 查看视图创建是否成功

索引
show index from 表名; 查看表中索引信息
create unique index (index_on表列名)索引名 表名(列名); 创建普通索引
drop index 索引名 on 表名; 删除索引
alter table 表名 add fulltext (index_on表列名) (列名); 修改表，增加索引
explain select * from 表名 where 列名=12; 加explain查看是否用到索引

隔离级别（repeatable read）从小到大，安全性递增，效率递减
read uncommitted： 读未提交，产生3种问题
read committde： 读已提交，产生虚读，幻读（Oracle，SQL Server数据库默认）
repeatable read： 可重复读，产生幻读（MySQL数据库默认）
serializable： 串行化，无问题
串行化（锁表：如果一个事务在操作表，另一个事务不可以操作，只有开锁后可操作）

select @@tx_isolation; 查看隔离级别
set global transaction isolation level 隔离级别; 设置隔离级别（重新连接生效）

备份
数据库——右键——备份/导入——备份数据库
还原
主目录——右键——执行SQL脚本——选择脚本——

命令行备份
cmd——
mysqldump -u用户名 -p密码 -h localhost（IP地址）数据库名称 表名1 表名2 >d：保存路径

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