数据库（MySQL）
一、数据库概述
1.为什么学习数据库
实现数据持久化到本地；
使用完整的管理系统统一管理，可以实现结构化查询，方便管理；
2.数据库存在的意义
为了方便数据的存储和管理，它将数据按照特定的 规则存储在磁盘上,就是一个存储数据的仓库。通过数据库管理系统，可 以有效的组织和管理存储在数据库中的数据
3.数据库相关概念
DB：数据库（DataBase）
存储数据的容器，它保存了一系列有组织的数据。
DBMS：数据库管理系统（DataBase Management System）
又称为数据库软件或数据库产品，用于创建或管理DB。
SQL：结构化查询语言（Structure Query Language）
用于和数据库通信的语言，不是某个数据库软件特有的，而是几乎所有的 主流数据 库软件通用的语言
4.数据库工作图解
5.数据库分类
根据不同的存储类型可以分为：
- 关系型数据库 ：以表为单位，表之间存在关系
关系型数据库管理系统称为RDBMS，R指Relation
Oracle:功能强大,收费. （oracle全球最大的企业级软件公司）
MySQL 快捷、可靠 开源、免费 （也属于oracle）
SQL Server(微软): 只能安装在Windows操作系统
DB2 (IBM):适合处理海量数据,收费.
-
非关系型数据库
键=值
MongdoDB
Redis
二、MySQL数据库
-
MySQL是一个关系型数据库管理系统**，**由瑞典MySQL AB 公司开发，目 前属于 Oracle旗下产品。MySQL 流行的关系型数据库管理系统。
-
MySql是一种关系数据库管理系统。
-
MySql软件是一种开放源码软件,你可以修改源码来开发自己的 Mysql 系统。
-
MySql数据库服务器具有快速、可靠和易于使用的特点。
-
MySql使用标准的sql语言,并且支持多种操作系统,支持多种语言.
-
mysql商业版与社区版 MySQL商业版是由MySQL AB公司负责开发与维护,需要付费才能使用
-
MySQL社区版是由分散在世界各地的MySQL开发者、爱好者一起开发与维 护，可以免费使用
1.安装（参考我之前写的数据库安装）
2.命令方式连接MySQL
● 命令行方式连接mysql （用dos窗口）
登录：mysql [-hlocalhost -P3306]（本机可省略） -uroot -p（可以直 接写密码，不能有空格）
-h：主机名
-P：端口号
-u：用户名
-p：密码
退出：exit
3.MySQL的常用命令
MySQL的常用命令
- 查看mysql数据库的版本 select version();（mysql命令）
- 查看当前所有的数据库：show databases;
- 选择指定的库：use 库名
- 查看当前的所有表：show tables;
- 查看其他库的所有表：show tables from 库名;
（用可视化工具操作这些常用命令都是一眼的）
4.MySQL语法规范
MySQL语法规范
- 不区分大小写，建议关键字大写，表名、列名小写
- 每句话用;
- 注释
- 单行注释：#注释文字
- 单行注释：-- 注释文字（要有空格）
- 多行注释：/* 注释文字 */
在程序应用中SQL语句嵌套在代码中
三、sql（结构化查询语言）【重要】
结构化查询语言(Structured Query Language)简称SQL，是一种特殊 目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以 及查询、更新和管理关系数据库系统
SQL优点：
不是某个特定数据库供应商专有的语是言，几乎所有DBMS都支持SQL 简单易学 实际上强有力的语言，灵活使用可以进行非常复杂和高级的数据库操作
sql是脚本语言（不需要编译直接通过某种解释器解释执行）
–sql脚本
1.DDL
- 数据(结构)定义语言DDL(Data Definition Language)，是用于创 建和修改数据库表结构的语言。
(1)常用的语句：
create(创建) ,alter（修改）,drop（删除）,rename（重命名）
创建,删除数据库
- 创建数据库并设置编码格式
CREATE DATABASE [if not exists] 数据库名 [ CHARSET utf8]
- 删除数据库
DROP DATABASE 数据库名 / [IF EXISTS数据库名];
- 修改字符集
ALTER DATABASE 数据库名 CHARSET gbk;
-- 创建数据库
CREATE DATABASE school_db
-- 如果数据库不存在则创建数据库
CREATE DATABASE IF NOT EXISTS schoole_db
-- 如果数据库不存在创建数据库并指定编码
CREATE DATABASE IF NOT EXISTS schoolf_db CHARSET utf8
-- 删除指定数据库
DROP DATABASE school_db
-- 修改指定数据库编码
ALTER DATABASE schoole_db CHARSET gbk
(2)数据库存储数据的特点
将数据放到表中，表再放到库中
一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表名具有 唯一性
表具有一些特性，这些特性定义了数据在表中如何存储，类似Java中“类”的 设计
表由列组成，我们也称列为字段。所有表都是由一个或多个列组成的，每一列 类似java中的”属性”
表中的数据是按行存储的，每一行类似于Java中的“对象
（3）数据库表的基本概念
1.数据表**（相当于java中的类）**
表（table）是数据存储的最常见和最简单的形式，是构成关系型数据库的基本元素。 表的最简单形式是由行和列组成，分别都包含着数据。 每个表都有一个表头和表体，表头定 义表名和列名 .表中的行被看作是文件中的记录，表中的列被看作是这些记录的字段
2.记录**（相当于java中的一个对象）**
记录也被称为一行数据，是表里的一行。在关系型数据库的表里，一行数据是指一条完 整的记录。
3.字段**（相当于类中的属性）**
字段是表里的一列，用于保存每条记录的特定信息。如客户订单表的字段包括“订单 ID”、“姓名”、“客户ID”、“职务”、“上级”、“地区”、“运货商”、“国家”等。 数据表的一列包含了特定字段的全部信息。
一个数据库表只能储存一类信息
（4）设计表
对于具体的某一个表，在创建之前，需要确定表的下列特征：
- 表名(表信息)
- 表中的字段
- 字段的数据类型和长度
- 哪些约束
1）数据类型
char（n） 长度为n的定长字符串
varchar(n） 最大长度为n的可变长字符串
date （日期） 年月日
datetime**(时间)** 年月日 时分秒
整数
浮点
BLOB（4种） 是一个二进制大对象，可以容纳可变数量的数据，用于存储图片视频信息
TINYBOLB BLOB MEDIUMBOLB LONGBLOB 它们只是可容纳的最大长度值不同
TEXT列字符字符串 （4种）
TINYTEXT TEXT MEDIUMTEXT LONGTEXT
它们（TEXT和BLOB同）的长度：
Tiny：最大长度255个字符(2^8-1)
BLOB或TEXT：最大长度65535(2^16-1)
Medium：最大长度16777215(2^24-1)
LongText 最大长度4294967295(2^32-1)
2）约束
主键：
在一张表中代表唯一的一条记录,不能为空,不能重复
约束:
-
设置主键约束 RIMARY KEY 不能为空,不能重复, 一个表中只能有一个主键约束
-
不能为空约束 NOT NULL 一个表中可以有多个
-
唯一性约束 UNIQUE 一个表中可以有多个
-
检查约束 设置条件 设置条件 例如age>=18
-
外键约束 多表关联时使用
-
主键自动增长 AUTO_INCREMENT
-
默认值 DEFAULT default_value
-
字段注释: comment ‘注释’
3）创建表语法
一个案例
CREATE TABLE t_studnet(
num INT PRIMARY KEY AUTO_INCREMENT COMMENT '学号', -- 添加了主键约束，主键自动增长 还有字段注释
NAME VARCHAR(4) NOT NULL COMMENT'名字', -- 添加了不能为空约束 字段注释
sex CHAR(1) NOT NULL DEFAULT'男' COMMENT'性别' , -- 添加了不能为空约束 默认值 字段注释
birthday DATE,
height FLOAT(4,1) NOT NULL CHECK(height>100.0), -- 添加了不能为空约束 检查约束
phont CHAR(11),
register_time DATETIME
)
-- 删除数据库表
DROP TABLE t_studnet
4）常用DDL语法（单表）
删除表，修改表名
-
删除表 DROP TABLE [if exists ]表名
-
修改表名 RENAME TABLE 旧表名 TO 新表名
-
复制表结构 CREATE TABLE 新表名 LIKE 被复制表名;
修改表结构
- 添加主键约束
ALTER TABLE 表名 ADD PRIMARY KEY (列名)
ALTER TABLE 表名 DROP PRIMARY KEY
- 设置自动增长
ALTER TABLE 表名 MODIFY 列名 类型 AUTO_INCREMENT ;
删除自动增长
ALTER TABLE 表名 MODIFY 列名 类型 ;
- 设置不能为空
ALTER TABLE 表名 MODIFY 列名 类型 NOT NULL ;
ALTER TABLE 表名 MODIFY 列名 类型 N U L L ;
- 添加唯一约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE(列名)
ALTER TABLE 表名 DROP INDEX 约束名;
- 添加检查约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK(条件)
ALTER TABLE 表名 DROP CHECK 约束名
- 添加列
ALTER TABLE 表名 ADD 列名 数据类型
ALTER TABLE 表名 ADD 列名 数据类型 FIRST
ALTER TABLE 表名 ADD 列名 数据类型 AFTER 列名
- 删除列
ALTER TABLE 表名 DROP 列名
- 修改列名
ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型
- 修改列的数据类型
ALTER TABLE 表名 MODIFY 列名 新数据类型
-- 先创建一个基本的表 不涉及约束
-- 学生基本信息表 学号,姓名,性别,生日,身高,电话,登记时间
-- 用户表,角色,... sys_user sys_role hr_ cw
CREATE TABLE t_student(
num INT,
NAME VARCHAR(4),
sex CHAR(1),
birthday DATE,
height FLOAT(4,1),
phone CHAR(11),
register_time DATETIME
)
CREATE TABLE t_student2(
num INT,
NAME VARCHAR(4),
sex CHAR(1),
birthday DATE,
height FLOAT(4,1),
phone CHAR(11),
register_time DATETIME
)
-- 删除表
-- 表名
DROP TABLE IF EXISTS t_student2
-- 修改表名
-- 旧表名 新表名
RENAME TABLE t_student TO stu
-- 添加删除主键约束
-- 表名 列名
ALTER TABLE stu ADD PRIMARY KEY(num) -- 添加
ALTER TABLE stu DROP PRIMARY KEY -- 删除
-- 设置，删除主键自动增长
-- 表名 列名
ALTER TABLE stu MODIFY num INT AUTO_INCREMENT -- 设置 只有指定的表中的列设置为主键才能设置为自动增长的
ALTER TABLE stu MODIFY num INT -- 删除
-- 设置删除不能为空
-- 表名 列名 数据类型
ALTER TABLE stu MODIFY NAME VARCHAR(4) NOT NULL -- 设置
ALTER TABLE stu MODIFY NAME VARCHAR(4) NULL -- 删除
-- 添加删除唯一约束
-- 表名 约束名（自定义） 列名
ALTER TABLE stu ADD CONSTRAINT uni_phone UNIQUE(phone) -- 添加
-- 表名 自定义的约束名
ALTER TABLE stu DROP INDEX uni_phone -- 删除
-- 添加删除检查约束
-- 表名 约束名（自定义） 条件
ALTER TABLE stu ADD CONSTRAINT check_height CHECK(height>100) -- 添加
-- 表名 自定义的约束名
ALTER TABLE stu DROP CHECK check_height -- 删除
-- 添加列
-- 表名 新添加的列名 数据类型
ALTER TABLE stu ADD weight FLOAT(5,2) -- 默认添加在最后
ALTER TABLE stu ADD age FLOAT(5,2) AFTER phone -- 把新的列添加到after后面的列的后面
ALTER TABLE stu ADD id FLOAT(5,2) FIRST -- 把新的列添加在最前面
-- 删除列
-- 表名 列名
ALTER TABLE stu DROP age
-- 修改列名
-- 表名 旧列名 新列名 数据类型
ALTER TABLE stu CHANGE phone mobile VARCHAR(11)
-- 修改列名的数据类型
-- 表名 列名 新的数据结构
ALTER TABLE stu MODIFY mobile CHAR(11)
2.DML
- 数据操纵语言DML（Data Manipulation Language）
- 常用语句： insert,delete,update
（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) 查询语句(查询的列数与插入列数匹配)
- 修改数据
UPDATE 表名 SET 列名 = ‘新值’ WHERE 条件
- 删除数据
DELETE FROM 表名 WHERE 条件
TRUNCATE TABLE 表名; 清空整张表
-- DML 数据操作语言
-- insert(插入数据),delete(删除数据),update(修改数据)
-- now() 获取数据库所在系统的当前时间
-- 方式一
INSERT INTO t_student(NAME,sex,birthday,height,mobile,reg_time)
VALUES('张三','男','2000-1-1',170.1,'13333333333',NOW())
-- 方式二
INSERT INTO t_student SET NAME='李四',sex='女',birthday='2001-1-1',mobile='135555555',
reg_time=NOW()
-- 方式三
INSERT INTO t_student(NAME,sex,birthday,height,mobile,reg_time)
VALUES('张三1','男','2000-1-1',170.1,'13333333331',NOW()),
('李四1','男','2000-1-1',170.1,'13333333332',NOW())
DROP TABLE stu
-- 复制表结构
-- 新表名 被复制的表名
CREATE TABLE stu LIKE t_student
-- 方式四
-- 复制表数据
-- 复制的表及列
INSERT INTO stu(NAME,sex,birthday,height,mobile,reg_time) -- 被复制的表及列（from前面的）
SELECT NAME,sex,birthday,height,mobile,reg_time FROM t_student
-- 修改语句
UPDATE t_student SET NAME='王五'
-- 一个或多个键值对 -- 修改时需要注意条件，不然修改时像第一种把这列的值全都修改了
UPDATE t_student SET NAME='张三',sex='女' WHERE num = 1
-- 删除语句 删除注意删除条件 (一般条件都是主键)
DELETE FROM t_student WHERE num = 1
3.DQL
DQL（Data Query Language）数据查询语言查询是使用频率最高的一个操作， 可以从一个表中查询数据，也可以从多个表中查询数据。
基础查询
- 语法：
select 查询列表 from 表名;
- 特点：
查询列表可以是：表中的字段、常量、表达式、函数 查询的结果是一个虚拟的表格，并不会对本来的表格造成改变
（1）查询结果处理
函数:类似于java中的方法，将一组逻辑语句事先在数据库中定义好,可以直接调 用
-
好处： 隐藏了实现细节 提高代码的重用性
-
调用：select 函数名(实参列表) [from 表];
-
查询常量值 SELECT 100;
-
查询表达式：select 100*98;
-
查询函数：select 函数; / 例如version()
-
特定列查询:select column1,column2 from table
-
全部列查询: select * from table
-
排除重复行: select distinct column1,column2 from table
-
算数运算符:+ - * /
-- 查询语句 DQL 使用频率最高的语句
-- select 查询列表(结果) from 表名;
-- select 结果 from 表名 where 条件 排序 分组 行数限制 子查询
-- 多表 关联查询
-- 查询结果进行处理
SELECT 100,NAME FROM t_student
SELECT 10*10,NAME FROM t_student
SELECT height+5,NAME FROM t_student
SELECT VERSION()
SELECT CHAR_LENGTH(NAME) FROM t_student
-- 查询所有列
SELECT * FROM t_student
-- 查询特定列
SELECT num,NAME sex FROM t_student
-- 去除重复数据，针对查询出来的结果，要求是所有列都相同
SELECT DISTINCT * FROM t_student
SELECT DISTINCT NAME FROM t_student
分类：
单行函数：如concat、length、ifnull等
分组函数：做统计使用，又称为统计函数、聚合函数、组函数
1）单行函数
字符函数
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)：替换， 替换所有的子串
-- 字符函数
-- length(列名)字节个数
SELECT num, LENGTH(NAME) FROM t_student
-- char-lenght(列名)字符个数
SELECT num,CHAR_LENGTH(NAME) FROM t_student
-- concat(str,str2,.......) 拼接字符 如果没有AS的话新列的名字就是括号里的内容 （AS（可以省略）） str起别名,不只是这里起别名都可以使用
SELECT CONCAT(NAME,':',sex) FROM t_student
SELECT CONCAT(NAME,':',sex) AS NAME FROM t_student
SELECT CONCAT(NAME,':',sex) NAME FROM t_student
-- upper(列)转大写
SELECT UPPER(NAME) FROM t_student
-- lower（列）转小写
SELECT LOWER(NAME) FROM t_student
-- substring(str,pos,lenght) str字符串 pos 开始的地方 lenght 截取的长度
SELECT SUBSTRING(NAME,2,2) FROM t_student
-- instr(str,指定字符) 返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR(NAME,'i') FROM t_student
-- trim(str) 去除前后的空格
SELECT TRIM(NAME) FROM t_student
-- trim(指定字串 from 字符串) 去掉字符串前后的空格或子串
SELECT num,TRIM('a' FROM NAME) FROM t_student
-- lpad(str,length,填充字符) 用指定的字符实现 左填充 将str填充为指定长度（lenght）
SELECT num,LPAD(NAME,4,'c') FROM t_student
-- rpad(str,length,填充字符) 用指定的字符实现 右填充 将str填充为指定长度（lenght）
SELECT num,LPAD(NAME,4,'c') FROM t_student
-- replace(str,old,new) 替换所有子串
SELECT num,REPLACE(NAME,'三','王') FROM t_student
逻辑处理
-
case when 条件 then 结果1 else 结果2 end; 可以有多个when
-
ifnull(被检测值,默认值)函数检测是否为null，如果为null，则返回指定的值，否则返回原 本的值
-
if函数：if else的效果 if(条件,条件成立,条件不成立)
-- 逻辑处理
-- case when 条件 then 结果1 else 结果2 end; 可以有多个when
SELECT num,
NAME,
CASE WHEN weight>=150.0 THEN '偏胖'
WHEN weight<=150.0 AND weight>=120.0 THEN '正常'
ELSE '好好改变体重吧' END
FROM t_student
SELECT num,
NAME,
( CASE WHEN weight>=150.0 THEN '偏胖'
WHEN weight<=150.0 AND weight>=120.0 THEN '正常'
ELSE '好好改变体重吧' END)weight
FROM t_student
-- ifnull(被检测值,默认值)函数检测是否为null，如果为null，则返回指定的值，否则返回原 本的值
SELECT IFNULL(birthday,'暂未注册')birthday FROM t_student
-- if函数： if(条件,条件成立,条件不成立)
SELECT NAME,IF(height<175 AND height>170,'中等','高个或低个')height FROM t_student
数学函数
round(数值)： 四舍五入
ceil(数值)： 向上取整，返回>=该参数的最小整数
floor(数值)： 向下取整，返回<=该参数的最大整数
truncate(数值,保留小数的位数)： 截断，小数点后截断到几位
mod(被除数,除数)： 取余，被除数为正，则为正；被除数为负，则为负
rand()： 获取随机数，返回0-1之间的小数
日期函数
now()： 返回当前系统日期+时间
curdate()： 返回当前系统日期，不包含时间
curtime()： 返回当前时间，不包含日期
可以获取指定的部分，年、月、日、小时、分钟、秒
YEAR(日期),MONTH(日期),DAY(日期) ,HOUR(日期) ,MINUTE(日期) SECOND(日期)
str_to_date ：将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE(‘1998-3-2’, ‘%Y-%m-%d’);
date_format：将日期转换成字符串
SELECT DATE_FORMAT(NOW(), ‘%y年%m月%d日)’) ;
ddatediff(big,small)： 返回两个日期相差的天数
-- 日期函数
-- now() 获取当前系统时间
SELECT num,NAME,NOW() FROM t_student
-- curdate() 返回当前系统日期 年月日
SELECT num,NAME,CURDATE() FROM t_student
-- curtime() 获取当前时间不包含日期 时分秒
SELECT num,NAME,CURTIME() FROM t_student
-- 可以获取指定的部分，年、月、日、小时、分钟、秒 YEAR(日期),MONTH(日期),DAY(日期) ,HOUR(日期) ,MINUTE(日期) SECOND(日期)
SELECT num,NAME,YEAR(birthday),MONTH(birthday),DAY(birthday),HOUR(NOW()) FROM t_student
-- str_to_date(str,date) 将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE('2000-1-1','%Y-%m-%d')
-- date_format(date,str) 将日期转换成指定的日期字符串格式
SELECT DATE_FORMAT(birthday,'%Y-%m') FROM t_student
-- datediff(big,small） 返回两个日期相差的天数
SELECT DATEDIFF(NOW(),birthday) FROM t_student
2）分组函数
功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：sum 求和、avg 平均值、max 最大值、min 最小值、count 计数 （非空）
1.sum，avg一般用于处理数值型max，min，count可以处理任何类型
2.以上分组函数都忽略null值
3.count函数的一般使用count（*）用作统计行数
4.和分组函数一同查询的字段要求是group by后的字段
-- 分组函数
-- sum（列数值），avg（列数值），max（列），min（列） count（列）
-- 统计所有
SELECT SUM(weight) FROM t_student
-- 分组统计
SELECT SUM(weight), sex FROM t_student GROUP BY sex
-- 平均值
SELECT AVG(weight)FROM t_student
-- 最大值
SELECT MAX (weight)FROM t_student
-- 最小值
SELECT MIN (weight)FROM t_student
-- 统计数量(*/主键/指定列)
SELECT COUNT(height) FROM t_student
SELECT COUNT(num) FROM t_student
SELECT COUNT(*) FROM t_student -- 在这里*和主键的作用是一样的
（2）条件查询
使用WHERE 子句，将不满足条件的行过滤掉，WHERE 子句紧随 FROM 子句。
语法:select <结果> from <表名> where <条件>
比较 =, != 或<>, >, <, >=, <=
逻辑运算 ：and 与 or 或 not 非
-- 条件查询
-- 使用WHERE 子句，将不满足条件的行过滤掉，WHERE 子句紧随 FROM 子句。
-- 语法:select <结果> from <表名> where <条件>
-- 比较=, != 或<>, >, <, >=, <=
-- 逻辑运算 and 与 or 或 not 非
-- 比较=, != 或<>, >, <, >=, <=
SELECT * FROM t_student WHERE sex='男'
SELECT * FROM t_student WHERE sex!='男'
SELECT * FROM t_student WHERE sex<>'男' -- <> 等于 !=
-- and 连接多个条件，多个条件同时成立
SELECT * FROM t_student WHERE sex='男' AND height>=170.0 AND weight>=130.0
-- or 只要成立一个条件即可
SELECT * FROM t_student WHERE sex='男' OR height>170 AND weight<=120
1）模糊查询
因为精确查询效率太低时间为了提高效率有了模糊查询
- LIKE ：是否匹配于一个模式 一般和通配符搭配使用，可以判断字符型数值 或数值型. 通配符： % 任意多个字符，包含0个字符 _ 任意单个字符
- between and 两者之间,包含临界值；
- in 判断某字段的值是否属于in列表中的某一项
- IS NULL（为空的）或 IS NOT NULL（不为空的）
-- 模糊查询
-- LIKE ：是否匹配于一个模式 一般和通配符搭配使用，可以判断字符型数值 或数值型. 通配符： % 任意多个字符，包含0个字符 _ 任意单个字符
-- between and 两者之间,包含临界值；
-- in 判断某字段的值是否属于in列表中的某一项
-- IS NULL（为空的）或 IS NOT NULL（不为空的）
-- LIKE 是否匹配于一个模式和通配符搭配使用 %任意个字符 _单个字符
SELECT * FROM t_student WHERE NAME LIKE 'j%'
SELECT * FROM t_student WHERE NAME LIKE '%j%'
SELECT * FROM t_student WHERE NAME LIKE '_j_'
-- between and 在两者之间包含临界值
SELECT * FROM t_student WHERE height BETWEEN 165 AND 180
-- not is in
SELECT * FROM t_student WHERE height IN(165,175)
SELECT * FROM t_student WHERE height IS NULL
SELECT * FROM t_student WHERE height IS NOT NULL
（3）合并（UNION）
- UNION 的语法如下：
[SQL 语句 1]
UNION
[SQL 语句 2]
- UNION ALL 的语法如下：
[SQL 语句 1]
UNION ALL
[SQL 语句 2]
当使用union 时，mysql 会把结果集中重复的记录删掉，而使用union all ，mysql 会把所有的记录返回，且效率高于union 。
-- UNION ALL 的语法如下：
/* [SQL 语句 1]
UNION ALL
[SQL 语句 2]*/
-- 当使用union 时，mysql 会把结果集中重复的记录删掉
-- 而使用union all ， mysql 会把所有的记录返回，且效率高于union 。
-- 将多条查询结果合并，会去去掉重复的行
SELECT num,NAME,sex FROM t_student WHERE sex='男'
UNION
SELECT num,NAME,sex FROM t_student WHERE height>170
-- 将多条查询结果合并，不会去掉重复的行
SELECT num,NAME,sex FROM t_student WHERE sex='男'
UNION ALL
SELECT num,NAME,sex FROM t_student WHERE height>170
(4) 排序
- 查询结果排序,使用 ORDER BY 子句排序 order by 排序列 ASC/DESC asc代表的是升序，desc代表的是降序，如果不写，默认是升序 order by子句中可以支持单个字段、多个字段、表达式、函数、别名
-- 排序
/*查询结果排序,使用 ORDER BY 子句排序
order by 排序列 ASC/DESC asc代表的是升序，desc代表的是降序，如果不写，默认是升序
order by子句中可以支持单个字段、多个字段、表达式、函数、别名*/
SELECT * FROM t_student ORDER BY num DESC
-- 有条件 排序在条件的后面，对筛选过后的结果进行排序 asc升序 desc降序
SELECT * FROM t_student WHERE num>1 ORDER BY num DESC
SELECT * FROM t_student WHERE num>1 ORDER BY birthday DESC
-- 多个字段进行排序 当第一个相同时，使用第二个字段排序
SELECT * FROM t_student WHERE num>1 ORDER BY height DESC,weight ASC
(5)数量限制
limit子句：对查询的显示结果限制数目 (sql语句最末尾位置)
SELECT * FROM table LIMIT offset rows;
SELECT * from table LIMIT 0,5;
-- 数量限制 -----分页问题
-- limit子句：对查询的显示结果限制数目 (sql语句最末尾位置)
-- 用法： limit 开始位置, 查询的数量
SELECT * FROM t_student LIMIT 0,2
-- 查询男生，身高最高的
SELECT * FROM t_student WHERE sex='男' ORDER BY height DESC LIMIT 0,1
（6）分组查询
语法：
select 分组函数，列（要求出现在group by的后面）
from 表
[where 筛选条件]
group by 分组的列表
[having 分组后的筛选]
[order by 子句]
注意：查询列表比较特殊，要求是分组函数和group by后出现的字段 分组查询中的筛选条件分为两类:
数据源 源位置 关键字
分组前筛选 原始表 group by子句的前面 where
分组后筛选 分组后的结果集 group by的后面 having
-- 分组查询
/*
分组--统计 count（）
按性别分组，年纪，班级，部门
GROUP BY 列 按照哪个列的值进行分组 把内容相同的数据分到一个组进行统计
*/
/*语法：
语法：select 分组函数，列（要求出现在group by的后面）
from 表
[where 筛选条件]
group by 分组的列表
[having 分组后的筛选]
[order by 子句]
*/
-- 查询男生女生各自的人数count（*）分组统计 分别统计男生组 女生组
SELECT sex,COUNT(*) FROM t_student GROUP BY sex
SELECT sex,SUM(height) FROM t_student GROUP BY sex
SELECT sex,AVG(height) FROM t_student GROUP BY sex
-- 查询男生和女生人数最多的是哪一个
SELECT
sex,
COUNT(*)c
FROM
t_student
WHERE height>160 -- 对原始表数据条件筛选
GROUP BY sex -- 按某个条件进行分组
HAVING COUNT(*)>1 -- 对分组后的数据进行条件筛选
ORDER BY c DESC -- 对处理完的数据进行排序
LIMIT 0,1 -- 数量限制
（7）子查询
含义：出现在其他语句中的select语句，称为子查询或内查询；外部的查询语句，称为主查询或 外查询. 子查询在SELECT语句内部可以出现SELECT 语句。 语句结果可以作为外部语句中条件子句的一部分，也可以作为外部查询的临时表。
分类：
按子查询出现的位置：
select后面：仅仅支持标量子查询
from后面：支持表子查询
where或having后面：支持标量子查询，列子查询，行子查询
按功能、结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）（较少）
表子查询（结果集一般为多行多列）
-- 子查询
/*含义：出现在其他语句中的select语句，称为子查询或内查询；外部的查询语句，称为主查询或 外查询.
分类：
按子查询出现的位置：
insert into后面：支持列子查询，表子查询
delete update 后面where
select后面：仅仅支持标量子查询
from后面：支持表子查询
where或having后面：支持标量子查询，列子查询，行子查询
按功能、结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）（较少）
表子查询（结果集一般为多行多列）
*/
-- 在修改语句中使用了子查询，注意的是，子查询不能是当前正在操作的表
UPDATE t_student SET NAME='aaa' WHERE num=(SELECT num FROM t_student WHERE height=180)
DELETE FROM t_student WHERE num=(SELECT num FROM t_student WHERE height=180)
-- 重点 在查询语句中使用子查询
-- 在一条sql中如果出现来给两个以上的表名时，可以为表名定义别名
-- 在select 后面写子查询 用外面查询结果作为条件
SELECT
ts.num,
(SELECT NAME FROM t_student t WHERE t.NAME=ts.NAME)
FROM
t_student ts
-- where 后面
-- 标量子查询 一行一列
SELECT * FROM t_student WHERE height = (SELECT MAX(height) FROM t_student)
-- 列子查询 一列多行
SELECT * FROM t_student WHERE height IN(SELECT height FROM t_student WHERE height>=175)
-- 行子查询 一行多列 查询身高最高 体重最重的
SELECT * FROM t_student WHERE (height,weight) =(SELECT MAX(height),MAX(weight) FROM t_student)
-- from 后面 使用表子查询 返回多行多列
-- 查询男生，女生总人数大于1的
SELECT * FROM (SELECT sex,COUNT(*)c FROM t_student GROUP BY sex
)t WHERE t.c>2
-- 用的很多的： where 标量子查询 列子查询 from 表子查询
四.多表设计_关联查询
1.设计目的
为什么需要多表关联？
数据冗余—拆分
2.数据库设计范式
-
为了建立冗余较小，结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结
-
目前关系数据库有5种范式：第一范式（1NF）、第二范式（2NF）、第 三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）
-
满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步 满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般 来说，数据库只需满足第三范式(3NF）就行了
（1）第一范式（1NF）
（2）第二范式（2NF）
- 第二范式就是要有主键，要求其他字段都依赖于主键。
- 没有主键就没有唯一性，没有唯一性在集合中就定位不到这行记录，所以要主键。
- 其他字段为什么要依赖于主键？因为不依赖于主键，就找不到他们。更重要的是，其他字段组成的这行记录和主键表示的是同一个东西，而主键是唯一的，它们只需要依赖于主键，也就成了唯一的。
（3）第三范式（3NF）
3.外键
弱关联–人为定义的关系 没有实际约束 删除关联表数据没有任何限制
强关联–添加外键约束 外键关联另一个表的主键
使用外键时的注意事项：
1、当主表中没有对应的记录时，不能将记录添加到从表
2、不能更改主表中的值而导致从表中的记录孤立
3、从表存在与主表对应的记录，不能从主表中删除该行
4、删除主表前，先删从表
在使用外键时主表和从表的区分：
- 主表（父表）
在数据库中建立的表格即Table，其中存在主键(primary key)用于与其它表相关联，并且作为在主表中的唯一性标识。 - 从表（子表）
以主表的主键（primary key）值为外键 (Foreign Key)的表，可以通过外键与主表进行关联查询。从表与主表通过外键进行关联查询。 - 关系及用法概述
从表数据依赖于主表，一般最后查询数据时把主表与从表进行关联查询。
4.关联查询
（1）表与表之间对应的关系
一对多 多对一 一对一（外键不重复） 多对多
（2）内连接
在关联时添加条件，把满足条件的数据关联起来
代码及案例（自关联）：
CREATE TABLE t_area( -- 建一张地区表 准备用内来接的方式来查询它
id INT PRIMARY KEY,
NAME VARCHAR(10),
pid INT
)
-- 用内连接的方式来查询它的这张表格的父子关系
SELECT ta.name '子',tp.name'父' FROM t_area ta INNER JOIN t_area tp ON ta.pid=tp.id WHERE ta.id=6101001
建立的表数据和查询结果：
（3）左外连接与右外连接
1）左外连接
无论关联条件是否成立，都会将左连接的数据全部查询出来
代码及案例：
-- 建表代码
CREATE TABLE studnet( -- 学生表
num INT PRIMARY KEY AUTO_INCREMENT COMMENT'学号',
NAME VARCHAR(10) NOT NULL COMMENT'姓名',
sex CHAR(1) NOT NULL COMMENT'性别',
birthday DATE NOT NULL COMMENT'生日',
height FLOAT(4,1) CHECK(height>80.0) COMMENT'体重',
phone CHAR(11) NOT NULL UNIQUE COMMENT'电话',
reg_time DATETIME COMMENT'注册时间'
)
ALTER TABLE studnet ADD gradeid INT AFTER phone -- 给学生表添加年级编号列
CREATE TABLE grade( -- 年级表
id INT PRIMARY KEY COMMENT'年级编号',
NAME VARCHAR(5) COMMENT'年级名称'
)
-- 给学生表设置外键关联 将学生表与年级表 用外键的方式关联起来
ALTER TABLE studnet ADD CONSTRAINT fk_gradeid FOREIGN KEY(gradeid) REFERENCES grade(id)
建表结果
查询：
-- 查询代码
-- 左外连接
-- 无论关联条件是否成立，都会将左边表中的数据查询出来
-- 查询学生基本和年级信息
SELECT s.num'学号',
s.name'姓名',s.sex'性别',
g.name'年级'
FROM studnet s
LEFT JOIN grade g
ON s.gradeid=g.id
查询结果
2）右外连接
无论关联条件是否成立，都会将右边表的数据全部查询出来
代码及案例：
-- 建表代码
CREATE TABLE studnet( -- 学生表
num INT PRIMARY KEY AUTO_INCREMENT COMMENT'学号',
NAME VARCHAR(10) NOT NULL COMMENT'姓名',
sex CHAR(1) NOT NULL COMMENT'性别',
birthday DATE NOT NULL COMMENT'生日',
height FLOAT(4,1) CHECK(height>80.0) COMMENT'体重',
phone CHAR(11) NOT NULL UNIQUE COMMENT'电话',
reg_time DATETIME COMMENT'注册时间'
)
ALTER TABLE studnet ADD gradeid INT AFTER phone -- 给学生表添加年级编号列
CREATE TABLE grade( -- 年级表
id INT PRIMARY KEY COMMENT'年级编号',
NAME VARCHAR(5) COMMENT'年级名称'
)
-- 给学生表设置外键关联 将学生表与年级表 用外键的方式关联起来
ALTER TABLE studnet ADD CONSTRAINT fk_gradeid FOREIGN KEY(gradeid) REFERENCES grade(id)
建表结果：
查询目的及查询代码和结果：
-- 右外连接
-- 无论关联条件是否成立，都会将右边表中的数据查询出来
-- 统计各年级人数信息
SELECT COUNT(s.gradeid)'人数',
g.id'年级编号',
g.name'年级名称'
FROM studnet s RIGHT JOIN grade g ON s.gradeid=g.id
GROUP BY g.id,g.name
查询结果：
1013
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
