csdn入门测试教程------mysql数据库命令大全以及常用命令 安装教程 基础知识 附【练习题】

前言：

mysql数据库是每一个测试小白入行必学的一个知识，学会这些命令呢也能让你在玩转数据库的时候给你提供很多的便利也是很不错的，最后希望各位小伙伴学有所成，心想事成咯，

废话不多说直接上正文。

【文章末尾给大家留下了大量的粉丝福利哈】

数据库常用命令

启动/暂停

net start mysql; 启动
net stop mysql; 暂停

使用root账户登录

mysql -uroot -p123456
root是默认用户,超级管理员
-p后面接密码

显示所有数据库

show databases; 

使用某个数据库

use 数据库名

修改密码

1.使用mysql数据库
use mysql
2.修改密码(将root用户密码修改为123456)
update mysql.user set authentication_string=password('123456') where user='root';
3.刷新
flush privileges;

创建用户

create user 'username'@'host' identified by 'password';

给用户授权

grant privileges on databasename.tablename to 'username'@'host';

• privileges:用户操作权限,如select,insert,update等,如果要授予所有权限使用all

• databasename:数据库名

• tablename:表名,如果要授予用户对所有数据库和表相应操作权限可以使用*表示,如*.*

如:我要username用户,对所有数据库和表有查和修改功能

grant select,update on *.* to 'username'@'host';

注意:以上授权用户不能给其他用户授权,如果想要给其他用户授权,用以下命令:

grant privileges on databasename.tablename to 'username'@'host' with grant option;

创建一个数据库

• 方式1:

  create database 数据库名;

• 方式2:

  create database if not exists 数据库名 default 默认编码集(utf8)

删除一个数据库

• 方式1:

  drop database 数据库名

• 方式2:

  drop database if exists 数据库名

什么是SQL

SQL由四部分组成

DDL:创建和删除结构

DML:对表和视图进行插入,删除,更新操作

DQL:对表和试图进行查询操作

TCL:使用事务管理DML操作

mysql的数据类型

理论实战

• 经常变化的字段用varchar

• 知道固定长度用char

• 超过255字节只能用varchar和text

• 能用varchar的地方不用text

• 能够用数字类型的字段尽量选择数字类型而不用字符串类型,这会降低查询和连接的性能,并会增加存储开销.

  这是因为引擎在处理查询和连接会逐个比较字符串中的每一个值,而对于数字类型只需比较一次就够了.

• 同一张表出现多个大字段,能合并时尽量合并,不能合并时考虑分表

DDL操作

创建表

create table 表名(
    列名1 数据类型1,
    列名2 数据类型2,
    列名3 数据类型3,
    ....
    列名称n 数据类型n
)defualt charset=字符集编码(utf8),engine=指定表的存储引擎(innodb)--可选

查看表结构

• 方式1

  desc 表名称;

• 方式2

  show create table 表名称; -- 可以看的建表语句

修改表结构

基本语法:arlter table 表名

1. 向表中添加一列

   alter table 表名 add column 列名称 数据类型;
   ​
   如:向student表新增加一列stu_height学生身高
   alter table student add column stu_height int(11);

2. 删除某一列

   alter table 表名 drop column 列名称
   ​
   例如：删除student表中的学生身高
   alter table student drop column stu_height;

3. 修改列名称和数据类型

   alter table student change column 原列名 新列名 新数据类型;
   ​
   例如修改stu_sex 为 student_sex 数据类型修改为 varchar(30)
   alter table student change column stu_sex student_sex varchar(30);

4. 修改某一列的数据类型

   alter table student modify column 原来的列名 新的数据类型;
   

   例如：将学生年龄修改为int类型
   alter table student modify column stu_age int(11);

删除表

1. 直接删除

   drop table 表名称;
   

2. 删除之前先判断表是否存在

   drop table if exists 表名称;
   

显示当前库的所有表

show tables;

DML操作

添加数据

1. 单个添加

   插入所有数据时

   insert into 表名称 values (值1,值2,……);
   insert into student values(101,'老王',0,'足球',31);
   

   注意:在插入所有的数据时values后面的括号必须填满所有列,不然会报错,如果你不想插入全部的列,就可以在表名称后面指定列名称.如:insert into 表名称 (列名1,列名2) values(列名1值,列名2值)

2. 批量添加

   行与行之间使用半角逗号分离，value关键字只定义一次

   insert into student(id,stu_name,stu_sex,stu_hobby,stu_age)
   values
   (108,'老王',1,'Basketball',39),(1031,'老张',1,'Basketball',39);
   

修改数据

1. 批量修改

   update 表名称 set 列名称=值;
   

   例如：student表的年龄全部修改为50
   update student set stu_age=50;

2. 单个修改

   如果只想修改某一行的数据，需要加上条件

   例如：我只想修改id为1031的年龄为60 where 表示行过滤
   update student set stu_age=60 where id=1031;
   

3. 单个修改多个值

   例如:修改id为1031的年龄为60,姓名为大大
   update student set stu_age=60,stu_name='大大' where id=1031;
   

删除数据

1. 删除一个

   删除一条数据
   delete from 表名 where 条件;
   例如：删除id为108的student数据
   delete from student where id=108;

2. 删除全部

   delete from 表名;
   例如：删除student表所有数据
   -- 开发时最好不要用这个,可以定义一个字段用来表示是否删除,0表示存在1表示要删除,然后查询时使用where条件判断字段进行查询出来.
   delete from studnet;

3. truncate关键字

   truncate是DDL,删除所有数据,不支持where ,不能回滚

   delete是DML,可以删除部分数据,因为支持where,可以回滚

   总体上truncate的效率比delete高,它们都只能删除数据不能删除表结构,只要drop才能删除表结构.

4. 

数据完整性

因为上面的表缺乏安全性,会有重复的id等数据这些都是脏数据.

我们要给表定义规则,确保表中的数据有效性,一致性,安全性,尽最大可能减少脏数据,重复的的数据

主键约束

主键:主要的关键字，一个表如果创建了主键那么该行的所有数据必须在表中唯一.设置了主键约束那么这个字段不能为空,不能重复

如:身份证必须是唯一的且不能为空,那么就把身份证设置为主键

总结:非空且唯一

• 设计原则:一般主键为数值类型,呈递增趋势.如果不想每次插入主键可以不手动添加可以设置为自动增长,自动增长从1开始,如果手动添加后设置了一个新值,那么下次自动设置时主键会从你手动设置的值往后递增.

  语法:

  create table tb_class(
  	id int(11) auto_increment, -- id是主键列,不用显示插入值会自动增长
      class_name varchar(30),
      class_desc varchar(100),
      primary key(id)    -- 为tb_class表的id设置主键约束
  )
  注意： MySQL 数据库 auto_increment自动增长和主键primary key 是配套的不能单独使用
  

非空约束

某字段的值不允许为空,如果其字段没有设置值将报错

create table tb_class(
	id int(11) auto_increment, -- id是主键列,不用显示插入值会自动增长
    class_name varchar(30) not null,-- not null非空约束
    class_desc varchar(100),
    primary key(id)    -- 为tb_class表的id设置主键约束
)

唯一约束

制定一个规则让某一列的数据必须唯一

特征:某列数据可以为空,但必须唯一,可以有多个null值,一般设置了唯一约束那么最高设置非空约束,这样能提升索引效率.

例如：学生的手机号码
create table tb_1(
    phone int unique -- unique
)

默认约束

为某一列制定一个默认规则

如:性别默认为0
create table tb_1(
    phone int unique,
	sex int DEFAULT 0 -- default 0
)

外键约束

外部的关键字叫外键,通常为多张表建立联系,确保表与表之间的安全性,一致性,能够减少数据冗余

前面个几个约束建立在一张表中

外键约束:多张表建立的约束,如果一张表的某个字段建立了外键约束,这个字段的取值就必须是你所依赖的主表中主键的值.如果取其他值会报错,当然如果主表的主键被其他从表所引用了这则个字段就不能删除了

场景：创建一个tb_student表，为其添加默认约束、唯一约束、非空约束、外键约束

create table tb_student(
id int(11) auto_increment, -- id自动增长
stu_name varchar(50) not null, -- name不能为空
stu_mobile varchar(20) unique, -- mobile唯一不能重复
stu_sex bit default 1, -- 设置sex默认为1
class_id int(11) not null, -- 设置class_id不能为空
primary key(id), -- 设置id为主键
foreign key(class_id) references tb_class(id) -- 学生表的class_id建立外键去关联tb_class表的主键id,建立外键时一定要保证外键依赖的主表要先创建

)engine=innodb,default charset utf8;

约束总结

约束分为两类:

1. 行级别约束: 主键约束,外键约束

2. 列级别约束: 非空约束,唯一约束,默认约束

外键约束小结:

• foreign key(外键列) references 主表(主键列)

• references关键字的右边是主表的主键列,左边是从表的外键列,该关键字在主表和从表之间建立了联系

创建表小结:没有外键的表是主表,有外键的表是从表.先创建主表后在创建从表(有外键的表是从表)

插入数据小结:先插入主表数据后插入从表数据

删除数据小结:先删除从表数据后删除主表数据

表关系

一对一

数据表间一对一关系的表现有两种，一种是外键关联，一种是主键关联

一对多/多对一

存在最普遍的映射关系，简单来讲就如班级与学生的关系；

一对多：从班级度来说一个班级拥有多个学生 即为一对多

多对一：从学生角度来说多个学生属于一个班级 即为多对一

-- 班级表(主表)
create table tb_class(
	id int(11) auto_increment, -- 编辑编号
    class_name varchar(30) not null, -- 班级名称
    class_desc varchar(100), -- 班级描述
    primary key(id) -- 为class表设置主键约束
)engine=innodb,default charset utf8;

-- 学生表(从表)
create table tb_student(
id int(11) auto_increment,
stu_name varchar(10) not null,
stu_mobile varchar(50) unique,
stu_sex bit default 1,
class_id int(11) not null,
primary key(id),
foreign key(class_id) references tb_class(id) -- 设置外键class_id 关联tb_class表的主键
)engine=innodb,default charset utf8;

多对多

例如:学生与选修课之间的关系,一个学生可以选多门课程,一门课程可以被多个学生选择

通常多对多关系我们一般采用中间表处理,将多对多转换为两个一对多的关系.

代码示例:

-- 学生表
create table tb_student(
	id int(11) auto_increment,
    stu_name varchar(50) not null,
    stu_mobile varchar(20) unique,
    stu_sex bit default 1,
    class_id int(11) not null,
    primary key(id),
    foreign key(class_id) references tb_class(id) -- 设立外键表明这个表是tb_class的从表
)engine=innodb,default charset utf8;

-- 课程表
create table tb_course(
id int(11) auto_increment,
course_name varchar(30) not null,
primary key(id)
)engine=innodb,default charset utf8;

-- 学生课程表(中间表)
create table tb_student_course(
sut_id int(11), -- 学生id
course_id int(11), -- 课程id
course_score int(11), -- 课程成绩
primary key(stu_id,course_id), -- 复合主键
foreign key(stu_id) references tb_student(id),-- 将学生id设置为外键关联tb_student学生表的主键
foreign key(course_id) references tb_course(id)-- 将课程id设置为外键关联tb_course课程表的主键
)engine=innodb,default charset utf8;

自行设计一个员工系统

-- 部门表(dept)
drop table if exists dept;
create table dept(
	deptNo int(11) not null comment'部门编号',
    dName varchar(20) not null comment'部门名称', 
    loc varchar(20) comment'部门所在的位置', 
    primary key(deptNo) -- 主键约束
)engine=innodb,default charset utf8 comment='部门表';

-- 员工表(emp)
drop table if exists emp;
create table emp(
empNo int not null comment'雇员编号',
ename varchar(10) not null comment'雇员姓名',
job varchar(10) not null comment'职位',
mgr int(11) null default null comment'雇员对应的领导编号',
hiredate date null default null comment'雇员的雇佣日期',
sal decimal(7,2) null default null comment'基本工资',
comm decimal(7,2) null default null comment'奖金,佣金',
deptno int not null comment'雇员所在的部门编号',
primary key(empNo),-- 设置主键约束
foreign key(deptno) references dept(deptNo) -- 为deptno设置为外键关联表dept主键
)engine=innodb character set=utf8 comment'员工表';

-- 工资等级表(salgrade)
drop table if exists salgrade;
create table salgrade(
grade int not null comment'工资等级',
losal decimal(7,2) not null comment'此等级的最低工资',
hisal decimal(7,2) not null comment'此等级的最高工资',
primary key(grade)
)engine=innodb character set=utf8 comment='工资等级表';

初始化数据脚本

-- 部门表插入数据
insert into dept values(10,'财务部','武汉');
insert into dept values(20,'研发部','武汉');
insert into dept values(30,'销售部','深圳'),
						(40,'业务部','上海');
-- 员工表插入数据
insert into emp values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800.00, null, 20),
 					(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20',1600.00, 300.00, 30),
  					(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22',1250.00, 500.00, 30),
 					 (7566, 'JONES', 'MANAGER', 7839, '1981-04-02',2975.00, null, 20),
   					(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28',1250.00, 1400.00,30),
   					(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, null, 30),
   					 (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.00, null,10),
   					 (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000.00, null, 20),
   					  (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000.00, null,10),
      				 (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.00, 0.00,30),
     				  (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100.00, null,20),
                       (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.00, null, 30),
                       (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.00, null, 20),
                        (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.00, null, 10);
-- 工资等级表插入数据
insert into salgrade  values (1, 700, 1200)
 ,(2, 1201, 1400)
 ,(3, 1401, 2000)
 ,(4, 2001, 3000)
 ,(5, 3001, 9999);

DQL查询操作

单表数据查询

简单查询

select * from emp; -- 查询表emp中的全部数据
select * from dept; -- 查询表dept中的全部数据
select empno, ename,sal,job  from emp; -- 查询emp表中的empno,ename,sal,job四列的的全部数据

使用算术表达式

select empno, ename,sal,sal*1.08 from emp;-- 查询emp表的empno,ename,sal三个字段,最后在查询sal字段*1.08后的值最后显示一列,字段名为sal*1.08
select empno, ename,sal, sal*12 from emp;-- 查询emp表的empno,ename,sal三个字段,最后在查询sal字段*12后的值最后显示一列,字段名为sal*12
select empno, ename,sal, sal*12 +1000 from emp;-- 查询emp表的empno,ename,sal三个字段,最后在查询sal字段*12+1000后的值最后显示一列,字段名为sal*12+1000
注：在Select语句中，对数值型数据可以使用算术运算符创建表达式

使用字段别名

上面的使用sal*12,英文做为字段名并不是我们想要的在某些场景中,所以可以给字段重新命名

-- 一下三种方式都可以重命名
-- 字段名 as 重命名(AS可以省略)-->字段名 重命名
-- 别名如果含有空格或其他特殊字符或大小写敏感，需用双引号引起来:字段名 '重命名'

select empno as 员工编号, ename 员工姓名, sal12 年薪 from emp;
select empno, ename "Ename", sal12 "Anual Salary" from emp;
select sal*12+5000 as "年度工资(加年终奖)" from emp;

去重查询

如我们想要看emp表中的部门id编号有那些,而查询全部会有重复的部门id,影响我们的可读性.

所以可以使用distinct关键字去除重复的

select distinct deptno from emp;

-- DISTINCT的作用范围是后面所有字段的组合
select distinct deptno,job from emp;

排序查询

使用order by子句对查询结果进行排序,排序方式包括升序(asc,不写默认),降序(desc)

select empno, ename, sal from emp order by sal;-- 查询三个字段按照sal升序排序
select empno, ename, sal from emp order by sal desc ;  -- 降序排序
-- 按多字段排序
select deptno, empno, ename, sal from emp order by deptno, sal;
-- 使用字段别名排序
select empno, ename, sal*12 annsal from emp order by annsal;

分页查询

关键字:limit

-- 查询前三条员工信息
select * from emp limit 3;-- limit 后面跟1个参数 ，例如 limit 3 此时表示最多返回3行

-- 查询第三条3条到第6条的客户信息
select * from emp limit 3,6; -- limit 从第3条开始查6条数据 第一个参数是从第几个开始,第二个参数查几条数据.
-- 分页查询，每页5条
-- 第1页
select * from emp limit 0, 5;
-- 第2页
select * from emp limit 5, 5;
-- 第3页
select * from emp limit 10, 5;
select * from emp limit 15, 5;
select * from emp limit 20, 5;
-- 找规律，第一个数字与页数的关系
-- 要查询第几页：pageNumber
-- 每页显示条数：pageSize
-- 每页查询起始位置：start
start=(pageNumber-1)*pageSize;
-- -- 每页显示3条，查询第4页的数据
select * from emp limit 9,3;

合并查询

将多个select语句合并为一个select语句,关键字有union和union all

union:如果有重复过滤掉重复的数据.

union all:不管是否有重复的,全部合并.

-- 过滤掉了重复的
select deptno from emp where job='clerk'
 union
select deptno from emp where job='manager';
-- 不过滤,直接合并
select deptno from emp where job='clerk'
 union all
select deptno from emp where job='manager';

where子句

用法

作用：指定查询条件使用where子句

select * from emp where deptno=10;
select * from emp where ename = 'JACK';
select * from emp where hiredate = '2020-12-12';

注意：

• 字符串和日期值要用单引号扩起来

• 字符串大小写敏感

• 日期值格式敏感，缺省的日期格式是'YYYY-MM-DD HH:mm:ss'

查询条件中的比较运算符

MySQL支持如下比较运算符： > >= < <= != <>

between...and..:介于两值之间(包括两值)

in:出现在集合中

like:模糊查询

is null:为空值

注意:mysql中的=运算符用来判断表达式是否相等,它没有==

java使用!=来表示不等于,mysql也支持,但不要使用效率低,mysql使用<>表示不等于

select * from emp where sal > 2900; -- 查询表emp中sal大于2900的全部员工
select * from emp where deptno <> 30;-- 查询表emp中deptno不等于30的全部员工
select * from emp where sal between 1600 and 2900;-- 查询emp表中sal在1600(包括)到2900(包括)的所有员工
select * from emp where ename in('SMITH','CLARK','KING');-- 查询表emp中ename有在其中(Smith,clark,king)的全部员工

使用like运算符进行模糊查询

符号	作用	举例
%	表示0个或多个字符	select * from emp where ename like 'S%';
_	表示1字符	select * from emp where ename like '_A%';
escape	查找特殊符号	select * from emp where ename like '/_A%' escape '/' 把/后面的字符不要当做通配符,就是查找第一个字符为下划线的ename

使用IS NULL运算符进行空值判断

select * from emp where comm is null;
select * from emp where comm is not null;

查询条件中可以使用逻辑运算符

-- 在emp表中查询部门编号等于10并且sal大于1000 and(相当与Java的并且&&)
select * from emp where deptno=10 and sal>1000;
-- 在emp表中查询部门编号为10或者职位是clerk的员工 or(相当与java的或者||)
select * from emp where deptno=10 or job='clerk';
-- 在emp中查询工资不在800,1500,2000的员工
select * from emp where sal not in(800,1500,2000);

SQL优化问题:

• AND: 把检索结果较少的条件放到后面

• OR: 把检索结果较多的条件放到后面

聚合函数

使用函数可以大大提高select语句操作数据库的能力,它给数据的转换和处理提供了方便.函数只是将取出的数据进行处理,不会改变数据库中的值.

函数分类:

单行函数

数学函数

• abs(x) 返回x的绝对值

• pi() 返回圆周率,默认显示六位小数

• sqrt(x) 返回非负数的x的二次方根

• mod(x,y) 返回x被y除后的余数

• ceil(x)，ceiling(x) 返回不小于x的最小整数

• floor(x) 返回不大于x的最大整数

• round(x) round(x,y) 前者返回最接近于x的整数,即对x进行四舍五入;后者返回最接近x的数,其值保留到小数点后面y位,若y为负值,则将保留到x到小数点左边y位.

• sign(x) 返回参数x的符号,-1表示负数,0表示0,1表示正数

• pow(x,y)和power(x,y) 返回x的y次乘方的值

• exp(x) 返回e的x乘方后的值

• log(x) 返回x的自然对数,x相对于基数e的对数

• log10(x) 返回x的基数为10的对数

• radians(x) 返回x由角度转化为弧度的值

• degrees(x) 返回x由弧度转化为角度的值

• SIN(x)、ASIN(x) 前者返回x的正弦，其中x为给定的弧度值；后者返回x的反正弦值，x为正弦

• COS(x)、ACOS(x) 前者返回x的余弦，其中x为给定的弧度值；后者返回x的反余弦值，x为余弦

• TAN(x)、ATAN(x) 前者返回x的正切，其中x为给定的弧度值；后者返回x的反正切值，x为正切

• COT(x) 返回给定弧度值x的余切

• rand(x) 返回0-1之间的随机小数

字符串函数

• char_length(str) 计算字符串字符个数

• concat(s1,s2,.....) 返回连接参数产生的字符串,一个或多个待拼接的内容,任意一个为null则返回值为null

• concat_ws(x,s1,s2.,,,,) 返回多个字符串拼接之后的字符串,每个字符串之间有一个x

• insert(s1,x,len,s2) 返回字符串s1，其子字符串起始于位置x，被字符串s2取代len个字符

• lower(str)和lcase(str)、upper(str)和ucase(str)前两者将str中的字母全部转换成小写，后两者将字符串中的字母全部转换成大写

• left(s,n)、right(s,n) 前者返回字符串s从最左边开始的n个字符，后者返回字符串s从最右边开始的n个字符

• lpad(s1,len,s2)、rpad(s1,len,s2)前者返回s1，其左边由字符串s2填补到len字符长度，假如s1的长度大于len，则返回值被缩短至len字符；前者返回s1，其右边由字符串s2填补到len字符长度，假如s1的长度大于len，则返回值被缩短至len字符

• ltrim(s)、rtrim(s)前者返回字符串s，其左边所有空格被删除；后者返回字符串s，其右边所有空格被删除

• trim(s)返回字符串s删除了两边空格之后的字符串

• trim(s1 FROM s)删除字符串s两端所有子字符串s1，未指定s1的情况下则默认删除空格

• repeat(s,n) 返回一个由重复字符串s组成的字符串，字符串s的数目等于n

• space(n) 返回一个由n个空格组成的字符串

• replace(s,s1,s2)返回一个字符串，用字符串s2替代字符串s中所有的字符串s1

• strcmp(s1,s2)若s1和s2中所有的字符串都相同，则返回0；根据当前分类次序，第一个参数小于第二个则返回-1，其他情况返回1

• substring(s,n,len)、MID(s,n,len)两个函数作用相同，从字符串s中返回一个第n个字符开始、长度为len的字符串,不写长度默认到最后.

• locate(str1,str)、position(str1 IN str)、instr(str,str1)三个函数作用相同，返回子字符串str1在字符串str中的开始位置（从第几个字符开始）

• reverse(s) 将字符串s反转

• elt(N,str1,str2,str3,str4,...)返回第N个字符串

日期和时间函数

• CURDATE()、CURRENT_DATE()将当前日期按照"YYYY-MM-DD"或者"YYYYMMDD"格式的值返回，具体格式根据函数用在字符串或是数字语境中而定

• CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE()这四个函数作用相同，返回当前日期和时间值，格式为"YYYY_MM-DD HH:MM:SS"或"YYYYMMDDHHMMSS"，具体格式根据函数用在字符串或数字语境中而定

• MONTH(date)和MONTHNAME(date)前者返回指定日期中的月份，后者返回指定日期中的月份的名称

• WEEK(d)、WEEKOFYEAD(d)前者计算日期d是一年中的第几周，后者计算某一天位于一年中的第几周

• DAYOFYEAR(d)、DAYOFMONTH(d)前者返回d是一年中的第几天，后者返回d是一月中的第几天

• EXTRACT(type FROM date)从日期中提取一部分，type可以是YEAR、YEAR_MONTH、DAY_HOUR、DAY_MICROSECOND、DAY_MINUTE、DAY_SECOND

• TimeStampDiff(间隔类型，前一个日期，后一个日期)

  例如距离现在差了多少年：TimeStampDiff(year, 前一个日期, now())

  间隔类型有：second秒，minute分，hour时，day天，week周，month月，quarter季度，year年

• DATE_ADD(date,INTERVAL expr type)、ADD_DATE(date,INTERVAL expr type)返回将起始时间加上expr type之后的时间，比如DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 SECOND)表示的就是把第一个时间加1秒

条件判断函数

• IF(expr,v1,v2)如果expr是TRUE则返回v1，否则返回v2

• IFNULL(v1,v2)函数 如果v1的值不为NULL，则返回v1，否则返回v2。

• CASE expr WHEN v1 THEN r1 [WHEN v2 THEN v2] [ELSE rn] END 如果expr等于某个vn，则返回对应位置THEN后面的结果，如果与所有值都不想等，则返回ELSE后面的rn

系统信息函数

（1）VERSION()查看MySQL版本号

（2）CONNECTION_ID()查看当前用户的连接数

（3）USER()、CURRENT_USER()、SYSTEM_USER()、SESSION_USER()查看当前被MySQL服务器验证的用户名和主机的组合，一般这几个函数的返回值是相同的

（4）CHARSET(str)查看字符串str使用的字符集

（5）COLLATION()查看字符串排列方式

加密函数

（1）PASSWORD(str)从原明文密码str计算并返回加密后的字符串密码，注意这个函数的加密是单向的（不可逆），因此不应将它应用在个人的应用程序中而应该只在MySQL服务器的鉴定系统中使用

（2）MD5(str)为字符串算出一个MD5 128比特校验和，改值以32位十六进制数字的二进制字符串形式返回

（3）ENCODE(str, pswd_str)使用pswd_str作为密码，加密str

（4） DECODE(crypt_str,pswd_str)使用pswd_str作为密码，解密加密字符串crypt_str，crypt_str是由ENCODE函数返回的字符串

格式化函数

（1）FORMAT(x,n)将数字x格式化，并以四舍五入的方式保留小数点后n位，结果以字符串形式返回

（2）CONV(N,from_base,to_base)不同进制数之间的转换，返回值为数值N的字符串表示，由from_base进制转换为to_base进制

（3）INET_ATON(expr)给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数，地址可以使4或8比特

（4）INET_NTOA(expr)给定一个数字网络地址（4或8比特），返回作为字符串的该地址的点地址表示

（5）BENCHMARK(count,expr)重复执行count次表达式expr，它可以用于计算MySQL处理表达式的速度，结果值通常是0（0只是表示很快，并不是没有速度）。另一个作用是用它在MySQL客户端内部报告语句执行的时间

多行函数

对一组数据进行运算，针对一组数据（多行记录）只返回一个结果，也称分组函数。

常用的多行函数

• 数值型:sum 求和 avg() 求平均值

• 任何类型:count() 计数 max()最大值 min() 最小值

多行函数与空值

多行函数除了count(*)之外,都跳过空值,而处理非空值

select count(comm),sum(comm),avg(comm) from emp; -- comm为4

可使用IF()函数强制多行函数处理空值

select count(if(comm is null,0,comm)),sum(if(comm is null,0,comm)),avg(if(comm is null,0,comm)) from emp;

group by 子句

将表中数据分成若干小组

语法格式:

select column, group_function(column)
from table
[where condition]
[group by group_by_expression]
[order by column];

-- 查询部门id,平均工资在emp表中按照部门id分组
select deptno,avg(sal) from emp group by deptno;-- 使用deptno分组,每一组计算avg
select  deptno, count(*),avg(sal) from emp group by deptno；-- 使用deptno分组每一组显示count,和avg

注意:出现在SELECT列表中的字段，如果不是包含在多行函数中，那么该字段必须同时在GROUP BY子句中出现。

having 子句

对分组查询的结果进行过滤,要使用having从句.

having从句过滤分组后的结果，它只能出现在group by从句之后，而where从句要出现在group by从句之前。

where过滤行，having过滤分组。having支持所有where操作符。

语法格式:

select column, group_function(column)
from table
[where condition]
[group by  group_by_expression]
[having group_condition]
[order by column];

执行过程:

from-->where-->group by-->having-->select-->order by

先从那个表查,有什么条件,按照什么分组,分组后的过滤,查询之后排序

-- 列出平均工资大于8000元的部门ID
select deptno,avg(sal) from emp group by deptno having avg(sal)>800;

思考练习:

-- 统计人数小于4的部门的平均工资。
select deptno,count(deptno) 人数,avg(sal) from emp group by deptno having count(deptno)<4;
-- 统计各部门的最高工资，排除最高工资小于8000的部门。
select deptno,max(sal) 最高工资 from emp group by deptno having max(sal)>2900;
-- 显示部门编号大于10 的部门的人数，要求人数大于3
select deptno,count(deptno) from emp group by deptno having deptno>10 and count(deptno)>3;

连接查询SQL92

有笛卡尔集,等值连接,非等值连接,外连接,自连接

查询语法:

SELECT	table1.column, table2.column
FROM	table1, table2
WHERE	table1.column1 = table2.column2;

特点:

在 WHERE 子句中写入连接条件

当多个表中有重名列时，必须在列的名字前加上表名作为前缀

连接的类型:

等值连接 -- Equijoin

非等值连接 -- Non-equijoin

左连接 --LEFT JOIN

右连接 --RIGHT JOIN

自连接 -- Self join

笛卡儿集

select * from dept;//4条记录
select * from emp; ;//14条记录
select * from dept,emp; ;//4*14=56条记录

总结 检索出的行的数目将是第一个表中的行数乘以第二个表中的行数 检索出的列的数目将是第一个表中的列数加上第二个表中的列数 应该保证所有联结都有where子句，不然数据库返回比想要的数据多得多的数据

等值查询

select * from emp,dept where emp.deptno=dept.deptno;-- 查询两个表根据deptno相同关联两个表
-- 可以取别名
select * from emp e,dept d where e.deptno=d.deptno;
-- 可以有选择的查询两个表中的某些字段
select d.deptno,dname,loc,empno,ename,job from dept d,emp e where d.deptno=e.deptno;
-- where后面还可以跟条件表达式
select * from emp e,dept d where e.deptno=d.deptno and e.deptno=10;
select d.deptno,dname,loc,empno,ename,job from dept d,emp e where d.deptno=e.deptno and loc='武汉';

注意:当多个表中出现在同名字段时,必须在字段前加上"表名."前缀,可以使用操作符添加查询条件,使用表别名可以简化查询,使用表名（表别名）前缀可提高查询效率；

非等值查询

多个表之间没有相同字段

要求：查询员工的工资等级

select * from emp e,salgrade s where e.sal>s.losal and e.sal<s.hisal;
-- 可以有多个and连接
select  empno,ename,job,sal,grade 
from emp e,salgrade s
where e.sal<s.hisal and e.sal>s.losal and e.job='MANAGER';

自连接

特点：将一个表当两个表使用

-- 查询每个员工的工号、姓名、直接领导姓名
select e.empno,e.ename,e1.ename 领导 from emp e,emp e1 where e.mgr=e1.empno;
-- 查询每个员工的工号、姓名、经理姓名
select e1.empno,e1.ename,e1.job,e2.ename 
from emp e1 ,emp e2
where e1.mgr=e2.empno
order by e1.empno;

连接查询SQL99

SQL92语法规则缺点:它的查询条件都放在where子句中,过滤条件多时,就不太友好了.

SQL99就修正了把联结条件,过滤条件分开

有以下结构:

• 交叉连接（Cross join）

• 自然连接（Natural join）

• 使用Using子句建立连接

• 使用On子句建立连接

• 连接（ Outer join ）

  • 内连接INNER JOIN

  • 左连接LEFT JOIN

  • 右连接RIGHT JOIN

语法:

select 字段列表
from table1
[cross join table2] |                                    //1：交叉连接
[natural join table2] |                                  //2：自然连接
[join table2 using (字段名)] |                      //3：using子句
[join table2 on (table1.column_name = table2.column_name)] | //4：on子句
[(left | right | full outer) join table2
on (table1.column_name = table2.column_name)]; //5：左/右/满外连接

交叉连接

cross join产生了一个笛卡尔集,其效果等同于在两个表进行连接时未使用WHERE子句限定连接条件;可以使用where条件从笛卡尔集中选出满足条件的记录。

select * from emp cross join dept;
-- 等价与
select * from emp,dept;

自然连接

natural join 基于两个表中同名字段进行连接

• 从两个表中选出同名列的值均对应相等的所有行

• 如果两个表中同名列的数据类型不同，则出错

• 不允许在参照列上使用表名或者别名作为前缀

• 自然连接的结果不保留重复的属性

select * from emp natural join dept;
-- 等价与
select * from emp e,dept d where e.deptno=d.deptno;
-- 不同的是natural join去除了同名字段,只保留一个,where可以指定按照那个同名列进行连接,而natural join连接全部同名字段

Using子句

自然连接是基于多张表的所有同名字段的等值连接,如果我们想为它设置指定连接的列名使用using子句

using子句引用的列在sql任何地方不能使用表名或者别名做前缀

select e.ename,e.sal,deptno,d.dname from emp e join dept d using(deptno);
-- using(deptno) 等价与 e.deptno=d.deptno;

On子句

为了设置任意的连接条件或者指定连接的列，需要使用ON子句

连接条件与其它的查询条件分开书写

select ename,dname from emp join dept on (emp.deptno=dept.deptno) where  emp.deptno=30;
select empno,ename,dname,sal,emp.deptno from emp join dept on(emp.deptno=dept.deptno and sal>1500);

外连接

1. 左外连接

   在两个表连接过程中除返回满足连接条件的行外,还返回左表中不满足条件的行,这中连接称为左外连接.

   left join

   select deptno, dname,empno,ename from dept left join emp using(deptno);
   

2. 右外连接

   在两个表连接过程中除返回满足连接条件的行外,还返回右表中不满足条件的行,这中连接称为右外连接.

   right join

   select deptno,dname,empno,ename from emp right join dept using(deptno);
   

3. 满外连接

   两个表在连接过程中除返回满足连接条件的行以外，还返回两个表中不满足条件的所有行，这种连接称为满外联接。

   full join

   select deptno,dname,empno,ename from emp full join dept using(deptno); -- 有问题
   

4. 内连接

   只返回满足连接条件的数据

   inner join

   select deptno,dname,empno,ename from emp inner join dept using(deptno);
   

子查询

当条件也需要查询时,我们可以把条件也写成一个查询,这个查询叫子查询

如:如何查得所有比“CLARK”工资高的员工的信息

1. 明确要查询的是员工信息ename

2. 查询条件是工资高于CLARK的工资

3. 而CLARK的工资也要查询出来,所以我们可以把查询CLARK的工资写成一个子查询做为条件

   select ename from emp where sal>(select sal from emp where ename='CLARK');
   

   -- 查询工资高于平均工资的雇员名字和工资。
   select ename,sal from emp where sal>(select avg(sal) from emp);
   -- 查询和turner同一部门且比他工资低的雇员名字和工资。
   -- 分析:首先使用子查询查出与turner同一部门的所有人在使用and后跟子查询查询工资比turner低的人
   select ename,sal from emp where deptno=(select deptno from emp where ename='turner') and sal<(select sal from emp where ename='turner');
   

特点:

子查询在主查询前执行一次

主查询使用子查询的结果

使用子查询注意事项

在查询是基于未知值时应考虑使用子查询

子查询必须包含在括号内

建议将子查询放在比较运算符的右侧，以增强可读性。

除非进行Top-N 分析，否则不要在子查询中使用ORDER BY 子句。

如果子查询返回单行结果，则为单行子查询，可以在主查询中对其使用相应的单行记录比较运算符

如果子查询返回多行结果，则为多行子查询，此时不允许对其使用单行记录比较运算符

1. 单行子查询

   -- 查询工资最高的雇员名字和工资。
   -- 方法一:根据sal来查询
   select ename,sal from emp where sal=(select max(sal) from emp);
   -- 方法二:嵌套两个子查询,根据empno来查询
   select ename,sal from emp where empno=(select empno from emp where sal=(select max(sal) from emp));
   -- 查询职务和SCOTT相同，比SCOTT雇佣时间早的雇员信息
   select * from emp where job=(select job from emp where ename='scott') and hiredate<(select hiredate from emp where ename='scott');
   -- 查询工资比SCOTT高或者雇佣时间比SCOTT早的雇员的编号和名字
   select empno,ename from emp where sal>(select sal from emp where ename='scott') or hiredate<(select hiredate from emp where ename='scott');

2. 多行子查询

   多行子查询返回多行记录

   对多行子查询只能使用多行记录比较运算符

   ALL 和子查询返回的所有值比较

   ANY 和子查询返回的任意一个值比较

   IN 等于列表中的任何一个

   -- 子查询查出来的条件是有多条的
   -- 查询工资低于任何一个'CLERK'的工资的雇员信息。
   select * from emp where sal<any(select sal from emp where job='clerk') and job<>'clerk';
   -- 查询工资比所有的 'SALESMAN'都高的雇员的编号、名字和工资。
   select empno,ename,sal from emp where sal>all(select sal from emp where job='salesman');
   -- 查询部门20中职务同部门10的雇员一样的雇员信息。 
   -- 首先查询出部门deptno为10的所有job,在排除掉部门deptno为10的.
   select * from emp where job in(select job from emp where deptno=10) and deptno=20;

   --查询在雇员中有哪些人是领导
   -- 首先先把mgr中的领导编号去重,把去重后的mgr编号与empno进行in
   select empno,ename from emp where empno in(select distinct mgr from emp);
   -- 找出部门编号为20的所有员工中收入最高的职员
   select * from emp where sal>=all(select sal from emp where deptno=20) and deptno=20;
   -- 查询每个部门平均薪水的等级
   -- 首先查出每个部门的平均新水,进行分组
   -- 把上面查出来的当成一张表,与salgrade进行联合查询
   select * from salgrade s,(select deptno,avg(sal) avg from emp group by deptno) t where
   t.avg between s.losal and s.hisal;
   ​

存储引擎

主要介绍Innodb和myISAM引擎

INNODB引擎

它是mysql5.5之后的默认支持的存储引擎

优点:

• 灾难恢复性好

• 支持事务

• 使用行级锁

• 支持外键关联

• 支持热备份

• 对于InnoDB中的表,其数据的物理组织形式是簇表,主键索引和数据是一起的,数据按主键的顺序物理分布

• 实现了缓存管理,不仅能缓冲索引也能缓冲数据,并且会自动创建散列索引以加快数据的获取

MyISAM引擎

特性:

• 不支持事务

• 使用表级锁,并发性差

• 主机宕机后,MyISAM表易损坏,灾难恢复性不佳

• 可以配合锁,实现操作系统下的复制备份,迁移

• 只缓冲索引,数据的缓存是利用操作系统缓冲区来实现的.可能引发过多的系统调用且效率不佳

• 数据紧凑存储,因此可以获得更小的索引个更快的全表扫描性能

两个引擎的区别

1）InnoDB支持事务，MyISAM不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。

2）MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用

3）InnoDB支持外键，MyISAM不支持

4）从MySQL5.5.5以后，InnoDB是默认引擎

5）InnoDB不支持FULLTEXT类型的索引

6）InnoDB中不保存表的行数，如select count() from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时MyISAM也需要扫描整个表。

7）对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引。

8）清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表。

9）InnoDB支持行锁（某些情况下还是锁整表，如 update table set a=1 where user like '%lee%'

有人说MYISAM只能用于小型应用，其实这只是一种偏见。

如果数据量比较大，这是需要通过升级架构来解决，比如分表分库，读写分离，而不是单纯地依赖存储引擎。

现在一般都是选用InnoDB了，主要是MyISAM的全表锁，读写串行问题，并发效率锁表，效率低，MyISAM对于读写密集型应用一般是不会去选用的。

事务

完成一个事情需要的一系列步骤(操作)，这些操作要么同时成功，要么同时失败

如: 两个用户,A有10000,B有10000. 现A向B转账1000,A转账后扣除了1000元但是现在机房停电等异常情况,导致后果是B账户没有加1000,而A账户减了1000,现实中这种情况是很危险的,所以我们需要使用事务来进行管理

事务的四大特性(ACID)

原子性

事务的每一步都是不可再分的.

一致性

A和B账户一共20000元,不管转账多少次总金额不变

持久性

当一个事务执行(成功)完毕后,数据会持久的存储到磁盘文件中

隔离性

两个事务操作同一份数据,相互之间不影响

事务的提交方式

自动提交

mysql默认为自动提交,不需要写commit就会自动将DML持久化提交

手动提交

Oracle默认为手动提交

在mysql中查看提交方式:

-- 查询结果为1表示自动提交,0表示手动提交
select @@autocommit;
-- 修改提交方式(自动提交修改为手动提交)
set @@autocommit=0;

开启事务

-- 开启事务
start transaction
-- 提交事务
commit;
-- 回滚事务
rollback;

注意：一旦使用start transaction;开启事务那么自动提交将失效

如果所有操作都正常执行使用commit;提交事务

当发生异常情况回滚事务，数据(此时为tb_account表)通常回滚到开启事务之前的状态

一个事务一旦开启了，在没有执行commit;或者rollback;之前事务不会结束

事务的隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
读已提交（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否