显示数据库
| 1 |
show databases；
|
创建数据库
| 1 2 |
CREATE DATABASE 数据库名称 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
CREATE DATABASE 数据库名称 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
|
删除数据库
| 1 |
drop 数据库名
|
创建表
| 1 2 3 4 |
create table 表名(
列名 类型 是否可以为空，
列名 类型 是否可以为空
)ENGINE=InnoDB DEFAULT CHARSET=utf8
|
| 1 2 3 4 5 6 |
CREATE TABLE test1 (
id INT NOT NULL AUTO_INCREMENT, # int 类型，不为空，自增
col1 INT NOT NULL DEFAULT 1, # int 类型，不可为空，默认值为 1，不为空
col2 VARCHAR(45) NULL, # 变长字符串类型，最长为 45 个字符，可以为空
col3 DATE NULL, # 日期类型，可为空
PRIMARY KEY ('id')); # 设置主键为 id
|
修改表-添加列
| 1 2 |
ALTER TABLE mytable
ADD col CHAR(20);
|
修改表-删除列
| 1 2 |
ALTER TABLE mytable
DROP COLUMN col;
|
删除表
| 1 |
DROP TABLE mytable;
|
重命名表
| 1 |
rename table mytable to mytable2
|
向表中插入数据
| 1 2 |
INSERT INTO test(col1, col2)
VALUES(val1, val2);
|
向表中插入检索出来的数据
| 1 2 3 |
INSERT INTO mytable1(col1, col2)
SELECT col1, col2
FROM mytable2;
|
将一个表的内容插入到一个新表
| 1 2 |
CREATE TABLE newtable AS
SELECT * FROM mytable;
|
更新表中数据
| 1 2 3 |
UPDATE test
SET col2 = 'val', name = "test1"
WHERE id = 1;
|
删除表中数据
| 1 2 |
DELETE FROM mytable
WHERE id = 1;
|
TRUNCATE TABLE 清空表，也就是删除所有行
| 1 |
TRUNCATE TABLE mytable;
|
DISTINCT， 相同值只会出现一次
| 1 2 |
SELECT DISTINCT col1, col2
FROM mytable;
|
LIMIT 限制返回的行数。可以有两个参数，第一个参数为起始行，从 0 开始(省略时，默认从0开始）；第二个参数为返回的总行数。
| 1 2 3 |
SELECT *
FROM mytable
LIMIT 5;
|
| 1 2 3 |
SELECT *
FROM mytable
LIMIT 0, 5;
|
| 1 2 3 |
SELECT *
FROM mytable
LIMIT 2, 3; --返回第 3 ~ 5 行：
|
GROUP BY 分组
| 1 2 3 |
SELECT col, COUNT(*) AS num
FROM mytable
GROUP BY col;
|
ORDER BY 排序 默认ASC：升序，可省略； DESC：降序
| 1 2 3 |
SELECT *
FROM mytable
ORDER BY col1 DESC, col2 ASC;
|
GROUP BY 自动按分组字段进行排序，ORDER BY 也可以按汇总字段来进行排序
| 1 2 3 4 |
SELECT col, COUNT(*) AS num
FROM mytable
GROUP BY col
ORDER BY num;
|
WHERE 过滤行，HAVING 过滤分组，行过滤应当先于分组过滤
| 1 2 3 4 5 |
SELECT col, COUNT(*) AS num
FROM mytable
WHERE col > 2
GROUP BY col
HAVING num >= 2;
|
- GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前；
- 除了汇总字段外，SELECT 语句中的每一字段都必须在 GROUP BY 子句中给出；???
- NULL 的行会单独分为一组；
- 大多数 SQL 实现不支持 GROUP BY 列具有可变长度的数据类型。
子查询
子查询中只能返回一个字段的数据
可以将子查询的结果作为 WHRER 语句的过滤条件
| 1 2 3 4 |
SELECT *
FROM mytable1
WHERE col1 IN (SELECT col2
FROM mytable2);
|
检索客户的订单数量
| 1 2 3 4 5 6 |
SELECT cust_name, (SELECT COUNT(*)
FROM Orders
WHERE Orders.cust_id = Customers.cust_id)
AS orders_num
FROM Customers
ORDER BY cust_name;
|
过滤条件
| 1 2 3 |
SELECT *
FROM mytable
WHERE col IS NULL;
|
LIKE 通配符
% 匹配 任意字符(0个或多个)；
_ 匹配 任意字符（1个）；
[ ] 可以匹配集合内的字符，例如 [ab] 将匹配字符 a 或者 b。字符 ^ 表示不匹配集合内的字符。
| 1 2 3 |
SELECT *
FROM mytable
WHERE col LIKE '[^AB]%'; -- 不以 A 或 B 开头的任意文本
|
AS 取别名
| 1 2 |
SELECT col1 * col2 AS alias
FROM mytable;
|
CONCAT() 用于连接两个字段。许多数据库会使用空格把一个值填充为列宽，因此连接的结果会出现一些不必要的空格，使用 **TRIM()** 可以去除首尾空格。
| 1 2 |
SELECT CONCAT(TRIM(col1), '(', TRIM(col2), ')') AS concat_col
FROM mytable;
|
INNER JOIN 多表查询，连接
| 1 2 3 4 5 6 7 |
SELECT DISTINCT tc.module, m.name, count(*) AS count
FROM test.uitestcase tc
INNER JOIN module m
ON tc.script_name LIKE 'Item%' AND m.id = tc.module
GROUP BY module
ORDER BY tc.module DESC
LIMIT 2, 4;
|
函数
| AVG() | 返回某列的平均值 |
| COUNT() | 返回某列的行数 |
| MAX() | 返回某列的最大值 |
| MIN() | 返回某列的最小值 |
| SUM() | 返回某列值之和 |
AVG() 会忽略 NULL 行。
使用 DISTINCT 可以汇总不同的值。
| 1 2 |
SELECT AVG(DISTINCT col1) AS avg_col
FROM mytable;
|
文本处理
| LEFT() | 左边的字符 |
| RIGHT() | 右边的字符 |
| LOWER() | 转换为小写字符 |
| UPPER() | 转换为大写字符 |
| LTRIM() | 去除左边的空格 |
| RTRIM() | 去除右边的空格 |
| LENGTH() | 长度 |
| SOUNDEX() | 转换为语音值 |
其中， SOUNDEX() 可以将一个字符串转换为描述其语音表示的字母数字模式
| 1 2 3 |
SELECT *
FROM mytable
WHERE SOUNDEX(col1) = SOUNDEX('apple')
|
日期和时间处理
- 日期格式：YYYY-MM-DD
- 时间格式：HH:MM:SS
| ADDDATE() | 增加一个日期（天、周等） |
| ADDTIME() | 增加一个时间（时、分等） |
| CURDATE() | 返回当前日期 |
| CURTIME() | 返回当前时间 |
| DATE() | 返回日期时间的日期部分 |
| DATEDIFF() | 计算两个日期之差 |
| DATE_ADD() | 高度灵活的日期运算函数 |
| DATE_FORMAT() | 返回一个格式化的日期或时间串 |
| DAY() | 返回一个日期的天数部分 |
| DAYOFWEEK() | 对于一个日期，返回对应的星期几 |
| HOUR() | 返回一个时间的小时部分 |
| MINUTE() | 返回一个时间的分钟部分 |
| MONTH() | 返回一个日期的月份部分 |
| NOW() | 返回当前日期和时间 |
| SECOND() | 返回一个时间的秒部分 |
| TIME() | 返回一个日期时间的时间部分 |
| YEAR() | 返回一个日期的年份部分 |
| 1 |
SELECT NOW(); --结果： 2019-12-15 20:25:11
|
数值处理
| SIN() | 正弦 |
| COS() | 余弦 |
| TAN() | 正切 |
| ABS() | 绝对值 |
| SQRT() | 平方根 |
| MOD() | 余数 |
| EXP() | 指数 |
| PI() | 圆周率 |
| RAND() | 随机数 |