資料庫MySQ語法的基本使用

MySQL的基本陳述句
– 創建資料庫
create database 資料庫名;
– 創建表
create table 表名(
列名 型別 約束,
列名2 型別 約束
);
– 型別：char、varchar、int、double、date 等
– 約束：primary key、unique、not null、foreign key等

– 增
insert into 表名(列名,…) values(‘值’,…);
– 改
update 表名 set 列名=值,… where 條件;
– 刪
delete from 表名 where 條件;

– 查
select
distinct * | 列名,…
from 表名
where 條件
group by 分組
having 分組條件
order by 列名 asc|desc
limit 開始索引,每頁顯示個數

單表的基本查詢

2.1 排序

– order by 排序關鍵字
– asc 升序、desc 降序
select * from 表名 order by 排序列 asc | desc;

– 練習
– 1.使用價格排序(降序)
select * from product order by price desc;

– 2.在價格排序(降序)的基礎上，以分類排序(降序)
select * from product order by price desc, category_id desc;

– 3.顯示商品的價格(去重復)，并排序(降序)
select distinct price from product order by price desc;

2.2 聚合

• 聚合函式：SQL基本函式，聚合函式對一組值執行計算，并回傳單個值，也被稱為組函式，

• 常見聚合函式：

  • 除 COUNT 以外，聚合函式忽略空值，如果COUNT函式的應用物件是一個確定列名，并且該列存在空值，此時COUNT仍會忽略空值，

  – 統計個數：count
  – 最大值：max
  – 最小值：min
  – 求和：sum
  – 平均值：avg

– 練習
– 聚合函式
– 1 統計個數
– count(列名 | * | 數字)
select count(category_id) from product; # 結果：12，忽略了空值
select count(*) from product; # 結果：13
select count(1) from product; # 結果：13

– 2 價格最大值
select max(price) from product;

– 3 價格最小值
select min(price) from product;

– 4 價格求和
select sum(price) from product;

– 5 價格平均值
select avg(price) from product;

2.3 分組

select 分組列 | 聚合函式 from 表名 group by 分組列 having 分組條件;

– 練習
– 分組
– #1 統計各個分類商品的個數
select category_id , count(1) from product group by category_id

– #2 統計各個分類商品的個數,且只顯示個數大于1的資訊
select category_id , count(1) from product group by category_id having count(1) > 1

select category_id , count(1) as c from product group by category_id having c > 1

2.4 分頁

– limit 開始索引,每頁顯示個數
---- 開始索引，從0開始，
– 第一頁，每頁3條
select * from product limit 0,3;
– 第二頁，每頁3條
select * from product limit 3,3;
– 第三頁，每頁3條
select * from product limit 6,3;
– 第pageNum頁，每頁pageSize條
select * from product limit (pageNum - 1) * pageSize, pageSize;

多表的基本連查

3.1 表與表之間的關系

• 一對多
• 多對多
• 一對一

3.2 一對多

• 一對多關系：通過主外鍵關系，形成一對多關系，

  • 一表：又稱為主表，主表需要提供主鍵，
  • 多表：又稱為從表，從表需要提供外鍵，

• 實體：分類 + 商品

  • 一個分類，擁有多個商品，稱為一對多關系，

  [外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-Nm3d1ZDR-1600502227433)(assets/image-20200910113002484.png)]

• 需要通過外鍵約束，描述主外鍵關系

alter table 從表 add constraint [外鍵名稱] foreign key (外鍵) references 主表 (主鍵)
– 添加外鍵約束時，如果填寫“外鍵名稱”，是為了洗掉外鍵約束

create database en002;
use en002;
– 一對多關系
– 主表（分類表）
create table tb_category(
cid int primary key ,
cname varchar(50)
);

– 從表（商品表）-- 注意 外鍵型別 必須 與主表主鍵型別一致
create table tb_product(
pid int primary key,
pname varchar(50),
category_id int
);

– 主外鍵關系
alter table tb_product add constraint foreign key (category_id) references tb_category (cid);

3.3 多對多

• 多對多：提供一個中間表，將一個多對多拆分成了2個一對多，

  • 中間表要求：至少提供2個外鍵列

• 實體：訂單 + 商品

  [外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-7d6l2zz6-1600502227444)(assets/image-20200910114829948.png)]

• 練習：

– 多對多
– 1 主表（訂單表）
create table tb_order(
oid int primary key,
price double
);

– 2 主表（商品表）[已有]

– 3 中間表
create table tb_order_product(
order_id int,
product_id int
);

– 4 中間表 和 訂單表 主外鍵關系
alter table tb_order_product add constraint foreign key (order_id) references tb_order (oid);

– 5 中間表 和 商品表 主外鍵關系
alter table tb_order_product add constraint foreign key (product_id) references tb_product (pid);