SQL(结构化查询语言)入门课件

1、数据库入门MySQL+SQL 第1页，共54页。MySQL简介数据库简介Oracle、 SQLServer、 DB2MySQL(Apache+Mysql+Php)SyBaseMySQL数据库的安装和配置第2页，共54页。数据库数 据 表存储过程视 图. 订单 客户 产品 产品数据库表第3页，共54页。数据表行(记录)列(字段)第4页，共54页。数据在表中的存放编号姓名年龄民族部门1王涛33汉族人事管理部2李梅27汉族人事管理部1127李梅21133王涛1部门编码民族编码年龄姓名编号回族2汉族1民族民族编码市场营销部2人事管理部1部门部门编码存在冗余为减少数据查找的麻烦，允许数据有一定的冗余第5

2、页，共54页。数据存储的完整性Roll NumberNameAddressBookTaken12Alicia Ruth12, Temple StreetAC09114Jason Darren123, Sunset Blvd.AC04315Mary Beth32, Golden AvenueAC02112Alicia Ruth12, Temple StreetAC04312Alicia Ruth12, Tmple StreetAC01115Mary Beth33, Golden AvenueAC011不同的地址!存在不正确、不准确的数据，数据库“失去了完整性”姓名第6页，共54页。数据的完整性数

3、据完整性+=可靠性准确性第7页，共54页。完整性分类数据实体完整性字段完整性引用完整性自定义完整性第8页，共54页。数据实体完整性河南新乡赵可以0010016河南新乡张丽鹃0010015江西南昌雷铜0010014湖南新田吴兰0010013山东定陶李山0010012.地址姓名学号江西南昌雷铜0010014约束方法：唯一约束、主键约束、标识列第9页，共54页。字段完整性河南新乡赵可以0010016河南新乡张丽鹃0010015江西南昌雷铜0010014湖南新田吴兰0010013山东定陶李山0010012.地址姓名学号湖北江门李亮870000000约束方法：限制数据类型、检查约束、外键约束、默认值、非

4、空约束第10页，共54页。引用完整性河南新乡赵可以0010016河南新乡张丽鹃0010015江西南昌雷铜0010014湖南新田吴兰0010013山东定陶李山0010012地址姓名学号980010021数学约束方法：外键约束科目学号分数数学001001288数学001001374语文001001267语文001001381数学001001698第11页，共54页。自定义完整性AV121322乔峰CV0016AV372133玄痛CV0015AV378291沙悟净AV0014AV378290猪悟能AV0013AV378289孙悟空AV0012.会员证用户姓名用户编号约束方法：规则、存储过程、触发器帐

5、号姓名信用.00192孙悟空700288猪悟能612333段誉890111虚竹4093000岳不群-10触发器：检查信用值第12页，共54页。完整性包括输入的类型是否正确？年龄必须是数字输入的格式是否正确？身份证号码必须是18位是否在允许的范围内？性别只能是”男”或者”女”是否存在重复输入？学员信息输入了两次是否符合其他特定要求？信誉值大于5的用户才能够加入会员列表列值要求（约束）整行要求（约束）第13页，共54页。数据库服务器、数据库和表的关系MySQL程序DB1DB2表1表3表2Client程序所谓安装数据库服务器，只是在机器上装了一个数据库管理程序，这个管理程序可以管理多个数据库，一般开

6、发人员会针对每一个应用创建一个数据库。为保存应用中的数据，一般会在数据库创建多个表，以保存程序中的数据。数据库服务器、数据库和表的关系如图所示：第14页，共54页。数据在数据库中的存储方式id=1name=“lisi”age=23User对象idnameage1lisi232wang24User表id=2name=“wang”age=24行(row)列(column)表的一行称之为一条记录表中一条记录对应一个对象的数据User对象第15页，共54页。phpMyAdminhttp:/localhost/phpMyAdmin/用户名:root密码:123第16页，共54页。创建数据库CREATE

7、DATABASE IF NOT EXISTS db_name create_specification , create_specification . create_specification: DEFAULT CHARACTER SET charset_name | DEFAULT COLLATE collation_name CHARACTER SET：指定数据库采用的字符集COLLATE：指定数据库字符集的比较方式、规则，比如排序练习：创建一个名称为mydb1的数据库。创建一个使用utf8字符集的mydb2数据库。创建一个使用gb2312字符集的mydb3数据库。第17页，共54页。查

8、看、删除数据库显示数据库语句：SHOW DATABASES显示数据库创建语句：SHOW CREATE DATABASE db_name使用数据库USE db_name数据库删除语句：DROP DATABASE IF EXISTS db_name 练习：查看当前数据库服务器中的所有数据库查看前面创建的mydb2数据库的定义信息删除前面创建的mydb1数据库第18页，共54页。修改、备份、恢复数据库数据ALTER DATABASE IF NOT EXISTS db_name alter_specification , alter_specification . alter_specificatio

9、n: DEFAULT CHARACTER SET charset_name | DEFAULT COLLATE collation_name备份数据库表中的数据(退到window命令行窗口)mysqldump -u 用户名 -p 数据库名 D:文件名.sql恢复数据库(进入mysql控制台)source D:文件名.sql练习 查看服务器中的数据库，并把其中mydb3库的字符集修改为utf8。 备份mydb3库中的数据，并恢复。第19页，共54页。创建表(基本语句)CREATE TABLE table_name(field1 datatype,field2 datatype,field3 da

10、tatype,)character set 字符集 collate 校对规则field：指定列名datatype：指定列类型注意：创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类型。例：user对象id int name stringpassword stringbirthday dateIdName Passwordbirthday注意：创建表前，要先使用use dbname语句使用库。第20页，共54页。MySQL常用数据类型分类备注和说明数据类型说明二进制数据类型存储非子符和文本的数据BLOB可用来存储图像文本数据类型字符数据包括任意字母、符号或数字字符的组合ch

11、ar固定长度的非 Unicode 字符数据varchar可变长度非 Unicode 数据text存储长文本信息日期和时间日期和时间在单引号内输入time时间date日期datetime 日期和时间数值型数据该数据仅包含数字，包括正数、负数以及浮点数intsmallint整数floatdouble浮点数货币数据类型用于财务数据decimal定点数Bit数据类型表示是/否的数据bit存储布尔数据类型第21页，共54页。PRIMARY KEY 主码约束(主键)UNIQUE唯一性约束NOT NULL非空值约束AUTO_INCREMENT用于整数列默认自增1UNSIGNED 无符号整数DEFAULT d

12、efault_value默认值约束DEFAULT cur_timestamp 创建新记录时默认保存当前时间（仅适用timestamp数据列）ON UPDATE cur_timestamp 修改记录时默认保存当前时间（仅适用timestamp数据列）CHARACTER SET name 指定字符集（仅适用字符串）常见完整性约束第22页，共54页。AUTO_INCREMENT 自动标识列，在需要产生唯一标志符号或者顺序值时候，可用此属性。值一般从1开始，每行增加1，在插入NULL到一个AUTO_INCREMENT列时，MySQL会插入一个比该列中当前最大值大1 的值，一个表中最多能有一个有此属性的

13、列。对于想使用此属性的列应该定义为NOT NULL,并定义为PRIMARY KEY 或者定义为UNIQUE键。举例：create table t(id int auto_increment not null primary key);UNSIGNED 无符号，此属性禁用负值，将数值的取值范围从零开始。第23页，共54页。NULL和NOT NULL默认为NULL，即插入值时没有在此字段插入值时自动填NULL，如果指定了NOT NULL，则必须在插入值时在此字段添入值，不允许插入NULL值。DEFAULT可以通过此属性来指定一个缺省值，即如果没有在此列添加值，那么默认添加DEFAULT后指定值。第

14、24页，共54页。修改表使用 ALTER TABLE 语句追加, 修改, 或删除列的语法.ALTER TABLE tableADD (column datatype DEFAULT expr , column datatype.);ALTER TABLE tableMODIFY (column datatype DEFAULT expr , column datatype.);ALTER TABLE tableDROP (column);修改表的名称：Rename table 原表名 to 新表名修改表的字符集：alter table student character set utf8;第2

15、5页，共54页。练习：建立一个学生信息表(user)定义列sno学号，类型为5位定长字符串，非空，主键定义列sname姓名，类型为8位定长字符串，非空定义列ssex性别，取值1或0定义列sage年龄，类型为短整型定义列sdept系名，数据类型为20位变长字符串练习 ：建立一个课程信息表（course）定义列courseid课程号，类型为整型，非空，自动增长，主键定义列cname课程名，类型为16位变长字符串，非空练习 ：建立一个学生考试成绩信息表(sc)定义列grade成绩，类型为整型要求把表字段补充完整，可以通过学号查看学生相关课程的成绩第26页，共54页。外键的方法ALTER TABLE

16、表名 ADD FOREIGN KEY (字段名) REFERENCES 表名(字段名)ALTER TABLE sc ADD FOREIGN KEY (uid) REFERENCES user(id)第27页，共54页。数据库CRUD语句Insert语句 (增加数据)Update语句 (更新数据)Delete语句 (删除数据)Select语句(查找数据)第28页，共54页。Insert语句employee.sqlINSERT INTOtable (column , column.)VALUES(value , value.);使用 INSERT 语句向表中插入数据。插入的数据应与字段的数据类型相

17、同。数据的大小应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。在values中列出的数据位置必须与被加入的列的排列位置相对应。字符和日期型数据应包含在单引号中。插入空值，不指定或insert into table value(null)第29页，共54页。Insert语句练习练习：使用insert语句向表user中插入三个学生的信息（英中文）。sno学号，类型为5位定长字符串，非空，主键sname姓名，类型为8位定长字符串，非空ssex性别，字符型 取值男或女sage年龄，类型为短整型sdept系名，数据类型为20位变长字符串第30页，共54页。Update语句U

18、PDATE tbl_name SET col_name1=expr1 , col_name2=expr2 . WHERE where_definition 使用 update语句修改表中数据。UPDATE语法可以用新值更新原有表行中的各列。SET子句指示要修改哪些列和要给予哪些值。WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。第31页，共54页。Update语句练习练习：创建的user表中修改表中的纪录。要求将所有学生系名修改为计算机工程系。将所有同学的年龄在原有基础上增加2。将张三同学的年龄减3第32页，共54页。Delete语句delete from tbl_nam

19、e WHERE where_definition 使用 delete语句删除表中数据。如果不使用where子句，将删除表中所有数据。Delete语句不能删除某一列的值（可使用update）使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，头脑中应该始终不要忘记这个潜在的问题。删除表中数据也可使用TRUNCATE TABLE 语句，它和delete有所不同。TRUNCATE(复制表结构-销毁表-重建表结构）DELETE(逐行删除记录)第33页，共54页。De

20、lete语句练习删除user表中名称姓名为zhangsan的记录。删除course表中所有记录。删除user表中名称性别为男,且年龄大于20的记录。第34页，共54页。Select语句(1)student.sqlSELECT DISTINCT *|column1, column2. column3.FROMtable;Select 指定查询哪些列的数据。column指定列名。*号代表查询所有列(开发中建议不要使用)。From指定查询哪张表。DISTINCT可选，指显示结果时，是否剔除重复数据基本select语句第35页，共54页。Select语句(1)练习：查询表中所有学生的信息。查询表中所有

21、学生的姓名和对应的年龄成绩。查询表中所有学生的性别过滤表中重复数据。第36页，共54页。Select语句()SELECT *|column1expression, column2expression，.FROMtable;在select语句中可使用表达式对查询的列进行运算在select语句中可使用as语句SELECT column as 别名 from 表名;第37页，共54页。Select语句()练习查询sc表,在所有学生分数上加4分特长分。统计所有学生所有课程的平均分。sum avg求编号1的课程的分数和使用别名表示学生分数。第38页，共54页。Select语句(3)使用where子句，进

22、行过滤查询。查询所有同学的成绩查询姓名为张三的学生成绩第39页，共54页。Select语句()在where子句中经常使用的运算符比较运算符 = = 大于、小于、大于(小于)等于、不等于BETWEEN .AND.显示在某一区间的值IN(set)显示在in列表中的值，例：in(100,200)LIKE 张pattern模糊查询IS NULL/IS NOT NULL判断是否为空逻辑运算符and多个条件同时成立or多个条件任一成立not不成立，例：where not(salary100);Like语句中，% 代表零个或多个任意字符，_ 代表一个字符，例first_name like _a%;第40页，

23、共54页。Select语句(4)查询有分数在 8090之间的同学的学号。查询所有姓李的学生信息。第41页，共54页。Select语句()SELECT column1, column2. column3.FROMtable;order by column asc|descOrder by 指定排序的列，排序的列即可是表中的列名，也可以是select 语句后指定的列名。Asc 升序、Desc 降序ORDER BY 子句应位于SELECT语句的结尾。练习：对成绩排序后输出。对学生信息按照年龄排序输出。使用order by 子句排序查询结果。第42页，共54页。合计函数count()Select co

24、unt(*)|count(列名) from tablenameWHERE where_definition 练习：统计共有多少学生？Count(列名)返回某一列，行的总数，除null外第43页，共54页。合计函数sum()Select sum(列名),sum(列名) from tablenameWHERE where_definition 练习：统计一个课程编号是1的总成绩。注意：sum仅对数值起作用，否则会报错。Sum函数返回满足where条件的行的和第44页，共54页。平均函数avg()Select sum(列名),sum(列名) from tablenameWHERE where_def

25、inition 练习：求一所有同学平均分。AVG函数返回满足where条件的一列的平均值第45页，共54页。最大最小值函数max()/min()Select max(列名)from tablenameWHERE where_definition 练习：求班级最高分和最低分。（数值范围在统计中特别有用）Max/min函数返回满足where条件的一列的最大/最小值第46页，共54页。Select语句(6)SELECT column1, column2. column3. FROMtable;group by column练习：使用group by 子句对列进行分组SELECT column1, c

26、olumn2. column3.FROMtable;group by column having .使用having 子句组过滤练习：Having和where均可实现过滤，但在having可以使用合计函数,having通常跟在group by后，它作用于组。第47页，共54页。定义表的约束(数据安全的保证)定义主键约束 primary key:不允许为空，不允许重复删除主键：alter table tablename drop primary key ;定义主键自动增长 auto_increment(MySQL特有/UUID类生成)定义唯一约束 unique定义非空约束 not null定义外

27、键约束constraint ordersid_FK foreign key(ordersid) references orders(id),第48页，共54页。表的关联设计（实体关系的体现）一对一人和身份证（外键唯一非空/主键外键合二唯一）一对多/多对一部门和员工多对多（中间表)老师和学生1号老师所对应的学员1号学员所对应的老师第49页，共54页。Tip：mysql中文乱码mysql有六处使用了字符集，分别为：client 、connection、database、results、server 、system。client是客户端使用的字符集。 connection是连接数据库的字符集设置类型

28、，如果程序没有指明连接数据库使用的字符集类型就按照服务器端默认的字符集设置。 database是数据库服务器中某个库使用的字符集设定，如果建库时没有指明，将使用服务器安装时指定的字符集设置。 results是数据库给客户端返回时使用的字符集设定，如果没有指明，使用服务器默认的字符集。 server是服务器安装时指定的默认字符集设定/utf8。 system是数据库系统使用的字符集设定。第50页，共54页。附录：时间日期相关函数参考ADDTIME (date2 ,time_interval )将time_interval加到date2CURRENT_DATE ( )当前日期CURRENT_TIM

29、E ( )当前时间CURRENT_TIMESTAMP ( )当前时间戳DATE (datetime )返回datetime的日期部分DATE_ADD (date2 , INTERVAL d_value d_type )在date2中加上日期或时间DATE_SUB (date2 , INTERVAL d_value d_type )在date2上减去一个时间DATEDIFF (date1 ,date2 )两个日期差NOW ( )当前时间YEAR|Month|DAY (datetime )年月日示例：select addtime(02:30:30,01:01:01); 注意：字符串、时间日期的引号问题 select date_add(entry_date,INTERVAL 2 year) from student;增加两年select addtime(time,1 1-1 10:09:09) from student;时间戳上增加，注意年后没有-第5