SQL—简单查询

SQL—查询

--基本查询

select * from emp;

--all 查询某列所有

select all ename from emp;

-- distinct 过滤重复数据

select distinct esex from emp;

--count   统计

select count(*) from emp;

select count(esex) from emp;

select count(distinct esex) from emp;

--top取前N条记录

select top 3 * from emp;

--alias  column name 列重命名

select eid as 编号, ename '名称', esex 性别 from emp;

--alias table name : 表重命名

select eid,ename,e.eid,e.ename from emp s;

--column  : 列运算

select (pay+reward) col from erp;

select d.dname+'-'+e.ename from dept d,emp e where d.did=e.did;

--where条件

select * from emp where eage=20;

select * from emp where eage>20;

select * from emp where eage<20;

select * from emp where eage<>20;

select * from emp where eage>=20;

select * from emp where eage<=20;

select * from emp where eage!>20;

select * from emp where eage!<20;

--and 并且

select * from emp where eage>20 and esex='男';

--or 或者

select * from emp where eage>20 and esex='男';

--between...and ...相当于并且

select * from emp where eage between 20 and 22;

select * from emp where eage not between 20 and 22;

--like:  模糊查询

select * from emp where ename like '%a%';

select * from emp where ename like '%[m][i]%'; --中间有个mi的

select * from emp where ename not like '%a%';  --中间没有a字母的

select * from emp where ename like 'Jo%';   --以Jo开头的

select * from emp where ename not like 'Jo%' and name not like 'Be%';   --不是一Jo开头的

select * from emp where ename not  like '%[jn]%';  --中间不含j或n的

select * from emp where ename  like '%[jna]%';  --中间含有j或n或a的

select * from emp where ename  like '[^RJT]%';  --不是以RJT开头的

select * from emp where ename  like '[^J][o]%'; --第一个字母是不是J,第二个字母是o

select * from emp where ename  like '[JTB][^o]%';  --第一个字符是J或B或T,第二个字母不是o的

select * from emp where ename like '%[ja_on]%';

--in :   条件范围

--语法:   where 字duan in (值1,值2);  或

--问题: 求出emp表eid为1,3,5,7,9的员工信息

select * from emp where eid in(1,3,5,7,9);

--not in:  不在条件范围

--问题: 求出emp表eid不为1,3,5,7,9的员工信息

select * from emp where eage is null;

--为空判断:   判断某个列的值是否为空

--语法 :   字段名 is null

select * from student where age is null;

--不为空判断: 

--语法:  is not null

select * from emp where eage is not null;

--any、all、some

--求出市场部中年龄比开发部任何一个都大的员工信息

select * from emp where eid=3 and eage>all(

select eage from emp where eid=1); 

--求出其它部门中，有比开发部年龄小的员工信息

select * from emp where eid<>1 and eage<any(

select eage from emp where eid=1);

--排序:  按某个列进行升降序显示

--语法:  order by  列名1 [desc/asc], 列名2 [desc/asc],

--问题:

--  1. 按工资排序

select * from erp order by pay;    --默认为升序

-- 2. 按工资降序排序

select * from erp order by pay desc;

--3. 按工资升序排序

select * from erp order by pay asc;

--4. 先按工资降序排序，再按奖金升序排序

select * from erp order by pay desc, reward asc;

select * from emp,erp where emp.eid=erp.eid order by eage;

---综合案例一:

--需求描述:在数据库表中，数据行位置并不重要，但是一个单位中要根据奇数行和偶数行的数据来汇总，在这个汇总的基础上再得到一个数值，

--因此，要查询数据库表的奇数行和偶数行的总数, 但原表中的id列的值并不是完全连续的，其中有一些数据已经删除了.

create table tab1(

id int primary key,

total int

)

insert into tab1 values(1,33);

insert into tab1 values(3,44);

insert into tab1 values(4,2);

insert into tab1 values(5,6);

insert into tab1 values(8,88);

insert into tab1 values(9,3);

insert into tab1 values(15,33);

insert into tab1 values(17,34);

insert into tab1 values(19,34);

insert into tab1 values(20,29);

select * from tab1;

--解决方案: 只能依靠标识列的值来进行判断和选取, 但原有主键列已经不能用，所以必须生成一个新表，将原表的total列的数据插过去，并新增加标识列

--然后再在新表中通过新标识列来求奇数列值和偶数列值

--将一个表的数据插入到另一个表语法:  select 列名, identity(种子，自增)  into 新表名 from 源表名

select IDENTITY(int,1,1) as id, total

into newtab1

from tab1;

select SUM(total)

from newtab1

where ID%2=1

select SUM(total)

from newtab1

where ID%2=0

--聚合函数

select MAX(age), MIN(age),COUNT(age),AVG(age),SUM(age) from student;

--日期时间函数

select DATEADD(day,3,getDate());

select DATEADD(year,3,getDate());

select DATEADD(hour,3,getdate());

--返回跨两个指定日期的日期边界数和时间边界数

select DATEDIFF(day,'2012-06-1',getdate());

--相差秒数

select DATEDIFF(second,'2012-06-1 11:00:00',getdate());

--相差小时数

select DATEDIFF(hour,'2011-6-22 10:00:00',getdate());

select DATENAME(month,getdate()); -- 返回指定日期的指定日期部分的字符串

select DATENAME(minute, getdate());

select DATENAME(weekday,getdate());

select DATEPART(month,getdate());  --返回指定日期的指定日期部分的整数

select DATEPART(weekday,getdate());

select DATEPART(second,getdate());

select DAY(getdate());     --几号

select DAY('2012-06-06');

select MONTH(getdate());

select YEAR(getdate());

select GETDATE();

--数学函数

select PI();

select RAND(100),RAND(50),RAND(),RAND();  --100表示种子值，表所有的rand(100)都生成同样的值

select ROUND(rand(),3); --3表示精确到的位数

--元数据

select COL_NAME(object_id('student'),1); --student表中第一列列名

select COL_NAME(object_id('student'),2); --student表中第二列列名

--该列数据类型长度

select COL_LENGTH('student',col_name(OBJECT_ID('student'),2));

--返回类型名称，类型id

select TYPE_NAME(type_id('varchar')),TYPE_ID('varchar');

--字符串函数

select ascii('a');

select ASCII('A');

select CHAR(97);

select CHAR(65);

select NCHAR(65);  --Unicode字符数据类型。Unicode字符数据类型包括Nchar、Nvarchar、Ntext三种。

select NCHAR(45231);  --unicode转换字符

select UNICODE('A'),UNICODE('中');  --unicode编码

--匹配字符索引

--dex在dexjack中开始的索引下标 某一模式开始的索引下标

select patindex('dex%','dexjack'), PATINDEX('%a','ta'),PATINDEX('%ac%','jcaack');

--查找索引 下标从1开始

select charindex('o','hello world');

--返回指定字符串从左边开始的3个字符

select LEFT('hello world',3);

--返回指定字符串从右边开的3个字符

select right('hello world',3);

--返回指定的次数重复的字符串   将he空格重复3次输出

select replicate('he ',3);

--比较字符串是否相同

--在以下示例的第一部分，对两个非常相似的字符串的 SOUNDEX 值进行了比较，DIFFERENCE 返回的值为 4。

--在以下示例的第二部分，对两个差别很大的字符串的 SOUNDEX 值进行了比较，DIFFERENCE 返回的值为 0。

select difference('hello','hello world');

select DIFFERENCE('hello','h world');

--替换

select replace('abcedef','e','E');

--指定位置替换

select stuff('hello world',3,4,'ABC');  --从第三个开始的后面4个字符替换为ABC

--截取字符符串，截那个字符串，从哪里截，截多长

select substring('abc',1,1),substring('abc',1,2),SUBSTRING('hello world',7,5);

--返回字符串长度

select len('abc');

--反序

select reverse('sqlserver');

--取左边字符串

select left('leftstring',4), left('leftstring',7);

select right('leftstring',4), left('leftstring',7);

--大小写转换

select lower('aBc'),upper('aBc');

--去掉空格

select ltrim('    abc    '),ltrim('#    abc    #'),rtrim('    abc    '),rtrim('#    abc    #');

--安全函数

select CURRENT_USER;

select USER;

--系统函数

select APP_NAME();   --当前会话的应用程序名

--类型转换

--cast与convert最大的区别在于convert可以指定转换的样式，主要是对money、float类型和日期型的

select cast('2012' as datetime), cast('10' as money),cast('0' as char);  --将一种数据类型的表达式转换为另一种数据类型的表达式。

select convert(datetime,'2012');  --将2012转换成日期型输出

select convert(varchar(20),GETDATE(),120); --120表示按yyyy-mm-dd hh:mi:ss(24h)的格式输出2012-12-12  将当前日期转换为字符串按120格式输出

  --101  mm/dd/yyyy   102 yy.mm.dd   110 mm-dd-yyyy  126 yyyy-mm-dd hh:mi:ss.mmm(24h)

select convert(money,'2011',[style]);  --哪种格式的金钱表示方式

select convert(varchar(20),cast('2012' as money),120);--将2011转化成money类型，然后再将其转为字符串型以120(2,012.00)格式输出，默认输出为2012.00

--当前时间搓

select current_timestamp;

select CURRENT_TIMESTAMP;

--判断

select isdate(getdate()), isdate('abc'),isnumeric(1);

select datalength('abcd周'); -- 返回用于表示任何表达式的字节数。

select LEN('abdc周'); --返回字符表达式中的字符数。如果字符串中包含前导空格和尾随空格，则函数会将它们包含在计数内。LEN 对相同的单字节和双字节字符串返回相同的值