数据库管理系统(DBMS)
为了方便数据的存储和管理,将数据按照特定的规则存储在硬盘上,是一个存储数据的仓库
关系型数据库
以数据表为单位,表与表之间存在关联关系
Mysql是一个关系型数据库
具有快速,可靠和易于使用的特点
非关系数据库 redis
缓存 Key:value
SQL(结构化查询语言)
是一种特殊目的的编程语言;
是一种标准的数据库操作语言;
用于数据的存取,查询,更新,和管理关系型数据库
SQL语句
根据操作的不同,分为不同类型的SQL语句
1.DDL
数据(结构)定义语言
创建和修改数据库表结构的语言
常用的语句:create ,alter,drop,rename
数据库名一旦创建就不能被修改,只能修改字符集
-- 创建数据库
create database test;
-- 创建数据库并设置字符集编码,判断数据库是否存在
create database if not exists test charset utf8;
-- 删除数据库
drop database test;
-- 修改字符集
alter database test charset utf8;
数据表
表是数据存储的最常见和最简单的形式,是构成关系型数据库的基本元素。
表的最简单形式是由行和列组成,分别都包含着数据。
每个表都有一个表头和表体,表头定义表名和列名 .表中的行被看作是文件中的记录,表中的列被看作是这些记录的字段。
设计表(数据类型)
1.字符型
char(n) 长度为n的定长字符串,最大长度255个字符
varchar(n) 最大长度为n的可变长字符串
date 日期, 包含年月日
datetime 年月日 时分秒
2.整型
类型 | 字节 | 最小值 | 最大值 |
TINYINT | 1 | -128 | 127 |
SMALLINT | 2 | -32768 | 32767 |
MEDIUMINT | 3 | -8388608 | 8388607 |
INT | 4 | -2147483648 | 2147483647 |
BIGINT | 8 | -9223372036854775808 | 9223372036854775807 |
signed 有符号 默认是有符号
unsigned 无符号
###### 3.浮点型
decimal
数据类型(M,D)
M:精度,数据的总长度;
D:标度,小数点后的长度
###### 4.TEXT列字符字符串
长文本类型
有4种TEXT类型:TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。
-- 创建学生表 (学号,姓名,性别,电话,体重,地址,出生年月)
CREATE TABLE student(
num INT,
NAME VARCHAR(10),
gender CHAR(1),
phone CHAR(11),
height DECIMAL(3,2),
address VARCHAR(30),
brith_time DATETIME
)
-- 删除表
DROP TABLE student
-- 修改表名
RENAME TABLE student TO stu
-- 复制表结构
CREATE TABLE stu LIKE student;
添加约束
PRIMARY KEY -- 设置为主键
AUTO_INCREMENT -- 设置主键列自然增长,只能修饰主键列,而且主键列类型为整数
not null -- 不能为空约束 可以添加到多个普通列
unique -- 唯一性约束 可以添加到多个普通列
check (height<2.60) -- 检查约束
2.DML
数据操纵语言DML(Data Manipulation Language)
常用语句: insert,delete,update
-- 修改语句 需要注意条件的准确性,否则修改全部数据
update student set address='陕西',height=1.23 where num = 1
-- 删除语句
delete from student -- 没有条件全部删除
delete from student where num=1
/*插入数据
方式1: INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2…..,值n);
方式2: INSERT INTO 表名 set 列名1=值1,..列名n=值n;
方式3: INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2…..,值n),(值1,值2…..,值n);
方式4:INSERT INTO 表名(列1,列2……,列n) 查询语句(查询的列数与插入列数匹配)*/
-- 方法一
INSERT INTO student (num,NAME,gender,phone,height) VALUES(1,'张三','男',12345678912,1.72);
-- 方法二
INSERT INTO student SET num=2,NAME='张三',gender='男',phone=12345678934,height=1.82;
-- 方法三
INSERT INTO student (num,NAME,gender,phone,height) VALUES(3,'张三','男',12345678912,1.72),
(4,'张三','男',12345678956,1.92),
(5,'张三','男',12345678978,1.42);
-- 方法四
INSERT INTO stu (num,NAME,gender,phone,height)
SELECT num,NAME,gender,phone,height FROM student
3.DQL
查询列表可以是:表中的字段、常量、表达式、函数
查询的结果是一个虚拟的表格
查询结果处理:
特定列查询:select column1,column2 from table
全部列查询: select * from table
算数运算符:+ - * /
排除重复行: select distinct column1,column2 from table
查询函数:select 函数; / 例如version()
sum()称为聚合函数,把多行数据放到一个中
sql中 +-*/ 只能用做算术运算, +不能连接字符串
字符函数
-- length():获取参数值的字节个数
SELECT NAME,LENGTH(NAME) FROM student
-- char_length()获取参数值的字符个数
SELECT NAME,CHAR_LENGTH(NAME)AS NAME,gender FROM student
-- concat(str1,str2,.....):拼接字符串
SELECT num,CONCAT(NAME,':',gender)AS NAME FROM student
-- upper()/lower():将字符串变成大写/小写
SELECT UPPER(NAME),LOWER(gender) FROM student
-- substring(str,pos,length):截取字符串 位置从1开始
SELECT SUBSTRING(NAME,2,3) FROM student
-- instr(str,指定字符):返回子串第一次出现的索引,如果找不到返回0
SELECT INSTR(NAME,'张') FROM student
-- trim(str):去掉字符串前后的空格或子串,trim(指定子串 from 字符串)
SELECT CHAR_LENGTH(TRIM(NAME)) FROM student
-- lpad(str,length,填充字符):用指定的字符实现左填充将str填充为指定长度
SELECT LPAD(NAME,5,'a') FROM student
-- rpad(str,length,填充字符):用指定的字符实现右填充将str填充为指定长度
SELECT RPAD(NAME,5,'b') FROM student
-- replace(str,old,new):替换,替换所有的子串
SELECT REPLACE(NAME,'张','王') FROM student
逻辑处理
case when 条件 then 结果1 else 结果2 end; 可以有多个when
ifnull(被检测值,默认值)函数检测是否为null,如果为null,则返回指定的值,否则返回
原本的值
if函数:if else的 效果 if(条件,结果1,结果2)
SELECT NAME,
(CASE WHEN (height>=1.80) THEN '高个子'
WHEN (height>=1.60) THEN '正常身高'
ELSE '低个子' END)AS height,
gender FROM student
SELECT NAME,
IFNULL(address,'暂未确认')AS address
FROM student
SELECT NAME,
IF(height>=1.80,'高个子','低个子')AS height FROM student
数学函数
round(数值):四舍五入
ceil(数值):向上取整,返回>=该参数的最小整数
floor(数值):向下取整,返回<=该参数的最大整数
truncate(数值,保留小数的位数):截断,小数点后截断到几位
mod(被除数,除数):取余,被除数为正,则为正;被除数为负,则为负
rand()478*89:获取随机数,返回0-1之间的小数
日期函数
now():返回当前系统日期+时间
curdate():返回当前系统日期,不包含时间
curtime():返回当前时间,不包含日期
可以获取指定的部分,年、月、日、小时、分钟、秒
YEAR(日期列),MONTH(日期列),DAY(日期列) ,HOUR(日期列) ,MINUTE(日期列)
SECOND(日期列)
str_to_date(字符串格式日期,格式):将日期格式的字符转换成指定格式的日期
date_format(日期列,格式):将日期转换成字符串
datediff(big,small):返回两个日期相差的天数
-- 日期格式化
SELECT NAME, YEAR(brith_time),MONTH(brith_time),DAY(brith_time) FROM student
-- 将字符串格式转化为日期类型
SELECT NAME, STR_TO_DATE('1982-01-17','%Y-%m-%d')AS birthday FROM player
-- 返回当前系统日期
SELECT NAME, NOW() FROM student
-- 返回当前系统日期,不包括时间
SELECT NAME, CURDATE() FROM student
-- 将日期转换为字符串
SELECT NAME, DATE_FORMAT(birthday,'%Y-%m-%d-%H') FROM player
-- 返回两个日期差的天数
SELECT NAME, DATEDIFF(NOW(),birthday) FROM player
-- SECOND(日期列)
SELECT NAME,SECOND(birthday) FROM player
分组函数 /聚合函数 /统计函数
sum()求和,avg()平均值,max()最大值,min()最小值,count()计数
1.sum,avg一般用于处理数值型max,min,count可以处理任何类型
2.以上分组函数都忽略null值
3.count函数的一般使用count(*)用作统计行数
4.和分组函数一同查询的字段要求是group by后的字段
5.count(列名),如果数据中存在null,就不会统计它
条件查询
select 结果列 from 表名 where 条件
比较
=, != 或<>, >, <, >=, <=
逻辑运算
and 与
or 或
not 非
模糊查询
like %字符%
%:通配符,任意多个字符
between .... and ... 表示在两个区间,包含临界值
in 判断某字段的值是否属于in列表中的某一项
in(... , ... , ...)
IS NULL(为空的)或 IS NOT NULL(不为空的)
UNION
1.UNION 的语法如下:
[SQL 语句 1]
UNION
[SQL 语句 2]
2、UNION ALL 的语法如下:
[SQL 语句 1]
UNION ALL
[SQL 语句 2]
当使用union 时,mysql 会把结果集中重复的记录删掉,而使用union all ,
mysql 会把所有的记录返回,且效率高于union 。
排序
查询结果排序,使用 ORDER BY 子句排序 order by 排序列 ASC/DESC
asc代表的是升序,desc代表的是降序,如果不写,默认是升序
什么排序都没有,默认按主键进行升序排列
order by子句中可以支持单个字段、多个字段
数量限制
limit 开始位置(开始位置为0),查询的数量
SELECT * from table LIMIT 0,5
分组查询
语法:
select 分组函数,列(要求出现在group by的后面)
from 表
[where 筛选条件]
group by 分组的列表
[having 分组后的筛选]
[order by 子句]
注意:查询列表比较特殊,要求是分组函数和group by后出现的字段
-- 分组查询
-- 性别中的查询
SELECT MAX(height),gender FROM student GROUP BY gender
SELECT COUNT(*),gender FROM student WHERE height>=1.80 GROUP BY gender
-- 每个位置平均体重的查询
SELECT AVG(weight),lamp FROM player GROUP BY lamp
-- 每个位置总共体重的查询
SELECT SUM(weight),lamp FROM player GROUP BY lamp
-- 重复姓名查询
SELECT COUNT(*), NAME FROM student GROUP BY NAME HAVING COUNT(*)>1
-- 年份重复查询
SELECT COUNT(*), DATE_FORMAT(birthday,'%Y') FROM player GROUP BY DATE_FORMAT(birthday,'%Y')
数据库设计范式
第一范式:列保证原子性
例如:联系方式:QQ,地址,邮箱......
第二范式要有主键,要求其他字段都依赖于主键
第三范式:要求一个数据库表中不包含已在其他表中包含的非主关键字信息
-- 修改表,添加列 ALTER TABLE player ADD senum INT
这里的senum 称为外键,外键必须与另一个表的主键相关联,且数据类型一致
添加外键约束,如果没有添加约束,数据与数据之间没有任何束缚,可以随意操作
添加外键约束后,操作时就不能随意操作,要保证数据的完整性
关联查询
含义:又称多表查询,当查询的字段来自于多个表时,就会用到连接查询
笛卡尔乘积现象:表1有m行,表2有n行,结果=m*n
发生原因:没有有效的连接条件
如何避免:添加有效的连接条件
按功能分类:
1.自连接
2.内连接
3.外连接
左外连接:将左边的表中的全部列都列出来,右边的只列出满足条件的
右外连接:将右边的表中的全部列都列出来,左边的只列出满足条件的
子查询
含义:出现在其他语句中的select语句,称为子查询或内查询;外部的查询语句,称为主查询或外查询.
在一个查询语句中又出现了查询语句
分类:
按子查询出现的位置:
from后面:支持表子查询
where:支持标量子查询,列子查询
按功能、结果集的行列数不同:
标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
表子查询(结果集一般为多行多列