sql学习

HuYi_code
156 阅读7分钟

1. 数据库以及分类

  • MySQL 数据库，Oracle 数据库，SQL Server 数据库，属于关系型数据库（SQL 数据库）。
  • MongoDB 数据库，属于非关系型数据库（非 SQL 数据库）

2. 传统数据库

1.传统数据库的数据组织结构

  • 数据库（database）
    • 数据表（table）
      • 数据行（row）
        • 字段（field）

2.安装并配置 MySQL 数据

  • MySQL server:专门用来提供数据存储和服务的软件
  • MySQL WorkBench：可视化的MySQL的管理工具

3.SQL 语言(可以操作数据库的语言)

1.SELECT 语句

  • SELECT * FROM 表名称
  • SELECT 列名称 FROM 表名称

2. INSERT INTO 语句

  • INSERT INTO table_name(row1,row2) VALUES (val1,val2)

3. UPDATE 语句

  • UPDATE table SET filed = 'val' WHERE filed = 'something'

4. DELETE 语句

  • DELETE FROM tablen WHERE field = 'val'

5. WHERE 子句 （条件限定语句）

  • SQL 语句 + WHERE 列 运算符 值

6. AND 和 OR 运算符

  • 一般和 WHERE 联合起来使用,提供多种条件查询

7. ORDER BY 语句

1.排序

  • SELECT * FROM tbale ORDER BY status;（默认升序）
  • SELECT * FROM table ORDER BY status ASC;（升序）
  • SELECT * FROM table ORDER BY id DESC;(降序排序)

2.多重排序

  • SELECT * FROM table ORDER BY status DESC,userName ASC 先根据 status 进行降序排序，再根据 userName 进行升序排序。

6. COUNT(*)函数

  • SELECT COUNT(*) FROM table WHERE status = 0 统计表里面 status 为 0 的数据的总数量。(此时数据库会出现一个名字叫 COUNT(*)的列,然后显示数据的多少个)

7. AS 为列设置别名

  • SELECT COUNT(*) AS total FROM table WHERE status = 0 统计表里面 status 为 0 的数据的总数量。(此时数据库会出现一个名字叫 total 的列,然后显示数据结果)

练习书写：

-- SELECT * FROM users
-- SELECT userName, password FROM users
-- INSERT INTO users (userName, password) VALUE ('tony', '123333')
-- UPDATE users SET password = '888888' WHERE id = '4' 
-- UPDATE users SET password='admin123',status=1 WHERE id = 2
-- DELETE FROM users WHERE id=4
-- where 子句演示
-- select * from users where status =1 
-- select * from users where id >=2
-- select * from users where userName != 'ls'
-- AND 和 OR
-- select * from users where status=0 and id<3
-- select * from users where status =1 or userName = 'zs'
-- select * from users order by status
-- select * from users order by id desc
-- select * from users order by status desc,userName asc
-- select count(*) from users where status = 0
-- select count(*) as total from users where status = 0
-- select username as uname,password as pwd from users

总结：

查询语句：

select */colNm,... from tableNm where colNm (=/in/like/not in/is null/is not null) condition and/or ... order by colNm asc/desc group by colNm

  1. select:选取
  2. colNm:选取的列名
  3. tableNm:表名
  4. where:筛选条件的关键字
  5. =/in/like/not in/is null/is not null:等/包含于/相似(以...开头/结尾/在...之间)/不包含于/为null/不为null
  6. and/or:and => 且 or => 或
  7. order by:把筛选出来的数据，根据某列col的数据排序，asc(正序)/desc(倒序)
  8. group by:把筛选出来的数据，根据某列col的不同数据分别形成集合，然后在对集合的数据进行相应的操作后，形成新的数据选取出来。eg:把table的表数据根据col列的数据不同，形成两列数据，一列是col的不同的数据，一列是col的不同数据出现的次数count(1) as num。
select col, count(1) as num from table group by col
  1. 当group by和 order by 同时出现时，group by一般会优先于order by，先把数据进行相应的group by形成集合之后，在对处理后的数据进行order by排序，一般order by放在最后执行。
子查询：

在实际开发过程中，需要查询的数据可能来自于不同的表，有时就会出现在一个SQL中进行多个表查询的情况，而子查询就是其中的一种，一般情况下，子查询要么是筛选出来的list作为一个条件对主查询的数据进行一个筛选，要么是筛选出来的结果拼接到主查询筛选出来的表里面去等等，有很多种应用。但是主要分为一般子查询和相关子查询

  1. 一般子查询:一般子查询的数据的筛选条件与主查询的操作无关，这种情况一般会先进行子查询得到子查询的结果，然后把子查询的结果作为主查询的一个筛选条件加入到主查询中进行查询。
  2. 相关子查询:相关子查询的数据的筛选条件与主查询的某个操作得到的变量有关，这种情况一般会先进行主查询得到某个操作的变量结果，然后把这个变量结果带到子查询中进行相应的筛选后，进而得到相应的子查询的数据。
联表查询:

实际开过程中，需要的数据往往是来自不同的表经过相应的联表查询条件之后得到的结果。(inner join,left join,right join,full outer join,union,union all)

  1. inner join(join): A inner join B on A.key = B.key,表示A表与B表分别根据一致的key值的筛选出来相应的数据组合起来为新数据list，(会根据每一条A数据的A.key得到与之对应相等的B.key的B数据,然后把两个表的数据组合起来)。【A与B的key交集组成的AB表数据list】
  2. left join(left outer join): A left join B on A.key = B.key,表示A表与B表分别根据一致的key值的筛选出来相应的数据组合起来为新数据list，然后再加上A表中没找到与B.key相等的数据与Null组成的新数据list。(会根据每一条A数据的A.key得到与之对应相等的B.key的B数据,然后把两个表的数据组合起来 + 如果A数据中的A.key没有找到相等的B.key的B数据，则此条A数据与Null结合补齐B数据的遗留的位置，从而形成一个条新数据)【A与B的key交集组成的AB表数据list + A(不符合条件的) + Null组成的新list】
  3. A left join B on A.key = B.key where B.key = null:A表与B表联表查询完，只保留B.key = null的数据。 【A(不符合条件的) + Null组成的新list】
  4. right join与left join一样，只是改变了方向而已，【A与B的key交集组成的AB表数据list + B(不符合条件的) + Null组成的新list】
  5. A full outer join B on A.key = B.key:【A与B的key交集组成的AB表数据list + A(不符合条件的) + Null组成的新list + B(不符合条件的) + Null组成的新list】
  6. A full outer join B on A.key = B.key where B.key is null or A.key is null: 【A(不符合条件的) + Null组成的新list + B(不符合条件的) + Null组成的新list】
  7. A union B,A union all B: 不属于联表查询，这个只是把A表每条数据和B表每条数据加起来形成一个相加的新list(要求A表和B表的字段colNm个数相同，且colNm的数据类型也要相同),相当于A表数据list+B表数据list。【(union去重，而union all不去重)，而联表查询是需要把A表和B表符合条件的那一行数据，进行组合成为一个新行，所以union/union all显然不是联表查询】
pg_sql 操作总结：
1. 批量insert数据到A表，如果A表的id已存在,则进行update这条id下的其他属性。
insert into A 
    (key1,key2,key3,...)
    values
    (value1,value2,value3,...),
    (value1,value2,value3,...),
    (value1,value2,value3,...),
    (value1,value2,value3,...),
    (value1,value2,value3,...),
    ...
on conflict (id)
do update set 
   key1 = value1,
   key3 = value3,
   upd_dtm = now(),
   ...
2. 函数操作总结：
  • array_agg: 把一个分组中的所有column的数据，聚合成一个SQL数组{}。
  • array_to_json: 把SQL数组{}转成JSON数组[]。
  • row_to_json: 把SQL筛选出来table的每一行的row转换成一个JSON对象(row得是一个对象而不是字符串等类型)。
  1. aggregate:就是集合，聚合的意思，一般agg为后缀的方法都是集合聚合的功能，一般要搭配group by来使用。如果想把group by分组出来的数据使用起来，就必须使用聚合函数，如下示例： table name: stu
classnameid
onejame00001
onetom00002
onekobe00003
twoben00004
twojerry00005
threemary00006
threeamy00007
select class , 
       (select array_to_json(array_agg(row_to_json(st))) 
               from (select name from stu) as st) as stu_name 
from stu s group by class

结果为:

classstu_name
one[{"name":"jame"},{"name":"tom"},{"name":"kobe"}]
two[{"name":"ben"},{"name":"jerry"}]
three[{"name":"mary"},{"name":"amy"}]

这样的好处是能直接预处理部分数据，减少在后端开发过程中的遍历次数（由7次 -> 3次），大大提高代码效率。

avatar
文章
阅读
粉丝