理论
关系型数据库:
概念:一列数据表示实体的一个属性,属性在数据库也称为字段。一行数据表示一个实体
主键:用于表示两个实体不同的列
外键:表示两个表之间的关系的那一个列
主表:被引用的表;
从表:引用别人的表
关系型数据库中的三种关系:
1.一对一建表(主键对外键)
2.一对多建表(外键建对方)
3.多对多建表了(需要衍生出第三张表)
判断关系:看两边并根据业务需求而定
由四个部分:
1.数据定义语言:DDL(用于定义数据库、定义数据表、定义视图与索引等)
2.数据操作语言:DML(对数据进行增删改)
3.数据库查询语言:DQL(查询数据)
4.数据控制语言:DCL(对数据库进行设置权限);
drop和delete的区别:
drop用于删除数据库(表单),delete用于删除表单中的数据
CHAR(n)和VARCHAR(n)的区别:
CHAR是定长,VARCHAR是变长,定长无论多少个数都会以n数计算,变长是在n以内的数,定长比变长效率高但不节约内存
代码操作
示例代码(用于解说):
CREATE TABLE t_stu (
s_id INT PRIMARY KEY auto_increment,
s_name VARCHAR (10) UNIQUE,
s_sex CHAR (2) DEFAULT '男',
s_age INT,
s_class_id INT
);
insert into t_stu (s_id,s_name,s_sex,s_age,s_class_id) values(null,'刘基','男',20,1);
insert into t_stu (s_id,s_name,s_sex,s_age,s_class_id) values(null,'刘伯','男',15,2);
insert into t_stu (s_id,s_name,s_sex,s_age,s_class_id) values(null,'六三','女',22,3);
insert into t_stu (s_id,s_name,s_sex,s_age,s_class_id) values(null,'长官','男',13,4);
insert into t_stu (s_id,s_name,s_sex,s_age,s_class_id) values(null,'王博','女',13,5);
insert into t_stu (s_id,s_name,s_sex,s_age,s_class_id) values(null,'力来','男',18,6);
insert into t_stu (s_id,s_name,s_sex,s_age,s_class_id) values(null,'天伯','女',19,7);
insert into t_stu (s_id,s_name,s_sex,s_age,s_class_id) values(null,'黄剑锋','男',16,8);
insert into t_stu (s_id,s_name,s_sex,s_age,s_class_id) values(null,'建筑','女',17,9);
insert into t_stu (s_id,s_name,s_sex,s_age,s_class_id) values(null,'指定','女',24,10);
数据库、
show datebases; 显示数据库
create database name; 创建数据库
drop database name; 删除数据库
管理表、
use name; 使用xxx数据库
create table (列名 类型) ; 创建一个表
show tables; 展示所有表
drop table name; 删除xxx表
alter table t_student add age int; 添加一个列
数据完整性
一个列可以添加多个约束
添加主键约束:
1.表内在列后添加primary key关键字
例: create table t_test(
stuId int primary key
);
2.表外在最后加一个primary key(约束名);
例:alter table t_test add primary key(stuId);
一个表只能包含一个主键约束并且此列必须是唯一不能为空的;
在primary key后加上auto_increment关键字可以让主键值自动增长(默认从1开始,并且只能用在主键上)
修改自动增长的初始值:
使用:alter table name auto_increment=值;
添加唯一约束(去重,唯一的):
在列后添加一个unique关键字;
主键约束和唯一约束的区别:
主键约束不能为空,唯一约束可以为空。一张表只能有一个主键约束,一张表可以有多个唯一约束;
设置非空约束:
在列后添加一个not null关键字;
设置默认约束:
在列后添加一个default ‘值’;
引用完整性
添加外键约束:
constraint foreign key(外键名) referencse 引用表名(主键名)
外键和外键约束的区别:
外键是用来表示表与表之间的联系,是表中的一个列。外键约束是指在外键列上加一个约束,强制外键列引用数据是正确的;
DOL语句
运算符:
(1)and 和
(2)or 或者
(3)in 在某个集合内
例:in(1,3,5) 表示1或3或5
(4)not in 不在某个集合内
通配符:一般需使用like
(1)_ 表示任何一个字符
例:select s_name from t_stu where s_name like '_伯';
(2)% 表示包含0个或多个字符
处理空值数据:
判断某列的值是否为空: 使用 is null
判断某列的值是否不为空: 使用 is not null
1.插入值(设置值,添加列表值)
使用insert语句
格式:(推荐使用) insert into 表名 ( 列表列名… ) values ( 列表值 ); 注:列表名与列表值要一致
可读性高,效率快一点
例:insert into t_stu (s_id,s_name,s_sex,s_age,s_class_id) values(null,'刘基','男',20,1);
2.inset into 表名 values ( 列表值); 注:列表值须与建表时的字段顺序一致
例:insert into t_stu values(null,'刘基','男',20,1);
2.查询值
表前缀(当多张表一起查询时,列名相同,用于区分): select 表名.列表名… from 表名;
例:select t_stu.s_name from t_stu where s_name like '_伯';
列别名和表别名:
使用as关键字(注:as可省略加空格)
列
格式:select 列名 as 新名字 from 表名;
例:select s_name as'学生姓名' from t_stu where s_name like '_伯';
表
格式:select 列名 from 表名 as 新名字;
查询使用select语句
格式:
1.select 列表名… from 表名; ( 查询列表名中的(常用,效率高) ) select * form 表名 (查询所有)
2.select distinct 列名 from 表名 (去重查询数据,注当查询多个列时是查询多个列加起来是否唯一)
3.select 列表名… from 表名 limit 开始行数,返回几行数据 (限定行数的查询,注开始行数默认为0不可为负数但可不写);
select 列表名… from 表名 limit (当前页数-1)*每页显示条数,每页显示条数(分页查询数据)
select 列表名… from 表名 where 条件 (单条件选择查询)
4.select 列表名… from 表名 order by 排序数据列名 asc/desc (单列排序查询 注:asc为升序(默认,可不写),desc为降序)
select 列表名… from 表名 条件 order by 排序数据列名 asc/desc
select 列表名… from 表名 order by 排序数据列名1 asc/desc , 排序数据列名2 asc/desc (多列排序查询,先按排序数据列名1进行排序若有相同的值时再按排序数据列名2进行排序)
聚合函数
定义:用于对数据进行统计和分析
分类:
1.count 统计
格式:select count (计算规范) from 表名 (注:计算规范可选值:(1). * : 统计所有函数包含null的行数, (2). all 列名 :计数指定列的所有非空值的行数(默认,可不写all),(3).distinct 列名:计数指定列的所有唯一不重复非空值的行数)
2.sum 记和
格式:select sum (计算规范) from 表名
3.avg 求平均值
格式:select avg(计算规范) from 表名
注:计算规范可选值: (1). all 列名 :计数指定列的所有非空值的行数(默认,可不写all),(2).distinct 列名:计数指定列的所有唯一不重复非空值的行数
一般用:ROUND(AVG(IFNULL(score,0)),2) ,取平均值
4.ifnull(参数1,参数2) 函数:用于判断空值,若参数1为空值则按参数2显示返回,若参数1不为空值则按参数1显示返回(注参数可写列名)
5。round(数值,保留小数位数)函数:用于把数值舍入为指定小数位数
6.max 获取最大值
格式:select max(列名) from 表名
7.min 获取最小值
格式:select min(列名) from 表名
数据分组
投影要求:1.group by 后的列名可以投影
或
2.必须是聚合函数
使用group by语句:
格式:select 列名 from 表名 group by 列名;(注前一个列名须与后一个一样或者为聚合函数,否则可能会数据错误)
select 列名 聚合函数 from 表名 where 条件 group by 列名;
select 列名1,列名2 from 表名 group by 列名1,列名2; (多列分组,将列一值相同并且列二值相同的分到同一组)
having子句
定义:用于对分组后的数据进行过滤
格式:select 列名 from 表名 group by 列名 having 聚合条件;
having和where的区别:
二者都是过滤条件,
where运行在分组前,因此不能执行任何聚合函数。
having是运行在分组后,只能用做聚合函数的过滤。
sql的执行顺序:
执行FROM ----> WHERE条件过滤 ----> GROUP BY分组 ----> 执行SELECT投影列 ----> HAVING条件过滤 ----> 执行ORDER BY排序
3.更改(修改)值
使用update语句:
格式: update 表名 set 列名 where 条件
4.删除:
使用delete语句
格式: 1.delete form 表名 where 条件(注:此语句中会删除一行或多行)
2.truncate table 表名(注:删除表中所有数据,一般用于测试)
注:delete语句和truncate语句的区别:
delete(会有记录日志)删除后可恢复,但效率低。删除数据后自动增长的值不会恢复到默认值
truncate(不会记录日志)删除后不可恢复,但效率高且不能用于有外键约束引用的表,删除数据后自动增长的值会恢复到默认值
5.备份数据
格式: create table 赋值到的表名 select *from 原表名;(备份全部数据)
create table 赋值到的表名 select *from 原表名 where 1=0(假条件);(只复制表结构,不复制数据)
create table 赋值到的表名 select *from 原表名 where 条件;(按条件复制数据)
create table 赋值到的表名 select 列名 原表名 ;(复制指定列数据)
代码练习示例:
一张表数据如下:
要求
1.统计每个班的学员的数量
2.统计每个班的总分
3.统计每个班的平均分
4.统计每个班的最高分
5.统计每个班的最低分
6.统计每个班学员的数量,总分,平均分,最高分,最低分
8.统计班级ID为T01的学员的数量,总分,平均分,最高分,最低分
9.查询平均分上85的班级有哪些?
10.查询有女生的班级是哪些?
实现代码:
--创建表
CREATE TABLE t_class (
name VARCHAR (20),
age INT,
gender CHAR (2),
stuNum VARCHAR (10),
score INT,
classNum VARCHAR (5)
);
--向表中添加数据
insert into t_class(name,age,gender,stuNum,score,classNum)values('Jacky',20,'男','xh1001',90,'T01');
insert into t_class(name,age,gender,stuNum,score,classNum)values('Simth',30,'男','xh1002',75,'T02');
insert into t_class(name,age,gender,stuNum,score,classNum)values('Jay',18,'男','xh1003',80,'T01');
insert into t_class(name,age,gender,stuNum,score,classNum)values('Helen',19,'女','xh1004',75,'T02');
insert into t_class(name,age,gender,stuNum,score,classNum)values('Liiy',22,'女','xh1005',90,'T03');
insert into t_class(name,age,gender,stuNum,score,classNum)values('Green',23,'男','xh1006',85,'T02');
insert into t_class(name,age,gender,stuNum,score,classNum)values('RedChar',18,'男','xh1007',60,'T01');
insert into t_class(name,age,gender,stuNum,score,classNum)values('Kevin',17,'女','xh1008',45,'T03');
-- 牢记SQL执行顺序:执行FROM ==> WHERE条件过滤 ==>GROUP BY分组 ==>执行SELECT投影列 ==>HAVING条件过滤 ==>执行ORDER BY排序
--解答
-- 1.统计每个班的学员的数量
select classNum as'班级', COUNT(*) from t_class GROUP BY classNum;
-- 2.统计每个班的总分
select classNum, sum(score) from t_class GROUP BY classNum ;
-- 3.统计每个班的平均分
select classNum,ROUND(AVG(IFNULL(score,0)),2) from t_class GROUP BY classNum;
-- 4.统计每个班的最高分
select classNum,MAX(score) from t_class GROUP BY classNum;
-- 5.统计每个班的最低分
select classNum,MIN(score) from t_class GROUP BY classNum;
-- 6.统计每个班学员的数量,总分,平均分,最高分,最低分
select classNum as'班级',COUNT(*) as'人数',SUM(score) as'总分',ROUND(AVG(IFNULL(score,0)),2) as'平均值',MAX(score) as'最高分',MIN(score) as'最低分' from t_class GROUP BY classNum;
-- 8.统计班级ID为T01的学员的数量,总分,平均分,最高分,最低分
select count(*) as'总人数',sum(score) as'总分',round(avg(IFNULL(score,0)),2) as'平均分',max(score) as'最高分',min(score) as'最低分' from t_class where classNum='T01';
-- 9.查询平均分上75的班级有哪些?
select classNum,round(avg(ifnull(score,0)),2) from t_class GROUP BY classNum having round(avg(ifnull(score,0)),2)>=75;
-- 10.查询有女生的班级是哪些?
select classNum,COUNT(*) from t_class where gender='女' group by classNum;
select * from t_class;