主键
- 总是应该定义主键
- 主键要求:
- 不更新主键的值
- 不重用主键的值
- 不在主键中使用可能会更改的值
连接
- 连接数据库:
mysql -u{username} -p{password} - 选择表:
USE tables; - 查看表/数据库:
SHOW tables/databases; - 查看列:
SHOW COLUMNS FROM tables;
检索数据
- 单列:
SELECT col FROM tables; - 多列:
SELECT col1, col2 FROM tables; - 所有列:
SELECT * FROM tables; - 检索不同的行:
SELECT DISTINCT col FROM tables; - 限制结果:
SELECT col FROM tables LIMIT 1,1
注意:
- 检索出来的数据都是未排序的,并且原始数据没有格式
- 一般不要使用*通配符,指出需要检索的列提高性能
- 通配符用处:检索未知列
- 不能部分使用DISTINCT
- LIMIT第一个参数为开始位置,第二个为检索的行数,开始位置从0开始
排序检索数据
- 单列排序:
SELECT col FROM table ORDER BY col - 多列排序:
SELECT col1,col2 FROM table ORDER BY col1,col2 - 指定排序方向:
SELECT col FROM table ORDER BY col DESC - 多列指定方向:
SELECT col1, col2 FROM table ORDER BY col1 DESC, col2
注意:
- 如果不明确排序顺序,不应假定检索出来的数据有顺序
- 多列时排序关键字有主次顺序之分
- MySQL默认为升序排序
- 如果想要对多个列降序排序,需要对每个列指定DESC关键字
- 位置: FROM ORDER BY LIMIT
过滤数据
- 检查单个值:
SELECT col FROM table WHERE col = ? - 检查不匹配:
SELECT col FROM table WHERE col <> ? - 检查范围值:
SELECT col FROM table WHERE col < ? - 空值检查:
SELECT col FROM table WHERE col IS NULL - 组合WHERE子句:
- AND操作符:
SELECT col FROM table WHERE col1 = ? AND col2 = ? - OR操作符:
SELECT col FROM table WHERE col1 = ? OR col2 = ? - IN操作符:
SELECT col FROM table WHERE col IN (val1, val2) - NOT操作符:
SELECT col FROM table WHERE col NOT IN (val1, val2)
- AND操作符:
注意:
- 数据检索和过滤应在数据库中完成,以提高应用的性能和减少带宽消耗
- AND和OR计算次序:AND会被优先计算,优先级需要使用圆括号来提升
- IN的功能和OR相当,但是IN的执行更快且可以包含其他SELECT语句建立子句
用通配符过滤数据
- 百分号(%)通配符:
SELECT col FROM table WHERE col LIKE 'xx%' - 下划线(_)通配符:
SELECT col FROM table WHERE col LIKE '_ xx'
注意:
- 尾空格会干扰通配符匹配
- %不能匹配NULL的值
- 下划线只匹配单个字符
- 不要过度使用通配符
使用正则表达式过滤
SELECT col FROM table WHERE col REGEXP 'reg'- 匹配字符类
- 定位符
注意:
- MySQL只支持正则的一个很小的子集
- 区分大小写需要在REGEXP后加BINARY关键字
计算字段
- 拼接字段:
SELECT Concat(col1, 'str', col2, 'str2') FROM table - 使用别名:
SELECT col AS name FROM table - 执行算术计算:
SELECT col*col2 AS name FROM table
注意:
- 计算字段用于格式化检索出的数据
- LTrim()和RTrim()函数可以去除字段左右两边空格
数据处理函数
常用文本处理函数:
常用日期处理函数:
- 检索时间:
SELECT col FROM table WHERE Date(col2) = 'xxxx-xx-xx' - 检索时间段:
SELECT col FROM table WHERE Date(col) BETWEEN 'xxxx-xx-xx' AND 'xxxx-xx-xx'
注意:
- 总是应该使用4位数的年份
数值处理函数:
汇总数据
聚集函数
注意:
- 使用COUNT(*)计数包括NULL值,COUNT(col)则不包含NULL值
分组数据
- 创建分组:
SELECT col FROM table GROUP BY col - HAVING过滤分组:
SELECT col, COUNT(*) FROM table GROUP BY col HAVING COUNT(*) > 2
注意:
- GROUP BY子句可以包含任意数目的列
- 建立分组时,指定的所有列都一起计算
- GROUP BY子句中列出的每个列都必须是检索列或有效表达式
- 除计算语句外, SELECT的每个列都需要在GROUP BY中给出
- 如果分组中有NULL值,将NULL作为一个分组返回
- GROUP BY子句必须出现在WHERE之后,ORDER BY之前
- WHERE在分组之前过滤,HAVING在分组之后过滤
- GROUP BY之后应该
使用子查询
- 利用子查询进行过滤:
SELECT col FROM table1 WHERE col IN (SELECT col2 FROM table2 WHERE col2=?) - 作为计算字段:
SELECT col,(SELECT COUNT(*) FROM table2 WHERE col3=col4) FROM table1
注意:
- 列必须匹配,子查询SELECT和WHERE应保持相同的列
- 使用子查询时需要考虑效率和性能
联结表
- 创建联结:
SELECT col1, col2 FROM table1, table2 WHERE table1.col=table2.col - 内部联结:
SELECT col1, col2 FROM table1 INNER JOIN table2 ON table1.col=table2.col - 外部联结:
SELECT col1, col2 FROM table1 LEFT OUTER JOIN table2 ON table1.col=table2.col
注意:
- 在引用的列名可能出现二义性时需要使用完全限定列名
- 没有WHERE语句限定的结果将返回笛卡尔积
- WHERE为内部联结,是等值联结
- 可以使用表别名缩短sql语句和重复使用一张表
组合查询
- 使用UNION:
SELECT col FROM table1 WHERE col2=? UNION SELECT col FROM table1 WHERE col3=?
注意:
- UNION必须由两条或以上的SELECT组成
- UNION中的每个查询必须包含相同的列,表达式或聚集函数
- 列数据必须兼容
- UNION默认会去重,取消去重使用UNION ALL查询
- UNION只能使用一条排序语句出现在最后一个SELECT之后
全文本搜索
SELECT col FROM table WHERE Match(col) Against('text')- 对结果排序:
SELECT col,Match(col) Against('text') AS rank FROM table
使用视图
CREATE VIEW view1 AS SELECT col FROM table