常用sql语句总结

发表于2023-10-24|更新于2023-10-24

|阅读量:

DDL数据库定义语言

创建数据库

create database db1

如果不存在就创建

create database if not exist db1

查看所有数据库

show databases

查看某个数据库的定义信息

show create database db1

修改数据库字符

alter create database db1 character set utf-8

删除数据库

drop database b1

查看表结构

desc b1

展示创建表的sql语句

show create table b1

修改表名

alter table b1 rname to b2

添加列

alter table b1 add 列名 数据类型

删除列

alter table b1 drop 列名

删除表

drop table b1

如果纯在就删除表

drop table if exist b1

DML数据操作语言

插入所有列名数据

insert into b1（列名1，列名2，···列名n）values（值1，值2，···值n）/ insert into b1 values （值1，值2，···值n）

插入部分列名数据

insert into b1（列名1，列名2）values（值1，值2）

删除表中数据

delete from b1 where 列名 = 值

删除表中所有数据

delete from b1

删除表中所有数据（高效，先删除表，在创建一个空表）

truncate table b1；

不带条件修改

update b1 set 列名 = 值

带条件修改

update b1 set 列名 = 值 where 列名 = 值

DQL数据查询语言

查询表中数据

select * from b1

查询表中部分数据

select 列名1，列名2 from b1

带条件查询数据

select * from b1 where 列名 = 值

排序查询order by

select * from b1 order by 列名（默认升序，加desc降序）

聚合函数

avg max min sum count

分组查询group by

select gender ，avg（score）from student group by gender 按照性别进行分组，查询男女同学的平均分

select gender ，avg（score），count（id）from student group by gender按照性别进行分组，查询男女同学的平均分和人数

分页查询limit 开始索引，条数

select * from b1 limit 0，3

select * from b1 limit 3，3

select * from b1 limit 6，3

子查询：查询中嵌套查询

select student_name, ( select AVG(score) from grades where grades.student_id = students.student_id ) from students 查询每个学生的平均分数。

select * from students where age = （select max（age） from students）查询年龄最高的人的信息

连接查询

select * from b1 left join b2 on 条件

select * from b1 right join b2 on 条件

举例：

select customers.customer_name, orders.order_id, orders.order_date
from customers left join orders on customers.customer_id = orders.customer_id;

查询每个用户以及他的订单

查看数据库是哪个版本

1
select version()

重构项目往往涉及到数据库表的迁移，而迁移又往往会涉及到表结构的变化，如下是润华项目重构中一些迁移数据库的示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
表变动数据迁移sql脚本

INSERT INTO `t_regulation` (`id`, `type`, `name`, `specs`, `gross_weight`, `net_weight`, `del`, `create_time`, `update_time`)
SELECT
    `id`,
    `type`,
    `name`,
    `marker` AS `specs`,
    `gross_weight` * 1000 AS `gross_weight`,  -- 将单位从kg转换为g
    `net_weight` * 1000 AS `net_weight`,      -- 将单位从kg转换为g
    `is_del` AS `del`,
    `create_time`,
    `update_time`
FROM `t_foil_specs`;



UPDATE alt_copper_0411.t_plan_material pm
JOIN t_get_material gm ON pm.plan_no = gm.plan_no AND pm.material_id = gm.material_id
SET
    pm.get_weight = gm.get_weight,
    pm.one_weight = gm.one_weight,
    pm.two_weight = gm.two_weight
WHERE
    pm.plan_no = gm.plan_no AND pm.material_id = gm.material_id;




-- 迁移数据
INSERT INTO alt_copper_0411.t_in_out_produce_apply (
    id,
    type,
    plan_no,
    cargo_type,
    product_name,
    marker,
    serial_number,
    number,
    weight,
    workshop,
    business_unit,
    material_id,
    material_type,
    submitter,
    user,
    status,
    del,
    create_time,
    update_time
)
SELECT
    id,
    CASE
        WHEN type = 7 THEN
            CASE
                WHEN material_type = 1 THEN 3  -- 成品 -> 成品生产入库
                WHEN material_type = 2 THEN 4  -- 半成品 -> 半成品生产入库
                WHEN material_type = 4 THEN 5  -- 废料 -> 废料生产入库
                ELSE 7 -- 其他保持原值，假设没有其他值
            END
        WHEN type IN (5, 6) THEN type - 4  -- 将 type = 5 映射为 1，将 type = 6 映射为 2
        ELSE type  -- 保持原值
    END AS type,
    order_form_no AS plan_no,
    name AS cargo_type,  -- 将 name 映射到 cargo_type
    product_name,
    marker,
    serial_number,
    number,
    weight,
    workshop,
    business_unit,
    material_id,
    material_type,
    submitter,
    user,
    status,
    is_del AS del,
    create_time,
    update_time
FROM
    t_inbound_and_outbound_records
WHERE
    type IN (5, 6, 7);




-- 迁移数据从新表到旧表
INSERT INTO copper.t_into_warehouse (
    id,
    record_id, 
    order_form_no, 
    plan_no, 
    product_id, 
    type, 
    serial_number, 
    product_name, 
    name, 
    marker, 
    number, 
    gross_weight, 
    weight, 
    coil_core,  -- 映射到旧表的 coil_core
    roll_weight,  -- 映射到旧表的 roll_weight
    core_volume_weight,  -- 映射到旧表的 core_volume_weight
    tolerance,  -- 映射到旧表的 tolerance
    hardness, 
    erichsen, 
    state, 
    whether_complete,  -- 将 whether_complete 从 1/2 转换为 0/1
    is_can_add, 
    is_new, 
    business_unit, 
    user, 
    registrant, 
    is_notice, 
    register_time, 
    is_print, 
    del,  -- 映射到旧表的 del
    create_time, 
    update_time
)
SELECT
    id,
    record_id, 
    order_form_no, 
    plan_no, 
    product_id, 
    type, 
    serial_number, 
    product_name, 
    name, 
    marker, 
    number, 
    gross_weight, 
    weight, 
    core_volume AS coil_core,  -- 将新表 core_volume 映射到旧表 coil_core
    core_weight AS roll_weight,  -- 将新表 core_weight 映射到旧表 roll_weight
    core_volume_weight,  -- 保持一致
    public_errand AS tolerance,  -- 将新表 public_errand 映射到旧表 tolerance
    hardness, 
    erichsen, 
    state, 
    CASE WHEN whether_complete = 1 THEN 0 ELSE 1 END AS whether_complete, -- 将 whether_complete 从 1/2 转换为 0/1
    is_can_add, 
    is_new, 
    business_unit, 
    user, 
    registrant, 
    is_notice, 
    register_time, 
    is_print, 
    is_del AS del,  -- 将新表 is_del 映射到旧表 del
    create_time, 
    update_time
FROM t_into_warehouse_1;



-- 迁移数据从新表到旧表
INSERT INTO copper.t_records_warehouse (
    id,
    type,
    record_type,
    records_id,
    warehouse_id,
    update_warehouse_id,
    weight,
    number,
    whether_complete,  -- 将 whether_complete 从 1/2 转换为 0/1
    warehouse_status,
    del,  -- 映射到旧表的 del
    create_time,
    update_time
)
SELECT
    id,
    1 AS type,  -- 新表中没有对应的 type 字段，设置为 NULL 或默认值
    record_type,
    records_id,
    warehouse_id,
    update_warehouse_id,
    weight,
    number,
    CASE WHEN whether_complete = 1 THEN 0 ELSE 1 END AS whether_complete,  -- 将 whether_complete 从 1/2 转换为 0/1
    warehouse_status,
    is_del AS del,  -- 将新表 is_del 映射到旧表 del
    create_time,
    update_time
FROM t_records_warehouse_1;

UPDATE copper.t_records_warehouse
SET type = CASE
               WHEN record_type IS NULL THEN 5  -- 如果 record_type 是 NULL，则设置 type 为 5
               WHEN record_type IN (5, 6, 7) THEN type  -- 如果 record_type 是 5, 6, 7，则保持原值
               ELSE 4  -- 如果 record_type 不是 5, 6, 7，则设置为 4
           END;