一.数据库三范式
讲解:MySQL数据库范式
三范式的作用:
约束数据库建表的规范性。
三范式的最终目标:
不存在冗余数据
第一范式:
不要向表中输入完全重复的数据(设置主键)
下面哪些字段可以作为主键
学号 | | 姓名 | 年龄 |
023145 | zhangs@xdf.com | 张三 | 23 |
023146 | lisi@xdf.com | 李四 | 24 |
023147 | wangw@xdf.com | 王五 | 25 |
… |
|
|
|
023258 | zhaol@xdf.com | 赵六 | 25 |
1.在多个字段可以被选择的情况下,作为主键的字段应该选择最符合逻辑的一个,一般选择 与业务无关的字段,比如自增的Id
2.由于效率的关系,请尽量选择一个数值类型的字段或者定长字符串。
第二范式:
存在多对多关系时只有一个字段作为主键是不够的。
部分依赖,会产生冗余数据,需要分解表
学号 | 学生姓名 | 教师编号 | 教师姓名 |
023145 | 张三 | 988010 | 张老师 |
023146 | 李四 | 988010 | 张老师 |
023147 | 王五 | 988011 | 李老师 |
023145 | 张三 | 988011 | 李老师 |
023258 | 赵六 | 988010 | 张老师 |
学生教师关系表:
学号 | 教师编号 |
023145 | 988010 |
023145 | 988011 |
023147 | 988011 |
023146 | 988010 |
023258 | 988010 |
学生信息表:
学号 | 学生姓名 |
023145 | 张三 |
023146 | 李四 |
023147 | 王五 |
023258 | 赵六 |
教师信息表:
教师编号 | 教师姓名 |
988010 | 张老师 |
988011 | 李老师 |
第三范式:
班级名称和班级信息这一组值没有必要的重复出现,这些值并不直接依赖于学号(主键),依赖于班级号,也就是时说表中出现了传递依赖
学号(主键) | 学生姓名 | 班级编号 | 班级名称 | 班级信息 |
023145 | 张三 | 987654 | 4班 | 普通班 |
023146 | 李四 | 987231 | 3班 | 特招班 |
023147 | 王五 | 987654 | 4班 | 普通班 |
… |
|
|
|
|
023258 | 赵六 | 987654 | 4班 | 普通班 |
学生信息表:
学号(主键) | 学生姓名 | 班级编号 |
023145 | 张三 | 987654 |
023146 | 李四 | 987231 |
023147 | 王五 | 987654 |
… |
|
|
023258 | 赵六 | 987654 |
班级信息表:
班级编号 | 班级名称 | 班级信息 |
987631 | 3班 | 特招班 |
987654 | 4班 | 普通班 |
二.增查小细节
增:
- 插入全部列
- 插入部分列
- 插入多条
查:
1.查询所有行所有列
2.查询部分列所有行
3.查询部分行所有列
4.表别名(两种方式)
语法:select column from table as table_alias
5.字段别名(两种方式)
语法:字段名 [as] 别名;
6.去除重复数据 distinct
示例:select distinct deptno from emp;
三.查询语法详解
where
条件比较
=,!=,<>,<,>,<=,>=,any,some,all
示例讲解:
all
SELECT count(*) FROM world.country WHERE Name<>all(SELECT Name FROM world.country where Name like "C%");
与子集中的每一个元素
比如:
val>ALL(subset):表示val比subset中每一个元素都大
val<>ALL(subset):表示val不在subset中,等效于NOT IN (subset)
any,some
SELECT count(*) FROM world.country WHERE Name=any(SELECT Name FROM world.country where Name like "C%");
ANY表示任一个,SOME是ANY的变体,含义相同
比如:
val=ANY(subset):表示val等于subnet子集中的任何一个元素皆可,等同于val IN (subnet)
val>ANY(subnet):表示val比subnet中最小的元素大即可
is null,is not null
between x and y
in(list),not in(list)
like _ 代表一个字符,%代表多个字符(模糊查询)
逻辑复合条件
and,or
备份表数据
第一步:备份表结构(包含相关键,B预先不存在)
CREATE TABLE B_Table LIKE A_Table
第二步:备份表结据(把表A数据备份到表B中,B表结构与A结构一样)
INSERT INTO B_Table SELECT * FROM A_Table;
Order by(排序)
按照单个列排序
order by col
降序和升序:
order by col desc (asc)
按多个列排序(那么排序顺序是什么呢?)
order by col1 desc(asc), col2 desc(asc) (依次按照字规则排序)
创建计算字段
Sql允许select子句中出现+,-,*,/以及列名和常数的表达式
!!!!!计算字段并不实际存在于数据库表
四.函数
函数一般是在数据上执行的,它给数据的转换和处理提供了方便。只是将取出的数据进行处理,不会改变数据库中的值。
Sql函数可以分为组函数和单行函数。
组函数又被称作聚合函数,用于对多行数据进行操作,并返回一个单一的结果,组函数仅可用于选择列表或查询的having子句
单行函数对单个数值进行操作,并返回一个值。
单行函数分类:字符串函数,数字函数,日期函数,转换函数,其他函数
1.字符串函数
if(条件,值1,值2)
Length()字符串字节长度
Char_length():字符串字符长度
INSERT(str,pos,len,newstr) 示例:SELECT INSERT('情侣在聊天',3,0,'XXXXX');//(源字符串,起始位置,替换长度(为0不替换),替换内容
SUBSTRING(str FROM pos)
2.数字函数
3.日期函数
1、NOW 获取当前日期和时间的函数 语法: NOW() 例如:select NOW();
2、CURDATE 获取当前的日期 语法:CURDATE()
3、CURTIME() 获取当前时间 语法:CURTIME()
4、DATE 获取日期时间或者日期的日期部分 语法:DATE(date) date 参数是合法的日期表达式。
5、EXTRACT 获取返回日期/时间的单独部分 比如年、月、日、小时、分钟等等。语法:EXTRACT(unit FROM date) date 参数是合法的日期表达式。
unit 参数可以是下列的值:MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR、SECOND_MICROSECOND、MINUTE_MICROSECOND、MINUTE_SECOND、HOUR_MICROSECOND、HOUR_SECOND、HOUR_MINUTE、DAY_MICROSECOND、DAY_SECOND、DAY_MINUTE、DAY_HOUR、YEAR_MONTH
例如获取当前年月:select EXTRACT(YEAR_MONTH FROM now()) ;
6、DATE_ADD 将日期添加指定的时间间隔 语法:DATE_ADD(date,INTERVAL expr type)
date 参数是合法的日期表达式。expr 参数是添加的时间间隔。
type 参数可以是下列值:MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR、SECOND_MICROSECOND、MINUTE_MICROSECOND、MINUTE_SECOND、HOUR_MICROSECOND、HOUR_SECOND、HOUR_MINUTE、DAY_MICROSECOND、DAY_SECOND、DAY_MINUTE、DAY_HOUR、YEAR_MONTH
例如:select DATE_ADD(now(),INTERVAL 2 DAY); //获取两天后的时间
7、DATE_SUB 将日期减去指定的时间间隔 语法:DATE_SUB(date,INTERVAL expr type)
date 参数是合法的日期表达式。expr 参数是添加的时间间隔。
type 参数可以是下列值:MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR、SECOND_MICROSECOND、MINUTE_MICROSECOND、MINUTE_SECOND、HOUR_MICROSECOND、HOUR_SECOND、HOUR_MINUTE、DAY_MICROSECOND、DAY_SECOND、DAY_MINUTE、DAY_HOUR、YEAR_MONTH
例如:select DATE_SUB(now(),INTERVAL 2 DAY); //获取两天前的时间
8、DATEDIFF 获取两个日期之间的天数 (只有日期部分参与计算)语法:DATEDIFF(date1,date2) date1 和 date2 参数是合法的日期或日期/时间表达式。
例如:SELECT DATEDIFF(NOW(),'2008-08-08') AS 北京奥运会多天前;
9、DATEFORMAT 格式化日期时间
语法:DATE_FORMAT(date,format) date 参数是合法的日期。format 规定日期/时间的输出格式。
格式:描述
%a:缩写星期名
%b:缩写月名
%c:月,数值
%D:带有英文前缀的月中的天
%d:月的天,数值(00-31)
%e:月的天,数值(0-31)
%f:微秒
%H:小时 (00-23)
%h:小时 (01-12)
%I:小时 (01-12)
%i:分钟,数值(00-59)
%j:年的天 (001-366)
%k:小时 (0-23)
%l:小时 (1-12)
%M:月名
%m:月,数值(00-12)
%p:AM 或 PM
%r:时间,12-小时(hh:mm:ss AM 或 PM)
%S:秒(00-59)
%s:秒(00-59)
%T:时间, 24-小时 (hh:mm:ss)
%U:周 (00-53) 星期日是一周的第一天
%u:周 (00-53) 星期一是一周的第一天
%V:周 (01-53) 星期日是一周的第一天,与 %X 使用
%v:周 (01-53) 星期一是一周的第一天,与 %x 使用
%W:星期名
%w:周的天 (0=星期日, 6=星期六)
%X:年,其中的星期日是周的第一天,4 位,与 %V 使用
%x:年,其中的星期一是周的第一天,4 位,与 %v 使用
%Y:年,4 位
%y:年,2 位
4.转换函数
了解:转换函数
5.组函数
- avg()返回某列的平均值
- min()返回某列的最小值
- max()返回某列的最大值
- sum()返回某列值的和
- count()返回某列的行数
- 组函数仅在选择列表和Having子句中有效
- 组函数除了count(*)外,都跳过空值而处理非空值
- select count(*) from emp;
- select count(comm) from emp;
- ifnull(参数,值) 如果参数是null 显示出来的值 就是函数第二个参数
组函数一般结合分组使用
- group by 子句
- group by 子句可以包含任意数目的列。
- 除组函数语句外,select语句中的每个列都必须在group by 子句中给出。(oracle特性,mysql不是)
- 如果分组列中具有null值,则null将作为一个分组返回。如果列中有多行null值,他们将分为一组。
- Group by 子句必须出现在where子句之后,order by 子句之前。
- 过滤分组(having子句)
- Where过滤行,having过滤分组。
- Having支持所有where操作符(写在group后面)。
- where 后面不能直接跟组函数
- 语法:
-
SELECT distinct column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression Having ….] [ORDER BY column]; 使用GROUP BY子句将表分成小组 结果集隐式按升序排列,如果需要改变排序方式可以使用Order by 子句
出现在SELECT列表中的字段,如果出现的位置不是在组函数中,那么必须出现在GROUP BY子句中不能在 WHERE 子句中使用组函数.不能在 WHERE 子0句中限制组. 使用Having 对分组进行限制
关键字:
子句 | 说明 |
select | 要返回的列或表达式 |
from | 从中检索数据的表 |
where | 行级过滤 |
group by | 分组说明 |
Having | 组级过滤 |
order by | 输出排序顺序 |