MYSQL详解（CRUD）

目录

一、用命令行打开MYSQL

二、数据库操作

1、查看当前数据库

 2、创建数据库

 3、选中数据库

 三、表操作

1、查看数据库中的表

2、创建表

3、查看指定表的表结构

4、删除表

5、在表中新增/插入数据

6、 查找/排序表中信息

 a.查找表中的所有列

 b.查找指定列

 c.列和列运算

 d.去重 

 e.排序​

 f.条件查询 

 g.模糊查询​

 h.空值判定 

 i.分页查询

 7、修改表中数据

 8、删除表中数据

9、插入联合查询（将表中的数据插入到另一张表上）

10、聚合查询

a. count

b.sum

c.avg 

11、联合查询 

四、MYSQL中的数据类型

五、警告warnings

1、查看警告信息

2、Date truncated数据截断

六、数据库约束

1、约束类型

a.NOT NULL 保证数据不为空

b.UNIQUE 保证数据唯一性

c.DEFAULT 设置默认值

d.PRIMARY KEY 主键 保证这一列的数据不能为空且不能重复

​e.FOREIGN KEY  外键 判断表之间是否关联

f.CHECK

七、表的设计

1、实体关系

2、E-R图  （实体关系图）

---

---

一、用命令行打开MYSQL

先按win+R打开运行，输入cmd打开命令行

在命令行中输入

mysql -u root -p

随后输入数据库密码

 

 启动成功

---

二、数据库操作

1、查看当前数据库

在命令行中输入

show databases;

 2、创建数据库

在命令行中输入

create database 数据库名;
//数据库名由数字、字母、下划线组成
//不能以数字开头，不能是mysql中的关键字
//如果想拿关键字作为数据库名，可以用反引号``把数据库名引起来
//创建数据库的名字不能重复

 3、选中数据库

在命令行中输入

use 数据库名;

 4、删除数据库

在命令行输入

drop database 数据库名;

---

 三、表操作

1、查看数据库中的表

use 表名;
show tables;

先选中表，再查看表，不然会报错

 

2、创建表

create table 表名(列名 类型，列名 类型);//列名和类型可以有多个

 

3、查看指定表的表结构

desc 表名;

4、删除表

drop table 表名;

drop table if exists 表名;

 

5、在表中新增/插入数据

insert into 表名 values(值,值……);


insert into 表名 (列名1,列名2……) values (value1,value2……);//只在某个/某些列插入数据


insert into 表名 values(value1,value2……),(value3,value4……);//一次性插入多组数据。也可指定插入的列


//关于时间的插入，时间的类型为datetime,格式为'2023-06-08 10:25:34'
//想得到当前时间，可以用now()函数
insert into 表名 values(value1,now());

 

 

 

6、 查找/排序表中信息

select * from 表名;//查找表中的所有列


select 列名,列名……  from 表名; //查找指定列


select 列名,列名……(也可以跟着表达式)  from 表名; //列和列运算
//such as: 
select name,age +10 from student;//意思是，在表student中，每个学生的年龄都加10岁
select name,math + chinese + english from student;//意思是把所有学生的三科成绩相加
select name,math + chinese + english as total from student;//相加之后汇总到一列


select distinct 列名 from 表名; //对表中的某列，进行去重，重复的只留了一个


select 列名1,列名2 from 表名 order by 列名2;     //根据列名2，对表中的数据进行排序,默认升序
select * from 表名 order by 列名;

select 列名1,列名2 from 表名 order by 列名2 desc;//根据列名2，对表中的数据进行排序,降序
select * from 表名 order by 列名;
//desc是降序，asc是升序，默认为升序排列


select * from 表名 order by 列名1 desc,列名2 desc……;//先按列名1降序排序，相同的再按列名2降序排序 ，以此类推，列名越靠前越关键




//条件查询
select * from 表名 where 条件;//（先执行where后面的条件，再执行前面的）
//such as:
select * from exam where chinese > 90;//查询exam表中语文成绩大于90的数据
select * from exam where math > chinese;//查询exam表中数学成绩大于语文成绩的数据
select * from exam where math+chinese+english>200;//查询exam表中数学语文英语成绩加起来大于200的数据
select name,math+chinese+english from exam where math+chinese+english>200;//只显示名字和总和大于200的数据
select * from exam where math>90 and chinese >90;
select * from exam where math>90 or chinese >90;
//如果一个where中既存在and又存在or，则先执行and后执行or
select * from exam where chinese between 90 and 100;//范围查找between and，左闭右闭
select * from exam where math in (90,100);//查询所有数学成绩等于90或100的数据

//模糊查询like
//like只支持两个用法：
//1、使用%，代表任意0个或者n个字符
//2、使用_，代表任意一个字符
//such as:
select * from exam where name like 'b%';//查询所有名字是b什么什么的成绩数据
select * from exam where name like 'b_';//一个下划线只能代表一个字符，如果想查bsn，则需要b__

//查询空数据
//null和其他数值进行计算，结果还是null
//null在条件中时，相当于false
//所以使用<=>，比较相等运算，用来处理null的比较
//such as:
select * from exam where chinese <=> null;//查询所有语文成绩为null的数据
或者select * from exam where chinese is null;



//分页查询limit（一个网站数据很多，一般都分为很多页）
select * from exam limit 3;//查询前3条数据
select * from exam limit 2 offset 1;//从第一条开始查，往后查询两条数据

select name,math+chinese+english as total from exam order by total desc limit 3;//查询语数英排名前3的数据，降序排列

 a.查找表中的所有列

 

 

 b.查找指定列

 

 c.列和列运算

 

 

d.去重 

 

 e.排序

 

 

 

f.条件查询 

 

 

 

 

 

 

 

 

 g.模糊查询

h.空值判定 

 

 i.分页查询

 

 

 7、修改表中数据

update 表名 set 列名1=值1，列名2等于值2…… where 条件;
//such as:
update exam set math=95,chinese=95,english=100 where name='xsj1';//把xsj1的数学，语文，英语成绩分别改为95，95，100

//综合运用：
//将exam表中语数英总分倒数前三的同学的数学都减5分
update exam set math=math-5 order by math+chinese+english asc limit 3;
//将所有同学的数学成绩都改为原来的一半(不加where条件，则操作所有数据)
update exam set math=math/2;

 

 

 8、删除表中数据

delete from 表名 where 条件;
//可以和前面学的全部结合起来

9、插入联合查询（将表中的数据插入到另一张表上）

create table student1(id int,name varchar(20));
create table student2(id int,name varchar(20));

insert into student1 values(1,'xsj'),(2,'bsn');

insert into student2 select * from student1;
//将student1表中的数据插入到student2表中

 

 使用条件：必须保证两张表的列数，类型相匹配，列名可以不同

10、聚合查询

查询过程中，表的行与行之间进行一定的运算。

依赖聚合函数，聚合函数是SQL提供的库函数

聚合函数

count(列名)//返回数据个数
sum(列名)//求和
avg(列名)//求平均值

a. count

create table student(id int,name varchar(20));
insert into student values (1,'xsj'),(2,'bsn'),(3,'bsn1'),(4,null);

select count(*) from 表名;//返回表中数据的数量
select count(列名) from 表名;//返回表中某列存放的数据个数，如果是null，则不算在内
//such as:
select count(*) from student;
select count(id) from student;
select count(name) from student;

 b.sum

select sum(列名) from 表名;//将表中某列的所有数据相加
//会自动跳过null值
//such as:
select sum(math) from exam;

只能针对数字求和，如果是字符串则会有warnings. 

c.avg 

select avg(列名) from 表名;//求表中某列数据的平均值

//such as:
select avg(chinese) from exam;
//也可以搭配表达式使用
select avg(math+chinese+english) from exam;


//分组聚合
//select指定的列要么是带有聚合函数，要么就是指定group by的列，不能非聚合，非group by
//such as:
//创建一个employee表
create table employee(id int,name varchar(20),role varchar(20),salary int);
//在表中插入数据
insert into employee values(1,'xsj','教授',20000),(2,'xsj1','老板',40000),(3,'bsn','教授',22222),(4,'bsn1','老板娘',20000),(5,'xsj2','老板',50000),(6,'bsn2','教授',60000);
//role相同的分为一组，根据role来对数据进行分类，并且算出一类role中对应的平均工资
select role,avg(salary) from employee group by role;


//分组前进行计算
//根据role进行分组，计算除去xsj2之后的对应平均工资
select role,avg(salary) from employee where name !='xsj2' group by role;
//分组后还想去掉role为老板的平均工资，要用having
select role, avg(salary) from employee group by role having role !='老板';

//也可以同时进行，既除去xsj2，也除去老板
select role,avg(salary) from employee where name !='xsj2' group by role having role !='老板';

 

11、联合查询 

---

四、MYSQL中的数据类型

TINYINT //大小为1个字节，相当于java中的byte

SMALLINT //大小为2个字节，相当于java中的short

INT //大小为4个字节，相当于java中的integer

BIGINT //大小为8个字节，相当于java中的long

FLOAT(M,D) //大小为4个字节，单精度，M指定长度，D指定小数位数

DOUBLE(M,D) //大小为8个字节，双精度，M代表长度，D代表小数位数

DECIMAL(M,D) //相比于DOUBLE，精度更大，但存储空间也更大

NUMERIC(M,D) //和DECIMAL一样




VARCHAR(SIZE) //设定一个可变长度字符串，SIZE指定的是最大长度，单位是字符

TEXT //长文本数据

MEDIUMTEXT //中等长度文本数据

BLOB //二进制形式的长文本数据




DATATIME  //8个字节的时间戳

TIMESTAMP  //4个字节的时间戳

---

五、警告warnings

1、查看警告信息

show warnings;

2、Date truncated数据截断

一般是因为对数据进行操作后，小数位数不够用

例如只设置了一个小数位，数据原本是22.5，对其进行除以2操作，得到11.25，但是只能显示一个小数位，所以发生截断，表中数据变为11.3（自动四舍五入）

---

六、数据库约束

1、约束类型

NOT NULL  //指示某列的数据不能是空值
UNIQUE    //保证某列的数据有唯一性，不能重复
DEFAULT   //规定没有给列赋值时的默认值
PRIMARY KEY  //主键，保证这一列的数据不能为空且不能重复
FOREIGN KEY  //外键，判断表之间是否关联
CHECK  //保证列中的值符合指定的条件

a.NOT NULL 保证数据不为空

create table classmates(id int not null,name varchar(20) not null);
//创建一个classmates表，两项数据均不能为空

 此时如果插入的数据有空值，则会报错。

 b.UNIQUE 保证数据唯一性

保证数据的唯一性。

插入/修改数据时，会先查询，判断数据是否已经存在。

如果不存在，则插入/修改成功。若存在，则失败。

create table friend(id int unique,name varchar(20));
//创建一个friend表，其中id不能重复

 此时，如果插入重复数据，则会报错

 c.DEFAULT 设置默认值

在insert指定列插入的时候，其他未被指定到的列按默认值填充

create table apples (id int,name varchar(20) default '未知');

insert into apples(id) values (111);

 

 d.PRIMARY KEY 主键 保证这一列的数据不能为空且不能重复

create table bananas (id int primary key,name varchar(20));
创建一个bananas表，其中id这一列数据不能为空且不能重复

 不能为空且不能重复，否则报错。

Question:如何给一个记录加一个主键？

Answer:用自增主键。

create table bananas (id int primary key auto_increment,name varchar(20));

给自增主键插入数据时，可以手动指定一个值，也可以让mysql自动分配。

如果想自动分配，则在插入时，把id设为null即可。 

 e.FOREIGN KEY  外键 判断表之间是否关联

create table class(classId int primary key auto_increment,className varchar(20));

create table student(studentId int primary key auto_increment,name varchar(20),classId int,foreign key(classId) references class(classId));
//判断student表中的学生信息中的班级ID是否在class表中的classID真实存在

//class叫做student的父表

此时要求student表中的每一个记录的classId都必须在class表中的classId中存在。 

 修改的时候，外键的作用和插入的时候一样。

子表中的数据如果在父表中存在，则不能删除父表中的对应数据。

Question:如何在不删除订单表中某商品的交易信息的情况下，把商品表中的该商品下架处理？

Answer:给商品表增加一列，用来表示该商品是否下架。因为有外键，所以不能直接删表。

f.CHECK

mysql不支持，想了解的话自己搜搜相关文章。

---

七、表的设计

1、实体关系

一对一，一对多，多对多，没关系

such as:

一对一：一个学生只能有一个账号；一个账号只能供一个学生使用

一对多：一个班级可以包含多个学生，但一个学生只能处于一个班级

多对多：一个学生可以选择多门课程，一个课程也可以供多个学生选择

2、E-R图  （实体关系图）