sql简单语句整理

2020-02-12

字数统计: 1.9k字 | 阅读时长: 8分

登录mysql：
net start mysql
mysql -u root -p
Enter password: **

SQL不区分大小写哦！

1 数据库准备

创建数据库：create database example;
显示数据库中的数据表：use example;
查看数据库中的表：show tables；
其实最开始是Empty set (0.10 sec)的，因为什么都没有。
然后把Ben Forta的提供得例子，直接复制黏贴就得到下面5个表格了：

查看表结构：desc table_name;

2 检索数据

SELECT语句
检索单个列：SELECT prod_name FROM Products;
检索多个列：SELECT prod_id, prod_name, prod_price FROM Products;
检索所有列：SELECT * FROM Products;

检索某一列中不同的（唯一的）值：DISTINCT关键字
如：SELECT DISTINCT vend_id FROM Products;

如果只选择检索部分的行（注意：这里的语句和其他数据库不一样）:
如返回prod_name列的前5行数据：SELECT prod_name FROM Products LIMIT 5;
返回prod_name列的从第4行起的5行数据：SELECT prod_name FROM Products LIMIT 5 OFFSET 4;
注：第一个被检索的是第0行，所以这里的第4行是实际的第5行。

3 排序检索数据

排序：ORDER BY 子句（要确保是SELECT语句的最后一个子句）
如：SELECT prod_name FROM Products ORDER BY prod_name;
降序排序用DESC关键字。
如：SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price DESC;
如果想对多个列进行降序排序，就需要对每一列指定关键字DESC。

4 过滤数据

WHERE 子句
如：SELECT prod_name, prod_price FROM Products WHERE prod_price = 3.49;

检查某个范围的值：BETWEEN 操作符，指定两个值，范围的低端值和高端值，用关键字AND分隔。
如：SELECT prod_name, prod_price FROM Products WHERE prod_price BETWEEN 5 AND 10;
空值检查：IS NULL 子句，
如：SELECT prod_name FROM Products WHERE prod_price IS NULL;

5 高级数据过滤

组合WHERE子句：AND或OR操作符
OR只匹配任一条件即可
AND 在求值过程中优先级更高
可使用圆括号对操作符进行明确的分组，圆括号具有更高的求值顺序。

IN 操作符：取一组由逗号分隔、括在圆括号中的合法值。
如：SELECT prod_name, prod_price FROM Products WHERE vend_id IN ( 'DLL01', 'BRS01' ) ORDER BY prod_name;
NOT 操作符：否定其后跟的任何条件

6 用通配符进行过滤

LIKE 操作符：指示后面的搜索模式利用通配符匹配而不是简单的相等匹配。
1）百分号（%）通配符
如：SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE 'Fish%';
检索任意以Fish开头的词，%指接受Fish后的任意字符。（此处区分大小写）
还可以’%bean bag%’，’F%y’。

2）下划线（_）通配符：只匹配单个字符
如：SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '__ inch teddy bear';
3）方括号（[]）通配符：指定一个字符集，必须匹配指定位置的一个字符。

7 创建计算字段

字段：与列的意思相同
拼接字段：各个数据库上的命令不同，MySQL中要用到具体的函数，
如：SELECT Concat(vend_name, ' (', vend_country, ')') AS vend_title FROM Vendors ORDER BY vend_name;

使用别名：别名（有时也叫导出列）是一个字段或值的替换名。如上面的as vend_title
执行算数计算：
如：SELECT prod_id,quantity,item_price,quantity*item_price AS expanded_price FROM OrderItems WHERE order_num = 20008;

8 使用数据处理函数

UPPER()函数：将文本转换为大写
如：SELECT vend_name, UPPER(vend_name) AS vend_name_upcase FROM Vendors ORDER BY vend_name;

SOUNDEX()对字符串进行发音比较，
如：SELECT cust_name, cust_contact FROM Customers WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');
日期处理函数：各个数据库的命令不同，MySQL中YEAR()函数提取年份，
如：SELECT order_num FROM Orders WHERE YEAR(order_date) = 2012;

9 汇总数据

COUNT()函数：如果指定列名，会忽略指定列的值为空的行；如果不指定列，用（*），则不忽略。

10 分组数据

创建分组：GROUP BY()
如：SELECT vend_id, COUNT(*) AS num_prods FROM Products GROUP BY vend_id;

GROUP BY 子句必须出现在WHERE 子句之后， ORDER BY 子句之前。

过滤分组：HAVING 子句（与WHERE相似，但WHERE过滤行，HAVING 过滤分组；WHERE在数据分组之前进行过滤，HAVING在数据分组之后过滤）

使用HAVING 子句应该结合GROUP BY 子句。
如：SELECT cust_id, COUNT(*) AS orders FROM Orders GROUP BY cust_id HAVING COUNT(*) >= 2;（过滤了两个订单以上的分组）
如：SELECT vend_id, COUNT(*) AS num_prods FROM Products WHERE prod_price >= 4 GROUP BY vend_id HAVING COUNT(*) >= 2;（列出具有2个以上订单产品且价格大于等于4的供应商）

11 联结表

联结：要保证所有联结都有WHERE子句。
如建立联结:SELECT vend_name, prod_name, prod_price FROM Vendors, Products WHERE Vendors.vend_id = Products.vend_id;

等值联结，也叫内联结
上面的语句也可以表示为：SELECT vend_name, prod_name, prod_price FROM Vendors INNER JOIN Products ON Vendors.vend_id = Products.vend_id;

联结多个表：

12 创建高级联结

自联结：用同一个表进行两次检索（自己总结的）

外联结：如检索包括没有订单顾客在内的所有顾客
如：SELECT Customers.cust_id, Orders.order_num FROM Customers LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;
RIGHT或LEFT关键字指定其包括所有行的表（此处是CUSTOMERS表）

与内联结的区别：

13 组合查询

UNION 操作符：组合数条SQL查询

此处相当于将两条SELECT语句的结果组合为一个查询结果集。
其结果与下面语句的结果一样：SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_state IN ('IL','IN','MI') OR cust_name = 'Fun4All';
注意：UNION中的每个查询必须包含相同的列、表达式或聚集函数

UNION ALL返回所有匹配的行（不删掉重复的行）
进行排序时只能使用一次ORDER BY子句。位于最后一个SELECT子句的后面

14 插入数据

INSERT语句：INSERT INTO 表格名（列名）+ VALUES（）

按照给出的列名的次序进行填充，而不管原先表格的次序如何。
还可以省略列，即只给某些列提供值。
注意：不要使用没有明确给出列的INSERT语句。

INSERT SELECT 语句：从一个表检索出相应的列插入到另一个表中（导出数据）
SELECT INTO语句：复制表格（导入数据）（MySQL的语句不同）
如：CREATE TABLE CustCopy AS SELECT * FROM Customers;

15 更新和删除数据

更新数据：UPDATE（总是以更新的表名开始）
如：UPDATE Customers SETECT cust_contact = 'Sam Roberts', cust_email = 'sam@toyland.com' WHERE cust_id = '1000000006';
（不要忘了WHERE子句，避免更新所有行）

删除某个列的值：
如：UPDATE Customers SETECT cust_email = NULL WHERE cust_id = '1000000005';
删除行：DELETE
如：DELETE FROM Customers WHERE cust_id = '1000000006';
删除所有行：TRUNCATE TABLE

16 创建和操纵表

创建表：CREATE TABLE

NOT NULL 是定义必须需要的内容
指定默认值：DEFAULT关键字，默认值经常用于日期或时间戳列。

更新表（列）：ALTER TABLE
如：ALTER TABLE Vendors ADD vend_phone CHAR(20);
如：ALTER TABLE Vendors DROP COLUMN vend_phone;
删除表：DROP TABLE + 表名

17 使用视图

视图是虚拟的表，包含的不是数据而是根据需要检索数据的查询。它提供了一种封装SELECT语句的层次，可用来简化数据处理，重新格式化和保护基础数据。
创建视图：CREATE VIEW

CREATE VIEW ProductCustomers AS
SELECT cust_name, cust_contact, prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num;

18 管理事务

事务：指一组SQL语句
控制事务处理：START TRANSACTION

19 使用游标

游标：是一个存储在DBMS服务器上的数据库查询，不是一条SELECT语句，而是被该语句检索出来的结果集。
创建游标：DECLARE语句，
如：DECLARE CustCursor CURSOR FOR SELECT * FROM Customers WHERE cust_email IS NULL
打开游标：OPEN CURSOR
访问游标数据：FETCH
关闭游标：

20 高级SQL特性

主键：PRIMARY KEY关键字
外键：REFERENCES关键字
创建索引：CREATE INDEX，
如：CREATE INDEX prod_name_ind ON Products (prod_name);

本笔记基于Ben Forta的《SQL必知必会》