Day14 MySQL基础
01 mysql介绍
web开发：web框架+前端+mysql
关系型数据库
Mysql
非关系数据库
问题：
持久化保存
需要完成更精细的数据管理
电商系统存用户密码。存入文件，难以查询
动态页面：
根据请求，返回不同的商品信息
请求内容根据数据相关
拿到数据，嵌入到页面
02 mysql的连接
数据库就是存储数据的仓库
SQL（Structured Query Language，结构化查询语言）
Redis 存在内存上，快
软件有两部分
1 mysqld 服务端
2 mysql 客户端
核心内容
关于库的增删改查
关于表的增删改查
关于表内数据的增删改查（重点）
03 数据库的增删改查
库管理
增删改查
1 创建
create database urils;
mysql> create database urils;
编程语言做对接，考虑健壮性
mysql> create database if not exists urils;
-- 1 创建数据库（在磁盘上创建一个对应的文件夹）
mysql> create database [if not exists] db_name [character set xxx];
2 查
show databases;
mysql> show databases;
mysql> show create database urils;
mysql> select database();
3 删除
drop database urils;
mysql> drop database urils;
4 修改字符集
--修改数据库字符集
mysql> alter database db_name [character set xxx]
MariaDB [(none)]> show create database urils;
MariaDB [(none)]> alter database urils character set utf8mb4;
5 切换数据库
MariaDB [(none)]> use mysql;
MariaDB [mysql]> select database();
总结database的增删改查
1 create database [if not exists] 数据库名 character set utf8mb4；
2 show databases;
3 show create database 数据库名;
4 alter database 数据库名;
5 drop database 数据库名;
6 use 数据库名;
7 select database();
MariaDB [mysql]> select version();
04 数据表的增删改查
excel表中 没有约束
主键字段： 非空，且唯一
一般会设置自增auto_increament
1 查看数据表
mysql> show tables;
mysql> show create table emp;
mysql> desc emp;
2 创建数据表
create table 表（
field 类型(约束),
field 类型(约束),
field 类型(约束),
field 类型(约束)
约束:是否可为空,
char 长度必须一致，一般用varchar
“2012-12-12” 取年份，取月份？
mysql> create table emp(
id int primary key auto_increment,
name varchar(32) not null unique,
age int,
birth date);
3 修改表结构alter
3.1 添加字段
添加列（字段） ALTER ADD
mysql> ALTER TABLE <表名> ADD <新字段名><数据类型>[约束条件]［first｜after 字段名］;
first after 默认放在表最后，first name 放在name字段前
mysql> alter table emp add gender tinyint not null after name;
添加多个字段
mysql> create table user(id int primary key auto_increment,name varchar(32));
mysql> alter table user
add addr varchar(20),
add age int first,
add birth varchar(20) after name;
mysql> alter table emp add gender tinyint not null after name;
修改某字段类型 ALTER MODIFY
ALTER TABLE <表名> MODIFY <字段名> <数据类型> [完整性约束条件] [first|after 字段名];
修改某字段名（不常用） ALTER CHANGE
ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型> [完整性约束条件][first|after 字段名];
3.2 删除字段
删除字段 ALTER DROP
mysql> ALTER TABLE <表名> DROP <新字段名>;
mysql> alter table emp drop age;
3.3 修改表
修改表名 ALTER RENAME
mysql> ALTER TABLE <旧表名> RENAME [TO] <新表名>;
mysql> alter table emp rename emps;
修改表所用的字符集
mysql> show create table emps;
mysql> ALTER TABLE 表名 [DEFAULT] CHARACTER SET <字符集名>;
4 删除表 drop
mysql> DROP TABLE [IF EXISTS] 表名1[,表名2，表名3 ...];
mysql> drop table [IF EXISTS] 表名;
mysql> drop table emps;
案例
MariaDB [urils]> CREATE TABLE employee( id int primary key auto_increment, name varchar(20), gender bit default 1, birthday date, department varchar(20), salary double(8,2) unsigned, resume text )character set=utf8mb4;
double(8,2)
999999.99
约束
primary key, auto_increment
not null
unique
下午 05 数据表记录的增删改查
数据表记录的增删改查
1 添加记录
2 查看记录
3 删除记录
4 修改记录
删除和修改需要先查询出来
1 添加记录
INSERT [INTO] <表名> [ <列名1> [ ,...<列名n>] ] VALUES (值1) [..., (值n)];
MariaDB [urils]> insert emp (id,name,age,brith)values(1,'user01',23,'2012-12-12');
MariaDB [urils]> select * from emp;
添加第2条记录
是否必须有顺序？
MariaDB [urils]> insert into emp (name,brith,age)values('user11','2012-12-12',26);
插入
insert into emp (name,brith,age)values('user12','2012-12-12',26);
insert into emp (name,brith,age)values('user13','2012-12-12',26);
insert into emp (name,brith,age)values('user14','2012-12-12',26);
insert into emp (name,brith,age)values('user15','2012-12-12',26);
insert into emp (name,brith,age)values('user16','2012-12-12',26);
一条一条插入，性能查？怎么办
insert into emp (name,brith,age)values('user22','2012-12-12',26),
('user23','2012-12-12',26),
('user24','2012-12-12',26),
('user25','2012-12-12',26),
('user26','2012-12-12',26),
('user27','2012-12-12',26);
能否为空？
MariaDB [urils]> DESC emp;
birth可以为空 Null YES
MariaDB [urils]> insert emp (name,brith) values('zz','2022-01-01');
多条插入
MariaDB [urils]> insert emp (name,brith) values('zz1','2022-01-01'),
('zz2','2022-01-02'),
('zz3','2022-01-03'),
('zz4','2022-01-04');
MariaDB [urils]> inset into emp set name='zz7',age=18;
name有唯一约束，不能再添加
name varchar(32) not null unique
综合练习
创建
create table emp(
id int primary key auto_increment,
name varchar(32) not null unique,
age int,
brith date);
插入
insert emp (name,brith,age) values("user02","2022-01-01",26),
("user03","2022-02-01",26),
("user04","2022-03-01",null),
("user05",null,26),
("user06","2022-05-01",26);
insert emp set name="user07",age=18;
下午 06 where 语句和order by语句
2 查询记录
select *|字段1，字段2... from 表名 where 条件过滤
使用* 有个匹配字段的过程。
MariaDB [urils]> select * from emp;
MariaDB [urils]> select name,age,salary from emp;
select * from emp
select name,age from emp
MariaDB [urils]> select name,age,salary from emp where age >23;
Where 语句
--where字句中可以使用:
--比较运算符
> < >= <= <> !=
between 80 and 100 值在10到20之间
in (80,90,100) 值是80或90或100
like 'user%';
/* parttern可以是 % 或者 _ */
--逻辑运算符
在多个条件直接可以使用逻辑运算符 and or not
MariaDB [urils]> select name,age,salary from emp where age >24 or name='user11';
--正则
MariaDB [urils]> select name,age,salary from emp where name REGEXP '^u';
MariaDB [urils]> select name,age,salary from emp where name REGEXP '5$';
3 更新表
update emp set salary=6000 where id=1;
update emp set salary=10000 where id=2;
update emp set salary=5000 where id=3;
update emp set salary=9000 where id=6;
update emp set salary=12000 where id=7;
查询 between
select * from emp where salary between 5000 and 10000;
查新 in
select * from emp where salary in (6000,10000);
查询 >
select * from emp where salary in (6000,10000,5000);
select * from emp where birth > '2011-12-31';
查询like
MariaDB [urils]> select * from emp where name like 'a%';
MariaDB [urils]> select * from emp where name like '%n';
MariaDB [urils]> select * from emp where name like '%a%';
总结
select *|字段1，字段2... from 表名 where 条件过滤
MariaDB [urils]> select name,age from emp where age>23;
MariaDB [urils]> select * from emp where salary between 6000 and 10000;
MariaDB [urils]> select * from emp where salary in (6000,10000,5000);
MariaDB [urils]> select * from emp where birth < '2000-01-01';
MariaDB [urils]> select * from emp where name like '%s%';
MariaDB [urils]> select * from emp where age>25 and salary >8000;
查询and
Select * from emp where age >23 and salary <5000;
查询order by
Select * from emp order by salary;
查询order by desc
Select * from emp order by salary desc;
查询 order by salary desc,id desc;
第一个一样，再按第二个字段排
Select * from emp order by salary desc,id desc;
查询 where 再order by
Select * from emp where age>23 order by salary desc,id desc;
综合练习
1插入测试数据
insert into emp (name,age,brith)values("usera11",23,"2012-12-12"),
("usear12",23,"2011-12-12"),
("usaer13",26,"1980-12-12"),
("aser14",25,"1980-12-12"),
("usea15",26,"1990-12-12"),
("user16",23,"2000-12-12");
2修改表
alter table emp add salary double(8,2) not null after name;
alter table emp drop salary;
alter table emp add salary double(8,2) not null default 8000 after name;
3 更新
update emp set salary=6000 where id=8
update emp set salary=5000 where id=9
update emp set salary=9000 where id=10;
update emp set salary=10000 where id=12
下午 07 group by
group by
max min avg count
alter table emp add depart varchar(32) default '销售部';
update emp set depart='技术部' where id in (1,3,7);
修改部门
update emp set depart='运营部' where id in (2,4)
每个部门平均薪水
select depart from emp group by depart
select depart,avg(salary) from emp group by depart
select depart,max(salary) from emp group by depart
Group by本质
select depart,count(*) from emp group by depart
Limit 分页相关
select * from emp
select * from emp limit 2,3
默认索引0开始。索引2查3条
select * from emp limit 2
Distinct
select distinct(salary) from emp
select distinct(salary),age from emp
下午08 删除和更新
删除记录
delete from 表名 where
注意为null时要用is
delete from emp where age is null;
select * from emp where age is null
更新记录
update emp set age=24 where id in (2,3);
练习
update emp set age = 24 where id in (11,13);
update emp set age = 24 where id in (10,12);
update emp set age=null where id =10;
select * from emp where age is null
delete from emp where age is null
去重
select distinct(age) from emp
下午09 表与表关系
表关系
Book
id title price publisher publish_email publish_addr
1 西游记 199 苹果出版社 123@qq.com 北京
2 水浒传 299 苹果出版社 123@qq.com 北京
3 红楼梦 399 苹果出版社 123@qq.com 北京
拆分
Book
id title price
1 西游记 199
2 水浒传 299
3 红楼梦 399
Publish
id name email addr
1 苹果出版社 123@qq.com 北京
2 西瓜出版社 223@qq.com 南京
一对多
创建一对多的关联关系：在“多”的表中创建一个关联字段
Book
id title price publish_id
1 西游记 199 1
2 水浒传 299 1
3 红楼梦 399 2
Publish
id name email addr
1 苹果出版社 123@qq.com 北京
2 西瓜出版社 223@qq.com 南京
子查询
查询西游记的出版社的邮箱
select publish_id from book where title = “西游记”;
select email from Publish where id=1
多对多
Book
id title price publish_id
1 西游记 199 1
2 水浒传 299 1
3 红楼梦 399 2
Publish
id name email addr
1 苹果出版社 123@qq.com 北京
2 西瓜出版社 223@qq.com 南京
author
id name age
1 user1 23
2 user2 33
多对多关系： 创建一个关系表
book2author
id book_id author_id
1 1 1
2 1 2
子查询：查询user1 出版了哪些数据
select id from author where name=”user1” 查出2
select book_id from book2author where author_id=2; 查出（1,2）
select title from Book where book_id in(1,2)
一对一关系
author
id name age | | tel addr
1 user1 23 | |
2 user2 33 | |
表关系
创建一对多的关联关系：在“多”的表中创建一个关联字段
创建多对多的关联关系：建立一个关系表
创建一对一的关联关系：在两张表中任意一张表中建立一个关系字段即可
唯一约束
author
id name age ad_id（unique）
1 user1 23 1
2 user2 33 1
下午10 关联查询（未整理完）
子查询
创建表
创建作者表
外键约束
当删除时，数据库会提示 可能不能删除掉。
外键约束，影响性能
去除外键约束，建立逻辑约束。
查询：西游记有多少个作者？
西游记 查出id
Book2author 查book_id = 1 的author_id
作者表 查询author_id
关联查询
1 子查询
2 join查询（连表）
不利于where 条件操作，引入join
Join查询 inner join ... on
子查询一次只差一条
Join 很多条都需要这个查询记录时用。
多对多join
查询西游记对应的作者
查询yuan出版的书籍
下午 11 left Join
left join ... on
查询每本书有多少作者？
Inner join 看不到部分书籍的信息
下午12 约束
主键
默认第一个非空且唯一的字段，为主键。没有非空且唯一 隐含一个_id做主键
聚簇索引 需要主键，建立关联关系
复合（联合）主键
create table sc(studentid int, courseid int, score int,primary key(studentid,courseid));
自增
Create table t1(id int(4) primary key auto_increment,name varchar(25));
唯一
默认值
外键约束
创建外键约束
级联删除
为空删除
创建表注意外键关系
插入数据
注意先插入