前端必了解的数据库基础

七七喝可乐
312 阅读3分钟

「这是我参与11月更文挑战的第1天，活动详情查看：2021最后一次更文挑战」

小小前端有时候也需要写一写API，所以对平时常用的 SQL 语句简单的总结了一下。

插入数据

  1. 向已有表插入数据
insert into [DB_name].[dbo].[table_name]
values('column1', 'column2')
  1. 插入数据并返回该条数据的自增列ID
-- 返回本次语句块中插入数据的最后一个自增量ID
insert into [DB_name].[dbo].[table_name]
values('column1', 'column2')
select SCOPE_IDENTITY();

-- 返回整个数据库中插入数据的最后一个自增量ID
insert into [DB_name].[dbo].[table_name]
values('column1', 'column2')
select @@IDENTITY

[注意] 插入时，忽略自增列的值

从已有数据表向另一个表追加数据

insert into dbo.a
select * from dbo.b

将查询数据插入临时表(临时表之前不存在)

select * 
into #tempTable 
from dbo.b

删除数据

delete from [DB_name].[dbo].[table_name]
where colName = 'columnValue' and colName = 'columnValue'

查询数据

select top 1000 *
from [DB_name].[dbo].[table_name]
order by colName

修改数据

update [DB_Name].[dbo].[Table_Name]
set column1 = newValue, column2 = newValue
where id = '1'

批量替换特定列的特定字符串

update [dbo].[Table_Name]
set list_URL = replace(list_URL, 'https://baidu.com', 'https://google.com')
where list_URL LIKE '%baidu.com%'

创建表

CREATE TABLE [DB_Name].[dbo].[Table_Name](
    id int identity(1, 1), --自增列，从1开始，递增量为1
    name char NOT NULL,
    age int NOT NULL,
    sex boolean NOT NULL
)

修改表结构

-- 新增字段
ALTER TABLE [Table_Name] ADD [Column_Name] NVARCHAR(50) NULL
-- 在特定位置新增字段
ALTER TABLE [Table_Name] ADD [Column_Name] NVARCHAR(50) NULL [FIRST | AFTER] [Column_Name]
-- 新增列的时候添加默认值
ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

-- 删除字段
ALTER TABLE [Table_Name] DROP COLUMN [Column_Name]

-- 修改字段
ALTER TABLE [Table_Name] ALTER COLUMN [Column_Name] NVARCHAR(50) NULL

-- 修改字段长度
ALTER TABLE [Table_Name] ALTER COLUMN [Column_Name] varchar(255)

SQL表连接

  • [INNER] JOIN: 内连接, 如果表中有至少一个匹配，则返回行
  • LEFT JOIN: 左连接, 即使右表中没有匹配，也从左表返回所有的行
  • RIGHT JOIN: 右连接, 即使左表中没有匹配，也从右表返回所有的行
  • FULL JOIN: 全连接, 只要其中一个表中存在匹配，就返回行

Table Person

Id_PNameCity
1WinterLondon
2GrossNew York
3MarsBeijing

Table Orders

Id_OOrderNoId_P
1111113
2222223
3333331
4444441
55555565
  1. 谁订购了产品，分别订购了什么产品？
select Person.Name, Orders.OrderNo
from Person, Orders
where Person.Id_P = Orders.Id_O

the Results:

NameOrderNo
Winter33333
Winter44444
Mars11111
Mars22222
  1. 列出所有人的订购（inner join）
select Person.Name, Orders.OrderNo
from Person
inner join Orders
on Person.Id_P = Orders.Id_P
order by Person.Name

the Results:

NameOrderNo
Winter33333
Winter44444
Mars11111
Mars22222

查看特定表的字段结构信息

使用 INFORMATION_SCHEMA.COLUMNS

-- 实体表
select  column_name, data_type, * 
from    serverName.DBName.INFORMATION_SCHEMA.COLUMNS 
where   TABLE_NAME='Portal_DimList'

-- 临时表
select * 
from tempdb.INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME like '#tempTableName%'

使用 sp_columns 存储过程

适用于 DBLink 下的其他 Server 下的表

缺点：不能筛选特定需要的字段

-- 实体表
exec serverName.DBName.DBOwner.sp_columns 'B2B_InvoiceNumber_Flag_New'

-- 临时表
exec [tempdb].[dbo].sp_columns #tempTableName

使用 sys.columns

缺点：获取到的字段类型是数据库里的数字代号，不是可读文字

-- 临时表
select * 
from tempdb.sys.columns 
where object_id=OBJECT_ID('tempdb.dbo.#tempTableName');

查看特定表的信息

select  * 
from    INFORMATION_SCHEMA.tables 
where   TABLE_NAME='Portal_DimList'

插入特殊字符值

-- 1. 修改列类型为 'NVARCHAR'
-- 2. 插入数据时，引号前加 "N"

select N"￠"
 
insert into table_name
values(991, 'm01', 'point', 'Point', 'doubleSum', N'c2￠')

修改 text 类型的列为 ntext 类型

-- 先修改列的类型为 varchar(max)
alter table [dbo].[Portal_JSON]
alter column JSON_text [varchar](max) NULL

-- 再将列的类型修改为 ntext
alter table [dbo].[Portal_JSON]
alter column JSON_text ntext NULL
avatar
文章
阅读
粉丝