使用命令创建MySQL数据库 用命令语句创建数据库

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 = 2
delete  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 = 29
update gsbh set xingming = '唐僧' where bianhao = 2
update 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 bumen
drop table bumen

insert 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 bumen
select bianhao 编号 ,mingcheng 名称,didian 地点 from bumen


insert 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 renwu
select  * from renwu
select bianhao 编号 ,mingcheng 名称,didian 地点 from bumen

--查询宋江7
select  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,bumen
select * 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)