MySQL

---

数据库简介：

通过使用流操作文件的方式保存数据的弊端： 1、效率低2、实现过程比较复杂，开发效率底。3、一般只能保存小量数据4、只能保存文本

什么是DB

DataBase:数据库，数据库是一个文件集合。

什么是DBMS

DataBaseManagmentSystem:数据库管理系统，用于管理数据库文件。 常见的DBMS有哪些：Oracle MySQL DB2 SQLserver Sqlite

数据库的分类

1、关系型数据库 以表为数据库存储数据的单位，关系型数据库是经过数学理论验证可以保存现实生活中任何关系型的数据。 2、非关系型数据库 主要应用在一些特定场景，解决某些具体的问题，比如解决高并发访问时的数据缓存，（redis数据库属于非关系型 数据库redis内部以键值对的形式保存数据） 如：name = xxx

主流关系型数据库的介绍

1、MySQL开源产品 2、Oracle闭源产品

什么是SQL

Structured Query Language:结构化查询语言，用于程序员和DBMS进行交互

账户：root密码：123456 端口启动：mysql：mysql -uroot -p 输入密码：123456 “数据库中不区分大小写”

数据库相关的SQL(命令以分号结尾)

1、查看所有数据库：

show databases;

2、创建数据库：

create database 数据库名称; 例：create database db1;

3、删除数据库

drop database 数据库名称; 例：drop database db1;

4、查看数据库详细信息

show create database 数据库名称;

5、创建指定字符集的数据库

create database 数据库名称 character set gbk; 例：create database db2 character set gbk;

6、使用数据库

use 数据库名称 例：use db2;

表相关操作：DDL

什么是表：数据库中保存数据的单位 1、创建表 格式：create table 表名(字段1名 字段1类型，字段2名 字段2类型，...) 例子：创建一个person表 保存名字、性别、年龄、工资 create table person(name varchar(10),gender varchar(5),age int,salary int); 2、查看所有表 格式：show tables; 3、查看单个表详细信息 格式：show create table 表名; 4、创建表时指定引擎和字符集 格式：create table 表名(字段1名 字段1类型，字段2名 字段2类型，...) engine=innodb/myisam charset=gbk/utf8; 查看单个表：show create table 表名; 5、查看表字段信息 格式：desc 表名; 例子：desc person; 6、修改表名 格式：rename table 原名 to 新名; 例子：rename table person to people; 7、修改表引擎和字符集 格式：alter table people engine=innodb/myisam charset=gbk/utf8; 8、添加表字段 格式1：alter table 表名 add 字段名 字段类型; <添加到最后> 格式2：alter table 表名 add 字段名 字段类型 first; <添加到最前面> 格式3：alter table 表名 add 字段名 字段类型 after 字段名; <添加到字段名之后> 9、删除表字段 格式：alter table 表名 drop 字段名; 例子：alter table people drop age; 10、修改字段名称、类型 格式：alter table 表名 change 原字段名 新字段名 新类型; 例子：alter table hero change name age int; 11、修改字段类型和位置 格式: alter table 表名 modify 原字段名 新类型 firt(after xxx) 例子：alter table hero modify age varchar(10) after money; 12、删除表 格式：drop table 表名;

数据库表的引擎

1、innodb 支持数据库的复杂操作，包括外键、事务等 2、myisam 只支持数据的基础增删改查操作

数据相关SQL DML(数据操作语言)

示例：create table hero(id int,name varchar(10),gender varchar(5)); 解决字符串中文乱码问题：set names gbk; 1、插入数据 全表插入：insert into hero values(1,'Tom','Man'); <字符串使用单引号> 指定字段插入：insert into hero (name,gender) values('张飞','男'); 批量插入：insert into hero values(3,'唐僧','男'),(4,'孙悟空','男'),(5,'白骨精','女'),(6,'八戒','男'); insert into hero (name) values('马云'),('马化腾'),('李嘉诚'),('俞敏洪'),('王健林'); 2、查询数据 格式：select 字段信息 from 表名 where id<5; <where 查询条件可有可无> 例子：select * from hero; <查询所有数据的所有字段信息> select name,gender from hero; <查询所有的名字和性别> select * from hero where name='孙悟空'; <查询孙悟空的所有信息> select name from hero where gender='Man'; <查询性别为Man的所有名字> select * from hero where sal is null; <查询所有工资为null的员工信息> 3、修改数据 格式：update 表名 set gender='女' where id=1; 例子：update hero set gender='女' where id=1; <修改id=1的性别为女> update hero set gender='女' where id<5; <修改id<5的性别为女> update hero set name='悟能',gender='男' where name='八戒'; <修改八戒的名字为悟能> 4、删除数据 格式：delete from 表名 where 条件; 例子：delete from hero where name='张飞'; delete from hero where id<3; <删除id小于3的信息> delete from hero; <删除表中所有数据>

主键约束

主键：用于表示数据唯一性的字段称为主键 约束：就是对表字段的值添加限制条件 主键约束：保证主键的值唯一且非空 格式：create table t1(id int primary key,name varchar(10)); 测试：insert into t1 (id,name) values(1,'aaa'); <添加成功> insert into t1 (id,name) values(1,'bbb'); <报错，主键不能重复> insert into t1 (id,name) values(null,'ccc'); <报错，主键不能为空>'还不是自增，所以不能为空'

主键约束+自增

格式：create table t2(id int primary key auto_increment,name varchar(10)); 测试：insert into t2 values(null,'aaa'); <添加成功> insert into t2 (name) values('bbb'); <添加成功> insert into t2 values(10,'ccc'); <添加成功,可以指定不重复id> insert into t2 values(null,'ddd'); <添加成功,id=11> delete from where id>=10; <删除id=10与id=11> insert into t2 values(null,'eee'); <添加成功,id=12> delete from t2; <删除表中所有数据> insert into t2 values(null,'fff'); <添加成功,id=13> 自增数值只增不减，从历史最大值基础上增加1; truncate table 表名; <删除原表，并且创建新表，保留原表的属性以及字段信息> truncate table t2;

注释 comment

格式：create table t3(id int primary key auto_increment comment '主键',name varchar(10) comment '名字'); 查看注释内容：show create table t3;

数据冗余

如果表设计不合理，可能会出现大量的重复数据，这种现象称为数据冗余，通过拆分表的形式解决此问题。 练习：创建表保存以下信息 保存集团总部下财务部财务A部里面的张三，工资8000，年龄18； 保存集团总部下研发部的李四，工资2000，年龄675； 需要保存的信息有：部门名 员工姓名 工资 年龄 -考虑数据冗余问题所以创建两个表：员工表和部门表 create table t_emp(id int primary key auto_increment,name varchar(10),sal int,age int,dept int); create table t_dept(id int primary key auto_increment,name varchar(10),parent_id int); -插入数据，先插入部门数据，再插入员工表数据 insert into t_dept values(null,'集团总部',null),(null,'财务部',1),(null,'财务A部',2),(null,'研发部',1); insert into t_emp values(null,'张三',8000,18,3),(null,'李四',2000,75,4); 练习：分类：category 价格：price 库存：num 1、保存家电分类 下洗衣机分类 下的海尔洗衣机，价格2300，库存38； 2、保存办公用品下，笔分类下的晨光圆珠笔，价格5，库存100； create table haier(id int primary key auto_increment,name varchar(10),price int,num int,category_id int); create table category(id int primary key auto_increment,name varchar(10),parent_id int); insert into category values(null,'家电',null),(null,'洗衣机',1),(null,'办公用品',null),(null,'笔',3); insert into haier values(null,'海尔洗衣机',2300,38,2),(null,'晨光圆珠笔',5,100,4);

事务

事务（transaction）是数据库中执行同一业务多条SQL语句的工作单元，事务可以保证多条SQL语句全部执行成功，或者全部执行失败。 和事务相关的SQL语句： 第一种开启事务保护的方式： 1、begin; 开启事务 2、rollback; 回滚事务，把内存中的改动清除 3、commit; 提交事务，把内存中的改动数据调到磁盘中 开启事务： begin; -执行语句; 回滚： rollback; 如果有一条语句执行失败，所以不能把内存中的改变提交到磁盘中，所以需要执行回滚指令，执行之前可以打开另外一个窗口查看数据库中的数据是否发生改变（变的是内存中的数据，数据库里面的数据并没有发生改变） 提交： commit; 由于多条SQL语句全部执行成功，所以可以执行提交指令，把内存中的改动提交到磁盘中 第二种开启事务保护的方式： 数据库中的事务默认是自动提交的 1、查看数据库自动提交的状态 show variables like '%autocommit%'; 2、修改自动提交的状态 ON打开(1)，OFF关闭(0)； set autocommit=0; 关闭自动提交之后需要手动提交； 保存回滚点： savepoint 标识; rollback to 标识; <回滚到回滚点> 测试：begin; update user set money=10000 where id=1; savepoint s1; update user set money=20000 where id=1; savepoint s2; update user set money=30000 where id=1; savepoint s3; rollback to s1; select * from user; rollback;

SQL分类

DDL->Data Definition Language(数据定义语言) 包括：create alter drop truncate <不支持事务操作> DML->Data Manipulation Language(数据操作语言) 包括：insert delete update select(DQL) <支持事务操作> DQL->Data Query Language(数据查询语言) 包括：select TCL->Transaction Control Language(事务控制语言) 包括：begin rollback commit savepoint xxx; rollback to xxx; DCL->Data Control Language(数据控制语言) 用于分配用户去权限,包括：grant revoke

数据类型

1、整数类型 int(m) 对应java中int，m代表显示数据长度 bigint(m) 对应java中的long，m代表显示长度，需要结合zerofill使用(长度不够，高位补0填充) 测试：create table t_int(id int,age int(8) zerofill); insert into t_int values(1,26); select * from t_int; 2、浮点数类型 float(m,d) 单精度浮点型，8位精度(4字节)，m代表总长度，d代表小数长度 double(m,d) 双精度浮点型，16位精度(8字节)，m代表总长度，d代表小数长度 例如：76.232 （m=5,d=6小数点不算） decimal 超高精度小数，当涉及超高精度运算时使用 3、字符串类型 char(m) 固定长度，最大值为255 ； 例如：char(10), 存入"abc"，空格补充，则所占长度为10； varchar(m) 可变长度，最大值65535 例如：varchar(10),存入'abc'，所占长度为3，可变长度更节省空间，固定长度执行效率略高。 varchar 最大长度为65535，但是建议保存255以内的长度，超过255使用text text 可变长度，最大值65535 4、日期 date:只能保存年月日 time:只能保存时分秒 datetime:保存年月日时分秒，最大值9999-12-31,默认值为null timestamp:保存年月日时分秒，最大值2038-1-19，默认为当前时间。 例子： create table t_date(d1 date,d2 time,d3 datetime,d4 timestamp); insert into t_date values('2018-11-15','16:58:50',null,null); insert into t_date values('2018-11-15','16:58:50','2018-11-15 16:59:45',null); select * from t_date; 注意datetime的书写格式

冗余练习：

1. 设计表保存以下数据 小明 男 18岁 工资3000 销售部 北京 无领导 小丽 女 20岁 工资4000 市场部 上海 无领导 小黄 女 25岁 工资1500 销售部 北京 上级领导小明 李白 男 35岁 工资800 市场部 上海 小丽 典韦 男 33岁 工资1500 市场部 上海 小丽 create table people(id int primary key auto_increment,name varchar(10),gender varchar(10),age int,sal int,leader int,category_id int); create table t_category(id int primary key auto_increment,category varchar(10),address varchar(10),parent_id int); insert into t_category values(null,'销售部','北京',null),(null,'市场部','上海',null); insert into people values(null,'小明','男',18,3000,null,1),(null,'小丽','女',20,4000,null,2),(null,'小黄','女',25,8000,1,1),(null,'李白','男',35,800,2,2),(null,'典韦','男',33,1500,2,2); select * from t_category; select * from people; 2. 查询市场部的所有员工姓名、性别和工资 select name,gender,sal from people where category_id=2; 3. 查询工作地点在上海的部门名称 select category from t_category where address='上海'; 4. 修改小丽的名字为丽丽 update people set name='丽丽' where name='小丽'; 5. 查询25岁以下的女员工有谁 select * from people where age<25 and gender='女'; 6. 查询市场部的男员工姓名和工资 select name,sal from people where category_id=2 and gender='男'; 7. 删除没有领导的男员工 delete from people where leader is null and gender='男'; 8. 指定字段插入三个员工的姓名和工资，刘备2000、关羽1500、张飞100 insert into people (name,sal) values('刘备',2000),('关羽',1500),('张飞',100); 9. 删除工资低于1000的员工 delete from people where sal<1000; 10. 删除表里所有数据、删除表 、删除库。 delete from people; drop table people; drop database newdb2;

导入*.sql数据到MYSQL里面

windows:source D:/tables.sql; Linux：source /home/user1/desktop/tables.sql; 导入之后查看：show tables; ->newdb3

is null 与is not null 的区别

1、没有上级领导的员工编号，姓名，工资: select empno,ename,sal from emp where mgr is null; 2、查询emp表中没有奖金comm的员工姓名，工资，奖金: select ename,sal,comm from emp where comm is null; 3、查询emp表中有奖金的员工信息： select * from emp where comm is not null and comm>0;

别名

把查询到的员工姓名ename改成 名字(``与单引号''都可以) select ename as `名字` from emp; select ename '名字' from emp; select ename 名字 from emp;

查重distinct ‘去重复’

查询emp表中出现的所有职位job select distinct job from emp;

比较运算符

符号：> < >= <= = != 不等于：<> 1、查询工资小于等于1600的员工姓名，工资 select ename,sal from emp where sal<1600; 2、查询部门编号是20的所有员工姓名，职位job和部门编号deptno select ename,job,deptno from emp where deptno=20; 3、查询职位是manager的所员工姓名和职位 select ename,job from emp where job='manager'; 4、查询部门不是10号部门的所有员工姓名和部门编号（两种写法） select ename,deptno from emp where deptno!=10; select ename,deptno from emp where deptno<>10; 5、查询t_item表单价格price等于23的商品信息 select * from t_item where price=23; 6、查询t_item 表单中单价不等于8443的商品标题title和单价 select title,price from t_item where price!=8443;

and 和 or 关键字

and和java中的&&效果一样 or 和java中的||效果一样 1、查询不是10号部门并且工资小于3000的员工信息 select * from emp where deptno!=10 and sal<3000; 2、查询部门编号为30或者上级领导为7698的员工姓名，职位，上级领导和部门编号 select ename,job,mgr,deptno from emp where deptno=30 or mgr=7698; 3、查询emp表中工资为5000,1500,3000的员工信息 select * from emp where sal=5000 or sal=1500 or sal=3000;

in 与 not in 关键字/* 同一个字段等于多个值的时候使用*/

1、查询emp表中工资为5000,1500,3000的员工信息 select * from emp where sal in(5000,1500,3000); 对比or用法：select * from emp where sal=5000 or sal=1500 or sal=3000; 2、查询emp表中工资不等于5000,1500,3000的员工信息 select * from emp where sal not in(5000,1500,3000); 3、查询emp表中工资在2000到3000之间的员工信息 select * from emp where sal>2000 and sal<3000;

between x and y 包括x,y

1、查询emp表中工资在2000到3000之间的员工信息 select * from emp where sal between 2000 and 3000; 2、查询emp表中工资在1000到3000之外的员工信息 ‘not between’ select * from emp where sal not between 1000 and 3000;

模糊查询like\not like

_ :代表单个未知字符 % :代表0或多个未知字符 举例： 1、查询以a开头 like 'a%'; 2、以m结尾 like '%m'; 3、第二个字符是a like '_a%'; 4、第三个字符是x,倒数第二个字符是y like '__x%y_'; 5、倒数第三个字符是x like '%x__'; 6、包含字符x like '%x%'; 7、不包含字符x not like '%x%';

案例练习：

1、查询t_item表中标题中包含 记事本 的商品信息（标题） select title from t_item where title like '%记事本%'; 2、查询单价低于100的记事本（title 包含记事本） select title,price from t_item where title like '%记事本%' and price<100; 3、查询单价在50到200之间的 得力商品（title包含 得力） select title,price from t_item where title like '%得力%' and price between 50 and 200; 4、查询有图片的得力商品（有图片image字段不为null） select * from t_item where image is not null and title like '%得力%'; 5、查询分类(category_id)为238,917的商品信息 select * from t_item where category_id in(238,917); 6、查询有赠品的商品信息（卖点sell_point包含赠字） select title,sell_point from t_item where sell_point like '%赠%'; 7、查询标题中不包含得力的 商品标题 select title from t_item where title not like '%得力%'; 8、查询价格在50到200之外的商品 select * from t_item where price not between 50 and 200;

排序order by(默认升序排序)

格式 order by 字段名 desc降序/asc升序; 1. 查询所有员工的姓名和工资，按照工资升序排序 select ename,sal from emp order by sal; 2. 查询所有员工的姓名，工资和部门编号，按照部门编号降序排序 select ename,sal,deptno from emp order by deptno desc;

多字段排序： order by 字段名1 asc/desc,字段名2 asc/desc;

1. 查询所有员工的姓名，工资和部门编号，按照部门编号降序排序,如果部门一致则按照工资升序排序 select ename,sal,deptno from emp order by deptno desc,sal; "如果查询语句中有where,那么order必须在where 之后使用"

分页查询 limit

格式：limit 跳过的条数,请求的数量 举例： 1、请求第1页20条数据 limit 0,20; 2、请求第3页10条数据 limit 20,10; 3、请求第5页8条数据 limit 32,8; 4、请求第4页7条数据 limit 21,7; 公式：limit (页数-1)*每页数量,每页数量

练习：

1、查询所有商品 按照单价升序排序，显示第二页每页的7条数据 select price from t_item order by price limit 7,7; 2、查询员工表所有员工的姓名和工资，按照工资降序排序，显示第3页每页3条数据 select ename,sal from emp order by sal desc limit 6,3; 3、查询所有员工中工资前三名的姓名和工资 select ename,sal from emp order by sal desc limit 0,3; 4、查询工资最高的员工姓名和工资 select ename,sal from emp order by sal desc limit 0,1;

数值计算 + - * / %

7%2 等效于 mod(7,2) 1、查询员工姓名，工资，及年终奖（年终奖=工资*5） select ename,sal,sal*5 年终奖 from emp; 2、查询t_item 表中商品单价，库存和总金额（单价*库存） select price,num,num*price 总金额 from t_item; 3、修改每个员工的工资，每个人张10块钱 update emp set sal=sal+10; update emp set sal=sal-10;

日期相关的函数

select 'helloworld'; <SQL的HelloWorld> 1、获取当前日期+时间 now() select now(); 2、获取当前的日期 curdate() select curdate(); 3、获取当前时间 curtime() select curtime(); 4、从年月日时分秒中提取年月日 和提取时分秒 年月日：select date(now()); 时分秒：select time(now()); 举例： 查询商品上传的日期 select date(created_time) from t_item; 查询商品上传的时间 select time(created_time) from t_item; 5、从年月日时分秒中提取时间分量 extract(year from now()) select extract(year from now()); select extract(month from now()); select extract(day from now()); select extract(hour from now()); select extract(minute from now()); select extract(second from now()); 举例： 查询每个员工入职的年份 select extract(year from hiredate) from emp; 6、日期格式化 格式： %Y 代表YYYY 2018 %y 代表YY 18 %m 代表两位月 05 %c 代表一位月 5 %d 代表日 %H 代表24小时 %h 代表12小时 %i 代表分 %s 代表秒 date_format(时间,格式) 举例： 把时间默认格式转成 年月日时分秒 select date_format(now(),'%Y年%m月%d日 %H时%i分%s秒'); 把非标准格式的时间转回默认格式 str_to_date('非标准格式的时间',格式); 举例： 把14.08.2018 08:00:00转成标准格式 select str_to_date('14.08.2018 08:00:00','%d.%m.%Y %H:%i:%s');

ifnull(x,y)函数

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

聚合函数

聚合函数用于对多行数据进行统计，平均值，最大值，最小值，求和，统计数量 平均值：avg(字段名称) 1、查询所有员工的的平均工资 select avg(sal) from emp; 2、查询10号部门的平均工资 select avg(sal) from emp where deptno=10; 3、查询戴尔商品的平均单价 select avg(price) from t_item where title like '%戴尔%'; 最大值：max(字段名) 1、查询所有员工的最高工资 select max(sal) from emp; 2、查询30部门中的最高奖金 select max(comm) from emp where deptno=30; 最小值：min(字段名称) 1、查询20号部门的最低工资 select min(sal) from emp where deptno=20; 2、查询商品表中所有商品中最便宜的价格是多少 select min(price) from t_item; 求和：sum(字段名) 1、查询30号部门每个月需要发多少工资 select sum(sal) from emp where deptno=30; 2、查询戴尔商品的库存总量 select sum(num) from t_item where title like '%戴尔%'; 统计数量：count(字段名) /* 不统计null值 */ 1、查询员工表中的员工人数 select count(ename) from emp; select count(*) from emp; /* 返回最大值*/ 2、查询员工表中30号部门工资大于2000块钱的人数 select count(*) from emp where deptno=30 and sal>2000;

字符相关

1、字符串拼接 concat('aa','bb') ->aabb 案例：查询emp表中员工姓名和工资 工资后面显示单位 元 select ename,concat(sal,'元') from emp; 2、获取字符串长度 char_length('abc') 3 案例：查询员工姓名和姓名的长度 select ename,char_length(ename) from emp; 3、获取字符串在另一个字符串出现的位置 格式：instr(str,substr) select instr('abcdefg','c'); 格式：locate(substr,str) select locate('c','abcdefg'); 4、插入字符串 /* 类似替换*/ 格式：insert(str,start,length,newstr) select insert('abcdefg',3,2,'m'); ->abmefg 5、转大小写 格式：upper(str) lower(str) select upper('abcD'),lower('BGFas'); 6、去两端空白 select trim(' a b '); ->输出：a b 7、截取字符串 left(str,num); /* 从左开始截取,num表示截取的字符数*/ select left('abcdefg',2); right(str,num) select right('abcdefg',2); /* 从右向左开始截取,num表示截取的字符数*/ substring(str,start,length) /* length 表示截取长度，如果不写表示截取到末尾*/ select substring('abcdefg',2,3); 8、重复 repeat(str,次数) select repeat('abc',3); /* 重复abc 3 次*/ 9、替换 replace(str,oldstr,newstr) /* 把oldstr替换成新的newstr*/ select replace('abcdefg','cd','AA'); 10、反转 reverse(str) /* 序列反转*/ select reverse('abcdefg');

数学相关的函数

1、向下取整floor(num) select floor(3.14); 2、四舍五入round(num)/round(num,m) 'm代表保留两位小数' select round(23.8); select round(23.5678,2); 3、非四舍五入truncate(num,m) 'm代表保留两位小数，多余小数直接去掉' select truncate(23.5678,2); 4、随机数rand()产生0-1之间的随机小数 取3-5之间的数 3,4,5 --> 0,1,2 select floor(rand()*3)+3; '0-3向下取整为0,1,2，再加上3'

分组查询(group by )

1、查询每个部门的平均工资 select deptno,avg(sal) from emp group by deptno; 查询每个职位的最高工资 select job,max(sal) '每个职位最高工资' from emp group by job; 查询每个部门的人数 select deptno,count(*) from emp group by deptno; 查询每个职位中工资大于1000的人数 select count(*) from emp where sal>1000 group by job; 查询每个领导的手下人数 select mgr,count(*) from emp where mgr is not null group by mgr; 2、多字段分组查询 1、查询每个部门每个主管的手下人数 select deptno,mgr,count(*) from emp where mgr is not null group by deptno,mgr; 2、案例：查询emp表中每个部门的编号，人数，工资总和，最后根据人数进行升序排列，如果人数一致根据工资总和降序排列。 select deptno,count(*),sum(sal) from emp group by deptno order by count(*),sum(sal) desc; select deptno,count(*) c,sum(sal) s from emp group by deptno order by c,s desc; 3、案例：查询工资在1000~3000之间的员工信息，每个部门的编号，平均工资，最低工资，最高工资，根据平均工资进行升序排列。 select deptno,avg(sal) a,min(sal),max(sal) from emp where sal between 1000 and 3000 group by deptno order by a; 4、案例：查询含有上级领导的员工，每个职业的人数，工资的总和，平均工资，最低工资，最后根据人数进行降序排列，如果人数一致，根据平均工资进行升序排列 select job,count(*) c,sum(sal),avg(sal) a,min(sal) from emp where mgr is not null group by job order by c desc,a;

having

having后面可以写普通字段的条件,也可以写聚合函数的条件,但是不推荐写普通字段的条件。 having要结合分组查询使用。having书写位置在group by之后。 where后面不能写聚合函数的条件 1、查询每个部门的平均工资，要求平均工资大于2000 错误：select deptno,avg(sal) a from emp where a>2000 group by deptno; 正确：select deptno,avg(sal) a from emp group by deptno having a>2000; 2、查询t_item表中每个分类的平均单价，要求平均单价低于100 select category_id,avg(price) a from t_item group by category_id having a<100; 3、查询emp表中工资在1000-3000之间的员工,每个部门的编号，工资总和，平均工资，过滤掉平均工资低于2000的部门，按照平均工资进行降序排序。 select deptno,sum(sal),avg(sal) a from emp where sal between 1000 and 3000 group by deptno having a>2000 order by a desc; 4、查询emp表中每个部门的平均工资高于2000的部门编号，部门人数，平均工资，最后根据平均工资降序排序 select deptno,count(*),avg(sal) a from emp group by deptno having a>2000 order by a desc; 5、查询emp表中job不是以s开头，每个职位的名字，人数，工资总和，最高工资，过滤掉平均工资是3000的职位， 根据人数升序，如果人数一致则根据工资总和降序排序。 select job,count(*) c,sum(sal) s,max(sal) from emp where job not like 's%' group by job having avg(sal)<>3000 order by c,s desc; 6、查询emp表中每年入职的人数 select extract(year from hiredate) ex,count(*) from emp group by ex; 7、查询每个部门的最高平均工资 select deptno,avg(sal) a from emp group by deptno order by a desc limit 0,1;

子查询(嵌套查询)

1、查询表中工资最高的员工信息 'select * from emp order by sal desc limit 0,1;' select * from emp where sal=(select max(sal) from emp); 2、查询emp表中工资大于平均工资的所有员工信息 select * from emp where sal>(select avg(sal) from emp); 3、查询工资高于20号部门最高工资的所有员工信息 select * from emp where sal>(select max(sal) from emp where deptno=20); 4、查询和jones相同工作的其他员工信息 select * from emp where job=(select job from emp where ename='jones') and ename<>'jones'; 5、查询工资最低的员工的同事的信息（同事=job）'不包含最低工资员工信息' select * from emp where job=(select job from emp where sal=(select min(sal) from emp)) and sal<>(select min(sal) from emp); 6、查询最后入职的员工信息 select * from emp where hiredate=(select max(hiredate) from emp); 7、查询员工king的部门编号和部门名称（需要用到dept表） select deptno,dname from dept where deptno=(select deptno from emp where ename='king'); 8、查询有员工的部门信息 select distinct deptno from emp; 'distinct去重' select * from dept where deptno in(select distinct deptno from emp); 9、查找平均工资最高的部门信息（最大难度）'本体注重解决并列问题' '平均工资最高': select avg(sal) from emp group by deptno order by avg(sal) desc limit 0,1; '平均工资最高的部门编号': select deptno from emp group by deptno having avg(sal)=(select avg(sal) from emp group by deptno order by avg(sal) desc limit 0,1;); '对应部门信息': select * from dept where deptno in(select deptno from emp group by deptno having avg(sal)=(select avg(sal) from emp group by deptno order by avg(sal) desc limit 0,1));

子查询总结：

1、嵌套在SQL语句中的查询语句称为子查询 2、子查询语句可以嵌套n层 3、子查询可以写在哪些位置？ 写在where或者having 后面作为查询条件的值 写在from 后面当成一张表使用'必须有别名' select * from emp where deptno=20; select ename from (select * from emp where deptno=20) t1; 写在创建的时候'将查询到的内容作为新表的内容' create table newemp as (select ename,sal,deptno from emp where deptno=20);

关联查询

同时查询多张表的查询方式称为关联查询 1、查询每一个员工姓名和其对应的部门名称 select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno; 2、查询在new york工作的所有员工的信息 select e.* from emp e,dept d where e.deptno=d.deptno and d.loc='new york'; 3、查询价格在50以内的商品标题和商品分类名称 select t.title,tc.name from t_item t,t_item_category tc where t.category_id=tc.id and t.price<50;

笛卡尔积 如果关联查询不写关联关系，则得到两张表的乘积，这个乘积称为笛卡尔积。 笛卡尔积是错误的查询方式导致的结果，工作中切记不要出现。

等值连接与内连接

两种查询方式得到的结果一样。 1、等值连接：select * from A,B where A.x=B.X and A.age=18; 2、内连接： select * from A [inner] join B on A.x=B.x where A.age=18; 举例： 1、查询一个员工姓名和其对应的部门名称 select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno; 'where xxxx' 3、外链接 '查询A，B两张表的数据，如果查询两张表的交集数据使用内连接或者等值连接，如果查询某一张表的全部数据和另外一张表的交集数据则使用外链接' 左外链接：select * from A left [outer] join B on A.x=B.x where A.age=18; 右外链接：select * from A right join B on A.x=B.x where A.age=18; 举例： 1、查询所有员工和对应的部门名称 -插入新数据 insert into emp (empno,ename,sal) values(null,'Tom',500); select e.ename,d.dname from emp e left join dept d on e.deptno=d.deptno; 2、查询所有部门名称和对应的员工姓名 select e.ename,d.dname from emp e right join dept d on e.deptno=d.deptno; 3、查询每个城市的员工人数（需要查询出 波士顿0人） select d.loc,count(ename) from emp e right join dept d on e.deptno=d.deptno group by d.loc; 4、查询每个员工的名字和对应的主管名字 '如果表中建立关系的字段指向当前的表的主键，则称为自关联，关联查询时把一张表当成两张表' select e.ename,m.ename from emp e left join emp m on e.mgr=m.empno; 5、查询每个员工的名字、对应的主管名字和对应的部门名字 select e.ename,m.ename,d.dname from emp e left join emp m on e.mgr=m.empno join dept d on e.deptno=d.deptno; 6、查询emp表中所有员工的姓名以及该员工的领导编号，领导姓名，领导职位 select e.ename,m.empno,m.ename,m.job from emp e left join emp m on e.mgr=m.empno; 7、查询emp表名字中没有字母K的所有员工的编号，姓名，职位，工资，部门名称 select e.empno,e.ename,e.job,e.sal,d.dname from emp e left join dept d on e.deptno=d.deptno where e.ename not like '%k%'; 8、查询dept表中所有部门的信息和与之关联的员工名字和工资，排除掉10号部门，根据部门编号降序排序，如果部门编号一致则按照工资升序排序 select d.*,e.ename,e.sal from emp e right join dept d on e.deptno=d.deptno where e.deptno != 10 order by e.deptno desc,e.sal;

关键字书写顺序： _<select ... from .... where .... group by ... having ... order by ... limit ...>_

表设计之关联联系

主键：是用于表示数据唯一性的字段 外键：是用于建立关系的字段，值通常指向另一张表的主键 '一对一' 什么是一对一关系： 有A、B两张表，A表中一条数据对应B表中的一条数据，称为一对一关系 应用场景： 用户表和用户信息扩展表，商品表和商品信息扩展表 如何建立关系：在从表中添加外键指向主表的主键 练习：创建用户表，用户信息表 create table user(id int primary key auto_increment,username varchar(10),password varchar(10)); insert into user values(null,'libai','admin'),(null,'liubei','admin'),(null,'guanyu','123456'); create table userinfo(uid int,nick varchar(10),age int,phone varchar(15)); insert into userinfo values(1,'李白',28,'13838384380'),(2,'刘备',33,'13222124678'),(3,'关羽',30,'13569804223'); 1、查询每一个用户的用户名、昵称、和电话 select us.username,uf.nick,uf.phone from user us join userinfo uf on us.id=uf.uid; 2、查询(nick)李白的用户名和密码 select us.username,us.password from user us join userinfo uf on us.id=uf.uid where uf.nick='李白'; 3、查询guanyu的昵称 select uf.nick from user us join userinfo uf on us.id=uf.uid where us.id=3; '一对多' 什么是一对多：AB两张表，A表中的一条数据对应B表中的多条数据，同时B表中的一条对应A表中的一条数据 应用场景：员工表和部门表 商品表和商品分类表 如何建立关系：在多的表中添加外键指向另一张表的主键 练习：创建t_emp(id,name,sal,dept_id)和t_dept(id,name,loc) 保存一下数据： 1 诸葛亮 3000 法师部 A国 2 周瑜 2000 法师部 A国 3 关羽 1500 战士部 B国 4 张飞 1000 战士部 B国 5 后羿 4000 射手部 C国 6 孙尚香 5000 射手部 C国 create table t_emp(id int primary key auto_increment,name varchar(10),sal int,dept_id int); create table t_dept(id int primary key auto_increment,name varchar(10),loc varchar(10)); insert into t_dept values(null,'法师部','A国'),(null,'战士部','B国'),(null,'射手部','C国'); insert into t_emp values(null,'诸葛亮',3000,'1'),(null,'周瑜',2000,'1'),(null,'关羽',1500,'2'),(null,'张飞',1000,'2'),(null,'后羿',4000,3),(null,'孙尚香',5000,3); 1、查询每个员工的名字和所在部门的名称 select e.name,d.name from t_emp e join t_dept d on e.dept_id=d.id; 2、查询A国的员工姓名 select e.name from t_emp e join t_dept d on e.dept_id=d.id where d.loc='A国'; 3、查询张飞的部门名称 select d.name from t_emp e join t_dept d on e.dept_id=d.id where e.name='张飞'; 4、查询工资在2000以下的员工姓名、所在地 select e.name,d.loc from t_emp e join t_dept d on e.dept_id=d.id where e.sal<2000; '多对多' 什么是多对多：AB两张表，A表中的一条数据对应B表中的多条数据，同时B表中的一数据对应A表中的多条。 应用场景：老师和学生表 如何建立关系：创建一个关系表，两个外键字段，分别指向另外两个表的主键 练习：创建student(id,name) teacher(id,name) t_s(tid,sid) create table student(id int primary key auto_increment,name varchar(10)); create table teacher(id int primary key auto_increment,name varchar(10)); create table t_s(tid int,sid int); 保存一下数据：tid sid 苍老师：小明 小红 小绿 小蓝 范老师：小明 小红 insert into teacher values(null,'苍老师'),(null,'范老师'); insert into student values(null,'小明'),(null,'小红'),(null,'小绿'),(null,'小蓝'); insert into t_s values(1,1),(1,2),(1,3),(1,4),(2,1),(2,2); 1、查询每个学生对应的老师 select s.name,t.name from student s join t_s ts on s.id=ts.sid join teacher t on ts.tid=t.id; 2、查询苍老师的学生有哪些 select t.name,s.name from student s join t_s ts on s.id=ts.sid join teacher t on ts.tid=t.id where t.name='苍老师'; 3、查询小红的老师是谁 select s.name,t.name from student s join t_s ts on s.id=ts.sid join teacher t on ts.tid=t.id where s.name='小红';

自关联

当前表的外键指向当前表的主键，这种关联方式叫做自关联 应用场景：需要保存上下级关联关系时使用 1、查询员工姓名和对应的主管姓名 select e.ename,m.ename from emp e join emp m on e.mgr=m.empno;

连接方式和关联方式

连接方式：包括等值连接，内连接，外连接；是指查询多张表数据时使用的查询方式 关联关系：包括一对一，一对多，多对多，是指设计表时，两张表之间存在的逻辑关系

表设计案例：权限管理

需要创建5张表，3张主表，2张关系表 create database db5 character set utf8; use db5; create table user(id int primary key auto_increment,name varchar(10)); create table role(id int primary key auto_increment,name varchar(10)); create table module(id int primary key auto_increment,name varchar(10)); create table u_r(uid int,rid int); create table r_m(rid int,mid int); 插入一下数据：角色权限大小：3 2 1 权限：男浏览 男发帖 男删帖 女浏览 女发帖 女删帖 角色：男管理员 男会员 男游客 女管理员 女会员 女游客 用户：刘德华(男会员，女游客) 张学友(男管理员) 凤姐(女管理员，男会员) insert into module values(null,'男浏览'),(null,'男发帖'),(null,'男删帖'),(null,'女浏览'),(null,'女发帖'),(null,'女删帖'); insert into role values(null,'男会员'),(null,'男管理员'),(null,'男游客'),(null,'女会员'),(null,'女管理员'),(null,'女游客'); insert into user values(null,'刘德华'),(null,'张学友'),(null,'凤姐'); insert into u_r values(1,2),(1,6),(2,1),(3,4),(3,2); insert into r_m values(1,1),(1,2),(1,3),(2,1),(2,2),(3,1),(4,4),(4,5),(4,6),(5,4),(5,5),(6,4); 1、查询每个用户对应的权限有哪些 select u.name,m.name from user u join u_r ur on u.id=ur.uid join r_m rm on ur.rid=rm.rid join module m on rm.mid=m.id; 2、查询凤姐的权限有哪些 select m.name from user u join u_r ur on u.id=ur.uid join r_m rm on ur.rid=rm.rid join module m on rm.mid=m.id where u.name='凤姐'; 3、查询拥有 男发帖权限的用户有哪些 select u.name from user u join u_r ur on u.id=ur.uid join r_m rm on ur.rid=rm.rid join module m on rm.mid=m.id where m.name='男发帖';

视图

什么是视图：数据库中的表和视图都是其内部的对象，视图可以理解成一条虚拟的表，视图本质就是取代了一条SQL查询语句。 为什么使用视图：因为有些数据的查询需要书写大量的SQL语句，每次书写效率太低，使用视图可以起到SQL重用的作用，视图可以隐藏敏感信息 1、创建视图 create view 视图名 as 子查询 对比：create table 表名 as 子查询 2、创建一个10号部门员工的视图 create view v_emp_10 as (select * from emp where deptno=10); 从视图中查询数据 select * from v_emp_10; '等效于' select * from emp where deptno=10; 3、创建一个没有工资的员工表视图 create view v_emp_nosal as (select empno,ename,comm,deptno from emp); 查询v_emp_nosal select * from v_emp_nosal;

视图的分类

1、简单视图：创建视图的子查询中不包含：去重，函数，分组，关联查询，可以对视图中的数据进行增删改查操作。 2、复杂视图：和简单视图相反，只能对视图中的数据进行查询操作。 创建一个复杂视图 create view v_emp_info as (select avg(sal),max(sal),min(sal) from emp); 查询：select * from v_emp_info; 对简单视图进行增删改查，操作方式和table一样

1、插入数据

insert into v_emp_10 (empno,ename,deptno) values(10011,'刘备',10); select * from v_emp_10; '数据插入' select * from emp; '数据插入' 如果插入一条数据在视图中不可见，但是在元表中却可见的数据称为数据污染。 insert into v_emp_10 (empno,ename,deptno) values(10012,'关羽',20); select * from v_emp_10; '数据没有插入' select * from emp; '数据插入' 通过 with check option 解决数据污染问题 create view v_emp_20 as (select * from emp where deptno=20) with check option; insert into v_emp_20 (empno,ename,deptno) values(10013,'赵云',20); '插入成功' insert into v_emp_20 (empno,ename,deptno) values(10014,'黄忠',30); '插入失败' 修改和删除视图中的数据(只能修改删除视图中的有的数据) update v_emp_20 set ename='赵2云' where ename='赵云'; '修改成功' update v_emp_20 set ename='刘2备' where ename='刘备'; '修改失败' delete from v_emp_20 where deptno=10; '没有数据被删除' 创建或替换视图 '如果视图表已经存在则替换原来的视图内容' create or replace view v_emp_10 as (select * from emp where deptno=10 and sal>2000); 删除视图 drop view 视图名; drop view v_emp_10; 如果创建视图的子查询中使用了别名，则对视图操作时只能使用别名 create or replace view v_emp_10 as (select ename name from emp where deptno=10); select name from v_emp_10; '成功' select ename from v_emp_10; '失败'

视图总结

1. 视图是数据库中的对象，代表一段SQL语句，可以理解成一张虚拟的表 2. 作用： 重用SQL，隐藏敏感信息 3. 分类：简单视图（创建视图时不使用去重、函数、分组、关联查询，可以对数据进行增删改查）和复杂视图（和简单视图相反，只能对数据进行查询操作） 4. 插入数据时有可能出现数据污染，可以通过with check option解决 5. 删除和修改只能操作视图中存在的数据 6. 起了别名后只能用别名

约束：

什么是约束：约束就是给表字段添加的限制条件

主键约束+自增：primary key auto_increment

作用：唯一且非空

非空约束

not null 作用：该字段的值不能为null create table t_null(id int,age int not null); insert into t_null values(1,18); '成功' insert into t_null values(1,null); '失败'

唯一约束

unique 用于设置多用的用户名或者密码不重复出现，即值唯一。 作用：该字段的值不能重复 create table t_unique(id int,age int unique); insert into t_unique values(1,28); '成功' insert into t_unique values(2,28); '失败，不能重复'

默认约束

作用：给字段设置默认值 create table t_default(id int,age int default 20); insert into t_default (id) values(1); '默认值会生效' insert into t_default values(2,null); '默认值不会生效' insert into t_default values(3,30); '可以赋其他值'

外键约束

外键：用来建立关系的字段称为外键 外键约束：添加外键的字段，值可以为null，可以重复，但是不能是关联表中不存在的数据，外键指向的数据不能先删除，外键指向的表也不能先删除。 如何使用外键约束： use db6; 1、创建部门表 create table dept(id int primary key auto_increment,name varchar(10)); 2、创建员工表 格式介绍：constraint 约束名称 foreign key(外键名称) references 依赖的表名(依赖的字段名) create table emp(id int primary key auto_increment,name varchar(10),dept_id int,constraint fk_dept foreign key(dept_id) references dept(id)); 插入测试数据： insert into dept values(null,'神仙'),(null,'妖怪'); insert into emp values(null,'悟空',1); '插入成功' insert into emp values(null,'赛亚人',3); '插入失败' delete table dept where id=1; '删除失败' drop table dept; '删除表失败，有其他表依赖它' 由于添加外键约束后，会影响测试效率，所以工作中很少使用，一般都是通过java代码实现逻辑外键。

索引

什么是索引：索引是数据库中用来提高查询效率的技术，类似于目录。 为什么使用索引：如果不使用索引数据会零散的保存在磁盘块中，查询数据需要挨个遍历每一个磁盘快，直到找到数据为止，使用索引后会将磁盘块以树状结构保存，查询数据时会大大降低磁盘块的访问数量，从而提高查询效率。 有索引就一定好吗？如果表中的数据很少，使用索引反而会降低查询效率。 索引是越多越好吗？不是，索引会占用磁盘空间，只针对查询时常用的字段创建索引。 导入数据： source D:/item_backup.sql; 测试查询耗时： select * from item2 where title='100'; '0.86'

如何创建索引

格式：create index 索引名 on 表名(字段名(m)) 'm代表取title 前五个字符作为索引，一般情况下都不写' create index index_item_title on item2(title); 再次查询：select * from item2 where title='100'; '0.02'

索引分类

聚集索引：通过主键创建的索引称为聚集索引；聚集索引中保存数据，只要给表添加主键约束，则会自动创建聚集索引。 非聚集索引：通过非主键创建的索引称为非聚集索引，非聚集索引中没有数据 如何查看索引 格式：show index from 表名; show index from item2;

删除索引

格式：drop index 索引名 on 表名;

复合索引

通过多个字段创建的索引称为复合索引 格式：create index 索引名 on 表名(字段1，字段2,...); create index index_title_price on item2(title,price);

索引总结

1. 索引是用来提高查询效率的技术，类似目录 2. 因为索引会占用磁盘空间，所以不是越多越好 3. 因为数据量小的时候使用索引会降低查询效率所以不是有索引就一定好 4. 分类：聚集索引和非聚集索引 5. 通过多个字段创建的索引称为复合索引

事务：

数据库中执行同一业务多条SQL语句的工作单元，可以保证全部执行成功或全部执行失败

事务的ACID特性

ACID是保证数据库事务正确执行的四大基本要素： 1、Atomicty 原子性：说的是一个事务内所有操作共同组成一个原子包，保证SQL全部成功，或者全部失败。 2、Consistenc 一致性y：保证事务从一个一致状态 到 另一个一致状态，一致性与原子性是密切相关的. 3、Isolation 隔离性：多个事务之间互不影响,一个事务的执行不能被其他事务干扰。 4、Durablity 持久性：一个事务一旦提交，它对数据库中数据的改变应该是永久性的。

事务相关的SQL

1. 开启事务 begin; 2. 回滚 rollback; 3. 提交 commit; 4. 保存回滚点 savepoint s1; 5. 回滚到某个回滚点 rollback to s1; 6. 查看自动提交状态 show variables like '%autocommit%'; 7. 修改自动提交状态 set autocommit=0/1;

group_concat()一行显示数据

查询每一部门所有员工的姓名和工资 select deptno,group_concat(ename,'-',sal) from emp group by deptno; 查询每个部门员工姓名，要求每个部门只显示一行 select deptno,group_concat(ename) from emp group by deptno; '每个部门的员工显示在一行'

MySQL单行函数

1、CONCAT(str1,str2,...) 返回来自于参数连结的字符串。如果任何参数是NULL，返回NULL。 可以有超过2个的参数。一个数字参数被变换为等价的字符串形式。 select CONCAT('My', 'S', 'QL'); --'MySQL' select CONCAT('My', NULL, 'QL'); --> NULL 2 LENGTH(str) 返回字符串str的长度。 select LENGTH('text'); -> 4 3 LOCATE(substr,str) 和 POSITION(substr IN str) 返回子串substr在字符串str第一个出现的位置，如果substr不是在str里面，返回0. select LOCATE('bar', 'foobarbar'); -> 4 select LOCATE('xbar', 'foobar'); -> 0 LOCATE(substr,str,pos) 返回子串substr在字符串str第一个出现的位置，从位置pos开始。如果substr不是在str里面，返回0。 select LOCATE('bar', 'foobarbar',5); -> 7 4 INSTR(str,substr) 返回子串substr在字符串str中的第一个出现的位置。这与有2个参数形式的LOCATE()相同，除了参数 被颠倒。 select INSTR('foobarbar', 'bar'); -> 4 select INSTR('xbar', 'foobar');-> 0 5 LPAD(str,len,padstr) 返回字符串str，左面用字符串padstr填补直到str是len个字符长。 mysql> select LPAD('hi',4,'??'); -> '??hi' 6 RPAD(str,len,padstr) 返回字符串str，右面用字符串padstr填补直到str是len个字符长。 mysql> select RPAD('hi',5,'?'); -> 'hi???' 7 LEFT(str,len) 返回字符串str的最左面len个字符。 mysql> select LEFT('foobarbar', 5); -> 'fooba' 8 RIGHT(str,len) 返回字符串str的最右面len个字符。 mysql> select RIGHT('foobarbar', 4); -> 'rbar' 9 SUBSTRING(str,pos,len) 从字符串str返回一个len个字符的子串，从位置pos开始。使用FROM的变种形式是ANSI SQL92语法。 mysql> select SUBSTRING('Quadratically',5,6); -> 'ratica' 10 LTRIM(str) 返回删除了其前置空格字符的字符串str。 mysql> select LTRIM(' barbar'); -> 'barbar' 11 RTRIM(str) 返回删除了其拖后空格字符的字符串str。 mysql> select RTRIM('barbar '); -> 'barbar' 12 TRIM(str) mysql> select TRIM(' bar '); -> 'bar' 13 SPACE(N) 返回由N个空格字符组成的一个字符串。 mysql> select SPACE(6); -> ' ' 14 REPLACE(str,from_str,to_str) 返回字符串str，其字符串from_str的所有出现由字符串to_str代替。 mysql> select REPLACE('www.mysql.com', 'w', 'x'); -> 'xxx.mysql.com' 15 REPEAT(str,count) 返回由重复countTimes次的字符串str组成的一个字符串。 如果count <= 0，返回一个空字符串。如果str或count是NULL，返回NULL。 mysql> select REPEAT('MySQL', 3); -> 'MySQLMySQLMySQL' 16 REVERSE(str) 返回颠倒字符顺序的字符串str。 mysql> select REVERSE('abc'); -> 'cba' 17 INSERT(str,pos,len,newstr) 返回字符串str，在位置pos起始的子串且len个字符长得子串由字符串newstr代替。 mysql> select INSERT('Quadratic', 3, 4, 'What'); -> 'QuWhattic' 18 ELT(N,str1,str2,str3,...) 如果N= 1，返回str1，如果N= 2，返回str2，等等。如果N小于1或大于参数个数，返回NULL。 mysql> select ELT(1, 'ej', 'Heja', 'hej', 'foo'); -> 'ej' mysql> select ELT(4, 'ej', 'Heja', 'hej', 'foo'); -> 'foo' 19 FIELD(str,str1,str2,str3,...) 返回str在str1, str2, str3, ...清单的索引。如果str没找到，返回0。FIELD()是ELT()反运算。 mysql> select FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo'); -> 2 mysql> select FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo'); -> 0 20 LOWER(str) 返回字符串str，根据当前字符集映射(缺省是ISO-8859-1 Latin1) 把所有的字符改变成小写。该函数对多字节是可靠的。 mysql> select LCASE('QUADRATICALLY'); -> 'quadratically' 21 UPPER(str) 返回字符串str，根据当前字符集映射(缺省是ISO-8859-1 Latin1) 把所有的字符改变成大写。该函数对多字节是可靠的。 mysql> select UCASE('Hej'); -> 'HEJ'

MySQL时间函数

1 DAYOFWEEK(date) 　　返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)。这些索引值对应于ODBC标准。 　　mysql> select DAYOFWEEK('1998-02-03'); 2 WEEKDAY(date) 　　返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。 　　mysql> select WEEKDAY('1997-10-04 22:23:00'); 3 DAYOFMONTH(date) 　　返回date的月份中日期，在1到31范围内。 　　mysql> select DAYOFMONTH('1998-02-03'); 4 DAYOFYEAR(date) 　　返回date在一年中的日数, 在1到366范围内。 　　mysql> select DAYOFYEAR('1998-02-03'); 5 MONTH(date) 　　返回date的月份，范围1到12。 　　mysql> select MONTH('1998-02-03'); 6 DAYNAME(date) 　　返回date的星期名字。 　　mysql> select DAYNAME("1998-02-05"); 7 MONTHNAME(date) 　　返回date的月份名字。 　　mysql> select MONTHNAME("1998-02-05"); 8 QUARTER(date) 　　返回date一年中的季度，范围1到4。 　　mysql> select QUARTER('98-04-01'); 9 WEEK(date) 　　　 10 WEEK(date,first) 　　对于星期天是一周的第一天的地方，有一个单个参数，返回date的周数， 范围在0到52。2个参数形式WEEK()允许你指定星期是否开始于星期天或星期一。 如果第二个参数是0，星期从星期天开始，如果第二个参数是1，从星期一开始。 　　mysql> select WEEK('1998-02-20'); 　　 　　mysql> select WEEK('1998-02-20',0); 　 　　mysql> select WEEK('1998-02-20',1); 　　 11 YEAR(date) 　　返回date的年份，范围在1000到9999。 　　mysql> select YEAR('98-02-03'); 　 12 HOUR(time) 　　返回time的小时，范围是0到23。 　　mysql> select HOUR('10:05:03'); 13 MINUTE(time) 　　返回time的分钟，范围是0到59。 　　mysql> select MINUTE('98-02-03 10:05:03'); 14 SECOND(time) 　　回来time的秒数，范围是0到59。 　　mysql> select SECOND('10:05:03'); 15　PERIOD_ADD(P,N) 　　增加N个月到阶段P（以格式YYMM或YYYYMM)。以格式YYYYMM返回值。注意阶段参数P不是日期值。 　　mysql> select PERIOD_ADD(9801,2); 16 PERIOD_DIFF(P1,P2) 　　返回在时期P1和P2之间月数，P1和P2应该以格式YYMM或YYYYMM。注意，时期参数P1和P2不是日期值。 　　mysql> select PERIOD_DIFF(9802,199703); 　　 17 DATE_ADD(date,INTERVAL expr type) 　　　 18 DATE_SUB(date,INTERVAL expr type) 　　　 19 ADDDATE(date,INTERVAL expr type) 　　　 20 SUBDATE(date,INTERVAL expr type) SECOND 秒 SECONDS 　　MINUTE 分钟 MINUTES 　　HOUR 时间 HOURS 　　DAY 天 DAYS 　　MONTH 月 MONTHS 　　YEAR 年 YEARS 　　MINUTE_SECOND 分钟和秒 "MINUTES:SECONDS" 　　HOUR_MINUTE 小时和分钟 "HOURS:MINUTES" 　　DAY_HOUR 天和小时 "DAYS HOURS" 　　YEAR_MONTH 年和月 "YEARS-MONTHS" 　　HOUR_SECOND 小时, 分钟， "HOURS:MINUTES:SECONDS" 　　DAY_MINUTE 天, 小时, 分钟 "DAYS HOURS:MINUTES" 　　DAY_SECOND 天, 小时, 分钟, 秒 "DAYS HOURS:MINUTES:SECONDS" 例如： select adddate('1997-12-31',interval 1 month); select date_add('1997-12-31',interval 1 month); 21 TO_DAYS(date) 　　给出一个日期date，返回一个天数(从0年的天数)。 0000 　　mysql> select TO_DAYS('1997-10-07'); 22 FROM_DAYS(N) 　　给出一个天数N，返回一个DATE值。 　　mysql> select FROM_DAYS(729669); 23 DATE_FORMAT(date,format) 　　根据format字符串格式化date值。下列修饰符可以被用在format字符串中: %M 月名字(January……December) 　　%W 星期名字(Sunday……Saturday) 　　%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。） 　　%Y 年, 数字, 4 位 　　%y 年, 数字, 2 位 　　%a 缩写的星期名字(Sun……Sat) 　　%d 月份中的天数, 数字(00……31) 　　%e 月份中的天数, 数字(0……31) 　　%m 月, 数字(01……12) 　　%c 月, 数字(1……12) 　　%b 缩写的月份名字(Jan……Dec) 　　%j 一年中的天数(001……366) 　　%H 小时(00……23) 　　%k 小时(0……23) 　　%h 小时(01……12) 　　%I 小时(01……12) 　　%l 小时(1……12) 　　%i 分钟, 数字(00……59) 　　%r 时间,12 小时(hh:mm:ss [AP]M) 　　%T 时间,24 小时(hh:mm:ss) 　　%S 秒(00……59) 　　%s 秒(00……59) 　　%p AM或PM 　　%w 一个星期中的天数(0=Sunday ……6=Saturday ） 　　%U 星期(0……52), 这里星期天是星期的第一天 　　%u 星期(0……52), 这里星期一是星期的第一天 　　%% 一个文字“%”。 　　所有的其他字符不做解释被复制到结果中。 　　mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y'); --> 'Saturday October 1997' 　　mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s'); --> '22:23:00' 　　mysql> select DATE_FORMAT('1997-10-04 22:23:00', 　　'%D %y %a %d %m %b %j'); --> '4th 97 Sat 04 10 Oct 277' 　　mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w'); --> '22 22 10 10:23:00 PM 22:23:00 00 6' 　　MySQL3.23中，在格式修饰符字符前需要%。在MySQL更早的版本中，%是可选的。 24 TIME_FORMAT(time,format) 　　这象上面的DATE_FORMAT()函数一样使用，但是format字符串只能包含处理小时、 分钟和秒的那些格式修饰符。其他修饰符产生一个NULL值或0 25 Mysql取系统函数： Select curtime(); Select curdate(): Select sysdate(): select now();

MYSQL流程控制函数

1、IFNULL(expr1,expr2) 如果 expr1 为非 NULL 的，IFNULL() 返回 expr1，否则返回 expr2。 2、NULLIF(expr1,expr2) 如果 expr1 = expr2 为真，返回 NULL，否则返回 expr1 3、IF(expr1,expr2,expr3) 如果 expr1 为真(expr1 <> 0 以及 expr1 <> NULL)，那么 IF() 返回 expr2，否则返回 expr3