MySQL数据库详细教程（附sql语句练习题及答案）

前言

本文记录了一些MySQL数据库相关的基础概念、基本操作、基本原理和相关应用。

。

---

一、数据库概述

1.1 什么是数据库？

简而言之，就是存储数据，管理数据的仓库。

1.2 什么是SQL语言？

Structured Query Language：结构化的查询语言。
SQL是操作所有关系型数据库的通用的语言。
SQL语言的分类:

1. DDL - - 数据库定义语言,指Create,Allter,Drop等操作(即创建,删除,修改数据库和数据表)。
2. DML - - 数据库操作语言,指Insert.Update.Delete等操作(即数据表中数据的增删改操作)。
3. DQL - - 数据查询语言(指Selete操作,即数据表中数据的查询操作)。
4. DCL - - 数据控制语言( 管理数据库：比如用户权限：grant revoke )

二、MySQL中的基础知识和常用操作

2.1数据库的相关操作

2.1.1 创建、删除数据库

# 使用指令创建数据库
CREATE DATABASE tzw_db04
#特别说明：在创建数据库，表的时候，为了规避关键字，可以使用反引号``解决
# 不设置即为默认的使用utf8字符集
CREATE DATABASE tzw_db05 CHARACTER SET utf8
#也可以自己设置
CREATE DATABASE tzw_db06 CHARACTER SET utf8 COLLATE utf8_bin
#校对规则：utf8_bin 区分大小写 utf8_general_ci不区分大小写
DROP DATABASE tzw_db03

2.1.2 查询数据库

#查看当前数据库服务器中的所有数据库
SHOW DATABASES 
#查看前面创建的tzw_db02数据库的定义信息(如何创建、定义格式)
SHOW CREATE DATABASE tzw_db02
#删除前面创建的tzw_db02数据库
DROP DATABASE tzw_db02

2.1.3 备份、恢复数据库

数据库的备份是把数据库的数据存储到文件当中，以防数据丢失；数据库的恢复是把存储在文件中的数据恢复到数据库中。
在SQLyog中可以选择备份、恢复，比较方便。这里主要掌握命令行的方式去备份。

• 备份数据库（在DOS执行）（备份的文件就是对应的SQL语句）
  
  语句为：mysqldump -u root -p -B 是固定格式 ，> 前是需要备份的数据库名称， > 后是备份到指定文件的路径

mysqldump -u root -p -B tzw_db02 > d:\\bak.sql

• 恢复数据库

1. 方法一：（执行mysql -u root -p 命令，进入到MySQL命令行）
   

mysql -u root -p #进入到mysql命令
->
#source  后接文件路径
source d:\\bak.sql

2. 方法二：复制需要恢复的sql文件的全部语句到SQLyog中执行一遍。

2.1.3.2 备份、恢复数据库的表

mysqldump -u root -p 数据库 表1 表2 …表n > 目标路径（d://bak.sql）

---

2.2Mysql常用数据类型（列类型）

常见列类型：

2.3表的相关操作

2.3.1表的创建

1. 图形化创建(在SQLyog中)

2. sql指令创建：

CREATE TABLE `goods`(
	id INT(10),
	goods_name VARCHAR(10),
	price DOUBLE);
	CHARACTER SET utf8 COLLATE utf8 bin ENGINE INNODB;
	#不写也可，按当前数据库的格式创建表。

3. 通过LIKE关键字把想要表的结构拿过来 创建

CREATE TABLE my_tab01 LIKE emp;

2.3.2表的删除

DROP 表名

2.3.3表的修改（Alter）(针对列的操作)

1. 添加列

ALTER TABLE tablename
ADD (列名 列的类型[大小], 列名 列类型[大小]...);

2. 修改列(相当于重新设置)

ALTER TABLE tablename
MODIFY (column（列名） datatype（类型） [],[]...);

修改列名：

ALTER TABLE employee
	CHANGE  `name` `user name` VARCHAR(64) NOT NULL DEFAULT ''

3. 删除列

ALTER TABLE tablename
DROP (COLUMN(列名));

4. 其它操作

• 查看表结构：desc 表名 – 可以查看表的列
• 修改表名：Rename TABLE 表名 TO 新表名
• 修改表字符集：alter TABLE 表名 CHARACTER SET 字符集

2.3.4 表的复制与去重

演示表的如何自我复制

-- 演示表的如何自我复制

-- 1、先把emp 表的记录复制到 my_tab01

INSERT INTO my_tab01
	(id,`name`,sal,job,deptno)
	SELECT empni,ename,sal.job,deptno FROM emp;
	
-- 2、自我复制
INSERT INTO my_tab01
	SELECT * FROM my_tab01;

表的去重

2.4Mysql的增删改查（CRUD）

2.4.1 Insert语句

语法格式：
INSERT INTO 表名（列名，列名，…）
VALUES （对应值，对应值，…）

CREATE TABLE student(
	id INT NOT NULL DEFAULT 1,
	NAME VARCHAR(20) NOT NULL DEFAULT '',
	chinese FLOAT NOT NULL DEFAULT 0.0,
	english FLOAT NOT NULL DEFAULT 0.0,
	math FLOAT NOT NULL DEFAULT 0.0
);
SELECT * FROM student;
INSERT INTO student(id,NAME,chinese,english,math) 
	VALUES(1,'韩顺平',89,78,90);

INSERT INTO student(id,NAME,chinese,english,math) 
	VALUES(2,'张飞',67,98,56);

INSERT INTO student(id,NAME,chinese,english,math) 
	VALUES(3,'宋江',87,78,77);

Insert语句细节说明：

1. 插入的数据应与字段的数据类型相同。比如把’abc’添加到int 类型会错误。
2. 数据的长度应在列的规定范围内，例如:不能将一个长度为80的字符串加入到长度为40的列中。
3. 在values中列出的数据位置必须与被加入的列的排列位置相对应。
4. 字符和日期型数据应包含在单引号中。
5. 列可以插入空值[前提是该字段允许为空]，insert into table value(null)。
6. insert into tab_name(列名…)values 0.0.(形式添加多条记录。
7. 如果是给表中的所有字段添加数据,可以不写前面的字段名称。
8. 默认值的使用,当不给某个字段值时，如果有默认值就会添加,否则报错。

2.4.2 Update语句（针对元素）

语法格式如下：

UPDATE employee SET salary = 5000
#不带条件，即把表中所有员工的salary改成5000
UPDATE employee 
	SET salary = 3000
	WHERE user_name = '张三';
#只把表中员工名为张三的salary改成3000

SELECT * FROM employee;

2.4.3 Delete语句

DELETE FROM employee
	WHERE user_name	= '张三';
#删除名字为张三的记录。
DELETE FROM employee;
#删除表中所有记录,表还在，只是没有记录了。

#彻底删除表：
DROP TABLE employee;

2.4.4 Select语句

2.4.4.1常用运算符及ORDER BY的使用

SELECT `name`,(chinese+english+math) FROM student;
#列名是可以运算的
 SELECT `name`,(chinese+english+math)AS total_score FROM student;
#还可以起个别名

• 在where子句中经常使用的运算符

1. 比较运算符

比较运算符	说明
> 、 < 、 >= 、 <=、!= 、<>	大于、小于、大于等于、小于等于、不等于
BETWENN … AND…	显示在某一区间的值
IN（set）	显示在in列表中的值 ，例：in（100，200）,值是100或200
LIKE ‘张%’ 、 NOT LIKE ’ ’	模糊查询

2. 逻辑运算符

逻辑运算符	说明
and	多个条件同时成立
or	多个条件任一成立
not	不成立

简单select语句练习:

SELECT * FROM student
	WHERE chinese BETWEEN 70 AND 90;
#查询学生语文成绩在70到90之间的,BETWEEN ... AND...是闭区间

SELECT * FROM student 
	WHERE (chinese + math) IN(190,191,192);
#查询语文和数学成绩之和是191、190、192的同学

SELECT * FROM student
	WHERE `name` LIKE '宋%' OR `name` LIKE '赵%' ;
#查询学生姓宋或者姓赵的

SELECT * FROM student
	WHERE math+30 < chinese;
#查询学生数学成绩比语文成绩少30的。

• ORDER BY 使用（DESC降序，默认升序可省略ASC）

SELECT * FROM student 
	ORDER BY math DESC;
#ORDER BY 排序有两种 ，ASC为升序，DESC为降序
#排序的列既可以是表中的列名，也可以是别名
SELECT `name`,(chinese+english+math)AS total_score FROM student 
	ORDER BY total_score DESC;
#DESC是降序
SELECT `name`,(chinese+english+math)AS total_score FROM student 
	ORDER BY total_score;
#默认升序 

2.4.4.2group by 和having的使用

基本语法格式:

SELECT column1, column2. column3... FROM TABLE
	GROUP BY COLUMN
#使用having子句对分组后的结果进行过滤
SELECT column1 , column2 , column3...
	FROM TABLE
	GROUP BY COLUMN HAVING ...

#按照部门来分组查询
SELECT AVG(sal),MAX(sal),deptno
	FROM emp GROUP BY deptno;

需求：显示平均工资低于2000的部门号和它的平均工资/别名

#显示平均工资低于2000的部门号和它的平均工资/别名
#写sql语句的思路是化繁为简,各个击破
-- 1．显示各个部门的平均工资和部门号
SELECT AVG (sal) , deptno
	FROM emp GROUP BY deptno
-- 2．在1的结果基础上，进行过滤，保留AVG(sal) <2000
SELECT AVG (sal) , deptno
	FROM emp GROUP BY deptno
		HAVING AVG (sal) < 2000;


-- 3．使用别名进行过滤（少了一次函数的使用，效率提高）

SELECT AVG (sal) AS avg_sal , deptno
	FROM emp GROUP BY deptno
		HAVING avg_sal < 2000;

2.4.4.3查询语句加强

2.4.4.3.1 order by 和 指定位置字符的模糊查询

-- % : 表示0到多个任意字符 
-- _ : 表示单个任意字符

-- 如何显示首字符为S的员工姓名和工资?
SELECT ename,sal FROM emp
	WHERE ename LIKE 'S%'

-- 如何显示第三个字符为大写o的所有员工的姓名和工资
SELECT ename,sal FROM emp
	WHERE ename LIKE '__O%';

--如何显示没有上级的雇员的情况
SELECT * FROM EMP
	WHERE mgr IS NULL;
-- 如何查询表的结构
DESC emp;

使用order by子句
-- 如何按照工资的从低到高的顺序，显示雇员的信息？
SELECT * FROM emp
	ORDER BY sal
-- 按照部门号升序而雇员的工资降序排列，显示雇员信息？

SELECT * FROM emp
	ORDER BY deptno ,sal DESC

2.4.4.3.2 分页查询

• 基本语法:
  select … limit start, rows
  表示从 start+1 行开始取，取出 rows 行, start 从0开始计算

• 分页查询公式：
  SELECT * FROM emp
  ORDER BY empno
  LIMIT 每页显示记录数*(第几页-1)，每页显示记录数

SELECT * FROM emp
	ORDER BY empno
	LIMIT 0,4;
SELECT * FROM emp
	ORDER BY empno
	LIMIT 4,4;
	
SELECT * FROM emp
	ORDER BY empno DESC
	LIMIT 10,5;
SELECT * FROM emp
	ORDER BY empno DESC
	LIMIT 20,5;
-- 推导一个公式
SELECT * FROM emp
	ORDER BY empno
	LIMIT 每页显示记录数*(第几页-1)，每页显示记录数

练习： 按雇员的empno号降序取出，每页显示5条记录。请分别显示第3页对应的sql语句

SELECT * FROM emp
	ORDER BY empno DESC