SQL语句基础

本文提供了SQL语句基础练习给到各位，具体地参考了cyc2018，入门小白可以参考~

注释与数据库创建

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`)
);

修改表

1. 添加列

ALTER TABLE mytable
ADD col CHAR(20);

2. 删除列

ALTER TABLE mytable
DROP COLUMN col;

3. 删除表

DROP TABLE mytable;

插入

1. 普通插入

INSERT INTO mytable(col1,col2)
VALUES(val1,val2);

2. 插入检索出来的数据

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

3. 将一个表的内容插入到一个新表

SELECT TABLE newtable AS
SELECT * FROM mytable;

更新

# 将表中id为1的某列的值更新
UPDATE mytable
SET col = val
WHERE id = 1;

删除

# 将表中id为1的数据删除
DELECT 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语句来过滤不必要的数据，而不是传输所有的数据到客户端中然后由客户端进行过滤。

SELECT * FROM mytable
WHERE col IS NULL;

用where子句可用的操作符：

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

注意点：

1. NULL与0、空字符串不同；
2. AND或OR用于连接多个过滤条件，优先处理AND；
3. IN操作用于匹配一组值，其后也可以接一个SELECT子句，从而匹配子查询得到的一组值；
4. NOT操作符由于否定一个条件。

通配符

通配符也是用在过滤语句中，但它只能用于文本字段。

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

使用like来进行通配符匹配：

SELECT * FROM mytable
WHERE col LIKE '[^AB]%';

计算字段

计算字段通常需要使用AS来取别名，否则输出的时候字段名为计算表达式。

SELECT col1*col2 AS yfj
FROM mytable;

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

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

函数

MySQL中的数学函数

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

使用DISTINCT可以汇总不同的值：

SELECT AVG(DISTINCT col1) AS avg_col
FROM mytable;

MySQL中的文本处理函数

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

日期和时间处理

• 日期格式：YYYY-MM-DD
• 时间格式：HH:MM:SS
  |函数|说明|
  |–|--|
  | ADDDATE() | |
  | ADDTIME() | |
  | CURDATE() | |
  | CURTIME | |
  | DATE() | |
  | TIME() | |
  | DATEDIFF() | |
  | DAY() | |
  | DAYOFWEEK() | |
  | NOW() | |

mysql>SELECT NOW();
# 输出：2021-6-25 20:40:00

数值处理

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

分组

把具有相同的数据值得行放在同一组
可以对同一分组数据使用汇总函数进行处理，例如求分组数据的平均值
指定的分组字段除了能按该字段进行分组，也会按该字段进行排序

SELECT col,COUNT(*) AS num
FROM mytable
GROUP BY col;

GROUP BY自动按照分组字段进行排序，ORDER BY也可以按汇总字段进行排序

SELECT col,COUNT(*) AS num
FROM mytable
GROUP BY col
ORDER BY num;

WHERE过滤行，HAVING过滤分组，行过滤应该优先于分组过滤

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列具有可变长度的数据类型。

子查询

子查询中只能返回一个字段的数据
可以将子查询的结果作为WHERE语句的过滤条件

SELECT * FROM mytable1
WHERE col IN(SELECT col2 FROM mytable2)

下面的语句可以检索出客户的订单数量：

SELECT cust_name,(SELECT COUNT(*)
                  FROM Orders
                  WHERE Customers.cust_id=Orders.cust_id)
                  AS orders_num
FROM Customers
ORDER BY cust_name;

连接

连接用于连接多个表，使用JOIN关键字，并且条件语句使用ON而不是WHERE;
连接可以替换子查询，效率一般比子查询快；
可以用AS给列名、计算字段和表名取别名，给表名取别名是为了简化SQL语句以及连接相同表。

内连接

内连接又称等值连接，用于连接两个表，使用INNER JOIN关键字。

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;

自连接

自连接可以看成是内连接的一种，只是连接的表示自身而已。

一张员工表，包含员工姓名和员工所属部门，要找出与Jim处在同一部门的所有员工姓名。

1. 子查询版本

SELECT name
FORM employee
WHERE department=(SELECT department
                  FROM employee
                  WHERE name='Jim');

2. 自连接版本

SELECT e1.name
FROM tablea as e1 INNER JOIN tableb as e1
ON e1.department=e2.department
AND e2.name='Jim';

自然连接

自然连接和自连接的区别：内连接需要提供连接的列，而自然连接自动连接所有同名列。

SELECT A.value，B.value
FROM tablea AS A INNER JOIN tableb AS B

外连接

外连接保留了没有关联的那些行。分为左外连接、右外连接和全外连接。左外连接就是保留左表没有关联的行。

检索所有顾客的订单信息，包括还没有订单信息的顾客。

SELECT Customers.cust_id,Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id=Orders.cust_id;

Customers表：

cust_id	cust_name
1	a
2	b
3	c

Orders表：

order_id	cust_id
1	1
2	1
3	3
4	3

结果：

cust_id	cust_name	order_id
1	a	1
1	a	2
3	c	3
3	c	4
2	b	NULL

组合查询

使用UNION来组合两个查询，如果第一个查询返回M行，第二个查询返回N行，那么组合查询的一般结果为M+N行。

• 每个查询必须包含相同的列、表达式和聚集函数；
• 默认会去除相同行，如果需要保留相同行，则用UNION ALL；
• 只能包含一个ORDER BY子句，并且必须位于子句的最后。

SELECT col FROM mytable1
WHERE co1=1
UNION
SELECT col FROM mytable2
WHERE col=2;

视图

视图是虚拟的表，本身不包含数据，不能对其进行索引操作；
对视图的操作和对普通表的操作一样；
视图具有以下好处：

• 简化复杂的SQL操作，比如复杂的连接；
• 只使用实际表的一部分数据；
• 通过只给用户访问视图的权限，保证数据的安全性；
• 更改数据格式和表示

CREATE VIEW myview AS
SELECT Concat(col1,col2) AS concat_col,col3*col4 AS compute_col
FROM mytable
WHERE col5 = val;