SQL课程

不要用", 双引号在SQL中有不同的含义
在单引号里面是大小写敏感的, 但是SQL的关键字本身是不敏感的
对于count函数, 如果传入的参数是*, 则统计该表的总行数(不管有没有缺失值), 如果传入的参数是某一个属性名, 则统计的是该属性中除去缺失值之外的个数, MAX, AVG等会自动忽略缺失值
INSERT INTO <table> VALUES <values>, UPDATE <table> SET <values> WHERE <conditions>, DELETE FROM <table> WHERE <conditions>

CREATE TABLE <table> (<attr1>, <type1>, ...), 注意PRIMARY KEY, NOT NULL, DEFAULT <default_value>, references <table>(<attr>)的使用, 以及DROP TABLE

例子

案例1案例2

create table Person (
    license integer primary key, -- 不要漏逗号
    name varchar(50) not null
); -- 不要漏分号

create table Car (
    regno char(6) primary key,
    model varchar(30),
    driver integer references person(license)
);

create table Studio (
    studio_id integer primary key,
    name varchar(50) not null,
    address varchar(100),
    country char(2) not null references Country(short_code)
);

范围条件判断, BETWEEN, IN, =, >, >=, <, <=

例子

案例1案例2

SELECT *
    FROM Enrolled
WHERE grade IN ('CR', 'D', 'HD');

SELECT *
FROM Student
WHERE age BETWEEN 20 and 25;

逻辑连接, OR, AND

例子

SELECT *
    FROM Student
WHERE (age = 19 OR age = 20)
    AND (age = 19 OR address = 'Bondi')
    AND (age = 20 OR address = 'Newtown')
    AND (address = 'Newtown' OR address = 'Bondi');

SQL课程

评论