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

318

創建數據庫

創建一個保存員工信息的數據庫
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 ? ?DOUBLE(M,D) ? ? ? ? 雙精度。D ? ?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表示?？墒褂肧ET語句來設置用戶變量：
? ? ? ?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語句的別名。 ?
總 ?結
通過本章學習, 您應學會如何使用DML語句改變數據和事務控制

創建數據庫

創建一個保存員工信息的數據庫
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 ? ?DOUBLE(M,D) ? ? ? ? 雙精度。D ? ?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 列表的順序排序?？梢允褂貌辉赟ELECT 列表中的列排序。

    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表示?？墒褂肧ET語句來設置用戶變量：
? ? ? ?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語句的別名。 ?
總 ?結
通過本章學習, 您應學會如何使用DML語句改變數據和事務控制

以上就是MySQL數據庫、表的管理，數據庫的增刪改查的內容，更多相關內容請關注PHP中文網（www.php.cn）！

? 版權聲明
THE END
數據庫
# 數據類型
喜歡就支持一下吧
點贊8 分享