MySQL
一.数据库的基本概念
1,数据库的基本概念
数据库(Database)是“按照数据结构来组织、存储和管理数据的仓库”。是⼀个⻓期存储在计算机内的、有组织的、可共享的、统⼀管理的⼤量数据的集合。其本质是⼀个⽂件系统,还是以⽂件的⽅式,将数据保存在电脑上。
2.什么是数据库管理系统
数据库管理系统(Database Management System)是⼀种操纵和管理数据库的⼤型软件,⽤于建⽴、使⽤和维护数据库,简称 DBMS。它对数据库进⾏统⼀的管理和控制,以保证数据库的安全性和完整性。
3.数据库的优点
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fwUTNPy8-1683969818362)(C:\Users\fjc23\AppData\Roaming\Typora\typora-user-images\image-20230512210528327.png)]
二.SQL
1.SQL的概念
结构化查询语⾔(Structured Query Language)简称SQL,是⼀种特殊的编程语⾔,是⼀种数据库查询和程序设计语⾔,⽤于存取数据以及查询、更新和管理关系数据库系统。
2.SQL的作用
1.是所有关系型数据库的统⼀查询规范,不同的关系型数据库都⽀持SQL。
2.所有的关系型数据库都可以使⽤SQL。
3.不同数据库之间的SQL有⼀些区别(⽅⾔)。
3.SQL 通用语法
1.SQL语句可以单⾏或者多⾏书写,以";"分号结尾。
2.可以使⽤空格和缩进来增加语句的可读性。
3.MySQL中使⽤SQL不区分⼤⼩写,⼀般关键字⼤写,数据库名、表名、列名⼩写。
4.注释方式
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8FJY2ETl-1683969818363)(C:\Users\fjc23\AppData\Roaming\Typora\typora-user-images\image-20230512211550227.png)]
#show databases; 单⾏注释
-- show databases; 单⾏注释
/*
多⾏注释
show databases;
*/
4.SQL的分类
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9PKlhYRK-1683969818364)(C:\Users\fjc23\AppData\Roaming\Typora\typora-user-images\image-20230512211654785.png)]
数据库管理系统、数据库和表的关系如图所示:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uO1mxj2L-1683969818364)(C:\Users\fjc23\AppData\Roaming\Typora\typora-user-images\image-20230512211715005.png)]
5.DDL 操作数据库
1 创建数据库
创建数据库的方式:
命令 | 说明 |
---|---|
CREATE DATABASE 数据库名 | 创建指定名称的数据库 |
CREATE DATABASE IF NOT EXISTSN 数据库名; | 判断数据库是否存在,不存在则创建数据库 |
CREATE DATABASE 数据库名 CHARCTER SET 字符集; | 创建指定名称的数据库,并且指定字符集(一般指定utf8) |
实例代码
--创建指定名称的数据库
create database mydb;
--判断数据库是否存在,不存在则创建数据库
create database if not exist mydb;
--创建指定名称的数据库,并且指定字符集(一般指定utf8)
create database mydb character set utf8;
2.查看数据库
命令 | 说明 |
---|---|
SHOW DATABASE; | 查看MySQL中都有哪些数据库 |
SHOW CREATE DATABASE 数据库名; | 查看一个数据库的定义信息 |
实例代码:
-- 查看MySQL中都有哪些数据库
show database
-- 查看MySQL中都有哪些数据库
show create database mydb;
3.修改数据库
命令 | 说明 |
---|---|
ALTER DATABASE 数据库名 CHARACTER SET 字符集 | 数据库字符集操作 |
实例代码
-- 数据库字符集操作
alter database mydb character set utf8;
-- 查看数据库信息是否修改成功
show create database mydb;
4.删除数据库
命令 | 说明 |
---|---|
drop database 数据库名 | 从mysql中永久的删除某个数据库 |
实例代码:
drop database mydb
5.使用数据库
命令 | 说明 |
---|---|
USE 数据库; | 使用切换数据库 |
select database(); | 查看当前正在使用的数据库,使用mysql中的全局函数 |
实例代码:
-- 切换数据库
use mydb
-- 查看当前使用的数据库
select database();
6.DDL操作数据库
1.mysql数据类型
常见数据类型:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oDXdRJy8-1683969818364)(C:\Users\fjc23\AppData\Roaming\Typora\typora-user-images\image-20230513113553336.png)]
详细数据类型:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kiv3aiHj-1683969818365)(C:\Users\fjc23\AppData\Roaming\Typora\typora-user-images\image-20230513113622938.png)]
char与varchar 区别:
char类型是固定长度,根据定义的字符串长度分配足够的空间,适合存储固定长度的字符串,比如密码,性别
varchar类型是可变长度,只使用字符串长度所需的空间,适合存储在一定范围内长度变化的字符串
1.创建表
语法格式:
create table 表名(
字段名1 数据类型(长度),
字段名2 数据类型(长度),
.....
);
实例代码:
-- 创建学生表,包括学号,姓名,性别等
create table student (
id int primary key auto_increment,
name varchar(255),
sex varchar
);
-- 注意: primary key 代表主键
-- auto_increment 代表自动增加
2.快速创建一个表结构相同的表**
语法格式:
create table 新表名 like 旧表名
代码实例:
-- 需求创建一个和student表结构相同的stu表
create table stu like student
-- 查看stu的表结构
desc stu
3.comment 注释
语法格式:
create table 表名(
字段名 字段类型 comment '字段的注释'
) comment = '表注释';
4.查看表
1.查询命令
命令 | 说明 |
---|---|
SHOW TABLES | 查看当前数据库中的所有表名 |
DESC 表名 | 查看数据表的结构 |
SHOW CREATE TABLE 表名 | 查看创建表的SQL语句 |
实例代码:
-- 查看当前数据库中的所有表名
show tables
-- 查看数据库的表结构
DESC 表名
-- 查看创建表的SQL语句
show create table 表名
2.MySQL存储引擎
1.MyISAM
优势:速度快,磁盘空间占用少;某个库或表的磁盘占用状况即能够经过操作系统查看相应的文件的大小.也能经过SQL语句 SHOW TABLE TATUS 查的
缺点:没有数据完整性机制,既不支持事务和外键
2.InnoDB
优势:支持事务和外键,数据完整性机制比较完备;能够用SHOW TABLE STATUS 查看某个库或表的磁盘占用
缺点:速度超慢,磁盘空间占用多,全部库都存于一个或者多个文件中,没发经过操作系统了解某个库或表的占用空间
3.删除表
命令 | 说明 |
---|---|
DROP TABLE 表名; | 删除表(从数据库中永久删除某一张表) |
DROP TABLE EXISTS 表名 | 判断表是否存在,存在删除,不存在不执行SQL语句 |
实例代码:
drop table nydb;
drop table exists mydb;
4.修改表
1.修改表名
rename table 旧表名 to 新表名
2.修改表的存储引擎和字符集
alter table 表名 enging = 引擎类型 charset = 字符集;
3.向表中添加列
alter table 表名 add 字段名 字段类型
实例代码:
-- 需求:向表中添加sex char
alter table mydb add sex char; # 添加在到最后面
alter table mydb add sex char first; #添加到最前面
alter table mydb add sex char name; #添加到name字段的后面
4.修改表中列的数据类型或长度或者字段位置
-- 默认字段追加在最后面
alter table 表名 modify 字段名称 新字段类型
-- 将字段添加到第一个位置
alter table 表名 modify 字段名称 字段类型 first;
-- 将字段添加到指定字段后面
alter table 表名 字段名称 字段类型 xxx;
5.修改列的名称
alter table 表名 change 旧列名 新列名 新类型;
6.删除列
alter table 表名 drop 列名
7.修改表和列的注释
-- 修改表的注释
alter table 表名 comment '表的注释内容
--修改字段的注释
alter table 表名 modify column 字段名称 字段类型 comment = '字段注释'
7.DML操作表中数据
SQL中DML用于对表中的数据进行增删该操作
1.插入数据
insert into 表名 (字段1,字段2.....) values (值1,值2....);
注意:
- 值与字段必须一一对应,个数相同且数据类型相同
- 数值的数据大小,必须在字段指定的长度范围内
- char varchar date类型的值必须使用单引号,或者双引号
- 如果要插入空值,可以忽略不写,或者插入null
- 如果插入指定字段的值,必须写上列名
2.更改数据
1.不带条件的修改数据
update 表明 set 字段名 = 值
2.带条件的修改数据
update 表名 set 列名 = 值 where 条件表达式
3.删除数据
命令 | 说明 |
---|---|
delete from 表名; | 不推荐,有多少记录删除多少记录的操作,效率低 |
truncate table 表名; | 推荐,先删除整张表,然后再重新创建一张一模一样的表,效率高 |
8.DQL查询表中的数据
1.简单查询
1.查alter table询所有的列
select * from 表名
2.查询指定列
select 字段名1,字段名2.... from student
3.指定列的别名查询
select 字段名 as 别名,字段名2 as 别名...from 表名 as 表别名
4.清除重复值
select distinct 字段名 from 表名
5.查询结果参与运算
某列和固定值运算
select 字段名+固定值 from 表名;
列和列进行运算
select 列1+列2 from 表名
2.条件查询
在实际应用中,通过指定查询条件进行过滤
1.语法
select 列名 from 表名 where 条件表达式;
2.比较运算符
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PFa5ZDE0-1683969818365)(C:\Users\fjc23\AppData\Roaming\Typora\typora-user-images\image-20230513145907355.png)]
4.null 运算符
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sYwGDmWB-1683969818366)(C:\Users\fjc23\AppData\Roaming\Typora\typora-user-images\image-20230513145935508.png)]
5.逻辑运算符
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SPVx4Fll-1683969818366)(C:\Users\fjc23\AppData\Roaming\Typora\typora-user-images\image-20230513145955604.png)]
-- 查询age⼤于35且性别为男的学⽣(两个条件同时满⾜)
select * from student3 where age>35 and sex='男';
-- 查询age⼤于35或性别为男的学⽣(两个条件其中⼀个满⾜)
select * from student3 where age>35 or sex='男';
-- 查询id是1或3或5的学⽣
select * from student3 where id=1 or id=3 or id=5;
-- 查询性别不是'男'的所有学⽣
select * from student3 where not sex='男';
6.in关键字
select 字段名 from 表名 字段名 in (数据1,是数据2...);
in中每个数据都会作为一次条件,只要满足条件就会显示
实例代码:
select * from student id in (1,2,3);
-- 查询满足id=1或id=2或id=3的所有信息
7.范围查询
between 值1 and 值2;
--需求查询student表中 成绩score大于60小于75 的学生信息
select * from student score between 60 and 75;
8.like 关键字
like模糊查询:
select * from 表名 where 字段名 like ="统配字符串";
通配符:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yANOmS3H-1683969818366)(C:\Users\fjc23\AppData\Roaming\Typora\typora-user-images\image-20230513150627774.png)]
# 查询含有'精'字的所有员⼯信息
SELECT * FROM emp WHERE ename LIKE '%精%';
# 查询以'孙'开头的所有员⼯信息
SELECT * FROM emp WHERE ename LIKE '孙%';
# 查询第⼆个字为'兔'的所有员⼯信息
SELECT * FROM emp WHERE ename LIKE '_兔%';
三.单表
1.DQL操作单表
1.数据库间表的复制
.在Navicat Premium窗⼝中,选择【⼯具】选项下的【数据传输】,在新打开的窗⼝中将“源”数据库中的表,通过“连接”复制到“⽬标”数据库中
2.排序
通过order by子句,可以查询出的结果集按照一个列或者多个列进行排序(不影响正式数据)
select 字段名 from 表名 where 字段名 = 值 order by 字段名 asc/desc
asc:升序(默认)
desc:降序.
1.单列排序:
只按照某一个字段进行排序,就是单列排序
-- 需求:使用salary 对emp数据进行排序
-- 默认
select * from emp order by salary;
-- 降序
select * from emp order by salary desc;
2.组合排序
同时对多个字段进行排序,如果第一个字段相同就按照第二个字段排序,以此类推.
-- 在薪水salary的基础上,再使用eid进行排序,如果薪水相同就以eid做降序排序
select * from emp order by salary desc,eid desc
3.聚合函数
语法:
select 聚合函数(字段名) from 表名;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3FqzWt11-1683969818367)(C:\Users\fjc23\AppData\Roaming\Typora\typora-user-images\image-20230513154226811.png)]
1.instr函数:
简介:
有时,想要在字符串中查找子字符串或检查字符串中是否存在子字符串,在这种情况下可以使用字符串内置instr()函数
instr()函数返回字符串中子字符串第一次出现的位置,如果在str中找不到子字符串,则instr()函数返回0;
instr(str,substr);
str:要搜索的字符串
substr:要搜苏的子字符串
instr不区分大小写搜索, 如果要强制区分大小写 在substr前使用 binary
2.instr函数和like运算符
1.假设要查找名称包含Car关键字产品,可以使用instr函数
select name from 表名 where instr(name,'Car')>0;
2.除了instr函数,还可以使用like运算符进行匹配
select name from 表名 where name like '%Car%'
3.group_concat()函数
mysql中group_concat()函数将组中的字符串连接成具有各种选项的单个字符串
group_concat( distinct expression order by expression separator sep);
4.分组
1.group by
分组查询指的是使用gruop by 语句结合聚合函数,根据一个或着多个列对结果进行分组,相同数据作为一组
select 分组字段,聚合函数 from 表名 group by 分组字段1,分组字段2...;
注意:分组时可以查询要分组的字段,或者使用聚合函数进行统计操作,使用*查询其他字段没有意义.
实例代码:
# 1.查询所有部⻔信息
select * from dept;
# 2.查询每个部⻔的平均薪资
select name, avg(salary) from dept group by name;
# 3.查询每个部⻔的平均薪资,部⻔名称不能为null
select name,avg(salary) from dept where name is not null group by name;
2.HAVING
通常having和group by组合使用,在having后面写聚合函数的条件,having子句可以筛选分组后的各组数据.
select 分组字段, 聚合函数 from 表名 group by 分组字段 ,having 条件;
# 1.查询平均薪资⼤于6000的部⻔
-- ⽅式1:需要在分组后再次进⾏过滤,使⽤having
SELECT
dept_name,
AVG(salary)
FROM emp WHERE dept_name IS NOT NULL GROUP BY dept_name HAVING AVG(salary) > 6000;
where和having的区别
通常在WHERE后⾯写普通字段的条件,⽽HAVING后⾯也可以写普通字段的条件,但是不推荐
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WcdhrNxT-1683969818367)(C:\Users\fjc23\AppData\Roaming\Typora\typora-user-images\image-20230513161659381.png)]
5.limit分页
limit关键字的作用:
- limit是限制的作用,用于限制返回的查询结果的行数
- limit是mysql的方言,用来完成分页
select 字段1,字段2,....from 表名 limit offset, length;
参数说明:
limit offset,length:关键字可以接受一个或两个为0或者正整数的参数
offset:起始行数,从0开始计数,如果省略则默认为0
length:返回的行数
各种关键字的顺序:
select 字段1 from 表名 where 条件表达式 gruop by 分组字段 having 条件表达式 order by 字段名 asc/desc limit offset,length;
分页公式:
起始索引 = (当前页-1) * 每页条数
实例代码:
# 1.查询emp表中的前5条数据
-- 参数1:起始值,默认是0;参数2:要查询的条数
SELECT * FROM emp LIMIT 5;
SELECT * FROM emp LIMIT 0, 5;
# 2.查询emp表中从第4条开始,查询6条
-- 起始值默认是从0开始的
SELECT * FROM emp LIMIT 3, 6;
四.约束
约束:创建表时给字段添加限制条件
1.SQL约束介绍
1.约束的作用
对表中的数据进一步限制,从而保证数据的正确性/有效性/完整性,违反约束的不正确数据,无法插入到表中.
2.常见约束
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rmTs1JR3-1683969818367)(C:\Users\fjc23\AppData\Roaming\Typora\typora-user-images\image-20230513162917159.png)]
2.默认值约束
默认值约束用来指定某列的默认值.如在数据库里有一项数据重复,可以设置为默认值
字段名 字段类型 default 默认值.
3.主键约束
主键:是表中的一个或多个字段,它的值用于唯一的标识表中的某一条记录.
主键约束:要求主键列数据唯一,并且不允许为null
字段名 字段类型 primary key;
# ⽅式1:创建⼀个带主键的表
CREATE TABLE emp3(
-- 设置主键,唯⼀⾮空
eid INT PRIMARY KEY,
ename VARCHAR(20),
sex CHAR(1)
);
-- ⽅式2:创建⼀个带主键的表
CREATE TABLE emp3(
eid INT,
ename VARCHAR(20),
sex CHAR(1),
-- 指定主键为eid字段
PRIMARY KEY (eid)
);
-- ⽅式3:创建⼀个带主键的表
CREATE TABLE emp3(
eid INT ,
ename VARCHAR(20),
sex CHAR(1)
)
-- 创建的时候不指定主键,然后通过DDL语句进⾏设置
ALTER TABLE emp3 ADD PRIMARY KEY (eid);
删除主键
-- 使⽤DDL语句删除表中的主键
ALTER TABLE emp3 DROP PRIMARY KEY;
删除主键约束说明:
- 删除主键后唯⼀性约束失效,即字段数据可以重复;
- 删除主键后⾮空性约束保留,即字段数据不能为空
主键自增
当主键定义为⾃增⻓后,那么这个主键的值就不需要⽤户输⼊数据,⽽由数据库系统根据定义⾃动赋值。当增加⼀条记录时,主键⾃动的以相同的步⻓来进⾏增⻓,这个步⻓是在建表的时候⽤户⾃⼰定义的。⼀般⾃增⻓数据只能是数值类型。
字段名 字段类型 primary key auto_increment
修改自增起始值
-- 创建主键⾃增的表 ,⾃定义⾃增其实值
CREATE TABLE emp5(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
sex CHAR(1)
) AUTO_INCREMENT=100;
delete和truncate对自增的影响
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-B2sltlVC-1683969818368)(C:\Users\fjc23\AppData\Roaming\Typora\typora-user-images\image-20230513165449513.png)]
4.非空约束
特点:某一列不能为null
字段名 字段类型 NOT NULL
3.唯一约束
唯⼀约束的特点:表中的某⼀列的值不能重复(对NULL值不做唯⼀约束的判断,但只能出现⼀个空值)。
语法格式:
字段名 字段类型 unique;
主键约束和唯一约束的区别
1.主键约束,唯一且不能为空
2.唯一约束,唯一但可以为null
3.一个表中只能有一个主键,但可以有多个唯一约束
4.外键约束
1.什么是外键约束
1.外键指的是在从表中与主表的主键对应的那个字段;⽐如员⼯表的dept_id,就是外键。
2.使⽤外键约束可以让两张表之间产⽣⼀个对应关系,从⽽保证主从表的引⽤的完整性。
3.多表关系中的主表和从表:
主表:主键id所在的表,是约束别的表的表;
从表:外键所在的表,是被约束的表。
2.创建外键约束
新建表时添加外键约束
constraint 外键约束名称 foreing key 外键字段名 references 主表(主键字段名);
在已有的表添加外键约束
alter table 表名 add constraint 外键约束名 foreing key 外键字段名 referencrs 主表(主键字段名);
-- 重新创建employee表,添加外键约束
CREATE TABLE employee(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
age INT,
dept_id INT,
-- 添加外键约束
CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)
);
-- ⽅式1:添加外键约束
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dept_id) REFERENCES
department(id);
-- ⽅式2:可以省略外键名称,系统会⾃动⽣成⼀个
ALTER TABLE employee ADD FOREIGN KEY (dept_id) REFERENCES department (id);
3.删除外键
alter table 从表 drop foreing key 外键名称;
4.级联删除
如果主键表中的记录被删除,则外键表中该行数据也会被删除
on delete cascade
-- 重新创建添加级联操作
CREATE TABLE employee(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
age INT,
dept_id INT,
CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)
-- 添加级联删除
ON DELETE CASCADE
);