标识符:用于唯一标识数据库系统的事物,它使用一个对象进行标识,例如有一叫SalesPeople的表,则它的标识符就是SalesPeople,访问这个表,则
SELECT PersonFirstName
From SalesPeople;
字面值:是一个实际的值,例如找到所有Bob的人
SELECT PersonFirstName,PersonLastName
From SalesPeople;
WHERE PersonFirstName=‘Bob’;
关键字:是对数据库有意义的单词,
SELECT CarMode1 FROM Cars WHERE CarMake = ‘Ford’;
SELECT CarMode1;
FROM Cars;
WHERE CarMake = ‘Ford’;
创建一个新的数据库
CREATE DATABASE myFirstDatabase;
删除数据库
DROP DATABASE myFirstDatabase
再次创建该数据库
CREATE DATABASE myFirstDatabase
创建表的基本语法
CREATE TABLE name_of_table
(
name_of_column column_datatype
)
更改现有表中的一个列基本语法
ALTER TABLE name_of_table
ADD name_of_filed data_type
删除现有表中的一个列基本语法
ALTER TABLE name_of_table
DROP COLUMN name_of_filed
删除现有表基本语法
DROP TABLE name_of_table
第二章 输入信息
插入新数据
INSERT INTO 语句
INSERT INTO table_name (column_names) VALUES (data_values)
例如:向Category表中添加一个记录
INSERT INTO Category (CategoryId,Category) VALUES (1,’Thriller’);
或者
INSERT INTO Category VALUES (1,’Thriller’);
更新数据UPDATE table_name
SET column_name = value
WHERE condition
例如:
UPDATE MemberDetails
SET
Street = ’45 upper road’
City = ‘New Town’
WHERE MemberId = 4;
把Jakes改成Tell
UPDATE MemberDetails
SET
LastName = ’Tell’
WHERE LastName = ‘Jakes’;
逻辑运算符
AND OR
删除数据
DELETE FROM MemberDetails;
DELETE FROM MemberDetails WHERE MemberId = 3;
SELECT 语句(获得哪些列数据)
SELECT column1 , column2 , …… columnx FROM table_name
如果是获得所有列
SELECT * FROM Location ; 或者
SELECT LocationId , Street , City , State FROM Location ;
仅返回不同的行
SELECT City FROM MemberDetails;
如果想获得唯一列表
SELECT DISTINCT City FROM MemberDetails;
使用别名
SELECT LastName As Surname FROM MemberDetails;
利用WHERE子句过滤结果
SELECT FirstName + ‘ ‘ + LastName As [Full Name]
FROM MemberDetails
WHERE City = ‘Big City’;
逻辑运算符
运算符 |
圆括号() |
NOT |
AND |
ALL,ANY,BETWEEN,IN,LIKE,OR,SOME |
排序
ORDER BY … ASC;
ORDER BY … DESC;
连结列
1.MS SQL Server 和 MS Access
SELECT FirstName + ‘ ‘ + LastName FROM MemberDetails;
2.Oracle 和 IBM DB2
SELECT FirstName || ‘ ’ || LastName AS FullName FROM MemberDetails;
3.MySQL
SELECT CONCAT (MemberId,FirstName,LastName) FROM MemberDetails;
从多个表中选择数据
(交叉)
Null数据概述
IS NULL
IS NOT NULL
第四章 数据库的高级设计
第一范式
第二范式
第三范式
利用约束确保数据的有效性
NOT MULL
CREATE TABLE MyTable
(
Column1 int NOT NULL,
Column2 varchar(20),
Column3 carchar(12) NOT NULL
);
UNIQUE
CREATE TABLE MyUniqueTable
(
Column1 int ,
Column2 varchar(20) UNIQUE,
Column3 carchar(12) UNIQUE
);
CHECK
CREATE TABLE NamesAges
(
Name varchar(50),
Age int CHECK (Age >= 0)
);
PRIMUARY KEY
CREATE TABLE HolidayBookings
(
CustomerId int PRIMUARY KEY,
BookingId int,
Destination varchar(50)
);
FOREIGN KEY
ALTER TABLE name_of_table_to_add_foreign_key
ADD CONSTRAINT name_of_foreign_key
FOREIGN KEY(name_of_column_that_is_foreign_key_column)
REFERENCES name_of_table_that_is_reference(name_of_column_being_referenced)
索引 CREATE INDEX
CREATE INDEX <index_name>
ON <table_name>(<column_name>)
常用的数字函数
1.ABS(x):返回一个数值的绝对值
2.POWER(expression,power_raise_to):指数函数
3.SQET(expression_to_square_root):平方根
4.RAND()在0和1之间一个随机小数
舍入数值
1.GEILING(number_to_be_rounded):删除小数点后面的所有数值,并向上舍入到领近 的最大整数
2.FLOOK(number_to_be_rounded):与GEILING相反,向下舍入到领近的最小整数。
3.ROUND(number_to_be_rounded,number_to_be_rounded):四舍五入
字符串
SUBSTRING(string,start_character_position,length_of_string_to_obtain):获得 字符串的某部分
大小写转换
UPPER():大写
LOWER():小写
Reverse():颠倒一个字符串中字符的顺序
TRIM():删除一个字符串的开始或者末尾部分中不想要的字符
LENGTH():长度
SOUNDEX():将一个字符串转化为一种特殊的4字符编码
DIFFERENCE(some_name,comparison_name):找到相似的信息
日期函数
DAY(date)
MONTH(date)
YEAR(date)
不同数据类型的转换
SELECT A,B。A/B
FROM....
COALESCE(first_expression,second_expression...last_expression):
INSERT INTO 和SELECT语句的综合使用
INSERT INTO destination_table_name
SELECT column_list FROM
source_table_name
WHERE condition
GROUP BY
GROUP BY子句必须位于SELECT语句中的FROM子句或者WHERE子句之后
COUNT()
SELECT COUNT(*) '*是通配符
SUN(expression_to_be_added_togther)
AVG()
MAX()
MIN()
综合使用HAVING子句和GROUP BY语句
HAVING是针对每个分组的,过滤掉了不匹配条件的分组,而WHERE子句是针对每个记录的,因此它过滤了不匹配条件的记录。
SELECT City
FROM MemnerDetails
GROUP BY City
HAVING COUNT (MemberId)>=3;
联合
name_of_table_on_left INNER JOIN name_of_table_on_right
ON condition
等值联合与不等值联合
one_table INNER JOIN anther_table
ON one_table.some_field = another_table.anther_field
多个联合与多个条件
SELECT FirstName,lastname,category.category
FROM MemberDetails INNER JOIN FavCategory
ON MemberDerails.MemberId=FavCaregory.MemberId
INNER JION Category
ON FavCategory.CategoryId=Category.CategoryId
ORDER BY LastName,FirstName;
交叉联合
SELECT Category,Street
FROM Category CROSS JOIN Location
ORDER BY Street;
自联合
SELECT MD1.FirstName,MD2.LastName
FROM MemberDetails AS MD1 INNER JION MemberDetails AS MD2ON MD1.Street=MD2.Street AND MD1.ZipCode=MD2.ZipCode;
外部联合
左外部联合
SELECT column_list
FROM left_table LEFT OUTER JOIN right_table
ON condition
右外部联合
SELECT column_list
FROM left_table RIFHT OUTER JOIN right_table
ON condition
完全外部联合
SELECT column_list
FROM left_table FULL OUTER JOIN right_table
ON condition
利用UNION运算符组合结果集
SELECT......FROM......
UNIONSELECT......FROM......;
第八章 子查询
外部查询
SELECT......FROM......;
嵌套
SELECT......FROM......;
WHERE ......=(SELECT......FROM......);
SELECT列表中的子查询
SELECT......,
(SELECT......()FROM......WHERE ),
CategoryID
FROM ......;
WHERE 子句中的查询
SELECT Category , MIN (DVDPrice)
FROM Category INNER JOIN Films
ON Category.CategoryId = Films.CategoryId
GROUP BY Category;
IN 运算符
SELECT .......
FROM........
WHERE.......IN.........;
ANY 和 SOME
SELECT .......
FROM........
WHERE.......=ANY.........;
ALL
SELECT .......
FROM........
WHERE.......=ALL.........;
EXISTS
SELECT .......
FROM........
WHERE EXISTS...(SELECT*FROM...WHERE....>..);
SELECT .......
FROM........
WHERE NOT EXISTS...(SELECT*FROM...WHERE....>..);
HAVING
SELECT .......
FROM........
GROUP BY ......
HAVING AVG......>......;
INSERT
UPDAYE
DELETE FROM