mysql删除数据库所有表的命令

数据库

• 数据库是按照特定数据结构来组织、存储和管理数据的仓库

删除数据库

DROP DATABASE [IF EXISTS] db_name;
-- [IF EXISTS] 用于放置当数据库不存在时发生错误

--删除testdb数据库
DROP DATABASE testdb;

SQL语句,即结构化查询语句,通过SQL语句可以操纵数据库

数据定义语句(Data Definition Language)

主要是对数据库中的表进行创建,修改,删除

创建 --- create
修改 --- alter
删除 --- drop

数据操纵语句(Data Manipulation Language)

主要是对表中的数据进行插入,更新,删除

插入 --- insert
修改 --- update
删除 --- delete

数据查询语句(Data Query Language)

主要是对表中的数据进行查询,使用最广泛

查询 --- select

事务控制语句(Transaction Control Language)

主要管理数据库的事务,维护数据的一致性

提交事务 --- commit
回滚事务 --- rollback

数据控制语句(Data Control Language)

主要是用于权限的授予和收回操作

授予 --- grant
收回 --- revoke

SQL语句不区分大小写,即对大小写不敏感 ✓ SQL语句中建议关键字使用大写,其他部分使用小写 ✓ SQL语句中使用2个中划线(--)用于单行注释 ✓ SQL语句以分号结尾(;)

数据库基础语句

查看当前所有存在的数据库

语法：

show databases;

创建数据库

语法：字符集一般填写utf8、gbk、latin1

create database 数据库名称 charset 字符集;

数据类型

• 数据类型是指列，存储过程参数，表达式和局部变量的数据特征，它决定了数据的存储格式，代表了不同的信息类型
• MySQL数据类型分为

• 整型
• 浮点型
• 定点型
• 字符型
• 日期时间型

整数类型

• TINYINT

• 在数据系统中占据一个字符
• 有符号范围: -128~127
• 无符号范围: 0~255

• SMALLINT

• 占2个字符
• 有符号范围：-32768~32767
• 无符号范围：0~65535

INT或INTEGER

• 占4个字符
• 有符号范围：-2147683648~2147683647
• 无符号范围：0~4294967295

• BIGINT

• 占8个字符
• 有符号范围：-9223372036854775808~9223372036854775807
• 无符号范围：0~184467440709551615

• BOOLEAN

• BOOL或BOOLEAN
• 0为flase，非0都被认为true

浮点型

• FLOAT

• 被称为单精度浮点
• FLOAT(M,D)

• M表示数字位数
• D表示小数点后的数字位数
• 可以省略，会取硬件系统的精度

• FLOAT最多可以精确到小数点后7为数字

• DOUBLE

• DOUBLE(M,D)

• M表示数字位数
• D表示小数点后的数字位数

• 最多可以精确到小数点后15位

定点数

• DECIMAL

• DECIMAL(M,D)

• M表示数字位数
• D表示小数点后的数字位数
• M的最大值为65，D的最大值为30
• 如果省略M则M的默认值为10
• 省略D则默认值为0

字符型

• CHAR

• 定长字符串类型
• 固定长度的字符串，其存储的右侧总是以空格填充
• 最大长度范围：0-255，如果没有写，默认为1

VARCHAR

• 变长字符串
• 最大长度范围：0~65535

日期时间型

• YEAR

• 用于存储2位或者4位年份
• 4位年份范围：1901~2155
• 2位年份范围：

• 1~69表示2001~2069年
• 70~99表示1970~1999年

• DATE

• 日期类型
• YYYY-MM-DD
• 支持范围：1000-01-01~9999-12-31

• TIME

• 时间类型
• HH:MM:SS
• 支持范围：-838:59:59~838:59:59

数据库表管理

• 数据表是关系型数据最重要的组成部分之一，是其他数据对象的基础
• 数据表是一张二维的表格，其中列称为字段，行称为记录
• 一张数据表中至少包含一个字段，但是可以包含至少0条记录

服务器与数据库和表的关系

创建数据表

CREATE TABLE [IF NOT EXISTS] table_name(column_name1 data_type,column_name2 data_type)

Demo

--创建一个test01数据库
CREATE DATABASE test01;
--在test01数据库中创建user表
USE test01
CREATE DATABASE user;
--user字段包含 username,password,age
--username,password为可变长度的字符串类型，范围在20
--age为整数类型，范围使用系统默认
CREATE TABLE user(username VARCHAR(20),password VARCHAR(20),age INT);


--创建一张表，表名为mall_pms
--用户id 为整数类型\用户名为字符串类型长度30\密码为字符串类型长度16
CREATE TABLE mall_pms(id INT,name CHAR(30),password VARCHAR(16));

--创建一张表，表名为crm_pms
--用户id 为整数类型\姓名为字符串类型长度30\年龄为整数类型\性别为字符串类型，长度为3\工资为小数类型长度为7 保留两位小数
CREATE TABLE crm_pms(id INT,name CHAR(30),age INT,sex CHAR(3),sal DOUBLE(7,2));

查看指定数据库的表

SHOW TABLES [FROM db_name] [LIKE '条件'|WHERE 表达式];

--查看当前数据库中所有数据表
SHOW TABLES;
--查看当前数据库中所有以t开头的数据表
SHOW TABLES LIKE 't%'; 
--查看test数据库下所有数据表
SHOW TABLES FROM test;
--查看test数据库下所有以字母t开头的数据表 
SHOW TABLES FROM test LIKE 't%';

查看数据表结构

--方法一
SHOW COLUMNS FROM tbl_name [FROM db_name];

--查看crm_pms的表结构
SHOW COLUMNS FROM crm_pms;
--查看test01数据库中mall_pms的表结构
SHOW COLUMNS FROM mall_pms FROM test01;

--方法二
DESCRIBE | DESC tbl_name
--查看crm_pms的表结构
DESC crm_pms;

添加表中新字段

ALTER TABLE 表名 add 字段名 数据类型(数据范围);

修改表中新字段

ALTER TABLE 表名 CHANGE 旧列名 新列名 新数据类型;
--修改crm_pms 表中address字段，改为add字段，查看该表结构
ALTER TABLE crm_pms CHANGE address add varchar(30);
DESC crm_pms;

删除一个字段

ALTER TABLE 表名 DROP 列名;
--删除crm_pms表中的add字段
ALTER TABLE crm_pms DROP add;
DESC crm_pms;

删除数据表

DROP TABLE [IF EXISTS] 表名;
--删除crm_pms表
DROP TABLE crm_pms;

约束

• 约束也称为数据库表约束，是为保证数据的完整性、一致性、有效性的规则；

约束类型

• 针对列的数量来划分

• 列级约束：对一个数据列建立的约束
• 表级约束：对多个数据列建立的约束

• 按功能进行划分

• 主键约束(PRIMARY KEY)
• 唯一约束(UNIMARY KEY)
• 外键约束(FOREIGN KEY)
• 非空约束(NOT NULL)
• 默认值约束(DEFAULT)

主键约束

• 可以保证数据的完整性，即防止数据表中的两条记录完全相同，一个基本表中只能定义一个主键约束，对于指定为主键的一列或多个列的组合，任何一列都不能出现空值。

主键约束特点

不允许表中有重复的记录

不允许表中有NULL记录

每个表中只能存在一个主键约束

• 创建主键约束后，系统将自动创建主键索引

语法格式

#创建数据表时添加表级别的主键约束
CREATE TABLE 表名(
        列名1 数据类型(数据范围) PRIMARY KEY,
        列名2 数据类型(数据范围) PRIMARY KEY
);
CREATE TABLE 表名(
        列名1 数据类型(数据范围),
        列名2 数据类型(数据范围),
        PRIMARY KEY(列名1,列名2)
);

Demo

--创建test数据库
CREATE DATABASE test;
--在test数据库中创建以下表
USE test;
--t1表中分别由id,username列,id为整数类型，username为字符串类型长度20，将id设置为主键约束
CREATE TABLE t1(
                id INT PRIMARY KEY,
                userneme CHAR(20)
);

--t2表中分别有first_name，list_name,属性均为字符串类型长度20，将两列均设置为主键约束
CREATE TABLE t2(
                first_name CHAR(20),
                list_name CHAR(20),
                PRIMARY KEY(first_name,list_name)
);
DESC t1;
DESC t2;
--根据以下列表编写语句

修改表时候添加主键约束

ALTER TABLE 表名 ADD PRIMARY KEY(列名1,列名2);

删除主键约束

ALTER TABLE 表名 DROP PRIMARY KEY;

唯一约束

• 用于指定一个或多个列的组成值具有唯一性，以防止在列中输入重复的值
• 定义了唯一约束的列，称为唯一键，系统自动为唯一建立唯一索引，从而保证唯一性

唯一约束特点

• 不允许表中有重复的记录
• 允许表中有NULL记录
• 创建唯一约束后，系统将自动创建唯一索引

语法格式

#创建数据表时添加列级别的主键约束,列级别只能一个，表级别可以多个
CREATE TABLE 表名(
        列名1 数据类型(数据范围) PRIMARY KEY,
        列名2 数据类型(数据范围) UNIQUE KEY,
        列名2 数据类型(数据范围) UNIQUE KEY
);
#创建数据表时添加表级别的主键约束
CREATE TABLE 表名(
        列名1 数据类型(数据范围),
        列名2 数据类型(数据范围),
        UNIQUE KEY(列名1,列名2)
);
多列之间是逗号，一行之间是空格

修改表时添加唯一约束

ALTER TABLE 表名 ADD UNIQUE KEY(列名1,列名2);

删除唯一约束

ALTER TABLE 表名 DROP INDEX 约束名称;

查看某张表中全部的索引名称

SHOW KEYS FROM 表名；

默认值约束

• 用于在某列指定默认值，当执行插入操作的时候，如果被默认值约束修饰的列，没有插入列值，系统将默认值自动变成列值

语法格式

CREATE TABLE 表名(
        列名1 数据类型(数据范围) PRIMARY KEY,
        列名2 数据类型(数据范围) UNIQUE KEY,
        列名3 数据类型(数据范围) DEFAULT 参数值
);

修改表时添加默认值

ALTER TABLE 表名 MODIFY 列名 数据类型 DEFAULT 默认值;

删除默认值约束

ALTER TABLE 表名 MODIFY 列名 数据类型 DEFAULT NULL;
ALTER TABLE 表名 MODIFY 列名 数据类型;

非空约束

• 用来修饰一列或多列的值，当插入数据的时候，被非空约束修饰的列，该列的值不能为空

语法格式：

CREATE TABLE 表名(
        列名1 数据类型(数据范围) PRIMARY KEY,
        列名2 数据类型(数据范围) UNIQUE KEY,
        列名3 数据类型(数据范围) DEFAULT 参数值,
        列名3 数据类型(数据范围) NOT NULL
);

修改表时添加非空约束

ALTER TABLE 表名 MODIFY 列名 数据类型 NOT NULL；

外键约束

• 用来维护两张表之间的关联关系，被外键约束所修饰的列，该列的值必须参照与之关联另外一张表主键所在列的列值
• 父表/主表：没有外键的表
• 子表：存在外键的表，被外键所修饰的列的列值，必须参照与主表中主键所在的列的列值

语法格式

CREATE TABLE 表名1(
        列名1 数据类型(数据范围) PRIMARY KEY,
        列名2 数据类型(数据范围) UNIQUE KEY,
        列名3 数据类型(数据范围) DEFAULT 参数值,
        列名3 数据类型(数据范围) NOT NULL
);
CREATE TABLE 表名2(
        列名1 数据类型(数据范围) PRIMARY KEY,
        列名2 数据类型(数据范围) UNIQUE KEY,
        列名3 数据类型(数据范围) DEFAULT 参数值,
        列名3 数据类型(数据范围) NOT NULL
        FOREIGN KEY(列名2a) REFERENCES 表名1(列表2)
);

修改表时添加外键约束

ALTER TABLE 子表 ADD CONSTRAINT 约束名称 FOREIGN KEY(列名) REFERENCES 主表(列名)
约束名称可自定义

删除外键约束

ALTER TABLE 表名 DROP FOREIGN KEY 约束名称;

自动递增

• AUTO_INCREMENT 属性为新行产生唯一的表示
• AUTO_INCREMENT是数据列的属性，只适合用于整数类型数据列
• 把数据列声明为UNSIGNED
• 必须是唯一索引
• 且数据列具备NOT NULL属性
• 该值开始是1，每次递增1

记录管理

添加记录

• 插入语句，指定列插入数据

INSERT INTO 表名(列名1，列名2) VALUES(列值1，列值2);
-- 表名中的列名必须和values中的列值一一对应

• 全部列插入数据

INSERT INTO 表名 VALUES(列值1，列值2)
-- VALUES中的列值，必须和表结构中的列名是对应的

查询记录

• 查询数据库表中列的数据

SELECT *,列名
FROM 表名
WHERE 条件
GROUP BY 分组
HAVING 过滤
ORDER BY 排序
LIMIT 分页查询

• 查询全部列的数据

SELECT * FROM 表名;

• 查询指定列的数据

SELECT 列名1，列名2 FROM 表名;

• 为列指定别名

SELECT 列名1 AS 别名1，列名2 AS 别名2 FROM 表名;
-- AS关键字可以省略

• 去掉重复的值

SELECT DISTINCT 列名 FROM 表名

• 表达式

SELECT 3 + 2 AS SUM;
SELECT 5 > 1; -- 布尔类型的值 1表示正确，0表示错误

• 函数操作

SELECT CONCAT('My','SQL');
-- 将括号中的字符串进行拼接

• 条件查询

SELECT */列名 FROM 表名
WHERE 条件
ORDER BY 列名1 ASC/DESC,列名2 ASC/DESC;
-- 条件可以包含关系运算符、逻辑运算符、特殊情况等
-- 是
-- 在几个选项之中
-- between and 在一定范围内

-- 要求查询emp表中员工编号大于等于50的员工编号
SELECT *
FROM 员工表
WHERE 员工编号>=50;


-- 查询员工表中员工编号是50的员工信息
SELECT *
FROM 员工表
WHERE 员工编号=50;


-- 查询员工表中员工编号在50-100之间的员工信息
SELECT *
FROM 员工表
WHERE 员工编号 BETWEEN 50 AND 100;

• 分组查询

• GROUP BY 子句用于根据一个或多个列对结果进行分组

-- 统计员工表中职位的分类信息
SELECT J职位 FROM 员工表 
GROUP BY 职位;

-- 统计员工表中每个职位有多少人
SELECT 职位,COUNT(员工编号) FROM 员工表
GROUP BY 职位;

• 过滤查询
• WHERE不能和聚合函数一起使用

• HAVING 子句用于指定分组的条件

-- 统计员工表中职位人数大于15的职位与数量
SELECT 职位,COUNT(员工编号) FROM 员工表
GROUP BY 职位
HAVING COUNT(员工编号)>15;

• 排序

SELECT /列名 FROM 表名 ORDER BY 列名1 ASC/DESC,列名2 ASC/DESC;
-- ASC升序（默认）
-- DESC 降序
-- 如果对多列进行排序的时候，首先排序第一列，第一列必须有相同列值，才会进行第二列排序

• 分页查询

SELECT */列名 FROM 表名 LIMIT 初始位置，行数;
-- 初始位置表示从哪一行开始，是一个可选值，默认值是0
-- 行数表示要查询的行数

-- 查询员工表中前5条记录
SELECT * 
FROM 员工表
LIMIT 5;

-- 查询员工表中，从第3条记录开始到第10条记录
SELECT *
FROM 员工表
LIMIT 2,8;

• 模糊查询

SELECT 列名 FROM 表名
WHERE 列名 LIKE 模糊条件 
OEDER BY 列名1 ASC/DESC,列名2 ASC/DESC;
-- % 表示0个或多个任意字符
-- _表示任意一个字符

连接

• 连接是关系型数据库模型的特点，也是它区别于其他类型数据库管理系统的重要标志
• 当查询数据库时候，通过连接操作可以直接管理存放在多张数据表中的记录信息
• MySQL支持多表查询、多表删除、多表更新
• MySQL连接分为内连接、外连接、自连接

内连接

• INNER JOIN称为内连接
• 连接查询就是，要查询的数据分布在不同的表中，为了一次将不同表中的数据查询出来，我们需要使用连接查询，查询几张表中有关系的记录

SELECT 别名1.*/列名,别名2*/列名
FROM 表1 别名1 INNER JOIN 表2 别名2
ON 连接条件(关联的条件)

-- 查询EMP表中，员工姓名（ename），职位（job），工资（sal），以及dept表中，部门名称（dname），部门地址（loc）
SELECT e.ename,e.job,e.sal,d.dname,d.loc
FROM emp as e INNER JOIN dept as d
ON e.deptno = d.deptno;

-- 查询emp表中，员工姓名不包含字母K，员工的姓名ename，职位job，工资sal，以及dept表中全部列数据
SELECT e.ename,e.job,e.sal,d.*
FROM emp e INNER JOIN dept d
ON e.deptno = d.deptno AND NOT e.name LIKE '%K%';

-- 查询cou01表中，课程编号（cno），课程名称cname以及sco01表中，学员编号sno，课程分数score，要求课程分数在70-90之间
SELECT c.cno,c.cname,s.sno,s.score
FROM cou01 c INNER JOIN  sno01 s
ON c.cno = S.cno AND s.score BETWEEN 70 AND 90;

-- 查询cou01表中，课程编号（cno），课程名称cname以及sco01表中，学员编号sno，课程分数score，要求课程分数在70-90之间，根据课程分数降序排序
SELECT c.cno,c.cname,s.sno,s.score
FROM cou01 c INNER JOIN  sno01 s
ON c.cno = s.cno AND s.score BETWEEN 70 AND 90
ORDER BY s.score DESC;


-- 查询emp表中，员工姓名，职位,工资，以及salgrade表中，工资等级grade，最低工资losal，最高工资hisal
SELECT e.ename 员工姓名,e.job 职位,e.sal 工资,s.grade 工资等级,s.losal 最低工资,s.hisal 最高工资
FROM EMP e INNER JOIN SALGRADE s
ON e.sal BETWEEN s.LOSAL AND s.HISAL; 


-- 查询stu01表中全部的列,以及cou01表中全部的列以及sco01表中课程分数（score）
SELECT t.*,c.*,s.score
FROM sco01 s 
INNER JOIN cou01 c ON s.cno = c.cno
INNER JOIN stu01 t ON s.sno = t.sno;

外连接

• 外连接查询就是查询一张表中的全部数据，以及另外一张表中，与之相关的部分数据
• 左外连接查询，以左边表的全部数据查询以及右边表与之有关系的部分数据
• 如果左表的某条记录在右表中没有与之匹配的记录，则右表相关字段显示空值

SELECT 别名1.*/列名,别名2.*/列名2
FROM 左表 别名1 LEFT JOIN 右表 别名2
ON 连接条件;

-- 查询dept表中，所有部门的信息以及，该部门下 员工的编号，姓名，职位，入职时间（hiredate），根据入职时间升序排列（外连接）
SELECT d.*,e.EMPNO,e.ename,e.job,e.hiredate
FROM dept d LEFT JOIN emp e
ON e.deptno = d.deptno
ORDER BY e.hiredate ASC;


-- 查询emp表中，员工姓名不包含字母k的，所有的员工编号、姓名、职位、工资以及该员工所属的部门名称（dname），部门地址（loc），根据工资降序排列
SELECT e.empno,e.name,e.job,e.sal,d.dname,d.loc
FROM emp e LEFT JOIN dept d
ON e.deptno = d.deptno
WHERE NOT e.ename LIKE '%K%'
ORDER BY e.sal DESC;

右外连接

• 查询右边表的全部数据，以及左边表的与之有关系的部分数据

SELECT 别名1.*/列名,别名2.*/列名2
FROM 左表 别名1 RIGHT JOIN 右表 别名2
ON 连接条件;

-- 查询dept表中，所有部门的信息以及，该部门下 员工的编号，姓名，职位，入职时间（hiredate），根据入职时间升序排列（外连接）
SELECT d.*,e.EMPNO,e.ename,e.job,e.hiredate
FROM dept d RIGHT JOIN emp e
ON e.deptno = d.deptno;



-- 查询stu01表中所有学员的，编号（sno），姓名（sname），年龄（age），地址（address）以及该学员的分数（score），根据学院的编号降序排列
-- 右外连接
SELECT t.sno,t.sname,t.age,t.address,s.score
FROM sco01 s RIGHT JOIN stu01 t
ON s.sno = t.sno
ORDER BY t.sno desc;

-- 左外连接
SELECT t.sno,t.sname,t.age,t.address,s.score
FROM stu01 t LEFT JOIN sco01 s
ON s.sno = t.sno
ORDER BY t.sno desc;

自查询

SELECT 别名1.*/列名,别名2.*/列名
FROM 表名 别名1，表1 别名2

子查询

• 也称为嵌套查询，将select语句嵌套在其它SQL语句中

-- 查询emp表中，工资大于平均工资的，员工的编号（empno），姓名（ename），职位（job），工资（sal）
SELECT empno,ename,job,sal
FROM emp
WHERE sal > (SELECT AVG(sal) FROM emp);

-- 查询student表中，和唐家三少是同一个班级(sclass)的，学员信息
#查询学员信息
#班级=唐家三少的班级 
SELECT *
FROM student
WHERE sclass = (SELECT sclass FROM student WHERE sname='唐家三少');

-- 查询emp表中，和30号部门员工，工资相同的，员工信息
SELECT *
FROM emp
WHERE sal IN(SELECT sal FROM emp WHERE deptno=30) AND deptno <> 30;

视图

• 视图就是一个虚拟表，可以通过查询一张或者多张表的数据，可以使查询变得简单，提高工作效率

CREATE VIEW 视图名称 AS 查询语句;

-- 创建一个视图，名称为view_emp，查询emp表中全部列的数据，作为视图的结果
CREATE VIEW view_emp
AS
SELECT * FROM emp;

-- 创建一个视图，名称为view_emp_dept，
-- 查询emp表中全部列数据以及dept表中部门名称（dname），部门地址（loc）
做为视图结果
CREATE view_emp_dept,
AS
SELECT e.*,d.dept,d.dname,d.loc
FROM emp e INNER JOIN dept d
WHERE e.deptno = d.deptno;

-- 创建一个视图，名称为view_stu_cou_sco，查询stu01表中全部列数据以及cou01表中全部列数据，以及sco01表中课程分数（score），做为视图结果
CREATE VIEW view_stu_cou_sco
AS 
SELECT s.*,c.*,s.score
FROM sco01 s
INNER JOIN cou01 c ON s.cno = c.cno
INNER JOIN stu01 t ON s.cno = t.sno;


-- 查询视图view_emp，员工姓名，职位，工资，奖金，根据工资降序排列
SELECT ename,job,sal,comm
FROM view_emp
ORDER BY sal DESC;

-- 查询视图view_stu_cou_sco中，学员姓名（sname），地址（address），课程名称（cname），课程分数（score),根据学员姓名升序排列
SELECT sname,address,cname,score
FROM view_stu_cou_sco 
ORDER BY sname ASC;

• 删除视图

DROP VIEW 视图名称;

SHOW TABLES;
-- 检查有无删除

索引

• 索引是建立在列上的数据库对象
• 用于提高查询速度，索引是一种提高查询效率的机制
• 创建索引

CREATE INDEX 索引名称 ON 表名(列名);

• 查看索引

SHOW INDEX FROM 表名;

• 被主键约束所修饰的列自带索引
• 被唯一约束所修饰的列自带索引

• 删除索引

DROP INDEX 索引名称 ON 表名;

数据备份和恢复

• 数据备份

mysqldump -u 用户名 -p 仓库名 > 备份路径

-- 对test表所在的数据库进行备份 
mysqldump -u root -p test > D;/文件名称/文件名.sql

• 数据恢复

mysql -u 用户名 -p 仓库名称 < 本地sql文件路径
-- 将d盘中.sql文件恢复到test数据库中
mysql -u root -p test < D:文件名称/文件名.sql

修改记录

• 更新语句，更新数据库表中数据

UPDATE 表名 SET 列名1=该列新值，列名2=该列新值 WHERE 条件;
-- 如果不指定WHERE条件，将更新表中所有记录

删除记录

• 删除表中数据

DELETE FROM 表名 WHERE 条件;
-- 如果不指定where条件，将删除表中所有记录。