数据库学习笔记2

DQL

DQL（Data Query Language）数据查询语言，查询是使用频率最高的一个操作，可以从一个表中查询数据，也可以从多个表中查询数据。

基础查询

语法：
select 查询列表 from 表名;
特点：
查询列表可以是：表中的字段、常量、表达式、函数
查询的结果是一个虚拟的表格

查询结果处理

特定列查询:select column1,column2 from table
全部列查询: select * from table
算数运算符:+ - * /
排除重复行: select distinct column1,column2 from table
查询函数：select 函数; / 例如version()

算术运算符只可以作用于数值型的列
concat(列/字符串，列/字符串) 用来连接字符串

SELECT NAME FROM student
SELECT * FROM student
SELECT idnumber*2-3 FROM student
SELECT CONCAT(NAME,':',"我是傻逼") FROM student
SELECT NAME FROM student
SELECT DISTINCT NAME FROM student

函数

类似于java中的方法，将一组逻辑语句事先在数据库中定义好,可以直接调用
分类：
单行函数：如concat、length、ifnull等
分组函数：做统计使用，又称为统计函数、聚合函数、组函数

单行函数

单行函数：字符函数

length()：获取参数值的字节个数
char_length()获取参数值的字符个数
concat(str1,str2,…)：拼接字符串
upper()/lower()：将字符串变成大写/小写
substring(str,pos,length)：截取字符串 位置从1开始
instr(str,指定字符)：返回子串第一次出现的索引，如果找不到返回0
trim(str)：去掉字符串前后的空格或子串,trim(指定子串 from 字符串)
lpad(str,length,填充字符)：用指定的字符实现左填充将str填充为指定长度
rpad(str,length,填充字符)：用指定的字符实现右填充将str填充为指定长度
replace(str,old,new)：替换，替换所有的子串

-- 单行函数类

-- 字符函数：

-- （所有函数后可加AS 新字段名 来重命名）

-- length()：获取参数值的字节个数
SELECT LENGTH(NAME) FROM stu
SELECT LENGTH(idnumber) FROM stu

-- char_length()获取参数值的字符个数
SELECT CHAR_LENGTH(NAME) FROM stu

-- concat(str1,str2,.....)：拼接字符串
SELECT CONCAT(NAME,"是个",gender,"的还是个傻逼") FROM stu

-- upper()/lower()：将字符串变成大写/小写
SELECT UPPER(NAME) FROM stu
SELECT LOWER(NAME) FROM stu

-- substring(str,pos,length)：截取字符串 位置从1开始
SELECT SUBSTRING(NAME,2,2) FROM stu

-- instr(str,指定字符)：返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR(NAME,'球') FROM stu

-- trim(str)：去掉字符串前后的空格或子串,trim(指定子串 from 字符串)
SELECT TRIM(NAME) FROM stu
SELECT TRIM('球' FROM NAME) FROM stu

-- lpad(str,length,填充字符)：用指定的字符实现左填充将str填充为指定长度
SELECT LPAD(NAME,10,'傻逼') FROM stu

-- rpad(str,length,填充字符)：用指定的字符实现右填充将str填充为指定长度
SELECT RPAD(NAME,8,12) FROM stu

-- replace(str,old,new)：替换，替换所有的子串
SELECT REPLACE(height,1,6) FROM stu

单行函数：逻辑处理

case when 条件 then 结果1 else 结果2 end; 可以有多个when
ifnull(被检测值,默认值)函数检测是否为null，如果为null，则返回指定的值，否则返回
原本的值
if函数：if else的 效果 if(条件,结果1,结果2)

-- 逻辑处理：

-- case when 条件 then 结果1 else 结果2 end; 可以有多个when
-- 注意逻辑&在SQL是AND
SELECT (CASE WHEN height<2 THEN "不是大高个子" ELSE "大高个子" END) FROM stu
SELECT (CASE WHEN height<1.8 THEN '矮' WHEN height>=1.8 AND height<2 THEN '高' ELSE '巨高' END)AS height FROM stu

-- ifnull(被检测值,默认值)函数检测是否为null，如果为null，则返回指定的值，否则返回原本的值
SELECT IFNULL(birthday,'死人') FROM stu

-- if函数：if else的 效果 if(条件,结果1,结果2)
SELECT IF(height=1.55,'牛','不牛') FROM stu

单行函数：数学函数

round(数值)：四舍五入
ceil(数值)：向上取整，返回>=该参数的最小整数
floor(数值)：向下取整，返回<=该参数的最大整数
truncate(数值,保留小数的位数)：截断，小数点后截断到几位
mod(被除数,除数)：取余，被除数为正，则为正；被除数为负，则为负
rand()：获取随机数，返回0-1之间的小数

-- 数学函数：

-- round(数值)：四舍五入
SELECT ROUND(height) FROM stu

-- ceil(数值)：向上取整，返回>=该参数的最小整数
SELECT CEIL(height) FROM stu

-- floor(数值)：向下取整，返回<=该参数的最大整数
SELECT FLOOR(height) FROM stu

-- truncate(数值,保留小数的位数)：截断，小数点后截断到几位
SELECT TRUNCATE(height,1) FROM stu

-- mod(被除数,除数)：取余，被除数为正，则为正；被除数为负，则为负
SELECT MOD(height,2) FROM stu

-- rand()：获取随机数，返回0-1之间的小数
SELECT RAND(height)AS number FROM stu

单行函数：日期函数

now()：返回当前系统日期+时间
curdate()：返回当前系统日期，不包含时间
curtime()：返回当前时间，不包含日期
可以获取指定的部分，年、月、日、小时、分钟、秒
YEAR(日期列),MONTH(日期列),DAY(日期列) , HOUR(日期列) ,MINUTE(日期列) SECOND(日期列)
str_to_date：将日期格式的字符转换成指定格式的日期
date_format：将日期转换成字符串
datediff(big,small)：返回两个日期相差的天数

-- 日期函数

-- now()：返回当前系统日期+时间
SELECT NOW()

-- curdate()：返回当前系统日期，不包含时间
SELECT CURDATE()

-- curtime()：返回当前时间，不包含日期
SELECT CURTIME()

-- 可以获取指定的部分，年、月、日、小时、分钟、秒 YEAR(日期列),MONTH(日期列),DAY(日期列) , HOUR(日期列) ,MINUTE(日期列) SECOND(日期列)
SELECT YEAR(reg_time),MONTH(reg_time),DAY(reg_time),HOUR(reg_time),MINUTE(reg_time),SECOND(reg_time) FROM stu

-- datediff(big,small)：返回两个日期相差的天数
SELECT DATEDIFF(NOW(),birthday) FROM stu

-- str_to_date(str,format)将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE


-- date_format(str,format)将日期转换成字符串，也就是将date
SELECT DATE_FORMAT(birthday,'%Y年%m-%d') FROM stu
SELECT DATE_FORMAT(reg_time,'%i-%s') FROM stu

日期格式

分组函数

功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：sum 求和、avg 平均值、max 最大值、min 最小值、count 计数
（非空）
1.sum，avg一般用于处理数值型max，min，count可以处理任何类型
2.以上分组函数都忽略null值
3.count函数的一般使用**count（*）**用作统计行数
4.和分组函数一同查询的字段要求是group by后的字段

-- 分组函数类

-- 功能：用作统计使用，又称为聚合函数或统计函数或组函数
-- 分类：sum、avg、max、min、count 这五大类全部忽略不计入(NULL)
-- sum和avg一般处理数值型，max、min、count可以处理任何类型
SELECT SUM(idnumber) FROM stu
SELECT AVG(idnumber) FROM stu
SELECT MAX(height) FROM stu
SELECT MIN(height) FROM stu
SELECT COUNT(*) FROM stu
SELECT COUNT(birthday) FROM stu
SELECT COUNT(idnumber) FROM stu

条件查询

使用WHERE 子句，将不满足条件的行过滤掉，WHERE 子句紧随 FROM 子句。
语法:select <结果> from <表名> where <条件>
比较
=, != 或<>, >, <, >=, <=
逻辑运算
and 与
or 或
not 非

-- 条件查询
-- 使用WHERE 子句，将不满足条件的行过滤掉，WHERE 子句紧随 FROM 子句.语法:select <结果> from <表名> where <条件>

-- 比较：=, != 或<>, >, <, >=, <=
SELECT * FROM stu WHERE idnumber = 1
SELECT * FROM stu WHERE idnumber <> 1

-- 逻辑运算 and 与 or 或 not 非
SELECT * FROM stu WHERE gender = '男' AND height = 1.55
SELECT * FROM stu WHERE gender = '女' OR NAME = '我嚓'
-- 使用not时搭配in()使用
SELECT * FROM stu WHERE height IN(1.55,1.44)
SELECT * FROM stu WHERE height NOT IN(1.55,1.44)

条件查询：模糊查询

LIKE ：是否匹配于一个模式 一般和通配符搭配使用，可以判断字符型数值或数值型.
通配符： % 任意多个字符，包含0个字符 _ 任意单个字符
between and 两者之间,包含临界值；
in 判断某字段的值是否属于in列表中的某一项
IS NULL（为空的）或 IS NOT NULL（不为空的）

-- 模糊查询

-- LIKE ：是否匹配于一个模式 一般和通配符搭配使用，可以判断字符型数值或数值型. 通配符： % 任意多个字符，包含0个字符 _ 任意单个字符
SELECT * FROM stu WHERE NAME LIKE '王%'
SELECT * FROM stu WHERE NAME LIKE '%毛%'
SELECT * FROM stu WHERE NAME LIKE '埃__'

-- between and 两者之间,包含临界值；
SELECT * FROM stu WHERE height BETWEEN 1.44 AND 2.22

-- in 判断某字段的值是否属于in列表中的某一项
SELECT * FROM stu WHERE height IN(-1.78,1.44,2.22)

-- IS NULL（为空的）或 IS NOT NULL（不为空的）
SELECT * FROM stu WHERE birthday IS NOT NULL
SELECT * FROM stu WHERE address IS NULL

union和union all

-- 当使用union 时，mysql 会把结果集中重复的记录删掉，而使用union all ，mysql 会把所有的记录返回，且效率高于union 。
/*1、UNION 的语法如下：
[SQL 语句 1]
UNION
[SQL 语句 2]
*/
SELECT NAME,gender FROM stu WHERE gender = '女'
UNION
SELECT NAME,gender FROM stu WHERE gender = '男'
UNION
SELECT NAME,gender FROM stu WHERE gender = '男'

/*2、UNION ALL 的语法如下：
[SQL 语句 1]
UNION ALL
[SQL 语句 2]
*/
SELECT NAME,gender FROM stu WHERE gender = '女'
UNION ALL
SELECT NAME,gender FROM stu WHERE gender = '男'
UNION ALL
SELECT NAME,gender FROM stu WHERE gender = '男'
UNION ALL
SELECT NAME,gender FROM stu WHERE gender = '男'

排序

查询结果排序,使用 ORDER BY 子句排序 order by 排序列 ASC/DESC
asc代表的是升序，desc代表的是降序，如果不写，默认是升序
order by子句中可以支持单个字段、多个字段

-- 排序 查询结果排序,使用 ORDER BY 子句排序 order by 排序列 ASC/DESC 
-- asc代表的是升序，desc代表的是降序，如果不写，默认是升序
-- order by子句中可以支持单个字段、多个字段
SELECT NAME,height,idnumber  FROM stu ORDER BY height DESC,idnumber ASC

数量限制

limit子句：对查询的显示结果限制数目 (sql语句最末尾位置)
SELECT * FROM table LIMIT offset rows;
SELECT * from table LIMIT 0,5;

-- 数量限制 limit子句：对查询的显示结果限制数目 (sql语句最末尾位置)
-- SELECT * FROM table LIMIT offset rows; SELECT * from table LIMIT 0,5;
-- select * from 表名 limit x,y
-- x是跳过几个记录，y是跳过后需要几个记录，最终显示的就是跳过x条记录后的y条个记录
SELECT * FROM stu LIMIT 0,3
SELECT * FROM stu LIMIT 1,2

分组查询

语法：
select 分组函数，列（要求出现在group by的后面）
from 表
[where 筛选条件]
group by 分组的列表
[having 分组后的筛选]
[order by 子句]
注意：查询列表比较特殊，要求是分组函数和group by后出现的字段
分组查询中的筛选条件分为两类:
数据源 源位置 关键字
分组前筛选 原始表 group by子句的前面 where
分组后筛选 分组后的结果集 group by的后面 having

-- 分组查询
/*语法：
select 分组函数，列（要求出现在group by的后面）
from 表
[where 筛选条件]
group by 分组的列表
[having 分组后的筛选]
[order by 子句]
*/
SELECT gender,AVG(height)h FROM stu GROUP BY gender
SELECT gender,AVG(height)h FROM stu GROUP BY gender HAVING h > 1.8

-- 执行顺序：from——where(紧跟from语句之后)——group by——having——select——order by——limit x,y

注意：group by 后要列出所有select后的原列名

多表设计——关联查询

为什么需要多表关联
数据冗余-----拆分

数据库设计范式

● 为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。
● 目前关系数据库有5种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。
● 满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般来说，数据库只需满足第三范式(3NF）就行了。

第一范式(确保每列保持原子性)

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

第二范式就是要有主键

要求其他字段都依赖于主键。
没有主键就没有唯一性，没有唯一性在集合中就定位不到这行记录，所以要主键。
其他字段为什么要依赖于主键？因为不依赖于主键，就找不到他们。更重要的是，其他字段组成的这行记录和主键表示的是同一个东西，而主键是唯一的，它们只需要依赖于主键，也就成了唯一的。

第三范式就是要消除传递依赖

方便理解，可以看做是“消除冗余”。

外键

引用另外一个数据表的某条记录。
外键列类型与主键列类型保持一致
数据表之间的关联/引用关系是依靠具体的主键（primary key）和外键（foreign key）建立起来
的。
create table score(
cid int not null auto_increment primary key, score int, sid int, CONSTRAINT 约束名 foreign key(sid) references student(sid)
);
约束名规则:
例：FK_ForeignTable_PrimaryTable_On_ForeignColumn

-- 外键(foreign key)，引用出另一个表的字段，并且和另一个表的主键数据类型必须一致！
-- 给学生表添加专业列
ALTER TABLE stu ADD COLUMN majorid INT

-- 给外键majorid设置强关联
ALTER TABLE stu ADD CONSTRAINT stu_major_majorid_fk FOREIGN KEY(majorid) REFERENCES major(id)

1、当主表中没有对应的记录时，不能将记录添加到从表
2、不能更改主表中的值而导致从表中的记录孤立
3、从表存在与主表对应的记录，不能从主表中删除该行
4、删除主表前，先删从表