MySql之sql语句

数据库操作

创建数据库
create {database|schema} [if not exist] 数据库名
[
[default] character set [=] 字符集 |
[default] collater [=] 校对规则名称
];

查看数据库
show {database|schema} [like 模式 | where 条件];

选择数据库
use 数据库名;

修改数据库
alter {database|schema} [数据库名] [default] character set [=] 字符集 | [default] collater [=] 校对规则名称;

删除数据库
drop {database|schema} [if exists] 数据库名;

存储引擎

查询所有支持的存储引擎
show engines;

查询默认存储引擎
show variables like ‘storage_engine%’;

修改表引擎
alter table 表 engine = xxx;

数据表操作

创建表
create [temporary] table [if no exists] 表名
（列…[auto_increment] [primary key] [unique] [default] [not null] [constraint … foreign key … references …] ）
engine=innodb auto_increment=1 default charset=utf8 collate=utf8_general_ci;

查看表
1：show [full] columns from 数据表名.数据库名;
2：describe 数据库表名 [列名];
3：show table 列出当前数据库所有表

查看表创建信息
show create table 表名;

修改表
alter table 数据表名 (add column … | add primary key … | alter 列 set default … | change 列 … | drop primary … | rename … | …);
（alter修改默认值，change修改名字，modify修改定义）

修改表引擎
alter table 表 engine = xxx;

重命名表
rename table name1 to name2;

复制表
create table [if not exists] 表 like 源表名；

删除表
drop table [if exists] 表；

完整性约束

主键约束
primary key

候选约束
NOT NULL UNIQUE

参照约束
REFERENCES 被参照表（列…）[ [ ON DELETE | ON UPDATE ] [ RESTRICT | CASCADE | SET NULL | NOT ACTION ] ]
例 ： REFERENCES tb_name ( name) ON UPDATE CASCADE ;

用户自定义约束
NOT NULL 、CHECK
例：age not null check(age>17 and age<40)

给完整性约束命名
CONSTRAINT

禁用/启用唯一性检查
set unique_checks=0/1;

流程控制

IF语句
IF XXX THEN XXX;
ELSEIF XXX;
ELSE XXX;
END IF;

CASE语句
CASE VALUE
WHEN XXX THEN XXX;
ELSE XXX;
END CASE;

WHILE语句
WHILE XXX DO
XXX
END WHILE;

LEAVE / ITERATE语句
离开 / 进入 循环

LOOP语句(与while相似，不同在于用leave退出循环)
loop_label:LOOP
XXX
IF XXX THEN LEAVE loop_label; END IF;
END LOOP;

REPEAT语句（do…while）
REPEAT
XXX
UNIT XXX END REPEAT;

创建存储过程
create procedure xxx（in/out/inout x int）
begin
…
end

调用存储过程
call xxx(x)

CRUD

插入
1:insert into xxx(…) values(…)，values(…);
2:insert into xxx set xxx=xxx,xxx=xxx;
3:insert into xxx select …;

修改
update xxx set xxx=xxx where … [order by xxx] [limit xxx];

删除
1:delete from xxx where … [order by xxx] [limit xxx];
2:truncate table 表；（删除表所有数据，主键重新计数）

基本查询
select [distinct] xxx from xxx (distinct去除重复行)
where …
group by …
order by …
having … (二次判断，且仅用于组，所以必须在group by 后)
limit …

子查询
嵌套
IN
EXISTS
ANY
ALL
等关键字

合并查询
select xxx from …
UNIQN (ALL) (有all不去重复行)
select xxx from …;

连接查询(笛卡尔积)
等值 / 非等值 连接 ：from 表一，表二 where 表一.字段 { = /< ， > } 表二.字段
（左 / 右）外连接 ：from 表一 [left | right ] join 表二 ON 表一.字段 = 表二.字段

正则、like查询
select xxx from xxx where xxx { REGEXP | LIKE } ‘正则’；

别名
select xxx AS xxx from …;

函数

常用聚合函数
COUNT()、SUM()、AVG()、MAX()、MIN()

常用数学函数
ABS(X) 绝对值
FLOOR(X) 向左取最近整数（小于等于）
RAND() 0-1随机
PI() Π
TRUNCATE(X,Y) 截取小数点后Y位
ROUND(X,Y) 小数点后Y位四舍五入
SQRT(X) 根号

常用字符串函数
INSERT(S1,X,LEN,S2) 从X位置开始将S1后LEN位替换为S2
UCASE(S)、LCASE(S) 大写
UPPER(S)、LOWER(S) 小写
LEFT(X,N) 返回前N位
RTRIM(S) 去末尾空格
SUBSTRING(S,N,LEN) 截取N处的后LEN位
REVERSE(S) 字符串颠倒
FIELD(S,S1,S2,…) 在S1、S2…中返回第一个与S匹配的位置
POSITION(S1 IN S) 子字符串S1在S中的开始位置
CONCAT(S1,S2,…) 字符串拼接

常用日期和时间函数
CURRENT_DATE()、CURDATE() 当前日期
CURRENT_TIME()、CURTIME() 当前时间
NOW() 当前日期和时间
DATEDIFF(D1,D2) D1和D2相差几天
ADDDATE(D,N) D加上N天
ADDDATE(D,INTERVAL expr type) D加上一段时间，如（D,INTERVAL ‘1 2’ YEAR_MONTH）表示加上1年2个月
SUBDATE(D,N) D减去N天

索引

索引分类
哈希索引、BTREE索引、全文索引

创建索引
1:创表时指定 [UNIQUE | FULLTEXT ] index 索引名（字段…）
2:create [UNIQUE | FULLTEXT ] index 索引名 ON 表（字段…）
3:alter table 表 add index 索引名（字段…）

查看索引
show index from 表；

删除索引
drop index 索引名 ON 表

禁用索引
alter table 表 disable keys

启用索引
alter table 表 enable keys

视图

创建视图
create view 视图名（字段…）
as select 字段… ;

创建/替换视图
create or replace view …

修改视图
alter view …

CRUD
与表类似

删除视图
drop view …

存储过程、存储函数

存储过程与存储函数的区别
存储过程是将一系列重复的sql操作封装，加快开发效率。而存储函数相当于自定义的系统函数，将一系列计算封装，并返回计算结果。

创建存储过程
create procedure …

创建存储函数
create function … returns …

局部变量
declare a int

全局变量
不必声明，直接用@引用

更改cmd命令边界符合
delimiter …

变量赋值
set …

调用存储过程
call …

调用存储函数
与系统定义函数一致

查看
1:show { PROCEDURE | FUNCTION } status [ like …];
2:show create{PROCEDURE | FUNBTION } 名;

触发器

创建触发一条执行语句的触发器
create trigger 触发器名 { before | after } 触发事件 on 表名 for each row 执行语句

创建触发多条执行语句的触发器
create trigger 触发器名 { before | after } 触发事件 on 表名 for each row begin 执行语句… end

查看触发器
show triggers

查看表中的触发器
select * from 表.triggers [ where trigger_name=“xxx”]

删除触发器
drop trigger 触发器名

事务

事务的四个特性（ACID）
原子性、一致性、隔离性、持久性

默认情况下创建表的类型都是MyISAM，但该类型不支持事务，所以要改成InnoDB或BDB。
alter table 表 engine = InnoDB/BDB;

查看事务的隔离级别
1：SELECT @@global.tx_isolation;
2：SELECT @@session.tx_isolation;
3：SELECT @@tx_isolation;

设置事务的隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
{READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
注：
默认的行为（不带session和global）是为下一个（未开始）事务设置隔离级别。如果你使用GLOBAL关键字，语句在全局对从那点开始创建的所有新连接（除了不存在的连接）设置默认事务级别。使用SESSION 关键字为将来在当前连接上执行的事务设置默认事务级别

开始事务
start transaction / begin /begin work；

在事务中创建一个保存点
savepoint identifier

删除一个事务的保存点
release savepoint identifier

提交事务
commit；

回滚事务
rollback；

把事务回滚到标记点；
rollback to identifier

设置自动提交参数
set autocommit = 0/1
0–sql不自动提交，不管是不是事务都必须显式写出commit语句提交sql语句才会起作用，且事务执行一半关闭数据库连接时事务默认回滚
1–sql自动提交，不用commit语句，sql也会自动提交（事务除外），且事务执行一半关闭数据库连接时事务默认提交

死锁的四个条件
互斥、占有且等待、不可抢占、循环等待

给数据表加锁
lock tables 表1 lock_type( read锁还是write锁 )，表2 lock_type…

解锁
unlock tables;

注意：
这里的读、写锁与JAVA中的读、写锁不一样。
MYSQL :读锁是指限制自己只能读不能写而不影响其他人。而写锁是指不限制自己而限制其他人的读和写。
JAVA :一般而言加了读锁其他人读得等读锁释放，同理加了写锁其他人写得等写锁释放。但ReadWriteLock中只有读写、写写互斥而读读不互斥（提高性能）。

事件

开启事件机制
set global enent_scheduler = ON;

查看事件是否开启
show variables like ‘event_scheduler’;

创建事件
create
[ DEFINR = { user | CURRENT_USER}] 事件执行时检查权限的用户
EVENT [ IF NOT EXISTS ] name 事件名
ON SCHEDULE schedule 执行事件的时间和时间间隔
[ ON COMPLETION [ NOT ] PRESERVE ] 事件执行一次还是永久执行，默认为一次，即NOT PRESERVE
[ ENABLE | DISABLE | DISABLE ON SLAVE ] 事件是否可用
[ COMMENT ‘comment’ ] 注释
DO event_body; 事件执行的代码，可以是存储过程、sql语句等
其中ON SCHEDULE 格式
AT timestamp [ + INTERVAL interval … ] | EVERY interval timestamp表示具体时间 ([ + INTERVAL interval … ] 表示一段时间（时间间隔）)
[ starts timestamp [ + INTERVAL interval … ] ]
[ ends timestamp [ + INTERVAL interval … ] ]
例：
每5秒执行一次插入语句
create event xxx
on schedule every 5 second
on completion preserve
do insert into …

修改事件
alter …(与创建类似)

删除事件
drop event xxx

sql优化

查看统计参数值
show status like ‘value’;
其中value有:
connections: 服务器连接次数
uptime: 服务器上线时间
slow_queries: 慢查询次数
com_select: 查询操作次数
com_insert: 插入操作次数
com_delete: 删除操作次数
innodb_rows_read: 查询操作次数（innodb引擎特有）
innodb_rows_inserted: 插入操作次数（同上）
innodb_rows_updated: 更新操作次数（同上）
innode_rows_deleted: 删除操作次数（同上）

查看select语句性能
explain select 语句；

sql优化策略：
1：使用索引

• 多列索引的应用必须有索引中第一字段的查询
• OR查询中必须两个都有索引，索引才会起作用
• 普通索引在like查询中以‘%’开头索引不起作用，这种情况得用全文索引（5.6之前只有MyISAM支持全文索引，5.6之后innodb也支持）
• 若是像平常一样like查询，则全文索引不起作用。得实现格式（select xxx from xxx where match（字段）against（单词））、文本大
• 如果数据量太小，发现全表扫描更快也不会用索引

2：连接查询与嵌套查询优先选连接查询

3：优化数据库结构

• 将字段较多，且部分字段不常用、拥有大量数据的表分解为多个表
• 将经常用到的连接查询独立成一个新表
• 整理磁盘碎片，即通过分析表（analyze table …），检查表（check table …），优化表（optimize table …）消除删除或更新造成的空间浪费

4：优化插入速度

• 插入大量数据时先禁用索引、唯一性检查，执行完再重新启用索引、唯一性检查（alter table xxx disable/enable keys、set unique_checks = 0/1）
• 多条记录一起insert要比分开insert快，因为一起insert减少了数据库之间的连接

5：使用高速缓存

• 查看高速缓存信息，show variables like ‘%query_cache%’
• 启用高速缓存，更改my.ini文件中的配置信息（修改query_cache_size=大小，增加query_cache_type=0/1/2 0-禁用 1-默认从缓存取 2-sql_cache指定从缓存取）
• 当query_cache_type=2时，从高速缓存中查询，select sql_cache * from …

备份和恢复（DOS命令）

mysqldump备份
1:mysqldump -u username -p dbname table1 table2 …>BackupName.sql （备份一个数据库）
2:mysqldump -u username -p --databases dbname1 dbname2 …>BackupName.sql （备份多个数据库）
3:mysqldump -u username -p --all -databases > BackupName.sql （备份所有数据库）

mysqlhotcopy备份
mysqlhotcopy[option] dbname1 dbname2… backupDir/

mysql还原
mysql -u root -p [dbname] <backup.sql

数据库迁移
1:mysqldump -h name1 -u root -password=password1 -all-databases | （ ‘|’表示管道，从本机迁移到host2 )
2:mysql -h host2 -u root -password=password2

导出文本文件
1:select … from … into outfile ‘目标文件’[option]
2:mysqldump -u root -p password -T 目标目录 dbname table [option]
3:mysql -u root -p password -e “select 语句” dbname > 文件路径

导入文本文件
1:load data [ low_priority | concurrent ] [ local ] infile file_name into table table_name [ option ]
2:mysqlimport --no-defaults -u root -p password -t database file_name [ option

权限及安全控制

创建用户
create user user1 [IDENTIFIED BY [PASSWORD ‘PASSWORD’]] ,user user2…

删除用户
drop user user1,user2…

重命名用户
rename user user1 to new_user1,user2 to new_user2…

权限控制
GRANT和REVOKE

权限生效
1:flush privileges
2:mysqladmin flush-privileges
3:mysqladmin reload

设置账户密码
1:mysqladmin -u user_name -h host_name password ‘newpwd’ (DOS命令)
2:set password [ for ‘jeffrey’@’%’ ] = password(‘biscuit’)
3:grant usage on . to ‘jeffrey’@’%’ identified by ‘biscuit’ (全局级别)
4:update user set password = password(‘bagel’) where host =‘xxx’ and user = ‘xxx’; (mysql数据库自带的user表)