Oublie的Hugo博客
文章 标签 分类 关于
Oublie的Hugo博客

Contents

数据库-SqlZoo

oublie included in 数据库
   2713 words   6 minutes 

本文为作者跟随SqlZoo学习sql的笔记，仅看mysql支持的内容

select

selcet用于提取数据，空格加标识符命名列名

1

select xxx x,yyy y;

数值字段允许计算：+-*/%

嵌套查询一定要给表取别名，否则会报错

1
2
3

select ...
from (select ...
      from ...) a

函数

较为常见的函数列表见官网

列举常用的函数，聚合函数配合group by使用，否则为整个表聚合:

语法

  • case:类似其他语言的switch
1
2
3
4

CASE WHEN condition1 THEN value1 
    WHEN condition2 THEN value2  
    ELSE def_value 
END
  • if:IF(condition, value_if_true, value_if_false)，case的二元形式的简写

CSDN 半笙彷徨

  • cast:显式类型转换，CAST(value AS datatype)

CSDN 山茶花开时

1

SELECT CAST(150 AS CHAR);

工具

  • cast(expr to type):类型转换
  • lead(field,n):往后n行记录的field值
  • lag(field,n):往前n行记录的field值

数值

  • ceil(n):n向上取整
  • floor(n):n向下取整
  • round(n,k):四舍五入保留k位小数
  • abs(n):n取绝对值
  • cos(n):求n余弦
  • sin(n):求n正弦
  • tan(n):求n正切
  • a div b:a地板除以b
  • coalesce(n…):取n切片中第一个非null值

字符串，索引以1开头

  • concat(s…):连接字符串
  • left(s,n):取s字符串的左边n个字符
  • right(s,n):取s字符串的右边n个字符
  • instr(s1,s2):s2在s1中出现第一次的索引
  • length(s):s的长度
  • replace(s,p,r):将s中的p替换成r
  • substr(s,n,k):将s从索引n向后取k个字符
  • trim(s):将s两端的空白符去掉
  • sss regexp xxx:正则匹配，xxx为正则表达式，sss为被查字段，与like的区别是like只能用%和_通配符
  • upper(s):将s转换为大写
  • lower(s):将s转换为小写

时间

  • current_date:当前时间，datetime类型，精确到日
  • current_timestamp:当前时间，datetime类型，精确到秒
  • extract(xxx from d):根据xxx粒度提取datetime类型d的信息，xxx可以是year,quarter,month,day,hour,minute和second，同时这些值也可以作为函数来提取值，比如datepart(year,d)和year(d)是一样的
  • +interval n xxx:增加n个xxx，xxx可以是year,quarter,month,day,hour,minute和second

聚合函数

  • avg(field):求field的平均值
  • count(field):求field为非空的记录数，count(*)查询表的记录数
  • max(field):求field的最大值
  • min(field):求field的最小值
  • sum(field):求field的和
  • rank,dense_rank,row_number:8.0之后能用，可以通过变量加case语法实现，RANK并列跳跃排名（v1和v2并列，v2的第一个记录rank为v1第一个记录rank+k，k为v1记录数），DENSE_RANK并列连续排序（v1和v2并列，v2的第一个记录rank为v1第一个记录rank+1），ROW_NUMBER连续排名（每行记录值+1）

CSDN 哲这这

1
2

# order by提到over后面的括号，xxx为order ~~by后每组内再细分一次~~
rank() over (partition by xxx,yyy order by score desc)

SELECT .. WHERE

添加对记录的条件判断

1
2
3

SELECT yr, city 
FROM games
WHERE yr = 2004

SELECT .. GROUP BY

分组进行统计

1
2

SELECT continent, COUNT(yr) FROM games
 GROUP BY continent

A JOIN B on condition

内连接两个表

1
2
3

SELECT games.yr, city.country
  FROM games JOIN city
       ON (games.city = city.name)

SELECT .. SELECT

嵌套查询，子查询得添加别名，格式为空格加别名

1
2
3
4
5
6

SELECT name, ROUND(gdp_per_capita)
  FROM
  (SELECT name,
          gdp/population AS gdp_per_capita
     FROM bbc) X
 WHERE gdp_per_capita>20000

INSERT .. VALUES

添加记录

1

INSERT INTO games(yr, city) VALUES (2012,'London')

INSERT .. SELECT

批量添加记录

1
2

INSERT INTO games(yr,city)
  SELECT yr+12, city FROM games;

UPDATE

更新记录

1

UPDATE games SET city='Paris' WHERE yr = 2012;

DELETE

删除记录

1

DELETE FROM games WHERE yr=2000;

CREATE TABLE

创建表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE games
(
  /* 这里举例了添加主键和非空 */
  yr   INT NOT NULL PRIMARY KEY,
  city VARCHAR(20)
  UNIQUE KEY indexname(index_column_1,index_column_2,...)  USING BTREE
  INDEX indexname(index_column_1,index_column_2,...) 
  /*前缀索引*/
  INDEX indexname(column_name(length))
);

CREATE VIEW

创建视图，其实就是一个存一个select语句，创建的视图的使用方法和table的使用方法一致

1
2
3

CREATE VIEW og AS
  SELECT yr,city FROM games
   WHERE yr<2006;

WITH CHECK OPTION

让视图随底层表更新

CREATE INDEX

创建索引

1

CREATE [unique] INDEX gamesIdx ON games(city,yr [desc]) USING BTREE;

在表中创建索引见CREATE TABLE

CSDN qq_41044636 MySQL InnoDB数据表不设置主键时的情况

第一种情况：用户定义了主键，那么InnoDB会使用主键作为聚簇索引； 第二种情况：用户没有定义主键，那么InnoDB会使用第一个非空的唯一索引作为聚簇索引； 第三种情况：用户既没有定义主键，也没有定义唯一索引，那么InnoDB会自动生成一个不可见的ROW_ID的列名为GEN_CLUST_INDEX的聚簇索引，该列是一个6字节的自增数值，随着插入而自增。

CSDN 思想永无止境 MySQL添加/删除主键、外键、唯一键、索引、自增

绝大部分情况下，mysql中的索引index和键key是同义词

DROP

删除表，视图，索引

1
2

DROP VIEW old_games;
DROP INDEX gamesIdx ON games;

ALTER

修改表，索引

1
2
3
4
5

ALTER TABLE games ADD season VARCHAR(6);
ALTER TABLE `test2` ADD UNIQUE ( `userid`)
ALTER TABLE `test2` DROP PRIMARY KEY ,ADD PRIMARY KEY ( `id` )
ALTER TABLE `test2` ADD KEY( `id`(12) )
alter table 表名 change column 旧列名 新列名  新列名格式

UNION

合并两个结构相同的表，会进行去重，union all不进行去重

1
2
3

SELECT name FROM bbc WHERE name LIKE 'Z%'
UNION
SELECT name FROM actor WHERE name LIKE 'Z%'

JOIN

内连接(INNER JOIN)、左连接(LEFT JOIN)、右连接(RIGHT JOIN)和全连接(FULL JOIN)

左外连接

1
2
3

SELECT games.yr, city.country
  FROM games LEFT JOIN city
       ON (games.city = city.name)

NULL

空值判断使用is null

1
2
3

SELECT name,gdp
FROM world
WHERE gdp IS NULL

个人笔记

select后的字段是否可以运算

可以直接引用from表里面的字段并进行运算，当使用group by时引用字段遵循group by规则（group by字段和聚合函数）。

注意可以直接进行运算

utf8和utf8mb4

CSDN 骑台风走 utf8和utf8mb4的区别

uft8最大字符长度为 3 字节

utf8mb4（mb4 = most bytes 4）

utf8mb4是utf8的超集。用来兼容四字节的unicode

变量

CSDN xiaoniuxqq

1
2
3
4

/* 修改变量*/
select @a := @a + 1
/* 定义变量*/
where xxx,(select @a := 1)

因为这种变量的可以直接在需要查询的表table的后面添加select ... from table,(select @var1=xxx,@var2=yyy)，这其实是一个联表查询，但是不影响要查询的表，因为变量所在的表只有一行记录

这种变量的定义和修改可以在select子句，case when 子句和then子句中使用，赋值语句的结果为赋值后的值

需要关键字字段名或值

可以使用反引号括起来就能用了，比如rank不让使用可以用`rank`来使用。

避免sql注入

  1. 使用预编译语句进行参数化查询

？占位符来预编译语句，然后使用setval类似的api来执行语句

  1. 客户端或服务端检查用户输入是否合法

子查询和嵌套查询

子查询在select的字段里面，where里面的表达式

嵌套查询在from里面

Updated on 2023-03-29  views
Back | Home
 |