MySQL—02—MySQL的操作

￡神魔★判官ぃ 2023-10-09 10:29 7阅读 0赞

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

# **一、****MySQL的操作** #

### **1.  ****创建与删除数据库** ###

*  **1.1****使用命令创建数据库**

创建一个 test 的数据库，并查看该数据库，以及该数据库的编码。

创建数据库：

create database test default character set utf8;

查看数据库：

show databases;

查看数据库编码：

select schema\_name,default\_character\_set\_name from information\_schema.sch

emata where schema\_name = 'test';

** **

*  **1.2****使用命令删除数据库**

Drop database 数据库名称

** **

### **2. ** 选择数据库  ###

需要在哪个库中创建表需要先选择该数据库。

Use  需要选择的库的名称;

示例: 　　创建一个名称为 bjsxt 的数据库，编码为 utf8。 　　create database bjsxt default character set = utf8; 　　选择该数据库 　　use bjsxt;

### **3.  MySQL** **中的数据类型** ###

*  **3.1****数值类型**

MySQL 支持所有标准 SQL 数值数据类型。

作为 SQL 标准的扩展，MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。

数值类型中的长度 m 是指显示长度，并不表示存储长度，只有字段指定 zerofill 时有用

例如：int(3)，如果实际值是 2，如果列指定了 zerofill，查询结果就是 002，左边用 0 来

填充

*  **3.2****浮点型**

**![1734761-20190917203502299-574573909.png][]**

*  **3.3****字符串型**

**![1734761-20190917203407806-1159419571.png][]**

**　　3.3.1char** **和** **varchar**

(1)char(n) 若存入字符数小于 n，则以空格补于其后，查询之时再将空格去掉。所以 char

类型存储的字符串末尾不能有空格，varchar 不限于此。

(2)char 类型的字符串检索速度要比 varchar 类型的快。

**　　3.3.2varchar** **和** **text**

(1)varchar 可指定 n，text 不能指定，内部存储 varchar 是存入的实际字符数 +1 个字

节（n<=255）或 2 个字节(n>255)，text 是实际字符数 +2 个字节。

(2)text 类型不能有默认值。

(3)varchar 可直接创建索引，text 创建索引要指定前多少个字符。varchar 查询速度快

于 text, 在都创建索引的情况下，text 的索引似乎不起作用。

*  **3.4****日期类型**

** ![1734761-20190917202847811-137501798.png][]**

*  **3.5****二进制数据****(BLOB)**

(1)BLOB 和 TEXT 存储方式不同，TEXT 以文本方式存储，英文存储区分大小写，而 Blob

是以二进制方式存储，不分大小写。

(2)BLOB 存储的数据只能整体读出。3)TEXT 可以指定字符集，BLOB 不用指定字符集。

### **4.  ****创建表与删除表**  ###

**　　4.1****创建表**

**　　4.1.1****通过** **DDL** **语句创建表　　**

示例; 创建一个 employees 表包含雇员 ID，雇员名字，雇员薪水。 create table employees(employee_id int,last_name varchar(30),salary float(8,2)); 查看已创建的表 Show tables;

**　　4.2****删除表**

**　　4.2.1****通过** **DDL** **语句删除表**

示例;
    
    删除 employees 表
    
    drop table employees;

### **5.  使用DDL语句修改** ###

### **5.1使用** **DDL** **语句修改表名** ###

ALTER TABLE 旧表名 RENAME 新表名

**示例:**

创建一个 employees 表包含雇员 ID，雇员名字，雇员薪水。

create table employees(employee\_id int,last\_name varchar(30),salary float(8,2));

将 employees 表名修改为 emp。

alter table employees rename emp;

**5.2****使用** **DDL** **修改列名**

ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 类型

**示例:**

将 emp 表中的 last\_name 修改为 name

alter table emp change column last\_name name varchar(30);

**5.3****使用** **DDL** **语句修改列类型**

ALTER TABLE 表名 MODIFY 列名 新类型

**示例:**

将 emp 表中的 name 的长度指定为 40

alter table emp modify name varchar(40);

**5.4****使用** **DDL** **语句添加列**

ALTER TABLE 表名 ADD COLUMN 新列名 类型

**示例:**

在 emp 表中添加一个新的列为 commission\_pct

alter table emp add column commission\_pct float(4,2);

**5.5****使用** **DDL** **语句删除列**

ALTER TABLE 表名 DROP COLUMN 列名

**示例:**

删除 emp 表中的 commission\_pct

alter table emp drop column commission\_pct;

### **6.  MySQL** **中的约束** ###

**6.1****约束类型**

*   非空约束(not null)
 *   唯一性约束(unique)
 *   主键约束(primary key) PK
 *   外键约束(foreign key) FK
 *   检查约束(目前 MySQL 不支持、Oracle 支持)

**6.2****创建表时添加约束**

查询表中的约束信息

SHOW KEYS FROM 表名

**示例:**

创建 departments 表包含 department\_id 该列为主键且自动增长，department\_name 列不允许重复，location\_id 列不允含有空值。

create table departments(department_id int primary key auto_increment,department_name varchar(30) unique,location_id int not null);

**示例:**

创建 employees 表包含 employees\_id 该列为主键且自动增长，last\_name 列不允许含有空值，email 列不允许有重复不允许含有空值，dept\_id 为外键参照 departments 表的主键。

create table employees(employees\_id int primary key auto\_increment,last\_name varchar(30) not null,email varchar(40) not null unique,dept\_id int,constrain temp\_fk foreign key(dept\_id) references departments(department\_id));

**6.3****修改表 实现约束的添加与删除**

*  **6.3.1****主键约束** 
 *  **6.3.1.1** **添加主键约束** 
 *  ALTER TABLE 表名 ADD PRIMARY KEY(列名)
 *      示例: 将 emp 表中的 employee_id 修改为主键且自动增长 添加主键：alter table emp add primary key(employee_id); 添加自动增长：alter table emp modify employee_id int auto_increment;
 *  **6.3.1.2** **删除主键约束** 
 *  ALTER TABLE 表名 DROP PRIMARY KEY
 *  注意：删除主键时，如果主键列具备自动增长能力，需要先去掉自动增长，然后在删除主键。
 *      示例: 删除 employee_id 的主键约束。 去掉自动增长： alter table emp modify employee_id int; 删除主键：alter table emp drop primary key;

*  **6.3.2****非空约束** 
 *  **6.3.2.1** **添加非空约束** 
 *  ALTER TABLE 表名 MODIFY 列名 类型 NOT NULL
 *      示例: 向 emp 表中的 salary 添加非空约束。 alter table emp modify salary float(8,2) not null;
 *  **6.3.2.2** **删除非空约束** 
 *  ALTER TABLE 表名 MODIFY 列名 类型 NULL
 *      示例: 删除 salary 的非空约束。 alter table emp modify salary float(8,2) null;

*  **6.3.3****唯一约束** 
 *  **6.3.3.1** **添加唯一约束** 
 *  ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE(列名)
 *  **示例:**
 *  向 emp 表中的 name 添加唯一约束。
 *  alter table emp add constraint emp\_uk unique(name);
 *  **6.3.3.2** **删除唯一约束** 
 *  ALTER TABLE 表名 DROP KEY 约束名
 *  **示例** 
 *  删除 name 的唯一约束。
 *  alter table emp drop key emp\_uk;

*  **6.3.4****外键约束** 
 *  **6.3.4.1** **添加外键约束** 
 *  ALTER TABLE 表 名 ADD CONSTRAINT 约 束 名 FOREIGN KEY( 列 名 )
 *  REFERENCES 参照的表名(参照的列名)
 *      示例: 修改 emp 表，添加 dept_id 列。 alter table emp add column dept_id int; 示例: 向 emp 表中的 dept_id 列添加外键约束。 alter table emp add constraint e_fk foreign key(dept_id) references depar tments(department_id);
 *  **6.3.4.2** **删除外键约束** 
 *  删除外键约束有两个步骤,  第一步删除约束,  第二步删除索引;
 *  ALTER TABLE 表名 DROP FOREIGN KEY 约束名
 *  删除外键索引(索引名与约束名相同):
 *  ALTER TABLE 表名 DROP INDEX 索引名
 *      示例:
        删除 dept_id 的外键约束。
        第一步删除外键约束：alter table emp drop foreign key e_fk;
        第二步删除索引： alter table emp drop index e_fk;

### **7.  MySQL** **中的** **DML** **操作**  ###

**DML(Data Manipulation Language)数据操纵语言命令使用户能够查询数据库以及操作已有数据库中的数据。**

**基本的数据操作分成两类四种:检索(查询)和更新(插入、删除、修改)**

**7.1****添加数据****(INSERT)**

**7.1.1****插入数据**

**7.1.1.1** **选择插入**

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

示例 向 departments 表中添加一条数据，部门名称为 market，工作地点 ID 为 1。 insert into departments(department_name,location_id) values("market",1);

**7.1.1.2** **完全插入**

INSERT INTO 表名 VALUES(值 1，值 2，值 3......)

如果主键是自动增长，需要使用 default 或者 null 或者 0 占位。

示例一 向 departments 表中添加一条数据，部门名称为 development，工作地点 ID 为 2。使用default 占位。 insert into departments values(default,"development",2); 示例二 向 departments 表中添加一条数据，部门名称为 human，工作地点 ID 为 3。使用 null 占位。 insert into departments values(null,"human",3); 示例三 向 departments 表中添加一条数据，部门名称为 teaching，工作地点 ID 为 4。使用 0 占位。 insert into departments values(0,"teaching",4);

**7.1.2****自动增长****(auto\_increment)**

MySQL 中的自动增长类型要求：

1.  一个表中只能有一个列为自动增长。
2.  自动增长的列的类型必须是整数类型。
3.  自动增长只能添加到具备主键约束与唯一性约束的列上。
4.  删除主键约束或唯一性约束，如果该列拥有自动增长能力，则需要先去掉自动增长然后再删除约束。

示例 创建一个 emp2 表。包含 id 该列为主键，包含 name，包含 seq_num 要求该列为具备唯一性约束，该列的值自动增长。 create table emp2(id int primary key ,name varchar(30),seq_num int unique auto_increment);
    删除这个表中seq_num列的自动增长
    alter table emp2 modify seq_num int null;

**7.1.3****默认值处理**

在 MySQL 中可以使用 DEFAULT 为字段设定一个默认值。如果在插入数据时并未指定该列的值，那么 MySQL 会将默认值添加到该列中。

**7.1.3.1** **创建表时指定列的默认值**

示例 创建 emp3 表，该表包含 emp_id 主键且自动增长，包含 name，包含 address 该列默认值为”未知”。 create table emp3(emp_id int primary key auto_increment,name varchar(30),address varchar(50) default 'Unknown');

**7.1.3.2** **修改表添加列的默认值**

示例: 修改 emp3 表，添加 job_id 该列默认值为 0 alter table emp3 add column job_id int default 0;

**7.1.3.3** **插入数据时的默认值处理**

如果在插入数据时并未指定该列的值，那么 MySQL 会将默认值添加到该列中。如果是

完全项插入需要使用 default 来占位。

示例 向 emp3 表中添加数据，要求 address 列与 job_id 列使用默认值作为该列的值。 (选择插入) insert into emp3(name) values("admin"); (完全插入) insert into emp3 values(default,"oldlu",default,default);

**7.2****更新数据****(UPDATE)**

UPDATE 表名 SET 列名=值，列名=值 WHERE 条件

**7.2.1mysql** **的** **update** **的特点**

(1)更新的表不能在 set 和 where 中用于子查询;

(2)update 后面可以做任意的查询

示例一 更新 emp3 表中的 id 为 1 的数据，添加 address 为 BeiJing。 update emp3 e set e.address = "BeiJing" where emp_id = 1;

示例二 方式一：更新 emp3 中 id 为 2 的数据，将地址修改为与 id 为 1 用户的地址相同 Oracle:update emp3 e set e.address = (select address from emp3 where emp_id = 1) where e.emp_id = 2; MySQL: update emp3 e ,(select address from emp3 where emp_id = 1)t set e.address= t.address where e.emp_id =2; 方式二：更新 emp3 中 id 为 2 的数据，将地址修改为与 id 为 1 用户的地址相同 update emp3 e set e.address = (select t1.address from (select emp_id, address from emp3)t1 where t1.emp_id = 1 ) where e.emp_id = 2;
    为什么这种方式可以使用呢?他没有违反"更新的表不能在 set 和 where 中用于子查询;"这句话吗? 没有,因为set后的查询,查询的是t1这个表,并不是更新表emp3;

## **7.3****删除数据****(DELETE)**  ##

**7.3.1****使用** **DELETE** **子句**

DELETE FROM 表名 WHERE 条件

示例 删除 emp3 表中 emp_id 为 1 的雇员信息。 delete from emp3 where emp_id = 1

**7.3.2****使用** **TRUNCATE** **清空表**

TRUNCATE TABLE 表名

示例 删除 emp3 表中的所有数据 truncate table emp3;

**7.3.3DELETE** **与** **TRUNCATE** **区别**

• truncate 是整体删除（速度较快）， delete 是逐条删除（速度较慢）；

• truncate 不写服务器 log，delete 写服务器 log，也就是 truncate 效率比 delete 高的原因；

• truncate 是会重置自增值，相当于自增列会被置为初始值，又重新从 1 开始记录，而不是接着原来的值。而 delete 删除以后，自增值仍然会继续累加。

## **7.4查询数据(select)** ##

**增删改查中的查询是非常复杂的, 它有很多约束条件;我们会在下一篇文章中, 着重介绍它;**

# #

## **8.  MySQL** **中的事务处理**  ##

在 MySQL 中，默认情况下，事务是自动提交的，也就是说，只要执行一条 DML 语句

就开启了事物，并且提交了事务

**8.1****关闭** **MySQL** **的事务自动提交**

START TRANSACTION

DML....

COMMIT|ROLLBACK

示例 向 emp3 表中添加一条数据，要求手动提交事务。 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into emp3 values(default,"oldlu",default,default); Query OK, 1 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.01 sec)

## 9.  MySql四种语言的解释 ##

*  DML（data manipulation language）： 它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言 
 *  DDL（data definition language）： DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用 
 *  DCL（Data Control Language）： 是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db\_owner或db\_securityadmin等人员才有权力执行DCL
 *  TCL - Transaction Control Language：事务控制语言，COMMIT - 保存已完成的工作，SAVEPOINT - 在事务中设置保存点，可以回滚到此处，ROLLBACK - 回滚，SET TRANSACTION - 改变事务选项

转载于:https://www.cnblogs.com/EricShen/p/11536960.html

[1734761-20190917203502299-574573909.png]: https://img2018.cnblogs.com/blog/1734761/201909/1734761-20190917203502299-574573909.png
[1734761-20190917203407806-1159419571.png]: https://img2018.cnblogs.com/blog/1734761/201909/1734761-20190917203407806-1159419571.png
[1734761-20190917202847811-137501798.png]: https://img2018.cnblogs.com/blog/1734761/201909/1734761-20190917202847811-137501798.png