SQL笔记

已于 2022-10-09 23:47:47 修改
阅读量582 收藏
点赞数
分类专栏： 数据分析 文章标签： 数据库 sql java
于 2022-10-09 23:40:35 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_42035862/article/details/127236245
版权

笔记部分

SQL分类：

DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE)

DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT)

DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK)

简单语句：

1、创建数据库

CREATE DATABASE database-name

2、删除数据库

drop database dbname

3、备份sql server

--- 创建 备份数据的 device

USE master

EXEC sp_addumpdevice 'disk', 'testBack', 'c:mssql7backupMyNwind_1.dat'

--- 开始 备份

BACKUP DATABASE test TO disk = 'c:\test' WITH FORMAT, NAME = 'Full Backup of MyNwind'

4、创建新表

create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

根据已有的表创建新表：

A：create table tab_new like tab_old (使用旧表创建新表)

B：create table tab_new as select col1,col2… from tab_old definition only

5、删除新表

drop table tabname

6、增加一个列

Alter table tabname add column col type

注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。

7、添加主键：

Alter table tabname add primary key(col)

删除主键：

Alter table tabname drop primary key(col)

8、创建索引：

create [unique] index idxname on tabname(col….)

删除索引：

drop index idxname

注：索引是不可更改的，想更改必须删除重新建。

9、创建视图：

create view viewname as select statement

删除视图：

drop view viewname

10、几个简单的基本的sql语句

选择：select * from table1 where

范围插入：insert into table1(field1,field2)

删除：delete from table1 where

范围更新：update table1 set field1=1 where

范围查找：select * from table1 where field1 like ’%1%’

排序：select * from table1 order by field1,field2 [desc]

11、几个高级查询运算词

A： UNION 运算符

UNION 运算符通过组合其他两个结果表并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。

B： EXCEPT 运算符

EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。

C： INTERSECT 运算符

INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。 注：使用运算词的几个查询结果行必须是一致的。

12、使用外连接

A、left outer join： 左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

B：right outer join： 右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。

C：full outer join： 全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。

1、复制表(只复制结构,源表名：a 新表名：b)

法一：select * into b from a where 1<>1

法二：select top 0 * into b from a

2、拷贝表(拷贝数据,源表名：a 目标表名：b)

insert into b(a, b, c) select d,e,f from b;

3、跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)

insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件例子：..from b in '"&Server.MapPath(".")&"data.mdb" &"' where..

4、子查询(表名1：a 表名2：b)

select a,b,c from a where a IN (select d from b )

或者: select a,b,c from a where a IN (1,2,3)

5、显示文章、提交人和最后回复时间

select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b

6、外连接查询(表名1：a 表名2：b)

select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

7、在线视图查询(表名1：a )

select * from (SELECT a,b,c FROM a) T where t.a > 1;

8、between的用法,between限制查询数据范围时包括了边界值,not between不包括

select * from table1 where time between time1 and time2

select a,b,c, from table1 where a not between 数值1 and 数值2

9、in 的使用方法

select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)

10、两张关联表，删除主表中已经在副表中没有的信息

delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )

11、四表联查问题：

select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....

12、日程安排提前五分钟提醒

SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5

13、一条sql 语句搞定数据库分页

select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段

14、前10条记录

select top 10 * form table1 where 范围

15、选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)

select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)

16、包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表

(select a from tableA ) except (select a from tableB) except (select a from tableC)

17、随机取出10条数据

select top 10 * from tablename order by newid()

18、随机选择记录

select newid()

19、删除重复记录

Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)

20、列出数据库里所有的表名

select name from sysobjects where type='U'

21、列出表里的所有的

select name from syscolumns where id=object_id('TableName')

22、列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。

select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type显示结果：

type vender pcs电脑 A 1电脑 A 1光盘 B 2光盘 A 2手机 B 3手机 C 3

23、初始化表table1

TRUNCATE TABLE table1

24、选择从10到15的记录

select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc

一、Date 类型：

数据类型

描述

DATE()

日期。格式：YYYY-MM-DD

注释：支持的范围是从 '1000-01-01' 到 '9999-12-31'

DATETIME()

*日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS

注释：支持的范围是从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'

TIMESTAMP()

*时间戳。TIMESTAMP 值使用 Unix 纪元('1970-01-01 00:00:00' UTC) 至今的秒数来存储。格式：YYYY-MM-DD HH:MM:SS

注释：支持的范围是从 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC

TIME()

时间。格式：HH:MM:SS

注释：支持的范围是从 '-838:59:59' 到 '838:59:59'

YEAR()

2 位或 4 位格式的年。

注释：4 位格式所允许的值：1901 到 2155。2 位格式所允许的值：70 到 69，表示从 1970 到 2069。

二、SQL Aggregate 函数

SQL Aggregate 函数计算从列中取得的值，返回一个单一的值。

AVG() - 返回平均值

COUNT() - 返回行数

FIRST() - 返回第一个记录的值

LAST() - 返回最后一个记录的值

MAX() - 返回最大值

MIN() - 返回最小值

SUM() - 返回总和

三、SQL Scalar 函数

SQL Scalar 函数基于输入值，返回一个单一的值。

UCASE() - 将某个字段转换为大写

LCASE() - 将某个字段转换为小写

MID() - 从某个文本字段提取字符，MySql 中使用

SubString(字段，1，end) - 从某个文本字段提取字符

LEN() - 返回某个文本字段的长度

ROUND() - 对某个数值字段进行指定小数位数的四舍五入

NOW() - 返回当前的系统日期和时间

FORMAT() - 格式化某个字段的显示方式

四、约束

NOT NULL- 指示某列不能存储 NULL 值。

UNIQUE- 保证某列的每行必须有唯一的值。

PRIMARY KEY- NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。

FOREIGN KEY- 保证一个表中的数据匹配另一个表中的值的参照完整性。

CHECK- 保证列中的值符合指定的条件。

DEFAULT- 规定没有给列赋值时的默认值。

使用：

CREATE TABLE table_name

(

column_name1 data_type(size) constraint_name,

column_name2 data_type(size) constraint_name,

column_name3 data_type(size) constraint_name,

....

);

五、通配符

%

替代 0 个或多个字符

_

替代一个字符

[charlist]

字符列中的任何单一字符

[^charlist]
或
[!charlist]

不在字符列中的任何单一字符

MySQL 中使用REGEXP 或NOT REGEXP运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式。

下面的 SQL 语句选取 name 以 "G"、"F" 或 "s" 开始的所有网站：

SELECT * FROM Websites
WHERE name REGEXP '^[GFs]';

六、常见命令：

AND / OR

SELECT column_name(s)
FROM table_name
WHERE condition
AND|OR condition

ALTER TABLE

ALTER TABLE table_name
ADD column_name datatype

or

ALTER TABLE table_name
DROP COLUMN column_name

GROUP BY

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

HAVING

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

IN

SELECT column_name(s)
FROM table_name
WHERE column_name
IN (value1,value2,..)

INSERT INTO

INSERT INTO table_name
VALUES (value1, value2, value3,....)

or

INSERT INTO table_name
(column1, column2, column3,...)
VALUES (value1, value2, value3,....)

INNER JOIN

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

LEFT JOIN

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

RIGHT JOIN

SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

FULL JOIN

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name

ORDER BY

SELECT column_name(s)
FROM table_name
ORDER BY column_name [ASC|DESC]

SELECT INTO

SELECT *
INTO new_table_name [IN externaldatabase]
FROM old_table_name

or

SELECT column_name(s)
INTO new_table_name [IN externaldatabase]
FROM old_table_name

SELECT TOP

SELECT TOP number|percent column_name(s)
FROM table_name

TRUNCATE TABLE

TRUNCATE TABLE table_name

UPDATE

UPDATE table_name
SET column1=value, column2=value,...
WHERE some_column=some_value

牛客网SQL入门

参考表格

user_profile

iddevice_idgenderageuniversitygpaactive_days_within_30question_cntanswer_cnt
12138male21北京大学3.47212
23214male复旦大学415525
36543female20北京大学3.212330
42315female23浙江大学3.6512
55432male25山东大学3.8201570
62131male28山东大学3.315713

question_practice_detail

iddevice_idquestion_idresultdate
12138111wrong2021-05-03
23214112wrong2021-05-09
33214113wrong2021-06-15
46543111right2021-08-13
52315115right2021-08-13
62315116right2021-08-14
72315117wrong2021-08-15

question_detail

idquestion_iddifficult_level
1111hard
2112medium
3113easy
4115easy
5116medium
6117easy

首次做错的题目

题目：运营想要查看参加了答题的山东大学的用户在不同难度下的平均答题题目数，请取出相应数据

解：三表连接，注意count+distinct函数left right join 保留的分别是哪些部分

select a.university,c.difficult_level,
count(b.question_id)/count(distinct a.device_id) as avg_answer_cnt
from user_profile as a
right join question_practice_detail as b
on a.device_id=b.device_id
left join question_detail as c
on b.question_id=c.question_id
where a.university="山东大学"
group by a.university,c.difficult_level
order by a.university

题目：现在运营想要分别查看学校为山东大学或者性别为男性的用户的device_id、gender、age和gpa数据，请取出相应结果，结果不去重。

解：注意结果不去重，则使用union all函数

select device_id,gender,age,gpa
from user_profile
where university="山东大学"
union all 
select device_id,gender,age,gpa
from user_profile
where gender="male"

题目：现在运营想要将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量（本题注意：age为null 也记为 25岁以下）

解：使用case when 函数，或者用if 函数，下面是简单介绍：

#场景1：有分数score，score<60返回不及格，score>=60返回及格，score>=80返回优秀
SELECT
  STUDENT_NAME,
  (CASE WHEN score < 60 THEN '不及格'
    WHEN score >= 60 AND score < 80 THEN '及格'
    WHEN score >= 80 THEN '优秀'
    ELSE '异常' END) AS REMARK
FROM TABLE
#if做法：
select if( age >=25,"25岁及以上","25岁以下") as age_cut,
count(*) as number
from user_profile
group by age_cut#注意，count作为聚集函数，需要分组
#case做法：
select (case when age >=25 then "25岁及以上" else "25岁以下" end ) as age_cut,
count(*) as number
from user_profile
group by age_cut
#注意case结束有end
#聚集函数改变整体呈现形态

题目：现在运营想要计算出2021年8月每天用户练习题目的数量，请取出相应数据。

DAY(date) 指定日期的月份中的第几天(1-31)

YEAR(date) 指定日期年

month(date) 指定日期月

select
  day(date) as day,#日期函数
  count(question_id) as question_cnt
from
  question_practice_detail
where
  year(date) = "2021" and month(date) = "08"#年月函数，注意这里要用date不能用day，day只剩数字了
group by  day

题目：现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。请你取出相应数据。

计算用户留存率，即两个筛选结果，其中一个等于另一个+1天，则可以连接得到所有第一天刷题后一天还刷题的用户数量。这种数量去重与所有用户数量相除，得到最终的平均概率

datediff(q2.date,q1.date)=1

或者

q2.date = DATE_ADD(q1.date, interval 1 day)

SELECT
  COUNT(q2.device_id) / COUNT(q1.device_id) AS avg_ret
FROM
  (SELECT DISTINCT device_id, date FROM question_practice_detail)as q1
LEFT JOIN
  (SELECT DISTINCT device_id, date FROM question_practice_detail) AS q2
ON q1.device_id = q2.device_id AND q2.date = DATE_ADD(q1.date, interval 1 day)

还我的鸭脖！
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
SQL笔记整理
07-17
SQL：structured query language 创建数据库：create database student (数据库名) 创建数据库表： create table student( userid int not null primary key, lastname varchar(255), firstname varchar(255))（创建...
SQL笔记下载
06-12
除了这些基础操作，SQL笔记可能还会涉及更复杂的查询技术，比如联接（JOIN）、子查询、视图（VIEW）的创建和使用，以及事务处理（TRANSACTION）等。联接允许你从多个表中合并数据，子查询可以在主查询内部执行查询，...
SQL 笔记
10-14 926
1.大小写忽略查询 UPPER/LOWER/INITCAP（首字母大写）SQL>SELECT * FROM employees WHERE UPPER(last_name) = 'WHALEN';SQL>SELECT * FROM employees WHERE LOWER(last_name) = 'whalen';2.转义字符 ESCAPE SQL> SELECT * FROM employees WHERE job_id LIKE '%SA/_%' ESCAPE '/';--把_视为普通字符，而不是l
SQL基础知识笔记
最新发布
Mr丶·Zhou博客
05-10 1069
SQL是一门大小写不敏感的语言，语句以英文半角分号（;）结尾，多行注释使用 /* ……*/，单行注释使用 – ……关键词：grant（赋予权限）、revoke（取消权限）、commit（提交）、rollback（回滚）*内层的查询为子查询，外层的查询为主查询。
sql笔记
爱吃鱼油
03-29 485
sql排序 id本身为字符串 select (ID+0) AS orderfield from cr_rightctl t ORDER BY orderfield select max(ID+0) from cr_rightctl
sql笔记.md
05-23
sql笔记.md
20170909学习sql笔记
04-30
标题“20170909学习sql笔记”表明这是一个关于SQL学习的资料，可能包含了一天的学习记录或者一个教程的集合。SQL，全称Structured Query Language，是用于管理和处理关系数据库的标准语言。这个标题暗示我们将探讨...
sql笔记sql笔记sql笔记sql笔记sql笔记sql笔记
05-25
sql笔记sql笔记sql笔记sql笔记sql笔记sql笔记
SQL语句基础知识
qq_44643302的博客
03-22 1698
SQL 1.数据库的三大范式 1、第一范式(1NF)是指数据库表的每一列都是不可分割的基本数据线；也就是说：每列的值具有原子性，不可再分割。 2、第二范式(2NF)是在第一范式(1NF)的基础上建立起来得，满足第二范式(2NF)必须先满足第一范式(1NF)。如果表是单主键，那么主键以外的列必须完全依赖于主键；如果表是复合主键，那么主键以外的列必须完全依赖于主键，不能仅依赖主键的一部分。 3、第三范式(3NF)是在第二范式的基础上建立起来的，即满足第三范式必须要先满足第二范式。第三范式(3NF)要求：表中的非
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值