概念
数据:data
数据库:DB(database),指存储数据的仓库。长期存储在计算机内有组织的、可共享的大量数据的集合。
数据库管理软件:DBMS用来操作和管理数据仓库的软件。
可分为两类:
- 关系型数据库管理软件:数据库里的数据是以表的方式存储:Mysql、Oracle、SQL server
注意:关系型数据库管理软件使用SQL做数据库管理(SQL:structured query language结构化查询语言) - 非关系型数据库管理软件:数据库里的数据是以文档或字符串等其他非表的方式来存储数据。常见有:MongoDB/Redis/HBase…
数据库系统:DBS在计算机系统中引入数据库以后的系统成为数据库系统
数据库管理员:DBA
SQL:
- DQL数据查询语言。用来检索查询数据库数据。在所有SQL语句这部分语法最为复杂和灵活(select from where group by having order by limit)
- DML数据操作语言(增加修改和删除)。用于改变数据库的数据。(insert、update、delete)
- DDL数据定义语言。用于定义数据结构。(create、alter、drop)
- DCL数据控制语言。用于定义数据库用户的权限。(grant、revoke)
部署
mysql部署
这里使用的是b站狂神大佬的教程,他的主页
navicat部署
b站dory同学呀的资源
常用操作
注:数据库通常包含若干表,一张表里通常包含若干数据
数据库的操作
- 显示当前链接下数据库列表
show databases;
- 创建数据库
create databases 数据库名;
(命名规则:字母、数字和下划线组成,且第一个为字母或下划线,不能是Mysql的关键字)
- 切换选择数据库
use 数据库名; - 查看当前链接的数据库是哪一个
select database(); - 删除数据库
drop database 数据库名;
表的操作
简单的建表语法
create table 表名(
字段1 数据类型1,
字段2 数据类型2,
。。。。。。。。。。。
字段n 数据类型n
);
注解:
- 表名和字段名需要符合Mysql标识符的命名规则。
-Mysql常用数据类型:整型(int) 字符型(char varchar) 浮点型(float double decimal)日期型(date time datetime)
增加约束
建表的时候通常伴随着约束,约束是对表里的字段取值的限制条件。约束的目的在于保证数据是正确的有意义的。
常用约束:
- 主键约束
primary key(主键是表里的一个或多个字段,他可以唯一标识表里的每一条记录,如果设置了主键约束,那么该字段既不能为空,也不能重复。) - 非空约束
not null - 唯一约束
unique - 默认值约束
default 默认值(如果你给某个字段复制了该约束,当你没有给这个字段赋值时,它使用默认值) - 外键约束
foreign key(如果某个字段,它在一张表里做主键,然后它又出现在另外一张表里,这个时候可以在另外一张表里把它设置成外键。一旦设立了外键约束,那么外键跟主键之间就有一个参照关系,即外键的取值要参照主键的取值)
表的其他操作
- 查看表结构
desc 表名; - 显示数据库中所有的表
show tables; - 修改表名
alter table 旧表名 rename to 新表名; - 修改字段名
alter table 表名 change 旧字段名 新字段名 新字段数据类型; - 添加字段
alter table 表名 add 字段名 字段的数据类型; - 删除字段
alter table 表名 drop 字段名; - 删除表
drop table 表名;
注意:删除有参照关系(主外键)的表时,通常先删除子表,再删除父表。
数据的操作
增删改查:DQL、DML
数据的插入
insert into 表名(字段1,字段2,…,字段n) values(值1,值2,…值n);(或省略字段,不省略更好维护)
一次插入多条
insert into 表名(字段1,字段2,…,字段n)
values(值1,值2,…值n),(值1,值2,…值n),…(值1,值2,…值n);
SQL文件的导入导出
- 导出
双击你要导出的数据库并将其激活
- 导入
数据的查询
简单查询
-
查询表里所有的字段
select * from 表名;
-
查询指定字段
select 字段1(,字段2,…,字段n) from 表名;
-
不显示重复数据(去重)查询
select distinct 字段 from 表名;
distinct会修饰后面的所有字段,只有后面字段同时相同才会去掉
-
条件查询
select *(字段1,…,字段n) from 表名 where 查询条件 ;
from字句的作用是明确数据的来源,select字句作用是过滤出满足需求的字段(列),where过滤出满足需求的记录。
关系运算符:= != >= <=
逻辑运算符:and or not
between and 在某个范围里 not between and 不在某个范围里 in 在某些项里面任选其一 not in
不在某个数据集里
查询一班学员的信息
查询李四的性别和籍贯
查询除了张三和李四以外的其他成员信息
查询年龄大于20的学员信息
查询年龄在19-21之间的学员信息
错误写法:SELECT * FROM xsb where 19>= nl<=21;
SELECT * FROM xsb where nl BETWEEN 21AND 19;
正确写法:
- 模糊查询
like
常用通配符:%表示此处有0个1个或多个字符 _表示此处有一个字符
举例查询姓张的学员信息
姓名就两个字的姓范的
查询名字里带一个飞的学员信息
- 空值查询
注意:不能用=null和!=null来表示空值或非空值
- 排序显示
select 字段 from 表名 where 查询条件 order by 字段[asc升序|desc降序]
不写默认升序
查询学员信息,按年龄从大到小排序
select * from xsb order by nl desc;
查询学员信息,按年龄从大到小排序,再按学号降序排
- 聚合函数
聚合函数一般用于分组统计。
常用的聚合函数有:
count()统计个数:空值不加入计算
avg()求平均值
sum()求和
max()求最大值
min()求最小值
举例:
查询学生表里有多少学员
select count(*) 【(as)学员个数】 from xsb;
查询001号学员最高分最低分和平均分
- 分组查询
- select *|字段1,字段2,…字段n from 表名 [where 查询条件] group by 分组字段 having 过滤条件 order by 字段 [asc|desc]
注解:
group by 子句:按某个字段做分组
having子句:对分组之后的数据做进一步过滤
where子句:对分组之前的数据做过滤,针对记录过滤
查询每个学员的成绩总分
查询学员成绩高于100分的
- 数据的更新和删除:
更新的语法:
updata 表名 set 修改的内容 [where 更新条件];有where条件则部分更新,没用则全部更新
将所有成绩表里所有人成绩减少2分
将张三的性别更新为女,年龄更新为21岁
- 删除的语法
delete from 表名 [where 删除条件];(保留表结构)
drop
删除001号学员的01号课程成绩
注意:直接删除001号xsb学员的信息会失败,因为001号学员在cjb里有记录,并且cjb(学号参照了xsb的xh,有主外键关联,需先删除cjb)
语法顺序:
select 5(筛选出符合条件的字段)
from 1(首先明确数据来源)
where 2(查看有无过滤条件)
group by 3(有没有分组的需求)
having 4(有无分组之后的进一步需求)
order by 6(排序显示)
小结:
一旦按照某个字段进行分组,那么select后面只会出现分组的字段和聚合函数
如果有where条件,那么一定在group by前面,它会在分组前先过滤不满足条件的数据,然后再在剩下的满足条件的数据中进行分组
如果有order by排序,那么一定在group by的后面,是对分组后的结果集的数据排序
如果分组后想要筛选符合条件的组而不是所有组,那么需要在group by后面使用having而不是where。
字查询
查询嵌套:在查询语句里再嵌入若干查询
例如:查询选修了Java语言的全部学院的平均成绩
思路:先通过Java语言课程名在课程表里找到对应的课程号,然后再在成绩表里根据课程号查询到对应的成绩表,然后求平均值
原本:
SELECT Kch FROM kcb WHERE Kcm=‘Java语言’;
SELECT round(avg(cj)) FROM cjb WHERE Kch=2;
查询叫李四同学Java语言成绩
列出选修Java课程所有学员的信息
查询Java语言课程分数高于这门课平均分的学员信息
小结:
子查询要用小括号扩起来
子查询经常用在where后做条件一部分
如果子查询返回多行,要用in而不是=
关联查询
需要将原本不在同一张表的字段显示在同一查询里,通过表连接来实现
通过笛卡尔积运算(行相乘,列相加)
当from子句后跟多张表后就会做笛卡尔运算,生成一张大表,存在垃圾数据(例如2号学员连接1号成员信息)
通过添加关联条件消除垃圾数据,通过学生表的学号跟成绩表的学号相等进行过滤
标准写法:select * from xsb inner join cjb on xsb.xh=cjb.xh;
查询李四同学各科成绩,要求查询结构要同时显示姓名、课程号、成绩
标准写法:select xsb.xm,cjb.kch,cjb.cj from xsb inner join cjb on xsb.xh=cjb.xh where xsb.xm=‘李四’;
小结:对于同名字段,我们需要在字段名前加表名区分(非同名可以不加)
from后面跟两张或以上表,这是在做笛卡尔积运算,需要添加关联条件消除
除了题目中查询的需求还有其他限定条件可以用and来连接,对于inner join标准写法,把条件放在where中
可以给字段取别名也可以给表取别名
三表查询:
查询选课学员的姓名、选修的课程名和成绩
标准写法:SELECT x.xm,k.Kcm,c.cj from xsb x inner join cjb c on x.xh=c.xh inner join kcb k on k.kch =c.kch;
表的连接方式
内连接:多表连接的一种,inner join ,只返回满足关联条件的结果集
外连接:
- 左外连接/左连接:left join/left out join指的是除了返回满足关联条件的结果集以外,还好把左边那张表完整展示出来,右边不满足条件的补空值(null)
- 右外连接/右连接:right join/right out join指的是除了返回满足关联条件的结果集以外,还好把右边那张表完整展示出来,左边不满足条件的补空值(null)
- 全外连接/全连接:full join/full out join 把两边表完整展示出,不满足条件的补空值(Mysql不支出full join可以通过union来实现)
查询所有学员的信息包括没有选课学员的信息
限制结果输出
如果只想返回一定数量行,用limit来实现
limit m 等同于limt 0,m
limit n,m表示从记录编号n开始的m条数据(记录编号从0开始)