一、数据库介绍与安装
1.1、什么是数据库软件
数据库软件：保存数据的仓库，俗称数据库。本质就是一款用来管理数据的软件。需要使用专业的语言（SQL语言）去操作软件中保存的数据。
1.2、数据库软件介绍
数据库软件都是由第三方公司研发提供，我们只需要下载安装使用即可。
常见的数据库软件
- Oracle：它是Oracle公司的产品，功能强大，收费高。和java程序兼容性好，适合中大型项目。
- MySql：早期瑞典一个公司发明，后期被sun公司收购，后期被Oracle。是开源的，功能一般，免费。和java程序兼容性好，适合中小型项目
- DB2：IBM公司的产品，收费，功能一般。帮助文档和问题解决方案没有Oracle丰富。
- SqlServer：微软数据库，功能一般，收费一般。和微软开发语言兼容性好，适合中小项目。
- Sybase：Sybase公司的产品，市场占有率低。
Java开发应用程序主要使用的数据库：Oracle、DB2、MySQL。
关系型数据库
在程序中数据和数据之间难免会存在一些关系，所以我们在保存数据的同时，也需要去维护数据和数据之间的关系。上述的几款数据库软件在保存数据时，就能够维护数据之间的关系，因此我们也把他们叫做关系型数据库。
二、数据库的安装和配置
2.1、mysql数据库的内部存储结构
数据库的使用步骤：
- 安装数据库软件
- 连接数据库服务器
- 数据仓库的CRUD
- 数据表的CRUD
- 表中数据的CRUD
总结：数据库软件，可以管理多个数据仓库。一个数据仓库可以管理多个数据表。每个数据表中可以存储多行数据记录。
2.2、连接数据库
在连接数据库之前，首先需要先打开数据库的服务。
开启数据库服务
-
右击此电脑，点击管理
-
如下图操作
-
如下图操作
数据库path配置
数据库安装后的默认目录C:\Program Files\MySQL\MySQL Server 8.0\bin配置到path变量中。
连接数据库
将mysql的路径配置到path变量后，在dos窗口中输入如下语句
mysql -h[服务器IP] -P[端口号] -u[用户名] -p[密码]
连接本机： 省略 -h 和 -P ,直接使用：mysql -u[用户名] -p[密码]
三、SQL语句
SQL语句：Structured Query Language结构化查询语言。SQL语句不依赖于任何平台，对所有的数据库软件都是通用的。
SQL语句的语法标准是由W3C组织制定的，具有查询、操纵、定义和控制关系型数据库的四方面功能。
SQL语句是一个非过程性的语言，每一条SQL执行完都会有一个具体的结果出现。
有些数据库厂商会给自家的数据库软件增加一些特有的具备强大功能的SQL语句。从而增加自家数据库的竞争力。比如 Sqlserver中的T-SQL还有Oracle中的PL/SQL等可以编写复杂的SQL语句。
SQL分类
DDL：数据定义语言 - Data Definition Language，用来定义数据库的对象，如数据表、视图、索引等。
DML：数据处理语言 - Data Manipulation Language，在数据表中更新，增加和删除记录。
DQL：数据查询语言 – Data Query Language，用于查询数据表中的数据，通过select关键字。
DCL：数据控制语言 – Data Control Language，指用于设置用户权限和控制事务语句。
四、数据仓库的操作
查询所有数据仓库
show databases;
创建数据库语法
create database databaseName [character set 编码表名]; #默认码表utf-8。
查看数据库编码表
创建数据库时，如果没有指定编码表，默认使用的是utf-8编码表。
show create database databaseName;
修改数据库编码集
alter database databaseName character set 字符集;
查看所在数据库语法
select database(); # null表示当前并没有在使用任何的数据仓库。
切换数据库
use databaseName;
删除数据库
drop database databaseName;
五、创建表的操作
有了数据仓库之后，我们就可以在数据仓库创建数据表，用来保存数据。
5.1、查看所有表
show tables;
5.1.1、创建数据表
create table tableName(
字段1 类型(长度) [约束] [默认值] [注释],
字段2 类型(长度) [约束] [默认值] [注释],
....
字段n 类型(长度) [约束] [默认值] [注释]
);
一张表可以对应到java中的一个类，如有下面一个类，我们应该如何创建对应的数据表。
public class Student{
private String snum;//学号
private String name;//姓名
private char sex; //性别
private int age;//年龄
private double score;//成绩
private Date birthday;//生日
}
mysql中的数据类型和java中的数据类型所有使用的关键字和细节是有区别的，所以如果想创建出对应的student表，我们就需要先了解mysql的数据类型。
5.1.2、MySQL的数据类型
| 数值类型 | |
|---|---|
| tinyint | 极小的整数，相当于java中的byte类型 |
| smallint | 小的整数，相当于java中的short类型 |
| int | 整数类型，相当于java中的int类型 |
| bigint | 大的整数，相当于java中的long类型 |
| float（m,n） | 单精度浮点型，m表示数值总长度，n表示小时长度 |
| double(m,n) | 双精度浮点型，m表示数值总长度，n表示小时长度 |
| 字符串类型 | |
|---|---|
| char(长度) | 固定长度，最多255个字符，索引效率级高，必须在括号里定义长度 |
| varchar(长度) | 可变长度，最多65535个字符，必须指定长度 |
| tinytext | 可变长度，最多255个字符 |
| text | 可变长度，最多65535个字符 |
| mediumtext | 可变长度，最多2的24次方-1个字符 |
| longtext | 可变长度，最多2的32次方-1个字符 |
| json | mysql5.7新增加的数据类型，用来存放json数据 |
| 日期类型 | |
|---|---|
| date | 日期，表示年月日 |
| time | 时间，表示时分秒 |
| datetime | 日期时间，年月日时分秒 |
| timestamp | 日期时间（时间戳），年月日时分秒 会随之数据的更新而跟着更新时间 |
| Logic类型 | |
|---|---|
| bit | 逻辑类型(布尔类型)， 只能有0和1两个结果 |
char、varchar、text区别
char：存储定长数据很方便，char字段上的索引效率级高，必须在括号里定义长度，可以有默认值，比如定义char(10)，那么不论你存储的数据是否达到了10个字节，都要占去10个字节的空间（自动用空格填充），且在检索的时候后面的空格会隐藏掉，所以检索出来的数据需要记得用什么trim之类的函数去过滤空格。
varchar：存储变长数据，但存储效率没有char高，必须在括号里定义长度，可以有默认值。保存数据的时候，不进行空格自动填充，而且如果数据存在空格时，当值保存和检索时尾部的空格仍会保留。另外，varchar类型的实际长度是它的值的实际长度+1，这一个字节用于保存实际使用了多大的长度。
text：存储可变长度的非Unicode数据，最大长度为2^31-1个字符。text列不能有默认值，存储或检索过程中，不存在大小写转换，后面如果指定长度，不会报错误，但是这个长度是不起作用的，意思就是你插入数据的时候，超过你指定的长度还是可以正常插入。
1、经常变化的字段用varchar；
2、知道固定长度的用char；
3、超过255字节的只能用varchar或者text；
4、能用varchar的地方不用text；
5、能够用数字类型的字段尽量选择数字类型而不用字符串类型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了；
5.1.3、练习创建Student表
create table student(
snum char(5),
name varchar(10),
sex char(1),
age int,
score double,
birthday date
);
5.1.4 查看表结构语法
desc tableName;
5.2、表单约束
约束：通过某些限制，来规定当前数据表中的某列数据是否可以为null，是否可以重复，某一列中的数据在当前表中必须唯一等限制。
约束的目的：只是为了保证存储在数据表中的数据完整性和有效性。
唯一约束：unique
字段 类型 unique; #该列（字段）的值不允许重复，一张表中可以有多列设置唯一。
非空约束：not null
字段 类型 not null; #表示该字段的值不能为空，一张表中可以多列设置非空。
主键约束：primary key
字段 类型 primary key; #效果非空+唯一，一张表中只能有一个字段为主键。
外键约束: foreign key
字段 类型 foreign key(外键) references 主表(主键);
自增长：auto_increment（非常重要。）
字段 类型 auto_increment; #必须整数类型，一般结合主键一起使用。
1）自增长必须是整数类型的字段，每次添加的时候，无须指定值，由数据库指定值，并且该值用后，就不在使用。
2）自增长从1开始，值不会重复使用，添加的时候无须为自增长字段设值
3）自增长的字段也可以手动赋值，但是不推荐，会造成冲突
提示：Oracle当中也提供了一个自增机制，叫做：序列（sequence）对象。
默认值：default
字段 类型 default '默认值'; #该字段不赋值时则使用指定的默认值
注释：comment
字段 类型 comment '注释内容';
和#号的文本注释内容不同，comment注释的内容会被编译运行，显示在表结构当中。
练习
创建一个 student2 表，添加相应的约束：
create table student2(
id int primary key auto_increment,
snum char(5) unique not null,
name varchar(10) not null,
sex char(1),
age int,
score double,
birthday date
);
5.3、数据表结构修改
修改数据表：可以对表名、表中的列名、列的类型、列的约束进行增删改。
基础语法
alter table 表名 增/删/改 字段 类型(长度) 约束;
增加列
alter table 表名 add 字段 类型(长度) 约束;
修改现有列类型、长度和约束语法
alter table 表名 modify 字段 类型(长度) 约束;
修改现有列名称语法
alter table 表名 change 旧字段名 新字段名 类型(长度) 约束;
删除现有列语法
alter table 表名 drop 字段;
修改表的字符集语法
alter table 表名 character set utf8;
修改表名语法
rename table 旧表名 to 新表名;
5.4、数据表删除
drop table tableName;
六、数据表的增删改
数据表的常见操作简称：CRUD：create read update delete
6.1、向数据表插入数据
#添加数据(推荐): 当某列值可以为null，自增长或有默认值时可以省略
insert into tableName (字段1,字段2,...字段N) values (值1,值2,...值N);
#添加数据:
insert into tableName value(值1,值2,...值N);
#添加数据(推荐):
insert into tableName set 字段1=值1,字段2=值2,...字段N=值N;
#批量添加:
insert into tableName (字段1,字段2,...字段N) values
(值1,值2,...值N),
(值1,值2,...值N),
(值1,值2,...值N);
6.2、数据记录修改操作
update tableName set 字段1=新值,字段2=新值 where 条件语句;
修改数据时如果不指定修改条件，则修改某个字段的所有值，指定修改条件则只修改满足条件的数据的值。
6.3、数据记录的删除操作
delete from tableName where 条件语句;
注意：如果删除表中的记录时，没有添加where条件，这时会把表中的所有数据删除。
delete from person; 把person表中的所有数据全部删除，但是person的表还存在。
drop table person; 把person数据表从数据库中删除。
面试题：删除表中的数据时没有加where 条件，会删除表中的所有数据，它与truncate 有什么区别？
delete删除表中的数据是按照行逐行删除，效率低。
truncate它是先把表删掉，然后再把表创建出来，效率高。
七、数据表记录的查询
7.1、查询表中的数据
查询当前某个数据表的数据，查询的方式有多种，有时需要查询所有数据，有时需要查询部分满足条件的数据，所以我们需要知道如何对数据的条件进行判断。
查询语法
select * from tableName where 查询条件; # 根据条件查询表中的所有字段
select 字段1,字段2... from tableName where 查询条件; # 根据条件查询指定字段
7.2、MySQL运算符
| 比较运算符 | |
|---|---|
| > | 大于 |
| >= | 大于等于 |
| < | 小于 |
| <= | 小于等于 |
| = | 等于 |
| <> | 不等于 |
| 逻辑运算符 | |
|---|---|
and(与运算&&) | 逻辑与运算，一假即假 |
or(或运算||) | 逻辑或运算，一真即真 |
not(逻辑非!) | 逻辑非运算，取反 |
| null运算符 | |
|---|---|
字段 is null | 判断某个字段是否为null |
字段 is not null | 判断某个字段是否不为null |
| 区间判断 | |
|---|---|
字段 between 值1 and 值2 | 判断某个字段的值是否在值1和值2之间，包含值1值2 |
| in语句 | |
|---|---|
字段 in (值1,值2…); | 判断某个字段的值是否在()中的值之间 |
| 模糊查询 | |
|---|---|
字段 like 'pattern' | %占位符：表示任意字符串 _占位符：表示单个字符 |
7.3、查询练习
准备数据
create table student1(
id int primary key auto_increment,
age int ,
name varchar(32) not null,
sex varchar(10) not null,
score double not null,
birthday date
);
insert into student1 (id,age,name,sex,score,birthday) values(null,23,'张三','男',98,'1990-09-09');
insert into student1 (id,age,name,sex,score,birthday) values(null,24,'zhangsan','男',85,'1989-05-19');
insert into student1 (id,age,name,sex,score,birthday) values(null,23,'李四','男',57,'1990-02-09');
insert into student1 (id,age,name,sex,score,birthday) values(null,24,'王五','女',75,'1988-01-01');
insert into student1 (id,age,name,sex,score,birthday) values(null,25,'赵六','男',80,'1984-10-18');
insert into student1 (id,age,name,sex,score,birthday) values(null,null,'秋香','女',72,'1990-02-22');
insert into student1 (id,age,name,sex,score,birthday) values(null,27,'东香','女',76,'1987-12-13');
insert into student1 (id,age,name,sex,score,birthday) values(null,null,'南香','女',92,'1985-02-21');
insert into student1 (id,age,name,sex,score,birthday) values(null,25,'北香','女',73,'1984-05-12');
insert into student1 (id,age,name,sex,score,birthday) values(null,24,'翠花','女',67,'1991-07-18');
insert into student1 (id,age,name,sex,score,birthday) values(null,24,'翠花','女',67,'1991-07-18');
insert into student1 (id,age,name,sex,score,birthday) values(null,24,'ZHANGSAN','男',67,'1991-07-18');
练习
-- 1. 查询所有学生的姓名和年龄
select name , age from student1;
-- 2. 查询表中年龄大于等于24岁的学生展示姓名和年龄
select age,name from student1 where age >= 24;
-- 3. 查询年龄等于23的所有学生信息。
select * from student1 where age = 23;
-- 4. 查询年龄不等于23岁的学员信息
select * from student1 where age <> 23;
select * from student1 where age != 23;
-- 5. 查询成绩在80~100(包含)之间的学生信息
select * from student1 where score >=80 and score <=100;
select * from student1 where score between 80 and 100;
-- 6. 查询年龄>23，成绩>70的同学信息
select * from student1 where age > 23 and score > 70;
select * from student1 where age > 23 && score > 70;
-- 7. 查询年龄为18，23，25的同学信息
select * from student1 where age in(18,23,25);
select * from student1 where age = 18 or age = 23 or age = 25;
-- 8. 查询所有带香的学生信息
select * from student1 where name like '%香%';
-- 9. 查询没有年龄的学员信息
select * from student1 where age is null;
-- 10. 查询有年龄的学员信息
select * from student1 where age is not null;
-- 11. 查询姓名叫zhangsan的学生信息。
select * from student1 where name = 'zhangsan';# 忽略大小写
select * from student1 where binary name = 'zhangsan'; # 不忽略大小写
7.4、排序查询
语法
select * from 表名 order by 列名 asc|desc;#asc是升序排列，desc是降序排列
练习
-- 1.对年龄排序按从高到低（降序）的顺序输出。
select * from student1 order by age desc;
-- 2.对学生年龄按照升序排序，年龄相同按照成绩降序
select * from student1 order by age asc , score desc;
7.5、别名
别名：可以对查询出来的列名 起别名（另外的名字，理解为外号也可以）。
语法
select 列名 as 别名,列名 as 别名,列名 as 别名.... from 表名 where 条件;
在使用别名的时候，as 关键字可以省略。
7.6、排重查询
重复的数据只查看一次。
语法
select distinct 列名 from 表名 where 条件;
7.7、截取分页查询
截取的查询需要使用到表中数据的下标，数据表中的数据下标和数组类似，也是从0开始。
语法
select * from 表名 limit n; # 查询数据时，从0下标开始只取n条数据
select * from tableName limit startIndex,pageSize; #startIndex起始索引，pageSize每页显示的数据条数
# 需要用户直接操作的数据：pageNum(页码，如第1页，第2页) , pageSize(每页显示的数据条数)
# 起始索引的计算公式：startIndex = (pageNum - 1) * pageSize;
练习
-- 1. 截取student1表中的前n个数据。
select * from student1 limit 3;
-- 2. 分页查询student1表中的数据，每页显示4条数据。
select * from student1 limit 0 , 4;
select * from student1 limit 4 , 4;
select * from student1 limit 8 , 4;
-- 3. 首先对student1表按照成绩降序排序，然后分页，每页显示5条数据，取第二页。
select * from student1 order by score desc limit 5 , 5;
-- 4. 首先对student1表按照成绩升序排序，获取前4条数据。
select * from student1 order by score asc limit 4;
八、SQL中的函数
函数分为：聚合函数，分组函数，聚集函数，内建函数，合计函数…
SQL语言中定义了部分的函数，可以帮助我们完成对查询结果的计算操作：
- count()函数：统计个数
- sum()函数：一般用于数值类型的字段求和
- avg()函数：一般用于数值类型的字段求平均值
- max()、min() 函数求字段的最大值和最小值
语法：
select 聚合函数 from tableName;
8.1、count 函数
select count(*) from student1;
select count(age) from student1;
select count(distinct age) from student1;
# 统计成绩大于80分的学生个数
select count(score) from student1 where score > 80;
8.2、sum 函数
select sum(age) from student1;
select sum(score) from student1;
select sum(name) from student1; #字符串和值8，不应该计算字符串的和值
select sum(birthday) from student1; #日期当做数值计算结果，但是没有意义
#计算年龄和成绩的和值
select sum(age) + sum(score) from student1; # 1152
select sum(age+score) from student1; # 988
ifnull(字段,临时值)：判断字段是否为null，如果为null则使用临时值进行运算。
select sum( ifnull(age,0) + ifnull(score,0) ) from student;
8.3、avg 函数
select sum(score) / count(score) from student1;
select avg(score) from student1;
select avg(age) from student1;
8.4、max 和 min 函数
select max(score) as 最高分,min(score) 最低分 from student1;
select max(name) ,min(name) from student1;
8.5、分组函数
语法
select * from tableName group by 字段;
- 分组查询一般结合聚合函数一起使用，当某个字段被分组之后，聚合函数则根据分组的结果进行聚合函数的运算。
- 如果使用group by对数据进行分组之后还要过滤。这时一般不能使用where，因为where关键字的后面不能跟上面讲解的这些函数。如果需要在过滤的条件中加上述的函数，只能使用having关键字。
准备数据
create table orders(
id int primary key auto_increment,
product varchar(20),
price double
);
insert into orders values (NULL,'电视机',2999);
insert into orders values (NULL,'电视机',2999);
insert into orders values (NULL,'洗衣机',1000);
insert into orders values (NULL,'洗衣机',1000);
insert into orders values (NULL,'洗衣机',1000);
insert into orders values (NULL,'冰箱',3999);
insert into orders values (NULL,'冰箱',3999);
insert into orders values (NULL,'空调',1999);
insert into orders values (NULL,'空调',1999);
insert into orders values (NULL,'空调',1999);
insert into orders values (NULL,'巧乐兹',3);
insert into orders values (NULL,'巧乐兹',3);
insert into orders values (NULL,'巧乐兹',3);
insert into orders values (NULL,'巧乐兹',3);
练习
#查询所有的商品信息
select * from orders;
#查询每类商品的总价格
select product,sum(price) from orders group by product;
#查询每类商品的总价格大于2000的商品信息
select product,sum(price) from orders group by product having sum(price) > 2000;
#查询单件商品价格大于1000，总价格大于2000的商品信息
select product,sum(price) from orders where price > 1000 group by product having sum(price) > 2000;
#查询单件商品价格大于1000，总价格大于2000的商品信息,并对价格排序
select product,sum(price) from orders where price > 1000 group by product having sum(price) > 2000 order by sum(price);
select product,sum(price) as sum from orders where price > 1000 group by product having sum > 2000 order by sum;
#统计每类商品的购买个数
select product,count(*) count from orders group by product having count > 2 order by count desc;
总结：一个完整的DQL语句怎么写？
select.5..from.1..where.2..group by.3..having.4..order by.6..
九、数据库的多表操作
9.1、主键约束 primary key
每个表应该具有主键，主键可以标识记录的唯一性，主键分为单一主键和复合（联合）主键，单一主键是由一个字段构成的，复合（联合）主键是由多个字段构成的
主键有什么作用？
- 表的设计三范式中有要求，第一范式就要求任何一张表都应该有主键。
- 主键的作用：主键值是这行记录在这张表当中的唯一标识。（就像一个人的身份证号码一样。）
主键的分类？
根据主键字段的字段数量来划分：
单一主键（推荐的，常用的。）
复合主键(多个字段联合起来添加一个主键约束)（复合主键不建议使用，因为复合主键违背三范式。）
根据主键性质来划分：
自然主键：主键值最好就是一个和业务没有任何关系的自然数。（这种方式是推荐的）
业务主键：主键值和系统的业务挂钩，例如：拿着银行卡的卡号做主键，拿着身份证号码作为主键。（不推荐用）
注意：
1.最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候，主键值可能也需要随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复。
2.一张表的主键约束只能有1个。（必须记住）
9.2、外键约束 foreign key
外键主要是维护表之间的关系的，主要是为了保证参照完整性，如果表中的某个字段为外键字段，那么该字段的值必须来源于参照的表的主键，如：emp中的deptno值必须来源于dept表中的deptno字段值。
表已经存在添加外键语法
alter table 从表 add foreign key(外键) references 主表(主键);
创建表时添加外键语法
foreign key(外键) references 主表(主键);
- 外键可以为NULL。
- 外键字段引用其他表的某个字段的时候，被引用的字段必须是主键吗？
注意：被引用的字段不一定是主键，但至少具有unique约束。
9.3、级联操作
级联操作指的是通过操作主表的数据，从而影响到从表中的数据。级联操作分为:
级联更新 ：外键约束语句 on update cascade;
级联删除 ：外键约束语句 on delete cascade;
给emp表中设置级联操作
alter table tableName add foreign key(外键) references 主表(主键); on update cascade on delete cascade;
查看外键约束
show create table tableName;
删除外键约束语法
alter table tableName drop foreign key 外键约束名;
注意：级联操作存在风险，在使用时一定要特别注意。
9.4、多表查询
笛卡尔积问题
笛卡儿积就是把两个（多个）表的结果集相乘，A表中的每一条数据与B表中的每一条数据匹配并呈现，数量级就是两表的成绩，属性为列相加。
笛卡尔积的数据，对程序是没有意义的，我们需要对笛卡尔积中的数据再次进行过滤。对于多表查询操作，需要过滤出满足条件的数据，需要把多个表进行连接，连接之后需要加上过滤的条件。
9.4.1、内连接查询
常规查询语法
select * from table1,table2 where 表名1.字段 = 表名2.字段;
内连接语法
select * from 表名1 inner join 表名2 on 条件;
9.4.2、外连接查询
外链接分为：左外连接、右外连接、全（外）连接、自连接[自查自连接语法]。
左外连接
用左边表去右边表中查询对应记录，不管是否找到，都将显示左边表中全部记录。
语法：
select * from 表1 left outer join 表2 on 条件;
右外连接
用右边表去左边表查询对应记录，不管是否找到，右边表全部记录都将显示
语法：
select * from 表1 right outer join 表2 on 条件;
全外连接
左外连接和右外连接的结果合并，但会去掉重复的记录。
语法：
select * from 表1 full outer join 表2 on 条件; # 但是mysql数据库不支持此语法。
#在sql语句全连接，其实就是左外链接和右外连接之和，并且使用union去掉重复的数据。
select * from 表1 left outer join 表2 on 条件
union all
select * from 表1 right outer join 表2 on 条件;
9.4.3、SQL关联子查询
子查询：把一个SQL语句的查询结果作为另外一个SQL语句查询的参数存在。
扫一扫
1118
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
