数据库入门

理论

关系型数据库:

概念：一列数据表示实体的一个属性，属性在数据库也称为字段。一行数据表示一个实体

主键：用于表示两个实体不同的列
外键：表示两个表之间的关系的那一个列
主表：被引用的表；
从表：引用别人的表

关系型数据库中的三种关系:
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;