検索

ホームページ >データベース >mysql チュートリアル >MySQL データベースとテーブルの管理、データベースの追加、削除、変更、クエリ

MySQL データベースとテーブルの管理、データベースの追加、削除、変更、クエリ

黄舟
黄舟オリジナル
2017-02-11 11:14:341620ブラウズ

createデータベースの作成omelly従業員情報を保存するデータベースを作成します。

命名規則

データベース名は 30 文字を超えてはならず、変数名は 29 文字に制限されています

A ～ Z、a ～ z、0 ～ 9、_63 文字のみを含める必要があります

オブジェクト名の文字の間にスペースを入れることはできません

他のユーザー定義オブジェクトと同じ名前を付けることはできません フィールドが予約語、データベース システム、または一般的なメソッドと競合しないことを確認する必要があります フィールド名と型の一貫性を維持する フィールドの名前とデータ型を指定するときは、必ず一貫性を確保してください。あるテーブルでデータ型が整数の場合、別のテーブルで文字型に変更しないでください
create database employeesCREATE TABLE ステートメント

必須アイテム:

テーブルの作成権限 収納スペース 必ず指定してください:

テーブル名

列名、データ型、サイズ CREATE TABLE [スキーマ.]テーブル (列データ型 [DEFAULT expr][, …]);
テーブルを作成します
文法
CREATE TABLE部門
(DEPTNO INT (2)、
dname VARCHAR(14)、
Loc varchar (13)); 確認します
DESCRIBE 部門
一般的なデータ型
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 の長いテキスト データ

show databases;
サブクエリを使用してテーブルを作成します

AS サブクエリ オプションを使用して、テーブルの作成とデータの挿入を組み合わせます
指定された列とサブクエリ内の列は 1 対 1 に対応している必要があります
列名とデフォルト値定義列による
    use employees;

サブクエリを使用してテーブルを作成する例

**创建表**
CREATE TABLE emp (  
    #int类型，自增  
    emp_id INT AUTO_INCREMENT,  
    #最多保存20个中英文字符  
    emp_name CHAR (20),  
    #总位数不超过15位  
    salary DOUBLE,  
    #日期类型  
    birthday DATE,  
    #主键  
    PRIMARY KEY (emp_id)
) ;

ALTER TABLEステートメント
ALTER TABLEステートメントを使用すると、次のことが実現できます:

既存のテーブルに列を追加します

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;


既存のテーブル列を破棄します
不要になった列を削除するには、DROP COLUMN 句を使用します。

ALTER TABLE dept80 
        ADD job_id varchar(15);`
**修改现有表中的列**
可以修改列的数据类型, 尺寸和默认值
对默认值的修改只影响今后对表的修改
``ALTER TABLE dept80
                MODIFY      (last_name VARCHAR(30));
既存のテーブルの列の名前を変更します

CHANGE old_column new_column dataType 句を使用して列の名前を変更します 

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

Drop table*

データと構造が削除されます 関連する実行中のトランザクションはすべてコミットされます 関連するインデックスがすべて削除されます
DROP TABLE ステートメントはロールバックできません
DROP TABLE dept80; テーブルを空にします

TRUNCATE TABLE ステートメント: テーブル内のすべてのデータを削除します テーブルのストレージスペースを解放します
テーブルの詳細_部門を切り捨てる

TRUNCATE ステートメントはロールバックできません

DELETE ステートメントを使用してデータを削除し、ロールバックできます 比較: emp2から削除します - [emp2から]を選択します ロールバック
EMP2 から * を選択します

オブジェクトの名前を変更します テーブルの名前変更ステートメントを実行します。ビューの名前はオブジェクトの所有者である必要があります ALTERテーブル部門
名前を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 3caa7f45a84dee24443d3a1b174e1ca4=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 75fc02dffa7b9658d690e3010e3e0437 'IT_PROG';

在多行子查询中使用 ALL 操作符
   题目：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary

    SELECT employee_id, last_name, job_id, salary
    FROM   employees
    WHERE  salary 2e11c18c7c8350820889853be2ad562a '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语句的别名。  
总  结
通过本章学习, 您应学会如何使用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<=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, &#39;Public Relations&#39;, 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 3caa7f45a84dee24443d3a1b174e1ca4=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 75fc02dffa7b9658d690e3010e3e0437 'IT_PROG';

在多行子查询中使用 ALL 操作符
   题目：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary

    SELECT employee_id, last_name, job_id, salary
    FROM   employees
    WHERE  salary 2e11c18c7c8350820889853be2ad562a '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语句的别名。  
总  结
通过本章学习, 您应学会如何使用DML语句改变数据和事务控制

以上就是MySQL数据库、表的管理，数据库的增删改查的内容，更多相关内容请关注PHP中文网（www.php.cn）！


声明：
この記事の内容はネチズンが自主的に寄稿したものであり、著作権は原著者に帰属します。このサイトは、それに相当する法的責任を負いません。盗作または侵害の疑いのあるコンテンツを見つけた場合は、admin@php.cn までご連絡ください。
前の記事：MySQL データベースの制約とページング次の記事：MySQL データベースの制約とページング

関連記事

続きを見る