前端必了解的数据库基础 插入数据 从已有数据表向另一个表追加

发表于

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

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

插入数据

  1. 向已有表插入数据
1
2
sql复制代码insert into [DB_name].[dbo].[table_name]
values('column1', 'column2')
  1. 插入数据并返回该条数据的自增列ID
1
2
3
4
5
6
7
8
9
sql复制代码-- 返回本次语句块中插入数据的最后一个自增量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

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

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

1
2
sql复制代码insert into dbo.a
select * from dbo.b

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

1
2
3
sql复制代码select * 
into #tempTable 
from dbo.b

删除数据

1
2
sql复制代码delete from [DB_name].[dbo].[table_name]
where colName = 'columnValue' and colName = 'columnValue'

查询数据

1
2
3
sql复制代码select top 1000 *
from [DB_name].[dbo].[table_name]
order by colName

修改数据

1
2
3
sql复制代码update [DB_Name].[dbo].[Table_Name]
set column1 = newValue, column2 = newValue
where id = '1'

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

1
2
3
sql复制代码update [dbo].[Table_Name]
set list_URL = replace(list_URL, 'https://baidu.com', 'https://google.com')
where list_URL LIKE '%baidu.com%'

创建表

1
2
3
4
5
6
sql复制代码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
)

修改表结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
sql复制代码-- 新增字段
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_P Name City
1 Winter London
2 Gross New York
3 Mars Beijing

Table Orders

Id_O OrderNo Id_P
1 11111 3
2 22222 3
3 33333 1
4 44444 1
5 55555 65
  1. 谁订购了产品，分别订购了什么产品？
1
2
3
sql复制代码select Person.Name, Orders.OrderNo
from Person, Orders
where Person.Id_P = Orders.Id_O

the Results:

Name OrderNo
Winter 33333
Winter 44444
Mars 11111
Mars 22222
  1. 列出所有人的订购（inner join）
1
2
3
4
5
sql复制代码select Person.Name, Orders.OrderNo
from Person
inner join Orders
on Person.Id_P = Orders.Id_P
order by Person.Name

the Results:

Name OrderNo
Winter 33333
Winter 44444
Mars 11111
Mars 22222

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

使用 INFORMATION_SCHEMA.COLUMNS

1
2
3
4
5
6
7
8
9
sql复制代码-- 实体表
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 下的表

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

1
2
3
4
5
sql复制代码-- 实体表
exec serverName.DBName.DBOwner.sp_columns 'B2B_InvoiceNumber_Flag_New'

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

使用 sys.columns

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

1
2
3
4
sql复制代码-- 临时表
select * 
from tempdb.sys.columns 
where object_id=OBJECT_ID('tempdb.dbo.#tempTableName');

查看特定表的信息

1
2
3
sql复制代码select  * 
from    INFORMATION_SCHEMA.tables 
where   TABLE_NAME='Portal_DimList'

插入特殊字符值

1
2
3
4
5
6
7
sql复制代码-- 1. 修改列类型为 'NVARCHAR'
-- 2. 插入数据时，引号前加 "N"

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

修改 text 类型的列为 ntext 类型

1
2
3
4
5
6
7
sql复制代码-- 先修改列的类型为 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

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%