MySQL
1、选择数据库
use dbname
show databases;
2、数据表
show tables
mysql> show columns from customers;
mysql> desc customers;
3、show 语句
show status
show create databases
show create table
show grants
4、select 检索
4.1.1 版本后不再区分大小写，但是为了容易阅读和调试，建议还是使用。
mysql> select cust_name from customers;
mysql> select cust_name cust_status from customers;
mysql> select distinct vend_id from products;
mysql> select prod_name from products limit 5;
mysql> select prod_name from products limit 5,5;
// 分页问题
从行 0 开始计数，limit5,5 是从第 5 行开始（不包括第五行），取 5 行，结果是：6:10 行
因为这个很容易引起误解，所以 MySQL5 开始支持另一个语法：limit 4 offset 3，意思是从行 3 开始取 4 行，等同于 limit 3,4
4-1、排序数据
// 单个字段排序
mysql> select prod_name from products order by prod_name;
// 多个字段排序，如果第一个字段全部唯一则第二个字段就不会生效
mysql> select prod_id,prod_price,prod_name from products order by prod_price ,prod_name;
4-2、指定排序方向
desc 降序
asc 升序 - 默认
注意顺序，from>ordrr by >limit
mysql> select prod_id,prod_price,prod_name from products order by prod_price desc;
mysql> select prod_id,prod_price,prod_name from products order by prod_price asc;
mysql> select prod_price from products order by prod_price desc limit 1;
5、where 条件
相关操作符：
= 等于
<> 不等于
!= 不等于
< 小于
> 大于
>= 大于或者等于
<= 小于或等于
between 两者之间 and
and 的优先级大于 or，需要处理 or，则需要括号
mysql> select prod_price,prod_name from products where prod_price = 2.50;
mysql> select prod_price,prod_name from products where prod_price between 5 and 10;
// IS NULL
mysql> select cust_id from customers where cust_email is null;
重点：空值检查空值既是：NULLMySQL 中判断是否是空值的子句是：IS NULL
example:
mysql> select cust_id FROM customers where cust_email IS NULL;
+———+
| cust_id |
+———+
| 10002 |
| 10005 |
+———+
6、where 数据过滤
（logical operator）逻辑操作符：and – or
mysql> select prod_id,prod_price,prod_name from products where vend_id = 1003 and prod_price<= 10;
mysql> select prod_id,prod_price,prod_name from products where vend_id = 1003 or vend_id = 1002;
运算符优先级问题：下列 SQL 中实际先运行 vend_id = 1002 and prod_price >= 10;, 再运行 vend_id = 1003. 因为 and 的优先级大于 or，如果要按理想执行，加括号！
mysql> select prod_id,prod_price,prod_name from products where vend_id = 1003 or vend_id = 1002 and prod_price >= 10;
mysql> select prod_id,prod_price,prod_name from products where (vend_id = 1003 or vend_id = 1002)and prod_price >= 10;
6-1、in 操作符 (not in)
mysql> select prod_id,prod_price,prod_name from products where vend_id in (1002,1003) order by prod_name;
6-2、or 操作符
mysql> select prod_id,prod_price,prod_name from products where vend_id not in (1002,1003) order by prod_name;
7、用通配符过滤
like 和 _ 的区别是后者只能匹配一个字符
7-1、like
** 注意 NULL 虽然似乎 % 通配符可以匹配任何东西，但有一个例外，即 NULL。即使是 WHERE prod_name LIKE ‘%’ 也不能匹配用值 NULL 作为产品名的行。**
mysql> select prod_id,prod_price,prod_name from products where prod_name LIKE ‘jet%’;
mysql> select prod_id,prod_price,prod_name from products where prod_name LIKE ‘%anv%’;
7-2、_
mysql> select prod_id,prod_price,prod_name from products where prod_name LIKE ‘_ ton anvil’;
8、正则表达式
like 是匹配全部，REGEXP 可以匹配全部和部分
mysql> select prod_name from products where prod_name =’JetPack 1000′;
+————–+
| prod_name |
+————–+
| JetPack 1000 |
+————–+
1 row in set (0.00 sec)
mysql> select prod_name from products where prod_name REGEXP ‘1000’;
+————–+
| prod_name |
+————–+
| JetPack 1000 |
+————–+
1 row in set (0.00 sec)
默认不区分大小写，需要区分大小写 binary
mysql> select prod_name from products where prod_name REGEXP ‘jetpack .000’;
mysql> select prod_name from products where prod_name REGEXP binary ‘JetPack .000’;
10、计算字段
concat 合并 讲两个字段合并成一个新的字段
mysql> select concat (vend_name , ‘C’,vend_country,’)’) from vendors order by vend_name;
+——————————————-+
| concat (vend_name , ‘C’,vend_country,’)’) |
+——————————————-+
| ACMECUSA) |
| Anvils R UsCUSA) |
| Furball Inc.CUSA) |
| Jet SetCEngland) |
| Jouets Et OursCFrance) |
| LT SuppliesCUSA) |
+——————————————-+
6 rows in set (0.00 sec)
rtrim (ltrim ,trim) 去掉空格
mysql> select concat (rtrim(vend_name) , ‘C’,vend_country,’)’) from vendors order by vend_name;
as 别名
mysql> select concat (rtrim(vend_name) , ‘(‘,rtrim(vend_country),’)’) as vend_title from vendors order by vend__name;
计算
+、-、*、\
mysql> select quantity*item_price as expand_price from orderitems where order_num =20005;
11、函数
trim、ltrim、rtrim 去掉空值
Upper 转为大写
mysql> select vend_name,upper(vend_name) as ven_name_upcase from vendors order by vend_name;
11-2 时间函数
AddDate() 增加一个日期 ( 天、周等)
AddTime() 增加一个时间 ( 时、分等)
CurDate() 返回当前日期
CurTime() 返回当前时间
==Date() 返回日期时间的日期部分 ==
DateDiff() 计算两个日期之差
Date_Add() 高度灵活的日期运算函数
Date_Format() 返回一个格式化的日期或时间串
Day() 返回一个日期的天数部分
DayOfWeek() 对于一个日期，返回对应的星期几
Hour() 返回一个时间的小时部分
Minute() 返回一个时间的分钟部分
Month() 返回一个日期的月份部分
Now() 返回当前日期和时间
Second() 返回一个时间的秒部分
Time() 返回一个日期时间的时间部分
Year() 返回一个日期的年份部分
取 9 月某一天的数据
mysql> select cust_id,order_num from orders where Date(order_date) = ‘2005-09-01’;
+———+———–+
| cust_id | order_num |
+———+———–+
| 10001 | 20005 |
+———+———–+
1 row in set (0.00 sec)
取 9 月整个月的订单
mysql> select cust_id,order_num from orders where Date(order_date) between ‘2005-09-01’ and ‘2005-09-30′;
+———+———–+
| cust_id | order_num |
+———+———–+
| 10001 | 20005 |
| 10003 | 20006 |
| 10004 | 20007 |
+———+———–+
3 rows in set (0.00 sec)
mysql> select cust_id,order_num from orders where Year(order_date) and month(order_date) = 9;
+———+———–+
| cust_id | order_num |
+———+———–+
| 10001 | 20005 |
| 10003 | 20006 |
| 10004 | 20007 |
+———+———–+
3 rows in set (0.00 sec)
11-4 数值处理函数
Abs() 返回一个数的绝对值
Cos() 返回一个角度的余弦
Exp() 返回一个数的指数值
Mod() 返回除操作的余数
Pi() 返回圆周率
Rand() 返回一个随机数
Sin() 返回一个角度的正弦
Sqrt() 返回一个数的平方根
Tan() 返回一个角度的正切
11-5 聚集函数
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和
DISTINCT
mysql> select avg(prod_price) as avg_price from products;
分组数据
GROUP BY 子句和 HAVING 子句
mysql> select vend_id,count(*) as num_prods from products group by vend_id;
+———+———–+
| vend_id | num_prods |
+———+———–+
| 1001 | 3 |
| 1002 | 2 |
| 1003 | 7 |
| 1005 | 2 |
+———+———–+
4 rows in set (0.00 sec)
mysql> select vend_id,count(*) as num_prods from products group by vend_id with rollup;
+———+———–+
| vend_id | num_prods |
+———+———–+
| 1001 | 3 |
| 1002 | 2 |
| 1003 | 7 |
| 1005 | 2 |
| NULL | 14 |
+———+———–+
5 rows in set (0.00 sec)
having 唯一的差别是 WHERE 过滤行，而 HAVING 过滤分组。WHERE 在数据 分组前进行过滤，HAVING 在数据分组后进行过滤
mysql> select vend_id,count(*) as num_prods from products group by vend_id having count(*)>=2;
+———+———–+
| vend_id | num_prods |
+———+———–+
| 1001 | 3 |
| 1002 | 2 |
| 1003 | 7 |
| 1005 | 2 |
+———+———–+
4 rows in set (0.00 sec)
mysql> select vend_id,count(*) as num_prods from products where prod_price>=10 group by vend_id having count(*)>=2;
+———+———–+
| vend_id | num_prods |
+———+———–+
| 1003 | 4 |
| 1005 | 2 |
+———+———–+
2 rows in set (0.00 sec)
mysql> select order_num ,sum(quantity*item_price) as ordertotal from orderitems
-> group by order_num
-> having sum(quantity*item_price) >= 50
-> order by ordertotal;
+———–+————+
| order_num | ordertotal |
+———–+————+
| 20006 | 55.00 |
| 20008 | 125.00 |
| 20005 | 149.87 |
| 20007 | 1000.00 |
+———–+————+
4 rows in set (0.00 sec)
顺序
select
from
where
group by
having
order by
limit
12 子查询
mysql> select cust_id from orders where order_num in (select order_num from orderitems where prod_id =’TNT2’);
+———+
| cust_id |
+———+
| 10001 |
| 10004 |
+———+
15 连接表
笛卡儿积 (cartesian product)
如果将两个表同时作为数据源 (from 后的表名)，不加任何的匹配条件，那么产生的结果集就是一个迪卡尔积。迪卡尔积的结果没有意义，但是迪卡尔积是联合查询、连接查询的基础。
1. 交叉连接 cross join
使用表 A 中的 1 条记录去表 B 中连接所有的记录，就是笛卡尔积
2. 内连接
select 字段列表 from 表 A【inner】join 表 B，匹配到的成功的记录
3. 外连接 分为左连接和右连接，
左连接保留左边的所有，右边匹配到的部分
4. using 关键字
在进行连接时，如果进行连接的两个字段的名子相同，则可以使用 using using(‘cid’)
当前笔记出自《MySQL 必知必会》