目录
①.首先我们需要来看一下mysql默认数据库里面的四张表（user，db，tables_priv，columns_priv）.
五.mysql之常用函数、聚合函数以及合并（union&union all）
一.mysql安装与配置
1.mysql简介
why
数据在内存中容易丢失
数据在文件中，不便于操作
what
使用完整的管理系统统一管理，易于查询
where
凡是需要持久化存储数据的地方
木语
DB
数据库（database）：存储数据的“仓库”。它保存了一系列有组织的数据。
DBMS
数据库管理系统（Database Management System）。数据库是通过DBMS创建和操作的内器
SQL
结构化查询语言（Structure Query Language）：专门用来与数据库通信的语言
2.mysql 安装
my.ini
【mysql】
设置mysql客户端默认字符集
default-character-set=utf8
【mysqld】
设置3306端口
port = 3306
设置mysql的安装目录
basedir=D:\\tools\\mysql-5.7.23-winx64
设置mysql数据库的数据的存放目录
datadir=D:\\tools\\mysql-5.7.23-winx64\\data
允许最大连接数
max_connections=200
服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
安装MySQL（8.0的版本）
1.解压MySQL
2.修改my.ini文件
3.mysqld install
4.mysqld --initialize --console
5.net start mysql
6.mysql -P 3306 -u root -p
7.ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
8.Navicat链接测试如果MySQL已经安装过，那么执行mysqld remove
安装MySQL（5.7的版本）
1.解压MySQL
2.修改my.ini文件
3.mysqld install
4.mysqld --initialize --console
5.net start mysql
6.mysql -P 3306 -u root -p
7.SET PASSWORD = PASSWORD("123456");
8.Navicat链接测试如果MySQL已经安装过，那么执行mysqld remove
删除MySQL（这是5.5版本的卸载方式）
停止MySQL
1添加删除程序中卸载MySQL
2到安装目录删除MySQL
3删除：C:\Documents and Settings\All Users\Application Data\MySQL
C:\ProgramData\MySQL
4查看注册表：
regedit
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services
搜索mysql，找到一律干掉！
============================需要重启电脑(win xp)，直接重新安装即可
3.命令行登陆及权限修改
登录
mysql -u root -p启动、停止、重启MySQL服务
linux
一.启动方式
1、使用 service 启动：service mysqld start 2、使用 mysqld 脚本启动：/etc/inint.d/mysqld start 3、使用 safe_mysqld 启动：safe_mysqld&二.停止
1、使用 service 启动：service mysqld stop 2、使用 mysqld 脚本启动：/etc/inint.d/mysqld stop 3、mysqladmin shutdown三.重启
1、使用 service 启动：service mysqld restart 2、使用 mysqld 脚本启动：/etc/inint.d/mysqld restartwindow
net stop mysql net start mysql
①.首先我们需要来看一下mysql默认数据库里面的四张表（user，db，tables_priv，columns_priv）.
1、user表（用户层权限）
因为字段太多，只截取了一部分。首先登陆的时候验证Host,User,Password(authentication_string)也就是ip,用户名，密码是否匹配，匹配登陆成功将会为登陆者分配权限，分配权限的顺序也是按照上面四张表的排列顺序进行的，举个例子，如果user表的Select_priv为Y说明他拥有所有表的查找权限，如果为N就需要到下一级db表中进行权限分配了。其中的%是通配符，代表任意的意思。2、db表（数据库层权限）
来到db表之后会匹配Host,User然后会根据Db字段对应的表进行权限分配，像Select_priv这些字段对应的权限大家应该都能看出来是对应着什么权限了吧，这里不细说了（不偷懒，举个例子Select_priv，Insert_priv，Update_priv，Delete_priv，Create_priv，Drop_priv分别代表着查询，增加，更新，删除，创建，销毁）。其中Y代表这拥有此项权限，N则代表没有此项权限。3、tables_priv表（表层权限）
与上面一样，这是通过Host，Db，User，Table来进行定位到表层的一个权限分配。不过它只有Table_priv和Column_priv两个字段来记录权限。4、columns_priv表（字段层权限）
顾名思义，字段层权限，通过Host，Db，User，Table，Column来进行定位到字段层的一个权限分配，只有Column_priv来记录权限。
相关SQL
SELECT Host,User,authentication_string from user;
SELECT * from user;
SELECT * from db;
SELECT * from tables_priv;
SELECT * from columns_priv;4.图形化工具navicat12的使用
略...
二.mysql之账号管理、建库以及四大引擎
账号管理一定是从mysql库开始的
use mysql;
1.数据库账号及权限管理
MySQL5.7及MySQL8的账号管理、建库以及四大引擎 - 晓码阁
http://47.100.191.44/blog/articles/423
1.查询用户
SELECT * FROM user;2.创建用户并设置登录密码（MySQL5.7）
#命令：create user 用户名 identified by '密码'; #注：identified by会将纯文本密码加密作为散列值存储 create user ls identified by '123456';MySQL8
#用户名密码创建需要分开 #命令：create user 用户名; create user ls;3.查看用户信息（MySQL5.7）
select host,user,password from user;MySQL8
select host,user,authentication_string from user;4.删除用户（慎用）
命令：drop user 用户名：drop user ls;5.修改用户密码
5.1修改密码（MySQL5.7）
命令：set password for 用户名=password('新密码'); set password for zs=password('123456');MySQL（8）
ALTER USER 用户 IDENTIFIED WITH mysql_native_password BY '密码'; ALTER USER 'ls'@'%' IDENTIFIED WITH mysql_native_password BY '123456';5.2刷新配置
命令：flush privileges;MySQL5.7与MySQL8关于权限操作没有差异性
6.设置权限（Grant）
#语法：grant privileges on databasename.tablename to username@'host'; #给 zs用户 赋予 数据库db_xiaoli中的表t_p1_user 查询权限 grant SELECT on db_xiaoli.t_p1_user to zs@'%'; #给 zs用户 赋予 数据库db_xiaoli中的表t_p1_user 修改权限 grant UPDATE on db_xiaoli.t_p1_user to zs@'%'; #给 zs用户 赋予 数据库db_xiaoli中所有表 查询权限 grant SELECT on db_xiaoli.* to zs@'%'; #给 zs用户 赋予 数据库db_xiaoli中所有表 所有权限 grant ALL on db_xiaoli.* to zs@'%';7.撤销权限（Revoke）
#语法：revoke privileges on databasename.tablename from username@'host'; #啥也不能回收,不会对GRANT ALL PRIVILEGES ON `db_xiaoli`.* TO `zs`@`%`有任何影响 revoke DELETE on db_xiaoli.t_p1_user from zs@'%'; #可以回收GRANT SELECT, UPDATE ON `db_xiaoli`.`t_p1_user` TO `zs`@`%`这条权限语句 revoke all on db_xiaoli.t_p1_user from zs@'%'; #可以回收GRANT ALL PRIVILEGES ON `db_xiaoli`.* TO `zs`@`%`这条赋权语句带来的权限 revoke all on db_xiaoli.* from zs@'%'; #注：revoke只能回收grants列表中更小的权限；设置权限（Grant）和撤销权限（Revoke）的参数说明：
1) privileges： 用户的操作权限,如SELECT,INSERT,UPDATE,DELETE等，如果要授予所有权限直接使用：all;
2) databasename：数据库名;
3) tablename： 表名，如果要授予用户对所有数据库和表的操作权限直接使用：*.*;8.查看用户权限
命令：show grants for 用户名：show grants for 'zs'@'%';user表中host列的值的意义
% 匹配所有主机
localhost localhost不会被解析成IP地址，直接通过UNIXsocket连接
127.0.0.1 会通过TCP/IP协议连接，并且只能在本机访问；
::1 ::1就是兼容支持ipv6的，表示同ipv4的127.0.0.1
2.数据库创建
1.MySQL默认数据库介绍
1)information_schema：是一个信息数据库，它保存着关于MySQL服务器所维护的所有其他数据库的信息；
2)mysql：核心数据库，类似于sql server中的master表，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息；
3)test：测试数据库，没有东西；
2.创建数据库
语法：create database 数据库名;
或者
create database if not exists 数据库名 default charset utf8 collate utf8_general_ci;
注：默认的数据库编码集：utf8（即UTF-8），collate表示排序规则为utf8_general_ci
3.查看所有数据库
语法：show databases;
4.删除数据库(慎用)
语法：drop database 数据库名;
3.四大引擎的作用及区别
存储引擎查看
MySQL给开发者提供了查询存储引擎的功能，我这里使用的是MySQL5.1，可以使用：
SHOW ENGINESInnoDB存储引擎
InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，上图也看到了，InnoDB是默认的MySQL引擎。InnoDB主要特性有：
1、InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来，甚至在同一个查询中也可以混合
2、InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的
3、InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件）。这与MyISAM表不同，比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上
4、InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键
5、InnoDB被用在众多需要高性能的大型数据库站点上
InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件
MyISAM存储引擎
MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物。MyISAM主要特性有：
1、大文件（达到63位文件长度）在支持大文件的文件系统和操作系统上被支持
2、当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块自动完成
3、每个MyISAM表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16
4、最大的键长度是1000字节，这也可以通过编译来改变，对于键长度超过250字节的情况，一个超过1024字节的键将被用上
5、BLOB和TEXT列可以被索引
6、NULL被允许在索引的列中，这个值占每个键的0~1个字节
7、所有数字键值以高字节优先被存储以允许一个更高的索引压缩
8、每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列，当INSERT和UPDATE操作的时候该列被更新，同时AUTO_INCREMENT列将被刷新。所以说，MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快
9、可以把数据文件和索引文件放在不同目录
10、每个字符列可以有不同的字符集
11、有VARCHAR的表可以固定或动态记录长度
12、VARCHAR和CHAR列可以多达64KB
使用MyISAM引擎创建数据库，将产生3个文件。文件的名字以表名字开始，扩展名之处文件类型：frm文件存储表定义、数据文件的扩展名为.MYD（MYData）、索引文件的扩展名时.MYI（MYIndex）
MEMORY存储引擎MEMORY存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问。MEMORY主要特性有：
1、MEMORY表的每个表可以有多达32个索引，每个索引16列，以及500字节的最大键长度
2、MEMORY存储引擎执行HASH和BTREE缩影
3、可以在一个MEMORY表中有非唯一键值
4、MEMORY表使用一个固定的记录长度格式
5、MEMORY不支持BLOB或TEXT列
6、MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引
7、MEMORY表在所由客户端之间共享（就像其他任何非TEMPORARY表）
8、MEMORY表内存被存储在内存中，内存是MEMORY表和服务器在查询处理时的空闲中，创建的内部表共享
9、当不再需要MEMORY表的内容时，要释放被MEMORY表使用的内存，应该执行DELETE FROM或TRUNCATE TABLE，或者删除整个表（使用DROP TABLE）
存储引擎的选择
不同的存储引擎都有各自的特点，以适应不同的需求，如下表所示：
| 功 能 | MYISAM | Memory | InnoDB | Archive |
|---|---|---|---|---|
| 存储限制 | 256TB | RAM | 64TB | None |
| 支持事物 | No | No | Yes | No |
| 支持全文索引 | Yes | No | No | No |
| 支持数索引 | Yes | Yes | Yes | No |
| 支持哈希索引 | No | Yes | No | No |
| 支持数据缓存 | No | N/A | Yes | No |
| 支持外键 | No | No | Yes | No |
如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择
如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率
如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果
如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive
使用哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能
三.mysql之数据类型、建表以及约束
1.数据类型介绍
MySQL中定义数据字段的类型对你数据库的优化是非常重要的。
MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。
1）字符类型：char varchar text blob
2）数值类型：int bigint float decimal
int -> int
bigint -> long
float -> 成绩
decimal -> 货币类型(精度,小数)
3）日期类型：date time datetime timestamp
date -> yyyy:MM:dd HH:mm:ss
time -> HH:mm:ss
datetime -> yyyy:MM:dd
timestamp（时间戳） -> 长整数
2.建表、删表语法
1.表的创建 ★
/*
语法：
create table 表名(
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
...
列名 列的类型【(长度) 约束】
)
*/
案例：创建表Book
CREATE TABLE book(
id INT,#编号
bName VARCHAR(20),#图书名
price DOUBLE,#价格
authorId INT,#作者编号
publishDate DATETIME#出版日期
);
DESC book;
案例：创建表author
CREATE TABLE IF NOT EXISTS author(
id INT,
au_name VARCHAR(20),
nation VARCHAR(10)
)
DESC author;
2.表的修改
/*
语法
alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;
*/
①修改列名
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;
②修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;
③添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE;
④删除列
ALTER TABLE book_author DROP COLUMN annual;
⑤修改表名
ALTER TABLE author RENAME TO book_author;
DESC book;
3.表的删除
DROP TABLE IF EXISTS book_author;
SHOW TABLES;
通用的写法：
DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;
DROP TABLE IF EXISTS 旧表名;
CREATE TABLE 表名();
4.表的复制
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.主键（自动增长）、外键、非空等约束的使用
/*
含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性
分类：六大约束
NOT NULL：非空，用于保证该字段的值不能为空
比如姓名、学号等
DEFAULT:默认，用于保证该字段有默认值
比如性别
PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空
比如学号、员工编号等
UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空
比如座位号
CHECK:检查约束【mysql中不支持】
比如年龄、性别
FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值
在从表添加外键约束，用于引用主表中某列的值
比如学生表的专业编号，员工表的部门编号，员工表的工种编号
添加约束的时机：
1.创建表时
2.修改表时
约束的添加分类：
列级约束：
六大约束语法上都支持，但外键约束没有效果表级约束：
除了非空、默认，其他的都支持
主键和唯一的大对比：
保证唯一性 是否允许为空 一个表中可以有多少个 是否允许组合
主键√×至多有1个 √，但不推荐
唯一√√可以有多个 √，但不推荐
外键：
1、要求在从表设置外键关系
2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
3、主表的关联列必须是一个key（一般是主键或唯一）
4、插入数据时，先插入主表，再插入从表
删除数据时，先删除从表，再删除主表
*/
CREATE TABLE 表名(
字段名 字段类型 列级约束,
字段名 字段类型,
表级约束
)
CREATE DATABASE students;
一、创建表时添加约束
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 字段名 字段类型 新约束;
2、添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;
*/
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT
)
DESC stuinfo;
1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
3.添加主键
①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
4.添加唯一
①列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
②表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);
5.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);
三.修改表时删除约束
1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;
3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;
4.删除唯一
ALTER TABLE stuinfo DROP INDEX seat;
5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
SHOW INDEX FROM stuinfo;
四.mysql之CRUD
http://47.100.191.44/blog/articles/425http://47.100.191.44/blog/articles/425
1.基础查询
/*
语法：
select 查询列表 from 表名;
类似于：System.out.println(打印东西);
特点：
1、查询列表可以是：表中的字段、常量值、表达式、函数
2、查询的结果是一个虚拟的表格
*/
USE myemployees;
1.查询表中的单个字段
SELECT last_name FROM t_mysql_employees;
2.查询表中的多个字段
SELECT last_name,salary,email FROM t_mysql_employees;
3.查询表中的所有字段
方式一：
SELECT
`employee_id`,
`first_name`,
`last_name`,
`phone_number`,
`last_name`,
`job_id`,
`phone_number`,
`job_id`,
`salary`,
`commission_pct`,
`manager_id`,
`department_id`,
`hiredate`
FROM
t_mysql_employees ;
方式二：
SELECT * FROM t_mysql_employees;4.查询常量值
SELECT 100;
SELECT 'john';5.查询表达式
SELECT 100%98;6.查询函数
SELECT VERSION();
7.起别名
/*
①便于理解
②如果要查询的字段有重名的情况，使用别名可以区分开来*/
方式一：使用as
SELECT 100%98 AS 结果;
SELECT last_name AS 姓,first_name AS 名 FROM t_mysql_employees;
方式二：使用空格
SELECT last_name 姓,first_name 名 FROM t_mysql_employees;
案例：查询salary，显示结果为 out put
SELECT salary AS "out put" FROM t_mysql_employees;
8.去重
案例：查询员工表中涉及到的所有的部门编号
SELECT DISTINCT department_id FROM t_mysql_employees;
9.+号的作用
/*
java中的+号：
①运算符，两个操作数都为数值型
②连接符，只要有一个操作数为字符串
mysql中的+号：
仅仅只有一个功能：运算符
select 100+90; 两个操作数都为数值型，则做加法运算
select '123'+90;只要其中一方为字符型，试图将字符型数值转换成数值型
如果转换成功，则继续做加法运算
select 'john'+90;如果转换失败，则将字符型数值转换成0
select null+10; 只要其中一方为null，则结果肯定为null
*/
案例：查询员工名和姓连接成一个字段，并显示为 姓名
SELECT CONCAT('a','b','c') AS 结果;
SELECT
CONCAT(last_name,first_name) AS 姓名
FROM
t_mysql_employees;
2.过滤和排序数据
1、过滤
2、在查询中过滤行
3、where子句
4、比较运算
5、between
6、in
7、like
8、null
9、逻辑运算
3.过滤查询案例
/*
语法：
select
查询列表
from
表名
where
筛选条件;
分类：
一、按条件表达式筛选简单条件运算符：> < = != <> >= <=
二、按逻辑表达式筛选
逻辑运算符：
作用：用于连接条件表达式
&& || !
and or not&&和and：两个条件都为true，结果为true，反之为false
||或or： 只要有一个条件为true，结果为true，反之为false
!或not： 如果连接的条件本身为false，结果为true，反之为false三、模糊查询
like
between and
in
is null*/
3.1、按条件表达式筛选
案例1：查询工资>12000的员工信息
SELECT
*
FROM
t_mysql_employees
WHERE
salary>12000;
案例2：查询部门编号不等于90号的员工名和部门编号
SELECT
last_name,
department_id
FROM
t_mysql_employees
WHERE
department_id<>90;
3.2、按逻辑表达式筛选
案例1：查询工资z在10000到20000之间的员工名、工资以及奖金
SELECT
last_name,
salary,
commission_pct
FROM
t_mysql_employees
WHERE
salary>=10000 AND salary<=20000;
案例2：查询部门编号不是在90到110之间，或者工资高于15000的员工信息
SELECT
*
FROM
t_mysql_employees
WHERE
NOT(department_id>=90 AND department_id<=110) OR salary>15000;
3.3模糊查询
/*
like
between and
in
is null|is not null
*/
1.like
/*
特点：
①一般和通配符搭配使用
通配符：
% 任意多个字符,包含0个字符
_ 任意单个字符
*、
案例1：查询员工名中包含字符a的员工信息
select
*
from
employees
where
last_name like '%a%';#abc
案例2：查询员工名中第三个字符为e，第五个字符为a的员工名和工资
select
last_name,
salary
FROM
t_mysql_employees
WHERE
last_name LIKE '__n_l%';
案例3：查询员工名中第二个字符为_的员工名
SELECT
last_name
FROM
t_mysql_employees
WHERE
last_name LIKE '_$_%' ESCAPE '$';
2.between and
/*
①使用between and 可以提高语句的简洁度
②包含临界值
③两个临界值不要调换顺序
*/
案例1：查询员工编号在100到120之间的员工信息
SELECT
*
FROM
t_mysql_employees
WHERE
employee_id <= 120 AND employee_id>=100;
----------------------
SELECT
*
FROM
t_mysql_employees
WHERE
employee_id BETWEEN 100 AND 120;
3.in
/*
含义：判断某字段的值是否属于in列表中的某一项
特点：
①使用in提高语句简洁度
②in列表的值类型必须一致或兼容
③in列表中不支持通配符
*/
案例：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
SELECT
last_name,
job_id
FROM
t_mysql_employees
WHERE
job_id = 'IT_PROT' OR job_id = 'AD_VP' OR JOB_ID ='AD_PRES';
------------------
SELECT
last_name,
job_id
FROM
t_mysql_employees
WHERE
job_id IN( 'IT_PROT' ,'AD_VP','AD_PRES');
4、is null
/*
=或<>不能用于判断null值
is null或is not null 可以判断null值
*/
案例1：查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
t_mysql_employees
WHERE
commission_pct IS NULL;
案例1：查询有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
t_mysql_employees
WHERE
commission_pct IS NOT NULL;
----------以下为×
SELECT
last_name,
commission_pct
FROM
t_mysql_employees
WHERE
salary IS 12000;
安全等于 <=>
案例1：查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
t_mysql_employees
WHERE
commission_pct <=>NULL;
案例2：查询工资为12000的员工信息
SELECT
last_name,
salary
FROM
t_mysql_employees
WHERE
salary <=> 12000;
#is null pk <=>
IS NULL:仅仅可以判断NULL值，可读性较高，建议使用
<=> :既可以判断NULL值，又可以判断普通的数值，可读性较低
4.order by 子句
#、进阶3：排序查询
/*
语法：
select 查询列表
from 表名
【where 筛选条件】
order by 排序的字段或表达式;
特点：
1、asc代表的是升序，可以省略
desc代表的是降序
2、order by子句可以支持 单个字段、别名、表达式、函数、多个字段
3、order by子句在查询语句的最后面，除了limit子句
*/
1、按单个字段排序
SELECT * FROM t_mysql_employees ORDER BY salary DESC;
2、添加筛选条件再排序
案例：查询部门编号>=90的员工信息，并按员工编号降序
SELECT *
FROM t_mysql_employees
WHERE department_id>=90
ORDER BY employee_id DESC;
3、按表达式排序
案例：查询员工信息 按年薪降序
SELECT *,salary*12*(1+IFNULL(commission_pct,0))
FROM t_mysql_employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
4、按别名排序
案例：查询员工信息 按年薪升序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM t_mysql_employees
ORDER BY 年薪 ASC;
5、按函数排序
案例：查询员工名，并且按名字的长度降序
SELECT LENGTH(last_name),last_name
FROM t_mysql_employees
ORDER BY LENGTH(last_name) DESC;
6、按多个字段排序
案例：查询员工信息，要求先按工资降序，再按employee_id升序
SELECT *
FROM t_mysql_employees
ORDER BY salary DESC,employee_id ASC;
排序练习1.查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序
SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM t_mysql_employees
ORDER BY 年薪 DESC,last_name ASC;
2.选择工资不在8000到17000的员工的姓名和工资，按工资降序
SELECT last_name,salary
FROM t_mysql_employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;
3.查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT *,LENGTH(email)
FROM t_mysql_employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,department_id ASC;
5.分组查询
/*
功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：
sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数
特点：
1、sum、avg一般用于处理数值型
max、min、count可以处理任何类型
2、以上分组函数都忽略null值
3、可以和distinct搭配实现去重的运算
4、count函数的单独介绍
一般使用count(*)用作统计行数
5、和分组函数一同查询的字段要求是group by后的字段
*/
1、简单 的使用
SELECT SUM(salary) FROM t_mysql_employees;
SELECT AVG(salary) FROM t_mysql_employees;
SELECT MIN(salary) FROM t_mysql_employees;
SELECT MAX(salary) FROM t_mysql_employees;
SELECT COUNT(salary) FROM t_mysql_employees;
SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
FROM t_mysql_employees;
SELECT SUM(salary) 和,ROUND(AVG(salary),2) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
FROM t_mysql_employees;
2、参数支持哪些类型
SELECT SUM(last_name) ,AVG(last_name) FROM t_mysql_employees;
SELECT SUM(hiredate) ,AVG(hiredate) FROM t_mysql_employees;
SELECT MAX(last_name),MIN(last_name) FROM t_mysql_employees;
SELECT MAX(hiredate),MIN(hiredate) FROM t_mysql_employees;
SELECT COUNT(commission_pct) FROM t_mysql_employees;
SELECT COUNT(last_name) FROM t_mysql_employees;
3、是否忽略null
SELECT SUM(commission_pct) ,AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 FROM t_mysql_employees;
SELECT MAX(commission_pct) ,MIN(commission_pct) FROM t_mysql_employees;
SELECT COUNT(commission_pct) FROM t_mysql_employees;
SELECT commission_pct FROM t_mysql_employees;
4、和distinct搭配
SELECT SUM(DISTINCT salary),SUM(salary) FROM t_mysql_employees;
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM t_mysql_employees;
5、count函数的详细介绍
SELECT COUNT(salary) FROM t_mysql_employees;
SELECT COUNT(*) FROM t_mysql_employees;
SELECT COUNT(1) FROM t_mysql_employees;
效率：
MYISAM存储引擎下 ，COUNT(*)的效率高
INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些
6、和分组函数一同查询的字段有限制,employee_id是最小的那个
SELECT AVG(salary),employee_id FROM t_mysql_employees;
分组函数练习1.查询公司员工工资的最大值，最小值，平均值，总和
SELECT MAX(salary) 最大值,MIN(salary) 最小值,AVG(salary) 平均值,SUM(salary) 和
FROM t_mysql_employees;
2.查询员工表中的最大入职时间和最小入职时间的相差天数 （DIFFRENCE）
SELECT MAX(hiredate) 最大,MIN(hiredate) 最小,(MAX(hiredate)-MIN(hiredate))/1000/3600/24 DIFFRENCE
FROM t_mysql_employees;
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) DIFFRENCE
FROM t_mysql_employees;
SELECT DATEDIFF('1995-2-7','1995-2-6');
3.查询部门编号为90的员工个数
SELECT COUNT(*) FROM t_mysql_employees WHERE department_id = 90;
五.mysql之常用函数、聚合函数以及合并（union&union all）
1.笛卡尔集
笛卡尔集会在下面条件下产生
– 省略连接条件
– 连接条件无效
– 所有表中的所有行互相连接
• 为了避免笛卡尔集， 可以在 WHERE 加入有 效的连接条件。
2.等值/连接连接
使用连接在多个表中查询数据
• 在 WHERE 子句中写入连接条件。
• 在表中有相同列时，在列名之前加上表名前缀
区分重复的列名
• 在不同表中具有相同列名的列可以用表的别名
加以区分。
• 如果使用了表别名，则在select语句中需要使
用表别名代替表名
• 表别名最多支持32个字符长度，但建议越少越
好
表的别名
• 使用别名可以简化查询。
• 使用表名前缀可以提高执行效率。
连接多个表
• 连接 n个表,至少需要 n-1个连接条件。 例如：连接
三个表，至少需要两个连接条件。
连接查询案例
/*
含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件分类：
按年代分类：
sql92标准:仅仅支持内连接
sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接按功能分类：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接
右外连接
全外连接交叉连接
*/SELECT * FROM beauty;
SELECT * FROM boys;
SELECT NAME,boyName FROM boys,beauty
WHERE beauty.boyfriend_id= boys.id;一、sql92标准
1、等值连接
/*① 多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③ 多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选
*/案例1：查询女神名和对应的男神名
SELECT NAME,boyName
FROM boys,beauty
WHERE beauty.boyfriend_id= boys.id;#案例2：查询员工名和对应的部门名
SELECT last_name,department_name
FROM t_mysql_employees,departments
WHERE t_mysql_employees.`department_id`=t_mysql_departments.`department_id`;
2、为表起别名
/*
①提高语句的简洁度
②区分多个重名的字段注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
*/
查询员工名、工种号、工种名SELECT e.last_name,e.job_id,j.job_title
FROM t_mysql_employees e,jobs j
WHERE e.`job_id`=j.`job_id`;
3、两个表的顺序是否可以调换查询员工名、工种号、工种名
SELECT e.last_name,e.job_id,j.job_title
FROM t_mysql_jobs j,t_mysql_employees e
WHERE e.`job_id`=j.`job_id`;
4、可以加筛选
案例：查询 有奖金 的员工名、部门名SELECT last_name,department_name,commission_pct
FROM t_mysql_employees e,t_mysql_departments d
WHERE e.`department_id`=d.`department_id`
AND e.`commission_pct` IS NOT NULL;案例2：查询 城市名中第二个字符为o 的部门名和城市名
SELECT department_name,city
FROM t_mysql_departments t_mysql_d,locations l
WHERE d.`location_id` = l.`location_id`
AND city LIKE '_o%';5、可以加分组
案例1：查询 每个城市 的部门个数SELECT COUNT(*) 个数,city
FROM t_mysql_departments d,t_mysql_locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY city;
案例2：查询 有奖金 的 每个部门 的 部门名和部门的领导编号 和该部门的最低工资
SELECT department_name,d.`manager_id`,MIN(salary)
FROM t_mysql_departments d,t_mysql_employees e
WHERE d.`department_id`=e.`department_id`
AND commission_pct IS NOT NULL
GROUP BY department_name,d.`manager_id`;
6、可以加排序
案例：查询 每个工种 的 工种名和员工的个数，并且 按员工个数降序SELECT job_title,COUNT(*)
FROM t_mysql_employees e,t_mysql_jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;7、可以实现三表连接？
案例：查询员工名、部门名和所在的城市
SELECT last_name,department_name,city
FROM t_mysql_employees e,t_mysql_departments d,t_mysql_locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`
AND city LIKE 's%'ORDER BY department_name DESC;
2、非等值连接
案例1：查询员工的工资和工资级别SELECT salary,grade_level
FROM t_mysql_employees e,t_mysql_job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
AND g.`grade_level`='A';/*
select salary,employee_id from employees;
select * from job_grades;
CREATE TABLE job_grades
(grade_level VARCHAR(3),
lowest_sal int,
highest_sal int);INSERT INTO job_grades
VALUES ('A', 1000, 2999);INSERT INTO job_grades
VALUES ('B', 3000, 5999);INSERT INTO job_grades
VALUES('C', 6000, 9999);INSERT INTO job_grades
VALUES('D', 10000, 14999);INSERT INTO job_grades
VALUES('E', 15000, 24999);INSERT INTO job_grades
VALUES('F', 25000, 40000);*/
3、自连接
案例：查询 员工名和上级的名称SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM t_mysql_employees e,t_mysql_employees m
WHERE e.`manager_id`=m.`employee_id`;
3.join链接
分类
– 内连接 [inner] join on
– 外连接
• 左外连接 left [outer] join on
• 右外连接 right [outer] join on
使用ON 子句创建连接
• 自然连接中是以具有相同名字的列为连接条件的。
• 可以使用 ON 子句指定额外的连接条件。
• 这个连接条件是与其它条件分开的。
• ON 子句使语句具有更高的易读性。
join案例
二、sql99语法
/*
语法：
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
分类：
内连接（★）：inner
外连接
左外(★):left 【outer】
右外(★)：right 【outer】
全外：full【outer】
交叉连接：cross*/
一）内连接
/*
语法：select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;分类：
等值
非等值
自连接特点：
①添加排序、分组、筛选
②inner可以省略
③ 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集*/
1、等值连接
案例1.查询员工名、部门名SELECT last_name,department_name
FROM t_mysql_departments d
JOIN t_mysql_employees e
ON e.`department_id` = d.`department_id`;案例2.查询名字中包含e的员工名和工种名（添加筛选）
SELECT last_name,job_title
FROM t_mysql_employees e
INNER JOIN t_mysql_jobs j
ON e.`job_id`= j.`job_id`
WHERE e.`last_name` LIKE '%e%';3. 查询部门个数>3的城市名和部门个数，（添加分组+筛选）
①查询每个城市的部门个数
②在①结果上筛选满足条件的
SELECT city,COUNT(*) 部门个数
FROM t_mysql_departments d
INNER JOIN t_mysql_locations l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;
案例4.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）①查询每个部门的员工个数
SELECT COUNT(*),department_name
FROM t_mysql_employees e
INNER JOIN t_mysql_departments d
ON e.`department_id`=d.`department_id`
GROUP BY department_name② 在①结果上筛选员工个数>3的记录，并排序
SELECT COUNT(*) 个数,department_name
FROM t_mysql_employees e
INNER JOIN t_mysql_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 t_mysql_employees e
INNER JOIN t_mysql_departments d ON e.`department_id`=d.`department_id`
INNER JOIN t_mysql_jobs j ON e.`job_id` = j.`job_id`ORDER BY department_name DESC;
二）非等值连接
查询员工的工资级别
SELECT salary,grade_level
FROM t_mysql_employees e
JOIN t_mysql_job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
查询工资级别的个数>20的个数，并且按工资级别降序
SELECT COUNT(*),grade_level
FROM t_mysql_employees e
JOIN t_mysql_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 t_mysql_employees e
JOIN t_mysql_employees m
ON e.`manager_id`= m.`employee_id`;查询姓名中包含字符k的员工的名字、上级的名字
SELECT e.last_name,m.last_name
FROM t_mysql_employees e
JOIN t_mysql_employees m
ON e.`manager_id`= m.`employee_id`
WHERE e.`last_name` LIKE '%k%';
二、外连接/*
应用场景：用于查询一个表中有，另一个表没有的记录特点：
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接，left join左边的是主表
右外连接，right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
*/
引入：查询男朋友 不在男神表的的女神名SELECT * FROM t_mysql_beauty;
SELECT * FROM t_mysql_boys;左外连接
SELECT b.*,bo.*
FROM t_mysql_boys bo
LEFT OUTER JOIN t_mysql_beauty b
ON b.`boyfriend_id` = bo.`id`
WHERE b.`id` IS NULL;
案例1：查询哪个部门没有员工
左外
SELECT d.*,e.employee_id
FROM t_mysql_departments d
LEFT OUTER JOIN t_mysql_employees e
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;
右外SELECT d.*,e.employee_id
FROM t_mysql_employees e
RIGHT OUTER JOIN t_mysql_departments d
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;
全外
USE girls;
SELECT b.*,bo.*
FROM t_mysql_beauty b
FULL OUTER JOIN t_mysql_boys bo
ON b.`boyfriend_id` = bo.id;
交叉连接SELECT b.*,bo.*
FROM t_mysql_beauty b
CROSS JOIN boys bo;
多表查询练习
1.显示所有员工的姓名，部门号和部门名称。
USE myemployees;SELECT last_name,d.department_id,department_name
FROM t_mysql_employees e,t_mysql_departments d
WHERE e.`department_id` = d.`department_id`;
2.查询90号部门员工的job_id和90号部门的location_idSELECT job_id,location_id
FROM t_mysql_employees e,t_mysql_departments d
WHERE e.`department_id`=d.`department_id`
AND e.`department_id`=90;3.选择所有有奖金的员工的
last_name , department_name , location_id , city
SELECT last_name , department_name , l.location_id , city
FROM t_mysql_employees e,t_mysql_departments d,t_mysql_locations l
WHERE e.department_id = d.department_id
AND d.location_id=l.location_id
AND e.commission_pct IS NOT NULL;
4.选择city在Toronto工作的员工的
last_name , job_id , department_id , department_nameSELECT last_name , job_id , d.department_id , department_name
FROM t_mysql_employees e,t_mysql_departments d ,t_mysql_locations l
WHERE e.department_id = d.department_id
AND d.location_id=l.location_id
AND city = 'Toronto';5.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT department_name,job_title,MIN(salary) 最低工资
FROM t_mysql_employees e,t_mysql_departments d,t_mysql_jobs j
WHERE e.`department_id`=d.`department_id`
AND e.`job_id`=j.`job_id`
GROUP BY department_name,job_title;6.查询每个国家下的部门个数大于2的国家编号
SELECT country_id,COUNT(*) 部门个数
FROM t_mysql_departments d,t_mysql_locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY country_id
HAVING 部门个数>2;7、选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
employeesEmp#managerMgr#
kochhar101king100
SELECT e.last_name employees,e.employee_id "Emp#",m.last_name manager,m.employee_id "Mgr#"
FROM t_mysql_employees e,t_mysql_employees m
WHERE e.manager_id = m.employee_id
AND e.last_name='kochhar';
外连接案例
一、查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充
SELECT b.id,b.name,bo.*
FROM t_mysql_beauty b
LEFT OUTER JOIN t_mysql_boys bo
ON b.`boyfriend_id` = bo.`id`
WHERE b.`id`>3;
二、查询哪个城市没有部门SELECT city
FROM t_mysql_departments d
RIGHT OUTER JOIN t_mysql_locations l
ON d.`location_id`=l.`location_id`
WHERE d.`department_id` IS NULL;三、查询部门名为SAL或IT的员工信息
SELECT e.*,d.department_name,d.`department_id`
FROM t_mysql_departments d
LEFT JOIN t_mysql_employees e
ON d.`department_id` = e.`department_id`
WHERE d.`department_name` IN('SAL','IT');
SELECT * FROM t_mysql_departments
WHERE `department_name` IN('SAL','IT');
常见函数
1.字符函数
| 作用 | 函数 | 结果 |
|---|---|---|
| 转小写 | LOWER('SQL Course') | sql course |
| 转大写 | UPPER('SQL Course') | SQL COURSE |
| 拼接 | CONCAT('Hello', 'World') | HelloWorld |
| 截取 | SUBSTR('HelloWorld',1,5) | Hello |
| 长度 | LENGTH('HelloWorld') | 10 |
| 字符出现索引值 | INSTR('HelloWorld', 'W') | 6 |
| 字符截取后半段 | TRIM('H' FROM 'HelloWorld') | elloWorld |
| 字符替换 | REPLACE('abcd','b','m') | amcd |
2.数字函数
| 作用 | 函数 | 结果 |
|---|---|---|
| 四舍五入 | ROUND(45.926, 2) | 45.93 |
| 截断 | TRUNC(45.926, 2) | 45.92 |
| 求余 | MOD(1600, 300) | 100 |
3.日期函数
| 作用 | 函数 | 结果 |
|---|---|---|
| 获取当前日期 | now() | |
| 将日期格式的字符转换成指定格式的日期 | STR_TO_DATE('9-13-1999','%m-%d-%Y') | 1999-09-13 |
| 将日期转换成字符 | DATE_FORMAT(‘2018/6/6’,‘%Y年%m月%d日’) | 2018年06月06日 |
常见函数案例
进阶4：常见函数
/*
概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1、隐藏了实现细节 2、提高代码的重用性
调用：select 函数名(实参列表) 【from 表】;
特点：
①叫什么（函数名）
②干什么（函数功能）分类：
1、单行函数
如 concat、length、ifnull等
2、分组函数功能：做统计使用，又称为统计函数、聚合函数、组函数
常见函数：
一、单行函数
字符函数：
length:获取字节个数(utf-8一个汉字代表3个字节,gbk为2个字节)
concat
substr
instr
trim
upper
lower
lpad
rpad
replace数学函数：
round
ceil
floor
truncate
mod日期函数：
now
curdate
curtime
year
month
monthname
day
hour
minute
second
str_to_date
date_format
其他函数：
version
database
user
控制函数
if
case
*/
一、字符函数
1.length 获取参数值的字节个数
SELECT LENGTH('john');
SELECT LENGTH('张三丰hahaha');SHOW VARIABLES LIKE '%char%'
2.concat 拼接字符串
SELECT CONCAT(last_name,'_',first_name) 姓名 t_mysql_FROM employees;
3.upper、lower
SELECT UPPER('john');
SELECT LOWER('joHn');
示例：将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM t_mysql_employees;4.substr、substring
注意：索引从1开始
截取从指定索引处后面所有字符
SELECT SUBSTR('李莫愁爱上了陆展元',7) out_put;截取从指定索引处指定字符长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put;
案例：姓名中首字符大写，其他字符小写然后用_拼接，显示出来SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) out_put
FROM t_mysql_employees;5.instr 返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR('杨不殷六侠悔爱上了殷六侠','殷八侠') AS out_put;
6.trim
SELECT LENGTH(TRIM(' 张翠山 ')) AS out_put;
SELECT TRIM('aa' FROM 'aaaaaaaaa张aaaaaaaaaaaa翠山aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa') AS out_put;
7.lpad 用指定的字符实现左填充指定长度
SELECT LPAD('殷素素',2,'*') AS out_put;
8.rpad 用指定的字符实现右填充指定长度
SELECT RPAD('殷素素',12,'ab') AS out_put;
9.replace 替换SELECT REPLACE('周芷若周芷若周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put;
二、数学函数
round 四舍五入
SELECT ROUND(-1.55);
SELECT ROUND(1.567,2);
ceil 向上取整,返回>=该参数的最小整数SELECT CEIL(-1.02);
floor 向下取整，返回<=该参数的最大整数
SELECT FLOOR(-9.99);truncate 截断
SELECT TRUNCATE(1.69999,1);
mod取余
/*
mod(a,b) ： a-a/b*bmod(-10,-3):-10- (-10)/(-3)*（-3）=-1
*/
SELECT MOD(10,-3);
SELECT 10%3;
三、日期函数
now 返回当前系统日期+时间
SELECT NOW();curdate 返回当前系统日期，不包含时间
SELECT CURDATE();curtime 返回当前时间，不包含日期
SELECT CURTIME();
可以获取指定的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年;
SELECT YEAR('1998-1-1') 年;SELECT YEAR(hiredate) 年 FROM t_mysql_employees;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;
str_to_date 将字符通过指定的格式转换成日期SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;
#查询入职日期为1992--4-3的员工信息
SELECT * FROM employees WHERE hiredate = '1992-4-3';SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');
date_format 将日期转换成字符SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;
查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期
FROM t_mysql_employees
WHERE commission_pct IS NOT NULL;按月份分组
SELECT
DATE_FORMAT( b.releaseDate, '%Y年%m月' ) releaseStr,
count( 1 )
FROM
t_p1_blog b
GROUP BY
releaseStr
ORDER BY
releaseStr DESC;
博客类别分组
SELECT
bt.typeName,
count( b.btid ) num
FROM
t_p1_blog b,
t_p1_blogtype bt
WHERE
b.btid = bt.btid
GROUP BY
b.btid
ORDER BY
num DESC;
四、其他函数
SELECT VERSION();
SELECT DATABASE();
SELECT USER();
五、流程控制函数
1.if函数： if else 的效果
SELECT IF(10<5,'大','小');
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，呵呵','有奖金，嘻嘻') 备注
FROM t_mysql_employees;
2.case函数的使用一： switch case 的效果/*
java中
switch(变量或表达式){
case 常量1：语句1;break;
...
default:语句n;break;
}mysql中
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n;
end
*//*案例：查询员工的工资，要求
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资*/
SELECT salary 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM t_mysql_employees;3.case 函数的使用二：类似于 多重if
/*
java中：
if(条件1){
语句1；
}else if(条件2){
语句2；
}
...
else{
语句n;
}mysql中：
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
。。。
else 要显示的值n或语句n
end
*/案例：查询员工的工资的情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000，显示C级别
否则，显示D级别
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM t_mysql_employees;);
子查询练习
1. 查询工资最低的员工信息: last_name, salary
①查询最低的工资
SELECT MIN(salary)
FROM t_mysql_employees②查询last_name,salary，要求salary=①
SELECT last_name,salary
FROM t_mysql_employees
WHERE salary=(
SELECT MIN(salary)
FROM t_mysql_employees
);2. 查询平均工资最低的部门信息
方式一：
①各部门的平均工资
SELECT AVG(salary),department_id
FROM t_mysql_employees
GROUP BY department_id
②查询①结果上的最低平均工资
SELECT MIN(ag)
FROM (
SELECT AVG(salary) ag,department_id
FROM t_mysql_employees
GROUP BY department_id
) ag_dep③查询哪个部门的平均工资=②
SELECT AVG(salary),department_id
FROM t_mysql_employees
GROUP BY department_id
HAVING AVG(salary)=(
SELECT MIN(ag)
FROM (
SELECT AVG(salary) ag,department_id
FROM t_mysql_employees
GROUP BY department_id
) ag_dep);
④查询部门信息
SELECT d.*
FROM t_mysql_departments d
WHERE d.`department_id`=(
SELECT department_id
FROM t_mysql_employees
GROUP BY department_id
HAVING AVG(salary)=(
SELECT MIN(ag)
FROM (
SELECT AVG(salary) ag,department_id
FROM t_mysql_employees
GROUP BY department_id
) ag_dep)
);
方式二：
①各部门的平均工资
SELECT AVG(salary),department_id
FROM t_mysql_employees
GROUP BY department_id②求出最低平均工资的部门编号
SELECT department_id
FROM t_mysql_employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1;③查询部门信息
SELECT *
FROM t_mysql_departments
WHERE department_id=(
SELECT department_id
FROM t_mysql_employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1
);
3. 查询平均工资最低的部门信息和该部门的平均工资
①各部门的平均工资
SELECT AVG(salary),department_id
FROM t_mysql_employees
GROUP BY department_id
②求出最低平均工资的部门编号
SELECT AVG(salary),department_id
FROM t_mysql_employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1;
③查询部门信息
SELECT d.*,ag
FROM t_mysql_departments d
JOIN (
SELECT AVG(salary) ag,department_id
FROM t_mysql_employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1) ag_dep
ON d.`department_id`=ag_dep.department_id;4. 查询平均工资最高的 job 信息
①查询最高的job的平均工资
SELECT AVG(salary),job_id
FROM t_mysql_employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1②查询job信息
SELECT *
FROM t_mysql_jobs
WHERE job_id=(
SELECT job_id
FROM t_mysql_employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1);
5. 查询平均工资高于公司平均工资的部门有哪些?①查询平均工资
SELECT AVG(salary)
FROM t_mysql_employees②查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM t_mysql_employees
GROUP BY department_id③筛选②结果集，满足平均工资>①
SELECT AVG(salary),department_id
FROM t_mysql_employees
GROUP BY department_id
HAVING AVG(salary)>(
SELECT AVG(salary)
FROM t_mysql_employees);
6. 查询出公司中所有 manager 的详细信息.
①查询所有manager的员工编号
SELECT DISTINCT manager_id
FROM t_mysql_employees②查询详细信息，满足employee_id=①
SELECT *
FROM t_mysql_employees
WHERE employee_id =ANY(
SELECT DISTINCT manager_id
FROM t_mysql_employees
);7. 各个部门中 最高工资中最低的那个部门的 最低工资是多少
①查询各部门的最高工资中最低的部门编号
SELECT department_id
FROM t_mysql_employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1
②查询①结果的那个部门的最低工资
SELECT MIN(salary) ,department_id
FROM t_mysql_employees
WHERE department_id=(
SELECT department_id
FROM t_mysql_employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1
);8. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
①查询平均工资最高的部门编号
SELECT
department_id
FROM
t_mysql_employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1②将employees和departments连接查询，筛选条件是①
SELECT
last_name, d.department_id, email, salary
FROM
t_mysql_employees e
INNER JOIN t_mysql_departments d
ON d.manager_id = e.employee_id
WHERE d.department_id =
(SELECT
department_id
FROM
t_mysql_employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1) ;
综合查询案例
一、查询每个专业的学生人数
SELECT majorid,COUNT(*)
FROM student
GROUP BY majorid;二、查询参加考试的学生中，每个学生的平均分、最高分
SELECT AVG(score),MAX(score),studentno
FROM result
GROUP BY studentno;三、查询姓张的每个学生的最低分大于60的学号、姓名
SELECT s.studentno,s.`studentname`,MIN(score)
FROM student s
JOIN result r
ON s.`studentno`=r.`studentno`
WHERE s.`studentname` LIKE '张%'
GROUP BY s.`studentno`
HAVING MIN(score)>60;
四、查询每个专业生日在“1988-1-1”后的学生姓名、专业名称SELECT m.`majorname`,s.`studentname`
FROM student s
JOIN major m
ON m.`majorid`=s.`majorid`
WHERE DATEDIFF(borndate,'1988-1-1')>0
GROUP BY m.`majorid`;
五、查询每个专业的男生人数和女生人数分别是多少SELECT COUNT(*),sex,majorid
FROM student
GROUP BY sex,majorid;
六、查询专业和张翠山一样的学生的最低分
①查询张翠山的专业编号
SELECT majorid
FROM student
WHERE studentname = '张翠山'②查询编号=①的所有学生编号
SELECT studentno
FROM student
WHERE majorid=(
SELECT majorid
FROM student
WHERE studentname = '张翠山')
②查询最低分
SELECT MIN(score)
FROM result
WHERE studentno IN(SELECT studentno
FROM student
WHERE majorid=(
SELECT majorid
FROM student
WHERE studentname = '张翠山')
)七、查询大于60分的学生的姓名、密码、专业名
SELECT studentname,loginpwd,majorname
FROM student s
JOIN major m ON s.majorid= m.majorid
JOIN result r ON s.studentno=r.studentno
WHERE r.score>60;
八、按邮箱位数分组，查询每组的学生个数
SELECT COUNT(*),LENGTH(email)
FROM student
GROUP BY LENGTH(email);
九、查询学生名、专业名、分数SELECT studentname,score,majorname
FROM student s
JOIN major m ON s.majorid= m.majorid
LEFT JOIN result r ON s.studentno=r.studentno
十、查询哪个专业没有学生，分别用左连接和右连接实现
左
SELECT m.`majorid`,m.`majorname`,s.`studentno`
FROM major m
LEFT JOIN student s ON m.`majorid` = s.`majorid`
WHERE s.`studentno` IS NULL;右
SELECT m.`majorid`,m.`majorname`,s.`studentno`
FROM student s
RIGHT JOIN major m ON m.`majorid` = s.`majorid`
WHERE s.`studentno` IS NULL;
十一、查询没有成绩的学生人数SELECT COUNT(*)
FROM student s
LEFT JOIN result r ON s.`studentno` = r.`studentno`
WHERE r.`id` IS NULL
分页查询
分页查询案例
/*
应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
语法：
select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit 【offset,】size;offset要显示条目的起始索引（起始索引从0开始）
size 要显示的条目个数
特点：
①limit语句放在查询语句的最后
②公式
要显示的页数 page，每页的条目数sizeselect 查询列表
from 表
limit (page-1)*size,size;size=10
page
10
2 10
320*/
案例1：查询前五条员工信息
SELECT * FROM t_mysql_employees LIMIT 0,5;
SELECT * FROM t_mysql_employees LIMIT 5;
案例2：查询第11条——第25条
SELECT * FROM t_mysql_employees LIMIT 10,15;
案例3：有奖金的员工信息，并且工资较高的前10名显示出来
SELECT
*
FROM
t_mysql_employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10 ;
六.mysql之视图
含义：虚拟表，和普通表一样使用
mysql5.1版本出现的新特性，是通过表动态生成的数据
比如：舞蹈班和普通班级的对比
创建语法的关键字是否实际占用物理空间使用
视图create view只是保存了sql逻辑增删改查，只是一般不能增删改
表create table保存了数据增删改查
案例：查询姓张的学生名和专业名
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.`majorid`= m.`id`
WHERE s.`stuname` LIKE '张%';
CREATE VIEW v1
AS
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.`majorid`= m.`id`;
SELECT * FROM v1 WHERE stuname LIKE '张%';
一、创建视图
/*
语法：
create view 视图名
as
查询语句;
*/
USE myemployees;
1.查询姓名中包含a字符的员工名、部门名和工种信息
①创建
CREATE VIEW myv1
AS
SELECT last_name,department_name,job_title
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON j.job_id = e.job_id;
②使用
SELECT * FROM myv1 WHERE last_name LIKE '%a%';
2.查询各部门的平均工资级别
①创建视图查看每个部门的平均工资
CREATE VIEW myv2
AS
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id;
②使用
SELECT myv2.`ag`,g.grade_level
FROM myv2
JOIN job_grades g
ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
3.查询平均工资最低的部门信息
SELECT * FROM myv2 ORDER BY ag LIMIT 1;
4.查询平均工资最低的部门名和工资
CREATE VIEW myv3
AS
SELECT * FROM myv2 ORDER BY ag LIMIT 1;
SELECT d.*,m.ag
FROM myv3 m
JOIN departments d
ON m.`department_id`=d.`department_id`;
二、视图的修改
方式一：
/*
create or replace view 视图名
as
查询语句;
*/
SELECT * FROM myv3
CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;
方式二：
/*
语法：
alter view 视图名
as
查询语句;
*/
ALTER VIEW myv3
AS
SELECT * FROM employees;
三、删除视图
/*
语法：drop view 视图名,视图名,...;
*/
DROP VIEW emp_v1,emp_v2,myv3;
四、查看视图
DESC myv3;
SHOW CREATE VIEW myv3;
五、视图的更新
REATE OR REPLACE VIEW myv1
AS
SELECT last_name,email,salary*12*(1+IFNULL(commission_pct,0)) "annual salary"
FROM employees;
CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email
FROM employees;
SELECT * FROM myv1;
SELECT * FROM employees;
1.插入
INSERT INTO myv1 VALUES('张飞','zf@qq.com');
2.修改
UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';
3.删除
DELETE FROM myv1 WHERE last_name = '张无忌';
具备以下特点的视图不允许更新
①包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
CREATE OR REPLACE VIEW myv1
AS
SELECT MAX(salary) m,department_id
FROM employees
GROUP BY department_id;
SELECT * FROM myv1;
更新
UPDATE myv1 SET m=9000 WHERE department_id=10;
②常量视图
CREATE OR REPLACE VIEW myv2
AS
SELECT 'john' NAME;
SELECT * FROM myv2;
更新
UPDATE myv2 SET NAME='lucy';
③Select中包含子查询
CREATE OR REPLACE VIEW myv3
AS
SELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资
FROM departments;
更新
SELECT * FROM myv3;
UPDATE myv3 SET 最高工资=100000;
④join
CREATE OR REPLACE VIEW myv4
AS
SELECT last_name,department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;
更新
SELECT * FROM myv4;
UPDATE myv4 SET last_name = '张飞' WHERE last_name='Whalen';
INSERT INTO myv4 VALUES('陈真','xxxx');
⑤from一个不能更新的视图
CREATE OR REPLACE VIEW myv5
AS
SELECT * FROM myv3;
更新
SELECT * FROM myv5;
UPDATE myv5 SET 最高工资=10000 WHERE department_id=60;
⑥where子句的子查询引用了from子句中的表
CREATE OR REPLACE VIEW myv6
AS
SELECT last_name,email,salary
FROM employees
WHERE employee_id IN(
SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL
);
更新
SELECT * FROM myv6;
UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';
视图练习一、创建视图emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资、邮箱
CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name,salary,email
FROM employees
WHERE phone_number LIKE '011%';
二、创建视图emp_v2，要求查询部门的最高工资高于12000的部门信息
CREATE OR REPLACE VIEW emp_v2
AS
SELECT MAX(salary) mx_dep,department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary)>12000;
SELECT d.*,m.mx_dep
FROM departments d
JOIN emp_v2 m
ON m.department_id = d.`department_id`;
138
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
