pt10mysql基础

数据库

关系型： 采用关系模型（二维表）来组织数据结构的数据库 ，如Oracle 、SQL_Server、 MySQL
非关系型： 不采用关系模型组织数据结构的数据库，如：MongoDB、Redis

数据库管理

show databases;
create database stu character set utf8;   #编码为utf8   库名区分字母大小写
create database stu charset=utf8;

use stu;
select database();
drop database test;

数据表管理

数字类型：
- 整数类型：INT，SMALLINT，TINYINT，MEDIUMINT，BIGINT
- 浮点类型：FLOAT，DOUBLE，DECIMAL
- 比特值类型：BIT  #比特值类型指0，1值,表达2种情况，如真，假
#对于准确性要求比较高的东西，比如money，用decimal类型减少存储误差。
#DECIMAL(M,D)。M是最大数字位数，D是小数点右侧的位数。
#比如 DECIMAL(6,2)最多存6位数字，小数点后占2位,取值范围-9999.99到9999.99。

基础数据类型:
- 普通字符串： CHAR，VARCHAR
- 存储文本：TEXT
- 存储二进制数据： BLOB
- 存储选项型数据：ENUM(单选)，SET（多选）

字段约束

- 如果你想设置数字为无符号则加上 UNSIGNED
- 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
- DEFAULT 表示设置一个字段的默认值
- COMMENT  增加字段说明
- AUTO_INCREMENT 定义列为自增的属性，一般用于主键，数值会自动加1。
- PRIMARY KEY 关键字用于定义列为主键。主键的值不能重复,且不能为空。

e.g.  创建班级表
create table class (
id int primary key auto_increment,
name varchar(30) not null,
age tinyint unsigned,
sex enum('m','w','o'),
score float default 0
);

e.g. 创建兴趣班表
create table hobby (
id int primary key auto_increment,
name char(30) not null,
hobby set("sing","dance","draw"),
level char  comment "评级",
price decimal(7,2),
remark text  comment "备注信息"
);


show  tables；
desc class;
show create table class;   #查看表原始的创建 语句
drop table student;

表数据基本操作

插入(insert)

insert into 表名 values(值1，值2...),(值1，值2...),...;
insert into 表名 (字段1,...) values (值1，值2...),...;

insert into class values
(1,"Lily",18,'f',89),
(2,"Lucy",18,'f',76),
(3,"Tom",17,'m',83);

insert into class
(name,age,sex,score)
values
("Levi",18,'m',86),
("Sunny",17,'m',91),
("Eva",17,'f',71);

查询(select)

select * from 表名 [where 条件];
select 字段1,字段2 from 表名 [where 条件];

where子句

select * from class_1 where age % 2 = 0;

比较运算符

select * from class where age > 8;
select * from class where age between 8 and 10;
select * from class where age in (8,9);
select * from class where sex is null;

逻辑运算符

select * from class where sex='m' and age>9;

查询练习

1. 查找30多元的图书
２．查找人民教育出版社出版的图书　
３．查找老舍写的，中国文学出版社出版的图书　
４．查找备注不为空的图书
５．查找价格超过60元的图书，只看书名和价格
６．查找鲁迅写的或者茅盾写的图书


1. 查找30多元的图书
select * from books
where price between 30 and 39.99;

2．查找人民教育出版社出版的图书　
select * from books where press="人民教育出版社";

3．查找老舍写的，中国文学出版社出版的图书　
select * from books
where author="老舍" and press="中国文学出版社";

4．查找备注不为空的图书
select * from books where comment is not null;

5．查找价格超过60元的图书，只看书名和价格
select bname,price from books where price>60;

6．查找鲁迅写的或者茅盾写的图书
select * from books where author in ("鲁迅","茅盾");

更新表记录(update)

update 表名 set 字段1=值1,字段2=值2,... where 条件;
# 注意:update语句后如果不加where条件,所有记录全部更新
update class set age=18,score=91 where name="Abby";
update class set sex='m' where sex is null;
update class set age=age+1;

删除表记录（delete）

delete from 表名 where 条件;
注意:delete语句后如果不加where条件,所有记录全部清空
delete from class where score=0 and sex='m';

表字段的操作(alter)

语法 ：alter table 表名 执行动作;

* 添加字段(add)
    alter table 表名 add 字段名 数据类型;
    alter table 表名 add 字段名 数据类型 first;
    alter table 表名 add 字段名 数据类型 after 字段名;
* 删除字段(drop)
    alter table 表名 drop 字段名;
* 修改数据类型(modify)
    alter table 表名 modify 字段名 新数据类型;
* 修改字段名(change)
    alter table 表名 change 旧字段名 新字段名 新数据类型;

--增加字段
alter table hobby add phone char(10) after price;

--删除字段
alter table hobby drop level;

--修改字段数据类型
alter table hobby modify phone char(16);

--修改字段名
alter table hobby change phone tel char(16);

时间类型数据

- 日期 ： DATE
- 日期时间： DATETIME，TIMESTAMP
- 时间： TIME
- 年份 ：YEAR

date ："YYYY-MM-DD"
time ："HH:MM:SS"
datetime ："YYYY-MM-DD HH:MM:SS"
timestamp ："YYYY-MM-DD HH:MM:SS"

create table marathon (
id int primary key auto_increment,
athlete varchar(32),
birthday date,
r_time datetime comment "报名时间",
performance time
);

insert into marathon values
(1,"曹操","1998-2-16","2021/5/6 10:10:27","2:38:49"),
(2,"关羽","2000-7-19","2021/4/30 16:22:09","2:27:18"),
(3,"孙策","1995-10-23","2021/5/2 20:1:2","2:44:00");

select * from marathon where birthday>='2000-01-01';
select * from marathon where birthday>="2000-07-01" and performance<="2:30:00";

日期时间函数 now()

select * from marathon where   r_time<now();

练习：

create table books(
id int primary key auto_increment,
bname varchar(50) not null,
author varchar(30),
press varchar(128),
price float,
`comment` text
);
insert into books
(bname,author,press,price,comment)
values
("边城","沈从文","机械工业出版社",36,"小城故事多"),
("骆驼祥子","老舍","机械工业出版社",43,"你是祥子么？"),
("茶馆","老舍","中国文学出版社",55,"老北京"),
("呐喊","鲁迅","人民教育出版社",71,"最后的声音"),
("朝花夕拾","鲁迅","中国文学出版社",53,"好时光"),
("围城","钱钟书","中国文学出版社",44,"你心中的围城是什么");

insert into books
(bname,author,press,price)
values
("林家铺子","茅盾","机械工业出版社",51),
("子夜","茅盾","人民教育出版社",47);

1.将呐喊的价格修改为45元
update books set price=45 where bname="呐喊";

2.增加一个字段出版时间 类型为 date 放在价格后面
alter table books
add p_time date comment "出版时间" after price;

3.修改所有老舍的作品出版时间为 2018-10-1
update books set p_time="2018-10-1"
where author="老舍";

4.修改所有中国文学出版社出版的但是不是老舍的作品
出版时间为 2020-1-1
update books set p_time="2020-1-1"
where press="中国文学出版社" and author!="老舍";

5.修改所有出版时间为Null的图书出版时间为2019-10-1
update books set p_time="2019-10-1"
where p_time is null;

6.所有鲁迅的图书价格增加5元
update books set price=price+5
where author="鲁迅";

7.删除所有价格超过70元或者不到40元的图书
delete from books where price not between 40 and 70;

高级查询语句

模糊查询 like

LIKE用于在where子句中进行模糊查询，%来表示任意0个或多个字符，下划线`_`表示任意一个字符。

select * from class where name like "T%";
select * from class where name like "____";
select * from hobby where hobby like "%draw%";

as 用法

在sql语句中as用于给字段或者表重命名

select name as 姓名,score as 分数 from class;
select cls.name,cls.score from class as cls where cls.score>80;
#先解析from后的部分，再解析 where后的部分，最后select之后

排序&复合排序

ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。

默认情况ASC表示升序，DESC表示降序

select * from class order by score desc;
select * from class where sex='m' order by score;

复合排序：对多个字段排序，即当第一排序项相同时按照第二排序项排序

select * from class order by age,score desc;

限制 limit

LIMIT 子句用于限制由 SELECT 语句返回的数据数量 或者 UPDATE,DELETE语句的操作数量

SELECT column1, column2, columnN 
FROM table_name
WHERE field
LIMIT [num] [OFFSET num]  

--男生第一名
select * from class where sex='m' order by score desc limit 1;
--男生第二名
select * from class where sex='m' order by score desc limit 1 offset 1; #offset 1   跳过第一个

联合查询

UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

UNION 操作符语法格式：

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

默认UNION后卫 DISTINCT表示删除结果集中重复的数据。如果使用ALL则返回所有结果集， 包含重复数据。

--分数大于80的男生和分数大于90的女生
select * from class where score>80 and sex='m'
union
select * from class where score>90 and sex='w';

--可以查询不同字段，但是字段数量必须一致，字段名同前一个查询
select name,age,score from class where score>80
union
select name,hobby,price from hobby;

--all表示如果查询结果有重复不去重，
--order by只能加在最后表示对union结果一起排序
select * from class where sex='m'
union all
select * from class where score>80
order by score;

子查询

当一个语句中包含另一个select 查询语句，则称之为有子查询的语句。子查询使用位置：

from 之后 ，此时子查询的内容作为一个新的表内容，再进行外层select查询

select * from (select * from class where sex='m') as man 
where score > 80;
#注意： 需要将子查询结果集重命名(as man表)一下，方便where子句中的引用操作

where子句中，此时select查询到的内容作为外层查询的条件值

--查询与tom同岁的学生
select * from class
where age=(select age from class where name='Tom');   #返回结果需要一个明确值
--查询class表中，与hobby表同名的信息
select * from class
where name in (select name from hobby);

书写与查询过程

(5)SELECT DISTINCT <select_list>                     
(1)FROM <left_table> <join_type> JOIN <right_table> ON <on_predicate>
(2)WHERE <where_predicate>
(3)GROUP BY <group_by_specification>
(4)HAVING <having_predicate>
(6)ORDER BY <order_by_list>
(7)LIMIT <limit_number>

高级查询练习

create table stu.sanguo(
id int primary key auto_increment,
name varchar(30),
gender enum('男','女'),
country enum('魏','蜀','吴'),
attack smallint,
defense tinyint
);


insert into sanguo
values (1, '曹操', '男', '魏', 256, 63),
       (2, '张辽', '男', '魏', 328, 69),
       (3, '甄姬', '女', '魏', 168, 34),
       (4, '夏侯渊', '男', '魏', 366, 83),
       (5, '刘备', '男', '蜀', 220, 59),
       (6, '诸葛亮', '男', '蜀', 170, 54),
       (7, '赵云', '男', '蜀', 377, 66),
       (8, '张飞', '男', '蜀', 370, 80),
       (9, '孙尚香', '女', '蜀', 249, 62),
       (10, '大乔', '女', '吴', 190, 44),
       (11, '小乔', '女', '吴', 188, 39),
       (12, '周瑜', '男', '吴', 303, 60),
       (13, '吕蒙', '男', '吴', 330, 71);

1. 查找所有蜀国人信息，按照攻击力排名
select * from sanguo
where country="蜀"
order by attack desc;

2. 吴国英雄攻击力超过300的改为300，最多改2个
update sanguo set attack=300
where country="吴" and attack>300
limit 2;

3. 查找攻击力超过200的魏国英雄名字和攻击力并显示为姓名， 攻击力
select name as 姓名,attack as 攻击力
from sanguo
where attack>200 and country="魏";

4. 所有英雄按照攻击力降序排序，如果相同则按照防御升序排序
select * from sanguo
order by attack desc,defense;

5. 查找名字为3字的
select * from sanguo where name like "___";

6. 找到魏国防御力排名2-3名的英雄
select * from sanguo
where country="魏"
order by defense desc
limit 2 offset 1;

7. 查找所有女性角色中攻击力大于180的和男性中攻击力小于250的
select * from sanguo where gender="女" and attack>180
union
select * from sanguo where gender="男" and attack<250;

8. 查找攻击力比魏国最高攻击力的人还要高的蜀国英雄
select * from sanguo
where country="蜀" and
 attack > (select attack from sanguo
where country="魏" order by attack desc
limit 1);

聚合操作

聚合操作指的是在数据查找基础上对数据的进一步整理筛选行为，实际上聚合操作也属于数据的查询筛选范围。

方法	功能
avg(字段名)	该字段的平均值
max(字段名)	该字段的最大值
min(字段名)	该字段的最小值
sum(字段名)	该字段所有记录的和
count(字段名)	统计该字段记录的个数

select max(attack) from sanguo;  #找出表中的最大攻击力的值
select count(name) as number from sanguo;  #表中共有多少个英雄
select count(*) from sanguo where attack > 200;  #蜀国英雄中攻击值大于200的英雄的数量

注意： 此时select 后只能写聚合函数，无法查找其他字段，除非该字段值全都一样。
select gender,avg(attack) from sanguo  where gender="男";

--count 不会统计null值  count(*)统计记录数量
select count(*) from class;

聚合分组

group by 给查询的结果进行分组 select 后普通字段与group by后保持一致

#按照 country 分组，统计每组平均攻击和统计  普通字段country前后保持一致，加聚合函数
select country,avg(attack),count(*)
from sanguo
group by country;

对多个字段创建分组，此时多个字段都相同时为一组

--统计每个国家男性英雄和女性英雄的平均攻击力
select country,gender,avg(attack) from sanguo
group by country,gender;

所有国家的男英雄数量前2名的 国家名称及英雄数量，显示为 国家 男英雄数量

select country as 国家,count(*) as 男英雄数量
from sanguo
where gender = "男"
group by country
order by count(*) desc
limit 2;

聚合筛选 having

对分组聚合后的结果进行进一步筛选

--统计平均攻击力大于250的国家的英雄数量
select country,avg(attack),count(*) from sanguo
group by country
having avg(attack)>250;

注意
1. having语句必须与group by联合使用。
2. having语句存在弥补了where关键字不能与聚合函数联合使用的不足,where只能操作表中实际存在的字段。

去重语句 distinct

不显示字段重复值

#表中都有哪些国家
select distinct country from sanguo;
#计算一共有多少个国家
select count(distinct country) from sanguo;
  
注意: distinct和from之间所有字段都相同才会去重    

聚合练习

1. 统计每位作家出版图书的平均价格
2. 统计每个出版社出版图书数量
3. 统计同一时间出版图书的最高价格和最低价格
4. 筛选出那些出版过超过50元图书的出版社，并按照其出版图书的平均价格降序排序

1. 统计每位作家出版图书的平均价格
select author,avg(price) from books
group by author;

2. 统计每个出版社出版图书数量
select press,count(*) from books
group by press;

3. 统计同一时间出版图书的最高价格和最低价格
select p_time,max(price),min(price) from books
group by p_time;

4. 筛选出那些出版过超过50元图书的出版社，
并按照其出版图书的平均价格降序排序
select press,avg(price) from books
group by press
having max(price) > 50
order by avg(price) desc;

索引操作

优点 ： 加快数据检索速度,提高查找效率
缺点 ：占用数据库物理存储空间，当对表中数据更新时,索引需要动态维护,降低数据写入效率

索引分类

普通索引 ：字段值无约束,KEY标志为 MUL
唯一索引(unique) ：字段值不允许重复,但可为 NULL,KEY标志为 UNI
主键索引（PRI）: 一个表中只能有一个主键字段, 主键字段不允许重复,且不能为NULL，KEY标志为PRI。通常设置记录编号字段id,能唯一锁定一条记录

• 创建表时直接创建索引

create table 表名(
字段名 数据类型，
字段名 数据类型，
index 索引名(字段名),
index 索引名(字段名),
unique 索引名(字段名)
);

• 在已有表中创建索引：

create [unique] index 索引名 on 表名(字段名);

• 主键索引添加

 alter table 表名 add primary key(id);

• 查看索引

1. desc 表名;  --> KEY标志为：MUL 、UNI。
2. show index from 表名;

• 删除索引

drop index 索引名 on 表名;
alter table 表名 drop primary key;  # 删除主键

• 扩展： 借助性能查看选项去查看索引性能

set  profiling = 1； 打开功能，记录执行信息。 （项目上线一般不打开）
执行sql
show profiles  查看语句执行信息

外键约束和表关联关系

外键约束

约束 : 约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、关联性

foreign key 功能 : 建立表与表之间的某种约束的关系，由于这种关系的存在，能够让表与表之间的数据，更加的完整，关连性更强，为了具体说明创建如下部门表和人员表。

示例，表格拆分，部门数据会产生大量的重复。

-- 创建部门表
CREATE TABLE dept (id int PRIMARY KEY auto_increment,dname VARCHAR(50) not null);

insert into dept values
(1,"技术部"),
(2,"销售部"),
(3,"市场部"),
(4,"行政部"),
(5,'财务部'),
(6,'总裁办公室');

-- 创建人员表
CREATE TABLE person (
  id int PRIMARY KEY AUTO_INCREMENT,
  name varchar(32) NOT NULL,
  age tinyint unsigned,
  salary decimal(8,2),
  dept_id int
) ;

insert into person values
(1,"Lily",29,20000,2),
(2,"Tom",27,16000,1),
(3,"Joy",30,28000,1),
(4,"Emma",24,8000,4),
(5,"Abby",28,17000,3),
(6,"Jame",32,22000,3);

上面两个表中每个人员都有指定的部门，在没有约束的情况下可以添加一个不存在的部门。

主表和从表：若同一个数据库中，B表的外键与A表的主键相对应，则A表为主表，B表为从表。

foreign key 外键的定义语法：

[CONSTRAINT symbol] FOREIGN KEY（外键字段） 

REFERENCES tbl_name (主表主键)

[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用

-- 创建表时直接简历外键
CREATE TABLE person (
id int PRIMARY KEY AUTO_INCREMENT,
name varchar(32) NOT NULL,
age tinyint unsigned,
salary decimal(10,2),
dept_id int ,
constraint dept_fk foreign key(dept_id) references dept(id)
);

-- 建立表后增加外键
alter table person add
constraint dept_fk
foreign key(dept_id)
references dept(id);

注意：如果没有类似上面的约束关系时也可以不建立。从表的外键字段数据类型与指定的主表主键应该相同。

-- 通过外键名称解除外键约束     根据业务实际情况使用
alter table person drop foreign key dept_fk;
-- 查看外键名称
show create table person;
注意：删除外键后发现desc查看索引标志还在，其实外键也是一种索引，需要将外键名称的索引删除之后才可以。

级联动作

restrict(默认) : on delete restrict on update restrict

​ 如果从表中有相关联记录则不允许主表删除、更改主键字段值时

cascade ：数据级联更新 on delete cascade on update cascade

​ 当主表删除记录或更改被参照字段的值时,从表会级联更新

alter table person add
constraint dept_fk
foreign key(dept_id)
references dept(id)
on delete cascade on update cascade;

set null : on delete set null on update set null

​ 当主表删除记录、更改主键字段值时，从表外键字段值变为null

alter table person add
constraint dept_fk
foreign key(dept_id)
references dept(id)
on delete set null on update set null;

表关联关系

当我们应对复杂的数据关系的时候，数据表的设计就显得尤为重要，认识数据之间的依赖关系是更加合理创建数据表关联性的前提。一对多和多对多是常见的表数据关系：

• 一对多关系

一张表中有一条记录可以对应另外一张表中的多条记录；但是反过来，另外一张表的一条记录
只能对应第一张表的一条记录，这种关系就是一对多或多对一

举例： 一个人可以拥有多辆汽车，每辆车登记的车主只有一人。

create table person(
  id varchar(32) primary key,
  name varchar(30),
  age int
);

create table car(
  id varchar(32) primary key,
  brand varchar(30),
  price decimal(10,2),
  pid varchar(32),
  foreign key(pid) references person(id)
);

• 多对多关系

一对表中（A）的一条记录能够对应另外一张表（B）中的多条记录；同时B表中的一条记录
也能对应A表中的多条记录

举例：一个运动员可以报多个项目，每个项目也会有多个运动员参加,这时为了表达多对多关系需要单独创建关系表。

CREATE TABLE athlete (
  id int primary key AUTO_INCREMENT,
  name varchar(30),
  age tinyint NOT NULL,
  country varchar(30) NOT NULL
);

CREATE TABLE item (
  id int primary key AUTO_INCREMENT,
  rname varchar(30) NOT NULL
);

CREATE TABLE athlete_item (
   id int primary key auto_increment,
   aid int NOT NULL,
   tid int NOT NULL,
   FOREIGN KEY (aid) REFERENCES athlete (id),
   FOREIGN KEY (tid) REFERENCES item (id)
);

-- 关系表中添加排名字段
alter table athlete_item add ranking int after tid;

表关系设计练习：
根据所学 用户朋友圈表内容表，使其合理，假设现有如下内容需要存储：
姓名  密码  电话  图片 内容  地点 时间 点赞  评论

create table user(
id int primary key auto_increment,
name varchar(30),
passwd char(64),
tel char(16)
);

create table friends(
id int primary key auto_increment,
image char(50),
content varchar(1024),
time datetime ,
address varchar(50),
user_id int ,
foreign key(user_id) references user(id)
);

create table like_comment(
id int primary key auto_increment,
`like` bit,
comment text,
user_id int ,
friends_id int ,
foreign key(user_id) references user(id),
foreign key(friends_id) references friends(id)
);

E-R模型图了解

• 定义

E-R模型(Entry-Relationship)即 实体-关系 数据模型,用于数据库设计
用简单的图(E-R图)反映了现实世界中存在的事物或数据以及他们之间的关系

• 实体、属性、关系

​ 实体

1、描述客观事物的概念
2、表示方法 ：矩形框
3、示例 ：一个人、一本书、一杯咖啡、一个学生

​ 属性

1、实体具有的某种特性
2、表示方法 ：椭圆形
3、示例
   学生属性 ：学号、姓名、年龄、性别、专业 ... 
   感受属性 ：悲伤、喜悦、刺激、愤怒 ...

​ 关系

1、实体之间的联系
2、一对多关联(1:n)
3、多对多关联(m:n) 

• E-R图的绘制

矩形框代表实体,菱形框代表关系,椭圆形代表属性

表关系设计练习：
将book表拆分为三张表（图书   作家    出版社）,自行设计表之间的关系，通过E-R图分析表关系和属性，然后写出创建语句

create table author(
id int primary key auto_increment,
name varchar(20),
sex char,
age tinyint
);

create table press(
id int primary key auto_increment,
pname varchar(20),
tel char(16),
address varchar(512)
);

create table book(
id char(18) primary key,
bname varchar(30),
price float,
author_id int,
press_id int ,
foreign key (author_id) references author(id),
foreign key (press_id) references press(id)
);

create table author_press(
id int primary key,
author_id int,
press_id int ,
foreign key (author_id) references author(id),
foreign key (press_id) references press(id)
);