澳门金沙vip 2

SQL学习笔记五去重,给新加字段赋值的点子

去掉数据重复 增加两个字段 alter TABLE T_Employee Add FSubCompany
VARchar(20); ALTER TABLE T_Employee ADD FDepartment VARCHAR(20);
给新加的字段赋值 UPDATE T_Employee SET
FSubCompany=’Beijing’,FDepartment=’Development’ where FNumber=’DEV001′;
UPDATE T_Employee SET FSubCompany=’ShenZhen’,FDepartment=’Development’
where FNumber=’DEV002′; UPDATE T_Employee SET
FSubCompany=’Beijing’,FDepartment=’HumanResource’ where FNumber=’HR001′;
UPDATE T_Employee SET FSubCompany=’Beijing’,FDepartment=’HumanResource’
where FNumber=’HR002′; UPDATE T_Employee SET
FSubCompany=’Beijing’,FDepartment=’InfoTech’ where FNumber=’IT001′;
UPDATE T_Employee SET FSubCompany=’ShenZhen’,FDepartment=’InfoTech’
where FNumber=’IT002′; UPDATE T_Employee SET
FSubCompany=’Beijing’,FDepartment=’Sales’ where FNumber=’SALES001′;
UPDATE T_Employee SET FSubCompany=’Beijing’,FDepartment=’Sales’ where
FNumber=’SALES002′; UPDATE T_Employee SET
FSubCompany=’ShenZhen’,FDepartment=’Sales’ where FNumber=’SALES003′;
查询并去重 select distinct FDepartment from T_Employee select distinct
FDepartment,FSubCompany from T_Employee

去除重复数据

澳门金沙vip 1

去除重复数据

比较下列代码:
select FDepartment from T_Employee
select DISTINCT FDepartment from T_Employee
DISTINCT
是对整个结果集进行数据重复处理的,而不是针对每一个列,因此下面的语句并不会保留FDepartment进行重复值处理:
select FDepartment,FSubCompany from T_Employee

相关代码:

澳门金沙vip 2

本节代码

alter table T_Employee ADD FSubCompany varchar(20);
alter table T_Employee ADD FDepartment varchar(20);
update T_Employee set FSubCompany=’Beijing’,FDepartment=’Development’
where FNumber=’DEV001′;
update T_Employee set
FSubCompany=’ShenZhen’,FDepartment=’Development’
where FNumber=’DEV002′;
update T_Employee set
FSubCompany=’Beijing’,FDepartment=’HumanResource’
where FNumber=’HR001′;
update T_Employee set
FSubCompany=’Beijing’,FDepartment=’HumanResource’
where FNumber=’HR002′;
update T_Employee set FSubCompany=’Beijing’,FDepartment=’InfoTech’
where FNumber=’IT001′;
update T_Employee set FSubCompany=’ShenZhen’,FDepartment=’InfoTech’
where FNumber=’IT002′;
update T_Employee set FSubCompany=’Beijing’,FDepartment=’Sales’
where FNumber=’SALE001′;
update T_Employee set FSubCompany=’Beijing’,FDepartment=’Sales’
where FNumber=’SALE002′;
update T_Employee set FSubCompany=’ShenZhen’,FDepartment=’Sales’
where FNumber=’SALE003′;

一、**SQL**
基础知识

1、DDL(数据定义语言)

1)创建数据表

–创建数据表

create
table
Test(Id int not null, Age char(20))

 

–创建数据表

create table T_Person1(Id int not null,

Name
nvarchar(50),

Age int null)

 

 

 

–创建表,添加外键

Create table T_Students(

StudentNo
char(4),

CourseNo
char(4),

Score
int,

Primary key(StudentNo),

Foreign key(CourseNo) References T_Course(CourseNo)

);

 

2)修改表结构

–修改表结构,添加字段

Alter table T_Person add NickName nvarchar(50) null;

 

–修改表结构,删除字段

Alter table T_Person Drop NickName;

 

3)删除数据表

–删除数据表

Drop table T_Person;

 

–删除数据表

drop table test

4)创建索引

Create
[Unique]
Index <索引名>
on <基本表名>(<列明序列>);

 

2、DML(数据操纵语言)

1)插入语句

insert into T_Person1(Id,Name,Age) values(1,‘Vicky’,20)

–插入一条据数,字段和值必须前后对应

insert into T_Preson1(Id,Name,Age) values(2,‘Tom’,19)

insert into T_Person1(Id,Name,Age) values(4,‘Jim’,19)

insert into T_Person1(Id,Name,Age) values(5,‘Green’,20)

insert into T_Person1(Id,Name,Age) values(6,‘Hanmeimei’,21)

insert into T_Person1(Id,Name,Age) values(7,‘Lilei’,22)

insert into T_Person1(Id,Name,Age) values(8,‘Sky’,23)

 

insert into T_Person1(Id,Name,Age) values(newid(),‘Tom’,19)

 

2)更新语句

–修改列,把所有的age字段改为30

update T_Person1 set age=30

 

–把所有的Age字段和Name字段设置为…

update T_Person1 set Age=50,Name=‘Lucy’

 

 

update T_Person1 set Name=‘Frankie’ where Age=30

 

update
T_Person1 set Name=N’中文字符’ where Age=20

–中文字符前面最好加上N,以防出现乱码

 

update
T_Person1 set Name=N’成年人’ where Age=30 or Age=50

 

3)删除语句

delete from T_Person1

–删除表中全部数据

 

delete from T_Person1 where Name=‘Tom’

–根据条件删除数据

 

4)查询语句

查询语句非常强大,几乎可以查任意东西!


—- 数据检索 —–


–查询不与任何表关联的数据.

SELECT
1+1; –简单运算
select

1+2 as
结果

 

SELECT
newid();–查询一个GUID字符创

 

select
GETDATE() as
日期 –查询日期

 

–可以查询SQLServer版本

select
@@VERSION as
SQLServer版本

 

–一次查询多个

select
1+1
结果,
GETDATE() as
日期,
@@VERSION as
版本,
NEWID() as
编号

 

 

–简单的数据查询.HelloWorld级别

SELECT * FROM T_Employee;

 

–只查询需要的列.

SELECT FNumber FROM T_Employee;

 

–给列取别名.As关键字

SELECT
FNumber AS
编号,
FName AS
姓名 FROM
T_Employee;

 

–使用 WHERE 查询符合条件的记录.

SELECT FName FROM T_Employee WHERE FSalary<5000;

 

–对表记录进行排序,默认排序规则是ASC

SELECT * FROM T_Employee ORDER BY FAge ASC,FSalary DESC;

 

–ORDER BY 子句要放在 WHERE 子句之后.

SELECT * FROM T_Employee WHERE FAge>23 ORDER BY FAge DESC,FSalary DESC;

 

–WHERE 中可以使用的逻辑运算符:or、and、not、<、>、=、>=、<=、!=、<>等.

 

–模糊匹配,首字母未知.

SELECT * FROM T_Employee WHERE FName LIKE ‘_arry’;

 

–模糊匹配,前后多个字符未知.

SELECT * FROM T_Employee WHERE FName LIKE ‘%n%’;

 

–NULL 表示”不知道”,有 NULL 参与的运算结果一般都为 NULL.

 

–查询数据是否为 NULL,不能用 = 、!= 或 <>,要用IS关键字

SELECT * FROM T_Employee WHERE FName IS NULL;

SELECT * FROM T_Employee WHERE FName IS NOT NULL;

 

–查询在某个范围内的数据,IN 表示包含于,IN后面是一个集合

SELECT * FROM T_Employee WHERE FAge IN (23, 25, 28);

 

–下面两条查询语句等价。

SELECT * FROM T_Employee WHERE FAge>=23 AND FAge<=30;

SELECT * FROM T_Employee WHERE FAge BETWEEN 23 AND 30;

 

—-创建一张Employee表,以下几个Demo中会用的这张表中的数据

—-在SQL管理器中执行下面的SQL语句,在T_Employee表中进行练习

create table T_Employee(FNumber varchar(20),

FName
varchar(20),

FAge
int,

FSalary
Numeric(10,2),

primary key (FNumber)

)

 

insert into T_Employee(FNumber,FName,FAge,FSalary) values(‘DEV001’,‘Tom’,25,8300)

insert into T_Employee(FNumber,FName,FAge,FSalary) values(‘DEV002’,‘Jerry’,28,2300.83)

insert into T_Employee(FNumber,FName,FAge,FSalary) values(‘SALES001’,‘Lucy’,25,5000)

insert into T_Employee(FNumber,FName,FAge,FSalary) values(‘SALES002’,‘Lily’,25,6200)

insert into T_Employee(FNumber,FName,FAge,FSalary) values(‘SALES003’,‘Vicky’,25,1200)

insert into T_Employee(FNumber,FName,FAge,FSalary) values(‘HR001’,‘James’,23,2200.88)

insert into T_Employee(FNumber,FName,FAge,FSalary) values(‘HR002’,‘Tom’,25,5100.36)

insert into T_Employee(FNumber,FName,FAge,FSalary) values(‘IT001’,‘Tom’,28,3900)

insert into T_Employee(FNumber,FAge,FSalary) values(‘IT002’,25,3800)

 

–开始对T_Employee表进行各种操作

–检索所有字段

select * from T_Employee

 

–只检索特定字段

select FName,FAge from T_Employee

 

–带过滤条件的检索

select * from T_Employee

where FSalary<5000

 

–可更改显示列名的关键字as,as—起别名

select
FName as
姓名,FAge
as
年龄,FSalary as 薪水 from T_Employee

 

 

二、**SQL
**Server 中的数据类型

1、精确数字类型

bigint

int

smallint

tinyint

bit

money

smallmoney

2、字符型数据类型,MS建议用VarChar(max)代替Text

Char

VarChar

Text

3、近似数字类型

Decimal

Numeric

Real

Float

4、Unicode字符串类型

Nchar

NvarChar

Ntext

5、二进制数据类型,MS建议VarBinary(Max)代替Image数据类型,max=231-1

Binary(n) 存储固定长度的二进制数据

VarBinary(n)
存储可变长度的二进制数据,范围在n~(1,8000)

Image 存储图像信息

6、日期和时间类型,数据范围不同,精确地不同

DateTime

SmallDateTime

7、特殊用途数据类型

Cursor

Sql-variant

Table

TimeStamp

UniqueIdentifier

XML

 

三、SQL中的内置函数

 


—– 数据汇总-聚合函数 ———


–查询T_Employee表中数据条数

select COUNT(*) from T_Employee

 

–查询工资最高的人

select MAX(FSalary) as Top1 from T_Employee

 

–查询工资最低的人

select Min(FSalary) as Bottom1 from T_Employee

 

–查询工资的平均水平

select
Avg(FSalary) as
平均水平 from T_Employee

 

–所有工资的和

select
SUM(FSalary) as
总工资 from T_Employee

 

–查询工资大于5K的员工总数

select COUNT(*) as total from T_Employee

where FSalary>5000

 

 


—– 数据排序 ——-


–按年龄排序升序,默认是升序

select * from T_Employee

order by FAge ASC

 

–多个条件排序,先什么,后什么,在前一个条件相同的情况下,根据后一个条件进行排列

–where在order by之前

select * from T_Employee

order by FAge ASC, FSalary
DESC

 

 


—– 模糊匹配 ——-


–通配符查询

–1.单字符通配符_

–2.多字符通配符%

–以DEV开头的任意个字符串

select * from T_Employee

where FNumber like ‘DEV%’

 

–以一个字符开头,om结尾的字符串

select * from T_Employee

where FName like ‘_om’

 

–检索姓名中包含m的字符

select * from T_Employee

where FName like ‘%m%’

 

 


—– 空值处理 ——-


–null表示不知道,不是没有值

–null和其他值计算结果是null

select null+1

 

–查询名字是null的数据

select * from T_Employee

where FName is null

 

–查询名字不为空null的数据

select * from T_Employee

where FName is not null

 

–年龄是23,25,28中的员工

select * from T_Employee

where FAge=23 or FAge=25 or FAge=28

 

 

–或者用in 集合查询

–年龄是23,25,28中的员工

select * from T_Employee

where FAge in (23,25,28)

 

–年龄在20到25之间的员工信息

select * from T_Employee

where FAge>20 and FAge<25

 

–年龄在20到25之间的员工信息,包含25

select * from T_Employee

where FAge between 20 and 25

 

 


—– 数据分组 ——-


Select FAge,COUNT(*)
from
T_Employee

group by FAge

–1.根据年龄进行分组

–2.再取出分组后的年龄的个数

 

–注意:没有出现在group by 子句中的字段,不能出现在select语句后的列名列表中(聚合函数除外)

–group by 必须出现在where后面

Select FAge,AVG(FSalary),COUNT(*)
from
T_Employee

group by FAge

 

–错误用法

Select FAge,FName,COUNT(*)
from
T_Employee

group by FAge

 

–加上where的group by 子句

–group by 必须出现在where后面

Select FAge,AVG(FSalary),COUNT(*)
from
T_Employee

where FAge>=25

group by FAge

 

 

–Having不能包含查不到的字段,只能包含聚合函数和本次查询有关的字段

select FAge,COUNT(*)
from澳门金沙vip,
T_Employee

group by FAge

Having COUNT(*)>1

 

select FAge,COUNT(*)
from
T_Employee

where FSalary>2500

group by FAge

 

–HAVING 子句中的列 ‘T_Employee.FSalary’ 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中

–Having是对分组后信息的过滤,能用的列和select中能有的列是一样的。

–因此,having不能代替where

select FAge,COUNT(*)
from
T_Employee

group by FAge

Having FSalary>2500

 

 


—– 确定结果集行数 ——-


–取出所有员工的信息,根据工资降序排列

select * from T_Employee

order by FSalary DESC

 

–取出前三名员工的信息,根据工资降序排列

select top 3 * from T_Employee

order by FSalary DESC