使用教程一、MySQL基础知识二、数据库基本操作三、数据操作四、备份与恢复五、实例六、查询七、存储关系八、事务九、索引十、与python的交互十一、创建表示例一、MySQL基础知识E-R模型1.E表示实体R表示关系 2.一个实体转换为数据库中的一个表 3.关系描述两个实体之间的对应规则包括一对一、一对多、多对多 4.关心转换为数据库表中的一个列*在关系型数据库中一行就是一个对象三范式1.经过研究和对使用中问题的总结对于设计数据库提出来一些规范这些规范被称为范式 2.第一范式1NF列不可拆分 3.第二范式2NF唯一标识 4.第三范式3NF引用主题 5.说明后一个范式都是在前一个范式的基础上建立得字段类型1.数字intdecimal decimal(5,2)数字长度为5包含2位小数 2.字符串charvarchartext char(8)存的字符固定的长度为8不够会在右边加上空格超过自动补位 varchar(16)存的字符是可变的长度为16且固定超过16取前16个 text大文本 编码utf-8gb-2312 3.日期datetime 4.bit布尔 bit默认为一个位 bit(8)存放8个二进制的位约束主键primary key不会重复根据主键查数据很快物理存储方式 非空not null 唯一unique值不能重复 默认default 外键foreign key逻辑删除-这点很重要适用于重要数据在表里面添加一栏为 ‘isDelete’0表示未删除1表示删除不会从物理层面删除在需要的时候吧0和1相互二、数据库基本操作进入数据库进入数据库mysql -uroot -p密码直接回车 进入MySQL后输入help可以查看更多命令 在MySQL输入quit或者exit退出 在MySQL目录下输入mysql --help 可以查看更多的命令 查看版本select version(); 要加分号 显示当前时间select now(); 要加分号远程连接mysql -h ip地址 -uroot -p 1.-h 后面写要连接的主机ip地址 2.-u 后面写连接的用户名 3.-p 回车后写密码数据库操作1.创建数据库create database 数据库名 charsetutf8; 2.删除数据库drop database 数据库名; 3.切换数据库接下来的操作都是在这个数据库上进行use 数据库名; 4.查看当前选择的数据库select database(); 5.查看当前所有数据库show databases;表操作1.查看当前数据库中所有表show tables; 2.创建表auto_increment表示自动增长create table 表名(列及类型...); create table students( id int auto_increment paimary key not null, name varchar(10) not null, gender bit default 1, birthday datetime, isDelete bit default 0 ); 多个列用,隔开 3.修改表alter table 表名 add|change|drop 列名 类型; alter table students add isDelete bit default 0; 3.1 删除列 ALTER TABLE users DROP COLUMN phone; 3.2 修改列 ALTER TABLE users MODIFY COLUMN username VARCHAR(100); 3.3 增加列 ALTER TABLE users ADD COLUMN sp_no VARCHAR ( 50 ) DEFAULT NULL COMMENT 关联企业微信审批单号; 4.查看当前表结构desc students; 5.删除表drop table 表名; 6.查看表结构desc 表名; 7.更改表名称rename table 原表名 to 新表名; 8.查看表的创建语句show create table 表名;三、数据操作查询select * from 表名;增加-插入的值的结构顺序要和原表一样1.全列插入insert into 表名 values(...); insert into students values(0,郭靖,1,1990-1-1,0) 2.缺省插入insert into 表名(列1...) values(...); a.insert into students(name) values(黄蓉); b.insert into students(gender,name) values(0,小龙女); 3.同时插入多条数据insert into 表名 values(...),(...)...; 或 insert into 表名 values(值1,...),(值1,...)...; a.insert into students(name) values(杨过),(雕),(黄药师);INSERT INTO account (id, code, pwd, name, nickname, address, city, birthday, age, avatar, sex) VALUES (4, 2776, b123456, test, 测试人员, 香港, 东区, 2024-11-08, 25, NULL, 1), (5, 2777, c123456, test2, 测试人员2, 香港, 西区, 2024-11-09, 26, NULL, 1);修改update 表名 set 列1值1,... where 条件; 实例update students set birthday1990-2-2 where id2;删除delete from 表名 where 条件; 实例delete from students where id5;逻辑删除-本质就是修改操作update1.alter table students add isDelete bit dafault 0; 2.删除列update students isdelete1 where ...;四、备份与恢复数据备份1.进入超级管理员sudo -s 2.进入mysql目录cd ../mysql 3.运行mysqldump命令mysqldump -uroot -p 数据库名 ~/存储位置/backup.sql数据恢复1.连接mysql创建新的数据库 2.退出连接执行如下命令 mysql -uroot -p 新的数据库名 ~/存储位置/backup.sql五、实例1.设计班级表与学生表关联并进行查询create table T_classes( id int auto_increment primary key not null, name varchar(10) ); create table T_students( id int auto_increment primary key not null, name varchar(10), classid int, FOREIGN key(classid) references T_classes(id) ); create view V_stu_cls as select T_students.id, T_students.name, T_classes.name as title from T_students inner join T_classes on T_classes.idT_students.classid;2.设计分类表自关联并进行查询create table T_type( id int auto_increment primary key not null, name varchar(10), pid int, FOREIGN key(pid) references T_type(id) ); create view V_type as select son.* from T_type as father inner join T_type as son on father.idson.pid;3.创建两个表并关联两个表向表中添加数据create table rooms( id int primary key not null, title varchar(10) ); create table stu( id int auto_increment primary key not null, name varchar(10), roomid int ); alter table stu add constraint stu_room foreign key(roomid) references rooms(id); insert into stu values(314,聚义厅); insert into stu values(0,郭靖,314);六、查询查询的基本语法select * from 表名;1.from关键字后面写表名表示数据来源于这张表 2.select后面写表中的列名*表示在结果中显示表中所有的列 3.在select后面的列名部分可以使用as为列起别名这个别买出现在结果集中 4.如果要查询多个列之间使用逗号分隔 实例 use python3; 数据库名 show tables; 显示所有的表 select * from students; 查看students表 select id,name from students; 只查看id和name得到结果集消除重复行在select后面列前使用distinct可以消除重复的行 select distinct gender from students; gender的结果只有1和0结果只会一个1和一个0 select distinct id,gender from students; id没有重复值gender有重复值以id为准条件使用where句子对标准化的数据进行帅选结果为true的行会出现在结果集中条件运算符,,,,,!select * from 表名 where 条件; select * from students where id3; 查询编号大于3的学生 select * from students where name!黄蓉; 查询姓名不是黄蓉的学生 select * from students where idDelete0; 查询没被删除的学生逻辑运算符and,or,notselect * from students where id3 and gender0; 查询编号大于3的女同学 select * from students whereid4 or isDelete0; 查询编号小于4或没被删除的学生模糊查询1.like 2.%表示任意多个任意字符 3._表示一个任意字符 select * from students where name like 黄_; 查询叫黄x的学生 select * from students where name like %龙%; 查询名字里有龙的学生 select * from students where name like 黄%; 查询姓黄的学生 select * from students where name like 黄% or name like %靖%; 查询姓黄或者叫靖的学生范围查询1.in表示在一个非连续的范围内 select * from students where id in(1,3,8); 查询编号为1或3或8的学生 2.between...and...表示在一个连续的范围内 select * from students where id between 3 and 8; 查询学生id是3到8的学生 select * from students where id between 3 and 8 and gender1; 查询id是3到8的男生空判断注意null和是不同的 1.判空is null select * from students where hometown is null; 查询没有填地址的学生 select * from students where birthday is null; 查询没有填写生日的学生 2.判非空is not null select * from students where hometown is not null; 查询填写地址的学生 select * from students where hometown is not null and gender0; 查询谢了地址的女生优先级1.小括号not比较运算符逻辑运算符 2.and比or西安运算如果同时出现并希望先算or需要结合()使用聚合为了快速得到统计数据提供了5个聚合函数1.count(*)表示计算总行数括号中写*与列名结果是相同的 select count(*) from students; 查询学生总数 select count(*) from students where isDelete0; 查询未被删除的学生数量 2.max(列)表示求此列的最大值 select max(id) from students where gender0; 查询女生编号的最大值 3.min(列)表示求此列的最小值 select min(id) from students where isDelete0; 查询未删除的学生的最小编号 4.sum(列)表示求此列的和 select sum(id) from students where gender1; 查询男生的编号之和 5.avg(列)表示求此列的平均值 select avg(id) from students where isDelete0 and gender0; 查询未删除女生的编号平均值分组1.按照字段分组表示此字段相同的数据会被放到一个组中 2.分组后只能查询出相同的数据列对于有差异的数据列无法出现在结果集中 3.可以对分组后的数据进行统计做聚合运算 4.语法select 列1列2聚合... from 表名 group by 列1,列2,列3...; a.查询男女生总数 select gender as 性别,count(*) from students group by gender; b.查询各城市人数 select hometown as 家乡,count(*) from students group by hometown;分组后的数据筛选语法select 列1,列2,聚合... from 表名 group by 列1,列2,列3... having 列1,..聚合...; having后面的条件运算符号与where的相同 1.查询男生总人数两种方法操作的集不一样a是在原始集上操作b是在结果集上操作 a.select count(*) from students where gender1; b.select gender as 性别,count(*) from students group by gender having gender1; 2.查询男生总人数 select gender,count(*) from students group by gender having gender0; 3.查询同性别中大于2的人数 select gender,count(*) from students group by gender having count(*)2; select gender,count(*) as num from students group by gender having num2; where与having 1.where是对from后面指定的表进行数据筛选属于对原始数据的筛选 2.having是对group by的结果进行筛选排序语法select * from 表名 order by 列1 asc|desc,列2 asc|desc,...; 1.讲行数据按照列1进行排序如果某些行列1的值相同时则按照列2排序以此类推 2.默认按照劣质从小到大排序 3.asc从小到大排列即升序 4.desc从大到小排序即降序 a.查询未删除男生学生信息按学号降序 select * from students where gender1 and isDelete0 order by id desc; b.查询未删除科目信息按名称升序 先添加isDeletealter table subjects add isDelete bit defaule 0; 再添加被删除的数据update subjects set isDelete1 where titlr in(linux,redis); 再进行查询select * from subject where isDelete0 order by title;获取部分行当数据量过大时在一页中查看数据是意见非常麻烦的事情语法select * from 表名 limit start,count; 从start开始获取count条数据start索引从0开始 select * from students limit 2,1; 从2开始查询后面1条数据 select * from students limit 1,3; 从1开始查询后面3条数据 实例分页 1.已知每页显示m条数据房钱显示第n页 2.求总页数此段逻辑后面会在python中实现 a.查询总条数p1 b.使用p1除以m德奥p2 c.如果整除则p2为总数页 d.如果不整除则p21为总页数 3.求第n页的数据 select * from students where isDeletd0 limit(n-1)*m,m;总结1.完整的查询语句 select distinct * from 表名 where ... group by ... having ... oeder by ... limit start,count; 2.执行顺序 a.from 表名 b.where ... c.group by ... d.select distinct * e.having ... f.order by ... g.limit start,count 3.实际使用中知识语句中某些部分的组合而不是全部语句七、存储关系简介a.实体与实体之间有三种关系这些关系也要存储下来 b.视图用于完成查询语句的封装 c.十五可以保证复杂的的增删改操作有效 d.当数据巨大时为了提高查询速度可以通过索引实现关系-不允许闭合关系也是数据创建成绩表scores结构如下 id学生科目成绩 学生列的数据不是在这里新建的而应该从学生表引用过来关系也是一条数据根据范式要求应该存储学生的 编号而不是学生的姓名等其他信息。同理科目表也是关系列引用科目表中的数据。 学生表——班级表 郭靖 Python 100 students.name subjects.title scores.score 实例 create teble scores( id int primary key auto_increment not null, stuid int subid int, score decimal(4,1), foreign key(stuid) references students(id), foreign key(subid) references subjects(id) ); insert into scores values(0,100,1,1); ...外键的级联操作a.在删除students表的数据时如果这个id值在scores中已经存在则会抛出异常 b.推荐使用逻辑删除还可以解决这个问题 c.可以创建表时指定级联操作也可以在创建表后再修改外键的级联操作 语法alter table scores add constraint stu)sco foreign key(stuid) reference students(); d.级联操作的类型 restrict限制默认值抛异常 cascade级联如果主表的记录删掉则从表中相关联的记录都将被删除 set null将外键设置为空 no action什么都不做级联查询select students.name,subjects.title,scores.score from scores inner join students on scores.stuidstudents.id inner join subjects on scores.subidsubjects.id; 另一种写法 select students.name,subjects.titlr,scores.score from students inner join scores on scores.stuidstudents.id inner join subjects on scores.subidsubjects.id;连接查询a.表A inner join 表B表A与表B匹配的行会出现在结果中 b.表A left join 表B表A与表B匹配的行会出现在结果中外加表A中堵头的数据未对应的数据使用null填充 c.表A right join 表B表A与表B匹配的行会出现在结果中外加表B中堵头的数据未对应的数据使用null填充 e.在查询或条件中推荐使用‘表名’、‘列名’的语法 f.如果多个表中列名不重复可以省略‘表名’部分 g.如果表的名称太长可以在表名后面使用‘as 简写名’或‘简写名’为表起个临时的简写名称 总结 select distinct 列* from 表1 inner|left|right join 表2 on 表1与表2的关系 where ... group by ... having ... order by ... asc|desc limit start,count;关联查询1.查询学生的姓名、平均分 select students.name,avg(scores.score) from scores inner join students on scores.stuidstudents.id group by students.name; 2.查询男生的姓名、平均分 select students.name,avg(scores.score) from scores inner join students on scores.stuidstudents.id where students.gender1 group by students.name; 3.查询科目的名称、平均分 select subjects.title,avg(scores.score) from scores inner join subjects on scores.subidsubjects.id group by subjects.title; 4.查询男生的姓名、总分 select name,sum(score) from students inner join scores on students.idscores.stuid where gender1 group by students.id; 5.查询为身处科目的名称、最高分、平均分 select subjects.title,avg(scores.score),max(scores.score) from scores inner join subjects on scores.subidsubjects.id where subjects.isDelete0 group by subjects.title;自关联1.设计省信息的表结构provinces id title 2.设计市信息的表结构citys id title proid 3.citys表的proid表示城市所属的省对应着provinces表的id值 4.将两个表合成一个表可以定义表areas结构如下: id title pid 5.实例 create table areas( id int primary key auto_increment not null, title varchar(10), pid int, foreign key(pid) references areas(id) ) a.从sql文件中导入数据 source areas.sql b.查询一共有多少个省 select count(*) from areas ehere pid is null; c.查询名称为“山西省”的所有城市 select city.* from areas as city inner join areas as province on city.pidprovince.id where province.title山西省; d.查询市的名称为“广州市”的所有区县 select dis.* from areas as dis inner join areas as city on city.iddis.id where city.title广州市; e.查询山西省的所有市 select sheng.id as sid,sheng.title as stitle, shi.id as shiid,shi.title as shititle from areas as sheng inner join areas as shi on sheng.idshi.pid where sheng.pid is null and sheng.titlr山西省;视图1.将多个表进行连接查询 select * from scores inner join students on scores.stuidstudents.id inner join subjects on scores.subidsubjects.id; 2.创建一个视图 create view v_stu_sub_sco as select stu.*,sco.score,sub.title from scores as sco inner join students as stu on sco.stuidstu.id inner join subjects as sub on sco.subidsub.id where stu.isDelete0 and sub.isDelete0;八、事务事务1.当一个业务逻辑需要多个sql完成时如果其中某条sql语句出错则希望整个操作都退出 2.使用事务可以完成退回的功能保证业务逻辑的正确性 3.事务四大特性 a.原子性事务中的全部操作在数据库中是不可分割的要么全部完成要么都不执行 b.一致性几个并行执行的事务其执行结果必须与按某一顺序串执行的结果相一致 c.隔离性事务的执行不收其他事务的干扰事务执行的中间结果对其他事务必须是透明的 d.持久性对于任意已提交事务系统必须保证该事物对数据库的改变不被丢失及时数据库出现故障 4.要求表的类型必须是innodb或bdb类型才可以对此使用事务 5.查看表的创建语句 show create table students; 6.修改表的类型 alter table 表名 engineinnodb 7.事务语句 开启begin; 提交commit; 回滚follback; 8.实例 a.打开两个终端连接mysql使用同一个数据库操作同一张表 终端1select * from students; 终端2begin; insert into students(name) values(张飞); 终端1select * from students; 终端2rollback; select * from students;九、索引思考在图书馆中如何找到一本书1.一般的应用系统读写比例在10:1左右而且插入操作和更新操作很少出现性能问题遇到最多的也是最容易 出问题的还是一些复杂的查询操作所以查询语句的忧患很重要 2.当数据库中的数据量很大时查找数会变得很慢 3.索引能提高数据访问性能 4.主键和唯一索引都是索引可以提高查询速度选择列的数据类型1.越小的数据类型通常更好他们所需要的空间也越小便于处理 2.简单的数据类型更好整型数据比起字符其处理开销更小 3.尽量避免null应该制定为not null除非要存储null在mysql中含有空值的列很难进行查询优化因为 它们使得所有、索引的统计信息以及比较运算更加复杂应该用0、一个特殊的值或者一个空串代替空值索引操作1.索引分单列索引和组合索引 单列索引即一个索引只包含单个列一个表可以有多个单列索引但这不是组合索引 组合索引即一个索引包含多个列 2.查看索引 show index from table_name; 3.创建索引 create index indexName on mytable(username(length)); 4.删除索引 drop index [indexName] on mytable; 缺点a.虽然索引大大提高了查询速度同时会降低更新表的速度如对标进行insert、update和delete。因为更新 表时mysql不仅要保存数据还要保存索引文件 b.同时建立索引会占用磁盘空间的索引文件 5.实例 a.开启运行时间检测 set profiling1; b.执行查询语句 select * from areas where a titlt北京; c.查看执行时间 show profiles; d.为表areas的title列创建索引 create index titleIndex on areas(title(20)); e.执行查询语句 select * from areas where title北京市; f.再次查看执行的时间 show prodiles;十、与python的交互Connection对象1.用于建立与数据库的连接 2.创建对象调用connect()方法 connconnect(参数列表) a.host:连接的mysql主机如果本机则是localhost b.port:连接的mysql主机的端口默认是3306 c.db:数据库的名称 d.user:连接的用户名 e:passwd:连接的密码 f:charset:通信采用的编码方式推荐使用utf8 3.对象的方法 a.close()关闭连接 b.commit()事务所以需要提交才会生效 c.rollback()事务放弃之前的操作 d.cursor()返回Cursor对象用于执行sql语句并获得结果Cursor对象1.执行sql语句 2.创建对象调用Connection对象的cursor()方法 cursor1conn.cursor() 3.对象的方法 a.close()关闭 b.execute(operation,[parameters])执行语句返回受影响的行数 c.fectone()执行查询语句时获取查询结果集的第一个行数据返回一个元祖 d.next()执行查询语句时获取当前行的下一行 e.fetchall()执行查询时获取结果集的所有行一行构成一个元祖再将这些元祖装入一个元祖返回 f.scroll(value,[mode])将行指针移动到某个位置 * mode表示移动的方式 * mode的默认值为relative表示基于当前行移动到valuevalue为正则向下移动value为负 则向上移动 * mode的值为absolute表示基于第一条数据的位置第一条数据的位置为0 4.对象的属性 a.rowcount只读属性表示最近一次execute()执行后受影响的行数 b.connection获得当前连接对象实例1import pymysql conn pymysql.connect( hostlocalhost, port3306, dbtest, userroot, passwdmysql, charsetutf8 ) # 获取游标 cursor conn.cursor() # 插入数据 sql insert into students (name,sex,age) values (%s,%s,%d) data (张良, male, 32) count cursor.execute(sql % data) print(count) conn.commit() print(successful insert data!) # 修改数据 sql update students set age%d where name%s data (36, 李白) cursor.execute(sql % data) conn.commit() print(successful change data!) # 查询数据 sql select name,sex from students where age%s data (36) cursor.execute(sql % data) for row in cursor.fetchall(): print(Name:%s\t sex:%s\t % row) print(found the data!) # 删除数据 sql delete from students where age32 limit %s data (32, male) cursor.execute(sql % data) conn.commit() print(delete successful!) # 事务处理 sql_1 update students set ageage1 where name李白 sql_2 update students set incomeincome1000 where name李白 sql_3 update students set expendexpend10 where name李白 try: cursor.execute(sql_1) cursor.execute(sql_2) cursor.execute(sql_3) except Exception as e: conn.rollback() print(cursor failed, e) else: conn.commit() print(cursor successful, cursor.rowcount) cursor.close() conn.close()实例2# 创建集合当插入的数据大于size时后面的数据会覆盖前面的数据 db.createCollection(sub,{capped:true,size:4}) # 插入第一条数据 db.sub.insert({title:linux,count:10}) db.sub.find() # 插入第二条数据 db.sub.insert({title:web,count:15}) db.sub.find() # 插入第三条数据 db.sub.insert({title:sql,count:8}) db.sub.find() # 插入第四条数据 db.sub.insert({title:python,count:12}) db.sub.find() # 插入第五条数据 db.sub.insert({title:mongo,count:4}) db.sub.find() # 插入第六条数据 db.sub.insert({title:css,count:8}) db.sub.find()十一、创建表示例idbigint(20)NOTNULLAUTO_INCREMENT,modelvarchar(32)CHARACTERSETutf8mb4COLLATEutf8mb4_general_ciNOTNULLCOMMENT所属业务模块:对应dept_remark表中的字段,namevarchar(30)CHARACTERSETutf8mb4COLLATEutf8mb4_general_ciNOTNULLCOMMENT字典名称,codevarchar(30)CHARACTERSETutf8mb4COLLATEutf8mb4_general_ciNOTNULLCOMMENT字典编码:对应表_字段,remarkvarchar(200)CHARACTERSETutf8mb4COLLATEutf8mb4_general_ciNOTNULLCOMMENT字典批注,sortint(3)NOTNULLCOMMENT字典排序,create_timedatetime(0)NOTNULLCOMMENT创建时间,create_uservarchar(32)CHARACTERSETutf8mb4COLLATEutf8mb4_general_ciNOTNULLCOMMENT创建人,update_timedatetime(0)NOTNULLCOMMENT修改时间,update_uservarchar(32)CHARACTERSETutf8mb4COLLATEutf8mb4_general_ciNOTNULLCOMMENT修改人,is_deltinyint(1)NOTNULLCOMMENT是否删除:0否1是,PRIMARYKEY(id)USINGBTREE)ENGINEInnoDBAUTO_INCREMENT1CHARACTERSETutf8mb4COLLATEutf8mb4_general_ciCOMMENT公共字典表ROW_FORMATDynamic;