MySQL-DDL&DML&限制

目錄

• 一、DDL(create ,drop,alter)
• • `create`
  • • 常見類型
    • char和varchar怎麼選擇？
    • 表的複制
  • `drop`
  • `alter`
• 二、DML(insert,delete,update)
• • `insert`
  • • 一次插入多行資料
    • 将查詢結果插入到一張表中
  • `delete` & `truncate`
  • `update`
• 三、限制
• • 1. 非空限制 (`not null`)
  • 2. 唯一性限制(`unique`)
  • 3. 主鍵限制(`primary key`)
  • • 添加主鍵
    • • 列級限制添加主鍵
      • 使用表級限制方式定義主鍵：
    • 主鍵相關的術語？
    • 主鍵有什麼作用？
    • 主鍵的分類？
    • mysql提供主鍵值自增`auto_increment`：（非常重要。）
  • 4. 外鍵限制(`foreign key`)：重難點
  • • 順序要求：
    • 建立外鍵限制

一、DDL(create ,drop,alter)

create

create table 表名(
	字段名1 資料類型,
	字段名2 資料類型,
	字段名3 資料類型,
	....
);
           

• 表名在資料庫當中一般建議以：t_或者tbl_開始。

常見類型

常用類型	描述
int	整數型(java中的int)
bigint	長整型(java中的long)
float	浮點型(java中的float double)
char	定長字元串(String)
varchar	可變長字元串(StringBuffer/StringBuilder)
date	日期類型 （對應Java中的java.sql.Date類型。1986-10-23
BLOB	二進制大對象（存儲圖檔、視訊等流媒體資訊） Binary Large OBject （對應java中的Object）
CLOB	字元大對象（存儲較大文本，比如，可以存儲4G的字元串。） Character Large OBject（對應java中的Object）

char和varchar怎麼選擇？

• 當某個字段中的資料長度不發生改變的時候，是定長的，例如：性别、生日等都是采用char。
• 當一個字段的資料長度不确定，例如：簡介、姓名等都是采用varchar。

表的複制

create table 表名 as select語句;
create table dept1 as select * from dept;
           

• 将查詢結果當做表建立出來。

drop

drop table 表名; // 這個通用。
drop table if exists 表名; // oracle不支援這種寫法。
           

alter

• 實際開發中很少發生。
• 使用工具完成較為穩妥，也足夠應用了。

二、DML(insert,delete,update)

insert

• 字段的數量和值的數量相同，并且資料類型要對應相同。
• 是

  values

• 字段可以省略不寫，但是後面的value對數量和順序都有要求。

一次插入多行資料

insert into t_student
	(no,name,sex,classno,birth) 
values
	(3,'rose','1','gaosi2ban','1952-12-14'),
	(4,'laotie','1','gaosi2ban','1955-12-14');
           

• 逗号隔開，括号括起來。

将查詢結果插入到一張表中

• 查詢出來的字段要能對上。

delete

&

truncate

delete from 表名 where 條件;

# 怎麼删除大表中的資料？（重點）
truncate table 表名; // 表被截斷，不可復原。永久丢失。
           

• delete

  删除效率較低，但較為安全。
• 用

  truncate

  再三小心，表被截斷，不可復原，永久丢失。并且自增列歸零。

update

update 表名 set 字段名1=值1,字段名2=值2... where 條件;
update dept1 set loc = 'SHANGHAI', dname = 'RENSHIBU' where deptno = 10;
           

• set

  多個字段之間是逗号，不是and。
• 不加

  where

  更新所有記錄。

三、限制

1. 非空限制(

   not null

   )：限制的字段不能為NULL
2. 唯一限制(

   unique

   )：限制的字段不能重複，可以為空
3. 主鍵限制(

   primary key

   )：限制的字段既不能為NULL，也不能重複（簡稱PK）
4. 外鍵限制(

   foreign key

   )：…（簡稱FK）
5. 檢查限制(

   check

   )：注意Oracle資料庫有check限制，但是mysql沒有，目前mysql不支援該限制。

1. 非空限制 (

not null

)

drop table if exists t_user;
create table t_user(
	id int,
	username varchar(255) not null,
	password varchar(255)
);
insert into t_user(id,password) values(1,'123');
ERROR 1364 (HY000): Field 'username' doesn't have a default value
           

• 注意：not null限制隻有列級限制。沒有表級限制。

2. 唯一性限制(

unique

)

• 唯一限制修飾的字段具有唯一性，不能重複。但可以為NULL。
• 案例：給某一列添加

  unique

  【列級限制】

drop table if exists t_user;
	create table t_user(
		id int,
		username varchar(255) unique  // 列級限制
	);
	insert into t_user values(1,'zhangsan');
	insert into t_user values(2,'zhangsan');
	ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'username'
           

• 案例：給兩個列或者多個列添加

  unique

  【表級限制】

drop table if exists t_user;
	create table t_user(
		id int, 
		usercode varchar(255),
		username varchar(255),
		unique(usercode,username) // 多個字段聯合起來添加1個限制unique 【表級限制】
	);

	insert into t_user values(1,'111','zs');
	insert into t_user values(2,'111','ls');
	insert into t_user values(3,'222','zs');
	select * from t_user;
	insert into t_user values(4,'111','zs');
	ERROR 1062 (23000): Duplicate entry '111-zs' for key 'usercode'

	drop table if exists t_user;
	create table t_user(
		id int, 
		usercode varchar(255) unique,
		username varchar(255) unique
	);
	insert into t_user values(1,'111','zs');
	insert into t_user values(2,'111','ls');
	ERROR 1062 (23000): Duplicate entry '111' for key 'usercode'
           

3. 主鍵限制(

primary key

)

• 一張表的主鍵限制隻能有1個。（必須記住）

添加主鍵

列級限制添加主鍵

create table t_user(
	id int primary key,  // 列級限制
	username varchar(255),
	email varchar(255)
);
           

使用表級限制方式定義主鍵：

create table t_user(
	id int,
	username varchar(255),
	primary key(id)
);

# 以下内容是示範以下複合主鍵，不需要掌握：
create table t_user(
	id int,
	username varchar(255),
	password varchar(255),
	primary key(id,username) # 複合主鍵
);
insert .......
           

主鍵相關的術語？

主鍵限制 : primary key

主鍵字段 : id字段添加primary key之後，id叫做主鍵字段

主鍵值 : id字段中的每一個值都是主鍵值。

主鍵有什麼作用？

• 主鍵的作用：主鍵值是這行記錄在這張表當中的唯一辨別。（就像一個人的身份證号碼一樣。）
• 索引

主鍵的分類？

根據主鍵字段的字段數量來劃分：

• 單一主鍵（推薦的，常用的。）
• 複合主鍵(多個字段聯合起來添加一個主鍵限制)（複合主鍵不建議使用，因為複合主鍵違背三範式中的第二範式。）

根據主鍵性質來劃分：

• 自然主鍵：主鍵值最好就是一個和業務沒有任何關系的自然數。（這種方式是推薦的）
• 業務主鍵：主鍵值和系統的業務挂鈎，例如：拿着銀行卡的卡号做主鍵。（不推薦用）

• 最好不要拿着和業務挂鈎的字段作為主鍵。因為以後的業務一旦發生改變的時候，主鍵值可能也需要随着發生變化，但有的時候沒有辦法變化，因為變化可能會導緻主鍵值重複。

mysql提供主鍵值自增

auto_increment

：（非常重要。）

drop table if exists t_user;
create table t_user(
	id int primary key auto_increment, // id字段自動維護一個自增的數字，從1開始，以1遞增。
	username varchar(255)
);
           

• 提示：Oracle當中也提供了一個自增機制，叫做：序列（sequence）對象。

4. 外鍵限制(

foreign key

)：重難點

• 外鍵值可以為NULL

• 外鍵字段引用其他表的某個字段的時候，被引用的字段必須是主鍵嗎？

  注意：被引用的字段不一定是主鍵，但至少具有unique限制。

順序要求：

1. 删除資料的時候，先删除子表，再删除父表。
2. 添加資料的時候，先添加父表，在添加子表。
3. 建立表的時候，先建立父表，再建立子表。
4. 删除表的時候，先删除子表，在删除父表。

建立外鍵限制

create table t_class(
	cno int,
	cname varchar(255),
	primary key(cno)
);

create table t_student(
	sno int,
	sname varchar(255),
	classno int,
	primary key(sno),
	foreign key(classno) references t_class(cno)
);