MySQL数据库
| 阿里云国内75折 回扣 微信号：monov8 |
| 阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6 |
目录
数据库MySQL
引言，没有数据库发明之前采用方式就是数据以文件形式存储到自己的计算机中，因此文件路径不统一数据格式也不统一这样导致开发效率极低。之后就有了数据库服务，数据存储到网络中文件路径以及数据格式都得到统一了。从底层原理分析数据库也是操作数据的进程即一堆代码、这就意味着每一名程序员都有资格编写出数据库；从实际应用角度分析数据库是可视化操作界面即常说的软件、说明数据库也是CS架构的程序。数据库有两种分类，一是关系型数据库、二是非关系型数据库。本次通过学习关系型数据库MySQL来学习数据库的操作，因为数据库软件也很多但是操作方式大差不差。
- 关系型数据库有固定的表结构即字段名、字段类型；数据之间可以建立数据库层面的关系
- 非关系型数据库有没有固定表结构数据存储采用键值对的方式；数据之间无法建立数据层面的关系
关系型数据库举例
- MySQL 开源免费 使用最广 性价比高
- Oracle 收费 使用成本高安全性也高
- Postgresql开源免费 支持二次开发 兼容性高
- Mariadb 跟MySQL是同一个作者 也是开源免费
- sqlite 小型数据库主要用于本地测试Django框架就用到了
非关系型数据库举例
- Redis 使用频率最高的非关系型数据库也叫缓存数据库 最火
- MongoDB 文档型数据库 最像关系型数据库的非关系型数据库
一、MySQL基本操作SQL语句
- 登录MySQL的命令
mysql -uroot -p
- 修改登录密码命令
mysqladmin -uroot -p原密码 password 新密码
- 忘记密码补救命令
net stop mysql
mysqld --skip-grant-table
update mysql.user set password=password('123') where Host='localhost' and User='root';
最后关闭服务端然后正常运行方式启动即可解决忘记密码的问题
- 创建数据库
create database 库名;
- 编辑数据库
alter database 库名 charset='utf8';
- 删除数据库
drop database 库名;
- 切换数据库
use 库名;
- 创建表
create table 表名(字段名 字段类型,字段名 字段类型);
- 编辑表
alter table 表名 rename 新表名;
- 删除表
drop table 表名;
- 插入数据
nsert into 表名 values(数据值1,数据值2);
- 查询数据
select * from 表名;
- 编辑数据
update 表名 set 字段名=新数据 where 筛选条件;
- 删除数据
delete from 表名 where id=2;
- 查看所有的库名称
show databases;
- 查看所有的表名称
show tables;
- 查看所有的表记录
select * from mysql.user;
制作系统服务的操作
- 先把bin目录添加到环境变量
- 将MySQL添加到系统服务里面
- 首次添加不会自动启动需操作
查看系统服务 services.msc
启动系统服务 net start mysql
先关闭服务端 net stop mysql
移除系统服务 mysqld --remove
二、修改字符编码配置文件以及数据库存储引擎
1.修改字符编码
值得注意的是只有MySQL5.6及之前版本的MySQL数据库需要该操作 之后的版本都已经全部默认统一 如果想要永久编码配置需要操作配置文件。具体操作如
先找到默认编码配置文件my-default.ini
拷贝改文件并把文件名改为my.ini
清楚原有的内容再把以下代码CV即可
操作完之后需要重启服务端
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
2.数据库存储引擎
存储引擎 ；数据库针对数据采取的多种存取方式
查看数据库存储引擎的命令
show engines;
- MyISAM；MySQL5.5之前版本的默认存储引擎 存取数据速度快功能少安全性底
- InnoDB；MySQL5.5之前版本的默认存储引擎 速度不快功能较多安全性更高
- Memory；基于内存存取数据仅用于临时表数据存取
- Blackhole；任何写入的数据都会立刻丢失 黑洞
3.严格模式
当我们在使用数据库存储数据的时候 如果数据不符合规范 应该直接报错而不是擅自修改数据 这样会导致数据的失真(没有实际意义)正常都应该报错 但是我们之前不小心改了配置文件。
展示严格模式命令
show variables like '%mode%';
临时修改严格模式；如果想永久修改你就直接改配置既可
set session sql_mode='strict_trans_tables';在当前客户端有效
set global sql_mode='strict_trans_tables';在当前服务端有效
三、创建表的完整的语法
create table 表名(
字段名 字段类型(数字) 约束条件,
字段名 字段类型(数字) 约束条件,
字段名 字段类型(数字) 约束条件
);
- 字段名和字段类型是必须的
- 数字和约束条件是可选的
- 约束条件多了用逗号隔开
- 最后一行结尾不能加逗号
四、字段类型
1.字段类型之整型
tinyint 1bytes 正负号(占1bit)
smallint 2bytes 正负号(占1bit)
int 4bytes 正负号(占1bit)
bigint 8bytes 正负号(占1bit)
2.字段类型之浮点型
float(20,10)总共存储20位数 小数点后面占10
double(20,10)总共存储20位数 小数点后面占10
decimal(20,10)总共存储20位数 小数点后面占10
float < double < decimal
3.字段类型之字符类型
char(4) 最多存储四个字符 超出就报错 不够四个空格填充至四个
varchar(4) 最多存储四个字符 超出就报错 不够则有几位存几位
set global sql_mode='strict_trans_tables,pad_char_to_full_length';
4.字段类型之枚举与集合
枚举
多选一
create table t15(
id int,
name varchar(32),
gender enum('male','female','others')
);
insert into t15 values(1,'tony','猛男');
insert into t15 values(2,'jason','male');
insert into t15 values(3,'kevin','others');
集合
多选多(多选一)
create table t16(
id int,
name varchar(16),
hobbies set('basketabll','football','doublecolorball')
);
insert into t16 values(1,'jason','study');
insert into t16 values(2,'tony','doublecolorball');
insert into t16 values(3,'kevin','doublecolorball,football');
5.字段类型之日期类型
datetime 年月日时分秒
date 年月日
time 时分秒
year 年
create table t17(
id int,
name varchar(32),
register_time datetime,
birthday date,
study_time time,
work_time year
);
insert into t17 values(1,'jason','2000-11-11 11:11:11','1998-01-21','11:11:11','2000');
五、字段约束条件
1.无符号 零填充
id int unsigned
id int(5) zerofill
2.非空 not null
所有字段类型不加约束条件的情况下默认都是可以为空
create table table1(
id int,
name varchar(16) not null
);
3.默认值default
create table table2(
id int default 666,
name varchar(16) default '匿名'
);
4.唯一值unique
create table table3(
id int unique,
name varchar(32) unique
);
5.主键primary key 自增auto_increment
create table table4(
id int primary key
name varchar(32)
);
主键相当于非空且唯一not null and unique
如果没有明确主键那么第一个字段默认升级为主键
创表时应该有一个字段用来表示数据的唯一性通常用id字段
自增单独是无效、只能与主键配合着使用且只能出现一次
六、字段约束条件之外键及其创建
外键字段用于表示数据与数据之间关系的字段，也是两个表之间的关系，该关系有四种、关系的判断可以采用换位思考的原则。建立外键时先定义出含有普通字段的表再考虑外键字段。创建表时一定要先创建关联表、录入数据的时候也一定先录入被关联表、修改数据的时候外键字段无法修改和删除，可以有简化操作即级联更新级联删除。外键是强耦合不符合解耦合的特性所以很多时候实际项目中当表较多的情况可能不使用外键而是使用代码建立逻辑层面关系。
- 一对一：用户表与用户详情表，两张表之间一一对应的关系，外键在查询频率多的一方
- 一对多：员工表与部门表，一个可以一个不可以，外键建在多的一方
- 多对多：书籍表与作者表，两个都可以，需要创建第三张关系表
- 无关系：言外之意就是两张表之间没有关系哈哈哈
# 一对一
create table user(
id int primary key auto_increment,
name varchar(32),
detail_id int unique,
foreign key(detail_id) references userdetail(id)
on update cascade
on delete cascade
);
create table userdetail(
id int primary key auto_increment,
phone bigint
);
# 一对多
create table emp(
id int primary key auto_increment,
name varchar(32),
age int,
dep_id int,
foreign key(dep_id) references dep(id)
);
create table dep(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(64)
);
create table emp1(
id int primary key auto_increment,
name varchar(32),
age int,
dep_id int,
foreign key(dep_id) references dep1(id)
on update cascade
on delete cascade
);
create table dep1(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(64)
);
# 多对多
create table book(
id int primary key auto_increment,
title varchar(32),
price float(5,2)
);
create table author(
id int primary key auto_increment,
name varchar(32),
phone bigint
);
create table book2author(
id int primary key auto_increment,
author_id int,
foreign key(author_id) references author(id)
on update cascade
on delete cascade,
book_id int,
foreign key(book_id) references book(id)
on update cascade
on delete cascade
);
七、SQL语句查询关键字
- select跟字段信息
- from跟表信息
- where筛选
- group by分组
- having过滤
- distinct去重
- order by排序
- limit分页
- regexp正则表达式
SQL语句的编写也类似于代码编写不是一蹴而就也是需要反反复复的修修补补之后写出来的，针对select后面字段可以先用*号占位后面回来修改就可以、但是实际运用中很少有这个操作因为数据特别多的情况下非常浪费数据库资源学习该部分需要实操数据库，所以需要提前做数据准备，具体准备如下：
create table emp(
id int primary key auto_increment,
name varchar(20) not null,
gender enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);
#插入记录
#三个部门：教学，销售，运营
insert into emp(name,gender,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','浦东第一帅形象代言',7300.33,401,1), #以下是教学部
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('呵呵','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('乐乐','female',18,'20160513','sale',3000.29,402,2),
('拉拉','female',28,'20170127','sale',4000.33,402,2),
('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);
1.最常用的select from
select 后面要跟指定的字段名信息写*号默认差所有的字段信息
from 后面要跟指定的表名
select * from userinfo; # 从userinfo表里面查询所有的字段
select id, name, from userinfo; # 从userinfo表里面查询id，name字段
编写的时候先写select再写from，代码执行的时候恰好相反
2.筛选where
# 1.查询id大于等于3小于等于6的数据
select * from emp where id >= 3 and id <= 6;
select * from emp where id between 3 and 6;
# 2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary=20000 or salary=18000 or salary=17000;
select * from emp where salary in (20000,18000,17000);
# 3.查询id小于3大于6的数据
select * from emp where id<3 or id>6;
select * from emp where id not between 3 and 6;
# 4.查询员工姓名中包含字母o的员工姓名与薪资(模糊查询用like)
select * from emp where name like '%o%';
# 5.查询员工姓名是由四个字符组成的员工姓名与其薪资
select * from emp where name like '____';
select * from emp where char_length(name) = 4;
# 6.查询岗位描述为空的员工名与岗位名 针对null不能用等号，只能用is
select * from emp where post_comment is NULL;
3.分组group by
聚合函数 专门用于分组之后的数据统计
max\min\sum\avg\count 最大值、最小值、求和、平均值、计数
# 1.将员工数据按照部门分组
select * from emp group by post;
# 2.获取每个部门的最高工资
select post,max(salary) from emp group by post;
select post as '部门',max(salary) as '最高薪资' from emp group by post;
# 3.一次获取部门薪资相关统计
select post,max(salary) '最高薪',min(salary) '最低薪',avg(salary) '平均薪资',sum(salary) '月支出' from emp group by post;
# 4.统计每个部门的人数
select post,count(id) from emp group by post;
#5.统计每个部门的部门名称以及部门下的员工姓名
'''分组以外的字段无法直接填写 需要借助于方法'''
select post,name from emp group by post;
select post,group_concat(name) from emp group by post;
select post,group_concat(name,age) from emp group by post;
select post,group_concat(name,'|',age) from emp group by post;
select post,group_concat(name,'_NB') from emp group by post;
select post,group_concat('DSB_',name,'_NB') from emp group by post
4.过滤having
having与where本质是一样的 都是用来对数据做筛选只不过where用在分组之前(首次筛选)having用在分组之后(二次筛选)
# 1.统计各部门年龄在30岁以上的员工平均工资 并且保留大于10000的数据
步骤1:先筛选出所有年龄大于30岁的员工数据
select * from emp where age > 30;
步骤2:再对筛选出来的数据按照部门分组并统计平均薪资
select post,avg(salary) from emp where age > 30 group by post;
步骤3:针对分组统计之后的结果做二次筛选
select post,avg(salary) from emp where age > 30 group by post having avg(salary) > 10000;
5.去重distinct
数据必须一模一样才可以去重
select distinct id,age from emp; 关键字针对的是多个字段组合的结果
select distinct age from emp;
select distinct age,post from emp;
6.排序order by
select * from emp order by age; 默认升序 asc升序 desc降序
select * from emp order by age,salary desc; 先按照年龄升序排 相同的情况下再按照薪资降序排
7.分页limit
select * from emp limit 5; 直接限制展示的条数
select * from emp limit 5,5; 从第5条开始往后读取5条
8.正则表达式regexp
SQL语句的模糊匹配如果用不习惯 也可以自己写正则批量查询
select * from emp where name regexp '^j.*?(n|y)$';
八、多表查询
前期数据准备
create table dep(
id int primary key auto_increment,
name varchar(20)
);
create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'财务');
insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('dragon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);
1.多表查询方法之连表操作
select * from emp inner join dep on emp.dep_id=dep.id;内连接
该方法只连接两张表共有的部分，此外还有left、right、union join
2.多表查询方法之子查询
将一条SQL语句用括号括起来当成另外一条SQL语句的查询条件
这个只能用例子来展示
题目:求姓名是jason的员工部门名称
子查询类似于我们日常生活中解决问题的方式>>>:分步操作
步骤1:先根据jason获取部门编号
select dep_id from emp where name='jason';
步骤2:再根据部门编号获取部门名称
select name from dep where id=200;
总结select name from dep where id=(select dep_id from emp where name='jason');
九、多表查询练习跟Navicat可视化软件同步操作
查询所有的课程的名称以及对应的任课老师姓名
# 1.先确定需要用到几张表 课程表 分数表
# 2.预览表中的数据 做到心中有数
select * from course;
select * from teacher;
# 3.确定多表查询的思路 连表 子查询 混合操作
SELECT
teacher.tname,
course.cname
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid;
查询平均成绩大于八十分的同学的姓名和平均成绩
# 1.先确定需要用到几张表 学生表 分数表
# 2.预览表中的数据
select * from student;
select * from score;
# 3.根据已知条件80分 选择切入点 分数表
# 求每个学生的平均成绩 按照student_id分组 然后avg求num即可
select student_id,avg(num) as avg_num from score group by student_id having avg_num>80;
# 4.确定最终的结果需要几张表 需要两张表 采用连表更加合适
SELECT
student.sname,
t1.avg_num
FROM
student
INNER JOIN (
SELECT
student_id,
avg(num) AS avg_num
FROM
score
GROUP BY
student_id
HAVING
avg_num > 80
) AS t1 ON student.sid = t1.student_id;
查询没有报李平老师课的学生姓名
# 1.先确定需要用到几张表
老师表 课程表 分数表 学生表
# 2.预览每张表的数据
# 3.确定思路 思路1:正向筛选 思路2:筛选所有报了李平老师课程的学生id 然后取反即可
# 步骤1 先获取李平老师教授的课程id
select tid from teacher where tname = '李平老师';
select cid from course where teacher_id = (select tid from teacher where tname = '李平老师');
# 步骤2 根据课程id筛选出所有报了李平老师的学生id
select distinct student_id from score where course_id in (select cid from course where teacher_id = (select tid from teacher where tname = '李平老师'))
# 步骤3 根据学生id去学生表中取反获取学生姓名
SELECT
sname
FROM
student
WHERE
sid NOT IN (
SELECT DISTINCT
student_id
FROM
score
WHERE
course_id IN (
SELECT
cid
FROM
course
WHERE
teacher_id = (
SELECT
tid
FROM
teacher
WHERE
tname = '李平老师'
)
)
)
查询没有同时选修物理课程和体育课程的学生姓名(报了两门或者一门不报的都不算)
# 1.先确定需要的表
学生表 分数表 课程表
# 2.预览表数据
# 3.根据给出的条件确定起手的表
# 4.根据物理和体育筛选课程id
select cid from course where cname in ('物理','体育');
# 5.根据课程id筛选出所有跟物理 体育相关的学生id
select * from score where course_id in (select cid from course where cname in ('物理','体育'))
# 6.统计每个学生报了的课程数 筛选出等于1的
select student_id from score where course_id in (select cid from course where cname in ('物理','体育'))
group by student_id
having count(course_id) = 1;
# 7.子查询获取学生姓名即可
SELECT
sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
WHERE
course_id IN (
SELECT
cid FROM
course
WHERE
cname IN ('物理', '体育')
)
GROUP BY
student_id
HAVING
count(course_id) = 1
)
查询挂科超过两门(包括两门)的学生姓名和班级
# 1.先确定涉及到的表
分数表 学生表 班级表
# 2.预览表数据
select * from class
# 3.根据条件确定以分数表作为起手条件
# 步骤1 先筛选掉大于60的数据
select * from score where num < 60;
# 步骤2 统计每个学生挂科的次数
select student_id,count(course_id) from score where num < 60 group by student_id;
# 步骤3 筛选次数大于等于2的数据
select student_id from score where num < 60 group by student_id having count(course_id) >= 2;
# 步骤4 连接班级表与学生表 然后基于学生id筛选即可
SELECT
student.sname,
class.caption
FROM
student
INNER JOIN class ON student.class_id = class.cid
WHERE
student.sid IN (
SELECT
student_id
FROM
score
WHERE
num < 60
GROUP BY
student_id
HAVING
count(course_id) >= 2
);
十、Python操作MySQL及事物操作
需要下载pymysql第三方模块模块 pip3 install pymysql
import pymysql
# 1.连接MySQL服务端
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123',
db='db4_03',
charset='utf8mb4'
)
# 2.产生游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # [{},{}]
# 3.编写SQL语句
sql = 'select * from score;'
# 4.发送SQL语句
affect_rows = cursor.execute(sql) # execute也有返回值 接收的是SQL语句影响的行数
print(affect_rows)
# 5.获取SQL语句执行之后的结果
res = cursor.fetchall()
print(res)
1.获取数据
fetchall() 获取所有的结果
fetchone() 获取结果集的第一个数据
fetchmany() 获取指定数量的结果集
cursor.scroll(1,'relative') # 基于当前位置往后移动
cursor.scroll(0,'absolute') # 基于数据的开头往后移动
2.增删改查
autocommit=True # 针对增 删 改 自动确认(直接配置)
conn.commit() # 针对 增 删 改 需要二次确认(代码确认)
事务的四大特性(ACID)
A:原子性 事务中的各项操作是不可分割的整体 要么同时成功要么同时失败
C:一致性 使数据库从一个一致性状态变到另一个一致性状态
I:隔离性 多个事务之间彼此不干扰
D:持久性 也称永久性，指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的
create table user(
id int primary key auto_increment,
name char(32),
balance int
);
insert into user(name,balance)
values
('jason',1000),
('kevin',1000),
('tank',1000);
# 修改数据之前先开启事务操作
start transaction;
# 修改操作
update user set balance=900 where name='jason'; #买支付100元
update user set balance=1010 where name='kevin'; #中介拿走10元
update user set balance=1090 where name='tank'; #卖家拿到90元
# 回滚到上一个状态
rollback;
# 开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘
commit;