MySQL—DDL操作1(创建、查询、删除、修改数据库和表结构)qq45938466的博客-

MySQL—DDL操作1(创、查、删、改数据库和表)

知识大纲

• SQL语句的分类（什么是DDL）

• 操作Database

• 表结构操作TABLE

学习任务

• 操作Database
  1. 创建数据库
  2. 查看有哪些数据库
  3. 删除数据库
  4. 选择数据库
  5. 查看当前正在使用哪个数据库
• 表结构操作TABLE
  1. 查看当前数据库所有表
  2. 创建表结构
  3. 查看表结构
  4. 删除表结构
  5. 修改表结构
• 小练习

SQL语句分类

DDL[数据定义语言]

DDL 用于结构定义、操作方法定义等。

包括：数据段、数据库、表、列、索引等数据库对象操作。

主要的语句关键字包括： create drop alter等

操作Database

注意：database 不能改名。一些可视化工具可以改名，它是建新库,把所有表复制到新 库,再删旧库完成的。

1.创建数据库

create database 数据库名 [charset 字符集]; (关键字大写效果：CREATE DATABASE 数据库名;)

如果不指定字符集，则按照安装 mysql 服务时选择的默认字符集。例如：

#创建数据库 #方式-1：直接创建[采用MySQL默认字符集latin1 -- cp1252 West European] CREATE DATABASE mysql03; #方式-2：创建并指定字符集[utf8 -- UTF-8 Unicode] CREATE DATABASE mysql04 CHARACTER SET utf8; #查看校对规则 SHOW CHARACTER SET; #方式-3：创建并指定字符集[utf8]和校对规则[utf8_general_ci] CREATE DATABASE mysql05 CHARACTER SET utf8 COLLATE utf8_general_ci; #创建数据库[若数据库不存在] #IF EXISTS[删除表使用过] CREATE DATABASE IF NOT EXISTS mysql05; 

2.查看有哪些数据库

show databases;

提示：当前用户有权限查看的

#查看有哪些数据库 SHOW DATABASES; 

3.删除数据库

drop database 数据库名;

#删除数据库[直接删除] DROP DATABASE mysql03; #删除数据库[判断删除] DROP DATABASE IF EXISTS mysql04; DROP DATABASE IF EXISTS mysql05; 

4.选择数据库

use 数据库名;

#定位数据库 USE mysql01; 

5.查看当前正在使用哪个数据库

select database();

注意：要操作表格和数据之前必须先说明是对哪个数据库进行操作，否则就要对所有 对象加上“数据库名.”。

#查看当前正在使用的数据库 SELECT DATABASE(); #跨数据库查询表数据[数据库名.表名] #empinfo[mysql02] #SELECT * FROM mysql01.empinfo; SELECT * FROM mysql02.empinfo; 

表结构操作 TABLE

1.查看当前数据库所有表

show tables; #前面必须有 use 数据库名语句，否则报错

show tables from 数据库名;

#前往mysql02数据库 USE mysql02;#SELECT DATABASE() -> mysql02 #查看当前数据库所有表 SHOW TABLES; #查看指定数据库的表内容 SHOW TABLES FROM mysql01; 

2.创建表结构

（1）基础版

（2）详细版

#创建测试数据库test DROP DATABASE IF EXISTS test; CREATE DATABASE IF NOT EXISTS test CHARACTER SET utf8; #定位到test[若没有定位数据库 那么表会被存放到当前数据库中] USE test; #在test库中创建表Student[简单建表方式] CREATE TABLE student (   stu_id INT,   stu_name VARCHAR(20),   stu_sex CHAR(2),   stu_age INT ); #插入数据 INSERT INTO student VALUES(1001,'张三','男',16); INSERT INTO student VALUES(1002,'李四','女',17); #查询信息 SELECT * FROM student; #SELECT DATABASE(); /* 建表配置 可选参数： ENGINE=INNODB [当前表格的引擎] AUTO_INCREMENT=1 [增长的起始值] DEFAULT CHARSET=utf8; [表数据的默认字符集] */ #复杂的建表方式 #创建表emp [要求：定义主键、自增字段、非空约束、默认值] DROP TABLE IF EXISTS emp; CREATE TABLE emp (   emp_id INT PRIMARY KEY AUTO_INCREMENT,   emp_name VARCHAR(20) NOT NULL,   emp_sex CHAR(2) DEFAULT '男',   emp_age INT NOT NULL )ENGINE=INNODB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8; #添加数据测试 INSERT INTO emp(emp_name,emp_sex,emp_age) VALUES('八戒','男',28); INSERT INTO emp(emp_name,emp_sex,emp_age) VALUES('嫦娥','女',18); #INSERT INTO emp(emp_name,emp_sex) VALUES('梅超风','女'); INSERT INTO emp(emp_name,emp_age) VALUES('梅超风',38); #查看所有内容 SELECT * FROM emp; 

3.查看表结构

desc 表名称;

查看表的定义：SHOW CREATE TABLE 表名;

#查看表结构 DESC emp; #查看表定义[获得表定的标准SQL代码] SHOW CREATE TABLE emp; 

4.删除表结构

drop table 表名称;

注意：

数据和结构都被删除

所有正在运行的相关事务被提交

所有相关索引被删除

DROP TABLE 语句不能回滚

#删除表的 数据 DELETE FROM emp; #删除表的 数据和结构 DROP TABLE IF EXISTS emp; #SELECT * FROM emp; 

5.修改表结构

（1）重命名表

alter table 表名 rename 新表名;

rename table 表名 to 新表名;

#修改表名 语法-1[emp -> employee] ALTER TABLE emp RENAME employee; #emp已经被重命名为employee(不存在) #SELECT * FROM emp; SELECT * FROM employee; #修改表名 语法-2[employee -> employees] RENAME TABLE employee to employees; SELECT * FROM employees; 

（2）增加一列

alter table 表名 add 【column】 列名 数据类型; #默认在最后

alter table 表名 add 【column】 列名 数据类型 after 某一列;

alter table 表名 add 【column】 列名 数据类型 first;

注意：**!没有!**alter table 表名 add 【column】 列名 数据类型 before 某一列;

#修改表 语法-1 新增列[emp_city] [默认添加为表的最后一列] ALTER TABLE employees ADD emp_city VARCHAR(20); #修改表 语法-2 新增列[emp_address] [修饰"first" 使新列成为首列] ALTER TABLE employees ADD emp_address VARCHAR(20) FIRST; #修改表 语法-3 新增列[emp_mail] [修饰"after 列名" 使新列插入在指定的列名后] ALTER TABLE employees ADD emp_mail VARCHAR(20) AFTER emp_sex; #ERROR #ALTER TABLE employees ADD emp_xx VARCHAR(20) BEFORE emp_sex; 

（3）删除列

alter table 表名 drop 【column】 列名;

#删除列 [将刚刚添加的"emp_mail"列删除] ALTER TABLE employees DROP COLUMN emp_mail; #删除列 [将刚刚添加的"emp_address"列删除] ALTER TABLE employees DROP emp_address; SELECT * FROM employees; DESC employees; 

（4）修改列类型

alter table 表名 modify 【column】 列名 数据类型;

alter table 表名 modify 【column】 列名 数据类型 after 某一列;

alter table 表名 modify 【column】 列名 数据类型 first;

#修改列数据类型 语法-1 [emp_name varchar(20) -> varchar(30)] ALTER TABLE employees MODIFY emp_name varchar(30); #修改列数据类型 语法-1 [emp_age int -> float] ALTER TABLE employees MODIFY emp_age float; #修改列数据类型 语法-2 [使用"after 列名" 在修改类型的同时 移动列的位置到指定列的后方] #修改emp_age列的数据类型为int ALTER TABLE employees MODIFY emp_age int AFTER emp_name; #修改列数据类型 语法-3 [使用"first" 在修改类型的同时 移动列的位置到首列] ALTER TABLE employees MODIFY emp_age float FIRST; 

（5）修改列名等

alter table 表名 change 【column】 列名 新列名 数据类型

#修改列的名称 [emp_city -> emp_address] ALTER TABLE employees CHANGE emp_city emp_address VARCHAR(50); 

练习

• 题目：

#创建数据库mytest1[简单创建] #创建数据库mytest2 指定其编码格式为utf8 查看校对规则 并选择合适的规则 #查看有哪些数据库 #若数据库mytest1存在 将其移除 #定位数据库mytest2 #查看当前正在使用的数据库是否为mytest2 #在mytest2中查看其他数据库的表数据   #创建学生表 #[具备以下列：学号(自增字段)、姓名(非空约束)、性别(默认为女)、年龄(非空约束)、地址、电话] #[当前表格的引擎为INNODB 增长的起始值为1001 表数据的默认字符集为UTF8]  #进行以下操作： #查看表中所有内容 #查看表结构 #查看表定义   #修改表 修改表名[2种方式] #修改表 新增列[city-城市 ...][3种方式] #修改表 修改列的数据类型[name varchar2(20) -> varchar2(40) ...][3种方式] #修改表 修改列的名[phone-tell] #修改表 移除列[address]  #删除表的 数据 #删除表的 数据和结构  

• 答案：

#创建数据库mytest1[简单创建] CREATE DATABASE mytest1; #查看校对规则 SHOW CHARACTER SET; #创建数据库mytest2 指定其编码格式为utf8 查看校对规则 并选择合适的规则 CREATE DATABASE mytest2 CHARACTER SET utf8 COLLATE utf8_general_ci; #查看有哪些数据库 SHOW DATABASES; #若数据库mytest1存在 将其移除 CREATE DATABASE IF EXISTS mytest1; #定位数据库mytest2 USE mytest2; #查看当前正在使用的数据库是否为mytest2 SELECT DATABASE(); #在mytest2中查看其他数据库的表数据 SELECT * FROM mysql02.empinfo; #创建学生表 #[具备以下列：学号(自增字段)、姓名(非空约束)、性别(默认为女)、年龄(非空约束)、地址、电话] #[当前表格的引擎为INNODB 增长的起始值为1001 表数据的默认字符集为UTF8] CREATE TABLE student (        id VARCHAR(12) PRIMARY KEY AUTO_INCREMENT,        name VARCHAR(30) NOT NULL,        sex CHAR(2) DEFAULT '女',        age INT NOT NULL,        address VARCHAR(50),        phone VARCHAR(11) )ENGINE=INNODB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8; #进行以下操作： #查看表中所有内容 SELECT * FROM student; #查看表结构 DESC student; #查看表定义 SHOW CREATE TABLE student; #修改表 修改表名[2种方式] ALTER TABLE student RENAME students; RENAME TABLE students to student; #修改表 新增列[city-城市 ...][3种方式] ALTER TABLE student ADD city VARCHAR(20); ALTER TABLE student ADD column1 VARCHAR(20) FIRST; ALTER TABLE student ADD column2 VARCHAR(20) AFTER age; #修改表 修改列的数据类型[name varchar2(20) -> varchar2(40) ...][3种方式] ALTER TABLE student MODIFY name varchar2(40); ALTER TABLE student MODIFY column1 varchar2(22) AFTER name; ALTER TABLE student MODIFY column2 varchar2(24) FIRST; #修改表 修改列的名[phone-tell] ALTER TABLE student CHANGE phone tell VARCHAR(11); #修改表 移除列[address] ALTER TABLE student DROP address; #删除表的 数据 DELETE FROM student; #删除表的 数据和结构 DROP TABLE IF EXISTS student;