读书笔记——MySQL必知必会

发表于 | 更新于 | 分类于 >
本文字数： 5.3k | 阅读时长 ≈ 5 分钟

MySQL基础命令

  1. MySQL登录：mysql -u -p -h myserver -P 9999
  2. 获取帮助：mysql –help（help select）
  3. 退出程序：quit、exit
  4. 选择数据库：use 数据库名称
  5. 查看可用数据库：show databases
  6. 查看表：show tables
  7. 查看表的字段：show columns from 表名（快捷：describe 表名）
  8. 显示广泛的服务器状态信息：show status
  9. 显示创建数据库和表的语句：show create database、show create table
  10. 显示授予用户的安全权限：show grants
  11. 显示服务器错误或警告信息：show errors、show warnings

检索数据

  1. 简单搜索：select 字段名 from 表名
  2. 检索多个列：select 字段1，字段2，… from 表名
  3. 检索所有列：select * from 表名
  4. 检索并去除重复：select distinct 字段名 from 表名
  5. 分页查找（5行）：select 字段名 from 表名 limit 5（总是第一行开始）
  6. 分页查找（下一个5行）：select 字段名 from 表名 limit 5，5（开始行，行数）

排序数据

  1. 排序：select * from 表名 order by 字段名（可用不在检索内的列）
  2. 多个列排序：select * from 表名 order by 字段1，字段2
  3. 降序排序：select * from 表名 order by 字段 desc（只针对该desc前的一个列有效）
  4. 找出最昂贵物品的值：select price from products order by price desc limit 1
  5. 字段顺序：order by 在from，where，group by，having后，limit在order by后

过滤数据

  1. 基础搜索：select * from 表名 where 字段 = 条件
  2. 操作符：<>、!=(不等于)、between a and b（指定两个值之间，包头包尾）
  3. 空值检查：select * from 表名 where 列名 is null

数据过滤

  1. and操作符：select * from 表名 where 条件一 and 条件2
  2. or操作符：select * from 表名 where 条件一 or 条件2
  3. and 和 or 的优先级：and更高
  4. in操作符：select * from 表名 where 字段名 in（1002，1003）order by 字段
  5. in和or相比的优势：
    • 多个数值时，in比or直观
    • 因为使用的操作符少，执行次序更容易管理
    • in执行速度更快
    • in可以包含其他where语句
  6. not操作符：select * from 表名 where 字段名 not in（1002，1003）order by 字段
  7. not补充：MySQL的not支持：in、between和exists子句取反，不同于其他DBMS支持各种条件

用通配符进行过滤

  1. %通配符：select * from 表名 where name like ‘jet%’（%代表：任意字符、任意次数）
  2. %通配符不能匹配null
  3. _通配符：select * from 表名 where name like ‘_jet’（_代表：一个字符）
  4. 注意事项：
    • 尽量少使用通配符
    • 尽量少放在搜索模式的开始处，因为这样效率最低

用正则表达式进行搜索

  1. 简单正则表达式：select * from 表名 where name REGEXP ‘1000’ order by name
  2. 匹配一个字符：select * from 表名 where name REGEXP ‘.000’ order by name
  3. like与正则表达式区别：like没有通配符与列值相同并不会返回，正则表达式则会
  4. 正则表达式不区分大小写，除非：select * from 表名 where name REGEXP BINARY ‘.000’
  5. or匹配：select * from 表名 where name REGEXP ‘1000|2000|3000’
  6. 匹配几个字符之一：select * from 表名 where name REGEXP ‘[123]Tom’
  7. 否定字符集合：select * from 表名 where name REGEXP ‘[^123]Tom’
  8. 简化字符集合：select * from 表名 where name REGEXP ‘[1-5]Tom’
  9. 匹配特殊字符（转义）：select * from 表名 where name REGEXP ‘\.’
  10. \ \引用元字符：
    • \ \f：换页
    • \ \n：换行
    • \ \r：回车
    • \ \t：制表
    • \ \v：纵向制表
  11. 匹配字符类：
    • [:alnum:] 任意字母数字（同[a-zA-Z0-9]）
    • [:alpha:] 任意字符（同[a-zA-Z]）
    • [:blank:] 空格和制表（同\ \t）
    • [:cntrl:] ASCII控制字符（ASCII 0到31和127）
    • [:digit:] 任意数字（同[0-9]）
    • [:graph:] 与[:print:]相同，不包括空格
    • [:lower:] 任意小写字母（同[a-z]）
    • [:print:] 任意可打印字符
    • [:punct:] 既不在[:alnum:]又不在[:cntrl:]内的字符
    • [:space:] 包括空格在内的任意空白字符（同\ \f\ \t\ \r\ \n\ \v）
    • [:upper:] 任意大写字母（同[A-Z]）
    • [:xdigit:] 任意十六进制数字（同a-fA-F0-9）
  12. 重复元字符：
    • * ：0个或多个匹配
      • ：一个或多个匹配，等同于{1，}
    • ？：0个或一个匹配，等同于{0，1}
    • {n} ：指定数目匹配
    • {n，} ：不少于指定数目匹配
    • {n，m} ：匹配数目范围（m不超过255）
  13. 定位符：
    • ^ ：文本开始
    • $ ：文本结束
    • [[:<:]] ：词的开始
    • [[:>:]] ：词的结束
  14. ^ 的双重作用：（1）集合中，否定集合；（2）表示文本开始

创建计算字段（field）

  1. 字段拼接：select Concat(name,’(‘,country,’)’) from vendors order by name
  2. 去除空格：select Concat(Trim(name),’(‘,Trim(country),’)’) from vendors
  3. 去除空格：（1）右空格：RTrim()（2）左空格：LTrim()（3）左右空格：Trim()
  4. 使用别名：select Concat(name,’(‘,country,’)’) AS title from vendors
  5. 省略from子句：select Trim(‘ABC’)

使用数据处理函数

文本处理函数

  1. 文本大写：select name,Upper(name) as name_upcase from vendors
  2. 常用的文本处理函数：
    • Left() ： 返回串左边的字符
    • Length() ： 返回串的长度
    • Locate() ： 找出串的一个子串
    • Lower() ： 将串转换为小写
    • LTrim() ： 去除左空格
    • Right() ： 返回串右边字符
    • RTrim() ： 去除右空格
    • Soundex() ： 返回串的Soundex值（模拟发音）
    • SubString() ： 返回子串的字符
    • Upper() : 将串转换为大写

日期函数

  1. 日期函数：select * from 表名 where Date(order_date) = ‘2015-1-10’（仅返回日期，过滤时间）
  2. 日期函数：select * from 表名 where Year(order_date) = 2015 and Month(order_date) = 9
  3. 返回星期几：DayOfWeek()
  4. 返回当前日期：CurDate()
  5. 返回当前时间：CurTime()
  6. 计算日期差：DateDiff()

数值处理函数

  1. 绝对值 ：Abs()
  2. 余弦函数 ：Cos()
  3. 指数函数 ：Exp()
  4. 返回余数 ：Mod()
  5. 圆周率 ：Pi()
  6. 返回随机数 ：Rand()
  7. 正弦函数 ：Sin()
  8. 平方根 ：Sqrt()
  9. 正切 ：Tan()

汇总数据

聚集函数

函数 说明 补充
AVG() 返回某列的平均值 忽略null值的行
COUNT() 返回某列的行数 COUNT(*)：不忽略null
COUNT(列名)：忽略null
MAX() 返回某列最大值 忽略null值的行
MIN() 返回某列最小值 忽略null值的行
SUM() 返回某列值之和 忽略null值的行
  1. 聚集不同值：select AVG(DISTINCT price) AS avg_price from products where id=1003
  2. 聚集不同值说明：MySQL 5及后期版本可用

分组数据

  1. 分组数据：select vend_id，COUNT(*) AS num_prods from 表名 GROUP BY vend_id
  2. 分组数据补充：聚集函数对每个分组聚集而不是整个结果集
  3. GROUP BY子句规定：
    • 可以包含任意数目列（分组可以嵌套）
    • 如果嵌套，数据将在最后的分组上汇总（不能从个别列取回数据)
    • 该子句中所有列必须是检索列或有效表达式（不能是聚集函数），如果select子句中使用表达式，该子句需要使用相同表达式，不能使用别名
    • 除聚集计算语句外，select语句每个列必须在GROUP BY子句中给出
    • 若分组列具有null，null将作为一个分组返回；若列有多行null，将他们分为一组
    • GROUP BY必须在where后，order by前
  4. 获得汇总级别值：select id,COUNT(*) AS num_prods from 表名 group by id WITH ROLLUP

过滤分组

HAVING子句：
    可以和WHERE子句一样使用
    区别：
        where过滤行，having过滤分组
        where在分组前过滤，having在分组后过滤
  1. 过滤组内超过两项数据的行：select id,COUNT(*) from 表名 group by id having COUNT(*) > 2

分组和排序

ORDER BY GROUP BY
排序产生的输出
任意列都可以使用（甚至非选择的列）
不一定需要		 分组行，但输出可能不是分组的顺序
只能使用选择列或表达式列，而且必须使用每个列表达式
如果与聚集函数一起使用列，则必须使用

使用子查询

1
2
3
4
5
-- 子查询从内向外处理
select id from orders 
where order_num in (select order_num 
                    from orderitems 
                    where prod_id = 'TNT2')

联结表

1
2
3
4
5
-- 创建联结，无连接条件（笛卡尔积）
select vend_name,prod_name,prod_price
from vendors,products
where vendors.vend_id = products.vend_id
ORDER BY vend_name,prod_name

内联接

1
2
3
4
5
-- 内联接
select vend_name,prod_name,prod_price
from vendors INNER JOIN products
where vendors.vend_id = products.vend_id
ORDER BY vend_name,prod_name

创建高级联接

使用表别名

1
2
3
4
5
select cust_name,cust_contact
from customers AS c,orders AS o,orderitems AS oi
where c.cust_id = o.cust_id
    and oi.order_num = o.order_num
    and prod_id = 'TNT2'

表别名只在查询中使用。与列别名不同，表别名不会返回客户机

使用不同类型的联接

自联接

1
2
3
4
select p1.prod_id,p1_prod_name 
from products AS p1,products AS p2
where p1.vend_id = p2.vend_id
    and p2.prod_id = 'DTNTR'