-------------------------------增:向数据表中添加一条记录(一个元组)--insert into <tableName> valuses (v1,v2,v3...)insertinto tb_courses values('大学语文',3);insertinto tb_stus values ('10010','张三','男',23,'13030303300');--要求:当进行添加操作时,必须在表名后面通过()列出赋值的字段名,即使是所有字段,也请列出字段名--好处1:所给的值无需和表定义的列顺序保持一致,只需和表名后列出的字段名保持一致--好处2:可以提高语句的兼容性/健壮性(例如新添一个字段后则无法使用上面的方式)--insert into <tableName> (c1,c2,c3...) valuses (v1,v2,v3...)insertinto tb_stus (stu_num,stu_name,stu_sex,stu_age,stu_tel) values ('10011','李四','女',21,'13030303301');--删:从数据表中删除不再需要的数据(删除操作针对一个或者多个元组/记录)deletefrom tb_stus where stu_num = '10016';deletefrom tb_stus where stu_age < 24or stu_age>25;deletefrom tb_stus where stu_age between 24and25;deletefrom tb_stus where stu_age not between 24and25;--改:修改数据表中某条记录的某一列或某几列update tb_stus set stu_age=27where stu_num='10010';update tb_stus set stu_age=17,stu_name='小花',stu_tel='13030303322'where stu_num='10011';update tb_stus set stu_age=stu_age+1where stu_sex='女';update tb_stus set stu_age=stu_age+1;
DML_查询
--查询语句:获取数据库中数据(不会影响数据库中的数据)--单表查询:从某一张表中查询数据-- * 表示查询出满足条件的所有列select * from tb_stus;-- 显示指定列select stu_num,stu_name from tb_stus where stu_age between 23and30;--给字段取别名(加不加as都可以)select stu_num as'学号',stu_name '姓名',stu_age '年龄'from tb_stus;--计算列select stu_num as'学号',stu_name '姓名',2016-stu_age '出生年份'from tb_stus;--条件查询:在select语句后添加where子句表示查询满足条件的数据select * from tb_stus where stu_sex='男';select * from tb_stus where stu_age>20;--like查询--查询出名字中包含字母'T'的学生信息(%代表0~n个字符)select * from tb_stus where stu_name like'%T%';--查询出名字中首字母'T'的学生信息select * from tb_stus where stu_name like'T%';--查询出名字中包含字母'i'的学生信息( _ 代表任意一个字符)select * from tb_stus where stu_name like'_i%';--between..and.. 关键字select * from tb_stus where stu_age between 19and24;--not 条件取反select * from tb_stus where stu_name notlike'T%';select * from tb_stus where stu_age not between 19and24;--多条件查询 ( or , and)select * from tb_stus where (stu_age<20and stu_sex='女') or stu_num='10010';select * from tb_stus where stu_age <=20or stu_sex='女';--group by分组查询 select 不能字节显示字段名,只能显示被分组的字段名。--或者聚合函数(count,avg,max,min,sum)--count(*) 统计记录数--avg(column) 统计指定字段的平均值--max(column) 获取当前字段的最大值--min(column) 获取当前字段的最小值--sum(column) 统计指定字段的值总和select
stu_sex '性别',
sum(stu_age) '总和',
min (stu_age) '最小年龄',
max(stu_age) '最大值',
avg(stu_age) '平均值',
count(*) '人数'from tb_stus
groupby stu_sex;select stu_sex '性别',count(*) '人数'from tb_stus groupby stu_sex;--筛选后分组select stu_sex '性别',count(*) '人数'from tb_stus where stu_age>20groupby stu_sex;--分组后筛选 having--显示平均年龄大于24的分组(按性别分组)select stu_sex '性别',count(*) '人数'from tb_stus groupby stu_sex havingavg(stu_age)>24;--嵌套查询select * from tb_stus where stu_age>20and stu_num in
(select stu_num from tb_stus where stu_sex='女');--排序 order by :对查询结果按照某个字段查询select * from tb_stus orderby stu_age;--默认升序select * from tb_stus orderby stu_age desc;--降序select * from tb_stus orderby stu_age asc;--升序--多字段排序select * from tb_stus orderby stu_sex desc,stu_age asc;--查询前2条select top(2) * from tb_stus;select top(2) stu_num from tb_stus;select * from tb_stus
where stu_num notin (select top(2) stu_num from tb_stus);--分页查询 --select top(m) * from tb_stus--where stu_num not in (select top((n-1)*m) stu_num from tb_stus);
DML_多表联合查询
--创建部门表
use db_test
createtable dept(
deptno intprimarykeyidentity(1001,1),
dname varchar(20) notnull,
daddr varchar(30) notnull
);
use db_test
createtable emp(
eno varchar(5) primarykey,
name varchar(20) notnull,
age intnotnull,
sal decimal(8,2) notnull,
deptno int,
constraint fk_emp_dept foreignkey (deptno) references dept(deptno)
);--添加5个部门信息insertinto dept(dname,daddr) values ('市场部','武汉');insertinto dept(dname,daddr) values ('研发部','宜昌');insertinto dept(dname,daddr) values ('人事部','北京');insertinto dept(dname,daddr) values ('财务部','上海');insertinto dept(dname,daddr) values ('售后服务部','深圳');--添加8个员工信息--研发部1002insertinto emp(eno,name,age,sal,deptno) values ('10001','张三',23,1000.00,1002);insertinto emp(eno,name,age,sal,deptno) values ('10003','李四',29,1300.00,1002);insertinto emp(eno,name,age,sal,deptno) values ('10007','赵六',33,1700.00,1002);--市场部1001insertinto emp(eno,name,age,sal,deptno) values ('10002','mike',19,800.00,1001);insertinto emp(eno,name,age,sal,deptno) values ('10004','rose',21,1200.00,1001);--财务部1004insertinto emp(eno,name,age,sal,deptno) values ('10005','jack',22,3000.00,1004);--无部门insertinto emp(eno,name,age,sal,deptno) values ('10006','tom',26,2400.00,null);insertinto emp(eno,name,age,sal,deptno) values ('10008','hans',28,2000.00,null);--多表联合查询:查询的数据来至于多张表select * from dept;select * from emp;--自然连接查询:是一种特殊的等值连接--无需指定字段,可以自动匹配--查询结果要去除重复列select dept.deptno,dname,daddr,eno,name,age,sal from dept,emp where emp.deptno = dept.deptno;--左连接:左表数据都会显示select * from dept leftjoin emp on dept.deptno=emp.deptno;--右连接:右表数据都会显示select * from dept rightjoin emp on dept.deptno=emp.deptno;--全连接:两张表数据都会显示select * from dept fulljoin emp on dept.deptno=emp.deptno;--等值连接查询:列出两张表中某个字段值相等的匹配,只显示匹配成功项select * from dept join emp on emp.deptno = dept.deptno ;select * from dept,emp where emp.deptno = dept.deptno;--给表取别名select * from dept d,emp e where e.deptno = d.deptno;--多表嵌套联合查询--查询出研发部的部门编号select deptno from dept where dname='研发部';--查询出员工表中部门编号为1002的员工信息select * from emp where deptno=1002;--查询研发部所有员工的信息select * from emp where deptno in (select deptno from dept where dname='研发部');--统计各个部门员工人数select deptno,count(*) 'num'from emp groupby deptno having deptno>0;select * from dept leftjoin(select deptno,count(*) 'num'from emp groupby deptno having deptno>0) v on dept.deptno=v.deptno;--查询有员工的部门名称(distinct用于select之后用于去除查询结果的重复项)select dname from dept where deptno in (selectdistinct deptno from emp);select * from tb_stus;select * from tb_couresses
;--集合查询select deptno,dname from dept
unionselect eno,name from emp;
视图
use db_test;
--视图:可以将查询的结果单独创建成一张"表"createview stu_grade as
(select deptno,count(*) 'num'from emp groupby deptno having deptno>0);createview stu_nv as(select * from tb_stus where stu_sex='女');--对视图的更新(删除/添加/修改)都会影响到原表select * from tb_stus;select * from stu_nv;
索引
--索引相当于一本书的目录,可以很大程度上提高我们的查询效率--创建索引--1若将某数据表的某字段设置为主键则会自动为其建立索引--2若将某数据表的某字段设置为唯一键(unique)则也会自动为其建立索引--3如果我们频繁的根据某一字段进行查询操作,则需手动在此字段建立索引create index stu_name_tb_stus on tb_stus(stu_name);drop index stu_name_tb_stus on tb_stus;--同时在两个字段建立索引create index stu_name_tb_stus on tb_stus(stu_name desc,stu_age asc);
SQL Server 基本SQL用法SQL Server 基本SQL用法DDL_数据库操作DDL_数据表操作DML_增删改DML_查询DML_多表联合查询视图索引DDL_数据库操作--这是注释--有关数据库操作的DDL指令--①创建一个新的数据库--一个SQLsever数据库包含一个数据文件,和一个日志文件--数据文件(mdf)用来存储数据库中的数据--日志文件(ldf)