引:虽然用了这么久的数据库，但是有时候使用起来难免会去查怎么使用，这样下去也不是办法，所以自己就在这里以Mysql为例总结一下经常用到的地方。
基本注意点
- SQL关键字使用大写，所有列和表名使用小写
- SQL语句由子句构成，一个子句通常由一个关键字和一个数据组成，子句的顺序一般为:
SELECT 数据 FROM 数据 WHERE 数据 GROUP BY 数据 HAVING 数据 ORDER BY 数据 LIMIT 数据
- 通配符搜索的处理一般要比其他搜索所花时间长，不要过度使用通配符，如果要，就应该将通配符放在搜索集合小的搜索上
- 多数DBMS使 +或||来实现拼接，MYSQL则使 Concat函数来实现
- 数据库列般称为列，计算字段一般称为字段
- MySQL使用的日期格式必须是yyyy-mm-dd
- WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤
- 子查询最常见的使用是在WHERE子句的IN操作符后
- 在删除或更新表之前先SELECT出来，防止删错
- 利用外键约束，防止删除与其他表相关联的行
MySQL基本使用
- MySQL帮助
相关命令 -h/–help
- MySQL服务启动和关闭
mysql.server start/stop
MySQL连接
mysql -u 用户名 -p 密码 -h 主机名 -P 端口
（默认用户名为使用者名，主机名默认为localhost，端口默认为3306）
- 退出命令行
quit/exit
- 显示所有数据库
SHOW DATABASES
- 使用数据库
USE 数据库名
- 显示某库下所有的表
SHOW TABLES
- 显示某表下面的列
SHOW COLUMNS FROM 表名 / DISCRIBE 表名
- 显示服务器的状态信息
SHOW STATUS
- 显示创建特定数据库或表的MySQL语句
SHOW CREATE DATABASE 数据库名 / TABLE 表名
安全管理
用户管理
USE mysql
SELECT user FROM user
CREATE USER rex IDENTIFIED BY ‘密码’ //创建用户账号
RENAME USER rex TO rex1 // 修改用户名
DROP USER rex // 删除用户账号
SHOW GRANTS FOR rex // 显示rex的权限，USAGE ON .表示没有权限
GRANT SELECT ON 数据库/表.* TO rex // 给rex对数据库/表的读权限
REVOKE SELECT ON 数据库/表.* TO rex // 撤销rex对数据库/表的读权限
更改密码
SET PASSWORD FOR rex = Password ‘新密码’
SET PASSWORD = Password ‘新密码’ // 修改当前用户的密码
数据库维护
- 数据库备份
- 使用mysqldump
- 使用mysqlhotcopy
- 使用BACKUP TABLE或SELECT INTO OUTFILE
维护数据库
ANALYZE/CHECK TABLE 表名 // 检查表键是否正确
OPTIMIZE TABLE 表名 // 回收所用空间
- 查看日志文件
- 错误日志：通常为/data/hostname.err，可通过–log-error修改文件
- 查询日志：通常为/data/hostname.log，可通过–log修改文件
- 二进制文件：通常为/data/hostname-bin
- 缓慢查询日志：通常为/data/hostname-slow.log，可通过hostname-slow- queries修改文件
创建和操纵表
- 创建表
CREATE IF NOT EXISTS TABLE 表名(id int NOT NULL AUTO_INCREMENT ,…, PRIMARY KEY(id)) ENGINE=InnoDB
更新表
ALTER TABLE 表名 ADD 类型 // 增加列
ALTER TABLE 表1 ADD CONSTRAINT fk表1表2 FOREIGN KEY(表2id) REFERENCES 表2(表2id)
删除表
DROP TABLE 表名
- 重命名表
RENAME TABLE 表名1 TO 表名2
查询数据
基本查询数据
- 检索单个列
SELECT prod_name FROM product
- 检索多个列
SELECT prod_id, prod_name FROM product
- 检索所有列
SELECT * FROM product
- 检索不同的行
SELECT DISTINCT vend_id FROM product
限制结果的行数为前5条/第二个5条
SELECT * FROM product LIMIT 5 / LIMIT 5(开始位置，第一行其实第0 行),5(要检索的行数)
SELECT * FROM product LIMIT 5 (偏移量) OFFSET 5 (开始位置)
排序检索数据
- 按一个列排序
SELECT prod_name FROM product ORDER BY prod_name
- 按多个列排序(先按prod_price排序，如果相同，则比较prod_name)
SELECT prod_id, prod_price, prod_name FROM product ORDER BY prod_price, prod_name
- 指定排序方向(默认升序ASC，降序DESC)
SELECT prod_id, prod_price, prod_name FROM product ORDER BY prod_price DESC
过滤数据
- WHERE子句过滤
SELECT prod_name, prod_price FROM product WHERE prod_price =
2.50 WHERE子句操作符
操作符 | 说明
—|—
= | 等于
<> | 不等于
!= | 不等于
< | 小于
<= | 小于等于
> | 大于
>= | 大于等于
BETWEEN… AND | 在指定的两个值之间(mysql包括两端)
NOT BETWEEN… AND | 不在指定的两个值之间(mysql不包括两端)- 空值检查(需要注意null和没有值得区别)
SELECT prod_name FROM products WHERE prod_price IS NULL
- 数据过滤操作符
操作符 | 说明
—|—
AND | 且（优先级比OR高）
OR | 或
IN | 在指定范围内，如WHERE id IN (1002 1003)
NOT IN | 不在指定范围内 - 数据过滤通配符(利用LIKE操作符)
操作符 | 说明
—|—
% | 表示任意字符出现的任意次数
_ | 表示单个字符 - 用正则表达式进行搜索(正则表达式的使用自行搜索)
SELECT * FROM product WHERE prod_name REGEXP ‘.000’ OR
prod_name REGEXP ‘1000|2000’ OR prod_name REGEXP ‘[123] Ton’
创建计算字段
计算字符是运行时在SELECT语句内创建的。
- 拼接字段(Concat函数)
SELECT Concat(vend_name,’(‘,vend_country,’)’) FROM vendors
- 使用别名(列名，表名都可以使用，使用关键字AS，也可省略)
SELECT Concat(RTrim(vend_name),’(‘, RTrim(vend_country),’)’) AS vend_title FROM vendors
- 执行算术计算(加减乘除)
SELECT quantity*item_price AS expanded_price FROM orderitems
使用数据处理函数
- 文本处理函数
函数 | 说明
—|—
Left() | 返回串左边的字符
Right() | 返回串右边的字符
Length() | 返回串的长度
Locate() | 找出一个串的子串
Lower() | 将串转化为小写
Upper() | 将串转化为大写
LRrim() | 去掉串左边的空格
RTrim() | 去掉串右边的空格
Trim() | 去掉串两边的空格
SubString() | 返回子串的字符 日期和时间处理函数
函数 | 说明
—|—
AddDate() | 增加一个日期(天、周等)
AddTime() | 增加一个时间(时、分等)
CurDate() | 返回当前的日期
CurTime() | 返回当前的时间
Date() | 返回日期时间的日期部分
DateDiff() | 计算两个日期之差
Date_Format() | 返回一个格式化的日期或时间串
Day() | 返回一个日期的天数部分，类推年月日，时分秒
Now() | 返回当前日期和时间数值处理函数
函数 | 说明
—|—
Abs() | 返回一个数的绝对值
Cos() | 返回一个角度的余弦，类推正弦
Exp() | 返回一个数的指数值
Mod() | 返回除数操作的余数
Rand() | 返回一个随机数
Sqrt() | 返回一个数的平方根
汇总数据
聚集函数
函数 | 说明
—|—
AVG() | 返回某列的平均值
COUNT() | 返回某列的行数
MAX() | 返回某列的最大值
MIN() | 返回某列的最小值
SUM() | 返回某列的总和
分组数据
- 创建分组(先分组后聚集)
SELECT vend_id COUNT(*) AS num_prods FROM product GROUP BY vend_id
- 过滤分组
SELECT cust_id COUNT() AS orders FROM order GROUP BY cust_id HAVING COUNT()>=2
使用子查询
- 利用子查询进行过滤(找出买TNT2的顾客信息)
SELECT cust_name, cust_contact FROM customers WHERE cust_id IN (SELECT cust_id FROM order WHERE order_num IN (SELECT order_num FROM orderitem WHERE prod_id=’TNT2’))
- 使用计算字段使用子查询(显示顾客的信息和订单量)
SELECT cust_name, cust_state, (SELECT COUNT (*) FROM order WHERE order.cust_id = customer.cust_id) AS orders FROM customer
联结表查询
- 联结查询(没有WHERE子句会产生笛卡尔积)
SELECT vend_name, prod_name FROM vendor, product, WHERE vendor.vend_id = product.vend_id
- 内部联结(等值联结，首选)
SELECT vend_name, prod_name FROM vendor INNER JOIN product ON vendor.vend_id = product.vend_id
- 多表联结
SELECT vend_name, prod_name, quantity FROM vendor, product, orderitem WHERE product.vend_id = vendor.vend_id AND orderitem.prod_id = product.prod_id
- 自联结(一般联结查询要比子查询快)
ELECT p1.prod_id, p1.prod_name FROM product p1, product p2 WHERE
p1.vend_id = p2.vend_id AND p2.prod_id = ‘DTNTR’ - 自然联结(排除一个列多次出现，使每个列只返回一次; 大部分内部联结都是自然联结)
- 外部联结(联结包括了那些在相关表没有关联行的行，左外联结(左边的所有行必出现)，右外联结(右边的所有行必出现))
SELECT vend_name, prod_name FROM vendor LEFT OUTER JOIN product ON vendor.vend_id = product.vend_id
组合查询
- 创建组合查询(大部分使用WHERE子句更快，但是对于多个表使用UNION可能更简单，重复行会被自动取消，如果不取消，使用UNION ALL)
SELECT vend_id, prod_id, prod_price FROM product WHERE prod_price <=5 UNION SELECT vend_id, prod_id, prod_price FROM product WHERE vend_id IN (1001, 1002)
插入数据
- 插入一行数据(如果是完整的行，可以不写列名，但是要注意值的顺序)
INSERT INTO customer(列名) VALUES(类名对应的值)
- 插入多个行
INSERT INTO customer(列名) VALUES(类名对应的值),VALUES(类名对应的值)
- 插入检索出的数据
INSERT INTO customer(列名) SELECT (列名) FROM 表名
更新数据
- 更新表中特定的列(一定不要忘了WHERE子句)
UPDATE customer SET 列名 = 值 WHERE 子句
- 更新多列
UPDATE customer SET 列名 = 值,列名 = 值 WHERE 子句
删除数据
- 删除特定的行
DELETE FROM 表 WHERE 子句
使用视图
- 创建视图
CREATE VIEW 视图名 AS SELECT 语句
- 查看创建视图的语句
SHOW CREATE VIEW 视图名
- 删除视图
DROP VIEW 视图名
- 更新视图（先删除原视图，再创建新视图）
参考
- 《MySQL必知必会》