文章目录
1. DISTINCT —> 去重
select distinct name from user;
distinct 不能部分使用,该关键字应用于指定的所有列而不单单是前置列。
2. limit 与 offset ----> 偏移开始的位置
select * from user limit 3; //指检索行数为3行
select * from user limit 3 offset 4; //指从行4开始查询3个数据
(这里行4指跳过行4,即搜索出第5、6、7三行的数据)
等同于select * from user limit 4,3
以分页为例,设pageNumber为页数,pages为每页显示的条数。如果需返回第pageNumber页,条目数为pages,则sql语句如下:
select * from user limit pages offset (pageNumger-1)*pages;
select * from user limit (pageNumber-1)*pages ,pages;
3. 完全限定名
sql语句中可通过完全限定的名字来引用列,即同时使用表名和列名–>表名.列名
4. 排序检索数据
4.1 ORDER BY
按指定列进行检索排序(以列数据的字母顺序进行排序,默认升序)
select * from user order by name;
同时,也可指定多个列进行排序:select * from user order by name,age;
当name不唯一时,才会进行age排序比较,否则只按name进行排序。
4.2 DESC
指定降序的方向(升序【A-Z】还是降序【Z-A】)
数据排序默认是升序的,若要降序则为:
select * from user order by name desc;
desc关键字只应用于前置的列名,如要实现多个列降序,则需每个列都要加上desc关键字。
4.3 ASC
指定升序的方向(默认,无需指定)
找出用户里面年龄最大的用户信息: select * from user order by age desc limit 1;
5 数据过滤
5.1 AND 与 OR
需要注意的是,and的优先级大于or,所以同时用到两个操作符,最好加上圆括号表示计算次序。
select * from user where (city="湖南" or city="上海") and age>18;
5.2 IN 与 NOT
IN操作符用来指定条件范围,范围中的每个条件都可以进行匹配。功能与or语句一样
select * from user where age in (16,18);
select * from user where age = 16 or age = 18;
NOT操作符作用于否定跟在它之后的条件
select * from user where age not in (16,18);
6 通配符进行过滤
为在搜索语句后使用通配符,则必须使用LIKE操作符
6.1 百分号(%)
在搜索语句中,%表示任何字符可以出现任意次数。
查询用户姓张的用户:select * from user where name like "张%"
注意:当搜索条件的%后存在尾空格时,会干扰到通配符匹配,解决办法为在使用函数。
6.2 下划线(_)
下划线表示匹配单个字符。
注意:通配符的搜索时间比其它操作符的搜索时间都要长,当有多个搜索条件时,切记不能将通配符放在搜索模式的开始处,这样的搜索速度是最慢的。
7 正则表达式
要使用正则表达式,则需使用关键字REGEXP,在后面加上匹配条件即可。
select * from user where age like '%0';
select * from user where age regexp '0$';
两条语句都为搜索年龄为0结尾的用户
如果语句是这样的:
select * from user where age like '0';
select * from user where age regexp '0';
则第一条语句不会返回数据,第二条会返回年龄中包含0的用户。因为使用like语句时需搭配上通配符。
7.1 进行OR匹配
正则表达式用‘|’来表示匹配左右条件的匹配符
7.2 使用 [] 匹配单一字符
[123]相当于 ‘1|2|3’
7.3 使用‘^’可以对搜索条件进行否定
查询年龄没有789的用户:select * from user where age regexp '[^789]'
7.4 匹配范围
匹配数字0到9:[0-9]; 匹配字母a到z:[a-z]
7.5 匹配特殊字符
为匹配特殊字符,需使用\为前导
匹配名字中有’.'的用户 : select * from user where name regexp '\\.';
\同时也可以用来引用元字符(具有特有含义的字符。
\t 制表 、 \f换页、\n换行、\r回车、\v纵向制表
注意:匹配反斜杠’’,需要使用’\’; 多数的正则表达式都是实现单个反斜杠即可实现特殊字符的转义,但mysql需要使用两个,因为mysql自己解释用一个,正则表达式库解释另一个。
7.6 匹配多个实例
正则表达式提供重复元字符来匹配多个实例
* : 0个或多个匹配
+: 1个或多个匹配(相当于{1,})
? : 0个或1个匹配 (相当于{0,1})
{n}: 指定数目的匹配
{n,}: 不少于指定数目的匹配
{n,m}: 匹配数目的范围
7.7 定位符
‘^’:文本的开始、’$’:文本的结尾、’[[:<:]]’:词的开始、’[[:>:]]’:词的结尾
其中’^’ 还可以表示否定的意思,在集合中[^789]指除了789数字外。
LIKE和REGEXP的不同在于,LIKE匹配整个串而REGEXP匹配子串。
简单的正则表达式测试(返回0或1):select 'hello' REGEXP '[0-9]';
返回0,因为字符串hello明显不存在数字。
8 计算字段
8.1 拼接字段
Concat()函数用于拼接字段,每个串用逗号分开。
查询格式为name(age)的用户(按年龄排序):
select Concat(name,'(',age,')') from user order by age;
8.2 去除空格
Trim()函数用于去除串左右两边的空格,RTrim()去除串右边的空格,LTrim()去除串左边的空格
select Concat( Trim(name) ,'(', Trim(age) ,')') from user order by age;
8.3 使用别名,定义返回值的别名
select Concat( Trim(name) ,'(', Trim(age) ,')') AS nameAge from user order by age;
8.4 算术计算
计算物品的总价格:
select number,price,number*price AS sum from orders where orderId = 1001;
9 函数
9.1 Soundex()函数
能对串的字符发音进行比较。例如需要查询Y.Lee的用户,而用户表里只有Y.Lie。这时就需要用到该函数:
select name,age from user where Soundex(name) = Soundex('Y.Lee');
9.2 聚集函数
AVG()计算列的平均值:select AVG(price) AS avg_price from orders where orderId = '1001';
COUNT()对表的行数进行计算:select COUNT(*) as num_use from user;
对特定列的行数计算:select COUNT(email) as num_use from user;
两者的区别在于count(*)不会忽略null值。
MAX()返回指定列中的最大值:select MAX(price) AS max_price from products;
在用于文本数据时,如果数据按相应的列排序,则MAX()返回最后一行。
SUM()返回指定列的和。
附表:
10. 分组数据
10.1 GROUP BY
分组允许把数据分为多个逻辑组,以便能对每个组进行聚集计算。
查询每个供应商的产品数量:
select vend_id,count(*) as num_prods from products group by vend_id;
这里以供应商的id分组,分别计算各组的数量。
10.2 HAVING —> 过滤分组
having 与 where 非常类似,基本上where能用的,都可以用having来替代。唯一的区别是where过滤行,而having过滤分组。
查询订单数目大于2的产品供应商:
select cus_id,count(*) as orders from orders group by cust_id having count(*) >=2;
查询订单数目大于2,价格大于10的产品供应商:
select vend_id,count(*) as num_prods from products where prod_price >=10
group by vend_id having count(*) >=2;
10.3 分组和排序
即使ORDER BY和GROUP BY经常能完成一样的功能,但实际上两者的区别还是挺大的。
order by:
a. 排序产生的输出
b. 任意列都可以使用
c. 不一定需要
group by:
a. 分组行。但输出可能不是分组的顺序
b. 只可能使用选择列或表达式列,而且必须使用每个选择列表达式
c. 如果与聚集函数一起使用列(或表达式),则必须使用。
查询订单价格大于等于50的订单号和总计订单价格:
SELECT order_num,SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >=50;
查询订单价格大于等于50的订单号和总计订单价格并按总价格排序输出:
SELECT order_num,SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >=50
ORDER BY ordertotal;
10.4 SELECT 子句顺序
select > from > where > group by > having > order by > limit
11 联结表
联结是一种机制,用来在一条SELECT语句中关联表,因此称之为联结。使用特殊的语法,可以联结多个表返回一组输出,联结在运行时关联表中正确的行。
笛卡儿积:
由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。即返回两个表中的所有需要查询的数据。
11.1 创建联结
等值联结:
select vend_name,prod_name,prod_price from vendors,products where vendors.vend_id = products.vend_id order by vend_name,prod_name;
上述的联结基于两个表之间的相等测试,这种也称为内部联结。
INNER JOIN:
select vend_name,prod_name,prod_price from vendors inner join products on vendors.vend_id = products.vend_id;
该select语句与上面的语句返回结果完全一样,只是用了稍微不同的语法来明确指定联结的类型。
11.2 自联结
自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。
查询ID为DTNTR的供应商所生产的产品:
select prod_id,prod_name from products where vend_id = (select vend_id from products where prod_id='DTNTR');
使用自联结:
select p1.prod_id,p1.prod_id from products as p1,products as p2 where p1.vend_id = p2.vend_id and p2.vend_id = 'DTNTR';
虽然最终的结果是相同的,但有时候处理联结远比处理子查询快得多。应该试一下两种方法,以确定哪一种的性能更好。
11.3 外部联结(左外联结与右外联结)
许多联结将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行。与内部联结关联两个表中的行不同的是,外部联结还包括没有关联行的行。
检索出所有用户,包括没有订单的客户:
select customers.cust_id,orders.order_num from customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;
检索所有用户及每个用户的订单数:
select customers.cust_name,customers.cust_id,count(orders.order_num) as num_ord from customers INNER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id;
检索所有用户及每个用户的订单数包括没有下订单的客户:
select customers.cust_name,customers.cust_id,COUNT(orders.order_num) as num_ord from customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id;
使用联结的注意事项:
- 注意所使用的联结类型。一般我们使用内部联结,但使用外部联结也是有效的。
- 保证使用正确的联结条件,否则将返回不正确的数据。
- 应该总是提供联结条件,否则会得出笛卡儿积。
- 在一个联结中可以包含多个表,甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的,一般也很有用,但应该在一起测试它们前,分别测试每个联结。这将使故障排除更为简单。
12 组合查询
使用组合查询的基本情况
- 在单个查询中从不同的表返回类似结构的数据;
- 对单个表执行多个查询,按单个查询返回数据。
组合两个表的查询工作与具有多个where子句条件的单条查询完成的工作是相同的。
12.1 使用UNION
查询价格小于等于5的所有物品以及包括供应商1001和1002生产的所有物品:
select vend_id,prod_id,prod_price from products where prod_price<=5 UNION
select vend_id,prod_id,prod_price from products where vend_id IN (1001,1002);
相同结果的使用where子句:
select vend_id,prod_id,prod_price from products where prod_price<=5 OR vend_id IN (1001,1002);
12.2 使用UNION的规则
- 必须有两条select语句以上才能执行。
- UNION的每个查询必须有相同的列、表达式或聚集函数。
- 列数据的类型必须兼容
12.3 包含或取消重复的行
使用union查询中,会自动去除重复的行,这是UNION默认行为,如果需要不去重,则需使用UNION ALL 即可
12.4 对组合查询结果排序
使用union查询时,只允许使用一条order by排序语句,且出现在最后一条select 语句上,因为这是为了防止一条select语句排序一部分,而另一条select语句排序另一部分,这引起混乱。
select vend_id,prod_id,prod_price from products where prod_price<=5
UNION
select vend_id,prod_id,prod_price from products where vend_id IN (1001,1002)
ORDER BY vend_id,prod_price;
13 全文本搜索
略了。。。
14 数据插入
INSERT INTO customer VALUES(..);
INSERT INTO customer(字段名) VALUES(..);
如果数据检索是最重要的,则可以通过在insert 与 into之间添加关键字LOW_PRIORITY,指示mysql降低insert语句的优先级:INSERT LOW_PRIORITY INTO。同时,UPDATE与DELETE也适用该语法。
如果要一次性插入多条数据,可以使用:
INSERT INTO customer VALUES(..), VALUES(..);
14.1 插入检索出的数据 (INSERT SELECT)
查询custnew表中数据,再插入到customer中。其中,查询出来的数据应该与插入的字段类型一致:
insert into customer(..) select .. from custnew;
15 更新和删除数据
15.1 更新
update customers set cust_email = 'email@163.com' where cust_id = '1005';
注意:如果用update更新多行数据,在这些行中的一行或多行发生错误时,则整个update操作被取消。如果想即使发生错误,也能继续更新,则需使用IGNORE关键字,
UPDATE IGNORE customer …
使用update可以删除列,即将列更新为NULL。
update customers set cust_email = NULL where cust_id =1005;
15.2 删除
删除id为1005的行数据:delete from customers where cust_id = 1005;
如果想从表中删除所有行,可以使用TRUNCATE TABLE语句,它比delete更快,原理在于truncate 实际上删除原来的表再新建一个,而不是像delete那样逐行删除表中的数据。