SQL语言
SQL语言
语法基础
注释
# 使用'#'注释
-- 使用'--'注释
/*
多行注释
*/
创建使用数据库
CREATE DATABASE db01;
USE db01;
创建表
CREATE TABLE table01 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
col1 INT NOT NULL DEFAULT 1,
col2 VARCHAR(45) NULL,
col3 DATE NULL);
修改表
# 添加新列
ALTER TABLE table01 ADD col CHAR(20);
# 修改列 ALTER TABLE 表名 CHANGE 原字段名 新字段名 字段类型 约束条件
ALTER TABLE table01 CHANGE col col4 CHAR(32) NOT NULL DEFAULT 'xxx';
# 删除列
ALTER TABLE table01 DROP COLUMN col4;
# 删除表
DROP TABLE table01;
增删改
# 添加数据
INSERT INTO table01(col1, col2)
VALUES(val1, val2);
# 表数据迁移
CREATE TABLE newtable AS
SELECT * FROM table01;
# 更新
UPDATE table01
SET col = val
WHERE 条件;
# 删除
DELETE FROM table01
WHERE 条件;
查询
基础
# 去重
SELECT DISTINCT * FROM table01;
# 限制返回
SELECT * FROM table01 LIMIT 5, 2; # 从5开始（不包括5）返回两行数据，即6，7
# 排序 ASC升序，DESC降序
SELECT * FROM table01
ORDER BY col1 DESC, col2 ASC;
# 筛选
SELECT * FROM table01 WHERE 条件;
计算字段
| 函 数 | 说 明 |
|---|---|
| AVG() | 返回某列的平均值 |
| COUNT() | 返回某列的行数 |
| MAX() | 返回某列的最大值 |
| MIN() | 返回某列的最小值 |
| SUM() | 返回某列值之和 |
# 举例：计算去重的col1列的均值命名为avg_col
SELECT AVG(DISTINCT col1) AS avg_col
FROM table01;
文本处理
可以使用 LIKE + 正则表达式 筛选文本
SELECT *
FROM table01
WHERE col1 LIKE '[^AB]%'; -- 不以 A 和 B 开头的任意文本
还可以使用一些函数进行文本处理：
| 函数 | 说明 |
|---|---|
| LEFT() | 左边的字符 |
| RIGHT() | 右边的字符 |
| LOWER() | 转换为小写字符 |
| UPPER() | 转换为大写字符 |
| LTRIM() | 去除左边的空格 |
| RTRIM() | 去除右边的空格 |
| LENGTH() | 长度 |
| SOUNDEX() | 将一个字符串转换为描述其语音表示的字母数字模式 |
分组
分组就是把具有相同的数据值的行放在同一组中。可以对同一分组数据使用汇总函数进行处理。
WHERE 过滤行，HAVING 过滤分组，行过滤应当先于分组过滤。
SELECT col1, COUNT(*) AS num
FROM table01
WHERE col1 > 2
GROUP BY col1
HAVING num >= 2;
还需要注意以下几点
- GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前；
- 除了汇总字段外，SELECT 语句中的每一字段都必须在 GROUP BY 子句中给出；
- NULL 的行会单独分为一组；
- 大多数 SQL 实现不支持 GROUP BY 列具有可变长度的数据类型。
子查询
子查询中只能返回一个字段的数据。可以将子查询的结果作为 WHRER 语句的过滤条件:
SELECT *
FROM table01
WHERE col1 IN (SELECT col1
FROM table02);
连接查询
连接用于连接多个表，使用 JOIN 关键字，并且条件语句使用 ON 而不是 WHERE。
等值连接：
SELECT A.value, B.value
FROM tablea AS A INNER JOIN tableb AS B
ON A.key = B.key;
也可以不明确使用 INNER JOIN，而使用普通查询并在 WHERE 中将两个表中要连接的列用等值方法连接起来。
SELECT A.value, B.value
FROM tablea AS A, tableb AS B
WHERE A.key = B.key;
自然连接：
自然连接是把同名列通过等值测试连接起来的，同名列可以有多个。
SELECT A.value, B.value
FROM tablea AS A NATURAL JOIN tableb AS B;
外连接：
分为左外连接，右外连接以及全外连接，左外连接就是保留左表没有关联的行，右外连接同理。
SELECT A.value, B.value
FROM tablea AS A LEFT OUTER JOIN tableb as b
ON A.col1 = B.col1;
组合查询
使用 UNION 来组合两个查询，如果第一个查询返回 M 行，第二个查询返回 N 行，那么组合查询的结果一般为 M+N 行。每个查询必须包含相同的列、表达式和聚集函数。
默认会去除相同行，如果需要保留相同行，使用 UNION ALL。
SELECT *
FROM table01
WHERE col1 = 1
UNION
SELECT *
FROM table01
WHERE col1 =2;
使用视图查询
视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。对视图的操作和对普通表的操作一样。
下面是一个简单的视图创建：
CREATE VIEW myview AS
SELECT 语句;
权限管理
在mysql中，账户信息保存在 mysql 这个数据库中
USE mysql;
SELECT * FROM user;
账户操作
# 创建一个账户
CREATE USER myuser IDENTIFIED BY '密码';
# 修改用户名
RENAME myuser TO newname;
# 删除用户
DROP USER newname;
# 查看权限
SHOW GRANTS FOR myuser;
# 授权
GRANT SELECT, INSERT ON mydatabase.* TO myuser;
# 删除权限
REVOKE SELECT, INSERT ON mydatabase.* FROM myuser;
# 更改密码
SET PASSWROD FOR myuser = Password('new_password');
