mysql notee_MySQL 必知必会 note

concat.png

顺序问题：

from --> where --> group by --> having --> order by --> limit

显示所有的数据库

show databases;

使用指定的数据库

use |数据库名|;

进入到指定的数据库，可以显示该数据库中所有的表

show tables;

显示某个表中的所有字段信息

show columns from |表名|;

or

describe |表名|;

显示创建特定数据库或者表的MySQL语句

show create database |数据库名|;

show create table |表名|;

显示授予用户的权限

show grants

导入sql文件

source |路径|

取消重复行:如果指定多个列，则只有当多个列组合起来的这个组合不同时，才会起作用，也就是说distinct针对的是全部你选择的列且必须放到列名的最前面

select distinct |列名| from |表名|;

限制输出limit：mysql默认是第0行；带一个参数：最多输出几行；带两个参数：从第一个参数开始，最多输出第二参数行

select prod_name from products limit 5; "最多输出5行

select prod_name from products limit 5,5; "从行5开始的5行

排序数据:order by :如果按照多列进行排序，则执行顺序：先按照第一个列排，如果第一列相同，则按第二列排;如果要升序排列某个字段，则在order by 后面的列名后加asc(但是默认的排序就是升序的，所以没啥用)，如果要降序排列某个字段，则在order by 后面的列名后加desc

select |列名|,|列名|... from |表名| order by |列名|,|列名|... ;

order by 和 limit组合可以选中最大的或者最小的一行

select prod_price from products order by prod_price desc limit 1; "选中最高价格的一行

过滤数据

where子句操作符

操作符

说明

=

等于

<>

不等于

!=

不等于

<

小于

<=

小于等于

>

大于

>=

大于等于

between A and B

在指定的两个值之间,包括开始值和结束值

is NULL

不具有该字段的值，就是没写

连接个where子句

操作符

说明

AND

且，优先级最高(括号除外)，即先过滤该条件

OR

或者

IN

条件范围，和or一样，但是比or快,最大的优点是可以包含其他select语句

NOT

只可以对IN Between exists子句取反

select prod_name,prod_price

from products

where vend_id = 1002 or vend_id =1003 and prod_price >=10;

"供应商1003制造的价格在10元之上的商品或者供应商编号是1002的所有商品 即where vend_id = 1002 or (vend_id =1003 and prod_price >=10);

"供应商1002或1003制造的且价格都在10元之上的商品

select prod_name,prod_price

from products

where (vend_id = 1002 or vend_id =1003) and prod_price >=10;

通配符过滤：不到万不得已不要使用

通配符：比其他搜索要慢，放在过滤的最开始，搜索起来是最慢的

通配符

说明

%

任意字符出现任意次数，但不能匹配NULL

_

单个字符

select prod_id,prod_name from products where prod_name like '_ ton anvil';

select prod_id,prod_name from products where prod_name like '% ton anvil';

"会匹配到这样一条数据： .5 ton anvil

正则过滤 regexp

+ like和regexp的最大差别在于：like匹配整个列，如果匹配的文本在列值中出现，like将不会找到它，也就不会返回相应的行，而regexp则会

```sql

select prod_name from products where prod_name like '1000' order by prod_name;"没有数据

select prod_name from products where prod_name regexp '1000' order by prod_name;"会出现1000的一条数据

```

+ 匹配区分大小写，mysql中的正则是不匹配大小写的，但是可以指定区分大小写，eg: where prod_name regexp binary 'JetPack .000';

通配符

说明

.

一个字符

*

随便

+

至少有一个

？

最多有一个

^

文本的开始

$

文本的结尾

{n}

指定数目的匹配

{n,}

大于指定数目的匹配

{n,m}

匹配数目的范围

|

OR

[123]

另一种or，匹配1|2|3

[^123]

除了这些字符外的任何东西

[0-9]

0到9

[a-z]

任意字母字符

双反斜杠

前导：将特殊字符匹配，MySQL自己解释一个，正则解释一个，所以用两个反斜杠

[:alnum:]

任意字母和数字

[:alpha:]

任意字符和[a-zA-Z]一样

[:blank:]

空格和制表

[:upper:]

任意大写字母

计算字段

拼接字段 Concat()

select concat(vend_name,'(',vend_country,')')

from vendors

order by vend_name;

concat.png

另一个常用：对检索出来的数据进行算术计算

select prod_id,quantity,item_price,quantity*item_price as expanded_price

from orderitems

where order_num = 2005;

计算字段.png

处理数据

函数名

作用

RTrim()

去除列值右边的空格

Upper()

转换成大写

AVG()

返回某列的平均值,忽略null值的行

COUNT()

返回某列的行数,count(*)包括null，count(column)则不包括null

MAX()

返回某列的最大值,忽略null值的行

MIN()

返回某列的最小值,忽略null值的行

SUM()

返回某列值之和,忽略null值的行

IFNULL(A，B)

如果A返回空，则将结果设置为B

select avg(prod_price) as avg_price

from products

where vend_id =1003;

avg2.png

select avg(distinct prod_price) as avg_price

from products

where vend_id = 1003;

avg1.png

group by 子句

select vend_id,count(*) as num_prods

from products

group by vend_id;

"group by 子句指示MySQL按vend_id进行排序并分组数据

“规定：group by 中出现的列 必须在select中出现

“如果分组列中出现null值，则null作为一个分组返回

groupby.png

with rollup

select vend_id,count(*) as num_prods

from products

group by vend_id with rollup;

withrollup.png

过滤分组

select cust_id,count(*) as orders

from orders

group by cust_id

having count(*) >=2;

"对于分组之后的数据再进行过滤,而where在分组之前进行过滤

having.png

数据增删改

插入多条数据

insert into customers(

cust_name,cust_address,cust_city,

cust_state,cust_zip,cust_country)

values(

'Pep E. LaPew',

'100 main street',

'Los Angeles',

'CA',

'90046',

'USA'

),

(

'Pep2 E. LaPew',

'1002 main street',

'Los2 Angeles',

'CA2',

'900462',

'USA'

);

"这样的插入多条数据比多条insert效率要高

插入检索出来的数据:复制表的数据到另一个表中

insert into customers(

cust_name,cust_address,cust_city,

cust_state,cust_zip,cust_country

)

select

cust_name,cust_address,cust_city,

cust_state,cust_zip,cust_country

from custnew;

更新数据

update customers

set cust_email = 'example@qq.com',

set cust_name = 'The Fudds',

set cust_xxx = null "设置某个值为空就是删除该列的值

where cust_id = 10005; “

删除数据

delete from customers

where cust_id = 10006;"如果没有where语句 ，则删除表中的所有行，但没有删除表

“更快的删除表中所有行 实际上是删除了原来的表并重新创建一个表

truncate table 表名;

数据引擎

使用ENGINE=引擎名 创建表的时候

引擎

说明

InnoDB

可靠的事务处理引擎

MEMORY

数据存储在内存，速度很快

MyISAM

性能极高的引擎，支持全文本搜素，但不支持事务处理

更新表

添加列

alter table vendors

add vend_phone char(20);

删除列

alter table vendors

drop column vend_phone;

常用：定义外健

alter table orderitems

add constrain fk_orderitems_orders

foreign key(order_num) reference orders (order_num);

删除表

删除表

drop table customers2;

重命名表

rename table customers2 to customers,

backup_vendors to vendors,

bakcup_products to products;

视图

视图的作用：

举个例子：要用查询来检索订购某个产品的客户，任何需要这个数据的人都必须理解相关表的结构，并且知道如何创建查询和对表进行联结。

为了检索其他产品的相同数据，必须修改最后的where子句。

那么，现在假如可以把整个查询包装成一个名为productcustomer的虚拟表，则可以如下轻松的检索出相同的数据

作用：

重用sql语句

简化复杂的sql操作，编写查询后，可以方便的重用它而不必知道它的基本查询细节

使用表的组成部分而不是整个表

保护数据，可以给用户授予表的特定部分的访问权限而不是整个表的访问权限

更改数据格式和表示。视图可以返回与底层表的表示和格式不同的数据

视图的限制

view1.png

--把table 换成view

视图更新的限制：如果视图定义中有以下操作：

分组

联结

子查询

并

聚集函数

distinct

导出(计算)列