[大数据]基本SQL语句(一篇就够了)

基本SQL语句(一篇就够了)

SQL(Structured Query Language)结构化查询语言，用于存取，查询，更新数据以及管理关系型数据库系统

SQL是1981年由IBM公司推出

SQL分类

根据SQL指令完成的数据库操作的不同，可以将SQL指令分为四类

• DDL Data Defintion language 数据库定义语言
  • 用于完成对数据库对象(数据表,数据库,视图,索引)的创建，删除，修改
• DML Data Manipulation language 数据操作语言
  • 用于完成对数据表中的数据添加，删除，修改
• DQL Data Query language 数据查询语言
  • 用于将数据表中的数据查询出来
• DCL Data Control Laguage 数据控制语言
  • 用于完成事务管理等控制型操作

SQL 基本语法

SQL指令不区分大小写

每条SQL表达式结束之后都以;结束

SQL关键字之间都以 空格进行分割

SQL之间可以不限制换行(可以有空格的地方就可以有换行)

DDL

DDL-数据库操作

使用DDL语句可以创建数据库，删除数据库，修改数据库

查询数据库

##显示mysql中所有是数据库

show databases;

##显示指定数据库的创建的SQL指令

show create database ;

创建数据库

##在mysql中创建一个数据库 dbName表示数据库名称

create database ;

##创建数据库，指定名称不存在的时候创建

create database if not exists ;

##在创建数据库的同时指定数据库的字符集(字符集：数据存储在数据库)

create database character set 字符集;

修改数据库

##修改数据库的字符集

alter database character set 字符集;

删除数据库

删除数据库时会删除数据库中所有的表以及数据库中的所有的数据

##删除数据库

drop database ;

##如果数据库存在则删除数据库

drop database if exists ;

使用/切换数据库

use ;

DDL-数据表的操作

创建数据表

数据表实际上就是一个二维的表格，一个表格是由多列组成的表格[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ae5Us90b-1637930805267)(/home/ddj/图片/2021-11-21 01-51-51 的屏幕截图.png)]

查询数据表

show tables;

查询表结构

desc ;

删除数据表

##删除数据表

drop table ;

##删除数据表如果表存在

drop table if exists ;

修改数据表

##修改表名

alter table rename to ;

##数据表也是有字符集的,默认字符集和数据库一致

alter table character set 字符集;

##添加字段(列)

alter table add ;

##修改的列表名和类型

alter table change ;

##只修改字段类型

alter table modify ;

##删除字段(列)

alter table drop ;

MySQL数据类型

数值类型

在MySQL中有多种数据类型可以有存放数值，不同类型存放的数值范围或者形式不同的

字符类型

存储字符序列的类型

日期类型

在MySQL数据库中,我们可以用字符串来存储时间,但是我们需要基于时间字段进行查询操作(查询在某个时间段内的数据)就不便于查询实现

字段约束

在创建数据表的时候,指定的对数据的列的数据限制性的要求(对表的列中的数据进行限制)

为什么给表中的数据添加数据呢?

• 保证数据的有效性
• 保证数据的完整性
• 保证数据的正确性

字段常见的约束有那些呢？

• 非空约束(not null):限制此列的值必须提供,不能null
• 唯一约束(unique):在表中的多条数据,此列的值不能重复
• 主键约束(primary key):非空+唯一,能够唯一标示数据表中的一条数据.
• 外键约束(foreign key):建立不同表之间的关联关系.

主键约束

主键–就是数据表中的记录中的唯一标识，在一张表中只能有一个主键(主键可以是一个字段,也可以是多个列组合)

当一个字段声明为主键之后，添加数据时;

• 此字段数据不能为null;
• 此字段数据不能重复;

创建表时添加主键约束

create table books(
    book_isbn char(4),
    book_name varchar(20) primary key
);

或者

create table books(
    book_isbn char(4);
    book_name varchar(20),
    primary key(book_name)
);

删除数据表主键约束

alter table books drop primary key;

创建表之后添加主键约束

## 创建表时没有添加主键约束
create table books(
    book_isbn char(4),
    book_name varchar(10) 
);

## 创建表之后添加主键约束
alter table books modify book_isbn char(4) primary key;

主键自动增长

在我们创建一张数据表时，如果数据表中有可以作为主键(列如:学生表的学号,图书表的isbn)我们可以直接设为这个字段为主键,

当有些数据没有合适的字段作为主键的时候，我们可以定义一个与记录无关的列(ID)作为主键

,此数据无具体含义主要作为标识唯一,在mysql中我们可以将此定义为int ，同事设置为自动增长

当我们想数据表中新曾一条数据时，无需提供ID列的值，它会自动增长.

定义主键自动增长(只能是数值类型)

auto_increment

create table types(
    type_id double primary key auto_increment,
    type_name varchar(20) not null,
    type_reamrk varchar(20)
);

注意：自动增长从1开始，每次添加一条数据，自动增长的列会+1，但是如果把某条数据删除，自动增长的数据也不会重复生产(自动增长的数据不会保证连续)

联合主键

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nE1DQ3Ug-1637930805271)(/home/ddj/图片/2021-11-21 15-18-40 的屏幕截图.png)]

定义联合主键

create table grades(
    stu_num char(8),
    course_id int,
    score int,
    primary key(stu_num,course_id)

);

注意:在实际企业项目的数据库设计中，联合主键使用频率不高，当一个数据表中没有明确的字段可以作为主键时，我们可以额外添加一个ID字段作为主键

外键约束

pass

DDL数据操作语言

用于完成对数据表中的数据中插入，删除，修改

插入数据

insert into <tableName>(colmnName,...)values(value1,....);

示例

## 向数据表中指定的列添加数据(不允许为空的必须提供数据)
insert into stus(stu_num,stu_name,sut_gender)values('202012321','张三','男',21,'130313131');
## 数据表名后字段名列表顺序可以与表名不一致，但是values中的值的顺序必须与表明后字段名顺序对应
## 当要向表中的数据所有列添加数据的时候，表名后面的字段列表可以省略，但是values中的值顺序要于数据表中定义的字段保持一致
## 建议把字段写出了(增强字段的稳定性)

###　删除数据

从数据表中删除满足特定条件(所有)的数据

delete from <tableName> where conditions; 

示例

## 删除学号为20210102的记录
delete from stus where stu_num=220210102;
## 删除年龄大于20岁的学生信息(如果多条数据满足情况则删除多条记录)
delete from stus where stu_age>20;
## 如果删除语句没有where子句，则表示删除当前数据表中的所有记录(敏感操作)
delete from stus;

修改数据

对数据表中已经添加的记录进行修改

update <tableName> set columName=值 [where conditions]

示例

## 将学号为20210105的学生姓名修改为'孙七'
update stus set stu_name='孙七'　where stu_num='20210105';
## 将学号20210103的学生　性别修改为'男'，同事QQ修改为'77777' (修改多列)　
update stus set sut_age='男',stu_qq='77777' where stu_num='20210103';

## 如果update语句没有where子句,则表示修改当前表中的所有行(记录)
update stus set stu_name='Tom'

DQL 数据查询语言

从数据表中提取满足特定条件的记录

• 单表查询
• 多表联合查询

单表查询

查询的基础语法

## select 关键字查询的是
select colnumName1,.. from <tableName>;
## 如果要显示查询到的的记录的所有列，可以使用*代替字段列表(在项目中不建议使用)
select * from <tableName>;

where子句

在删除，修改及修改的语句后都可以添加where子句(conditions)，用于筛选出满足特定的数据进行，删除，修改和查询操作．

delete from tableName where conditions;
update <tableName> set ... where contitions;
select ... from <tableName> where contitions;

条件

## ＝　等于
select * from stus where sut_num = '20202';
## != <>不等于
select * from stus where sut_num != '20202';
select * from stus where sut_num <> '20202';
## >大于
select * from stus where sut_age>18;
## <小于
select * from stus where sut_age<18;
## >=大于等于
select * from stus where sut_age>=18;
## <=小于等于
select * from stus where stu_age<=18;
## between and 区间查询
select * from stus where stu_age between 18 and 20;

多条件查询

在where子句中，可以将多个条件通过逻辑预算(and or not)进行连接,通过多个条件来筛选需要的操作

## and 并且　筛选多个条件同时满足的记录
select * from stus where stu_gender='女' and stu_age<21;
## or　或者
select * from stus where stu_gender='女'　or stu_gender='中间';
## not 取反
select * from stus where stu_age not between 18 and 21;

like 子句

在where子句中可以使用like关键字来实现模糊查询

select * from tableName where columName like 'reg';

• 在like关键字后的reg表达式中
  • %表示任意多字符［%o%表示含有o］
  • _表示任意一个字符

## 查询学生姓名包含字符o的学生信息
select * from stus where stu_name like '%o%';
## 查询学生姓名第一个字为张的学生信息
select * from stus where stu_name like '张%';
## 查询学生姓名最后一个字母为o的学生信息
select * from stus where stu_name like '%o';
##　查询学生姓名中第二个字母为o的学生信息
select * from stus where stu_name like '_o％';

对查询结果的处理

设置查询的列

声明显示查询结果的指定列

select colnumName,... from stus where stu_age>20;

计算列

对从数据表中查询的记录的列进行一定的运算之后显示出来.

## 出生年份　＝现在的年份-年龄
select stu_name,2021-stu_age from stus;

字段别名

我们可以为查询的结果的字段　取一个语义更强的别名

as 字段别名

##给2021-stu_age取一个别名(as　可以省略)
select stu_name,2021-stu_age as 出生　from stus;

消除重复行

distinct

从查询的结果中将重复的记录消除

select distinct stu_age from stus;

order by排序

将查询到的满足条件的记录按照指定的列的值升序/降序排列

select * from <tableName> where conditions by order by columnName asc|desc;

• order by columnName 表示将查询结果按照指定的列排序
  • asc 按照指定的列升序排序
  • desc 按照指定的列降序排序和查看表结构是同一个缩写(纯属巧合)

示例

##单字段排序
select * from stu order by sex desc;
##多字段排序  首先性别降序　然后id升序
select * from stu order by sex, id asc,

聚合函数

sql中提供了一些可以对查询记录进行列计算的的函数–聚合函数

• count() 统计函数,统计满足条件的指定字段的个数(记录数)

  • ## 统计学生个数
    select (id) from stu;
    ## 统计女生个数
    select count(sex) from stu WHERE sex='男';
    

• max()记录指定列中的最大值

• min()记录中指定列的最小值

• sum()记录中指定列的和

• avg()记录中指定记录的平均值

日期函数 和　字符串函数

日期函数

当我们向日期类型的列添加数据时,可以通过字符串类型(字符串格式必须是　yyyy-MM-dd hh:mm:ss)

如果我们要获取当前系统的时间添加到日期类型的列　可以使用now()或者sysdate()

curtime(); 返回当前时间
curdate();　返回当前日期

字符串函数

## upper(colynm)将字段的值转换成大写
select upper(name) from stu;
## lower(column)将指定列的值转换成小写
select upper(name) from stu;
## substring(column,start,len)从指定列中截取部分显示　start从1开始
select id,substring(name,4,8);

分组查询

select 分组字段/聚合函数　from 表名　[where 条件] group by 分组字段名;

• select 后使用*显示对查询的结果进行分组之后，显示每一组的第一条记录(这种显示通常是无意义的)
• select 后通常显示分组字段和聚合函数(对分组后的数据进行统计,求和,平均值等)

# 先对查询的学生信息按性别进行分组(分成男,女),然后分别统计每组学生的个数
select sex,count(sex)from stu group by sex;
# 先对查询的学生信息按性别进行分组　然后计算
select sex,avg(scroe) from stu group by sex;
# 先对学生按照班级分组　然后统计各组的学生数量，在排序
select age,count(stu) from stu group by age order by age asc;
# 查询所有学生,按年龄进行分组,然后分别统计每组>1的人数,再筛选年龄升序 (having　隐藏记录　有group by 才能有　having)
select age,count(num)
from stu
group by age
having count(num)>1
order by age asc;

分页查询

当数据表中的记录比较多的时候,如果一次性全部查询出来的显示给用户,用户的可读性/体验性就不太好,因为此我们可以将这些数据分页进行展示.

select ... from ... where ... limit param1,param2;

• param1 int 获取查询语句的结果的第一条数据的索引(索引从0开始)
• param2 int 获取查询语句的结果的条数(如果剩下的数据条数<pram2,则返回剩下的记录)

案例

对数据表中的学生信息进行分页显示,总共59条数据,我们每页显示20条

总记录数: count 59

每页显示 page 20

总页数 pageCount = cpimt%page ?count/page:count/page +1;

# 查询第一页:
select * from stu limit 0,20;
# 查询第二页
select * from stu limit 20,20;
# 查询第三页
select * from stu limit 40,20;
# 如果一张表中,pageNum表示查询的页码,pageSize表示查询每页的条数:
select * from stu limit (pageNum-1)*pageSize,pageSize;

数据表的关联关系

MySQL是一个关系型数据库,不仅可以存储数据,还可以维护数据与数据之间的关系–通过数据表中的数据字段的外键约束

数据与数据之间的关系分析分为四种

• 一对一关联
• 一对多关联
• 多对一关联
• 多对多关联

一对一关联

人–身份证

学生–学籍

用户–用户详情

方案一:主键关联–两张数据表中的主键相同的数据互相对应的数据

方案二:唯一外键–在任意一张表中添加外键约束与另一张表主键关联,并且将外键列添加唯一约束

一对多与多对一

班级—学生(一对多)一个班级可以包含多个学生

学生–班级(多对一) 多个学生可以属于同一个班级

图书–分类 商品–商品类别

多对多关联

学生–课程 一个学生可以选择多门课,一门课也可以由多个学生选择

会员–社团 一个会员

外键约束

外键约束–将一个列添加外键约束与另一张表的主键(唯一列)进行关联之后,这个外键约束添加的数据必须要在关联的主键字段中存在

案例:学生表 与 班级表(在学生表中添加外键与班级表的主键进行关联)

1. 先创建班级表

   create table classes(
   	class_id int primary key auto_increment,
   	class_name varchar(40) not null unique,
   	class_remark varchar(200)
   );
   

2. 创建学生表(在学生表中添加外键与班级表主键进关联)

# 在创建表的时候,定义cid 字段,并添加外键约束
# 由于cid列 要与classes表class_id进行关联,因此cid字段类型长度要与class_id一致
create table students(
 	stu_num char(8) primary key,
 	stu_name varchar(30) not null unique,
 	stu_gender char(2) not null,
 	stu_age int not null,
 	cid int,
 	constraint FK_STUDENTS_CLASSES foreign key(cid) references classes(class_id)
);
# 创建表之后为cid 添加外键约束
alter table students add constraint FK_STUENTS_CLASSES foreign key(cid) classes(class_id);

3. 向班级表添加班级信息

   insert into classes(class_name,class_remark) values('Python','...'),('mysql','...'),('JAVA','...');
   

4. 向学生表中添加学生信息

   insert into students (stu_num,stu_name,stu_gender,stu_age,cid)values('11111','杜东京','男',18,2);
   # 添加学生时,设置给cid外键列的值必须在其关联的主表classes的class_id列存在
   

   当学生表中存在的学生信息关联班级的某条记录时,就不能对班级表的这条记录进行修改ID和删除操作

delete from classes where class_id=2;
update classes set class_id=8 where class_id=2;
#SQL 错误 [1451] [23000]: Cannot delete or update a parent row: a foreign key constraint fails (`dp_test2`.`students`, CONSTRAINT `FK_STUDENTS_CLASSES` FOREIGN KEY (`cid`) REFERENCES `classes` (`class_id`))

修改和删除被关联表的记录或者ID

如果一定要修改Java2104的班级ID,该如何实现?

• 将应用Python班级的id的学生记录的cid修改为null
• 在修改班级信息中的Python记录的class_id
• 讲学生表中cid为NULL的记录设重新修改为 Python这个班级的class_id

UPDATE students set cid=null where cid=2;
UPDATE classes  set class_id=4 where class_id =2;
UPDATE students set cid = 4 where cid  is null;

我们可以通通过级联操作实现:(on cascade)

1. 在添加外键时,设置级联修改和级联删除

# 删除原有的外键
alter table sutdents drop foreign key FK_STUDENTS_CLASSES;
# 重新添加外键,并设置级联修改和级联删除
alter table students add constraint FK_STUDENTS_CLASSES foreign key(cid) references classes(class_id) on update cascade on delete cascade;

• 当添加on delete cascade声明后 删除班级某记录之后 学生表外键绑定此记录的都会被删除
• 当添加on update cascade声明后 修改班级记录的主键 学生表与之关联的外键都会跟着改变

连接查询

通过DQL的学习,我们可以很轻松的从一张数据表中查询出需要的数据:在企业的应用开发,我们经常需要从多张表中查询数据(列如:我们查询学生信息的时候),可以通过连接查询从多张数据表提取数据:

在MySQL中可以使用join实现多表的联合查询–链接查询,

• inner join 内连接
• left join 左连接
• right join 右连接

内连接

select ... from tableName inner join tableName2;

笛卡尔积

• 笛卡尔积(A集合&B集合):使用A中的每个记录依次关联B中每个记录,笛卡尔集的总数=A总数*B总数
• 如果直接执行select … from tableNmae1 inner join tableName2; 会获取两张数据表中的数据集合的笛卡尔积(依次使用tableName1表中的每条数据 去 匹配tableName2的每条数据)

内连接条件

两张表同时用inner join连接查询之后产生笛卡尔积数据很多是无意义的,我们如何消除无意义的数据----添加两张进行连接的查询时的条件

• 使用on设置两张表连接查询的匹配条件

  -- 使用where设置过滤条件:先生成笛卡尔积再从笛卡尔积中过滤数据(效率很低)
  select * from students inner join classes where students.cid=classes.class_id;
  -- 使用ON设置连接查询条件:先判断连接条件是否成立,如果成立两张表的数据进行组合在生成一条结果记录
  select * from students inner join classes on students.cid=classes.class_id;
  

  结果:只取两种表中匹配条件成立的数据,任何一张表在另一张表如果没有找到对应匹配则不会出现在查询结果中(例如:小红和小明没有对应的班级信息 python课没有被人选)

  左连接 LEFT JOIN

  需求:查询出所有的学生信息,如果学生有对应的班级信息,则将对应的班级信息也查询出来.

  左连接:显示左表中的所有数据,如果在右表中满足条件的数据,则进行匹配;如果右表中不存在匹配数据,则显示为null

  select * from leftTable join rightTable on 匹配条件;
  

  右连接 RIGHT JOIN

  右连接:示右表中的所有数据,如果在右表中满足条件的数据,则进行匹配;如果左表表中不存在匹配数据,则显示为null

数据表别名

如果连接查询的多张表中存在相同名字的字段,我们可以使用表名.字段名来分区,如果表名太长则不便于SQL语句的编写,我们可以使用数据表别名

select s.*,c.class_name from students s inner join classes c  on s.cid=c.class_id;

子查询/嵌套查询

子查询–先进行一次查询,第一次查询的结果作为第二次查询的/条件(源) (第二次查询是基于第一次查询的结果进行的)

案例:查询班级班级名为(mysql)的学生信息(只知道班级名称,而不知道班级ID)

• 传统方式

-- a.查询mysql的班级的编号
select class_id from classes where class_name='mysql';
-- b.查询此班级编号的学生信息
select * from students where cid=2

子查询单行单列

• 子查询:

select * from students where cid=(select class_id from classes where class_name='mysql');

案例2:查询所有Java班级中的学生信息

• 传统方式

-- a查询所有Java班的班级编号
select class_id from classes where class_name like 'Java%';
-- b查询这些班级编号中的学生信息(union 将多个查询语句的结果整合到一起)
select * from students where cid = 1
union
select * from students where cid = 2
union
select * from students where cid = 3

子查询多行单列

• 子查询

-- 如果查询返回的结果是多个值(单列多行),条件使用**in / not in**
select * from students where cid in (select class_id from classes where class_name like 'mysql%');

子查询返回多个值,多行多列

案例3:查询cid=1的班级中性别为男的学生信息

-- 传统的多条件查询
select * from students where cid=1 and stu_gender='男';
-- 子查询(先查询cid=1班级中的所有信息,将这些信息作为一个整体虚拟表 在基于这个虚拟表查询性别为男的学生信息)
select * from (select * from students where cid=1) t where t.stu_gender='男';