SQL常见用法

常见用法"/>

SQL常见用法

2019独角兽企业重金招聘Python工程师标准>>>

前言:

            据说熟练了掌握了SQL语句,可以节省大部分时间,为什么这样说呢!

            其实一些SQL语句就可以实现业务上的功能 , 掌握了数据就get了一项新技能 ,

            对于任何公司,数据难道不都是重中之重 ?

一、通用SQL语句

1.表分组查询取每组第一

select t.* from  (select table_name.*,row_number() over (partition by name order by user_id desc) rn from table_name) as twhere rn=1

2. 字段值数据类型转换  

标准的SQL函数
1、COALESCE — 空值替换函数。
2、regexp_split_to_table — 行专列

--1.COALESCE()SELECT COALESCE(name, 'no') FROM users 
--如果name列的值为null，则name的值将被替换为'no'--2.regexp_split_to_table()SELECT regexp_split_to_table(col, 'splitor') FROM users--如果col的值为‘1,2'，而splitor为','，则该条记录被转换成5条记录，且各条记录的col列的值依次为1、2

3.自建函数的使用

 create function average1(@cnum char(20)) --创建一个带参数的函数  returns int --返回值为一个int类型的整数  as  begin  declare @aver int  --声明一个@aver变量  select @aver=    --查询这个变量  (  --给变量赋值  select COUNT(*) from user where name = @cnum  group by sex  )  return @aver  --返回变量  end  go  select dbo.average1('李四') --调用刚刚创建的函数average1  

4.字符串的截取 

1. 字符串函数应用

--从指定索引截取指定长度的字符串
SELECT substring('abcdefg',2,5) 
--获取字符串中指定字符的索引（从1开始）
select charindex(',','ab,cdefg')
--实际应用中的语句
select substring(money,0,charindex('.',money)) as money from table_name

2. 日期函数应用

--获取三个月前的时间
SELECT DATEADD(month, -3,getdate())

5.分页查询相关语句

--Mysql数据库实现：查询语句
select * from user limit 0,10--Oracle数据库实现：查询语句
select* from(select s.*,rownum rn from(select * from user)s where rownum<=10)where rn>=1

一、定义变量
--简单赋值 
declare @a int
set @a=5 
print @a --使用select语句赋值 
declare @user1 nvarchar(50) 
select @user1='张三'
print @user1 
declare @user2 nvarchar(50) 
select @user2 = Name from ST_User where ID=1 
print @user2 --使用update语句赋值 
declare @user3 nvarchar(50) 
update ST_User set @user3 = Name where ID=1 
print @user3二、表、临时表、表变量--创建临时表1 
create table #DU_User1 
( [ID] [int]  NOT NULL, [Oid] [int] NOT NULL, [Login] [nvarchar](50) NOT NULL, [Rtx] [nvarchar](4) NOT NULL, [Name] [nvarchar](5) NOT NULL, [Password] [nvarchar](max) NULL, [State] [nvarchar](8) NOT NULL
); 
--向临时表1插入一条记录 
insert into #DU_User1 (ID,Oid,[Login],Rtx,Name,[Password],State) values (100,2,'LS','0000','临时','321','特殊'); --从ST_User查询数据，填充至新生成的临时表 
select * into #DU_User2 from ST_User where ID<8 --查询并联合两临时表 
select * from #DU_User2 where ID<3 union select * from #DU_User1 --删除两临时表 
drop table #DU_User1 
drop table #DU_User2--创建临时表 
CREATE TABLE #t 
( [ID] [int] NOT NULL, [Oid] [int] NOT NULL, [Login] [nvarchar](50) NOT NULL, [Rtx] [nvarchar](4) NOT NULL, [Name] [nvarchar](5) NOT NULL, [Password] [nvarchar](max) NULL, [State] [nvarchar](8) NOT NULL, 
) --将查询结果集（多条数据）插入临时表 
insert into #t select * from ST_User 
--不能这样插入 
--select * into #t from dbo.ST_User --添加一列，为int型自增长子段 
alter table #t add [myid] int NOT NULL IDENTITY(1,1) 
--添加一列，默认填充全球唯一标识 
alter table #t add [myid1] uniqueidentifier NOT NULL default(newid()) select * from #t 
drop table #t
--给查询结果集增加自增长列 --无主键时： 
select IDENTITY(int,1,1)as ID, Name,[Login],[Password] into #t from ST_User 
select * from #t --有主键时： 
select (select SUM(1) from ST_User where ID<= a.ID) as myID,* from ST_User a order by myID
--定义表变量 
declare @t table
( id int not null, msg nvarchar(50) null
) 
insert into @t values(1,'1') 
insert into @t values(2,'2') 
select * from @t三、循环--while循环计算1到100的和 
declare @a int
declare @sum int
set @a=1 
set @sum=0 
while @a<=100 
beginset @sum+=@a set @a+=1 
end
print @sum
四、条件语句--if,else条件分支 
if(1+1=2) 
beginprint '对'
end
else
beginprint '错'
end--when then条件分支 
declare @today int
declare @week nvarchar(3) 
set @today=3 
set @week=casewhen @today=1 then '星期一'when @today=2 then '星期二'when @today=3 then '星期三'when @today=4 then '星期四'when @today=5 then '星期五'when @today=6 then '星期六'when @today=7 then '星期日'else '值错误'
end
print @week五、游标declare @ID int
declare @Oid int
declare @Login varchar(50) --定义一个游标 
declare user_cur cursor for select ID,Oid,[Login] from ST_User 
--打开游标 
open user_cur 
while @@fetch_status=0 
begin
--读取游标 fetch next from user_cur into @ID,@Oid,@Login print @ID --print @Login 
end
close user_cur 
--摧毁游标 
deallocate user_cur
六、触发器触发器中的临时表：Inserted 存放进行insert和update 操作后的数据 Deleted 存放进行delete 和update操作前的数据--创建触发器 
Create trigger User_OnUpdate  On ST_User  for Update 
As declare @msg nvarchar(50) --@msg记录修改情况 select @msg = N'姓名从“' + Deleted.Name + N'”修改为“' + Inserted.Name + '”' from Inserted,Deleted --插入日志表 insert into [LOG](MSG)values(@msg) --删除触发器 
drop trigger User_OnUpdate
七、存储过程--创建带output参数的存储过程 
CREATE PROCEDURE PR_Sum @a int, @b int, @sum int output
AS
BEGINset @sum=@a+@b 
END--创建Return返回值存储过程 
CREATE PROCEDURE PR_Sum2 @a int, @b int
AS
BEGINReturn @a+@b 
END--执行存储过程获取output型返回值 
declare @mysum int
execute PR_Sum 1,2,@mysum output
print @mysum --执行存储过程获取Return型返回值 
declare @mysum2 int
execute @mysum2= PR_Sum2 1,2 
print @mysum2八、自定义函数函数的分类：1）标量值函数2）表值函数a:内联表值函数b:多语句表值函数3）系统函数--新建标量值函数 
create function FUNC_Sum1 
( @a int, @b int
) 
returns int
as
beginreturn @a+@b 
end--新建内联表值函数 
create function FUNC_UserTab_1 
( @myId int
) 
returns table
as
return (select * from ST_User where ID<@myId) --新建多语句表值函数 
create function FUNC_UserTab_2 
( @myId int
) 
returns @t table
( [ID] [int] NOT NULL, [Oid] [int] NOT NULL, [Login] [nvarchar](50) NOT NULL, [Rtx] [nvarchar](4) NOT NULL, [Name] [nvarchar](5) NOT NULL, [Password] [nvarchar](max) NULL, [State] [nvarchar](8) NOT NULL
) 
as
begininsert into @t select * from ST_User where ID<@myId return
end--调用表值函数 
select * from dbo.FUNC_UserTab_1(15) 
--调用标量值函数 
declare @s int
set @s=dbo.FUNC_Sum1(100,50) 
print @s --删除标量值函数 
drop function FUNC_Sum1
谈谈自定义函数与存储过程的区别：一、自定义函数：1. 可以返回表变量2. 限制颇多，包括不能使用output参数；不能用临时表；函数内部的操作不能影响到外部环境；不能通过select返回结果集；不能update，delete，数据库表；3. 必须return 一个标量值或表变量自定义函数一般用在复用度高，功能简单单一，争对性强的地方。二、存储过程1. 不能返回表变量2. 限制少，可以执行对数据库表的操作，可以返回数据集3. 可以return一个标量值，也可以省略return存储过程一般用在实现复杂的功能，数据操纵方面。

二、特殊SQL语句

1、PostgreSQL

1.时间的操作

--1.当前时间获取SELECT now(); 
--查询结果：2017-11-18 15:52:37.558+08
--current_timestamp 同 now()实现一样;--2.获取当前日期(年月日)SELECT CURRENT_DATE
--查询结果：2017-11-18--3.获取当前时间SELECT current_time
--查询结果：15:59:09.037+08

2.时间的计算

select now() + interval '10 min';   select now() + '10 min';     select now() + '10 m';       

1.时间差age(timestamp, timestamp)select age(now(), timestamp '2017-11-15')
--查询结果：3 days 16:27:27.934
select age(timestamp '2007-09-15')
--查询结果：3 days2.时间的截取EXTRACT(时间对象 FROM 取得日期)select extract(year from now());
--查询结果:2017
--时间对象的类型有:year,month,day

3.数据类型的转换

1、将数值转成字符串类型

 方法1：调用to_char(int, text)函数，int为要转换值，text为数值格式化模式，其中模式描述为：

使用举例：

SELECT to_char(12345, '9999999999999999999')
//结果'12345'，结果字符串前面有空格，位数跟格式化模式中9的位数有关；SELECT to_char(12345, '99999')
//结果'12345'SELECT to_char(12345, '9999')
//结果'####'，当模式串小于数字个数时，字符串会显示为#，位数跟格式化模式中9的位数有关；SELECT to_char(12345, '')
//结果''

问题：将数值转成字符串且不要前面的空格实现起来很麻烦，由于无法判断格式化模式中9的位数。可用下面方法2解决：

方法2：通过||连接

''||12345

2、将字符串转成数值

方法调用： to_number（text,text）函数，参数1是要转的数字字符串，参数2为模式参数

使用举例：

SELECT to_number('12345', '9999999999999999999')
//12345SELECT to_number('12345', '99999')
//12345SELECT to_number(''||12345, '9999')
//1234，由于模式是4位，结果忽略最后一位；SELECT to_number('    12345', '9999999999999999999')
//12345SELECT to_number('  ab  ,1,2a3,4b5', '9999999999999999999')
//12345，会忽略所有字符串中非数字字符

4.时间戳转换普通时间

select to_char(now(),'yyyy-MM-dd HH24-MI-SS') as time
--显示结果:2017-11-20 15-02-31select to_char(now(),'yyyyMMddHH24MISS') as time
--显示结果:20171120150231

5.随机取出一条数据

SELECT user_id FROM users ORDER BY RANDOM() LIMIT 1;

6.字符串的截取

--从指定索引截取指定长度的字符串
SELECT substring('abcdefg',2,5) 

7.分页查询相关

--PostgreSQL数据库实现：查询语句
select * from user limit 10 offset 0

详见postgresql官方文档：/

三、MySQL常用语句

以下来源于网络亲测ok, 估计部分存在问题, 欢迎指正 : )

MYSQL常用命令一、启动与退出 1、连接MYSQL　  
格式：mysql -h主机地址-u用户名－p用户密码　　  
例：连接到本机上的MYSQL  
首先在打开DOS窗口，然后进入目录mysqlbin，再键入命令mysql -uroot -p，
回车后提示你输密码，如果刚安装好MYSQL，超级用户root是没有密码的，
故直接回车即可进入到MYSQL中了，MYSQL的提示符是：mysql> 　　  
例：连接到远程主机上的MYSQL  
假设远程主机的IP为：.110.110.110，用户名为root,密码为abcd123。则键入以下命令：　　　  
mysql -h110.110.110.110 -u root -p abcd123 　　  2、修改密码　　  
格式：mysqladmin -u用户名-p旧密码password 新密码　  
例：给root加个密码ab12。首先在DOS下进入目录mysqlbin，然后键入以下命令　　  
mysqladmin -uroot -password ab12 　　  
注：因为开始时root没有密码，所以-p旧密码一项就可以省略了。　　  
例：再将root的密码改为djg345  
mysqladmin -uroot -pab12 password djg345  3 新建一个用户帐号以便可以访问数据库，需要进行如下操作：  
mysql> CREATE USER 'username'@'host' IDENTIFIED BY 'password'; 
Query OK, 0 rows affected (0.15 sec)  
此后就创建了一个新用户叫：testuser，这个用户只能从localhost连接到数据库并可以连接到test 数据库。下一步，我们必须指定testuser这个用户可以执行哪些操作：  
4 授权　　
mysql> GRANT select, insert, delete,update  
-> ON test.*  
-> TO testuser@localhost;  
Query OK, 0 rows affected (0.00 sec)  
此操作使testuser能够在每一个test数据库中的表执行SELECT，INSERT和DELETE以及UPDATE查询操作。现在我们结束操作并退出MySQL客户程序：  5 .创建用户同时授权mysql> grant all privileges on mq.* to test@localhost identified by '1234';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
PS:必须执行flush privileges; 
否则登录时提示：ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES ) 6、进入MySQL：启动 MySQL Command Line Client，直接输入安装时的密码即可。此时的提示符是：mysql>或者进入bin目录下执行以下命令 mysql -uroot -p > ******(然后输入密码)7、退出MySQL：quit或exit 二、备份与恢复1.导出整个数据库  mysqldump -u 用户名 -p -character-set-server=latin1 数据库名 > 导出的文件名(数据库默认编码是latin1)  –default-character-set=latin1mysqldump -u root -p database_name > file_name.sql  2.导出一个表  mysqldump -u 用户名 -p 数据库名 表名 > 导出的文件名  mysqldump -u root -p database_name table_name > file_name.sql  3.导出一个数据库结构 mysqldump -u root -p -d database_name > d:file_name.sql  -d 没有数据 –add-drop-table 在每个 create 语句之前增加一个 drop table  4.导入数据库A:常用source 命令 进入mysql数据库控制台，  mysql -u root -pmysql>use 数据库名 然后使用source命令，后面参数为脚本文件(script_file.sql) mysql>source script_file.sql  B:使用mysqldump命令mysqldump -u root -p database_name < file_name.sql C:使用mysql命令 mysql -u root -p -D database_name < file_name.sql  D:将文本数据转到数据库中  文本数据应符合的格式：字段数据之间用tab键隔开，null值用\n来代替.  
mysql> LOAD DATA LOCAL INFILE "D:/mysql.txt"INTO TABLE table_name;  三、库的创建与删除命令：create database <数据库名>  
例如：建立一个名为test的数据库  
mysql> create database test;  一个建库和建表以及插入数据的实例  drop database if exists school; //如果存在SCHOOL则删除  create database school; //建立库SCHOOL  use school; //打开库SCHOOL  create table teacher //建立表TEACHER  (  id int(3) auto_increment not null primary key,  name char(10) not null,  address varchar(50) default ‘深圳’,  year date  ); //建表结束  //以下为插入字段  insert into teacher values('','glchengang','深圳一中','-10-10');  insert into teacher values('','jack','深圳一中','-12-23');  注：在建表中（）将ID设为长度为的数字字段:int(3)并让它每个记录自动加一:auto_increment并不能为空:not null而且让他成为主字段primary key  （）将NAME设为长度为的字符字段  （）将ADDRESS设为长度的字符字段，而且缺省值为深圳。varchar和char有什么区别呢，只有等以后的文章再说了。  （）将YEAR设为日期字段。  如果你在mysql提示符键入上面的命令也可以，但不方便调试。你可以将以上命令原样写入一个文本文件中假设为school.sql，然后复制到c:\下，并在DOS状态进入目录\mysql\bin，然后键入以下命令：  mysql -uroot -p 密码< c:\school.sql  如果成功，空出一行无任何显示；如有错误，会有提示。（以上命令已经调试，你只要将//的注释去掉即可使用）。四、表的相关操作命令：create table <表名> ( <字段名> <类型> [,..<字段名n> <类型n>]);  
mysql> create table MyClass(  
> id int(4) not null primary key auto_increment,  
> name char(20) not null,  
> sex int(4) not null default ’′,  
> degree double(16,2));  constraint FK_sid foreign key(sid) references student(id)  #通过外键创建链接创建外键的方法有很多，其中最常见创建外键的格式是：constraint FK_*** foreign key(**) references 链接的外表删除外键:alter table drop foreign key '外键名'.--常用字段类型
--1．INT[(M)] 型：正常大小整数类型  
--2．DOUBLE[(M,D)] [ZEROFILL] 型：正常大小(双精密)浮点数字类型  
--3．DATE 日期类型：支持的范围是-01-01到-12-31。MySQL以YYYY-MM-DD格式来显示DATE值，但是允许你使用字符串或数字把值赋给DATE列  
--4．CHAR(M) 型：定长字符串类型，当存储时，总是是用空格填满右边到指定的长度  
--5．BLOB TEXT类型，最大长度为(2^16-1)个字符。  
--6．VARCHAR型：变长字符串类型 2、获取表结构  命令：desc 表名，或者show columns from 表名  
mysql>DESCRIBE MyClass  
mysql> desc MyClass;  
mysql> show columns from MyClass;  3、删除表  
命令：drop table <表名>  
例如：删除表名为 MyClass 的表  
mysql> drop table MyClass;  4、插入数据  命令：insert into <表名> [( <字段名>[,..<字段名n > ])] values ( 值 )[, ( 值n )]  例如，往表 MyClass中插入二条记录, 这二条记录表示：编号为的名为Tom的成绩为.45, 编号为 的名为Joan 的成绩为.99，编号为 的名为Wang 的成绩为.5.  
mysql> insert into MyClass values(1,’Tom’,96.45),(2,’Joan’,82.99), (3,’Wang’, 96.59);  5、查询表中的数据  1)、查询所有行  
命令：select <字段，字段，...> from < 表名 > where < 表达式 >  
例如：查看表 MyClass 中所有数据  
mysql> select * from MyClass;  2）、查询前几行数据  
例如：查看表 MyClass 中前行数据  
mysql> select * from MyClass order by id limit 0,2;  
或者：  
mysql> select * from MyClass limit 0,2; 3)追加查询  
INSERT子句可以将一个或一组记录追加到一个或多个表的尾部.  
INTO 子句指定接受新记录的表  
valueS 关键字指定新记录所包含的数据值.  
INSERT 子句的语法:  
INSETR INTO 目的表或查询(字段,字段,…)  
valueS(数值,数值,…)  
例:增加一个客户  
INSERT INTO Employees(FirstName,LastName,title)  
valueS(‘Harry’,’Washington’,’Trainee’)  4)生成表查询 
可以一次性地把所有满足条件的记录拷贝到一张新表中,通常制作记录的备份或副本或作为报表的基础.
SELECT INTO子句用来创建生成表查询语法:  
SELECT 字段,字段,…  
INTO 新表[IN 外部数据库]  
FROM 来源数据库  
WHERE 准则  
例:为定单制作一个存档备份  
SELECT *  
INTO OrdersArchive  
FROM Orders 5)联合查询  
UNION运算可以把多个查询的结果合并到一个结果集里显示.  
UNION运算的一般语法:  
[表]查询UNION [ALL]查询UNION …  
例:返回巴西所有供给商和客户的名字和城市  
SELECT CompanyName,City  
FROM Suppliers  
WHERE Country = ‘Brazil’  
UNION  
SELECT CompanyName,City  
FROM Customers  
WHERE Country = ‘Brazil’  
注:  
缺省的情况下,UNION子句不返回重复的记录.如果想显示所有记录,可以加ALL选项  
UNION运算要求查询具有相同数目的字段.但是,字段数据类型不必相同.  
每一个查询参数中可以使用GROUP BY 子句或HAVING 子句进行分组.要想以指定的顺序来显示返回的数据,可以在最后一个查询的尾部使用OREER BY子句.  6、删除表中数据  
命令：delete from 表名 where 表达式  
例如：删除表 MyClass中编号为 的记录  
mysql> delete from MyClass where id=1;7、修改表中数据：update 表名 set 字段=新值,…where 条件  
mysql> update MyClass set name=’Mary’where id=1;  
8、在表中增加字段：  
命令：alter table 表名 add字段 类型 其他;  
例如：在表MyClass中添加了一个字段passtest，类型为int(4)，默认值为  
mysql> alter table MyClass add passtest int(4) default ’′  
9、更改表名：  
命令：rename table 原表名 to 新表名;  
例如：在表MyClass名字更改为YouClass  
mysql> rename table MyClass to YouClass;  
更新字段内容  
update 表名 set 字段名 = 新内容  
update 表名 set 字段名 = replace(字段名,'旧内容','新内容') 十、其他1、显示所有的数据库  
命令：show databases （注意：最后有个s）  
mysql> show databases;  
2、删除数据库  
命令：drop database <数据库名>  
例如：删除名为 xhkdb的数据库  
mysql> drop database xhkdb;  
3、连接数据库  
命令：use <数据库名>  
例如：如果xhkdb数据库存在，尝试存取它：  
mysql> use xhkdb;  
屏幕提示：Database changed  
4、查看当前使用的数据库  
mysql> select database();  
5、当前数据库包含的表信息：  
mysql> show tables; （注意：最后有个s）  
三、表操作，操作之前应连接某个数据库  
6、显示数据表的结构：  describe 表名;在windows中MySql以服务形式存在，在使用前应确保此服务已经启动，未启动可用net start mysql命令启动。
而Linux中启动时可用“/etc/rc.d/init.d/mysqld start”命令，注意启动者应具有管理员权限。  
刚安装好的MySql包含一个含空密码的root帐户和一个匿名帐户，这是很大的安全隐患，
对于一些重要的应用我们应将安全性尽可能提高，在这里应把匿名帐户删除、root帐户设置密码，可用如下命令进行：  use mysql;  
delete from User where User="";  
update User set Password=PASSWORD(‘newpassword’) where User=’root’;  如果要对用户所用的登录终端进行限制，可以更新User表中相应用户的Host字段，
在进行了以上更改后应重新启动数据库服务，此时登录时可用如下类似命令:mysql -uroot -p;  
mysql -uroot -pnewpassword;  
mysql database_name -uroot -p;  
mysql database_name -uroot -pnewpassword;在进行开发和实际应用中，用户不应该只用root用户进行连接数据库，
虽然使用root用户进行测试时很方便，但会给系统带来重大安全隐患，
也不利于管理技术的提高。我们给一个应用中使用的用户赋予最恰当的数据库权限。
如一个只进行数据插入的用户不应赋予其删除数据的权限。MySql的用户管理是通过User表来实现的，
添加新用户常用的方法有两个，一是在User表插入相应的数据行，同时设置相应的权限；
二是通过GRANT命令创建具有某种权限的用户。其中GRANT的常用用法如下： show grants for 用户名;
grant 权限列表 on 库名.表名 to 用户名@来源地址 [ identified by '密码']
grant all on mydb.* to NewUserName@HostName identified by “password”;  
grant usage on *.* to NewUserName@HostName identified by “password”;  
grant select,insert,update on mydb.* to NewUserName@HostName identified by “password”;  
grant update,delete on mydb.TestTable to NewUserName@HostName identified by “password”; 若要给此用户赋予他在相应对象上的权限的管理能力，可在GRANT后面添加WITH GRANT OPTION选项。
而对于用插入User表添加的用户，Password字段应用PASSWORD 函数进行更新加密，以防不轨之人窃看密码。
对于那些已经不用的用户应给予清除，权限过界的用户应及时回收权限，
回收权限可以通过更新User表相应字段，也可以使用REVOKE操作。下面给出本人从其它资料(www-java)获得的对常用权限的解释：  全局管理权限：  
FILE: 在MySQL服务器上读写文件。  
PROCESS: 显示或杀死属于其它用户的服务线程。  
RELOAD: 重载访问控制表，刷新日志等。  
SHUTDOWN: 关闭MySQL服务。  
数据库/数据表/数据列权限：  
Alter: 修改已存在的数据表(例如增加/删除列)和索引。  
Create: 建立新的数据库或数据表。  
Delete: 删除表的记录。  
Drop: 删除数据表或数据库。  
INDEX: 建立或删除索引。  
Insert: 增加表的记录。  
Select: 显示/搜索表的记录。  
Update: 修改表中已存在的记录。  
特别的权限：  
ALL: 允许做任何事(和root一样)。  
USAGE: 只允许登录–其它什么也不允许做。 

转载于: