1、数据库应用（数据库应用（SQL Server）全）全册配套完整课件册配套完整课件数据库应用（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个人电脑和移动计算设备(手机、各种Pad)互补50 N
2、odes Intel Xeon E5420*2/8GB16GB/500GB1TB12 NodesE5-2630 v3*2/ 64GB/ 2*1TB IBM Power 小型机P560AIX 系统Intel Xeon E5 2620, 32GB内存, 2x NVIDIA GFORCE GTX680硬件基本知识nCPUn主板 (Main board)n内存 (Memory)n硬盘 (Hard disk)Intel Core i7 Sandy Bridge 2600K model CPUhttp:/en.wikipedia.org/wiki/Southbridge_(computing)二进制位(B
3、it)，字节(Byte)，字(Word)n位(Bit)n字节(Byte)q1 byte= 8 bit q1 KB = 1024 Byteq1 MB = 1024 KB = 1024X1024 Byteq1 GB = 1024 MB = 1024X1024X1024 Byteq1 TB = 1024 GB = 1024X1024X1024X1024 Byteq1 PB = 1024 TBn字(Word)存储容量n内存qRAM q个人电脑，2GB8GBq智能手机/平板，512MB, 1GB, 2GB, .qPC服务器，8GB 128 GB.n外存q硬盘，500GB, 1TB, 2TB.q移动存储设
4、备，U盘，移动硬盘数据传输速度nRAM q峰值传输速度(Peak transfer rate)nDDR3/1066, 8GB/SnDDR3/1333, 10GB/Snhttp:/en.wikipedia.org/wiki/DDR3_SDRAMn硬盘qSerial ATA(http:/en.wikipedia.org/wiki/SATA)nSATA revision 3.0 - 6 Gbit/s - 600 MB/sn实际读写速度: 7200转的SATA硬盘平均读写速度一般小于100MB/S，服务器使用的万转以上的SAS硬盘可达150200MB/S, 固态硬盘SSD速度约为150MB400MB/
5、SnUSB 3.0q45 Gbit/s , 500 MB/Sq实际写速度约为 510 MB/S, 读速度 1040 MB/Sqhttp:/en.wikipedia.org/wiki/Usb#USB_3.0n网络速度q百兆网络q千兆网络qInternet数据传输速度操作系统OS: Operating System操作系统(作業系統)WindowsLinuxUnixMac OS XChrome OSWindowsn从PC起家的 Microsoft Windows q从单用户到多用户q单任务到多任务qWindows PC到Windows ServerqWindows 95/98, Windows M
6、E, Windows XP, Windows Vista, Windows 7, Windows 8qWindows NT, Windows 2000, Server 2003, Server 2008, Server 2012n硬件平台qIntel, AMD 等 x86平台qWindows RT 可以支持 32-bit ARMUnixnUnix / Linuxq1960sqMIT, GE, AT&T Bell LabsqTime sharingqMainframenBSD / System VnHP-UX, Solaris, AIX, IRIXnSPARC, IA-32, x86-64, I
7、A-64, PowerPC, PA-RISC, MIPS, IBM POWERKen Thompson (sitting) and Dennis Ritchie working together at a PDP-11已经消失的Unix巨头们PDP, VAX, Alpha,VT100 terminalDECSun MicrosystemsUNIX TodaynIBM AIXnHP HP-UXBSD (Berkeley Software Distribution)nBSD Unix, Unix-like systemnFreeBSD, NetBSD and OpenBSDnOS X: Mac O
8、S X, is a series of Unix-based graphical interface operating systems developed and marketed by Apple Inc. http:/en.wikipedia.org/wiki/UnixUnix history-simple by Eraserhead1, Infinity0, Sav_vas - Levenez Unix History Diagram, Information on the history of IBMs AIX on . Licensed under CC BY-SA 3.0 via
9、 Wikimedia Commons - http:/commons.wikimedia.org/wiki/File:Unix_history-simple.svg#/media/File:Unix_history-simple.svgLinuxnLinus Benedict Torvalds 不同的Linux发行版nDebiannUbuntu/ Lubuntu / Kubuntu / MintnRed Hat / Fedora /CentOS.nSUSE / OpenSUSEUbuntunDebian-basednUnity not GNOME (Desktop environment)nM
10、ost popular desktop ?Desktop environmentsnGNOME和KDE占主导qKubuntuqUbuntu GNOMEnUnity , UbuntunLXDE 轻量级桌面, LubuntuGNOME: the GNU Network Object Model EnvironmentWindows or Linuxn操作系统的优劣与选择q技术高低和市场q历史的包袱q新的潮流和革命q王垠：漫谈Linux、Windows和Mac (http:/ 虚拟机软件 选择安装版本 安装过程 磁盘、磁盘分区 文件系统 用户和超级用户虚拟机软件 (VM: Virtual Machi
11、ne)nVMWare/ VMware Player (非商业用途免费)nOracle / Virtual Box (GPL)nMicrosoft / Virtual PC (仅限于Windows平台)nMicrosoft / Hyper-V ( Windows server 2008, Windows server 2012 )nXen, KVM. (Linux)选择安装版本n64位还是32位qIntel系列64位CPU标准 AMD64, Intel 64, IA64.q64位操作系统和32位操作系统n选择Linux的发行版qUbuntu/Lubuntu/Mint Linux qCentOSn
12、桌面、服务器虚拟机设置n1 GB内存n桥接网络与NATn安装过程中需要网络安装过程n选择语言（建议选择英语）n磁盘分区n网络设置磁盘分区n磁盘和磁盘分区上图显示两个物理磁盘，第一个磁盘有3个分区，第二个磁盘2个分区Linux 文件系统格式nWindows 文件系统格式 NTFSnLinux文件系统格式 Ext 2, Ext 3, Ext 4用户和超级用户nWindows q普通用户和管理员q操作权限设定q文件的权限 nLinux q普通用户q超级用户rootLinux 基本操作n用户/终端/登录nShellnLinux 命令用户/终端/登录多用户环境和主机不同，终端仅有输入(命令)和输出(显示
13、)功能，真正的程序在主机上运行终端和主机的连接方式多种多样。串口、电话线(通过Modem)、网络终端模拟软件ShellnUnix shell也叫做命令行界面，它是Unix操作系统下传统的用户和计算机的交互界面。用户直接输入命令来执行各种各样的任务。nShell of Windows, cmd.exen在Windows操作系统下，可能有些用户从来都不会直接的使用shell，然而在Unix系列操作系统下，shell仍然是控制系统启动、X Window启动和很多其他实用工具的脚本解释程序。nBourne shell (bsh) Steve Bourne, while at Bell Labs. Fi
14、rst distributed with Version 7 Unix, circa 1978, and enhanced over the years.nC shell (csh): Bill Joy, University of California, Berkeley. First distributed with BSD in 1978SSH (Secure Shell)nSSH为一项创建在应用层和传输层基础上的安全协议，为计算机上的Shell（壳层）提供安全的传输和使用环境。n传统的网络服务程序，如rsh、FTP、POP和Telnet其本质上都是不安全的；因为它们在网络上用明文传送数
15、据、用户帐号和用户口令，很容易受到中间人（man-in-the-middle）攻击方式的攻击。就是存在另一个人或者一台机器冒充真正的服务器接收用户传给服务器的数据，然后再冒充用户把数据传给真正的服务器。n而SSH是目前较可靠，专为远程登录会话和其他网络服务提供安全性的协议。利用SSH协议可以有效防止远程管理过程中的信息泄露问题。通过SSH可以对所有传输的数据进行加密，也能够防止DNS欺骗和IP欺骗。nSSH之另一项优点为其传输的数据可以是经过压缩的，所以可以加快传输的速度。SSH有很多功能，它既可以代替Telnet，又可以为FTP、POP、甚至为PPP提供一个安全的“通道”。Linux 命令n
16、系统信息相关n用户信息n文件管理n网络相关n进程管理系统信息nhostname 主机名nuname Linux内核版本硬件信息-CPUcat /proc/cpuinfo硬件信息-CPU内存信息free mByte, KB, MB, GB, TB内存信息cat /proc/meminfo文件系统信息-分区sda, sdb, sdc . 物理硬盘sda1 sda硬盘的第一个分区sda3 sda硬盘的第三个分区谁在用这个主机nwho和w文件n文件的安全性(用户和组)n文件的属性n文本文件、二进制文件n可执行文件中的脚本和程序Linux的用户管理和文件系统文件的基本操作文件和目录目录的操作文件的操作用
17、户(user)的管理增加和删除用户组(group)Linux的文件系统n目录和文件n文件管理器Lubuntu/PCManFMLinux Mint/Nemo目录操作命令n当前目录 (pwd)n目录的表示法 ( .和.)n主目录n改变当前目录 (cd)q绝对路径和相对路径操作练习cd cd .cd /cd homecd studentcd Desktop目录操作命令n创建目录 (mkdir)n删除目录 (rmdir)文件系统和文件n列出文件名的命令 lsls -lll如何创建文件ntouch 创建一个空的文件n通过重定向创建文件 qecho 命令qecho hello world hello.tx
18、tncat 打印文本文件的内容nhead , tail文件的复制n复制命令 cpq在同一目录内复制文件n cp hello.txt hello1.txtq把文件复制到另外一个目录n cp hello.txt /Desktopn cp hello.txt .文件的移动nmv 命令qmv hello.txt .qmv hello.txt qmv /home/student/hello.txt .文件的删除nrm 命令qrm hello.txt 删除当前目录下的hello.txtqrm ./hello.txt 删除上一级目录的hello.txt通配符 *nh* (h开头的文件或者目录)nh*txt
19、(h开头且以txt结尾的文件或者目录)ls h* 列出以h*开头的文件或者目录文件的安全性(用户和组) 文件和目录属于某个用户和组文件的权限和属性nrwx, 421qr: readqw: writeqx: execute nchmod命令chmodnchmod 660 hello.txtnchmod ug+wr hello.txtnchmod o-wr hello.txt用户(user)nLinux是多用户系统n最高权限用户rootn/etc/passwd文件nadduser 命令 图形化的管理工具 users-admin System tools, Users and Groups修改密码n
20、密码怎么存储q关于Hash算法,md5,sha,sha1npasswd文件和shadown修改密码的命令passwdsu /sudonsu切换到其他帐号nsudo 以root帐号的权限执行命令qsudo adduserqsudo apt-get installProgrammingnCPU、程序、操作系统nCPU的指令集n计算机语言、源程序和可执行程序n编译和连接CPU、程序、操作系统n程序需要在CPU上运行。CPU执行的指令各有规范，互不兼容nCPU厂商qIntel, AMD 个人电脑qIBM, HP, Fujitsu等大型机qARM架构的CPU 平板电脑，手机等Intel 的x86指令集程
21、序说明：MOV 存储指令ADD 加法指令AX,BX是CPU的寄存器0200是内存地址汇编语言：指令和数据 B8 01 00中断CPU执行的代码CPU执行的指令其实也是一些数字计算机语言、源程序和可执行程序n计算机语言qASM汇编语言qFORTRAN, C, BASIC, PASCAL, LISP, COBAL.qSmalltalk, C+, Java (Object Oriented)qScheme, Perl, Ruby, Python.(Dynamic Language)q.n源程序，代码，编程序n可执行程序，程序编译和连接n源程序变成可执行代码为编译q编写源程序q编译成可执行代码n连接q
22、小段程序连接成一个完整的程序q把别人做好的功能(库)连接到程序中无需编译也可运行的代码nScript(脚本)nJava和Java virtual machine(虚拟机模式，跨硬件平台)nC#, VB.NET和 .NET FrameworknDynamic Language, Pythonnsudo apt-get install pythonn大部分Linux的安装版本已经包含了Python安装PythonPythonn开源(Open Source)n动态语言(Scheme ,Perl, Ruby, Tcl)n面向对象(Object Oriented)n代码缩进取代了复杂的括号n广泛的支持库
23、安装和运行nsudo apt-get install pythonn大部分Linux的安装版本已经包含了PythonnPython运行Pythonn逐行执行方式q执行Python，进入交互执行状态，输入程序执行n编写程序q用vi 或gedit输入程序qchmod +x 设置执行属性q运行Hello, World!Print(“Hello, world”)变量和变量的类型n数值q整数q浮点数n字符用range()的循环for i in range(10): print(i)S=0for i in range(10): s=s+i+1print sSQL 数据库介绍Client/Server Mo
24、deln上世纪八十年代，个人计算机(PC)的功能日益增强，计算机网络技术成熟。n客户机（通过网络）请求服务器提供服务n服务器（通过网络）接受命令，执行所需的程序，返回结果n客户机和服务器是抽象的概念，并不特指物理的计算机。实际上，这两个角色可以运行在同一个计算机上。SQL数据库n采用C/S模型的数据库qClient 通过网络连接ServerqClient 发布命令(SQL)qServer分析SQL指令，执行指令qServer通过网络返回运行结果nServer:数据库服务器软件nClient: 各种应用程序PC数据库软件n计算机上的个人数据库软件qAccessqFoxpron个人数据库软件的局限
25、性q服务器端的缺失，性能局限性q多用户存取冲突q难以通过网络访问SQL数据库软件n商业的SQL 数据库厂商qMicrosoftqOracleqIBMn各种开源、免费的SQL数据库qMySQL, MariaDBqPostgreSQL开源的数据库软件开源的数据库软件University of California at Berkeley Computer Science Department历史和特色n1980s, The Berkeley POSTGRES Projectn1994, Postgres95, POSTGRES 4.2n1996, PostgreSQL 6.0n2014, Post
26、greSQL 9.3.4安装PostgreSQLnUbuntu上安装PostgreSQLnsudo apt-get install postgresql其他需要安装的工具n安装管理工具qsudo apt-get install pgAdmin3n安装Python数据库接口qsudo apt-get install python-psycopg2PostgreSQL服务器n启动和停止sudo /etc/init.d/postgresql start/restartsudo /etc/init.d/postgresql stop管理PostgreSQLnpostgres用户是默认的管理用户npsq
27、l是默认的管理工具q进入psql的命令npsqlnpsql mydb1q退出psqlnqn或者按ctrl+d创建database每个用户自己有个默认的database，名字和登录名一致第一步需要自己创建自己的database创建命令：createdb创建完成后，用psql进入postgres软件获得帮助输入？在冒号提示符下按q回车退出用vi的光标移动命令上下移动看帮助信息j - 下k - 上psql的命令 l 列出所有的databasedu 列出所有的角色(role)dt 列出所有的表(Tables)数据结构nDatabaseqTables表ncolumn, rowqViews视图nCREAT
28、E DATABASE.nCREATE TABLE.数据类型(Data types)n数值q整数nSMALLINT - 2 bytesnINTEGER - 4 bytesnBIGINT - 8 bytesq浮点数nREAL/FLOATnDOUBLE PRECISIONq定点数nDECIMAL数据类型(Data types)n浮点数和定点数CREATE TABLE test (a float, b float);INSERT INTO test VALUES (0.1, 0.2);SELECT * FROM test WHERE a+b=0.3;CREATE TABLE test2 (a deci
29、mal, b decimal);INSERT INTO test2 VALUES (0.1, 0.2);SELECT * FROM test2 WHERE a+b=0.3;数据类型(Data types)n字符qVARCHAR qCHARqTEXTn变长和定长字符数据库设计介绍创建表输入数据检索数据表之间的关系修改表的设计createdbn创建databasen每个用户默认的database名和其登录名一致n创建database之后，用命令psql就能连接自己的databasenl 命令显示所有的database权限问题创建的database，其他用户可以连接c s01表示连接到s01 的da
30、tabase其他用户可以在其中创建表，但默认情况下只能读取自己创建的表如果不希望其他用户访问可以设定权限 revoke all on database s01 from public取消其他账户对s01 的访问权限，这样设定之后其他用户将无法连接到s01创建表nSQL: Create Tableq举例：CREATE TABLE City ( name text, location text, population integer);创建表CityCreate Table 举例CREATE TABLE IF NOT EXISTS City ( name text UNIQUE, location
31、 text, population integer);UNIQUE: 数据不能重复，比如只能城市名字不能出现两个“广州”IF NOT EXISTS:防止建立同名的TableNULLCREATE TABLE City ( name text NOT NULL, location text, population integer);NOT NULL: 数据不能为空值NULL空值意味着没有填写任何数据，列的类型是字符时NULL和或者空格都不同，数值则不等于0或者任何数值Tony Hoare 在1965年发明了 null 引用, 并认为这是他犯下的“几十亿美元的错误”. 即便是50年后的今天, SQL
32、中的 null 值还是导致许多常见错误的罪魁祸首.输入数据SQL: INSERT INTO Insert into city (name, location, population) Values ( 广州, 中国南方，广东省,800 );Insert into city (name, population) Values ( 北京, 1000 );UpdatenSQL: Update修改数据Update city set location=中国北方 where name=北京查询数据SQL：SELECT . FROM . WHERESELECT * FROM City;SELECT * FR
33、OM City Where name=广州;SELECT * FROM City Where location is null;SELECT * FROM City Where Population=1000;删除数据nSQL: DELETE FROM . WHERE .DELETE FROM City WHERE population200; CREATE TABLEnCREATE TABLE IF NOT EXISTS Book ( Bookid SERIAL PRIMARY KEY, Title text, Author text, Publisher text, Category te
34、xt, ISBN text, Price decimal )SERIAL: 自动增加的整数, 添加数据的时候如果不赋值则自动递增Primary KeynPrimary Key: 主键qUNIQUEqNOT NULLn主键可以是名称，号码等具有实际意义的列。也可以由SERIAL 属性的列组成。创建表的练习n根据内容提示自行设计数据表，并输入一定的数据，练习插入数据国家n国名(中文名，英文名)n地理(亚洲/欧洲/美洲，面积)n气候n人口城市(City)n名称, 别称, 译名n地理(面积,地貌,气候)n人口n语言n文化(曲艺戏剧,饮食,传统工艺)n旅游图书n图书名称n作者n出版商/社n出版日期n页数
35、n字数n价格n分类（教材，小说.）n学科（物理，化学，文学)n编码数据之间的关系n从属关系。例如城市属于国家，或者每个国家有多个城市。国家和城市之间还有可能有省。n在表和表之前表现数据的关系q在City表中增加一列Country以记录Country的信息修改表的设计nSQL: Alter TableALTER TABLE City ADD Country text;为City增加一个新的列CountryALTER TABLE City DROP Country 删除City的列Country 复杂的关系n城市和国家的关系比较简单，虽然一个国家可以拥有多个城市，但一个城市只能属于某一个国家。n有
36、些数据关系比较复杂。例如书和作者的关系。一本书可以有多个作者，而每个作者也可以有多部作品，可能是单一作者也可能是多个作者合作复杂关系的表达n复杂关系的表达根据需要可能有多种方式，请设计一个模型以表达类似书书和作者作者的数据关系n要求q找到某个特定作者作者的所有的书书q找到某一本书书的所有作者作者q找到某几个作者作者 合作的书数学操作符和函数+, -, *, / :加减乘除除法计算注意列的类型如果是整数则结果也是整数% 计算余数, 例如 5 % 4 =1abs() 绝对值round() , round(42.4)=42数学操作符和函数练习:select 100 / 3;select 100 /
37、3. ;select 5 % 4;select round(3.14);select round(3.14,1);select round(3.16, 1);字符操作符和函数| (两条竖线): 表示连接两个字符例如: Post | greSQL= PostgreSQL也可以将字符和其他类型的数据连接起来例如: Post | 42= Post42字符操作符和函数lower() 转为小写upper() 转为大写length()/char_length() 字符个数octet_length() 字符所占字节数(bytes)strpos()查找功能, 例如:strpos(university, ver
38、), 表示在univeristy中找ver, 返回3,表示ver在university的起始位置为3字符操作符和函数Substr()substr(south, 2,3)表示从south的第2个字符开始取3个字符(out)Trim(), LTrim(), RTrim()Trim(abcd )去掉字符串右边的空格Trim(abcdxxxx,x)去掉字符串右边的xreplace(abc#def,#,|) 把#替换成|字符操作符和函数练习:select Post | greSQL;select 100+50= | 100+50;select today is | current_date;select
39、 lower(HELLO);select char_length(Hello);select 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 (模式)% (百分号)匹配任意个字符_ (
40、下划线)匹配一个字符例如:abc LIKE abc trueabc LIKE a% trueabc LIKE _b_ trueabc LIKE c false日期和时间n当前日期current_daten当前时间current_timennow()nselect date_part(year,now();nselect date_part(month,now();nselect date_part(day,now();表的连接和视图 两个表间连接的举例 将连接表的数据存入另一个表（连接关系的保存） 超过两个表的连接 数组ARRAY 视图的创建Book,Author,Auth_BookBook：
41、select bookid, title, 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
42、auth_id, bookid 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_Bookn三个表的连接select book.bookid, book.title, author.auth_name, book.publisher from book inner join auth_book on (
43、book.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.利用三个表建立连接，并进行查询ARRAYn数组的定义举例：一个整数的数组create table int_array1 (a1 int); 表int_array1， 列a1是整数的数组insert into int_array1 values (1,2,3);in
44、sert 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的行nANY select
45、 * from city where Shanghai= any (cities);含有Shanghai这个元素的行ARRAY练习1.建立Country表，其中包含两列CountryName Text, Cities Text2.输入数据，例如:China,“Beijing”,”Shanghai”,”Guangzhou”USA,“New York”,” Washington DC”,”Chicago”3.检索数据“Guangzhou”=ANY (Cities)“Chicago”=ANY (Cities)“New York”=Cities1SELECT查询创建ARRAYnselect array
46、 (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返回的数据，每本书的每个作者都形成一行用Array返回的数据select book.bookid, boo
47、k.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 Authorsfrom book;练习1.用SELECT 建立ARRAYSELECT BOOKID FROM BOOK;SELECT ARRAY (SELECT BOOKID FROM BOOK);SELECT ARRAY (SELECT TITLE FROM BOOK);SELECT ARRAY
48、(SELECT LEFT(TITLE,4) FROM BOOK);2.比较三个表的连接返回的数据和用ARRAY查询返回的数据视图(VIEW)的创建n视图是保存的查询，优点在于1.SQL语法事先检查和优化2.省去输入长串SQL的麻烦3.数据结构更加清晰nCREATE VIEWCreate ViewCREATE VIEW v_BOOK_AUTHOR ASselect book.bookid, book.title, author.auth_name, book.publisher from book 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和dvndt 列出tablendv列出view练习n创建v_Book_Author视图n创建利用ARRAY的视图v_Book_Authorsn利用创建的视图进行查询练习q找出F. Albert Cotton为作者的书q找出书名含有Inorganic 的书
