常用sql整理增删改查

--创建数据库
create database hero

--删除数据库
drop database hero

--删除表
drop table heros
drop table herostype

--创建数据库表
create table heros
(
herosid  int primary key,
herosname  nvarchar(30),
sex  nchar(2),
age   int,
sal  numeric(10,2),
comm  numeric(10,2),
nickname  nvarchar(30),
addtime  datetime,
typeid  int foreign key references herostype(typeid)
)

create table herostype
(
typeid  int primary key,
typename nvarchar(30),
typeoperation nvarchar(30),
typeaddtime datetime
)

--日期类型
--datetime 表示日期 getdate()
--timestamp 时间戳
--主键和外键
--主键:primary key
--外键:foreign key references 表名(主键字段)  --外键只能指向主键,外键不能为空,两者数据类型要一致


--添加数据
--语法:insert into [表名] values('字段1的值','字段2的值','字段3的值',...)
insert into heros values(1,'宋江','男',40,5000,null,'及时雨',getdate(),1);
insert into heros values(2,'卢俊义','男',38,3000,null,'玉麒麟',getdate(),2);
insert into heros values(3,'吴用','男',46,3200,800,'智多星',getdate(),2);
insert into heros values(4,'公孙胜','男',34,2500,100,'上云龙',getdate(),3);
insert into heros values(5,'林冲','男',28,2000,null,'豹子头',getdate(),4);
insert into heros values(6,'孙二娘','女',null,1200,null,'母夜叉',getdate(),5);
insert into heros values(7,'鲁智深','男',36,1700,50,'花和尚',getdate(),4);
insert into heros values(8,'秦明','男',38,2100,null,'霹雳火',getdate(),3);
insert into heros values(9,'呼延灼','男',34,1300,300,'双鞭',getdate(),4);
insert into heros values(10,'花荣','男',26,1650,null,'小李广',getdate(),4);
insert into heros values(11,'柴进','男',28,1450,null,'小旋风',getdate(),4);
insert into heros values(12,'朱仝','女',null,800,1100,'美髯公',getdate(),6);

insert into herostype values(1,'帝王','总裁',getdate());
insert into herostype values(2,'大将军','副总裁',getdate());
insert into herostype values(3,'军师','总裁助理',getdate());
insert into herostype values(4,'将军','经理',getdate());
insert into herostype values(5,'武将','职员',getdate());
insert into herostype values(6,'文官','职员',getdate());

--修改数据(primary key 可以修改 但是不能为空/重复)
--语法:update [表名] set [字段1]='字段1的新值',[字段2]='字段2的新值',... where [字段n]='字段n的值'
update heros set [id]=5 where id=6
update heros set [name]='鲁智深' where id=5
update heros set [name]='孙二娘',sex='女' where id=5
update heros set [name]='孙二娘' where sex is null

--删除数据
--语法:delete from [表名];删除所有数据
--语法:delete from [表名] where [字段1]='字段1的值'and [字段2]='字段2的值' or [字段3]='字段3的值';删除指定数据
delete from heros where age is null  --删除年龄为null的人
delete from heros where (age>=40 and age<=30) or age is null --删除年龄大于等于40 和 年龄小于等于30的人 或者年龄为null的人


--查询数据库
--语法:select [字段1],[字段2]...from [表名] where [字段n]='字段n的值'
select * from heros
select * from herostype --查询所有表

--查询 年龄 职称编号
select nickname,age, typeid from heros where herosname='林冲'

--取消重复行 distinct
select distinct age from heros where typeid=4


--显示每个将才的年工资+奖金
--处理空值 isnull(字段,空值赋值为)
select herosname 英雄,(sal+isnull(comm,0))*13 年工资 from heros

--显示工资高于3000的人
select * from heros where sal>3000

--查找2011.4.19添加的人
select * from heros where addtime>'2011-4-19'

--显示工资在2000到2500之间的人
select * from heros where sal>=2000 and sal<=3000
--或者使用between 表示 (>=  <=) 注意包含等号
select * from heros where sal between 2000 and 3000

--使用like 模糊查询 % 代表任何多个字符  _ 代表任何单个字符
--查找工资开头为2的的人
select sal,herosname from heros where sal like '2%'
--查找工资第二个字符为0的人
select sal,herosname from heros where sal like '_0%'

--显示年龄 为 28,34,38的员工 使用in
select * from heros where age=28 or age=34 or age=38
select * from heros where age in(28,34,38)

--显示没有加薪的人,以及没有年龄的人
select * from heros where comm is null and age is null

--查询工资高于 2000的 或者 是大将军 同时他的名字首字为 '卢'
select * from heros where (sal>2000  or typeid>3) and herosname like '卢%'

--按照工资从低到高 排列所有人
--order by [字段] asc 默认为升序
--order by [字段] desc 降序
select * from heros order by sal asc --默认asc 可以省略
--按照工资从低到高 排列所有人
select * from heros order by sal desc

--按名字排序
select * from heros order by herosname

--按照名字排序升序,而人的工资降序
select * from heros order by nickname,sal desc

--使用别名排序,统计每个人的年薪,并按照从低到高顺序排序
select herosname,(sal+isnull(comm,0))*13 年薪 from heros order by (sal+isnull(comm,0))*13
select herosname,(sal+isnull(comm,0))*13 年薪 from heros order by 年薪  --这个效率比上面的高

--显示所有人最低工资和最高工资和人名
select herosname,sal from heros where sal=(select min(sal) from heros) or sal=(select max(sal) from heros)


--显示所有人的平均工资和工资总和
select avg(sal) 平均工资,sum(sal) 总工资 from heros

--把高于平均工资的人的名字和工资以及平均工资及与平均工资的差额显示出来
select herosname,sal,(select avg(sal) from heros),sal-(select avg(sal) from heros) from heros where sal>(select avg(sal) from heros)

--统计有多少人
select count(*) from heros

--group by  having
--group by 用于对查询的结果分组统计
--having 用户对限制分组显示结果

--显示每个部门的平均工资和最高工资
select avg(sal) 平均工资,max(sal) 最高工资,typeid  from heros group by typeid
--显示部门男女的平均工资和最低工资
select avg(sal) 平均工资,min(sal) 最低工资,typeid,sex from heros group by typeid,sex order by typeid

--显示平均工资低于3000的部门和他的平均工资
--having 往往和 group by 结合使用,可以对分组查询结果进行筛选,并从低到高排序
select avg(sal),typeid from heros group by typeid having avg(sal)<3000 order by avg(sal)

--多表查询(注意避免笛卡尔集)
select * from herostype,heros ---(出现笛卡尔集)

--显示人员的全部信息
select * from herostype,heros where herostype.typename='武将' and herostype.typeid=heros.typeid

--显示人员的身份和职位
select herosname,sal,typename,typeoperation from herostype,heros where herostype.typename='将军' and herostype.typeid=heros.typeid
select herosname,sal,typename,typeoperation from herostype,heros where  herostype.typeid=heros.typeid

--如果两张表都有相同的名字的字段,则需要带表名(或别名)
select herosname,sal,typename,typeoperation,heros.typeid from herostype,heros where  herostype.typeid=heros.typeid
select herosname,sal,typename,typeoperation,h.typeid from herostype t,heros h where  t.typeid=h.typeid

--如何显示部门号为1的部门名,员工名,工资
select t.typename,h.sal,h.herosname,t.typeoperation from heros h,herostype t where h.typeid=4 and t.typeid=h.typeid order by h.sal desc

select * from heros
select * from herostype

--自连接
--在同一张表的链接查询
select herosname,nickname from heros where herosname=(select herosname from heros where typeid=1 )
--显示公司每个员工名字和他上级的名字
--分析将一个表看成两张表
select h.herosname 员工,t.herosname 上级,t.nickname from heros h,heros t where h.typeid=t.herosid  --内连接

--外连接:分两种 左外连接 和 右外连接


--显示公司每个员工名字和他上级的名字,没有上级的名字也要显示出来

--左外连接 :指left左边的表的记录全部显示,如果没有匹配的记录,就用null来补充
--语法:select  [字段1],[字段2] from [表一]   left join [表二]  on  [条件]

--右外连接 :指right左边的表的记录全部显示,如果没有匹配的记录,就用null来补充
--语法:select  [字段1],[字段2] from [表一]   right join [表二]  on  [条件]

--内连接 :指inner两个表的记录全部匹配的记录都显示显示,如果没有匹配的记录,就用去除
--语法:select  [字段1],[字段2] from [表一]   inner join [表二]  on  [条件]


select h.herosname 员工,t.herosname 上级,t.nickname from heros h,heros t where h.typeid=t.herosid  --内连接
select h.herosname 员工,t.herosname 上级,t.nickname from heros h inner join heros t on h.typeid=t.herosid  --内连接


select h.herosname 员工,t.herosname 上级,t.nickname from heros h left join heros t on h.typeid=t.herosid--外连接
select h.herosname 员工,t.herosname 上级,t.nickname from heros h right join heros t on h.typeid=t.herosid--外连接


--子查询
--子查询是嵌入在其他sql语句中的select语句,也叫嵌套查询
--单行子查询 只换回一行数据的子查询
--显示林冲同一部门的所有员工
select * from heros where typeid=(select typeid from heros where herosname='林冲')
select * from heros where typeid in (select typeid from heros where herosname='林冲')

--多行子查询 返回多行数据的查询
select * from heros where sex in(select distinct sex from heros where typeid=4) and typeid=4

--显示高于部门平均工资的人的信息
--在from子句中使用子查询
--分析,先得到各个部门的平均工资,在把得到的结果作为零时表对待,必须给子查询指定别名,如果需要调用子查询中的字段 也需加入别名
select avg(sal),typeid from heros group by typeid
select * from heros

select h.herosname,h.nickname,h.sal,s.avgs from heros h,(select avg(sal) avgs,typeid
from heros group by typeid) s
where h.typeid=s.typeid and h.sal>s.avgs


--分页查询
select * from heros order by herosid
--显示第5个到第10职员 按照年龄大小顺序
--分析:显示第一个到第4个职员
select top 4 * from heros order by herosid

select top 6 * from heros where herosid not in (select top 4 herosid from heros order by herosid) order by herosid


--表示 identity(1,1)表示testID 自增长,从1开始,每次加1
create table test
(
 testid int primary key identity(1,1),
 testname varchar(30),
 testpass varchar(30)
)
insert into test (testname,testpass) values('google','baidu')

select * from test

select testid from test


--压力测试 自助添加数据
insert into test(testname,testpass) select testname,testpass from test;

select count (*) from test

--test表按照id排序 100 105

select top 60 * from test where testid not in (select top 999999 testid from test order by testid) order by testid


--用查询结果创建新表

--语法: select * into 另一个表名 from 表名

--删除掉一张表的重复记录

create table cat
(
catid int,
catname varchar(40)
)

insert into cat values(2,'bb')

select * from cat
--drop table cat--删除cat表

select distinct * into #temp from cat --把表非重复的记录加入表#temp中
delete from cat--删除cat表内的记录
insert into cat select * from #temp --将#temp内的记录添加到cat表中
drop table #temp --删除#temp表


--约束

not null(非空)
如果在列上定义 not null 当插入数据时,必须提供数据

unique(唯一)
当定义了唯一约束后,该字段列不能重复,但是可以为null,最多只能有一个null

primary key(主键)
用于唯一的标示表行的数据,当定义了主键,该字段列不能重复而且也不能为null,一个表只有一个主键

--行级定义
--在每一行进行限制定义
create table test3
(
testid int primary key identity(1,1),
testname varchar(30), unique
testpass varchar(30),
testage int
)

--表级定义 把所有字段写完了 再写主键
--复合组件
create table test3
(
testid int,,
testname varchar(30),
testpass varchar(30),
testage int,
primary key(testid,testname)
)

--备份数据库
backup database hero to disk='d:/hero.bak'
--语法:backup database 数据库 to disk='路径+文件名.bak'

--还原数据库
restore database hero from disk='d:/hero.bak'
--语法:restore database 数据库 from disk='路径+文件名.bak'

--删除数据库
drop database hero
--语法:drop database 数据库名

--备份数据库的表

Crud 介绍(增删改查)
Crud 是在做计算处理是增加(Create),查询(Retrieve)(重新得到的数据),更新(update),和删除(delete)几个单词的首字母简写.主要被被用在描述
软件系统中数据库或者持久层的基本操作功能.

C reate new records
R etrieve existing records
U pdate existing records
D elete existing records

jdbc 的基本概念
1.jdbc-odbc桥连接
2.本地协议的java驱动程序
3.网络协议的java驱动程序
4.本地api

hibernate项目 是对jdbc再封装 实现了对数据库操作更广发的统一和更好的一致性


odbc 的基本概念
open database


--演示sql注入漏洞
create table users
(
username varchar(30),
passwd varchar(30)
)
insert into users values('username','password')

select * from users where username='username' and passwd='ee' or 1='1'