数据库应用（SQL Server）全册配套最完整精品课件3.ppt

1、数据库应用（数据库应用（SQL Server）全）全 册配套最完整精品课件册配套最完整精品课件3 数据库应用（SQL Server） 关于本课程 n历史 n课程内容 q基本概念 q操作系统(Linux) q数据库(SQL Server, MySQL/MariaDB, PostgreSQL) q应用程序(Visual Basic, Python) n讲授和上机练习 n作业和成绩 基本知识 n计算机的历史 q主机系统 (Mainframe in wikipedia) q个人电脑 (Personal Computer in wikipedia) n目前的潮流 q云计算、计算机集群取代主机系统 q个人

2、电脑和移动计算设备(手机、各种Pad)互补 50 Nodes Intel Xeon E5420*2/8GB16GB/500GB1TB 12 Nodes E5-2630 v3*2/ 64GB/ 2*1TB IBM Power 小型机 P560 AIX 系统 Intel Xeon E5 2620, 32GB内存, 2x NVIDIA GFORCE GTX680 硬件基本知识 nCPU n主板 (Main board) n内存 (Memory) n硬盘 (Hard disk) Intel Core i7 Sandy Bridge 2600K model CPU http:/en.wikipedia.

3、org/wiki/Southbridge_(computing) 二进制位(Bit)，字节(Byte)，字(Word) n位(Bit) n字节(Byte) q1 byte= 8 bit q1 KB = 1024 Byte q1 MB = 1024 KB = 1024X1024 Byte q1 GB = 1024 MB = 1024X1024X1024 Byte q1 TB = 1024 GB = 1024X1024X1024X1024 Byte q1 PB = 1024 TB n字(Word) 存储容量 n内存 qRAM q个人电脑，2GB8GB q智能手机/平板，512MB, 1GB, 2G

4、B, . qPC服务器，8GB 128 GB. n外存 q硬盘，500GB, 1TB, 2TB. q移动存储设备，U盘，移动硬盘 数据传输速度 nRAM q峰值传输速度(Peak transfer rate) nDDR3/1066, 8GB/S nDDR3/1333, 10GB/S nhttp:/en.wikipedia.org/wiki/DDR3_SDRAM n硬盘 qSerial ATA(http:/en.wikipedia.org/wiki/SATA) nSATA revision 3.0 - 6 Gbit/s - 600 MB/s n实际读写速度: 7200转的SATA硬盘平均读写速度

5、一般小于 100MB/S，服务器使用的万转以上的SAS硬盘可达 150200MB/S, 固态硬盘SSD速度约为150MB400MB/S nUSB 3.0 q45 Gbit/s , 500 MB/S q实际写速度约为 510 MB/S, 读速度 1040 MB/S qhttp:/en.wikipedia.org/wiki/Usb#USB_3.0 n网络速度 q百兆网络 q千兆网络 qInternet 数据传输速度 操作系统 OS: Operating System 操作系统(作業系統) Windows Linux Unix Mac OS X Chrome OS Windows n从PC起家的 M

6、icrosoft Windows q从单用户到多用户 q单任务到多任务 qWindows PC到Windows Server qWindows 95/98, Windows ME, Windows XP, Windows Vista, Windows 7, Windows 8 qWindows NT, Windows 2000, Server 2003, Server 2008, Server 2012 n硬件平台 qIntel, AMD 等 x86平台 qWindows RT 可以支持 32-bit ARM Unix nUnix / Linux q1960s qMIT, GE, AT INS

7、ERT INTO test VALUES (0.1, 0.2); SELECT * FROM test WHERE a+b=0.3; CREATE TABLE test2 (a decimal, b decimal); INSERT INTO test2 VALUES (0.1, 0.2); SELECT * FROM test2 WHERE a+b=0.3; 数据类型(Data types) n字符 qVARCHAR qCHAR qTEXT n变长和定长字符 数据库设计介绍 创建表 输入数据 检索数据 表之间的关系 修改表的设计 createdb n创建database n每个用户默认的da

8、tabase名和其登录名一致 n创建database之后，用命令psql就能连接自己 的database nl 命令显示所有的database 权限问题 创建的database，其他用户可以连接 c s01 表示连接到s01 的database 其他用户可以在其中创建表，但默认情况下只 能读取自己创建的表 如果不希望其他用户访问可以设定权限 revoke all on database s01 from public 取消其他账户对s01 的访问权限，这样设定之后 其他用户将无法连接到s01 创建表 nSQL: Create Table q举例： CREATE TABLE City ( nam

9、e text, location text, population integer); 创建表City Create Table 举例 CREATE TABLE IF NOT EXISTS City ( name text UNIQUE, location text, population integer); UNIQUE: 数据不能重复，比如只能城市名字不 能出现两个“广州” IF NOT EXISTS:防止建立同名的Table NULL CREATE TABLE City ( name text NOT NULL, location text, population integer); N

10、OT NULL: 数据不能为空值NULL 空值意味着没有填写任何数据，列的类型是 字符时NULL和或者空格都不同，数值则不 等于0或者任何数值 Tony Hoare 在1965年发明了 null 引用, 并认为这是他犯下的“几十亿美元的错 误”. 即便是50年后的今天, SQL中的 null 值还是导致许多常见错误的罪魁祸首. 输入数据 SQL: INSERT INTO Insert into city (name, location, population) Values ( 广州, 中国南方，广东省,800 ); Insert into city (name, population) Va

11、lues ( 北京, 1000 ); Update nSQL: Update 修改数据 Update city set location=中国北方 where name=北京 查询数据 SQL：SELECT . FROM . WHERE SELECT * FROM City; SELECT * FROM City Where name=广州; SELECT * FROM City Where location is null; SELECT * FROM City Where Population=1000; 删除数据 nSQL: DELETE FROM . WHERE . DELETE FR

12、OM City WHERE population200; CREATE TABLE nCREATE TABLE IF NOT EXISTS Book ( Bookid SERIAL PRIMARY KEY, Title text, Author text, Publisher text, Category text, ISBN text, Price decimal ) SERIAL: 自动增加的整数, 添加数据的时候如果不赋值则自动递增 Primary Key nPrimary Key: 主键 qUNIQUE qNOT NULL n主键可以是名称，号码等具有实际意义的列。 也可以由SERIA

13、L 属性的列组成。 创建表的练习 n根据内容提示自行设计数据表，并输入一定的 数据，练习插入数据 国家 n国名(中文名，英文名) n地理(亚洲/欧洲/美洲，面积) n气候 n人口 城市(City) n名称, 别称, 译名 n地理(面积,地貌,气候) n人口 n语言 n文化(曲艺戏剧,饮食,传统工艺) n旅游 图书 n图书名称 n作者 n出版商/社 n出版日期 n页数 n字数 n价格 n分类（教材，小说.） n学科（物理，化学，文学) n编码 数据之间的关系 n从属关系。例如城市属于国家，或者每个国家 有多个城市。国家和城市之间还有可能有省。 n在表和表之前表现数据的关系 q在City表中增加一

14、列Country以记录Country的信息 修改表的设计 nSQL: Alter Table ALTER TABLE City ADD Country text; 为City增加一个新的列Country ALTER TABLE City DROP Country 删除City的列Country 复杂的关系 n城市和国家的关系比较简单，虽然一个国家可 以拥有多个城市，但一个城市只能属于某一个 国家。 n有些数据关系比较复杂。例如书和作者的关系。 一本书可以有多个作者，而每个作者也可以有 多部作品，可能是单一作者也可能是多个作者 合作 复杂关系的表达 n复杂关系的表达根据需要可能有多种方式，请 设

15、计一个模型以表达类似书书和作者作者的数据关系 n要求 q找到某个特定作者作者的所有的书书 q找到某一本书书的所有作者作者 q找到某几个作者作者 合作的书 数学操作符和函数 +, -, *, / :加减乘除 除法计算注意列的类型如果是整数则结果也是整数 % 计算余数, 例如 5 % 4 =1 abs() 绝对值 round() , round(42.4)=42 数学操作符和函数 练习: select 100 / 3; select 100 / 3. ; select 5 % 4; select round(3.14); select round(3.14,1); select round(3.1

16、6, 1); 字符操作符和函数 | (两条竖线): 表示连接两个字符 例如: Post | greSQL= PostgreSQL 也可以将字符和其他类型的数据连接起来 例如: Post | 42= Post42 字符操作符和函数 lower() 转为小写 upper() 转为大写 length()/char_length() 字符个数 octet_length() 字符所占字节数(bytes) strpos()查找功能, 例如: strpos(university, ver), 表示在univeristy中找ver, 返 回3,表示ver在university的起始位置为3 字符操作符和函数

17、Substr() substr(south, 2,3)表示从south的第2个字符开始 取3个字符(out) Trim(), LTrim(), RTrim() Trim(abcd )去掉字符串右边的空格 Trim(abcdxxxx,x)去掉字符串右边的x replace(abc#def,#,|) 把#替换成| 字符操作符和函数 练习: select Post | greSQL; select 100+50= | 100+50; select today is | current_date; select lower(HELLO); select char_length(Hello); sele

18、ct char_length(你好); 字符操作符和函数 select octet_length(Hello); select octet_length(你好Hello); select substr(South China, 3, 7); select substr(华南师范大学, 3); select substr(华南师范大学, 3, 2); select replace(abc|def,|,#); select md5(password); Like 表达式 nPattern (模式) % (百分号)匹配任意个字符 _ (下划线)匹配一个字符 例如: abc LIKE abc true

19、 abc LIKE a% true abc LIKE _b_ true abc LIKE c false 日期和时间 n当前日期current_date n当前时间current_time nnow() nselect date_part(year,now(); nselect date_part(month,now(); nselect date_part(day,now(); 表的连接和视图 两个表间连接的举例 将连接表的数据存入另一个表（连接关 系的保存） 超过两个表的连接 数组ARRAY 视图的创建 Book,Author,Auth_Book Book：select bookid, t

20、itle, authors from book order by bookid; author：select auth_id, auth_name from author; Book和Author的连接 select auth_id, auth_name, bookid, title from book inner join author on book.authors like % | author.auth_name | %; 将连接表的数据存入另一个表 n连接关系的保存 insert into auth_book (auth_id, book_id) select auth_id, bo

21、okid from book inner join author on book.authors like % | author.auth_name | %; n连接关系保存后，book表和author表的关系就 保存于auth_book表。 n连接关系保存到auth_book表之后，book表的 authors列还需要么 Book, Author, Auth_Book n三个表的连接 select book.bookid, book.title, author.auth_name, book.publisher from book inner join auth_book on (book.

22、bookid=auth_book.book_id) inner join author on (auth_book.auth_id=author.auth_id); 练习 1.完成上次课程的三个表book, author, auth_book并输入数据 2.通过连接关系将book, author的关系保存到 auth_book表 3.利用三个表建立连接，并进行查询 ARRAY n数组的定义 举例：一个整数的数组 create table int_array1 (a1 int); 表int_array1， 列a1是整数的数组 insert into int_array1 values (1,2,

23、3); insert into int_array1 values (100,200); insert into int_array1 values (13,15,17,19); 字符数组 create table city ( cities text ); insert into city values (Beijing,Shanghai,Guangzhou); insert into city values (New York,Atlanta); 数组的检索 n数组元素的检索 select * from city where cities2=Shanghai; 第二个元素为Shanghai

24、的行 nANY select * from city where Shanghai= any (cities); 含有Shanghai这个元素的行 ARRAY练习 1.建立Country表，其中包含两列CountryName Text, Cities Text 2.输入数据，例如: China,“Beijing”,”Shanghai”,”Guangzhou” USA,“New York”,” Washington DC”,”Chicago” 3.检索数据 “Guangzhou”=ANY (Cities) “Chicago”=ANY (Cities) “New York”=Cities1 SEL

25、ECT查询创建ARRAY nselect array (select bookid from book); nselect array (select auth_name from author); 将ARRAY应用到更复杂的查询中 select book.bookid, book.title, ARRAY(select auth_id from auth_book where auth_book.book_id=book.bookid) as author_id, book.publisher from book; 三个表的连接 没有用Array返回的数据，每本书的每个作者都形成一行 用Ar

26、ray返回的数据 select book.bookid, book.title, ARRAY (select auth_name from author where author.auth_id = ANY (ARRAY (select auth_id from auth_book where auth_book.book_id=book.bookid) AS Authors from book; 练习 1.用SELECT 建立ARRAY SELECT BOOKID FROM BOOK; SELECT ARRAY (SELECT BOOKID FROM BOOK); SELECT ARRAY

27、(SELECT TITLE FROM BOOK); SELECT ARRAY (SELECT LEFT(TITLE,4) FROM BOOK); 2.比较三个表的连接返回的数据和用ARRAY查 询返回的数据 视图(VIEW)的创建 n视图是保存的查询，优点在于 1.SQL语法事先检查和优化 2.省去输入长串SQL的麻烦 3.数据结构更加清晰 nCREATE VIEW Create View CREATE VIEW v_BOOK_AUTHOR AS select book.bookid, book.title, author.auth_name, book.publisher from book

28、 inner join auth_book on (book.bookid=auth_book.book_id) inner join author on (auth_book.auth_id=author.auth_id); 视图的使用 n视图创建完成后，查询方式和Table一致 Select * from v_BOOK_AUTHOR; Select * from v_BOOK_AUTHOR WHERE BOOKID=1; dt和dv ndt 列出table ndv列出view 练习 n创建v_Book_Author视图 n创建利用ARRAY的视图v_Book_Authors n利用创建的视

29、图进行查询练习 q找出F. Albert Cotton为作者的书 q找出书名含有Inorganic 的书 Linux的文本编辑 vi, vim Bill Joy 几个最基本的vi命令 n光标移动命令 h左，l右，k上，j下 w右移一个字word b左移一个字 n插入字符，i n删除命令d dd 删除整行 dh, dl, dk, dj,dw,db 3dh nu 撤销 几个最基本的vi命令 :q 不保存退出 :x 保存退出 :w 保存 几个最基本的vi命令 Leafpad, gedit . cp /home/pengbin/books2.sql . sed -i s/pengbin/s01/g books2.sql psql -f books2.sql