sql 语句总结

show databases; 显示所有数据库
show tables; 显示一个数据库中的列表
show status 用于显示服务器状态信息

检索数据

select语句

select name from product; 检索单个列
select id,name from product;检索多个列 
select * from product; 检索所有列
select distinct id from product 检索不同的行 

限制结果
select name from product limit 5;limit 5 指示返回不多于5行
select name from product limit 5,5;表示从行5开始的接下来5行

使用完全限定的表名
select product.name from products;

排序检索数据

select name from product order by name; 根据一个列排序
select id,price name from product order by price,name;根据多个列排序
select id,price,name from prodct order by price DESC, name; 指定排序方向

在给出order by 子句时，应该保证它位于from之后，如果使用limit，则limit位于order by 之后

过滤数据

select name,price from product where price=20; 使用where子句

where 子句操作符 <>和！= 标识不等于。between 标识指定两个值之间。

select name,price from prodct where price between 5 and 10;     between
select name from product where price is NULL; 空值检查 

select id,price,name from products where id=30 and price=1;
select name,pricee from product where id=1 or price=1; 多个where语句用and or 连接。可以用（）改变计算顺序。

select name,price from products where id in (1,2) order by name;圆括号在where子句的另一种用法
select name,price from product where id=1 or id=2 order by name; in 操作符用来指定条件的范围。
上面两句含义相同。
select name in product where id not in (1,2) order by name; not 操作符用来否定后面跟的条件。

用通配符进行过滤

通配符是用来匹配值的一部分特殊字符。

like操作符

%通配符

% 表示任何字符出现任意次数(包括0次)

select id,name from prodcut where name like 'jet%';找出以jet开头的产品。
select id,name from product where name like ‘%jet%’; 找出包含jet的产品
select id,name from prodct where name like 'a%z'; 找出以 a 开头,以 z 结尾的字段。

_通配符

_用来匹配单个字符

select id,name from product where name like '_ jet' 返回了 1 jet, 2 jet 。 （注意空格）

使用正则表达式进行搜索

有 wkn wkn1 wkn2 wkn3 4个数据
select name from person where name like'wkn' 找到一个wkn
select name from person where name regexp 'wkn'
找到四个. 
所以Like 匹配整个列，regexp 在列值内进行匹配。

基本字符匹配

select id，name from prodct where name REGEXP 'a' order by name;检索name中包含a 的所有行
此句子告诉mysql regexp 后跟的东西作为正则表达式

.在正则表达式中表示匹配任意一个字符

select name from product where name REGEXP '.0'
order by name; 返回了 10，20 两个name.
select id,name from product where name REGEXP '1000|2000' order by name;| 用于搜索两个串之一。
select id,name from product where name REGEXP ‘[123]on’;[]用来匹配中括号中的单一字符。匹配1on,2on,3on;
select id,name from priduct where name REGEXP '[1-3]on'; 相当于 1on,2on,3on; 
select name from prodcut where name regep '\\.' 表示查找. 类似，\\- 标识-

匹配多个实例

字符类

[:alnum:] 任意字母和数字
[:alpha:]任意字符
[:dight:]任意数字

重复元字符（匹配前面一个字符）

* 0个或者多个匹配

+ 1个或多个匹配

？ 0个或者1个匹配

{n}指定数目的匹配

{n,}不少于指定数目的匹配

{n,m}指定的范围

select name from product where name regexp '\\([0-9] sticks?\\)'
\\(匹配（ [0-9]匹配任意数字，sticks? 匹配stick 和sticks s后的？使s可选。

定位符

^ 文本的开始

$ 文本的结尾

[[:<:]]词的开始

[[:<:]]词的结尾

select name from product where name regexp '^[0-9\\.]'
找出以数字或者以.开头的所有信息。

创建计算字段

字段 基本与列的意思相同，数据库一般成为列，字段通常用在计算字段的连接上。

拼接字段

在mysql的select语句总，可以使用concat()函数来拼接两个列。

select concat(name, '(', country,')') from prodcut order by name;

concat((name, ‘(‘, country,’)’)
ACE(USA)
Brett(CHA)

使用别名AS

别名是一个字段或者值的替换名，使用as 进行赋予。

select concat(name, '(', country,')') as aaa from prodcut order by name;
select id,aprice，a*aprice as bprice from orders where name=11; 使用算术运算

使用数据处理函数

文本处理函数

select name,upper(name)as uname from products;

• Left()返回串左边的字符
• length() 返回长度
• locate() 找出串的一个子串
• lower() 将串转换为小写
• ltrim() 去掉左边空格
• upper()转换为大写

日期时间处理函数/数值处理函数

汇总数据

聚集函数

聚集函数运行在行组上，计算和返回单个值的函数。

• AVG() 返回某列的平均值
• COUNT() 返回某列的行数
• MAX() 返回某列的最大值
• MIN() 返回某列的最小值
• SUM() 返回某列值之和

select avg(price) as aprice from product ;//返回所有产品的平均价格。
select avg(price) as aprice from prodcut where id in (2,3) //返回2和3的平均值

select count(*) as num_cust from customers //返回表中的条目数。
select count(email) as num_email from customers//统计有电子邮件的人数
select max(price) from as maxprice from product;
select sun(quantity) from prodcut where num=100;//返回指定列值的和。

聚集不同值

select avg(distinct price) as avg_price from product where id=1003;//此平均值只考虑不同的价格。

组合聚集函数

select count(*) ,MIN(price),MAX(price) from product;

分组数据

把数据分为多个逻辑组，以便对每个组进行聚集计算。

创建分组 group by

select id,count(*) as num_p from product group by id;

id	num_p
1	3
2	7
3	4
4	8

使用group by 后，就可以对每个组进行聚集了。

过滤分组

where不能完成过滤分组的任务，因为where筛选的行。应该使用having,having 专门用来过滤分组

select id,count() as orders from priduct group by id having count(*)>=2;
select id，count() from product where price>10 group by price having count()>2;//合在一起

使用子查询

子查询 即嵌套在其他查询中的查询。

select name, contanct from customers where id in(select cust_id from orders where order_num in (select order_num  from orderitems where pro_id=2));//
利用子查询进行过滤<主要使用IN关键字进行配合>
加入列出订购物品2的所有客户
1）检索包含物品2的所有订单的编号
2）检索具有前一步骤列出订单编号的所有客户ID
3)检索前一步骤返回的所有客户ID的客户信息。
利用子查询组合成一个语句。

在select语句中，子查询总是从内向外处理。

联结表

关系表的设计就是要保证把信息分解成多个表，一类数据一个表。

外键 ： 外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。

现在有两个表 一个供应商表vend，一个产品表product。供应商表存储供应商信息，每个供应商占一行，唯一的标识就成为主键。产品表只存储产品信息，它除了存储供应商的ID外不存储供应商的其他信息供应商表的主键在产品表中又称为外键。利用供应商ID可以从产品表中找出相应供应商的详细信息。

如果数据存储在多个表中，怎么用单条select 语句检索出数据。答案是使用联结。
1)创建联结 规定要联结的表以及它们如何关联即可。 此处必须使用完全限定列名 ，因为两个表中都有vend_id; (下面的也称为等值联结)

select vend_name, pro_name,pro_price from vend,products where vend.vendid=product.vendid order by vend_name, pro_name;

下面的结果同上 两个表的关系是from 子句的组成部分，以INNER JOIN 指定。

select vend_name,pro_name,pro_price from vend INNER JOIN products ON vend.vendid=product.vendid;

联结多个表

select pro_name,vend_name,pro_price,quantity from orderitems,prodct,vends where product.vendid = vend.vendid and orderitem.proid=product.proid and order_num=25;//此法优于子查询

高级联结

除了给列名和计算字段起别名外，还允许对表名起别名。

select cust_name ,cust_contact from customers as c,orders as o,orderitems as oi where c.cust_id=o.cust_id and oi.order_num=o.order_num and pro_id ='t';

自联结

假如你发现某商品(Id为1001) 存在问题，因此想知道生产该商品的生产商生产的其他商品是否存在问题，此查询要求首先找到生产Id为1001 的生产商，然后找出这个供应商生产的其他商品。

select pro_id,pro_name from product where vend_id=(select vend_id from products where pro_id = 1001)

select p1.pro_id,p1.pro_name from product as p1,product as p2, where p1.vend_id =p2.vend_id and p2.pro_id=1001;//使用自联结。一个相同的表两个名称。

自然联结

标准的内部联结返回所有数据，甚至相同的列多次出现，自然联结排序多次出现，使每个列只返回一次。

外部联结

许多联结将一个表中的行与另一个表中的行进行关联，但有时候会需要包含哪些没有关联行的那些行。包含了相关表中没有关联行的联结称为外部联结。

例如 列出所有产品及订购数量，包括没有人订购的产品。

select customers.cust_id,orders.order_num from customers LEFT OUTER JOIN orders ON customers.cust_id=order.cust_id;

类似内部联结，这条语句使用outer join 指定联结的类型，外部联结还包含了没有关联行的行。在使用outer join时，必须用right或者left指定其所有行的表（right指出的是outer join右边的表，left指出的是outer join 左边的表）上面例子是哦那个left从customer表选择所有行。

a	id	name	b	id	job	parent_id
	1	张三		1	23	1
	2	李四		2	34	2
	3	王五		3	34	4

a.id 和 b.parent_id存在关系。

内联结

select a.* ,b.* from a inner join b on a.id=b.parent_id

张三	1	23	1
李四	2	34	2

左联结(指向a a 是完整的)

select a.*,b.* from a left join b on a.id=b.parennt_id

张三	1	23	1
李四	2	34	2
王五	null	null	nill

右联结（指向b b 是完整的）

select a.*,b.* from a right join b on a.id=b.paren_id

张三	1	23	1
李四	2	34	2
null	3	34	4

Mysql NULL 值处理

MySQL 使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。

为了处理这种情况，MySQL提供了三大运算符:

• IS NULL: 当列的值是 NULL,此运算符返回 true。
• IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
• <=>: 比较操作符（不同于=运算符），当比较的的两个值为 NULL 时返回 true。

关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。

应该使用IS NULL 和 IS NOT NULL 运算符。

组合查询

允许执行多个select语句，并将结果作为单个查询结果集返回。这些组合查询通常成为union 或者复合查询。

创建组合查询

使用UNION组合数条sql查询 简单组合在一起即可。
UNION 从查询结果中自动去除了重复的行 如果不想，可以使用UNION ALL

全文本搜索

插入数据 /insert

插入完整的行

insert into customers values(NULL,'wkn','Los angeles')

每个必须提供一个值，如果没有值的话必须使用NULL。
第一列为id,也为NULL,因为该列mysql会自动增量。

insert into customers(name,address,city) values('wkn','sysu','GZ');

 此法在表名后面明确的列出了列名，插入行时，会对应的进行插入。这样即使表的结构改变，仍然可以正常工作。

插入多个行

insert into customers(name,address,city) values('wkn','qqq','ddd'),('mmm','ds','er');

插入检索出的数据

insert 还可以将select 语句的结果直接插入到表中，也就是insert select.

更新和删除数据

更新数据

• 更新特定行
• 更行所有行

更新由三部分组成，要更新的表，列名和他们的新植，确定要更新行的过滤条件。

update customers set email='www@qq.com' where id=1005;

update语句总是以要更新的表的名字开始， set命令用来将新值赋给被更新的列。update语句以where子句结束，告诉mysql更新哪一行。没有where子句，就会更新所有行。

更新多列的语法稍有不同

update customers set name='wkn',address='Golden state' where id=1005;

为删除某列，可以把值直接置为null.

删除数据

• 删除特定行
• 删除所有行

删一行

delete from customers where id=1006;