SQL数据库基础-课堂笔记案例

1、目 录第一章 数据库技术2数据库介绍2数据表2数据完整性3第二章 SQL语句6SQL语句的分类:6SQL语法案例6数据表的增删改查7数据表操作案例：8数据表约束限制9数据表约束限制案例：9第三章 数据库综合查询查询12聚合函数：13排序查询13分组函数14第四章 SQLServer数据库的函数16日期函数:16字符串函数:16转换函数:18数学函数:18第五章 重点: 多表联合查询19内连接:20外连接21第六章 MySql23说明：本文档是对马果老师的SQL数据库部分的讲课笔记进行二次整理，不仅含有课堂笔记，还把上课案例也添加进去了，希望可以对大家的以后的复习提供一些帮助，谢谢！-XZC第一

2、章 数据库技术数据库介绍答：数据库就是存储数据的仓库，应用程序只要访问这些仓库，就可以得到有用的数据信息。数据库: database数据库的总类:最主要的三种:SQLServer:它是微软的产品，其他数据库的语法，也是以SQLServer语法为基础。SQLServer7.0SQLServer2000SQLServer2005SQLServer2008(机房)SQLServer2010.MySql:它是一个小型数据库，适合学习与开发。Oracle：(甲骨文)它是一个大型数据库，java的开发，绝大部份都是采用oracle,安全性高，数据库类型丰富，稳定-SQLServer数据库-2005使用步骤

3、:1、开启数据库服务在一个数据库系统中，可以包含两种类型的数据库:第一种:系统数据库(包含系统信息)这种数据库不需要用户创建，它是系统自己的master-系统管理数据库-系统的核心数据库第二种:用户自定义数据库(用户自己创建的数据库)这种数据库，需要用户自己创建才能产生 数据库名称: mydb用户创建的数据库，会有两个文件:主要数据文件: *.mdf (main data file)#存放在当前数据库的核心数据日志数据文件: *.ldf (log data file)存放用户的一些操作记录，主要用于系统恢复系统一个数据库，可以包含很多数据表,每一个张数据表，就是具体存放数据的地方数据表数据表是

4、由：行与列来构成列:(字段)行:(记录) 一行数据，就是一条完整记录在创建表的时候，必须指定，当前表中包含哪一些列(字段)public class Stu int id; String name; int age;在创建数据表中的字段，也必须声明每一个字段的数据类型，不同的类型，分配的空间大小不同int -整数型 float-浮点型 char(100)-字符 varchar(100)-字符char 与 varchar都是字符型区别是：一个空间大小可变，一个不可变 char(20),varchar(20) 都表可以存放20个字符 char(20) 定义以后，系统会分配20个字符的存储空间大小，但

5、实际如果只存了一个字符，系统依然会让它占用20个字符空间大小，就会造成空间浪费 varchar(20) 它的空间大小，可以改变，一开始会分配20个字符的空间大小，如果实际只存放了1个字符，系统就会把多余19个空间释放,这样不会造成空间的浪费 varchar类型，存了数据以后，系统还需要额外消耗资源判断，空间是否占满，如果没有占满就会准备释放-由于要去检查空间是否占满，所以速度相对慢一点。 char类型，由于空间是固定的，存以数据，系统不会去检测空间的占用情况，所以速度更快 char:浪费空间，但速度快 varchar：节约空间，但速度相对较慢如果确定数据的长度占几个空间，这时候，用char类型

6、更好如果不能确定数据的长度，这时候使用varchar更节约空间 nchar(2) 可以存放两个汉字 char(2) 只可以存放一个汉字nvarchar(2)-如何拷贝数据库:分离数据库:断开数据库与数据库系统之间的连接附加数据库:建立连接数据库设计的时候，要保证数据的完整性数据完整性答：保证每一条数据准确性、可靠性、唯一性数据的完整性，包含三方面的内容:1、实体完整性: 保证数据表的每一条数据都可以被唯一的标识(不出现重复数据)第一种方式：设置主键 primary key第二种方式：设置标识列第三种方式: 设置唯一键unique key主键：对表中某一列，设置主键以后，这一列，就不允许出现重复

7、数据标识列: 这一个列的值，不需要用户来赋值，而是由系列自动分配。for( int i=1; i2 and len(pwd)0 and age0 and age21; -查询年龄大于岁select * from inf2 where age =21; -年龄大于等于岁select * from inf2 where age=21; -年龄为岁select * from inf2 where age!=21; -年龄不等于岁select * from inf2 where age21; -年龄不等于岁select * from inf2 where age21 and age23;-多条件查询-

8、修改数据select * from inf2;update inf2 set age=33 where id=10;-把编号为数据，年龄改成;update inf2 set age=55,name=张三 where id=10;update inf2 set age=age-5;-删除数据delete from inf2 where id=10;delete from inf2 where age=25;delete from inf2;-截断表truncate table inf2;insert into inf2 values(andy,22);delete 与 truncate的区别de

9、lete 可以带条件删除指定数据truncate 必须删除整张表的数据，不能带条件如果是删除全部数据truncate的性能会更好delete from inf;truncate table inf;-数据表约束限制1、主键约束 primary key2、唯一约束3、标识列identity(1,1)-保证实体完整性(数据不重复)4、非空约束 not null5、默认值约束 default 湖北省武汉市6、检查约束-保证字段的值是有效的值7、外键约束-保证数据的引用完整性数据表约束限制案例： 1. 在创建的时候添加约束drop table inf3;create table city -主键表(c

10、id int primary key identity(1,1),-城市编号 -主键cname varchar(20) not null-城市名称)insert into city values(武汉);insert into city values(襄樊);insert into city values(黄石);create table stu -外键表(id int primary key identity(1,1), -主键，标识列name varchar(20) not null,-非空age int check (age between 1 and 120),-取值在-120pwd

11、varchar(20) check (len(pwd)2 and len(pwd)2 and len(name)2 and len(name)=60;-任务：统计班考试合格的男生总人数-任务：统计班考试成绩最高的男生成绩select count(*) as 总人数 from info;select count(*) 总人数 from info;select id 编号,name 姓名,gender 性别 from info;-select count(*) 总人数,min(score) 最小值,max(score) 最大值, sum(score) 总分数,avg(score) 平均分 from

12、 info;select * from info;排序查询 order by -asc 升序 -desc降序语法: select * from 表 order by 字段 排序方式select * from info order by score desc;-排序显示-查询s1t80班考试合格的男生，降序排列select * from info where class=s1t80 and gender=男 and score=60order by score desc;create table exam(id int primary key identity(1,1),name varchar

13、(20),math int, -数学成绩chinese int,-语文成绩english int -英语成绩)insert into exam values(张三,95,90,92);insert into exam values(李四,85,95,97);insert into exam values(王五,91,87,52);insert into exam values(赵六,92,84,99);insert into exam values(田七,77,100,100);select * from 表 order by 字段 排序方式,字段 排序方式select id,name,mat

14、h,chinese,english,(math+chinese+english) 总成绩 from examorder by (math+chinese+english) desc,chinese desc;聚合函数: min,max,count,sum,avg排序函数: order by desc降序 desc升序 默认生序(where只能出现在order by之前)分组函数 group by select 分组的字段 from 表名 group by 分组的字段示例:select class,count(*) from info group by class ;-按照班级分组，查看有哪几个

15、班级-如果语句中使用到了分组函数，就只能查询两种数据:1、用于分组的字段2、聚合函数/先分组，再聚合select class,count(*) from info group by class ;-先按照班级分组，然后统计每一个班的人员-查询每一个班，考试合格的总人数(先筛选考试合格的学生，再分组,再统计人数-按照每一个班考试合格的人数，降序排序)select class,avg(score) 分数 from info where score=60 group by classhaving avg(score)77order by avg(score) desc;-统计每一个班考试合格的学生的

16、平均分，按照平均分降序排列-只显示平均成绩在分-以上的-where 不能出现在group by 之后如果要在分组以后，设置查询条件，只能用having, 这个关键字不能单独用，它必须结合group by 一起使用where 与 having 都可以设置查询条件，区别再于where 只能出现在group by 之前having 只能出现在group by 之后select .表名 where .group by .having.order by-select * from info order by score desc;-top 前几条数据select top 5 * from info or

17、der by score asc;-查询所有s1t81学生和s1t82select * from info where class=s1t81 or class=s1t82-in 用于指定一个查询范围，如果值在这个范围中，就满足条件select * from info where class in (s1t81,s1t82);-not in 用于指定一个查询范围，如果值不在这个范围中，就满足条件select * from info where class not in (s1t81,s1t82);create table inf5(id int primary key identity(1,1

18、),name varchar(20),bir datetime)insert into inf5 values(jack,2011-12-01); -年份-月份-日期insert into inf5 values(andy,getDate();insert into inf5 values(chris,dateadd(mm,3,getDate();select * from inf5;第四章 SQLServer数据库的函数1、日期函数2、字符串函数3、数学函数4、系统函数-日期函数:1、getDate()-得到当前的系统时间select getDate();2、dateAdd(时间符号,值,时

19、间) - 在指定时间上的指定时间部份，加上一个值，形成一个新的时间-yyyy年,mm月,dd日期hh小时mi 分钟 ss秒 dw:今天是一个星期的第几天select dateadd(yyyy,3,2010-02-03);select dateadd(mm,8,getDate();select name,dateadd(yyyy,-10,bir) from info2;3、datediff(时间格式,开始时间，结束时间);-计算两个时间差(单位由时间格式决定)select datediff(yyyy,1979-09-12,getDate();-计算时间差select name,bir,dated

20、iff(yyyy,bir,getDate() 年龄 from info2 where datediff(yyyy,bir,getDate()=20 order by bir asc;4、datePart();-取得日期指定部份的值-取值以西方算法select datepart(dw,getDate();5、datename();-取得日期指定部份的值select datename(dw,getDate();字符串函数:len() 用于计算字符串的长度select len(abcd);select name from info;select name,len(name) 姓名长度 from in

21、fo;-查询所有姓名为个字的学生select * from info where name like _;select * from info where len(name)=3;-upper() 将字符串中字母，全部转换成大写-lower() 将字符串中字母，全部转换成小写select id,name,gender,score,upper(class) 班级 from info;select lower(ABVC);-ltrim() 去掉左侧的空格-rtrim() 去掉右侧的空格select ltrim( abcd);-right(字符串，长度); 把这个字符串，从右向左截取指定长度的内容s

22、elect right(张三丰来了,2);-substring(字符串,开始的位置，指定长度) -从这个字符串开始的位置，截取指定的长度select substring(abc123,1,3);select substring4,5);create table inf3(id int primary key,name varchar(20),tel varchar(20)insert into inf3 values(1,jack;insert into inf3 values(2,bruce;insert into

23、 inf3 values(3,christina;insert into inf3 values(4,bill;insert into inf3 values(5,mariah;insert into inf3 values(6,chris;insert into inf3 values(7,lee;insert into inf3 values(8,lee;select id,name,tel,substring(tel,4,5) fro

24、m inf3;-replace(字符串，旧的内容，新的内容)-把字符串，指定的旧的内容，用的新内容替换掉select replace(张三丰来了,张三丰,李小龙);select id,name,replace(tel,substring(tel,4,5),*) from inf3;-stuff(字符串,开始位置，长度，新内容)作用相当于 substring+replace把字符串，从开始位置到指定长度的内容，用新的内容替换select stuff(tel,4,5,*) from inf3;字符串函数:len(),upper(),lower(),ltrim(),rtrim(),right(),s

25、ubstring()replace(),stuff();转换函数: convert()select * from info;语法:convert(varchar(10),数值)-将一个数值类型的数据，转换成varchar类型语法:转换时间select convert(varchar(20),时间类型的数据,120) from 表只显示年月日时分秒,不显示毫妙-select 姓名是:+name from info;select 成绩是:+ convert(varchar(10),score) from info;select convert(varchar(10),getDate(),120);

26、数学函数:rand(); 产生一个-1之间的随机数，永远不能到达select rand();-floor(浮点数); 得到一个比当前浮点数小的最大的整数select floor(22.3457);-任务，得到个-9之间的随机数-任务，得到个位的随机数select right(convert(varchar(20),rand(),4);rand(),floor(),abs()-取绝对值power(m,n)- 计算m的n次方ceiling(浮点数)-取得大于当前浮点数的最小整数round(浮点数，小数精确的位数);select round(123.4567,2);sqrt()-开方select sqrt(2);-第五章 重点: 多表联合查询多表联合查询-当多张表之间，通过一定的条件进行关联，把多张表的数据一起查询出来多表联合查询的方式分为:1、内连接2、外连接左外连接右外连接完全外连接-create table stu(sid int primary key identity(1,1), -学生编号sname varchar(20) -学生名称)create table score(id int primary key identity(1,