Mysql

显示数据库

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() | 随机数 |