数据库基础培训SQL语言培训1.ppt
数据库基础:SQL语法(1),北明软件员工培训讲义,2,章节,查询数据的SQL语句修改数据的SQL语句管理数据定义的SQL语句,3,查询数据的SQL语句,SELECT 语句,4,查询数据的SQL语句-SELECT,本章讲述如何使用SELECT语句的五个子句。在SELECT语句中，你必须按以下顺序使用这些子句：SELECT子句FROM子句WHERE子句ORDER BY子句SELECT 字段1,字段2字段n FROM 表1,表 2表n,5,SELECT语句基础,可以用多种方法来查询数据库中的一个表。你可以裁剪SELECT语句来达到以下目的：检索所有的或指定的列select id,name from student（查询编号，姓名)select*from student(查询所有字段)对检索所得的数据执行计算或其它函数select id,name,sum(mark)from student group by id,name(按照编号、姓名为分组，对“成绩”进行汇总)(简单的说，就是查询每个学生的总成绩),6,Where子句,Where 子句用于限制数据select id,name from student where id=222233444上句查找出编号为222233444的学生的学生编号和姓名select id,name from student where name like 张%上句查找出姓名为“张”开头的学生的学生编号和姓名其它约束方法：Where column1 like _b%(部分匹配,%为通配符,表示=0个任意字符,_表示一个任意字符)Where id between 10000 and 20000(范围匹配)Where id=10000 and id=,.),7,Where子句(2),多条件限制1)select id,name from student where id 23456 and name like 张%2)select id,name from student where id 23456 or name like 张%3)select id,name from student where(id 23456 or name like 张%)and mark60不要考虑条件匹配的先天优先级，在你想优先匹配的地方加上括号，永远是对的,8,Order by 子句(用于排序),单列排序Select id,name from student order by id asc(升序)Select id,name from student order by id desc(降序)多列排序select id,name,mark from student order by mark desc,id asc(先按照成绩降序，如果成绩相同，则按照编号升序)asc可以省略，因为默认是按照升序排列但是desc不可以如:select id,name from student order by id(升序),9,Group by(用于汇总计算),GROUP 中文意思：分组select id,name,sum(mark)from student group by id,name（上句按照id,name 为分组，计算mark的汇总值)Select id,name,avg(mark),sum(mark)from student group by id,name(上句按照id,name 为分组，计算mark的平均值)Select id,name,avg(mark)from student group by id(上一句语法是错误的，因为有一个字段name在分组之外，数据库不知道怎么归类计算)Select id,name,mark1+mark2 from student group by id,name(上一句也是错误的，mark1+mark2是两个字段间的计算，没有汇总计算，分组没有意义),10,UNION 组合查询结果,1)Union 组合查询结果,合并重复记录Select id,name from studentUnionSelect id,name from teacher2)Union All 组合查询结果，不合并重复记录Select id,name from studentUnion AllSelect id,name from teacher,11,多表关联(1),内连接(inner join)写法一:select a.id,a.name,b.id,b.name from student a,teacher b where a.techer_id=b.id写法二:select a.id,a.name,b.id,b.name from student a inner join techer b on a.techer_id=b.id（写法二是标准语法),12,多表关联(2),外连接(outer join)左外连接(left outer join)写法一:select a.id,a.name,b.id,b.name from student a,teacher b where a.techer_id*=b.id写法二:select a.id,a.name,b.id,b.name from student a left outer join techer b on a.techer_id=b.id（写法二是标准语法)右外连接(right outer join)写法一:select a.id,a.name,b.id,b.name from student a,teacher b where a.techer_id=+b.id写法二:select a.id,a.name,b.id,b.name from student a right outer join techer b on a.techer_id=b.id（写法二是标准语法),13,子查询,子查询的结果相当于一张tableSelect A.id,A.name,sum(A.mark)from(select id,name,mark from student where mark 60)A Group by A.id,A.name子查询的结果相当于一个集合Select id,name from student where id in(select id from student where mark 60)很多数据库支持：Select id,name from student where(id,name)in(select id,name from student where mark 60),14,Tea Break!,15,修改数据的SQL语句,修改数据的SQL语句Insert(插入)Delete(删除)Update(修改),16,INSERT语句,基本语法:INSERT INTO table(column1,column2,columnN)Values(value1,value2,valueN)例子:Insert into student(id,name,subject,mark)Values(25,李四,语文,60),17,INSERT语句(2),根据查询结果插入数据Insert into table1(select column1,column2,columnN from table2)Insert into student(select id,name,subject,mark from student2)注意:student表一定要刚好有 id,name,subject,mark四个字段,18,DELETE 语句-删除数据,情况一:全表删除 Delete from table1(删除table1的全部数据)建议用 truncate 语句进行删除truncate table student情况二:组合Where子句 Delete from student where id=2234343,19,UPDATE语句修改数据,语法:UPDATE table set column1=xxx,column2=xxx,columnN=xxxx where conditionSample:Update student set id=333 where name=张三,20,Tea Break!,21,管理数据定义的SQL语句,CREATE(创建数据库对象)DROP(删除数据库对象)ALTER(修改数据库对象),
