数据库基本概念
数据库(Database)与数据库管理系统(Database Management System)
数据库DataBase
保存一组数据的仓库就被称为数据库.
例如:xxxx项目中创建了一个名为users的目录,里面存放了若干用户的信息.此时这个users目录就可以称为是一个数据库.
数据库管理系统DBMS
数据库管理系统是一套专门的软件,可以维护磁盘上的数据.提供高效的,灵活的数据维护操作.
常见的DBMS有MySQL,Oracle,DB2,SQLServer等
数据库管理系统中的常见概念
库与表
库(database):是表的集合,用来保存一组数据表.实际开发中都是一个项目会对应一个库
表(table):一张表用来保存一组数据,表是由行(row)和列(column)构成;每行又称作一条记录,列又称为字段
库与表的关系:
DBMS与库之间的关系:
如何操作数据库
数据库所处是一个服务端的角色.因此要想操作数据库,需要以客户端的形式先进行网络连接然后与服务端进行交互.
我们向数据库服务端发送SQL语句,数据库理解该SQL语句的含义并执行相关操作并回馈结果.
所有的DBMS都支持SQL92标准,该标准中规定了数据库相关操作的标准SQL语法.
SQL92标准中定义的sql语法相当于是数据库界的普通话
非SQL92标准的操作,不同的DBMS自己定义的SQL语法则不相同,这类SQL相当于是数据库界的方言
数据库的连接方式
-
命令行
-
图形化界面
-
集成开发环境(IDEA,Eclipse)
-
JDBC(java数据库连接
SQL语言介绍
SQL:全称Structured Query Language 翻译为结构化查询语言
SQL是用来对数据库进行操作的语言
SQL的分类
-
DDL(Database Difinition Language) 数据定义语言,是用来对数据库对象进行操作的语言,数据库对象(库,表,视图,索引,序列等)
-
DML(Database Manual Language) 数据操作语言,是对表中数据进行操作的语言(增,删,改)
-
DQL(Database Query Language) 数据查询语言,是对表中数据查询的语言
-
DCL(Database Control Language) 数据控制语言,对数据库权限的管理(DBA需要了解的)
-
TCL(Transaction Control Language) 事务控制语言.对事务进行操作
DDL - 数据定义语言
DDL是用来对数据库对象进行操作的语言,数据库对象(库,表,视图,索引,序列等)
涉及到的关键字:
CREATE——新建
ALTER——修改
DROP——删除
对数据库的操作
新建数据库
注:SQL语句本身是不区分大小写的,但行业规范关键字大写,非关键字小写
CREATE DATABASE 数据库名 CHARSET=字符集名称
创建数据库时可以单独指定字符集,例如:
CREATE DATABASE mydb1 CHARSET=utf8;
CREATE DATABASE mydb2 CHARSET=gbk;
查询DBMS中已有的数据库
SHOW DATABASES;
查看创建数据库的信息
SHOW CREATE DATABASE 数据库名;
#例
SHOW CREATE DATABASE mydb;
删除数据库
DROP DATABASE 数据库名
#例
DROP DATABASE mydb;
切换数据库
USE 数据库名;
#例
USE mydb1;#切换到mydb1
USE mydb2;#切换到mydb2
对表的操作
创建表
CREATE TABLE 表名(
字段1名字 类型 [默认值,约束],
字段2名字 类型 [默认值,约束],
字段3名字 类型 [默认值,约束],
...
字段n名字 类型 [默认值,约束]
)[CHARSET=字符集名字]
#例
#测试前的准备工作:
CREATE DATABASE mydb; #创建一个库mydb
USE mydb; #切换到mydb上,这样后续创建的表都是在mydb中创建的
#创建一个用于保存用户信息的表
CREATE TABLE user(
id INT,
username VARCHAR(30),
password VARCHAR(30),
nickname VARCHAR(30),
age INT(3)
)
类型是方言,Mysql和MariaDB整数为INT.oracle为NUMBER,字符串类型为VARCHAR,()中指定的是长度信息,表示占用的字节数.例如utf-8编码如果长度为30只能保存10个汉字.
整数类型指定长度表示位数.3表示可以保存一个3位整数
查看表结构
DESC 表名
#例
DESC user;#查看user表的结构
查看创建表时的详细信息
SHOW CREATE TABLE 表名
#例
SHOW CREATE TABLE user;
查看当前库中已经创建的表
SHOW TABLES
修改表名
RENAME TABLE 原表名 TO 新表名
#例:将user表改名为userinfo
RENAME TABLE user TO userinfo;
删除表
DROP TABLE 表名
#例删除userinfo表
DROP TABLE userinfo;
修改表结构
如下准备一张表
CREATE TABLE hero(
name VARCHAR(30),
age INT(3)
)
添加字段
在表的末尾追加新字段
ALTER TABLE 表名 ADD 字段名 类型
例:向表hero中追加一个gender字段,类型为字符串,长度为10字节
ALTER TABLE hero ADD gender VARCHAR(10)
在表的最开始插入新字段
ALTER TABLE 表名 ADD 字段名 类型 FIRST
例:在hero表的最开始添加id字段
ALTER TABLE hero ADD id INT FIRST
在表中插入一个字段
即将指定的字段插入到表中某字段之后
ALTER TABLE 表名 ADD 字段名 类型 AFTER 表中某字段
例:在name之后插入pwd字段
ALTER TABLE hero ADD pwd VARCHAR(30) AFTER name
删除字段
ALTER TABLE 表名 DROP 字段名
删除hero表中的pwd字段
ALTER TABLE hero DROP pwd;
修改字段
ALTER TABLE 表名 CHANGE 原字段名 新字段名 类型 [默认值,约束]
修改age字段的长度为5
ALTER TABLE hero CHANGE age age INT(5);
将age字段的类型改为VARCHAR(20)
ALTER TABLE hero CHANGE age age VARCHAR(20);
将hero表中的gender字段改名为nickname并且类型为VARCHAR(30)
ALTER TABLE hero CHANGE gender nickname VARCHAR(30);
注意事项
修改表结构时,最好表中不要有数据
如果表中包含数据,修改表结构的注意事项
尽量不要改变字段的类型,因为有可能当前字段原数据类型与修改的类型不匹配.
如果修改类型的长度尽量不要缩短,因为现有数据长度违背修改后的长度.
如果添加新的约束,表中现有数据不能违背该约束.
DML语言 -数据操作语言
DML就是对表中的数据进行操作的语言,涉及的具体操作:
-
增(INSERT):用于向表中插入新记录的操作
-
删(DELETE):用于删除表中记录的操作
-
改(UPDATE):用于修改表中记录的操作
准备工作
在mydb库中创建一张表Person
USE mydb;
CREATE TABLE person( name VARCHAR(30), age INT(3) )
INSERT语句-插入数据
INSERT INTO 表名 [(字段名1,字段名2,....)] VALUES (字段1值,字段2值,...)
INSERT INTO person (name,age) VALUES ('张三',18);
INSERT INTO person (age,name) VALUES (22,'李四');
注
在数据库中,字符串的字面量使用单引号('')括起来的,并且字符串的内容是区分大小写的
在INSERT语句中指定字段时,字段的顺序,个数可以与表定义时的不同,但是VALUES后面指定的值必须与前面自定的字段顺序,个数,类型完全一致
全列插入
在INSERT语句中不指定字段名时,则为全列插入.
此时要求VALUES子句中指定的字段值必须与表中定义的字段的顺序,类型,个数完全一致
例
INSERT INTO person VALUES('钱七',32);
在全列插入时,可以使用字段的默认值,此时使用关键字DEFAULT代替指定的值即可
INSERT INTO person VALUES('孙八',DEFAULT);
插入时还可以使用关键字NULL来显示的为字段插入默认值
INSERT INTO person VALUES('周九',NULL);
错误示范:
INSERT INTO person VALUES('老十'); //缺少字段值
INSERT INTO person VALUES(25,'老十') //值的顺序与表中字段顺序不一致
UPDATE语句-修改数据
语法
UPDATE 表名
SET 字段1名=字段1值,字段2名=字段2值,...
[WHERE 过滤条件]
例
UPDATE person SET age=40
执行上述SQL后,person表中每条记录的age字段值都会被改为40!
当UPDATE语句不添加WHERE子句时会将表中所有记录进行修改,这种操作比较少见.
WHERE在UPDATE中可以限制修改表中的记录,只有满足WHERE子句条件要求的记录才会被修改.WHERE子句中的条件可以使用:>,>=,<,<=,<>(不等于建议使用"<>","!="不是所有数据库都支持)
例
将年龄高于30岁的人,改为年龄29
UPDATE person
SET age=29
WHERE age>30
使用表达式进行修改
将person表中每个人的年龄涨一岁
UPDATE person SET age=age+1
修改多个字段
修改person中名为李四的记录,将其名字改为李老四,年龄改为55
UPDATE person SET name='李老四',age=55 WHERE name='李四'
DELETE语句-删除数据
语法
DELETE FROM 表名 [WHERE 过滤条件]
注
通常DELETE语句都要伴随WHERE子句,如果不添加WHERE子句则是清空表操作!!
例
删除person表的中'李老四'
DELETE FROM person WHERE name='李老四'
仅删除person表中满足WHERE条件的记录
删除person表中年龄小于25的人
DELETE FROM person WHERE age<25
清空表操作
DELETE FROM person
DQL语言-数据查询语言
DQL语言是用来检索表中数据的语言
语法
执行顺序
SELECT 子句 6
FROM 子句 1
JOIN.. ON.. 子句 2
WHERE 子句 3
GROUP BY 子句 4
HAVING 子句 5
ORDER BY 子句 7
LIMIT 子句 8
基础查询
一条DQL语句中至少要包含的子句有两个:SELECT子句和FROM 子句
-
SELECT子句用来指定检索表中哪些字段
-
FROM 子句用来指定检索哪些表
具体语法
SELECT 字段1[,字段2,...] 字段可以是具体字段名,也可以是"*",也可以是函数或表达式
FROM 表1[,表2]
例
检索teacher表中所有记录
SELECT * FROM teacher
上述SQL中SELECT子句中我们使用"*","*"表达查询该表中的所有字段.
注:
实际开发中,我们不要使用SELECT *.
原因: 当数据库看到SELECT * 时,会去检索数据字典了解该表的表结构,从而确定表中的字段名然后进行检索.这会消耗不必要的性能开销.
-
检索表中某些字段的值
查看所有老师的名字,性别,工资,年龄 SELECT name,gender,salary,age FROM teacher
结果集中会将teacher表所有记录列出来,但是每条记录仅列出SELECT要求的字段
WHERE子句
在DQL中添加WHERE子句时,可以仅将满足WHERE子句要求的记录检索出来
查看年龄在30岁以上(含)的老师的名字,职称,工资,奖金
SELECT name,title,salary,comm
FROM teacher
WHERE age>=30
连接多个条件
-
AND:"与",都为真时才为真
-
OR:"或",都为假时才为假
例
查看7岁的"大队长"都有谁?列出这些学生的名字,年龄,性别和职位
SELECT name,age,gender,job
FROM student
WHERE age=7 AND job='大队长'
查看所有一级讲师和三级讲师的名字,职称,工资?
SELECT name,title,salary
FROM teacher
WHERE title='一级讲师' OR title='三级讲师'
AND优先级高于OR的,为了提高OR的优先级,可以使用"()"
查看班级编号在6(含)以下的所有大队长和中队长的名字,年龄,性别,班级编号和职位 SELECT name,age,gender,class_id,job FROM student WHERE class_id<=6 AND job='大队长' OR job='中队长' 上面并不能达到查询需求,数据库会误解 SELECT name,age,gender,class_id,job FROM student WHERE class_id<=6 AND (job='大队长' OR job='中队长') 要使用()来提高OR的优先级达到需求
IN(列表)
IN(列表):判断是否等于列表中的其中之一
例
-
查看所有大队长,中队长和小队长的名字,性别,年龄和职位?
-
SELECT name,gender,age,job FROM student WHERE job IN ('大队长','中队长','小队长') job字段的值只要等于列表中其中之一即可,等效OR
NOT IN(列表)
NOT IN(列表):判断不在列表中
例
查看除大队长,中队长,小队长的其他学生的名字,职位,性别,年龄
SELECT name,job,gender,age
FROM student
WHERE job NOT IN('大队长','中队长','小队长');
BETWEEN...AND...
BEWTEEN...AND...用于判断是否在一个区间范围内
例
查看工资在2000到5000之间的老师的名字,性别,年龄,工资
SELECT name,gender,age,salary
FROM teacher
WHERE salary BETWEEN 2000 AND 5000
DISTINCT去重
DISTINCT关键字要紧跟在SELECT关键字之后
作用:可以将DISTINCT后面指定的字段值相同的记录从结果集中去除.
例
查看老师的职称都有哪些?
SELECT title
FROM teacher
上述SQL表达的是查看每个老师的职称,并不是查看所有老师都有哪几种职称(会有重复值)
SELECT DISTINCT title
FROM teacher
在结果集中对title去除重复值后才能达到需求
LIKE,模糊查询
LIKE中有两个通配符:
-
_:下划线表示任意一个字符(1个)
-
%:百分号表示任意个字符(0-多个)
组合示范
LIKE '%X%':字符串中含有X(X前面可以有任意个字符,后面也可以有任意个字符) LIKE '_X%':字符串中第二个字符是X LIKE 'X%' :字符串以X开头 LIKE '%X' :字符串以X结尾 LIKE '%X_Y':字符串倒数第三个字符是X,最后一个字符是Y
例
查看姓张的学生都有谁?
SELECT name
FROM student
WHERE name LIKE '张%'
NULL值判断
判断一个字段的内容是否为空:
-
IS NULL:判断字段值为空
-
IS NOT NULL:判断字段值不为空
不可以使用=或<>判断NULL值
例
查看哪些老师的奖金为空?
SELECT name,salary,comm
FROM teacher
WHERE comm IS NULL
查看有奖金的老师?
SELECT name,salary,comm
FROM teacher
WHERE comm IS NOT NULL
ORDER BY-排序
ORDER BY子句,用于对结果集按照指定的字段进行升序或降序排序.
-
ORDER BY 字段名 [ASC]:将结果集按照指定的字段升序排(从小到大)
-
ORDER BY 字段名 DESC:将结果集按照指定的字段降序排(从大到小)
-
可以按照多字段排序,排序存在优先级,有线按照第一个字段排序结果集,当第一个字段值相同的记录再按照第二个字段排序以此类推.
-
不指定排序方式时,默认为升序
-
ORDER BY 子句应当是DQL中最后一个子句(由于方言的存在,在Mysql,MariaDB中分页使用时会在其后追加LIMIT子句)
例
查看老师的工资排名,从多到少
SELECT name,salary
FROM teacher
ORDER BY salary DESC
分页查询
将一个DQL的查询结果集分段查询出来.
当一个DQL语句查询的结果集记录数很多时,我们就可以分段将数据查询数来,提高查询速度减少资源开销
语法
ORDER BY 字段
LIMIT M,N
M:(当前页数-1)*每页显示的记录数
N:每页显示的记录数
例
查看老师工资的前5名?
每页显示5条记录,显示第1页,就可以达到此效果.
SELECT name,salary
FROM teacher
ORDER BY salary DESC
LIMIT 0,5
聚合函数
概念
聚合函数又称为:分组函数,多行函数.对结果集进行统计的
将结果集中所有的记录按照指定的字段进行统计并最终得到一条结果.
聚合函数分类:
-
MIN:求最小值
-
MAX:求最大值
-
SUM:求和
-
AVG:求平均数
-
COUNT:统计记录数的
MIN,MAX,SUM,AVG是对值的统计,而COUNT是对记录数的统计.
聚合函数忽略NULL值,尤其在AVG和COUNT中体现的比较明显.
要现将参与统计的记录查询出来,然后再基于聚合函数对结果集进行统计.
GROUP BY子句 -分组
GROUP BY 子句可以将结果集按照指定的字段值相同的记录分组,然后在组内根据聚合函数进行统计并得出结果
-
GROUP BY子句是配合聚合函数使用的,如果SELECT子句中没有聚合函数,通常不会使用GROUP BY子句
-
当SELECT子句中出现了聚合函数,那么不在聚合函数中的其它字段都要出现在GROUP BY子句中.
按照单字段分组
例
查看每个班级各多少人?
student表中class_id字段记录了该学生的班级号.那么班级号相同的记录就可以看作同一个班的学生,统计记录数
SELECT COUNT(*),class_id
FROM student
GROUP BY class_id
按照多字段分组
GROUP BY子句中后面指定多个字段,此时会将结果集按照这些字段值组合相同的记录看作一组
例
查看同班级同性别的学生分别多少人?
SELECT COUNT(*),gender,class_id
FROM student
GROUP BY gender,class_id 结果集中性别相同并且班级号相同的记录会被划分为一组
按照聚合函数排序
查看每个科目老师的平均工资排名?
SELECT AVG(salary),subject_id
FROM teacher
GROUP BY subject_id
ORDER BY AVG(salary) DESC
分组中的过滤条件
聚合函数不能出现在WHERE子句中
原因
WHERE子句的过滤时机:在检索表中每条记录时用于过滤使用的,在生成结果集时WHERE会发挥作用.
我们希望将统计结果进行过滤,这应当实在检索表(WHERE发挥作用)之后生成结果集,并将结果集分组统计后才能得到统计结果从而进行过滤(这个过滤时机已经是在WHERE之后进行了).
HAVING子句
HAVING与WHERE一样都是用来添加过滤条件进行过滤的
他们的区别:
-
WHERE是对记录进行过滤的,而HAVING是对分组过滤的
只有满足HAVING要求的分组才会被保留
-
过滤时机不同
-
WHERE是在检索表时进行过滤的,产生结果集之前进行过滤的
-
HAVING是在产生结果集并根据GROUP BY对结果集分组后进行过滤的
-
-
HAVING可以使用聚合函数作为过滤条件,WHERE不可以.
例
-
查看每个科目老师的平均工资?但是仅查看平局工资高于6000的那些?
SELECT AVG(salary),subject_id 4 根据筛选出的分组再进行统计并得到实际的结果集 FROM teacher 1 确定数据来自哪张表 GROUP BY subject_id 2 确定结果集按照那个字段分组,执行后结果集已经分号组 HAVING AVG(salary)>6000 3 根据HAVING的过滤条件筛选出符合要求的分组