数据库概述
数据库（DataBase）为了方便数据的存储和管理，它将数据按照特定的
规则存储在磁盘上,就是一个存储数据的仓库。
● 数据库的相关概念
DB：数据库（DataBase）
存储数据的容器，它保存了一系列有组织的数据。
DBMS：数据库管理系统（DataBase Management System）
又称为数据库软件或数据库产品，用于创建或管理DB。
● 常见的数据库产品：
国外
MySQL 快捷、可靠 开源、免费
Oracle:功能强大,收费.
SQL Server(微软): 只能安装在Windows操作系统
DB2 (IBM):适合处理海量数据,收费.
国内
南大通用GBASE: 天津南大通用数据技术股份有限公司
达梦:武汉达梦数据库股份有限公司
人大金仓:北京人大金仓信息技术股份有限公司
神通:神舟通用公司
MySQL是一个关系型数据库管理系统**，**由瑞典MySQL AB 公司开发，目
前属于 Oracle旗下产品。MySQL 流行的关系型数据库管理系统。
● MySql是一种关系数据库管理系统。
● MySql软件是一种开放源码软件,你可以修改源码来开发自己的 Mysql 系统。
● MySql数据库服务器具有快速、可靠和易于使用的特点。
● MySql使用标准的sql语言,并且支持多种操作系统,支持多种语言.
● mysql商业版与社区版
● MySQL商业版是由MySQL AB公司负责开发与维护,需要付费才能使用
● MySQL社区版是由分散在世界各地的MySQL开发者、爱好者一起开发与维
护，可以免费使用
命令行方式连接mysql
登录：mysql [-hlocalhost -P3306]（本机可省略） -uroot -p（可以直
接写密码，不能有空格）
-h：主机名
-P：端口号
-u：用户名
-p：密码
退出：exit
• MySQL的常用命令
查看当前所有的数据库：show databases;
选择指定的库：use 库名
查看当前的所有表：show tables;
查看其他库的所有表：show tables from 库名;
查看mysql版本 select version();
• 安装可视化客户端工具
SQLyog / Navicat
结构化查询语言(Structured Query Language)简称SQL，是一种特殊
目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以
及查询、更新和管理关系数据库系统.
SQL优点：
不是某个特定数据库供应商专有的语是言，几乎所有DBMS都支持SQL
简单易学，灵活使用可以进行非常复杂和高级的数据库操作
DDL
● 数据(结构)定义语言DDL(Data Definition Language)，是用于创
建和修改数据库表结构的语言。
● 常用的语句：create ,alter,drop,rename
创建,删除数据库
创建数据库并设置编码格式
CREATE DATABASE [if not exists] 数据库名 [ CHARSET utf8]
删除数据库
DROP DATABASE 数据库名 / [IF EXISTS数据库名];
修改字符集
ALTER DATABASE 数据库名 CHARSET gbk;
数据库表的基本概念
1、数据表
表（table）是数据存储的最常见和最简单的形式，是构成关系型数据库的基本元素。
表的最简单形式是由行和列组成，分别都包含着数据。 每个表都有一个表头和表体，表头定
义表名和列名 .表中的行被看作是文件中的记录，表中的列被看作是这些记录的字段。
2、字段
字段是表里的一列，用于保存每条记录的特定信息。如客户订单表的字段包括“订单
ID”、“姓名”、“客户ID”、“职务”、“上级”、“地区”、“运货商”、“国家”等。
数据表的一列包含了特定字段的全部信息。
3、记录
记录也被称为一行数据，是表里的一行数据。
设计表(数据类型)
char（n） 长度为n的定长字符串,最大长度255个字符
varchar(n） 最大长度为n的可变长字符串
date 日期， 包含年月日
datetime 年月日 时分秒
设计表(数据类型)
浮点
decimal
数据类型(M,D)
M：精度，数据的总长度；
D：标度，小数点后的长度
设计表(数据类型)
TEXT列字符字符串
有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。
它的长度：
TINYTEXT：最大长度255个字符(2^8-1)
TEXT：最大长度65535(2^16-1)
MEDIUMTEXT：最大长度16777215(2^24-1)
LONGTEXT最大长度4294967295(2^32-1)
主键：
在一张表中代表唯一的一条记录,不能为空,不能重复
约束:
PRIMARY KEY 设置主键约束
NOT NULL 不能为空约束
UNIQUE 唯一性约束
检查约束 设置条件
外键约束
主键自动增长,设置为自动增长时,只能为整数类型
AUTO_INCREMENT
默认值
DEFAULT default_value
字段注释:
comment ‘注释’
创建表语法:
CREATE TABLE 表名(列名 数据类型 [约束] [默认值] [ 注释],…)
CREATE TABLE t_user(
id INT [PRIMARY KEY NOT NULL AUTO_INCREMENT],
number INT(5) NOT NULL,
stuname VARCHAR(10) NOT NULL,
age INT(3) CHECK(age>18),
birthday DATE,
weight DOUBLE,
opertime datetime,
[CONSTRAINT 约束名 约束规则]
)
删除表，修改表名
删除表
DROP TABLE [if exists ]表名
修改表名
RENAME TABLE 旧表名 TO 新表名
复制表结构
CREATE TABLE 新表名 LIKE 被复制表名;
实例
-- 创建数据库
-- CREATE DATABASE [if not exists] 数据库名 [ CHARSET utf8]
CREATE DATABASE test;
-- 创建数据库 并设置字符集编码
CREATE DATABASE schooldb CHARSET utf8;
-- 创建数据库 并设置字符集编码 并判断数据库是否存在
CREATE DATABASE IF NOT EXISTS test CHARSET utf8;
-- 删除数据库
DROP DATABASE test;
-- mysql数据库一旦创建不能修改,只能修改字符集编码
ALTER DATABASE test CHARSET utf8;
/*
创建数据库表
表 表名
列 特定信息 姓名,性别....
行 数据 张三 男
创建表的步骤:
确定表名: 学生信息 -- 学生表
确定列名: 学生的具体信息 姓名,性别,生日....
列的数据类型:
字符串型:
char(n) 长度为n的定长字符串, 例如n=5, 如果我们只存储了2个字符进去,长度依然是5,会补空格
一般用来存储长度固定的数据, 例如性别,电话.
varchar(n) 最大长度为n的变长字符串, 例如n=10,如果我们只存储了3个字符,实际长度为3
日期时间类型
date 日期--年月日
datetime 时间--年月日 时分秒
数值
整数
TINYINT 1
SMALLINT 2
MEDIUMINT 3
int 4
bigint 8
浮点:
decimal(M,D)
3,2
1.73
TEXT列字符字符串 长文本类型 例如存储新闻信息,小说...
列的约束: 列的规则
*/
-- 学生表 学号,姓名,性别,生日,电话,地址,身高,注册时间
CREATE TABLE student(
num INT,
NAME VARCHAR(10),
gender CHAR(1),
birthday DATE,
phone CHAR(11),
address VARCHAR(30),
height DECIMAL(3,2),
reg_time DATETIME
)
-- 删除表
DROP TABLE student
-- 创建表,并为列添加约束
/*
学号 唯一,不能为空 而且只能有一个学号
可以为学号列添加主键约束(唯一不能重复,不能为空,一个表中只能有一个列添加主键约束)
PRIMARY KEY 设置列为主键
AUTO_INCREMENT 设置主键列自动增长, 只能修饰主键列,而且主键列类型为整数
not null 不能为空约束 可以添加到多个普通列
unique 唯一约束 可以添加到多个普通列
CHECK(height<2.60) 检查约束
*/
CREATE TABLE student(
num INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10) NOT NULL,
gender CHAR(1) NOT NULL,
birthday DATE,
phone CHAR(11) NOT NULL UNIQUE,
address VARCHAR(30),
height DECIMAL(3,2) CHECK(height<2.60),
reg_time DATETIME
)
DROP TABLE student
CREATE TABLE student(
num INT PRIMARY KEY AUTO_INCREMENT COMMENT '学号 主键',
NAME VARCHAR(10) NOT NULL COMMENT '姓名',
gender CHAR(1) NOT NULL DEFAULT '男' COMMENT '性别',
birthday DATE,
phone CHAR(11) NOT NULL UNIQUE,
address VARCHAR(30),
height DECIMAL(3,2) CHECK(height<2.60),
reg_time DATETIME
)
-- 修改表名
RENAME TABLE student TO stu
RENAME TABLE stu TO student
-- 复制表结构
CREATE TABLE stu LIKE student
DML
数据操纵语言DML（Data Manipulation Language）
● 常用语句： insert,delete,update
插入数据
方式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
/*
方式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(NAME,gender,birthday,phone)VALUES('张三','男','2003-3-5','15233336666')
INSERT INTO student(NAME,gender,birthday,phone,address,height,reg_time)
VALUES('张三','男','2003-3-5','15233336688','汉中',1.75,NOW())
INSERT INTO student(NAME,gender,birthday,phone,address,height,reg_time)
VALUES('张三丰','男','2003-3-5','15233336677','汉中',1.75,NOW()),
('李四','女','2003-3-3','15233336699','汉中',1.85,NOW()),
('王五','男','2003-4-3','15233336609','汉中',1.95,NOW())
INSERT INTO student SET NAME = '赵六',gender='女',phone='15777778888'
INSERT INTO stu(NAME,gender,birthday,phone,address,height,reg_time)
SELECT NAME,gender,birthday,phone,address,height,reg_time FROM student
-- 修改语句 需要注意条件的准确性, 否则修改所有的数据
UPDATE student SET address='陕西西安',height=1.65,reg_time = '2003-4-5 14:20:10' WHERE num = 1
-- 删除语句
DELETE FROM stu -- 没有条件 删除所有的数据
DELETE FROM student WHERE num = 13
**DQL-**基础查询
● DQL（Data Query Language）数据查询语言查询是使用频率最高的一个操作，
可以从一个表中查询数据，也可以从多个表中查询数据。
● 基础查询
● 语法：
select 查询列表 from 表名;
● 特点：
查询列表可以是：表中的字段、常量、表达式、函数
查询的结果是一个虚拟的表格
● 查询结果处理：
特定列查询:select column1,column2 from table
全部列查询: select * from table
算数运算符:+ - * /
排除重复行: select distinct column1,column2 from table
查询函数：select 函数; / 例如version()
● 查询结果处理：
● 函数:类似于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)：替换，替换所有的子串
● 单行函数
• 逻辑处理
case when 条件 then 结果1 else 结果2 end; 可以有多个when
ifnull(被检测值,默认值)函数检测是否为null，如果为null，则返回指定的值，否则返回
原本的值
if函数：if else的 效果 if(条件,结果1,结果2)
● 单行函数
● 数学函数
round(数值)：四舍五入
ceil(数值)：向上取整，返回>=该参数的最小整数
floor(数值)：向下取整，返回<=该参数的最大整数
truncate(数值,保留小数的位数)：截断，小数点后截断到几位
mod(被除数,除数)：取余，被除数为正，则为正；被除数为负，则为负
rand()：获取随机数，返回0-1之间的小数
● 单行函数
日期函数
now()：返回当前系统日期+时间
curdate()：返回当前系统日期，不包含时间
curtime()：返回当前时间，不包含日期
可以获取指定的部分，年、月、日、小时、分钟、秒
YEAR(日期列),MONTH(日期列),DAY(日期列) ,HOUR(日期列) ,MINUTE(日期列)
SECOND(日期列)
str_to_date(字符串格式日期,格式)：将日期格式的字符转换成指定格式的日期
date_format(日期列,格式)：将日期转换成字符串
datediff(big,small)：返回两个日期相差的天数
分组函数
功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：sum 求和、avg 平均值、max 最大值、min 最小值、count 计数
（非空）
1.sum，avg一般用于处理数值型max，min，count可以处理任何类型
2.以上分组函数都忽略null值
3.count函数的一般使用count（*）用作统计行数
4.和分组函数一同查询的字段要求是group by后的字段
条件查询
使用WHERE 子句，将不满足条件的行过滤掉，WHERE 子句紧随 FROM 子句。
语法:select <结果> from <表名> where <条件>
比较
=, != 或<>, >, <, >=, <=
逻辑运算
and 与
or 或
not 非
● 条件查询
模糊查询
LIKE
：是否匹配于一个模式 一般和通配符搭配使用，可以判断字符型数值
或数值型.
通配符： % 任意多个字符
between and 两者之间,包含临界值；
in 判断某字段的值是否属于in列表中的某一项
IS NULL（为空的）或 IS NOT NULL（不为空的）
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子句：对查询的显示结果限制数目 (sql语句最末尾位置)
SELECT * FROM table LIMIT offset rows;
SELECT * from table LIMIT 0,5;
分组查询
语法：
select 分组函数，列（要求出现在group by的后面）
from 表
[where 筛选条件]
group by 分组的列表
[having 分组后的筛选]
[order by 子句]
注意：查询列表比较特殊，要求是分组函数和group by后出现的字段
分组查询中的筛选条件分为两类:
数据源 源位置 关键字
分组前筛选 原始表 group by子句的前面 where
分组后筛选 分组后的结果集 group by的后面 having
实例
-- 基本查询语法
-- select 查询的列 from 表名 where 条件 排序 数量限制 分组....
-- select 结果处理 from 表名
-- 结果处理
-- 查询特定的列
SELECT num,NAME,gender FROM student
-- 查询所有的列, 在开发中一般不建议, 使用哪些列,查询哪些
SELECT * FROM student
-- sql中+ - * / 只能做算数运算 +不能连接字符串
SELECT num+100,NAME FROM student
-- 字符串函数 连接多个字符串
SELECT num,CONCAT(NAME,':',gender)AS NAME FROM student
SELECT VERSION();
-- 去除查询结果中重复数据, 何为重复数据: 所有的列都相同
SELECT DISTINCT NAME,gender,birthday FROM student
-- 查询结果中使用函数
-- 单行函数--会对查询的每条记录进行操作
-- 分组函数--sum() 也称为聚合函数,统计函数, 把多行数据最终处理为一行
-- 字符函数
-- length()：获取参数值的字节个数
SELECT NAME,LENGTH(NAME) FROM student
-- char_length()获取参数值的字符个数
SELECT NAME,CHAR_LENGTH(NAME)AS NAME,gender FROM student
-- concat(str1,str2,.....)：拼接字符串 as 别名
SELECT num,CONCAT(NAME,':',gender)AS NAME FROM student
-- upper()/lower()：将字符串变成大写/小写
SELECT UPPER(NAME),LOWER(NAME) FROM student
-- substring(str,pos,length)：截取字符串 位置从1开始
SELECT SUBSTRING(NAME,2,3) FROM student
-- instr(str,指定字符)：返回子串第一次出现的索引(indexof())，如果找不到返回0
SELECT INSTR(NAME,'三') FROM student
-- trim(str)：去掉字符串前后的空格或子串,trim(指定子串 from 字符串)
SELECT CHAR_LENGTH(TRIM(NAME)) FROM student
SELECT TRIM('张' FROM NAME) FROM student
-- lpad(str,length,填充字符)：用指定的字符实现左填充将str填充为指定长度
SELECT LPAD(NAME,5,'a') , RPAD(NAME,5,'b')FROM student
-- rpad(str,length,填充字符)：用指定的字符实现右填充将str填充为指定长度
-- replace(str,old,new)：替换，替换所有的子串
SELECT REPLACE(NAME,'i','I') 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 '高个子' ELSE '正常身高' END)AS height,
gender
FROM student
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,'高个子','正常身高') FROM student
-- 数学函数
-- round(数值)：四舍五入
SELECT NAME,ROUND(height) FROM student
-- 指定保留小数位数
SELECT NAME,ROUND(height,1) FROM student
-- ceil(数值)：向上取整，返回>=该参数的最小整数
SELECT NAME,CEIL(height),FLOOR(height) FROM student
-- floor(数值)：向下取整，返回<=该参数的最大整数
-- truncate(数值,保留小数的位数)：截断，小数点后截断到几位, 不会四舍五入
SELECT NAME,TRUNCATE(height,1) FROM student
-- mod(被除数,除数)：取余，被除数为正，则为正；被除数为负，则为负
SELECT NAME, MOD(num,3) FROM student
-- rand()：获取随机数，返回0-1之间的小数
SELECT NAME, RAND() FROM student
-- 日期函数
-- now()
SELECT NAME, NOW(),CURDATE(),CURTIME() FROM student
-- 日期格式化
SELECT NAME, YEAR(reg_time),MONTH(reg_time),DAY(reg_time) FROM student
-- select count(*) from student group by year(birthday)
-- 将字符串格式化为日期类型
SELECT STR_TO_DATE('2001-2-2','%Y-%m-%d') FROM student
-- 将日期格式化为指定的字符串
SELECT DATE_FORMAT(birthday,'%Y-%m') FROM student
-- 计算两个日期之间相差的天数
SELECT DATEDIFF(CURDATE(),birthday) FROM student
SELECT DATEDIFF(STR_TO_DATE('2020-1-1','%Y-%m-%d'),birthday) FROM student
-- 分组函数 / 聚合函数, 统计函数、
-- sum 求和、avg 平均值 、 max 最大值 、 min 最小值、count 计数
-- sum(), avg() 只能用于数值类型
-- max (), min() ,count()可以适用于所有类型
-- 2.以上分组函数都忽略null值
-- 3.count函数的一般使用count（*）用作统计行数
-- .和分组函数一同查询的字段(属性)要求是group by后的字段(属性)
SELECT SUM(height),AVG(height) ,MAX(height),MIN(height) FROM student
SELECT MAX(height),MIN(height) FROM student
-- 统计行数 count(*)官方推荐的 count(1) count (列名) 列的值为空不统计
SELECT COUNT(*) FROM student
SELECT COUNT(address)FROM student
SELECT COUNT(1) FROM student
SELECT COUNT(num) FROM student
SELECT * FROM student WHERE height= ( SELECT MAX(height) FROM student)
-- 条件查询 select 结果列 from 表名 where 条件
SELECT * FROM student WHERE num = 1
-- and 所有条件都需要满足
SELECT *FROM student WHERE gender = '男' AND height>=1.70 AND address = '汉中'
-- or 满足一个条件即可
SELECT *FROM student WHERE gender = '男' OR height >=1.70
-- 不等于
SELECT * FROM student WHERE gender != '男'
SELECT * FROM student WHERE gender <> '男'
-- 模糊查询 like %字符% 通配符
SELECT * FROM student WHERE NAME LIKE '张%'
SELECT * FROM student WHERE NAME LIKE '%三%'
SELECT *FROM student WHERE height >=1.70 AND height <= 2.0
SELECT *FROM student WHERE height BETWEEN 1.70 AND 2.0
SELECT *FROM student WHERE height = 1.75 OR height = 1.85 OR height = 1.95
-- 查询身高 1.75 1.85 1.95
SELECT *FROM student WHERE height IN(1.75,1.85,1.95)
SELECT *FROM student WHERE height NOT IN(1.75,1.85,1.95)
-- 查询值为空的数据
SELECT *FROM student WHERE address IS NULL
SELECT *FROM student WHERE address IS NOT NULL
-- union 合并多个结果 并去重 合并多个查询的结果,可以去除重复的数据
SELECT num ,NAME, gender FROM student WHERE gender = '男'
UNION
SELECT num ,NAME, gender FROM student WHERE gender = '女'
SELECT num ,NAME, gender FROM student WHERE gender = '男'
UNION ALL
SELECT num ,NAME, gender FROM student WHERE gender = '女'
SELECT num ,NAME, gender FROM student WHERE gender = '男'
UNION
SELECT num ,NAME, gender FROM student WHERE height = 1.65
-- union all 合并多个查询结果, 只是合并, 不去重
-- 当使用union 时，mysql 会把结果集中重复的记录删掉，而使用union all ，
-- mysql 会把所有的记录返回，且效率高于union 。
SELECT num ,NAME, gender FROM student WHERE gender = '男'
UNION ALL
SELECT num ,NAME, gender FROM student WHERE height = 1.65
UNION ALL
SELECT num ,NAME, gender FROM student WHERE height = 1.75
-- 排序
-- 按升高排序, 默认是升序排序
SELECT *FROM student ORDER BY height
SELECT *FROM student ORDER BY height ASC
-- ASC 升序(默认) DESC降序
SELECT *FROM student ORDER BY birthday DESC
SELECT *FROM student ORDER BY height DESC
-- 多个排序条件
SELECT *FROM student WHERE num > 0 ORDER BY birthday DESC, height ASC-- 先对生日排序 在生日排序的基础上再对升高排序
-- 先对生日进行降序排序 再对身高进行升序排序(先对生日进行降序排序, 在生日相同的数据中进行身高的升序排序)
-- 有生日相同再对身高进行排序
-- 什么排序都没有,默认主键 升序排序
SELECT * FROM student
-- 数量限制 limit 开始位置(开始为0),查询数量
-- 实际使用场景, 数据分页显示 一次只查询一部分数据 , 提高查询效率
SELECT * FROM student LIMIT 0,2 -- 1 查询页码
SELECT * FROM student LIMIT 2,2 -- 2
SELECT * FROM student LIMIT 4,2 -- 2
-- limit (n-1)*2,2
-- limit 在sql的最末尾
SELECT *FROM student WHERE num>0 ORDER BY height DESC LIMIT 0,2
-- 分组查询
-- 将某类数据分到一个组中进行处理 例如性别查询
-- 查询男生 女生各有多少人(统计数量) 例如按性别分组 每月增量
-- GROUP BY 分组条件(列名)
-- 出现分组group by ,分组查询 select 查询属性只能出现group by后面的分组条件(属性)
-- 否则报错
-- group by中出现的属性 select 中必须出现这个属性否则报错
SELECT COUNT(*) ,gender FROM student GROUP BY gender -- select后面只能出现分组属性(条件)
SELECT SUM(height),gender FROM student GROUP BY gender -- 男女的各个的总身高
SELECT AVG(height),gender FROM student GROUP BY gender -- 男女的各个平均升高
-- 统计每年出生的人数
SELECT COUNT(*),DATE_FORMAT (birthday,'%Y')FROM student GROUP BY DATE_FORMAT (birthday,'%Y')
SELECT COUNT(*),YEAR (birthday)FROM student GROUP BY YEAR (birthday)
SELECT COUNT(*) ,gender FROM student WHERE num> 0 GROUP BY gender -- 先筛选(条件)后分组
-- 分组统计名字数量, 查询哪些名字重复了
-- where 是对原始表中的数据进行过滤
-- HAVING 在group by 后面使用 给分组后的数据加过滤条件
SELECT COUNT(*) n,NAME FROM student GROUP BY NAME HAVING COUNT(*)>1
SELECT * FROM (SELECT COUNT(*)AS n,NAME FROM student GROUP BY NAME) AS t WHERE t.n>1
多表设计**_**关联查询
● 数据库设计范式
● 1．第一范式(确保每列保持原子性)
第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就
说明该数据库表满足了第一范式。
● 数据库设计范式
2． 第二范式就是要有主键，要求其他字段都依赖于主键。
• 没有主键就没有唯一性，没有唯一性在集合中就定位不到这行记录，所以要主键。
• 其他字段为什么要依赖于主键？因为不依赖于主键，就找不到他们。更重要的是，其
他字段组成的这行记录和主键表示的是同一个东西，而主键是唯一的，它们只需要依
赖于主键，也就成了唯一的。
● 数据库设计范式
3.第三范式,确保每列都和主键列直接相关,而不是间接相关,要求一个数据库表中不包含
已在其它表中包含的非主关键字信息
● 外键：引用另外一个数据表的某条记录。
外键列类型与主键列类型保持一致
数据表之间的关联/引用关系是依靠具体的主键（primary key）和外键
（foreign key）建立起来的。
create table student(
id int not null auto_increment primary key,
num int,
name varchar(10)
majorid int,
CONSTRAINT 约束名 foreign key(majorid ) references major(id)
);
约束名规则:
例：FK_ForeignTable_PrimaryTable_On_ForeignColumn
1、当主表中没有对应的记录时，不能将记录添加到从表
2、不能更改主表中的值而导致从表中的记录孤立
3、从表存在与主表对应的记录，不能从主表中删除该行
4、删除主表前，先删从表
关联查询
含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件
按功能分类：
自连接
内连接
外连接
左外连接
右外连接
● 关联查询
● 内连接(inner join)
● 把满足了条件的两张表中的交集数据查询出来
语法：Select 结果 from 表1，表2 where 表1.column1 = 表2.column2
● 多表关联
● 左外连接(left join)
select 结果 from
表1 left join 表2 on
表1.column1 = 表2.column2
多表关联
右外连接(right join)
实例
/*
数据库设计范式(范例)
好的数据库设计,事倍功半,不会有歧义
第一范式 : 列(属性)保证原子性(属性不可再分)
联系方式: 电话/邮箱/qq(可在分,不呈现原子性)
第二范式 : 要有主键 其他字段(属性) 必须依赖于某个列(主键)
通过主键可以精确定位到某行数据(元组)
第三范式:保每列都和主键列直接相关
就是在一个表中关联另一个表 ,这个表中加的是另一个表中的主键, 不添加另一个表中非主键的内容(否则会出现数据冗余)
因为可以根据外键找到另一个表中非主键的信息
如果两张表有关系的话,只需要在一张表放置另一个表的主键进行关联,关联表中其他的非主键信息不需要了(没必要加, 加上反而数据冗余浪费空间使表不美观)
学生关联专业
例如通过 专业编号可以找到 该专业编号对应的名称以及其他信息
*/
/*
专业信息 -- 专业表
学生关联专业
*/
CREATE TABLE major(
id INT PRIMARY KEY AUTO_INCREMENT ,
NAME VARCHAR(20) NOT NULL
)
-- 修改表, 添加列
ALTER TABLE student ADD majorid INT
-- majorid 称为外键(外码) 不是这个关系的主码是另一个关系的主码则称这个码为这个关系的外码(例如majorid)
-- 外键必须是与另一个表的主键关联 , 且数据类型一致
-- 通过学生表中的majorid(属性 )列 把学生与专业关联起来, 在学生表中只需要添加专业编号就行
-- 其他信息不需要添加
ALTER TABLE student ADD CONSTRAINT fk_student_major_on_majorid FOREIGN KEY (majorid)REFERENCES major(id)
-- 表与表关系 : 多对一关系 也可以称为一对多
-- 给student 添加外键约束 要给majorid添加 说明在major中是主键 不是student的主键是major的主键
-- 说明majorid 是student的外键 这样就让这两个表进行了关联
-- 添加外键约束, 为什么要添加约束, 如果没有添加约束,数据与数据之间,没有任何束缚,可以随意操作,
-- 例如删除专业, 但是专业还有学生关联, 此种场景删除专业就不太友好.(对不上号)
-- 对外建添加约束后, 操作时就不能随便操作了, 保证数据的完整性
CREATE TABLE course(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
)
DROP TABLE course
/*
学生选课关系
多对多关系
课程表
由于一个学生可以选择多个课程,所以在学生表中添加课程外键就行不通了.
一个课程可以被多名学生选
需要再设计一个学生选课表(只要存储学生和课程的编号(主键)即可), 一般把这种表也称为关系表(联系表)
*/
CREATE TABLE student_course (
id INT PRIMARY KEY AUTO_INCREMENT,
student_num INT,
courseid INT,
CONSTRAINT fk_student_student_course_on_student_num FOREIGN KEY (student_num) REFERENCES student(num),
CONSTRAINT fk_couse_student_course_on_courseid FOREIGN KEY (courseid) REFERENCES course(id)
)
-- 关联查询
-- 查询学生信息 学号,姓名,性别,生日,专业名称
-- select * from student,major 表与表没有添加任何的关联条件 ,导致出现笛卡尔乘积现象.
-- 内连接
SELECT * FROM student,major
-- 如何避免卡尔乘积现象? 为关联添加条件,只把满足条件数据过滤出来
SELECT * FROM student,major WHERE majorid= id
SELECT
s.num ,
s.name,
s.gender,
m.name,
m.id
FROM student s INNER JOIN major m ON s.majorid=m.id
WHERE gender ='男'
-- 外连接
-- 左外连接 把左边表的满足条件的和不满足条件的都查询出来, 右边表只把满足条件的查询出来这就是左外连接
SELECT
*
FROM
student s
LEFT JOIN major m
ON s.majorid = m.id
-- 右外连接 把右边表的满足条件的和不满足条件的都查询处理, 左边表只把满足条件的查询出来这就是右外连接
SELECT
*
FROM
student s
RIGHT JOIN major m
ON s.majorid = m.id
-- 统计每个专业的人数
SELECT
COUNT(s.num),
m.name
FROM
student s
RIGHT JOIN major m
ON s.majorid = m.id
GROUP BY m.name
-- group_concat(c.name) cname
-- 学号,姓名,性别,专业名,选择的课程名称
-- GROUP_CONCAT(c.name) 把同一个组中,多个课程名称连接起来
SELECT
s.num,
s.name,
s.gender,
m.name,
GROUP_CONCAT(c.name) cname
FROM student s LEFT JOIN major m ON s.majorid= m.id
LEFT JOIN student_course sc ON s.num = sc.student_num -- 学生表里面的学号与选课表里面的学号进行连接
LEFT JOIN course c ON c.id = sc.courseid -- 课程表里面的课程编号与选课表里面的课程编号进行连接
-- 课程表里面的课程编号与选课表里面的课程编号进行连接
GROUP BY s.num,s.name, s.gender,m.name
-- 查询结果可以看出学生 的专业以及学生选的课程
510
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
