1 MySQL 中的約束

1.1約束類型

? 非空約束(not null)

? 唯一性約束(unique)

? 主鍵約束(primary key) PK

? 外鍵約束(foreign key) FK

? 檢查約束(目前 MySQL 不支持、Oracle 支持)

1.2 MySQL和Oracle創(chuàng)建表時(shí)添加約束

1)修改表時(shí)添加主鍵

MySQL：Alter table emp modify(id constraint pk primary key);

Oracle：Alter table emp add primary key(id);

刪除主鍵約束

Alter table emp drop primary key;

刪除主鍵約束時(shí)，如果主鍵列具備自增長能力，增需先去掉自增，再刪除主鍵。

添加自增

Alter table emp modify id int auto_increment;

去掉自增能力

Alter table emp modify id int (null);//不加null只能在unique情況下而primary key 不能加null；

2)修改表時(shí)添加非空約束；

MySQL：Alter table emp modify salary not null;

Oracle：Alter table emp modify salary float(8,2) not null;

刪除MySQL非空約束

Alter table emp modify salary float(8,2) null;

3)修改添加唯一性約束

MySQL：Alter table emp modify(last_name unique);

Oracle：Alter table emp add constraint emp_uk unique(last_name);

刪除唯一性約束

Alter table emp drop key emp_uk;

4)修改時(shí)添加外鍵

MySQL：Alter table emp add constraint e_fk foreign key(dep_id) references departments(departmetn_id);

Oracle：Alter table emp add constraint e_fk foreign key(dep_id) references departments(department_id);

刪除外鍵

第一步

Alter table emp drop foreign key e_fk;

第二步(刪除外鍵索引，索引名與外鍵別名相同)

Alter table emp drop index e_fk;

5)查看表的所有約束

Show keys form emp;

Show tables;

Show databases;

7 MySQL 中的 DML 操作

2 MySQL中的DML操作

2.1添加數(shù)據(jù)(INSERT)

選擇插入

INSERT INTO 表名(列名 1，列名 2，列名 3.....) VALUES(值 1，值 2，值 3......)

2.1.1示例

向 departments 表中添加一條數(shù)據(jù)，部門名稱為 market，工作地點(diǎn) ID 為 1。

insert into departments(department_name,location_id) values("market",1);

2.1.2完全插入

INSERT INTO 表名 VALUES(值 1，值 2，值 3......)

如果主鍵是自動(dòng)增長，需要使用 default 或者 null 或者 0 占位。

示例

向 departments 表中添加一條數(shù)據(jù)，部門名稱為 development，工作地點(diǎn) ID 為 2。使用default 占位。

insert into departments values(default,"development",2);

向 departments 表中添加一條數(shù)據(jù)，部門名稱為 human，工作地點(diǎn) ID 為 3。使用 null 占位。

insert into departments values(null,"human",3);

向 departments 表中添加一條數(shù)據(jù)，部門名稱為 teaching，工作地點(diǎn) ID 為 4。使用 0 占位。

insert into departments values(0,"teaching",4);

2.1.3 自動(dòng)增長(auto_increment)

MySQL 中的自動(dòng)增長類型要求：

? 一個(gè)表中只能有一個(gè)列為自動(dòng)增長。

? 自動(dòng)增長的列的類型必須是整數(shù)類型。

? 自動(dòng)增長只能添加到具備主鍵約束與唯一性約束的列上。

? 刪除主鍵約束或唯一性約束，如果該列擁有自動(dòng)增長能力，則需要先去掉自動(dòng)增長然后在刪除約束。

示例

創(chuàng)建一個(gè) emp2 表。包含 id 該列為主鍵，包含 name，包含 seq_num 要求該列為具備唯一性約束，該列的值自動(dòng)增長。

create table emp2(id int primary key ,name varchar(30),seq_num int unique auto_increment);

2.2 默認(rèn)值處理

在 MySQL 中可以使用 DEFAULT 為字段設(shè)定一個(gè)默認(rèn)值。如果在插入數(shù)據(jù)時(shí)并未指定該列的值，那么 MySQL 會(huì)將默認(rèn)值添加到該列中。

2.2.1 創(chuàng)建表時(shí)指定列的默認(rèn)值

示例

創(chuàng)建 emp3 表，該表包含 emp_id 主鍵且自動(dòng)增長，包含 name，包含 address 該列默認(rèn)值為”未知”。

create table emp3(emp_id int primary key auto_increment,name varchar(30), address varchar(50) default 'Unknown');

2.2.2 修改表添加列的默認(rèn)值

示例

修改 emp3 表，添加 job_id 該列默認(rèn)值為 0。

alter table emp3 add column job_id int default 0;

2.2.3 插入數(shù)據(jù)時(shí)的默認(rèn)值處理

如果在插入數(shù)據(jù)時(shí)并未指定該列的值，那么 MySQL 會(huì)將默認(rèn)值添加到該列中。如果是完全項(xiàng)插入需要使用 default 來占位。

示例

向 emp3 表中添加數(shù)據(jù)，要求 address 列與 job_id 列使用默認(rèn)值作為該列的值。

insert into emp3(name) values("admin");

insert into emp3 values(default,"oldlu",default,default);

2..3 更新數(shù)據(jù)(UPDATE)

UPDATE 表名 SET 列名=值，列名=值 WHERE 條件

2.3.1mysql 的 update 的特點(diǎn)

? 更新的表不能在 set 和 where 中用于子查詢

? update 后面可以做任意的查詢

示例一

更新 emp3 表中的 id 為 1 的數(shù)據(jù)，添加 address 為 BeiJing。

update emp3 e set e.address = "BeiJing" where emp_id = 1;

示例二

方式一：更新 emp3 中 id 為 2 的數(shù)據(jù)，將地址修改為與 id 為 1 用戶的地址相同

Oracle:update emp3 e set e.address = (select address from emp3 where emp_id = 1) where e.emp_id = 2;

MySQL: update emp3 e ,(select address from emp3 where emp_id = 1)t set e.address = t.address where e.emp_id =2;

方式二：更新 emp3 中 id 為 2 的數(shù)據(jù)，將地址修改為與 id 為 1 用戶的地址相同

update emp3 e set e.address = (select t1.address from (select emp_id, add ress from emp3)t1 where t1.emp_id = 1 ) where e.emp_id = 2;

2.4 刪除數(shù)據(jù)(DELETE)

2.4.1使用 DELETE 子句

DELETE FROM 表名 WHERE 條件

示例

刪除 emp3 表中 emp_id 為 1 的雇員信息。

delete from emp3 where emp_id = 1

2.4.2 使用 TRUNCATE 清空表

truncate table 表名

示例

刪除 emp3 表中的所有數(shù)據(jù)

truncate table emp3;

2.4.5 DELETE 與 TRUNCATE 區(qū)別

? truncate 是整體刪除(速度較快)， delete 是逐條刪除(速度較慢)；

? truncate 不寫服務(wù)器 log，delete 寫服務(wù)器 log，也就是 truncate 效率比 delete 高的原因；

? truncate 是會(huì)重置自增值，相當(dāng)于自增列會(huì)被置為初始值，又重新從 1 開始記錄，而不是接著原來的值。而 delete 刪除以后，自增值仍然會(huì)繼續(xù)累加。

3 MySQL 中的事務(wù)處理

在 MySQL 中，默認(rèn)情況下，事務(wù)是自動(dòng)提交的，也就是說，只要執(zhí)行一條 DML 語句就開啟了事物，并且提交了事務(wù)

3.1關(guān)閉 MySQL 的事務(wù)自動(dòng)提交

START TRANSACTION

DML.... COMMIT|ROLLBACK

示例

向 emp3 表中添加一條數(shù)據(jù)，要求手動(dòng)提交事務(wù)。

start transaction;

insert into emp3 values(default,"oldlu",default,default);

commit;

3.2 MySQL 中的連字符

MySQL 中并不支持||作為連字符，需要使用 concat 函數(shù)。在參數(shù)數(shù)量上與 oracle 的 concat函數(shù)有區(qū)別。

示例

查詢雇員表中的所有數(shù)據(jù)，將所有數(shù)據(jù)連接到一起，每列值中通過#分割。

select concat(employees_id,'#',last_name,'#',email,"#",salary,"#",commission_pct) from employees;

4 MySQL 中常見的單行函數(shù)

4.1大小寫控制函數(shù)

LOWER(str) 轉(zhuǎn)換大小寫混合的字符串為小寫字符串

UPPER(str) 轉(zhuǎn)換大小寫混合的字符串為大寫字符串。

4.2 字符處理

CONCAT(str1,str2,...) 將 str1、str2 等字符串連接起來

SUBSTR(str,pos,len) 從 str 的第 pos 位(范圍：1~str.length)開始，截取長度為 len的字符串

LENGTH(str) 獲取 str 的長度

INSTR(str,substr) 獲取 substr 在 str 中的位置

LPAD(str,len,padstr)/RPAD(str,len,padstr)

TRIM(str) 從 str 中刪除開頭和結(jié)尾的空格(不會(huì)處理字符串中間含有的空格)

LTRIM(str) 從 str 中刪除左側(cè)開頭的空格

RTRIM(str) 從 str 中刪除右側(cè)結(jié)尾的空格

REPLACE(str,from_str,to_str) 將 str 中的 from_str 替換為 to_str(會(huì)替換掉所有符合from_str 的字符串)

4.3 數(shù)字函數(shù)

ROUND(arg1,arg2)：四舍五入指定小數(shù)的值。

ROUND(arg1)：四舍五入保留整數(shù)。

TRUNC(arg1,arg2)：截?cái)嘀付ㄐ?shù)的值，不做四舍五入處理。

MOD(arg1,arg2)：取余。

4.4日期函數(shù)

SYSDATE() 或者 NOW() 返回當(dāng)前系統(tǒng)時(shí)間，格式為 YYYY-MM-DD hh-mm-ss

CURDATE() 返回系統(tǒng)當(dāng)前日期，不返回時(shí)間

CURTIME() 返回當(dāng)前系統(tǒng)中的時(shí)間，不返回日期

DAYOFMONTH(date) 計(jì)算日期 d 是本月的第幾天

DAYOFWEEK(date) 日期 d 今天是星期幾，1 星期日，2 星期一，以此類推

DAYOFYEAR(date) 返回指定年份的天數(shù)

DAYNAME(date) 返回 date 日期是星期幾

LAST_DAY(date) 返回 date 日期當(dāng)月的最后一天

4.5轉(zhuǎn)換函數(shù)

DATE_FORMAT(date,format) 將日期轉(zhuǎn)換成字符串(類似 oracle 中的 to_char())

STR_TO_DATE(str,format) 將字符串轉(zhuǎn)換成日期(類似 oracle 中的 to_date())

示例一

向 employees 表中添加 hire_date 列 類型為 date 類型

alter table employees add column hire_date date

示例二

向 employees 表中添加一條數(shù)據(jù)，名字：King ，email：king@sxt.cn，部門 ID：1，薪水：9000，入職時(shí)間：2018 年 5 月 1 日，傭金：0.6

insert into employees values(default,'King','king@sxt.cn',1,9000,0.6,STR_TO_DATE('2018 年 5 月 1 日','%Y 年%m 月%d 日'))

示例三

查詢 employees 表中雇員名字為 King 的雇員的入職日期，要求顯示格式為 yyyy 年 MM月 dd 日。

select DATE_FORMAT(hire_date,'%Y 年%m 月%d 日') from employees where last_name ='King

4.6 通用函數(shù)

IFNULL(expr1,expr2)判斷 expr1 是否為 null，如果為 null，則用 expr2 來代替 null(類似 oracle 的 NVL()函數(shù))

NULLIF(expr1,expr2) 判斷 expr1 和 expr2 是否相等，如果相等則返回 null，如果不相等則返回 expr1

IF(expr1,expr2,expr3) 判斷 expr1 是否為真(是否不為 null)，如果為真，則使用 expr2替代 expr1；如果為假，則使用 expr3 替代 expr1(類似 oracle 的 NVL2()函數(shù))

COALESCE(value,...)判斷 value 的值是否為 null，如果不為 null，則返回 value；如果為 null，則判斷下一個(gè) value 是否為 null……直至出現(xiàn)不為 null 的 value 并返回或者返回最一個(gè)為 null 的 value

CASE WHEN THEN ELSE END 條件函數(shù)

4.7 MySQL支持left outr join 和 right outer join 但不支持full outer join

全外鏈接

注意：MySQL 中不支持 FULL OUTER JOIN 連接，可以使用 union 實(shí)現(xiàn)全完連接。

4.7.1UNION

可以將兩個(gè)查詢結(jié)果集合并，返回的行都是唯一的，如同對(duì)整個(gè)結(jié)果集合使用了distinct。

4.7.2 UNION ALL

只是簡單的將兩個(gè)結(jié)果合并后就返回。這樣，如果返回的兩個(gè)結(jié)果集中有重復(fù)的數(shù)據(jù)，那么返回的結(jié)果集就會(huì)包含重復(fù)的數(shù)據(jù)了。

4.7.3 語法結(jié)構(gòu)

SELECT 投 影 列 FROM 表 名 LEFT OUTER JOIN 表 名 ON 連 接 條 件 UNION

SELECT 投影列 FROM 表名 RIGHT OUTER JOIN 表名 ON 連接條件

示例

查詢所有雇員的名字以及他們的部門名稱，包含那些沒有雇員的部門以及沒有部門的雇員。

(select e.last_name,d.department_name from employees e LEFT OUTER JOIN departments d on e.dept_id = d.department_id) UNION (select e1.last_name,d1.department_name from employees e1 RIGHT OUTER JOIN departments d1 on d1.department_id = e1.dept_id)

5子查詢

可以將子查詢放在許多的 SQL 子句中，包括：

? WHERE 子句

? HAVING 子句

? FROM 子句

5.1使用子查詢的原則

? 子查詢放在圓括號(hào)中。

? 將子查詢放在比較條件的右邊。

? 在單行子查詢中用單行運(yùn)算符，在多行子查詢中用多行運(yùn)算符。

示例

select em.last_name,em.salary from employees em where em.salary > (select e.salary fromemployees e where e.last_name = 'Oldlu')

《新程序員》：云原生和全面數(shù)字化實(shí)踐50位技術(shù)專家共同創(chuàng)作，文字、視頻、音頻交互閱讀

總結(jié)

以上是生活随笔為你收集整理的mysql 9.0创建数据库_数据库基础学习——MySQL数据库知识小结（9）的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。