1、数据库分类
关系型数据库(SQL)
- Mysql、Oracle、SqlServer、DB2、SQLlite
- 通过表和表之间,行和列之间的关系进行数据的存储
非关系型数据库(NoSQL)Not only SQL
- Redis、MongDB
- 对象存储,通过对象自身的属性来决定
2、DBMS
数据库管理系统
- 数据库的管理软件,科学有效的管理数据、维护和获取数据
- MySQL数据库管理系统
3、MySQL简介
MySQL本质是一个关系型数据库管理系统,
前世:MySQLAB
今生:Oracle
MySQL是世界上最好的关系型数据库之一(开源),体积小、速度快、成本低,适用中小型网站、大型网站、集群
官网 www.mysql.com
5.7 稳定
现在 8.0
安装建议
- 尽量不要使用exe,卸载很麻烦,很难卸载干净注册表
- 尽可能使用压缩包安装
4、安装MySQL
配置环境变量
- 配置到bin目录下就可以
- 安装目录下新建my.ini
[mysqld]
basedir=D:\\softwares\mysql-5.7.32\
# 数据目录会自动生成,不要手动去建立
datadir=D:\\softwares\mysql-5.7.32\data\
port=3306
skip-grant-tables
管理员cmd下 -> 进入mysql的bin目录(cd /d + mysql的bin目录) -> 然后运行mysql -install 安装mysql服务
再输入 初始化数据文件
mysql --initialize-insecure --user=mysql
启动mysql
net start mysql
用命令进入管理界面
mysql -uroot -p
没有密码,直接回车
更改密码
update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';
mysql 8.0以上改密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;
配置文件中删除跳过密码
- 注释掉my.ini文件中最后一行的跳过密码
重启mysql
net stop mysql
net start mysql
清空mysql服务
sc delete mysql 尽量不要清空
5、mysql一些命令
show databases; -- 查看所有数据库
use school; -- 切换数据库
show tables; -- 查看所有表
desc student; -- 查看表的结构
create database haha; -- 创建数据库
-- 我的注释
/*
sql的多行注释
*/
6、四种语言
DDL 数据库定义语言
- 定义和管理数据对象,如数据库、数据表
- create、drop、alter
DML 数据库操作语言
- 用于操作数据库对象所包含的数据
- insert update
DQL 数据库查询语言
- 用于查询数据库数据
- select
DCL 数据库控制语言
- 用于管理数据库的语言,包括管理权限及数据更改
- grant、commit、rollback
7、数据库操作
创建数据库
create database [if not exists] 数据库名称;
移除数据库
drop database [if exists] 数据库名称;
使用数据库
use 数据库名称;
use `表名或字段名为特殊字符`;
查看数据库
show databases; -- 查看所有的数据库
修改表
-- 修改表名 alter table 旧表名 rename as 新表名
alter table teacher rename as teacher1;
-- 增加表的字段 alter table 表名 add 字段名 列属性
alter table teacher1 ADD age int(11);
-- 修改表的字段(重命名,修改约束)alter table 表名 modify 字段名 列属性[]
alter table teacher1 modify age varchar(11); -- 修改约束
alter table teacher1 change age age1 int(11); -- 字段重命名 alter table 表名 change 旧名字 新名字 列属性[]
alter table teacher1 change age age1 int(1); -- 字段重命名
删除表的字段
-- alter table 表名 drop 字段名
alter table teacher1 drop age1;
删除表(存在再删除)
所有的创建和删除操作尽量加上判断
drop table if exists teacher1;
8、数据库的列类型
数值
- tinyint 十分小的数据 1字节
- smallint 较小数据 2字节
- mediumint 中等大小数据 3字节
- int (长度:只宽度) 标准的整数 4字节
- bigint 较大数据 8字节
- float 浮点数 4字节
- double 浮点数 8字节
- decimal 字符串形式的浮点数 金融计算
字符串
- char 固定大小字符串 0-255
- varchar 可变长字符串 0-65535 常用(String)
- tinytext 微型文本 2^8-1
- text 文本串 2^16-1 (保存大文本)
时间日期
- date YYYY-MM-DD 日期格式
- time HH:mm:ss 时间格式
- datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
- timestamp 时间戳 1970.01.01到现在的毫秒数(常用)
- year 年份表示
null
- 没有值,位置
- 注意不要使用NULL进行运算,结果为NULL
9、数据库的字段属性(重点)
Unsigned:
- 无符号的整数
- 声明了该列不能声明为负数
zerofill:
- 0填充
- 不足的位数,使用0来填充 int(3) 5 -- 005
自增:
- 自动在上一条记录的基础上 + 1(默认)
- 通常用来设计唯一的主键 index 必须是整数类型
- 可以自定义设计主键自增的起始值和步长
非空(NULL not null):
- 假设设置为not null,如果不给它赋值,会报错
- NULL,如果不填写值,默认就是null
默认:
- 设置默认的值
- sex,默认值为男,如果不指定该列的值,会有默认值
固定字段(每一个表,必须存在以下五个字段)
- id 主键
- version 乐观锁
- is_delete 伪删除
- gmt_create 创建时间
- gmt_update 修改时间
10、创建数据库表
-- 注意:使用英文括号,表的名称和字段尽量使用``括起来
-- AUTO_INCREMENT 自增
-- 字符串使用 单引号括起来
-- 所有的语句后面加 ,(英文的)最后一个不用加
-- primary key 主键,一般一个表只有一个唯一的主键
create table if not exists `student`(
`id` int(4) not null auto_increment comment '学号',
`name` varchar(30) not null default '匿名' comment '姓名',
`pwd` varchar(20) not null default '123456' comment '密码',
`sex` varchar(2) not null default '女' comment '性别',
`birthday` datetime default NULL comment '出生日期',
`address` varchar(100) default NULL comment '家庭出生地址',
`email` varchar(50) default NULL comment '邮箱',
primary key(`id`)
)engine=innodb default charset=utf8
-- 查看创建数据库的定义
show create database school;
-- 查看创建数据表的定义
show create table student;
-- 显示表结构
desc student;
格式:
create table [if not exists] `表名`(
`字段名` 列类型 [属性] [索引] [注释],
...
`字段名` 列类型 [属性] [索引] [注释]
)[表类型] [字符集设置] [注释]
11、数据表的类型
-- 关于数据库引擎
INNODB: 默认使用
MyISAM: 早先年使用
在物理空间存在的位置
本质就是文件存储,所有的数据库文件都存在data目录下:
- 位置:MySQL安装目录\data\下存放数据库,目录名对应数据库名,该目录下文件名对应数据表
MySQL引擎在物理文件上的区别
- InnoDB在数据库表中只有一个*.frm文件,以及上级目录下的ibdata1文件
- MyISAM对应文件
- *.frm 表结构的定义文件
- *.MYD 数据文件(data)
- *.MYI 索引文件(index)
设置数据库的字符集编码
必须设置,会是mysql默认的字符集编码(不支持中文字符)
charset=utf8
在my.ini文件中设置
character-set-server=utf8
12、MySQL数据管理
外键(了解)
- 学生表 id name age grade
- 年级表 id name
- 学生的grade列引用年级表的id(约束)
CREATE TABLE `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`gradeid` INT(10) NOT NULL COMMENT '年级编号' ,
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`),
key `FK_gradeid` (`gradeid`),
constraint `FK_gradeid` foreign key (`gradeid`) references `grade`(`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
删除有外键关系的表,必须先删除引用别的表(从表),再删除被引用的表(主表)
创建表成功后,添加外键约束
-- 语法
alter table 表名 add constraint 约束名 foreign key(作为外键的列) references 那个表(哪个字段)
alter table `student` add constraint `FK_gradeid` foreign key(`gradeid`) references (`grade`)(`gradeid`)
13、DML语言(掌握)
insert添加
-- 语法结构
insert into 表名([字段1,字段2,字段3,...]) values (值1,值2,值3,...);
-- 插入多条语句
insert into 表名([字段1,字段2,字段3,...]) values (值1,值2,值3,...),(值4,值5,值6,...),...;
INSERT INTO `student`(`name`,`pwd`,`sex`,`birthday`,`gradeid`,`address`,`email`) VALUES('小王','34567','男','2019-03-24 09:23:55',1,'北京','234567@163.com')
-- 由于主键自增我们可以省略
-- 一般写插入语句,我们一定要数据和字段一一对应
updata修改
update 表名 set colnum_name=value where [条件]
update 表名 set colnum_name=value[,colnum_name=value,...] where [条件]
UPDATE `student` SET `name`='小' WHERE id BETWEEN 2 AND 3;
update `student` set `birthday`=current_time where `name`='长江7号' and sex='女';
delete删除
-- 语法
delete from 表名 [ where [条件]]
-- 删除数据
delete from `student` where id = 1;
truncate命令
作用:完全清空一个数据库表,表的结构和索引约束不会变
-- 清空student表
truncate table `student` -- 自增会归零
delete from `test` -- 不会影响自增
delete和truncate区别
相同点:都能删除数据,都不会删除表结构
不同点:
- truncate重新设置自增列,计数器会归零
- truncate 不会影响事务
- delete不会重新设置自增列
delete删除问题,重启数据库,现象
- innodb 自增列会从1开始(存在内存中,断电即失)
- MyISAM 继续从上一个自增量开始(存在文件中,不会丢失)
14、DQL查询数据(重点)
数据查询语言
- 所有的查询操作 select
- 简单、复杂都可查
- 使用频率最高
-- 查询全部的学生
select 字段 from 表名
select * from student
-- 查询指定字段
select `StudentId`,`StudentName` From student
-- 别名,给列名起名字as
select `StudentId` as 学号,`StudentName` as 学生姓名 From student as s
-- 函数 concat(a,b)
select concat('姓名:',StudentName) as 新名字 from student
去重distinct
作用:去除select查询出来的结果中重复的数据,只显示一条
-- 发现重复数据,去重
select distinct `studentNo` from student
数据库的列(表达式)
-- 查询系统版本(函数)
select version()
-- 用来计算(表达式)
select 100*3-1 as 计算结果
-- 查询自增的步长(变量)
select @@auto_increment_increment
-- 学员考试成绩 + 1分查看
select `studentNo`,`studentResult`+1 as '提分后' from result
数据库中的表达式:文本值、列、NULL、函数、计算表达式、系统变量…
select 表达式 from 表
where条件子句
作用:检索数据中符合条件的值
逻辑运算符:
- and &&
- or ||
- not !
模糊查询:
比较运算符
| 运算符 |语法 |描述 |
| —————— | —————— | —————— |
| is null | 为空 | |
| is not numm | 不为空 | |
| between…and|a between b and c | |
| like |a like b | SQL匹配,如果a匹配b,则结果为真 |
| in |a in (a1,a2,a3,…) | |
-- %代表0到任意个字符
-- _代表一个字符
select `studentNo`,`studentName` from `student` where studentName like '刘%'
select `studentNo`,`studentName` from `student` where studentName like '%家%'
-- 查询1001,、1002、1003号学员
select `studentNo`,`studentName` from `student` where studentNo in (1001,1002,1003);
-- 查询在北京和西安的学员
select `studentNo`,`studentName` from `student` where `address` in ('北京','西安');
select `studentNo`,`studentName` from `student` where `address`='' or address is null;
select `studentNo`,`studentName` from `student` where `address`is not null;
联表查询:
- join
- 七种join理论
- inner join
- left join
- right join
-- 查询参加了考试的学生(学号、姓名、科目编号、分数)
select s.studentNo,studentName,SubjectNo,StudentResult
from student as s
inner join result as r
where s.studentNo = r.studentNo;
select s.studentNo,studentName,SubjectNo,StudentResult
from student as s
inner join result as r
on s.studentNo = r.studentNo;
select s.studentNo,studentName,SubjectNo,StudentResult
from student as s
right join result as r
on s.studentNo = r.studentNo;
select s.studentNo,studentName,SubjectNo,StudentResult
from student as s
left join result as r
on s.studentNo = r.studentNo;
select s.studentNo,studentName,SubjectNo,StudentResult
from student as s
left join result as r
on s.studentNo = r.studentNo
where studentResult is null;
-- 查询参加考试的同学信息(学号、学生姓名、科目名、分数)
select s.studentNo,studentName,subjectName,studentResult
from student as s
right join result as r
on s.studentNo = r.studentNo
inner join subject as su
on r.subjectNo = su.subjectNo
-- 我要查询哪些数据 select
-- 从那几个表中查From表 xxx join 连接的表 on 交叉条件
-- 假设存在一种多张表查询,慢慢来,先查询两张表然后再慢慢增加
自连接:
定义: 自己的表和自己的表连接,核心:一张表拆为两张一样的表即可。
-- 查询父子信息:把一张表看为两个一模一样的表
-- 查询父类对应的子类
select a.`categoryName` as '父栏目',b.`categoryName` as '子栏目'
from `category` as a,`category` as b
where a.`categoryid` = b.`pid`
-- 查询参加 数据库结构-1考试的同学信息:学号,学生姓名,科目名,分数
select s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
from student s
inner join `result` r
on s.StudentNo = r.StudentNo
inner join `subject` sub
on r.`SubjectNo` = sub.`SubjectNo`
where subjectName='数据库结构-1';
select完整语法:
select [all|distinct]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
from table_name [as table_alias]
[left | right| inner join table_name2] -- 联表查询
[where ...] -- 指定结果需满足的条件
[group by ...] -- 指定结果按照哪几个字段来分组
[having] -- 过滤分组的记录必须满足的次要条件
[order by] -- 指定查询记录按一个或多个条件排序
[limit {[offset,]row_count | row_countOFFSET offset}]; -- 指定查询的记录从哪条至哪条
分页limit和排序 order by
排序
升序:ASC
降序:DESC
-- order by 通过那个字段排序,怎么排
select s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
from student s
inner join `result` r
on s.StudentNo = r.StudentNo
inner join `subject` sub
on r.`SubjectNo` = sub.`SubjectNo`
where subjectName='数据库结构-1'
order by studentResult ASC;
分页:
原因:缓解数据库压力,给人的体验更好(图片:瀑布流)
语法:limit 起始值,页面的大小
- limit 0,5 1-5
网页应用:当前页,总的页数,页面的大小
-- (n-1)*pageSize,pageSize
-- 【pageSize: 页面大小 (n-1)*pageSize起始值】
-- 【n: 当前页】
-- 【数据总数/页面大小 = 总页数】
-- limit (查询起始下标, pageSize)
select s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
from student s
inner join `result` r
on s.StudentNo = r.StudentNo
inner join `subject` sub
on r.`SubjectNo` = sub.`SubjectNo`
where subjectName='数据库结构-1'
order by studentResult ASC
-- 查询JAVA第一学年 课程成绩排名前十的学生,并且分数要大于80的学生信息(学号、姓名、课程名称、分数)
select s.`studentNo`,`studentName`,`subjectName`,`studentResult`
from student as s
inner join subject as sub
on s.studentNo = sub.studentNo
inner join result as r
on r.subjectNo = sub.subjectNo
where subjectName = 'JAVA第一学年' and studentResult > 80
order by studentResult DESC
LIMIT 0,10
子查询:
where(值是固定的,这个值是计算出来的)
本质:在where语句中嵌套一个子查询语句
由里及外
where (select * from)
-- 查询数据库结构-1的所有考试结果(学号、科目编号、成绩),降序排列
select `studentNo`,`subjectNo`,`studentResult`
from `result`
where subjectNo = (
select subjectNo from `subject`
where subjectName = '数据库结构-1'
)
order by studentResult DESC
-- 分数不小于80分的学生的学号和姓名
-- 思路
-- 首先查询学生表的学号和姓名
-- 再查询分数不小于80分的学号
select distinct s.`studentNo`,`studentName`
from student s
where studentNo in (
select `studentNo` from result
where StudentResult >= 80 and subjectNo = (
select subjectNo from `subject` where
`subjectName` = '高等数学-2'
)
)
-- 查询c语言-1 前5名同学的成绩的信息(学号、姓名、分数)
select `studentNo`,`studentName`,`studentResult`
from student
where `studentNo` in (
select `studentNo`
from result
where subjectNo = (
select `subjectNo` from subject where `subjectName` = 'c语言-1'
)
order by studentResult desc
limit 0,5
)
分组和过滤
-- 查询不同课程的平均分,最高分,平均分大于80
-- 核心:(根据不同的课程分组)
select subjectName, avg(studentResult) as 平均分,max(studentResult) as 最高分, min(studentResult) as 最低分
from result r
inner join `subject` sub
on r.`subjectNo` = sub.`subjectNo`
group by r.subjectNo -- 通过什么字段来分组
having 平均分>80