/*对于表employee1本身进行操作*/
/*创建employee1表*/
/*
create table employee1(
first varchar(15),
last varchar(20),
age numeric(3),
address varchar(30),
city varchar(20),
state varchar(20));
*/
/*主键自动增长identity,主键约束primary key,外键约束foreign key,check约束,default约束,唯一约束unique,null,not null,最前面的记录top,不允许重复distinct,*/
/*where后面按次序:in, between and,=,>,<,>=,<=,or,and,null,not null,isnull,模糊函数like(%,%A%,_A%,_,[a-f],[a,f],[~a-c]注意要加单引号(oracle/mssql)或者(双引号mssql)),group by,having,order by,*/
/*聚合函数max(),min(),avg(),count(),count(*) */ ,如果使用group by 的话select只能是分组后的部分整体信息,不能出现组内的详细信息,having是对group by 分组后的数据进行过滤
/*修改整个employee1表*/
alter table employee1;
/*删除整个employee1表*/
--drop table employee1;
/*对于表employee1里面的内容进行操作*/
/*插入数据employee1表*/
/*
insert into employee1(first ,last ,age ,address ,city ,state)
values('lin' ,'san' ,20 ,'南宁' ,'北海','广西');
insert into employee1(first ,last ,age ,address ,city ,state)
values('liu' ,'er' ,30 ,'恢复' ,'玉林','广西');
insert into employee1(first ,last ,age ,address ,city ,state)
values('zhang' ,'si' ,56 ,'四分卫' ,'湖南','广西');
insert into employee1(first ,last ,age ,address ,city ,state)
values('sun' ,'jia' ,76 ,'×××' ,'桂林','广西');
insert into employee1(first ,last ,age ,address ,city ,state)
values('ou' ,'yu' ,23 ,'提货人' ,'柳州','广西');
insert into employee1(first ,last ,age ,address ,city ,state)
values('peng' ,'feng' ,43 ,'个人网' ,'北流','广西');
insert into employee1(first ,last ,age ,address ,city ,state)
values('chen' ,'yangsheng' ,89 ,'维尔瓦' ,'白色','广西');
*/
/*更新employee1表中某一条数据*/
/*
update employee1
set age=100 ,state='广东'
where first='lin';
*/
/*删除employee1表中全部数据*/
--delete from employee1;
/*删除employee1表中某一条数据*/
--delete from employee1 where state='广西';
/*
select top 、、、distinct
from 表1
join 表2
on 连接条件(相当于where 的条件)
join 表3
on 连接条件(相当于where 的条件)
where后面按次序:in, between and
=,>,<,>=,<=
or,and,null,not null,isnull
模糊函数like(%,%A%,_A%,_,[a-f],[a,f],[~a-c]注意要加单引号(oracle/mssql)或者(双引号mssql))
group by
having
order by
*/
/*select查询语句*/
/*
select first ,last ,age
from employee1
where age > 70;
*/
/*比较运算符号 = , > ,< ,>= ,<= ,<> ,like */
/*
select first ,last ,age
from employee1
where first like '%g';
*/
--select * from employee1;
/*更新employee1表中age列是20的哪一行*/
/*
update employee1
set first='tang' ,last='hong' ,age=33 , address='上海' ,city='南京' ,state='中国'
where age=20;
*/
/*合计函数min ,max ,sum ,avg ,count ,count(*) 一般需要"GROUP BY"子句,也可不用*/
/*下面两个例子是不用"GROUP BY"子句的*/
--select avg(age) from employee1;
--select avg(age) from employee1 where state='广西';
/* count(*) */
--select count(*) 行数 from employee1;
/*gruop by指 state这一列数据有部分相同的情况下的、年龄最小的哪一行*/
--select min(age) ,state from employee1 group by state;
select SUM(age) , state from employee1 group by state;
--update employee1 set state='广东' where age=23;
--select min(age) ,state from employee1 group by state;
/*在同一个地方(state)的人的平均年龄[avg(age)],having指这些人的平均年龄中又要大于50的*/
--select avg(age) 同地方的人平均年龄 from employee1 group by state having avg(age) > 50;
/*order by*/
--select first ,age ,state from employee1 where state='广西' order by age desc;
--select first ,age ,state from employee1 where state='广东' order by age;
分页查询:
嵌套查询:
外连接:
完全连接:
交叉连接:
联合连接union:
自连接:
视图:
事务:
索引:
存储过程:
TL-SQL:
游标:
触发器: