MySQL笔记

登陆本地MySQL：mysql -u root -p密码
数据库的相关概念：
1.DB:数据库，保存一组有组织的数据的容器
2.DBMS:数据库管理系统，又称为数据库软件（产品），用于管理DB中的数据
3.SQL：结构化查询语言，用于和DBMS通信的语言

数据库特点：
1.将数据放到表中，表再放到库中
2.一个数据库中可以有多个表，每个表都有一个名字，用来标识自己，表名具有唯一性
3.表具有一些特性，这些特性定义了数据在表中如何存储，类似java中类的设计
4.表由列组成，我们也称为字段。所有表都是由一个或者多个列组成，每一列类似java中的属性
5.表中的数据是按行存储的，每一行类似java中的对象

MySQL下载：https://www.mysql.com

创建数据库：CREATE DATABASE 数据库名称 [库选项];
CREATE DATABASE表示创建数据库；数据库名称可以是字母、数字和下划线组成的任意字符串；库选项用于设置此数据库的相关特性，如字符集CHARSET,校对集COLLATE.
例：CREATE DATABASE mydb;
如果创建的数据库已经存在，则程序会报错，在创建数据库时可以在数据库名称前添加 IF NOT EXISTS;
例：CREATE DATABASE IF NOT EXISTS mydb;

查看MySQL中已经存在的数据库：SHOW DATABASES;
在完成创建数据库后，若要查看该数据库中的信息：SHOW CREATE DATABASE 数据库名称;
选择数据库：USE 数据库名称;
删除数据库：DROP DATABASE 数据库名称;
在使用DROP DATABASE删除数据库时，若待删除数据库不存在，MySQL服务器会报错，可以使用DROP DATABASE IF EXISTS 数据库名称; 来删除数据库

在MySQL数据库中，所有的数据都储存在数据表中，若要对数据执行增删改查等操作，首先需要在指定的数据库中准备一张表
创建数据表：CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 表名(
字段名 字段类型 [字段属性]…) [表选项]
可选项TEMPORARY 表示临时表，仅在当前会话中可见，并且在会话关闭时自动关闭。字段名指的是数据表的列名；字段类型设置字段中保存的数据类型，如时间日期类型等；
可选项“字段属性”指的是指的是字段的某些特殊约束条件。可选的“表选项”用于设置表的相关属性，如储存引擎(ENGINE)、字符集(CHARSET)和校对集(COLLATE)
在操作数据表前需要先用 USE 数据库 选择数据库

例：#创建mydb数据库
CREATE DATABASE mydb;
#选择数据库
USE mydb;
#创建goods数据表
CREATE TABLE goods(
id INT COMMENT ‘编号’,
name VARCHAR(32) COMMENT ‘商品名’,
price INT COMMENT ‘价格’,
description VARCHAR(255) COMMENT ‘商品描述’
);
INT用于设置字段数据类型是整型；VARCHAR(L)表示可变长度的字符串，L表示字符串数，COMMENT用于在创建表时添加注释内容，并将其保存到表结构中。

查看数据表：SHOW TABLES [LIKE匹配模式];
若不添加可选项LIKE匹配模式，表示查看当前数据库中的所有数据表；匹配模式符有两种，分别为 % 和 _ 。
前者表示匹配一个或多个字符，代表任意长度的字符串，长度也可以为0，后者仅可以匹配一个字符串。

查看数据库的相关信息（名称、创建时间、存储引擎）：SHOW TABLE STATUS [FROM 数据库名] [LIKE匹配模式];

修改数据表名称：
1:ALTER TABLE 旧表名 RENAME [TO|AS] 新表名;
2:RENAME TABLE 旧表名1 TO 新表名1[,旧表名2 TO 新表名2]…

修改表选项：数据表中的表选项字符集、储存引擎以及校对集也可以通过ALTER TABLE 修改：ALTER TABLE 表选项 [=] 值；
例：ALTER TABLE my_goods CHARSET = utf8;修改数据表的字符集

查看数据表的字段信息：MySQL提供的DESCRIBE语句可以查看数据表中所有字段或指定字段信息，包括字段名、字段类型等。
查看所有字段的信息:{ DESCRIBE | DESC } 数据表名;
查看指定字段的信息:{ DESCRIBE | DESC } 数据表名 字段名;

查看创建数据表的具体SQL语句以及表的字符编码，可以使用:SHOW CREATE TABLE 表名;

查看数据表结构：
1:SHOW [FULL] COLUMNS FROM 数据表名 [FROM 数据库名];
2:SHOW [FULL] COLUMNS FROM 数据库名.数据表名;
SHOW FULL COLUMN语句除与 DESC 语句查询出的相同字段外，还包括Collation (校对集)字段、Privileges(权限)字段和Comment(注释)字段。

修改字段名：ALTER TABLE 数据表名 CHANGE [COLUMN] 旧字段名 新字段名 字段类型 [字段属性];

修改字段的位置：
数据表在创建时，字段编写的先后顺序就是其在数据库中储存的顺序，若需要调整某个字段的位置，也可以使用MODIFY实现
ALTER TABLE 数据表名 MODIFY [COLUMN] 字段名1 数据类型 [字段属性] [ FIRST | AFTER 字段名2]
修改字段的位置就是在修改字段类型的后面添加 FIRST 或 AFTER 字段名2。前者表示将‘字段名1’调整为数据表的第1个字段，后者表示将‘字段名1’插入到‘字段名2’的后面

新增字段：
新增一个字段，并可以指定其位置：ALTER TABLE 数据表名 ADD [COLUMN] 新字段名 字段类型 [ FIRST | AFTER 字段名]
同时新增多个字段：ALTER TABLE 数据表名 ADD [COLUMN] (新字段名1 字段类型1，新字段名2 字段类型2，…)
在不指定位置的情况下，新增的字段默认添加到表的最后。

删除字段：ALTER TABLE 数据表名 DROP [COLUMN] 字段名；

删除数据表：DROP [TEMPORARY] TABLE [IF EXISTS] 数据表1[,数据表2]…;
删除数据表操作指的是删除数据库中已经存在的表

为数据表中的所有字段添加数据：INSERT [INTO] 数据表名 {VALUES | VALUE}(值1,[,值2]…);
例：mysql>INSERT INTO goods
->VALUES(1,‘notebook’,4998,‘High cost performance’);

通常需要在创建数据库时添加表选项，设置数据表的字符集,例：
CREATE TABLE goods(
字段…
)DEFAULT CHARSET utf8;
对于已经添加数据的数据表，则可以通过ALTER TABLE … CHANGE/MODIFY完成对表字段字符集的设置，例：
mysql>ALTER TABLE goods
->MODIFY name VARCHAR(32) CHARACTER SET utf8,
->MODIFY description VARCHAR(255) CHARACTER SET utf8;

除了为数据表中所有字段添加数据外，还能通过指定字段名的方式增加数据。指定的字段名可以是数据表中全部的字段，也可以是部分的字段
INSERT [INTO] 数据表名 (字段名1[,字段名2]…)
{VALUE | VALUE}(值1[，值2])；
例：将编号为3的Mobile phone 插入到goods数据表中
mysql>INERT INTO goods (id,name) VALUE (3,‘Mobile phone’);

MySQL中还提供了另外一种使用INSERT语句为指定字段添加数据的方式：
INSERT [INTO] 数据表名
SET 字段名1 = 值1[,字段名2=值2]…
例：INSERT INTO goods SET id=3,name=‘Mobile phone’;

一次添加多行数据：
INSERT [INTO] 数据表名 [(字段列表)]
{VALUES|VALUE}(值列表)[,(值列表)]…;
多个值列表之间使用逗号分隔，其中，字段列表在省略时，插入的数据需严格按照数据表创建的顺序插入，否则‘值列表’插入的数据仅需与字段列表中的字段对应即可
mysql>INSERT INTO goods VALUES
->(1,‘notebook’,4998,‘High cost performance’),
->(2,‘笔记本’,9998,‘续航时间超过10小时’),
->(3,‘Mobile phone’,NULL,NULL);

查询表中全部数据：SELECT * FROM 数据表名;
指定字段查询数据：SELECT {字段名1,字段名2,字段名3,…} FROM 数据表名;
查询数据时，若想要查询出符合条件的相关数据记录时，可以使用WHERE实现
SELECT * |{字段名1,字段名2,字段名3,…} FROM 数据表名 WHERE 字段名 =值;

修改数据：
UPDATE 数据表名
SET 字段名1=值1[,字段名2=值2，…]
[where 条件表达式]
例：将goods中编号为2的商品价格改为5899元
mysql>UPDATE goods SET price=5899 WHERE id=2;

删除数据：DELETE FROM 数据表名 [WHERE 条件表达式];
例：DELETE FROM goods WHERE id=3;

例：电子杂志订阅表的操作：
1.选择数据库
mysql>CREATE DATABASE IF NOT EXISTS mydb;
mysql>use mydb;
2.创建电子杂志订阅表
mysql>CREATE TABLE subscribe(
->id INT COMMENT ‘编号’,
->email VARCHAR(60) COMMENT ‘邮件订阅的邮箱地址’,
->status INT COMMENT ‘是否确认，0未确认，1已确认’,
->code VARCHAR(10) COMMENT ‘邮箱确认的验证码’
->)DEFAULT CHARSET=utf8;
3.操作电子杂志订阅表的数据
(1):添加数据
mysql>INSERT INTO subscribe VALUES
->(1,‘tom123@163.com’,1,‘TRBXPO’),
->(2,‘lucy123@163.com’,1,‘LOICPE’),
->(3,‘lily123@163.com’,0,‘JIXDAMI’),
->(4,‘jimmy123@163.com’,0,‘QKOLPH’),
->(5,‘joy123@163.com’,1,‘JSMWNL’);
(2):查询数据
mysql>SELECT * FROM subscribe WHERE statue=1;
(3):修改数据
mysql>UPDATE subscribe SET status=1 WHERE id=4;
(4):删除数据
mysql>DELETE FROM subscribe WHERE id=5;

数据类型：MySQL数据库提供了多种数据类型，其中包括数字类型、时间和日期类型、字符串类型。

数字类型：
1.整数类型
MySQL中根据取值范围的不同，整数类型可以分为5种，分别是TINYINT SMALLINT MEDIUMINT BIGINT
为字段设置零填充（ZEROFILL）时，若数值宽度小于显示宽度，会在左侧填充0,。
例：
mysql>CREATE TABLE my int2(
->int_1 INT(3) ZEROFILL,
->int_2 INT(6) ZEROFILL
);

浮点数类型：
在MySQL中，储存的小数都是使用浮点数或定点数来表示的。浮点数的类型有两种，分别是单精度浮点数（FLOAT）和双精度浮点数（DOUBLE），当浮点数使用UNSIGNED修饰为无符号时，取值范围将不包含负数。

时间和日期类型：
为了方便在数据库中储存日期和时间，MySQL提供了表示日期和时间的数据类型，分别是YEAR DATE TIME TIMESTAMP

1.YEAR类型用于表示年份
CREATE TABLE my_year(y YEAR); #设置y字段的数据类型为YEAR
INSERT INTO my_year VALUES(2020); #插入年份数据,2020

2.DATE类型
DATE类型用于表示日期值，不包含时间部分
CREATE TABLE my_date (d DATE); #设置d字段的数据类型为DATE
INSERT INTO my_date VALUES(‘2020-01-21’); #插入日期数据
INSERT INTO my_date VALUES(CURRENT_DATE); #插入当前系统日期
INSERT INTO my_date VALUES(NOW()); #插入当前系统日期

3.TIME类型
TIME类型用于表示时间值，它的显示形式一般为HH:MM:SS HH表示小时 MM表示分 SS表示秒

4.DATETIME类型
DATETIME用于类型用于表示日期和时间，它的显示形式为’YYYY-MM-DD HH:MM:SS’ YYYY表示年，MM表示月，DD表示日，HH表示小时，MM表示分，SS表示秒
使用NOW()来输入当前系统的日期和时间。

5.TIMESTAMP类型：
TIMESTAMP(时间戳)类型用于表示日期和时间，它的显示形式与DATETIME相同，但取值范围比DATETIME小

(1):NOT NULL表示非空约束，该字段将不允许保存NULL值
(2):DEFAULT表示默认约束，当字段无任何输入时，自动设置某个值作为默认值。此处设为CURRENT_TIMESTAMP表示使用系统当前日期和时间作为默认值

字符串类型：
CHAR 固定长度字符串
VARCHAR 可变长度字符串
TEXT 大文本数据
ENUM 枚举类型
SET 字符串对象
BINARY 固定长度的二进制数据
VARBINARY 可变长度的二进制数据
BLOB 二进制大对象

ENUM类型：
ENUM类型又称为枚举类型，定义ENUM类型的方式：ENUM(‘值1’,‘值2’,‘值3’,…)
(‘值1’,‘值2’,‘值3’,…)称为枚举列表，ENUM类型的数据只能从枚举列表中取，并且只能取一个
例：
#创建表
mysql>CREATE TABLE my_enum (gender ENUM(‘male’,‘female’));
#插入两条测试记录：
mysql>INSERT INTO my_enum VALUES(‘male’),(‘female’);
#查询记录，查询结果为"female"
mysql>SELECT * FROM my_enum WHERE gender=‘female’;
#插入枚举列表中没有的测试值 结果会报错
mysql>INSERT INTO my_enum VALUES(‘m’);

SET类型：
SET类型用于保存字符串对象，其定义格式与ENUM类型类似
SET(‘值1’,‘值2’,‘值3’,…)
SET类型与ENUM的区别在于，它可以从列表中选择一个或多个值来保存，多个值之间用逗号分隔
例：
#创建表
mysql>CREATE TABLE my_set (hobby SET(‘book’,‘game’,‘code’));
#插入3条测试记录
mysql>INSERT INTO my_set VALUES(’’),(‘book’),(‘book,code’);
#查询记录，查询结果为’book,code’
mysql>SELECT * FROM my_set WHERE hobby=‘book,code’;

ENUM类型类似于单选框，SET类型类似于复选框
ENUM和SET类型列表中的值都可以使用中文，但必须设置支持中文的字符集，例如CREATE TABLE my_enum(gender ENUM(‘男’，‘女’)) CHARSET=GBK;

BINARY和VARBINARY类型:
BINARY类型的长度是固定的，如果数据的长度不足最大长度，将在数据的后面用"\0"补齐，最终达到指定长度.

BLOB类型：
BLOB类型用于保存数据量很大的二进制数据，如图片、PDF文档
BLOB类型与TEXT类型很相似，但BLOB类型数据是根据二进制编码进行比较和排序，而TEXT类型数据是根据文本模式进行比较和排序

JSON数据类型：
#JSON数组：
[“abc”,10,null,true,false]
#JSON对象
{“k1”:“value”,“k2”:10}
JSON数组中保存的数据可以是任意类型。JSON数组使用[]实现，多个值之间使用逗号分隔，JSON对象使用{}实现，保存的数据是一组键值对.
例：
#创建表
mysql>CREATE TABLE my_json(j1 JSON,J2 JSON);
mysql>INSERT INTO my_json VALUES
->(’{“k1”:“value”,“k2”:10}’,’[“run”,“sing”]’);
#查询记录
mysql>SELECT * FROM my_json;

表的约束：
默认约束：默认约束用于为数据表中的字段指定默认值，即当在表中插入一条新记录时，如果没有给这个字段赋值，数据库系统会自动为这个字段插入默认值。
定义默认值：字段名 数据类型 DEFAULT 默认值;
BLOB、TEXT数据类型不支持默认约束
例：
mysql>CREATE TABLEB my_default(
->name VARCHAR(10),
->age INT UNSIGNED DEFAULT 18
);

删除默认约束：
mysql>ALTER TABLE my_default MODIFY age INT UNSIGEND;
添加默认约束：
mysql>ALTER TABLE my_default MODIFY age INT UNSIGNED DEFAULT 18;

非空约束：
非空约束指的是字段的值不能为NULL,在MySQL中，非空约束是通过NOT NULL定义的
字段名 数据类型 NOT NULL;

唯一约束：
唯一约束用于保证数据表中字段的唯一性，即表中字段的值不能重复出现。唯一约束是通过UNIQUE定义的。
#列级约束
字段名 数据类型 UNIQUE;
#表级约束
UNIQUE(字段名1，字段名2，…);
例：
（1）：创建表，分别通过列级约束和表级约束的方式增加唯一性约束
#列级约束
mysql>CREATE TABLE my_unique_1(
->id INT UNSIGNED UNIQUE,
->usernmae VARCHAR(10) UNIQUE
);
#表级约束
mysql>CREATE TABLE my_unique_2(
->id INT UNSIGNED,
->username VARCHAR(10),
->UNIQUE(id),
->UNIQUE(username)
->);
（2）：为含唯一约束的字段插入记录
mysql>INSERT INTO my_unique_1 (id) VALUES(1);
mysql>INSERT INTO my_unique_1 (id) VALUES(2);
#插入重复记录，插入失败
mysql>INSERT INTO my_unique_1 (id) VALUES(1);

(3):添加和删除唯一性约束
#创建测试表
mysql>CREATE TABLE my_unique_3 (id INT);
#添加唯一性约束
mysql>ALTER TABLE my_unique_3 ADD UNIQUE(id);
#删除唯一性约束
mysql>ALTER TABLE my_unique_3 DROP INDEX id;

主键约束：
在MySQL中，为了快速查找表中的信息，可以设置主键来实现。
#列级约束
字段名 数据类型 PRIMARY KEY
#表级约束
PRIMARY KEY(字段名1,字段名2,…)
例：
（1）创建my_primary表，为id字段添加主键约束
mysql>CREATE TABLE my_primary(
->id INT UNSIGNED PRIMARY KEY,
->username VARCHAR(20)
->)；

添加主键约束后，插入重复值或NULL值会失败
（2）为一个表添加或删除主键约束
#删除主键约束
mysql>ALTER TABLE my_primary DROP PRIMARY KEY;
#删除id字段的非空约束
mysql>ALTER TABLE my_primary MODIFY id INT UNSIGNED;
#添加主键约束
mysql>ALTER TABLE my_primary ADD PRIMARY KEY (id);

自动增长：
在为数据表设置主键约束后，每次插入记录时，都需要检查主键的值，防止插入的值重复导致插入失败，这回给数据库的使用带来很多麻烦，
为此可以利用MySQL提供的自动增长功能来自动生成主键的值
基本语法：字段名 数据类型 AUTO_INCREMENT
注意点：
1.一个表只能有一个自动增长字段，该字段的数据类型是整数类型，且必须定义主键，如UNIQUE KEY , PRIMARY KEY
2.若为自动增长字段插入NULL、0、DEFAULT或在插入时省略该字段，则该字段就会使用自动增长值；若插入的值是一个具体的值，则不会使用自动增长值
3.自动增长值从1开始自增，每次加1.若插入的值大于自动增长的值，则下次插入的自动增长值会自动使用最大值加1；若插入的值小于自动增长值，则不会对自动增长值产生影响
4.使用DELETE删除记录时，自动增长值不会减少或填补空缺
例：
（1）：创建my_auto表，为id字段添加自动增长
mysql>CREATE TABLE my_auto(
->id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
->username VARCHAR(20)
->);
(2):插入记录进行测试
#插入时省略id字段,将会自动使用自动增长值
mysql>INSERT INTO my_auto (username) VALUES(‘a’);
#为id字段插入NULL,将会使用自动增长值
mysql>INSERT INTO my_auto VALUES(NULL,‘b’);
#为id字段插入具体值6
mysql>INSERT INTO my_auto VALUES(6,‘c’);
#为id字段插入0，使用自动增长值
mysql>INSERT INTO my_auto VALUES(0,‘d’);

(3):为现有的表修改或删除自动增长
#修改自动增长值
mysql>ALTER TABLE my_auto AUTO_INCREMENT=10;
#删除自动增长
mysql>ALTER TABLE my_auto MODIFY id INT UNSIGNED;
#重新为id添加自动增长
mysql>ALTER TABLE my_auto MODIFY id INT UNSIGNED AUTO_INCREMENT;

例：设计用户表
1.创建用户表
mysql>CREATE TABLE mydb.user(
->id INT USIGNED PRIMARY KEY AUTO_INCREMENT COMMENT ‘用户id’,
->username VARCHAR(20) UNIQUE NOT NULL COMMENT ‘用户名’,
->mobile CHAR(11) NOT NULL COMMENT ‘手机号码’,
->gender ENUM(‘男’,‘女’,‘保密’) NOT NULL COMMENT ‘性别’,
->reg_time TIMESTAMP DEFAULE CURRENT_TIMESTAMP COMMENT ‘注册时间’,
->level TINYINT UNSIGNED NOT NULL COMMENT ‘会员等级’
->)DEFAULT CHARSET=utf8;
2.添加测试记录
mysql>INSERT INTO mydb.user VALUES(
->NULL,‘小明’,
->‘12311111111’,‘男’,
->‘2018-01-01 11:11:11’,1
->);

查询基础：
语法：select 查询列表 from 表名
查询列表可以是：表中的字段、常量、表达式、函数
查询的结果是一个虚拟的表格

例：
查询表中的单个字段：
select last_name from employees;
查询表中的多个字段：
select last_name,salary from employees;
查询表中的所有字段：
select * from employees;
查询表达式：
select 100*98;
查询函数：
select version();

变量，在这个符号当中的变量为非关键字
查询常量值不需要加from

起别名：
方式一：
select 100%98 as 结果;
select last_name as 姓,first_name as 名 from employees;

方式二：
select last_name 姓,first_name 名 from employees;

案例：查询salary,显示结果为out put
select salary as “out put” from employees;
此时的out是关键字，空格是特殊字符，需要使用双引号括起来

去重(distinct)：
案例：查询员工表中涉及到的所有的部门编号
select distinct department_id from employees;

+号的作用 ：
java中的+号：
1.运算符，两个操作数都为数值型
2.连接符，只要有一个操作数为字符串

mysql中的+号：仅仅有一个功能:运算符

select 100+98;两个操作数都为数值型，则做加法运算
select ‘123’+90;其中一方为字符型，试图将字符型数值转换成数值型，如果转化成功，则继续做加法运算，如果转换失败，则将字符型数值转换成0
select null+10;只要有一方结果为null，则结果肯定为null

连接符号（concat）：
select concat(‘a’,‘b’,‘c’) as 结果;
案例：查询员工名和姓连接成一个字段，并显示为 姓名
select concat(last_name,first_name) as 姓名 from 表;

条件查询：
select 查询列表 from 表名 where 筛选条件;

分类：
一、按条件表达式筛选
条件运算符：> ＜ = != ＜> >= ＜=
二.按逻辑表达式筛选
逻辑运算符：&& || !
三.模糊查询
like between and in (is null)

例1：查询工资大于12000的员工信息
select * from employees where salary>12000;

例2：查询部门编号不等于90号的员工名和部门编号
select last_name,department_id from employees where department_id!=90;

二、按逻辑表达式筛选
select last_name,salary,commission_pct from employees where salary>=10000 and salary＜=20000

1.like:
特点：1.一般和通配符搭配使用
通配符：
% 任意多个字符，包含0个字符
_ 任意单个字符

例1：查询员工名中包含字符a的员工信息
select * from employees where last_name like ‘%a%’;
例2：查询员工名中第三个字符为e，第五个字符为a的员工名和工资
select last_name,salary from employees where last_name like ‘__n_l%’;

MySQL中的转义字符为\

2.between and
between and包含临界值
例：查询id在100到120之间的员工信息
select * from employees where employee_id between 100 and 120;

3.in关键字
含义：1.判断某字段的值是否属于in列表中的某一项
2.in列表的值类型必须一致或兼容
案例：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
select last_name,job_id from employees where job_id in(‘IT_PROT’,‘AD_VP’,‘AD_PRES’);

4.is null
=不能用于判断null值
案例1：查询没有奖金的员工名和奖金率
select last_name,commission_pct from employees where commission_pct is null;
查询有奖金的员工名和奖金率
select last_name,commission_pct from employees where commission_pct is not null;
＜=＞既可以判断null值，又可以判断普通的数值
＜＞代表不等于

排序查询：
语法：select 查询列表 from [where 筛选条件] order by 排序列表 [asc|desc];
asc代表是升序，desc代表的是降序，如果不写，默认是升序
案例：查询员工信息，要求工资从高到低
select * from employees order by salary desc;

查询部门编号>=90的员工信息，按入职时间的先后进行排序
select * where department_id>=90 order by hiredate asc;

例：查询员工的姓名和部门号和年薪,按年薪降序 按姓名升序
select last_name,department_id,salary*12 年薪 from employees order by 年薪 desc,last_name asc;

例：选择工资不在8000到17000的员工的姓名和工资，按工资降序
select last_name,salary from employees where salary not between 800 and 17000 order by salary desc;

查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序
select * from employees where email like ‘%e%’ order by length(email) desc,department_id asc;

常见函数：
概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1.隐藏了实现细节 2.提高代码的重用性
调用：select 函数名(实参列表) [from 表];
特点：
1.叫什么（函数名）
2.干什么（函数功能）
分类：
1.单行函数：concat、length、ifnull等
2.分组函数
功能：做统计使用，又称为统计函数、聚合函数、组函数

一、字符函数：
1.length：获取参数值的字节个数
例：select length(‘jhon’)

2.concat 拼接字符串
select concat(last_name,’_’,first_name) from employees;

3.upper、lower：将字符串转换为大写或者小写
select upper(‘john’);
select lower(‘JOHN’);
例：将姓变大写；名变小写，然后拼接
select concat(upper(last_name),lower(first_name)) 姓名 from employees;

4.substr、substring
例：截取从指定索引处指定字符长度的字符
select substr(‘李莫愁爱上了陆展元’,1,3) out_put from employees;
结果为：李莫愁

5.instr
select instr(‘杨不悔爱上了殷六侠’,‘殷六侠’) as out_put;
返回子字符串在父字符串第一次出现的索引,如果找不到返回0

6.trim
select trim(‘a’ from ‘aaaaaaa张aaaaaa翠山’) as out_put;
结果为：张aaaaaa翠山

7.lpad 用指定的字符串实现左填充达到指定长度
例：select lpad(‘殷素素’,10,’*’) as out_put;

8.rpad 用指定的字符串实现左填充达到指定长度

9.replace替换(替换全部)
select replace(‘张无忌爱上了周芷若’,‘周芷若’,‘赵敏’) as out_put;

二、数学函数
1.round 四舍五入
select round(1.45);
select round(1.567,2);//四舍五入并且保留两位小数

2.ceil 向上取整，返回>=该参数的最小整数
select ceil(1.001);

3.floor:向下取整，返回＜=该参数的最大整数

4.truncate 截断
select truncate(1.65,1);结果为1.6

5.#mod取余
select mod(10,3);

三、日期函数
now 返回当前系统日期+时间
select now();

#curdate 返回当前系统日期，不包含时间
select curdate();

#可以获取指定的部分，年、月、日、小时、分钟、秒
select year(now()) 年;
select month(now()) 月;

四、其他函数
select version();
select database();
select user();

五.流程控制函数
1.if函数： if else 的效果
select if(10>5,‘大’,‘小’);

2.case函数的使用一：
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 salary1.1
when 40 then salary1.2
when 50 then salary*1.3
else salary
end as 新工资
from employees;

3.case 函数的使用二：类似于 多重if:
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 employees;

二、分组函数
功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：
sun 求和、avg 平均值、max 最大值、min 最小值、count 计算个数

特点：
1.sum、avg一般用于处理数值型，max、min、count可以处理任何类型
2.以上分组函数都忽略null值
3.可以和distinct搭配实现去重的运算

1.简单的使用 例：
select sum(salary) from employees;
select sum(salary) 和,avg(salary) 平均,max(salary) 最大值

select sount(*) from employees;：可以统计表的行数

例1.：查询公司员工工资的最大值，最小值，平均值，总和
select max(salary) mx_sal,min(salary) mi_sal,avg(salary) ag_sal,sum(salary) sm_sal from employees;
例2：查询部门编号为90的员工个数
select count(*) from employees where department_id = 90;

进阶5：分组查询
引入：查询每个部门的平均工资
分组数据：group by 子句语法
可以使用group by子句将表中的数据分成若干组
语法：
select 分组函数,列（要求出现在group by的后面）
from 表
[where 筛选条件]
group by 分组列表
[order by 子句]
注意：
查询列表必须特殊，要求是分组函数和group by后出现的字段

例1：查询每个工种的最高工资
select max(salary),job_id
from employees
group by job_id;

例2：查询邮箱中包含a字符的，每个部门的平局工资
select avg(salary),department_id
from employees
where email like ‘%a%’
group by department_id;

案例2：查询有奖金的每个领导手下员工的最高工资
select max(salary),manager_id
from employees
where commission_pct is not null
group by manager_id;

例：查询哪个部门的员工个数>2
（1）：查询每个部门的员工个数
select count(*),department_id
from employees
group by department_id;

(2):根据1的结果进行筛选，查询哪个部门的员工个数>2
select count(),department_id
from employees
group by departemnt_id
having count()>2(分组后的条件);

例：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
select max(salary),job_id
from employees
where commission_pct is not null
having max(salary)>12000;

案例：查询每个部门每个工种的员工的平均工资，并且按平均工资的高低显示
select avg(salary) a,department_id,job_id
from employees
where department_id is not null
group by department_id,job_id
having a>10000
order by a desc;

连接查询：
含义：又称多表查询，当查询的字段来自多个表时，就会用到连接查询
笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

例：
select name,bodyName from boys,beauty
where beauty.boyfriend_id=boys.id;

例2：查询员工名和对应的部门名
select last_name,department_name
from employees,departments
where employees.department_id=departments.department_id;

2.为表起别名
1.提高语句的简洁度
2.区分多个重名的字段
注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

1.多表等值连接的结果为多表的交集部分
2.n表连接，至少需要n-1个连接条件

查询员工名、工种号、工种名
select e.last_name,e.job_id,j.job_title
from employees e,jobs j
where e.job_id=j.job_id;

案例：查询有奖金的员工名、部门名
select last_name,department_name,commission_pct
from employees e,departments d
where e.department_id=d.department_id
and e.commission_pct is not null;

案例2：查询城市名中第二个字符为o的部门名和城市名
select department_name,city
from departments d,locations l
where d.location_id=l.location_id
and city like ‘_o%’;

查询每个城市的部门个数
select count(*) 个数,city
from departments d,locations l
where d.location_id=l.location_id
group by city;

查询每个工种的工种名和员工的个数，并且按员工个数降序
select job_title,count()
from employees e,jobs j
where e.job_id=j.job_id
group by job_title
order by count() desc;

7.实现三表连接
案例：查询员工名、部门名和所在城市，城市以s开头
select last_name,department_name,city
from employees e,departments d,locations l
where e.department_id=d.department_id
and d.location_id=l.location_id
and city like ‘s%’;

自连接：自己连接自己，把原来这张表当成两张表或多张表使用
例：查询员工名和上级的名称
select e.employee_id,e.last_name,m.employee_id,m.last_name
from employees e,employees m
where e.manger_id=m.employee_id;

已知表student，里面有id（学号），name，gradeId(年级编号)
已知表grade里面有id（年级编号），name（年级名）
已知表result，里面有id，score，studentNO（学号）
要求查询姓名、年级名、成绩
select s.name,g.name,r.score
from student s,grade g,result r
where s.id=r.studentNo
and g.id=s.gradeid;

其他函数：
version:当前数据库服务器的版本
database：当前打开的数据库
user:当前用户
password(‘字符’)：返回该字符的密码形式
md5(‘字符’)：返回该字符的md5加密形式

流程控制函数：
1.if(条件表达式,表达式1,表达式2)：如果条件表达式成立，返回表达式1，否则返回表达式2

2.case情况1
case 变量或表达式或字段
when 变量1 then 值1
when 变量2 then 值2
…
else 值n
end

2.case情况2
case
when 条件1 then 值1
when 条件2 then 值2
…
else 值n
end

二、sql99语法
语法：
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
连接类型分类：
内连接：inner
外连接：
左外：left 【outer】
右外：right 【outer】
全外：full 【outer】
交叉连接：cross

1.内连接：
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;

1.等值连接：
案例1.查询员工名、部门名
select last_name,department_name
from employees e
inner join departments d
on e.department_id=d.department_id;

案例2：查询名字中包含e的员工名和工种名（添加筛选）
select last_name,job_title
from employees e
inner join jobs j
on e.last_name like ‘%e%’;

案例3：查询部门个数>3的城市名和部门个数(添加分组+筛选)
#1.查询每个城市的部门个数
#2.在1的结果上筛选满足条件的
select city,count() 部门个数
from departements d
inner join locations
on d.location_id=l.location_id
group by city
having count()>3;