mysql语法

对表的操作

1 检索数据

1.1 select 检索单个列

select 列名 from 表名;
select col1 from table1;

1.2 select检索多个列

select 列名1，列名2 from 表名;
select col1,col2 from table1;

1.3 检索所有列

select * from table1;

1.4 使用注释

单行注释使用：

# 注释内容

多行注释使用：

/*注释内容*/

1.5 检索不同的值(distinct)

#对单列去重
select distinct 列名1 from 表名;
select distinct col1 from table1;
#对多列去重
select distinct 列名1，列名2 from 表名;
select distinct col1,col2 from table1;
#注意：distinct 关键字作用于所有的列，不仅仅是跟在其后的那一列

1.6 限制结果

在mysql中，第一个被检索的是第0行

1.6.1 返回结果不超过n（n<表中行数）行数据

select 列名 from 表名 limit n;
#返回不超过五行数据
select col1 from table1 limit 5;

1.6.2 返回特定范围的行数

# limit m offset n 指示返回从第m行起的n行数据，可以简写为limit n,m;
select 列名 from 表名 limit m offset n;
select col1 from table1 limit 5 offset n;#从第五行起的5行数据
#limit 1 offset 1 会检索第2行，而不是第一行

2. 排序检索数据

2.1. 排序数据

使用order by进行排序

select 列名 from 表名 order by 列名;
select col1 from table1 order by col1;

子句（clause）：
SQL语句由子句构成，有些子句是必需的，有些则是可选的。一个子句通常由一个关键字加上所提供的数据组成。子句的例子由select语句的from子句可以看出来。

order by 子句的位置
在指定一条order by 子句时，应该保证它是select语句中最后一条子句。如果它不是最后的子句，将会出错。

通过非选择列进行排序

通常，order by 子句中使用的列将是为显示而选择的列。但是，实际上并不一定要这样，用非检索的列排序数据是完全合法的。

2.2. 按多个列名排序

select 列1，列2，列3 from 表名 order by 列2，列3;
# 第一个关键字重复，才按第二个关键字排序
select col1,col2,col3 from table1 order by col2, col3;

2.3. 按列位置排序

order by 还支持按相对列位置进行排序

select 列1，列2，列3 from 表名 order by 2，3;
# 先按col2排序，再按col3排序
select col1,col2,col3 from table1 order by 2, 3;

2.4. 指定排序方向

升序：ASC（ASCENDING），数据库默认也是升序排列。
降序：DESC（DESCENDING），如果想在多个列上进行降序排序，必须对每一列指定DESC关键字。

select 列1，列2，列3 from 表名 order by 列2，列3 DESC;
#只有col3列将序排列，其它选择列都是升序排列
select col1，col2，col3 from table1 order by col2，col3 DESC;

3. 数据过滤

3.1. 组合where子句

为了进行更强的过滤控制，SQL允许给出多个WHERE子句。这种子句有两种使用方式，即以AND子句或OR子句的方式使用。

操作符（operator）
用来联结或改变WHERE子句中的子句的关键字，也称为逻辑操作符。

3.1.1. AND操作符

要通过不止一个列进行过滤，可以使用AND操作符给WHERE子句附加条件。

select 列1，列2，列3 from table1 where 列1 = '001' and 列2 < = 4; 
# and前后两个条件都成立才能检索出结果
select col1, col2, col3 from table1 where col1='001' and col2<=4;

3.1.2. OR操作符

带有OR的语句中，第一个条件得到满足情况下，就不再计算第二个条件了（在第一个条件满足时，不管第二个条件是否满足，相应的行都被检索出来）。

select 列1，列2，列3 from table1 where 列1 = '001' or 列2 < = 4; 
# or前后一个条件成立就可检索出结果（如果or前面条件成立，则不判断后面条件）
select col1, col2, col3 from table1 where col1='001' or col2<=4;

在where子句中使用圆括号
任何时候使用具有and和or操作符的where子句，都应该使用圆括号明确地分组操作符。不要过分依赖默认求值顺序，即使它确实如你希望的那样。使用圆括号没有什么坏处，它能消除歧义。

3.2. in 操作符

in
where子句中用来指定要匹配值的清单的关键字，功能与or相当。

select 列1，列2，列3 from 表1 where 列1 in ("001","002");
#in使用方法
select col1，col2，col3 from table1 where col1 in ("001","002");

为什么要使用in操作符？其优点如下。

• 存在有很多合法选项时，in操作符语法更清楚，更直观。
• in操作符一般比一组or操作符执行得更快。
• in的最大优点是可以包含其他select语句，能够更动态地建立where子句。

3.3. not操作符

not
where子句中用来否定其后条件的关键字。

select 列1 from 表名 where not 列2 = '001';
# 不是001条件满足
select col1 from table1 where not col2 = '001';

4. 使用通配符进行过滤

4.1. like操作符

使用通配符，可以创建比较特定数据的搜索模式（由字面值、通配符或两者组合构成的搜索条件），用来匹配值的一部分的特俗字符。为在搜索子句中使用通配符，必须使用like操作符。通配符搜索只能用于文本字段（字符串），非文本数据类型字段不能使用通配符搜索。

4.1.1. 百分号（%）通配符

%通配符使用:

• 匹配以"yves"开头的记录:(包括记录"yves")

select * from products where products.prod_name like 'yves%';

• 匹配包含"yves"的记录(包括记录"yves")

select * from products where products.prod_name like '%yves%';

• 匹配以"yves"结尾的记录(包括记录"yves",不包括记录"yves ",也就是yves后面有空格的记录,这里需要注意)

select * from products where products.prod_name like '%yves';

%除了NULL，能匹配0个、1个或多个字符。

4.1.2. 下划线(_)通配符

下划线(_)只能匹配单个字符，而不是多个字符

#匹配任一字母+"yves"的记录
select * from products where products.prod_name like '_yves';

4.2. 通配符的技巧

正如所见，SQL的通配符很有用。但这种功能是有代价的，即通配符搜索一般比前面讨论的其他搜索要耗费更长的时间处理。这里给出一些使用通配符时要记住的技巧。

• 不要过度使用通配符，如果其它操作符能达到相同的目的，应该使用其他操作符。
• 在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处。把通配符放到开始处，搜索起来是最慢的。
• 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

5. 计算字段

字段
基本上与列的意思相同，经常互换使用，不过数据库列一般称为列，而字段这个术语通常在计算字段这种场合下使用。

5.1. 拼接字段

select concat(列1,'(',列2，‘)’) from 表名1;

5.2. 使用别名

一个未命名的列不能用于客服端应用中，因为客服端没有办法引用它。为了解决这个问题，SQL支持列别名。别名是一个字段或值的替换名，别名有时也称为导出列。

select 列1 as col1 from 表名1;
select 计算字段 as col2 from 表名1;

6. 使用函数处理数据

6.1. 文本处理函数

函数	说明
LEFT()（或使用子字符串函数）	返回字符串左边的字符
LENGTH() （或LEN() ）	返回字符串的长度
LOWER()	将字符串转换为小写
LTRIM()	去掉字符串左边的空格
RIGHT()	返回字符串右边的字符
RTRIM()	去掉字符串右边的空格
SUBSTR()或 SUBSTRING()	提取字符串的组成部分
SOUNDEX()	返回字符串的SOUNDEX值
UPPER()	将字符串转换为大写

# cust_contact中包含Michael Green的数据
select cust_name, cust_contact from customers where soundex(cust_contact)= soundex('Michael Green');

6.2. 日期和时间处理函数

# 使用datepart()函数选取2020年
select 列1 from 表名1 where datepart(yy,日期列)=2020;

6.3. 数值处理函数

函数	说明
ABS()	返回一个数的绝对值
COS()	返回一个角度的余弦
EXP()	返回一个数的指数值
PI()	返回圆周率pi的值
SIN()	返回一个角度的正弦
SQRT()	返回一个数的平方根
TAN()	返回一个角度的正切

7. 汇总数据

7.1. 聚集函数

对某些行运行的函数，计算并返回一个值。

7.1.1. SQL聚集函数

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

AVG()函数

select avg(列1) as avg_列1 from products;

AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个AVG()函数。只有一个例外是要从多个列计算出一个值时。avg()函数忽略列值为NULL的行。

COUNT()函数

#COUNT()函数两种使用方法
#第一种，使用COUNT(*)对表中行的数目进行计数，不管表列中包含的空值（NULL）还是非空值
select COUNT(*) as num_cust from 表名;
#第二种，使用COUNT(列名)对特定列中具有值的行进行计数，忽略NULL值。
select COUNT(列名) as col from 表名;

如果指定列名，则COUNT()函数会忽略指定列的值为NULL的行，但如果COUNT()函数中用的是星号（*），则不忽略。同时MIN()、MAX()和SUM()都会忽略列值为NULL的行。

7.2. 聚集不同值

select avg(distinct 列名1) as col1 from  表名1;

distinct不能用于count(*)
如果指定列名，则distinct只能用于count()。distinct不能用于count(*)。类似地，distinct必须使用列名，不能用于计算或表达式。

8. 分组数据

8.1. 数据分组

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

8.2. 创建分组

#分组使用select语句group by 子句建立的。
select 列1(主键), count(*) as col1 from products group by 列1;

• group by id(id是主键)的时候，select什么都没有问题，包括有聚集函数。
• group by role(非主键)的时候，select 只能是聚集函数和role(group by 的字段)，否则报错。

8.3. 过滤数据

可以使用having和where进行数据过滤

两者区别，where在数据分组前进行过滤，having在数据分组后进行过滤。这是一个重要的区别，where排除的行不包括在分组中。这可能会改变计算值，从而影响having子句中基于这些值过滤掉的分组。

select 列1 from 表1 where 列2>n group by 列2 having 列3>m;

8.4. 分组和排序

order by 和 group by 经常完成相同的工作，但它们非常不同。

order by	group by
对产生的输出排序	对行分组，但输出可能不是分组的顺序
任意列都可以使用（甚至非选择的列也可以使用）	只可能使用选择列或表达式列，而且必须使用每个选择表达式
不一定需要	如果与聚集函数一起使用列（或表达式），则必须使用

一般在使用group by 子句时，应该也给出order by 子句。这是保证数据正确排序的唯一方法。千万不要仅依赖group by 排序数据。

8.5. select子句及其顺序

函数	说明	是否必须使用
select	要返回的列或表达式	是
from	从中检索数据的表	仅在从表选择数据时使用
where	行级过滤	否
group by	分组说明	仅在按组计算聚集时使用
having	组级过滤	否
order by	输出排序顺序	否

9. 使用子查询

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

9.1. 利用子查询进行过滤

举例：

#分开书写mysql
select order_num from orderitems where prod_id = 'RGAN01';
select cust_id from orders where order_num in (20007,20008);
#结合这两个查询，把第一个查询变为子查询
select cust_id from orders where order_num in(select order_num from orderItems where prod_id = 'RGAN01');

作为子查询的select语句只能查询单个列。企图检索多个列将返回错误。

9.2. 利用计算字段使用子查询

举例：

select count(*)as orders from orders where cust_id = 1000000001;
#对每个顾客执行count(*),应该将它作为一个子查询。
select cust_name,cust_state,(select count(*) from orders where
orders.cust_id = customers.cust_id) as orders from customers 
order by cust_name;

完全限定名：
表名.列名

10. 联结表

为何使用联结
使用联结可以用一条select语句就检索出多个表中的数据。

10.1. 关系表

关系表的设计就是把信息分解成多个表，一类数据一个表。各表通过某些共同的值互相关联（也叫关系数据库）

可伸缩性
能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称为可伸缩性好。

10.2. 创建联结

举例：

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,products;

由于没有where子句，第一个表中的每一行将与第二个表中的每一行配对，而不管它们逻辑上是否能配在一起。要保证所有联结都有where子句，否则将返回比想要的数据多得多的数据。

10.3. 内联结

内联结也称为等值联结。
例1:

#两个表
select vend_name, prod_name,prod_price from Vendors inner join products on vendors.vend_id = products.vend_id;
#三个表
select prod_name,vend_name,prod_price,quantity from orderitems, products,vendors where products.vend_id = vendors.vend_id and orderItems.prod_id = products.prod_id and order_num = '20007';

性能考虑
数据库管理系统在运行时关联指定的每个表，以处理联结。这种处理可能非常耗费资源，因此应该注意，不要联结不必要的表。联结的表越多，性能下降的越厉害。

11. 创建高级联结

11.1. 使用表别名

表别名只在查询执行中使用。与列别名不一样，表别名不返回到客服端。

#表别名使用方法
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 prod_id = 'RGAN01';

11.2. 外联结

外联结详解
外联接

12. 组合查询

12.1. 组合查询

多数SQL查询只包含从一个或多个表中返回数据的单条select语句。但是，SQL也允许执行多个查询，并将结果作为一个查询结果集返回。这些组合查询通常称为并（union）或复合查询。

• 在一个查询中从不同的表返回结构数据。
• 对一个表执行多个查询，按一个查询返回数据。

12.2. 创建组合查询

可用UNION操作符来组合数条SQL查询。利用UNION，可给出多条select语句，将它们的结果组合成一个结果集。
分句查询：

select cust_name,cust_contact,cust_email from customers where cust_state in('IL','IN','MI');

select cust_name,cust_contact,cust_email from customers where cust_name='Fun4All';

使用UNION连接：

select cust_name,cust_contact,cust_email from customers where cust_state in('IL','IN','MI')
union 
select cust_name,cust_contact,cust_email from customers where cust_name='Fun4All';

12.3. UNION规则

UNION在组合使用时候需要注意的规则：

• union必须由两条或两条以上的select语句组成，语句之间用关键字UNION分隔（因此，如果组合四条select语句，将要使用三个union关键字）。
• union中的每个查询必须包含相同的列、表达式或聚集函数（不过，各个列不需要以相同的顺序列出）。
• 列数据类型必须兼容：类型不必完全相同，但必须是数据库管理系统可以隐含转换的类型。

如果结合union使用的select语句遇到不同的列名，那么会返回什么名字呢？比如说，如果一条语句是select pro_name,而另一条语句是select productname,那么查询结果返回的是什么名字呢？

答案是：它会返回第一个名字，举的这个例子就会返回prod_name,而不管第二个不同的名字，这也意味着你可以对第一个名字使用别名，因而返回一个你想要的名字。

12.4. 对组合查询结果排序

在最后一个select语句中使用order by, order by将排序整个列表。

13. 插入数据

13.1. 数据插入

13.1.1. 插入完整的行

insert into customers values(1000000006,'Toy Land','123 Any Street','New York','NY','11111','USA',NULL,NULL);

这个例子将一个新顾客插入到Customers表中。存储到表中每一列的数据在VALUES子句中给出，必须给每一列提供一个值。如果某列没有值，如上面的cust_contact和cust_email列，则应该使用NULL值(假定表允许对该列指定空值)。各列必须以它们在表中出现的次序填充。这种方式比较方便，但是不安全，不能保证各列在下一次表结构变动后保持完全相同的次序。

安全方式

insert into customers(cust_id,cust_name,
cust_address,cust_city,cust_state,cust_zip,
cust_country,cust_contact,cust_email) values(1000000006,
'Toy Land','123 Any Street','New York','NY','11111','USA',NULL,NULL);

在插入行时，数据库管理系统将用VALUES列表中的相应值填入列表中的对应项。VALUES中的第一个值对应于第一个指定的列名，第二个值对应于第二个列名，如此等等。这种方法列名可选的，对应VALUES值也是可选的。

13.1.2. 插入检索出的数据

insert 一般用来给表插入具有指定列值的行。insert还存在另一种形式，可以利用它将select 语句的结果插入表中，这就是所谓的insert select。

假如想把另一表中的顾客列合并到Customers表中，不需要每次读取一行再将它用Insert插入，可以如下进行：

insert into customers(cust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)
select cust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country from CustNew;

注意：
数据库管理系统使用的是列的位置，因此select中的第一列（不管其列名）将用来填充表列中指定的第一列，第二列将用来填充表列中的指定第二列，如此等等。

13.2. 从一个表复制到另一个表

#复制全表使用*，复制部分表使用列名
select * into custcopy from customers;

14. 更新和删除数据

14.1. 更新数据

更新（修改）表中的数据，可以使用update语句。有两种使用update的方式：

• 更新表中的特定行；
• 更新表中的所有行。
  在使用update时一定要细心。因为稍微不注意，就会更新表重的所有行，所以不要省略where子句。
  

#update语句由三部分组成,分别为：
#1.要更新的表；
#2.列名和它们的新值；
#3.确定要更新哪些行的过滤条件。
update customers set cust_email = 'kim@thetoystore.com' where cust_id = 1000000005;

14.2. 删除数据

从一个表中删除（去掉）数据，使用delete语句。有两种使用delete的方式：

• 从表中删除特定的行；
• 从表中删除所有行。

#从customers表中删除一行：
delete from customers where cust_id = 1000000006;

delete不需要列名或通配符。delete删除整行而不是删除列。要删除指定的列，请使用update语句。
delete语句从表中删除行，甚至是删除表中所有行。但是，delete不删除表本身。如果想从表中删除所有行，不要使用delete。可使用truncate table语句，它完成相同的工作，而速度更快（因为不记录数据的变动）；

14.3. 更新和删除的指导原则

如果省略了where子句，则update或delete将被应用到表中所有的行。换句话说，如果执行update而不带where子句，则表中每一行将用新值更新。类似地，如果执行delete语句而不带where子句，表的所有数据都将被删除。

使用update或delete时所遵循的重要原则：

• 除非确实打算更新和删除每一行，否则绝对不要使用不带where子句的update或delete语句。
• 保证每个表都有主键，尽可能像where子句那样使用它（可以指定各主键、多个值或值得范围）。
• 在update或delete语句使用where子句前，应该先用select进行测试，保证它过滤的是正确的记录，以防编写的where子句不正确。

15. 创建表和操纵表

15.1. 创建表

一般有两种创建表的方法：

• 使用交互式创建和管理数据库表的工具；
• 表也可以直接用SQL语句操纵。

15.1.1. 表创建基础

利用create table创建表，必须给出下列信息：

• 新表的名字，在关键字create table之后给出；
• 表列的名字和定义，用逗号分隔；
• 有的DBMS还要求指定表的位置。

create table products(
prod_id char(10) not null,
vend_id char(10) not null,
prod_name char(254) not null,
prod_price decimal(8,2) not null,
prod_desc varchar(1000) null 
);

15.1.2. 使用NULL值

NULL值就是没有值或缺值。允许NULL值的列也允许在插入行时不给出该列的值。不允许NULL的列不接受没有列值的行，换句话说，在插入或更新行时，该列必须有值。

理解NULL
不要把NULL值与空字符串相混淆。NULL值没有值，不是空字符串。如果指定’'(两个单引号，其间没有字符)，这在NOT NULL 列中是允许的。空字符串是一个有效的值，它不是无值。NULL值用关键值NULL而不是空字符串指定。

15.1.3. 指定默认值

默认值在create table语句的列定义中用关键字default指定。

create table orderitems(
order_num integer not null,
order_item integer not null,
prod_id char(10) not null,
quantity integer not null default 1,
item_price decimal(8,2) not null
);

15.2. 更新表

给表中增加一列：

alter table vendors add vend_phone char(20);

给表中删除一列

alter table vendors drop column vend_phone;

复杂的表结构更改一般需要手动删除过程，它涉及以下步骤

1. 用新的列布局创建一个新表；
2. 使用insert select语句，从旧表复制数据到新表。有必要的话，可以使用转换函数和计算字段。
3. 检验包含所需数据的新表；
4. 重命名旧表（如果确定，可以删除它）；
5. 用旧表原来的名字重新命名新表；
6. 根据需要，重新创建触发器、存储过程、索引和外键。

小心使用alter table
使用alter table要极为小心，应该在进行改动前做完整的备份（表结构备份和数据备份）。类似地，如果删除了不应该删除的列，可能会丢失该列中所有数据。

15.3. 删除表

drop table custcopy;

删除表没有确认步骤，也不能撤销，执行这条语句将永远删除该表。

15.4. 重命名表

# 第一种方法，RENAME TABLE 表名 TO 新表名； 这里面的TO不可以省略
rename table user11 to user10;
# 第二种方法，ALTER TABLE 表名 RENAME [TO|AS] 新表名
alter table user10 rename to user11;

16. 使用视图

16.1. 视图

视图是虚拟的表。与包含数据的表不同，视图只包含使用时动态检索数据的查询。

16.1.1. 为什么使用视图

• 重用SQL语句。
• 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道其基本查询细节。
• 使用表的一部分而不是整个表。
• 保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限。
• 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。创建视图以后，可以用与表基本相同的方式使用它们。可以对视图执行select操作，过滤和排序数据，将视图联结到其他视图或表，甚至添加和更新数据。

要知道视图仅仅是用来查看存储在别处数据的一种设施，视图本身不包含数据，因此返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时，视图将返回改变过的数据。

性能问题
因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时需要的所有检索。如果你用多个联结和过滤创建了复杂的视图或嵌套了视图，性能可能会下降得很厉害。因此，在部署使用了大量视图的应用前，应该进行测试。

16.1.2. 视图的规则和限制

下面是关于视图创建和使用的一些最常见的规则和限制。

• 与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）。
• 对于可以创建的视图数目没有限制。
• 创建视图，必须具有足够的访问权限。这些权限通常由数据库管理人员授予。
• 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造视图。不过嵌套视图可能会严重降低查询的性能，因此在产品环境中使用之前，应该对其进行全面测试。
• 视图不能索引，也不能有关联的触发器或默认值。

16.2.创建视图

视图用create view 语句来创建。与create table 一样，create view 只能用于创建不存在的视图。删除视图，可以使用drop语句，其语法为drop view viewname;。覆盖（或更新）视图，必须先删除它，然后再重新创建。

16.2.1. 利用视图简化复杂的联结

#创建视图
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 ;

这条语句创建了一个productcustomers 的视图，它联结三个表，返回已订购了任意产品的所有顾客的列表。如果执行select * from productcustomers,将列出订购了任意产品的顾客。

检索订购产品RGAN01的顾客，可如下进行：

select cust_name,cust_contact from productcustomers where prod_id = 'RGAN01';

这条语句通过where子句从视图中检索特定数据。当数据库处理此查询时，它将指定的where子句添加到视图查询已有的where子句汇总，以便正确过滤数据。

16.3. 小结

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

17. 使用存储过程

18. 管理事务处理

18.1. 事务处理

使用事务处理，通过确保成批的SQL操作要么完全执行，要么完全不执行，来维护数据库的完整性。

处理事务的几个术语：

• 事务（transaction）指一组SQL语句；
• 回退（rollback）指撤销指定SQL语句的过程；
• 提交（commit）指将未存储的SQL语句写入数据库表；
• 保留点（savepoint）指事务处理中设置的临时占位符，可以对它发布回退。

18.2. 控制事务处理