MySQL基礎部分總結
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 byprod_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 fromproducts where prod_price = 2.50; mysql> select prod_price,prod_name fromproducts where prod_pricebetween 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 customerswhere 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_nameLIKE 'jet%'; mysql> select prod_id,prod_price,prod_name from products where prod_nameLIKE '%anv%';
7-2、_
mysql> select prod_id,prod_price,prod_name from products where prod_nameLIKE '_ ton anvil';
8、正則表示式
like是匹配全部,REGEXP可以匹配全部和部分
mysql> select prod_namefrom 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_nameREGEXP '1000'; +--------------+ | prod_name| +--------------+ | JetPack 1000 | +--------------+ 1 row in set (0.00 sec)
預設不區分大小寫,需要區分大小寫binary
mysql> select prod_name from products where prod_nameREGEXP 'jetpack .000'; mysql> select prod_name from products where prod_nameREGEXP 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_titlefrom 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
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>=10group 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必知必會》