写作主旨
记录一些SQL的基本语法
SQL背景
结构化查询语言,是一种标准,应用于关系型数据库。
不同的数据库管理系统(MySql, SQL Server, Oracel等)有自己的语言扩展,本文主要基于MqSql。
table的操作
table 承载数据,一类数据的集合,对象的抽象结构
create table 创建;
- 一般的创建语法
create table device
(
id bigint default 0 not null comment '设备ID'
primary key,
device_name varchar(64) null comment '设备名称',
parent_id bigint null comment '上级ID',
apply_type bigint null comment '申报类型(1=按需 2=按容量)',
data_index int default 1 null comment '排序',
status int null comment '状态(1=启用,2=禁用)',
remark varchar(256) null comment '描述',
create_time datetime default CURRENT_TIMESTAMP not null,
update_time datetime default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP
);
-
提一下约束
规则,如果存在违反约束的数据行为,行为会被约束终止。
包括:NOT NULL、UNIQUE 、 PRIMARY KEY 、 FOREIGN KEY 、DEFAULT 、CHECK(逻辑语句) 等
-
一些其他的创建table的方法
复制同一个库内其他单元的表结构,这种方法只会复制表结构
create table sql_test.device like ms_energy.ms_device;
使用select的结果创建表,会将select的结果同时插入到创建的表内
create table sql_test.device select * from ms_energy.ms_device limit 100;
- 外键
alter table sql_test.device_point
add constraint device_point_device_id_fk
foreign key (ms_device_id) references sql_test.device (id);
create index 索引;
create index device_ms_device_type_id_index
on sql_test.device (ms_device_type_id);
drop 删除;
drop table sql_test.device;
alter 修改;
修改column的属性
alter table sql_test.device change ms_station_id bigint null comment '站点ID 表结构修改测试';
修改column的名称(不建议)
alter table device change ms_station_id ms_station_id_test bigint null comment '站点ID';
基础SQL语法
基础语法的特点:几乎得到所有数据库管理系统的支持。
select 查询的开始 R;
SELECT * FROM device;
where 筛选的开始;
上面的查询配上筛选条件
SELECT * FROM device WHERE device_code = 222;
and&or 条件的连接;
连接多个查询条件,满足逻辑判断。(这个例子在后面还会再次用到的)
SELECT * FROM device WHERE is_gate_meter != 1 and ms_device_type_id = 1 or ms_device_type_id = 16;
order by 排序;
正序
SELECT * FROM device ORDER BY parent_id;
倒序
SELECT * FROM device ORDER BY parent_id desc;
delete 删除 D;
删除满足指定条件的记录
DELETE FROM sql_test.device WHERE id = 18;
删除所有(不推荐)
DELETE FROM sql_test.device ;
update 更新 U;
根据条件更新
UPDATE sql_test.device t SET t.device_name = '飒飒_测试更逊' WHERE t.id = 75;
insert into 插入 C;
完全的插入数据
INSERT INTO sql_test.device_point (update_time, create_time, status, data_index, unit, data_key, device_id, id) VALUES (DEFAULT, DEFAULT, DEFAULT, DEFAULT, '元', '费用', 3647395324436736, DEFAULT);
有默认值、自增约束的字段可以不用传
INSERT INTO sql_test.device_point ( unit, data_key, device_id) VALUES ( '元', '费用1', 3647395324436736);
INSERT INTO sql_test.device_point ( device_id) VALUES ( 3647395324436736);
复杂SQL语法
复杂的语法在不同的数据库管理系统中的实现方式可能会有区别。
limit 数量限制;
查询第一条记录
select * from device limit 1;
一般会配合order by使用,用来筛选最靠前的记录或用于分页
select * from device order by parent_id limit 1;
like 模糊查询;
in 集合;
SELECT * FROM device WHERE is_gate_meter != 1 and ms_device_type_id in [1, 16];
between 区间;
SELECT * FROM device WHERE is_gate_meter != 1 and ms_device_type_id between 1 and 16;
as 别名;
为device_name字段设置别名为abb
select device_name as abb from device;
join 连接;
Mysql 的多表查询 交集
select device_name, dp.data_key from device d, device_point dp where d.id = dp.device_id;
结果相同的语法 交集
select *, dp.data_key from device d right join device_point dp on d.id = dp.device_id;
以左侧表为主,匹配到右侧表,匹配不到补空
select *, dp.data_key from device d left join device_point dp on d.id = dp.device_id;
union 联合;
两个结果的并集
select device_name from device union select data_key from device_point;
insert into … select 查询结果插入。
参考table操作的介绍序号3
视图
视图是select的结果,总是显示最新的数据。
create view 创建;
创建一张试图
create view device_point_keys as select device_name, dp.data_key from device d, device_point dp where d.id = dp.device_id;
使用试图
select * from device_point_keys;
create or repalce view 更新;
create or replace view device_point_keys as select d.device_name, d.create_time as device_create_time ,dp.data_key, dp.create_time as point_create_time from device d, device_point dp where d.id = dp.device_id;
drop view 删除;
drop view device_point_keys;
数据类型
不同的数据库管理系统支持的数据类型有所不同,但多数情况都差不多。
函数
功能丰富,结合SQL的基础和复杂语法,可以实现强大的功能。
编程
创建一个函数 执行加2操作
CREATE FUNCTION MyF( l INT)
RETURNS INT
BEGIN
DECLARE l2 int;
SET l2 = 2;
RETURN l + l2;
END;
函数的使用
select MyF(2);
删除函数
drop function MyF;