一、MySQL的安装和配置
使用命令行窗口连接MYSQL数据库
1、mysql -h主机名-P端口-u用户名-p密码
2、登录前，保证服务启动
启动mysq|数据库的常用方式:[Dos命令]
1、服务方式启动(界面)
2、net stop mysq|服务名
3、net start mysq|服务名
安装流程
1）下载ZIP文件
2）解压的路径不要有中文和空格
3）解压到D:\hspmysql\mysql-5.7.19-winx64目录下
4）添加环境变量：电脑-属性-高级系统设置-环境变量，在Path环境变量增加mysql的安装目录/bin目录，如图
5）在D:\hspmysql\mysql-5.7.19-winx64目录下创建my.ini文件
[client]
port=3306
default-character-set=utf8
[mysqld]
#设置为自己MYSQL的安装目录
basedir=D:\hspmysql\mysql-5.7.19-winx64
#设置为MYSQL的数据目录
datadir=D:\hspmysql\mysql-5.7.19-winx64\data\
port=3306
character_set_server=utf8
#跳过安全检查
skip-grant-tables
6）使用管理员身份打开cmd，并切换到D:\hspmysql\mysql-5.7.19-winx64\bin目录下并执行mysqld -install
cd /D D:\hspmysql\mysql-5.7.19-winx64\bin
mysqld -install
7）初始化数据库：mysqld --initialize-insecure --user=mysql
如果执行成功会生成data目录
8)启动mysql服务：net start mysql【停止mysql服务指令：net stop mysql】，如果成功：
9）进入mysql管理终端：mysql -u root -p【当前root用户密码为空】
10）修改root用户密码
use mysql;
update user set authentication_string=password('hsp') where user='root' and Host='localhost';
上面的语句就是修改root用户的密码为hsp
注意:在后面需要带分号，回车即可执行该指令
执行: flush privileges; 刷新权限
退出：quit
11)修改my.ini，再次进入就会进行权限验证了
12）重启mysql
13)再次进入mysql，输入正确的用户名和密码
mysql -u root -p【密码是hsp】
此时安装服务已全部完成，如果不按步骤安装出现了错误，需要清除mysql服务，再次安装。
navicat安装和使用
Navicat下载地址: http://www.navicat.com.cn/products/
SQLyog安装和使用
SQLyog下载地址: https://sqlyog.en.softonic.com/
二、数据库
数据库三层结构-破除MySQL神秘
1.所谓安装Mysql数据库，就是在主机安装一个数据库管理系统(DBMS), 这个管理程序可以管理多个数据库。DBMS(database manage system)
2.一个数据库中可以创建多个表，以保存数据(信息)。
3.数据库管理系统(DBMS)、数据库和表的关系如图所示:
SQL语句分类
DDL:数据定义语句【create表，库…】
DML:数据操作语句【增加insert，修改update，删除delete】
DQL:数据查询语句【select】
DCL:数据控制语句【管理数据库：比如用户权限grant（授权） revoke（撤销权限）】
创建数据库
CRAEATE DATABASE [IF NOT EXISTS] 创建一个数据库。括号内的意思是判断他是否存在，不写时已经存在这个数据库就会报错，写了就不会帮你创建
1. CHARACTER SET:指定数据库采用的字符集，如果不指定字符集,默认utf8
2. COLLATE:指定数据库字符集的校对规则(常用的utf8_bin[区分大小写]、utf8_general_ci[不区分大小写]注意默认是utf8_general_ci) [举例说明database.sql文件]
练习:
1.创建一个名称为hsp_db01的数据库。[图形化和指令演示]
2.创建-个使用utf8字符集的hsp_db02数据库
3.创建一个使用utf8字符集，并带校对规则的hsp_db03数据库
查看、删除数据库
显示数据库语句:
SHOW DATABASES 查看有哪些数据库，显示所有数据库
显示数据库创建语句:
SHOW CREATE DATABASE db_name 查看别人创建数据库时用的命令
数据库删除语句【一定要慎用】:
DROP DATABASE [IF EXISTS] db_name 删除数据库
练习:database02.sql
1.查看当前数据库服务器中的所有数据库
2.查看前面创建的hsp_db01数据库的定义信息
3.删除前面创建的hsp_db01 数据库
db01被删除了
备份、恢复数据库
备份数据库(注意:在DOS执行)命令行
mysqldump -u 用户名-p -B数据库1数据库2数据库n >文件名.sql
恢复数据库(注意:进入Mysql命令行再执行)
Source 文件名.sql
练习: database03.sql备份hsp_ db02和hsp db03库中的数据，并恢复
中间有报错的原因应该是某个进程占用这个文件夹的锁，所以mysql命令进不去，才一直打不开的。所以再保存备份文件的时候需要注意。
备份恢复数据库的表
备份库的表
. mysqldump -u用户名-p密码数据库表1表2表n > d:\文件名.sql
提示:自己测试，非常简单，比如备份hsp db02的t2
三、表
创建表
CREATE TABLE table name
(field1 datatype,
field2 datatype,
field3 datatype)
character set字符集 collate校对规则 engine存储引擎
field:指定列名 datatype: 指定列类型(字段类型)
character set :如不指定则为所在数据库字符集
collate: 如不指定则为所在数据库校对规则
engine:引擎(这个涉及内容较多，瓜面单独讲解)
注意: hsp_db02创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类型。例: user表 (快速入门案例create tab01 .sql )
id 整形 [图形化，指令]
name 字符串
password 字符串
birthday 日期
CREATE TABLE `user` (
id INT,
`name` VARCHAR(255),
`password` VARCHAR(255),
birthday DATE)
CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;
前面带不带反引号取决于是不是关键字，如果不清楚关键字就全部带上反引号，最后一句话的意思是确定字符集、校对规则、引擎。
MySQL自带的空白表DUAL，可以方便试公式
删除表？
delete from 表名; 当你要删除部分记录或者有可能会后悔的话，用 delete（可以留下空白表）。
drop table 表名; 当你不再需要该表时， 用 drop;
修改表⭐
基本介绍
使用ALTER TABLE语句追加，修改，或删除列的语法。
添加列：
ALTER TABLE tablename ADD ( column datatype [DEFAULT expr][，column datatype] .. .) ;
修改列：
ALTER TABLE tablename MODIFY ( column datatype [DEFAULT expr][，column datatype] .. .) ;
删除列：
ALTER TABLE tablename DROP (column) ;
查看表的结构: desc 表名; --可以查看表的列
修改表名: Rename table 表名 to 新表名
修改表字符集:alter table 表名 character set 字符集;
应用实例altertab.sql
员工表emp的上增加一个image列， varchar类型 (要求在resume后面)。
修改job列，使其长度为60。
删除sex列。
表名改为employee。
修改表的字符集为utf8
列名name修改为user_ name
ALTER TABLE epc CHANGE `sex` `sexx` CHAR(1) #修改sex为sexx，必须带上类型，不然报错
数据库表结构设置不能为空报错invalid use of Null value（无效使用空值）
原因是数据表中已经存在为空的数据，这与设置not null产生冲突
解决办法：可以将为空的数据删除，或者将为空的数据修改为不为空
增加数据？
INSERT INTO 表名 VALUES(数据）在这个表里面最后的数据后增加数据
四、MySQL数据类型⭐
Mysql数据类型（列类型/字段类型）
数值型（整数）的基本使用
使用规范：在能够满足需求的情况下，尽量选择占用空间小的类型
数值型的不能输入文字，用相应范围就好
实例:
#使用tinyint来演示范围有符号-128~ 127如果没有符号 0-255
#说明:表的字符集， 校验规则,存储引擎，老师使用默认
#1.如果没有指定unsinged, 则TINYINT就是有符号
#2.如果指定unsinged, 则TINYINT就是无符号0-255
CREATE TABLE t3 (
id TINYINT); #有符号，默认是有符号
CREATE TABLE t4 (
id TINYINT UNSIGNED); #无符号
INSERT INTO t3 VALUES(127); #这是非常简单的添加语句
SELECT * FROM t3 #查询、查看表
INSERT INTO t4 VALUES(255);
SELECT * FROM t4;
如何定义一个无符号的整数
int.sql文件
create table t10 (id tinyint ); 默认是有符号的
create table t11 (id tinyint unsigned ); 无符号的
数值型(bit)的使用
1.基本使用
mysql> create table t05 (num bit(8))
mysql> insert into t05 (1, 3);
mysql> insert into t05 values(2, 65):
2.细节说明 bit.sql
bit 字段显示时，按照位的方式显示
查询的时候仍然可以用使用添加的数值
如果一个值只有 0，1 可以考虑使用 bit(1),可以节约空间
位类型。M指定位数，默认值1，范围1-64
使用不多
数值型(小数)的基本使用
1. FLOAT/DOUBLE [UNSIGNED] #UNSIGNED表示无符号，无特殊需求不加
Float 单精度精度，Double 双精度
2. DECIMAL [M,D] [UNSIGNED]
可以支持更加精确的小数位。M是小数位数(精度)的总数，D是小数点(标度)后面的位数。（精度是小数点前后总共的位数，标度才是小数点后面的位数。）
#（单精度与双精度的意思是说精确到小数点后几位数。
小数有效位数
float 6位。小数点后6位都是准确的，而第7位就不一定，会被四舍五入
double 15位。同理）
如果D是0，则值没有小数点或分数部分。M最大65。D最大是30。如果D被省略，默认是0。如果M被省略，默认是10。
建议:如果希望小数的精度高，推荐使用decimal
案例演示 floatDoubleDec.sgl 文件，测试数据 88.12345678912345
字符串的基本使用
CHAR(size) 0~255
固定长度字符串 最大255 字符
VARCHAR(size) 0~65535
可变长度字符串 最大65532字节【utf8编码最大21844字符 1-3个字节用于记录大小】
utf8编码3个字节等于1个字符，所以（65535-3）/3=21844字符
每种编码的字节和字符的转换不一样
应用案例 charVarchar.sql 文件
DROP TABLE 删除表
字符串使用细节
细节1
char(4) //这个4表示字符数(最大255)，不是字节数，不管是中文还是字母都是放四个,按字符计算.
varchar(4)//这个4表示字符数 ，不管是字母还是中文都以定义好的表的编码来存放数据。
不管是中文还是英文字母，都是最多存放4个，是按照字符来存放的
字符是给使用者看，字节是给计算机看的数
字符串就是一个个文字（此处10个字符串）
细节2
char(4) 是定长(固定的大小)，就是说，即使你 插入aa’，也会占用 分配的4个字符的空间.
varchar(4)是变长(变化的大小)，就是说，如果你插入了aa'实际占用空间大小并不是4个字符，而是按照实际占用空间来分配(老韩说明:varchar本身还需要占用1-3个字节来记录存放内容长度)
如图所示，char是定长，输入aa会浪费两个字符。
varchar真是占用的空间是aa+(1-3)个字节
细节3
什么时候使用 char，什么时候使用varchar
1.如果数据是定长，推荐使用char，比如md5的密码,邮编，手机号，身份证号码等.char(32)
2.如果一个字段的长度是不确定，我们使用varchar ，比如留言,文章
查询速度: char > varchar
细节4
在存放文本时，也可以使用Text数据类型.可以将TEXT列视为VARCHAR列，注意Text不能有默认值。大小0-2^16字节
如果希望存放更多字符，可以选择
MEDIUMTEXT 0-2^24或者LONGTEXT 0~2^32
日期类型的基本使用
CREATE TABLE birthday6
(t1 DATE, t2 DATETIME,
t3 TIMESTAMP NOT NULL DEFAULT
CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP ) ; timestamp时间戳
mysql> INSERT INTO birthday (t1,t2)
VALUES('2022-11-11','2022-11-11 10:10:10');
日期类型的细节说明
TimeStamp在Insert和update时，自动更新datetime.sql
图中标出的就是时间戳的配置
创建表练习
创建一个员工表emp(课堂练习），选用适当的数据类型createtable.sql
CREATE TABLE `epc` (
id INT,
`name` VARCHAR(32),
sex CHAR(1),
birthday DATE,
entry_date DATETIME,
job CHAR(255),
Salary DOUBLE,
`resume` TEXT);
编号
姓名
性别
生日
入职时间
工作
薪水
个人介绍
此处命令有问题，需要吧开头的EPC删掉
五、CRUD⭐
CRUD是指在做计算处理时的增加(Create)、读取查询(Retrieve)、修改(Update)和删除(Delete)几个单词的首字母简写。
lnsert（添加数据）
使用INSERT语句向表中插入数据。insert.sql
INSERT INTO table_ name [ (column [， columo...]) ] 列名
VALUES (value [，value...]) ; 数据 一个数据对应一个列的添加
快速入门案例:
1.创建一张商品表(id int, goods name varchar(10),price double );
2.添加2条记录
INSERT INTO `goods`(id,goods_name,price)
VALUES(1,'华为手机',6400)
添加两条个人信息
试验一条，写了好久都会出错，记得带单引号("),带单引号是一个好习惯
细节说明insertdetail.sql
1.插入的数据应与字段的数据类型相同。
比如把'abc'添加到int类型会错误
2.数据的长度应在列的规定范围内，例如:不能将一个长度为80的字符串加入到长度为40的列中。
3.在values中列出的数据位置必须与被加入的列的排列位置相对应。
4.字符和日期型数据应包含在单引号中。
5.列可以插入空值[前提是该字段允许为空]，insert into table value(null)
6. insert into tab_name (列名..) values (),(),() 形式添加多条记录
7.如果是给表中的所有字段添加数据，可以不写前面的字段名称
8.默认值的使用，当不给某个字段值时，如果有默认值就会添加，否则报错
创建表的时候指定某个列的值不能为空，给一个默认值，如图所示（NOT NULL DEFAULT 100）
如果不输入值就会显示预设的默认值
Update（修改数据）
使用update语句修改表中数据
UPDATE tb1_name
SET col_ name1= expr1 [，col name2=expr2 。。.]
[WHERE where defini tion] #这行条件不写是对这个表的所有记录都进行修改
基本使用update.sql
要求:在上面创建的employee表中修改表中的纪录
1.将所有员工薪水修改为5000元。
2.将姓名为小妖怪的员工薪水修改为3000元。
3.将老妖怪的薪水在原有基础，上增加1000元。
●使用细节:
1. UPDATE语法可以用新值更新原有表行中的各列。
2. SET子句指示要修改哪些列和要给予哪些值。
3. WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。
4.如果需要修改多个字段，可以通过set 字段1=值1,字段2=值2....(如下图)
Delete（删除数据）
使用delete语句删除表中数据。
DELETE from tb1 NAME
[WHERE WHERE de fini tion]
快速入门案例(使用employee测试) delete. SQL
删除表中名称为’老妖怪'的记录。
删除表中所有记录。
使用细节
1.如果不使用where子句，将删除表中所有数据。
2.Delete语句不能删除某一列的值(可使用update 设为null或者")
3.使用delete语句仅删除记录，不删除表本身。如要删除表，使用droptable语句。
drop table表名;
Select⭐（查找数据）
单表查询
from子句——取消重复行
基本语法
SELECT [DISTINCT]*| { column1, column2. column3. . } #DISTINCT取消重复行
FROM tablename;
SELECT*FROM
注意事项(创建测试表学生表)
1.Select指定查询哪些列的数据。
2.columm指定列名。
3.*号代表查询所有列。
4.From指定查询哪张表。
5.DISTINCT可选，指显示结果时，是否去掉重复数据
如果表里出现重复与数据，可以写
select distinct 列名 from 表名
使用表达式对查询的列进行运算
SELECT*| { column1| expression, column2| expression, . 。}
FROM tablename;
在select语句中可使用as语句
SELECT column_name as 别名 from 表名;
练习
1.统计每个学生的总分
2.在所有学生总分加10分的情况
3.使用别名表示学生分数。
课后练习
在赵云的总分上增加60%
统计关羽的总分。
使用别名表示学生的数学分数。
where子句——过滤查询
在where子句中经常使用的运算符
（xxx between 小 and 大 ） 定义xxx在大小区间
使用where子句，进行过滤查询
1.查询姓名为赵云的学生成绩
2.查询英语成绩大于90分的同学
3.查询总分大于200分的所有同学
使用where子句，课堂练习[5min]:
查询math大于60并且(and) english大于90的学生成绩
查询英语成绩大于语文成绩的同学
查询总分大于200分并且数学成绩小于语文成绩，的姓韩的学生.
课堂练习
1.查询英语分数在80 - 90之间的同学。
2.查询数学分数为89, 90,91的同学。
3.查询所有姓李的学生成绩。
4.查询数学分> 80,语文分> 80的同学。
课堂练习[学员自己练习]
1.查询语文分数在70 - 80之间的同学。
2.查询总分为189,190,191的同学。
3.查询所有姓李或者姓宋的学生成绩。
4.查询数学比语文多30分的同学。
order by子句——排序查询结果
使用order by子句排序查询结果
SELECT column1， column2 . column3 . .
FROM table;
order by column asc| desc,
1. Order by指定排序的列，排序的列既可以是表中的列名，也可以是select语句后指定的列名。
2. Asc升序[默认]、 Desc降序
3. ORDER BY子句应位于SELECT语句的结尾。
课堂练习: orderby.sql
对数学成绩排序后输出[升序]。
对总分按从高到低的顺序输出
对姓李的学生成绩排序输出(升序)
group by+having子句——分组+过滤
使用group by子句对列进行分组[先创建测试表]
SELECT column1 ，column2 . column3. . FROM table
group by column
使用having子句对分组后的结果进行过滤
SELECT column1 ，column2 . column3. . FROM table
group by column having
group by用于对查询的结果分组统计，(示意图)
having子包用于限制分组显示结果
?如何显示每个部门的平均工资和最高工资
?显示每个部门的每种岗位的平均工资和最低工资
?显示平均工资低于2000的部门号和它的平均工资//别名
查询加强
where子句加强
如何查找1992.1.1后入职的员工
如何使用like操作符
%:表示0到多个字符 _ : 表示单个字符
?如何显示首字符为S的员工姓名和工资
?如何显示第三个字符为大写0的所有员工的姓名和工资
如何显示没有.上级的雇员的情况
查询表结构
order by子句加强
使用order by子句
?如何按照工资的从低到高的顺序，显示雇员的信息
?按照部门号升序而雇员的工资降序排列,显示雇员信息
部门号升序后输出一张表，用逗号连接后接着排序（多条件排序，先排部门，同部门的排工资）
limeit——分页查询
1.按雇员的empno号升序取出，每页显示3条记录，请分别显示第一页，第二页，第三页
2.基本语法:select...limit start, rows表示从start+1行开始取，取出rows行，start从0开始计算
课堂练习题:
按雇员的empno号降序取出，每页显示5条记录。 请分别显示第3页，第5页对应的sq|语句
图中有错，降序需要在empno后面加上desc表示降序
group by子句
使用分组函数和分组子句group by
(1)显示每种岗位的雇员总数、平均工资。
(3)显示雇员总数，以及获得补助的雇员数。
(4)显示管理者的总人数。
(5)显示雇员工资的最大差额。
多思考多尝试，sql语句非常灵活
数据分组的总结
如果select语句同时包含有group by ,having，limitorder by那么他们的顺序是group by，having , order by
应用案例:请统计各个部门的平均工资，并且是大于1000的，并且按照平均工资从高到低排序，取出前两行记录.
SELECT column1 ，column2 . column3. . FROM table
group by column
having condi tion
order by column
limit start , rows;
子句顺序不能出错
多表查询⭐
在前面我们讲过mysq|表的基本查询，但是都是对一张表进行的查询，这在实际的软件开发中，还远远的不够。下面我们讲解的过程中，将使用前面创建三张表(emp,dept,salgrade)为大家演示如何进行多表查询（重点、难点）
多表查询是指基于两个和两个以上的表查询.在实际应用中,查询单个表可能不能满足你的需求，(如下面的课堂练习)， 需要使用到(dept表和emp表)
SELECT * FROM emp,dept
在默认情况下同时查询两个表会有以下规则：
1、从第一张表中，取出一行和第二张表的没一行进行组合，返回结果【含有两张表的所有列】
2、一共返回的记录数为：第一张表行数*第二张表的行数
3、这样多表查询默认处理返回的结果，称为笛卡尔集
4、解决这个多表的关键就是要写出正确的过滤条件【where】
SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno
多表查询练习many_tab.sql
?显示雇员名,雇员工资及所在部门的名字[笛卡尔集]
小技巧:多表查询的条件不能少于表的个数-1,否则会出现笛卡尔集
?如何显示部门号为10的部门名、员工名和工资
?显示各个员工的姓名，工资，及其工资的级别
显示雇员名,雇员工资及所在部门的名字,并按部门排序[降序排]
多表查询——自连接
自连接是指在同一张表的连接查询[将同一张表看做两张表]。self.sql
思考题:显示公司员工和他的上级的名字
每个表需要有别名，不然会报错
子查询
什么是子查询
子查询是指嵌入在其它sq|语句中的select语句，也叫嵌套查询
单行子查询
单行子查询是指只返回一行数据的子查询语句
请思考:如何显示与SMITH同一部门的所有员工?
多行子查询
多行子查询指返回多行数据的子查询使用关键字 IN
练习:如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号，但是不含10自己的.
子查询——用做临时表
用做临时表需要起别名！！！
子查询——使用all/any操作符
在多行子查询中使用al操作符
请思考:显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
all的意思是：比其中所有的
在多行子查询中使用any操作符
如何显示工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号
any的意思是：比其中任意一个
多列子查询
多列子查序则是指查询返回多个列数据的子查询语句
请思考如何查询与smith的部门和岗位完全相同的所有雇员(并且不含smith本人)
(字段1,字段2 .. = (select字段 1,字段2 from。。。。)
查询和宋江数学，英语，语文完全相同的学生
他的数学，英语，语文有顺序要求
子查询练习
在from子句中使用子查询subquery03.sql
查找每个部门工资高于本部门平均工资的人的资料
这里要用到数据查询的小技巧，把个子查询当作一 个临时表使用
查找每个部门工资最高的人的详细资料
显示每个部门的信息(包括:部门名，编号,地址)和人员数量,我们一起完成。
表复制
自我复制数据(蠕虫复制
有时，为了对某个sq语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据。
合并查询
有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号
union , union all union.sql
1. union all
该操作符用于取得两个结果集的并集。当使用该操作符时，不会取消重复行。
2. union
该操作赋与union all 相似，但是会自动去掉结果集中重复行
六、函数⭐
统计函数
count——返回行的总数
Count返回行的总数
Select count(*) | count(列名) from table_name
[WHERE where defini tion]
课堂练习: statistics.sql
统计一个班级共有多少学生?
统计数学成绩大于90的学生有多少个?
统计总分大于250的人数有多少?
count(*)和count(列)的区别
合计函数
sum——求和
Sum函数返回满足where条件的行的和
Select sum(列名) { , sum(列名...} from table_name
[WHERE where defini tion]
课堂练习:
统计一个班级数学总成绩?
统计一个班级语文、英语、数学各科的总成绩
统计一个班级语文、英语、数学的成绩总和
统计一个班级语文成绩平均分
注意: sum仅对数值起作用，否则会报错。
注意:对多列求和，“， ”号不能少。
avg——平均值
AVG函数返回满足where条件的一列的平均值
Select avg (列名) { ,avg(列名...} from table_name
[WHERE where defini tion]
练习:
求一个班级数学平均分?
求一个班级总分平均分?
Max/min——最大/最小值
合计函数- Max/min
Max/min函数返回满足where条件的一列的最大/最小值
Select max (列名) from tablename
[WHERE where_ defini tion]
练习:求班级最高分和最低分(数值范围在统计中特别有用)
字符串函数
trim(去除左右两端的空格）
练习：以首字母小写的方式显示所有员工emp的姓名 str.sql
concat的时候可以直接连接，理解为&
数学函数
数学相关函数
rand()返回一个随机浮点值V ,范围在0到1之间(即，其范围为0≤v≤1.0)。若已指定一个整数参数N ,则它被用作种子值，用来产生重复序列。
ROUND(数值) 此函数是进行四舍五入
FLOOR(数值1 % 数值2 )函数：获取用数值1除以数值2的余数。FLOOR(10 % 3 ) 获得1
TRUNCATE(X,D)函数
TRUNCATE(X,D) 是MySQL自带的一个系统函数。
其中，X是数值，D是保留小数的位数。
其作用就是按照小数位数，进行数值截取（此处的截取是按保留位数直接进行截取，没有四舍五入）。
规则如下：
1）当 D 大于0，是对数值 X 的小数位数进行操作；
2）当 D 等于0，是将数值 X 的小数部分去除，只保留整数部分；
3）当 D 小于0，是将数值 X 的小数部分去除，并将整数部分按照 D 指定位数，用 0 替换。
时间日期函数
时间日期相关函数
查询：
显示所有留言信息，发布日期只显示日期，不用显示时间.
请查询在10分钟内发布的帖子
请在mysql的sqI语句中求出2011-11-11和1990-1-1相差多少天
请用mysql的sq|语句求出你活了多少天? [练习]
如果你能活80岁，求出你还能活多少天.[练习]
显示某个月的最后一天的日期，LAST_DAY(NOW())也可以进行减天数
加密和系统函数
流程控制函数
先看两个需求:
1.查询emp表，如果comm是null ,则显示0.0
2.如果emp表的job是CLERK则显示职员，如果是MANAGER则显示经理如果是SALESMAN则显示销售人员，其它正常显示.
七、内连接
第五章数据分组的总结这节开始至章节结束，写的自连接，与内连接有相似的地方。但是老师没有具体讲内连接，或者说没有提示开始讲内连接
八、外连接⭐
提出一个问题：
1.前面我们学习的查询，是利用where子句对两张表或者多张表，形成的笛卡尔积进行筛选，根据关联条件，显示所有匹配的记录，匹配不上的，不显示
2.比如:列出部门名称和这些部门的员工名称和工作，同时要求显示出那些没有员工的部门。
3.使用我们学习过的多表查询的SQL,看看效果如何?
dept表里有40号部门，但是之前的搜索方法不显示，=>引出外连接
外连接——左|右外连接
1.左外连接(如果左侧的表完全显示我们就说是左外连接)
2.右外连接(如果右侧的表完全显示我们就说是右外连接)
3.使用左连接(显示所有人的成绩，如果没有成绩，也要显示该人的姓名和id号,成绩显示为空)
4.使用右连接(显示所有成绩，如果没有名字匹配，显示空)
创建两个表
左外连接的写法，此时stu就是左表，exan就是右表
右外连接的写法，此时stu就是左表，exan就是右表
练习：列出部门名称和这些部门的员工信息(名字和工作)，同时列出那些没有员工的部门。
1.使用左外连接实现
2.使用右外连接实现
不仅完成，且用order by 进行排序
小结:
在实际的开发中，我们绝大多数情况下使用的是前面学过的连接方法
九、约束
基本介绍
约束用于确保数据库的数据满足特定的商业规则。
在mysq|中，约束包括: not null、unique、primary key、foreign key和check五种。
nut null（非空）
创建表的时候指定某个列的值不能为空，给一个默认值，如果不输入值就会显示预设的默认值
（NOT NULL DEFAULT 100）
unique（唯一）
unique细节(注意):
1.如果没有指定not null,则unique字段可以有多个null
2.一张表可以有多个unique字段
primary key（主键）
演示：
主键使用的细节讨论
-- primary key不能重复而且不能为null。
-- 一张表最多只能有一个主键，但可以是复合主键
-- 主键的指定方式有两种
1.直接在字段名后指定:字段名primakry key
2. 在表定义最后写primary key(列名) ;
-- 使用desc表名，可以看到primary key 的情况
-- 提醒：在实际开发中，每个表往往都会设计一个主键
（两种添加方式也可以再第十章课后练习看到）
foreign key（外键）
foreign key(外键)
用于定义主表和从表之间的关系: 外键约束要定义在从表上，主表则必须具有主键约束或是unique约束.当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null (学生/班级图示)
foreign key(外键)一细节说明
1.外键指向的表的字段，要求是primary key或者是unique
2.表的类型是innodb,这样的表才支持外键
3.外键字段的类型要和主键字段的类型一致(长度可以不同)
4.外键字段的值，必须在主键字段中出现过，或者为null [前提是外键字段允许为null]
INSERT INTO my_stu
VALUES(3,'hsp',null); -- 这里会成功...null
5.一旦建立主外键的关系，数据不能随意删除了.
check（检查）
check
用于强制行数据必须满足的条件,假定在sal列.上定义了check约束,并要求sal列值在1 000 ~ 2000之间如果不再1000 ~ 2000之间就会提示出错。
提示：oracle和sql server均支持check ,但是mysql5.7目前还不支持check ,只做语法校验，但不会生效。check.sql
在mysql中实现check的功能，- -般是在程序中控制，或者通过触发器完成。
商店售货系统表设计案例
现有一个商店的数据库shop_db,记录客户及其购物情况，由下面三个表组成:
商品goods(商品号goods_id,商品名goods_name,单价unitprice,商品类别category,供应商provider);
客户customer(客户号customer_id,姓名name,住址address,电邮email性别sex,身份证card_ld);
购买purchase(购买订单号order_id, 客户号customer_id, 商品号goods_id,购买数量nums);
1建表，在定义中要求声明[进行合理设计]:
(1)每个表的主外键;
(2)客户的姓名不能为空值;
(3)电邮不能够重复;
(4)客户的性别[男|女] check 枚举..
(5)单价unitprice在1.0-9999.99之间check
最终代码
次键
主键
注意：建表顺序是先建主键表后建次键表，顺序打乱可能会出错(推测)
自己的思路没走通，purchase为复合主键customer、goods为次键，建表时会报错
自增长
自增长基本介绍一个问题
在某张表中，存在个id列(整数) 我们希望在添加记录的时候，该列从1开始，自动的增长，怎么处理? increment.sql
先创建表，再修改默认的开始值
自增长使用细节：
1.一般来说自增长是和primary key配合使用的
2.自增长也可以单独使用[但是需要配合一个unique]
3.自增长修饰的字段为整数型的(虽然小数也可以但是非常非常少这样使用)
4.自增长默认从1开始，你也可以通过如下命令修改altertable表名auto increment =新的开始值;
5.如果你添加数据时，给自增长字段(列)指定的有值，则以指定的值为准，如果指定了自增长，一般来说，就按照自增长的规则来添加数据。
十、索引⭐
说起提高数据库性能，索引是最物美价廉的东西了。不用加内存，不用改程序，不用调sql,查询速度就可能提高百倍干倍。这里我们举例说明索引的好处[构建海量表8000000] (快速index.sql)
是不是建立一个索引就能解决所有的问题?
ename上没有建立索引会怎样?
select * from emp where ename=‘axJxC’
索引的原理
没有索引为什么会慢? 因为全表扫描
使用索引为什么会快? 形成一个索引的数据结构，比如二叉树
索引的代价
I.磁盘占用
lI.对dml(update delete insert)语句的效率影响
索引的类型
1.主键索引，主键自动的为主索引(类型Primary key)
2.唯一索引(UNIQUE)
3. 普通索引(INDEX)
4.全文索引(FULLTEXT) [适用于MyISAM]
开发中考虑使用:全文搜索Solr和ElasticSearch (ES)
添加索引
ALTER TABLE t26 ADD PRIMARY KEY (id)
SHOW INDEXES FROM t26 ;
删除索引
修改索引
修改索引就是先删除索引，再添加新的索引。
查询索引
课后练习
建立索引(主键)课后练习
要求:1.创建一张订单表order (id号，商品名，订购人，数量). 要求id号为主键，请使用两种方式来创建主键. (提示:为练习方便，可以是order1 , order2 )
建立索引(唯一-) 课后练习
要求:1.创建一张特价菜谱表menu (id号，菜谱名，厨师，点餐人身份证，价格).要求id号为主键，点餐人身份证是unique请使用两种方式来创建unique.(提示:为练习方便，可以是menu1 , menu2
建立索引(普通)课堂练习
要求:1.创建一张运动员表sportman (id号， 名字，特长).要求id号为主键，名字为普通索引，请使用两种方式来创建索引(提示:为练习方便，可以是不同表名sportman1 , sportman2
十一、事务
什么是事务
事务用于保证数据的一致性，它由一组相关的dm语句组成，该组的dml语句要么全部成功，要么全部失败。如:转账就要用事务来处理，用以保证数据的一致性。
事务和锁
当执行事务操作时(dmI语句) ,mysq|会在表上加锁,防止其它用户改表的数据.这对用户来讲是非常重要的
mysql数据库控制台事务的几个重要操作(基本操作transaction.sql)
1. start transaction ——开始一个事务
2. savepoint 保存点名——设置保存点
3.rollback to保存点名——回退事务
4.rollback——回退全部事务
5.commit ——提交事务，所有的操作生效，不能回退
细节:
1.没有设置保存点
2.多个保存点
3.存储引擎
4.开始事务方式
回退事务
在介绍回退事务前，先介绍一下保存点(savepoint).保存点是事务中的点用于取消部分事务，当结束事务时(commit) ,会自动的删除该事务所定义的所有保存点.当执行回退事务时，通过指定保存点可以回退到指定的点，这里我们作图说明
提交事务
使用commit语句可以提交事务.当执行了commit语句子后，会确认事务的变化、结束事务、删除保存点、释放锁，数据生效。当使用commit语句结束事务子后，其它会话[其他连接]将可以查看到事务变化后的新数据[所有数据就正式生效.]
事务细节讨论transaction_ _detail.sql
1.如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚
2.如果开始一个事务，你没有创建保存点.你可以执行rollback,默认就是回退到你事务开始的状态.
3.你也可以在这个事务中(还没有提交时)，创建多个保存点比如: savepoint aaa;执行dmI , savepoint bbb;
4.你可以在事务没有提交前，选择回退到哪个保存点.
5. mysql的事务机制需要innodb的存储引擎才可以使用,myisam不好使.
6.开始一个事务start transaction,set autocommit= off;
隔离级别⭐
事务隔离级别介绍
1.多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。(通俗解释)
2.如果不考虑隔离性，可能会引发如下问题:
脏读
不可重复读
幻读
查看事务隔离级别查看当前会话隔离级别select @@tx isolation;
脏读(dirty read):当一个事务读取另一个事务尚未提交的修改时，产生脏读
不可重复读(nonrepeatable read):同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读。
幻读(phantom read):同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读。
说明:我们待会举例一个案例来说明mysql的事务隔离级别.以对account表的操作来说明上面的几种情况.
简单的说：控制台A和B两个，在终端操作mysql。
1.设置好隔离级别，A是可重复读B是读未提交。
2.两边同时启动事务，在同一数据库下查看同一张表
3.A进行增、删、改时，B可以看到表的更改，增加数据可以看到，删除数据可以看到，改写数据可以看到，这就是读未提交
4.B改隔离级别为读已提交，在A提交事务的时候，B可以看到A进行的增、删、改。
5.B改隔离级别为可重复读，A进行任何事情不影响B看到表的数据
6..B改隔离级别为可串行化，A在进行表的修改时候，B不能查看表
B在进行事务的时候看某张表，需要在这个表的数据在这一刻是固定的，A和B同时在11点查看这个表，B需要一直看到11点时这个数据是多少，不能A进行修改了，B的数据也跟着修改，或者A进行提交了，B还在事务的房间里，直接能看到A提交的结果，这样也是不行的。
理解：在进行事务的时候，事务就是一间房，AB会在一起操作，数据会混乱。把A和B隔离开，A进行A的操作，B进行B的操作，互不影响，甚至可以加锁让某一方单独进行操作。
mysq|I事务隔离级别
1. 查看当前会话隔离级别set_transaction.sql
select @@tx isolation;
2.查看系统当前隔离级别
select @@global.tx isolation;
3.设置当前会话隔离级别
set session transaction isolation level repeatable read;
4.设置系统当前隔离级别
set global transaction isolation level repeatable read;
5.mysql默认的事务隔离级别是repeatable read ,-般情况下，没有特殊要求，没有必要修改(因为该级别可以满足绝大部分项目需求)
●全局修改， 修改mysql.ini配置文件，在最后加上
#可选参数有: READ-UNCOMMITTED,
READ-COMMITTED,
REPEATABLE-READ,
SERIALIZABLE.
[mysqId]
transaction-isolation = REPEATABLE-READ
ACID
事务的acid特性
1.原子性(Atomicity)
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
2.一致性(Consistency)
事务必须使数据库从一个一致性状态变换到另外-个一致性状态
3.隔离性(Isolation)
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
4.持久性(Durability)
持久性是指一个事务- -旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响
●事务的课堂练习[-定要自己去练习，体会]
1.登录mysq|控制客户端A,创建表Dog (id, name),开始一个事务，添加两条记录;
2.登录mysq|控制客户端B,开始一个事务，设置为读未提交.
3. A客户端修改Dog -条记录,不要提交。看看B客户端是否看到变化，说明什么问题?
4.登录mysq|客户端C,开始一个事务，设置为读已提交，这时A客户修改一条记录，不要提交，看看C客户端是否看到变化，说明什么问题?
3.B能看到A修改的数据，说明出现了脏读
4.C看不到变化，说明没有出现脏读，但有可能出现不可重复读和幻读
十二、其他
mysql表类型和存储引擎
基本介绍
1. MySQL的表类型由存储引擎(Storage Engines)决定，主要包括MyISAM、innoDB、Memory等。
2. MySQL 数据表主要支持六种类型,分别是: CSV、 Memory、 ARCHIVE、MRG_MYISAM、MYISAM、 InnoBDB。
3.这六种又分为两类，一类是”事务安全型" (transaction-safe), 比如:InnoDB;其余都属于第二类，称为"非事务安全型" (non-transaction-safe)[mysiam和memory].
主要的存储引擎/表类型特点
细节说明
我这里重点给大家介绍三种: MyISAM、InnoDB. MEMORY
1. MyISAM不支持事务、 也不支持外键，但其访问速度快，对事务完整性没有要求
2. InnoDB存储引擎提供 了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
3. MEMORY存储引擎使用存在内存中的内容来创建表。 每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦MySQL服务关闭，表中的数据就会丢失掉，表的结构还在。
三种存储引擎表使用案例
对前面我们提到的三种存储引擎，我们举例说明:
如何选择表的存储引擎
1.如果你的应用不需要事务,处理的只是基本的CRUD操作，那么MyISAM
是不二选择,速度快
2.如果需要支持事务，选择InnoDB。
3. Memory 存储引擎就是将数据存储在内存中，由于没有磁盘I./O的等待，
速度极快。但由于是内存存储引擎,所做的任何修改在服务器重启后都将
消失。(经典用法用户的在线状态().)
修改存储引擎
视图(view)
看一个需求
emp表的列信息很多，有些信息是个人重要信息(比如sal, comm,mgr, hiredate) ,如果我们希望某个用户只能查询emp表的(empno.ename, job和deptno )信息，有什么办法?
基本概念
1.视图是一个虚拟表,其内容由查询定义。同真实的表一样，视图包含列，其数据来自对应的真实表(基表)
2.视图和基表关系的示意图
视图的基本使用
1. create view视图名as select语句
2. alter view视图名as select语句
3. SHOW CREATE VIEW视图名
4. drop view视图名1,视图名2
完成前面提出的需求view.sql
创建一个视图emp_view01, 只能查询emp表的(empno、ename, job和deptno )信息
视图细节讨论
1.创建视图后，到数据库去看，对应视图只有一一个视图结构文件(形式:视图名.frm)
2.视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete ]
---针对前面的雇员管理系统-----
mysql> create view myview as select empno ,ename，job, comm from emp;
mysql> select * from myview;
mysq|> update myview set comm= 200 where empno= 7369; //修改视图,对基表都有变化
mysq|> update emp set comm= 100 where empno= 7369; //修改基表，对视频也有变化
3.视图中可以再使用视图[案例演示]
视图最佳实践
1. 安全。一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段。
2.性能。关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时，数据库查询通常会用到连接(JOIN)。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据。
3.灵活。如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。
视图练习
针对emp ,dept, 和salgrade三张表.创建一个视图emp_view03，可以显示雇员编号，雇员名，雇员部门名称和薪水级别[使用三张表，构建一个视图]
MySQL管理
mysql用户管理
Mysq!用户
mysql中的用户，都存储在系统数据库mysql中user表中
其中user表的重要字段说明:
1. host:允许登录的"位置”, localhost表示该用户只允许本机登录，也可以指定ip地址，比如:192.168.1.100
2.user: 用户名:
3. authentication string: 密码，是通过mysql的password()函数加密之后的密码。
创建用户
create user '用户名'@'允许登录位置' identified by '密码'
说明:创建用户，同时指定密码
删除用户
drop user '用户名’ @'允许登录位置’
drop user 'lwh_edu'@'localhost'
用户修改密码
修改自己的密码:
set password = password("密码');
修改他人的密码(需要有修改用户密码权限) :
set password for '用户名@登录位置' = password(密码);
MySQL中的权限
给用户授权
基本语法:
grant 权限列表 on 库.对象名 to '用户名’ @'登录位置’ [identified by‘密码’]
说明:
1,权限列表，多个权限用逗号分开
grant select on ........
grant select, delete, create on ........
grant all [privileges]on ........ //表示赋予该用户在该对象.上的所有权限
2.特别说明
*.* :代表本系统中的所有数据库的所有对象(表，视图，存储过程)
库.* :表示某个数据库中的所有数据对象(表，视图，存储过程等)
3，identified by可以省略，也可以写出.
(1)如果用户存在，就是修改该用户的密码。
(2)如果该用户不存在，就是创建该用户！
回收用户授权
基本语法:
revoke 权限列表 on 库.对象名 from '用户名" @ "登录位置';
权限生效指令
如果权限没有生效，可以执行下面命令.
基本语法:
FLUSH PRIVILEGES;
用户管理练习题
1.创建一个用户(你的名字，拼音),密码123, 并且只可以从本地登录,不让远程登录mysql
2.创建库和表testdb 下的news 表, 要求:使用root用户创建
3. 给用户分配查看news表和添加数据的权限
4.测试看看用户是否只有这几个权限
5. 修改密码为abc ,要求:使用root用户完成
6.重新登录
7.使用root用户删除你的用户
8.在删除用户前回收权限
1.create user 'hsp'@'localhost' identified by '123'
2.创建库testdb下的表news 表, 要求:使用root用户创建: CRAEATE DATABASE testdb 建库，CREATE TABLE news （ id INT,`name` VARCHAR(255),） 创建表，INSERT INTO news VALUES (100,上海) 添加数据
3. 查看news表和添加数据的权限：grant select,INSERT on testdb.news to 'hsp’ @'localhost’ root用户赋予权限
4.测试看看用户是否只有这几个权限
5. 修改密码为abc ,要求:使用root用户完成set password for ''hsp'@'localhost' = password(abc);
6.重新登录
7.使用root用户删除你的用户。drop user 'hsp’ @'localhost’
8.回收权限：revoke select,INSERT on testdb.news from 'hsp’ @'localhost’
evoke all on testdb.news from 'hsp’ @'localhost’
MySQL管理细节说明
细节说明： manage_detail.sql
1.在创建用户的时候，如果不指定Host,则为% , %表示表示所有IP都有连接权限
create userXXX;
2.你也可以这样指定
create user 'xxx' @' 192.168.1.%表示xxx用户在192.168.1.*的ip可以登录mysql
3.在删除用户的时候，如果host不是%,需要明确指定'用户@'host值'
课后练习
SELECT*FROM emp
SELECT*FROM dept
DESC dept
-- 显示部门名称
SELECT dname FROM dept
-- 显示13个月工资+奖金，别名年收入
SELECT ename,(sal+IFNULL(comm,0))*13 AS'年收入' FROM emp
-- 工资超过2850的雇员
SELECT ename,sal FROM emp WHERE sal>2850
-- 工资不在1500到2850的雇员
SELECT ename,sal FROM emp WHERE sal>2850 OR sal<1500
SELECT ename,sal FROM emp WHERE NOT (sal<=2850 AND sal>=1500)
-- 编号为7566的雇员姓名及部门编号
SELECT empno,ename,deptno FROM emp WHERE empno=7566
-- 部门编号10和30中工资超过1500的雇员和工资
SELECT ename,sal FROM emp WHERE (deptno=10 OR deptno=30) AND sal>1500
-- 显示无管理者的雇员及岗位
SELECT ename,job FROM emp WHERE mgr IS NULL
-- 排序
-- 1991-2-1到1991-5-1之间雇佣的雇员，岗位及雇佣日期，并以雇佣日期进行排序
SELECT empno,job,hiredate FROM emp WHERE hiredate
BETWEEN '1991-02-01' AND '1991-05-01' ORDER BY hiredate
-- 方法二
SELECT empno,job,hiredate FROM emp
WHERE hiredate >= '1991-02-01' AND hiredate <= '1991-05-01' ORDER BY hiredate
-- 显示获得补助的所有雇员名，工资及补助，工资排序降序
SELECT empno,sal,comm FROM emp WHERE comm IS NOT NULL ORDER BY sal DESC
-- 选择部门30中的所有员工.
SELECT*FROM emp WHERE deptno=30
-- 列出所有办事员(CLERK)的姓名，编号和部门编号.
SELECT ename,empno,deptno FROM emp WHERE job='CLERK'
-- 找出佣金高于薪金的员.
SELECT*FROM emp WHERE IFNULL(comm,0)>sal
-- 找出佣金高于薪金60%的员工.
SELECT*FROM emp WHERE IFNULL(comm,0)>sal*0.6
-- 找出部门10中所有经理(MANAGER)和部20中所有办事员(CLERK)的详细资料.
SELECT*FROM emp WHERE (deptno=10 AND job='MANAGER')
OR(deptno=20 AND job='CLERK')
-- 找出部门10中所有经理(MANAGER),部门20中所有办事员(CLERK),还有既不
-- 是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料.
SELECT*FROM emp WHERE (deptno=10 AND job='MANAGER')
OR(deptno=20 AND job='CLERK')
OR(job!='MANAGER' AND job!='CLERK'AND sal>2000)
-- 找出收取佣金的员工的不同工作.
SELECT DISTINCT job FROM emp WHERE comm IS NOT NULL
-- 找出不收取佣金或收取的佣金低于100的员工.
SELECT*FROM emp WHERE comm IS NULL OR IFNULL(comm,0)<100
-- 找出各月倒数第3天受雇的所有员工.
-- 提示：LAST_DAY('日期')，可以返回该日期所在月份的最后一天
SELECT LAST_DAY(NOW())
SELECT LAST_DAY('2019-9-9')
-- LAST_DAY('日期')-2得到日期月份的倒数第三天
SELECT*FROM emp WHERE LAST_DAY(hiredate)-2=hiredate
-- 若是求在月底最后三天入职的人，可以写成
SELECT*FROM emp WHERE LAST_DAY(hiredate)-2<=hiredate
-- 找出早于12年前受雇的员工.
SELECT*FROM emp WHERE hiredate<(DATE_SUB(NOW(),INTERVAL 12 YEAR))
SELECT*FROM emp WHERE NOW() >(DATE_ADD(hiredate,INTERVAL 12 YEAR))
-- 以首字母小写的方式显示所有员工的姓名.
SELECT CONCAT(LCASE(SUBSTRING(ename,1,1)),SUBSTRING(ename,2))
AS ename FROM emp
-- 显示正好为5个字符的员的姓名.
SELECT*FROM emp WHERE ename LIKE '_____'
SELECT*FROM emp WHERE LENGTH(ename)=5
-- 显示不带有R的员工的姓名.
SELECT*FROM emp WHERE ename NOT LIKE '%R%'
-- 显示所有员工姓名的前三个字符.
SELECT SUBSTRING(ename,1,3) FROM emp
SELECT LEFT(ename,3) FROM emp
-- 显示所有员工的姓名，用a替换所有A
SELECT REPLACE(ename,'A','a') FROM emp
-- 显示满10年服务年限的员工的姓名和受雇日期.
SELECT ename,hiredate FROM emp WHERE DATE_ADD(hiredate,INTERVAL 10 YEAR )<=NOW()
-- 显示员工的详细资料按姓名排序
SELECT*FROM emp ORDER BY ename
-- 显示员工的姓名和受雇日期，根据其服务年限,将最老的员工排在最前面.
SELECT ename,hiredate FROM emp ORDER BY hiredate
-- 显示所有员工的姓名、工作和薪金按工作降序排序，若工作相同则按薪金排序
SELECT ename,job,sal FROM emp ORDER BY job DESC,sal
-- 显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若
-- 月份相同则将最早年份的员工排在最前面.
SELECT ename,YEAR(hiredate) AS year_,MONTH(hiredate) AS month_ FROM emp
ORDER BY month_,year_
-- 显示在一个月为30天的情况所有员 工的日薪金,忽略余数.
SELECT ename,TRUNCATE((sal+IFNULL(comm,0))/30,0)AS '日薪金' FROM emp
-- 找出在(任何年份的)2月受聘的所有员工.
SELECT ename,MONTH(hiredate) AS month_ FROM emp WHERE MONTH(hiredate) = 2
SELECT*FROM emp WHERE MONTH(hiredate) = 2
-- 对于每个员工，显示其加入公司的天数.
SELECT ename,DATEDIFF(NOW(),hiredate)AS 入职天数 FROM emp
-- 显示姓名字段的任何位置包含A的所有员工的姓名.
SELECT*FROM emp WHERE ename LIKE '%A%'
-- 以年月日的方式显示所有员工的服务年限. (大概)
DATEDIFF(NOW(),hiredate) #入职天数
SELECT TRUNCATE(12077/365,0) FROM DUAL #年
SELECT FLOOR(12077/365) FROM DUAL #年
SELECT TRUNCATE((12077-(TRUNCATE(12077/365,0)*365))/30,0) FROM DUAL #月
SELECT FLOOR((12077-FLOOR(12077/365)*365)/30) FROM DUAL #月
SELECT FLOOR(12077 % 365/30) FROM DUAL #月
SELECT MOD(12077-(TRUNCATE(12077/365,0)*365),30) FROM DUAL #日
SELECT MOD(12077-(FLOOR(12077/365)*365),30) FROM DUAL #日
SELECT (12077 % 365)-FLOOR(12077 % 365/30)*30 FROM DUAL #日
SELECT TRUNCATE(DATEDIFF(NOW(),hiredate)/365,0) FROM emp #年
SELECT TRUNCATE((DATEDIFF(NOW(),hiredate)-(TRUNCATE(DATEDIFF(NOW(),hiredate)/365,0)*365))/30,0) FROM emp #月
SELECT MOD(DATEDIFF(NOW(),hiredate)-(TRUNCATE(DATEDIFF(NOW(),hiredate)/365,0)*365),30) FROM emp #日
-- 优化后
SELECT FLOOR(DATEDIFF(NOW(),hiredate)/365) FROM emp #年
SELECT FLOOR(DATEDIFF(NOW(),hiredate) % 365/30) FROM emp #月
SELECT MOD(DATEDIFF(NOW(),hiredate)-(FLOOR(DATEDIFF(NOW(),hiredate)/365)*365),30) FROM emp #日
SELECT ename,
CONCAT(
TRUNCATE(DATEDIFF(NOW(),hiredate)/365,0),'年',
TRUNCATE((DATEDIFF(NOW(),hiredate)-(TRUNCATE(DATEDIFF(NOW(),hiredate)/365,0)*365))/30,0),'月',
MOD(DATEDIFF(NOW(),hiredate)-(TRUNCATE(DATEDIFF(NOW(),hiredate)/365,0)*365),30),'天'
) AS '入职年份'
FROM emp
-- 第二种方法
SELECT ename,
FLOOR(DATEDIFF(NOW(),hiredate)/365)AS'工作年',
FLOOR(DATEDIFF(NOW(),hiredate) % 365/30)AS'工作月',
MOD(DATEDIFF(NOW(),hiredate)-(FLOOR(DATEDIFF(NOW(),hiredate)/365)*365),30)AS'工作天'
FROM emp
-- 列出至少有一个员工的所有部门
SELECT dname,COUNT(ename) FROM emp RIGHT JOIN dept ON emp.deptno=dept.deptno
GROUP BY dname HAVING COUNT(ename)>1 #很繁琐
SELECT COUNT(*) AS c, deptno FROM emp GROUP BY deptno HAVING c >1
-- 列出薪金比'SMITH'多的所有员工。
-- #'SMITH'的薪资
SELECT sal FROM emp WHERE ename='SMITH'
-- #代码
SELECT*FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='SMITH')
-- 列出受雇日期晚于其直接上级的所有员工。
-- #生成表
SELECT * FROM emp a,emp b WHERE a.mgr=b.empno
-- #代码
SELECT a.ename,a.mgr,a.hiredate,b.ename,b.hiredate FROM emp a,emp b
WHERE a.mgr=b.empno AND a.hiredate>b.hiredate
-- 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
SELECT dname,emp.* FROM dept LEFT JOIN emp ON dept.deptno=emp.deptno
SELECT dname,emp.* FROM emp RIGHT JOIN dept ON dept.deptno=emp.deptno
-- 列出所有"CLERK"(办事员)的姓名及其部门名称。
SELECT*FROM emp,dept WHERE emp.deptno=dept.deptno AND job="CLERK"
-- 列出最低薪金大于1500的各种工作。
SELECT job,COUNT(job)FROM emp WHERE sal>1500 GROUP BY job
-- 列出在部门"SALES" (销售部) 工作的员工的姓名。
SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno AND dname="SALES"
-- 列出薪金高于公司平均薪金的所有员工。
-- 1.列出平均薪资
SELECT AVG(sal) FROM emp
-- 2.比较薪资
SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp)
-- 列出与"SCOTT"从事相同工作的所有员工。
-- 找出"SCOTT"从事的工作
SELECT job FROM emp WHERE ename="SCOTT"
-- 查找结果
SELECT*FROM emp WHERE job=(SELECT job FROM emp WHERE ename="SCOTT")
-- 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
-- 部门30的薪金最高的薪水是多少
SELECT MAX(sal) FROM emp WHERE deptno=30
-- 查找结果
SELECT*FROM emp WHERE sal>(SELECT MAX(sal) FROM emp WHERE deptno=30)
-- 列出在每个部门工作的员工数量、平均工资和平均服务期限。
SELECT dname,dept.deptno,
COUNT(*)AS "员工数量",
AVG(sal)AS "平均工资" ,
AVG(DATEDIFF(NOW(),hiredate))AS "平均工作天数"
FROM dept LEFT JOIN emp ON dept.deptno=emp.deptno
GROUP BY dept.deptno,dname
-- 列出所有员工的姓名、部门名称和工资。
SELECT ename,(sal+IFNULL(comm,0))AS "工资",dname
FROM emp,dept WHERE emp.deptno=dept.deptno
-- 列出所有部门的详细信息和部门人数。
SELECT dname,loc,COUNT(*)
FROM dept LEFT JOIN emp ON dept.deptno=emp.deptno
GROUP BY dname,loc
-- 列出各种工作的最低工资。
SELECT job,MIN(sal) FROM emp GROUP BY job
-- 列出MANAGER(经理)的最低薪金。
SELECT ename,job,sal FROM emp WHERE job="MANAGER" ORDER BY sal LIMIT 0,1 #蠢啊
SELECT job,MIN(sal) FROM emp WHERE job="MANAGER"
-- 列出所有员工的年工资,按年薪从低到高排序。
SELECT ename,(sal+IFNULL(comm,0))*12 AS "年薪" FROM emp ORDER BY 年薪
课后练习二
扫一扫
2122
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
