sql语句

夭氘

于 2022-12-16 14:39:55 发布

阅读量388

点赞数

文章标签： sql 数据库 oracle

本文链接：https://blog.csdn.net/qq_37732597/article/details/106569319

版权

注释

# 一个井号键注释后面的代码
-- 两个短横杠注释后面的代码
/*  前后斜杠和星号可以注释连续多行的代码  */

创建用户并授权

create user ***（用户名） identified by ***(密码) ;
grant resource, connect, DBA  to ***（用户名）;

删除旧同名表（**为表格名

Declare
tmp integer default 0;
Begin
select count(*) into tmp from user_tables where table_name='**';
if(tmp>0) then
execute immediate 'drop table **';
end if;
select count(*) into tmp from user_tables where table_name='**';
if(tmp>0) then
execute immediate 'drop table **';
end if;
select count(*) into tmp from user_tables where table_name='**';
if(tmp>0) then
execute immediate 'drop table **';
end if;
end;

创建表格 create

create table **
(
 属性名 类型(长度)  /特殊属性 ;
 属性名 类型(长度)  /特殊属性 ;
属性名 类型(长度)  /特殊属性 
);

类型：integer  varchar(长度)
特殊属性：primary key	主键
not null    不为空
Unique    唯一

Create table student
(
S_id varchar(255) not null;
S_num varchar(255) not null;
S_sex varchar(255) ;
Unique (s_id)
//constraint  uc_studentid unique (s_id ,s_num)    多个unique约束
)

添加unique约束
Alter table student
Add unique (s_id)
//add constraint uc_studentid unique (s_id,s_num) 

撤销unique约束
Alter table student
Drop index uc_studentid

创建与表A相同结构的表B并向其中插入特定数据（表B有相同的属性，但没有约束值）
CREATE TABLE B 
SELECT * FROM A 
WHERE +条件

插入数据 insert

插入全属性数据：
insert into 表格名 
values(‘ ** ’ , ’ ** ’ , ‘ ** ’ , ‘ ** ’ , ’ ** ’ , ’ ** ’);

插入部分属性数据：
insert into 表格名 (属性名,属性名)
values (‘**’ , ‘**’);

向B表插入A表的数据（两个表格的属性完全一致）
insert into B 
SELECT * FROM A 
WHERE +条件

修改/更新 update

update 表名
set 属性名=’ 新值 ’ , 属性名=’新值’
where 属性名=’ ** ‘ ;

--交换表中的性别，及男变成女，女变成男
update student
set sex=if(sex='男','女','男')
--if(sex=a,b,c)即如果sex=a，则将sex=a修改为sex=b，否则修改为sex=c

删除 delete/drop

删除表的所有行，保留表的结构：
delete *  from 表名

删除表的某一行：
delete  from 表名
where 表属性=’**’

删除A表中与B表相同的数据
delete from A
using A,B
where A.属性=B.属性

删除表内容保留结构：
turncate table 表名 ；;

删除表格：
drop table 表名;

删除数据库：
drop database 数据库名 ；

删除索引
alter table 表名
drop index 索引名 ;

语句顺序

SELECT DISTINCT **
FROM ***
** JOIN ** 
ON **
WHERE **
GROUP BY **
HAVING ** 
ORDER BY **
LIMIT **

查询 select

查询全属性：
select  * from  表格名
where 属性名=’**’ ;

查询部分属性：
select 属性名 from  表格名
where 属性名=’**’ ;

查询不同的值：
select distinct 属性名 from 表名 ;
当distinct后接多给属性时，表示多个属性不完全相同，而不单单指一个
select distinct name,sex from student ;

查询表结构
desc 表名

查询限定结果
select name from student
limit 3 offset 4;             --表示从第五（4+1）行开始查询三个结果
                              --首行为第0行，而非第1行

过滤数据，条件限定 WHERE

操作符说明
=  等于      <>  不等于      !=  不等于  
<  小于      <=  小于等于    !<  不小于 
>  大于      >=  大于等于    !>  不大于  
between...and  之间      is null  空

--限定条件为数字时，直接加上数字进行比较
select price from products
where price < 10;

--限定条件为字符串时，需要加单引号进行识别
select id from products
where id <>'davie' ;

--between...and 条件检查
select price form products
where price between 5 and 10;

--is null条件检查，不能写成 =null
select name from products
where price is null;

--and  or连用（要用括号分隔开，否则会错乱）
select name from products
where (name='a' or name='b') and price<10 ;

--in用法
select name from products
where name in ('a' , 'b');

--not用法（查询条件外的任何值）
select name from products
where not name='a'

通配符过滤条件

LIKE
#   %表示任意长度字符串
-- 查找以 la 开头的名字（查找内容区分大小写）
select name from products
where name like 'la%' ;

--查找包含 jane ie 的名字，不管前后出现任意字符
select name from products
where name like '%jane ie%' ;

--查找以 k 开头并且以 a 结尾的名字
select name from products
where name like 'k%a' ;

#  _表示一个字符
--查找第一个字符任意，剩下三个字符为 ame 的名字
select name from products
where name like '_ame' ;

正则表达式
regexp
#  ^ 匹配文本开始字符
--查询以 l 开头的名字
select name from products
where name regexp '^l' ;

#  $ 匹配文本结束字符
--查询以 d 结尾的名字
select name from products
where name regexp 'd$' ;

# . 匹配任意一个字符
--两个字母之间存在一个字符的名字，不管前后有没有，都符合要求
select name from products
where name regexp 'e.e' ;
--(lele ela esel均符合要求)

#  * 匹配任意多个字符
select name from products
where name regexp 'l*r'
--(只要名字中包含 l 或者 r ，均符合要求)

#  + 匹配其前面字符至少一次
select name from products 
where name regexp 'li|e|m+' ;
--(i e m 三个字母至少出现一次，就符合要求)

--包含某个或某几个字符
select name from products 
where name regexp 'le' ;
--（只要名字中出现 le ，即符合要求）

#  [] 匹配中括号中任意一个字符即可
select name from products
where name regexp '[le]'
--（只要名字中出现 l 或者 e 即符合要求）

#  [^]匹配不包含在指定集合中的任何字符
select price from products
where price regexp '[^10-15]' ;
--价格为10-15均不符合要求，剔除

统计人数 count

Select count(属性) from 表格

Select count(s_id) from student  //指定列
Select count(*) from student    //表中的记录值
Select count(distinct s_name) from student  //合并同名记录，返回不同名个数

连接 join
特别注意：条件查询用 on ！！！

内连接：join / inner join 只有满足条件，才返回值
Select 属性 From  A表（左表）
Inner join B表（右表）
On  A表属性=B表属性（条件）

左连接：left join   左表记录全部列出，如果左表有右表没有，则对应属性为空
                                    如果左表没有右表有，则记录不列出
Select 属性 From  A表（左表）
Left  join B表（右表）
On  A表属性=B表属性（条件）

右连接：right join  右表记录全部列出，如果右表有左表没有，则对应属性为空
                                    如果左表有右表没有，则记录不列出
Select 属性 From  A表（左表）
Right  join B表（右表）
On  A表属性=B表属性（条件）

全连接：full join  左右表记录全部列出，如果右表有左表没有，则对应属性为空
                                    如果左表有右表没有，则对应属性为空
Select 属性 From  A表（左表）
Full  join B表（右表）
On  A表属性=B表属性（条件）

多表查询

select a.id ,b.num from a and b
where a.id=b.id

排序查询指定数据

order by默认升序，若降序，则用desc

排序检索
--按名字检索，默认升序，order by应是最后一条语句，否则会出错
select name from student
order by name;

可同时多个列排序
--先按第一个排序，如果相同，再按第二个排序
select id,price,name from products
order by price,name;

若多个排序都用降序，则每个列名后都要加desc
select id,price,name from products
order by price desc ,name desc ;

可以按列的相对位置指定排序
--按第二列排序，若第二列相同，再按第三列排序
select id ,price ,name from products
order by 2,3;

查询最高
select max(salary) from employee 

查询第二高的薪水，给定名称
select
 ( select max(salary) from employee where salary<(select max(salary) from employee))
 as  SecondHighestSalary

薪水排序
select salary form employee order by salary desc

查询第N高薪水
select salary from employee order by salary desc limit N-1,1
(limit 2,1 代表除第一个外，排序第二的元素，即第三)

同个表格连接多次

--用到别称时，若表名用别称，则直接在表名后空格加别名，若属性名用别名，则需要加as
select a.name as employee (as为查询结果以什么名称出现）
from employee  a ,employee  b （a,b为别称）
where a.xxx=b.xxx and ------

计算字段
拼接字段

--数据库中有两列信息，但输出时我们希望只当一个列值输出
select contact(name , ' (' ,country, ')' )  from country
as title
order by name ;
--contact是给这个组合一个新名字，输出格式为：name      (country     )
--as 后为别名，赋予新值一个列名，方便以后直接调用

--使用RTRIM()函数,RTRIM函数去掉右边所有空格
select RTRIM(name) + ' (' + RTRIM(country) + ')' from country
AS title
order by name ;
--输出格式为：name(country)

--执行算数计算
select name,price,num,price*num as total_price  from products
where id=1000;
--单价乘以数量作为总价输出

函数处理

# UPPER（str）函数  统一将字符转换为大写
select name ,UPPER(name) as name_upper 
from products;

# soundex(str)函数 发音相似匹配
select name from products
where soundex(name)=soundex('xiao pin');

函数             作用
left(str,len)    返回字符串左边len个字母 
right(str,len)   返回字符串右边len个字母
length(str)      返回字符串长度
upper(str)       统一将字符转为大写
lower(str)       统一将字符转为小写
ltrim(str)       去掉字符串左边空格
rtrim(str)       去掉字符串右边空格
soundex(str)     统一转化为字母数字格式，用于发音相似匹配

--year（）函数。查找日期中对应的年份
select name from products
where year(createtime)=2020;
--查询2020年生产的产品名称

--数值处理函数
函数
abs(x)         将x绝对值化,也可将某一列的值绝对值化
cos(x)         返回x角度的余弦值
sin(x)         返回x角度的正弦值
exp(x)         返回指数值，e的x次方
pi()           返回圆周率
sqrt(x)        返回x的平方根
tan(x)         返回x角度的正切值

查询每门功课最好的两名学生的姓名

SELECT a.`课程号`,a.`成绩` FROM score a
WHERE 2>(SELECT COUNT(*) FROM score WHERE `课程号`=a.`课程号` AND `成绩`>a.`成绩`)
ORDER BY `课程号`,`成绩` desc

--查询每个部门工资最高的员工（若最高工资存在多个，则均输出）
select b.Name as Department , a.Name as Employee , a.Salary
from Employee a join Department b 
on a.DepartmentId=b.Id
where (a.DepartmentId , a.Salary) in (
    select DepartmentId, max(Salary) from Employee
    group by DepartmentId
)

限定条数的数据中某个属性值有重复数据，计算该属性不重复的值有多少

1、查询表中前1000条数据的vin码
SELECT
		vin 
	FROM
		`10月第2周` 
	WHERE
		`车辆品牌` = '玛莎拉蒂' 
		AND LENGTH( vin ) = 17 
		AND decode_source_name = '纯人工译码' 
	ORDER BY
	vin 
	LIMIT 1000
2、计算1000条数据中不重复的vin码有多少
错误语句：
select 
	count(distinct vin)
from
	`10月第2周`
where
	vin in (
	SELECT
		vin 
	FROM
		`10月第2周` 
	WHERE
		`车辆品牌` = '玛莎拉蒂' 
		AND LENGTH( vin ) = 17 
		AND decode_source_name = '纯人工译码' 
	ORDER BY
	vin 
	LIMIT 1000
	)
错误原因在于：mysql语句不支持in的子查询包含limit限定条件

正确语句：
SELECT 
	COUNT(DISTINCT vin)
FROM
	`10月第2周` 
WHERE
	vin IN (
	SELECT
		vin 
	FROM
	(SELECT
		vin 
	FROM
		`10月第2周` 
	WHERE
		`车辆品牌` = '玛莎拉蒂' 
		AND LENGTH( vin ) = 17 
		AND decode_source_name = '纯人工译码' 
	ORDER BY
	vin 
	LIMIT 1000) a
	)
将查询到的1000条数据的vin码作为一个表a再查询一次，则第二层子查询不包含limit

查询vin码存在于A表但不存在于B表（NOT IN）

错误sql语句：
SELECT DISTINCT
	vin 
FROM
	A,
	B 
WHERE
	A.vin != B.vin

正确解法：
SELECT DISTINCT
	vin 
FROM
	A 
WHERE
	vin NOT IN ( SELECT DISTINCT vin FROM B )

新增序号字段并对指定字段进行排序

1. 连续排名：row_number()
SELECT  s.score,(@i:=@i+1) as sort 
FROM scores s,(SELECT @i:=0) r
ORDER BY s.score desc 
或
select Score , row_number() over(order by Score desc) as 'Rank'
from Scores

2.连续并列排名：dense_rank()
select Score , dense_rank() over(order by Score desc) as 'Rank'
from Scores

3. 并列跳跃排名：rank()
select Score , rank() over(order by Score desc) as 'Rank'
from Scores

可参考下列表格排序
|Score|row_number| dense_rank|	 rank|
|	95|			1|			1|		1|
|	95|			2|			1|		1|
|	90|			3|			2|		3|
|	85|			4|			3|		4|