MySQL基础部分总结

摘要：在数据分组前进行过滤，在数据分组后进行过滤顺序子查询连接表笛卡儿积如果将两个表同时作为数据源后的表名，不加任何的匹配条件，那么产生的结果集就是一个迪卡尔积。迪卡尔积的结果没有意义，但是迪卡尔积是联合查询连接查询的基础。

use dbname

show databases;

show tables

mysql> show columns from customers;

mysql> desc customers;

show status

show create databases

show create table

show grants

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

//单个字段排序
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;

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;

相关操作符：

= 等于

<> 不等于

!= 不等于

< 小于

> 大于

>= 大于或者等于

<= 小于或等于

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;

重点：空值检查
空值既是：NULL
MySQL中判断是否是空值的子句是：    IS NULL

example:

 mysql> select cust_id FROM customers  where cust_email IS NULL;    
+---------+
| cust_id |
+---------+
|   10002 |
|   10005 |
+---------+

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

mysql> select prod_id,prod_price,prod_name from products where vend_id in (1002,1003) order by prod_name;

mysql> select prod_id,prod_price,prod_name from products where vend_id not in (1002,1003) order by prod_name;

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%";

mysql> select prod_id,prod_price,prod_name from products where prod_name  LIKE "_ ton anvil";

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

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;

trim、ltrim、rtrim 去掉空值

Upper 转为大写

mysql> select vend_name,upper(vend_name) as ven_name_upcase from vendors order by vend_name;

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)

Abs() 返回一个数的绝对值

Cos() 返回一个角度的余弦

Exp() 返回一个数的指数值

Mod() 返回除操作的余数

Pi() 返回圆周率

Rand() 返回一个随机数

Sin() 返回一个角度的正弦

Sqrt() 返回一个数的平方根

Tan() 返回一个角度的正切

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

mysql>  select cust_id from orders where order_num in  (select order_num from orderitems where prod_id ="TNT2");
+---------+
| cust_id |
+---------+
|   10001 |
|   10004 |
+---------+

如果将两个表同时作为数据源(from后的表名)，不加任何的匹配条件，那么产生的结果集就是一个迪卡尔积。
迪卡尔积的结果没有意义，但是迪卡尔积是联合查询、连接查询的基础。

使用表A中的1条记录去表B中连接所有的记录，就是笛卡尔积

select 字段列表 from 表A 【inner】 join 表B ，匹配到的成功的记录

左连接保留左边的所有，右边匹配到的部分

在进行连接时，如果进行连接的两个字段的名子相同，则可以使用using using("cid")

当前笔记出自 《MySQL必知必会》