下面是选自 "Websites" 表的数据：
```
+----+--------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
+----+---------------+---------------------------+-------+---------+
```
下面是 "access_log" 网站访问记录表的数据：
```
mysql> SELECT * FROM access_log;
+-----+---------+-------+------------+
| aid | site_id | count | date |
+-----+---------+-------+------------+
| 1 | 1 | 45 | 2016-05-10 |
| 2 | 3 | 100 | 2016-05-13 |
| 3 | 1 | 230 | 2016-05-14 |
| 4 | 2 | 10 | 2016-05-14 |
| 5 | 5 | 205 | 2016-05-14 |
| 6 | 4 | 13 | 2016-05-15 |
| 7 | 3 | 220 | 2016-05-15 |
| 8 | 5 | 545 | 2016-05-16 |
| 9 | 3 | 201 | 2016-05-17 |
+-----+---------+-------+------------+
9 rows in set (0.00 sec)
```
- DISTINCT
```
SELECT DISTINCT column_name,column_name FROM table_name;
```
- select
```
SELECT column_name,column_name FROM table_name WHERE column_name operator value;
```
- where
```
SELECT * FROM Websites WHERE alexa > 15 AND (country='CN' OR country='USA');
```
- order by
```
SELECT column_name,column_name FROM table_name ORDER BY column_name,column_name ASC|DESC;
```
- insert into
```
INSERT INTO table_name VALUES (value1,value2,value3,...);
INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);
```
- update
```
UPDATE table_name SET column1=value1,column2=value2,... WHERE some_column=some_value;
```
```
update table_name set id=1,name="war3" ; 这样没有where的话,会将表里的所有数据的这两个字段全改了;
为了安全，都需要启动更新锁： set sql_safe_updates=1;
```
```
DELETE FROM table_name WHERE some_column=some_value;
```
- limit
```
SELECT column_name(s) FROM table_name LIMIT number;
```
- like
```
SELECT * FROM Websites WHERE name LIKE '%k';
SELECT * FROM Websites WHERE name NOT LIKE '%oo%';
```
- 通配符
```
% 替代一个或多个字符
_ 仅替代一个字符
[charlist] 字符列中的任何单一字符
[^charlist]或者[!charlist] 不在字符列中的任何单一字符
其中搭配以上通配符可以让LIKE命令实现多种技巧：
1、LIKE'Mc%' 将搜索以字母 Mc 开头的所有字符串（如 McBadden）。
2、LIKE'%inger' 将搜索以字母 inger 结尾的所有字符串（如 Ringer、Stringer）。
3、LIKE'%en%' 将搜索在任何位置包含字母 en 的所有字符串（如 Bennet、Green、McBadden）。
4、LIKE'_heryl' 将搜索以字母 heryl 结尾的所有六个字母的名称（如 Cheryl、Sheryl）。
5、LIKE'[CK]ars[eo]n' 将搜索下列字符串：Carsen、Karsen、Carson 和 Karson（如 Carson）。
6、LIKE'[M-Z]inger' 将搜索以字符串 inger 结尾、以从 M 到 Z 的任何单个字母开头的所有名称（如 Ringer）。
7、LIKE'M[^c]%' 将搜索以字母 M 开头，并且第二个字母不是 c 的所有名称（如MacFeather）。
```
- in(满足其中之一)
```
SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...);
```
- between(闭区间，包含两端)
```
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
```
```
SELECT * FROM Websites WHERE (alexa BETWEEN 1 AND 20) AND country NOT IN ('USA', 'IND');
```
- 别名
```
列的 SQL 别名语法
SELECT column_name AS alias_name FROM table_name;
表的 SQL 别名语法
SELECT column_name(s) FROM table_name AS alias_name;
```
- join
```
1.INNER JOIN：如果表中有至少一个匹配，则返回行
2.LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
3.RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
4.FULL JOIN：只要其中一个表中存在匹配，则返回行
```
```
select mem.id,cen.remark from member mem inner join census_login_on_app_or_h5 cen on mem.id = cen.user_id ;
SELECT Websites.id, Websites.name, access_log.count, access_log.date FROM Websites INNER JOIN access_log ON Websites.id=access_log.site_id;
```
- UNION
```
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。
```
```
SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;
注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
SELECT country, name FROM Websites WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps WHERE country='CN' ORDER BY country;
```
- 复制表
```
CREATE TABLE 新表 AS SELECT * FROM 旧表
```
- SELECT INTO
```
MySQL 数据库不支持 SELECT ... INTO 语句，但支持 INSERT INTO ... SELECT
```
```
我们可以复制所有的列插入到新表中：
SELECT * INTO newtable [IN externaldb] FROM table1;
或者只复制希望的列插入到新表中：
SELECT column_name(s) INTO newtable [IN externaldb] FROM table1;
```
- CREATE
```
CREATE DATABASE dbname;
```
```
CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
```
- 约束
```
NOT NULL - 指示某列不能存储 NULL 值。
UNIQUE - 保证某列的每行必须有唯一的值。
PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK - 保证列中的值符合指定的条件。
DEFAULT - 规定没有给列赋值时的默认值。
```
- NOT NULL
```
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
```
删除表的字段的 not null 约束：
```
alter table x modify column_name null;
alter table x modify column_name not null;
```
- UNIQUE
```
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)
```
当表已被创建时，如需在 "P_Id" 列创建 UNIQUE 约束，请使用下面的 SQL：
```
ALTER TABLE Persons ADD UNIQUE (P_Id)
```
如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束，请使用下面的 SQL 语法：
```
ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
```
如需撤销 UNIQUE 约束，请使用下面的 SQL：
```
ALTER TABLE Persons DROP INDEX uc_PersonID
```
- PRIMARY KEY
```
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)
```
当表已被创建时，如需在 "P_Id" 列创建 PRIMARY KEY 约束，请使用下面的 SQL：
```
ALTER TABLE Persons ADD PRIMARY KEY (P_Id)
```
如需撤销 PRIMARY KEY 约束，请使用下面的 SQL：
```
ALTER TABLE Persons DROP PRIMARY KEY
```
- FOREIGN KEY
```
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)
```
- CHECK
如果对单个列定义 CHECK 约束，那么该列只允许特定的值。
如果对一个表定义 CHECK 约束，那么此约束会基于行中其他列的值在特定的列中对值进行限制。
```
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)
```
- DEFAULT
```
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)
```
- CREATE INDEX
更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引
```
在表上创建一个简单的索引。允许使用重复的值：
CREATE INDEX index_name ON table_name (column_name)
```
- DROP
用于 MySQL 的 DROP INDEX 语法：
```
ALTER TABLE table_name DROP INDEX index_name
```
```
DROP TABLE table_name
DROP DATABASE database_name
TRUNCATE TABLE table_name(清空表)
```
###### 函数
- AVG()
```
SELECT AVG(column_name) FROM table_name
SELECT AVG(count) AS CountAverage FROM access_log;
```
- COUNT
```
SELECT COUNT(column_name) FROM table_name;
SELECT COUNT(count) AS nums FROM access_log WHERE site_id=3;
```
- MAX()
```
SELECT MAX(column_name) FROM table_name;
SELECT MAX(alexa) AS max_alexa FROM Websites;
```
- MIN()
```
SELECT MIN(column_name) FROM table_name;
SELECT MIN(alexa) AS min_alexa FROM Websites;
```
- SUM()
```
SELECT SUM(column_name) FROM table_name;
SELECT SUM(count) AS nums FROM access_log;
```
- GROUP BY
GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组
```
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
SELECT site_id, SUM(access_log.count) AS nums
FROM access_log GROUP BY site_id;
SELECT Websites.name,COUNT(access_log.aid) AS nums FROM access_log
LEFT JOIN Websites
ON access_log.site_id=Websites.id
GROUP BY Websites.name;
```
- HAVING
在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。
HAVING 子句可以让我们筛选分组后的各组数据。
```
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;
```
```
SELECT Websites.name, Websites.url, SUM(access_log.count) AS nums FROM (access_log
INNER JOIN Websites
ON access_log.site_id=Websites.id)
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;
// 结果如下图
```
```
SELECT Websites.name, SUM(access_log.count) AS nums FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id
WHERE Websites.alexa < 200
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;
// 结果如下图
```
- UCASE()
UCASE() 函数把字段的值转换为大写。
```
SELECT UCASE(column_name) FROM table_name;
SELECT UCASE(name) AS site_title, url FROM Websites;
```
- LCASE()
LCASE() 函数把字段的值转换为小写。
```
SELECT LCASE(column_name) FROM table_name;
SELECT LCASE(name) AS site_title, url FROM Websites;
```
- MID()
MID() 函数用于从文本字段中提取字符。
```
SELECT MID(column_name,start[,length]) FROM table_name;
column_name 必需。要提取字符的字段。
start 必需。规定开始位置（起始值是 1）。
length 可选。要返回的字符数。如果省略，则 MID() 函数返回剩余文本。
SELECT MID(name,1,4) AS ShortTitle FROM Websites;
```
- LEN()
LEN() 函数返回文本字段中值的长度。
```
SELECT LENGTH(column_name) FROM table_name;
SELECT name, LENGTH(url) as LengthOfURL FROM Websites;
```
- ROUND()
ROUND() 函数用于把数值字段舍入为指定的小数位数。
```
SELECT ROUND(column_name,decimals) FROM table_name;
column_name 必需。要舍入的字段。
decimals 必需。规定要返回的小数位数。
```
- NOW()
NOW() 函数返回当前系统的日期和时间。
```
select NOW();
SELECT NOW() FROM table_name;
SELECT name, url, Now() AS date FROM Websites;
```
- FORMAT()
FORMAT() 函数用于对字段的显示进行格式化。
```
SELECT FORMAT(column_name,format) FROM table_name;
column_name 必需。要格式化的字段。
format 必需。规定格式。
SELECT name, url, DATE_FORMAT(Now(),'%Y-%m-%d') AS date
FROM Websites;
```
评论区