MySQL 数据库
MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 旗下产品。
数据库的基本概念
- **数据(Data):**是数据库中存储的基本对象。定义:描述事物的符号记录。
- **数据库(Database):**顾名思义,是存储数据的仓库。指长期存储在计算机内的、有组织、可共享的数据集合。
- **数据库管理系统(Database Management System):**是位于用户与操作系统之间的一层数据管理软件,其主要目标是使数据作为一种可管理的资源来处理,是一种用于操纵和管理数据库的大型软件。可分为关系型数据库管理软件和非关系型数据库管理软件:
关系型数据库管理软件管理的数据库是以表的方式存储数据。常见的有:MySQL、Oracle、SQL Server。
非关系型数据库管理软件管理的数据库是用文档或字符串等其它非表的方式来存储数据。常见的有:MongoDB、Redis、HBase。 - **数据库管理系统(Database System):**是由数据库及其管理软件组成的系统。它是为适应数据处理的需要而发展起来的一种较为理想的数据处理的核心机构。它是一个实际可运行的存储、维护和应用系统提供数据的软件系统,是存储介质、处理对象和管理系统的集合体。它通常由软件、数据库和数据管理员组成。其软件主要包括操作系统、各种宿主语言、实用程序以及数据库管理系统。
SQL
SQL(Structured Query Language):结构化查询语言,主要包含一下几个部分:
- **DQL(Data Query Language):**数据查询语言,用来查询数据库数据。
- **DML(Data Manipulation Language):**数据库操纵语言,用来改变数据库的数据。
- **DDL(Data Definition Language):**数据定义语言,用来定义数据的结构(数据库/表)。
- **DCL(Data Control Language):**数据控制语言,用来定义数据库用户的权限。
MySQL 数据库操作
MySQL的标识符命名规则:由字母、数字、下划线所组成,且第一个字符必须是字母或下划线(即第一个字符不能是数字),不能是MySQL的关键字。不要取中文名。
1、创建数据库
create database 数据库名 default character set = 'utf8';
【注意:】
1、default character set = ‘utf8’ 必须加上,否则可能不支持中文;
2、数据库名不能使用中文,不能以数字和特殊符号开头;
3、语句中引号和符号,必须为英文符号,否则执行会报错,注意中英文切换。
2、查看当前连接下数据库列表
show databases;
3、切换/选择/使用数据库
use 数据库名;
4、查看当前连接的是哪一个数据库
select database();
5、删除数据库
drop database 数据库名;
MySQL 表的操作
数据类型
名字 | 说明 |
---|---|
int | 整数 |
float | 浮点小数 |
decimal | 定点小数 |
char | 定长字符串 |
varchar | 变长字符串 |
text | 长文本数据 |
datetime | YYYY-MM-DD hh:mm:ss |
date | YYYY-MM-DD |
enum | 枚举 |
约束
约束条件 | 说明 |
---|---|
primary key | 主键约束,是表里的一个或多个字段,它的值可以唯一的标识表里的每一条记录 |
not null | 非空约束 |
unique | 唯一约束,取值不能重复 |
default | 默认值,当没有给该字段赋值时,使用默认值;给该字段赋值,就使用赋的值。 |
foreign key | 外键约束,如果某个字段,它在一张表里是主键,然后它又出现在另外一张表里,那么可以在另外一张表里对它设置外键约束 |
auto_increment | 通常用来设计唯一的主键,必须是整数类型 |
1、建表
create table student(
字段1 数据类型1,
字段2 数据类型2,
......
字段n 数据类型n
);
【注意:】
1、表名和字段名不能用中文,不能以数字和特殊符号开头,不可以为MySQL的关键字;
2、语句中的引号、逗号、括号以及分号,必须为英文符号,否则执行会报错,注意中英文切换;
3、最后一个字段后面没有逗号。
2、查看表结构
desc 表名;
3、显示数据库中所有的表
show tables;
4、表的修改
- 修改表名
alter table 旧表名 rename to 新表名;
- 修改字段名
alter table 表名 change 旧字段名 新字段名 新字段的数据类型;
- 添加字段
alter table 表名 add 字段名 字段的数据类型;
- 删除字段
alter table 表名 drop 字段名;
5、删除表
表数据和表结构一起删除。
drop table 表名;
MySQL 数据增删改查
增加
1、插入一条数据
insert into 表名(字段1,字段2,……字段n) values(值1,值2,……值n);
2、插入多条数据
insert into 表名(字段1,字段2,……字段n)values(值1,值2,……值n),(值1,值2,……值n),……(值1,值2,……值n);
查询
select 子句:过滤出满足需求的列(字段)
from 子句:明确数据的来源
查询所有字段
select * from 表名;
2、查询特定字段
select 字段1,字段2,……字段n from 表名;
3、过滤重复记录
select distinct 字段 from 表名;
4、条件查询
where 子句:过滤出满足需求的行(记录)
关系运算符:= != < > <= <=
逻辑运算符:and 与,并且;or 或,或者;not 非,取反;between and 在…之间;in 在一个集合里任选其一
- 示例1:查询张三学员的性别和籍贯
select xb,jg from xsb where xm='张三';
【注意:】字符类型的值,不可以用 > < >= <=,可以用 = !=
- 示例2:查询年龄大于20岁的学员信息
select * from xsb where nl>20;
- 示例3:查询年龄在19-21岁之间的学员信息(包含边界)
select * from xsb where nl>=19 and nl<=21;
select * from xsb where nl between 19 and 21;
【注意:】使用between and时,between后面跟的是较小的值,and后面跟的是较大的值。
select * from xsb where nl=19 or nl=20 or nl=21;
select * from xsb where nl in(19,20,21);
5、模糊查询
条件查询支持模糊查询,模糊查询使用like关键字。
常用的两个通配符:% 表示此处有0个或1个或多个字符;_ 标识此处有1个字符。
- 示例1:查询姓张的学员信息
select * from xsb where xm like '张%';
- 示例2:查询姓张的学员信息(该学员名字就2个字)
select * from xsb where xm like '张_';
- 示例3:查询名字里包含“龙”的学员信息
select * from xsb where xm like '%龙%';
6、空值查询
条件查询还支持空值查询,空值查询用 is 连接,而不是=;
is null 是空值;
is not null 不是空值;
- 示例1:查询班级为空值的学员信息
select * from student where grade is null;
7、排序查询
asc:ascend 升序;
desc:descend 降序;
- 示例1:按年龄从小到大排序
select * from xsb order by nl asc;
- 示例2:按年龄从大到小排序
select * from xsb order by nl desc;
- 示例3: 默认升序
select * from xsb order by nl;
- 示例4:按年龄从大到小排序,如果年龄相同,则按学号从大到小排序
select * from xsb order by nl desc,xh desc;
8、限制结果查询
limit子句用于限制查询结果返回的数量,常见的用法:
limit n 等同于limit o,n(o 表示索引号,n 展示多少条数据)
limit n,m 表示从记录编号n开始的m条数据(记录的编号从0开始)
- 示例1:返回学生表的前5条记录
select * from xsb limit 5;
更改
如果不跟更新条件,则表里所有的记录都会更新;
语法:
update 表名 set 修改的内容 where 条件;
示例1:将成绩表里所有人的成绩减少2分
update cjb set cj=cj-2;
示例2:将001号学员成绩增加2分
update cjb set cj=cj+2 where xh='001';
示例3:将张三的性别更新为女,年龄更新为21岁
update xsb set xb='女',nl=21 where xm='张三';
删除
如果不跟条件则会删除表里所有的数据(保留表结构)
语法:
delete from 表名 where 条件;
- 示例1:删除001号学员的01号课程的成绩记录
delete from cjb where xh='001' and kch='01';
- 示例2:删除所有学员的课程成绩
delete from cjb;
聚合函数
聚合函数一般用于统计。常用的聚合函数有:
count() 统计个数
avg() 求平均值
sum() 求和
max()/min() 求最大值/最小值
- 示例1:查询学生表里有多少个学员
select count(*) as 学员个数 from xsb;
select count(xh) as 学员个数 from xsb;
count(*)和count(字段)在字段不取空值的情况下二者等效,否则不等效
- 示例2:查询001号学员的最高分、最低分、平均分和总分
select max(cj),min(cj),round(avg(cj),1),sum(cj) from cjb where xh='001';
分组查询
group by:按某个字段做分组
having:对分组之后的数据做进一步过滤,针对组的过滤
where:对分组之前的数据做过滤,针对行的过滤,聚合函数不能出现在where子句里
- 示例1:统计各个班级的人数
select bj,count(*) from xsb group by bj;
- 示例2:统计班级人数在2个人以上的班级
select bj,count(*) from xsb group by bj having count(*)>2;
- 示例3:查询平均成绩高于80分的科目
步骤1:先查询各个科目的平均成绩
select kch,round(avg(cj)) from cjb group by kch;
步骤2:然后过滤出平均成绩高于80分的
select kch,round(avg(cj)) from cjb group by kch having avg(cj)>80;
- 示例4:查询不止1门课程不及格的学员学号
步骤1:首先过滤出不及格的学员学号
select xh from cjb where cj<60;
步骤2:然后按学号分组
select xh from cjb where cj<60 group by xh;
步骤3:过滤出分组数目大于1的分组
select xh from cjb where cj<60 group by xh having count(*)>1;
关联查询
需要将原本不在同一张表的字段一起显示在同一个查询结果里,通过关联查询(表连接)来实现。
通过笛卡尔积运算(将第一张表里的每一行跟第二张表里的每一行一一组合,从而生成一张大的表的过程)连接。
select * from xsb,cjb;
笛卡尔积运算会产生垃圾数据,需要添加关联条件来消除垃圾数据;
select * from xsb,cjb where xsb.xh=cjb.xh;
性能弱,可以则以逐步调试
1、内连接
多表连接的一种,它只返回满足关联条件的结果集;
select * from xsb inner join cjb on xsb.xh=cjb.xh;
2、左连接
左连接:left join 指的是除了返回满足关联条件的结果集以外,还会把left join左边的那张表完整的展示出来,右边的那张表里不满足关联条件的字段位置补空值(null)
- 示例1:查询所有学员的学号、姓名、课程号和成绩(要求没有选课的学员信息也要展示出来)
select x.xh,x.xm,c.kch,c.cj from xsb x left join cjb c on x.xh=c.xh;
3、右连接
右连接:right join 指的是除了返回满足关联条件的结果集以外,还会把right join右边的那张表完整的展示出来,左边的那张表里不满足关联条件的字段位置补空值(null)
select x.xh,x.xm,c.kch,c.cj from cjb c right join xsb x on x.xh=c.xh;
全外连接
全连接 full join 指的是除了返回满足关联条件的结果集以外,还会把full join两边的表完整的展示出来,两边不满足关联条件的字段位置补空值(null)
注意:MySQL不支持full join,可以通过union集合操作来实现全外连接
select x.xh,x.xm,c.kch,c.cj from xsb x left join cjb c on x.xh=c.xh union select x.xh,x.xm,c.kch,c.cj from cjb c left join xsb x on x.xh=c.xh;