MySQL概述
1、什么是数据库
数据库是一个存储数据的仓库
2、哪些公司在用数据库
金融机构、游戏网站、购物网站、论坛网站 ... ...
3、提供数据库服务的软件
1、软件分类
MySQL、SQL_Server、Oracle、DB2、MariaDB、MongoDB ...
2、在生产环境中，如何选择使用哪个数据库软件
1、是否开源
开源软件：MySQL、MariaDB、MongoDB
商业软件：Oracle、DB2、SQL_Server
2、是否跨平台
不跨平台：SQL_Server
跨平台：MySQL、MariaDB、MongoDB、Oracle、DB2
3、公司的类型
商业软件：政府部门、金融机构
开源软件：游戏网站、购物网站、论坛网站 ... ...
4、MySQL特点
1、关系型数据库
1、数据是以行和列的形式去存储
2、这一系列的行和列称为表
3、表中的每一行叫一条记录
4、表中的每一列叫一个字段
5、表和表之间的逻辑关联叫关系
示例：
1、关系型数据库存储
表1：学生信息表
姓名 年龄 班级
张无忌 23 AID01
小昭 25 AID02
表2：班级信息表
班级 班主任
AID01 孙大大
AID02 侯大大
2、非关系数据库中存储
{"姓名":"张无忌","年龄":23,"班级":AID01,"班主任":"孙大大}
{"姓名":"小昭","年龄":23,"班级":AID01}
2、跨平台
可以在Unix、Linux、Windows上运行MySQL数据库服务
3、支持多种编程语言
Python、Java、php ... ...
2、MySQL安装
1、Ubuntu上安装MySQL服务
1、安装服务端
sudo apt-get install mysql-server
2、安装客户端
sudo apt-get install mysql-client
2、Windows上安装MySQL服务
1、下载MySQL安装包(windows)
mysql-installer***5.7.***.msi
2、双击、按照教程安装即可
3、启动和连接MySQL服务
1、服务端启动
1、查看MySQL服务的状态
sudo /etc/init.d/mysql status
2、停止MySQL服务
sudo /etc/init.d/mysql stop
3、启动MySQL服务
sudo /etc/init.d/mysql start
4、重启MySQL服务
sudo /etc/init.d/mysql restart
2、客户端连接
1、命令格式
mysql -h主机地址 -u用户名 -p密码
mysql -hlocalhost -uroot -p123456
2、本地连接可以省略 -h 选项
mysql -uroot -p123456
4、基本SQL命令
1、SQL命令的使用规则
1、每条命令必须以 ; 结尾
2、SQL命令不区分字母大小写
3、使用 \c 终止SQL命令的执行
2、库的管理
1、库的基本操作
1、查看已有的库
show databases;
2、创建库(指定字符集,默认为latin1)
create database 库名 default charset=utf8;
3、查看创建库的语句
show create database 库名;
4、查看当前所在库
select database();
5、切换库
use 库名;
6、查看库中已有表
show tables;
7、删除库
drop database 库名;
2、库的命名规则
1、可以使用数字、字母、_,但是不能使用纯数字
2、库名区分字母大小写
3、库名具有唯一性
4、不能使用特殊字符和mysql的关键字
3、练习
1、创建库AID01,指定字符集为utf8
2、进入到库AID01
3、查看当前所在库
4、查看库中已有的表
5、查看AID01的默认字符集(创建库的语句)
6、删除库AID01
3、表的管理
1、表的基本操作
1、创建表(指定字符集)
create table 表名(
字段名 数据类型,
字段名 数据类型,
... ...
)default charset=utf8;
2、查看创建表的语句(字符集)
show create table 表名;
3、查看表结构
desc 表名;
4、删除表
drop table 表名;
2、表的命名规则(同库的命名规则)
3、练习
1、创建库 Python
2、在Python库中创建表py_mysql,并指定字符集utf8
字段有：id、kuname、biaoname,数据类型自己定义
3、查看创建表py_mysql的语句(字符集)
4、查看表py_mysql的表结构
4、注意
1、所有的数据都是以文件形式存放在数据库目录下
2、数据库目录：/var/lib/mysql
4、表记录管理
1、在表中插入记录
insert into 表名 values(值1),(值2),...,(值N);
2、查询表记录
1、select * from 表名 [where 条件];
select * from t1;
select * from t1 where name="Green";
2、select 字段1,字段2,... from 表名 [where 条件];
5、练习
1、查看已有的库
show databases;
2、创建一个新库studb,并切换到该库
create database studb;
use studb;
3、在studb中创建表stuinfo,字段有id name age三个
create table stuinfo(
id int,
name char(20),
age int);
4、查看stuinfo的表结构
desc stuinfo;
5、在表 stuinfo 中任意插入2条记录
insert into stuinfo values(1,"Lucy",20),(2,"Bob",23);
6、查看 stuinfo 中的所有记录
select * from stuinfo;
7、删除表 stuinfo
drop table stuinfo;
8、删除库 studb
drop database studb;
5、更改库、表的默认字符集
1、方法
通过更改MySQL配置文件来实现
2、步骤
cp 文件名 新的文件名
vi 文件名
a -> 写内容
esc -> shift + :
wq
1、获取root用户权限
sudo -i
2、cd到mysql配置文件所在路径
cd /etc/mysql/mysql.conf.d/
3、把配置文件mysqld.cnf复制一份,mysqld.cnf.bak
cp mysqld.cnf mysqld.cnf.bak
4、vi打开配置文件mysqld.cnf
vi mysqld.cnf
在[mysqld]下写入如下语句：
character_set_server=utf8
5、重启MySQL服务
sudo /etc/init.d/mysql restart
6、客户端把数据存储到数据库服务器上的过程
1、连接到数据库服务器 mysql -uroot -p123456
2、选择一个库 use 库名
3、创建表或者修改表
4、断开与数据库服务器的连接 exit; | quit; | \q;
7、数据类型
1、数值类型(有符号signed和无符号unsigned)
1、整型
1、int 大整型(4个字节)
取值范围：2**32 - 1(42亿多)
2、tinyint 微小整型(1个字节)
1、有符号(signed默认): -128 ~ 127
2、无符号(unsigned): 0 ~ 255
3、smallint 小整型(2个字节)
取值范围：0 ~ 65535
4、bigint 极大整型(8个字节)
取值范围：0 ~ 2**64 - 1
2、浮点型
1、float(4个字节,最多显示7个有效位)
1、用法
字段名 float(m,n) m->总位数,n->小数位位数
float(5,2) 取值范围？ -999.99 ~ 999.99
2、注意
1、浮点型插入整数时会自动补全小数位
2、小数位如果多于指定的位数，会对下一位进行四舍五入
2、double(8个字节，最多显示15个有效位)
1、用法
double(m,n)
3、decimal(M+2个字节,最多显示28个有效位)
1、用法
字段名 decimal(M,D)
2、字符类型
1、char(定长)
1、宽度取值范围：1~255
2、不给定宽度默认宽度为1
2、varchar(变长)
1、取值范围：1~65535
2、注意
1、varchar没有默认宽度,必须给定一个宽度值
2、char、varchar使用时都给定宽度,但不要超过各自的范围
3、char和varchar的特点
1、char(定长)
浪费存储空间,但是性能高
2、varchar(变长)
节省存储空间,但是性能低
4、练习
1、创建一个库STUDB,指定字符集为utf8,并切换到该库
create database STUDB default charset=utf8;
2、在STUDB中创建表stuinfo01,字段要求如下：
姓名name:变长,宽度为20
班级class：定长,宽度为5
年龄age：微小整型,要求不能输入负数
身高height：浮点型,小数位为2位
use STUDB;
create table stuinfo01(
name varchar(20),
class char(5),
age tinyint unsigned,
height float(5,2)
);
3、查看表结构
desc stuinfo01;
4、在表中一次性插入3条记录
insert into stuinfo01 values
("张飞","AID01",23,175),
("关羽","AID01",25,180.3),
("郭嘉","AID03",23,170.88);
5、查询所有的表记录
select * from stuinfo01;
6、查询年龄 > 20 的记录
select * from stuinfo01 where age>20;
3、枚举类型
1、定义
字段值只能在列举的范围内去选择
2、enum 单选(最多有65535个不同的值)
字段名 enum(值1,值2,...)
3、set 多选(最多有64个不同的值)
字段名 set(值1,值2,...)
插入记录时 "Python,MySQL,Study
4、日期时间类型
1、year ：年 YYYY
2、date ：日期 YYYYMMDD
3、time ：时间 HHMMSS
4、datetime ：日期时间 YYYYMMDDHHMMSS
5、timestamp：日期时间 YYYYMMDDHHMMSS
create table t7(
id int,
name char(15),
age tinyint unsigned,
birth_year year,
birthday date,
class time,
meeting datetime
);
datetime和timestamp区别示例：
create table t8(
username varchar(15),
password varchar(15),
zhuce datetime,
current timestamp);
6、注意
1、插入记录时datetime字段不给值默认返回NULL
2、插入记录时timestamp字段不给值默认返回系统当前时间
作业：
1、面试题
1、填空题
1、MySQL中的数据类型有___、____、____、____
2、关系型数据库的核心内容是 关系 即 二维表
2、简答题
1、简述客户端把数据存储到数据库服务器上的过程
2、var和varchar的区别及各自的特点
2、操作题
1、创建一个学校的库school
2、在school库中创建一张表students来存储学生信息
字段有：学号、姓名、年龄(不能输入负数)、成绩(浮点)、
性别(单选)、爱好(多选)、入学时间(不给值默认返回当前时间)
3、查看students的表结构
4、在表中随意插入4条记录
5、查看所有学生信息
6、查看所有学生信息的姓名和成绩
7、查看成绩大于60的学生的姓名和成绩
MySQL-Day01回顾
1、MySQL的特点
1、关系型数据库
2、跨平台
3、支持多种编程语言
2、MySQL的启动和连接
1、服务端启动
sudo /etc/init.d/mysql start | stop | restart | status
2、客户端连接
mysql -h主机地址 -u用户名 -p密码
3、注意
1、MySQL中的数据是以文件的形式存放在数据库目录/var/lib/mysql下
2、关系型数据库的核心内容是 关系 即 二维表
3、基本SQL命令
1、库的管理
1、创建库(指定字符集)
create database 库名 [default charset=utf8];
2、查看创建库的语句
show create database 库名;
3、查看当前所在库
select database();
4、切换库
use 库名;
5、查看库中已有的表
show tables;
6、删除库
drop database 库名;
2、表的管理
1、创建表(指定字符集)
create table 表名(
字段名 数据类型,
字段名 数据类型,
...
)[default charset=utf8];
2、查看创建表的语句
show create table 表名;
3、查看表结构
desc 表名;
4、删除表
drop table 表名;
3、表记录管理
1、插入表记录
insert into 表名(字段名1,字段名2,...) values(值1),(值2),...;
2、查看表记录
select 字段名1,字段名2,...,字段名n from 表名 where 条件;
4、如何更改默认字符集
1、方法
通过更改配置文件来实现/etc/mysql/mysql.conf.d/mysqld.cnf
2、步骤
1、sudo -i
2、cd /etc/mysql/mysql.conf.d/
3、cp mysqld.cnf mysqld.cnf.bak
4、vi mysqld.cnf
[mysqld]
character_set_server=utf8
5、sudo /etc/init.d/mysql restart
5、客户端把数据存储到数据库服务器上的过程
1、连接到数据库服务器
2、选择库
3、创建表/修改表
4、断开连接
6、数据类型
1、数值类型(有符号signed和无符号unsigned)
1、整型
1、int(4个字节)
2、tinyint(1个字节):有符号signed 无符号unsigned常用
3、smallint(2个字节)
4、bigint(8个字节)
2、浮点型
1、float(4个字节,最高显示7个有效位)
2、double(8个字节,最高显示15个有效位)
3、decimal(M+2个字节,最高显示28个有效位)
float(m,n) m->总位数 n->小数位位数
4、注意
1、浮点型 插入整数时 会自动补全小数位位数
2、小数位如果多于指定位数,会对下一位四舍五入
2、字符类型
1、char(定长,浪费存储空间,性能高)##不指定宽度,默认为1
2、varchar(变长,节省存储空间,性能低)##必须指定宽度
3、枚举类型
1、enum单选(最多有65535个不同值)
2、set多选(最多有64个不同值)
4、日期时间类型
1、year
2、date
3、time
4、datetime ##不给值默认返回NULL
5、timestamp ##不给值默认返回系统当前时间
-------------------------------------------------------------------------------------
MySQL-Day02笔记
1、字符类型的宽度和数值类型的宽度的区别
1、数值类型的宽度为显示宽度,只用于select查询时显示,和占用的存储空间大小无关,可用zerofill来显示效果
2、字符类型的宽度超过指定宽度则无法存储
create table t1(
id int(3) zerofill,
name char(20)
);
insert into t1 values(1,"lucy"); ## 显示效果为 001
2、表字段的操作
1、语法：alter table 表名 执行动作;
2、添加字段(add)
1、添加到末尾
alter table 表名 add 字段名 数据类型;
2、添加到第一列
alter table 表名 add 字段名 数据类型 first;
3、添加到指定位置
alter table 表名 add 字段名 数据类型 after 字段名;
3、删除字段(drop)
alter table 表名 drop 字段名;
4、修改字段数据类型(modify)
alter table 表名 modify 字段名 新的数据类型;
5、修改字段名(change)
alter table 表名 change 旧字段名 新字段名 数据类型;
6、修改表名(rename)
alter table 表名 rename 新表名;
3、表记录的管理
1、删除表记录
1、delete from 表名 where 条件;
2、注意
delete语句后如果不加where条件子句会将表中所有记录全部删除
3、示例
create table t4(
id int(3) zerofill,
name varchar(20),
sex enum("boy","girl")
);
insert into t4 values
(1,"Lucy","girl"),
(2,"Tom","boy"),
(3,"Bob","boy");
select * from t4;
2、更新表记录
1、update 表名 set 字段名=值1,字段名=值2,...,where 条件;
2、注意
update语句后如果不加where条件会将表中所有的值修改
3、练习(见建表脚本MOSHOU.hero.txt)
1、查找所有蜀国人的信息
select * from hero where country="蜀国";
2、查找女英雄的姓名、性别和国家
select name,sex,country from hero where sex="女";
3、把曹操的国籍改为蜀国
update hero set country="蜀国" where name="曹操";
4、把魏延的性别改为 女 ,国籍改为 泰国
5、把id为2的记录的姓名改为司马懿,性别为男,国家为魏国
6、删除所有的泰国人
7、将表中所有的记录的国家改为吴国
8、删除所有英雄的信息
4、运算符操作(配合查询、修改、删除)
1、数值比较&字符比较
1、数值比较运算符：=、!=、>、>=、<、<=
2、字符比较运算符：=、!=
3、练习
1、找出攻击力高于150的英雄的名字和攻击力的值
2、找出防御力值不是66的英雄信息
2、逻辑比较
1、运算符：and(多个条件同时满足)
or(多个条件有一个满足即可)
2、练习
1、找出攻击值大于200的蜀国英雄的名字、攻击值及国家
2、将吴国英雄中攻击值为110的英雄的攻击值设置为100，防御值设置为60
3、查找蜀国和魏国的英雄信息
3、范围内比较
1、运算符：between and、in、not in
2、语法
字段名 between 值1 and 值2
字段名 in(值1,值2,...,值N)
字段名 not in(值1,值2,...,值N)
3、练习
1、查找攻击值在100-200之间的蜀国英雄信息
2、找到蜀国和吴国以外的国家的女英雄信息
3、找到编号为1、3或5的蜀国英雄和貂蝉的编号、姓名、国家
4、匹配空、非空
1、空：is null
2、非空：is not null
3、练习
1、查找姓名为空值(NULL)的蜀国男英雄信息
2、查找姓名为空字符串("")的英雄信息
4、注意
1、NULL ：空值,必须用is或者is not去匹配
2、"" ：空字符串,用 = 或者 != 去匹配
5、模糊比较
1、语法
字段名 like 表达式
2、表达式
1、_ ：匹配单个字符
2、% ：匹配0到多个字符
3、练习
1、select id,name from sanguo where name like "_%_";
# 匹配名字中至少有两个字符的
2、select id,name from sanguo where name like "%";
# 匹配除NULL之外的所有英雄信息
3、select id,name from sanguo where name like "___";
# 匹配名字中有三个字符的
4、select id,name from sanguo where name like "赵%";
# 匹配姓赵的英雄信息
5、SQL查询
1、总结(执行顺序)
3、 select ...聚合函数 from ...
1、 where ...
2、 group by ...
4、 having ...
5、 order by ...
6、 limit ...
2、order by
1、作用：给查询的结果进行排序
2、排序方式
1、ASC(默认) ：升序
2、DESC ：降序
3、语法格式
order by 字段名 排序方式
4、练习
1、将英雄信息按防御值从低到高排序
select * from sanguo order by fangyu asc;
2、将蜀国英雄信息按攻击值从高到低排序
select * from sanguo where country="蜀国" order by gongji DESC;
3、将魏蜀两国的男英雄中名字为三个字的英雄按防御值升序排列
select * from sanguo where
country in("蜀国","魏国") and sex="男" and name like "___"
order by fangyu;
3、limit(永远放在SQL语句的最后写)
1、作用 ：限制显示查询记录的条数
2、用法
1、limit n -->显示几条记录
2、limit m,n
m -> 从第几条记录开始显示,n表示显示几条
## m的值是从0开始计数的,3则表示第四条记录开始
limit 3,5 ：显示4-8条记录
3、练习
1、查找攻击值前三名且名字不为空值的蜀国英雄的姓名、攻击值和国家
-> select name,gongji,country from sanguo
-> where
-> name is not null and country="蜀国"
-> order by gongji desc
-> limit 3;
2、查找防御值倒数第二名到倒数第四名的蜀国英雄记录
-> select * from sanguo
-> where
-> country="蜀国"
-> order by fangyu asc limit 1,3;
4、聚合函数
1、分类
1、avg(字段名) : 求字段的平均值
2、sum(字段名) : 求字段的和
3、max(字段名) : 求字段的最大值
4、min(字段名) : 求字段的最小值
5、count(字段名):统计该字段的记录的个数
2、练习
1、攻击力最强值是多少
select max(gongji) from sanguo;
2、统计一下表中id,name字段分别有独傲少条记录
select count(id),count(name) from sanguo;
## 空值NULL不会被统计,空字符串""会被统计
3、计算蜀国英雄的总攻击力
-> select sum(gongji) from sanguo
-> where country="蜀国";
4、统计蜀国英雄中攻击值大于200的英雄的数量
-> select count(*) from sanguo
-> where
-> country="蜀国" and gongji>200;
5、group by
1、作用：给查询的结果进行分组
2、练习
1、查询sanguo表中一共有几个国家
select country from sanguo group by country;
2、计算所有国家的平均攻击力
select country,avg(gongji) from sanguo
group by country;
3、查找所有国家中英雄数量最多的前两名的国家名称和英雄数量
-> select country,count(*) from sanguo
-> group by country
-> order by count(*) desc
-> limit 2;
3、注意
1、group by之后的字段名必须要为select之后的字段名
2、如果select之后的字段名和group by之后的字段名不一致,则必须要对该字段值进行聚合处理(聚合函数)
6、having语句
1、作用：对查询的结果进行进一步的筛选
2、练习
1、找出平均攻击力大于105的国家的前两名,显示国家名和平均攻击力
-> select country,avg(gongji) from sanguo
-> group by country
-> having avg(gongji)>105
-> order by avg(gongji) desc
-> limit 2;
3、注意
1、having语句通常与group by语句联合使用,用于过滤由group by语句返回的记录集
2、having语句的存在弥补了where条件子句不能与聚合函数联合使用的不足,where只能操作表中实际存在的字段,having操作的是聚合函数生成的显示列
7、distinct
1、作用：不显示字段的重复值
2、练习
1、sanguo表中有哪几个国家
select distinct country from sanguo;
# 多个字段用逗号隔开
2、计算蜀国一共有多少个英雄
select count(distinct name) from sanguo where country="蜀国";
3、注意
1、distinct处理的是distinct与from之间的所有字段,所有字段值必须全部相同才能去重
2、distinct不能对任何字段做聚合处理
8、查询表记录时做数学运算
1、运算符
+、-、*、/、%
2、练习
1、查询显示时所有英雄的攻击力全部乘以10
select id,name,country,gongji*10 as xgj from sanguo;
2、查询时显示所有英雄的防御力+5
select id,name,country,fangyu+5 from sanguo;
6、约束
1、作用
为了保证数据的完整性、一致性、有效性的规则
可以限制无效的数据插入到数据表中
2、约束分类
1、默认约束(default)
1、作用 ：在插入记录时,如果不给该字段赋值,则使用默认值
2、格式 ：字段名 数据类型 default 值
2、非空约束(not null)
1、作用 ：不允许该字段的值有NULL记录
2、格式 ：字段名 数据类型 not null
7、索引
1、定义
对数据库中表的一列或者多列的值进行排序的一种结构(MySQL中用BTREE方式)
2、优点
可以加快数据的检索速度
3、缺点
1、当你对表中的数据进行增加、修改和删除的时候,索引也要动态维护,降低了数据的维护速度
2、索引需要占用物理空间
4、索引类型
1、普通索引(index)
1、使用规则
1、一个表中可以有多个index字段
2、字段的值可以有重复,且可以为NULL值
3、经常把做查询条件的字段设置为index字段
4、index字段的key标志是MUL
2、创建index
1、创建表时创建
index(字段名1),index(字段名2)
2、在已有表中创建index
create index 索引名 on 表名(字段名);
3、注意
索引名一般和字段名相同
3、查看普通索引
1、desc 表名; -->查看key标志为MUL
2、show index from 表名;
4、删除普通索引
drop index 索引名 on 表名;
注意：
删除索引只能一个一个删除
2、唯一索引
3、主键索引
4、外键索引
------------------------------------------------------------------------------------
MySQL-Day02回顾
1、表记录的管理(删除、更改)
1、删除表记录
1、delete from 表名 where 条件;
## 不加where条件将表记录全部删除
2、更新表记录
1、update 表名 set 字段名=值1,...,where 条件;
## 不加where条件表中所有记录全部更改
2、运算符
1、数值比较&字符比较
1、数值比较：= != > >= < <=
2、字符比较：= !=
2、逻辑比较
1、运算符：and or
3、范围内比较
1、运算符：between and 、 in 、not in
4、匹配空、非空
1、空：is null
2、非空：is not null
3、注意
1、null ：空值,必须用is、is not
2、"" ：空字符串,用 = !=去匹配
5、模糊比较
1、语法：字段名 like 表达式
2、_ 匹配单个字符
% 匹配0到多个字符
3、SQL查询
1、order by(排序)
1、order by 字段名 ASC/DESC;
2、limit(永远放在SQL语句的最后写)
limit m
limit m,n
3、聚合函数
avg(字段名)、sum(字段名)、max()、min()
count(字段名) ##字段值NULL不会被统计
4、group by(给查询的结果进行分组)
1、注意
1、group by之后的字段名必须要为select后的字段名
2、如果select后的字段名没有在group by之后出现，则必须要对该字段进行聚合处理(聚合函数)
5、having(对查询结果进行进一步筛选)
1、having与group by语句联合使用，用来过滤由group by语句返回的记录集
2、弥补了where关键字不能与聚合函数联合使用的不足
3、where只能操作表中实际存在的字段,having可操作聚合函数生成的显示列
6、distinct(不显示字段的重复值)
1、distinct和from之间所有的字段必须全部相同才能去重
2、distinct不能对任何字段进行聚合处理
7、查询表记录时做数学运算
+ - * / %
8、总结(执行顺序)
3、select ...聚合函数 from ...
1、where ...
2、group by ...
4、having ...
5、order by ...
6、limit ...
4、约束
1、默认约束(default)
2、非空约束(not null)
5、索引
1、BTREE
2、优点：可以加快数据的检索速度
3、缺点
1、需要动态维护,降低数据的维护速度
2、索引占用物理空间
4、索引类型
1、普通索引(index-MUL)
1、创建
1、创建表：index(字段名),index(字段名)...
2、已有表：create index 索引名 on 表名(字段名);
2、查看
1、desc 表名;
2、show index from 表名;
3、删除(只能一个一个删)
drop index 索引名 on 表名;
MySQL-Day03笔记
1、索引
1、普通索引(index)
2、唯一索引(unique)
1、使用规则
1、一个表中可以有多个unique字段
2、unique字段的值不允许重复,但可以为空值
3、unique的key标志是UNI
2、创建唯一索引
1、创建表时创建
1、方式一
create table t1(
id int(3) zerofill,
name char(20) unique
);
2、方式二
create table t2(
id int,
name char(20),
age tinyint,
unique(name),
unique(age)
);
2、在已有表中创建
create unique index 索引名 on 表名(字段名);
3、删除唯一索引(unique)
drop index 索引名 on 表名;
删除普通索引、唯一索引只能一个一个删
3、主键(primary key)
1、使用规则
1、一个表中只能有一个主键(primary)字段
2、对应字段的值不允许重复,且不能为空值
3、主键字段的KEY标志为PRI
4、把表中能够唯一标识一条记录的字段设置为主键字段
2、创建主键(primary key)
1、在创建表时创建
1、方式一：字段名 数据类型 primary key,
create table t3(
id int primary key,
name char(20)
);
2、方式二：
create table t4(
id int,
name char(20),
primary key(id)
);
2、在已有表中创建主键
alter table 表名 add primary key(字段名);
3、删除主键
如果主键带自增长属性,则先要删除自增长属性,再删除主键
alter table 表名 modify 字段名 数据类型;
alter table 表名 drop primary key;
3、自增长属性(auto_increment)
1、作用：通常和主键一起配合使用
2、创建表时添加自增长属性
字段名 数据类型 primary key auto_increment
create table t5(
id int primary key auto_increment,
name char(15)
);
3、在已有表中添加自增长属性
alter table 表名 modify 字段名 数据类型 primary key auto_increment;
2、数据导入
1、作用
把文件系统的内容导入到数据库中
2、语法
load data infile "文件名"
into table 表名
fields terminated by "分隔符"
lines terminated by "\n"
3、练习
把/etc/passwd文件中的内容导入到库day03下的userinfo表中
tarena : x : 1000 : 1000 : tarena,,,
用户名 密码 UID GID 用户描述
:/home/tarena : /bin/bash
主目录 登录权限
4、操作步骤
1、在数据中创建对应的表
2、将要导入的文件拷贝到数据库的默认搜索路径中
3、将系统文件导入到创建的表中
1、创建表
create table userinfo(
username char(20),
password char(1),
uid int,
gid int,
comment varchar(50),
homedir varchar(50),
shell varchar(50)
);
2、将要导入的文件拷贝到数据库的默认搜索路径中
1、如何查看数据库的默认搜索路径
show variables like "secure_file_priv";
2、sudo cp /etc/passwd /var/lib/mysql-files/
3、执行数据导入语句
load data infile "/var/lib/mysql-files/passwd"
into table userinfo
fields terminated by ":"
lines terminated by "\n"
4、在userinfo表中第一列添加一个id字段,类型为int,设置为主键带自增长属性
alter table userinfo add id int primary key auto_increment first
5、练习2
导入AID1709.csv成绩表
1、创建对应的表
create table AID1709(
id int,
name varchar(15),
score float(5,2),
phone char(11),
class char(7)
)default charset=utf8;
2、拷贝到数据库目录下
sudo cp /home/tarena/AID1709.csv /var/lib/mysql-files/
3、执行数据导入语句
load data infile "/var/lib/mysql-files/AID1709.csv"
into table AID1709
fields terminated by ","
lines terminated by "\n"
6、注意Ubuntu中文件的权限问题
1、sudo -i
2、cd /var/lib/mysql-files
3、ls -l AID1709.csv
4、chmod 644 AID1709.csv
5、ls -l AID1709.csv ## 有了r权限
6、执行导入语句
load data infile "/var/lib/mysql-files/AID1709.csv"
into table AID1709
fields terminated by ","
lines terminated by "\n";
4、数据导出
1、作用
将数据库表中的记录保存到系统文件里
2、语法格式
select ... from 表名
into outfile "文件名"
fields terminated by "分隔符"
lines terminated by "\n"
3、练习
1、把userinfo表中的用户名、密码和uid三个字段导出来,文件名为user.txt
select username,password,uid from userinfo
into outfile "/var/lib/mysql-files/user.txt"
fields terminated by " "
lines terminated by "\n"
2、把mysql库下user表中的 user、host两个字段的值导出到user2.txt,将其存放在数据库搜索路径下
select user,host from mysql.user
into outfile "/var/lib/mysql-files/user2.txt"
fields terminated by " "
lines terminated by "\n";
4、注意
1、导出的内容完全由SQL查询语句决定
2、执行导出命令时路径必须指定在对应的数据库搜索路径中
show variables like "secure_file_priv";
5、表的复制
1、表的复制
1、语法
create table 表名 select 查询语句;
2、练习
1、复制userinfo表中全部记录和字段,userinfo2
create table userinfo2 select * from userinfo;
2、复制userinfo表的前10条记录,userinfo3
create table userinfo3 select * from userinfo limit 10;
3、复制userinfo表的username,password,uid三个字段的第2-10条记录,userinfo4
create table userinfo4 select username,password,uid from userinfo limit 1,9;
2、复制表结构
1、语法格式
create table 表名 select 查询语句 where false;
2、注意
复制表的时候不会把原表的 key 属性复制过来
3、练习
1、创建user1表,包含userinfo表中username,uid两个字段的前2条记录
create table user1 select username,uid from userinfo limit 2;
2、创建user2表,包含userinfo表中gid,homedir两个字段的前3条记录
create table user2 select gid,homedir from userinfo limit 3;
6、嵌套查询
1、定义
把内层的查询结果作为外层的查询条件
2、语法格式
select 查询语句 where 条件(select查询语句);
3、练习(利用userinfo表操作)
1、把uid的值小于这个字段的平均值的用户名和uid显示出来
select username,uid from userinfo where uid < (select avg(uid) from userinfo);
7、多表查询
1、两种方式
1、select 字段名列表 from 表名列表; 笛卡尔积
select * from user1,user2;
2、select 字段名列表 from 表名列表 where 条件;
3、练习
1、显示省市详细信息
select sheng.s_name,city.c_name from sheng,city where sheng.s_id=city.cfather_id;
2、显示省市县详细信息
-> select sheng.s_name,city.c_name,xian.x_name
-> from sheng,city,xian
-> where
-> sheng.s_id=city.cfather_id and
-> city.c_id=xian.xfather_id;
8、连接查询
1、内连接
1、定义
从表中删除与其他被连接表中没有匹配的所有行
2、语法格式
select 字段名列表 from 表1
inner join 表2 on 条件;
3、显示省市详细信息,没有匹配到的不显示
-> select sheng.s_name,city.c_name,xian.x_name
-> from sheng
-> inner join city on sheng.s_id=city.cfather_id
-> inner join xian on city.c_id=xian.xfather_id;
2、外连接
1、左连接
1、定义
以左表为主显示查询结果
2、语法
select 字段名列表 from 表1
left join 表2 on 条件;
3、练习
1、显示省市详细信息,以省表为主显示
select sheng.s_name,city.c_name from sheng left join city on sheng.s_id=city.cfather_id;
2、显示省市县详细信息,要求市全部显示
-> select sheng.s_name,city.c_name,xian.x_name
-> from sheng right join city
-> on sheng.s_id=city.cfather_id
-> left join xian on city.c_id=xian.xfather_id;
2、右连接
以右表为准显示查询结果,用法同左连接
9、ER模型&ER图
1、定义
ER模型即实体-关系模型,ER图即实体-关系图
2、三个概念
1、实体
1、定义：现实世界中任何可以被认知、区分的事物
2、示例
1、学校 ：学生、教师、课程、班主任
2、企业 ：职工、产品
2、属性
1、定义：实体所具有的特性
2、示例
1、学生属性：学号、姓名、年龄、性别
2、产品属性：产睥睨编号、名称、规格
3、关系
1、定义：实体之间的联系
2、分类
一对一关系(1:1) 班级和班长
一对多关系(1:n) 公司和职工
多对多关系(m:n) 学生和课程
4、ER图的绘制
1、矩形框代表实体,菱形框代表关系,椭圆形代表属性
MySQL-Day03回顾
1、索引
1、普通索引(index)
2、唯一索引(UNI,字段值不允许重复,可为NULL)
1、创建
1、创建表时创建
1、字段名 数据类型 unique
2、unique(字段名),unique(字段名)
2、在已有表中创建
create unique index 索引名 on 表名(字段名);
2、删除
drop index 索引名 on 表名;
3、主键&自增长(PRI,字段值不允许重复,不能为NULL)
1、注意
一个表中只能有一个主键字段,通常把编号字段设置为主键字段
2、创建
1、创建表时创建
1、字段名 数据类型 primary key auto_increment
2、primary key(字段名)
2、在已经有表创建
alter table 表名 add primary key(字段名);
3、删除
1、先删除自增长属性
alter table 表名 modify 字段名 数据类型
2、再删除主键
alter table 表名 drop primary key;
4、在已有表中添加自增长属性
alter table 表名 modify 字段名 数据类型 primary key auto_increment;
2、数据导入
1、语法格式
load data infile "文件名"
into table 表名
fields terminated by "分隔符"
lines terminated by "\n"
## 文件名处必须要写绝对路径
2、数据导入的步骤
1、在数据库中创建对应的表
2、查看数据库的搜索路径
show variables like "secure_file_priv"; | "%secure%"
3、将文件拷贝到数据库的搜索路径中
4、执行数据导入命令
mysql> load data infile ...
3、数据导出
1、语法格式
select 查询语句
into outfile "文件名"
fields terminated by "分隔符"
lines terminated by "\n"
2、数据导出的步骤
1、查看数据库的搜索路径
2、执行数据导出命令
3、注意
1、导出的内容完全由select查询语句决定
2、执行导出命令时路径必须要为数据的搜索路径
4、表复制
1、语法格式
create table 表名 select 查询语句;
2、复制表结构
create table 表名 select 查询语句 where false
3、复制表不会把原表的key属性复制过来
5、子查询(嵌套查询)
1、语法格式
select 查询语句 where 字段名 运算符(SQL查询)
6、多表查询
1、笛卡尔积(不加where条件)
select 字段名列表 from 表名列表;
2、加where条件
select 字段名列表 from 表名列表 where 条件;
7、连接查询
1、内连接(只显示匹配到的行)
select 字段名列表 from 表1
inner join 表2 on 条件;
2、左连接(以左表为主显示查询结果)
... 表1 left join 表2 on 条件;
3、右连接(以右表为主显示查询结果)
... 表1 right join 表2 on 条件;
1、外键
1、定义
让当前表的字段的值在另一张表的范围内选择
2、语法格式
foreign key(参考字段名)
references 被参考表名(被参考字段名)
on delete 级联动作
on update 级联动作
3、使用规则
1、两张表被参考字段和参考字段数据类型要一致
2、被参考字段必须是key的一种,通常primary key
4、案例
表1：缴费信息表(财务)
学号 姓名 班级 缴费金额
1 唐伯虎 AID01 28000
2 点秋香 AID01 20000
create table jftab(
id int primary key,
name varchar(20),
class char(5),
money int
)default charset=utf8;
insert into jftab values
(1,"唐伯虎","AID01",28000),
(2,"点秋香","AID01",20000),
(3,"文征明","AID01",25000);
表2：学生信息表(班主任)
学号 姓名 缴费金额
1 唐伯虎 28000
create table bjtab(
stu_id int,
name varchar(20),
money int,
foreign key(stu_id) references jftab(id)
on delete cascade
on update cascade
)default charset=utf8;
insert into bjtab values
(1,"唐伯虎",28000),
(2,"点秋香",20000);
5、级联动作
1、cascade ：数据级联更新
当主表删除记录或者更改被参考字段的值时,从表会级联更新
2、restrict(默认)
当主表删除或者更新被参考字段的值时,如果从表中有相关联记录则不允许主表删除或更新,必须先删除从表中相关联记录
3、set null
当主表删除或者更新被参考字段值时,从表中相关联记录的外键字段值自动设置为NULL
4、no action(同restrict)
on delete no action on update no action
同restrict,都是立即检查外键限制
6、删除外键
alter table 表名 drop foreign key 外键名;
注意：
1、外键名的查看方式
show create table 表名;
7、在已有表中添加外键
alter table 表名 add foreign key(参考字段名) references 被参考表名(被参考字段名)
on delete 级联动作
on update 级联动作
注意：
在已有表中添加外键时,会收到表中原有数据的限制
2、数据备份(mysqldump,在Linux终端下操作)
1、命令格式
mysqldump -u用户名 -p 源库名 > 路径/XXX.sql
2、示例
1、备份day03库,放到用户主目录下mydata/day03.sql
3、源库名的表示方式
--all-databases 备份所有库
库名 备份单个库
-B 库1 库2 库3 备份多个库
库名 表1 表2 备份指定库的指定表
4、练习
1、备份所有库all_mysql.sql
2、备份MOSHOU和day02库为md2.sql
3、备份MOSHOU库下的sanguo、hero两张表sh.sql
3、数据恢复
1、命令格式
mysql -u用户名 -p 目标库名 < 路径/XXX.sql
2、从所有库的备份文件中恢复某一个库
mysql -u用户名 -p --one-database 库名 < all_mysql.sql
3、注意
1、恢复库时如果恢复到原库会将表中数据覆盖,新增的表不会删除
2、在数据恢复时如果要恢复的库不存在,则先要创建空库
4、事务和事务回滚
1、定义
一件事从开始发生到结束的整个过程
2、作用
确保数据的一致性
3、属性
1、原子性：一个事务是不可分割的工作单位,事务中的各个操作要么都做,要么都不做
2、一致性：事务必须从一个一致性状态到另一个一致性状态
3、隔离性：一个事务的执行不能被其他并发的事务干扰
4、持久性：一个事务一旦提交,它对数据库中数据的改变是持久性的
4、事务及事务回滚的应用
1、MySQL中默认的SQL语句结果会自动commit到数据库
show variables like "autocommit";
2、事务应用
1、开启事务
mysql> start transaction;
mysql> ... 一句或多句SQL命令
##此时autocommit被禁用,SQL命令不会对数据库中数据做修改
2、终止事务
mysql> commit; | rollback;
3、注意
事务和事务回滚只针对于对表记录的操作:增删改有效,对库和表的操作无效
5、案例
1、背景
你：建行卡
你朋友：工行卡
你在建行的自动提款机上给你朋友转账5000元
2、过程
表1：CCB
create table CCB(
name char(20),
money int
);
insert into CCB values("转钱",100000);
表2：ICBC
create table ICBC(
name char(20),
money int
);
insert into ICBC values("借钱",0);
6、python数据库编程
1、python数据库接口(Python DB-API)
1、定义
为开发人员提供的数据库应用编程接口
2、支持的数据库服务软件
MySQL、Oracle、SQL_Server、Sybase、MongoDB ...
3、Python提供的操作MySQL的模块
pymysql
4、pymysql的使用流程
1、建立数据库连接
2、创建游标对象
3、使用游标对象的方法和SQL语句操作数据库
4、提交commit
5、关闭游标对象
6、关闭数据库连接
5、建立数据库连接
1、语法
对象名=pymysql.connect
("主机地址","用户名","密码","库名",charset="utf8",port=3306)
2、connect对象(db)支持的方法
1、cursor() 创建一个游标对象
2、commit() 提交到数据库(增删改)
3、rollback() 回滚
4、close() 关闭数据库连接
3、关于cursor对象支持的方法
1、execute(op) 执行基本SQL命令
2、fetchone() 取得结果集的第一行记录
3、fetchmany(size)取得结果集的size行记录
4、fetchall() 取得结果集的所有行
5、close() 关闭游标对象
属性：
rowcount 返回数据条数或影响行数
1、什么是数据库
数据库是一个存储数据的仓库
2、哪些公司在用数据库
金融机构、游戏网站、购物网站、论坛网站 ... ...
3、提供数据库服务的软件
1、软件分类
MySQL、SQL_Server、Oracle、DB2、MariaDB、MongoDB ...
2、在生产环境中，如何选择使用哪个数据库软件
1、是否开源
开源软件：MySQL、MariaDB、MongoDB
商业软件：Oracle、DB2、SQL_Server
2、是否跨平台
不跨平台：SQL_Server
跨平台：MySQL、MariaDB、MongoDB、Oracle、DB2
3、公司的类型
商业软件：政府部门、金融机构
开源软件：游戏网站、购物网站、论坛网站 ... ...
4、MySQL特点
1、关系型数据库
1、数据是以行和列的形式去存储
2、这一系列的行和列称为表
3、表中的每一行叫一条记录
4、表中的每一列叫一个字段
5、表和表之间的逻辑关联叫关系
示例：
1、关系型数据库存储
表1：学生信息表
姓名 年龄 班级
张无忌 23 AID01
小昭 25 AID02
表2：班级信息表
班级 班主任
AID01 孙大大
AID02 侯大大
2、非关系数据库中存储
{"姓名":"张无忌","年龄":23,"班级":AID01,"班主任":"孙大大}
{"姓名":"小昭","年龄":23,"班级":AID01}
2、跨平台
可以在Unix、Linux、Windows上运行MySQL数据库服务
3、支持多种编程语言
Python、Java、php ... ...
2、MySQL安装
1、Ubuntu上安装MySQL服务
1、安装服务端
sudo apt-get install mysql-server
2、安装客户端
sudo apt-get install mysql-client
2、Windows上安装MySQL服务
1、下载MySQL安装包(windows)
mysql-installer***5.7.***.msi
2、双击、按照教程安装即可
3、启动和连接MySQL服务
1、服务端启动
1、查看MySQL服务的状态
sudo /etc/init.d/mysql status
2、停止MySQL服务
sudo /etc/init.d/mysql stop
3、启动MySQL服务
sudo /etc/init.d/mysql start
4、重启MySQL服务
sudo /etc/init.d/mysql restart
2、客户端连接
1、命令格式
mysql -h主机地址 -u用户名 -p密码
mysql -hlocalhost -uroot -p123456
2、本地连接可以省略 -h 选项
mysql -uroot -p123456
4、基本SQL命令
1、SQL命令的使用规则
1、每条命令必须以 ; 结尾
2、SQL命令不区分字母大小写
3、使用 \c 终止SQL命令的执行
2、库的管理
1、库的基本操作
1、查看已有的库
show databases;
2、创建库(指定字符集,默认为latin1)
create database 库名 default charset=utf8;
3、查看创建库的语句
show create database 库名;
4、查看当前所在库
select database();
5、切换库
use 库名;
6、查看库中已有表
show tables;
7、删除库
drop database 库名;
2、库的命名规则
1、可以使用数字、字母、_,但是不能使用纯数字
2、库名区分字母大小写
3、库名具有唯一性
4、不能使用特殊字符和mysql的关键字
3、练习
1、创建库AID01,指定字符集为utf8
2、进入到库AID01
3、查看当前所在库
4、查看库中已有的表
5、查看AID01的默认字符集(创建库的语句)
6、删除库AID01
3、表的管理
1、表的基本操作
1、创建表(指定字符集)
create table 表名(
字段名 数据类型,
字段名 数据类型,
... ...
)default charset=utf8;
2、查看创建表的语句(字符集)
show create table 表名;
3、查看表结构
desc 表名;
4、删除表
drop table 表名;
2、表的命名规则(同库的命名规则)
3、练习
1、创建库 Python
2、在Python库中创建表py_mysql,并指定字符集utf8
字段有：id、kuname、biaoname,数据类型自己定义
3、查看创建表py_mysql的语句(字符集)
4、查看表py_mysql的表结构
4、注意
1、所有的数据都是以文件形式存放在数据库目录下
2、数据库目录：/var/lib/mysql
4、表记录管理
1、在表中插入记录
insert into 表名 values(值1),(值2),...,(值N);
2、查询表记录
1、select * from 表名 [where 条件];
select * from t1;
select * from t1 where name="Green";
2、select 字段1,字段2,... from 表名 [where 条件];
5、练习
1、查看已有的库
show databases;
2、创建一个新库studb,并切换到该库
create database studb;
use studb;
3、在studb中创建表stuinfo,字段有id name age三个
create table stuinfo(
id int,
name char(20),
age int);
4、查看stuinfo的表结构
desc stuinfo;
5、在表 stuinfo 中任意插入2条记录
insert into stuinfo values(1,"Lucy",20),(2,"Bob",23);
6、查看 stuinfo 中的所有记录
select * from stuinfo;
7、删除表 stuinfo
drop table stuinfo;
8、删除库 studb
drop database studb;
5、更改库、表的默认字符集
1、方法
通过更改MySQL配置文件来实现
2、步骤
cp 文件名 新的文件名
vi 文件名
a -> 写内容
esc -> shift + :
wq
1、获取root用户权限
sudo -i
2、cd到mysql配置文件所在路径
cd /etc/mysql/mysql.conf.d/
3、把配置文件mysqld.cnf复制一份,mysqld.cnf.bak
cp mysqld.cnf mysqld.cnf.bak
4、vi打开配置文件mysqld.cnf
vi mysqld.cnf
在[mysqld]下写入如下语句：
character_set_server=utf8
5、重启MySQL服务
sudo /etc/init.d/mysql restart
6、客户端把数据存储到数据库服务器上的过程
1、连接到数据库服务器 mysql -uroot -p123456
2、选择一个库 use 库名
3、创建表或者修改表
4、断开与数据库服务器的连接 exit; | quit; | \q;
7、数据类型
1、数值类型(有符号signed和无符号unsigned)
1、整型
1、int 大整型(4个字节)
取值范围：2**32 - 1(42亿多)
2、tinyint 微小整型(1个字节)
1、有符号(signed默认): -128 ~ 127
2、无符号(unsigned): 0 ~ 255
3、smallint 小整型(2个字节)
取值范围：0 ~ 65535
4、bigint 极大整型(8个字节)
取值范围：0 ~ 2**64 - 1
2、浮点型
1、float(4个字节,最多显示7个有效位)
1、用法
字段名 float(m,n) m->总位数,n->小数位位数
float(5,2) 取值范围？ -999.99 ~ 999.99
2、注意
1、浮点型插入整数时会自动补全小数位
2、小数位如果多于指定的位数，会对下一位进行四舍五入
2、double(8个字节，最多显示15个有效位)
1、用法
double(m,n)
3、decimal(M+2个字节,最多显示28个有效位)
1、用法
字段名 decimal(M,D)
2、字符类型
1、char(定长)
1、宽度取值范围：1~255
2、不给定宽度默认宽度为1
2、varchar(变长)
1、取值范围：1~65535
2、注意
1、varchar没有默认宽度,必须给定一个宽度值
2、char、varchar使用时都给定宽度,但不要超过各自的范围
3、char和varchar的特点
1、char(定长)
浪费存储空间,但是性能高
2、varchar(变长)
节省存储空间,但是性能低
4、练习
1、创建一个库STUDB,指定字符集为utf8,并切换到该库
create database STUDB default charset=utf8;
2、在STUDB中创建表stuinfo01,字段要求如下：
姓名name:变长,宽度为20
班级class：定长,宽度为5
年龄age：微小整型,要求不能输入负数
身高height：浮点型,小数位为2位
use STUDB;
create table stuinfo01(
name varchar(20),
class char(5),
age tinyint unsigned,
height float(5,2)
);
3、查看表结构
desc stuinfo01;
4、在表中一次性插入3条记录
insert into stuinfo01 values
("张飞","AID01",23,175),
("关羽","AID01",25,180.3),
("郭嘉","AID03",23,170.88);
5、查询所有的表记录
select * from stuinfo01;
6、查询年龄 > 20 的记录
select * from stuinfo01 where age>20;
3、枚举类型
1、定义
字段值只能在列举的范围内去选择
2、enum 单选(最多有65535个不同的值)
字段名 enum(值1,值2,...)
3、set 多选(最多有64个不同的值)
字段名 set(值1,值2,...)
插入记录时 "Python,MySQL,Study
4、日期时间类型
1、year ：年 YYYY
2、date ：日期 YYYYMMDD
3、time ：时间 HHMMSS
4、datetime ：日期时间 YYYYMMDDHHMMSS
5、timestamp：日期时间 YYYYMMDDHHMMSS
create table t7(
id int,
name char(15),
age tinyint unsigned,
birth_year year,
birthday date,
class time,
meeting datetime
);
datetime和timestamp区别示例：
create table t8(
username varchar(15),
password varchar(15),
zhuce datetime,
current timestamp);
6、注意
1、插入记录时datetime字段不给值默认返回NULL
2、插入记录时timestamp字段不给值默认返回系统当前时间
作业：
1、面试题
1、填空题
1、MySQL中的数据类型有___、____、____、____
2、关系型数据库的核心内容是 关系 即 二维表
2、简答题
1、简述客户端把数据存储到数据库服务器上的过程
2、var和varchar的区别及各自的特点
2、操作题
1、创建一个学校的库school
2、在school库中创建一张表students来存储学生信息
字段有：学号、姓名、年龄(不能输入负数)、成绩(浮点)、
性别(单选)、爱好(多选)、入学时间(不给值默认返回当前时间)
3、查看students的表结构
4、在表中随意插入4条记录
5、查看所有学生信息
6、查看所有学生信息的姓名和成绩
7、查看成绩大于60的学生的姓名和成绩
MySQL-Day01回顾
1、MySQL的特点
1、关系型数据库
2、跨平台
3、支持多种编程语言
2、MySQL的启动和连接
1、服务端启动
sudo /etc/init.d/mysql start | stop | restart | status
2、客户端连接
mysql -h主机地址 -u用户名 -p密码
3、注意
1、MySQL中的数据是以文件的形式存放在数据库目录/var/lib/mysql下
2、关系型数据库的核心内容是 关系 即 二维表
3、基本SQL命令
1、库的管理
1、创建库(指定字符集)
create database 库名 [default charset=utf8];
2、查看创建库的语句
show create database 库名;
3、查看当前所在库
select database();
4、切换库
use 库名;
5、查看库中已有的表
show tables;
6、删除库
drop database 库名;
2、表的管理
1、创建表(指定字符集)
create table 表名(
字段名 数据类型,
字段名 数据类型,
...
)[default charset=utf8];
2、查看创建表的语句
show create table 表名;
3、查看表结构
desc 表名;
4、删除表
drop table 表名;
3、表记录管理
1、插入表记录
insert into 表名(字段名1,字段名2,...) values(值1),(值2),...;
2、查看表记录
select 字段名1,字段名2,...,字段名n from 表名 where 条件;
4、如何更改默认字符集
1、方法
通过更改配置文件来实现/etc/mysql/mysql.conf.d/mysqld.cnf
2、步骤
1、sudo -i
2、cd /etc/mysql/mysql.conf.d/
3、cp mysqld.cnf mysqld.cnf.bak
4、vi mysqld.cnf
[mysqld]
character_set_server=utf8
5、sudo /etc/init.d/mysql restart
5、客户端把数据存储到数据库服务器上的过程
1、连接到数据库服务器
2、选择库
3、创建表/修改表
4、断开连接
6、数据类型
1、数值类型(有符号signed和无符号unsigned)
1、整型
1、int(4个字节)
2、tinyint(1个字节):有符号signed 无符号unsigned常用
3、smallint(2个字节)
4、bigint(8个字节)
2、浮点型
1、float(4个字节,最高显示7个有效位)
2、double(8个字节,最高显示15个有效位)
3、decimal(M+2个字节,最高显示28个有效位)
float(m,n) m->总位数 n->小数位位数
4、注意
1、浮点型 插入整数时 会自动补全小数位位数
2、小数位如果多于指定位数,会对下一位四舍五入
2、字符类型
1、char(定长,浪费存储空间,性能高)##不指定宽度,默认为1
2、varchar(变长,节省存储空间,性能低)##必须指定宽度
3、枚举类型
1、enum单选(最多有65535个不同值)
2、set多选(最多有64个不同值)
4、日期时间类型
1、year
2、date
3、time
4、datetime ##不给值默认返回NULL
5、timestamp ##不给值默认返回系统当前时间
-------------------------------------------------------------------------------------
MySQL-Day02笔记
1、字符类型的宽度和数值类型的宽度的区别
1、数值类型的宽度为显示宽度,只用于select查询时显示,和占用的存储空间大小无关,可用zerofill来显示效果
2、字符类型的宽度超过指定宽度则无法存储
create table t1(
id int(3) zerofill,
name char(20)
);
insert into t1 values(1,"lucy"); ## 显示效果为 001
2、表字段的操作
1、语法：alter table 表名 执行动作;
2、添加字段(add)
1、添加到末尾
alter table 表名 add 字段名 数据类型;
2、添加到第一列
alter table 表名 add 字段名 数据类型 first;
3、添加到指定位置
alter table 表名 add 字段名 数据类型 after 字段名;
3、删除字段(drop)
alter table 表名 drop 字段名;
4、修改字段数据类型(modify)
alter table 表名 modify 字段名 新的数据类型;
5、修改字段名(change)
alter table 表名 change 旧字段名 新字段名 数据类型;
6、修改表名(rename)
alter table 表名 rename 新表名;
3、表记录的管理
1、删除表记录
1、delete from 表名 where 条件;
2、注意
delete语句后如果不加where条件子句会将表中所有记录全部删除
3、示例
create table t4(
id int(3) zerofill,
name varchar(20),
sex enum("boy","girl")
);
insert into t4 values
(1,"Lucy","girl"),
(2,"Tom","boy"),
(3,"Bob","boy");
select * from t4;
2、更新表记录
1、update 表名 set 字段名=值1,字段名=值2,...,where 条件;
2、注意
update语句后如果不加where条件会将表中所有的值修改
3、练习(见建表脚本MOSHOU.hero.txt)
1、查找所有蜀国人的信息
select * from hero where country="蜀国";
2、查找女英雄的姓名、性别和国家
select name,sex,country from hero where sex="女";
3、把曹操的国籍改为蜀国
update hero set country="蜀国" where name="曹操";
4、把魏延的性别改为 女 ,国籍改为 泰国
5、把id为2的记录的姓名改为司马懿,性别为男,国家为魏国
6、删除所有的泰国人
7、将表中所有的记录的国家改为吴国
8、删除所有英雄的信息
4、运算符操作(配合查询、修改、删除)
1、数值比较&字符比较
1、数值比较运算符：=、!=、>、>=、<、<=
2、字符比较运算符：=、!=
3、练习
1、找出攻击力高于150的英雄的名字和攻击力的值
2、找出防御力值不是66的英雄信息
2、逻辑比较
1、运算符：and(多个条件同时满足)
or(多个条件有一个满足即可)
2、练习
1、找出攻击值大于200的蜀国英雄的名字、攻击值及国家
2、将吴国英雄中攻击值为110的英雄的攻击值设置为100，防御值设置为60
3、查找蜀国和魏国的英雄信息
3、范围内比较
1、运算符：between and、in、not in
2、语法
字段名 between 值1 and 值2
字段名 in(值1,值2,...,值N)
字段名 not in(值1,值2,...,值N)
3、练习
1、查找攻击值在100-200之间的蜀国英雄信息
2、找到蜀国和吴国以外的国家的女英雄信息
3、找到编号为1、3或5的蜀国英雄和貂蝉的编号、姓名、国家
4、匹配空、非空
1、空：is null
2、非空：is not null
3、练习
1、查找姓名为空值(NULL)的蜀国男英雄信息
2、查找姓名为空字符串("")的英雄信息
4、注意
1、NULL ：空值,必须用is或者is not去匹配
2、"" ：空字符串,用 = 或者 != 去匹配
5、模糊比较
1、语法
字段名 like 表达式
2、表达式
1、_ ：匹配单个字符
2、% ：匹配0到多个字符
3、练习
1、select id,name from sanguo where name like "_%_";
# 匹配名字中至少有两个字符的
2、select id,name from sanguo where name like "%";
# 匹配除NULL之外的所有英雄信息
3、select id,name from sanguo where name like "___";
# 匹配名字中有三个字符的
4、select id,name from sanguo where name like "赵%";
# 匹配姓赵的英雄信息
5、SQL查询
1、总结(执行顺序)
3、 select ...聚合函数 from ...
1、 where ...
2、 group by ...
4、 having ...
5、 order by ...
6、 limit ...
2、order by
1、作用：给查询的结果进行排序
2、排序方式
1、ASC(默认) ：升序
2、DESC ：降序
3、语法格式
order by 字段名 排序方式
4、练习
1、将英雄信息按防御值从低到高排序
select * from sanguo order by fangyu asc;
2、将蜀国英雄信息按攻击值从高到低排序
select * from sanguo where country="蜀国" order by gongji DESC;
3、将魏蜀两国的男英雄中名字为三个字的英雄按防御值升序排列
select * from sanguo where
country in("蜀国","魏国") and sex="男" and name like "___"
order by fangyu;
3、limit(永远放在SQL语句的最后写)
1、作用 ：限制显示查询记录的条数
2、用法
1、limit n -->显示几条记录
2、limit m,n
m -> 从第几条记录开始显示,n表示显示几条
## m的值是从0开始计数的,3则表示第四条记录开始
limit 3,5 ：显示4-8条记录
3、练习
1、查找攻击值前三名且名字不为空值的蜀国英雄的姓名、攻击值和国家
-> select name,gongji,country from sanguo
-> where
-> name is not null and country="蜀国"
-> order by gongji desc
-> limit 3;
2、查找防御值倒数第二名到倒数第四名的蜀国英雄记录
-> select * from sanguo
-> where
-> country="蜀国"
-> order by fangyu asc limit 1,3;
4、聚合函数
1、分类
1、avg(字段名) : 求字段的平均值
2、sum(字段名) : 求字段的和
3、max(字段名) : 求字段的最大值
4、min(字段名) : 求字段的最小值
5、count(字段名):统计该字段的记录的个数
2、练习
1、攻击力最强值是多少
select max(gongji) from sanguo;
2、统计一下表中id,name字段分别有独傲少条记录
select count(id),count(name) from sanguo;
## 空值NULL不会被统计,空字符串""会被统计
3、计算蜀国英雄的总攻击力
-> select sum(gongji) from sanguo
-> where country="蜀国";
4、统计蜀国英雄中攻击值大于200的英雄的数量
-> select count(*) from sanguo
-> where
-> country="蜀国" and gongji>200;
5、group by
1、作用：给查询的结果进行分组
2、练习
1、查询sanguo表中一共有几个国家
select country from sanguo group by country;
2、计算所有国家的平均攻击力
select country,avg(gongji) from sanguo
group by country;
3、查找所有国家中英雄数量最多的前两名的国家名称和英雄数量
-> select country,count(*) from sanguo
-> group by country
-> order by count(*) desc
-> limit 2;
3、注意
1、group by之后的字段名必须要为select之后的字段名
2、如果select之后的字段名和group by之后的字段名不一致,则必须要对该字段值进行聚合处理(聚合函数)
6、having语句
1、作用：对查询的结果进行进一步的筛选
2、练习
1、找出平均攻击力大于105的国家的前两名,显示国家名和平均攻击力
-> select country,avg(gongji) from sanguo
-> group by country
-> having avg(gongji)>105
-> order by avg(gongji) desc
-> limit 2;
3、注意
1、having语句通常与group by语句联合使用,用于过滤由group by语句返回的记录集
2、having语句的存在弥补了where条件子句不能与聚合函数联合使用的不足,where只能操作表中实际存在的字段,having操作的是聚合函数生成的显示列
7、distinct
1、作用：不显示字段的重复值
2、练习
1、sanguo表中有哪几个国家
select distinct country from sanguo;
# 多个字段用逗号隔开
2、计算蜀国一共有多少个英雄
select count(distinct name) from sanguo where country="蜀国";
3、注意
1、distinct处理的是distinct与from之间的所有字段,所有字段值必须全部相同才能去重
2、distinct不能对任何字段做聚合处理
8、查询表记录时做数学运算
1、运算符
+、-、*、/、%
2、练习
1、查询显示时所有英雄的攻击力全部乘以10
select id,name,country,gongji*10 as xgj from sanguo;
2、查询时显示所有英雄的防御力+5
select id,name,country,fangyu+5 from sanguo;
6、约束
1、作用
为了保证数据的完整性、一致性、有效性的规则
可以限制无效的数据插入到数据表中
2、约束分类
1、默认约束(default)
1、作用 ：在插入记录时,如果不给该字段赋值,则使用默认值
2、格式 ：字段名 数据类型 default 值
2、非空约束(not null)
1、作用 ：不允许该字段的值有NULL记录
2、格式 ：字段名 数据类型 not null
7、索引
1、定义
对数据库中表的一列或者多列的值进行排序的一种结构(MySQL中用BTREE方式)
2、优点
可以加快数据的检索速度
3、缺点
1、当你对表中的数据进行增加、修改和删除的时候,索引也要动态维护,降低了数据的维护速度
2、索引需要占用物理空间
4、索引类型
1、普通索引(index)
1、使用规则
1、一个表中可以有多个index字段
2、字段的值可以有重复,且可以为NULL值
3、经常把做查询条件的字段设置为index字段
4、index字段的key标志是MUL
2、创建index
1、创建表时创建
index(字段名1),index(字段名2)
2、在已有表中创建index
create index 索引名 on 表名(字段名);
3、注意
索引名一般和字段名相同
3、查看普通索引
1、desc 表名; -->查看key标志为MUL
2、show index from 表名;
4、删除普通索引
drop index 索引名 on 表名;
注意：
删除索引只能一个一个删除
2、唯一索引
3、主键索引
4、外键索引
------------------------------------------------------------------------------------
MySQL-Day02回顾
1、表记录的管理(删除、更改)
1、删除表记录
1、delete from 表名 where 条件;
## 不加where条件将表记录全部删除
2、更新表记录
1、update 表名 set 字段名=值1,...,where 条件;
## 不加where条件表中所有记录全部更改
2、运算符
1、数值比较&字符比较
1、数值比较：= != > >= < <=
2、字符比较：= !=
2、逻辑比较
1、运算符：and or
3、范围内比较
1、运算符：between and 、 in 、not in
4、匹配空、非空
1、空：is null
2、非空：is not null
3、注意
1、null ：空值,必须用is、is not
2、"" ：空字符串,用 = !=去匹配
5、模糊比较
1、语法：字段名 like 表达式
2、_ 匹配单个字符
% 匹配0到多个字符
3、SQL查询
1、order by(排序)
1、order by 字段名 ASC/DESC;
2、limit(永远放在SQL语句的最后写)
limit m
limit m,n
3、聚合函数
avg(字段名)、sum(字段名)、max()、min()
count(字段名) ##字段值NULL不会被统计
4、group by(给查询的结果进行分组)
1、注意
1、group by之后的字段名必须要为select后的字段名
2、如果select后的字段名没有在group by之后出现，则必须要对该字段进行聚合处理(聚合函数)
5、having(对查询结果进行进一步筛选)
1、having与group by语句联合使用，用来过滤由group by语句返回的记录集
2、弥补了where关键字不能与聚合函数联合使用的不足
3、where只能操作表中实际存在的字段,having可操作聚合函数生成的显示列
6、distinct(不显示字段的重复值)
1、distinct和from之间所有的字段必须全部相同才能去重
2、distinct不能对任何字段进行聚合处理
7、查询表记录时做数学运算
+ - * / %
8、总结(执行顺序)
3、select ...聚合函数 from ...
1、where ...
2、group by ...
4、having ...
5、order by ...
6、limit ...
4、约束
1、默认约束(default)
2、非空约束(not null)
5、索引
1、BTREE
2、优点：可以加快数据的检索速度
3、缺点
1、需要动态维护,降低数据的维护速度
2、索引占用物理空间
4、索引类型
1、普通索引(index-MUL)
1、创建
1、创建表：index(字段名),index(字段名)...
2、已有表：create index 索引名 on 表名(字段名);
2、查看
1、desc 表名;
2、show index from 表名;
3、删除(只能一个一个删)
drop index 索引名 on 表名;
MySQL-Day03笔记
1、索引
1、普通索引(index)
2、唯一索引(unique)
1、使用规则
1、一个表中可以有多个unique字段
2、unique字段的值不允许重复,但可以为空值
3、unique的key标志是UNI
2、创建唯一索引
1、创建表时创建
1、方式一
create table t1(
id int(3) zerofill,
name char(20) unique
);
2、方式二
create table t2(
id int,
name char(20),
age tinyint,
unique(name),
unique(age)
);
2、在已有表中创建
create unique index 索引名 on 表名(字段名);
3、删除唯一索引(unique)
drop index 索引名 on 表名;
删除普通索引、唯一索引只能一个一个删
3、主键(primary key)
1、使用规则
1、一个表中只能有一个主键(primary)字段
2、对应字段的值不允许重复,且不能为空值
3、主键字段的KEY标志为PRI
4、把表中能够唯一标识一条记录的字段设置为主键字段
2、创建主键(primary key)
1、在创建表时创建
1、方式一：字段名 数据类型 primary key,
create table t3(
id int primary key,
name char(20)
);
2、方式二：
create table t4(
id int,
name char(20),
primary key(id)
);
2、在已有表中创建主键
alter table 表名 add primary key(字段名);
3、删除主键
如果主键带自增长属性,则先要删除自增长属性,再删除主键
alter table 表名 modify 字段名 数据类型;
alter table 表名 drop primary key;
3、自增长属性(auto_increment)
1、作用：通常和主键一起配合使用
2、创建表时添加自增长属性
字段名 数据类型 primary key auto_increment
create table t5(
id int primary key auto_increment,
name char(15)
);
3、在已有表中添加自增长属性
alter table 表名 modify 字段名 数据类型 primary key auto_increment;
2、数据导入
1、作用
把文件系统的内容导入到数据库中
2、语法
load data infile "文件名"
into table 表名
fields terminated by "分隔符"
lines terminated by "\n"
3、练习
把/etc/passwd文件中的内容导入到库day03下的userinfo表中
tarena : x : 1000 : 1000 : tarena,,,
用户名 密码 UID GID 用户描述
:/home/tarena : /bin/bash
主目录 登录权限
4、操作步骤
1、在数据中创建对应的表
2、将要导入的文件拷贝到数据库的默认搜索路径中
3、将系统文件导入到创建的表中
1、创建表
create table userinfo(
username char(20),
password char(1),
uid int,
gid int,
comment varchar(50),
homedir varchar(50),
shell varchar(50)
);
2、将要导入的文件拷贝到数据库的默认搜索路径中
1、如何查看数据库的默认搜索路径
show variables like "secure_file_priv";
2、sudo cp /etc/passwd /var/lib/mysql-files/
3、执行数据导入语句
load data infile "/var/lib/mysql-files/passwd"
into table userinfo
fields terminated by ":"
lines terminated by "\n"
4、在userinfo表中第一列添加一个id字段,类型为int,设置为主键带自增长属性
alter table userinfo add id int primary key auto_increment first
5、练习2
导入AID1709.csv成绩表
1、创建对应的表
create table AID1709(
id int,
name varchar(15),
score float(5,2),
phone char(11),
class char(7)
)default charset=utf8;
2、拷贝到数据库目录下
sudo cp /home/tarena/AID1709.csv /var/lib/mysql-files/
3、执行数据导入语句
load data infile "/var/lib/mysql-files/AID1709.csv"
into table AID1709
fields terminated by ","
lines terminated by "\n"
6、注意Ubuntu中文件的权限问题
1、sudo -i
2、cd /var/lib/mysql-files
3、ls -l AID1709.csv
4、chmod 644 AID1709.csv
5、ls -l AID1709.csv ## 有了r权限
6、执行导入语句
load data infile "/var/lib/mysql-files/AID1709.csv"
into table AID1709
fields terminated by ","
lines terminated by "\n";
4、数据导出
1、作用
将数据库表中的记录保存到系统文件里
2、语法格式
select ... from 表名
into outfile "文件名"
fields terminated by "分隔符"
lines terminated by "\n"
3、练习
1、把userinfo表中的用户名、密码和uid三个字段导出来,文件名为user.txt
select username,password,uid from userinfo
into outfile "/var/lib/mysql-files/user.txt"
fields terminated by " "
lines terminated by "\n"
2、把mysql库下user表中的 user、host两个字段的值导出到user2.txt,将其存放在数据库搜索路径下
select user,host from mysql.user
into outfile "/var/lib/mysql-files/user2.txt"
fields terminated by " "
lines terminated by "\n";
4、注意
1、导出的内容完全由SQL查询语句决定
2、执行导出命令时路径必须指定在对应的数据库搜索路径中
show variables like "secure_file_priv";
5、表的复制
1、表的复制
1、语法
create table 表名 select 查询语句;
2、练习
1、复制userinfo表中全部记录和字段,userinfo2
create table userinfo2 select * from userinfo;
2、复制userinfo表的前10条记录,userinfo3
create table userinfo3 select * from userinfo limit 10;
3、复制userinfo表的username,password,uid三个字段的第2-10条记录,userinfo4
create table userinfo4 select username,password,uid from userinfo limit 1,9;
2、复制表结构
1、语法格式
create table 表名 select 查询语句 where false;
2、注意
复制表的时候不会把原表的 key 属性复制过来
3、练习
1、创建user1表,包含userinfo表中username,uid两个字段的前2条记录
create table user1 select username,uid from userinfo limit 2;
2、创建user2表,包含userinfo表中gid,homedir两个字段的前3条记录
create table user2 select gid,homedir from userinfo limit 3;
6、嵌套查询
1、定义
把内层的查询结果作为外层的查询条件
2、语法格式
select 查询语句 where 条件(select查询语句);
3、练习(利用userinfo表操作)
1、把uid的值小于这个字段的平均值的用户名和uid显示出来
select username,uid from userinfo where uid < (select avg(uid) from userinfo);
7、多表查询
1、两种方式
1、select 字段名列表 from 表名列表; 笛卡尔积
select * from user1,user2;
2、select 字段名列表 from 表名列表 where 条件;
3、练习
1、显示省市详细信息
select sheng.s_name,city.c_name from sheng,city where sheng.s_id=city.cfather_id;
2、显示省市县详细信息
-> select sheng.s_name,city.c_name,xian.x_name
-> from sheng,city,xian
-> where
-> sheng.s_id=city.cfather_id and
-> city.c_id=xian.xfather_id;
8、连接查询
1、内连接
1、定义
从表中删除与其他被连接表中没有匹配的所有行
2、语法格式
select 字段名列表 from 表1
inner join 表2 on 条件;
3、显示省市详细信息,没有匹配到的不显示
-> select sheng.s_name,city.c_name,xian.x_name
-> from sheng
-> inner join city on sheng.s_id=city.cfather_id
-> inner join xian on city.c_id=xian.xfather_id;
2、外连接
1、左连接
1、定义
以左表为主显示查询结果
2、语法
select 字段名列表 from 表1
left join 表2 on 条件;
3、练习
1、显示省市详细信息,以省表为主显示
select sheng.s_name,city.c_name from sheng left join city on sheng.s_id=city.cfather_id;
2、显示省市县详细信息,要求市全部显示
-> select sheng.s_name,city.c_name,xian.x_name
-> from sheng right join city
-> on sheng.s_id=city.cfather_id
-> left join xian on city.c_id=xian.xfather_id;
2、右连接
以右表为准显示查询结果,用法同左连接
9、ER模型&ER图
1、定义
ER模型即实体-关系模型,ER图即实体-关系图
2、三个概念
1、实体
1、定义：现实世界中任何可以被认知、区分的事物
2、示例
1、学校 ：学生、教师、课程、班主任
2、企业 ：职工、产品
2、属性
1、定义：实体所具有的特性
2、示例
1、学生属性：学号、姓名、年龄、性别
2、产品属性：产睥睨编号、名称、规格
3、关系
1、定义：实体之间的联系
2、分类
一对一关系(1:1) 班级和班长
一对多关系(1:n) 公司和职工
多对多关系(m:n) 学生和课程
4、ER图的绘制
1、矩形框代表实体,菱形框代表关系,椭圆形代表属性
MySQL-Day03回顾
1、索引
1、普通索引(index)
2、唯一索引(UNI,字段值不允许重复,可为NULL)
1、创建
1、创建表时创建
1、字段名 数据类型 unique
2、unique(字段名),unique(字段名)
2、在已有表中创建
create unique index 索引名 on 表名(字段名);
2、删除
drop index 索引名 on 表名;
3、主键&自增长(PRI,字段值不允许重复,不能为NULL)
1、注意
一个表中只能有一个主键字段,通常把编号字段设置为主键字段
2、创建
1、创建表时创建
1、字段名 数据类型 primary key auto_increment
2、primary key(字段名)
2、在已经有表创建
alter table 表名 add primary key(字段名);
3、删除
1、先删除自增长属性
alter table 表名 modify 字段名 数据类型
2、再删除主键
alter table 表名 drop primary key;
4、在已有表中添加自增长属性
alter table 表名 modify 字段名 数据类型 primary key auto_increment;
2、数据导入
1、语法格式
load data infile "文件名"
into table 表名
fields terminated by "分隔符"
lines terminated by "\n"
## 文件名处必须要写绝对路径
2、数据导入的步骤
1、在数据库中创建对应的表
2、查看数据库的搜索路径
show variables like "secure_file_priv"; | "%secure%"
3、将文件拷贝到数据库的搜索路径中
4、执行数据导入命令
mysql> load data infile ...
3、数据导出
1、语法格式
select 查询语句
into outfile "文件名"
fields terminated by "分隔符"
lines terminated by "\n"
2、数据导出的步骤
1、查看数据库的搜索路径
2、执行数据导出命令
3、注意
1、导出的内容完全由select查询语句决定
2、执行导出命令时路径必须要为数据的搜索路径
4、表复制
1、语法格式
create table 表名 select 查询语句;
2、复制表结构
create table 表名 select 查询语句 where false
3、复制表不会把原表的key属性复制过来
5、子查询(嵌套查询)
1、语法格式
select 查询语句 where 字段名 运算符(SQL查询)
6、多表查询
1、笛卡尔积(不加where条件)
select 字段名列表 from 表名列表;
2、加where条件
select 字段名列表 from 表名列表 where 条件;
7、连接查询
1、内连接(只显示匹配到的行)
select 字段名列表 from 表1
inner join 表2 on 条件;
2、左连接(以左表为主显示查询结果)
... 表1 left join 表2 on 条件;
3、右连接(以右表为主显示查询结果)
... 表1 right join 表2 on 条件;
1、外键
1、定义
让当前表的字段的值在另一张表的范围内选择
2、语法格式
foreign key(参考字段名)
references 被参考表名(被参考字段名)
on delete 级联动作
on update 级联动作
3、使用规则
1、两张表被参考字段和参考字段数据类型要一致
2、被参考字段必须是key的一种,通常primary key
4、案例
表1：缴费信息表(财务)
学号 姓名 班级 缴费金额
1 唐伯虎 AID01 28000
2 点秋香 AID01 20000
create table jftab(
id int primary key,
name varchar(20),
class char(5),
money int
)default charset=utf8;
insert into jftab values
(1,"唐伯虎","AID01",28000),
(2,"点秋香","AID01",20000),
(3,"文征明","AID01",25000);
表2：学生信息表(班主任)
学号 姓名 缴费金额
1 唐伯虎 28000
create table bjtab(
stu_id int,
name varchar(20),
money int,
foreign key(stu_id) references jftab(id)
on delete cascade
on update cascade
)default charset=utf8;
insert into bjtab values
(1,"唐伯虎",28000),
(2,"点秋香",20000);
5、级联动作
1、cascade ：数据级联更新
当主表删除记录或者更改被参考字段的值时,从表会级联更新
2、restrict(默认)
当主表删除或者更新被参考字段的值时,如果从表中有相关联记录则不允许主表删除或更新,必须先删除从表中相关联记录
3、set null
当主表删除或者更新被参考字段值时,从表中相关联记录的外键字段值自动设置为NULL
4、no action(同restrict)
on delete no action on update no action
同restrict,都是立即检查外键限制
6、删除外键
alter table 表名 drop foreign key 外键名;
注意：
1、外键名的查看方式
show create table 表名;
7、在已有表中添加外键
alter table 表名 add foreign key(参考字段名) references 被参考表名(被参考字段名)
on delete 级联动作
on update 级联动作
注意：
在已有表中添加外键时,会收到表中原有数据的限制
2、数据备份(mysqldump,在Linux终端下操作)
1、命令格式
mysqldump -u用户名 -p 源库名 > 路径/XXX.sql
2、示例
1、备份day03库,放到用户主目录下mydata/day03.sql
3、源库名的表示方式
--all-databases 备份所有库
库名 备份单个库
-B 库1 库2 库3 备份多个库
库名 表1 表2 备份指定库的指定表
4、练习
1、备份所有库all_mysql.sql
2、备份MOSHOU和day02库为md2.sql
3、备份MOSHOU库下的sanguo、hero两张表sh.sql
3、数据恢复
1、命令格式
mysql -u用户名 -p 目标库名 < 路径/XXX.sql
2、从所有库的备份文件中恢复某一个库
mysql -u用户名 -p --one-database 库名 < all_mysql.sql
3、注意
1、恢复库时如果恢复到原库会将表中数据覆盖,新增的表不会删除
2、在数据恢复时如果要恢复的库不存在,则先要创建空库
4、事务和事务回滚
1、定义
一件事从开始发生到结束的整个过程
2、作用
确保数据的一致性
3、属性
1、原子性：一个事务是不可分割的工作单位,事务中的各个操作要么都做,要么都不做
2、一致性：事务必须从一个一致性状态到另一个一致性状态
3、隔离性：一个事务的执行不能被其他并发的事务干扰
4、持久性：一个事务一旦提交,它对数据库中数据的改变是持久性的
4、事务及事务回滚的应用
1、MySQL中默认的SQL语句结果会自动commit到数据库
show variables like "autocommit";
2、事务应用
1、开启事务
mysql> start transaction;
mysql> ... 一句或多句SQL命令
##此时autocommit被禁用,SQL命令不会对数据库中数据做修改
2、终止事务
mysql> commit; | rollback;
3、注意
事务和事务回滚只针对于对表记录的操作:增删改有效,对库和表的操作无效
5、案例
1、背景
你：建行卡
你朋友：工行卡
你在建行的自动提款机上给你朋友转账5000元
2、过程
表1：CCB
create table CCB(
name char(20),
money int
);
insert into CCB values("转钱",100000);
表2：ICBC
create table ICBC(
name char(20),
money int
);
insert into ICBC values("借钱",0);
6、python数据库编程
1、python数据库接口(Python DB-API)
1、定义
为开发人员提供的数据库应用编程接口
2、支持的数据库服务软件
MySQL、Oracle、SQL_Server、Sybase、MongoDB ...
3、Python提供的操作MySQL的模块
pymysql
4、pymysql的使用流程
1、建立数据库连接
2、创建游标对象
3、使用游标对象的方法和SQL语句操作数据库
4、提交commit
5、关闭游标对象
6、关闭数据库连接
5、建立数据库连接
1、语法
对象名=pymysql.connect
("主机地址","用户名","密码","库名",charset="utf8",port=3306)
2、connect对象(db)支持的方法
1、cursor() 创建一个游标对象
2、commit() 提交到数据库(增删改)
3、rollback() 回滚
4、close() 关闭数据库连接
3、关于cursor对象支持的方法
1、execute(op) 执行基本SQL命令
2、fetchone() 取得结果集的第一行记录
3、fetchmany(size)取得结果集的size行记录
4、fetchall() 取得结果集的所有行
5、close() 关闭游标对象
属性：
rowcount 返回数据条数或影响行数
391
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
