MySQL的SQL语法

一、MySQL编码规范

• 全部采用小写字母命名，字母开头，下划线分隔
• 数据库命名方式：系统名_子系统名
• 表名命名方式：t_模块名_表名
• 字段均需要设置 not null，避免null值出现

二、DDL

• create

# 获取MySQL的帮助信息
mysql > help create database
# 创建数据库
create database if not exists test default charset utf8 collate utf8_general_ci;

# 创建数据表
create table if not exists t_students 
(
  id int unsigned primary key auto_increment,
  name varchar(10) not null,
  gender char(1) not null,
  phone char(11) not null,
  address varchar(100) not null
) engine=innodb default charset=utf8;
# 创建视图，视图本身并不存放数据，相当于查询真实表命令的一个别名
create view view_students as select stuid,name,age from students;
# 创建索引
create index index_age on students(age);
help create index;
# 创建存储过程
mysql> delimiter //  #修改语句结束标识符

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM t;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> CALL simpleproc(@a);  #调用存储过程
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a;
+------+
| @a   |
+------+
| 3    |
+------+
1 row in set (0.00 sec)

• drop

# 删除数据库
drop database db_name;
# 删除数据表
drop tabale t_name;
truncate table table_name;  # 清空表内数据保留表的结构
# 删除视图
drop view view_students;
# 删除索引
drop index index_age on students;
# 删除存储过程
DROP PROCEDURE IF EXISTS sp_name;
# 删除MySQL用户
drop user test@'192.168.30.%';

• alter (数据库或表创建后一般很少修改）

# 修改存储引擎
alter table t_name engine=innodb;
# 删除外键
alter table drop foreign key fk_name;

三、DML

• update：可同时更新一个或多个字段

use test;
update t_students set gender='f' where id=2;

• insert

insert into t_students (name,gender,phone,address) values ('li hao','m','11144511222','buskkkkk');

• delete

delete from t_stdents where id=3;

四、DQL

• 简单的一条语句

# 此为一组SQL语句，由三个子句构成，SELECT,FROM和WHERE都是关键字
select * from t_students where id=3;
select * from t_students where id between 2 and 5;
select * from t_students where name='liu' and gender='f';
# 视图可以像数据表一样查询
select * from view_students;

• 模糊查询，%匹配任意字符任意长度，_ 匹配任意单个字符

select * from t_students where name like '%li';
select * from t_students where name like '__';

• 分组查询,根据指定的条件把查询结果进行“分组”以用于做“聚合”运算。常用：avg(), max(), min(), count(), sum()

select gender,count(id) as 'number of student' from t_students group by gender;

• 排序查询

select * from t_students order by name asc;
select * from t_students order by name desc;
# 对查询结果的行数进行 数量限制
select * from t_students order by name desc limit 3;

• 多表查询

# 内连接查询
select students.name,classes.name from students inner join classes on students.classid=classes.id;
# 左外连接查询
select students.name,classes.name from students left outer join classes on students.classid=classes.id;
# 右外连接查询
select students.name,classes.name from students right outer join classes on students.classid=classes.id;

• 纵向组合两个表中数据到一个表中

select stuid,name from students
union
select tid,name from teachers ;

五、DCL

• grant

# 创建用户并授权
grant all on db_name.* to test@'192.168.30.7' identified by 'password';
grant select,insert on db_name.* to test@'192.168.%.%' identified by 'password';

• revoke

REVOKE DELETE ON testdb.* FROM 'testuser'@'%‘;

六、其他命令

• 查看数据库

show databases;

• 查看变量 (官方参考文档)

# 查看会话变量
show variables like 'innodb_file%';
#查看全局变量
show global variables like 'autocommit';
select @@autocommit;
# 查询状态变量
show global status like 'Qcache%';
show global status like 'innodb%read%';
# 查看事务隔离设置
select @@tx_isolation;

• 设置变量

# 查看帮助
help set
# 设置会话变量
SET system_var_name=value;
# 设置全局变量
SET GLOBAL system_var_name=value;

• 查看存储过程

show procedure status;

• 查看数据表状态

show table status from db_name;
show table ststus like 'table_name';

• 查看数据表定义

show create table table_name;

• 查看索引状态

show indexes from students;
# 查看索引的使用数量
set global userstat=1;
show index_statistics;  # 只要有查询结果才能在这里统计到

• 查看用户权限

SHOW GRANTS FOR 'user'@'host';
SHOW GRANTS FOR CURRENT_USER;

• 事务的使用（事务中如果使用DDL、DCL操作是无法撤销的）

# 设置事务的隔离级别
set tx_isolation='SERIALIZABLE'  # 这四个枚举值之一READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE

#显示的开启一个事务
start transaction;    
update students set gender='F' where stuid=23;
update students set age=40 where stuid=23;
rollback;  #回滚事务，结束事务并撤销所有未提交的修改。如果要保存修改，使用commit；
select * from students;  # 将看到没有任何修改

• 查看命令帮助

help KEYWORD;

• 查看支持所有字符集

SHOW CHARACTER SET;