Mysql数据库2

分手后的思念是犯贱 2022-11-16 10:53 152阅读 0赞

**目录**

一.DML操作

1.新增（INSERT）

2.修改（UPDATE）

3.删除（DELETE）

4.清空整表数据（TRUNCATE）

二.数据表操作

1.数据表的创建（CREATE）

2.数据表的修改(ALTER)

2.1向现有表中添加列

2.2修改表中的列

2.3删除表中的列

2.4修改列名

2.5修改表名

3.数据表的删除(DROP)

三.约束

1.实体完整性约束

1.1主键约束

1.2唯一约束

1.3自动增长列

2. 域完整性约束

2.1非空约束

2.2默认值约束

四.事务

1.事务的概念

2.事务的边界

3.事务的原理

4.事务的特性

五.权限管理

1.创建用户

2.授权

3.撤销权限

4.删除用户

六.视图

1.概念

2.视图特点

优点

缺点

3.视图的创建

4.视图的修改

5.图的删除

6.视图的注意事项

七.SQL 语言分类

1.SQL语言分类

--------------------

# 一.DML操作 #

## 1.新增（INSERT） ##

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

#添加一条员工信息
    INSERT INTO `t_employees`
    （EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID）
    VALUES 
    ('194','Samuel','McCain','SMCCAIN', '650.501.3876', '1998-07-01', 'SH_CLERK', '3200', NULL, '123', '50');

1.指定字段新增

2.不指定字段新增

3.批量新增

注意：表名里面的列名和values里面的值要一一对应（个数，顺序，数据类型）

## 2.修改（UPDATE） ##

UPDATE 表名 SET 列1=新增1，列2=新增2，...WHERE 条件;

#修改编号为135 的员工信息岗位编号为 ST_MAN，工资为3500
    UPDATE t_employees SET JOB_ID='ST_MAN',SALARY = 3500 WHERE EMPLOYEE_ID = '135';

注意：SET 后多个列名=值，绝大多数情况下都要加 WHERE 条件，指定修改，否则为整表更新

## 3.删除（DELETE） ##

DELETE FROM 表名 WHERE 条件；

#删除姓Peter，并且名为 Hall 的员工
    DELETE FROM t_employees WHERE FIRST_NAME = 'Peter' AND LAST_NAME='Hall';

## 4.清空整表数据（TRUNCATE） ##

TRUNCATE TABLE 表名;

清空整张表

#清空t_countries整张表
    TRUNCATE TABLE t_countries;

# 二.数据表操作 #

## 1.数据表的创建（CREATE） ##

> CREATE TABLE 表名（
> 
> 列名 数据类型 \[约束\],
> 
> 列名 数据类型 \[约束\],
> 
> ....
> 
> 列名 数据类型 \[约束\] //最后一列的末尾不加逗号
> 
> ）\[charset=utf8\] //可根据需要指定表的字符编码集

#依据上述表格创建数据表，并向表中插入 3 条测试语句
    CREATE TABLE subject(
    	subjectId INT,
      subjectName VARCHAR(20),
      subjectHours INT
    )charset=utf8;
    
    INSERT INTO subject(subjectId,subjectName,subjectHours) VALUES(1,'Java',40);
    INSERT INTO subject(subjectId,subjectName,subjectHours) VALUES(2,'MYSQL',20);
    INSERT INTO subject(subjectId,subjectName,subjectHours) VALUES(3,'JavaScript',30);

## 2.数据表的修改(ALTER) ##

ALTER TABLE 表名 操作;

### 2.1向现有表中添加列 ###

#在课程表基础上添加grade 列
    ALTER TABLE subject ADD grade int;

### 2.2修改表中的列 ###

#修改课程表中课程名称长度为10个字符
    ALTER TABLE subject MODIFY subjectName VARCHAR(10);

### 2.3删除表中的列 ###

#删除课程表中 gradeId 列
    ALTER TABLE subject DROP gradeId;

### 2.4修改列名 ###

#修改课程表中 subjectHours 列为 classHours
    ALTER TABLE subject CHANGE subjectHours classHours int ;

### 2.5修改表名 ###

#修改课程表的subject 为 sub
    ALTER TABLE subject rename sub;

## 3.数据表的删除(DROP) ##

#删除学生表
    DROP TABLE student;

# 三.约束 #

## 1.实体完整性约束 ##

表中的一行数据代表一个实体（entity），实体完整性的作用即是标识每一行数据不重复、实体唯一。

### 1.1主键约束 ###

PRIMARY KEY唯一，标识表中的一行数据，此列的值不可重复，且不能为 NULL

#为表中适用主键的列添加主键约束
    CREATE TABLE subject(
    	subjectId INT PRIMARY KEY,#课程编号标识每一个课程的编号唯一，且不能为 NULL
      subjectName VARCHAR(20),
      subjectHours INT
    )charset=utf8;
    
    INSERT INTO subject(subjectId,subjectName,subjectHours) VALUES(1,'Java',40);
    INSERT INTO subject(subjectId,subjectName,subjectHours) VALUES(1,'Java',40);#error 主键 1 已存在

### 1.2唯一约束 ###

UNION唯一，标识表中的一行数据，不可重复，可以为 NULL

#为表中列值不允许重复的列添加唯一约束
    CREATE TABLE subject(
    	subjectId INT PRIMARY KEY,
      subjectName VARCHAR(20) UNIQUE,#课程名称唯一！
      subjectHours INT
    )charset=utf8;

### 1.3自动增长列 ###

AUTO INCREMENT自动增长，给主键数值列添加自动增长。从 1 开始，每次加 1。不能单独使用，和主键配合。

#为表中主键列添加自动增长，避免忘记主键 ID 序号
    CREATE TABLE subject(
    	subjectId INT PRIMARY KEY AUTO_INCREMENT,#课程编号主键且自动增长，会从 1 开始根据添加数据的顺序依次加 1
      subjectName VARCHAR(20) UNIQUE,
      subjectHours INT
    )charset=utf8;

## 2. 域完整性约束 ##

限制列的单元格的数据正确性。

### 2.1非空约束 ###

NOT NULL 非空，此列必须有值。

### 2.2默认值约束 ###

DEFAUL值 为列赋予默认值，当新增数据不指定值时，书写DEFAULT，以指定的默认值进行填充。

# 四.事务 #

## 1.事务的概念 ##

事务是一个原子操作。是一个最小执行单元。可以由一个或多个SQL语句组成，在同一个事务当中，所有的SQL语句都成功执行时，整个事务成功，有一个SQL语句执行失败，整个事务都执行失败。

## 2.事务的边界 ##

*  开始：连接到数据库，执行：**STRAT TRANSACTION; 即开启事务**。然后输入DML操作
 *  结束：
    
    1). 提交：
    
    a. 显示提交：commit;
 *  b. 隐式提交：一条创建、删除的语句，正常退出（客户端退出连接）;
    
    2). 回滚：
    
    a. 显示回滚：rollback;
    
    b. 隐式回滚：非正常退出（断电、宕机），执行了创建、删除的语句，但是失败了，会为这个无效的语句执行回滚。

#start transaction或者SET autocommit=0;   1为自动commit 0为不自动commit
    START TRANSACTION;
    UPDATE  account SET money=money-1000  WHERE moneyid =1;
    
    UPDATE  account SET money=money+1000  WHERE moneyid =2;
    SELECT * FROM account;
    #commit;提交  
    COMMIT;
    #rollback 回滚
    ROLLBACK;

## 3.事务的原理 ##

>        数据库会为每一个客户端都维护一个空间独立的缓存区(回滚段)，一个事务中所有的增删改语句的执行结果都会缓存在回滚段中，只有当事务中所有SQL 语句均正常结束（commit），才会将回滚段中的数据同步到数据库。否则无论因为哪种原因失败，整个事务将回滚（rollback）。

## 4.事务的特性 ##

>  *  原子性 ——>表示一个事务内的所有操作是一个整体，要么全部成功，要么全部失败
>  *  一致性 ——>表示一个事务内有一个操作失败时，所有的更改过的数据都必须回滚到修改前状态
>  *  隔离性 ——>事务查看数据操作时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。
>  *  持久性 ——>持久性事务完成之后，它对于系统的影响是永久性的。

# 五.权限管理 #

## 1.创建用户 ##

CREATE USER 用户名 IDENTIFIED BY 密码

> \#创建一个 zhangsan 用户
> 
>  *  CREATE USER \`zhangsan\` IDENTIFIED BY '123';

## 2.授权 ##

GRANT ALL ON 数据库.表 TO 用户名;

> \#将 companyDB下的所有表的权限都赋给 zhangsan
> 
>  *  GRANT ALL ON companyDB.\* TO \`zhangsan\` ;

## 3.撤销权限 ##

REVOKE ALL ON 数据库.表名 FROM 用户名

> \#将 zhangsan 的 companyDB 的权限撤销
> 
>  *  REVOKE ALL ON companyDB.\* FROM \`zhangsan\`;

注意：撤销权限后，账户要重新连接客户端才会生效。

## 4.删除用户 ##

DROP USER 用户名

> \#删除用户 zhangsan
> 
>  *  DROP USER \`zhangsan\`;

# 六.视图 #

## 1.概念 ##

视图，虚拟表，从一个表或多个表中查询出来的表，作用和真实表一样，包含一系列带有行和列的数据。视图中，用户可以使用SELECT语句查询数据，也可以使用INSERT，UPDATE，DELETE修改记录，视图可以使用户操作方便，并保障数据库系统安全。

## 2.视图特点 ##

### 优点 ###

>  *  简单化，数据所见即所得。
>  *  安全性，用户只能查询或修改他们所能见到得到的数据。
>  *  逻辑独立性，可以屏蔽真实表结构变化带来的影响。

### 缺点 ###

>  *  性能相对较差，简单的查询也会变得稍显复杂。
>  *  修改不方便，特别是复杂的聚合视图基本无法修改。

## 3.视图的创建 ##

语法：CREATE VIEW 视图名 AS 查询数据源表语句;

> \#创建 t\_empInfo 的视图，其视图从 t\_employees 表中查询到员工编号、员工姓名、员工邮箱、工资
> 
>  *  CREATE VIEW t\_empInfo 
>  *  AS 
>  *  SELECT EMPLOYEE\_ID,FIRST\_NAME,LAST\_NAME,EMAIL,SALARY from t\_employees;

## 4.视图的修改 ##

*  方式一：CREATE OR REPLACE VIEW 视图名 AS 查询语句
 *  方式二：ALTER VIEW 视图名 AS 查询语句

#方式 1：如果视图存在则进行修改，反之，进行创建
    CREATE  OR  REPLACE VIEW t_empInfo 
    AS
    SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,SALARY,DEPARTMENT_ID from t_employees;
    
    #方式 2：直接对已存在的视图进行修改
    ALTER VIEW t_empInfo
    AS 
    SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,SALARY from t_employees;

## 5.图的删除 ##

DROP VIEW 视图名

> \#删除t\_empInfo视图
> 
>  *  DROP VIEW t\_empInfo;

注意：删除视图不会影响原表

## 6.视图的注意事项 ##

注意：

*  视图不会独立存储数据，原表发生改变，视图也发生改变。没有优化任何查询性能。
 *  如果视图包含以下结构中的一种，则视图不可更新（查询操作不更新）
    
     *  聚合函数的结果
     *  DISTINCT 去重后的结果
     *  GROUP BY 分组后的结果
     *  HAVING 筛选过滤后的结果
     *  UNION、UNION ALL 联合后的结果

# 七.SQL 语言分类 #

## 1.SQL语言分类 ##

>  *  数据查询语言DQL（Data Query Language）：select、where、order by、group by、having 。
>  *  数据定义语言DDL（Data Definition Language）：create、alter、drop。
>  *  数据操作语言DML（Data Manipulation Language）：insert、update、delete 。
>  *  事务处理语言TPL（Transaction Process Language）：commit、rollback 。
>  *  数据控制语言DCL（Data Control Language）：grant、revoke。