SQL基础-1

• SQL uses English keywords & user-defined names CREATE TABLE Staff(StaffNo INTEGER, Salary FLOAT, Lname VARCHAR(20)); INSERT INTO Staff VALUES (32, 25000.0, 'Smith'); • By convention, keywords are upper-case • Text data is enclosed using single quotes ' • Round brackets () are used to group related items • Commas , separate items in a list • Statements are terminated with a semicolon ; 关键字通常大写 文本数据用单引号'包括 （）用于分组相关项  ，用于分割表的项 ；用于结束语句

• The SELECT statement retrieves & formats data • SELECT is the most frequently used SQL statement — SELECT * FROM Staff; • Here, asterisk * acts as a ‘wild card’ - all columns • By default, SELECT outputs all the rows in the table • Use SELECT DISTINCT target list FROM Staff; for avoiding duplicates SELECT检索和格式化数据 *一般用于充当‘wild card’代表所有列 默认情况下SELECT语句会输出表中的所有行 使用SELECT DISTINCT target list FROM Staff;中的DISTINCT可以避免输出重复数据

SELECT target-list FROM relation-list WHERE qualification;  relation-list关系列表 target-list关系列表中关系的属性列表 qualification比较 符号可用> , = , ̸= , ≤ , ≥ 连接词可用AND  OR NOT

• Specific columns can be output by giving their names: SELECT Lname, Position, Salary FROM Staff; • NB. must have a comma , between column names • Can consider the output from SELECT as a new table 可以通过给出列名输出特定列 但是列名之间一定要有， 可以将SELECT输出视为一个新表

• Specific rows can be selected with a WHERE clause: SELECT Lname, Position, Salary FROM Staff WHERE Salary > 20000; • The symbol > (greater than) is a comparison operator • Other comparison operators: =; !=; <>就是不等于 • The condition Salary > 20000 is called a predicate • For each row, if predicate is true, row is output 使用WRERE筛选特定数据

 

可以通过组合语句达到复合筛选数据

• Other predicates include BETWEEN , IN , and LIKE • But they still evaluate to either true or false• % matches zero or more characters • matches exactly one character • NB. Some DMBSs use * and ? for wildcards 还可以使用BETWEEN , IN , LIKE  % 匹配 0 个或多个字符 _ 精确匹配一个字符

WHERE column BETWEEN value1 AND value2;
WHERE column IN (value1, value2, ...);要查询的字段值要等于后面的value其中之一
WHERE column LIKE pattern; 用单引号括住想要查询的属性值

数据库术语与SQL语言对照

SQL is a very large and powerful language, but every type of SQL statement falls within one of three main categories (or sub-languages): • Data Definition Language (DDL) for creating a DB — e.g., CREATE , DROP , ALTER • Data Control Language (DCL) for administering a DB — e.g., GRANT , DENY , USE • Data Manipulation Language (DML) to access a DB — e.g., SELECT , INSERT , UPDATE , DELETE SQL组件分类 DDL创建数据库 DCL管理数据库 DDL访问数据库

• C&B use a special ‘BNF’ notation to describe SQL syntax: • BNF (Backus-Normal form) is a meta language... • meta language: a language that describes a language BNF描述SQL语法

[]可选元素 {}必选元素 |替代   ...大于等于0

QBE示例查询Query By Example (QBE) Modern DBMSs often provide simple form-based methods of specifying queries (QBE). For example, MS-Access:

Conclusion• SQL is the standard query language for RDBMS • Three main categories of SQL — DDL, Data Definition Language — DCL, Data Control Language — DML, Data Manipulation Language • SELECT belongs to DML • SELECT retrieves & displays data from the database