[toc]

SHOW命令#

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

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’

匹配几个字符中的一个即可
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个任意数字

^定位为文本的开始，$定位为文本的结束
注意： ^有另外一个用途，可以用来否定集合[]

特殊UDF#

在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的订单数量。

DDL#

创建表：#

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);