2021-11-22 MySQL_mysql表中的总价值怎么写-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_44700775/article/details/121463959

本文详细介绍了数据库的基础知识，包括关系型和非关系型数据库的区别，以及MySQL、Oracle等常见数据库的操作。重点讲解了数据库表的设计，强调了主键、外键、索引、视图和数据类型的重要性。此外，还讨论了事务处理、数据冗余和表间关系，以及外键在数据库中的作用。最后，探讨了DML操作、数据类型、日期时间处理和字符串函数。通过实例展示了如何创建、修改和管理数据库，以及如何进行数据的增删改查。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.数据库分类

关系型数据库:行、列（SQL）
- MySQL、Oracle、SqlServer、DB2、SQLlite
- 通过表和表之间,行和列之间的关系进行数据的存储
非关系型数据库:{key:value} （NoSQL） Not Only SQL
- Redis、MongoDB、HBase
- 非关系型数据库,对象存储，通过对象的自身的属性来决定

2.库操作

-- 操作数据库 > 操作数据库中的表 > 操作数据库中表的数据
------------ 库操作 -----------
-- 命令行连接数据库
mysql -hlocalhost -uroot -p123456
-- 创建数据库
create database dtmapp;
create database if not EXISTS dtmapp; -- 如果不存在就创建
-- 创建数据库 指定字符集
create database 数据库名 character set utf8/gbk;
-- 查看所有数据库
show databases;
-- 查看创建数据库语句
show create database dtmapp;
-- 进入到mysql数据库下
use 数据库名;
-- 删除数据库
drop database 数据库名;

update mysql.user u set u.authentication_string = PASSWORD('123456') where u.`User`='root' and Host = 'localhost'; -- 修改密码

FLUSH PRIVILEGES; -- 刷新权限

3.表操作

-- 1. 创建表
-- 格式：create table 表名(字段1名 字段1类型,字段2名 字段2类型,.....);
CREATE TABLE [IF NOT EXISTS] `表名`(
	`字段名` 字段类型 [属性] [索引] [注释]，
	`字段名` 字段类型 [属性] [索引] [注释]，
	`字段名` 字段类型 [属性] [索引] [注释]，
	PRIMARY KEY(`字段名`)
) [表类型]ENGINE=INNODB [字符集设置]DEFAULT CHARSET=utf8 [注释]
create table student(id int,name varchar(10),chinese int,math int,english int);
-- 判断是否存在,不存在则创建
create table IF NOT EXISTS student(id int,name varchar(10),chinese int,math int,english int);
-- 创建表指定引擎和字符集
-- 格式： create table 表名(字段1名 字段1类型,字段2名 字段2类型,.....) engine=innodb/myisam charset=gbk/utf8;
-- 引擎：1. innodb：默认，支持事物、外键等高级操作;2. myisam： 只支持基础的增删改查操作，不支持事物、外键等高级操作。
-- 2. 查看所有表
show tables;
-- 3. 查看建表语句
show create table dtmapp;
-- 4.删除表
drop table 表名;
-- 判断是否存在,如果存在就删掉
drop table if EXISTS student;
-- 5.修改表名
rename table 原名 to 新名;
-- 6.修改表引擎和字符集
alter table 表名 engine=myisam/innodb charset=utf8/gbk;
-- 7.查看表结构
desc 表名;
-- 8.添加表字段
alter table 表名 add 字段名 字段类型; -- 添加到最后
alter table 表名 add 字段名 字段类型 first; -- 添加到最前面
alter table 表名 add 字段名 字段类型 after xxx; -- 添加到xxx后面
-- 9.删除表字段
alter table 表名 drop 字段名;
-- 10.修改字段名和字段类型
alter table 表名 change 原名 新名 列属性[];
-- 11.修改字段类型和位置和约束
alter table 表名 modify 字段名 类属性[] first/ after xxx;

冗余:由于表设计不够合理，导致出现的大量重复数据，称为数据冗余，通过合理拆分表的形式解决冗余问题
表设计:一般来说,每张表应有固定的几个字段

(1) id 主键

(2)version 版本(用于做乐观锁)

(3)is_delete 删除标志(伪删除:不希望数据真正被删除,通过标志位来标志)

(4)gmt_create 创建时间

(5)gmt_update 修改时间
表设计：关联关系
- 外键:用于建立关系的字段称为外键
- 一对一：有AB两张表，A表中的一条数据对应B表中的一条数据，同时B表一条数据也是对应A表中的一条数据，称为一对一
  
  应用场景：用户表和用户信息扩展表商品表和商品详情表
  
  如何建立关系：在从表中添加外键，指向主表的主键
- 一对多：A表中一条数据对应B表中的多条数据，同时B表中的一条数据对应A表的一条数据
  
  应用场景：员工表和部门表，商品表和分类表
  
  如何建立关系：在多的表中添加外键指向另外一张表的主键
- 多对多：有AB两张表，A表中一条数据对应B表中的多条数据，同时B表中的一条对应A表中的多条，称为多对多
  
  应用场景：用户表和权限表老师表和学生表
  
  如何建立关系：新建关系表，在新表中两个外键指向另外两个表的主键
关于数据库表类型(引擎)

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持(5.6.4版本后支持)
表空间大小	较小	较大,约为MYISAM的2倍

常规使用操作:

MYISAM 节约空间，速度较快
INNODB 安全性高,支持事务操作,多表多用户操作

4.视图

什么是视图：数据库中表和视图都是其内部的对象，视图可以理解成一张虚拟的表，视图的数据来自于原表，视图的本质实际上就是取代了一段SQL语句。

视图的作用：1. 可以起到复用的作用提高开发效率 2. 可以隐藏敏感信息

创建视图:

create view 视图名 as (子查询);
--  创建了一个真实存在的表 而且表中有自己独立的数据
create table 表名 as (子查询);

视图分类：

简单视图：创建视图的子查询中不包含：去重、聚合函数、分组查询、关联查询的视图称为简单视图，可以对视图中的数据进行增删改查操作
复杂视图：和简单视图相反，只能对视图中的数据进行查询操作

创建一个显示每个部门平均工资，最高工资，最低工资的复杂视图：

create view v_emp_info as(select deptno,avg(sal),max(sal),min(sal) from emp group by deptno);

对视图数据进行增删改查操作方式和table一样:

create view v_emp_10 as (select * from emp where deptno=10);
insert into v_emp_10 (empno,ename,sal) values (10010,'Tom',3000);

如果往视图中插入一条在视图中不可见但是在原表中却可见的数据称为数据污染。如何禁止出现数据污染的情况：在创建视图时使用with check option 关键字

create view v_emp_20 as (select * from emp where deptno=20) with check option;
insert into v_emp_20(empno,ename,deptno) values(10012,'Lucy',20);
insert into v_emp_20(empno,ename,deptno) values(10013,'Lily',30);
-- 视图中进行删除和修改时只能对视图中存在的数据进行操作
delete from v_emp_20 where empno=10012;//成功
delete from v_emp_20 where deptno=10;//失败

创建或替换视图

create or replace view v_emp_10 as(select ename,empno from emp where deptno=10);

删除视图

drop view v_emp_10;

视图别名：如果创建视图的子查询中使用了别名则对视图进行操作时只能使用别名

create view v_emp_30 as (select ename name from emp where deptno=30);

5.数据类型

数值类型

tinyint 十分小的数据 1字节
smallint 较小的数据 2字节
mediumint 中等大小的数据 3字节
int 标准整数 4字节
bigint 较大的整数 8字节
float 浮点数 4个字节
double 浮点数 8个字节(精度问题)
decimal 字符串形式的浮点数金融计算的时候,一般是使用decimal
整数： int(m) bigint(m) ,bigint对应java 中的long ，m代表显示长度,表示整数列的显示宽度与mysql需要用多少个字符来显示该列数值，与该整数需要的存储空间的大小都没有关系，int类型的字段能存储的数据上限还是2147483647(有符号型)和4294967295(无符号型)。字段定义为int(1)依旧可以插入数据:10000;

-- 2. 浮点数: double(m,d) m代表总长度 d代表的是小数长度  25.321 m=5 d=3 ,decimal(m,d)是超高精度浮点数只有涉及超高精度运算时使用

字段属性（约束）

主键

-- 主键：用于表示唯一性的字段称为主键
-- 约束： 创建表时给字段添加的限制条件
-- 主键约束：让字段不能重复并且不能为空(唯一且非空)
create table t1(id int primary key,name varchar(10));
create table t1(id int,name varchar(10), PRIMARY KEY(`id`));

zerofizerofill属性的作用：

(1)插入数据时，当该字段的值的长度小于定义的长度时，会在该值的前面补上相应的0；
(2) zerofill默认为int(10);
(3)当使用zerofill 时，默认会自动加unsigned（无符号）属性，使用unsigned属性后，数值范围是原值的2倍，例如，有符号为-128_{+127，无符号为0}256。
unsigned

(1)无符号整数

(2)不能声明为负数

create table t_int(id int,age int(10) zerofill);

自增(auto_increment)

(1)自动在上一条记录的基础上+1(默认)

(2)通常用来设计唯一的主键,index必须时整数类型

(3)可以自定义自增的起始值和步长

-- 主键约束+自增
-- 从历史最大值基础上+1
create table t2(id int primary key auto_increment,name varchar(10));

非空 NOT NULL,NULL

(1)设置为not null，如果不给它复制,就会报错

(2)设置为NULL，不填写默认就是null
默认值DEFAULT

create table `t2`(`name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名');

注释

-- 给字段添加的描述
create table t3(id int primary key auto_increment comment '这是个id', name varchar(10) comment '这是名字');
-- `的作用：用于创建表时 修饰表名和字段名， 可以省略
-- 如果表名或字段带有关键字,用`号括起来
create table `t4`(`id` int);

唯一约束 unique

字符串类型

char 固定长度 0~255
varchar 可变长度 0~65535
tinytxt 微型文本 2^8-1
text 文本串 2^16

varchar存储规则：

4.0版本以下，varchar(20)，指的是20字节，如果存放UTF-8汉字时，只能存6个（每个汉字3字节）
　　5.0版本以上，varchar(20)，指的是20字符，无论存放的是数字、字母还是UTF-8汉字（每个汉字3字节），都可以存放20个字符 Mysql4中最大也不过是20个字节,但是Mysql5根据编码不同,存储大小也不同。

注意，char 和 varchar 后面的长度表示的是字符的个数，而不是字节数。

varchar和char 的区别：

区别1：定长和变长
char 表示定长，长度固定，varchar表示变长，即长度可变。当所插入的字符串超出它们的长度时，视情况来处理，如果是严格模式，则会拒绝插入并提示错误信息，如果是宽松模式，则会截取然后插入。如果插入的字符串长度小于定义长度时，则会以不同的方式来处理，如char（10），表示存储的是10个字符，无论你插入的是多少，都是10个，如果少于10个，则用空格填满。而varchar（10），小于10个的话，则插入多少个字符就存多少个。
区别:2：存储的容量不同
对 char 来说，最多能存放的字符个数 255和编码无关。而 varchar 呢，最多能存放 65532 个字符。VARCHAR的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是65532(65535-3=65532 减1的原因是实际行存储从第二个字节开始; 减2的原因是varchar头部的2个字节表示长度;)字节。
– MySQL中varchar最大长度是多少？
这不是一个固定的数字。先简要说明一下限制规则。

字段的限制在字段定义的时候有以下规则：

a) 存储限制

varchar 字段是将实际内容单独存储在聚簇索引之外，内容开头用1到2个字节表示实际长度（长度超过255时需要2个字节），因此最大长度不能超过65535字符。

b) 编码长度限制

字符类型若为gbk，每个字符最多占2个字节，最大长度不能超过32766;
字符类型若为utf8，每个字符最多占3个字节，最大长度不能超过21845。
若定义的时候超过上述限制，则varchar字段会被强行转为text类型，并产生warning。

c) 行长度限制

导致实际应用中varchar长度限制的是一个行定义的长度。 MySQL要求一个行的定义长度不能超过65535bytes。若定义的表长度超过这个值，则提示：ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

由字符集来确定，字符集分单字节和多字节：

Latin1 一个字符占一个字节，最多能存放 65532 个字符
GBK 一个字符占两个字节，最多能存 32766 个字符
UTF8 一个字符占三个字节，最多能存 21844 个字符

举两个例说明一下实际长度的计算。
a) 若一个表只有一个varchar类型，如定义为
create table t4(c varchar(N)) charset=gbk;
则此处N的最大值为(65535-1-2)/2= 32766 个字符。
减1的原因是实际行存储从第二个字节开始’;
减2的原因是varchar头部的2个字节表示长度;
除2的原因是字符编码是gbk。
b) 若一个表定义为
create table t4(c int, c2 char(30), c3 varchar(N)) charset=utf8;
则此处N的最大值为 (65535-1-2-4-303)/3=21812
减1和减2与上例相同;
减4的原因是int类型的c占4个字节;
减303的原因是char(30)占用90个字节，编码是utf8。
如果被varchar超过上述的b规则，被强转成text类型，则每个字段占用定义长度为11字节，当然这已经不是“varchar”了。
则此处N的最大值为 (65535-1-2-4-30*3)/3=21812
create table t4(c int, c2 char(30), c3 varchar(21812)) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- 字符串：
-- char(m):，char(10) "abc"   10 ,执行效率高     最大长度255：这里的255指char的取值范围，这里的m最大可以设置为
-- varchar(m):可变长度,varchar(10) "abc"  3 , 节省空间  最大长度65535 超过255建议使用text
-- text(m):可变长度  最大长度65535

日期类型

-- date：只能保存年月日
-- time：只能保存时分秒
-- datetime：保存年月日时分秒，默认值为null，最大值9999-12-31
-- timestamp：时间戳（距1970年1月1日 08：00：00毫秒数）保存年月日时分秒，最大值是2038-01-19,默认值当前系统时间

------------ 日期相关 -----------
-- 2. 获取当前系统时间 now()
select now();
-- 3. 获取当前年月日 curdate()      cur=current
select curdate();
-- 4. 获取当前时分秒 curtime()
select curtime();
-- 5. 从年月日时分秒中提取年月日和提取时分秒
select date(now()),time(now());
select date(CURRENT_TIME);
select time(CURRENT_TIME);
-- 6. 从年月日时分秒中提取时间分量
select extract(year from now());
select extract(month from now());
select extract(day from now());
select extract(hour from now());
select extract(minute from now());
select extract(second from now());
-- 查询每个员工的姓名和入职年份
select ename,extract(year from hiredate) from emp;
-- 查询员工表中有员工入职的年份
select distinct extract(year from hiredate) from emp;
-- 7. 日期格式化 date_format()
-- 格式： date_format(时间,格式);
-- %Y 4位年 %y 2位年
-- %m 2位月 05 %c 1位月 5
-- %d 日
-- %H 24小时 %h 12小时
-- %i 分
-- %s 秒
-- 把now() 转成 2019年4月19号 15点15分20秒
select date_format(now(),'%Y年%c月%d号 %H点%i分%s秒');
-- 8. 把非标准时间格式转成标准时间格式 str_to_date()
-- 格式： str_to_date(非标准字符串时间,格式)
-- 把 19.04.2019 15:15:20 转成标准时间
select str_to_date('19.04.2019 15:15:20','%d.%m.%Y %H:%i:%s');

6.数据管理

6.1 外键

年级表:

CREATE TABLE `grade`(
	`gradeid` INT(10) NOT NULL  AUTO_INCREMENT	COMMENT '年级id',
	`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
	PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

学生表:

CREATE TABLE `student`(
	`id` INT(4) NOT NULL  AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(12) NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`gradeid` INT(10) NOT NULL COMMENT '年级id',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY(`id`), -- 设置主键
	KEY `FK_gradeid` (`gradeid`),  -- 定义外键key
	CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`) -- 给外键添加约束(执行引用)  reference 引用
)ENGINE=INNODB DEFAULT CHARSET=utf8;

删除有外键关系的表的时候,必须先删除引用别人的表(从表),在删除被引用的表(主表)

通过ALTER给表添加外键

ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`)

以上操作都是物理外键,数据库级别的外键,我们不建议使用！

最佳实践:

数据库就是单纯的表,只用来存数据,只有行(数据)和列(字段)
我们想使用多张表的数据,想使用外键(程序去实现)

6.2 DML语言

6.2.1 增删改查

-- 添加数据
-- 全表插入格式： insert into 表名 values (值1,值2,值3);  要求值得数量和顺序必须和表字段的数量、顺序一致
insert into person values(1,'Tom',18);
-- 指定字段插入格式： insert into 表名 (字段1名,字段2名) values (值1,值2); 要求值得数量和顺序和前面指定的一致
insert into person (id,name) values(2,'Jerry');
-- 批量插入数据
insert into person values(3,'aaa',10),(4,'bbb',11);
insert into person (id,name) values(5,'ccc'),(6,'ddd');
-- 查询数据
-- 格式： select 字段信息 from 表名 where 条件;
select name,age from person where id<3;

-- 修改数据
-- 格式： update 表名 set 字段名=值 where 条件;
update person set age=50 where id=2;

-- 删除数据
-- 格式： delete from 表名 where 条件;
delete from person where name='Tom';
-- 清楚全表数据
delete from person；
truncate person；
-- delete 和 truncate 区别
truncate：重新设置自增列，自增计数器会归零；不会影响事务
delete： 不影响自增

-- 导入*.sql文件
-- 1. linux系统： 把文件放到桌面 执行以下指令:source /home/soft01/桌面/tables.sql;
-- 2. window系统：把文件放到D盘根目录:source d:/tables.sql;

-- 去重 distinct:
select distinct deptno from emp;

-- 别名
select name from person;
select name as '姓名' from person;
select name '姓名' from person;
select name 姓名 from person;

-- is null 和 is not null:判断字段值为null不能用=，要使用is null
-- 1. 查询age为null的姓名
select name from person where age is null;
-- 2. 查询age不为null的姓名
select name from person where age is not null;

-- 查看版本
select version();
-- 查看自增步长
select @@auto_increment_increment;

-- 模糊查询 like 
-- %:代表的是0或多个未知字符     _:代表的是1个未知字符
-- 以a开头: a%  以m结尾: %m
-- 第一个字符是x 最后一个是y x%y
-- 包含x %x%
-- 第三个字符时x 倒数第二个字符是y __x%y_

-- 在两者之间  between x and y
select ename,sal from emp where sal>=2000 and sal<= 3000;
select ename,sal from emp where sal between 2000 and 3000;

-- in
-- 1. 查询员工工资为800，1300，1500的员工信息
select * from emp where sal=800 or sal=1300 or sal=1500;
select * from emp where sal in(800,1300,1500);
-- 2. 查询商品价格为56，58，89的商品标题和单价
select title,price from t_item where price in(56,58,89);

-- 排序 order by 
-- 查询每个员工的姓名、工资、部门编号 按照部门编号降序排序,如果部门编号相同则按钮工资降序排序
select ename,sal,deptno from emp order by deptno desc,sal desc;

-- 分页查询 limit
-- 格式:  limit 跳过的条数,请求条数
-- 请求 前5条数据 limit 0,5
-- 第三页的4条数据 limit 8,4
-- 第10也的7条数据 limit 63,7
-- 第9页的8条 limit （页数-1）*条数,条数
-- 1. 查询员工表工资降序前5条数据
select * from emp order by sal desc limit 0,5;
-- 2. 查询商品标题和单价 按照单价升序排序 第三页的5条数据
select title,price from t_item order by price limit 10,5;
-- 3. 查询30号部门中工资最高的三个员工的信息
select * from emp where deptno=30 order by sal desc limit 0,3;

-- 各种关键字的顺序：
select ..... from 表名 where .... group by .... having .... order by..... limit....

6.2.2 比较运算符

> < >= <= =  !=和<>
-- 1. 查询员工表中部门编号大于10 并且工资在2000以内的员工姓名，工资，部门编号
select ename,sal,deptno from emp where deptno>10 and sal<2000;
-- 2. 查询员工工资小于等于1600的员工姓名，职位和工资
select ename,job,sal from emp where sal<=1600;
-- 3. 查询工作是manager的员工姓名，工资和工作
select ename,sal,job from emp where job='manager';
-- 4. 查询不是10号部门的员工姓名，部门编号（两种写法）
select ename,deptno from emp where deptno!=10;
select ename,deptno from emp where deptno<>10;
-- 5. 查询单价为23的商品信息
select * from t_item where price=23;

-- and、or、not,&&、||、! (尽量使用引英文字母)
select * from emp where deptno=10 and sal<2000;
select * from emp where deptno=10 && sal<2000;
select * from emp where deptno=30 or sal>3000;
select * from emp where NOT deptno=30;

6.2.3 数值计算

+  - * /  7%2 = mod(7,2)
-- 1. 查询员工姓名，工资和年终奖(工资*5个月)
select ename,sal,sal*5 年终奖  from emp;
-- 2. 查询商品标题，单价，库存和总价值（库存*单价）
select title,price,num,num*price 总价值 from t_item;
-- 3. 查询每个员工涨薪5块钱之后的工资
select ename,sal,sal+5 from emp;

-- 1. 向下取整 floor(num)
select floor(3.84);
-- 2. 四舍五入 round(num)
select round(23.8);
-- 3. 四舍五入 round(num,m) m代表小数位数
select round(23.879,2);
-- 4. 非四舍五入 truncate(num,m)
select truncate(23.879,2);
-- 5. 随机数 rand()
select rand();

6.2.4 聚合函数

-- 对多条数据进行统计查询，统计平均值、最大值、最小值、求和、计数
-- 1. 平均值 avg(字段名)
-- 查询员工表中20号部门的平均工资
select avg(sal) from emp where deptno=20;
-- 查询员工表的平均奖金
select avg(comm) from emp;
-- 2. 最大值 max(字段名)
-- 3. 最小值 min(字段名)
-- 查询30号部门的最高工资和最低工资
select max(sal),min(sal) from emp where deptno=30;
-- 4. 求和sum(字段名)
-- 查询所有员工每月发出的总工资
select sum(sal) from emp;
-- 把低于100块钱的商品全都买了花多少钱
select sum(price) from t_item where price<100;
-- 5. 统计数量 count(字段名)
-- 查询员工表的人数
select count(*) from emp;
-- 查询有奖金的员工数量
select count(*) from emp where comm>0;

-- ifnull()；
-- age = ifnull(x,y);   如果x值为null则age=y 如果不为null则age=x
-- 把员工表中奖金为null的改成0 不为null的不变
update emp set comm=ifnull(comm,0);

6.2.5 字符串相关函数

-- 1. 字符串拼接 concat(s1,s2) 
select concat('aaa','bbb');
-- 查询每个员工的姓名和工资 工资以元为单位
select ename,concat(sal,'元') from emp;
-- 2. 获取字符串长度 char_length()
-- 查询每个员工的姓名和名字长度
select ename,char_length(ename) from emp;
-- 3. 获取字符串出现的位置
-- 格式： instr(str,substr)
select instr('abcdefg','d');
-- 4. 转大写和转小写
select upper('nba'),lower('NBa');
-- 5. 截取字符串
-- 左边截取:
select left('abcdefg',2);
-- - 右边截取
select right('abcdefg',2);
-- 自由截取
select substring('abcdefg',2,3);
-- 6. 去空白 trim
select trim('  a b  ');
-- 7. 重复 repeat
select repeat('ab',2);
-- 8. 替换 replace(str,old,new)
select replace('abcd abc','b','m');
-- 9. 反转 reverse(str)
select reverse('abc');

6.2.6 分组查询

-- 1. 查询每个部门的平均工资
select deptno,avg(sal) from emp group by deptno;
-- 2. 查询每种职业的最高工资
select job,max(sal) from emp group by job;
-- 3. 查询每个部门的人数
select deptno,count(*) from emp group by deptno;
-- 4. 查询工资大于1000的员工中每个部门的最高工资
select deptno,max(sal) from emp where sal>1000 group by deptno;
-- 5. 查询每个主管的手下人数(通过主管id分组 统计数量)
select mgr,count(*) from emp where mgr is not null group by mgr;

------------ having -----------
-- 和group by结合使用
-- 在having后面写聚合函数的条件
-- 在where后面写普通字段的条件
-- having后面也可以写普通字段的条件但是不推荐
-- 1. 查询每个部门的平均工资要求平均工资大于2000
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

6.2.7 子查询（嵌套查询)

-- 1. 查询员工表中工资最高的员工信息
select * from emp where sal=(select max(sal) from emp);
select * from bank order by money desc LIMIT 0,1;
-- 2. 查询工资高于平均工资的员工信息
select * from emp where sal>(select avg(sal) from emp);
-- 3. 查询和jones相同工作的其他员工信息
select * from emp where job=(select job from emp where ename='jones') and ename!='jones';
-- 4. 查询平均工资最高的部门信息(难度最高，需要考虑可能出现的并列第一的问题)
-- 查询最高的平均工资 
select avg(sal) a from emp group by deptno order by a desc limit 0,1;
-- 通过最高的平均工资查询对应的部门编号
select deptno from emp group by deptno having avg(sal)=(select avg(sal) a from emp group by deptno order by a desc limit 0,1);
-- 通过部门编号查询部门信息
select * from dept where deptno in(上面一坨);
-- 子查询可以写的位置
-- 1. 写在where或having后面 当做查询条件的值。
-- 2. 写在创建表的时候
create table emp_10 as (select * from emp where deptno=10);
-- 3. 写在from后面 **必须起别名**
select ename from (select * from emp where deptno=20) newtable;

6.2.8 关联查询

-- 同时查询多张表数据的查询方式 称为关联查询
-- 关联查询必须写关联关系，如果不写关联关系会得到两张表的乘积，这种乘积称为笛卡尔积，工作中切记不要出现这种情况
-- 1. 查询每一个员工的姓名和对应的部门名
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
-- 等值连接和内连接：
1. 等值连接：select * from A,B where A.x=B.x and A.xxx
2. 内连接：select * from A join B on A.x=B.x where A.xxx
内连接与等值连接效果是相同的，执行效率也相同，只是书写方式不一样，内连接是由SQL 1999规则定的书写方式
-- 由于mysql默认是内连接，所以，join 等同于 inner join
-- 外连接：查询一张表的全部数据和对应另外一张表的交集数据
格式： select * from A left/right join B on A.x=B.x where A.xxx<3000;
关联查询总结：
1. 如果需要查询两张表的交集数据需要使用等值连接或内连接(推荐)
2. 如果查询两张表中一张表的全部数据和对应一张表的交集数据使用外连接
3. 使用外连接时，要查哪张表的数据就以那张表为基础,决定使用left或right

-- 自连接：讲一张表拆成两张表
SELECT a.`categoryName` '父栏目', b.`categoryName` '子栏目'
FROM `category` AS a, `category` AS b
WHERE a.`categoryid` = b.`pi`

7.事务相关

 -- 什么是事务： 事务是数据库中执行同一业务多条SQL语句的工作单元，可以保证多条SQL语句全部执行成功，或全部执行失败，不会出现部分成功部分失败的情况。
 -- 事务相关指令：1. 开启事务： begin;2. 提交事务： commit;3. 回滚事务： rollback;
 -- 验证事务回滚流和提交流程程：
 -- 1. 开启事务:
 begin;
 -- 2. 蝙蝠侠-100
 update bank set money=money-100 where id=1 and status='正常';
 -- 3. 此时开启另外一个终端查询数据是否发生改变(没变，改变的是内存数据)
 -- 4. 超人+100
 update bank set money=money+100 where id=2 and status='正常';
 -- 5. 此时判断出两条SQL只成功了一条，所以需要将内存中的改动回滚(此处需要加入判断条件)
 rollback; 
 -- 当两条都执行成功时,
 commit;
 
 -- 保存回滚点
 begin;
 update user set money=6000 where id=2;
 savepoint s1;
 update user set money=5000 where id=2;
 savepoint s2;
 update user set money=4000 where id=2;
 -- 回滚到指定的回滚点
 rollback to s2;
 
 -- 事务的四大特性ACID： 是保证事务正确执行的四大基本要素
 -- 1. Atomicity： 原子性，最小不可拆分，保证全部执行成功或全部失败
 -- 2. Consistency：一致性，从一个一致状态到另一个一致状态
 -- 3. Isolation： 隔离性，多个事务之间互相隔离，互不影响。
 -- 4. Durability：持久性，事务提交之后数据保存到磁盘中持久生效
 
 -- SQL分类：
 -- 1. DDL：Data Definition Language，数据定义语言 包括：create、drop、alter、truncate；不支持事物。
 truncate table 表名；-- 删除表并创建新表，比删除全表数据执行效率高，删除全表数据自增数值不变，这种方式自增数值清零
 -- 2. DML：Data Manipulation Language, 数据操作语言 包括:insert、delete、update、select（DQL）；支持事务。
 -- 3. DQL： Data Query Language，数据查询语言  只包括select。
 -- 4. TCL： Transaction Control Language，事务控制语言 包括：begin,commit rollback,savepoint xxx,rollback to xxx；
 -- 5. DCL： Date Control Language，数据控制语言， 分配用户权限相关的SQL。

8.索引

什么是索引：索引是数据库中提高查询效率的技术，数据量越大索引效果越明显，索引类似于目录
为什么使用索引：数据会零散的保存在磁盘中的每个磁盘块中，如果不使用索引，查找数据只能挨个遍历每一个磁盘块进行查找，如果使用了索引后，磁盘块会以树状结构进行保存，查找数据时大大降低了磁盘块的访问量，从而达到了提高查询效率的目的
有索引就一定好吗？不是，如果数据量比较小的话使用索引反而会降低查询效率，只有数据量比较大时才使用索引
索引是越多越好吗？不是，因为索引会占用磁盘空间，只针对查询数据时频繁使用的字段创建索引。
如何使用索引

-- 创建索引格式
create index 索引名 on 表名(字段名(?长度));
create index i_item2_title on item2(title);

索引的分类(了解)
1. 聚集索引：通过主键字段创建的索引，给表添加主键约束时会自动创建，在聚集索引的树桩结构中的磁盘块里面保存：主键值+地址+数据
2. 非聚集索引：通过非主键字段创建的索引，在索引的树桩结构中没有数据
如何查看索引

show index from item2;

删除索引

格式：drop index 索引名 on 表名

drop index i_item2_title on item2;

复合索引: 通过多个字段创建的索引

create index i_item2_title on item2(title,price);

总结：
- 索引是数据库中提高查询效率的技术类似于目录
- 因为索引会占用磁盘空间，不是越多越好，只针对查询数据时频繁使用的字段创建索引
- 如果数据小的话使用索引会降低查询效率，所以不是有索引就一定好