SQL 是结构化查询语言,用于访问数据库。
资源网站
基础
刷题
- sqlzoo
- hackerrank
- LeetCode
知识点记录
1、单引号与双引号
SQL 中使用单引号表示字符串(双引号对于原生 SQL 来说只是普通字符)。
不过,现在很多衍生版的 SQL,比如 MySQL 中即可以使用单引号也可以使用双引号。
注:SQL 字段(列名)不需要用字符串表示
关于单引号和双引号
- Python 中单引号和双引号都表示字符串,本质没有区别
- C 语言中单引号表示字符,实质上表示一个整数;而双引号表示字符串,代表的是一个指向无名数组起始字符的指针。
2、select 语句可以新建栏/列,只需在 select
后使用相应表达式
3、SQL 中的关键字:in、between、like
in
表示包含关系between
表示范围,注意是包含性的like
经常与万用字串%
以及万用字符_
组合使用
注: 表示两个字段的值相同也可以直接使用like
连接
4、SQL 中使用concat()
进行字符串拼接
%
、%%
、_%
有区别
其中,concat(name, '_%')
可以避免值不变(同name
),而%
、%%
不行
select name, capital from world
where capital like concat(name, '_%')
name capital
Andorra Andorra la Vella
Guatemala Guatemala City
Kuwait Kuwait City
Mexico Mexico City
Monaco Monaco-Ville
Panama Panama City
select name, capital from world
where capital like concat(name, '%%')
name capital
Andorra Andorra la Vella
Djibouti Djibouti
Guatemala Guatemala City
Kuwait Kuwait City
Luxembourg Luxembourg
Mexico Mexico City
Monaco Monaco-Ville
Panama Panama City
San Marino San Marino
Singapore Singapore
可看到后者有许多项没有变化(目标是找出有扩展的名称)
5、SQL 使用 replace(A, B, '')
来去除 A 中的 B 部分
6、SQL 中使用=
而不是==
作为比较运算符
7、SQL 使用类似 Python 元组的()
来装多个字符串(搭配 in
使用),比如('France', 'Germany', 'Italy')
8、SQL 字符串的截取:left(str, num)
和 right(str, num)
;字符串的长度:length(str)
9、
SQL进阶(牛客网)笔记
增删改查操作
插入记录
基本形态:
INSERT INTO Table[(c1, c2, ...)] VALUES(v1, v2, ...)
用法总结:
普通插入(全字段):INSERT INTO table_name VALUES (value1, value2, ...),自增id列可以传入null或者0
普通插入(限定字段):INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...),即在表格名后指定相应的列名
多条一次性插入:INSERT INTO table_name (column1, column2, ...) VALUES (value1_1, value1_2, ...), (value2_1, value2_2, ...), ...,即多条记录的值通过逗号分隔开来
从另一个表导入:INSERT INTO table_name SELECT * FROM table_name2 [WHERE key=value],即使用一个select子查询替代values关键字,值得注意的是新表会自动生成id序列
题目链接:SQL2 插入记录(二)
还有两个特殊的插入
1、insert replace into···values()
作用:若表格中已经存在此行数据(根据主键或者唯一索引判断),则先删除此行数据,再进行插入操作
INSERT REPLACE INTO examination_info
VALUES(NULL,9003,'SQL','hard',90,'2021-01-01 00:00:00');
2、insert ignore into values()
作用:如数据存在,则忽略
INSERT IGNORE INTO actor VALUES('3', 'ED', 'CHASE', '2006-02-15 12:34:33')
SQL 插入小结:
insert into:插入数据,如果主键重复,则报错
insert repalce:插入替换数据,如果存在主键或unique数据则替换数据
insert ignore:如果存在数据,则忽略。
更新记录
基本形态:
UPDATE Table
SET c1_name = 'a'
WHERE c1_name = 'A'
将字段c1_name中的大写A变为小写a
例子:
设置为新值:UPDATE table_name SET column_name=new_value [, column_name2=new_value2] [WHERE column_name3=value3]
根据已有值替换:UPDATE table_name SET key1=replace(key1, '查找内容', '替换成内容') [WHERE column_name3=value3]
删除记录
基本形态:
DELETE FROM Table [WHERE xxx]
没有where则是删除所有行,(*)可加可不加
总结:
DELETE TABLE 删除数据(根据指定条件)
TRUNCATE TABLE 清空数据,并保留结构
DROP TABLE 清空数据,并销毁表格
表与索引操作
新建表格
1、直接创建新表格
CREATE TABLE [IF NOT EXISTS](
c_name, c_type -- 列名和类型必选
[ PRIMARY KEY -- 可选的约束,主键
| FOREIGN KEY -- 外键,引用其他表的键值
| AUTO_INCREMENT -- 自增ID
| COMMENT comment -- 列注释(评论)
| DEFAULT default_value -- 默认值
| UNIQUE -- 唯一性约束,不允许两条记录该列值相同
| NOT NULL -- 该列非空
], ...
)
[CHARACTER SET charset] -- 字符集编码
[COLLATE collate_value] -- 列排序和比较时的规则(是否区分大小写等)
一些细节:
自增ID:AUTO_INCREMENT;
设置主键:PRIMARY KEY;
唯一性约束:UNIQUE
非空约束:NOT NULL
设置默认值:DEFAULT 0
当前时间戳:CURRENT_TIMESTAMP
评论/注释:COMMENT
如果该表已创建过,正常返回:IF NOT EXISTS
例子:
CREATE TABLE IF NOT EXISTS user_info_vip (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int UNIQUE NOT NULL COMMENT '用户ID',
nick_name varchar(64) COMMENT '昵称',
achievement int DEFAULT 0 COMMENT '成就值',
`level` int COMMENT '用户等级',
job varchar(32) COMMENT '职业方向',
register_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间'
) CHARACTER SET utf8 COLLATE utf8_general_ci;
2、从另一张表复制表结构创建表
CREATE TABLE tb_name LIKE tb_name_old
3、从另一张表的查询结果创建表
CREATE TABLE tb_name AS SELECT * FROM tb_name_old WHERE options # 这里 AS 可省略
修改表
{ ADD COLUMN <列名> <类型> -- 增加列
| CHANGE COLUMN <旧列名> <新列名> <新列类型> -- 修改列名或类型
| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT } -- 修改/删除 列的默认值
| MODIFY COLUMN <列名> <类型> -- 修改列类型
| DROP COLUMN <列名> -- 删除列
| RENAME TO <新表名> -- 修改表名
| CHARACTER SET <字符集名> -- 修改字符集
| COLLATE <校对规则名> } -- 修改校对规则(比较和排序时用到)
例子:
ALTER TABLE user_info ADD school VARCHAR(15) AFTER level;
ALTER TABLE user_info CHANGE job profession varchar(10);
ALTER TABLE user_info MODIFY achievement int(11) DEFAULT 0;
删除表
DROP TABLE [IF EXISTS] 表名1 [, 表名2]
创建索引
CREATE
[UNIQUE -- 唯一索引
| FULLTEXT -- 全文索引
] INDEX index_name ON table_name -- 不指定唯一或全文时默认普通索引
(column1[(length) [DESC|ASC]] [,column2,...]) -- 可以对多列建立组合索引
删除索引
drop方式删除索引:
DROP INDEX <索引名> ON <表名>
alter方式删除索引:
ALTER TABLE <表名> DROP INDEX <索引名>
聚合分组查询
多表查询
窗口函数
两篇很好的问文章:
1、OVER()函数的理解
共有四种用法:
非常好的一个参考:SQL窗口函数OVER详细用法,一学就会
2、窗口函数较为全面的解析
只有你还不知道窗口函数 (略)
其他常用操作
参考: