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

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

知识大纲

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

• 操作Database

• 表结构操作TABLE

学习任务

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

SQL语句分类

名字	类型	作用的对象	作用
DDL 英文全称 (Data Definition Language)	数据定义语言	库、表、列	创建、删除、修改、库或表结构，对数据库或表的结构操作
DQL 英文全称(Data Query Language)	数据查询语言	数据库记录（数据）	查、用来查询数据，对表记录的查询
DCL 英文全称(Data Control Language)	数据控制语言	数据库用户	用来定义访问的权限和安全级别，对用户的创建，及授权
DML 英文全称(Data Manipulation Language	数据操作语言	数据库记录（数据）	增、删、改，对表记录进行更新(增、删、改)

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;