MySQL基础学习（一）

一、常用命令

一般命令不区分大小写
启停服务：

net start mysql
net stop mysql

登录命令：

mysql -uroot -p[password] or mysql -uroot -p
mysql -uroot -p//隐藏密码
Enter password: *******

查看有哪些数据库：

show databases;

选择要使用的数据库：

use [database-name];

创建数据库：

create database [database-name];

查看某个数据库下有哪些表：

show tables;

导入数据表：

source [文件路径];

后缀为sql的文件被称为sql脚本文件，其中编写了大量的SQL语句，执行时，该文件中的语句会全部执行

查看表中的所有数据：

select * from 表名;

查看表的结构：

desc 表名;//describe

查看当前数据库版本号

select version();

查看当前使用的是哪个数据库？

select database();

MySQL不见分号不结束命令的执行，此时输入\c or crtl+c可结束当前输入

二、概念

数据库中的最基本的单元是表(table)
数据库当中是以表格的形式存储数据的，因为表比较直观。
任何一张表都有行和列：
行(row)：被称为数据/记录
列(column)：被称为字段
姓名字段、年龄字段、性别字段
每个字段都有：字段名、数据类型、约束等属性

三、SQL语句的分类

DQL：数据查询语言（凡是带有select关键字的都是查询语言）

DML：数据操作语言（凡是对表中的数据进行增删改查的都是DML）
insert 增
delete 删
update 改
DML主要操作的是表中的数据data

DDL：数据定义语言（凡是带有create、drop、alter的都是DDL）
DDL主要操作的是表的结构，不是表中的数据

TCL：事务控制语言
事物提交：commit;
事物回滚：rollback;

DCL：数据控制语言
例如：授权grant、撤销权限revoke…

四、DQL查询语句

简单查询

查询一个字段？

select 字段名 from 表名;

其中要注意：select和from都是关键字，字段名和表名都是标识符

查询多个字段怎么办？（*是全部）

select 字段名1,字段名2,… from 表名;

如何给查询的列起别名？

select 字段名 as 别名 from 表名;
select 字段名 别名 from 表名;

Q：别名里面有空格怎么办？
A：可以用单双引号把别名括起来，注意：在所有数据库中，字符串统一用单引号括起来，单引号是标准。
注意：select语句永远不会进行修改操作

实操演练

计算员工年薪？

select ename as Name,sal*12 'Annual Salary' from emp;

注意：此处只是进行运算后输出，并没有修改

条件查询

查询出符合条件的记录

select 字段1,字段2,字段3… from 表名 where 条件;

条件

=：等于
<>、!=：不等于
<：小于
‘’>‘’ ：大于
‘’>=‘’：大于等于
between …and…：介于两个值之间，等同于>= and <=
is null：为空
is not null：不为空
and：并且
or：或者
in：包含
not in：不包含

select * from emp where job in ('Manager','Salesman');
select * from emp where job = 'Manager' or job = 'Saleman';

模糊查询

like ：称为模糊查询，支持%或下划线匹配
%匹配任意个字符，一个下划线只能匹配一个字符
and优先级比or高

找出名字中含有o的员工信息

select * from emp where ename like '%o%';

找出名字中含有下划线的员工信息

select * from emp where ename like '%\_%';

当查询特殊字符时，前面加上\，作为转义字符

排序

select ename,sal from emp order by sal;//默认升序
select ename,sal from emp order by sal asc;//指定升序：ascend
select ename,sal from emp order by sal desc;//降序：descend

多个字段排序？

查询员工名字和薪资，要求按照薪资升序，如果薪资相同，按照名字升序

select ename,sal from emp order by sal,ename asc;

也可以根据字段的位置进行排序，不建议开发中使用，因为列顺序会改变

select * from emp order by 2;//2表示第二列，第二列是sal

数据处理函数

数据处理函数又被称为单行处理函数
单行处理函数的特点：一个输入对应一个输出
和单行处理函数相对的是：多行处理函数（多个输入对应一个输出）

lower 转换小写
upper 转换大写
substr 取子串（substr(被截取的字符串,起始下标,截取的长度)，起始下标从1开始）
length 取长度
trim 去空格
str_to_date 将字符串转换成日期
date_format 格式化日期
format 设置千分位
round(数字,小数位) 四舍五入
rand() 生成随机数
Ifnull 可以将 null 转换成一个具体值（NULL主要参与运算，结果就是NULL）
CASE column
WHEN condition1 THEN value
WHEN condition2 THEN value
…
ELSE value END;

select concat(upper(substr(ename,1,1)),lower(substr(ename,2,length(ename)-1))) from emp;
select round((rand()*100)%32+1,0) rand from emp;//随机值范围在1-32
select ename,sal+ifnull(comm,0) Sum from emp;
select ename,
		job,
		(case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) 'New Salary' 
		from emp;

分组函数（多行处理函数）

多行处理函数（多个输入对应一个输出）
count 计数
sum 求和
avg 平均值
max 最大值
min 最小值

分组函数自动忽略null，不需要对null进行提前处理

select count(*) from emp;//统计所有记录个数
select count(comm) from emp;//统计不为null的记录个数

分组查询

在实际应用中，可能需要对数据先进行分组，再对每一组的数据进行操作。
计算每个部分的工资和？
计算每个工作岗位的平均薪资？
找出每个工作岗位的最高薪资？

select
	 … 
from
	 … 
where
	 … 
group by 
	… 
having//分完组后根据条件进行过滤
	… 
order by 
	… 

语句执行顺序

from——where——group by——having——select——order by
为什么分组函数不能直接使用在where后面？

select ename,sal from emp where sal > min(sal);//报错

因为分组函数在使用时必须先分组之后才能使用
where执行的时候，还没有分组。

select deptno,max(sal) from emp 
group by deptno having max(sal)>3000 order by max(sal) desc;//低效
select deptno,max(sal) from emp where sal>3000 group by deptno;
//找出每个部门最高薪资，要求显示薪资大于3000的

优化策略：where和having，优先选择where

distinct关键字

把查询结果去除重复记录，原表数据不会被修改

select distinct job from emp;
select distinct job,deptno from emp;//两个字段联合起来去重

连接查询

多张表联合起来查询数据，这种跨表查询，被称为连接查询
主要学习SQL99语法

表连接方式

内连接：完全能够匹配上该条件的数据查询出来

等值连接
非等值连接
自连接

外连接：

左外连接（左连接）
右外连接（右连接）

全连接

笛卡尔积现象

当两张表连接查询，没有任何条件限制，最终查询结果条数，是两张表条数的乘积，这种现象被称为笛卡尔积现象

select ename,loc from emp,dept;

如何避免笛卡尔积现象？
连接时加条件，满足条件的记录被筛选出来

select ename,dname from emp,dept where emp.deptno = dept.deptno;
select ename,dname from emp e,dept d where e.deptno = d.deptno;
//给表起别名，很重要

注意：最终查询的结果是14条，但是匹配的过程中，匹配的次数没有减少
因此，一定要尽量降低表的连接次数

内连接之等值连接

案例：查找每个员工所在部门的名称，显示员工名和部门名？

select ename e.Name,d.dname Dept from emp e,dept d where e.deptno = d.deptno;
//SQL92语法
select e.ename Name,d.dname Dept from emp e join dept d on e.deptno = d.deptno;
//SQL99语法

92缺点：结构不清晰，表的连接条件和后期进一步筛选的条件，都放where后面
99优点：表连接是独立的，连接以后，若需进一步筛选，再往后添加where
99语法

select … from a  inner join b on a和b的连接 where 筛选条件
//inner可以省略，带着inner可读性更好

内连接之非等值连接

案例：找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级？

select e.ename,e.sal,s.grade from emp e join salgrade s 
on sal between s.losal and s.hisal order by grade asc;

内连接之自连接

案例：查询员工的上级领导，要求显示员工名和对应领导名？
技巧：一张表看成两张表

select a.ename Worker,b.ename Leader from emp a join emp b on a.MGR = b.Empno;

外连接（右外连接）

right表示将join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表。

select e.ename,d.dname from emp e right outer join dept d 
on e.deptno = d.deptno;//outer可以省略

外连接（左外连接）

left表示将join关键字边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表。
任何一个右连接都有左连接的写法，任何一个左连接都有右连接的写法

select e.ename,d.dname from dept d left join emp e on e.deptno = d.deptno;

外连接的查询结果条数一定是 >= 内连接的查询结果条数
案例：查询每个员工的上机领导，要求显示所有员工的名字和领导名？

select a.ename Name,b.ename Leader from emp a left join emp b 
on a.mgr = b.empno order by Leader desc;

三张、四张，多张表怎么连接？

select … from a join b on a和b的连接条件 join c on a和c的连接条件 join d on
a和d的连接条件

一条SQL语句中内连接和外连接可以混合，都可以出现！
案例：找出每个员工的部门名称以及工资等级，要求显示员工名、部门名、薪资、薪资等级？

select e.ename Name,d.dname Dept,e.sal Salary,s.grade Grade 
from emp e left join dept d on e.deptno=d.deptno left join salgrade s on e.sal 
between s.losal and s.hisal order by Salary desc;

子查询（嵌套查询）

select语句中嵌套select语句，被嵌套的select语句称为子查询

select …(select)… from …(select)… where …(select)…

where字句中的子查询

案例：找出比最低工资高的员工姓名和薪资？

select ename,sal from emp where sal > (select min(sal) from emp);

from字句中的子查询

注意：from后面的子查询，可以将子查询的查询结果当做一张临时表（技巧）

案例：找出每个岗位的平均工资的薪资等级
①找出每个岗位的平均薪资
②克服心理障碍，把以上的查询结果当成一张真实存在的表
③两张表进行表连接

select b.job JOB,b.avg Aversalary,s.grade Level from salgrade s right join (select job,avg(sal) avg from emp group by job) b on b.avg between s.losal and s.hisal;

select后面出现的子查询（不要求）

案例：找出每个员工的部门名称，要求显示员工名，部门名？

select e.ename,(select dept.dname from dept where dept.deptno=e.deptno) 
from emp e;

注意：对于select后面的子查询来说，这个子查询只能一次返回1条结果，多于1条就报错

union合并查询结果集

案例：查询工作岗位是manager和salesman的员工

select * from emp where job in ('manager','salesman');
select * from emp where job = 'manager' union select * from emp 
where job = 'salesman';

union的效率较高，对于表连接来说，union可以减少匹配的次数，还可以完成两个结果集的拼接
eg.a连接b，b连接c （均10条记录），匹配次数是1000条
a连接b，a连接c，二者使用union的话：100+100 = 200次（union把乘法变成加法运算）
注意：union在结果集合并时，要求两个结果集的列数相同，数据类型尽量也相同

limit（重要）

limit将查询结果集的一部分取出，通常放在分页查询中。
完整用法：limit 起始下标,长度
起始下标从0开始
案例：按照薪资降序，取出排名在前5名的员工

select * from emp order by sal desc limit 5;//前5

注意：MySQL中limit在order by后执行

分页

每页显示pageSize条记录
第PageNo页：limit (PageNo - 1) * pageSize pageSize;

五、表

表的创建（建表）

建表的语法格式：（建表属于DDL语句，DDL包括：create drop alter）

create table 表名(字段名1 数据类型1,字段名2，数据类型2,……);

表名：建议以t_ 或者tbl_开始，可读性强，见名知意。

指定默认值

create table t_student(
	no int,
	name varchar(32),
	sex char(1) default 'm',//性别默认为'm'
	age int(3),
	email varchar(255)
);

快速创建表

原理：将一个查询结果当做一张表新建

create table dup_emp as (select * from emp);

MySQL的数据类型

varchar：可变长度的字符串，动态分配空间，但速度较慢，最长255
char：定长字符串，分配固定长度空间来存储数据，速度较快，但使用不恰当时，可能导致空间浪费，最长255
int：整数数字，等同于int，最长11位
bigint：长整型，等同于long
float：单精度浮点型
double：双精度浮点型
date：短日期类型
datetime：长日期类型
clob：字符大对象(Character Large Object)，最多可以存储4G的字符串，如文章、简介、说明等
blob：二进制大对象(Binary Large Object)，专门用来存储图片、声音、视频等流媒体数据，插入时需要使用IO流

删除表

drop table 表名;//当这张表不存在时会报错
drop table if exists t_student;//如果这张表存在的话，删除

插入数据

insert into 表名(字段名1，字段名2,字段名3,…) values(值1,值2,值3);

注意：字段名和值要一一对应，数量要对应，数据类型要对应。
注意：没有给其他字段指定值的话，默认是null

字段名可省略，前提是值全部写上

insert into t_student values(10,'Cuihua','F',18,'Cuihua@xupt.com');

插入日期

str-to-date函数

str_to_date：将字符串varchar类型转换成date类型

str_to_date('字符串日期','日期格式');

若提供的字符串格式是%Y-%m-%d，则不需要str_to_date函数转换（1990-01-11）

mysql日期格式

%Y：年
%m：月
%d：日
%h：时
%i：分
%s：秒

date_format函数

date_format函数：将date类型转换成具有一定格式的varchar字符串类型
这个函数通常使用在查询日期方面，设置展示的日期格式

date_format(日期类型数据,'日期格式');
insert into t_user values(15,'zhangsan','2001-03-07',now());
//now获取系统当前时间

修改数据

update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3 …… where 条件;

注意：没有条件限制会导致所有数据全部更新。

删除数据

delete from 表名 where 条件;

注意：没有条件，整张表的数据会全部删除！
delete语句虽然删除效率低，且数据在硬盘上的真实储存空间不会被释放，但是支持回滚，可以恢复数据

快速删除

truncate语句删除效率较高，表被一次截断，物理删除，不支持回滚

truncate table 表名;

约束（重要）

constraint：在创建表的时候，我们可以给表中的字段加上一些约束，来保证表中数据的完整性、有效性

常见约束（列级约束）

非空约束：not null
唯一性约束：unique
主键约束：primary key（简称PK）
外键约束：foreign key（简称FK）
检查约束：check（MySQL不支持）

非空、唯一性约束

create table vip (
id int unique not null,
Name varchar(20) not null,
Level int,money double
				);
insert vip value(1,'Alan',1,2500.50);
insert vip (id,Level,money) value(1,1,2500.50);
# ERROR 1364 (HY000): Field 'Name' doesn't have a default value

insert vip values(1,'Frank',1,3000);
# ERROR 1062 (23000): Duplicate entry '1' for key 'vip.id'

多个字段的联合唯一性（表级约束）

create table vip (
id int not null,
Name varchar(20) not null,
unique(id,Name),//此处禁止记录中的id和name字段同时唯一
Level int,money double
);

在MySQL中，如果一个字段同时被not null和unique约束的话，该字段自动变成主键字段。（Oracle中不是）

主键约束

主键值：区分每一行记录的唯一标识

注意：任何一张表都应该有主键，没有主键，表无效！

给表添加主键约束

create table vip(id int primary key,name varchar(20),money double default 0.0);
//单个字段添加单个主键
 create table vip2(id int,name varchar(20),money double default 0.0,primary key(id,name));
//多个字段添加复合主键，表级约束

在实际开发中，不建议使用复合主键，建议使用单一主键。
注意：一张表中只有一个主键，主键值建议使用int、bigint、char等类型

主键的分类

主键除了单一主键和复合主键之外，还可以这样进行分类？
自然主键：主键值是一个自然数，与业务无关。
业务主键：主键值和业务紧密关联，如银行卡号做主键值
实际开发中，自然主键用的较多
因为主键一旦和业务挂钩，当业务发生变动的时候，可能会影响到主键值。
在MySQL中，有一种机制，可以帮我们自动维护一个主键值

create table vip (id int primary key auto_increment,name varchar(255));
// auto_increment 自动升序维护主键

外键约束

外键：其他表的字段
外键约束：某个记录的字段一旦受到外键约束，其记录值只能是外键中的记录

create table room(room_no int primary key auto_increment,room_name varchar(255));
table vip (id int primary key auto_increment,room_no int,name varchar(255)
,foreign key(room_no) references room(room_no));//受到room表中room_no的约束

外键值可以为null，外键引用的父表中的某个字段，不一定为主键，但必须有唯一性