SQL语句示例
1、创建表
CREATE TABLE `demo_department` (
`department_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '部门ID',
`parent_dept_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '父部门ID',
`department_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '名称',
`wbs_code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'WBS编码',
`enable` tinyint(4) NULL DEFAULT NULL COMMENT '是否启用:1-启用，0-禁用',
`remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
`create_time` datetime NOT NULL COMMENT '创建时间',
`create_user_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建用户ID',
`update_time` datetime NULL DEFAULT NULL COMMENT '修改时间',
`update_user_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '修改用户ID',
`tenant_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '所属租户编码'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '演示-部门' ROW_FORMAT = Compact;CREATE TABLE `demo_user` (
`user_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户ID',
`department_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '部门ID',
`user_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户姓名',
`birthday` date NULL DEFAULT NULL COMMENT '生日',
`age` int(11) NULL DEFAULT NULL COMMENT '年龄',
`weight` decimal(10, 2) NULL DEFAULT NULL COMMENT '体重',
`enable` tinyint(1) NULL DEFAULT NULL COMMENT '是否启用',
`remark` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '备注',
`create_time` datetime NOT NULL COMMENT '创建时间',
`create_user_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建用户ID',
`update_time` datetime NULL DEFAULT NULL COMMENT '修改时间',
`update_user_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '修改用户ID',
`tenant_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '所属租户编码',
PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '演示-用户表' ROW_FORMAT = Compact;create table demo_user_bak1(
select * from demo_user
);
create table demo_user_bak2(
select u.user_id, u.user_name from demo_user u
)2、查询数据
select * from demo_user;
select user_id, user_name, birthday, age, weight from demo_user;
-- 别名
select user_id, user_name from demo_user;
select user_id as myUserId, user_name from demo_user;
select u.user_id, u.user_name from demo_user as u;
INSERT INTO `demo`.`demo_user`(`user_id`, `department_id`, `user_name`, `birthday`, `age`, `weight`, `enable`, `remark`, `create_time`, `create_user_id`, `update_time`, `update_user_id`, `tenant_code`) VALUES ('c000000', NULL, 'testUserName1', '2010-01-01', 1, 1.00, 1, 'remark1', '2020-06-15 10:17:41', 'test_user_id', '2020-06-15 10:17:42', 'test_user_id', 'test_tenant_code');
INSERT INTO `demo`.`demo_user`(`user_id`, `department_id`, `user_name`, `birthday`, `age`, `weight`, `enable`, `remark`, `create_time`, `create_user_id`, `update_time`, `update_user_id`, `tenant_code`) VALUES ('c000001', NULL, 'testUserName2', '2010-01-01', 2, 2.00, 1, 'remark2', '2020-06-15 10:17:41', 'test_user_id', '2020-06-15 10:17:42', 'test_user_id', 'test_tenant_code');
INSERT INTO `demo`.`demo_user`(`user_id`, `department_id`, `user_name`, `birthday`, `age`, `weight`, `enable`, `remark`, `create_time`, `create_user_id`, `update_time`, `update_user_id`, `tenant_code`) VALUES ('c000002', NULL, 'testUserName3', '2010-01-01', 3, 3.00, 1, 'remark3', '2020-06-15 10:17:41', 'test_user_id', '2020-06-15 10:17:42', 'test_user_id', 'test_tenant_code');
select user_name, birthday, age, weight from demo_user;
select distinct user_name, birthday, age, weight from demo_user;
INSERT INTO `demo`.`demo_user`(`user_id`, `department_id`, `user_name`, `birthday`, `age`, `weight`, `enable`, `remark`, `create_time`, `create_user_id`, `update_time`, `update_user_id`, `tenant_code`) VALUES ('c1000000', NULL, 'testUserName1-1', '2012-01-01', 1, 1.00, 1, 'remark1', '2020-06-15 10:17:41', 'test_user_id', '2020-06-15 10:17:42', 'test_user_id', 'test_tenant_code');
select * from demo_user u where u.user_name = 'testUserName1';
select * from demo_user u where u.user_name like '%testUserName1%';
select * from demo_user u where u.birthday > '2000-01-01';
select * from demo_user u where u.age > 20;
select * from demo_user u where u.weight > 66.6;
INSERT INTO `demo`.`demo_user`(`user_id`, `department_id`, `user_name`, `birthday`, `age`, `weight`, `enable`, `remark`, `create_time`, `create_user_id`, `update_time`, `update_user_id`, `tenant_code`) VALUES ('c2000000', NULL, 'cestUserNamc1-1', '2012-01-01', 1, 1.00, 1, 'remark1', '2020-06-15 10:17:41', 'test_user_id', '2020-06-15 10:17:42', 'test_user_id', 'test_tenant_code');
INSERT INTO `demo`.`demo_user`(`user_id`, `department_id`, `user_name`, `birthday`, `age`, `weight`, `enable`, `remark`, `create_time`, `create_user_id`, `update_time`, `update_user_id`, `tenant_code`) VALUES ('d2000000', NULL, 'cestUserNamd1-1', '2012-01-01', 1, 1.00, 1, 'remark1', '2020-06-15 10:17:41', 'test_user_id', '2020-06-15 10:17:42', 'test_user_id', 'test_tenant_code');
select * from demo_user u where u.user_name like '%User%';
select * from demo_user u where u.user_name like 'testUserName_';
select * from demo_user u where u.user_name like '[t]%';
select * from demo_user u where u.user_name like '[^t]%';
select * from demo_user u where u.user_name in ('testUserName1','testUserName2');
select * from demo_user u where u.age in (1,2,3);
select * from demo_user u where u.department_id is null;
select * from demo_user u where u.department_id is not null;
select * from demo_user u where u.user_name = 'testUserName1' and u.age = 2;
select * from demo_user u where u.user_name = 'testUserName1' or u.age = 2;
select * from demo_user u order by u.user_name;
select * from demo_user u order by u.user_name asc;
select * from demo_user u order by u.user_name desc;
select * from demo_user u order by u.user_name, u.age desc;
select * from demo_user u order by u.user_name desc, u.age desc;
select top 1 * from demo_user u;
select * from demo_user u limit 1;
select * from demo_user u rownum 1;
select count(u.department_id) as count from demo_user u;
select count(1) as count from demo_user u;
select u.age, max(u.weight) as maxWeight, min(u.weight) as minWeight, avg(u.weight) as avgWeight, sum(u.weight) as sumWeight
from demo_user u
group by u.age;
select u.age, max(u.weight) as maxWeight, min(u.weight) as minWeight, avg(u.weight) as avgWeight, sum(u.weight) as sumWeight
from demo_user u
group by u.age
having max(u.weight) > 2;
select d.department_id, d.department_name from demo_department d;
select u.user_id, u.department_id, u.user_name from demo_user u;
select d.department_id, d.department_name, u.user_id, u.user_name
from demo_department d, demo_user u;
select d.department_id, d.department_name, u.user_id, u.user_name
from demo_department d, demo_user u
where d.department_id = u.department_id;
select d.department_id, d.department_name, u.user_id, u.user_name
from demo_department d
inner join demo_user u on d.department_id = u.department_id;
select d.department_id, d.department_name, u.user_id, u.user_name
from demo_department d
left join demo_user u on d.department_id = u.department_id;
select d.department_id, d.department_name, u.user_id, u.user_name
from demo_department d
right join demo_user u on d.department_id = u.department_id;
select d.department_id, d.department_name, u.user_id, u.user_name
from demo_department d
full join demo_user u on d.department_id = u.department_id;
select u.user_name, t.* from (
select u.age, max(u.weight) as maxWeight, min(u.weight) as minWeight, avg(u.weight) as avgWeight, sum(u.weight) as sumWeight
from demo_user u
group by u.age
) t join demo_user u on u.age = t.age;
select
u.user_name,
(select department_name from demo_department d where d.department_id = u.department_id) as department_name
from demo_user u;
select * from demo_user u
where exists (
select 1
from demo_department d
where d.department_id = u.department_id
)
select * from demo_user u
where u.department_id in (
select d.department_id
from demo_department d
)
select u.* from demo_user u
join demo_department d on d.department_id = u.department_id
select * from demo_user u
where u.department_id = (
select d.department_id from demo_department d limit 1
)
select u.age, max(u.weight) as maxWeight, min(u.weight) as minWeight, avg(u.weight) as avgWeight, sum(u.weight) as sumWeight
from demo_user u
group by u.age
having max(u.weight) > (
select avg(u.weight) from demo_user u
);
select d.department_id as id, d.department_name as text
from demo_department d
union
select u.user_id, u.user_name
from demo_user u;
select
u.user_name,
case u.age
when 1 then '青年'
when 2 then '中年'
when 3 then '老年'
else 'error' end ageText
from demo_user u;
select
u.user_name,
case
when u.age <= 2 then '青年'
when u.age = 2 then '中年'
when u.age >= 3 then '老年'
end ageText
from demo_user u;
select u.user_name, isnull(u.department_id, '无') as departmentId from demo_user u;
select u.user_name, nvl(u.department_id, '无') as departmentId from demo_user u;
select u.user_name, ifnull(u.department_id, '无') as departmentId from demo_user u;
select concat('【', u.user_name,'】') as userName from demo_user u;
select '【' + u.user_name + '】' as userName from demo_user u;
select concat(concat('【', u.user_name), '】') as userName from demo_user u;
select '【' || u.user_name || '】' as userName from demo_user u;
select concat('【', u.user_name,'】') as userName from demo_user u;
INSERT INTO `demo_user` VALUES ('000000', '00000001', 'testUserName1', '2010-01-01', 1, 1.00, 1, 'remark1', '2020-06-15 10:17:41', 'test_user_id', '2020-06-15 10:17:42', 'test_user_id', 'test_tenant_code');
INSERT INTO `demo_user`(`user_id`, `department_id`, `user_name`, `birthday`, `age`, `weight`, `enable`, `remark`, `create_time`, `create_user_id`, `update_time`, `update_user_id`, `tenant_code`) VALUES ('000000', '00000001', 'testUserName1', '2010-01-01', 1, 1.00, 1, 'remark1', '2020-06-15 10:17:41', 'test_user_id', '2020-06-15 10:17:42', 'test_user_id', 'test_tenant_code');
INSERT INTO `demo_user`
SELECT `user_id`, `department_id`, `user_name`, `birthday`, `age`, `weight`, `enable`, `remark`, `create_time`, `create_user_id`, `update_time`, `update_user_id`, `tenant_code` FROM `demo_user_bak`
select * into demo_user_bak1
from demo_user;
select u.user_id, u.user_name into demo_user_bak2
from demo_user u;3、更新数据
UPDATE `demo`.`demo_user`
SET `department_id` = '00000001',
`user_name` = 'testUserName1',
`birthday` = '2010-01-01',
`age` = 1,
`weight` = 1.00,
`enable` = 1,
`remark` = 'remark1',
`create_time` = '2020-06-15 10:17:41',
`create_user_id` = 'test_user_id',
`update_time` = '2020-06-15 10:17:42',
`update_user_id` = 'test_user_id',
`tenant_code` = 'test_tenant_code'
WHERE
`user_id` = '000000';
UPDATE test1
SET
test1.col1= test2.col1,
test1.col2 = test2.col2
FROM
test1
INNER JOIN test2 ON test1.id = test2.id;
UPDATE test1
SET
( test1.col1, test1.col2 ) =
( SELECT test2.col1, test2.col2 FROM test2 WHERE test2.id = test1.id );
UPDATE test1, test2
SET
test1.col1 = test2.col1,
test1.col2 = test2.col2
WHERE
test1.id = test2.id;
UPDATE test1
SET
col1 = ( SELECT col1 FROM test2 WHERE test2.id = test1.id ),
col2 = ( SELECT col2 FROM test2 WHERE test2.id = test1.id )
delete from demo_user where user_id = '1';
delete from demo_user where user_id in ('1','2');