《数据库基础培训SQL语言培训1.ppt》由会员分享,可在线阅读,更多相关《数据库基础培训SQL语言培训1.ppt(21页珍藏版)》请在三一办公上搜索。
1、数据库基础: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
2、 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 张%
3、上句查找出姓名为“张”开头的学生的学生编号和姓名其它约束方法: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,
4、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(先按照成绩降序,如果成绩相同,则按照编号升序)
5、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(mar
6、k)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
7、,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 o
8、uter 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.
9、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 fr
10、om 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(
11、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(修改数据库对象),