MySQL
基础概念
数据（Data）：文本，数字，图片，视频，音频等多种表现形式，能够被计算机存储和处理。
**数据库（Data Base—简称DB）：**存储数据的仓库，位于计算机存储上设备之上，数据按照一定格式存放的。
**数据库管理系统（DataBase Management System,简称DBMS）😗*用于建立、使用和维护数据库。是管理数据库的软件。
**数据库系统（DataBase System，简称DBS）😗*是一个完整的数据操作链路，包含从用户到数据的所有内容。
三者之间的关系：
命令方式
net start mysql --启动 net stop mysql --停止
使用Mysql客户端登录服务
1.没有配置环境变量情况下，先打开mysql安装目录，打开bin文件夹，执行登录口令
2.配置了环境变量情况下直接执行口令即可
登录语法：mysql -u用户名 [-h主机名] -p密码 [-P端口号] mysql -uroot -h127.0.0.1 -p123456 -P3306 -- root表示用户名 -- 127.0.0.1表示本机IP,也可使用localhost代替 -- 123456mysql登录密码 -- 3306表示MySQL端口号 -- 如果登录本机数据库，ip和端口号可以省略不写
完整操作步骤
1.打开cmd命令符操作界面，输入cmd回车
win+r2.登录mysql服务
mysql -uroot -p 输入密码3.查看数据库列表
show databases;4.打开mysql数据库
use mysql;5.展示当前库所有数据表
show tables;6.查询user表中记录
select * from user;7.退出mysql
1.exit 2.quit 3.q
数据库图形化管理工具
Navicat
SQLyog
Datagrip
Workbench
Navicat连接mysql
日期和时间型
year类型：1901~2155
date类型：YYYYY-MM-DD、YYYY/MM/DD、YYYYMMDD、YYYY.MM.DD、YYYY,MM,DD
time类型：HH:MM:SS
datetime:YYYYY-MM-DD HH:MM:SS
timestemp:显示从1970到现在所经过的毫秒数。
字符串类型
char: 定长字符串
char(长度)：长度取值范围1~255
二进制类型字符串
binary:固定长度，参考char
varbinary:可变长度,参考 varchar
文本类型
text:保存的是非二进制的长文本数据，例如文章内容、评论等比较长的文本。
子类型：tinytext、text、mediumtext、longtext
blob:保存数据量很大的二进制数据，如图片、声音、视频一级扫描文档。
子类型：tinyblob、blob、mediumblob、longblob
text与blob的区别
text以文本形式存储数据，blob以二进制方式存储。
text不区分大小写，blob区分大小写
字符集对text有影响，对blob没有影响
text和blob执行效率低于char和varchar类型枚举和集合类型
enum：枚举类型
格式：ENUM(‘值1’,‘值2’,‘值3’…)
枚举列表最多可以存储65535个值
ENUM(‘男’,‘女’)
set：集合类型
格式：SET(‘值1’,‘值2’,‘值3’…)
集合列表最多可以存储64个值。
SET(‘唱歌’,‘跳舞’,‘打篮球’)
枚举和集合的区别
枚举只支持单选，集合支持多选
只能插入规定的数据项，节省空间，提高查询效率
枚举和集合都支持中文
插入和查询操作时自动忽略末尾空格。mysql运算符
算术运算符
运算符 书写格式 功能 + n1+n2 加法运算 – n1-n2 减法运算 * n1*n2 乘法运算 /或div(整除) n1/n2 或 n1 div n2 除法运算 mod或% n1 mod n2 或n1%n2 取余运算
取余注意：
如果n1的绝对值能被n2的绝对值整除，余数为零。
n1与n2取余时，所得余数的符号与被除数n1的符号相同。
小数也能实现取余(求模)操作。
运算过程中只要有null参与也运算，那么最终结果就为null。
如果除法或取余运算除数为零时，系统不报错，结果为null。
比较运算符
运算符 功能 >、>= 判断大于、大于等于 <、<= 判断小于、小于等于 = 等值判断 <>、!= 判断不等于 <=> 判断是否为null in、not in 判断是否包含 is null、is not null 判断是否为空 between and、not between and 范围判断 like、not like 字符串匹配判断 regexp 正则表达式判断 注意：
比较运算符可用于多种数据类型的比较。
比较运算符返回值为逻辑型，可分为三种，分别是1(表示True)、0(表示False)、null。
比较字符串时，不区分大小写。
null值与任何类型的内容进行运算时，结果都为null。逻辑运算符
运算符 功能 && 或 and 运算符左右两边同时为真，结果才为真，其他情况都为假 || 或 or 运算符左右两边有一个结果为真，最终结果几位真。 ! 或 not 取反 XOR(异或) 运算符左右两边的结果相反，最终结果为真。否则结果为假。 注意：
逻辑与运算时，如果操作数中含有null,另一个操作数为真，结果为null，如果另一个操作数为假，结果为0
逻辑或运算时，如果操作数中含有null,另一个操作数为真，结果为1，如果另一个操作数为假时，结果为null
异或运算中，操作数中含有null,结果为null。
运算符优先级
优先级 运算符 1 ！ 2 +(正号)、-（负号） 3 ^(幂次方) 4 *、/、DIV、%（mod） 5 +、- 6 =、>、<。。。。关系运算符 7 between…and… 8 not 9 &&、and 10 XOR 11 ||、or 12 =(赋值) mysql函数
聚合函数
函数格式 功能 count(*) 计数函数，统计表中记录数 sum(字段名) 统计该字段名的和 avg(字段名) 统计该字段平均值 max(字段名) 统计该字段中最大值 min(字段名) 统计该字段中最小值 聚合函数通常需要配合group by分组语句使用。
数学函数
函数格式 功能 示例 sign(n) 符号函数，如果n为整数，返回值为1，如果n为负数，返回值为-1，如果n为0，返回值就为0 abs(n) **绝对值函数，**返回n的绝对值。 sqrt(n) **平方根函数，**返回n的平方根。n不能为负数，否则返回值为null。 mod(n1,n2) 取余函数，返回n1除以n2所得的余数。 ceil(n)或ceiling(n) 向上取整函数，返回大于或等于n的整数。 floor(n) 向下取整函数，返回小于或等于n的整数。 rand() 随机函数，返回0~1之间的随机数。获取指定范围随机数公式floor(下限+rand()*(上限-下限)) rand(n) 随机函数，返回0~1之间的随机数，n不变的话，每次返回的结果相同。 round(n) 四舍五入函数，将n进行四舍五入取整数。 round(n1,n2) 四舍五入函数，将n1四舍五入保留n2位小数。 format(n1,n2) 格式化函数，将n1四舍五入保留n2位小数，并将结果进行格式化“#，###，##”，结果以字符串形式返回。 truncate(n1,n2) 截断函数，将n1保留n2位小数，不四舍五入 pow(n1,n2)或power(n1,n2) 返回n1的n2次方 exp(n) 指数函数 log(n) 对数函数 log10(n) 对数函数，以10为底数 PI() 圆周率函数 radians(n) 将角度转换为弧度 degress(n) 将弧度在转换为角度 sin(n) 正弦函数 asin(n) 反正弦函数 cos(n) 余弦函数 acos(n) 反余弦函数 tan(n) 正切函数 atan(n) 反正切函数 cot(N) 正切函数 greatest(n1,n2,n3…) 返回列表中最大值 least(n1,n2,n3…) 返回列表中最小值 字符串函数
函数名 功能 示例 upper或ucase 将字符串中小写字母转换为大写 lower或lcase 将字符串中大写字母转换为小写 left(字符串,数值) 将字符串从左往右取n个字符 right(c,n) 将字符串从右往左取n个字符 substring(字符串，数值1，数值2)或mid(c,n1,n2) 将字符串从n1位置开始截取n2个长度的字符 ltrim© 去除字符串左边开头的空格 rtrim© 去除字符串右边结尾的空格 trim© 将字符串中头尾的空格删除掉 trim(c1 from c) 删除字符串c开始和结尾处的字符串c1 reverse© 将字符串倒序返回 concat(c1,c2…) 将括号中的字符串合并为一个字符串 concat_ws(x,c1,c2…) 使用x作为连接符连接各个字符串 char_length© 返回字符串的字符数。一个汉字为一个字符。 length© 返回字符串的字节数，gbk中一个汉字占用2个字节，utf-8中一个汉字占用3个字节。 position(c in c1)或locate(c,c1) 返回字符串c在c1中的第一次出现位置。 instr(c,c1) 返回字符串c1在c中第一次出现的位置 field(c,c1,c2…) 返回字符串c字符串列表中的位置。 find_in_set(c，c1) 返回c在含有英文逗号分隔的字符串c1中出现的位置。 elt(n,c1,c2…) 返回多个字符串中的第n个。 replace(c,c1,c2) 将c字符串中的c1字符替换为c2 insert(c1,n1,n2,c2) 使用字符串c2替换c1中第n1个位置开始的长度为n2的字符串，c1中第一个字符位置为1 Lpad(c1,n,c2) 使用字符串c2填充c1的开始处，使字符串长度达到n rpad(c1,n,c2) 使用字符串c2填充在c1的结尾处，使字符串长度达到n repeat(c,n) 将字符串重复n次 space(n) 返回n个空格 ord©或ascii© 返回字符串中第一个租房有的ASCII码 char(n) 返回ASCII码对用的字符 strcmp(c1,c2) 比较两个字符串，如果c1大于c2,返回1；如果c1等于c2返回0；c1小于c2返回-1； 日期和时间函数
函数 功能 示例 curdate()或current_date() 返回当前日期 curtime或current_time 返回当前的时间 now()或current_timestamp或sysdate() 返回当前的日期和时间 localtime()或localtimestamp() 返回当前的日期和时间 date(d) 返回表达式中的日期值 time(t) 返回表达式中的时间值 year(d) 返回表达式中的年份 month(d) 返回表达式中的月份 day(d) 返回表达式中的日期值 monthname(d) 返回表达式中月份的名称 dayname(d) 返回表达式中对应的星期名称 dayofyear(d) 返回日期是本年的第几天 dayofmonth(d) 返回日期是本月的第几天 dayofweek(d) 返回日期是本周的第几天 week(d) 返回表达式中的日期是本年的第几周 weekofyear(d) 同上（周一为第一天） quarter(d) 返回表达式日期是本年的第几个季度 hour(t) 返回小时数 minute(t) 返回分钟时 second(t) 返回秒数 adddate(d,n) 返回起始日期加上n天后的日期 subdate(d,n) 返回起始日期减去n天后的日期 addtime(t,n) 返起始日期时间t加上n秒后的日期 subtime(t,n) 返回起始日期时间减去n秒后的日期 datediff(d1,d2) 计算算两个日期相差的天数 timediff(t1,t2) 计算两个日期时间之间相差的秒数 系统信息函数
函数 功能 演示 version 查看mysql版本号 datebase或schema 返回当前数据库名 user或
sys_user
session_user
current_user返回当前登录的用户名 charset© 返回字符串c的字符集 collation© 返回字符串c的排列方式 connetion_id 返回当前客户连接服务器的次数 last_insert_id 返回最近生成的auto_increment值 其他函数
函数名 功能 示例 if(expr,x1,x2) 条件判断函数，如果expr条件成立，返回x1的值，否则返回x2的值。 ifnull(x1,x2) 条件判断函数，如果x1不为空，返回x1的值，否则返回x2的值。 数据库管理
创建数据库
create { database|schema} [if not exists] 数据库名 [[default] character set [=] <字符集名>] [[default] collate [=] <排序规则名>] -- 1.database和schema都是创建数据库的关键字，功能相同 -- 2.if not exists:数据库不存在时自动创建，否则忽略本次操作。 -- 3.default character set:指定数据库字符集 -- 4.defalut collate:指定数据库校对规则 数据库命名规则 1.可以是字母、数字、下划线和$组成的任意字符串，但是不能以纯数字命名。 2.同一连接下不能出现同名数据库 3.不能使用mysql的关键字作为数据库名练习：创建财务管理数据库db_financial,并指定字符集为“gbk”,校对规则为“gbk_chinese_ci”,确保如果数据库不存在就创建它，如果存在就忽略此条命令。
create database if not exists db_financial character set gbk collate gbk_chinese_ci删除数据库
drop { databases|schemas} [if exists] 数据库名称练习：删除db_shop数据库。
drop database if exists db_shop显示数据库列表
show { databases|schemas}查看创建数据库语句
show create database <数据库名>使用数据库
use 数据库名称修改数据库
alter { database|schema} [{数据库名}] [[default] character set [=] <字符集名>] [default] collate [=] <排序规则名>练习：将数据库db_financial的字符集改为utf8mb4,排序规则改为utf8mb4_general_ci。
alter database db_financial character set utf8mb4 collate utf8mb4_general_ci查看数据库下包含哪些表
show tables数据表管理
查看表结构
desc 表名 show [full] columns from 表名显示建表语句
show create table 表名 CREATE TABLE `person` ( `id` int(11) NOT NULL AUTO_INCREMENT, `pname` varchar(20) DEFAULT NULL, `brithday` datetime DEFAULT NULL, `idcode` varchar(20) DEFAULT NULL, `age` int(11) DEFAULT NULL, `mid` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 |创建表
create table [if not exists] 表名称( 字段名 数据类型(长度) 约束条件, 字段名2 数据类型(长度)约束条件)enging=存储类型 default charset=字符集类型 collate=检验规则 定义字段的语法格式： 字段名 数据类型 [not null|null] [default 默认值] [auto-increment] [unique key|primary key] [comment 注释内容] [reference 外键名称] [index 索引名] -- 练习 -- 创建一个学生表Student -- 包含学生id int、学生姓名 varchar(50)、性别 char(1)、专业 varchar(30)、任课老师 varchar(30)、地址 varchar(50)、照片 blob -- 将学生id设置为主键，不能为空，自动递增 -- 将所有字段添加注释 -- 将专业默认设置为“计算机” create table Student( 学生id int not null auto-increment primary key comment "学生编号", 学生姓名 varchar(50) comment "学生姓名", 性别 char(1) comment "性别", 专业 varchar(30) default "计算机" comment "专业", 任课老师 varchar(30) comment "任课老师", 地址 varchar(50) comment "地址", 照片 blob comment "照片" )修改表结构
语法：
alter table 表名 add 字段名 数据类型 [属性] [索引] [first | after 字段名] -- first将新增字段插入在首列，after将新增字段插入到指定字段后边 modify|alter 字段名 数据类型 [属性] [索引] -- 修改指定字段的数据类型等信息 change 字段名 新字段名 数据类型 [属性] [索引] -- change可以修改字段数据类型，也可以把指定的字段修改为一个新的名字 drop 字段名 -- 删除指定字段 auto-increment = n -- 设置字段初始值的 rename to|as 新表名 -- 修改表名 enging=存储引擎|charset=字符集|collate=校验规则练习：在Student学生表中，Stusex字段后面添加一个新的出生日期字段(Birthday)。
alter table Student add Birthday date after Stusex练习2：将学生表中姓名Stuname的长度修改为varchar(20);
alter table student modify Stuname varchar(20)练习3：将学生表名修改为studentbak；
alter table student rename to studentbak;重命名数据表
语法：rename table 旧表 名 to 新表名
练习：将student表名修改为studentTemp
rename table student to studentTemp复制数据表
语法：create table 新表名 like 旧表名
功能：把旧表的表结构、索引、默认值都复制到新表中。
练习：将学生表student复制，生成一张新表，名为studentTemp
create table studentTemp like student删除表结构
语法：drop table [if not exists] 表名
练习：删除student表
drop table if not exists student
**MyISAM:**节约空间，以及相应的速度。
InnoDB：安全性，事务处理及多用户操作数据表
数据表存放位置及形式
数据表以文件形式存放在磁盘中，目录为mysql安装目录下data文件夹中
innodb:只包含一个.frm文件
myisam:包含.frm，.myd和.myi文件——索引文件数据库完整性
实体完整性
实体(Entity)：对应的数据库表中一条完整的记录。
元组(Tuple)：表中的一行记录。
字段(Field):表中的一列，属性。
实体完整性：表中的每条记录保持唯一，通过主关键字的唯一性确保记录的唯一性，从而达到实体完整性。
实体完整性规则：
一个基本关系(表)对应一个实体集(多行记录)。
实体之间通过唯一标识进行区分，例如学生编号、员工编号、订单编号。
主关键字作为唯一标识，不能为空。数据库中实体完整性的实现
约束：作用于表中字段上的规则，用于限制存储在表中的数据，目的是保证数据库中数据的正确性、有效性和完整性。
约束类型：
主键约束(Primary Key)
每张表中只能有一个列被设置为主键，主键字段不能重复，不能为空。
-- 创建表时指定主键 create table XXX( id int primary key, ... ) create table XXX( 字段1 ....., primary key(字段名)) -- 已存在的表中指定主键 alter table 表名 add primary key(主键字段) -- 删除主键约束 alter table 表名 drop primary key联合主键：由多个字段组合而成的主键。
alter table 表名 add primary key(字段1,字段2)唯一约束(Unique Key)
不允许出现重复的字段，允许为空，但是一张表中只能有一个空值，一张表中可以创建多个唯一约束。
-- 1.创建表时增加唯一约束 create table 表名( 字段1 数据类型, 字段2 数据类型 Unique key, ... ) -- 2.方法2 create table 表名( 字段1 数据类型, 字段2 数据类型, ... unique key(字段名) ) -- 3.对已存在的表中设置唯一索引 alter table 表名 add 约束名 unique key(字段) -- 4.删除表中的唯一索引 alter table 表名 drop index 字段参照完整性
参照完整性规则
参照关系中的属性值必须能够在被参照关系中找到或者取空值。
引用关系
同一关系
两个关系参照完整性的实现
外键约束（foreign key）
-- 创建表时添加外键 create table 表名( 字段名 数据类型， foreign key 字段名 references 主表 字段名 ) -- 实例 create table studet5( stuid int primary key, name varchar(50), sex char(1), address varchar(50) ) create table student6( id int, sc varchar(10), class varchar(10), foreign key(id) references studet5(stuid) )用户自定义完整性
用户根据实际的完整性要求来定义的，在执行数据从操作时，数据库管理系统会检查数据是否满足限定条件。常使用的用户自定义完整约束包含非空约束、自增约束和默认值约束。
非空约束(not null):属于域完整性约束，字段的值不能为空，但是允许重复。
-- 创建表时，添加非空约束 create teble 表名( 字段名 数据类型 not null ) -- 给创建好的数据表增加非空约束 alter table 表名 change 旧字段名 新字段名 数据类型 not null -- 如何删除已经创建好的非空约束 alter table 表名 change 旧字段名 新字段名 数据类型默认值约束
默认值约束(default):给某个字段指定默认值，一旦指定默认值，再插入数据是，如果该字段没有赋值，则使用默认值。
-- 建表时添加默认值约束 create table 表名( 字段名 数据类型 defalut 默认值 ) -- 建表后添加默认值 alter table 表名 modify 字段名 数据类型 default 默认值 -- 删除默认值约束 alter table 表名 modify 字段名 数据类型完整性约束命名子句
主键约束：primary key
外键约束：foreign key
唯一约束：unique key
非空约束：not null
默认值约束：defalut
完整性约束命名子句语法
constraint 约束名称 约束条件 -- 练习 alter table stu add constraint id primary key(stuid) alter table stu add constraint name unique key(stuname) alter table stu modify column stuname varchar(20) not null alter table stu modify column sc int default 95mysql数据查询与更新
单表查询
查询语法： select 查询项 from 表名 [where 查询条件] [group by 分组字段] [having 筛选条件] [order by 排序字段] [limit 分页规则] [union 运算符] -- union子句用来将多个select语句查询结果合并为一个结果集简单查询
语法：
select [all|distinct] from 表名|视图 [limit n1,n2] all:表示结果集的所有行，包含重复行，all是默认选项 distinct:消除重复行，空值被认为相等。 limit n1,n2表示从n1行开始，返回n2条记录。初始行从0开始 示例：去除重复行记录 select distinct * from student 示例：-- 第一次查询显示1~3条记录 select * from student limit 0,3 -- 第二次查询显示4~6条记录 select * from student limit 3,3 查询结果的别名设置 select 字段名 as 别名... select 字段名 别名... -- 示例： select name as 姓名,address 家庭住址 from student条件查询
where 查询条件
查询条件 谓词 比较运算符 > 大于 >= 大于等于 < 小于 <= 小于等于 <>或!= 不等于 = 等于 <=> 相等或都等于空 逻辑运算符 not 非 and 与 or 或 xor 异或：运算符左右两边的结果不同则结果为真 字符匹配 like %：表示任意个任意字符 _:表示一个任意字符 区间查询 between…and… 小值在前，大值在后 包含 in() 包含 not in() 不包含 空值判断 is null 为空 is not null 不为空 示例： -- 查询年龄大于16岁的学生信息 select * from student where age>16 -- 查询年龄大于等于16岁的学生信息 select * from student where age>=16 -- 查询年龄小于16岁的学生信息 select * from student where age<16 -- 查询年龄小于等于16岁的学生信息 select * from student where age<=16 -- 查询年龄不等于16岁的学生信息 select * from student where age<>16 select * from student where age!=16 -- 查询年龄等于16岁的学生信息 select * from student where age=16 -- 查询年龄等于16岁的学生信息(使用逻辑非运算符) select * from student where not age<>16 -- 查询年龄在16岁以下，性别为女的同学信息 select * from student where age<16 and sex="女" -- 查询年龄在16岁以下，或者性别为男的同学信息 select * from student where age<16 or sex="男" -- 查询年龄在16岁以下的不是男生的信息或者16以上的男生信息 select * from student where age<16 xor sex="男" -- 查询姓张的同学信息 select * from student where name like "张%" -- 查询15~18之间的学生信息 select * from student where age between 15 and 18 select * from student where age>=15 and age<=18 -- 查询年龄是15，18，13的同学信息 select * from student where age in(15,18,13) -- 查询家庭住址为空的学生信息 select * from student where address is null排序查询
单字段排序 语法：order by 排序字段 [排序规则] 排序规则：desc降序 asc升序(默认值) 示例：将学生信息按照年龄从大到小的顺序显示 select * from student order by birthday asc 多字段排序 语法：order by 主排序字段 排序规则,次排序字段 排序规则 示例：-- 根据分数由高到低进行排序，如果分数相同，根据年龄由低到高显示 select * from student order by score desc,birthday desc分组查询
语法：group by 分组字段 [having 筛选条件] 注意：分组查询根据分组字段的不同值划分成不同的组别，然后在进行聚合运算，查询结果中需要加入分组字段 -- 统计男女同学的人数 select sex, count(*) from student group by sex 聚合函数： max():最大值 min():最小值 count():计数 sum():求和 avg():平均值 -- 统计男女同学的人数。只显示2人以上的记录 select sex, count(*) from student group by sex having count(*)>=2 -- 查询2010年以后出生的学生，根据性别分组，显示不同组别总分在150分以上的记录 select sex 性别,sum(socre) 总分 from student where birthday>="2010/1/1" group by sex having sum(socre)>=150 -- 统计班级中男女同学的人数 select sex as 性别,count(*) from student group by sex 分组后数据的筛选 having:分组之后要显示符合要求的数据，使用having做二次过滤。 having 与 where的区别 1.where:一次过滤，对表中所有的数据进行初步过滤，被过滤掉的数据无法参加排序、分页、分组等操作。 2.having:数据分组并且统计之后进行二次过滤，对不符合结果要求的聚合函数结果进行过滤筛选。 -- 按照不同的课程分组，分别算出其平均分、最高分和最低分，对于低于60分平均分的不予显示 --低配版 select subjectNo,avg(studentresult),max(studentresult),min(studentresult) from result group by subjectNo having avg(studentresult)>=60
左外连接
-- 查询参加考试的同学信息及分数信息 select * from student left join score on student.studentid=score.studentid
右外联接
-- 查询所有的考试信息及对应的学生信息 select * from student right join score on student.studentid=score.studentid
交叉连接|全连接（笛卡尔效应）
-- 不指定两表的关联关系，查询结果为两表记录数的乘积，cross可以省略 -- 无任何实际意义 select * from student cross join score
嵌套查询|子查询
说明：将内部查询的结果作为外部查询的条件使用
分类：
比较运算符子查询：当能确切知道子查询返回的是一个固定值时，可以使用比较运算符进行比较。
in 子查询：in 子查询的结果是一个集合，而不是一个单一的值。
exists子查询：用来判断子查询的结果是否为空
any或all子查询：子查询返回单一值时，可以用比较运算符子查询，返回多个值时，可以使用any或all查询。
-- 查询张三的所有科目的成绩信息 -- 1.查询张三的学号 select studentid from student where name="张三" -- 2.查询成绩信息 select * from score where studentid=1 -- 子查询 select * from score where studentid in (select studentid from student where name="张三")DML语句—数据操作语言
用于操作数据库对象所包含的数据
insert into(插入数据)
语法:insert into 表名(字段名1，字段名2…)values(值1，值2)
如果插入的是全字段，values前的字段名可省略，添加值需按照表结构顺序添加
插入多条语句时，values后边使用英文逗号分隔开
-- 全字段插入 insert into student(studentid,name,sex,address,birthday) values(9,"张无忌",'男',"光明顶","1967-12-21 12:12:12"); -- 全字段乱序插入 insert into student(birthday,sex,studentid,name,address) values("1900-1-1 10:10:10",'女',10,"周芷若","峨眉山") -- 插入全字段数据，可以省略字段名 insert into student values(12,"张三丰","男","武当山","1900-1-1"); -- 插入部分字段（字段名必须要指定） insert into student(studentid,name,sex,address) values(11,"谢逊",'男',"恶人岛") -- 一次插入多条记录 insert into student values (13,"赵敏",'女','蒙古',"1954-12-1"), (14,"金花婆婆",'女',"恶人岛","1900-1-14"), (15,"灭绝师太",'女',"峨眉山","1900-1-2")insert…set
语法：insert into 表名 set 字段名1=值1,字段名2=值2…
insert into student set studentid=16,name="宋青山",sex='男',address="武当山",birthday="1923-12-1"insert…select
功能：从一个表中复制信息带另一个表中。
语法：insert into 目标表名(字段名) select 字段名 from 源数据表 where 条件
-- 根据student表结构创建studenttemp表 create table studenttemp like student -- 将student表中所有的女性记录的学号，姓名，性别插入到studenttemp新表中 insert into studenttemp(studentid,name,sex) select studentid,name,sex from student where sex="女"练习
--创建年级表 create table grade( GradeID int(11) not null, GradeName varchar(50) not null )
--年级表插入数据 insert into grade(GradeName) values ("大一"),("大二"),("大三"),("大四")
--新建学生信息表 create table student( StudentNo int(11) not null, StudentName varchar(50) not null, Sex char(2), GradeID int(11), Phone varchar(11), Address varchar(255), Email varchar(50), IdCard varchar(18) )
--向学生表中插入数据 insert into student (StudentNo,StudentName,Sex,GradeId,Phone,Address,Email,IdCard) values (1011,"郭靖",'男',1,"13500000001","北京海淀区中关村大街1号","guojing@bdqn.cn","4502319861111000") --不指定字段，同时插入两条数据 insert into student VALUES (1012,"李文才",'男',2,"13500000002","河南洛阳","liwencai@bdqn.com","450323198112311000"), (1013,"李梅",'女',3,"13500000015","上海卢湾区","limei@bdqn.com","450323198612311000")
--新建科目表 create table subject( SubjectNo int(11) not null, SubjectName varchar(50), ClassHour int(10), GradeID int(11) )
--向科目表中插入数据 insert into subject values (1,"高等数学-1",120,1), (2,"高等数学-2",110,2), (3,"高等数学-3",100,3), (4,"高等数学-4",130,4)update(修改数据)
语法：update 表名 set 字段名 = 值 where 修改条件 示例：--将李梅班级编号修改为3 update student set GradeID = 3 where StudentName = "李梅"修改条件
排序
语法： select 查询项 from 表名 where 查询条件 order by 排序字段 [排序规则] 排序规则：asc(升序，默认) desc(降序) --查询学生信息表中男生信息，按照学生编号是降序排列 select * from student where sex='男' order by studentno desc 排序字段：根据此字段进行排序,汉字按照拼音首字母排序，英文按照字母顺序排序，日期按照时间先后进行排序（日期越往前越小） --查询学生表中的学生信息，按照家庭住址降序排列 select * from student order by address descs --排序字段指定多个，当主排序字段值相同时，根据次排序字段的排序规则进行数据显示 --语法： select 查询项 from 表名 where 查询条件 order by 主排序字段 [排序规则],次排序字段[排序规则] --查询课程表中的课程信息，按照课时量由大到小进行排序，如果课时量相同则按照课程编号降序排列 select * from subject order by classhour desc,subjectno desc分页查询：指定查询的记录从哪条至哪条
查询结果的数据量庞大时，我们需要将查询结果分批次显示
语法1： limit 起始位置,查询的条数 起始位置：数据查询从起始位置之后开始，起始位置可省略，默认从0开始 语法2： limit 查询条数 offset 起始位置 例题1：查询学生表中前5条数据 select * from student limit 0,5 select * from student limit 5 --省略起始位置，默认第一条开始 select * from student limit 5 offset 0 --语法2 例题2：--查询学生表中第6~15条数据 select * from student limit 5,10 select * from student limit 10 offset 5 --语法2 例题3：查询result表的考试成绩，并按照由高到低显示，同时把该成绩对应的学生的学号打印出来 a.第一次查询前5条记录 select studentno from result order by studentresult desc limit 5 b.第二次查询6-10条记录 select studentno from result order by studentresult desc limit 5,5 例题4：查询JAVA表课程成绩前10名且分数大于80的学生信息（学号，姓名，课程名，分数） select 学号,姓名,课程名称,分数 from JAVA where 分数>80 order by 分数 desc limit 10子查询
核心：内层查询的结果作为外层查询的条件
--查询课程为《高等数学-2》且分数不小于80分的学生的学号和姓名 --步骤分解 --1.查询高等数学-2的课程编号---------2 select subjectNo from subject where subjectname="高等数学-2" --2.查询高等数学二分数大于80分学生的编号------1013 select studentno from result where studentresult >80 and subjectNo=2 --3.查询学生信息 select * from student where studentNo=1013 --子查询--内层查询的结果作为外层查询的条件 select * from student where studentNo in (select studentno from result where studentresult >80 and subjectNo in (select subjectNo from subject where subjectname="高等数学-2")) --查询学号为1026的学生信息 select * from student where studentno = 1026 -- 查询成绩为99分的学生信息 -- 1.拿到学生编号 select studentno from result where studentresult=99 -- 2.查询学生信息 select * from student where studentno = 1017 -- 子查询 select * from student where studentno in (select studentno from result where studentresult=99) -- 查询2024年4月16号考试的内容及其课时量 -- 1.2024/4/16考试课程的编号 select subjectno from result where examdate = "2024-04-16 08:23:52" -- 2.根据课程编号获取课程内容及课时量 select subjectname,classhour from subject where subjectno = 1 -- 子查询 select subjectname,classhour from subject where subjectno in (select subjectno from result where examdate = "2024-04-16 08:23:52") --查询《高等数学-1》的前5名学生成绩信息 1.根据课程名称获取课程编号 select subjectno from subject where subjectname="高等数学-1" 2.查询该课程考试成绩 -- 排序，取前五条 select * from result where subjectno="1" order by studentresult desc limit 5 --子查询 select * from result where subjectno in (select subjectno from subject where subjectname="高等数学-1") order by studentresult desc limit 5数据库函数
统计函数/聚合函数
--查询学生信息表中有多少条数据 --count() 计数函数 select count(studentname) from student select count(*) from student --sum 求和 --求高等数学-1的总成绩 select sum(studentresult) from result where subjectno = "1" --avg 求平均值 --求高等数学-1的平均分 select avg(studentresult) from result where subjectno = "1" --max 求最大值 --求高等数学-1的最高分 select max(studentresult) from result where subjectno = "1" --min 求最小值 --求高等数学-1的最低分 select min(studentresult) from result where subjectno = "1"❤❤聚合函数位置在select后边
❤❤使用聚合函数前，查询结果应该为1条以上的数据，否则无意义
❤❤聚合函数括号中需要加入字段名，通常为数值型字段
分组查询
group by 分组字段 --根据分组字段的不同值，将数据分成不同的小组，后续进行聚合运算--高配版 select subjectname as 课程名称,avg(studentresult) as 平均分,max(studentresult) as 最高分,min(studentresult) as 最低分 from result,subject where result.subjectNo=subject.subjectNo group by result.subjectNo having avg(studentresult) >=60查询语句汇总
select [distinct] 查询项[as 别名] from 表名 [inner|left|right join] 关联表 on 表1.字段=表2.字段 where 筛选条件 group by 分组字段 having 分组后筛选条件 order by 排序字段 limit 分页 -- #查询课程为《JAVA第一学年》成绩前10名分数大于80的学生信息（学号，姓名，课程名，分数）事物
概念：事物就是将一组sql语句放在同一批次去执行，如果其中一条sql语句出错，所有的数据都会复原。
mysql事务处理支支持innodb类型的数据库表
-- 创建数据库shop和创建表account并插入2条数据 -- A账户成功减少500元，B账户应该增加500元，但一些错误导致未增加成功，这时则需返回A账户的500元，达到账户总额的平衡 -- 建库 create database shop -- 建表 create table account( id int(11), name varchar(32), cash decimal(9,2) ) insert into account values(1,"A",2000),(2,"B",10000) -- 1.关闭自动提交 set autocommit = 0 -- 2.开启一个事务 start TRANSACTION -- 3.sql语句 update account set cash=cash-500 where name="A" update account set cash=cash+500 where name="B" --4.提交事务 commit --5. 打开自动提交 set autocommit = 1数据库索引
作用：
提高查询速度
确保数据的唯一性
可以加速表和表之间的连接，实现表与表之间的参照完整性
使用分组和排序子句进行数据检索时，可以显著减少分组和排序的时间
全文检索字段进行搜索优化分类：
主键索引（PRIMARY KEY）
唯一索引（UNIQUE）
常规索引（INDEX）
全文索引（FULLTEXT）主键索引(一张数据表中只能存在一个主键)
创建数据库时指定某个字段为主键，目的为了保证数据的唯一性
主键索引可以通过视图方式和代码方式进行设置，当字段设置为主键时，该字段不能为空不能重复。
设置主键索引的方式
代码方式：
CREATE TABLE `Grade` ( `GradeID` INT(11) AUTO_INCREMENT PRIMARY KEY, #省略代码…… #主键索引也可在字段字义之后，如 # PRIMARY KEY(`GradeID`) ) -- 直接在字段后指定主键索引或者在建表语句最后声明主键字段视图方式：
关系模式
u用二维表的形式表示实体和实体间联系的数据模型****即关系模式
数据库设三范式
第一范式的目标是确保每列的原子性
第二范式要求每个表只描述一件事情
-- 邮箱Email，添加常规索引 -- 常规索引 alter table student add index index1(Email) -- 姓名StudentName，添加全文索引 (MyISAM类型数据表) -- 添加全文索引(修改表类型) alter table student add fulltext full1(StudentName) ```
数据库备份
数据备份的必要性
保证重要数据不丢失
数据转移备份方法
mysqldump备份工具
mysqldump -h 主机名 –u 用户名 –p 数据库名> 路径/文件名.sql 示例：mysqldump -uroot -p bdqn>d:/dbqn.sql 输入密码数据库管理工具，如navicat
直接拷贝数据库文件和相关配置文件
数据恢复
source
1.选择并打开要导入的数据库 source sql文件存放地址+sql文件名客户端
mysql -uroot -p 数据库名称<数据库文件路径+文件名称sql语句导出导入数据
导出
SELECT * INTO OUTFILE 'file_name' FROM tbl_name --输出的文件不能先存在，否则报错导入
LOAD DATA INFILE 'file_name ' INTO TABLE tbl_name[FIELDS]数据库设计
良好的数据库设计
节省数据的存储空间
能够保证数据的完整性
方便进行数据库应用系统的开发
糟糕的数据库设计
n数据冗余、存储空间浪费
n内存空间浪费
n数据更新和插入的异常
软件设计步骤
需求分析
概要设计
详细设计
代码编写
软件测试
安装部署数据库设计步骤
收集信息
设计酒店管理系统，包含客户信息管理和客房管理及入住管理功能
标识实体
包含客人和客房两个实体
标识实体属性
客人：姓名、性别、联系方式、入住日期、离店日期。。。 客房：房间号，床位，价格，入住状态。。
建立实体间关系
客人——-入住——–客房
ER图
![image-20240910155258776]()
映射基数
一对一：人——身份证
一对多：人——手机号
多对一：学生——宿舍
多对多：学生——老师酒店管理系统ER图[外链图片转存中…(img-QkrS4xdL-1745566790000)]
关系模式
u用二维表的形式表示实体和实体间联系的数据模型****即关系模式
数据库设三范式
第一范式的目标是确保每列的原子性
[外链图片转存中…(img-XLc33Z5N-1745566790000)]
第二范式要求每个表只描述一件事情
![image-20240910161438304]()
如果一个关系满足2NF，并且除了主键以外的其他列都不传递依赖于主键列，则满足第三范式（3NF）
![image-20240910161451918]()
[外链图片转存中…(img-89yrTbvn-1745566790001)]
暂无评论内容