MySQL基础部分总结

MySQL1、选择数据库use dbnameshow databases;2、数据表show tablesmysql> show columns from customers;mysql> desc customers;3、show 语句show statusshow create databasesshow create tableshow grants4、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,44-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 >limitmysql> 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 两者之间 andand 的优先级大于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 NULLmysql> select cust_id from customers where cust_email is null;重点：空值检查空值既是：NULLMySQL中判断是否是空值的子句是： IS NULLexample: mysql> select cust_id FROM customers where cust_email IS NULL; +———+| cust_id |+———+| 10002 || 10005 |+———+6、where 数据过滤（logical operator）逻辑操作符：and - ormysql> 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)默认不区分大小写，需要区分大小写binarymysql> 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 quantityitem_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() 返回某列值之和DISTINCTmysql> 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(quantityitem_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)顺序selectfromwheregroup byhavingorder bylimit12 子查询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必知必会》