Benson's blog

最好的安排

0%

SQL

发表于 更新于 分类于

SQL语句

参考教程：廖雪峰老师的SQL教程

数据类型

名称 类型 备注
INT 整型 4bit
BIGINT 长整型 8bit
REAL 单精度浮点型 4bit
DOUBLE 双精度浮点型 8bit
DECIMAL(M, N) 高精度小数 M表示总共位数，N表示小数点位数
CHAR(N) 定长字符串 N个字符的字符串，插入不满足N个的字符串用空格填充
VARCHAR(N) 变长字符串 0~N个字符的字符串
BOOLEAN 布尔类型 True or False
DATE 日期类型
TIME 时间类型
DATETIME 日期 + 时间类型

可以使用NOT NULL修饰一个字段类型，表示该字段不能为空 ### 关系型数据库模型

关系型数据库在于使用行和列组成的表来存储数据，然通过表间关系和结构化查询语句(SQL)来进行数据存储、管理和检索，从而实现数据库的高效组织和操作

主键

  • 主键：能通过某个字段唯一区分出不同的记录，这个字段称为主键
  • 联合主键：关系型数据库允许通过多个字段唯一标识记录，将两个或者更多的字段都设置为主键。对于联合主键，允许其中一列重复，主要不是所有主键列都重复即可

外键

  • 一对多
  • 多对多：通过一个中间表，关联两个一对多关系，形成多对多关系
  • 一对一：大型数据库可以将一个表拆分成多个子表，分离出不常使用的数据，以提高表查询效率
1
2
3
4
ALTER TABLE thisTable;
ADD CONSTRAINT foreignKey;  //指定约束名称
FOREIGN KEY (anotherCol);   //根据列名称指定本表中外键列
REFERENCES anotherTableName (colName); //指定另外一个表中的某一列作为外键约束参考

索引

当需要频繁根据某一列的数据进行查询时，可以为该列创建索引，索引的效率取决于索引列的值是否散列，列值越不相同，索引查询效率越高

1
2
ALTER TABLE thiSTable;
ADD INDEX indexName (colName); //为colName列创建索引indexName

查询语句

  • 基本查询：查询表中的所有行，可以查询到一张表中的所有记录

    1
    SELECT * FROM tableName;

  • 条件查询：查询符合条件的记录

    1
    2
    SELECT * FROM tableName WHERE <cond expr>;
    SELECT * FROM tableName WHERE <cond expr1> AND <cond expr2>; //多条件查询

  • 投影查询：查询特定的列（别名和映射）

    1
    2
    SELECT col1, col2, col3 FROM tableName;						//仅查询特定列
    SELECT col1 ailas1, col2 alias2, col3 alias3 FROM tableName; //结果集和原表的字段名可以不同

  • 排序：将查询结果按特定列的特定顺序输出，ORDER BY关键字默认按照升序输出，DESC关键字表示降序，ASC关键字表示升序但可以省略

    1
    2
    SELECT col1, col2, col3 FROM tableName ORDER BY col1; 		//按照col1的列数据升序输出结果集
    SELECT col1, col2, col3 FROM tableName ORDER BY col1 DESC; 	//降序输出结果集

  • 多表查询：多表查询又称为笛卡尔查询，结果集的列数是所有表的列数之和，结果集的行数是所有表的行数之积

    1
    SELECT * FROM table1, table2

    当table1、table2中存在重复列名时可以使用投影查询中的列名别名方法

    1
    2
    3
    4
    5
    6
    SELECT
    	table1.id sid,
    	table1.name sname,
    	table2.id cid,
    	table2.name cname,
    FROM table1, table2;

    在上述使用列名别名的方法有一些麻烦，可以使用表名别名的方法再次简化SQL语句

    1
    2
    3
    4
    5
    6
    7
    SELECT 
    	t1.id sid,
    	t1.name sname,
    	t2.id cid,
    	t2.name cname,
    FROM
    	table1 t1, table2 t2;

    用M行表和N行表进行多表查询会得到M * N行记录，造成结果集非常巨大，需要谨慎使用

  • 分页查询：对于查询结果比较大的情况，将结果集拆分成多页进行显示

    1
    2
    3
    4
    SELECT id, name, gender 
    FROM table1 
    ORDER BY id DESC
    LIMIT M OFFSET N

    LIMIT M OFFSET N设置一页中包含M条记录，显示第N条记录所在页

  • 聚合查询：用于查询结果集进行数学统计，SQL提供一些专门统计函数

    1
    SELECT COUNT(*) <OUTName> FROM table1

    使用COUNT()函数统计table1中有几行，注意输出结果时一行一列的COUNT(*)结果集，其中指定输出结果集即二维列表名称<OUTName>，以下是其他常用的聚合函数

    函数 说明
    SUM() 计算某一列的和值，该列必须为数值类型
    AVG() 计算某一列的平均值，该列必须为数值类型
    MAX() 计算某一列的最大值
    MIN() 计算某一列的最小值

  • 分组查询：当可以将表中的所有记录按某种条件进行分组时，比如班级、性别等，每次修改WHERE条件来显示单一次查询的结果比较麻烦，因此可以用GROUP BY关键字对记录进行分组

    1
    2
    3
    4
    //该SQL语句将相同class_id的记录分为一组，计算各组记录的score均值
    SELECT class_id, AVG(score)
    FROM table1
    GROUP BY class_id

    注意在分组查询中，查询结果中只能插入分组字段，分组无关字段不能插入

  • 连接查询：一种特殊的多表查询，SQL语句中FROM指定一张主表后，将多张表的记录按照特定规则连接到主表上，而不是简单的乘积形式拼接

    1
    2
    3
    SELECT * FROM students s
    INNER JOIN class c
    ON s.id = c.id

    INNER JOIN关键字声明需要连接的表，ON声明连接条件，连接方式有如下四种

    • INNER JOIN：返回同时存在于两张表中的满足条件字段所对应的记录，交集
    • LEFT OUTER JOIN：返回连接表的记录
    • RIGHT OUTER JOIN：返回主表的记录
    • FULL OUTER JOIN：返回主表和连接表的记录，并集

    注意对于OUTER JOIN连接的查询中，各表中不满足条件的记录使用null填充

修改语句

插入

1
INSERT INTO tableName (col1, col2, col3) VALUES (val1, val2, val3)

更新

1
UPDATE tableName SET col1=val1, col2=val2, ... WHERE col3=val3

注意UPDATE尽量搭配WHERE使用，否则将改变整张表的记录

删除

1
DELETE FROM tableName WHERE ...

注意DELETE语句也尽量搭配WHERE使用，否则将删除整张表的记录

其他关键字

  • IN：用于简略多个OR运算的写法，判断某条记录是否在集合中

    1
    SELECT * FROM table1 WHERE col1 IN (val1, val2)

    同时也可以使用IN关键字进行子查询，即在IN中再使用SQL语句，对结果集进行查询

    1
    2
    3
    SELECT emp_name
    FROM employees
    WHERE emp_department IN (SELECT dept_name FROM departments WHERE location = 'New York');

  • IS：用于判断某个值是否为NULL，不能直接用=进行比较，因为NULL表示未知的值，不能直接使用普通等号进行比较

    1
    2
    3
    SELECT column1, column2
    FROM table_name
    WHERE column1 IS NULL;