sql 经典命令行建立数据库 （增删改查）

sql  命令行  建立数据库  ，对于数据 增删改查的操作语句

--命令行建立数据库-- 注释的意思 --新建数据可create database sanguo--建表的同时建字段create table renwu(     bianhao  int,    xingming nvarchar(5),    xingbie  nchar(1),    zhiwu nvarchar(10),    nianlin int,    gongzi numeric (6,2))--删除表（直接把表删除，连字段都没有了）drop table renwu---增,删,改,查,----------------添加记录insert  into renwu values(1,'陈冠希1','男','摄影师1',28,8888);insert  into renwu (bianhao,xingming,xingbie,zhiwu)  values(2,'陈冠希2','男','摄影师2');insert  into renwu values(3,'陈冠希3','男','摄影师3',28,8810);insert  into renwu values(4,'陈冠希4','男','摄影师4',28,8811);insert  into renwu values(5,'陈冠希5','男','摄影师5',28,8812);--删除全部的记录；delete from renwu--删除某一个记录(delete 只删除记录，不删除表，字段还在)delete  from renwu where bianhao = 2delete  from renwu where xingming ='陈冠希2'--查询表--查询所有的字段select  * from renwu--查询某一个字段select (xingming) from renwu--查询某几个字段select xingming,gongzi from renwu--对于字段添加别名select xingming 姓名,gongzi 工资 from renwu--按条件查询select  bianhao ,xingming,xingbie,zhiwu,nianlin,gongzi from renwu where bianhao > 2--更改记录update renwu set gongzi  = gongzi *1.1 where gongzi > 8000--时间函数,获取时间create table time1( shijian datetime)insert into time1 values(getdate())insert into time1 values('1995-5-23')select  * from time1

===========对于数据库（sql server）的查询操作的命令行操作==============

--主键:不允许为空值create table gsbh(bianhao int primary key,--主键为编号xingming nvarchar(10),nianlin int)--添加字段记录  注意没有逗号,insert  into  gsbh  values(1, '悟空1', 28)insert  into  gsbh  values(2, '悟空2', 29)insert  into  gsbh  values(3, '悟空3', 30)select  * from gsbh//更改某个记录update  gsbh set nianlin = 23 where nianlin = 29update gsbh set xingming = '唐僧' where bianhao = 2update gsbh set xingming = '八戒' where nianlin = 30--删除delete from gsbh where nianlin = 30 and xingming = '八戒'--外键只能指向主键并且类型完全一致create table bumen( bianhao int primary key,mingcheng nvarchar(5),didian nvarchar(5),) create table renwu(paihang int primary key,xingming nvarchar(10),zhiwu nvarchar(5),shangji int,ruzhishijian datetime,gongzi numeric(6,1),buzhu numeric(5,1),bianhao int foreign key references  bumen(bianhao))delete   from bumendrop table bumeninsert into  bumen values(1,'总头领','聚义厅')insert into  bumen values(2,'文职','前山')insert into  bumen values(3,'马军','左山')insert into  bumen values(4,'步军','右山')insert into  bumen values(5,'水军','山下')insert into  bumen values(6,'后勤','后山')select  * from bumenselect bianhao 编号 ,mingcheng 名称,didian 地点 from bumeninsert into  renwu(paihang,xingming,zhiwu,ruzhishijian,gongzi,buzhu,bianhao) values (101,'宋江','寨主','2002-3-15',20009,5000,1)insert into renwu values  (102,'宋江2','寨主2','101','2002-3-15',20008,5000,1)insert into renwu values  (103,'宋江3','寨主3','102','2002-3-15',20007,4000,2)insert into renwu values  (104,'宋江4','寨主4','103','2002-3-15',20006,3000,3)insert into renwu values  (105,'宋江5','寨主5','104','2002-3-15',20005,2000,4)insert into renwu values  (106,'宋江6','寨主6','105','2002-3-15',20004,1000,5)insert into renwu values  (107,'宋江7','寨主7','106','2002-3-15',20003,500,6)insert into renwu values  (108,'宋江8','寨主8','107','2002-3-15',20002,400,6)insert into renwu values  (109,'宋江9','寨主9','108','2002-3-15',20001,300,6)delete from renwuselect  * from renwuselect bianhao 编号 ,mingcheng 名称,didian 地点 from bumen--查询宋江7select  xingming ,bianhao ,gongzi from renwu where paihang = 107--查询编号有多少(不重复)select distinct bianhao xingming from renwu --显示每个员工的姓名和年薪select xingming 姓名, ruzhishijian 入职时间 ,gongzi * 12 + buzhu* 12 年薪 from renwu--查询月薪补助小于3000的select xingming 姓名,zhiwu 职位,gongzi 工资 ,buzhu 补助 from renwu where  1000<=buzhu and buzhu < 3000 --模糊查询(进行姓名查询   ，注意_ 一个’_‘代表一个字符)select xingming 姓名,zhiwu 职位,gongzi 工资 ,buzhu 补助 from renwu where  xingming like '__8%'--批量查询select xingming 姓名,zhiwu 职位,gongzi 工资 ,buzhu 补助 from renwu where paihang in( 101,103,106,108,102)--显示一把手的工资(上级为空)select xingming 姓名,zhiwu 职位,gongzi 工资 ,buzhu 补助 from renwu where  shangji is null--排序默认是 升序   降序是desc  中文按照是拼音的顺序 和 音调Select xingming 姓名,zhiwu 职位,gongzi 工资,buzhu 补助 from renwu  order by gongzi  desc --升序排列工资 Select xingming 姓名,zhiwu 职位,gongzi 工资,buzhu 补助 from renwu  order by gongzi  --按照姓名排序(降序)Select xingming 姓名,zhiwu 职位,gongzi 工资,buzhu 补助 from renwu  order by xingming desc--求最大的工资select  max(gongzi ) from renwu--求最小工资select  min(gongzi ) from renwu--求总工资，和平均工资select sum(gongzi) '总工资', avg(gongzi)'平均工资' from renwu--查询工资最多的人的姓名，Select xingming 姓名,zhiwu 职位,gongzi 工资,buzhu 补助 from renwu where gongzi > (select  avg(gongzi ) from renwu)--将工资与平均工资对比（带有函数的要用字段标记起来）select xingming 姓名,zhiwu 职位,gongzi 工资,buzhu 补助,(select avg(gongzi)'平均工资' from renwu) from renwu where gongzi > (select  avg(gongzi ) from renwu)--按照部门编号升序，但是要按照工资降序select xingming 姓名,zhiwu 职位,gongzi * 12 + isnull(buzhu ,0)  年薪,buzhu 补助 from renwu order by bianhao,  年薪 desc --统计有多少条记录select count(*)  from renwu--select bianhao,(sum(gongzi)  * 12) 年薪,( avg(gongzi) * 12) 平均工资 ,(min(gongzi) * 12) 最低年工资,zhiwu 名称  from renwu group by bianhao,zhiwu order by bianhao--统计平均工资 大于20005的部门select bianhao,( avg(gongzi) * 12) 平均工资 from renwu group by bianhao having avg(gongzi) < 20005 --多表查询 --的笛卡尔集现象select * from renwu,bumenselect * from renwu,bumen where bumen.mingcheng = '水军' and renwu.bianhao= bumen.bianhao--2张表的编号完全重合select * from renwu,bumen where renwu.bianhao= bumen.bianhao--显示姓名和所在部门和部门所在的编号select xingming 姓名,mingcheng 名称,renwu.bianhao 部门编号 from renwu, bumen where renwu.bianhao = bumen.bianhao--显示姓名和所在部门和工资  和 部门编号 = 4的select xingming 姓名,mingcheng 名称,gongzi 工资,renwu.bianhao 部门编号 from renwu, bumen where renwu.bianhao = bumen.bianhao and bumen.bianhao = 4--显示部门编号，姓名，工资，并且按照部门编号进行排序select a.xingming 姓名,b.xingming 上级 from renwu a,renwu b  where a.shangji = b.paihang--显示与宋江7同部门的姓名select xingming 姓名,renwu.bianhao 部门 from renwu ,bumen where (renwu.bianhao = (select  bianhao from renwu where xingming = '宋江7'))  and (renwu.bianhao = bumen.bianhao)