MySQL：SQL

发表于 更新于 分类于
本文字数： 8.2k 阅读时长 ≈ 15 分钟
MySQL必知必会，即SQL语句

检索

对于MySQL可以配置是否区分大小写

1
2
3
4
5
6
select [all | distinct] <目标列表达式> [,<目标列表达式||计算字段>]...
	from <表名或视图名> [,<表名或视图名>]...
	[where <条件表达式> [AND | OR] [表达式]]
	[group by <列名> [having <表达式>]]
	[order by <列名> [ASC | DESC] [,<列名> [ASC |DESC]]]
    [limit <数量>]
  • select
    • 表示是一个查询操作
  • [all | distinct]
    • distinct。消除重复行，它会应用于所有的列，而不仅仅时前置它的列。
    • all，缺省值，保留所有行
  • <目标列表达式>
    • 查询若干列：则指明列名，也可使用列的全限定名
    • 查询所有列：*
    • 查询经过计算的值
      • 算术表达式
      • 字符串常量
      • 函数等
      • select name, 1-age, 'testString', count(*)
  • from <表名或视图名> [,<表名或视图名>]
    • 多表查询
  • where <条件表达式>
    • 条件表达式
      • 条件操作符
      • IN
    • 详情查看后续
  • group by
    • 对查询结果分组：按一列或多列取值相等分组，分组过后集函数只作用于一个组而不是整个查询结果
  • having
    • 对组进行筛选
  • order by
    • ASC升序、DESC降序
    • 对多列排序时，则会优先按写在前面的列排序
  • limit。限制返回的行数
    • limit 1,1则会从第2行开始返回1行（MySQL从第0行开始）
    • limit 4 offset 3意为从行3开始取4行

简单搜索

单个列：select prod_name from products

多个列：select prod_id, prod_name from products即利用,分隔列名。

所有列：select * from products

检索不同的列：select distinct vend_id from products返回vend_id不同的列。

限制结果：select prod_name from products limit 5

使用完全限定的列名：select products.prod_name from products

使用完全限定的表名：select products.prod_name from crashcourse.products

创建计算字段

存储在数据库表中的数据一般不是应用程序所需要的格式：

  • 如果想在应该字段中既显示公司名，又显示公司地址，但这两个信息一般包含在不同的表列中
  • 城市、州、邮政编码存储在不同的列中，但邮件标签打印程序需要将它们作为应该恰当格式的字段检索出来
  • 列数据是大小写混合的，但是报表程序需要所有数据大写
  • 订单表存储物品的价格与数量，为打印发票，需要总价
  • 需要根据表数据进行总数、平均数计算或其他计算

计算字段用于直接从数据库检索出转换、计算或格式化过的数据；

字段

基本上与列的意思相同，经常互换使用。数据库列一般称为列，而术语字段通常用在计算字段的连接。

拼接字段

拼接：将值联结到一起构成单个值。使用Concat()连接

1
2
3
select concat(vend_name,'(',vebd_country,')')
	frim vendors
	order by vend_name

Concat()拼接穿，将多个串连接起来形成一个较长的串。

计算

1
2
3
select quantity * item_price as expanded_price
	frim orderitems
	where order_num = 2005

别名

别名是一个字段或值的替换名，用AS赋值。有时候也称为导出列。

1
2
3
select concat(vend_name,'(',vebd_country,')') AS vend_title
	frim vendors
	order by vend_name

order by

以单列排序：select prod_name from products order by prod_name

以多列排序：select prod_id from products order by prod_price, prod_name

按降序排序：select prod_id from products order by prod_price DESC, prod_name

where

过滤单个值：select prod_name from products where prod_name= '2.5'即对串使用’’

范围值检查：select prod_name from products where prod_price between 5 and 10

空值检查：select prod_name from products where prod_price is NULL

操作符

条件操作符

  • =：
  • <>：不等于
  • !=：不等于
  • <：
  • <=：
  • >：
  • >=：
  • BETWEEN：指定的两个值之间

IN

IN操作符用来指定条件范围，范围内的每个条件都可以匹配，

select prod_name from products where vend_id IN (1002,1003)

  • 对于长的合法选项清单，IN操作符更加清楚只管，计算次序更容易管理
  • IN一般比OR操作符更快，并且可以包含其他的SELECT语句

select prod_name from products where vend_id IN (SELECT id from vend)

NOT

NOT操作符用于否定它之后所跟的任何条件。并只支持对IN、BETWEEN和EXISTS取反。

select prod_name from products where vend_id NOT IN (1002,1003)

组合过滤

即使用多个WHERE子句。以OR或AND的方式使用。

对多列进行过滤：select prod_name from products where prod_orice = 10 AND vend_id = 1003

检索匹配任一条件：``select prod_name from products where prod_orice = 10 OR vend_id = 1003`

AND与OR的计算次序：

SQL在处理OR之前优先会处理AND操作符。即AND操作符的优先级更高

select prod_name from products where vend_id = 1002 OR vend_id = 1003 AND prod_price>=10

该语句等价于：select prod_name from products where vend_id = 1002 OR (vend_id = 1003 AND prod_price>=10)

即当AND与OR混用时，一定要使用()

通配符过滤

通配符用来匹配值的一部分的特殊字符。通配符本身实际上时SQL的WHERE子句中有特殊含义的字符

%百分号

%表示任意字符出现任意次数。但是不匹配NULL

搜索以jet开头的词：select prod_name from products where prod_name like 'jet%'

_下划线

匹配任意字符出现单次

select prod_name from products where prod_name like '_ ton an'

通配符的技巧

  • 如果其他操作符能够达到相同目的，应该使用其他操作符
  • 搜索时除非必要，否则不要将通配符放在开始处
  • 仔细注意通配符的位置

正则表达式

使用REGEXP

select prod_name from products where prod_name regexp '1000' order by prod_name

group by

分组数据，count等函数只会对其对应的分组奏效

1
2
3
select vend_id, count(*) as num_prods
	from products 
	group by vend_id
  • group by子句可以包含任意数目的列，这使得能够对分组进行嵌套，为数据分组提供更细致的控制
  • 如果在group by中嵌套了分组，数据将在最后规定的分组上进行汇总，即指定的所有列都一起计算，因此不能从个别的列取回数据
  • group by子句中列出的每个列都必须时检索列或有效的表达式
  • 如果分组列中具有NULL，则NULL将作为一个分组返回
  • group必须在where之后，order by之前。

Having

过滤分组，规定包括哪些分组，排除哪些分组。即与where不同，where过滤行，having过滤分组。

例如想要列出至少有两个订单的所有顾客，因此需要基于组过滤，而不是行过滤。

1
2
3
select cust_id from orders
	group by cust_id
	having count(*)>=2;

子查询

嵌套在其他查询中的查询。在子查询当中，总是由内向外处理。

1
2
3
4
select cust_id from orders
	where order_num in (
    	select order_num from orderitems
    	where prod_id = 'TNT2')

列必须匹配：where子句当中使用子查询，则应该保证select语句具有与where子句中相同数目的列，通常子查询将返回单个列并与单个列匹配，但如果需要也可以使用多个列

子查询也可以使用=、<>等

子查询并不总是执行这种类型的数据检索的最有效的方法。

作为计算字段

1
2
3
select cust_name (select count(*) from orders 
                 where orders.cust_id = customers.cust_id)as orders
       from customers order by cust_name;

子查询并不总是执行这种类型的数据检索的最有效的方法。

联结表

联结是一种机制，用来在一条select语句中关联表。可以联结多个表返回一组输出，联结在运行时关联表中正确的行。

等值联结

1
2
3
4
select vend_name, prod_name 
	from vendors, products
	where vendors.vend_id = products.vend_id
	order by vend_name, prod_name

如果没有where语句，则将返回笛卡儿积，即返回的行数目，是第一个表的行数*第二个表。

联结的表越多，性能越差

内部联结（其效果与等值一样）

1
2
3
select vend_name, prod_name 
	from vendors inner join products
	on vendors.vend_id = products.vend_id

即在from处不同，并且使用了ON子句，而不是where

where虽然简单，但是innerjoin是首选语法，能够确保不会忘记联结条件，并且性能可能会稍微好一些

高级联结

使用别名进行联结

1
2
3
select vend_name, prod_name 
	from vendors as v inner join products as p
	on v.vend_id = p.vend_id

自联结

1
2
3
select p1.prod_id, p1.prod_name
	from products as p1, products as p2
	where p1.vend_id = p2.vend_id and p2.vend_id = 'DTNTR'

等价于：但是自联结的速度要快很多

1
2
3
4
select prod_id, prod_name from products
	where vend_id = (select vend_id
		from products
		where prod_id = 'DTNTR')

自然联结

对表进行联结时，可能有一个列不止一次出现在不止一个表当中，内部联结返回所有数据，自然联结排除多次出现，使得每个列只返回一次。但是MYSQL不自动完成该工作

外部联结

需要包含没有关联行的那些行。例如列出所有产品以及订购数量，包括没有人订购的产品。

1
2
3
select customers.cust_id, orders.order_num
	from customers left outer join orders
	on customers.cust_id = orders.cust_id

使用联结和联结条件

  • 注意所使用的联结类型，一般使用内部联结，但外部联结也是有效的
  • 保证使用正确的联结条件，
  • 在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。但在一起测试前，应该分别测试每个联结。

组合查询

执行多个查询，并将结果作为单个返回及返回。应用场景：

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

利用UNION进行组合。

UNION会自动去除重复行，其作用与单select使用多个where一样。如果需要重复行，则进行UNION ALL

1
2
3
4
5
select vend_id from products
	where prod_price <=5
UNION
select vend_id from products
	where vend_id in (1001,1002)

UNION规则

必须由两条或两条以上的select组成，并且每个查询必须包含相同的列、表达式或聚集函数

列数据类型必须兼容，类型可以不完全相同，但是必须是可以隐式转换的类型。

全文本搜索

MyISAM引擎支持，使用全文本必须索引被搜索的列，索引后，select可与match()和against()一起使用以实际执行搜索。

1
2
3
4
create table productnotes{
	note_text text null,
	FULLTEXT(note_text)#表明进行全文索引
}ENGINE=MyISAM

在导入数据时不应该开启FULLTEXT，而应该先导入数据，之后在开启索引，有助于更快地导入数据。

搜索

1
2
select note_text from productnotes
	where match(note_text) against('rabbit')

查询扩展

查询扩展用来设法放款所返回的全文本搜索结果的范围，

布尔搜索

以布尔的形式提供如下内容的细节

  • 要匹配的词
  • 要排斥的词
  • 排列提示（指定某些词比其他词更重要）
  • 表达式分组
  • 另外一些内容

Insert、update、delete

1
2
insert into customers values(null,'test')
insert into customers(cust_name, cust_address) values(null, 'test')

省略列需要满足以下某个条件：

  • 该列定义位允许NULL
  • 在表定义中给出默认值

插入多行

1
insert into customers(cust_name, cust_address) values(null, 'test'),('do','test2')

插入检索出的数据

这里并不关心表名，只关心第几列。

1
insert into customers(cust_name, cust_address) select cust_id,cust_address from custnew;

update

update可以更新表中特定行，或表中所有行。

1
2
3
update customers 
set cust_email = 'ela@gamil.com'
where cust_id =10005

在update语句中可以使用子查询。

IGNORE

如果update更新多行，如果在更新其中一行时出错，整个update会被取消。如果希望即使错误也继续执行则可以使用ignore

1
update ignore customers

delete

1
delete from customers where cust_id =10006

表操作

删除表

1
drop table customer2;

重命名表

1
rename table customer2 to customer

更新表

新增一列

1
alter table vendors add vend_phone char(20);

新增外键

1
2
3
alter table orders 
	add constranint fk_orders_customers foreign key (cust_id)
	references customers (cust_id)

函数

聚集函数

用于汇总数据而不用将它们实际检索出来。例如确定表中的行数，找出最大值等。

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

  • AVG()：堆表中的行数计数并计算特定列值之和，求得该列的平均值。会忽略所有值为NULL的行
1
2
select AVG(prod_price) as avg_price 
	from products
  • COUNT()：
    • count(*)计算所有行，不管是NULL还是非空
    • count(column)对特定列计数，并忽略NULL
1
select count(*) as num_cust from customers
  • MAX()：可以用于返回文本列的数据，
1
select MAX(prod_price) from products
  • SUM()：忽略NULL行
1
select SUM(prod_price) from products

聚集不同值

即Distinct与ALL。ALL为默认值。当使用Distinct时，各个聚集函数只会考虑不同的值。

处理函数

文本串处理函数

  • Trim()：去除串左右的空格
    • R(L)tim()：删除数据右(左)侧多余的空格
  • Upper()：将文本转换为大写
    • Lower()：转换为小写
  • SubString()：返回子串中的字符

数值计算函数

日期与时间函数

系统函数

视图

  • 重用SQL语句
  • 简化复杂的SQL操作，编写查询后，可以方便地重用而不必知道具体的查询细节
  • 使用表的组成部分而不是整个表
  • 保护数据
  • 更改数据格式和表示

因为视图不包含数据，如果使用了多个联结和过滤创建了复杂的视图，可能性能下降很多。

创建视图：

1
2
3
4
5
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

查询创建视图的语句：

1
show create view viewname

删除视图：

1
drop view viewname