MySQL数据库

• 数据库MySQL
  • 一、MySQL基本操作SQL语句
  • 二、修改字符编码配置文件以及数据库存储引擎
    • 1.修改字符编码
    • 2.数据库存储引擎
    • 3.严格模式
  • 三、创建表的完整的语法
  • 四、字段类型
    • 1.字段类型之整型
    • 2.字段类型之浮点型
    • 3.字段类型之字符类型
    • 4.字段类型之枚举与集合
    • 5.字段类型之日期类型
  • 五、字段约束条件
    • 1.无符号 零填充
    • 2.非空 not null
    • 3.默认值default
    • 4.唯一值unique
    • 5.主键primary key 自增auto_increment
  • 六、字段约束条件之外键及其创建
  • 七、SQL语句查询关键字
    • 1.最常用的select from
    • 2.筛选where
    • 3.分组group by
    • 4.过滤having
    • 5.去重distinct
    • 6.排序order by
    • 7.分页limit
    • 8.正则表达式regexp
  • 八、多表查询
    • 1.多表查询方法之连表操作
    • 2.多表查询方法之子查询
  • 九、多表查询练习跟Navicat可视化软件同步操作
  • 十、Python操作MySQL及事物操作

数据库MySQL

​引言，没有数据库发明之前采用方式就是数据以文件形式存储到自己的计算机中，因此文件路径不统一数据格式也不统一这样导致开发效率极低。之后就有了数据库服务，数据存储到网络中文件路径以及数据格式都得到统一了。从底层原理分析数据库也是操作数据的进程即一堆代码、这就意味着每一名程序员都有资格编写出数据库；从实际应用角度分析数据库是可视化操作界面即常说的软件、说明数据库也是CS架构的程序。数据库有两种分类，一是关系型数据库、二是非关系型数据库。本次通过学习关系型数据库MySQL来学习数据库的操作，因为数据库软件也很多但是操作方式大差不差。

• 关系型数据库有固定的表结构即字段名、字段类型；数据之间可以建立数据库层面的关系
• 非关系型数据库有没有固定表结构数据存储采用键值对的方式；数据之间无法建立数据层面的关系

关系型数据库举例

1. MySQL 开源免费 使用最广 性价比高
2. Oracle 收费 使用成本高安全性也高
3. Postgresql开源免费 支持二次开发 兼容性高
4. Mariadb 跟MySQL是同一个作者 也是开源免费
5. sqlite 小型数据库主要用于本地测试Django框架就用到了

非关系型数据库举例

(资料图片)

1. Redis 使用频率最高的非关系型数据库也叫缓存数据库 最火
2. MongoDB 文档型数据库 最像关系型数据库的非关系型数据库

一、MySQL基本操作SQL语句

• 登录MySQL的命令

mysql -uroot -p

• 修改登录密码命令

mysqladmin -uroot -p原密码 password 新密码

• 忘记密码补救命令

net stop mysqlmysqld --skip-grant-tableupdate 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;

制作系统服务的操作

1. 先把bin目录添加到环境变量
2. 将MySQL添加到系统服务里面
3. 首次添加不会自动启动需操作

查看系统服务 services.msc启动系统服务 net start mysql先关闭服务端 net stop mysql移除系统服务 mysqld --remove

二、修改字符编码配置文件以及数据库存储引擎

1.修改字符编码

值得注意的是只有MySQL5.6及之前版本的MySQL数据库需要该操作 之后的版本都已经全部默认统一 如果想要永久编码配置需要操作配置文件。具体操作如

先找到默认编码配置文件my-default.ini拷贝改文件并把文件名改为my.ini清楚原有的内容再把以下代码CV即可操作完之后需要重启服务端[mysqld]character-set-server=utf8mb4collation-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. 字段名和字段类型是必须的
2. 数字和约束条件是可选的
3. 约束条件多了用逗号隔开
4. 最后一行结尾不能加逗号

四、字段类型

1.字段类型之整型

tinyint   1bytes   正负号(占1bit)smallint   2bytes   正负号(占1bit)int       4bytes   正负号(占1bit)bigint   8bytes   正负号(占1bit)

2.字段类型之浮点型

float(20,10)总共存储20位数 小数点后面占10double(20,10)总共存储20位数 小数点后面占10decimal(20,10)总共存储20位数 小数点后面占10float

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语句查询关键字

1. select跟字段信息
2. from跟表信息
3. where筛选
4. group by分组
5. having过滤
6. distinct去重
7. order by排序
8. limit分页
9. 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不能用等号，只能用isselect * 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 courseINNER 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 先获取李平老师教授的课程idselect tid from teacher where tname = "李平老师";select cid from course where teacher_id = (select tid from teacher where tname = "李平老师");# 步骤2 根据课程id筛选出所有报了李平老师的学生idselect 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.根据物理和体育筛选课程idselect cid from course where cname in ("物理","体育");# 5.根据课程id筛选出所有跟物理 体育相关的学生idselect * 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_idhaving 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筛选即可SELECTstudent.sname,class.captionFROMstudentINNER JOIN class ON student.class_id = class.cidWHEREstudent.sid IN (SELECTstudent_idFROMscoreWHEREnum < 60GROUP BYstudent_idHAVINGcount(course_id) >= 2);

十、Python操作MySQL及事物操作

需要下载pymysql第三方模块模块 pip3 install pymysqlimport 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;