mysql基础之DDL

标识符命名规则

• 数据库名、表名不得超过30个字符，变量名限制为29个
• 必须只能包含 A–Z, a–z, 0–9, _共63个字符
• 数据库名、表名、字段名等对象名中间不要包含空格
• 同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
• 必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使 用`（着重号）引起来
• 保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了

数据库的操作

创建

CREATE DATABASE [IF NOT EXISTS] 库名 [character set 字符集名 collate 校对规则];

推荐：

CREATE DATABASE IF NOT EXISTS 库名 character set 字符集名;

查看

#查看当前数据库服务器中的所有数据库
SHOW DATABASES;

#查看创建数据库的结构
CREATE DATABASE mytest1;

#查看当前使用的库
select database()[FROM DUAL];

#查看当前使用的库中的表
show tables;

#查看指定数据库下保存的数据表
show table from database_name;

#查看表的创建语句
show create table `table_name`;
-- 注：在创建数据库表的时候，为了规避关键字，可以使用着重号解决

修改

修改数据库默认的字符集

ALTER DATABASE 库名 CHARACTER SET 字符集;
#例
ALTER DATABASE mytest CHARACTER SET 'utf8';

切换数据库

USE 库名;

删除

DROP DATABASE  if exists  库名;

如果要删除的数据库存在，则删除成功。如果不存在，则默默结束，不会报错。

备份与恢复

1. 备份, 要在Dos下执行mysqldump指令，其实在mysql安装目录\bin
   #这个备份的文件，就是对应的sql语句
   mysqldump -u root -p -B 库名 库名... 指定路径
   #例：
   mysqldump -u root -p -B yg_db02 yg_db03 > D:\\code\\mysqlCode\\test\\bak.sql
   #注:语句结尾也没有分号

2. 恢复数据库(注意：进入 Mysql 命令行再执行)
   source 路径
   source D:\CODE\mysqlCode\test\bak.sql
   #注：在dos下code不能加反引号、语句结尾也没有分号(若有分号也能恢复成功，但会报错ERROR:Unknown command ‘\’.)

表的操作

创建表

创建表的时候必须要先选择在那个数据库里建表。

方式1：

create table  [if not exists] 表名(
	字段名 字段类型 【约束】,
	字段名 字段类型 【约束】,
	...
	字段名 字段类型 【约束】 
)【 CHARSET 字符集 COLLATE 校对规则 ENGINE 引擎名】;

方式2：基于现有的表，同时导入数据

CREATE TABLE myemp2
AS
SELECT employee_id,last_name,salary
FROM employees;

#说明1：查询语句中字段的别名，可以作为新创建的表的字段的名称。
#说明2：此时的查询语句可以结构比较丰富，使用前面章节讲过的各种SELECT

CREATE TABLE myemp3
AS
SELECT e.employee_id emp_id,e.last_name lname,d.department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;

查看表

查看当前数据库中所有的表

show tables;

查看表的结构

DESC 表名;

查看创建表的 SQL 语句

SHOW CREATE TABLE 表名;

删除表

DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];

#删除指定数据库中的表
DROP TABLE yg_db02 .`user1`;

清空表

清空表，表示清空表中的所有数据，但是表结构保留。

TRUNCATE TABLE 表名;

• TRUNCATE语句不能回滚，而使用 DELETE 语句删除数据，可以回滚

修改表结构

1. 添加字段（列）

   ALTER TABLE 表名
   ADD 【COLUMN】 字段名 字段类型 【 FIRST|AFTER 字段名】;
   

2. 重命名一个列

   ALTER TABLE 表名
   CHANGE 【column】 列名 新列名 新数据类型;
   

3. 修改列的约束

   ALTER TABLE 表名 
   MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【 FIRST|AFTER 字段名2】;
   

4. 删除字段

   ALTER TABLE 表名 DROP 【COLUMN】字段名；
   

5. 修改表名

   rename table 表名 to 新表名;
   #或
   alter table 表名 rename 【to】新表名;
   

6. 修改表的字符集

   ALTER TABLE 表名 
   CHARACTER SET 字符集;
   

备份与恢复

#备份恢复数据库的表
#备份： mysqldump -u root -p 数据库名 表1 表2 表n > D:\\文件位置\\bak1.sql
mysqldump -u root -p yg_db01 USER > D:\\CODE\\mysqlCode\\test\\bak1.sql
#恢复：选择要使用的数据库，source D:\\文件位置\\bak1.sql
source D:\\CODE\\mysqlCode\\test\\bak1.sql

表的复制

复制表的结构

create table 表名 like 旧表;

复制表的结构+数据

create table 表名 
select 查询列表【*】 from 旧表【where 筛选】;
# 若where条件全为假，则只赋值表结构。

自我复制

INSERT INTO my_tb
	SELECT * FROM my_tb;

如何删除掉一张表重复记录

-- 1. 先创建一张表 my_tab02, -- 2. 让 my_tab02 有重复的记录
CREATE TABLE my_tab2 LIKE emp; -- 这个语句 把 emp 表的结构(列)，复制到 my_tab2

INSERT INTO my_tab2
	SELECT * FROM emp;

/*
思路
(1) 先创建一张临时表 my_tmp , 该表的结构和 my_tab2 一样
(2) 把 my_tab2 的记录 通过 distinct 关键字 处理后 把记录复制到 my_tmp
(3) 清除掉 my_tab02 记录
(4) 把 my_tmp 表的记录复制到 my_tab02
(5) drop 掉 临时表 my_tmp
*/
SELECT * FROM my_tmp;
-- (1) 先创建一张临时表 my_tmp , 该表的结构和 my_tab2 一样
CREATE TABLE my_tmp LIKE my_tab2;
-- (2) 把 my_tab2 的记录 通过 distinct 关键字 处理后 把记录复制到 my_tmp
INSERT INTO my_tmp
	SELECT DISTINCT*  FROM my_tab2;
-- (3) 清除掉 my_tab02 记录
DELETE FROM my_tab2;
-- (4) 把 my_tmp 表的记录复制到 my_tab02
INSERT INTO my_tab2
	SELECT *  FROM my_tmp;
-- (5) drop 掉 临时表 my_tmp
DROP TABLE my_tmp;

#3-5步可以先把my_tab2表删除，再把my_tmp表的名字该为my_tab2；