MySQL语法篇

一、SQL语句类型

DDL: 数据库定义语言,数据库、表、视图、索引、存储过程等,如,CREATE/DROP/ALTER等;
DML: 数据库操作语言,如INSERT/DELETE/UPDATE/SELECT等;
DCL: 数据库控制语言,如控制用户访问权限GRANT/REVOKE;

二、数据库操作

#创建数据库
create database dbname character set utf8 collate utf8_general_ci;

#查看数据库
show datbases;
show create database dbname;
select database();

#选择数据库
USE dbname;

#删除数据库
DROP DATABASE dbname;

#修改数据量
ALTER DATABASE dbname charset utf8;

#查看帮助
help CREATE DATABASE

三、数据表操作

3.1 表的创建
#创建数据表语法
CREATE TABLE tbname (
字段1 类型[宽度] [约束条件],
字段2 类型[宽度] [约束条件],
字段3 类型[宽度] [约束条件],
);

#创建表示例
CREATE TABLE student (
id int;
name varchar(50),
sex enum('male','female'),
age int(3)
);

#查看库下所有表
SHOW TABLES;

#查看表结构
DESCRIBE student;

#查看表创建
SHOW CREATE TABLE student\G;
3.2 表的修改
#修改数据表名称
ALTER TABLE old_name RENAME new_name;

#增加表字段
ALTER TABLE student ADD 字段1 数据类型 [约束条件];
ALTER TABLE student ADD 字段1 数据类型 [约束条件],字段2 数据类型 [约束条件];
ALTER TABLE student ADD 字段1 数据类型 [约束条件] FIRST; #添加到第一个字段位置
ALTER TABLE student ADD 字段1 数据类型 [约束条件] AFTER 字段3; #添加到字段3后面的位置

#删除表字段
ALTER TABLE 表名 DROP 字段名;

#修改表字段
ALTER TABLE 表名 MODIFY 字段名 数据类型 [约束条件]; #只改数据类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型 [约束条件]; #改字段名
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [约束条件]; #改字段与数据类型
3.3 表的删除
DROP TABLE 表名;
3.3 表的复制
#把查询结果写入到新表中,表结构+记录(主键,外键和索引不会复制)
create table new_table select * from mysql.user;

#仅赋值表结构,使用假条件来返回表结构到新表中
create table new_table select * from mysql.user where 1=2;

#或者使用like实现表结构复制
create table new_table like mysql.user;

四、增删改查

4.1 插入数据
#顺序插入数据
INSERT INTO 表名 (字段1,字段2,字段3...) VALUE (值1,值2,值3...);
INSERT INTO 表名 VALUE (值1,值2,值3...);

#插入多条记录
INSERT INTO 表名 VALUE
(值1,值2,值3...),
(值1,值2,值3...),
(值1,值2,值3...);

#插入查询结果
INSERT INTO 表名 (字段1,字段2,字段3...)
    SELECT (字段1,字段2,字段3...) FROM 表2 WHERE ...;
4.2 更新数据
UPDATE mysql.user SET password=password('123123')
    WHERE user='root' AND HOST='localhost';
4.3 删除数据
DELETE FROM mysql.user WHERE PASSWORD='';
4.4 查询数据
#聚合函数
1.max: 最大值
2.min: 最小值
3.avg: 平均值
4.sum: 求和
5.count: 统计
#查询的标准使用
SELECT DISTINCT 字段1,字段2,字段3,... FROM 表2
    WHERE 匹配条件
    GROUP BY 分组条件
    HAVING 过滤
    ORDER BY 排序字段
    LIMIT N;

#去重查询
SELECT DISTINCT stu_name FROM student;

#四则运算
SELECT stu_name,score*100 AS 分数 FROM student;

#显示格式,CONCAT()用于连接字符串
SELECT CONCAT('姓名: ',name, '性别: ',sex) AS info FROM student;
SELECT CONCAT('姓名: ',name, '性别: ',sex) AS info,CONCAT('电话: ',phone, '住址: ',address) AS personal FROM student;

五、单表查询

5.1 WHERE
1.比较运算符:> < >= <= <> !=
2.不闭合区间: between 80 and 100
3.成员运算符: in (80,9,100)
4.身份匹配符: is Null or is not Null
4.模糊匹配: like 'egon%' or like 'ega_' #"%"表示任意字符,"_"表示一个字符
#单条件匹配查询
SELECT name,port FROM emp
    WHERE port = 'sale';

#多条件匹配查询
SELECT name,id,port FROM emp
    WHERE port = 'sale' AND id > 7;
SELECT name,id,port FROM emp
    WHERE port = 'sale' OR id > 7;

#区间条件匹配
SELECT name,salary FROM emp
    WHERE salary BETWEEN 20000 AND 30000;

#成员包含匹配
SELECT name,id FROM emp
    WHERE id in (80,70,20);

#身份条件匹配
SELECT * FROM emp
    WHERE post is Null;
SELECT * FROM emp
    WHERE post is not Null;

#模糊条件匹配
SELECT * FROM emp
    WHERE name like 'jin%';
SELECT * FROM emp
    WHERE name like 'jin_';
5.2 GRUOP BY
#按照同类型分组(分完组以后只能查看分组字段的第一行的值,需要执行如下设置)
SET GLOBAL sql_mode="ONLY_FULL_GROUP_BY";
SELECT * FROM emp GROUP BY sex;

#聚合函数+分组统计
SELECT post,count(id) FROM emp GROUP BY sex;
SELECT post,avg(salary) FROM emp GROUP BY sex;
SELECT post,max(salary) FROM emp GROUP BY sex;
SELECT post,min(salary) FROM emp GROUP BY sex;
SELECT post,sum(age) FROM emp GROUP BY sex;
5.3 HAVING
注意事项:
1.WHERE发生在GROUP BY之前,因而WHERE中可以有任意字段,但是绝对不能使用聚合函数作为条件;
2.HAVING发送在GROUP BY之后,因而HAVING中可以使用分组的字段,无法直接取到其他字段,可以使用聚合函数作为条件;
#group_concat()用于分组查询中的字符串拼接
SELECT post,group_concat(name),count(id) FROM emp
    GROUP BY post HAVING count(id) < 2;
5.4 ORDER BY
#单列排序,默认升序(ASC)排列,降序使用(DESC)
SELECT * FROM emp
    GROUP BY age
    ORDER BY DESC;
SELECT * FROM emp
    GROUP BY age
    ORDER BY ASC;

#多列排序
SELECT * FROM emp
    ORDER BY age,
    salary DESC;
5.5 LIMIT
#查询3条,默认初始位置为0
SELECT * FROM emp limit 3;

#从0开始,即先查询第一条,然后往后查5条(含第一条)
SELECT * FROM emp limit 0,5;

#从5开始,即先查询第六条,然后往后查5条(含第6条)
SELECT * FROM emp limit 5,5;

六、正则查询

#如下示例,查询出jin开头的用户信息;
select * from emp where name like 'jin%'; #使用like匹配,无正则
select * from emp where name regexp '^jin'; #使用正则匹配

七、多表查询

7.1 表连接方式
1.内连接: 只取两张表的共同部分;
2.左连接: 在内连接基础上保留左表记录,无匹配为NULL;
3.右连接: 在内连接基础上保留右表记录,无匹配为NULL;
4.全外连接: 在内连接的基础上保留左右两表没有对应关系的记录;
7.2 表连接实现
#内连接
select * from emp inner join
    dep on emp.id = dep.id;

#左连接
select * from emp left join
    dep on emp.id = dep.id;

#右连接
select * from emp right join
    dep on emp.id = dep.id;

#外连接
select * from emp full join
    dep on emp.id = dep.id;
7.3 子查询
#查询部门平均年龄大于25岁的部门名
select name from dep where id = (
select dep_id from emp
    group by dep_id
    having avg(age) > 25
);

八、权限管理

#用户授权
GRANT ALL ON smzuat_20201012.* TO 'user1'@'172.16.0.16' IDENTIFIED BY 'qsEEd6^g%2NF39Za';
GRANT SELECT ON mysql.help_topic TO 'user2'@'172.16.0.15' IDENTIFIED BY 'qsEEd6^g%2NF39Za';
FLUSH PRIVILEGES;

#权限回收
revoke all privileges on smzuat_filter1012.*  from 'user1'@'172.16.0.15';
revoke SELECT ON mysql.help_topic from 'user2'@'172.16.0.15';
FLUSH PRIVILEGES;