[大数据]MySQL基础版本1.0

序章–MySQL介绍以及安装

JavaEE：企业级Java开发 web

• 前端（页面：展示，数据）
• 后台（连接点：连接数据库JDBC，连接前端（控制视图跳转，和前端传递数据））；
• 数据库（存数据）

1、为什么学习数据库？

原因：

• 岗位需求；
• 现在的世界，大数据时代；
• 被迫需求：存数据，去IOE
• 数据库是所有软件体系中最核心的存在

2、什么是数据库

数据库（DB,DataBase）

概念：数据仓库，软件；安装在操作系统之上（window，Linux，Mac、、、、）；SQL,可以存储大量数据；

作用：存储数据，管理数据

3、数据库分类

DBMS:数据库管理系统

关系型数据库（SQL）：

• MySQL，Oracle，Sql Server，DB2，SQLlite。
• 通过表和表之间，行和列之间的关系进行数据的存储，

非关系型数据库(NoSQL )：

NO:not only

• Redis，MongDB;
• 非关系型数据库，对象存储，通过对象的自身的属性来决定；

DBMS(数据库管理系统)

• 数据库的管理软件，科学有效的管理我们的数据。维护和获取数据；
• MySQL本质就是一个数据库管理系统。

4、MySQL简介

• MySQL是一个关系型数据库管理系统；
• MySQL是最好的RDMBS(relational database management system,数据库管理系统)应用软件之一。

特点：体积小，速度快，总体拥有成本低；

中小型网站或者大型网站

5、安装MySQL

安装建议：

1.尽量不要使用exe安装，

2.尽可能使用压缩包安装

教程：https://www.cnblogs.com/hellokuangshen/p/1024958.html

步骤：

1、解压

2、把这个包放到自己的电脑环境目录下

3、配置环境变量

4、新建MySQL配置文件ini

[mysqld]
#目录一定要换成自己的
basedir=F:\\MySQL\\mysql-8.0.22-winx64  
datadir=F:\\MySQL\\mysql-8.0.22-winx64\\Data 
port=3306
skip-grant-tables

5、启动管理员模式下的CMD，运行所有的命令

6、安装MySQL服务

7、初始化数据库文件

8、启动MySQL，进去修改密码

9、进入MySQL，通过命令行，修改密码（sql语句后面一定要加上分号）

10、注释掉ini文件中的跳过密码

11、重启MySQL，连接测试，如果连接成功就Ok了

建议：

可能遇到的问题：

1、缺少dll组件

2、命令输入出错（大小写问题在MySQL8.0中比较重要）

sc delete mysql;清空服务

6、安装Navicate

https://blog.csdn.net/WeiHao0240/article/details/110950197

https://blog.csdn.net/qq_42767653/article/details/106118313

所有建立的数据库都是会在data目录下面

新建一个数据库SCHOOL

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-z5segQoM-1632238468605)(C:\Users\Administrator\Desktop\QQ截图20210215180023.png)]

注意：

1. 其中的字符集是（utf8）以及排序规则（utf8_general_ci）;
2. Navicate 的每一个执行操作，本质上都是对应一个sql语句，可以在软件的历史日志中查看。

新建一张表student（id,name）

7、连接数据库

命令行连接

mysql -uroot -p123456 --连接数据库

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码'; --修改用户密码

flush privileges;--刷新权限

-------------------------------------
--所有的语句都需要“;”结尾
show databases;--查看所有的数据库
use database;--使用数据库

show tables;--查看所有的表
describe table;--显示数据库中所有的表的信息

create database test;--创建一个test数据库

exit;--退出连接

--  --代表单行注释（SQL的本来的注释）
/*（sql的多行注释）
hello	
*/

数据库语言—本质CRUD（增删改查）

DDL 定义语言

DML 操作语言

DQL 查询语言

DCL 控制语言

注意点：

• ``，字段名使用table键进行包裹；
• 注释：“–”或者是/**/;
• sql关键字大小写不敏感，建议大家写小写；
• 所有符号全部用英文。

1、MySQL8.0的新特性

2、数据库的基本操作

操作数据库>操作数据库中表>操作数据库中表的数据

注意：MySQL不区分大小写（8.0之前版本）

2.1、操作数据库

注意：其中"[]"代表的是可写可不写

2.1.1、创建数据库

create database [if not exists] database_name;

--案例
show databases;--查看所有的数据库

| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------

create database if not exists `db1`;--创建数据库db1

+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

2.1.2、使用数据库

use `database_name`;

mysql> use `db1`;
Database changed

注意事项：必须使用tab键上方的反引号用来修饰数据库名、表名、列名…否则会出现语法错误

> 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''db1'' at line 1
/*
> 1064 - 您的 SQL 语法出现错误：检查与 MySQL 服务器版本对应的手册，了解在 1 行"db1"附近使用的正确语法
*/
--1064语法错误

反引号与单引号，双引号的区别

https://blog.csdn.net/CHCH998/article/details/105611745

作为表名或者是字段名，最好还是加上反引号。而作为值，加上单引号即可。（大概率上会提高效率）

2.1.3、删除数据库

drop database [if exists] test;

show databases;--查看所有的数据库

| Database           |--命令行界面编写后的结果
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------

drop database if exists db1;
> OK
> 时间: 0.009s--navicate 图形管理页面

show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

注意：使用drop命令删除数据库时要谨慎，MySQL不会提示。如果删除数据库的话，数据库中所有的数据表以及数据也会一同删除，而且不能恢复。

2.1.4、查看数据库

show databases;--查看所有的数据库
show create database database_name;--查看创建数据库的语句，默认字符集是utf8

注意事项：只能查看创建的语句，暂时无法查看删除的语句，会出现语法错误。

eg:show delete database database_name;

mysql> show create database db1;
+----------+-------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                 |
+----------+-------------------------------------------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

学习思路：

• 对照可视化工具历史记录查看；
• 固定的语法或者关键字必须要记住。

学习案例：

--命令行界面

--登录数据库
C:\Users\Administrator>mysql -u root -p
Enter password: ******
--登录成功则会显示一下界面
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

具体语句：

--查看当前所有数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

--创建数据库db_demo
mysql> create database if not exists db_demo;
Query OK, 1 row affected (0.05 sec)

--查看当前所有数据库  
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db_demo            |
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

--选择当前的数据库
mysql> use `db_demo`;
Database changed
--不管是在命令行界面还是在navicate中，反引号都可以忽略。原理未知。

--查看数据库信息

mysql> show create database db_demo\G
*************************** 1. row ***************************
       Database: db_demo
Create Database: CREATE DATABASE `db_demo` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */
1 row in set (0.00 sec)

--删除数据库
mysql> drop database if exists db_demo;
Query OK, 0 rows affected (0.01 sec)

--查看所有的数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

2.2、数据库的列类型

数值

tinyint --十分小的数据	1个字节
smallint --较小的数据	2个字节
mediumint --中等大小的数据	3个字节

int 	--标准的整数		4个字节
bigint	--较大的数据		8个字节

float	--单精度浮点数	4个字节
double	--双精度浮点型	8个字节

decimal	--字符串形式的浮点数(适用于金融) 	

注意：int以及decimal

字符串

char	--字符串固定大小的0-255
varchar	--可变字符串	0-65535	常用的变量 String
tinytext --微型文本	2^8 -1
text 	--字符串 2^16 -1	保存大文本

时间日期

data	--YYYY-MM-DD，日期格式
time	--HH:mm:ss	时间格式
datatime --YYYY-MM-DD HH:mm:ss 最常用的时间格式
timestamp --时间戳，全球统一，较常用。1970.1.1到现在的毫秒数！
year	--年份表示

null

• 没有值，未知
• >注意，不要使用null进行运算，结果为null

2.3、数据库的字段属性（重点）

Unsigned:

• 无符号的整数；
• 声明了该列不能声明为负数；

zerofill：

• 0填充的
• 不足的位数，使用0来填充。
  • eg:int(3) 写一个5，那么现实的是005

自增：

• 通常理解为自增，自动在上一条记录的基础上+1（默认）；
• 通常用来设计唯一的主键，必须是整数类型；
• 可以自定义设计主键自增的起始值和步长；

非空 not null

• 假设设置为not null，如果不给他赋值，就会报错！（相当于添加一个限制）；
• Null，如果不填写值，默认就是null.

默认：

• 设置默认的值；
• sex，如果默认的值为“男”，那么即使不赋值。初始值还是“男”；

3、数据表的基本操作

3.1、创建数据表

3.1.1、语法格式

CREATE TABLE [IF NOT EXISTS] `表名`(
	`字段名` 数据类型 [属性] [注释],
    `字段名` 数据类型 [属性] [注释],
    ......
    `字段名` 数据类型 [属性] [注释]
)[表类型][字符集设置][注释];

注意事项：

• 表名不区分大小写，但是不能使用SQL语言中的关键字，如drop，alert等
• 数据表中的每一列（字段）的名称和数据类型，如果创建多列，需要使用逗号隔开。

案例

员工表tb_emp1结构

--创建数据库
create database if not exists test_db;

--选择数据库
use test_db;

--创建tb_emp1
create table if not exists tb_emp1(
		`id` int(11) comment '员工编号',
		`name` varchar(25) comment '员工名称',
		`deptId` int(11) comment '所在部门编号',
		`salary` float comment '工资'
);
> OK
> 时间: 0.462s

--查看数据表是否成功
show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_emp1           |
+-------------------+

注意：使用英文模式下面的“()”。而且表名和字段名尽量使用==（反引号``）==来括起来。

3.1.2、使用主键约束

• 主键：又称主码，是表中一列或多列的组合，即主键可以是单一的一列也可以是多列，但是常用的还是单列作为主键约束，主键类型分为单字段主键以及多字段联合主键。
• 而主键约束，要求主键列的数据唯一，并且不为空。
• 主键可以结合外键来定义不同的数据表之间的关系，以此来加快数据库查询的速度。但是现在不推荐使用外键查询，推荐使用联表查询。

单字段主键

注意：单字段主键分为两种：

1.定义列的同时指定主键

2.定义列之后再指定主键

--定义列的同时指定主键
字段名	数据类型 primary key [默认值]

--定义一个数据表tb_emp2,主键是id
create table if not exists `tb_emp2`(
	`id` int(11) primary key comment '学生id',
	`name` varchar(25) comment '学生姓名',
	`deptId` int(11) comment '部门编号',
	`salary` float comment '工资'
);

--定义完列后再指定主键
[constraint <约束名>] primary key(字段名)

--定义一个数据表tb_emp3,主键是id
create table if not exists `tb_emp3`(
	`id` int(11) comment '学生id',
	`name` varchar(25) comment '学生姓名',
	`deptId` int(11) comment '部门编号',
	`salary` float comment '工资',
    primary key(id)
);

多字段主键

多字段主键由多个字段联合组成。

--语法规则
primary key(字段1,字段2....字段n)

--定义一个数据表tb_emp4，假设表中没有主键id，为了唯一确定一个员工，可以把name,deptId联合起来作为主键
create table if not exists `tb_emp4`(
	`name` varchar(25) comment '员工姓名',
	`deptId` int(11) comment '部门编号',
	`salary` float comment '工资',
	primary key(`name`,`deptId`)
);

+-------------------+
| Tables_in_db_demo |
+-------------------+
| tb_emp2           |
| tb_emp3           |
| tb_emp4           |
+-------------------+

结果

3.1.3、使用外键约束（建议不使用）

1. 外键是用来在两个表的数据之间建立连接，可以是单列，也可以是多列。
2. 一个表可以有一个或者多个外键。
3. 外键对应的参照完整性，一个表的外键可以是空值
4. 若外键不为空值，则每一个外键必须等于另一个表中主键的某个值。
5. 外键可以不是本表的主键，但是要对应另外一个表的主键。（同4相同概念）

空值和null的区别

https://blog.csdn.net/Crystalqy/article/details/114088403

语法格式

[constraint <外建名>] foreign key 字段名1.... references <主表名>(主键列1...)
--[]可以写，也可以不写约束名

• 主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表；
• 从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的表就是从表。

子表的外键必须关联父表的主键，且数据类型必须相同。

注意：

• 外键的主要作用就是保证数据引用的完整性，定义外键后，不允许删除在另一个表中具有关联关系的字段。
• 如果要删除主表，要事先断开与从表之间的关联—删除外键。

eg：部门表td_dept的主键是id，而员工表tb_emp4中有一个字段（deptId）与这个id关联。

? sql语句

--创建部门表
create table if not exists `tb_dept`(
	`id` int(10) comment '部门编号',
	`name` varchar(20) comment '部门姓名',
	`location` varchar(50) comment '部门地址',
	primary key(`id`)
);

--创建员工表
--创建表时，添加外键
create table if not exists `tb_emp`(
	`id` int(10) comment '员工编号',
	`name` varchar(25) comment '员工姓名',
	`deptId` int(10) comment '部门编号',
	`salary` float comment '工资',
	constraint fk_emp_dept foreign key(`deptId`) references `tb_dept`(`id`)
    --constraint fk_emp_dept
);
/*注意，其中fk_emp_dept是外键约束名
	`deptId`是从表的关联主表的字段
	`tb_tept`是主表名
	`id`是主表关联从表的字段
*/

--创建表后，添加外键
alter table `tb_emp` add foreign key(`deptId`) references `tb_dept`(`id`);--属于添加字段

3.1.4、使用非空约束

非空约束（not null constraint）指的是字段的值不能为空。

对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统就会报错。

--语法格式
字段名 数据类型 not null

--定义数据表tb_emp5，指定员工的名称不能为空
#定义name不能为空
create table if not exists `tb_emp5`(
	`id` int(10) comment '员工编号',
	`name` varchar(25) not null comment '员工姓名',
	`deptId` int(10) comment '部门编号',
	`salary` float comment '工资',
	primary key(`id`)
);

使用非空约束，用户在添加数据时没有指定值

3.1.5、使用唯一约束

唯一性约束（unique constraint）要求该列唯一，允许为空，但只能出现一个空值。

作用：可以确保一列或者多列不会出现重复值。

1、定义完列之后，直接指定唯一约束。

--语法格式：字段名	数据类型	unique

--定义数据表tb_dept2，指定部门的名称唯一
#指定部门名称唯一
create table if not exists `tb_dept2`(
	`id` int(10) unique not null comment '部门编号',
	`name` varchar(25) unique comment '部门名称',
	`location` varchar(50) comment '部门地址',
	primary key(`id`)
)
> OK
> 时间: 0.037s

2、定义完所有列之后，指定唯一约束。

--语法格式：[constraint <约束名>] unique(字段名)
#定义数据表tb_dept2,指定部门名称唯一
create table if not exists `tb_dept2`(
	`id` int(10) comment '部门编号',
	`name` varchar(25) comment '部门名称',
	`location` varchar(50) comment '部门地址',
	constraint sth unique(`name`)
)
> OK
> 时间: 0.036s
--sth指的是约束名，constraint sth是可以省略的

• 如果重复就会出现1062错误，重复使用。
• 观察图片2和图片3可得，如果出现约束名，直接是以约束名替代字段名。

3、unique和primary key的区别

• 一个表中可以有多个字段声明为unique，但只能有一个primary声明。
• 声明为primary key 的字段不允许有空值，但是声明unique的字段可以有空值

4、与hashSet()一样，如果是在JDBC中设置如何，是否会繁琐

3.1.6、使用默认约束

默认约束（default constraint）就是指定某列的默认值。

eg：age 的默认值都设置为22，则新增一个，年龄还是22

--语法格式
字段名 数据类型 default 默认值

--定义数据表tb_emp6,指定员工的部门编号默认为1111
create table if not exists `tb_emp6`(
	`id` int(10) comment '员工编号',
	`name` varchar(25) not null comment '员工姓名',
	`deptId` int(10) default 1111 comment '部门编号',
	`salary` float comment '员工工资',
	primary key(`id`)
)
> OK
> 时间: 0.038s

3.1.7、设置表的属性值自动增加

• 在MySQL中，auto_increment的初始值为1，每次新增一条记录，字段值自动加一；
• 一个表中，只能有一个字段使用auto_incement，且该字段必须为主键或者是主键的一部分；
• auto_increment约束的字段可以是任何整数类型（tinyint,smallint,int,bigint）

--语法格式
字段名 数据类型 auto_increment

--定义数据表tb_emp7，指定员工的编号自动递增
create table if not exists `tb_emp7`(
	`id` int(10) auto_increment comment '员工编号',
	`name` varchar(25) not null comment '员工姓名',
	`deptId` int(10) comment '部门编号',
	`salary` float comment '工资',
	PRIMARY KEY(`id`)
)
> OK
> 时间: 0.031s

--插入记录
insert into `tb_emp7` (`name`,`salary`) values('zhangsan',1000),('lisi',1500),('zhaowu',2000);
/*
插入数据，使用insert into
插入字段的值，使用单引号，反引号是作为字段名、表名以及库名还有保留关键字。
*/


--查询数据
select * from tb_emp7;
+----+----------+--------+--------+
| id | name     | deptId | salary |
+----+----------+--------+--------+
|  1 | 1        |      1 |      1 |
|  2 | 2        |      2 |      2 |
|  3 | 3        |      3 |      3 |
|  4 | zhangsan |   NULL |   1000 |
|  5 | lisi     |   NULL |   1500 |
|  6 | zhaowu   |   NULL |   2000 |
+----+----------+--------+--------+
6 rows in set (0.00 sec)

id是自增，即使是设置为0，也会在初始数据为1的情况下，自增1后再自增1，回归设定好的数值。

3.2、查看数据表结构

在MySQL中，查看表结构，可以使用describe和show create table语句

3.2.1、查看表基本结构 语句–describe

describe/desc语句可以查看表的字段信息，其中包括字段名、字段数据类型、是否为主键、是否有默认值等。

--语法格式
describe 表名;/ desc 表名；

--分别使用describe以及desc命令查看表tb_dept 和 tb_emp的表结构
mysql> describe `tb_dept`;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> desc `tb_emp`;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | YES  |     | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int         | YES  | MUL | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

各个字段的含义：
NULL：表示该列是否可以储存null值

Key:表示该列是否已经编制索引。

• 其中，PRI代表的该列是主键或者是主键的一部分；
• UNI表示该列是unique索引的一部分；
• MUL表示在列中某个给定值允许出现多次。

Default:表示该列是否有默认值，有的话则指定默认值是多少

Extra:表示可以获取的与给定列有关的附加信息，例如：auto_increment

3.2.2、查看表详细结构语句show create table

show create table语句可以用来显示创建表时的语句----create table 语句

--语法格式
show create table table_name\G

注意：

• 使用show create table table_name 语句，可以查看表创建时候的详细语句，还可以查看存储引擎和字符编码。
• 如果不加“\G”参数，显示结果可能会非常杂乱，加上“\G”之后，可以使显示结果整洁。
• 注意，\G或者是\g本身原理相当于结束命令（send commander to server）。所以\G和\g之后就不需要加上分号了
• \G只能在命令行界面使用，在navicate中暂时无法使用

对比\g，\G的区别以及作用

https://blog.csdn.net/daofengsuoxiang/article/details/103067025

--使用show create table命令查看表tb_emp1的详细信息
mysql> show  create table `tb_emp7`;

+---------+-----------------------------------------------------------------------------+
| Table   | Create Table 
+---------+-----------------------------------------------------------------------------+
| tb_emp7 | CREATE TABLE `tb_emp7` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '员工编号',
  `name` varchar(25) NOT NULL COMMENT '员工姓名',
  `deptId` int DEFAULT NULL COMMENT '部门编号',
  `salary` float DEFAULT NULL COMMENT '工资',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8               
+---------+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)

--使用参数\G查看
mysql> show create table `tb_emp7`\G
*************************** 1. row ***************************
       Table: tb_emp7
Create Table: CREATE TABLE `tb_emp7` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '员工编号',
  `name` varchar(25) NOT NULL COMMENT '员工姓名',
  `deptId` int DEFAULT NULL COMMENT '部门编号',
  `salary` float DEFAULT NULL COMMENT '工资',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

3.3、修改数据表

MySQL使用alter table语句修改表。

常用的修改表操作包括：

1. 修改表名；

2. 修改字段数据类型或字段名；

3. 增加和删除字段；

4. 修改字段的排列位置；

5. 更改表的存储引擎；

6. 删除表的外键约束等等

3.3.1、修改表名

--语法格式
alter TABLE 旧表名 rename as/to 新表名

--将表tb_dept2改名为tb_deptment2
--查看数据库中所有的表。
mysql> show tables;
+-------------------+
| Tables_in_db_demo |
+-------------------+
| tb_dept           |
| tb_dept2          |
| tb_dept3          |
+-------------------+
10 rows in set (0.05 sec)

--修改表名
mysql> alter table `tb_dept2` rename `tb_deptment2`;
Query OK, 0 rows affected (0.48 sec)

mysql> alter table `tb_deptment2` rename to `tb_dept2`;
Query OK, 0 rows affected (0.03 sec)

mysql> alter table `tb_dept2` rename as `tb_deptment2`;
Query OK, 0 rows affected (0.03 sec)

--查看表结构
mysql> desc `tb_dept2`;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| name     | varchar(25) | YES  | UNI | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> desc `tb_deptment2`;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| name     | varchar(25) | YES  | UNI | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

注意：

• 其中修改表名，既可以加上to/as，也是可以直接省略掉；
• 修改表名是alter，而不是alert；
• 修改表名但是不会修改表的基本结构（修改前和修改后的表结构都是相同的）。

3.3.2、修改字段的数据类型

修改数据类型就是将字段的数据类型转换成另外的数据类型。

--语法格式
alter table table_name modify 字段名 数据类型

其中table_name，指的是要修改字段数据类型的所属的表
字段名 ：指的是要修改的字段
数据类型：指的是要修改后的数据类型

--将数据表tb_dept中name字段的数据类型由varchar(20)，修改为varchar(30)
--查看tb_dept的表结构
mysql> desc `tb_dept`;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

--修改tb——dept中name字段的数据类型
mysql> alter table `tb_dept` modify `name` varchar(30);
Query OK, 0 rows affected (0.44 sec)
Records: 0  Duplicates: 0  Warnings: 0

--查看表结构
mysql> desc `tb_dept`;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(30) | YES  |     | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

3.3.3、修改字段名

--语法格式
alter table table_name change 旧字段名 新字段名 新数据类型;

• 其中，“旧字段名”指的是修改前的字段名；
• “新字段名”指的是修改后的字段名；
• “新数据类型”指修改后的数据类型，如果不需要修改数据类型，那么就将新数据类型设置成与原来的一样，但是数据类型不能为空。

--将数据表tb_dept中的location字段名称改为loc,数据类型保持不变
--查看tb_dept中的表结构
mysql> desc `tb_dept`;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(30) | YES  |     | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

--修改字段名
mysql> alter table `tb_dept` change `location` `loc` varchar(50);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

--查看表结构
mysql> desc `tb_dept`;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| loc   | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

1、change 和modify修改数据类型对比

change 也是可以修改数据类型（保证两个字段名相同即可）

--查看表结构
mysql> desc tb_dept;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| location | varchar(30) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

--使用modify修改字段数据类型
mysql> alter table `tb_dept` modify `location` varchar(50);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

--查看表结构
mysql> desc `tb_dept`;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

--使用change命令修改字段的数据类型
mysql> alter table `tb_dept` change `location` `location` varchar(30);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

--查看表结构
mysql> desc `tb_dept`;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| location | varchar(30) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

2、注意：change 和modify的区别

https://blog.csdn.net/qq_37618361/article/details/104778615

相同点：都可以改变表中字段的数据类型；

不同点：

• change既可以修改字段的数据类型，同样也是可以修改字段名。而且change要修改数据类型（保证字段名不变）的情况下，需要写两次字段名；
• 而modify修改字段的数据类型，只需要写出一次字段名。

注意事项：由于不同类型的数据在机器中的存储的方式以及长度并不相同。所以，修改数据类型可能会影响到数据表中已有的数据记录。因此，当数据库表中已经有数据的时候，不要轻易的修改数据类型。

3.3.4、添加字段

--语法格式
alter table <表名> add <新字段名> <数据类型> [约束条件] [first | after 已经存在的字段名];

• 其中，<>代表的必选项，而[]是可选项。
• “first”为可选参数，其作用是将新添加到的字段设置为表的第一个字段；
• “after”也是可选参数，其作用是将新添加的字段放置到指定的 “已存在的字段名” 后面；
• 如果没有 “first” 和 “after 已经存在的字段名" ，则新添加的字段设置为数据表的最后列。

1、添加无完整性约束条件的字段

--在表tb_dept中添加一个没有完整性约束的int类型的字段---managerId（部门经理编号）
mysql> alter table `tb_dept` add `managerId` int(10);
Query OK, 0 rows affected, 1 warning (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 1

--查看表结构
mysql> desc `tb_dept`;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int         | NO   | PRI | NULL    |       |
| name      | varchar(20) | YES  |     | NULL    |       |
| location  | varchar(30) | YES  |     | NULL    |       |
| managerId | int         | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

2、添加有完整性约束条件的字段

--在数据表tb_dept中添加一个不能为空的varchar(12)类型的字段column1
mysql> alter table `tb_dept` add `column1` varchar(12) not null;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

--查看表结构
mysql> desc `tb_dept`;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int         | NO   | PRI | NULL    |       |
| name      | varchar(20) | YES  |     | NULL    |       |
| location  | varchar(30) | YES  |     | NULL    |       |
| managerId | int         | YES  |     | NULL    |       |
| column1   | varchar(12) | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

3、在表的第一列添加一个字段

--在表tb_dept中添加一个int(11)类型的字段column2
mysql> alter table `tb_dept` add  `column2` int(11) first;
Query OK, 0 rows affected, 1 warning (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 1

--查看表结构
mysql> desc `tb_dept`;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| column2   | int         | YES  |     | NULL    |       |
| id        | int         | NO   | PRI | NULL    |       |
| name      | varchar(20) | YES  |     | NULL    |       |
| location  | varchar(30) | YES  |     | NULL    |       |
| managerId | int         | YES  |     | NULL    |       |
| column1   | varchar(12) | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

4、在表的指定列之后添加一个字段

--在数据表tb_dept中的name列后，添加一个int(11)类型的字段column3
mysql> alter table `tb_dept` add `column3` int(11) after `name`;
Query OK, 0 rows affected, 1 warning (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 1

--查看表结构
mysql> desc `tb_dept`;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| column2   | int         | YES  |     | NULL    |       |
| id        | int         | NO   | PRI | NULL    |       |
| name      | varchar(20) | YES  |     | NULL    |       |
| column3   | int         | YES  |     | NULL    |       |
| location  | varchar(30) | YES  |     | NULL    |       |
| managerId | int         | YES  |     | NULL    |       |
| column1   | varchar(12) | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

3.3.5、删除字段

删除字段是将字段从数据表中移除。（不知道备份是否有用，）

--语法格式
alter table 表名 drop 字段名;

--删除表tb_dept中的column2字段
mysql> alter table `tb_dept` drop `column2`;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

--查看表结构
mysql> desc `tb_dept`;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int         | NO   | PRI | NULL    |       |
| name      | varchar(20) | YES  |     | NULL    |       |
| column3   | int         | YES  |     | NULL    |       |
| location  | varchar(30) | YES  |     | NULL    |       |
| managerId | int         | YES  |     | NULL    |       |
| column1   | varchar(12) | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

3.3.6、修改字段的排列位置

数据表的结构在创建的时候就以及确定（字段在表中的排列顺序以及确定），但是表的结构 不是不可以改变的。

--语法格式
alter table 表名 modify 字段1 数据类型 first |after 字段2;

其中，“字段1”指的是要修改位置的字段；

“first” 为可选参数，指将 “字段1”修改为表的第一个字段；

“after 字段2”指的是将 “字段1” 插入到 ”字段2“ 后面。

1、修改字段为表的第一个字段

--将数据表tb_dept中的column1字段修改为表的第一个字段
mysql> alter table `tb_dept` modify `column1` varchar(12) first;
Query OK, 0 rows affected (0.49 sec)
Records: 0  Duplicates: 0  Warnings: 0

--查看表结构
mysql> desc `tb_dept`;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| column1   | varchar(12) | YES  |     | NULL    |       |
| id        | int         | NO   | PRI | NULL    |       |
| name      | varchar(20) | YES  |     | NULL    |       |
| column3   | int         | YES  |     | NULL    |       |
| location  | varchar(30) | YES  |     | NULL    |       |
| managerId | int         | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

2、修改字段到表的指定列之后

--将数据表tb_dept中的column1字段插入到location字段后面
mysql> alter table `tb_dept` modify `column1` varchar(12) after `location`;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

--查看表结构
mysql> desc `tb_dept`;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int         | NO   | PRI | NULL    |       |
| name      | varchar(20) | YES  |     | NULL    |       |
| column3   | int         | YES  |     | NULL    |       |
| location  | varchar(30) | YES  |     | NULL    |       |
| column1   | varchar(12) | YES  |     | NULL    |       |
| managerId | int         | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

3.3.7、更改表的存储引擎

MySQL中主要的存储引擎有MyISAM、InnoDB、MEMORY(HEAP)、BDB、FEDERATED等。

可以使用show engines;语句查询系统支持的存储引擎。

MySQL支持的存储引擎

--更改表的存储引擎，语法格式
alter table 表名 engine =更改后的存储引擎名;

--将数据表tb_dept的存储引擎修改为MyISAM
--查看表tb_dept 的存储引擎
mysql> show create table `tb_dept`\G
*************************** 1. row ***************************
       Table: tb_dept
Create Table: CREATE TABLE `tb_dept` (
  `id` int NOT NULL COMMENT '部门编号',
  `name` varchar(20) DEFAULT NULL,
  `column3` int DEFAULT NULL,
  `location` varchar(30) DEFAULT NULL,
  `column1` varchar(12) DEFAULT NULL,
  `managerId` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8--默认的存储引擎是InnoDB
1 row in set (0.00 sec)

--修改存储引擎类型
mysql> alter table `tb_dept` engine=MyISAM;
ERROR 3776 (HY000): Cannot change table's storage engine because the table participates in a foreign key constraint.

----将数据表tb_dept的存储引擎修改为MyISAM
--查看表tb_emp3 的存储引擎
mysql> show create table `tb_emp3`\G
*************************** 1. row ***************************
       Table: tb_emp3
Create Table: CREATE TABLE `tb_emp3` (
  `id` int NOT NULL COMMENT '学生id',
  `name` varchar(25) DEFAULT NULL COMMENT '学生姓名',
  `deptId` int DEFAULT NULL COMMENT '部门编号',
  `salary` float DEFAULT NULL COMMENT '工资',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8--可以看到默认的存储引擎是InnoDB
1 row in set (0.00 sec)

--修改存储引擎
mysql> alter table `tb_emp3` engine=MyISAM;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table `tb_emp3`\G
*************************** 1. row ***************************
       Table: tb_emp3
Create Table: CREATE TABLE `tb_emp3` (
  `id` int NOT NULL COMMENT '学生id',
  `name` varchar(25) DEFAULT NULL COMMENT '学生姓名',
  `deptId` int DEFAULT NULL COMMENT '部门编号',
  `salary` float DEFAULT NULL COMMENT '工资',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8--修改过后的存储引擎是MyISAM
1 row in set (0.00 sec)

3.3.8、删除表的外键约束

对于数据库中定义的外键，如果不需要可以直接删除。删除之后，就会解除主表和从表间的关联关系。

--语法格式
alter table 表名 drop foreign key 外键约束名;

--删除数据表tb_emp8中的外键约束
--首先创建表tb_emp8，创建外键关联tb_dept表的主键id
 create table if not exists `tb_emp8`(
		`id` int(11) not null auto_increment comment '员工编号',
		`name` varchar(20) not null comment '员工姓名',
		`deptId` int(11) not null comment '部门编号',
		`salary` float not null comment '工资',
		primary key(`id`)
 );
 
 --添加外键（忘记创建的时候加上外键）
 alter table `tb_emp8` add constraint fk_emp_dept2 foreign key(`deptId`) references `tb_dept`(`id`);
 
 --查看表tb_emp8的表结构
 mysql> show create table `tb_emp8`\G
*************************** 1. row ***************************
       Table: tb_emp8
Create Table: CREATE TABLE `tb_emp8` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '员工编号',
  `name` varchar(20) NOT NULL COMMENT '员工姓名',
  `deptId` int NOT NULL COMMENT '部门编号',
  `salary` float NOT NULL COMMENT '工资',
  PRIMARY KEY (`id`),
  KEY `fk_emp_dept2` (`deptId`),
  CONSTRAINT `fk_emp_dept2` FOREIGN KEY (`deptId`) REFERENCES `tb_dept` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

--删除外键
alter table `tb_emp8` drop foreign key `fk_emp_dept2`;

--查看表结构
mysql> show create table `tb_emp8`\G
*************************** 1. row ***************************
       Table: tb_emp8
Create Table: CREATE TABLE `tb_emp8` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '员工编号',
  `name` varchar(20) NOT NULL COMMENT '员工姓名',
  `deptId` int NOT NULL COMMENT '部门编号',
  `salary` float NOT NULL COMMENT '工资',
  PRIMARY KEY (`id`),
  KEY `fk_emp_dept2` (`deptId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

3.4、删除数据表

在删除表的同时，表的定义和表中所有的数据都会被删除。因此，在删除操作前，最好对表中的数据做一个备份。

3.4.1、删除没有被关联的表

--语法格式
drop table [if exists] 表1,表2,表3......;

--删除数据表tb_dept2
mysql> drop table if exists `tb_dept2`;
Query OK, 0 rows affected (0.06 sec)

--查看表是否存在
mysql> show tables;
+-------------------+
| Tables_in_db_demo |
+-------------------+
| tb_dept           |
| tb_dept3          |
......省略部分内容.....
+-------------------+
11 rows in set (0.00 sec)

注意：

• 所有的创建和删除操作尽量加上判断 [if (not) exists]，以免报错。
• if exists 用于判断表是否存在。加上该参数，即使表不存在，SQL也是会继续执行，但是会发出warning
• 可以同时删除多个表，只需要将表的名字依次写在后面即可，相互之间用逗号隔开。

3.4.2、删除被其他表关联的表

在数据表之间存在外键关联的情况下，如果直接删除父表，结果会显示错误；

原因是直接删除将破坏表的参照完整性。

--tb_emp9,tb_emp8与表tb_dept关联
--删除表tb_dept
mysql> drop table if exists `tb_dept`;
ERROR 3730 (HY000): Cannot drop table 'tb_dept' referenced by a foreign key constraint 'fk_emp_dept' on table 'tb_emp'.

--直接删除表，破坏表的参照完整性。出现3730错误

解决办法：

1、如果必须要删除主表，则直接删除子表（与主表关联），再删除主表。—结果：同时删除主表和从表数据

2、保留子表，单独删除主表。将从表的外键约束条件删除，即可删除主表。

--首先创建两个关联表
--创建主表
 create table if not exists `tb_dept2`(
		`id` int(10) auto_increment not null comment '部门编号',
		`name` varchar(20) not null comment '部门姓名',
		`location` varchar(50) not null comment '部门地址',
		primary key(`id`)
 )
> OK
> 时间: 0.03s


--创建从表
 create table if not exists `tb_emp10`(
		`id` int(10) auto_increment not null comment '员工编号',
		`name` varchar(20) not null comment '员工姓名',
		`deptId` int(10) not null comment '部门编号',
		`salary` float not null comment '员工工资',
		primary key(`id`),
		constraint `fk_emp_dept2` foreign key(`deptId`) references `tb_dept2`(`id`)
 )
> OK
> 时间: 0.426s

--查看表结构(查看表的外键约束)
mysql> show create table `tb_emp10`\G
*************************** 1. row ***************************
       Table: tb_emp10
Create Table: CREATE TABLE `tb_emp10` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '员工编号',
  `name` varchar(20) NOT NULL COMMENT '员工姓名',
  `deptId` int NOT NULL COMMENT '部门编号',
  `salary` float NOT NULL COMMENT '员工工资',
  PRIMARY KEY (`id`),
  KEY `fk_emp_dept2` (`deptId`),
  CONSTRAINT `fk_emp_dept2` FOREIGN KEY (`deptId`) REFERENCES `tb_dept2` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

--直接删除主表tb_dept2
mysql> drop table if exists `tb_dept2`;
ERROR 3730 (HY000): Cannot drop table 'tb_dept2' referenced by a foreign key constraint 'fk_emp_dept2' on table 'tb_emp10'.
/*
存在外键时，主表不能直接删除
*/

--删除外键约束
mysql> alter table `tb_emp10` drop foreign key `fk_emp_dept2`;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

--删除主表
mysql> drop table if exists `tb_dept2`;
Query OK, 0 rows affected (0.01 sec)

--查看数据库中所有表是否有tb_dept2
mysql> show tables;
+-------------------+
| Tables_in_db_demo |
+-------------------+
| tb_dept           |
| tb_dept3          |
| tb_emp            |
| tb_emp10          |
.....省略其他内容......
| tb_emp8           |
| tb_emp9           |
+-------------------+
12 rows in set (0.00 sec)

3.5、综合案例----数据表的基本操作

案例目的：创建、修改和删除数据表，掌握表的基本操作。

创建数据库company，创建表offices以及employees，完成基本操作

offices表结构

employees表结构

3.5.1、连接MySQL

--打开Windows命令行，连接MySQL
C:\Users\Administrator>net start mysql
请求的服务已经启动。

--输入登录用户名以及密码
C:\Users\Administrator>mysql -uroot -p123456
--或者是 mysql -u root -p，之后再输入密码
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

3.5.2、创建数据库

--创建数据库company
mysql> create database if not exists `company`;
Query OK, 1 row affected (0.01 sec)

--选择数据库
mysql> use `company`;
Database changed

3.5.3、创建表offices

--创建offices表
create table if not exists `offices`(
		`officeCode` int(10) not null unique comment '办公代码',
		`city` varchar(50) not null comment '公司所在城市',
		`address` varchar(50) comment '公司详细地址',
		`country` varchar(50) not null comment '公司所在国家', 
		`postalCode` varchar(15) unique comment '邮政编码',
		primary key(`officeCode`)
)
> OK
> 时间: 0.466s

--查看数据库中的表
show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| offices           |
+-------------------+
1 row in set (0.00 sec)

3.5.4、创建表employees

--创建表employees
create table if not exists `employees`(
	`employeeNumber` int(11) not null unique auto_increment comment '员工编号',
	`lastName` varchar(50) not null comment '员工姓氏',
	`firstName` varchar(50) not null comment '员工第一个名字',
	`mobile` varchar(25) unique comment '员工电话',
	`officeCode` int(10) not null comment '办公代码',
	`jobTitle` varchar(50) not null comment '职称',
	`birth` datetime not null comment '出生日期',
	`note` varchar(255) ,
	`sex` varchar(5) comment '性别',
	primary key(`employeeNumber`),
	constraint fk_offices_employees foreign key(`officeCode`) references `offices`(`officeCode`)
)
> OK
> 时间: 0.081s

--查看数据库中的表
mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| employees         |
| offices           |
+-------------------+
2 rows in set (0.00 sec)

--查看两张表的表结构，是否正确
mysql> desc `offices`;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| officeCode | int         | NO   | PRI | NULL    |       |
| city       | varchar(50) | NO   |     | NULL    |       |
| address    | varchar(50) | YES  |     | NULL    |       |
| country    | varchar(50) | NO   |     | NULL    |       |
| postalCode | varchar(15) | YES  | UNI | NULL    |       |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> desc `employees`;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| employeeNumber | int          | NO   | PRI | NULL    | auto_increment |
| lastName       | varchar(50)  | NO   |     | NULL    |                |
| firstName      | varchar(50)  | NO   |     | NULL    |                |
| mobile         | varchar(25)  | YES  | UNI | NULL    |                |
| officeCode     | int          | NO   | MUL | NULL    |                |
| jobTitle       | varchar(50)  | NO   |     | NULL    |                |
| birth          | datetime     | NO   |     | NULL    |                |
| note           | varchar(255) | YES  |     | NULL    |                |
| sex            | varchar(5)   | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

3.5.5、修改mobile字段的排列位置

--将表employees中的mobile字段，修改到officeCode字段后面
mysql> alter table `employees` modify `mobile` varchar(25) after `officeCode`;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

--查看表employees的结构
mysql> desc `employees`;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| employeeNumber | int          | NO   | PRI | NULL    | auto_increment |
| lastName       | varchar(50)  | NO   |     | NULL    |                |
| firstName      | varchar(50)  | NO   |     | NULL    |                |
| officeCode     | int          | NO   | MUL | NULL    |                |
| mobile         | varchar(25)  | YES  | UNI | NULL    |                |
| jobTitle       | varchar(50)  | NO   |     | NULL    |                |
| birth          | datetime     | NO   |     | NULL    |                |
| note           | varchar(255) | YES  |     | NULL    |                |
| sex            | varchar(5)   | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

3.5.6、修改birth字段的字段名

--将表employees中的字段birth，修改为employee_birth
mysql> alter table `employees` change `birth` `employee_birth` datetime;
Query OK, 0 rows affected (0.54 sec)
Records: 0  Duplicates: 0  Warnings: 0

--查看表
mysql> desc `employees`;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| employeeNumber | int          | NO   | PRI | NULL    | auto_increment |
| lastName       | varchar(50)  | NO   |     | NULL    |                |
| firstName      | varchar(50)  | NO   |     | NULL    |                |
| officeCode     | int          | NO   | MUL | NULL    |                |
| mobile         | varchar(25)  | YES  | UNI | NULL    |                |
| jobTitle       | varchar(50)  | NO   |     | NULL    |                |
| employee_birth | datetime     | YES  |     | NULL    |                |
| note           | varchar(255) | YES  |     | NULL    |                |
| sex            | varchar(5)   | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)
--已经没有birth字段，只有employee_birth字段了

3.5.7、修改sex字段的数据类型

--将表employees中的sex字段的数据类型，修改为varchar(1)，非空约束
mysql> alter table `employees` modify `sex` varchar(1);
Query OK, 0 rows affected (0.51 sec)
Records: 0  Duplicates: 0  Warnings: 0

--修改表中字段的数据类型时，可以加上约束。eg:not null,unique
mysql> alter table `employees` modify `sex` varchar(1) not null;
Query OK, 0 rows affected (0.54 sec)
Records: 0  Duplicates: 0  Warnings: 0

--查看表
mysql> desc `employees`;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| employeeNumber | int          | NO   | PRI | NULL    | auto_increment |
| lastName       | varchar(50)  | NO   |     | NULL    |                |
| firstName      | varchar(50)  | NO   |     | NULL    |                |
| officeCode     | int          | NO   | MUL | NULL    |                |
| mobile         | varchar(25)  | YES  | UNI | NULL    |                |
| jobTitle       | varchar(50)  | NO   |     | NULL    |                |
| employee_birth | datetime     | YES  |     | NULL    |                |
| note           | varchar(255) | YES  |     | NULL    |                |
| sex            | varchar(1)   | NO   |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)
/*
对比上下文，之前的sex字段是null，之后修改为not null。数据类型也改成了varchar(1)

null 列显示为NO时，表示该列不允许控制
*/

3.5.8、删除字段note

--删除字段note
mysql> alter table `employees` drop note;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

--查看表
mysql> desc `employees`;
+----------------+-------------+------+-----+---------+----------------+
| Field          | Type        | Null | Key | Default | Extra          |
+----------------+-------------+------+-----+---------+----------------+
| employeeNumber | int         | NO   | PRI | NULL    | auto_increment |
| lastName       | varchar(50) | NO   |     | NULL    |                |
| firstName      | varchar(50) | NO   |     | NULL    |                |
| officeCode     | int         | NO   | MUL | NULL    |                |
| mobile         | varchar(25) | YES  | UNI | NULL    |                |
| jobTitle       | varchar(50) | NO   |     | NULL    |                |
| employee_birth | datetime    | YES  |     | NULL    |                |
| sex            | varchar(1)  | NO   |     | NULL    |                |
+----------------+-------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

3.5.9、添加字段favoriate_activity

--在employees表中添加字段favoriate_activity，数据类型时varchar(100)
mysql> alter table `employees` add `favoriate_activity` varchar(100);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

--查看表
mysql> desc `employees`;
+--------------------+--------------+------+-----+---------+----------------+
| Field              | Type         | Null | Key | Default | Extra          |
+--------------------+--------------+------+-----+---------+----------------+
| employeeNumber     | int          | NO   | PRI | NULL    | auto_increment |
| lastName           | varchar(50)  | NO   |     | NULL    |                |
| firstName          | varchar(50)  | NO   |     | NULL    |                |
| officeCode         | int          | NO   | MUL | NULL    |                |
| mobile             | varchar(25)  | YES  | UNI | NULL    |                |
| jobTitle           | varchar(50)  | NO   |     | NULL    |                |
| employee_birth     | datetime     | YES  |     | NULL    |                |
| sex                | varchar(1)   | NO   |     | NULL    |                |
| favoriate_activity | varchar(100) | YES  |     | NULL    |                |
+--------------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

3.5.10、删除表offices

因为表employees设置了外键，关联了主表offices的officeCode字段（主键）。

如果想要删除offices表，就必须要删除从表employees或者是删除外键约束

--查看employees外键约束
mysql> show create table `employees`\G --其中，加上\G是为了查询的时候，数据更整洁
*************************** 1. row ***************************
       Table: employees
Create Table: CREATE TABLE `employees` (
  `employeeNumber` int NOT NULL AUTO_INCREMENT COMMENT '员工编号',
  `lastName` varchar(50) NOT NULL COMMENT '员工姓氏',
  `firstName` varchar(50) NOT NULL COMMENT '员工第一个名字',
  `officeCode` int NOT NULL COMMENT '办公代码',
  `mobile` varchar(25) DEFAULT NULL,
  `jobTitle` varchar(50) NOT NULL COMMENT '职称',
  `employee_birth` datetime DEFAULT NULL,
  `sex` varchar(1) NOT NULL,
  `favoriate_activity` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`employeeNumber`),
  UNIQUE KEY `employeeNumber` (`employeeNumber`),
  UNIQUE KEY `mobile` (`mobile`),
  KEY `fk_offices_employees` (`officeCode`),
  CONSTRAINT `fk_offices_employees` FOREIGN KEY (`officeCode`) REFERENCES `offices` (`officeCode`)--外键约束名是:fk_offices_employees
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

--删除表employees的外键约束
mysql> alter table `employees` drop foreign key `fk_offices_employees`;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

--删除表offices
mysql> drop table if exists `offices`;
Query OK, 0 rows affected (0.02 sec)

--查看数据库中的表
mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| employees         |
+-------------------+
1 row in set (0.00 sec)

3.5.11、修改表employees存储引擎为MyISAM

--修改表引擎
mysql> alter table `employees` engine=MyISAM;
Query OK, 0 rows affected (0.48 sec)
Records: 0  Duplicates: 0  Warnings: 0

--查看表结构
mysql> show create table `employees`\G
*************************** 1. row ***************************
       Table: employees
Create Table: CREATE TABLE `employees` (
  `employeeNumber` int NOT NULL AUTO_INCREMENT COMMENT '员工编号',
  `lastName` varchar(50) NOT NULL COMMENT '员工姓氏',
  `firstName` varchar(50) NOT NULL COMMENT '员工第一个名字',
  `officeCode` int NOT NULL COMMENT '办公代码',
  `mobile` varchar(25) DEFAULT NULL,
  `jobTitle` varchar(50) NOT NULL COMMENT '职称',
  `employee_birth` datetime DEFAULT NULL,
  `sex` varchar(1) NOT NULL,
  `favoriate_activity` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`employeeNumber`),
  UNIQUE KEY `employeeNumber` (`employeeNumber`),
  UNIQUE KEY `mobile` (`mobile`),
  KEY `fk_offices_employees` (`officeCode`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8  --存储引擎已经由InnoDB修改为MyISAM
1 row in set (0.00 sec)

3.5.12、修改表employees的表名

--将表employees的表名改成employees_info
mysql> alter table `employees` rename to `employees_info`;
Query OK, 0 rows affected (0.02 sec)

--查看数据库中的表
mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| employees_info    |
+-------------------+
1 row in set (0.00 sec)

3.6、注意事项

3.6.1、表删除操作需谨慎

• 表删除操作将表的定义和表中数据一起从数据库中删除，而且,MySQL中删除操作之前没有任何的提示。
• ==删除操作前，最好还是对表中的数据进行备份，==这样当操作失误时，可以对数据进行恢复。
• 同理，alter table在进行基本修改操作时，也应该备份（数据库的操作是无法撤销的）。

3.6.2、每一个表中是否都需要主键？

不是每一个表都是需要主键的，一般多个表之间进行连续操作时需要用到主键。因此，不用为每个表设置主键，有些情况最好不用主键。

3.6.3、并不是每个表都可以任意选择存储引擎

MySQL可以支持多种存储引擎，每一个表都可以任意指定不同的存储引擎。

外键约束，不能跨引擎使用。

需要注意：外键约束是保证数据的参照完整性。如果表之间需要关联外键，但是表选择使用不同的存储引擎的话，那么这些表是不能创建外键约束的。因此，表的存储引擎的选择也不是随意的。

--查看表customers_info的表结构
mysql> show create table customers_info\G
*************************** 1. row ***************************
       Table: customers_info
Create Table: CREATE TABLE `customers_info` (
  `c_num` int NOT NULL AUTO_INCREMENT,
  `c_name` varchar(70) DEFAULT NULL,
  `c_birth` datetime NOT NULL,
  `c_phone` varchar(50) DEFAULT NULL,
  `c_gender` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`c_num`),
  UNIQUE KEY `c_num` (`c_num`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8--存储引擎是MyISAM
1 row in set (0.00 sec)

--创建order表，默认存储引擎是InnoDB
create table `order`(
	`o_num` int(11) not null unique auto_increment,
	`o_date` date ,
	`c_id` int(11),
	primary key(`o_num`),
	constraint `fk_orders_customers_info` foreign key(`c_id`) references `customers_info`(`c_num`)
 );
 
 --1824错误，默认存储引擎不匹配
 ERROR 1824 (HY000): Failed to open the referenced table 'customers_info'
 
 --解决办法，修改存储引擎
mysql> alter table `customers_info` engine=InnoDB;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

--查看表customers_info的存储引擎
mysql> show create table `customers_info`\G
*************************** 1. row ***************************
       Table: customers_info
Create Table: CREATE TABLE `customers_info` (
  `c_num` int NOT NULL AUTO_INCREMENT,
  `c_name` varchar(70) DEFAULT NULL,
  `c_birth` datetime NOT NULL,
  `c_phone` varchar(50) DEFAULT NULL,
  `c_gender` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`c_num`),
  UNIQUE KEY `c_num` (`c_num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 --引擎已经修改为InnoDB
1 row in set (0.00 sec)

--创建orders表，使用外键关联表customers_info
mysql> create table `order`(
    -> `o_num` int(11) not null unique auto_increment,
    -> `o_date` date ,
    -> `c_id` int(11),
    -> primary key(`o_num`),
    -> constraint `fk_orders_customers_info` foreign key(`c_id`) references `customers_info`(`c_num`)
    ->  );
Query OK, 0 rows affected, 2 warnings (0.04 sec)

3.6.4、auto_increment初始值是否为1

==在MySQL中，auto_increment的默认初始值是1，==每增加一次记录，字段值加1。

但是，也是可以自定义设置auto_increment的初始值。

eg:在tb_emp中插入第一条记录，同时指定id的值为5，则之后的id都是从5开始增加。

添加唯一性的主键约束时，往往需要设置字段值自动递增。

create table `tb_emp`(
	`id` int(10) not null unique auto_increment primary key
);

4、MySQL数据管理–DML语言（全部记住）

数据库意义：数据存储，数据管理

4.1、添加

--插入数据(添加)，语法格式
insert into 表名(字段1，字段2....) values('值1','值2'.....);