MySQL - 2.基本操作

4.数据库的基本操作

1.数据库的创建

CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>] 
[[DEFAULT] COLLATE <校对规则名>];

• 1.指在数据库系统中划分一块存储数据的空间，方便数据的分配，放置和管理
• 2.[]中的内容是可选的，<>中的内容是必填的
• 3.数据库名：创建的数据库的名称，在同一个数据库服务器上必须是唯一的，不允许重复
• 4.MySQL的数据存储区将以目录的方式表示MySQL数据库，因此数据库名称必须符合操作系统的文件夹命名规则并尽量有实际意义
  
• 5.IF NOT EXISTS：创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作，此选项用来避免数据库已经存在而重复创建的错误
• 6.[DEFAULT] CHARACTER SET：指定数据库的字符集，为了避免在数据库中存储的数据出现乱码的情况，如果在创建数据库时不指定字符集，那么就使用系统的默认字符集
• 7.[DEFAULT] COLLATE：指定字符集的默认校对规则
• 8.使用SHOW CREATE DATABASE 数据库名可以查看数据库的定义声明

  //例1：最简单的创建 MySQL 数据库的语句
  CREATE DATABASE test_db;
  //例2：MySQL 不允许在同一系统下创建两个相同名称的数据库，为了避免类似错误可以加上IF NOT EXISTS从句
  CREATE DATABASE IF NOT EXISTS test_db;
  //例3：创建 MySQL 数据库时指定字符集和校对规则
  CREATE DATABASE IF NOT EXISTS test_db_char
  DEFAULT CHARACTER SET utf8
  DEFAULT COLLATE utf8_general_ci;
  SHOW CREATE DATABASE test_db_char;
  

  
  
  
• 9.注意
  • 1.MySQL 的字符集CHARACTER和校对规则COLLATION是两个不同的概念
  • 2.字符集是用来定义 MySQL 存储字符串的方式，校对规则定义了比较字符串的方式
  • 3.字符集和校队规则具体可参考https://www.cnblogs.com/geaozhang/p/6724393.html

2.数据库的查看

SHOW DATABASES [LIKE '数据库名'];

• 1.查看或显示当前用户权限范围以内的数据库
  
  
• 2.information_schema、mysql、performance_schema，sakila，sys，world这六个数据库都是在MySQL安装完成后由系统自动创建的
  • 1.information_schema：信息数据库，主要存储了系统中的一些数据库对象信息，比如用户表信息、列信息、权限信息、字符集信息和分区信息等
  • 2.mysql：MySQL的核心数据库，主要负责存储数据库的用户、权限设置、关键字等控制和管理信息，可以在mysql 数据库的 user 表中修改 root 用户密码
  • 3.performance_schema：主要用于收集数据库服务器性能参数，该数据库中所有表的存储引擎均为performance_schema，而用户不能创建存储引擎为performance_schema的表
  • 4.sakila：MySQL 提供的样例数据库，该数据库共有 16 张表，这些数据表都是比较常见的在设计数据库时，可以参照这些样例数据表来快速完成所需的数据表
  • 5.sys：MySQL 5.7 安装完成后会多一个 sys 数据库，sys 数据库主要提供了一些视图，数据都来自于 performation_schema，主要是让开发者和使用者更方便地查看性能问题
  • 6.world：MySQL 自动创建的数据库，该数据库中只包括 3 张数据表，分别保存城市，国家和国家使用的语言等内容

SHOW CREATE DATABASE 数据库名称;

例：查看创建好的数据库SSCMS的信息
SHOW CREATE DATABASE SSCMS;

• 1.查看MySQL中某个已经创建的具体数据库的定义信息
  
• 2.结果显示数据库SSCMS的创建信息及编码方式，且数据库创建之后数据库编码方式就确定了

  CREATE DATABASE `SSCMS` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
  

3.数据库的修改

ALTER DATABASE [数据库名] 
[ DEFAULT ] CHARACTER SET <字符集名> |
[ DEFAULT ] COLLATE <校对规则名>;

• 1.MySQL 数据库中只能对数据库使用的字符集和校对规则进行修改，数据库的这些特性都储存在 db.opt 文件中
  
• 2.ALTER DATABASE [数据库名]：用于更改数据库的全局特性，使用 ALTER DATABASE 需要获得数据库 ALTER 权限
  • 1.其中数据库名称是要修改的数据库的名称，数据库名称可以忽略，此时语句对应于默认数据库
• 3.CHARACTER SET：更改默认的数据库字符集

  例：将数据库SSCMS的编码方式修改为gbk
  ALTER DATABASE SSCMS
  DEFAULT CHARACTER SET gbk
  COLLATE gbk_bin;
  

  

4.数据库的删除

DROP DATABASE [IF EXISTS] <数据库名称>；

• 1.<数据库名称>：指定要删除的数据库名
• 2.IF EXISTS：用于防止当数据库不存在时发生错误
• 3.DROP DATABASE：删除数据库中的所有表格并同时删除数据库，使用 DROP DATABASE需要获得数据库 DROP 权限

  例：删除名为SSCMS的数据库
  DROP DATABASE SSCMS;
  

  
• 4.注意
  • 1.MySQL 安装后系统会自动创建名为 information_schema 和 mysql 的两个系统数据库，用于存放一些和数据库相关的信息，如果删除了这两个数据库MySQL 将不能正常工作
  • 2.使用 DROP DATABASE 命令时要非常谨慎，执行该命令后MySQL 不会给出任何提示确认信息，DROP DATABASE 删除数据库后，数据库中存储的所有数据表和数据也将一同被删除而且不能恢复，因此最好在删除数据库之前先将数据库进行备份

5.数据库的选择

USE <数据库名>

• 1.MySQL 中就有很多系统自带的数据库，操作数据库之前要确定是哪一个数据库
• 2.当用 CREATE DATABASE 语句创建数据库之后，该数据库不会自动成为当前数据库，需要用 USE 来指定当前数据库
• 3.USE可以通知 MySQL 把<数据库名>所指示的数据库作为当前数据库,该数据库保持为默认数据库直到语段的结尾或直到遇见一个不同的 USE 语句
• 4.只有使用 USE 语句来指定某个数据库作为当前数据库之后，才能对该数据库及其存储的数据对象执行操作
• 5.如果出现Database changed提示，则表示选择数据库成功

5.数据表的基本操作

1.数据表的创建

CREATE TABLE 数据表名称（
字段名1 数据类型 [完整性约束条件]，
字段名2 数据类型 [完整性约束条件]，
...
字段名3 数据类型 [完整性约束条件]
）;

• 1.CREATE TABLE：创建给定名称的表，必须拥有表CREATE的权限
• 2.数据表名称：创建的数据表的名称，必须符合标识符命名规则
• 3.字段名：数据表的列名
• 4.数据类型：当前列的数据类型
• 5.完整性约束条件：字段的特殊约束条件

  例：在SSCMS数据库中创建一个用于存储教师信息的Teacher表
  USE SSCMS;
  CREATE TABLE Teacher(
  ID INT(5),-- 教师的工号
  NAME VARCHAR(10),-- 教师的姓名
  EMAIL VARCHAR(20)-- 教师的邮箱地址
  );
  或 创建表的同时指定存储引擎和字符以及字符排序规则
  CREATE TABLE `teacher` (
  `ID` int DEFAULT NULL,
  `NAME` varchar(10) DEFAULT NULL,
  `EMAIL` varchar(20) DEFAULT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
  

• 6.注意：创建数据表之前一定要使用USE 数据库名;明确是在哪个数据库中创建的，否则系统会抛出No database selected错误

2.数据表的查看

• 1.MySQL中查看数据表的方式有两种
  • 1.SHOW CREATE TABLE 数据表名称
  • 2.DESCRIBE 数据表名称
• 2.查看数据表前使用USE数据库名;明确是查看哪个的数据库中的数据表

1.SHOW CREATE TABLE

SHOW CREATE TABLE 数据表名称; //查看某一个数据表的信息
或
SHOW TABLES;//查看当前数据库所有的数据表

• 1.数据表名称：指定查看的数据表的名字
• 2.SHOW TABLES：查看所有已存在的数据表

  例：使用SHOW CREATE TABLE语句查看Teacher表
  SHOW CREATE TABLE Teacher;
  

  
• 3.SHOW CREATE TABLE查看数据表可以看出表的定义信息以及字符编码方式

2.DESCRIBE

DESCRIBE 数据表名称;
//简写
DESC 数据表名称;

• 1.数据表名称：查看的数据表的名字
• 2.DESCRIBE查看数据表可以看出数据表的字段名，类型，是否为空，是否为主键等信息

  例：使用DESCRIBE语句查看Teacher表
  DESCRIBE Teacher;
  或
  DESC Teacher;
  

  
  • 1.Field： 表示该表的字段名
  • 2.Type： 表示对应字段的数据类型
  • 3.Null： 表示对应字段是否可以存储NULL值
  • 4.Key： 表示对应字段是否编制索引和约束
  • 5.Default： 表示对应字段是否有默认值
  • 6.Extra： 表示获取到的与对应字段相关的附加信息

3.数据表的修改

ALTER TABLE <表名> [修改选项]
//修改选项的语法格式如下：
RENAME TO <新表名>
| ADD COLUMN <列名> <类型>
| MODIFY COLUMN <列名> <类型>
| CHANGE COLUMN <旧列名> <新列名> <新列类型>
| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
| DROP COLUMN <列名>
| CHARACTER SET <字符集名>
| COLLATE <校对规则名>

• 1.数据表创建之后用户可以对表中的某些信息进行修改

1.修改表名

ALTER TABLE 旧表名 RENAME [TO] 新表名;

例：将数据库中SSCMS中Teacher表的表名改为SSCMS_Teacher
ALTER TABLE Teacher RENAME SSCMS_Teacher;

2.添加字段

ALTER TABLE 表名 
ADD [COLUMN] 新字段名 数据类型 [约束条件] [FIRST|AFTER 已经存在的字段名]; 

• 1.新字段名： 新添加的字段名称
• 2.FIRST： 用于将新添加的字段设置为表的第一个字段
• 3.AFTER 已经存在的字段名： 用于将新添加的字段添加到指定字段的后面，如不指定位置则默认将新添加字段追加到表末尾

  例：在SSCMS_Teacher表中添加一个没有约束条件的INT（4）类型的AGE字段
  ALTER TABLE SSCMS_Teacher ADD AGE INT(4);
  

  

3.修改字段的数据类型

ALTER TABLE 表名 MODIFY [COLUMN] 字段名 新数据类型;

例：将SSCMS_Teacher表中WORKID字段的数据类型由TINYINT修改为INT(5)
ALTER TABLE SSCMS_Teacher MODIFY WORKID INT(5);

4.修改字段名和数据类型

ALTER TABLE 表名 CHANGE [COLUMN] 旧字段名 新字段名 新数据类型;

• 1.旧字段名：修改之前的字段名称

• 2.新字段名：修改之后的字段名称

• 3.新数据类型：修改后的数据类型

• 4.注意修改后的数据类型不能为空

  • 1.如果只修改字段名，不修改数据类型，可以将新数据类型写为字段原来的数据类型
  • 2.如果只修改数据类型，不修改字段类型，可以将新字段名写为旧字段名

  例：将SSCMS_Teacher表中ID字段改名为WORKID，数据类型保持不变
  ALTER TABLE SSCMS_Teacher CHANGE ID WORKID INT(5);
  

  
  

  例：将SSCMS_Teacher表中WORKID字段的数据类型由INT（5）修改为TINYINT
  ALTER TABLE SSCMS_Teacher CHANGE WORKID WORKID TINYINT;
  

  

5.修改字段的位置

ALTER TABLE 表名 MODIFY 字段名1 新数据类型 [FIRST|AFTER 字段名2]

• 1.FIRST： 用于将字段名1设置为表的第一个字段
• 2.AFTER： 用于将字段名1移动到字段名2的后面
• 3.新数据类型： 要修改字段的新数据类型，如果只修改位置，不修改数据类型，可以将新数据类型写为字段原来的数据类型

  例：将SSCMS_Teacher表中的NAME字段修改为表中的第一个字段
  ALTER TABLE SSCMS_Teacher MODIFY NAME VARCHAR(10) FIRST;
  

  

  例：重新添加AGE字段，并将SSCMS_Teacher表中的WORKID字段移动到AGE字段之后
  USE sscms;
  ALTER TABLE SSCMS_Teacher ADD AGE INT(4);
  ALTER TABLE sscms_teacher MODIFY WORKID INT AFTER AGE; 
  

  

6.删除字段

ALTER TABLE 表名 
DROP [COLUMN] 字段名; 

• 1.字段名： 指明要删除的字段名称

  例：删除SSCMS_Teacher表中
  ALTER TABLE SSCMS_Teacher DROP AGE;
  

  

7.修改表字符集

ALTER TABLE 表名 [DEFAULT] CHARACTER SET <字符集名> [DEFAULT] COLLATE <校对规则名>;

4.数据表的删除

• 1.一般数据库中的多个数据表之间可能会存在关联，要删除具有关联关系的数据表需先删除关联表或删除关联关系
• 2.删除表的同时表的结构和表中所有的数据都会被删除，因此在删除数据表之前最好先备份以免造成无法挽回的损失

1.删除数据表

DROP TABLE [IF EXISTS