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');