SQL学习笔记（1）

1. 登录mysql

$ mysql -u root -p
  root

2. 显示所有数据库

SHOW DATABASES;

3. 创建数据库

CREATE DATABASE test_db; -- 创建数据库test_db

4. 使用数据库

USE test_db;

5. 显示数据库中所有的表table

SHOW TABLES;

6. 数据库的行和列

6.1 列

表是由一个或多个列组成的。

每个列都有相应的数据类型。

6.2 行

表中的一条记录。行=记录

7. 主键

表中每一行都应该有一列（或几列）可以唯一标识自己。

虽然不是必须要主键，但是还是建议定义主键。

表中任意列都可以作为主键，只要满足一下条件：

• 任意两行不具有相同的主键值（唯一性）
• 每一行都必须具有一个主键值（主键列不允许控制NULL）
• 主键列中的值不允许修改和更新
• 主键值不能重用（如果某行从表中删除，它的主键不能赋给以后的新行）

注意，可以使用多列作为主键。在使用多列时，以上规则适用于所有的列。所有列值得组合必须是唯一的（主键列中的单个列的值可以不唯一，但是这些列的组合必须唯一）

8. 检索数据

8.1 检索单个列

 SELECT username FROM user;  -- 从表user中检索username列

注意多条SQL语句以分号;分隔。建议在单挑SQL语句后总是写上分号。

SQL不区分大小写，但是建议对于关键字使用大写，其他如列名表名使用小写。

8.2 检索多个列

列名用逗号隔开

 SELECT username, password FROM user; -- 从表user中检索username和password列

8.3 检索所有列

使用通配符*标识所有的列

 SELECT * FROM user; -- 从表user中检索所有列

8.4 检索不同的值DISTINCE

使用distinct只返回不同的值，需要放在列名前面

 SELECT DISTINCT username FROM user;

注意，DISTINCT不能用于部分列，而是用于所有的列。即为DISTINCT返回的是多个列的唯一组合。

8.5 限制结果

MYSQL通过LIMIT来限制返回结果

 SELECT username FROM user LIMIT 3,4; -- 从第3条结果开始返回四条结果 SELECT username FROM user LIMIT 4 OFFSET 3; -- 与上面的结果相同。需要注意limit后面的数字与上面是反的

注意表中的行是从第0行开始的而不是第1行

9. 注释

• 单行注释：

  -- 这是单行注释# 这是单行注释
  

• 多行注释

  /*这是多行注释*/
  

10. 排序检索数据 ORDER BY

10.1 按单个列排序

SELECT username FROM user ORDER BY username; -- 根据行的username对结果进行排序SELECT username FROM user ORDER BY password; -- 根据行的password对结果进行排序

必须保证ORDER BY出现在SELECT语句最后。如果不是最后，就会出错。

需要注意，ORBER BY作为排序依据的列可以不是SELECT检索的列（如上面的第二个例子）

10.2 按多个列排序

用逗号分隔开即可

SELECT username FROM user ORDER BY username,password; -- 根据行的username对结果进行排序，然后在此结果上根据password进行排序

上面的例子中，仅当多个行的username值相同时才根据password进行排序。如果所有行的username都唯一，那么就不会根据password排序

10.3 按列位置排序

SELECT username,password FROM user ORDER BY 1,2; -- 先根据SELECT的第一个列排序，然后在此基础上根据SELECT的第二个列排序

需要注意这里ORDER BY后面的列数是SELECT后面跟的要检索的列的顺序。如上面例子中username的顺序就是1，password的顺序就是2

10.4 指定排序方向：升序降序

默认是用升序排列的。

如果要用降序，需要指定DESC（DESCENDING）关键字

DESC关键字只应用到它前面的一个列名。如

SELECT username,password FROM user ORDER BY username DESC,password; -- 先按照username进行降序排列，在此基础上按照password进行升序排列

如果要想在多个列上进行降序排列，那么每个列都要指定DESC关键字

ELECT username,password FROM user ORDER BY username DESC,password DESC; -- 先按照username进行降序排列，在此基础上按照password进行降序排列

与DESC相对的是ASC（ASCENDING升序排列），ASC基本没用处，因为默认就是升序排列

11. 过滤数据 WHERE子句

在SELECT语句中，数据根据WHERE子句中指定的过滤条件进行过滤。WHERE在FROM子句之后给出。

如果同时也有order by子句，那么要保证order by在最后。

SELECT username FROM user WHERE username > 'a'; -- 过滤username列大于'a'的结果

WHERE子句操作符

操作符	说明
=	等于
<> !=	不等于
<	小于
<=	小于等于
!<	不小于
>	大于
>=	大于等于
!>	不大于
BETWEEN AND	在指定两个值之间，如BETWEEN 5 AND 10
IS NULL	为NULL值

注意，当比较的值是字符串时，需要用单引号包裹住。如果是数值的话则不用

SELECT username FROM user WHERE username > 'aaa'; -- 过滤username列大于字符串'aaa'的结果SELECT price FROM user WHERE price > 10; -- 过滤price列大于数值10的结果

范围值检查

使用BETWEEN AND操作符来检查某个范围的值

SELECT price FROM user WHERE price BETWEEN 5 AND 10; -- 过滤price在5-10的结果（包括5和10）

空值检查

NULL：无值。可以通过IS NULL来检查是否是NULL

SELECT username FROM userwhere username IS NULL; -- 过滤username是NULL的结果

12. 高级过滤：逻辑操作符、IN、NOT

12.1 逻辑操作符：

通过AND（与）和OR（或）来对WHERE子句进行组合

AND与

SELECT username FROM userWHERE username > 'a' AND username < 'z'; -- 过滤username大于'a'且小于'z'的结果

OR或

SELECT username FROM userWHERE username > 'a' OR password > '0'; -- 过滤username大于'a'或者password大于'0'的结果

需要注意当WHERE子句中同时出现AND和OR时，由于AND的优先级较高，因此先处理AND后处理OR。因此建议使用括号()来对操作符进行明确优先级分组，避免混乱

SELECT username FROM userWHERE (username = 'wudi' OR username = '111') AND password > '0';-- 过滤出username为'wudi'或'111'且password大于'0'的结果

12.2 IN操作符

IN用来指定范围，IN取一组逗号分隔、括在圆括号中的合法值

SELECT username FROM userWHERE username IN ('wudi','111');-- 过滤username为'wudi'或'111'

OR也可以实现IN相同的功能，但是IN更加清晰直观，且IN比OR执行的更快。

并且IN最大的优点是可以包含其他select子句

12.3 NOT操作符

否定其后所跟的任何条件

SELECT username FROM userWHERE NOT username = '111';-- 过滤username不为'111'的结果

13. 通配符过滤

在where子句中使用LIKE操作符配合通配符进行过滤，且通配符过滤只能用于字符串类型的列，非字符串类型的列不能用通配符过滤。

通配符：用来匹配值的一部分得特殊字符

13.1 百分号%通配符

%表示任何字符出现任何次数（包括0次）

%不匹配NULL

SELECT username FROM userWHERE username LIKE 'wu%'; -- 过滤以'wu'开头的username列

SELECT username FROM userWHERE username LIKE '%wu%'; -- 过滤'wu'出现在任何位置的username列

SELECT username FROM userWHERE username LIKE 'w%i'; -- 过滤以'w'开头'i'结尾的username列

注意有些DBMS使用空格来填补字段内容。例如某列有10个字符，而某行的该列值为’abc’，那么为了填满该列，需要在’abc’文本后面填充7个空格。这时使用’%c’匹配以’c’结尾的字段就会失败，这时的解决方法是用’%c%'来匹配’c’字符之后的空格，但是最好的方法是先用函数去掉多余的空格再进行通配符匹配。

13.2 下划线_通配符

只匹配单个字符，不能多也不能少

SELECT username FROM userWHERE username LIKE '__di'; -- 过滤'**di'这种字段+----------+| username |+----------+| wudi     || wudi     |+----------+2 rows in set (0.00 sec)

13.3 方括号[]通配符，MYSQL不支持

用来指定一个字符集，只匹配单个字符

SELECT username FROM userWHERE username LIKE '[abw]%';-- 过滤以'a'或'b'或'w'开头的字段

可以用’^'符号来否定字符集，NOT能获得一致结果

SELECT username FROM userWHERE username LIKE '[^abw]%';-- 过滤不以'a'或'b'或'w'开头的字段SELECT username FROM userWHERE NOT username LIKE '[abw]%';-- 结果一致

13.4 使用通配符的建议

• 不要过度使用通配符，如果其他操作符能达到目的就用其他操作符
• 尽量不要把通配符放于开始处，这样搜索起来是最慢的。

14. 计算字段

计算字段不存在于数据库表中，而是在SELECT语句中创建的。

有时我们需要从数据库中直接获取转换、计算、格式化后的数据，而不是检索出数据后由客户端应用程序进行处理，这时就需要用到计算字段。因为在数据库服务器上完成这些操作比在客户端中完成要快得多。

14.1 拼接字段

MYSQL中使用CONCAT函数将值连接在一起

SELECT CONCAT(username, ',', password) FROM user;-- 将username列，','字符，password列拼接起来形成一个拼接字段（可以理解为一个新的列）

其他的某些DBMS使用+和||进行拼接字段

SELECT username + ',' + password FROM user;

SELECT username || ',' || password FROM user;

去除字段左右空格

注意有些DBMS使用空格来填补字段内容，因此在拼接后会出现空格。因此可以先通过函数去除多余空格再进行拼接

TRIM()  -- 去掉字段左右空格RTRIM() -- 去掉字段右边空格LTRIM() -- 去掉字段左边空格

实例：将两个列字段去除多余空格后拼接

SELECT CONCAT(TRIM(username),',',TRIM(password))FROM user;

14.2 使用别名AS

可以发现上面的拼接字段的新列没有名字，因此可以使用AS关键字给列起别名

SELECT CONCAT(TRIM(username),',',TRIM(password)) AS user_passFROM user ORDER BY user_pass;-- 将拼接字段的新列起别名为user_pass

14.3 计算字段

对于数值类型的列，可以进行数值计算（+、-、*、/）。并且可以通过圆括号()确定计算的优先级关系

SELECT price * num AS sum_priceFROM user;-- 检索出的sum_price列是一个计算字段，结果是price列*num列