MySQL基础部分总结

MySQL

1、选择数据库

  1. use dbname
  2. show databases;

2、数据表

  1. show tables
  2. mysql> show columns from customers;
  3. mysql> desc customers;

3、show 语句

  1. show status
  2. show create databases
  3. show create table
  4. 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 条件

相关操作符:

  1. = 等于
  2. <> 不等于
  3. != 不等于
  4. < 小于
  5. > 大于
  6. >= 大于或者等于
  7. <= 小于或等于
  8. 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 |
+---------+

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、计算字段

  1. 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)
  1. rtrim (ltrim ,trim) 去掉空格
mysql> select concat (rtrim(vend_name) , 'C',vend_country,')') from vendors order by vend_name;
  1. as 别名
mysql> select concat (rtrim(vend_name) , '(',rtrim(vend_country),')') as vend_title   from vendors order by vend__name;
  1. 计算

+、-、* 、\

mysql> select quantity*item_price as expand_price from orderitems where order_num =20005;

11、函数

  1. trim、ltrim、rtrim 去掉空值
  2. Upper 转为大写
mysql> select vend_name,upper(vend_name) as ven_name_upcase from vendors order by vend_name;

11-2 时间函数

  1. AddDate() 增加一个日期(天、周等)
  2. AddTime() 增加一个时间(时、分等)
  3. CurDate() 返回当前日期
  4. CurTime() 返回当前时间
  5. ==Date() 返回日期时间的日期部分==
  6. DateDiff() 计算两个日期之差
  7. Date_Add() 高度灵活的日期运算函数
  8. Date_Format() 返回一个格式化的日期或时间串
  9. Day() 返回一个日期的天数部分
  10. DayOfWeek() 对于一个日期,返回对应的星期几
  11. Hour() 返回一个时间的小时部分
  12. Minute() 返回一个时间的分钟部分
  13. Month() 返回一个日期的月份部分
  14. Now() 返回当前日期和时间
  15. Second() 返回一个时间的秒部分
  16. Time() 返回一个日期时间的时间部分
  17. 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 数值处理函数

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

11-5 聚集函数

  1. AVG() 返回某列的平均值
  2. COUNT() 返回某列的行数
  3. MAX() 返回某列的最大值
  4. MIN() 返回某列的最小值
  5. SUM() 返回某列值之和
  6. 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必知必会》

相关推荐