数据库的发展历史
网状数据库 -> 层次型数据库 -> 关系型数据库 -> 非关系型数据库(NoSQL)
常见的关系型数据库
SQLite
-微型数据库，常用于移动设备
MySQL -开源中小型数据库，可用于各种操作系统
PostgreSQL
开源中小型数据库
SQL Server
Microsoft开发的中型数据库，只用于Windows系统
Oracle
Oracle公司开发的中大型数据库，可用于各种操作系统
DB2 IBM公司开发的中大型数据库，常与IBM服务器搭配
MySQL简介
MySQL最初由瑞典MySQL AB公司开发，后被SUN收购，随后SUN又被Oracle收购。
MySQL是最流行的开源RDBMS，在WEB应用方面是最好的RDBMS应用软件之一。
MySQL是一种关联数据库管理系统，将数据保存在不同的库中，每个库可包含多个表，每个表中可以有多行多列的数据。
Linux + Apache + MySQL + PHP
“LAMP”组合，目前最流行的开源服务器端技术之一!尤其适合于中小型站点、需要快速部署的Web应用。
MariaDB:mariadb.org/
mysql:www.mysql.com/
一个作者 马丁: MariaDB
Xampp
Xampp：是一个服务器套装，包含多款服务器软件，例如mysql，apache..
mysql部署结构
**服务器端：**负责存储/维护数据 —— 银行的数据库服务器
C:/xampp/mysql/bin/mysqld.exe 启动文件
占用3306端口
**客户端：**负责连接服务器端，对数据进行增删改查操作 —— ATM机
C:/xampp/mysql/bin/mysql.exe 客户端文件
使用客户端连接服务器端
mysql.exe -h127.0.0.1 -P3306 -uroot -p
-h host IP地址/域名 127.0.0.1/localhost
-P port 端口
-u user 用户名 root 管理员账户
-p password 密码 xampp下root用户对应的密码是空
mysql -uroot 简写形式
常用的管理命令
quit; 退出连接
show databases; 显示当前所有的数据库
use 数据库名称; 进入指定的数据库
show tables; 显示当前数据库下所有的数据表
desc 数据表名称; 描述数据表中都有哪些列
所有命令都是以英文的分号作为结束
SQL命令
结构化查询语言，用于操作关系型数据库服务器，主要是对数据的增删改查
SQL命令的执行方式
交互模式
在客户端输入一行，点击回车，服务器端就会执行一行，适用于临时性的查看数据
脚本模式
客户端把要执行的命令写在一个脚本文件中，一次性提交给服务器执行，适用于批量的操作数据
在连接之前
mysql -uroot< .sql文件(拖拽脚本文件过来) 回车
SQL命令的语法规范
- 一条SQL命令可以跨越多行，以英文的分号作为结束
- SQL命令不区分大小写，习惯上关键字大写，非关键字小写
- 假设某一条SQL命令出现语法错误，则此条命令及以后的命令不再执行
- 分为单行注释(#...)和多行注释(/…/)，注释的内容不被服务器执行
常用的SQL命令
1.丢弃数据库，如果存在
drop database if exists jd;
2.创建新的数据库
create database jd;
3.进入数据库
use jd;
4.创建数据表
create table student(
id int;
name varchar(8),
sex varchar(1),
score int
);
5.插入数据
insert into student values(‘1’, ’tao’, ’M’, ’61’);
insert into student values(‘2’, ’tao1’, ’W’, ’65’);
--不推荐
insert into student values
(‘3’, ’tao1’, ’W’, ’65’),
(‘4’, ’tao1’, ’W’, ’65’),
(‘5’, ’tao1’, ’W’, ’65’);
6.查询数据
select * from student;
7.删除数据
delete from student where id='2';
8.修改数据
update student set name='ttt',sex="W" where id='1';
计算机如何存储字符
如何存储英文字符
ASCII：总共有128个，对英文字母及其符号进行了编码
Latin-1：对欧洲字母及其符号进行了编码，总共有256，兼容ASCII
如何存储中文字符
GB2312：对常用的6千多汉字进行了编码，兼容ASCII
GBK：对两万的汉字进行了编码，兼容GB2312
BIG5：台湾繁体字编码
Unicode：对世界上主流国家常用的语言进行编码，具体分为三种存储方案，分别是utf-8，utf-16，utf-32
mysql中文乱码产生的原因
Mysql默认使用Latin-1编码，没有解决中文的存储
解决mysql中文乱码
脚本文件另存为的编码为UTF-8
客户端连接服务器端的编码为UTF-8
set names utf8;
服务器端创建数据库，存储的编码为UTF-8
create database student charset=utf8;
set names utf8;
drop database if exists jd;
create database jd charset=utf8;
use jd;
create table student(
id int,
name varchar(10),
sex varchar(1),
score int
);
insert into student values('1','tao','M','61');
insert into student values('2','ta','W','81');
insert into student values('3','小黑','W','81');
列类型
数值型 —— 可以不加引号
tinyint 微整型，占1个字节，范围**-128~127**
smallint 小整型，占2个字节，范围**-32768~32767**
int 整型，占4个字节，范围**-2147483648~2147483647**
bigint 大整型，占8个字节
float 单精度浮点型，占4个字节，最大的制3.4E38，以牺牲小数点后的若干位为代价，存储的数字越大精度越低
double 双精度浮点型，占8个字节，存储的数字越大精度越低
decimal(M,D) 定点小数，小数点的位置不会发生变化，M代表总的有效位数，D代表小数点后的有效位数
boolean 布尔型，通常只有两个值，分别是true和false，代表真和假，往往用于保存只有两个值的数据，例如：性别、是否在线…
布尔型在使用的时候会自动转换为tinyint，true转为1，false转为0，可以执行插入1或者0
true和false属于关键字，在使用的时候不能加引号
TB GB MB KB Byte(字节) Bit(位)
10240
1Byte = 8Bit
10进制
1 2 3 4 5 6 7 8 9 10
2进制
1 10 11 100 101 110
一个英文字母(不分大小写)占一个字节的空间，一个中文汉字占两个字节的空间．
日期时间型 —— 必须加引号
data 日期型 ‘2021-6-2’
time 时间型 ’15:19:30’
datetime 日期时间型 ‘2021-6-2 15:19:30’
字符串型 —— 必须加引号
varchar(M) 变长字符串，几乎不会产生空间浪费，数据的操作速度相对慢，M的最大值是65535，常用于存储变化长度的数据，例如：
标题、内容、姓名…
char(M) 定长字符串，可能会产生空间浪费，数据的操作速度相对快，M的最大值是255，常用于存储固定长度的数据，例如：手机号
码、身份证号码…
text(M) 大型变长字符串，M的最大值是2G
列约束
主键约束 —— primary key
声明了主键约束的列，不允许插入重复的值，一个表中只能有一个主键约束，通常加在编号列，可以加快数据的查找速度。
声明了主键约束的列不允许插入null
非空约束 —— not null
声明了非空约束的列上禁止插入null
唯一约束 —— unique
声明了唯一约束的列不允许插入重复的值，允许插入null，甚至多个null，一个表中可以出现多个唯一
默认值约束 —— default
可以使用default关键字来设置默认值，
设置默认值
price decimal(7,2) default 99999.99,
使用默认值
#方法一
insert into laptop values(4,'Tpe470',default,default,default,'2018-12-1',1,50);
#方法二
#选择插入的列，其他的列为默认值
insert into laptop(lid,title) values(5,'Apple MacBook');
检查约束 —— check
也称为自定义约束，用户可以自己制定约束的条件
create table student(
score tinyint check(score>=0 and score<=100)
);
mysql不支持检查约束，认为会极大的影响数据的插入速度，后期需要通过JS来实现。
外键约束
声明了外键约束的列称为外键列，这一个列取值范围到另一个表的主键中，目的是为了让两个表建立关联
外键列和对应的主键列的列类型保持一致
foreign key(外键列) references 另一个表(主键列)
foreign key(familyId) references family(fid)
自增列
auto_increment：自动增长，声明了自增列，插入数据的时候只需要赋值为null，就会获取最大值然后加1插入
注意事项：
- 自增列必须应用在整数形式的主键列
- 声明了自增列允许手动赋值
#设置自增
lid int primary key auto_increment,
insert into laptop(lid,title) values(null,'Apple MacBook');
#设置客户端连接服务器端的编码
set names utf8;
#丢弃数据库，如果存在的话
drop database if exists xz;
#创建新的数据库，设置存储的编码
create database xz charset=utf8;
#进入创建的数据库
use xz;
#创建保存分类数据的表family
create table family(
fid int primary key,
fname varchar(16) default '未知'
);
#插入数据
insert into family values
(10,'戴尔'),
(20,'联想'),
(30,'小米');
insert into family values(40,'华为');
insert into family values(50,default);
insert into family(fid) values(60);
#创建保存商品数据的表laptop
create table laptop(
lid int primary key auto_increment,
title varchar(128),
price decimal(7,2) default 99999.99, #99999.99
spec varchar(64),
detail varchar(5000),
shelfTime date,
isOnsale boolean, #1->是 0->否
familyId int,
#将familyId设置为外键列，取值范围到family表的fid
foreign key(familyId) references family(fid)
);
#插入数据
insert into laptop values(1,'小米Air',4199,'2021年新款','详情1','2021-1-1',1,30);
insert into laptop values(2,'燃7000',5399,'流光银','详情2','2020-5-1',0,10);
insert into laptop values(3,'小新700',6299,'电竞版','详情3','2018-12-1',1,20);
#使用default关键字应用默认值
insert into laptop values(4,'Tpe470',default,default,default,'2018-12-1',1,50);
#应用默认值
insert into laptop(lid,title) values(5,'Apple MacBook');
insert into laptop(lid,title) values(null,'Apple MacBook');
insert into laptop(lid,title) values(null,'Apple MacBook');
查询
#sql文件
#设置客户端连接服务器端的编码
set names utf8;
#丢弃数据库，如果存在的话
drop database if exists tedu;
#创建新的数据库，设置存储的编码
create database tedu charset=utf8;
#进入数据库
use tedu;
#创建保存部门数据的表
create table dept(
did int primary key auto_increment,
dname varchar(16) unique
);
#插入数据
insert into dept values(10,'研发部');
insert into dept values(20,'运营部');
insert into dept values(30,'市场部');
insert into dept values(40,'测试部');
#创建保存员工数据的表
create table emp(
eid int primary key auto_increment,
ename varchar(8) not null,
sex boolean default 1, #1-男 0-女
birthday date,
salary decimal(7,2), #99999.99
deptId int,
foreign key(deptId) references dept(did)
);
#插入数据
INSERT INTO emp VALUES(NULL,'Tom',1,'1990-5-5',6000,20);
INSERT INTO emp VALUES(NULL,'Jerry',0,'1991-8-20',7000,10);
INSERT INTO emp VALUES(NULL,'David',1,'1995-10-20',3000,30);
INSERT INTO emp VALUES(NULL,'Maria',0,'1992-3-20',5000,10);
INSERT INTO emp VALUES(NULL,'Leo',1,'1993-12-3',8000,20);
INSERT INTO emp VALUES(NULL,'Black',1,'1991-1-3',4000,10);
INSERT INTO emp VALUES(NULL,'Peter',1,'1990-12-3',10000,10);
INSERT INTO emp VALUES(NULL,'Franc',1,'1994-12-3',6000,30);
INSERT INTO emp VALUES(NULL,'Tacy',1,'1991-12-3',9000,10);
INSERT INTO emp VALUES(NULL,'Lucy',0,'1995-12-3',10000,20);
INSERT INTO emp VALUES(NULL,'Jone',1,'1993-12-3',8000,30);
INSERT INTO emp VALUES(NULL,'Lily',0,'1992-12-3',12000,10);
INSERT INTO emp VALUES(NULL,'Lisa',0,'1989-12-3',8000,10);
INSERT INTO emp VALUES(NULL,'King',1,'1988-12-3',10000,10);
INSERT INTO emp VALUES(NULL,'Brown',1,'1993-12-3',22000,NULL);
查询特定的列
select eid,ename from emp;
练习：查询出所有员工的姓名、性别、生日、工资
select ename,sex,birthday,salary from emp;
查询所有的列
#方法一
select * from emp;
#方法二
select eid,ename,sex,birthday,salary,deptId from emp;
给列起别名as
查询出所有员工的编号和姓名，使用汉字别名
select eid as 编号,ename as 姓名 from emp;
as关键字可以省略，保留空格即可
显示不同的记录distinct
查询出都有哪些性别的员工
select distinct sex from emp;
/*
+------+
| sex |
+------+
| 1 |
| 0 |
+------+
*/
查询出员工都分布在哪些部门
select distinct deptId from emp;
查询时执行计算
当计算器用，计算2+3+4+5-6+8*7.3
select 2+3+4+5-6+8*7.3;
查询出所有员工的姓名及其年薪
select ename,salary*12 年薪 from emp;
假设每个员工的工资增加1000，年终奖20000，查询出所有员工的姓名及其年薪，使用汉字别名
select ename 姓名,(salary+1000)*12+20000 年薪 from emp;
查询的结果排序order by 列名 asc/desc(升/降)
ascendant 升序的，descendant 降序，describe 描述
查询出所有的部门，结果按照编号升序排列
select * from dept order by did asc;
查询出所有的部门，结果按照编号降序排列
select * from dept order by did desc;
不加排序规则默认是按照升序排列
按照字符串排序是按照首个字符的Unicode码
查询出所有员工的数据，结果按照工资的降序排列，如果工资相同按照姓名排列
select * from emp order by salary desc,ename;
查询出所有员工的数据，结果按照年龄从小到大，要求所有女员工显示在前边。
select * from emp order by sex,birthday desc;
条件查询
查询出编号为5的员工
select * from emp where eid=5;
查询出姓名为king的员工
select * from emp where ename='king';
查询出20号部门的员工有哪些
select * from emp where deptId=20;
查询出工资在6000以上的员工有哪些
select * from emp where salary>6000;
比较运算符：> < >= <= = != and(&&),or(||),is,in,is not
查询出不在20号部门的员工有哪些
select * from emp where deptId!=20;
查询出没有明确部门的员工有哪些
select * from emp where deptId is null;
练习：查询出有明确部门的员工有哪些
select * from emp where deptId is not null;
练习：查询出工资7000以上的男员工有哪些
select * from emp where sex=1 and salary>7000;
练习：查询出工资在5000~8000之间的员工有哪些
select * from emp where salary>=5000 and salary <=8000;
select * from emp where salary between 5000 and 8000;
练习：查询出工资在5000以下或者8000以上的员工有哪些
select * from emp where salary<5000 or salary>8000;
select * from emp where salary<5000 || salary>8000;
select * from emp where salary not between 5000 and 8000;
练习：查询出1993年出生的员工有哪些
select * from emp where birthday>='1993-1-1' && birthday<='1993-12-31';
select * from emp where birthday between '1993-1-1' and '1993-12-31';
练习：查询出20号部门或者30号部门的员工有哪些
select * from emp where deptId=20 || deptId=30;
select * from emp where deptId in(20,30);
练习：查询不在20号部门并且不在30号部门的员工有哪些
select * from emp where deptId!=20 and deptId!=30;
select * from emp where deptId not in(20,30);
模糊条件查询
示例：查询出姓名中含有字母e的员工有哪些
select * from emp where ename like '%e%';
练习：查询出姓名中以e结尾的员工有哪些
select * from emp where ename like '%e';
练习：查询出姓名中倒数第2个字符是e的员工有哪些
select * from emp where ename like '%e_';
% 匹配任意个字符 >=0
_ 匹配任意1个字符 =1
以上两个匹配符结合着like关键字使用
分页查询limit
查询的结果中有太多的数据，一次显示不完可以做成分页显示
需要两个已知的条件：当前的页码、每页的数据量
开始查询的值 = (当前的页码 - 1) * 每页的数据量
#第1页：
select * from emp limit 0,5;
#第2页：
select * from emp limit 5,5;
#第3页：
select * from emp limit 10,5;
注意事项：开始查询的值和每页的数据量在SQL命令中，必须是数值型，不能加引号，否则报错。
复杂查询
聚合函数
函数：是一个独立的功能体，需要提供若干个数据，返回处理的结果
count()/sum()/avg() /max() / min()
数量 总和 平均 最大 最小
聚合查询/分组查询
示例：查询出所有员工的数量
select count(*) from emp;
练习：通过员工的编号列查询出所有员工的数量
select count(eid) from emp;
练习：通过所属部门编号列查询出所有员工的数量
select count(deptId) from emp;
练习：查询出所有男员工的工资总和
select sum(salary) from emp where sex=1;
练习：查询出10号部门的平均工资
select avg(salary) from emp where deptId=10;
练习：查询出工资最高的女员工的工资是多少
select max(salary) from emp where sex=0;
练习：查询出年龄最大的员工的生日是多少(生日最小)
select min(birthday) from emp;
分组查询：先按照指定的列分组，分组以后再进行聚合查询。
分组查询通常只能查询聚合函数和分组条件
示例：查询出男女员工的平均工资、最高工资、最低工资分别是多少
select avg(salary),max(salary),min(salary),sex from emp group by sex;
练习：查询各部门员工的最高工资，员工数量，平均工资
select max(salary),count(eid),avg(salary),deptId from emp group by deptId;
示例：获取日期中的年份部分 2021-6-4
select year('2021-6-4');
练习：查询出所有员工出生的年份
select year(birthday) from emp;
练习：查询出1993年出生的员工有哪些
select * from emp where year(birthday)=1993;
md5加密函数
select md5('123456');
INSERT INTO emp VALUES(NULL,md5('Brown'),1,'1993-12-3',22000,NULL);
select * from emp where eid=16;
/*
+-----+----------+------+------------+----------+--------+
| eid | ename | sex | birthday | salary | deptId |
+-----+----------+------+------------+----------+--------+
| 16 | ed63fc91 | 1 | 1993-12-03 | 22000.00 | NULL |
+-----+----------+------+------------+----------+--------+
*/
year() 获取日期中的年份
md5() 对字符串进行加密
子查询
是多个SQL命令的组合，把一个SQL命令的结果作为另一个SQL命令的条件
示例：查询出工资最高的员工
步骤1：查询出工资的最高值——22000
select max(salary) from emp;
步骤2：通过工资查询员工
select * from emp where salary=22000;
综合：
select * from emp where salary=(select max(salary) from emp);
练习：查询出比tom工资高的员工有哪些
select * from emp where salary>(select salary from emp where ename='Tom');
练习：查询出高于平均工资的男员工有哪些
select * from emp where sex=1 and (select avg(salary) from emp);
练习：查询出和tom同一年出生的员工有哪些
select * from emp where year(birthday)=(select year(birthday) from emp where ename='Tom') && ename!='tom';
多表查询
要查询的列是分布在不同的表中，前提在创建表的时候已经建立了关联 示例：查询出所有员工的姓名及其部门名称
select ename,dname from emp,dept where deptId=did;
select emp.ename,dept.dname from emp,dept where emp.deptId=dept.did;
内连接
select ename,dname from emp inner join dept on deptId=did;
和之前的查询结果一样
左外连接
以左边的表为主，显示emp所有的数据
select ename,dname from emp left outer join dept on deptId=did;
/*
+----------+--------+
| ename | dname |
+----------+--------+
| Tom | 运营部 |
| Jerry | 研发部 |
| David | 市场部 |
| Maria | 研发部 |
| Leo | 运营部 |
| Black | 研发部 |
| Peter | 研发部 |
| Franc | 市场部 |
| Tacy | 研发部 |
| Lucy | 运营部 |
| Jone | 市场部 |
| Lily | 研发部 |
| Lisa | 研发部 |
| King | 研发部 |
| Brown | NULL |
| ed63fc91 | NULL |
+----------+--------+
*/
显示左侧表中所有的记录，先写哪个表哪个就是左
outer关键字可以省略
右外连接
以右边的表为主，显示dept所有的数据
select ename,dname from emp right outer join dept on deptId=did;
/*
+-------+--------+
| ename | dname |
+-------+--------+
| David | 市场部 |
| Franc | 市场部 |
| Jone | 市场部 |
| NULL | 测试部 |
| Jerry | 研发部 |
| Maria | 研发部 |
| Black | 研发部 |
| Peter | 研发部 |
| Tacy | 研发部 |
| Lily | 研发部 |
| Lisa | 研发部 |
| King | 研发部 |
| Tom | 运营部 |
| Leo | 运营部 |
| Lucy | 运营部 |
+-------+--------+
*/
显示右侧表中所有的记录，后写哪个表哪个就是右
outer关键字可以省略
全连接
full join on
mysql不支持全连接写法
解决方法：将左外连接和右外连接的结果联合起来，合并相同的记录。
union 联合后，合并相同的记录
union all 联合后，不合并相同的记录
(select ename,dname from emp left outer join dept on deptId=did)
union
(select ename,dname from emp right outer join dept on deptId=did);
(select ename,dname from emp left outer join dept on deptId=did)
union all
(select ename,dname from emp right outer join dept on deptId=did);
