MySQL之MySQL服务的使用以及四种语言分类的DQL（查询），DML（操作）, DDL（数据定义），TCL（事务控制）

2022/1/17 19:10:48

编程Tag： mysql select 查询 id where DML employees department TCL

本文主要是介绍MySQL之MySQL服务的使用以及四种语言分类的DQL（查询），DML（操作）, DDL（数据定义），TCL（事务控制），对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

前言

一，数据库和SQL概述

1.简介

2.相关概念（DB,DBMS,SQL）

3.MySQL服务的使用

二，DQL语言（查询）

1.简单查询

2.条件查询

3.排序查询

4.分组查询

5.多表查询（★）

连接查询（★）

子查询（★）

分页查询（关键字limit)

联合查询（关键字union）

三，DML语言的学习（操作）

1.插入（insert）

2.修改（update）

3.删除（delete）

四，DDL语言的学习（数据定义语言）

1.库的管理：

2.表的管理：

3.常见的数据类型

4.常见的约束

5.标识列

五，TCL语言的学习（事务控制语言）Transaction Control Language

前言

学习数据库是为了实现数据持久化使用完整的管理系统统一管理，易于查询。

一，数据库和SQL概述

1.简介

数据库：数据库指的是长期存在计算机内、有组织、可共享的、大量数据的集合。数据是按照特定的数据模型来组织、存储在数据库中的。

2.相关概念（DB,DBMS,SQL）

①DB（数据库database）:存储数据的”仓库“，保存了一系列有组织的数据

②DBMS（数据管理系统Database ManagementSystem）:用于管理DB中的数据

③SQL（结构化查询语句StructureQueryLanguage）:结构化查询语言，用于和DBMS通信的语言（用于存取数据、查询、更新和管理关系数据库系统。）

特点：
1.不是某个特定数据库供应商专有的语言，几乎所有DBMS都支持SQL
2.简单易学
3.灵活使用其语言元素，可进行非常复杂和高级的数据库操作

什么是MySQL?
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。在Java企业级开发中非常常用，因为 MySQL 是开源免费的，并且方便扩展。

3.MySQL服务的使用

①启动和停止/登录和退出

启动和停止：

方式一：计算机——右击管理——服务——MySQL

方式二：通过管理员身份运行cmd命令提示行

net start mysql（启动服务） net stop mysql（停止服务）

登录和退出：

方式一：通过MySQL自带的客户端只限于root用户

方式二：通过cmd命令提示行关闭，未配置MySQL环境变量，需在MySQL安装的bin下启动cmd

退出： exit或ctrl+C

②语法规范

二，DQL语言（查询）

1.简单查询

特点：1、查询列表可以是字段、常量、表达式、函数，也可以是多个
2、查询结果是一个虚拟表

1）查询到表中所有记录：select * from 表名；

2）示例：

1、查询单个字段
select 字段名 from 表名;
2、查询多个字段
select 字段名，字段名 from 表名;
3、查询所有字段
select * from 表名
4、查询常量
select 常量值;
注意：字符型和日期型的常量值必须用单引号引起来，数值型不需要
5、查询函数
select 函数名(实参列表);
6、查询表达式
select 100/1234;
7、起别名
①as

 #方式一：使用as
SELECT 100%98 AS 结果;
SELECT last_name AS 姓,first_name AS 名 FROM employees;

②空格

#方式二：使用空格
SELECT last_name 姓,first_name 名 FROM employees;

8、去重
select distinct 字段名 from 表名;

#案例：查询员工表中涉及到的所有的部门编号
SELECT DISTINCT department_id FROM employees;

9、+
作用：做加法运算
select 数值+数值; 直接运算
select 字符+数值;先试图将字符转换成数值，如果转换成功，则继续运算；否则转换成0，再做运算
select null+值;结果都为null

10、【补充】concat函数
功能：拼接字符
select concat(字符1，字符2，字符3,...);

11、【补充】ifnull函数
功能：判断某字段或表达式是否为null，如果为null 返回指定的值，否则返回原本的值
select ifnull(commission_pct,0) from employees;

12、【补充】isnull函数
功能：判断某字段或表达式是否为null，如果是，则返回1，否则返回0

2.条件查询

关键字：where

语法:
select 查询列表
from 表名
where 筛选条件

筛选条件的分类
1、简单条件运算符
> < = <> != >= <= <=>安全等于
2、逻辑运算符
&& and
|| or
! not
3、模糊查询
like:一般搭配通配符使用，可以判断字符型或数值型
通配符：%任意多个字符，_任意单个字符

between and
in
is null /is not null：用于判断null值

is null PK <=>
           普通类型的数值   null值       可读性
is null       × √    √
<=> √ √   ×

例：

#1、按条件表达式筛选

#案例1：查询工资>12000的员工信息

SELECT 
	*
FROM
	employees
WHERE
	salary>12000;
	

#2、按逻辑表达式筛选

#案例1：查询工资z在10000到20000之间的员工名、工资以及奖金
SELECT
	last_name,
	salary,
	commission_pct
FROM
	employees
WHERE
	salary>=10000 AND salary<=20000;
3、模糊查询
#案例1：查询员工名中包含字符a的员工信息

select 
	*
from
	employees
where
	last_name like '%a%';#abc

3.排序查询

关键字：order by

语法：
select 查询列表
from 表
where 筛选条件
order by 排序列表【asc}desc】

特点：
1、asc ：升序，如果不写默认升序
desc：降序

2、排序列表支持单个字段、多个字段、函数、表达式、别名

3、order by的位置一般放在查询语句的最后（除limit语句之外）

例：

#1、按单个字段排序
SELECT * FROM employees ORDER BY salary DESC;

#2、添加筛选条件再排序

#案例：查询部门编号>=90的员工信息，并按员工编号降序

SELECT *
FROM employees
WHERE department_id>=90
ORDER BY employee_id DESC;


#3、按表达式排序
#案例：查询员工信息 按年薪降序


SELECT *,salary*12*(1+IFNULL(commission_pct,0))
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;


#4、按别名排序
#案例：查询员工信息 按年薪升序

SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 ASC;

#5、按函数排序
#案例：查询员工名，并且按名字的长度降序

SELECT LENGTH(last_name),last_name 
FROM employees
ORDER BY LENGTH(last_name) DESC;

#6、按多个字段排序

#案例：查询员工信息，要求先按工资降序，再按employee_id升序
SELECT *
FROM employees
ORDER BY salary DESC,employee_id ASC;

4.分组查询

关键字：where /having（后面均是筛选条件）

对比：

使用关键字       筛选的表   位置
分组前筛选   where   原始表       group by的前面
分组后筛选   having       分组后的结果   group by 的后面

语法：

select 分组函数，分组后的字段
from 表
【where 筛选条件】
group by 分组的字段
【having 分组后的筛选】
【order by 排序列表】

特点：

1、可以按单个字段分组
2、和分组函数一同查询的字段最好是分组后的字段
3、可以按多个字段分组，字段之间用逗号隔开
4、可以支持排序
5、having后可以支持别名

例：

#1.简单的分组

#案例1：查询每个工种的员工平均工资
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

#案例2：查询每个位置的部门个数

SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;


#2、可以实现分组前的筛选

#案例1：查询邮箱中包含a字符的 每个部门的最高工资

SELECT MAX(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;


#案例2：查询有奖金的每个领导手下员工的平均工资

SELECT AVG(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;



#3、分组后筛选

#案例：查询哪个部门的员工个数>5

#①查询每个部门的员工个数
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id;

#② 筛选刚才①结果

SELECT COUNT(*),department_id
FROM employees

GROUP BY department_id

HAVING COUNT(*)>5;


#案例2：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;


#案例3：领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资

manager_id>102

SELECT manager_id,MIN(salary)
FROM employees
GROUP BY manager_id
HAVING MIN(salary)>5000;


#4.添加排序

#案例：每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序

SELECT job_id,MAX(salary) m
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING m>6000
ORDER BY m ;


#5.按多个字段分组

#案例：查询每个工种每个部门的最低工资,并按最低工资降序

SELECT MIN(salary),job_id,department_id
FROM employees
GROUP BY department_id,job_id
ORDER BY MIN(salary) DESC;

5.多表查询（★）

为什么使用多表查询？

当查询中涉及到了多个表的字段，需要使用多表连接
select 字段1，字段2
from 表1，表2,...;

在学习多表查询之前，先了解下笛卡尔乘积

   * 产生原因：
1. 省略连接条件
2. 连接条件无效
3. 所有表中所有行互相连接
   * 解决方案：
       添加有效筛选条件

连接查询（★）

按年代分类：

三个连接：内连接/外连接/交叉连接

1.内连接：关键字（inner）

语法：

select 查询列表
from 表1 别名
【inner】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;

特点：

① 一般为表起别名
②多表的顺序可以调换
③n表连接至少需要n-1个连接条件
④等值连接的结果是多表的交集部分
sql199:
①添加排序、分组、筛选
②inner可以省略
③ 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集

分类：

等值连接

非等值连接

自连接

#案例1.查询员工名、部门名

SELECT last_name,department_name
FROM departments d
 JOIN  employees e
ON e.`department_id` = d.`department_id`;



#案例2.查询名字中包含e的员工名和工种名（添加筛选）
SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.`job_id`=  j.`job_id`
WHERE e.`last_name` LIKE '%e%';



#3. 查询部门个数>3的城市名和部门个数，（添加分组+筛选）

#①查询每个城市的部门个数
#②在①结果上筛选满足条件的
SELECT city,COUNT(*) 部门个数
FROM departments d
INNER JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;




#案例4.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）

#①查询每个部门的员工个数
SELECT COUNT(*),department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY department_name

#② 在①结果上筛选员工个数>3的记录，并排序

SELECT COUNT(*) 个数,department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;

#5.查询员工名、部门名、工种名，并按部门名降序（添加三表连接）

SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d ON e.`department_id`=d.`department_id`
INNER JOIN jobs j ON e.`job_id` = j.`job_id`

ORDER BY department_name DESC;

#二）非等值连接

#查询员工的工资级别

SELECT salary,grade_level
FROM employees e
 JOIN job_grades g
 ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;


 #查询工资级别的个数>20的个数，并且按工资级别降序
 SELECT COUNT(*),grade_level
FROM employees e
 JOIN job_grades g
 ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
 GROUP BY grade_level
 HAVING COUNT(*)>20
 ORDER BY grade_level DESC;


 #三）自连接

 #查询员工的名字、上级的名字
 SELECT e.last_name,m.last_name
 FROM employees e
 JOIN employees m
 ON e.`manager_id`= m.`employee_id`;

  #查询姓名中包含字符k的员工的名字、上级的名字
 SELECT e.last_name,m.last_name
 FROM employees e
 JOIN employees m
 ON e.`manager_id`= m.`employee_id`
 WHERE e.`last_name` LIKE '%k%';

注意：

1. 使用表名前缀在多个表中区分相同的列
2. 在不同表中具有相同列名的列可以用表的别名加以区分
3. 如果使用了表别名，则在select语句中需要使用表别名代替表名
4. 表别名最多支持32个字符长度，但建议越少越好

2.外连接（left、right，full outer）

应用场景：用于查询一个表中有，另一个表没有的记录

语法：

select 查询列表
from 表1 别名
left|right|full【outer】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;

特点：

1、外连接的查询结果为主表中的所有记录
   如果从表中有和它匹配的，则显示匹配的值
   如果从表中没有和它匹配的，则显示null
   外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接，left join左边的是主表
右外连接，right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的

分类：

#左外连接
 SELECT b.*,bo.*
 FROM boys bo
 LEFT OUTER JOIN beauty b
 ON b.`boyfriend_id` = bo.`id`
 WHERE b.`id` IS NULL;


 #案例1：查询哪个部门没有员工
 #左外
 SELECT d.*,e.employee_id
 FROM departments d
 LEFT OUTER JOIN employees e
 ON d.`department_id` = e.`department_id`
 WHERE e.`employee_id` IS NULL;


 #右外

  SELECT d.*,e.employee_id
 FROM employees e
 RIGHT OUTER JOIN departments d
 ON d.`department_id` = e.`department_id`
 WHERE e.`employee_id` IS NULL;


 #全外

 USE girls;
 SELECT b.*,bo.*
 FROM beauty b
 FULL OUTER JOIN boys bo
 ON b.`boyfriend_id` = bo.id;

3.交叉连接

语句：

select 查询列表
from 表1 别名
cross join 表2 别名;

 #交叉连接

 SELECT b.*,bo.*
 FROM beauty b
 CROSS JOIN boys bo;

子查询（★）

一条查询语句中又嵌套了另一条“完整”的select语句。被嵌套的select语句，称为子查询或内查询
在外面的查询语句，称为主查询或外查询。

特点：

1、子查询都放在小括号内
2、子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
3、子查询优先于主查询执行，主查询使用了子查询的执行结果
4、子查询根据查询结果的行数不同分为以下两类：
① 单行子查询
   结果集只有一行
   一般搭配单行操作符使用：> < = <> >= <=
   非法使用子查询的情况：
   a、子查询的结果为一组值
   b、子查询的结果为空

② 多行子查询
   结果集有多行
   一般搭配多行操作符使用：any、all、in、not in
   in：属于子查询结果中的任意一个就行
   any和all往往可以用其他查询代替

补充：

any/some:和子查询返回的某一值比较
all()：和子查询返回的所有值比较
in/not in：等于列表中的任意一个

分类：

1.按出现位置

1）select后面：仅支持标量子查询

2）from后面：表子查询

3）where或having后面：标量子查询，列子查询，行子查询

4）exists后面：标量子查询，列子查询，行子查询，表子查询

2.按结果集

1）标量子查询（结果集一行一列）

2）列子查询（结果集一列多行）

3）行子查询（结果级一行多列（不常用））

4）表子查询（多行多列（当作一个表使用））

总结

select后面：标量子查询
from后面：
   支持表子查询
where或having后面：★
   标量子查询（单行） √
   列子查询（多行） √
   行子查询
exists后面（相关子查询）
   表子查询

#1.标量子查询★

#案例1：谁的工资比 Abel 高?

#①查询Abel的工资
SELECT salary
FROM employees
WHERE last_name = 'Abel'

#②查询员工的信息，满足 salary>①结果
SELECT *
FROM employees
WHERE salary>(

	SELECT salary
	FROM employees
	WHERE last_name = 'Abel'

);

#非法使用标量子查询

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT  salary
	FROM employees
	WHERE department_id = 250


);
#2.列子查询（多行子查询）★
#案例1：返回location_id是1400或1700的部门中的所有员工姓名

#①查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)

#②查询员工姓名，要求部门号是①列表中的某一个

SELECT last_name
FROM employees
WHERE department_id  <>ALL(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400,1700)


);

#3、行子查询（结果集一行多列或多行多列）

#案例：查询员工编号最小并且工资最高的员工信息


SELECT * 
FROM employees
WHERE (employee_id,salary)=(
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
);

#①查询最小的员工编号
SELECT MIN(employee_id)
FROM employees


#②查询最高工资
SELECT MAX(salary)
FROM employees


#③查询员工信息
SELECT *
FROM employees
WHERE employee_id=(
	SELECT MIN(employee_id)
	FROM employees


)AND salary=(
	SELECT MAX(salary)
	FROM employees

);

select后面
/*
仅仅支持标量子查询
*/

#案例：查询每个部门的员工个数


SELECT d.*,(

	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id = d.`department_id`

 ) 个数
 FROM departments d;


 #案例2：查询员工号=102的部门名

SELECT (
	SELECT department_name,e.department_id
	FROM departments d
	INNER JOIN employees e
	ON d.department_id=e.department_id
	WHERE e.employee_id=102
	
) 部门名;

from后面
/*
将子查询结果充当一张表，要求必须起别名
*/

#案例：查询每个部门的平均工资的工资等级
#①查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

SELECT * FROM job_grades;

#②连接①的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal

SELECT  ag_dep.*,g.`grade_level`
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;


exists后面（相关子查询）

/*
语法：
exists(完整的查询语句)
结果：
1或0
*/

SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=300000);

#案例1：查询有员工的部门名

#in
SELECT department_name
FROM departments d
WHERE d.`department_id` IN(
	SELECT department_id
	FROM employees
)

#exists

SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE d.`department_id`=e.`department_id`


);

分页查询（关键字limit)

应用场景：当查询的条目太多，一页显示不全
（实现的web项目中需要根据用户的需求提交对应的分页查询的sql语句）

语法：

select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit 【offset,】size;
注意：
offset：代表的是起始的条目索引，默认从0开始
size：代表的是显示的条目数

例：

 示例：每页显示3条记录
SELECT * FROM student LIMIT 0,3; -- 第1页
SELECT * FROM student LIMIT 3,3; -- 第2页
SELECT * FROM student LIMIT 6,3; -- 第3页

联合查询（关键字union）

作用：union:合并，联合，将多次查询结果合并成一个结果

语法：

查询语句1
union 【all】
查询语句2
union 【all】
...

特点：

1、要求多条查询语句的查询列数必须一致
2、要求多条查询语句的查询的各列类型、顺序最好一致
3、union 去重，union all包含重复项

#案例：查询中国用户中男性的信息以及外国用户中年男性的用户信息

SELECT id,cname FROM t_ca WHERE csex='男'
UNION ALL
SELECT t_id,tname FROM t_ua WHERE tGender='male';

三，DML语言的学习（操作）

1.插入（insert）

语法：

方式一：
经典插入：
insert into 表明（列名，...）
values（值1，...）;

#方式一：
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐艺昕','女','1990-4-23','1898888888',NULL,2);

#方式二：

INSERT INTO beauty(id,NAME,sex,phone)
VALUES(15,'娜扎','女','1388888888');


#3.列的顺序是否可以调换
INSERT INTO beauty(NAME,sex,id,phone)
VALUES('蒋欣','女',16,'110');


#4.列数和值的个数必须一致

INSERT INTO beauty(NAME,sex,id,phone)
VALUES('关晓彤','女',17,'110');

#5.可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致

INSERT INTO beauty
VALUES(18,'张飞','男',NULL,'119',NULL,NULL);

方式二：
insert into 表明
set 列名=值，列名=值，...

INSERT INTO beauty
SET id=19,NAME='刘涛',phone='999';

两种插入方式比较：

方式一：支持插入多行

方式一支持插入多行,方式二不支持

INSERT INTO beauty
VALUES(23,'唐艺昕1','女','1990-4-23','1898888888',NULL,2)
,(24,'唐艺昕2','女','1990-4-23','1898888888',NULL,2)
,(25,'唐艺昕3','女','1990-4-23','1898888888',NULL,2);

方式一：支持查询，方式二不支持

方式一支持子查询，方式二不支持

INSERT INTO beauty(id,NAME,phone)
SELECT 26,'宋茜','11809866';

INSERT INTO beauty(id,NAME,phone)
SELECT id,boyname,'1234567'
FROM boys WHERE id<3;

注意事项：

1.插入的值的类型要与列的类型一致或兼容
2.不可以为null的列必须插入值，可以为null的列如何插入值？
方式一：写入null填充
方式二：去掉不添加值的列
3.列的顺序可调换（必须一一对应）
4.列数和值的个数必须一致
5.可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致

2.修改（update）

语法：

修改单表的记录：

update 表名
set 列=新值，列=新值，...
where 筛选条件；

#案例1：修改beauty表中姓唐的女神的电话为13899888899

UPDATE beauty SET phone = '13899888899'
WHERE NAME LIKE '唐%';

#案例2：修改boys表中id好为2的名称为张飞，魅力值 10
UPDATE boys SET boyname='张飞',usercp=10
WHERE id=2;

修改多表的记录：

sql192:
update 表1 别名，表2 别名
set 列=值，...
where 连接条件
and 筛选条件；

sql199:
update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列=值，...
where 筛选条件；

#案例 1：修改张无忌的女朋友的手机号为114

UPDATE boys bo
INNER JOIN beauty b ON bo.`id`=b.`boyfriend_id`
SET b.`phone`='119',bo.`userCP`=1000
WHERE bo.`boyName`='张无忌';



#案例2：修改没有男朋友的女神的男朋友编号都为2号

UPDATE boys bo
RIGHT JOIN beauty b ON bo.`id`=b.`boyfriend_id`
SET b.`boyfriend_id`=2
WHERE bo.`id` IS NULL;

SELECT * FROM boys;

3.删除（delete）

单表删除：

方式一：
delete from 表名 where 筛选条件

#案例：删除手机号以9结尾的女神信息

DELETE FROM beauty WHERE phone LIKE '%9';
SELECT * FROM beauty;

方式二：
truncate table 表名；

#方式二：truncate语句

#案例：将魅力值>100的男神信息删除
TRUNCATE TABLE boys ;

多表删除：

sql192:
delete 表1的别名，表2的别名
from 表1 别名，表2 表明
where 连接条件
and 筛选条件；

sql199:
delete 表1的别名，表2的别名
from 表1 别名
inner|left|right join 表2 别名
on 连接条件
where 筛选条件；

多表的删除

#案例：删除张无忌的女朋友的信息

DELETE b
FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id` = bo.`id`
WHERE bo.`boyName`='张无忌';


#案例：删除黄晓明的信息以及他女朋友的信息
DELETE b,bo
FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`='黄晓明';

delete，truncate比较（面试题★）

1.delete 可以加where 条件，truncate 不能加

2.truncate 删除，效率更高

3.假如要删除的表中有自增长列
delete:删除后，再插入数据，自增长列的值从断点开始
truncate ：删除后，再插入数据，自增长列的值从1开始

4.truncate 删除没有返回值，delete删除有返回值

5.truncate 删除不能回滚，delete删除可以回滚

SELECT * FROM boys;

DELETE FROM boys;
TRUNCATE TABLE boys;
INSERT INTO boys (boyname,usercp)
VALUES('张飞',100),('刘备',100),('关云长',100);

四，DDL语言的学习（数据定义语言）

1.库的管理：

1）创建（create）：create database 【if not exists】库名【 character set 字符集名】;

#案例：创建库Books

CREATE DATABASE IF NOT EXISTS books ;

2）修改（alter）: alter database 库名 character set 字符集名;

RENAME DATABASE books TO 新库名;

3）更改库的字符集

ALTER DATABASE books CHARACTER SET gbk;

4）删除（drop）: drop database 【if exists】库名;

DROP DATABASE IF EXISTS books;

2.表的管理：

1）创建

create table 表明(
列名列的类型【( 长度) 约束】，
列名列的类型【( 长度) 约束】，
....
列名列的类型【( 长度) 约束】，
)

例：

CREATE TABLE book(
	id INT,#编号
	bName VARCHAR(20),#图书名
	price DOUBLE,#价格
	authorId  INT,#作者编号
	publishDate DATETIME#出版日期
);
DESC book;

2）修改

alter table 表名 add column 列名类型【first|after 字段名】;

例：

CREATE TABLE IF NOT EXISTS author(
	id INT,
	au_name VARCHAR(20),
	nation VARCHAR(10)

)
DESC author;

2.修改列的类型或约束
alter table 表名 modify column 列名新类型【新约束】;
3.修改列名
alter table 表名 change column 旧列名新列名类型;
4 .删除列
alter table 表名 drop column 列名;
5.修改表名
alter table 表名 rename 【to】新表名;

3）删除

drop table【if exists】表名;

通用的写法
drop database if exists 旧库名；
create database 新库名；
drop table if exists 旧库名；
create table 表名（）；

总结：

alter table 表名 add\drop\modify\change column 列名列类型约束；

表的复制：

仅仅复制表的结构：

create table 表名 like 旧表;

复制表的结构+数据：

create table 表名
select 查询列表 from 旧表【where 筛选】;

仅仅复制某些字段：

create table 新表名
select 列名
from 被复制表明
where 0;

例：

INSERT INTO author VALUES
(1,'村上春树','日本'),
(2,'莫言','中国'),
(3,'冯唐','中国'),
(4,'金庸','中国');

SELECT * FROM Author;
SELECT * FROM copy2;
#1.仅仅复制表的结构

CREATE TABLE copy LIKE author;

#2.复制表的结构+数据
CREATE TABLE copy2 
SELECT * FROM author;

#只复制部分数据
CREATE TABLE copy3
SELECT id,au_name
FROM author 
WHERE nation='中国';


#仅仅复制某些字段

CREATE TABLE copy4 
SELECT id,au_name
FROM author
WHERE 0;

3.常见的数据类型

1）数值型

★整数类型

分类：tinyint,smallint,mediumint,int/integer,bigint
字节数：1， 2， 3， 4， 8

特点：

1.未设置无符号还是有符号，默认是有符号，
通过添加unsigned关键字设置无符号
2.如果插入的数值超出整型的范围，会报out of range异常，并且插入临界值
3.如果不设置长度，会有默认长度
长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用

★小数

浮点型：float(M,D),double(M,D)

定点型：dec(M,D),decimal(M,D)

特点：
①M代表整数部位+小数部位的个数，D代表小数部位
②如果超出范围，则报out or range异常，并且插入临界值
③M和D都可以省略，但对于定点数，M默认为10，D默认为0
④如果精度要求较高，则优先考虑使用定点数

原则：所选择的类型越简单越好，能保存数值的类型我越小越好

2）字符型

char、varchar、binary、varbinary、enum、set、text、blob

char：固定长度的字符，写法为char(M)，最大长度不能超过M，其中M可以省略，默认为1
varchar：可变长度的字符，写法为varchar(M)，最大长度不能超过M，其中M不可以省略

3）日期型

year年
date日期
time时间
datetime 日期+时间 8
timestamp 日期+时间 4 （比较容易受时区、语法模式、版本的影响，更能反映当前时区的真实时间）

4.常见的约束

含义：一种限制，用于限制表中表的数据，为了保证表中的数据的准确性和可靠性

1.六大约束（★）

1.NOT NULL:(非空)，用于保证该字段的值不能为空
比如姓名，学号等
2.DEFAULT：(默认)，用于保证该字段有默认值
比如性别
3.PRIMARY KEY:(主键)，用于保证该字段的值具有唯一性，并且非空
比如学号，员工编号等
4.UNIOUE:(唯一)，用于保证该字段的值具有唯一性，可以为空
比如座位号
5.CHECK：(检查)约束【MySQL不支持】
6.FOREIGN KEY:(外键) 用于限制两个表的关系，用于保证该字段的值
必须来自于主表的关联列的值
在从表添加外键约束，用于引用主表中某列的值
比如学生表的专业编号，员工表的部门编号，员工表的工种编号等

2.主键和唯一比较

3.外键

1、用于限制两个表的关系，从表的字段值引用了主表的某字段值
2、外键列和主表的被引用列要求类型一致，意义一样，名称无要求
3、主表的被引用列要求是一个key（一般就是主键）
4、插入数据，先插入主表
删除数据，先删除从表
可以通过以下两种方式来删除主表的记录
#方式一：级联删除
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;

#方式二：级联置空
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;

4.添加约束的时机

语法：

create table 表名(
   字段名字段类型 not null,#非空
   字段名字段类型 primary key,#主键
   字段名字段类型 unique,#唯一
   字段名字段类型 default 值,#默认
   constraint 约束名 foreign key(字段名) references 主表（被引用列）//外键
)

创建表时添加约束

支持类型       可以起约束名
列级约束       除了外键不可以
表级约束       除了非空和默认   可以，但对主键无效

列级约束可以在一个字段上追加多个，中间用空格隔开，没有顺序要求

1.添加列级约束 /* 语法：

直接在字段名和类型后面追加约束类型即可。

只支持：默认、非空、主键、唯一

USE students;
DROP TABLE stuinfo;
CREATE TABLE stuinfo(
	id INT PRIMARY KEY,#主键
	stuName VARCHAR(20) NOT NULL UNIQUE,#非空
	gender CHAR(1) CHECK(gender='男' OR gender ='女'),#检查
	seat INT UNIQUE,#唯一
	age INT DEFAULT  18,#默认约束
	majorId INT REFERENCES major(id)#外键

);


CREATE TABLE major(
	id INT PRIMARY KEY,
	majorName VARCHAR(20)
);

#查看stuinfo中的所有索引，包括主键、外键、唯一
SHOW INDEX FROM stuinfo;

2.添加表级约束

语法：在各个字段的最下面【constraint 约束名】约束类型(字段名)

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT,
	

	CONSTRAINT pk PRIMARY KEY(id),#主键
	CONSTRAINT uq UNIQUE(seat),#唯一键
	CONSTRAINT ck CHECK(gender ='男' OR gender  = '女'),#检查
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键

);

SHOW INDEX FROM stuinfo;


#通用的写法：★

CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	stuname VARCHAR(20),
	sex CHAR(1),
	age INT DEFAULT 18,
	seat INT UNIQUE,
	majorid INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)

);

修改表时添加或删除约束

1）非空
添加非空
alter table 表名 modify column 字段名字段类型 not null;
删除非空
alter table 表名 modify column 字段名字段类型 ;

2）默认
添加默认
alter table 表名 modify column 字段名字段类型 default 值;
删除默认
alter table 表名 modify column 字段名字段类型 ;

3）主键
添加主键
alter table 表名 add【 constraint 约束名】 primary key(字段名);
删除主键
alter table 表名 drop primary key;

4）唯一
添加唯一
alter table 表名 add【 constraint 约束名】 unique(字段名);
删除唯一
alter table 表名 drop index 索引名;

5）外键
添加外键
alter table 表名 add【 constraint 约束名】 foreign key(字段名) references 主表（被引用列）;
删除外键
alter table 表名 drop foreign key 约束名;

5.标识列

1）创建表时设置自增长列
create table 表(
字段名字段类型约束 auto_increment
)

2）修改表示设置自增长列

alter table 表 modify column 字段名字段类型约束 auto_increment

3）删除自增长列

alter table 表 modify column 字段名字段类型约束

五，TCL语言的学习（事务控制语言）
Transaction Control Language

1.查询相关存储引擎：show engiens

2.设置回滚点(关键字savepoint 搭配rollback to 使用)

★3.事务的ACID属性

4.事务的创建

1）隐式事务：指事务没有明显的开启和结束的标记
比如insert，update，delete语句等等

2）显示事务：事务具有明显的开启和结束的标记
前提：必须先设置自动提交功能为禁用

5.并发事务

事务的并发问题是如何发生的？
多个事务同时操作同一个数据库的相同数据时

并发问题：

解决并发问题：

★delete和truncate在事务中使用时的区别

DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。
TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

这篇关于MySQL之MySQL服务的使用以及四种语言分类的DQL（查询），DML（操作）, DDL（数据定义），TCL（事务控制）的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！

MySQL之MySQL服务的使用以及四种语言分类的DQL（查询），DML（操作）, DDL（数据定义），TCL（事务控制）

前言

一，数据库和SQL概述

1.简介

2.相关概念（DB,DBMS,SQL）

3.MySQL服务的使用

二，DQL语言（查询）

1.简单查询

2.条件查询

3.排序查询

4.分组查询

5.多表查询（★）

连接查询（★）

子查询（★）

分页查询（关键字limit)

联合查询（关键字union）

三，DML语言的学习（操作）

1.插入（insert）

2.修改（update）

3.删除（delete）

四，DDL语言的学习（数据定义语言）

1.库的管理：

2.表的管理：

3.常见的数据类型

4.常见的约束

5.标识列

五，TCL语言的学习（事务控制语言） Transaction Control Language

相关编程文章

五，TCL语言的学习（事务控制语言）
Transaction Control Language