数据库原理与开发.docx

1、数据库原理与开发1: 数据模型 = 信息结构 + 数据2 数据结构，数据操作（操作规则）3 数据库系统作用：组织存取，定义数据结构，增删改，4 drop删除 alter 修改 index索引5 select （目标，多个可用逗号并列。或者用*表示全部）。6 distinct 用于选取唯一不同的值。是列名称的修饰符。 Select distinct company from order 结果会自动去掉重复的后面可以再加上where 列 运算符 值Select distinct company from order where city = beijingSELECT * FROM Persons

2、 WHERE firstname=Thomas OR lastname=Carter多个条件并列方式对于文本值用单引号围起来数值的话不要做处理操作符描述=等于不等于大于=大于等于=小于等于BETWEEN在某个范围内LIKE搜索某种模式注释：在某些版本的 SQL 中，操作符 可以写为 !=。7 SELECT Company, OrderNumber FROM Orders ORDER BY Company 用于默认的生序排序多个条件用逗号并列，条件尾部加上desc为降序排列 加asc为升序排列8 INSERT INTO Persons (LastName, Address) VALUES (Wi

3、lson, Champs-Elysees）列名称表示当信息不全的时候插入一部分数据不加列名称表示插入整行9 UPDATE Person SET FirstName = Fred WHERE LastName = Wilson 定点更新数据。同样更新内容多个时用逗号并列10 DELETE FROM Person WHERE LastName = Wilson 删除整行也可直接delete * from 表名称。表示删除所有行但是表的结构还在11 SELECT TOP number|percent column_name(s)（条数或者百分率）FROM table_name 选择上限12 SELE

4、CT * FROM PersonsWHERE City LIKE N% 主要是通配符的用法前后均可通配。另外LIKE 的否定形式 NOT LIKE 附：通配符大全 通配符描述%替代一个或多个字符_仅替代一个字符charlist字符列中的任何单一字符charlist或者!charlist不在字符列中的任何单一字符13 SELECT * FROM PersonsWHERE LastName IN (Adams,Carter）在一定的范围内选择对象另外between and也可实现类似功能。但是一定要搞清楚所用编译器对于本条语法的处理方式主要是对于首尾的包含问题14 别名问题SELECT colum

5、n_name(s)FROM table_nameAS alias_name表的别名SELECT column_name AS alias_nameFROM table_name列的别名SELECT po.OrderID, p.LastName, p.FirstNameFROM Persons AS p, Product_Orders AS poWHERE p.LastName=Adams AND p.FirstName=John别名的话并不是必须的因为默认的话搜索结果是不变名称的。（不用的话直接省略就好）并列的话AND还是逗点区分15 通过key（键值）绑定两个表就是在表一中的一套键值在表二中

6、进行数据绑定，然后在两个表中按照键值进行搜索SELECT Persons.LastName, Persons.FirstName, Orders.OrderNoFROM Persons, OrdersWHERE Persons.Id_P = Orders.Id_P（一套两份键值）16 表连接 SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo（连接项）FROM PersonsINNER JOIN OrdersON Persons.Id_P = Orders.Id_P（连接条件）ORDER BY Persons.LastName

7、其他连接选项 JOIN: 如果表中有至少一个匹配，则返回行 同JOIN ON只要有至少一个匹配就返回可以返回多个但是没有的话就不会再返回 LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行 RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行（区别上表中至少一个） FULL JOIN: 只要其中一个表中存在匹配，就返回行（只要在任意一个表中存在该id就返回这一行） 17 表的结合，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL SELECT column_name(s) FROM table_name1 UNION ALL SELECT c

8、olumn_name(s) FROM table_name2 18 SELECT LastName,Firstname INTO Persons_backup FROM Persons WHERE City=Beijing充分表现出语法的扩展性。 INNER JOIN OrdersON Persons.Id_P=Orders.Id_p也可以加上查出新表然后备份19 创建数据库CREATE TABLE 表名称(列名称1 数据类型,列名称2 数据类型,列名称3 数据类型,.)数据类型描述 integer(size) int(size) smallint(size) tinyint(size)仅容纳

9、整数。在括号内规定数字的最大位数。 decimal(size,d) numeric(size,d)容纳带有小数的数字。size 规定数字的最大位数。d 规定小数点右侧的最大位数。char(size)容纳固定长度的字符串（可容纳字母、数字以及特殊字符）。在括号中规定字符串的长度。varchar(size)容纳可变长度的字符串（可容纳字母、数字以及特殊的字符）。在括号中规定字符串的最大长度。date(yyyymmdd)容纳日期。CREATE TABLE Persons(Id_P int,LastName varchar(255), NOT NULL,作为修饰符表明接受限制FirstName var

10、char(255),Address varchar(255),City varchar(255)注意：创建表时MySQL一般把额外属性加在表的最下方（另开一行加UNIQUE （lastname） 或者primary key（lastname）其他类型数据库直接在列后面加上额外属性（lastname varchar（255）UNIQUE Primary key）1 Unique约束：如果需要命名 UNIQUE 约束，以及为多个列定义 UNIQUE 约束，请使用下面的 SQL 语法：CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName) 当表已被创建时，如需在

11、P_Id 列创建 UNIQUE 约束，请使用下列 SQL：MySQL / SQL Server / Oracle / MS Access:ALTER TABLE Persons ADD UNIQUE (P_Id)多个的话逗号并列前面类似的加上？为什么？CONSTRAINT uc_PersonID（命名Unique约束在撤销的时候用）撤销 UNIQUE 约束MySQL:ALTER TABLE PersonsDROP INDEX uc_PersonIDSQL Server / Oracle / MS Access:ALTER TABLE PersonsDROP CONSTRAINT uc_Pers

12、onID2 ：Primary Key 属性用法同Unique（数据库之间差异性依然存在）3 ：Foreign Key 用法（均为在Order中定义person的一个属性为外键）MySQL：FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)定义属性在声明之后SQL Server / Oracle / MS Access:Id_P int FOREIGN KEY REFERENCES Persons(Id_P)定义属性在声明的时候如果在 Orders 表已存在的情况下为 Id_P 列创建 FOREIGN KEY 约束，请使用下面的 SQL：MySQL / SQL

13、 Server / Oracle / MS Access:ALTER TABLE OrdersADD FOREIGN KEY (Id_P)REFERENCES Persons(Id_P)如果需要命名 FOREIGN KEY 约束，以及为多个列定义 FOREIGN KEY 约束，请使用下面的 SQL 语法：MySQL / SQL Server / Oracle / MS Access:ALTER TABLE OrdersADD CONSTRAINT fk_PerOrdersFOREIGN KEY (Id_P)REFERENCES Persons(Id_P)撤销的话和其他一样撤销掉定义的别名MyS

14、QL:ALTER TABLE OrdersDROP FOREIGN KEY fk_PerOrdersSQL Server / Oracle / MS Access:ALTER TABLE OrdersDROP CONSTRAINT fk_PerOrders5： check属性限定值的取值范围用法和其他一样，是一个外加属性CONSTRAINT chk_Person CHECK (Id_P0 AND City=Sandnes)6：default约束创建表的时候可以直接加上City varchar(255) DEFAULT Sandnes 如果在表已存在的情况下为 City 列创建 DEFAULT

15、约束，请使用下面的 SQL：MySQL:ALTER TABLE PersonsALTER City SET DEFAULT SANDNESSQL Server / Oracle / MS Access:ALTER TABLE PersonsALTER COLUMN City SET DEFAULT SANDNES撤销 DEFAULT 约束如需撤销 DEFAULT 约束，请使用下面的 SQL：MySQL:ALTER TABLE PersonsALTER City DROP DEFAULTSQL Server / Oracle / MS Access:ALTER TABLE PersonsALTE

16、R COLUMN City DROP DEFAULT7 ：创建Index索引CREATE INDEX 实例本例会创建一个简单的索引，名为 PersonIndex，在 Person 表的 LastName 列：CREATE INDEX PersonIndexON Person (LastName) 如果您希望以降序索引某个列中的值，您可以在列名称之后添加保留字 DESC：CREATE INDEX PersonIndexON Person (LastName DESC) 假如您希望索引不止一个列，您可以在括号中列出这些列的名称，用逗号隔开：CREATE INDEX PersonIndexON Pe

17、rson (LastName, FirstName)8 ： 系列的撤销用于 MySQL 的语法:撤销索引ALTER TABLE table_name DROP INDEX index_nameSQL DROP TABLE 语句DROP TABLE 语句用于删除表（表的结构、属性以及索引也会被删除）：DROP TABLE 表名称SQL DROP DATABASE 语句DROP DATABASE 语句用于删除数据库：DROP DATABASE 数据库名称SQL TRUNCATE TABLE 语句如果我们仅仅需要除去表内的数据，但并不删除表本身，那么我们该如何做呢？请使用 TRUNCATE TABL

18、E 命令（仅仅删除表格中的数据）：TRUNCATE TABLE 表名称8： 修改表ALTER TABLE table_nameADD column_name datatype添加列（类似于修改列的类型ALTER COLUMN column_name datatype（ALTER COLUMN Birthday year把类型便成年）删除列类似（DROP COLUMN Birthday）9 ：自动插入主键的键值AUTO INCREMENT 字段我们通常希望在每次插入新纪录时，自动地创建主键字段的值。我们可以在表中创建一个 auto-increment 字段。用于 MySQL 的语法下列 SQL

19、语句把 Persons 表中的 P_Id 列定义为 auto-increment 主键：CREATE TABLE Persons(P_Id int NOT NULL AUTO_INCREMENT,（额外属性直接加在列的后面）LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255),PRIMARY KEY (P_Id)MySQL 使用 AUTO_INCREMENT 关键字来执行 auto-increment 任务。默认地，AUTO_INCREMENT 的开始值是 1，每

20、条新纪录递增 1。要让 AUTO_INCREMENT 序列以其他的值起始，请使用下列 SQL 语法：ALTER TABLE Persons AUTO_INCREMENT=100要在 Persons 表中插入新纪录，我们不必为 P_Id 列规定值（会自动添加一个唯一的值）：INSERT INTO Persons (FirstName,LastName)VALUES (Bill,Gates)上面的 SQL 语句会在 Persons 表中插入一条新纪录。P_Id 会被赋予一个唯一的值。FirstName 会被设置为 Bill，LastName 列会被设置为 Gates。9 函数MySQL Date

21、函数下面的表格列出了 MySQL 中最重要的内建日期函数：函数描述NOW()返回当前的日期和时间CURDATE()返回当前的日期CURTIME()返回当前的时间DATE()提取日期或日期/时间表达式的日期部分EXTRACT()返回日期/时间按的单独部分DATE_ADD()给日期添加指定的时间间隔DATE_SUB()从日期减去指定的时间间隔DATEDIFF()返回两个日期之间的天数DATE_FORMAT()用不同的格式显示日期/时间10 函数集；我们使用如下 SQL 语句：SELECT AVG(OrderPrice) AS OrderAverage FROM Orders结果集类似这样：Orde

22、rAverage950以此求的一列的平均值SQL COUNT() 语法SQL COUNT(column_name) 语法COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）：SELECT COUNT(column_name)（加上as语句是结果出现在另外一个表中） FROM table_name（后面还可以加上条件语句）MAX函数 SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders同理类似min函数Group bySELECT Customer（结果咧）,SUM(OrderPrice)（组合列） FROM

23、 OrdersGROUP BY Customer（结果咧）后面可以加HAVING函数进行结果筛选相信大家在自己的笔记本上装的是MySQL数据库，而教学时是ORACLE下面是我网上找的一些他们的区别处。希望对同学有帮助1.自动增长的数据类型处理MYSQL有自动增长的数据类型，插入记录时不用操作此字段，会自动获得数据值。ORACLE没有自动增长的数据类型，需要建立一个自动增长的序列号，插入记录时要把序列号的下一个值赋于此字段。CREATE SEQUENCE 序列号的名称 (最好是表名+序列号标记) INCREMENT BY 1 START WITH 1 MAXVALUE 99999 CYCLE N

24、OCACHE; 其中最大的值按字段的长度来定, 如果定义的自动增长的序列号 NUMBER(6) , 最大值为999999INSERT 语句插入这个字段值为: 序列号的名称.NEXTVAL2. 单引号的处理MYSQL里可以用双引号包起字符串，ORACLE里只可以用单引号包起字符串。在插入和修改字符串前必须做单引号的替换：把所有出现的一个单引号替换成两个单引号。3. 翻页的SQL语句的处理MYSQL处理翻页的SQL语句比较简单，用LIMIT 开始位置, 记录个数；PHP里还可以用SEEK定位到结果集的位置。ORACLE处理翻页的SQL语句就比较繁琐了。每个结果集只有一个ROWNUM字段标明它的位置

25、, 并且只能用ROWNUM80。以下是经过分析后较好的两种ORACLE翻页SQL语句( ID是唯一关键字的字段名 )：语句一：SELECT ID, FIELD_NAME,. FROM TABLE_NAME WHERE ID IN ( SELECT ID FROM (SELECT ROWNUM AS NUMROW, ID FROM TABLE_NAME WHERE 条件1 ORDER BY 条件2) WHERE NUMROW 80 AND NUMROW 100 ) ORDER BY 条件3; 语句二：SELECT * FROM ( SELECT ROWNUM AS NUMROW, c.* fro

26、m (select FIELD_NAME,. FROM TABLE_NAME WHERE 条件1 ORDER BY 条件2) c) WHERE NUMROW 80 AND NUMROW 100 ) ORDER BY 条件3; 4 长字符串的处理长字符串的处理ORACLE也有它特殊的地方。INSERT和UPDATE时最大可操作的字符串长度小于等于4000个单字节, 如果要插入更长的字符串, 请考虑字段用CLOB类型，方法借用ORACLE里自带的DBMS_LOB程序包。插入修改记录前一定要做进行非空和长度判断，不能为空的字段值和超出长度字段值都应该提出警告,返回上次操作。5. 日期字段的处理MYS

27、QL日期字段分DATE和TIME两种，ORACLE日期字段只有DATE，包含年月日时分秒信息，用当前数据库的系统时间为SYSDATE, 精确到秒，或者用字符串转换成日期型函数TO_DATE(2001-08-01,YYYY-MM-DD)年-月-日 24小时:分钟:秒 的格式YYYY-MM-DD HH24:MI:SS TO_DATE()还有很多种日期格式, 可以参看ORACLE DOC.日期型字段转换成字符串函数TO_CHAR(2001-08-01,YYYY-MM-DD HH24:MI:SS)日期字段的数学运算公式有很大的不同。MYSQL找到离当前时间7天用 DATE_FIELD_NAME SUB

28、DATE（NOW（），INTERVAL 7 DAY）ORACLE找到离当前时间7天用 DATE_FIELD_NAME SYSDATE - 7; MYSQL中插入当前时间的几个函数是：NOW()函数以YYYY-MM-DD HH:MM:SS返回当前的日期时间，可以直接存到DATETIME字段中。CURDATE()以YYYY-MM-DD的格式返回今天的日期，可以直接存到DATE字段中。CURTIME()以HH:MM:SS的格式返回当前的时间，可以直接存到TIME字段中。例：insert into tablename (fieldname) values (now()而oracle中当前时间是sysdate6. 空字符的处理MYSQL的非空字段也有空的内容，ORACLE里定义了非空字段就不容许有空的内容。按MYSQL的NOT NULL来定义ORACLE表结构, 导数据的时候会产生错误。因此导数据时要对空字符进行判断，如果为NULL或空字符，需要把它改成一个空格的字符串。7. 字符串的模糊比较MYSQL里用 字段名 like %字符串%,ORACLE里也可以用 字段名 like %字符串% 但这种方法不能使用索引, 速度不快，用字符串比较函数 instr(字段名,字符串)0 会得到更精确的查找结果。8. 程序和函数里，操作数据库的工作完成后请注意结果集和指针的释放。