前言
mysql高级SQL语句主要学习select语句
本章主要三个板块：
1.mysql进阶查询
2.mysql数据库函数
3.mysql存储过程
#准备实验所需的两张表 use stevelu; create table location (Region char(20),Store_Name char(20)); insert into location values('East','Boston'); insert into location values('East','New York'); insert into location values('West','Los Angeles'); insert into location values('West','Houston'); mysql> select * from location ; +--------+-------------+ | region | store_name | +--------+-------------+ | East | Boston | | East | New York | | West | Los Angeles | | West | Houstion | +--------+-------------+ create table store_info (Store_Name char(20),Sales int(10),Date char(10)); insert into store_info values('Los Angeles','1500','2020-12-05'); insert into store_info values('Houston','250','2020-12-07'); insert into store_info values('Los Angeles','300','2020-12-08'); insert into store_info values('Boston','700','2020-12-08'); mysql> select * from store_info; +-------------+-------+------------+ | Store_Name | Sales | Date | +-------------+-------+------------+ | Los Angeles | 1500 | 2020-12-05 | | Houston | 250 | 2020-12-07 | | Los Angeles | 300 | 2020-12-08 | | Boston | 700 | 2020-12-08 | +-------------+-------+------------+
一、mysql进阶查询（1）
1.1 SELECT
---- SELECT ----显示表格中一个或数个字段的所有数据记录 语法：SELECT "字段" FROM "表名"; #查询表中的指定字段 mysql> SELECT Store_Name FROM store_info; +-------------+ | Store_Name | +-------------+ | Los Angeles | | Houston | | Los Angeles | | Boston | +-------------+ #查询时可以把字段重新排序 mysql> select date,Store_Name,Sales from store_info; +------------+-------------+-------+ | date | Store_Name | Sales | +------------+-------------+-------+ | 2020-12-05 | Los Angeles | 1500 | | 2020-12-07 | Houston | 250 | | 2020-12-08 | Los Angeles | 300 | | 2020-12-08 | Boston | 700 | +------------+-------------+-------+
1.2 DISTINCT
---- DISTINCT ----不显示重复的数据记录 语法：SELECT DISTINCT "字段" FROM "表名"; #不显示重复记录（DISTINCT加在字段前面） mysql> SELECT DISTINCT Store_Name FROM store_info; +-------------+ | Store_Name | +-------------+ | Los Angeles | | Houston | | Boston | +-------------+ mysql> SELECT DISTINCT Store_Name,date FROM store_info; +-------------+------------+ | Store_Name | date | +-------------+------------+ | Los Angeles | 2020-12-05 | | Houston | 2020-12-07 | | Los Angeles | 2020-12-08 | | Boston | 2020-12-08 | +-------------+------------+
1.3 WHERE
---- WHERE ----有条件查询 语法：SELECT "字段" FROM "表名" WHERE "条件"; #查询Store_Name为Houston的记录 mysql> select * from store_info where Store_Name='Houston'; +------------+-------+------------+ | Store_Name | Sales | Date | +------------+-------+------------+ | Houston | 250 | 2020-12-07 | +------------+-------+------------+ #查询Sales大于1000的Store_Name mysql> SELECT Store_Name FROM store_info WHERE Sales > 1000; +-------------+ | Store_Name | +-------------+ | Los Angeles | +-------------+
1.4 AND OR
---- AND OR ----且 或 语法：SELECT "字段" FROM "表名" WHERE "条件1" {[AND|OR] "条件2"}+ ; #查询sales大于500小于1000的store_name mysql> select store_name from store_info where sales >500 and sales <1000; +------------+ | store_name | +------------+ | Boston | +------------+ #查看sales大于200小于500，或者大于1000，且和或同时有记得加括号，把括号里面作为一个整体优先看待 mysql> select store_name,sales from store_info where (sales >200 and sales <500) or sales>1000; +-------------+-------+ | store_name | sales | +-------------+-------+ | Los Angeles | 1500 | | Houston | 250 | | Los Angeles | 300 | +-------------+-------+
1.5 IN
---- IN ----显示已知的值的数据记录 #括号里面的值作为一个池子，有匹配该字段的就显示（值要用单引号） 语法：SELECT "字段" FROM "表名" WHERE "字段" IN ('值1', '值2', ...); #查询Store_Name等于'Los Angeles'或 'Houston' mysql> SELECT * FROM store_info WHERE Store_Name IN ('Los Angeles', 'Houston'); +-------------+-------+------------+ | Store_Name | Sales | Date | +-------------+-------+------------+ | Los Angeles | 1500 | 2020-12-05 | | Houston | 250 | 2020-12-07 | | Los Angeles | 300 | 2020-12-08 | +-------------+-------+------------+ 3 rows in set (0.00 sec) #查询Store_Name不等于'Los Angeles'或 'Houston' （与上述操作取反） mysql> SELECT * FROM store_info WHERE Store_Name not IN ('Los Angeles', 'Houston'); +------------+-------+------------+ | Store_Name | Sales | Date | +------------+-------+------------+ | Boston | 700 | 2020-12-08 | +------------+-------+------------+ 1 row in set (0.00 sec)
1.6 BETWEEN
---- BETWEEN ----显示两个值范围内的数据记录 语法：SELECT "字段" FROM "表名" WHERE "字段" BETWEEN '值1' AND '值2';（数值左小右大） #查询date在'2020-12-06' 和 '2020-12-10'之间的记录 mysql> SELECT * FROM store_info WHERE Date BETWEEN '2020-12-06' AND '2020-12-10'; +-------------+-------+------------+ | Store_Name | Sales | Date | +-------------+-------+------------+ | Houston | 250 | 2020-12-07 | | Los Angeles | 300 | 2020-12-08 | | Boston | 700 | 2020-12-08 | +-------------+-------+------------+ 3 rows in set (0.00 sec)
1.7 通配符与like
----通配符----通常通配符都是跟 LIKE 一起使用的（与Linux的通配符不同） ---- LIKE ----匹配一个模式来找出我们要的数据记录 语法：SELECT "字段" FROM "表名" WHERE "字段" LIKE {模式}; SELECT * FROM Store_Info WHERE Store_Name like '%os%'; % ：百分号表示零个、一个或多个字符 _ ：下划线表示单个字符 'A_Z'：所有以 'A' 起头，另一个任何值的字符，且以 'Z' 为结尾的字符串。例如，'ABZ' 和 'A2Z' 都符合这一个模式，而 'AKKZ' 并不符合 (因为在 A 和 Z 之间有两个字符，而不是一个字符)。 'ABC%': 所有以 'ABC' 起头的字符串。例如，'ABCD' 和 'ABCABC' 都符合这个模式。 '%XYZ': 所有以 'XYZ' 结尾的字符串。例如，'WXYZ' 和 'ZZXYZ' 都符合这个模式。 '%AN%': 所有含有 'AN'这个模式的字符串。例如，'LOS ANGELES' 和 'SAN FRANCISCO' 都符合这个模式。 '_AN%'：所有第二个字母为 'A' 和第三个字母为 'N' 的字符串。例如，'SAN FRANCISCO' 符合这个模式，而 'LOS ANGELES' 则不符合这个模式。 #查询以on结尾的Store_Name的记录 mysql> select * from store_info where store_name like '%on'; +------------+-------+------------+ | Store_Name | Sales | Date | +------------+-------+------------+ | Houston | 250 | 2020-12-07 | | Boston | 700 | 2020-12-08 | +------------+-------+------------+ 2 rows in set (0.00 sec)
1.8 ORDER BY
---- ORDER BY ----按关键字排序（通常是对数值排序） 语法：SELECT "字段" FROM "表名" [WHERE "条件"] ORDER BY "字段" [ASC, DESC]; #ASC 是按照升序进行排序的，是默认的排序方式。 #DESC 是按降序方式进行排序。 #对Sales值降序排序 mysql> SELECT Store_Name,Sales,Date FROM store_info ORDER BY Sales DESC; +-------------+-------+------------+ | Store_Name | Sales | Date | +-------------+-------+------------+ | Los Angeles | 1500 | 2020-12-05 | | Boston | 700 | 2020-12-08 | | Los Angeles | 300 | 2020-12-08 | | Houston | 250 | 2020-12-07 | +-------------+-------+------------+ 4 rows in set (0.00 sec) #对store_name值的字母升序排序 mysql> select * from store_info order by store_name; +-------------+-------+------------+ | Store_Name | Sales | Date | +-------------+-------+------------+ | Boston | 700 | 2020-12-08 | | Houston | 250 | 2020-12-07 | | Los Angeles | 1500 | 2020-12-05 | | Los Angeles | 300 | 2020-12-08 | +-------------+-------+------------+ 4 rows in set (0.00 sec)
