Mysql常用指令集

1、创建（Create）

1.数据库

- create database if not exists name; 创建数据库
- create database character set gbk; 指定编码创建数据库

---

2.数据表

- create table if not exists tablename(id int(10) primary key auto_increment,name varchar(20) ,age int(3)); 创建表
- create tablename like tablename2；复制表
- insert into 表名 （列名1，列名2） values (值1，值2); 插入数据

---

2、查询(Retrieve)

1.数据库

• show create database name 查看创建信息

---

2.数据表

---

- show tables; 显示表
- describe tablename;或者 desc tablename； 查看表结构
- select 中加上distinct去除重复字段
- select 列名1，列名2 from 表名字；查询
- select 列名1，列名2 from 表名字 order by 列名1 ASC,列名 DESC；  排序查询（列名1升序，相同则按列名2降序）
- select count(列名或*) from tablename; 查询有多少条数据（排除null）
- select count(IFNULL(列名，值)) from wxxs;把值赋给null，然后统计数据条数
- select max(列名或*) from tablename;列最大值
- select min(列名或*) from tablename;列最小值
- select avg(列名或*) from tablename;列平均值
- select sum(列名或*) from tablename;列的和
- select sex,avg(age) from tablename group by sex;按照性别分组查询，男女平均年龄
- select sex,avg(age),count(id) from tablename group by sex where age>20 having count(id)>2;按照性别分组查询，男女平均年龄,除去年龄大于20岁的，分组后现在数量大于2个的；where用在分组前（不可跟聚合函数（sum之类）），having用在分租后；
- select sex,avg(age),count(id) **num** from tablename group by sex where age>20 having **num**>2; 别名
- select * from 表名 limit 开始索引，一页显示条数;分页查询；
- select * from 表名 where id between 5 and 10;查询id为5到10的
- select * from 表名 where id in (5,10);查询id为5和10的数据
- select * from 表名 where id is (not) null;查询id为（非）空的数据
- select * from 表名 where name like '_张%'；_匹配一个字符，%匹配多个字符；

3、删除(Delete)

1.数据库

- drop database name ;直接删除数据库，不提醒
- drop datebase name if exists; 存在则删除数据库
- mysqladmin drop databasename 删除数据库前，有提示。

---

2.数据表

- delete from 表名字 where 条件；删除满足条件的数据
- delete from 表名; 删除所有数据（效率低）
- truncate table 表名字；清空表数据（实际是删除再创建，效率高）

---

4、修改（Update）

1.数据库

• alter datebase name character set utf8; 修改编码

---

2.数据表

---

- alter table tablename rename to NewName;修改表名字
- alter table tablename character set utf8; 修改编码
- alter table tablename add 列名 数据类型;增加一列
- alter table tablename change 列名 新列名 新数据类型; 修改列
- alter table tablename modify 列名 数据类型; 修改列数据类型
- alter table tablename drop 列名； 删除列
- uptate tablename set 行名=值，行名=值 where 条件; 修改满足条件的值
- uptate tablename set 行名=值；修改指定列名的所有数据位指定值

5、使用数据库

• use databasename; 选择数据库
• select database(); 查看当前使用的数据库
• select version(),current_date; 显示当前mysql版本和当前日期

---

6、约束

非空约束 not null；创建表时添加，或者使用alter添加/删除（alter table 表名 modify 列名 数据类型 not null），删除时不加not null即可；
唯一约束 unique；可以有多个null，创建表时添加，或者使用alter添加/删除（alter table 表名 drop index 列名），添加和非空约束一样；
主键约束 primary key；添加auto——increment可实现自增长，alter添加/删除与not null一样格式；
外键约束 foreign key；

a.创建表时添加

constraint 外健名称 foreign key 外键列名称 references 主表名称（主表列名称）；

b.创建后alter添加

alter table tablename constraint 外健名称 foreign key 外键列名称 references 主表名称（主表列名称）;

c.删除

alter table tablename drop foreign key 外键名字；

d.级联 更新/删除

alter table tablename constraint 外健名称 foreign key 外键列名称 references 主表名称（主表列名称）on update cascade on delete cascade;
主表删除从表数据也被删除，更新同理

7、多表查询

A、内连接查询

• a.隐式内连接
  select * from 表一名，表二名 where 条件；
• b.显示内连接
  select * from 表一名 别名1 (inner) join 表二名 别名2 on 条件;
  select * from table1 w (inner) join table2 m on w.id=m.id;

B、外连接查询

• a.左外连接
  select 字段列表 from 表一名 left （outer） join 表二 on 条件；
  查询左表全部，加上右表与左边交集；
• b.又外连接
  select 字段列表 from 表一名 right （outer） join 表二 on 条件；
  查询又表全部，加上左表与右边交集；

C、子查询

把查询语句当成条件使用来进行查询
a。子查询结果是单行单列的
b。子查询结果是单行多列的
c。子查询的结果是多行多列的

---

8、事务

a.基本操作

事务开启（start transaction），执行sql，发现错误回滚事务(rollback)，没出错提交事务(commit)；
select @@autocommit;查看自动提交， 1为自动0为手动；
set @@autocommit=0;设置为手动提交

b.事务的四大特征

1. 隔离性：多个事务之间，相互隔离。
2. 原子性：是不可分割的，要么同时成功，要么同时失败。
3. 持久性：事务提交或者回滚之后，数据会持久化保存。
4. 一致性：数据总量不变。例如转账

---

c.事务的隔离级别

1. 脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
2. 不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。
3. 幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。
   小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

• 事务隔离级别 脏读 不可重复读 幻读
• 读未提交（read-uncommitted） 是 是 是
• 不可重复读（read-committed） 否 是 是
• 可重复读（repeatable-read） 否 否 是
• 串行化（serializable） 否 否 否

查询隔离级别：select @@tx_isolation;
设置隔离级别：set global transaction isolation level 级别名；

9、用户模块

1.用户管理

1.添加用户

create user '用户名'@'主机名' identified by '密码';

2.删除用户

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

3.修改密码

update user set passward=passward('新密码') where user='用户名';
set password for '用户名'@'localhost'= password('新密码');

4.重置root密码

停止mysql服务（net stop mysql）->管理员执行（mysqld -skip-grant-tables）->新开窗口cmd（mysql）->修改密码；

5.查询用户

select (host,user,passeword) from user;

2.权限管理

1.查询权限

show grants for '用户名'@'主机名'

2.授予权限

grant 权限列表 on 数据库名.数据表名 to '用户名'@'主机名'
grant ALL on . to '用户名'@'主机名';给全部权限；
grant update,select on 数据库名.数据表名 to '用户名'@'主机名';给特定表更新查询权限；

3.撤销权限

revoke 权限列表 on 数据库名.数据表名 from '用户名'@'主机名'
revoke ALL on . from '用户名'@'主机名';撤销全部权限；
revoke update,select on 数据库名.数据表名 from '用户名'@'主机名';撤销特定表更新查询权限；