1.环境
此文章的操作运行在windows10上
MySQL版本为:mysql Ver 8.0.22 for Win64 on x86_64 (MySQL Community Server - GPL)
2.数据类型:
2.库操作
创建数据库 CREATE DATABASE <数据库名>;
删库drop database <数据库名>;
选择数据库 use <数据库名>;
3.表操作:
此处desc:description
4.基本数据操作(增加,删除,修改,查询):
mysql> alter table dog add isdelete bit default 0; //为表增加一个字段isdelete
mysql> desc dog;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| gender | bit(1) | YES | | b'0' | |
| age | int | YES | | NULL | |
| isdelete | bit(1) | YES | | b'0' | |
+----------+-------------+------+-----+---------+----------------+
mysql> insert into dog(id,name) values(0,"狗剩"); //插入单条数据
//若忘记了主键,但主键是自动递增的,则可以将id设置为0添加
mysql> select * from dog;
+----+----------+----------------+------+--------------------+
| id | name | gender | age | isdelete |
+----+----------+----------------+------+--------------------+
| 1 | 秋田犬 | 0x00 | 1 | 0x00 |
| 2 | 黄金金毛 | 0x00 | 1 | 0x01 |
| 3 | 小猎犬 | 0x01 | 2 | 0x00 |
| 4 | 土狗王 | 0x00 | NULL | 0x00 |
| 5 | 腊肠 | 0x01 | 2 | 0x00 |
| 6 | 约克夏 | 0x00 | 10 | 0x00 |
| 7 | 拳师犬 | 0x00 | NULL | 0x00 |
| 8 | 贵宾犬 | 0x00 | NULL | 0x00 |
| 9 | 泰迪 | 0x00 | NULL | 0x00 |
| 10 | 土狗 | 0x00 | NULL | 0x00 |
| 11 | 狗剩 | 0x00 | NULL | 0x00 |
mysql> insert into t1029 values(3,"tzy"),(2,"twj"); //插入多条数据
Query OK, 2 rows affected (0.01 sec)
- 模糊查询:
//多个模糊查询的条件需要写多个“name like”
mysql> select * from dog where name like "%犬" or name like "狗";
+----+--------+----------------+------+--------------------+
| id | name | gender | age | isdelete |
+----+--------+----------------+------+--------------------+
| 1 | 秋田犬 | 0x00 | 1 | 0x00 |
| 3 | 小猎犬 | 0x01 | 2 | 0x00 |
| 7 | 拳师犬 | 0x00 | NULL | 0x00 |
| 8 | 贵宾犬 | 0x00 | NULL | 0x00 |
- 范围查询:
mysql> select * from dog where id in(1,3,4,5,9);
+----+--------+----------------+------+--------------------+-----------------
| id | name | gender | age | isdelete | birthday |
+----+--------+----------------+------+--------------------+-----------------
| 1 | 秋田犬 | 0x00 | 1 | 0x00 | 2020-10-26 11:26:29 |
| 3 | 小猎犬 | 0x01 | 2 | 0x00 | 2020-10-26 11:26:29 |
| 4 | 土狗王 | 0x00 | NULL | 0x00 | 2020-10-26 11:26:29 |
| 5 | 腊肠 | 0x01 | 2 | 0x00 | 2020-10-26 11:26:29 |
| 9 | 泰迪 | 0x00 | NULL | 0x00 | 2020-10-26 11:26:29 |
+----+--------+----------------+------+--------------------+-----------------
mysql> select * from dog where id not in(1,3,4,5,9);
+----+----------+----------------+------+--------------------+---------------
| id | name | gender | age | isdelete | birthday |
+----+----------+----------------+------+--------------------+---------------
| 2 | 黄金金毛 | 0x00 | 1 | 0x01 | 2020-10-26 11:26:29 |
| 6 | 约克夏 | 0x00 | 10 | 0x00 | 2020-10-26 11:26:29 |
| 7 | 拳师犬 | 0x00 | NULL | 0x00 | 2020-10-26 11:26:29 |
| 8 | 贵宾犬 | 0x00 | NULL | 0x00 | 2020-10-26 11:26:29 |
| 10 | 土狗 | 0x00 | NULL | 0x00 | 2020-10-26 11:26:29 |
| 11 | 狗剩 | 0x00 | NULL | 0x00 | 2020-10-26 11:26:29 |
| 12 | 狗子 | 0x01 | 5 | 0x00 | 2020-10-26 11:27:48 |
| 13 | 狗子王 | 0x01 | 5 | 0x00 | 2020-10-26 11:28:37 |
+----+----------+----------------+------+--------------------+---------------
mysql> select * from dog where id between 1 and 5;
+----+----------+----------------+------+--------------------+---------------
| id | name | gender | age | isdelete | birthday |
+----+----------+----------------+------+--------------------+---------------
| 1 | 秋田犬 | 0x00 | 1 | 0x00 | 2020-10-26 11:26:29 |
| 2 | 黄金金毛 | 0x00 | 1 | 0x01 | 2020-10-26 11:26:29 |
| 3 | 小猎犬 | 0x01 | 2 | 0x00 | 2020-10-26 11:26:29 |
| 4 | 土狗王 | 0x00 | NULL | 0x00 | 2020-10-26 11:26:29 |
| 5 | 腊肠 | 0x01 | 2 | 0x00 | 2020-10-26 11:26:29 |
+----+----------+----------------+------+--------------------+---------------
字段是否为空:is [null /not null]
mysql> select * from dog where age is not null and gender = 0;
+----+----------+----------------+------+--------------------+---------------
| id | name | gender | age | isdelete | birthday |
+----+----------+----------------+------+--------------------+---------------
| 1 | 秋田犬 | 0x00 | 1 | 0x00 | 2020-10-26 11:26:29 |
| 2 | 黄金金毛 | 0x00 | 1 | 0x01 | 2020-10-26 11:26:29 |
| 6 | 约克夏 | 0x00 | 10 | 0x00 | 2020-10-26 11:26:29 |
+----+----------+----------------+------+--------------------+---------------
- 符号优先级:
关键词的有优先级(从大到小):
from >on>where>group by>having>select>distinct>order by>limit
- 聚合查询:
mysql> select max(age),name from dog where age > 5;
+----------+--------+
| max(age) | name |
+----------+--------+
| 10 | 约克夏 |
+----------+--------+
- 分组查询:
mysql> select gender as "性别" ,count(*) from dog group by gender;(性别可以不要引号
)
+------------+----------+
| 性别 | count(*) |
+------------+----------+
| 0x00 | 9 |
| 0x01 | 4 |
having是对group分组后在增加一层条件,如下:按性别分组,但只找gender=1的
mysql> select gender as "性别",avg(age) from dog group by gender having gender = 1;
+------------+----------+
| 性别 | avg(age) |
+------------+----------+
| 0x01 | 3.5000 |
- 排序:
mysql> select * from dog where isdelete = 0 order by like_ desc;
+----+--------+----------------+------+--------------------+-----------------
| id | name | gender | age | isdelete | birthday | like_ |
+----+--------+----------------+------+--------------------+-----------------
| 11 | 狗剩 | 0x00 | NULL | 0x00 | 2020-10-26 11:26:29 | 龙 |
| 12 | 狗子 | 0x01 | 5 | 0x00 | 2020-10-26 11:27:48 | 龙 |
| 13 | 狗子王 | 0x01 | 5 | 0x00 | 2020-10-26 11:28:37 | 龙 |
| 8 | 贵宾犬 | 0x00 | NULL | 0x00 | 2020-10-26 11:26:29 | 鸡 |
| 9 | 泰迪 | 0x00 | NULL | 0x00 | 2020-10-26 11:26:29 | 鸡 |
| 3 | 小猎犬 | 0x01 | 2 | 0x00 | 2020-10-26 11:26:29 | 猴 |
| 1 | 秋田犬 | 0x00 | 1 | 0x00 | 2020-10-26 11:26:29 | 猪 |
| 4 | 土狗王 | 0x00 | NULL | 0x00 | 2020-10-26 11:26:29 | 猪 |
| 5 | 腊肠 | 0x01 | 2 | 0x00 | 2020-10-26 11:26:29 | 猪 |
| 6 | 约克夏 | 0x00 | 10 | 0x00 | 2020-10-26 11:26:29 | 猪 |
| 7 | 拳师犬 | 0x00 | NULL | 0x00 | 2020-10-26 11:26:29 | 猪 |
| 10 | 土狗 | 0x00 | NULL | 0x00 | 2020-10-26 11:26:29 | 猪
mysql> select * from dog where like_ = '猪' order by id desc;
+----+--------+----------------+------+--------------------+-----------------
| id | name | gender | age | isdelete | birthday | like_ |
+----+--------+----------------+------+--------------------+-----------------
| 10 | 土狗 | 0x00 | NULL | 0x00 | 2020-10-26 11:26:29 | 猪 |
| 7 | 拳师犬 | 0x00 | NULL | 0x00 | 2020-10-26 11:26:29 | 猪 |
| 6 | 约克夏 | 0x00 | 10 | 0x00 | 2020-10-26 11:26:29 | 猪 |
| 5 | 腊肠 | 0x01 | 2 | 0x00 | 2020-10-26 11:26:29 | 猪 |
| 4 | 土狗王 | 0x00 | NULL | 0x00 | 2020-10-26 11:26:29 | 猪 |
| 1 | 秋田犬 | 0x00 | 1 | 0x00 | 2020-10-26 11:26:29 | 猪
+----+--------+----------------+------+--------------------+-----------------
- 分页:
mysql> select * from dog limit 3,2;(从id 3(第4个)开始,取两个)
+----+--------+----------------+------+--------------------+----------------
| id | name | gender | age | isdelete | birthday | like_ |
+----+--------+----------------+------+--------------------+----------------
| 4 | 土狗王 | 0x00 | NULL | 0x00 | 2020-10-26 11:26:29 | 猪 |
| 5 | 腊肠 | 0x01 | 2 | 0x00 | 2020-10-26 11:26:29 | 猪 |
+----+--------+----------------+------+--------------------+----------------
- 去重:
mysql> select distinct like_ from dog;
+-------+
| like_ |
+-------+
| 猪 |
| 猴 |
| 鸡 |
| 龙 |
+-------+
5.建表实例
级联约束的设置方法
资料:快速建立经典的stu-course-sc表
6.链接查询(多表查询):
若一张表无法查到需要的所有信息
mysql> select sname,cname,grade from student inner join course,sc where student.sno = sc.sno and course.cno = sc.cno; //将三个表连接起来(主键和外键相连)
+-------+----------+-------+
| sname | cname | grade |
+-------+----------+-------+
| 刘晨 | 数学 | 90 |
| 刘晨 | 信息系统 | 80 |
| 李勇 | 数据库 | 92 |
| 李勇 | 数学 | 85 |
| 李勇 | 信息系统 | 88 |
mysql> select sname,cname,grade from student inner join sc on student.sno = sc .sno
-> inner join course on course.cno = sc.cno;
on和where在inner join中的作用是一样的,并且mysql是忽略大小写的,Cno和cno一样
左外连接,左边表中的数据全部保存下来,右边的表中若无对应数据,则显示NULL
mysql> select * from student left join sc on sc.sno = student.sno;
+-----------+-------+------+------+-------+-----------+------+-------+
| Sno | Sname | Ssex | Sage | Sdept | Sno | Cno | Grade |
+-----------+-------+------+------+-------+-----------+------+-------+
| 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 1 | 92 |
| 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 2 | 85 |
| 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 3 | 88 |
| 201215122 | 刘晨 | 女 | 19 | CS | 201215122 | 2 | 90 |
| 201215122 | 刘晨 | 女 | 19 | CS | 201215122 | 3 | 80 |
| 201215123 | 王敏 | 女 | 18 | MA | NULL | NULL | NULL |
| 201215125 | 张立 | 男 | 19 | IS | NULL | NULL | NULL |
| 201215128 | 陈冬 | 男 | 20 | IS | NULL | NULL | NULL |
+-----------+-------+------+------+-------+-----------+------+-------+
同理,右外连接,右边表中的数据全部保存下来,左边的表中若无对应数据,则显示NULL
mysql> select * from student right join course on course.cno = student.sno;
+------+-------+------+------+-------+-----+------------+------+---------+
| Sno | Sname | Ssex | Sage | Sdept | Cno | Cname | Cpno | Ccredit |
+------+-------+------+------+-------+-----+------------+------+---------+
| NULL | NULL | NULL | NULL | NULL | 1 | 数据库 | 5 | 4 |
| NULL | NULL | NULL | NULL | NULL | 2 | 数学 | NULL | 4 |
| NULL | NULL | NULL | NULL | NULL | 3 | 信息系统 | 1 | 4 |
| NULL | NULL | NULL | NULL | NULL | 4 | 操作系统 | 6 | 4 |
| NULL | NULL | NULL | NULL | NULL | 5 | 数据结构 | 7 | 4 |
| NULL | NULL | NULL | NULL | NULL | 6 | 数据处理 | NULL | 4 |
| NULL | NULL | NULL | NULL | NULL | 7 | Pascal语言 | 6 | 4 |
+------+-------+------+------+-------+-----+------------+------+---------+
MySQL是不支持全外的连接的!(full outer join)
内连接不指定条件相当于是笛卡尔积。
mysql> select * from course join sc;
mysql> select * from course inner join sc; //上下等效
+-----+------------+------+---------+-----------+-----+-------+
| Cno | Cname | Cpno | Ccredit | Sno | Cno | Grade |
+-----+------------+------+---------+-----------+-----+-------+
| 1 | 数据库 | 5 | 4 | 201215121 | 1 | 92 |
| 1 | 数据库 | 5 | 4 | 201215121 | 2 | 85 |
| 1 | 数据库 | 5 | 4 | 201215121 | 3 | 88 |
| 1 | 数据库 | 5 | 4 | 201215122 | 2 | 90 |
| 1 | 数据库 | 5 | 4 | 201215122 | 3 | 80 |
| 2 | 数学 | NULL | 4 | 201215121 | 1 | 92 |
| 2 | 数学 | NULL | 4 | 201215121 | 2 | 85 |
| 2 | 数学 | NULL | 4 | 201215121 | 3 | 88 |
| 2 | 数学 | NULL | 4 | 201215122 | 2 | 90 |
| 2 | 数学 | NULL | 4 | 201215122 | 3 | 80 |
| 3 | 信息系统 | 1 | 4 | 201215121 | 1 | 92 |
/*不是全部的数据*/
内连接也可以设置条件,如下相当于查出sc中的统计的成绩实际上是哪几门课
mysql> select * from course join sc on course.cno = sc.cno;
+-----+----------+------+---------+-----------+-----+-------+
| Cno | Cname | Cpno | Ccredit | Sno | Cno | Grade |
+-----+----------+------+---------+-----------+-----+-------+
| 1 | 数据库 | 5 | 4 | 201215121 | 1 | 92 |
| 2 | 数学 | NULL | 4 | 201215121 | 2 | 85 |
| 3 | 信息系统 | 1 | 4 | 201215121 | 3 | 88 |
| 2 | 数学 | NULL | 4 | 201215122 | 2 | 90 |
| 3 | 信息系统 | 1 | 4 | 201215122 | 3 | 80 |
7.Where和On的区别
在inner join中,where和on的作用是相同的,可以用于连接和条件,在outer join中,where不可以用于连接,并且两者区别如下:
1、 on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。
2、where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。
3、on按照条件查数据,where查完所有数据再用条件筛选,因此on的速度快于where(因为ON限制条件发生时间较早,临时表的数据集要小,因此ON的性能要优于WHERE)
//用on会按照on的条件生成表,但又是左外连接,会保留所有左边的数据
mysql> select * from student left join sc on student.sno = sc.sno and student.sno = 201215121;
+-----------+-------+------+------+-------+-----------+------+-------+
| Sno | Sname | Ssex | Sage | Sdept | Sno | Cno | Grade |
+-----------+-------+------+------+-------+-----------+------+-------+
| 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 1 | 92 |
| 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 2 | 85 |
| 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 3 | 88 |
| 201215122 | 刘晨 | 女 | 19 | CS | NULL | NULL | NULL |
| 201215123 | 王敏 | 女 | 18 | MA | NULL | NULL | NULL |
| 201215125 | 张立 | 男 | 19 | IS | NULL | NULL | NULL |
| 201215128 | 陈冬 | 男 | 20 | IS | NULL | NULL | NULL |
+-----------+-------+------+------+-------+-----------+------+-------+
//where和on公用会先按照on的条件生成临时表(会按外连接的要求保留一般),再按where的条件对表进行二次筛选(不会按外连接的要求保留)
mysql> select * from student left join sc on student.sno = sc.sno where student.sno = '201215121';
+-----------+-------+------+------+-------+-----------+------+-------+
| Sno | Sname | Ssex | Sage | Sdept | Sno | Cno | Grade |
+-----------+-------+------+------+-------+-----------+------+-------+
| 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 1 | 92 |
| 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 2 | 85 |
| 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 3 | 88 |
+-----------+-------+------+------+-------+-----------+------+-------+
8.聚合+连接实例:
1.求每个男生的名字,性别和平均分
先将学生表(student)和选课表(sc)连接起来,然后选择男生的成绩,按照学校求男生的平均分(也体现了优先级on>where>group by )
mysql> select student.sname,student.ssex,avg(sc.grade) 成绩 from sc right join student on student.sno = sc.sno where student.ssex='男' group by student.sno;
+-------+------+---------+
| sname | ssex | 成绩 |
+-------+------+---------+
| 李勇 | 男 | 88.3333 |
| 张立 | 男 | NULL |
| 陈冬 | 男 | NULL |
+-------+------+---------+
2.求每门课的课名和其平均分
mysql> select course.cname 课程名,avg(sc.grade) 平均分 from sc left join course on sc.cno = course.cno group by course.cno;
+----------+---------+
| 课程名 | 平均分 |
+----------+---------+
| 数据库 | 92.0000 |
| 数学 | 87.5000 |
| 信息系统 | 84.0000 |
3.求每个学生的名字,其平均分,最高分,最低分
mysql> select student.sname,avg(sc.grade),max(sc.grade),min(sc.grade) from student
-> join sc on student.sno = sc.sno //多个连接join
-> group by student.sname;
+-------+---------------+---------------+---------------+
| sname | avg(sc.grade) | max(sc.grade) | min(sc.grade) |
+-------+---------------+---------------+---------------+
| 李勇 | 88.3333 | 92 | 85 |
| 刘晨 | 85.0000 | 90 | 80 |
+-------+---------------+---------------+---------------+
9.视图view:
将复杂的sql查询语句封装起来,或者是为了安全,只想让特定用户看到特定信息,将这些信息用视图展示。视图只能查看不能修改,不会影响原表的数据。
mysql> create view avgsocre as
-> select student.sname,avg(sc.grade),max(sc.grade),min(sc.grade) from student
-> join sc on student.sno = sc.sno
-> group by student.sname;
| sname | avg(sc.grade) | max(sc.grade) | min(sc.grade) |
+-------+---------------+---------------+---------------+
| 李勇 | 88.3333 | 92 | 85 |
| 刘晨 | 85.0000 | 90 | 80 |
+-------+---------------+---------------+---------------+
mysql> select * from avgsocre;
+-------+---------------+---------------+---------------+
| sname | avg(sc.grade) | max(sc.grade) | min(sc.grade) |
+-------+---------------+---------------+---------------+
| 李勇 | 88.3333 | 92 | 85 |
| 刘晨 | 85.0000 | 90 | 80 |
+-------+---------------+---------------+---------------+
//可以通过show tables查看到该视图
mysql> show tables;
+------------------+
| Tables_in_animal |
+------------------+
| avgsocre |
| course |
| dog |
| sc |
| slave |
| student |
mysql> drop view avgsocre; //删除视图
10.自关联
在之前设置的course表中,先修课字段cpno自关联了同一张表中cno字段(cpno是course表的外键,但是参考的对象不是其他表中的字段,而是当前表中cno)
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| Cno | char(4) | NO | PRI | NULL | |
| Cname | char(40) | YES | | NULL | |
| Cpno | char(4) | YES | MUL | NULL | |
| Ccredit | smallint | YES | | NULL | |
+---------+----------+------+-----+---------+-------+
mysql> select * from course c //自连接查询所有课程的先修课程
-> join course
-> on c.cpno = course.cno; //注意应该是左边的表的cpno = 右边表的cno
+-----+------------+------+---------+-----+------------+------+---------+
| Cno | Cname | Cpno | Ccredit | Cno | Cname | Cpno | Ccredit |
+-----+------------+------+---------+-----+------------+------+---------+
| 1 | 数据库 | 5 | 4 | 5 | 数据结构 | 7 | 4 |
| 3 | 信息系统 | 1 | 4 | 1 | 数据库 | 5 | 4 |
| 4 | 操作系统 | 6 | 4 | 6 | 数据处理 | NULL | 4 |
| 5 | 数据结构 | 7 | 4 | 7 | Pascal语言 | 6 | 4 |
| 7 | Pascal语言 | 6 | 4 | 6 | 数据处理 | NULL | 4 |
+-----+------------+------+---------+-----+------------+------+---------+
mysql> select * from course where cno in ( //多个嵌套查询返回的结果用in
-> select cpno from course);
+-----+------------+------+---------+
| Cno | Cname | Cpno | Ccredit |
+-----+------------+------+---------+
| 1 | 数据库 | 5 | 4 |
| 5 | 数据结构 | 7 | 4 |
| 6 | 数据处理 | NULL | 4 |
| 7 | Pascal语言 | 6 | 4 |
+-----+------------+------+---------+
11.事务
- 事务的性质
A:事务中的操作要么都发生,要么都不发生
C:并行执行的结果必须和某个顺序执行的结果相同
I:事务之间对数据库的操作不会互相影响
D:对数据库的操作,DB会保证数据的修改一定存在
mysql> show create table course; //查询建表的sql语句
| course | CREATE TABLE `course` (
`Cno` char(4) NOT NULL,
`Cname` char(40) DEFAULT NULL,
`Cpno` char(4) DEFAULT NULL,
`Ccredit` smallint DEFAULT NULL,
PRIMARY KEY (`Cno`),
KEY `Cpno` (`Cpno`),
CONSTRAINT `course_ibfk_1` FOREIGN KEY (`Cpno`) REFERENCES `course` (`Cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 | // InnoDB表示可以创建事务
Alter table tablename engine = innodb //使目标表能够支持事务
mysql> begin; //开始事务
mysql> insert into dog(name) values('人工智能'); //在事务中添加数据
在同一账户中可以看到修改,但是在其他账户看不到修改
mysql> commit; //提交事务后,另外一个账户也可以看到修改
mysql> begin;
mysql> insert into dog(name) values('爬虫');
mysql> rollback; //在事务中的操作回滚后,操作会被取消
12.查询效率
为province表中的province字段建立索引,减少了运行时间
mysql> create index provinceindex on province(province(40));
mysql> show profiles; //结果如下:
mysql> show index from province; //检查增加的索引
mysql> drop index provinceindex on province; //删除索引后查看
mysql> show index from province;
- 建立索引影响增删改的速度,索引的优缺点是什么
Pros:
索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存。如果没有索引,执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录。表里面的记录数量越多,这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引,MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。
对表中的某个字段设置了索引,那么在where条件以该条件搜索的时候,不需要从头到尾搜索数据,而是通过索引直接找到目标数据
Cons:
首先,索引要占用磁盘空间。通常情况下,这个问题不是很突出。但是,如果你创建每一种可能列组合的索引,索引文件体积的增长速度将远远超过数据文件。如果你有一个很大的表,索引文件的大小可能达到操作系统允许的最大文件限制。
第二,对于需要写入数据的操作,比如DELETE、UPDATE以及INSERT操作,索引会降低它们的速度。这是因为MySQL不仅要把改动数据写入数据文件,而且它还要把这些改动写入索引文件。
13.Python和MySQL交互
cursor用于查询语句
14.备份和恢复
- 将dog表备份到D:\sql_MySQL\dog_backup.sql目录下
C:\Users\username>mysqldump -u root -p animal dog > D:\sql_MySQL\dog_backup.sql
Enter password: ***********
- 删除表:
mysql> drop table dog;
mysql> show tables;
+------------------+
| Tables_in_animal |
+------------------+
| course |
| sc |
| student |
+------------------+
- 恢复表:
C:\Users\username>mysql -u root -p animal < D:\sql_MySQL\dog_backup.sql
Enter password: ***********
mysql> show tables; //可以发现,表已经成功恢复
+------------------+
| Tables_in_animal |
+------------------+
| course |
| dog |
| sc |
| student |
+------------------+
- 查看恢复的表:
15.杂项
1.不能删除其他表中有外键约束的数据(在sc中有201215121,又sc的sno关联了student的sno,所以不能删除student中的),也可以设置一个字段用于逻辑删除。
mysql> delete from student where sno = 201215121;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`animal`.`sc`, CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`Sno`) REFERENCES `student` (`Sno`))
2.CASCADE(是一种约束,只有设置外键的时候才能使用)
删除:删除主表时自动删除从表。删除从表,主表不变
更新:更新主表时自动更新从表。更新从表,主表不变
1. 创建父亲表:
mysql> create table father(
-> hid int auto_increment,
-> fname varchar(20),
-> primary key(hid));
2. mysql创建儿子表,设置外键hid参考father表中hid,并且设置级联删除和更新:
> create table son(
-> hid int,
-> sname varchar(20),
-> foreign key(hid) references father(hid)
-> on delete cascade
-> on update cascade);
- 查询两个表的数据,结果如下
mysql> select * from father;
| hid | fname |
| 1 | tzy |
mysql> select * from son;
| hid | sname |
| 1 | twj |
- 更新父亲表中的数据,儿子表的数据也发生了改变
mysql> update father set hid = 2 where hid = 1;
mysql> select * from father;
| hid | fname |
| 2 | tzy |
mysql> select * from son;
| hid | sname |
| 2 | twj |
- 删除父亲表中的数据后,儿子表的数据也被删除了
mysql> delete from father where hid = 2;
Query OK, 1 row affected (0.04 sec)
mysql> select * from father;
Empty set (0.00 sec)
mysql> select * from son;
Empty set (0.00 sec)
3.数据较小不一定需要严格按照范式规则(到时候可能要连接查询开销变大),不如直接放在一张表中
4.导入sql文件到mysql中
- 打开navicat连接数据库
- 右键选择需要导入的数据库后将sql语句导入
5.Mysql中 varcar或者是char指的是字节数,一个汉字相当于3个字节:
mysql> desc course;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| Cno | char(4) | NO | PRI | NULL | |
| Cname | char(40) | YES | | NULL | |
| Cpno | char(4) | YES | MUL | NULL | |
| Ccredit | smallint | YES | | NULL | |
mysql> select length(cname) from course;
+---------------+
| length(cname) |
+---------------+
| 9 |
| 6 |
| 12 |
| 12 |
| 12 |
| 12 |
| 12 |
6.怎么看mysql数据存的路径
SHOW GLOBAL VARIABLES LIKE "%datadir%";