MYSQL快速复习笔记

显示当前所有的可用数据库

SHOW DATABASE;    

显示当前数据库内可用表

SHOW TABLES ;

显示某个表中的所有属性信息

SHOW COLUMNS FORM 表名

SHOW STATUS 显示广泛的服务器状态

SHOW CREATE DATABASE/TABLE  显示创建库或表的SQL语句是啥

SHOW GRANTS 显示授权用户

SHOW ERRORS SHOW WARNINGS  显示服务器错误或警告消息

检索单个列

SELECT prod_name

FROM products

检索多个列

SELECT id,name,price

FROM products

检索所有列

SELECT *

FROM products

检索出不重复的行

SELECT DISTINCT id

FROM products

返回某些行

SELECT name

FROM products

LIMIT  begin,len  //即开始行，和行数

注意： 行从0开始，  故LIMIT1，返回的是第二行

按年龄排序

SELECT name,age

FROM products

ORDER BY age

找出年龄最小的？ 加个LIMIT 1即可

按多个列排序

先按price排，再按name排

SELECT id,price,name

FROM products

ORDER BY price, name

降序排序

按price降序排，再按name升序排

SELECT id,price,name

FROM products

ORDER BY price DEC, name

找出price=2的人

SELECT name, price

FROM products

WHERE price = 2

注意： ORDER BY 排序语句应该放在WHERE的后面

SQL做where字符匹配时，不区分大小写。

不匹配检查： 给出id不是1003的人制造的产品

SELECT id,name

FROM products

WHERE id <> 1003;   //或者 id != 1003

范围值检查：

SELECT name,price

FROM products

WHERE price BETWEEN 5 AND 10

空值检查

WHERE price IS NULL  

多个过滤条件（AND是且， OR是或）

WHERE id=1003 AND price <10 AND age < 18

AND和OR的组合

WHERE id=1002 OR id=1003 AND price>10

根据SQL的规则， AND优先于OR，变成了

WHERE id=1002 OR （id=1003 AND price>10）

所以最好在OR和AND组合时，加上括号

WHERE （id=1002 OR id=1003） AND price>10

取特定值，即id=1002、1003、1004的值皆可

WHERE id IN (1002,1003,1004) 

否定条件       NOT 条件，  则取不满足这个条件的行

WHERE id NOT IN (1002,1003,1004)

通配符，配合字符串匹配和LIKE使用

百分号通配符，查找以jet开头的任何名字

WHERE name LIKE  'jet%'

包含有anvil的名字

WHERE name LIKE  '%anvil%'

查找以s起头，e结尾的所有名字

WHERE name LIKE 's%e'

下划线通配符， 该符号只能代表1个任意字符

不能匹配0个字符，即该处必须有1个字符

WHERE name LIKE '_ton anvil'

使用通配符的注意事项

1.不要过度使用通配符

2.不要把通配符用在搜索模式的开始处

3.注意通配符的位置

正则表达式，伴随着REGEXP使用

LIKE匹配的是整个串，REGEXP只要出现了类似的即可，即有子串即可

例如LIKE ‘abc’ ，只能返回abc。

REGEXP ‘abc’， 则可以返回 abcd

.可以匹配任意一个字符

WHERE name REGEXP '.000'

搜索2个串, |类似于或OR

WHERE name REGEXP '1000|2000'

注意：  '1|2|3 Ton' 指代的是1或2或3 Ton， 故不要连续用|后又跟字符，而应该用[]

匹配几个字符中的一个即可

WHERE name REGEXP '[123] TON'

则可返回  1 Ton 和 2 Ton

匹配1-9加a-z

WHERE name REGEXP  '[1-9] [a-z]'

匹配 正则符号模样的特殊字符

用\\作为前导

WHERE name REGEXP '\\.'

则找到匹配.的， 此时.不代表任意字符

匹配某个字符的0个或1个

WHERE name REGEXP 'sticks?'

则可匹配stick或者sticks

指定匹配数目 {n}

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

匹配n到m数目的匹配 {n,m}

WHERE name REGEXP '[[:digit:]]{4}'

则匹配4个任意数字

^定位为文本的开始，$定位为文本的结束

注意： ^有另外一个用途，可以用来否定集合[]

在SQL中测试自己写的正则表达式对不对

SELECT ‘hello’ REGEXP 'abc'   

若正确，则返回1，否则返回0。 显然这个例子返回0

计算字段

SELECT Concat(name, '(' , country, ')' )

则返回的行里的每个值为   

LSX(CHINA)

CRISTINA(USA)

RTrim(name)可以删除name中多余的右边空格，常可以用在拼接中

给计算字段取别名

SELECT Concat(name, '(' , country, ')' ) AS newtitle

则列名叫newtitle

执行算数计算

SELECT id, num, price, num*price AS sumProfit

则多返回一个列，叫做总利润

测试拼接字段

SELECT 3*2+5,  则返回11

SELECT Now(),  则返回数据库的当前时间

数据处理函数

Select name, Upper(name) AS upcase_name       都转为大写

返回价格的平均值

SELECT AVG(price) AS avg_price

FROM products

WHERE id = 1003

计算的数量，包括NULL

SELECT COUNT（*） AS num_cust

计算num的数量，不包括NULL

SELECT COUTN（num） AS num_cust

还有MAX,MIN,SUM

指定不同元素的统计

SELECT AVG（DISTINCT price） AS avg_price

分出5个id，计算各id对应产品的数量

SELECT id, COUNT(*) AS id_num

FROM products

GROUP BY id

1.除了聚集计算语句外， SELECT中出现的每个列都必须出现在GROUP BY中

2.GROUP BY 必须出在 ORDER BY 之前

过滤分组

SELECT id， COUNT(*) AD orders

FROM orders

GROUP BY id

HAVING COUNT(*) >= 2

列出具有2个以上价格为10的产品

SELECT id, COUNT(*) AS  num

FROM products

WHERE price >= 10

GROUP BY id

HAVING COUNT(*) >=2

顺序：先WHERE过滤， 在COUNT过滤

语句使用顺序

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

LIMIT

子查询

找出订购了物品TNT2的所有客户id

SELECT cus_id

FROM orders

WHERE sail_id IN （SELECT sail_id

                                 FROM orderitem

                                 WHERE prod_id = ‘TNT2' ）

即先从产品表中，找到产品id为INT2的所有订单

再从订单表中，找到订单id匹配的所有客户。

检索出上述客户id的所有信息

SELECT cus_name, cus_age

FROM customer

WHERE  cus_id IN ( SELECT cus_id

                                   FROM orders

                                   WHERE sail_id IN （SELECT sail_id

                                                                     FROM orderitem

                                                                    WHERE prod_id = ‘TNT2' ））

显示每个客户的订单总数

SELECT name, state, (SELECT COUNT(*)

                                        FROM orders

                                        WHERE orders.cus_id = cus.cus_id  ) AS orders

FROM customers

ORDER BY name

对于customer中的每一个客户id， 都从订单表中进行检索，找出每个id的订单数量。

创建表：

CREATE TABLE 表名

（

     列名   数据类型   列属性

      ……

      PRIMARY KEY (主键列1，主键列2……) 

）ENGINE=InnoDB

添加列：

ALTER TABLE 表名

ADD 列名 数据类型

删除列：

ALTER TABLE 表名

DROP COLUMN 列名

 

删除表：

DROP TABLE 表名

重命名表：

RENAME TABLE 旧表名 TO 新表名

插入行：

INSERT INTO 表名(

列名1

列名2

……

) 

VALUES（

数据1

数据2

……

），

（

数据3

数据4

）;

这样就插入了2行

更新数据：

UPDATE 表名

SET  列名1=数据1

       列名2=数据2

WHERE 查询条件

删除数据：

DELETE FROM 表名

WHERE 查询条件

表中删除主键为：

alert table table_test drop primary key;

表中增加主键为：

alert table table_test add primary key(id);