MySQL資料庫數據庫、表的管理 數據庫的增刪改查

創建資料庫

創建一個保存員工信息的資料庫
create database employees;
相關其他命令

show databases;

查看當前所有資料庫

    use employees;

“使用”一個資料庫，使其作為當前資料庫
命名規則
資料庫名不得超過30個字符，變量名限制為29個
必須隻能包含 A–Z, a–z, 0–9, _共63個字符
不能在對象名的字符間留空格
必須不能和用戶設定的其他對象重名
必須保證你的字段沒有和保留字、資料庫系統或常用方法沖突
保持字段名和類型的一致性,在命名字段並為其指定數據類型的時候一定要保證一致性。假如數據類型在一個表裡是整數,那在另一個表裡可就別變成字符型瞭
CREATE TABLE 語句
必須具備:
CREATE TABLE權限
存儲空間
必須指定:
表名
列名, 數據類型, 尺寸
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr][, …]);
創建表
語法
CREATE TABLE dept
(deptno INT(2),
dname VARCHAR(14),
loc VARCHAR(13));
確認
DESCRIBE dept
常用數據類型
INT 使用4個字節保存整數數據
CHAR(size) 定長字符數據。若未指定，默認為1個字符，最大長度255
VARCHAR(size) 可變長字符數據，根據字符串實際長度保存，必須指定長度
FLOAT(M,D) 單精度，M=整數位+小數位，D=小數位。 D<=M<=255,0<=D<=30，默認M+D<=6
DOUBLE(M,D) 雙精度。D<=M<=255,0<=D<=30，默認M+D<=15
DATE 日期型數據，格式’YYYY-MM-DD’
BLOB 二進制形式的長文本數據，最大可達4G
TEXT 長文本數據，最大可達4G

**創建表**
CREATE TABLE emp (  
    #int類型，自增  
    emp_id INT AUTO_INCREMENT,  
    #最多保存20個中英文字符  
    emp_name CHAR (20),  
    #總位數不超過15位  
    salary DOUBLE,  
    #日期類型  
    birthday DATE,  
    #主鍵  
    PRIMARY KEY (emp_id)
) ;

使用子查詢創建表
使用 AS subquery 選項，將創建表和插入數據結合起來
指定的列和子查詢中的列要一一對應
通過列名和默認值設定列

CREATE TABLE table        
        [(column, column...)]
    AS subquery;

使用子查詢創建表舉例

CREATE TABLE    dept80  
        AS     
            SELECT  employee_id, last_name,            
            salary*12 ANNSAL,             hire_date    
            FROM    employees    
            WHERE   department_id = 80;

ALTER TABLE 語句
使用 ALTER TABLE 語句可以實現：
向已有的表中添加列

ALTER TABLE dept80 
        ADD job_id varchar(15);
`
**修改現有表中的列**
可以修改列的數據類型, 尺寸和默認值
對默認值的修改隻影響今後對表的修改
``
ALTER TABLE dept80
                MODIFY      (last_name VARCHAR(30));

        ALTER TABLE dept80
            MODIFY      (salary double(9,2) default 1000);

丟棄現有表中的列
使用 DROP COLUMN 子句丟棄不再需要的列.

    ALTER TABLE  dept80
            DROP COLUMN  job_id; 

重命名現有表中的列
使用 CHANGE old_column new_column dataType子句重命名列

        ALTER TABLE  dept80
        *CHANGE department_name dept_name varchar(15);      

丟棄表*
數據和結構都被刪除
所有正在運行的相關事務被提交
所有相關索引被刪除
DROP TABLE 語句不能回滾
DROP TABLE dept80;
清空表
TRUNCATE TABLE 語句:
刪除表中所有的數據
釋放表的存儲空間
TRUNCATE TABLE detail_dept;
TRUNCATE語句不能回滾
可以使用 DELETE 語句刪除數據,可以回滾
對比：
delete from emp2;
select * from emp2;
rollback;
select * from emp2;
改變對象的名稱
執行RENAME語句改變表, 視圖的名稱
必須是對象的擁有者
ALTER table dept
RENAME TO detail_dept;

數據處理之增刪改

數據操縱語言
DML(Data Manipulation Language – 數據操縱語言) 可以在下列條件下執行:
向表中插入數據
修改現存數據
刪除現存數據
事務是由完成若幹項工作的DML語句組成的

插入數據

    使用 INSERT 語句向表中插入數據。
        INSERT INTO table [(column [, column...])]
        VALUES      (value [, value...]);
    為每一列添加一個新值。
    按列的默認順序列出各個列的值。 
    在 INSERT 子句中隨意列出列名和他們的值。
    字符和日期型數據應包含在單引號中。       

INSERT INTO departments(department_id,                  department_name,                   
    manager_id, 
    location_id)
            VALUES      (70, 'Public Relations', 100, 1700);

        INSERT INTO employees(employee_id,last_name,email,hire_date,job_id)

向表中插入空值
隱式方式: 在列名表中省略該列的值。
INSERT INTO departments (department_id, department_name )
VALUES (30, ‘Purchasing’);
顯示方式: 在VALUES 子句中指定空值。
INSERT INTO departments
VALUES (100, ‘Finance’, NULL, NULL);
插入指定的值
NOW()函數：記錄當前系統的日期和時間。
INSERT INTO employees (employee_id,
first_name, last_name,
email, phone_number,
hire_date, job_id, salary,
commission_pct, manager_id,
department_id)
VALUES (113,
‘Louis’, ‘Popp’,
‘LPOPP’, ‘515.124.4567’,
NOW(), ‘AC_ACCOUNT’, 6900,
NULL, 205, 100);
從其它表中拷貝數據
在 INSERT 語句中加入子查詢。
不必書寫 VALUES 子句。
子查詢中的值列表應與 INSERT 子句中的列名對應

        INSERT INTO emp2 
        SELECT * 
        FROM employees
        WHERE department_id = 90;

        INSERT INTO sales_reps(id, name, salary, commission_pct)
        SELECT employee_id, last_name, salary, commission_pct
        FROM   employees
        WHERE  job_id LIKE '%REP%';

*更新數據*

    UPDATE 語句語法
    使用 UPDATE 語句更新數據。

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

可以一次更新多條數據。
如果需要回滾數據，需要保證在DML前，進行設置：SET AUTOCOMMIT = FALSE;
使用 WHERE 子句指定需要更新的數據。

        UPDATE employees
        SET    department_id = 70
        WHERE  employee_id = 113;

如果省略 WHERE 子句，則表中的所有數據都將被更新。
UPDATE copy_emp
SET department_id = 110;

更新中的數據完整性錯誤

    UPDATE employees
    SET    department_id = 55
    WHERE  department_id = 110;     
    不存在 55 號部門

刪除數據

    使用 DELETE 語句從表中刪除數據。

        DELETE FROM   table
        [WHERE    condition];

使用 WHERE 子句刪除指定的記錄。

         DELETE FROM departments 
        WHERE  department_name = 'Finance';

如果省略 WHERE 子句，則表中的全部數據將被刪除
DELETE FROM copy_emp;
刪除中的數據完整性錯誤

    DELETE FROM departments
    WHERE       department_id = 60;
    不存在60號部門    

---

資料庫處理之查詢

1—基本select語句

SELECT 標識選擇哪些列。
FROM 標識從哪個表中選擇。

// 查詢
select * from 表名;// 查詢表中的所有數據, 查詢所有列

// 虛表的列由select後面from前面的內容
select 
    manager_id,
    manager_id,
    department_name 
from 
    departments;

註 意：
SQL 語言大小寫不敏感。
SQL 可以寫在一行或者多行
關鍵字不能被縮寫也不能分行
各子句一般要分行寫。
使用縮進提高語句的可讀性。

列的別名:
重命名一個列。
便於計算。緊跟列名，也可以在列名和別名之間加入關鍵字‘AS’，別名使用雙引號，以便在別名中包含空格或特殊的字符並區分大小寫。
// 列的別名

select 
    manager_id as manager,
    manager_id,
    department_name "dept name" 
from 
    departments;    

字符串可以是 SELECT 列表中的一個字符,數字,日期。
日期和字符隻能在單引號中出現。
每當返回一行時，字符串被輸出一次。

使用 DESCRIBE 命令，表示表結構

2—過濾和排序數據

使用WHERE 子句，將不滿足條件的行過濾掉。
WHERE 子句緊隨 FROM 子句。
    SELECT employee_id, last_name, job_id, department_id
    FROM   employees
    WHERE  department_id = 90 ;

（1）比較運算
賦值使用 := 符號

SELECT last_name, salary
FROM   employees
WHERE  salary <= 3000;

（2）其它比較運算

    SELECT last_name, salary
    FROM   employeesWHERE  salary 
    BETWEEN 2500 AND 3500;

使用 IN運算顯示列表中的值。

    SELECT employee_id, last_name, salary, manager_id
    FROM   employees
    WHERE  manager_id IN (100, 101, 201);

使用 LIKE 運算選擇類似的值
選擇條件可以包含字符或數字:
% 代表零個或多個字符(任意個字符)。
_ 代表一個字符。

SELECT  first_name
FROM    employees
WHERE   first_name LIKE 'S%';

‘%’和‘-’可以同時使用。

    SELECT last_name
    FROM   employees
    WHERE  last_name LIKE '_o%';

使用 IS (NOT) NULL 判斷空值。

    SELECT last_name, manager_id
    FROM   employees
    WHERE  manager_id IS NULL;

（3）邏輯運算
AND 要求並的關系為真。

    SELECT employee_id, last_name, job_id, salary
    FROM   employees
    WHERE  salary >=10000
    AND    job_id LIKE '%MAN%';

OR 要求或關系為真。

    SELECT employee_id, last_name, job_id, salary
    FROM   employees
    WHERE  salary >= 10000
    OR     job_id LIKE '%MAN%';
NOT
    SELECT last_name, job_id
    FROM   employees
    WHERE  job_id        
            NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');

ORDER BY子句
使用 ORDER BY 子句排序
ASC（ascend）: 升序(默認方式)
DESC（descend）: 降序
ORDER BY 子句在SELECT語句的結尾。

    SELECT   last_name, job_id, department_id, hire_date
    FROM     employees
    ORDER BY hire_date ;

降序排序

    SELECT   last_name, job_id, department_id, hire_date
    FROM     employees
    ORDER BY hire_date DESC ;

按別名排序

    SELECT employee_id, last_name, salary*12 annsal
    FROM   employees
    ORDER BY annsal;

多個列排序：按照ORDER BY 列表的順序排序。可以使用不在SELECT 列表中的列排序。

    SELECT last_name, department_id, salary
    FROM   employees
    ORDER BY department_id, salary DESC;

3 — 多表查詢
從多個表中獲取數據
笛卡爾集
笛卡爾集會在下面條件下產生:
省略連接條件
連接條件無效
所有表中的所有行互相連接
為瞭避免笛卡爾集， 可以在 WHERE 加入有效的連接條件。

使用連接在多個表中查詢數據。
在 WHERE 子句中寫入連接條件。
在表中有相同列時，在列名之前加上表名前綴
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;
等值連接

SELECT employees.employee_id, employees.last_name,        
       employees.department_id, departments.department_id,       
       departments.location_id
FROM   employees, departments
WHERE  employees.department_id = departments.department_id;

多個連接條件與 AND 操作符
區分重復的列名
使用表名前綴在多個表中區分相同的列。
在不同表中具有相同列名的列可以用表的別名加以區分。
表的別名
使用別名可以簡化查詢。

SELECT e.employee_id, e.last_name, e.department_id,       
    d.department_id, d.location_id
FROM   employees e , departments d
WHERE  e.department_id = d.department_id;

連接多個表
連接 n個表,至少需要 n-1個連接條件。 例如：連接三個表，至少需要兩個連接條件。
使用ON 子句創建連接
自然連接中是以具有相同名字的列為連接條件的。
可以使用 ON 子句指定額外的連接條件。
這個連接條件是與其它條件分開的。
ON 子句使語句具有更高的易讀性。

SELECT e.employee_id, e.last_name, e.department_id,        
    d.department_id, d.location_idF
ROM   employees e JOIN departments d
ON     (e.department_id = d.department_id);

使用 ON 子句創建多表連接

SELECT employee_id, city, department_name
FROM   employees e 
JOIN   departments d
ON     d.department_id = e.department_id 
JOIN   locations l
ON     d.location_id = l.location_id;

4 — 單行函數
大小寫控制函數：這類函數改變字符的大小寫。

字符控制函數：這類函數控制字符

數字函數
ROUND: 四舍五入ROUND(45.926, 2) 45.93
TRUNCATE: 截斷TRUNC(45.926, 2) 45.92
MOD: 求餘MOD(1600, 300) 100
條件表達式(瞭解)
在 SQL 語句中使用IF-THEN-ELSE 邏輯
使用方法:
CASE 表達式
5 — 分組函數
分組函數作用於一組數據，並對一組數據返回一個值。
組函數類型
AVG() COUNT() MAX() MIN() SUM()
組函數語法
SELECT [column,] group_function(column), …
FROM table
[WHERE condition]
[GROUP BY column]
[ORDER BY column];
AVG（平均值）和 SUM （合計）函數
可以對數值型數據使用AVG 和 SUM 函數。

SELECT AVG(salary), MAX(salary),              
       MIN(salary), SUM(salary)
FROM   employees
WHERE  job_id LIKE '%REP%';

MIN（最小值）和 MAX（最大值）函數
可以對任意數據類型的數據使用 MIN 和 MAX 函數。

SELECT MIN(hire_date), MAX(hire_date)
FROM      employees;

COUNT（計數）函數
COUNT(*) 返回表中記錄總數,適用於任意數據類型。

SELECT COUNT(*)
FROM      employees
WHERE  department_id = 50;

COUNT(expr) 返回expr不為空的記錄總數。

SELECT COUNT(commission_pct)
FROM   employees
WHERE  department_id = 50;

分組數據: GROUP BY 子句語法
可以使用GROUP BY子句將表中的數據分成若幹組
明確：WHERE一定放在FROM後面

SELECT  column, group_function(column)
FROM        table
[WHERE  condition]
[GROUP BY   group_by_expression]
[ORDER BY   column];

在SELECT 列表中所有未包含在組函數中的列都應該包含在 GROUP BY 子句中。

SELECT   department_id, AVG(salary)
FROM     employees
GROUP BY department_id ;

包含在 GROUP BY 子句中的列不是必須包含在SELECT 列表中

SELECT   AVG(salary)
FROM     employees
GROUP BY department_id ;

在GROUP BY子句中包含多個列

SELECT   department_id dept_id, job_id, SUM(salary)
FROM     employees
GROUP BY department_id, job_id ;

非法使用組函數
不能在 WHERE 子句中使用組函數。
可以在 HAVING 子句中使用組函數。

SELECT   department_id, AVG(salary)
FROM     employees
WHERE    AVG(salary) > 8000
GROUP BY department_id;
WHERE  AVG(salary) > 8000;
ERROR at line 3:
ORA-00934: group function is not allowed here

過濾分組： HAVING 子句
使用 HAVING 過濾分組:
1. 行已經被分組。
2. 使用瞭組函數。
3. 滿足HAVING 子句中條件的分組將被顯示。

SELECT  column, group_function
    FROM        table
    [WHERE  condition]
    [GROUP BY   group_by_expression]
    [HAVING group_condition]
    [ORDER BY   column];

SELECT   department_id, MAX(salary)
FROM     employees
GROUP BY department_id
HAVING   MAX(salary)>10000 ;

6—子查詢
子查詢語法

SELECT  select_listF
ROM table
WHERE   expr operator           
                (SELECT select_list             
                FROM        table);

子查詢 (內查詢) 在主查詢之前一次執行完成。
子查詢的結果被主查詢(外查詢)使用 。

SELECT last_name
FROM   employees
WHERE  salary >               
            (SELECT salary                
            FROM   employees               
             WHERE  last_name = 'Abel');

註意事項：
子查詢要包含在括號內。
單行操作符對應單行子查詢，多行操作符對應多行子查詢。
子查詢類型
單行子查詢：子查詢返回給主查詢的結果為一個值。
隻返回一行。
使用單行比較操作符。

題目：返回job_id與141號員工相同，salary比143號員工多的員工姓名，job_id 和工資

    SELECT last_name, job_id, salary
    FROM   employees
    WHERE  job_id =                  
                (SELECT job_id                 
                FROM   employees                 
                WHERE  employee_id = 141)
    AND    salary >                
                (SELECT salary                 
                FROM   employees                 
                WHERE  employee_id = 143);

在子查詢中使用組函數

    SELECT last_name, job_id, salary
    FROM   employees
    WHERE  salary =                 
                (SELECT MIN(salary)                 
                FROM   employees);

子查詢中的 HAVING 子句
首先執行子查詢。向主查詢中的HAVING 子句返回結果。
題目：查詢最低工資大於50號部門最低工資的部門id和其最低工資

    SELECT   department_id, MIN(salary)
    FROM     employees
    GROUP BY department_id
    HAVING   MIN(salary) >                       
                    (SELECT MIN(salary)                       
                    FROM   employees                        
                    WHERE  department_id = 50);

非法使用子查詢

    SELECT employee_id, last_name
    FROM   employees
    WHERE  salary in                
                (SELECT   MIN(salary)                 
                FROM     employees                 
                GROUP BY department_id);

多行子查詢使用單行比較符
子查詢中的空值問題

    SELECT last_name, job_id
    FROM   employees
    WHERE  job_id =                
                (SELECT job_id                 
                FROM   employees                 
                WHERE  last_name = 'Haas');

子查詢不返回任何行
多行子查詢：子查詢返回給主查詢的結果多與一個值。
返回多行。
使用多行比較操作符。

在多行子查詢中使用 ANY 操作符
題目：返回其它部門中比job_id為‘IT_PROG’部門任一工資低的員工的員工號、姓名、job_id 以及salary

    SELECT employee_id, last_name, job_id, salary
    FROM   employees
    WHERE  salary < ANY                    
                    (SELECT salary                     
                    FROM   employees                     
                    WHERE  job_id = 'IT_PROG')
    AND    job_id <> 'IT_PROG';

在多行子查詢中使用 ALL 操作符
題目：返回其它部門中比job_id為‘IT_PROG’部門所有工資都低的員工的員工號、姓名、job_id 以及salary

    SELECT employee_id, last_name, job_id, salary
    FROM   employees
    WHERE  salary < ALL                   
                    (SELECT salary                     
                    FROM   employees                     
                    WHERE  job_id = 'IT_PROG')
    AND    job_id <> 'IT_PROG';

子查詢中的空值問題

    SELECT emp.last_name
    FROM   employees emp
    WHERE  emp.employee_id NOT IN                             
                            (SELECT mgr.manager_id                              
                            FROM   employees mgr);
    no rows selected

---

**

資料庫事務

**
事務：一組邏輯操作單元,使數據從一種狀態變換到另一種狀態。
資料庫事務由以下的部分組成:
一個或多個DML 語句
一個 DDL(Data Definition Language – 數據設定語言) 語句
一個 DCL(Data Control Language – 數據控制語言) 語句
在資料庫編程語言中，事務是將一個數據處理執行步驟的集合作為一個單元來處理。
也就是說，執行這些步驟就好像是執行一個單個的命令一樣。

設置提交狀態：SET AUTOCOMMIT = FALSE;
或者顯式的執行 start transaction或 begin
以第一個 DML 語句的執行作為開始
以下面的其中之一作為結束:
COMMIT 或 ROLLBACK 語句
DDL 語句（自動提交）
用戶會話正常結束
系統異常終止
COMMIT和ROLLBACK語句的優點
使用COMMIT 和 ROLLBACK語句,我們可以:
確保數據完整性。
數據改變被提交之前預覽。
將邏輯上相關的操作分組。
使用預處理語句
MySQL伺服器支持預處理語句。當想要執行多個查詢，而每個查詢之間隻有很小的差別時，預處理語句將會非常有用。
例如，可以預備一條語句，然後多次執行它，而每次隻是數據值不同。
除瞭提供執行的方便外，預處理語句還能提高性能。
可以在mysql命令行客戶端設定與使用預處理語句來測試與調試程式。

用戶設定變量
用戶可以保存一個值到用戶設定的變量中（也稱為用戶變量），然後在以後執行預處理語句時使用它。
用戶變量用@var_name表示。可使用SET語句來設置用戶變量：
SET @var_name = expr [, @var_name = expr] …
下列示例預備瞭一條語句：
確定一個給定國傢有多少種語言被使用
然後使用用戶設定的變量來執行它多次，並顯示結果：

    PREPARE my_stmt FROM         
    '
        SELECT COUNT(*) 
        FROM CountryLanguage 
        WHERE CountryCode= ?
    ';
    SET @code = 'ESP';
    EXECUTE my_stmt USING @code;

    SET @code = 'RUS';
    EXECUTE my_stmt USING @code;

    DEALLOCATE PREPARE my_stmt;     

可使用PREPARE語句設定一條SQL語句，用以在以後執行。
語句可以是不完整的，在預備時的未知數據值可以由問號（?）來表示，它作為參數標記。
在語句被執行時，可以為語句的每個參數指定一個數據值。伺服器將會用數據值來替換標記符號以完成該語句。
語句在每次執行時可以使用不同的值。
如果PREPARE語句使用的名稱已經存在，伺服器將丟棄該名稱原有的預處理語句，然後預備一個新的語句。
釋放預處理語句
當預處理語句被重新設定，或與伺服器的連接被中斷後，預處理語句將被自動刪除。
可使用DEALLOCATE PREPARE語句來釋放語句：
DEALLOCATE PREPARE namepop;
MySQL還提供瞭DROP PREPARE作為DEALLOCATE PREPARE語句的別名。