mysql distinct 多个字段_MySQL基础学习笔记

MySQL基础学习

1. 数据库相关概念

数据库是持久化数据的一种介质，可以理解成存储和管理数据的仓库。这里的持久化（persistence）指的是把数据保存到可掉电式存储设备中以供之后使用。

使用文件存储数据，查找不够方便；使用数据库管理数据可以：

• 可将数据持久化到硬盘
• 可存储大量数据
• 方便检索
• 保证数据的一致性和完整性
• 安全，可共享（三层验证）
• 通过组合分析，可以产生新数据。

常见的数据库产品有：Oracle、DB2、SQL Server、MySQL。

数据库中的相关概念有：

• DB（数据库，database）：存储数据的“仓库”。保存了一系列有组织的数据。（格式统一的数据）
• DBMS（数据库管理系统），DB是由DBMS创建和操作的容器。
• SQL（结构化查询语言），与数据库通信的语言。

数据库存储数据的特点：

• 将数据放到表中，将表再放在库中，每个数据库有多个表，==表名具有唯一性==。
• 表由列组成，称为==字段==，相当于“属性”；表中的数据是按行存储的，相当于==“对象”==

MySQL是一种开放源代码的关系型数据库管理系统。它的优点在于：

• 成本低：开放源代码，一般可以免费试用
• 性能高：执行快
• 简单：容易安装使用

DBMS分为两类：

• 一是基于共享文件系统的DBMS（Access）
• 二是基于客户机——服务器的DBMS C/S(client/server)，（MySQL、Oracle、SQL Server），要装服务端和客户端。

2. MySQL的使用

使用管理员权限的命令行启动/停止MySQL服务：

net start MySQL5.5
net stop MySQL5.5

使用命令行进行MySQL客户端登录：

mysql -h 主机名 -P 端口号 -u 用户名 -p密码
# 主机名 本地的话为localhost，端口号3306，用户名root，
# 连本地可以简略写为：
mysql -u root -p ...

MySQL命令要以；结尾。

MySQL常用命令

show databases; # 展示现有数据库 
use xx; # 使用xx数据库
show tables; //展示该数据库的表
show tables from xxx; # 展示xxx数据库的表，此时的位置还是xx数据库
select database(); # 查看当前所在的数据库

create table stuinfo( 
stuid int,
stuname varchar(20),
gender char,
borndata datetime); # 创建一个新的表，结构如上。

desc stuinfo; # 描述这个表stuinfo的结构。

select * from xx; # 从xx表中查询数据

insert into xx values(xxxxx); # 向xx中插入数据

update stuinfo set borndate='xxx' where stuid
 = 2; # 对于表stuinfo中，stuid = 2的数据，修改它的borndata字段为xxx

delete from stuinfo where stuid = 1; # 删除表中stuid为1的一行数据

drop table xx; # 删除xx表

SQL语法注意事项：

• SQL语句中不区分大小写，建议库名，表名，字段名大写
• 注释写法，单行注释#和-- ，多行注释/ /

3. DQL语句

SQL有几种：

• DDL（数据定义语言，定义数据库对象：库、表、列等）、create/drop/alter
• DML（数据操作语言，定义数据库记录，即数据）、insert/update/delete
• DCL（数据控制语言，定义访问权限和安全级别）、
• DQL（数据查询语言，用来查询数据记录）、select

3.1 基础查询

select 查询列表 from 表名;

查询到的结果集是一个虚拟表，不是真实存在的一个表。select后面的查询列表，可以由多个部分组成，中间用逗号隔开。比如：

select 字段1,字段2,表达式 from 表名;
select first_name from employees; # 先进行from，看看是否能找到这个表，然后再找first_name
select 100; #查询常量
select 100%3; #查询表达式
select first_name,last_name from employees; #查询多个字段
select * from employees; #查询所有字段
select database(); 
select version(); #查询函数（调用函数，获取返回值）
select user() as xxx; #对user起别名为xxx，xxx可以用引号引起来
# 上面的as可以换成空格。


select first_name + last_name as "姓 名" from employees;
/*
  mysql中的加号+只有加法作用：
  1.两个操作数为数值型，正常加
  2.当其中一个为字符型的时候，会强制转换为数值型，如果无法转换，则直接当做0
  3.其中一个操作数为null，结果为null
  因此上面的操作无法进行，将两个字段的数据组合，使用拼接函数concat
  需要注意，concat的时候，如果其中一个为null，那么最后的结果也会变成null
  需要使用ifnull函数判断
*/
select concat(first_name,last_name) as "姓 名" from employees;


# distinct 的使用
select distinct department_id from employees;
# 加上distinct关键字，查询的是去重之后的数据

# desc查看表的结构
desc employees;
# 也可以使用
show columns from employees;

#ifnull函数，a可以是字段、表达式......
ifnull(a,xx) #如果a是null，返回xx，否则正常返回a

查询的列表可以是：字段、表达式、常量、函数等。

当命令特别长的时候，使用F12可以自动换行对齐。

3.2 条件查询

根据条件过滤来查询语句。

语法：

select xxx from xxxx where 条件;
SELECT * FROM employees WHERE salary >= 12000;

执行顺序是，先查询from，找到表，然后对虚拟的结果表进行where筛选，最后进行select。

在where语句中，有如下关系运算符及关键字：

• ＞、<、>=、<=、=（只需要一个）、<>（不等于，写!=也行）。
• AND、OR、NOT。
• BETWEEN...AND、IN、LIKE、IS NULL。（模糊查询）

# 1. 关系运算
SELECT * FROM employees WHERE department_id <> 100;
SELECT first_name,last_name FROM employees WHERE salary < 15000;

# 2. 逻辑运算
SELECT 
  first_name,
  last_name,
  department_id,
  email 
FROM
  employees 
WHERE NOT (
    department_id >= 50 
    AND department_id <= 100
  ) ;
###
SELECT 
  * 
FROM
  employees 
WHERE commission_pct > 0.03 
  OR (
    employee_id >= 60 
    AND employee_id <= 110
  ) ;

# 模糊查询
/* 
  3. like
  一般与通配符进行匹配，对字符型数据进行部分匹配查询
  常见的通配符：_ 表示任意单个字符
             % 表示任意多个字符(0个也行)
  当要查询下划线时，不把它当做通配符时，需要加转义符''
  同样可以使用sql语句设置某个字符为转义符，escape 'x'
*/ 
SELECT * FROM employees  WHERE last_name LIKE '%a%'; #查找lastname中有a的数据
SELECT * FROM employees  WHERE last_name LIKE '__e%';#查找lastname第三个字符为e的数据
SELECT * FROM employees  WHERE last_name LIKE '_$_%' ESCAPE '$';
# 将'$'设置为转义符，查询时'$_'表示查询下划线。

/*
    4. in
    查询某字段的值，是否属于指定的列表之内。
    a in (常量值1，常量值2，......) 查询a字段的值是否在几个常量值中
    a not in ...是否不在...
*/
SELECT * FROM employees WHERE department_id IN (30,50,90);
SELECT * FROM employees WHERE job_id NOT IN ('SH_CLERK','IT_PROG');

/*
    5. between and
    判断某字段的值，是否在某个区间之内
    也有not between and
*/
SELECT * FROM employees WHERE department_id  BETWEEN 30 AND 90;

/*
    6. is null/is not null
    查询是不是null

*/
SELECT * FROM employees WHERE commission_pct IS NULL;

3.3 排序查询

语法与上面的查询语句一样，后面加上order by （排序列表）即可。

select 查询列表 from 表名 （where 筛选条件可有可无） order by 排序列表

执行顺序是：

• 先进行from查询
• 然后where筛选
• 然后select查出来
• 最后order by进行排序

SELECT last_name,salary FROM employees ORDER BY salary;
/*
    1.排序列表可以是单个字段、多个字段、别名、表达式、函数、列数（常量）、以及这几个的组合。
    2.默认升序排序，asc
      降序排序，使用desc
*/
SELECT * FROM employees ORDER BY salary ASC;
SELECT * FROM employees ORDER BY salary DESC;
# 使用别名"年薪"进行排序
SELECT 
  * , salary * 12 * (1+ IFNULL(commission_pct, 0)) 年薪
FROM
  employees 
ORDER BY  年薪 DESC ;

#函数LENGTH(xx)，可以获取xx字符的长度
SELECT last_name FROM employees ORDER BY LENGTH(last_name) ;

#按照多个字段进行排序，先按照工资降序排序，同样工资的按照姓名长度升序
SELECT last_name,salary FROM employees ORDER BY salary DESC , LENGTH(last_name) ;

#按照选择的第2列，即email进行排序
SELECT first_name,email FROM employees ORDER BY 2 ;

3.4 常见函数

MySQL中常见的函数有：

• 字符函数
• 数学函数
• 日期函数
• 流程控制函数

3.4.1 字符函数

• concat拼接字符。concat(s1,s2...)，将s1和s2...拼到一起。concat_ws(s,s1,s2...)功能类似，只不过在拼接的时候，每个字符串之间都加上字符s。
• length获取字节长度（中文一个字符三个字节），char_length获取字符长度（即字符的个数，一个中文也算一个）。
• substring(s,index,len)，截取字符串，从字符串s的idx位置开始截取len个字符。写成substr也行。注意，==SQL中起始的索引从1开始==。不使用len，默认为到最后。
• instr(str,substr)，获取子串字符substr第一次出现的索引。
• trim(s)，去掉s字符串开头和结尾的空格。trim(s1 from s)，去掉s字符串开头和结尾的s1字符。
• lpad/rpad (str,len,pad)，左填充/右填充，用字符串pad对str左/右进行填充，直到str长度为len个字符。
• upper(s)/lower(s)，将字符串s中所有的字符大写/小写。
• left(s,n)/right(s,n)，返回字符串s左边/右边的n个字符。
• strcmp(s1,s2)，比较两个字符串s1和s2，按照字典序，返回正负1和0。

# 示例
SELECT CONCAT_WS(',',first_name,last_name) AS 姓名 FROM employees;
SELECT SUBSTR('hello word',1,5);
SELECT INSTR('hello word hello','hello');
SELECT TRIM('    hello    ');
SELECT TRIM('ab' FROM 'abhelloab');
SELECT LPAD('x',10,'a');
SELECT CONCAT_WS('_',UPPER(first_name),last_name) AS output FROM employees;
SELECT STRCMP('ac','abcbbs'); #返回1

3.4.2 数学函数

• abs(x)，求绝对值。
• ceil(x)，返回大于x的最小整数值，向上取整。
• floor(x)，向下取整。
• round(x,y)，返回x的，取y位小数的四舍五入值，不写y，默认四舍五入取整。
• truncate(x,y)，截断，与上面一致，直接取y位小数，不写y，强制取整。
• mod(x,y)，返回x/y的余数。
• sqrt(x)，返回x的平方根。
• pow(x,y)，返回x的y次方。

#示例
SELECT CEIL(1.231); 
SELECT ROUND(1.231,2);
SELECT MOD(5,2);

3.4.3 日期函数

• now()，获取当前的日期和时间。
• curdate()，获取当前的日期。
• curtime()，获取当前的时间。
• datadiff(date1,date2)，返回两个日期的差。
• date_format(date,fmt)，按照fmt格式来格式化日期date。
• str_to_date(str,fmt)，按照fmt格式来将str解析成一个日期。
• fmt的格式比较复杂，一般记住几个即可。%Y表示4位数年份；%M，%m，%b表示月名，数字月份，缩写月份，%d表示两位数日；%H表示24小时制的小时，%i表示分钟，%S表示秒，%W表示星期...

# 示例
SELECT NOW();
SELECT CURDATE();
SELECT CURTIME();
SELECT DATEDIFF('2020-9-24','1997-3-21');
SELECT DATE_FORMAT('2020-9-24','%Y年%m月%d日');
##
SELECT 
  last_name,
  DATE_FORMAT(hiredate, '%Y年%m月%d日') AS 入职日期 
FROM
  employees 
ORDER BY hiredate ;
##

3.4.4 流程控制函数

• if(value,t,f)，如果value成立，返回t，否则返回f。
• ifnull(value1,value2)，如果value1不为null，返回value1，否则返回value2。
• case 表达式 when 值1 then 结果1 when 值 2 then 结果 2 ... else 结果n end。类似switch语句，当表达式为值1,2,3,...分别对应不同操作。
• case when 条件1 then 结果1 when 条件2 值2 ... else 值n end。即if，elif，elif...else语句。

# 示例
SELECT 
  last_name,
  salary * 12 * IFNULL(commission_pct, 0) AS 奖金 
FROM
  employees 
ORDER BY 奖金 DESC ;

##
SELECT 
  last_name,
  salary,
  CASE
    WHEN salary >= 20000 
    THEN 'A' 
    WHEN salary >= 15000 
    THEN 'B' 
    WHEN salary >= 10000 
    THEN 'C' 
    ELSE 'D' 
  END AS 级别 
FROM
  employees 
ORDER BY salary DESC;
##

3.5 分组（聚合）函数

将多个值进行运算，返回，做纵向运算的函数，往往将一组数据进行统计计算，最终得到一个值。

• sum(字段名)，求和。
• avg(字段名)，求平均值。
• count(字段名)，统计该字段不为null的数据个数。字段名**可以为。还可以*搭配distinct来实现去重的统计。
• max(字段名)/min(字段名)，返回最大或最小值，如果为字符串，那么按照字符串排序运算。

# 示例
SELECT SUM(salary),MAX(salary),MIN(salary),COUNT(commission_pct) FROM employees;
# 计算有员工的部门个数
SELECT COUNT(DISTINCT department_id) FROM employees;

3.6 分组查询

有时不是对所有的数据进行查询，而是需要对数据进行分组，那么需要使用GROUP BY子句来进行。通常，分组函数搭配分组查询使用。

group by后面接分组条件。

==注意分组条件作为筛选条件的，一定是先分组再筛选，使用having。==

# 示例
# 根据部门编号分组，计算每个部门的平均薪水。
SELECT 
  AVG(salary),
  department_id 
FROM
  employees 
GROUP BY department_id ;
## 查询每个部门的人数，并按照降序排序
SELECT 
  department_id,
  COUNT(*) AS 部门人数 
FROM
  employees 
GROUP BY department_id 
ORDER BY 部门人数 DESC ;

## 查询每个领导的员工中，有奖金的员工的平均工资，先筛选，后分组
SELECT AVG(salary) AS 平均工资,manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

## 查询哪个部门的员工个数＞5，那么应该先分组，再筛选，
## where应该放在group by后面，这时不能使用where，应该使用having。
SELECT COUNT(*) AS 员工个数 ,department_id 
FROM employees
GROUP BY department_id
HAVING 员工个数 > 5;

# 每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT job_id , MAX(salary) AS 最高工资
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING 最高工资 > 12000;

3.7 连接查询（sql92语法）

3.7.1 内连接

• 等值连接（连接条件是=）。mysql select 查询列表 from 表名1 别名1、表名2 别名2、... where 等值连接的连接条件
  为了解决多表中字段名重名的问题，通常会为表起别名，来提高语义性。表的顺序无要求，可以进行多个表的连接。
• 非等值连接（连接条件不是等号）。
• 自连接（自己和自己连接）。

# 示例
# 1.等值连接，找到员工姓名与对应部门名字，姓名在employees中，只有部门编号，需要内连接。
SELECT 
  last_name,
  department_name 
FROM
  employees e,
  departments d 
WHERE e.department_id = d.department_id ; #起别名之后，用别名即可，不要用表名了。

# 在连接之后（已经有一个where了），进行筛选，使用and关键字。
SELECT 
  `department_name`,
  `city`
FROM
  `departments` d ,
  `locations` l
WHERE d.`location_id` = l.`location_id`
AND d.department_id > 100;

# where连接，and是分组的筛选，group by分组。
SELECT department_name,d.manager_id,MIN(salary) AS 最低工资
FROM departments d,employees e
WHERE d.`department_id` = e.`department_id`
AND e.`commission_pct` IS NOT NULL
GROUP BY e.`department_id`;

# 3.自连接，把一张表当做两张或多张表，查询员工编号，姓名以及对应的领导的编号姓名
SELECT e.employee_id,e.last_name,e.manager_id,m.`last_name`
FROM employees e,employees m
WHERE e.`manager_id` = m.`employee_id`;

3.8 连接查询（sql99语法）

3.8.1 内连接

与sql92语法差不多，只有一点改变。

select 查询列表
from 表名1 别名
inner join  表名2 别名2  # inner 可以省略
on 连接条件
where 筛选条件
group by 分组列表
having 分组和筛选
order by 排序

sql99，使用join关键字代替了之前的逗号，并且将连接条件和筛选条件进行了分离（一个用on，一个用where），提高了阅读性。

3.8.2 外连接（左连接、右连接）

内连接针对的是A和B两个表中都有的元素，进行连接。

而如果想查某个表A中的哪些元素，在B中不存在之类的要求，使用外连接。

外连接将两个表分为主从表，对主表中所有的数据在从表中进行查询，对每个元素，查询到与连接条件相匹配的，则成功；如果没有查询到与之匹配的，返回null。

因此，==外连接之后的结果，会显示主表中的所有数据，==只不过有的数据对应的数据可能是null，再使用where语句筛选，即可找到为null的数据。

==一般用来查询主表中有的，从表中没有的记录数据。==

左连接，左边为主表；右连接，右边为主表。

# 左右连接的语法
select xx
from 表1 别名 left / right (outer) join 表2 别名
on 连接条件
where 筛选条件
# 示例
# 查询没有员工的部门编号及部门名称
SELECT d.department_id,d.department_name
FROM departments d LEFT JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;

3.9 子查询

当一个查询语句中，又嵌套了另一个完整的select语句，被嵌套的select语句称为子查询或内查询，外面的select被称为外查询。

子查询不一定嵌套在select内部，也可能嵌套在其他语句的内部。

按照子查询出现的位置，可以进行分类：

• 放在select后面，要求：子查询的结果为单行单列（标量子查询）
• 放在from后面，要求：子查询的结果可以为多行多列（表子查询）
• 放在where或having后面，要求：子查询的结果必须为单列
• 放在exists后面，要求：子查询结果必须为单列（相关子查询）

特点：

• 子查询放在条件中，必须放在条件的右侧
• 子查询一般放在小括号中，增加可读性
• 子查询的执行优先于主查询
• 单行子查询对应了单行操作符：大于小于号等；多行子查询对应了多行操作符：any/some等

# 示例
# 1. 查询和alotkey相同部门的员工姓名和工资
SELECT 
  last_name,
  salary 
FROM
  employees 
WHERE department_id = ( 
  SELECT 
    department_id 
  FROM
    employees 
  WHERE last_name = 'zlotkey') ;
# 2. 查询工资比公司平均工资高的员工的员工号姓名和工资
SELECT employee_id,last_name,salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

多行操作符：

• in：查询某字段是否在指定列表内，x in (子查询1，子查询2，...)，判断x是否在几个子查询的结果中。
• any/some：判断某字段的值是否满足其中任意一个。x > any(10,30,50)，x满足三个条件中任意一个即可。这里面的条件换为多个子查询，即满足一个子查询结果即可。
• all：判断某字段的值是否满足其中所有的条件。x > all(子查询1，子查询2...)。

# 示例
# 1.返回location_id是1400或1700的所有部门中所有员工的姓名
SELECT 
  last_name 
FROM
  employees 
WHERE department_id IN 
  (SELECT 
    department_id 
  FROM
    departments 
  WHERE location_id IN (1400,1700)); 
  # 查到所有的符合location条件的部门id，然后查满足条件的部门的员工姓名

# 2.exsits
select exists(子查询); #如果子查询查到了数据，则返回1，如果没查到，返回0

3.10 分页查询

分页查询的语句放在整个查询语句最后，执行顺序也是最后。

select 查询列表
from 表
where 条件
limit 起始查询条目索引,查询的条目数

• 起始条目的索引从0开始，默认为0
• limit后面支持两个参数，索引和条目数

# 示例
# 1.查询前5条员工信息
SELECT * FROM employees LIMIT 5;
# 2.查询有奖金的员工中，工资较高的11-20名的员工信息
SELECT * 
FROM employees 
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10,10;

3.11 联合查询

当查询结果来自于多张表，但是多张表之间没有关联，这个时候往往使用联合查询。

# 使用union将两个查询语句合并即可，得到总的结果
# 注意的是，这里面两个查询语句查询列表的列数需要一致，才能将结果联合成一张表
# union会自动去重，如果不需要，则使用union all，这样就会得到有重复的结果。
select 查询列表 from 表1 where 筛选条件
union
select 查询列表 from 表1 where 筛选条件

4. DDL语句

DDL语句，数据定义语言，用来操作和管理数据库和数据表。

4.1 库的管理

# 1.创建数据库xxx,如果这个xxx数据库不存在，那么创建一个xxx数据库。
CREATE DATABASE IF NOT EXISTS xxx;
# 2.删除数据库xxx，如果这个xxx存在，删除该数据库。
DROP DATABASE IF EXISTS xxx;

4.2 表的管理

• 创建表

# 1.创建表
CREATE TABLE IF NOT EXISTS表名(
    字段名 字段类型 字段约束(写不写都行)
    ...
);
# 示例
CREATE TABLE stus(
    NAME VARCHAR(20),
    age INT,
    gender VARCHAR(10)
);

MySQL中常见的数据类型：

• int：整型。
• double/float：double(5,2)表示必须有两位小数，一共最多五位数，因此最大为999.99。
• decimal：浮点型，精度更高，表示钱方面使用该类型，不会出现精度缺失的问题。
• char：固定长度字符串类型，char(n)表示最大字符个数，不管如何都开辟n个字符的空间，n可以省略不写，默认为1。
• varchar：可变长度字符串，varchar(n)中n表示最大字符个数，根据实际长度开辟空间。
• text：字符串类型，用于存储较长的文本。
• blob：存储图片数据。
• data：日期类型，yyyy-MM-dd。
• time：时间类型，hh:mm:ss。
• timestamp/datatime：时间戳类型，日期+时间。datatime保存的时间范围较大，占8字节，timestamp占4字节。

MySQL中常见约束

约束用于限制表中字段的数据，进一步保证数据表的数据是一致可靠的。

# 常见约束，约束是可以叠加的，对一个字段可以施加多个约束
# 1.非空约束，限制该字段为必填项。
NOT NULL
# 2.默认，如果该字段没有显式插入值，则直接为默认值。
DEFAULT
# 3.主键约束，用于限制该字段的值不能重复，即该字段为主键，主键默认不能为空。一个表只能有一个主键，如果多个字段为主键，则这几个字段合在一起为组合主键，只要这几个字段不都相同就可。
PRIMARY KEY
# 4.唯一约束，用于限制该字段的值不能重复，但不是主键，可以为空。
UNIQUE
# 5.检查约束，用于限制该字段的值，必须满足指定条件，MySQL不支持这个操作。
CHECK(条件)
# 6.外键约束，用于限制两个表的关系，要求外键列表的值必须来自主表的关联列、
# 主表的关联列与从表的关联列的类型必须一致，意思一样，名称无要求
# 主表的关联列要求必须是主键
FOREIGN KEY
# 语法为：xx为这个外部键的名字，自己起，可省略，则系统自己默认起名
# xxx为该表受约束的字段名，引用了xxtable这个表中的xxxx字段，
# 即xxx的值必须为xxxx出现过的值。
CONSTRAINT xx FOREIGN KEY (xxx) REFERENCES xxtable(xxxx)

• 修改表
  使用ALTER关键字。

# 语法
ALTER TABLE 表名 ADD / MODIFY / CHANGE / DROP COLUMN 字段名 字段类型 字段约束
# 1.修改表名
ALTER TABLE XX RENAME TO XXX;
# 2.添加字段
ALTER TABLE XX ADD COLUMN 字段名 字段类型 字段约束;
# 3.修改字段名(相当于把旧的删了，生成一个新的)
ALTER TABLE XX CHANGE COLUMN 旧字段名 新字段名 字段类型 字段约束;
# 4.修改字段的类型
ALTER TABLE XX MODIFY COLUMN 字段名 字段类型 字段约束;
# 5.删除某个字段
ALTER TABLE XX DROP COLUMN 字段名;

• 删除表

DROP TABLE IF EXISTS XX(表名); #删除表
DESC xx(表名); #查看表结构

• 复制表

# 以旧表的结构新建一个新的表，新的表与旧表有一样的结构，只复制表结构，不复制数据
CREATE TABLE newtable LIKE oldtable;
# 复制表的结构+数据，根据select语句不同，可以复制旧表的不同的部分
CREATE TABLE newtable SELECT * FROM oldtable;

5. DML语句

DML，即数据操作语言，主要有关键字：insert update delete，完成表中的数据的增删改。

5.1 插入数据

# 向表中插入数据，字段和值的类型需要一一对应，且遵循约束的限制。
# 数值型的值，不用单引号，但是非数值的值必须使用单引号
# 字段名的顺序不需要与表中顺序一致，但是字段和值必须对应
# 对于有默认值的字段，在values中可以直接写成DEFAULT
# 如果省略了字段名，那么values值中个数必须与表中的一致。
insert into 表名(字段1，字段2，...) values (值1，值2，...);

# 插入多行
insert into 表名(字段1，字段2，...) values (值1，值2，...),(值1，值2，...),(值1，值2，...),(值1，值2，...)...;

对于主键这种每行记录需要不一样的字段，可以设置自增长列，使得该列自动增长。这样在插入数据的时候，对应的位置的values可以写为NULL。

自增长列的数据类型需要为数值型，一个表中最多只能有一个自增长列。

# 设置自增长字段，在创建表的时候，在约束后面加AUTO_INCREMENT，比如
CREATE TABLE grade(
    id INT PRIMARY KEY AUTO_INCREMENT,
    sname VARCHAR(20)
);

5.2 修改数据

# 修改表中的数据
update 表名 set 列名1 = '值1',列名2 = '值2',...
where 筛选条件  #可有可没有

# 多个表的修改，即将两个表连接，然后修改
update 表1 别名 (left inner right) join 表2 别名
on 连接条件 set 列名1 = '值1',列名2 = '值2',...
where 筛选条件

5.3 删除数据

使用delete语句和truncate语句。

• delete可以添加where筛选，truncate不可以。
• truncate效率较高。
• 如果有带自增长列的表，使用delete删除之后，重新插入数据，自增长列从断点开始；使用truncate删除之后，重新插入数据，自增长列从头开始。
• delete删除数据，会返回受影响的行数，truncate不返回。
• delete删除数据，可以支持事务回滚（即可以还原），truncate不支持。

# 删除数据（整行数据）
delete from 表名 where 筛选条件;
# 删除表中所有数据，无筛选，全删了
truncate table 表名;

6. 事务概述

事务属于TCL控制语言（Transaction control language），它是由一条或多条sql语句构成，绑定在一起，要么全部执行成功，要么全部执行失败。

防止第一条成功了，第二条失败了造成的错误，使得几条语句绑定，必须都成功或都失败。

事务的四大特性（ACID）：

• 原子性（atomicity），事务中的所有操作都不可再分。
• 一致性（consistency），事务执行前后，数据库状态不变。
• 隔离性（isolation），事务在进行时，不同的事务应该隔离开来，使得并发的事务不会互相干扰。
• 持久性（durability），事务一旦提交成功，事务中的所有数据操作必须持久化道数据库中。、

MySQL中的事务步骤

# 1.取消隐式事务自动开启（隐式事务有insert等）
set autocommit = 0;
# 2.开启事务
start transaction;
... #3.事务的sql语句
# 4.结束事务(提交或回滚)
commit 或 rollback;