一、基础知识
1、一条数据的存储过程
存储数据是处理数据的第一步 。只有正确地把数据存储起来，我们才能进行有效的处理和分析。否则，只能是一团乱麻，无从下手。
在 MySQL 中，一个完整的数据存储过程总共有 4 步，分别是创建数据库、确认字段、创建数据表、插入数据。
我们要先创建一个数据库，而不是直接创建数据表呢？因为从系统架构的层次上看，MySQL 数据库系统从大到小依次是 数据库服务器 、 数据库 、 数据表 、数据表的 行与列 。MySQL 数据库服务器之前已经安装。所以，我们就从创建数据库开始。
2、标识符命名规则
（1）数据库名、表名不得超过30个字符，变量名限制为29个
（2）必须只能包含 A–Z, a–z, 0–9, _共63个字符
（3）数据库名、表名、字段名等对象名中间不要包含空格
（4）同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
（5）必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用`（着重号）引起来
（6）保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了
3、MySQL中的数据类型
其中，常用的几类类型介绍如下：
二、创建和管理数据库
1、创建数据库
# 方式1：创建数据库
# 格式：CREATE DATABASE 数据库名;
CREATE DATABASE mytest1;
SHOW CREATE DATABASE mytest1; # 查看创建的数据库的结构(可以看到数据库名和创建数据库语句)
# 方式2：创建数据库并指定字符集
# 格式：CREATE DATABASE 数据库名 CHARACTER SET 字符集;
CREATE DATABASE mytest2 CHARACTER SET 'utf8';
SHOW CREATE DATABASE mytest2;
# 方式3：判断数据库是否已经存在，不存在则创建数据库（ 推荐 ）
# 格式：CREATE DATABASE IF NOT EXISTS 数据库名;
CREATE DATABASE IF NOT EXISTS mytest2 CHARACTER SET 'utf8';# mytest2已经存在，但执行该语句不会报错
CREATE DATABASE IF NOT EXISTS mytest3 CHARACTER SET 'utf8';
2、管理数据库
# 查看当前所有的数据库
SHOW DATABASES;# #有一个S，代表多个数据库
# 使用/切换数据库
# 格式：USE 数据库名;
USE atguigudb;
# 查看当前正在使用的数据库
SELECT DATABASE();
# 查看指定库下所有的表
# 格式：SHOW TABLES FROM 数据库名;
SHOW TABLES FROM atguigudb;
# 查看数据库的创建信息
# 格式： SHOW CREATE DATABASE 数据库名; 或者： SHOW CREATE DATABASE 数据库名\G
SHOW CREATE DATABASE atguigudb;
注意：要操作表格和数据之前必须先说明是对哪个数据库进行操作，否则就要对所有对象加上“数据库名.”。
3、修改数据库（一般情况下不会修改数据库）
# 更改数据库字符集
# 格式：ALTER DATABASE 数据库名 CHARACTER SET 字符集; （比如：gbk、utf8等）
SHOW CREATE DATABASE mytest1;
ALTER DATABASE mytest1 CHARACTER SET 'gbk';
4、删除数据库
# 方式1：删除指定的数据库(若要删除的数据库存在，则删除成功。如果不存在，则报错)
DROP DATABASE mytest1;
# 方式2：删除指定的数据库(推荐)(若要删除的数据库存在，则删除成功。如果不存在，不会报错)
DROP DATABASE IF EXISTS mytest1;
DROP DATABASE IF EXISTS mytest2;
三、创建表
1、创建方式一
# 语法格式
CREATE TABLE [IF NOT EXISTS] 表名(
字段1, 数据类型 [约束条件] [默认值],
字段2, 数据类型 [约束条件] [默认值],
字段3, 数据类型 [约束条件] [默认值],
……
[表约束条件]
);
加上了IF NOT EXISTS关键字，则表示：如果当前数据库中不存在要创建的数据表，则创建数据表；
如果当前数据库中已经存在要创建的数据表，则忽略建表语句，不再创建数据表。
举例：
USE atguigudb;
CREATE TABLE IF NOT EXISTS myemp1(# 注意小括号中使用逗号分隔开
emp_id INT,
emp_name VARCHAR(25),
emp_hiredate DATE
);
# 查看表结构
DESC myemp1;
# 查看创建表的语句结构
SHOW CREATE TABLE myemp1;
# 查看表数据
SELECT * FROM myemp1;
2、创建方式二
# 使用AS选项，将创建表和插入数据结合起来
CREATE TABLE myemp2
AS
SELECT employee_id,last_name,salary
FROM employees;
# 查看表结构
DESC myemp2;
DESC employees;
#说明1：查询语句中字段的别名，可以作为新创建的表的字段的名称。
#说明2：此时的查询语句可以结构比较丰富，使用前面章节讲过的各种SELECT
CREATE TABLE myemp3
AS
SELECT e.employee_id "emp_id",e.last_name "emp_lname",d.department_name "emp_dname"
FROM employees e JOIN departments d
ON e.department_id=d.department_id;
SELECT *
FROM myemp3;
# 练习1：创建一个表employees_copy，实现对employees表的复制，包括表数据
CREATE TABLE employees_copy
AS
SELECT *
FROM employees;
SELECT *
FROM employees_copy;
# 练习2：创建一个表employees_blank，实现对employees表的复制，不包括表数据
CREATE TABLE employees_blank
AS
SELECT *
FROM employees
WHERE 1=2; # 一个不正确的判断条件保证不复制数据
SELECT *
FROM employees_blank;
四、修改表
修改表指的是修改数据库中已经存在的数据表的结构。使用 ALTER TABLE 语句可以实现：
向已有的表中添加列；
修改现有表中的列；
删除现有表中的列；
重命名现有表中的列
1、追加一个列
# 语法格式
ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;
举例：
#ALTER TABLE myemp1
ADD salary DOUBLE(10,2); # 默认添加到表中的最后一个字段的位置
#ALTER TABLE myemp1
ADD phone_number VARCHAR(20) FIRST; # FIRST表示加入的列第一个显示
ALTER TABLE myemp1
ADD email VARCHAR(45) AFTER emp_name; #AFTER表示加入的列在某列后显示
2、修改一个列
# 修改字段数据类型、长度、默认值、位置的语法格式
ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名 2】;
举例：
ALTER TABLE myemp1
MODIFY emp_name VARCHAR(25); # 修改数据类型
ALTER TABLE myemp1
MODIFY emp_name VARCHAR(25) DEFAULT 'A'; # 修改默认值
DESC myemp1;
3、重命名一个列
# 使用 CHANGE old_column new_column dataType子句重命名列的语法格式
ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;
举例：
ALTER TABLE myemp1
CHANGE salary emp_sal DOUBLE(10,2);
ALTER TABLE myemp1
CHANGE email emp_email VARCHAR(50);# 可以重命名列的同时修改其数据类型
DESC myemp1;
4、删除一个列
# 删除表中某个字段的语法格式
ALTER TABLE 表名 DROP 【COLUMN】字段名
举例：
ALTER TABLE myemp1
DROP COLUMN emp_email;
五、重命名表
# 方式一：使用RENAME
RENAME TABLE myemp1
TO myemp11;
# 方式二；使用ALTER和RENAME
ALTER TABLE myemp2
RENAME TO myemp22;
六、删除表
在MySQL中，当一张数据表没有与其他任何数据表形成关联关系 时，可以将当前数据表直接删除。
（1）数据和结构都被删除
（2）所有正在运行的相关事务被提交
（3）所有相关索引被删除
# 语法格式
DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];
# IF EXISTS 的含义为：如果当前数据库中存在相应的数据表，则删除数据表；如果当前数据库中不存
#在相应的数据表，则忽略删除语句，不再执行删除数据表的操作。
举例：
DROP TABLE IF EXISTS myemp2; # myemp2已经不存在，但执行不会报错
DROP table IF EXISTS myemp3; # 将存在的myemp3删除
七、清空表
TRUNCATE TABLE语句：
（1）删除表中所有的数据
（2）释放表的存储空间
举例：
SELECT * FROM employees_copy;
TRUNCATE TABLE employees_copy;
SELECT * FROM employees_copy;
DESC employees_copy;
八、总结
1、如何理解清空表、删除表等操作需谨慎？！
表删除 操作将把表的定义和表中的数据一起删除，并且MySQL在执行删除操作时，不会有任何的确认信息提示，因此执行删除操时应当慎重。在删除表前，最好对表中的数据进行 备份 ，这样当操作失误时可以对数据进行恢复，以免造成无法挽回的后果。
同样的，在使用 ALTER TABLE 进行表的基本修改操作时，在执行操作过程之前，也应该确保对数据进 行完整的 备份 ，因为数据库的改变是无法撤销 的，如果添加了一个不需要的字段，可以将其删除；相 同的，如果删除了一个需要的列，该列下面的所有数据都将会丢失。
2、 DCL 中 COMMIT 和 ROLLBACK
COMMIT:提交数据。一旦执行COMMIT，则数据就被永久的保存在了数据库中，意味着数据不可以回滚。
ROLLBACK:回滚数据。一旦执行ROLLBACK,则可以实现数据的回滚。回滚到最近的一次COMMIT之后。
3、对比 TRUNCATE TABLE 和 DELETE FROM
相同点：都可以实现对表中所有数据的删除，同时保留表结构。
不同点：
TRUNCATE TABLE：一旦执行此操作，表数据全部清除。同时，数据是不可以回滚的。
DELETE FROM：一旦执行此操作，表数据可以全部清除（不带WHERE）。同时，数据是可以实现回滚的。
4、DDL 和 DML 的说明
① DDL的操作一旦执行，就不可回滚。指令SET autocommit = FALSE对DDL操作失效。(因为在执行完DDL操作之后，一定会执行一次COMMIT。而此COMMIT操作不受SET autocommit = FALSE影响的。)
② DML的操作默认情况，一旦执行，也是不可回滚的。但是，如果在执行DML之前，执行了 SET autocommit = FALSE，则执行的DML操作就可以实现回滚。
九、小练习
# 练习一
#1. 创建数据库test01_office,指明字符集为utf8。并在此数据库下执行下述操作
CREATE DATABASE IF NOT EXISTS test01_office CHARACTER SET 'utf8';
USE test01_office;
#2. 创建表dept01
/*字段 类型
id INT(7)
NAME VARCHAR(25)
*/
CREATE TABLE IF NOT EXISTS dept01(
id INT(7),
`NAME` VARCHAR(25)
);
#3. 将表departments中的数据插入新表dept02中
CREATE TABLE dept02
AS
SELECT * FROM atguigudb.departments;
#4. 创建表emp01
/*字段 类型
id INT(7)
first_name VARCHAR (25)
last_name VARCHAR(25)
dept_id INT(7)
*/
CREATE TABLE IF NOT EXISTS emp01(
id INT(7),
first_name VARCHAR (25),
last_name VARCHAR(25),
dept_id INT(7)
);
#5. 将列last_name的长度增加到50
ALTER TABLE emp01
MODIFY last_name VARCHAR(50);
#6. 根据表employees创建emp02
CREATE TABLE emp02
AS
SELECT *
FROM atguigudb.employees;
#7. 删除表emp01
DROP TABLE emp01;
#8. 将表emp02重命名为emp01
RENAME TABLE emp02 TO emp01;
#9.在表dept02和emp01中添加新列test_column，数据类型为VARIANCE(10)，并检查所作的操作
ALTER TABLE dept02 ADD test_colum VARCHAR(10);
ALTER TABLE emp01 ADD test_colum VARCHAR(10);
#10.直接删除表emp01中的列 department_id
ALTER TABLE emp01
DROP COLUMN department_id;
# 练习二
# 1、创建数据库 test02_market
CREATE DATABASE IF NOT EXISTS test02_market CHARACTER SET 'utf8';
USE test02_market;
# 2、创建数据表 customers
CREATE TABLE IF NOT EXISTS customers(
c_num INT,
c_name VARCHAR(50),
c_contact VARCHAR(50),
c_city VARCHAR(50),
c_birth DATE
);
# 3、将 c_contact 字段移动到 c_birth 字段后面
ALTER TABLE customers
MODIFY c_contact VARCHAR(50) AFTER c_birth; # modify和add都可以使用after和first调整列的位置
# 4、将 c_name 字段数据类型改为 varchar(70)
ALTER TABLE customers
MODIFY c_name VARCHAR(70);
# 5、将c_contact字段改名为c_phone
ALTER TABLE customers
CHANGE c_contact c_phone VARCHAR(50);
# 6、增加c_gender字段到c_name后面，数据类型为char(1)
ALTER TABLE customers
ADD c_gender char(1) AFTER c_name;
# 7、将表名改为customers_info
RENAME TABLE customers TO customers_info;
# 8、删除字段c_city
ALTER TABLE customers_info
drop COLUMN c_city;
# 练习三
# 1、创建数据库test03_company
CREATE DATABASE IF NOT EXISTS test03_company CHARACTER SET 'utf8';
USE test03_company;
# 2、创建表offices
CREATE TABLE IF NOT EXISTS offices(
officeCode INT,
city VARCHAR(30),
address VARCHAR(50),
country VARCHAR(50),
postalCode VARCHAR(25)
);
# 3、创建表employees
CREATE TABLE IF NOT EXISTS employees(
empNum INT,
lastName VARCHAR(50),
firstName VARCHAR(50),
mobile VARCHAR(25),
`code` INT,
jobTitle VARCHAR(50),
birth DATE,
note VARCHAR(255),
sex VARCHAR(5)
);
# 4、将表employees的mobile字段修改到code字段后面
ALTER TABLE employees
MODIFY mobile VARIANCE(20) AFTER `code`;
# 5、将表employees的birth字段改名为birthday
ALTER TABLE employees
CHANGE birth birthday DATE;
# 6、修改sex字段，数据类型为char(1)
ALTER TABLE employees
MODIFY sex CHAR(1);
# 7、删除字段note
ALTER TABLE employees;
DROP COLUMN note;
# 8、增加字段名favoriate_activity，数据类型为varchar(100)
ALTER TABLE employees
ADD favoriate_activity VARCHAR(10);
# 9、将表employees的名称修改为 employees_info
RENAME TABLE employees TO employees_info;
所用数据库请看文章末尾：SQL语言和基本的select语句
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
