SQL分类

SQL简介

• 结构化查询语言，操作关系型数据库
• 定义操作所有关系型数据库的统一标准
• 对同一个需求，每一种数据库擦欧总的方式可能会存在一些不一样的地方

1、sql语句以分号结尾

2、不区分大小写
3、注释
单行：–（空格！！！）注释内容 或 #注释内容

多行：/*注释 */

SQL分类

• DDL:操作数据库、表；
• DML:对表中数据进行增删改；
• DQL:对表中数据进行查询
• DCL:数据控制语言，对数据库进行权限控制

DDL

数据定义语言，用于定义数据库对象：数据库、表、列等；

操作数据库

• 创建数据库
  
  若在创建时不知道某个数据库已经存在，重复创建时会出错
  
  避免上述情况，在创建时先进行判断，若不存在再创建
  
• 删除数据库
  
• 使用数据库
  

操作表

即对表进行增删改查

• 查询当前数据库下所有表
• 查询表结构
  

• 创建表
  
• 数据类型
  （资料：MySQL数据类型.xlsx）
  • 数值类型
    • tinyint:小整数型，一个字节
    • int：大整数类型，4个字节
      • age int
    • double：浮点类型，字段名 double(总长度，小数点后保留的位数）
      • score double(5,2)
• 日期和时间类型
  • date:日期值，只包含年月日
    • birthday date
  • datatime:混合日期和时间值
• 字符串类型
  • char:定长字符串；存储性能高 浪费空间
    • name char(10) 张三10个字符空间
  • varchar:变长 存储性能低 节约空间
    • name varchar(10) 张三2个字符空间
• 删除表
  
• 修改表
  • 改表名
    
  • 添加一列
    
  • 修改数据类型
    
  • 修改列名和数据类型
    
  • 删除列
    

DDL案例

需求：设计一张学生表，请注重数据类型、长度的合理性 
1. 编号 
2. 姓名，姓名最长不超过10个汉字 
3. 性别，因为取值只有两种可能，因此最多一个汉字 
4. 生日，取值为年月日 `在这里插入代码片`
5. 入学成绩，小数点后保留两位 
6. 邮件地址，最大长度不超过 64 
7. 家庭联系电话，不一定是手机号码，可能会出现 - 等字符 
8. 学生状态（用数字表示，正常、休学、毕业...）

-- 删除表
drop table if exists student;

-- 创建表
create table student(
	id int,
	name varchar(10),
	sex char(1),
	birthday date,
	score double(5,2),
	email varchar(64),
	tel varchar(20),
	status tinyint(1)
);

DML

数据操作语言，对表中数据进行增删改。

添加数据

-- 给指定列添加数据 指定要添加的列名
INSERT INTO stu(id, NAME) VALUES (1, '张三'); 
-- 给所有列添加数据，列名的列表可以省略的 
INSERT INTO stu(id,NAME,sex,birthday,score,email,tel,STATUS) VALUES(2,'李四','男','1999-11- 11',88.88,'lisi@itcast.cn','13888888888',1); 
INSERT INTO stu VALUES (2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1); 
-- 批量添加数据 
INSERT INTO stu VALUES 
		(2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1),
		(2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1),
		(2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1);

修改数据

-- UPDATE 表名 SET 列名1=值1,列名2=值2,… [WHERE 条件] ;

-- 将张三的性别改为女
update stu set sex='女' where name='张三';

-- 将张三的生日改为1999-12-12，分数改为99.99
update stu set birthday='1999-12-12',score='99.99' where name='张三';

-- 注：若update语句没有加where条件，则会将表中所有数据全部修改
update stu set sex='女';

删除数据

-- DELETE FROM 表名 [WHERE 条件] ;

-- 删除张三数据记录
delete from stu where name='张三';
-- 删除stu表中所有数据
delete from stu ;

DQL

数据查询语言，查询表中的数据（记录）

-- SELECT字段列表 FROM表名列表 WHERE条件列表 GROUP BY 分组字段 HAVING分组后条件 ORDER BY 排序字段 LIMIT分页限定

基础查询

-- 查询多个字段（用逗号隔开）  SELECT 字段列表 FROM 表名;
-- 查询name age两列
select name,age from stu;
-- 查询所有列的数据，列名的列表可以使用*替代。不建议使用*，不方便阅读
select * from stu;
-- 查询地址信息
select address from stu;

-- 去除重复记录（distinct）   SELECT DISTINCT 字段列表 FROM 表名;
select DISTINCT address from stu;
-- 起别名（as ,as也可省略）
-- 查询姓名，数学成绩，英语成绩
select name,math 数学成绩,english as 英语成绩 from stu;

条件查询

-- SELECT 字段列表 FROM 表名 WHERE 条件列表;
-- 1.查询年龄大于20岁的学员信息
select * from stu where age > 20;

-- 2.查询年龄大于等于20岁的学员信息
select * from stu where age>=20 ;

-- 3.查询年龄大于等于20岁 并且 年龄小于等于30岁的学员信息
select * from stu where age>=20 and age<=30;
select * from stu where age>=20 && age<=30;
select * from stu where age between 20 and 30;
-- and和&&都表示并且
-- 4.查询入学日期在 '1998-09-01’ 到’1999-09-01’ 之间的学员信息
select * from stu where hire_date between '1998-09-01' and '1999-09-01';

-- 5.查询年龄等于18岁的学员信息
select * from stu where age=18;

-- 6.查询年龄不等于18岁的学员信息
select * from stu where age !=18;
select * from stu where age <>18;

-- 7.查询年龄等于18岁 或年龄等于20岁 uo年龄等于22岁的学员信息
select * from stu where age=18 or age=20 or age=22;
select * from stu where age in(18,20,22);

-- 8.查询英语成绩为null的学员信息
select * from stu where english is null;
-- 注：null值的比较不能使用=或 !=，要用is 或 is not
select * from stu where  english is not null;

模糊查询

-- 模糊查询 like ==========
/*
	通配符：
	（1）_:代表单个任意字符
	（2）%:代表任意个数字符
*/
-- 1.查询姓'马'的学员信息
select * from stu where name like '马%';

-- 2.查询第二个字的'花'的学员信息
select * from stu where name like '_花%';

-- 3.查询名字中包含'德'的学员信息
select * from stu where name like '%德%';

排序查询

/*
排序查询
	1.语法：SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1 [排序方式1],排序字段名2 [排序方式2] …;
	2.排序方式：
		ASC:升序排列（默认值）
		DESC:降序排列
*/

-- 1.查询学生信息，按照年龄升序排列
select * from stu order by age;
select * from stu order by age asc;

-- 2.查询学生信息，按照数学成绩降序排列
select * from stu order by math desc;

-- 3.查询学生信息，按照数学成绩降序排列，如果数学成绩一样，再按照英语成绩升序排列
select * from stu order by math desc,english asc;

-- 注：若有多个排序条件，当前面的条件值一样时，才会根据第二条件进行排序

分组查询

聚合函数

将一列数据作为一个整体进行纵向计算
/*
	count 统计数量 取值：1 主键，2 *
	max 最大值
	min 最小值
	sum 求和
	avg 求平均值
*/

-- 1.统计班级一共有多少个学生
select count(id) from stu; -- count统计的列名不能为null
select count(*) from stu; 

-- 2.查询数学成绩的最高分
select max(math) from stu;

-- 3.查询数学成绩的最低分
select min(math) from stu;

-- 4.查询数学成绩的总分
select sum(math) from stu;

-- 5.查询数学成绩的平均分
select avg(math) from stu;

-- 6.查询英语成绩的最低分
select min(english) from stu;

-- 注：null不参与所有聚合函数运算

分组查询

/*
	分组查询
 select 字段列表 from 表名 [where 分组前条件限制] group by 分组字段名 [having 分组后条件过滤]
 执行顺序：where>聚合函数>having
 不满足where条件不参与分组，having分组之后对结果进行过滤
 where不可对聚合函数进行判断，having可以
*/

-- 1.查询男同学和女同学各自的数学平均分
-- select avg(math) from stu where sex='男';
-- select avg(math) from stu group by sex;
select sex,avg(math) from stu group by sex;
-- 注：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义

-- 2.查询男同学和女同学各自的数学平均分，以及各自人数
select sex,avg(math),count(*) from stu group by sex;

-- 3.查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组
select sex,avg(math),count(*) from stu where math>70 group by sex;

-- 4.查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组，分组之后人数大于2
select sex,avg(math),count(*) from stu where math>70 group by sex having count(*)>2;

分页查询

/*
	分页查询：将数据一页一页的展示给用户看，用
户也可以通过点击查看下一页的数据
	select 字段列表 from 表名 limit 起始索引,查询条目数;
  起始索引:从0开始	起始索引=（当前页码 - 1）*每页显示条数
	limit只在MySQL中，Oracle使用rownumber，SQL server 使用top
*/

select * from stu;
-- 1.从0开始查询，查询3条数据
select * from stu limit 0,3;

-- 2.每页显示3条数据，查询第1页数据
select * from stu limit 0,3;

-- 3.每页显示3条数据，查询第2页数据
select * from stu limit 3,3;

-- 4.每页显示3条数据，查询第3页数据
select * from stu limit 6,3;

约束

• 作用于表中列上的规则，限制表的数据
• 保证数据的正确性、有效性、完整性
  分类：

1. 非空not null，保证列中所有数据不能有null值
2. 唯一nuique，保证所有数据各不相同
3. 主键primary key，唯一标识一行数据，要求非空且唯一
4. 检查check（MySQL不支持）保证列中的值满足某一条件
5. 默认default，保存数据时未指定值采用默认值

-- 员工表
create table emp(
	id int primary key auto_increment, -- 员工id，主键且自增长
	ename varchar(50) NOT NULL UNIQUE, -- 员工姓名，非空并且唯一
	joindate date NOT NULL, -- 入职日期，非空
	salary double(7,2) NOT NULL, -- 工资，非空
	bonus double(7,2) default 0 -- 奖金，如果没有奖金默认为0

);

6. 外键foreign key,让两个表的数据之间建立链接，保证数据的一致性完整性

添加外键：
-- create table 表名（列名 数据类型,...,constraint 外键名称 foreign key (外键字段名称) references 主表名称（主表列名称）
-- alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称（主表列名称）
删除外键：
-- alter table 表名 drop foreign key 外键名称；

-- 创建表时添加
CREATE table emp(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dep_id int,

	-- 添加外键 dep_id 关联dept表的id主键
	CONSTRAINT fk_emp_dept FOREIGN KEY (dep_id) REFERENCES dept(id)

-- 添加外键（建完表后添加）
alter table emp add constraint fk_emp_fept foreign key (dep_id) references dept(id);
);

-- 删除外键
alter table emp drop foreign key fk_emp_dept;