DAY2:MySQL进阶

一.数据库三范式

讲解:MySQL数据库范式

三范式的作用：

约束数据库建表的规范性。

三范式的最终目标：

不存在冗余数据

第一范式：

不要向表中输入完全重复的数据（设置主键）

下面哪些字段可以作为主键

学号	email	姓名	年龄
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. 插入多条

查：

     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	输出排序顺序