目录
一、为什么要学习数据库
二、数据库的相关概念
- DBMS、 DB、 SQL
- DB: database 数据库,存储一系列有组织数据的容器
- DBMS:Database Management System 数据库管理系统,使用DBMS管理和维护DB
- SQL:StructureQueryLan 结构化查询语言,程序员用于和DBMS通信的语言
三、数据库存储数据的特点
- 数据先放在表中,表再放在库中
- 一个库可以有多张表,每张表都有自己的唯一标识名
- 一张表的设计,类似于Java中‘类’的设计/表中字段的设计,类似于属性的设计/表中的单条纪录,类似于对象/表中所有纪录,类似于对象的集合
四、初始MySQL
1、MySQL产品的介绍
- 体积小、安装较方便
- 开源、免费
- 性能高、稳定好
- 兼容性好
2、MySQL产品的安装
- 基于C/S架构的DBMS,需要安装服务端和客户端
- www.oracle.com
3、MySQL服务的启动和停止
- 方式一:图形化
- 右击——计算机管理——服务——MySQL服务
- 方式二:通过管理员身份运行dos
- net start 服务名
- net stop 服务名
4、MySQL服务的登录和退出
方式一:通过dos命令——MySQL-h主机名-P端口号-u用户名-p密码
- 注意:如果不是主机名-h主机名 可以省略
- 如果端口号是3306,则-P端口号可以省略
方式二:通过图形化界面客户端
5、MySQL的常见命令和语法规范
常见命令
- show databases 显示当前连接下所有数据库
- show tables 显示当前库中所有表
- show tables from 库名 显示指定库中所有表
- show columns from 表名 显示指定表中所有列
- use 库名 打开/使用指定库
语法规范
- 不区分大小写
- 每条命令结尾建议用分号
- 注释:
- # 单行注释
- --单行注释
- /多行注释
五、DQL语言的学习
DQL: Data Query Language 数据查询语言
1、基础查询
语法:SELECT 查询列表 FROM 表名
特点:
- 查询结果集是一个虚拟表
- 查询列表可以是单个字段、多个字段、常量、表达式、函数,也可以是以上的组合
引申1:起别名
引申2: +的作用
引申3:去重
- DISTINCT()
引申4:补充函数:
- SELECT VERSION();
- SELECT DATABASE();
- SELECT USER();
- SELECT IFNULL(字段名,表达式);
- SELECT CONCAT(字符1,字符2,字符3);
- SELECT LENGTH(字符/字段);获取字节长度
2、条件查询
语法:SELECT 查询列表 FROM 表名 WHERE 筛选条件;
特点:
筛选条件的分类
- 按条件表达式筛选:关系运算符 >= < <= > <> =
- 按逻辑表达式筛选:逻辑运算符 and or not
- 模糊查询: LIKE / BETWEEN AND / IN / IS NULL
3、排序查询
语法:SELECT 查询列表 FROM 表名 WHERE 筛选列表 ORDER BY 排序列表
特点:
- 排序列表可以是单个字段、多个字段、表达式、函数、列数、以及以上的组合
- 升序,ASC 默认行为 / 降序,DESC
4、常见函数
说明:SQL中的函数分为单行函数和分组函数
调用语法:SELECT 函数名(实参列表);
- 字符函数
concat(str1, str2) 拼接字符
substr(str,pos) 截取从pos开始的所有字符,起始索引从1开始
substr(str,pos,len) 截取len个从pos开始的字符,起始索引从1开始
length(str) 获取字节个数
char_length(str) 获取字符个数
upper(str) 变大写
lower(str) 变小写
trim(【substr from】str): 去前后指定字符,默认是去空格
left(str,len): 从左边截取指定len个数的字符
right(str,len): 从右边截取指定len个数的字符
lpad(str,substr,len) 左填充
rpad(str,substr,len) 右填充
strcmp(str1,str2) 比较两个字符的大小
instr(str,substr) 获取substr在str中第一次出现的索引 - 数学函数
ceil(x) 向上取整
floor(x) 向下取整
round(x,d) 四舍五入
mod(x,y) 取模/取余
truncate(x,d) 截断,保留小数点后d位
abs(x) 求绝对值 - 日期函数
now() 获取当前日期——时间
curtime() 只有时间
curdate() 只有日期
date_format(date,格式) 格式日期位字符
str_to_date(str,格式) 将字符转换成日期
datediff(date1,date2) 获取两个日期之间的天数差 - 流程控制函数
if(条件,表达式1,表达式2) 如果条件成立,返回表达式1,否则返回表达式2
case 表达式
when 值1 then 结果1
when 值2 then 结果2
...
else 结果n
end
5、分组函数
sum() avg() max() min() count()
特点:
- 实参的字段的类型,sum,avg只支持数值型,其他三个可以支持任意类型
- 这五个函数都忽略null值
- count可以支持以下参数
count(字段) 查询该字段非空值的个数
count(*) 查询结果集的行数
count(1) 查询结果集的行数 - 分组函数可以和distinct搭配使用,实现去重的统计
select count(distinct字段) from 表;
6、分组查询
语法:
select 分组函数,分组的字段
from 表名
where 分组前的筛选条件
group by 分组列表
having 分组后的筛选条件
order by 排序列表;
特点:
- 分组列表可以是单个字段、多个字段
- 筛选条件分为两类
筛选的基表 使用的关键字 位置 分组前筛选 原始表 where group by前面 分组后筛选 分组后的结果集 having group by后面
7、连接查询
说明:当查询中涉及到了多个字段,则需要通过多表连接
笛卡尔乘积:
出现原因:没有有效的连接条件
解决办法:添加有效的连接条件
- SQL92语法:
SELECT 查询列表
FROM 表1 别名,表2 别名
WHERE 连接条件
AND 筛选条件
GROUP BY 分组列表
HAVING 分组后筛选
ORDER BY 排序列表
执行顺序:FROM -- WHERE -- AND -- GROUP BY -- HAVING -- SELECT -- ORDER BY
- SQL99语法:
内连接
SELECT 查询列表
FROM 表1 别名
【INNER】 JOIN 表2 别名
ON 连接条件
WHERE 筛选条件
GROUP BY 分组列表
HAVING 分组后筛选
ORDER BY 排序列表
执行顺序:FROM -- WHERE -- AND -- GROUP BY -- HAVING -- SELECT -- ORDER BY
外连接
SELECT 查询列表
FROM 表1 别名
LEFT / RIGHT/ FULL【OUTER】 JOIN 表2 别名
ON 连接条件
WHERE 筛选条件
GROUP BY 分组列表
HAVING 分组后筛选
ORDER BY 排序列表
LIMIT 分页函数
特点:
- 外连接的查询结果=内连接的查询结果+主表有但从表没有的纪录
- 区分主从表:left join左边的表是主表 right join右边的是主表
- 一般俩将,外连接往往用于查询主表有但从表没有的纪录
- 案例:查询那个女神没有男朋友,查询哪个部门没有员工
8、子查询
说明:在一个select语句中,又嵌套了另外一个完整的select语句。里面的select语句称为子查询或内查询,外面的select语句称为主查询或外查询
分类:select后 / from后 / where或having后 / exists后
特点:
- 子查询最好用小括号括起来
- 子查询一般放在条件的右侧
- 子查询优先于主查询执行的,主查询使用到了子查询的结果
- 放在where或having后面的子查询,一般分两种
单行子查询:结果集为单行单列,一般搭配关系运算符使用(>< >= <= = <>)
多行子查询:结果集为多行单列,一般搭配多行操作符使用(any、some、all、in)
9、分页查询
语法:
SELECT 查询列表
FROM 表1 别名
LEFT / RIGHT/ FULL【OUTER】 JOIN 表2 别名
ON 连接条件
WHERE 筛选条件
GROUP BY 分组列表
HAVING 分组后筛选
ORDER BY 排序列表
LIMIT 分页函数 (起始索引,条目数)
特点:
- 表中条目索引默认从0开始
- 如果limit子句中,起始索引为0,则可以不写,limit 5;等价于limit 0,5;
- 公式:显示page页,每页size条 limit(page-1)*size,size
10、union联合查询
语法:
完整的select语句1 union
完整的select语句2 union
完整的select语句3 union
...
完整的select语句n union
特点:
- union默认去重,可以使用union all包含重复项
- 多条待合并的查询语句的查询列数必须一致
六、DML语言的学习
七、DDL语言的学习
一)库和表的管理
1)库的管理
创建库:create database 【if not exists】库名
删除库:drop database 【if exists】库名
2)表的管理
创建表
create table 【if not exists】表名(
字段名 字段类型 【字段约束】,
字段名 字段类型 【字段约束】,
字段名 字段类型 【字段约束】)
3)修改表【了解】
- 修改表名:alter table 表名 rename to 新表名;
- 添加新字段:alter table 表名 add column 新字段名 字段类型 【约束】;
- 修改字段类型或约束:alter table 表名 modify column 字段名 新字段类型 【新字段约束】;
- 修改字段名:alter table 表名 change column 旧字段名 新字段名 新字段类型 【新字段约束】;
- 删除字段:alter table 表名 drop column 字段名;
4)删除表:drop table 【if exists】表名;
5)复制表
- 仅仅复制表的结构:create table 表名 like 旧表;
- 可以复制表的结构+数据:create table 表名 子查询;
二)常见数据类型介绍
1)整型:tinyint、smallint、int、bigint
2)浮点型:float(m,n)、double(m,n)、decimal(m,n)
n:小数点后最多保留的位数
m:整数+小数 最多的位数
m,n都是可选的
例如:float(5,3)能保存的数值范围:-99.999-99.999
按精读排序:decimal > double > float
3)字符型:
char(n): n可选,默认是1,固定长度的字符,效率较高,适合存储长度固定的字段
varchar(n):n必选,不能省略。可变长度的字符,效率较低,适合存储长度变化较大的字段
text:保存较长文本
4)日期型:
- date 只能保存日期
- time 只能保存时间
- datetime 保存日期+时间 所占字节空间:8,能表示的日期范围较大
- timestamp保存日期+时间 所占字节空间:4,能表示的日期范围较小
三)常见约束
NOT NULL 非空
DEFAULT 默认
UNIQUE 唯一
PRIMARY KEY 主键
CHECK 检查,mysql不支持
FOREIGN KEY 外键
分类:表级约束和列级约束
支持列级约束:NOT NULL DEFAULT UNIQUE PRIMARY KEY CHECK
支持表级约束:UNIQUE PRIMARY KEY FOREIGN KEY
案例1:
create table stuinfo(
id int primary key,
name varchar(20) unique,
gender char default '男',
age int check(age>=1 and age<=100),
email varchar(20) not null,
majorid int,
constraint fk foreign key(majorid) references major(id));
案例2:
create table stuinfo(
id int ,
name varchar(20) ,
gender char default '男',
age int check(age>=1 and age<=100),
email varchar(20) not null,
majorid int,
primary key(id) # 添加主键
constraint uq unique(name) #添加唯一键
constraint fk foreign key(majorid) references major(id)); # 添加外键