SQL

一、基础

模式定义了数据如何存储、存储什么样的数据以及数据如何分解等信息，数据库和表都有模式。

主键的值不允许修改，也不允许复用（不能将已经删除的主键值赋给新数据行的主键）。

SQL（Structured Query Language)，标准 SQL 由 ANSI 标准委员会管理，从而称为 ANSI SQL。各个 DBMS 都有自己的实现，如 PL/SQL、Transact-SQL 等。

SQL 语句不区分大小写，但是数据库表名、列名和值是否区分依赖于具体的 DBMS 以及配置。

SQL 支持以下三种注释：

# 注释
SELECT *
FROM mytable; -- 注释
/* 注释1
   注释2 */

数据库创建与使用：

CREATE DATABASE test;
USE test;

 二、创建表

CREATE TABLE mytable (
  # int 类型，不为空，自增
  id INT NOT NULL AUTO_INCREMENT,
  # int 类型，不可为空，默认值为 1，不为空
  col1 INT NOT NULL DEFAULT 1,
  # 变长字符串类型，最长为 45 个字符，可以为空
  col2 VARCHAR(45) NULL,
  # 日期类型，可为空
  col3 DATE NULL,
  # 设置主键为 id
  PRIMARY KEY (`id`));

三、修改表

//添加列
ALTER TABLE mytable
ADD col CHAR(20);

//删除列
ALTER TABLE mytable
DROP COLUMN col;

//删除表
DROP TABLE mytable;

四、插入

//普通插入
INSERT INTO mytable(col1, col2)
VALUES(val1, val2);

//插入索引出来的数据

INSERT INTO mytable1(col1, col2)
SELECT col1, col2
FROM mytable2;

//将一个表的内容插入到一个新表
CREATE TABLE newtable AS
SELECT * FROM mytable;

 五、更新

UPDATE mytable
SET col = val
WHERE id = 1;

六、删除

DELETE FROM mytable
WHERE id = 1;

//TRUNCATE TABLE 可以清空表，也就是删除所有行
TRUNCATE TABLE mytable;

 七、查询

//DISTINCT
//相同值只会出现一次。它作用于所有列
//也就是说所有列的值都相同才算相同。
SELECT DISTINCT col1, col2
FROM mytable;

//LIMIT
//限制返回的行数。可以有两个参数
//第一个参数为起始行，从 0 开始；第二个参数为返回的总行数。

//返回前五行
SELECT *
FROM mytable
LIMIT 5;
 
SELECT *
FROM mytable
LIMIT 0, 5;

//返回3-5行
SELECT *
FROM mytable
LIMIT 2, 3;

八、排序

• ASC ：升序（默认）
• DESC ：降序

//可以按多个列进行排序，并且为每个列指定不同的排序方式
SELECT *
FROM mytable
ORDER BY col1 DESC, col2 ASC;

九、过滤

不进行过滤的数据非常大，导致通过网络传输了多余的数据，从而浪费了网络带宽。因此尽量使用 SQL 语句来过滤不必要的数据，而不是传输所有的数据到客户端中然后由客户端进行过滤。

 

下表显示了 WHERE 子句可用的操作符

操作符	说明
=	等于
<	小于
>	大于
<> !=	不等于
<= !>	小于等于
>= !<	大于等于
BETWEEN	在两个值之间
IS NULL	为 NULL 值

 注：NULL 与 0、空字符串都不同。

十、通配符

• % 匹配 >=0 个任意字符；
• _ 匹配 ==1 个任意字符；
• [ ] 可以匹配集合内的字符，例如 [ab] 将匹配字符 a 或者 b。用脱字符 ^ 可以对其进行否定，也就是不匹配集合内的字符。

使用 Like 来进行通配符匹配。

十一、计算字段

在数据库服务器上完成数据的转换和格式化的工作往往比客户端上快得多，并且转换和格式化后的数据量更少的话可以减少网络通信量。

计算字段通常需要使用 AS 来取别名

CONCAT() 用于连接两个字段。许多数据库会使用空格把一个值填充为列宽，因此连接的结果会出现一些不必要的空格，使用 TRIM() 可以去除首尾空格。

SELECT CONCAT(TRIM(col1), '(', TRIM(col2), ')') AS concat_col
FROM mytable;

十二、函数

函 数	说 明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

AVG() 会忽略 NULL 行。

函数	说明
LEFT()	左边的字符
RIGHT()	右边的字符
LOWER()	转换为小写字符
UPPER()	转换为大写字符
LTRIM()	去除左边的空格
RTRIM()	去除右边的空格
LENGTH()	长度
SOUNDEX()	转换为语音值

其中， SOUNDEX() 可以将一个字符串转换为描述其语音表示的字母数字模式。

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()	返回一个日期的年份部分

数值处理

函数	说明
SIN()	正弦
COS()	余弦
TAN()	正切
ABS()	绝对值
SQRT()	平方根
MOD()	余数
EXP()	指数
PI()	圆周率
RAND()	随机数