分类: 数据库开发技术
2012-04-17 22:13:17
SQL可以分为两类:数据操作语言(Data Manipulation Language,DML)和数据定义语言(Data Definition Language,DDL)。
DML由查询和更新命令组成:
DDL创建和删除数据库、创建和删除表、定义索引(关键字)、指定表之间的联系、定义表之间的约束。SQL里最重要的DDL的语句有:
用SELECT进行查询时,可以会有重复的记录。使用DISTINCT语句可以消除重复数据。比如:select distinct name from persons。使用WHERE可以指定查询的目标,比如:select * from persons where age >= 18。
Operator | Description |
---|---|
= | Equal |
<> | Not equal |
> | Greater than |
< | Less than |
>= | Greater than or equal |
<= | Less than or equal |
BETWEEN | Between an inclusive range |
LIKE | Search for a pattern |
IN | To specify multiple possible values for a column |
Note: In some versions of SQL the <> operator may be written as !=
IN的语法是
BETWEEN的语法是:
在对字符串比较时,需要用单引号包围字符串常量(多数据数据库系统也接受双引号),数值不应该使用绰号。
LIKE比较时可以使用的通配符有:
Wildcard | Description |
---|---|
% | A substitute for zero or more characters |
_ | A substitute for exactly one character |
[charlist] | Any single character in charlist |
[^charlist] or [!charlist] | Any single character not in charlist |
在进行条件判断时,可以使用AND和OR来连接逻辑表达式。
可以使用ORDER BY对查询的结果进行排序,它的语法为
SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC
在mysql里,可以使用LIMIT子句来定义查询结果的最大数量。 比如:select * from persons order by age limit 1。会看到年纪最小的人。在SQL Server里等价的语句是
注意limit和top都不是SQL标准。
INSERT用来插入数据,语法为:
The second form specifies both the column names and the values to be inserted:
UPDATE更新数据,语法为:
Note: The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.
UNION ALL保留重复的记录:We can select all columns into the new table:
Or we can select only the columns we want into the new table:
约束限定可以插入表的数据的类型。它可以在表创建(CREATE TABLE)时指定,也可以在表创建手(用ALTER TABLE)指定。常用的约束有:
NOT NULL约束强制某一列不接受NULL值。比如:
UNIQUE约束保证某列没有重复记录。在mysql中可以在创建表时有两种方法指定:
在表创建后加入UNIQUE约束的方法是:
或
注意:如果该列中已经有重复的记录,那么加入约束会失败
删除UNIQUE约束:
创建PRIMARY KEY约束:
修改表来加入PRIMARY KEY约束:
删除PRIMARY KEY约束:
创建外键 FOREIGN KEY:
修改表以创建FOREIGN KEY :
删除FOREIGN KEY约束:
或
修改表以创建CHECK约束:
或
删除CHECK约束:
或
创建DEFAULT约束:
My SQL / SQL Server / Oracle / MS Access:
修改表以创建DEFAULT约束:
或
删除DEFAULT约束:
创建AUTO INCREMENT约束,它只能作用在主键上:
索引用来提高数据查找的效率,用户看不到索引的存在。注意:使用索引会使更新表的速度变慢,因为数据更新的同时还要更新索引。
在表上创建一个允许重复值的索引:
在表上创建唯一的索引:
一个表上的约束可以通过SHOW INDEX FROM tbl_name来查看。mysql上可以通过修改一个列来增加约束:
DROP TABLE 删除一张表:
DROP DATABASE 删除一个数据库:
TRUNCATE TABLE可以删除一张表的所有数据而不删除该表:
删除一列:
修改一列:
视图是基于一个SQL语句的结果集的一张虚拟表。视图的优点有:
提供了逻辑数据独立性。当数据的逻辑结构发生改变时,原有的应用程序不用修改。
简化了用户观点。用户只需用到数据库中的一部分,视图适应了用户需要。
数据的安全保护功能。针对不同用户定义不同视图。
创建视图:
MySQL的日期函数:
Function | Description |
---|---|
Returns the current date and time | |
CURDATE() | Returns the current date |
CURTIME() | Returns the current time |
Extracts the date part of a date or date/time expression | |
Returns a single part of a date/time | |
Adds a specified time interval to a date | |
Subtracts a specified time interval from a date | |
Returns the number of days between two dates | |
Displays date/time data in different formats |
IS NOT NULL 操作符判断非空值:
MySQL没有ISNULL函数,所以它使用IFNULL:
或COALESCE()函数:
MySQL有三种主要类型:text, number和Date/Time。
Text types:
Data type | Description |
---|---|
CHAR(size) | Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis. Can store up to 255 characters |
VARCHAR(size) | Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis. Can store up to 255 characters.Note: If you put a greater value than 255 it will be converted to a TEXT type |
TINYTEXT | Holds a string with a maximum length of 255 characters |
TEXT | Holds a string with a maximum length of 65,535 characters |
BLOB | For BLOBs (Binary Large OBjects). Holds up to 65,535 bytes of data |
MEDIUMTEXT | Holds a string with a maximum length of 16,777,215 characters |
MEDIUMBLOB | For BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes of data |
LONGTEXT | Holds a string with a maximum length of 4,294,967,295 characters |
LONGBLOB | For BLOBs (Binary Large OBjects). Holds up to 4,294,967,295 bytes of data |
ENUM(x,y,z,etc.) | Let
you enter a list of possible values. You can list up to 65535 values in
an ENUM list. If a value is inserted that is not in the list, a blank
value will be inserted. Note: The values are sorted in the order you enter them. You enter the possible values in this format:ENUM('X','Y','Z') |
SET | Similar to ENUM except that SET may contain up to 64 list items and can store more than one choice |
Number types:
Data type | Description |
---|---|
TINYINT(size) | -128 to 127 normal. 0 to 255 UNSIGNED*. The maximum number of digits may be specified in parenthesis |
SMALLINT(size) | -32768 to 32767 normal. 0 to 65535 UNSIGNED*. The maximum number of digits may be specified in parenthesis |
MEDIUMINT(size) | -8388608 to 8388607 normal. 0 to 16777215 UNSIGNED*. The maximum number of digits may be specified in parenthesis |
INT(size) | -2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The maximum number of digits may be specified in parenthesis |
BIGINT(size) | -9223372036854775808 to 9223372036854775807 normal. 0 to 18446744073709551615 UNSIGNED*. The maximum number of digits may be specified in parenthesis |
FLOAT(size,d) | A small number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter |
DOUBLE(size,d) | A large number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter |
DECIMAL(size,d) | A DOUBLE stored as a string , allowing for a fixed decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter |
*The integer types have an extra option called UNSIGNED. Normally, the integer goes from an negative to positive value. Adding the UNSIGNED attribute will move that range up so it starts at zero instead of a negative number.
Date types:
Data type | Description |
---|---|
DATE() | A date. Format: YYYY-MM-DD Note: The supported range is from '1000-01-01' to '9999-12-31' |
DATETIME() | *A date and time combination. Format: YYYY-MM-DD HH:MM:SS Note: The supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59' |
TIMESTAMP() | *A
timestamp. TIMESTAMP values are stored as the number of seconds since
the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD HH:MM:SS Note: The supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC |
TIME() | A time. Format: HH:MM:SS Note: The supported range is from '-838:59:59' to '838:59:59' |
YEAR() | A year in two-digit or four-digit format. Note: Values allowed in four-digit format: 1901 to 2155. Values allowed in two-digit format: 70 to 69, representing years from 1970 to 2069 |
SQL聚集函数计算某列的各值,返回单一值。
有用的集体函数有:
这些函数会计算重复值,可以使用DISTINCT来消除重复,比如:select sum(distinct age) from persons。
结果也可以用AS来起别名,比如select sum(age) as MySum from persons。
在MySql里没有first和last,所以
等价于:
SELECT OrderPrice FROM Orders ORDER BY O_Id LIMIT 1
等价于:
GROUP BY可以根据记录在某列上的值把表的记录分组,然后分别计算各组的聚集函数的值。它也能指定多个列比如:
Having与WHERE的区别是:
where决定哪些元组被选择参加运算,作用于关系中的元组;
Having决定哪些分组符合要求,作用于分组;
聚集函数的条件关系必须用Having,Where不应该出现聚集函数。
SQL标量函数基于输入值返回单个值。有用的标量函数有:
Parameter | Description |
---|---|
column_name | Required. The field to be formatted. |
format | Required. Specifies the format. |