concat.png

順序問題：
from --> where --> group by --> having --> order by --> limit

• 顯示所有的數(shù)據(jù)庫

show databases;

• 使用指定的數(shù)據(jù)庫

use |數(shù)據(jù)庫名|;

• 進(jìn)入到指定的數(shù)據(jù)庫，可以顯示該數(shù)據(jù)庫中所有的表

show tables;

• 顯示某個表中的所有字段信息

show columns from |表名|;
or
describe |表名|;

• 顯示創(chuàng)建特定數(shù)據(jù)庫或者表的MySQL語句

show create database |數(shù)據(jù)庫名|;
show create table |表名|;

• 顯示授予用戶的權(quán)限

show grants

• 導(dǎo)入sql文件

source |路徑|

• 取消重復(fù)行:如果指定多個列，則只有當(dāng)多個列組合起來的這個組合不同時，才會起作用，也就是說distinct針對的是全部你選擇的列且必須放到列名的最前面

select distinct |列名| from |表名|;

• 限制輸出limit：mysql默認(rèn)是第0行；帶一個參數(shù)：最多輸出幾行；帶兩個參數(shù)：從第一個參數(shù)開始，最多輸出第二參數(shù)行

select prod_name from products limit 5; "最多輸出5行
select prod_name from products limit 5,5;  "從行5開始的5行

• 排序數(shù)據(jù):order by :如果按照多列進(jìn)行排序，則執(zhí)行順序：先按照第一個列排，如果第一列相同，則按第二列排;如果要升序排列某個字段，則在order by 后面的列名后加asc（但是默認(rèn)的排序就是升序的，所以沒啥用），如果要降序排列某個字段，則在order by 后面的列名后加desc

select |列名|,|列名|... from |表名| order by |列名|,|列名|... ;

• order by 和 limit組合可以選中最大的或者最小的一行

select prod_price from products order by prod_price desc limit 1; "選中最高價格的一行

過濾數(shù)據(jù)

• where子句操作符

• 連接個where子句

select prod_name,prod_price 
from products
where vend_id = 1002 or vend_id =1003 and prod_price >=10;
"供應(yīng)商1003制造的價格在10元之上的商品或者供應(yīng)商編號是1002的所有商品 即where vend_id = 1002 or （vend_id =1003 and prod_price >=10）;

"供應(yīng)商1002或1003制造的且價格都在10元之上的商品
select prod_name,prod_price 
from products
where (vend_id = 1002 or vend_id =1003) and prod_price >=10;

• 通配符過濾：不到萬不得已不要使用

  • 通配符：比其他搜索要慢，放在過濾的最開始，搜索起來是最慢的

  通配符	說明
  %	任意字符出現(xiàn)任意次數(shù)，但不能匹配NULL
  _	單個字符

select prod_id,prod_name from products where prod_name like '_ ton anvil';

select prod_id,prod_name from products where prod_name like '% ton anvil';
"會匹配到這樣一條數(shù)據(jù)： .5 ton anvil

• 正則過濾 regexp

+ like和regexp的最大差別在于：like匹配整個列，如果匹配的文本在列值中出現(xiàn)，like將不會找到它，也就不會返回相應(yīng)的行，而regexp則會

```sql
select prod_name from products where  prod_name like '1000' order by prod_name;"沒有數(shù)據(jù)

select prod_name from products where  prod_name regexp '1000' order by prod_name;"會出現(xiàn)1000的一條數(shù)據(jù)
```

+ 匹配區(qū)分大小寫，mysql中的正則是不匹配大小寫的，但是可以指定區(qū)分大小寫，eg: where prod_name regexp binary 'JetPack .000';

計算字段

• 拼接字段 Concat()

select concat(vend_name,'(',vend_country,')') 
from vendors
order by vend_name;

concat.png

• 另一個常用：對檢索出來的數(shù)據(jù)進(jìn)行算術(shù)計算

select prod_id,quantity,item_price,quantity*item_price as expanded_price
from orderitems
where order_num = 2005;

計算字段.png

處理數(shù)據(jù)

select avg(prod_price) as avg_price
from products 
where vend_id =1003;

avg2.png

select avg(distinct prod_price) as avg_price 
from products
where vend_id = 1003;

avg1.png

• group by 子句

select vend_id,count(*) as num_prods 
from products
group by vend_id;
"group by 子句指示MySQL按vend_id進(jìn)行排序并分組數(shù)據(jù)
“規(guī)定：group by 中出現(xiàn)的列 必須在select中出現(xiàn)
“如果分組列中出現(xiàn)null值，則null作為一個分組返回

groupby.png

• with rollup

select vend_id,count(*) as num_prods 
from products
group by vend_id with rollup;

withrollup.png

• 過濾分組

select cust_id,count(*) as orders
from orders
group by cust_id
having count(*) >=2;
"對于分組之后的數(shù)據(jù)再進(jìn)行過濾,而where在分組之前進(jìn)行過濾

having.png

數(shù)據(jù)增刪改

• 插入多條數(shù)據(jù)

insert into customers(
    cust_name,cust_address,cust_city,
    cust_state,cust_zip,cust_country)
values(
    'Pep E. LaPew',
    '100 main street',
    'Los Angeles',
    'CA',
    '90046',
    'USA'
    ),
    (
    'Pep2 E. LaPew',
    '1002 main street',
    'Los2 Angeles',
    'CA2',
    '900462',
    'USA'
    );
"這樣的插入多條數(shù)據(jù)比多條insert效率要高

• 插入檢索出來的數(shù)據(jù):復(fù)制表的數(shù)據(jù)到另一個表中

insert into customers(
    cust_name,cust_address,cust_city,
    cust_state,cust_zip,cust_country
    )
select 
    cust_name,cust_address,cust_city,
    cust_state,cust_zip,cust_country
from custnew;
    

• 更新數(shù)據(jù)

update customers 
set cust_email = 'example@qq.com',
set cust_name = 'The Fudds',
set cust_xxx = null "設(shè)置某個值為空就是刪除該列的值
where cust_id = 10005; “

• 刪除數(shù)據(jù)

delete from customers
where cust_id = 10006;"如果沒有where語句 ，則刪除表中的所有行，但沒有刪除表

“更快的刪除表中所有行 實際上是刪除了原來的表并重新創(chuàng)建一個表
truncate table 表名;

數(shù)據(jù)引擎

使用ENGINE=引擎名 創(chuàng)建表的時候

更新表

• 添加列

alter table vendors
add vend_phone char(20);

• 刪除列

alter table vendors
drop column vend_phone;

• 常用：定義外健

alter table orderitems
add constrain fk_orderitems_orders
foreign key(order_num) reference orders (order_num);

刪除表

• 刪除表

drop table customers2;

• 重命名表

rename table customers2 to customers,
                backup_vendors to vendors,
                bakcup_products to products;

視圖

視圖的作用：
舉個例子：要用查詢來檢索訂購某個產(chǎn)品的客戶，任何需要這個數(shù)據(jù)的人都必須理解相關(guān)表的結(jié)構(gòu)，并且知道如何創(chuàng)建查詢和對表進(jìn)行聯(lián)結(jié)。
為了檢索其他產(chǎn)品的相同數(shù)據(jù)，必須修改最后的where子句。
那么，現(xiàn)在假如可以把整個查詢包裝成一個名為productcustomer的虛擬表，則可以如下輕松的檢索出相同的數(shù)據(jù)

• 作用：

  • 重用sql語句
  • 簡化復(fù)雜的sql操作，編寫查詢后，可以方便的重用它而不必知道它的基本查詢細(xì)節(jié)
  • 使用表的組成部分而不是整個表
  • 保護數(shù)據(jù)，可以給用戶授予表的特定部分的訪問權(quán)限而不是整個表的訪問權(quán)限
  • 更改數(shù)據(jù)格式和表示。視圖可以返回與底層表的表示和格式不同的數(shù)據(jù)

• 視圖的限制

view1.png

--把table 換成view

• 視圖更新的限制：如果視圖定義中有以下操作：
  • 分組
  • 聯(lián)結(jié)
  • 子查詢
  • 并
  • 聚集函數(shù)
  • distinct
  • 導(dǎo)出（計算）列