mysql指令集_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.事务的四大特征

隔离性：多个事务之间，相互隔离。

原子性：是不可分割的，要么同时成功，要么同时失败。

持久性：事务提交或者回滚之后，数据会持久化保存。

一致性：数据总量不变。例如转账

c.事务的隔离级别

脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。

幻读：系统管理员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 '用户名'@'主机名';撤销特定表更新查询权限；