數(shù)據(jù)庫管理

新建數(shù)據(jù)庫

CREATE DATABASE  [IF NOT EXISTS] 數(shù)據(jù)庫名

示例

CREATE DATABASE IF NOT EXISTS MySQLDB;

數(shù)據(jù)類型

數(shù)值類型

日期類型

約束

重要，記下

非空約束（NOT NULL）

強制列不能為 NULL 值脑蠕，約束強制字段始終包含值。這意味著脾歧，如果不向字段添加值譬胎，就無法插入新記錄或者更新記錄磕蛇。
唯一約束（UNIQUE）

• 唯一約束可以保證記錄的唯一性，即就是同一個表中囱桨，相同字段的值不會出現(xiàn)重復(fù)仓犬。
• 唯一約束的字段可以為空值（NULL）。
• 每一張數(shù)據(jù)表可以存在多個唯一約束字段舍肠。

主鍵約束（PRIMARY KEY）

數(shù)據(jù)庫表要求表中的每一行記錄都必須是唯一的搀继，即在同一張表中不允許出現(xiàn)完全相同的兩條記錄窘面。在設(shè)計數(shù)據(jù)庫時，為了保證記錄的“唯一性”叽躯，最為普遍财边、最為推薦的做法是為表定義一個主鍵（primary key）。數(shù)據(jù)庫表中主鍵有以下兩個特征：
表的主鍵可以由一個字段構(gòu)成点骑，也可以由多個字段構(gòu)成（這種情況稱為復(fù)合主鍵）酣难。
數(shù)據(jù)庫表中主鍵的值具有唯一性且不能取空值（NULL），當數(shù)據(jù)庫表中的主鍵由多個字段構(gòu)成時畔况，每個字段的值不能取NULL值

CREATE TABLE classes (
    classes_id INT PRIMARY KEY,
    classes_name VARCHAR (10),
    classes_dept VARCHAR (10)
);

外鍵約束（FOREIGN KEY）

班級實體和班主任實體之間為一對一關(guān)系鲸鹦，班級實體和學(xué)生實體之間為一對多關(guān)系，學(xué)生實體和課程實體之間為多對多關(guān)系跷跪。
實體間的關(guān)系可以通過外鍵來表示馋嗜。如果表A中的一個字段a對應(yīng)于表B的主鍵b，則字段a稱為表A的外鍵吵瞻。此時存儲在表A中字段a的值葛菇，同時這個字段值也是表B主鍵b的值。

CREATE TABLE teacher (
    teacher_id INT PRIMARY KEY,
    teacher_name VARCHAR (10),
    cid INT,
    FOREIGN KEY (cid) REFERENCES classes (classes_id)
);

練習(xí)：

學(xué)校想做一個選課系統(tǒng)橡羞，其中涉及到課程表眯停，學(xué)生表，請分別創(chuàng)建這兩個表卿泽，自己思考表中應(yīng)有的列及數(shù)據(jù)類型莺债。
學(xué)校有一個選課系統(tǒng)，其中包括如下關(guān)系模式：
系(系編號： 主鍵签夭，系名稱: 唯一鍵齐邦，系主任： 非空約束，系所在校去：默認為渾南區(qū))
班級(班級編號： 主鍵第租，班級名稱： 唯一鍵措拇，所屬系： 外鍵)
創(chuàng)建學(xué)生表，包含如下屬性:
學(xué)號 定長字符型 10位 主鍵
姓名 變長字符型 20位 非空
性別 定長字符型 2位
出生日期 日期型
所在班級

-- 學(xué)校想做一個選課系統(tǒng)慎宾，其中涉及到課程表丐吓，學(xué)生表，
-- 請分別創(chuàng)建這兩個表趟据，自己思考表中應(yīng)有的列及數(shù)據(jù)類型券犁。
-- 學(xué)校有一個選課系統(tǒng)，其中包括如下關(guān)系模式：
-- 系(系編號： 主鍵汹碱，系名稱: 唯一鍵族操，系主任： 非空約束，系所在校去：默認為渾南區(qū))
-- 班級(班級編號： 主鍵，班級名稱： 唯一鍵色难，所屬系： 外鍵)
-- 創(chuàng)建學(xué)生表泼舱，包含如下屬性:
-- 學(xué)號 定長字符型 10位 主鍵
-- 姓名 變長字符型 20位 非空
-- 性別 定長字符型 2位
-- 出生日期 日期型
-- 所在班級
CREATE TABLE
IF NOT EXISTS xi (
    XINumber VARCHAR (20) NOT NULL PRIMARY KEY,
    XIName VARCHAR (20) NOT NULL UNIQUE,
    XIZhuren VARCHAR (20) NOT NULL,
    Xiaoqu enum ('渾南區(qū)')
);

CREATE TABLE
IF NOT EXISTS class (
    classid VARCHAR (20) NOT NULL PRIMARY KEY,
    cname VARCHAR (20) NOT NULL UNIQUE,
    xiname VARCHAR (10) NOT NULL,
    FOREIGN KEY (xiname) REFERENCES xi (xiname)
);

CREATE TABLE
IF NOT EXISTS student (
    xh VARCHAR (20) NOT NULL PRIMARY KEY,
    xm VARCHAR (20) NOT NULL,
    gender CHAR (2),
    birthday DATE,
    cname VARCHAR (20) NOT NULL UNIQUE,
    FOREIGN KEY (cname) REFERENCES class (cname)
);

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

DROP DATABASE 數(shù)據(jù)庫名

復(fù)制表

CREATE TABLE 新表名  LIKE 源表

方法二

CREATE TABLE 新表名 SELECT - FROM 源表
CREATE TABLE emphaha SELECT  *   FROM emp

刪除表

DROP TABLE 表名

表的重命名

RENAME TABLE 舊表名 TO 新表名

等效

ALTER TABLE person RENAME human;

創(chuàng)建與dept表相同表結(jié)構(gòu)的表dtest，將dept表中部門編號在40之前的信息插入該表枷莉。

CREATE TABLE dtest SELECT * from dept WHERE DEPTNO < 40;

創(chuàng)建與emp表結(jié)構(gòu)相同的表empl娇昙，并將其部門編號為前30號的員工信息復(fù)制到empl表。

CREATE TABLE empl SELECT * from emp WHERE DEPTNO < 30;

數(shù)據(jù)操作與事務(wù)控制

Data Manipulation Language ,簡稱DML笤妙，主要用來實現(xiàn)對數(shù)據(jù)庫表中的數(shù)據(jù)進行操作冒掌。
數(shù)據(jù)操作語言主要包括如下幾種：
增加行數(shù)據(jù)：使用INSERT語句實現(xiàn)
修改行數(shù)據(jù)：使用UPDATE語句實現(xiàn)
刪除行數(shù)據(jù)：使用DELETE語句實現(xiàn)

插入數(shù)據(jù)

INSERT INTO    table [(column [, column...])]
VALUES        (value [, value...]);

• 采用這種語法一次只能追加一條記錄；
• column部分叫做列名列表蹲盘，value部分叫做值列表股毫，列名列表和值列表必須在個數(shù)及數(shù)據(jù)類型上保持一致；
• 列名列表部分可以省略召衔，如果省略铃诬，默認包括該表的所有列，列的順序為使用 desc 表名 命令所查看的順序苍凛；
• 列名列表部分也可以指定部分非空的列趣席，注意值列表必須和列名列表對應(yīng)；
  字符和日期型數(shù)據(jù)必須要用單引號括起來

INSERT into dept VALUES(50, 'IT', '北京')

插入空值NULL

INSERT into dept VALUES(6
0, 'Test', null);

向部門表新增一個部門醇蝴，部門編號為50宣肚，部門名稱為HR，工作地點為SY悠栓。

 insert into dept values(50,"HR","SY"); 

向部門表新增一個部門霉涨，部門編號為60，部門名稱為MARKET惭适。

 insert into dept values(60,"MARKET","");

插入日期值

SYSDATE()

insert into emp values(8888,'haha','該溜子',null,SYSDATE(),4000,12,50  );

插入日期值

insert into emp values(8889,'李會長','小青年',null,'1888-01-01
',4000,12,50  );

向員工表中新增一個員工嵌纲，員工編號為8888，姓名為BOB腥沽，崗位為CLERK，經(jīng)理為號7788鸠蚪，入職日期為1985-03-03今阳，薪資3000，獎金和部門為空茅信。

insert into emp values(8899, 'BOB', 'clerk', 7788, '1985-03-03', 3000, null, null);

批量插入數(shù)據(jù)

INSERT INTO EMP(EMPNO, ENAME, JOB, SAL) 
VALUES 
('8881', '張三', '部門經(jīng)理', 6000),
('8882', '李四', '職員', 3000),
('8883', '王五', '職員', 3500),
('8884' ,'趙六', '部門經(jīng)理', 6500),
('8885', '高七', '職員', 2500),
('8886', '馬八', '職員', 3100),
('8887', '錢九', '部門經(jīng)理', 5000),
('8888', '孫十',  '職員', 2800);

通過子查詢插入多行數(shù)據(jù)

• INSERT子句和數(shù)據(jù)類型必須和子查詢中列的數(shù)量和類型相匹配中列的數(shù)量
  創(chuàng)建一個manager表

create table manager as select * from emp where 1=0;

插入數(shù)據(jù)盾舌， 全部工作為salesman的數(shù)據(jù)

insert into manager select * from emp where job='SALESMAN';

使用CREATE TABLE emp_back as SELECT - FROM EMP WHERE 1=0，創(chuàng)建emp_back表,拷貝下來即可蘸鲸。

create table emp_back as select * from emp where 1=0;

把emp表1982年1月1日之前的員工信息復(fù)制到emp_back表中

insert into manager select * from emp where hiredate <'1982-01-01';

修改數(shù)據(jù)

UPDATE        table
SET        column = value [, column = value]
[WHERE         condition];

Mysql日期計算

DATE_ADD(date,INTERVAL expr unit) 返回一個日期加上指定的時間單元
DATE_SUB(date,INTERVAL expr unit) 返回一個日期減去指定的時間單元

SELECT DATEDIFF('2020-08-05','2020-08-02') AS 相差時間;  -- 3天

unit 可選字段

修改部門20的員工信息妖谴，把82年之后入職的員工入職日期向后調(diào)整10天

UPDATE emp
SET HIREDATE = DATE_ADD(HIREDATE,INTERVAL 10 DAY)
WHERE
    DEPTNO = 20
AND HIREDATE > '1982-01-01';

修改獎金為null的員工，獎金設(shè)置為0

update emp set comm=0 where comm is null;

修改工作地點在NEW YORK或CHICAGO的員工工資，工資增加500

UPDATE emp e,
 dept d
SET e.sal = e.sal + 500
WHERE
    e.deptno = d.deptno
AND d.loc IN ('NEW YORK', 'CHICAGO');

刪除數(shù)據(jù)語法

DELETE FROM      table
[WHERE      condition];

DELETE FROM      empl
WHERE empl.EMPNO = 7902;

截斷表語法

TRUNCATE TABLE table; 

TRUNCATE和DELETE區(qū)別

• TRUNCATE是DDL膝舅，只能刪除表中所有記錄嗡载，釋放存儲空間，使用ROLLBACK不可以回滾仍稀。
• DELETE是DML洼滚，可以刪除指定記錄，不釋放存儲空間技潘，使用ROLLBACK可以回滾遥巴。
  刪除經(jīng)理編號為7566的員工記錄

delete from emp_copy where mgr=7566;

刪除工作在NEW YORK的員工記錄

DELETE
FROM
    emp_copy
WHERE
    deptno IN (
        SELECT
            DEPTNO
        FROM
            dept
        WHERE
            loc = 'NEW YORK'
    )

刪除工資大于所在部門平均工資的員工記錄

DELETE FROM emp_copy WHERE empno in (select * from (SELECT
    a.empno
FROM
    emp_copy AS a,
    (
        SELECT
            deptno,
            AVG(sal) AS avgsal
        FROM
            emp_copy
        GROUP BY
            deptno
    ) as b
WHERE 
a.deptno = b.deptno
AND a.sal > b.avgsal) as query)

delete from emp22 where sal>(select avg(sal) from emp e where emp22.deptno=e.deptno);

Mysql 事物

在數(shù)據(jù)庫中，事務(wù)由一組相關(guān)的DML或SELECT語句享幽，加上一個TPL語句（COMMIT铲掐、ROLLBACK）或一個DDL語句(CREATE、ALTER值桩、DROP摆霉、TRUNCATE等)或一個DCL（GRANT、REVOKE）語句颠毙。

事務(wù)特征可用四個字母的縮寫表示：即ACID
原子性（Atomicity）
事務(wù)就像“原子”一樣斯入，不可被分割，組成事務(wù)的DML操作語句要么全成功蛀蜜，要么全失敗刻两，不可能出現(xiàn)部分成功部分失敗的情況。
一致性（Consistency）
一旦事務(wù)完成滴某，不管是成功的磅摹，還是失敗的，整個系統(tǒng)處于數(shù)據(jù)一致的狀態(tài)霎奢。
隔離性（Isolation）
一個事務(wù)的執(zhí)行不會被另一個事務(wù)所干擾户誓。比如兩個人同時從一個賬戶從取錢，通過事務(wù)的隔離性確保賬戶余額的正確性幕侠。
持久性（Durability）
也稱為永久性帝美，指事務(wù)一旦提交，對數(shù)據(jù)的改變就是永久的晤硕，不可以再被回滾悼潭。

存儲引擎

• InnoDB存儲引擎的特點:
  支持外鍵（Foreign Key）
  支持事務(wù)（Transaction）：如果某張表主要提供OLTP支持，需要執(zhí)行大量的增舞箍、刪舰褪、改操作（insert、delete疏橄、update語句）占拍，出于事務(wù)安全方面的考慮，InnoDB存儲引擎是更好的選擇。
  最新版本的MySQL已經(jīng)開始支持全文檢索晃酒。
• MyISAM存儲引擎的特點：
  MyISAM具有檢查和修復(fù)表的大多數(shù)工具表牢。
  MyISAM表可以被壓縮
  MyISAM表最早支持全文索引
  但MyISAM表不支持事務(wù)
  但MyISAM表不支持外鍵（Foreign Key）。
  如果需要執(zhí)行大量的select語句掖疮，出于性能方面的考慮初茶，MyISAM存儲引擎是更好的選擇。

事務(wù)演示

建立一個account表

CREATE TABLE `account` (
  `id` int(12) NOT NULL,
  `name` varchar(12) NOT NULL,
  `balance` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• 1浊闪、手動開啟事務(wù)

start transaction;

• 2恼布、執(zhí)行一些操作
  給張三賬戶轉(zhuǎn) 50塊錢