Catalaog 分类:又叫DataBase TableSpace,不同类的数据应该放在不同的数据库中。
表Table :书放在书架 碗放碗橱 不同类型的资料放在不同的格子里
列 Column ,字段 Field :同一个意思 某一列
主键Primary Key :数据行的唯一标识,不会重复的列才能当主键。一个表可以不设主键,但会难以处理。
主键选用策略:业务主键:使用有业务意义的字段做主键,如身份证、银行卡号。 逻辑主键:使用没有任何业务意义的字段做主键,完全给程序看的,业务人员不会看的数据。
业务字段可能升位,身份证号码有人重复,因此推荐用逻辑主键。
标间关联,外键ForeignKey: 同过一个唯一的字段来关联两个表。
上面的写法不好,当厂家地址发生变化时,要更改大量的数据。因此用下面的两个表通过厂家编号进行关联的写法比较好。缺点是查询某个商品厂址时需要两条语句。
常用数据类型:
bit 可选值 0 1
datetime 时间
int 32位 bigint 64位
varchar(50) 不含中文信息 50字节 varchar(MAX)时可以保存很长的文本
nvarchar() 可以包含中文 日文等信息
varchar(50) 和 char(50)的区别: nvarchar 不足的部分不写,char会用空格补齐
var = variable 可变
SQL语句入门
字符串用单引号
SQL语句大小写不敏感 但数据时大小写敏感的
执行SQL语句 表上 右键 新建查询 输入语句 执行
SQL主要分成两种
DDL(数据定义语句) : Create Table ,Drop Table,Alter Table
DML(数据操作语句): Select , Insert ,Update , Delete
创建表
可以右键 新建表
也可以在新建查询中 使用 SQL语句
create table Person(Id int NOT NULL,Name nvarchar(50),Age int NOT NULL);
删除表 drop table Person;
大部分数据类型都可以设为主键,但是实际上一般只使用 int(或bigint)+标识列(又称自动增长序列),uniqueidentifier
GUID算法是一种可以产生唯一标识的高效算法,每次产生的GUID都不会重复。(数据类型 uniqueidentifier )
SQLSERVER中 newid()
select newid()
.NET中
Guid id = Guid.NewGuid();
int 自增字段的优点:占用空间小,无需开发人员干预,易读;缺点 效率低 数据导入导出痛苦
GUID优点:效率高,数据导入导出方便。缺点:占用空间大,不易读。
业界主流倾向于Guid
插入数据
主键 自增长int型 (ID字段不用写)
insert into Person3(Name,Age) Values(‘lily’,30);
insert into Person3 Values (‘lily’,30); //可以省略字段名 但不要使用
主键 GUID(uniquedentifier)型 (ID字段要写)
insert into Person4(Id,Name,Age) Values(newid(),’Lily’,30);
GUID做主键时 显示顺序和插入顺序不一致。可以给主键默认值newid() 但很少这么做
更新 update
update Person1 set Age=30; //所有的年龄都设置为30
update Person1 set Age=50,Name=’lucy’; //所有的年龄都设置为50 所有的名字都设置为lucy
update Person1 set Age=Age+1;
带条件的
update Person1 set NickName=N’青年人’ where Age>=30; //如果有中文字符串,前面加N
update Person1 set NickNAME=N’二十岁’ where Age=20; //等于判断是= 不等!= 或者 <>
带复合条件的 or and
update Person1 set NickName=N’二三十岁’ where Age=20 or Age=30;
删除数据
delete from person_3 清空整个表
elete from person_3 where name='lily'
检索数据
create table T_Employ(FNumber VARCHAR(20),FName varchar(20),FAge INT,
FSalary NUMERIC(10,2),primary key(FNumber))
insert into T_Employ(FNumber,FName,FAge,FSalary) values('DEV001','tom',25,8300)
,('DEV002','jerry',28,2300.80),('SALE001','jonh',23,5000),
('SALE002','kerry',28,6200),('SALE003','stone',22,1200),('HR001','jane',23,2200.88)
,('HR002','tina',25,5200.36),('IT001','smith',28,3900);
insert into T_Employ(FNumber,FAge,FSalary) values('IT002',27,2800)
select * from T_Employ //显示所有字段
select FName,FAge from T_Employ //查询FName FAge两个字段
select * from T_Employ where FSalary<5000 // 有过滤条件的显示所有字段
select FName,FAge from T_Employ where FSalary<5000; // 有过滤条件地显示两个字段
select FName as 姓名,FAge as 年龄,FSalary as 月薪 from T_Employ; 设置别名
select @@VERSION as 版本,1+1 as 列,NEWID() as 编号,GETDATE() as 日期; select 语句可以和表无关。 还可以设置别名
select FName as 姓名,FAge as 年龄,FSalary+10000 as 月薪 from T_Employ; 显示时能够计算,如月薪加10000
聚合函数
select COUNT(*) from T_Employ;显示有多少条记录
select MAX(FSalary) as 最高工资 from T_Employ;
select min(FSalary) as 最低工资 from T_Employ;
select avg(FSalary) as 平均工资 from T_Employ;
select SUM(FSalary) as 工资和 from T_Employ;
select COUNT(*) from T_Employ where FSalary >=5000;
排序 默认是升序 但是最好写上
select * from T_Employ order by FAge ASC; //按年龄升序排序
select * from T_Employ order by FSalary DESC; //按工资降序排序
//先按照年龄进行升序排序,如果年龄相同,按工资进行降序排序 越靠前越优先
select * from T_Employ order by FAge ASC,FSalary DESC;
//order by 应该放在where后面
select *from T_Employ where FAge<25 order by FAge ASC,FSalary ASC;
通配符过滤
单字符通配符 _ 匹配单个出现的字符
SELECT * from T_Employ where FName like '_erry';所有类似_erry的
多字符通配符 % 任意次数 0 或多个
SELECT * FROM T_Employ where FName like '%n%'; //所有带n的
select * from T_Employ where FNumber like 'DEV%';
空值处理
NULL 代表不知道 不是表示没有 几乎所有NULL参与的运算结构都是NULL
select null+1; //返回NULL
SELECT 'ABC'+'123';//ABC123
SELECT ''+'123'; //123
SELECT NULL+'123';/NULL
下面两条都不会输出语句 错误的!
SELECT * FROM T_Employ WHERE FName=NULL;
SELECT * FROM T_Employ WHERE FName<>NULL;
显示FName为null 和 不为null 的 语句
SELECT * FROM T_Employ WHERE FName IS NULL;
SELECT * FROM T_Employ WHERE FName IS NOT NULL;
多值匹配
显示年龄为或者的
SELECT * FROM T_Employ WHERE FAge=25 or FAge=28
SELECT * FROM T_Employ WHERE FAge IN (25,28);
显示年龄介于到的
SELECT * FROM T_Employ WHERE FAge>20 and FAge<30;
SELECT * FROM T_Employ WHERE FAge between 20 and 30;
分组 GROUP BY
要放在WHERE后面
SELECT COUNT(*) from T_Employ GROUP BY FAge; 数据条数按年龄分组
没有出现在GROUP BY中的字段是不能放在SELECT后的,聚合函数除外.
:
SELECT FAge,COUNT(*)as 人数 from T_Employ GROUP BY FAge; 按FAge分组 可以显示FAge 但不能SELECT FName
SELECT FAge,MAX(FSALARY) as 最高工资 FROM T_Employ GROUP BY FAge;
在where中根据聚合函数进行过滤
聚合函数是不能出现在where子句中的,必须使用having
having是对分组后信息的过滤 能用的列和select中能用的是一样的.
SELECT FAge,COUNT(*) from T_Employ group by FAge having COUNT(*)>1;
WHERE 是对原始数据进行过滤的
SELECT FAge,COUNT(*) from T_Employ WHERE FSalary>2000 group by FAge;
排序的前三个 TOP 3
SELECT TOP 3 * from T_Employ order by FSalary DESC;
SELECT TOP 3 FName,FAge,FSalary from T_Employ order by FSalary DESC,FAge DESC;
检索工资从高到低排序 第人开始的三个人的信息
SELECT TOP 3 * FROM T_Employ WHERE FNumber NOT IN(SELECT TOP 5 FNumber FROM T_Employ ORDER BY
FSalary) ORDER BY FSalary DESC;
添加两个字段
update T_Employ set FSubComPany='Beijing',FDepartment='Development'
where FNumber='DEV001';
update T_Employ set FSubComPany='ShenZhen',FDepartment='Development'
where FNumber='DEV002';
update T_Employ set FSubComPany='Beijing',FDepartment='HumanResource'
where FNumber='HR001';
update T_Employ set FSubComPany='Beijing',FDepartment='HumanResource'
where FNumber='HR002';
update T_Employ set FSubComPany='ShenZhen',FDepartment='InfoTech'
where FNumber='IT001';
update T_Employ set FSubComPany='Beijing',FDepartment='InfoTech'
where FNumber='IT002';
update T_Employ set FSubComPany='Beijing',FDepartment='Sales'
where FNumber='SALE001';
update T_Employ set FSubComPany='Beijing',FDepartment='Sales'
where FNumber='SALE002';
update T_Employ set FSubComPany='ShenZhen',FDepartment='Sales'
where FNumber='SALE003';
消除重复的行 distinct
select distinct FDepartment from T_Employ;
distinct 是用来消除完全重复的行用的 不是用来消除字段的
select distinct FDepartment,FSubCompany from T_Employ;
新建一个临时员工的表
CREATE TABLE T_TempEmployee (FIdCardNumber varchar(20),FName varchar(20),FAge int,primary key(FIdCardNumber));
INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge) values('1234567890121','Sarni',33),
('1234567890122','Tom',26),('1234567890123','Yalaha',38),('1234567890124','Tina',26)
,('1234567890125','Konkaya',29),('1234567890126','Fotifa',46);
union 将上下两个字段的结果联合在一起
上下两个字段的列数和数据类型要相同.
select FName,FAge from T_TempEmployee
union all
select FName,FAge from T_Employ ;
select FName,FAge,0 from T_TempEmployee
union all
select FName,FAge,FSalary from T_Employ ;
如果没有特殊理由 不要丢掉all 单独用union会去掉重复的行
select FName,FAge from T_TempEmployee
union
select FName,FAge from T_Employ ;
写报表经常用的方法:增加一个说明列,同时用UNION ALL合并.(不用UNION ALL就会出现多个表)
1 查询员工年龄
select '正式员工最高年龄',MAX(FAge) from T_Employ
UNION ALL
select '正式员工最低年龄',MIN(FAge) from T_Employ
UNION ALL
select '临时员工最高年龄',MAX(FAge) from T_TempEmployee
UNION ALL
select '临时员工最低年龄',MIN(FAge) from T_TempEmployee;
2 查询每位正式员工的信息,包括工号,工资,并且在最后一行加上所有员工工资额合计
select FNumber,FSalary from T_Employ
union all
select '工资合计',SUM(FSalary) from T_Employ;