use test;
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for `employee_tbl`
-- ----------------------------
-- DROP TABLE IF EXISTS `employee_tbl`;
CREATE TABLE `employee_tbl` (
`id` int(11) NOT NULL,
`name` char(10) NOT NULL DEFAULT '',
`date` datetime NOT NULL,
`singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `employee_tbl`
-- ----------------------------
BEGIN;
INSERT INTO `employee_tbl` VALUES ('1', '小明', '2016-04-22 15:25:33', '1'), ('2', '小王', '2016-04-20 15:25:47', '3'), ('3', '小丽', '2016-04-19 15:26:02', '2'), ('4', '小王', '2016-04-07 15:26:14', '4'), ('5', '小明', '2016-04-11 15:26:40', '4'), ('6', '小明', '2016-04-04 15:26:54', '2');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
SELECT DISTINCT concat
(id,',',name) FROM employee_tbl GROUP BY id,name;
SELECT DISTINCT concat
(name) FROM employee_tbl GROUP BY name;
SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;
SELECT
CONCAT( COLUMN_NAME, ',' )
FROM
information_schema.COLUMNS
WHERE
table_name = 'employee_tbl'
AND table_schema = 'test';
SELECT CASE
WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) >= 0
AND TIMESTAMPDIFF(YEAR, birthday, CURDATE()) <= 17 THEN '其他'
WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) >= 18
AND TIMESTAMPDIFF(YEAR, birthday, CURDATE()) <= 39 THEN '18-39岁'
WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) >= 40
AND TIMESTAMPDIFF(YEAR, birthday, CURDATE()) <= 59 THEN '40-59岁'
WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) >= 60
AND TIMESTAMPDIFF(YEAR, birthday, CURDATE()) <= 74 THEN '60-74岁'
WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) >= 75
AND TIMESTAMPDIFF(YEAR, birthday, CURDATE()) <= 89 THEN '75-89岁'
WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) >= 90 THEN '90岁以上'
END AS type, count(1) AS number
FROM USER u WHERE u.tenant_id in(1,2)
GROUP BY type;
SELECT name from employee_tbl WHERE singin>3 and id=4;
SELECT name from employee_tbl WHERE singin>=3 and singin<5;
SELECT name from employee_tbl WHERE singin in (1,2);
SELECT name from employee_tbl WHERE singin NOT in (1,2);
select * from employee_tbl ORDER BY singin desc , name asc;
SELECT sum(singin) FROM employee_tbl;
SELECT MAX(DISTINCT singin) FROM employee_tbl;
SELECT min(DISTINCT singin) FROM employee_tbl;
SELECT avg(singin) FROM employee_tbl;
SELECT LENGTH(name) as username FROM employee_tbl;
select SUBSTR("sdafff" FROM 2 FOR 3);
SELECT ROUND(-1.2);
SELECT mod(10,-3);
SELECT 10%3;
select CONCAT(curdate()," ", curtime()) as datetime;
SELECT DATE_FORMAT(date,'%Y年%m月%d日') datetime FROM employee_tbl;
SELECT if(10<5,'da','xiao');
SELECT COUNT(singin) FROM employee_tbl;
SELECT COUNT(DISTINCT singin) FROM employee_tbl;
select MAX(singin)-MIN(singin) as number FROM employee_tbl;
select MAX(singin),id FROM employee_tbl GROUP BY id;
SELECT COUNT(*), shen_id FROM employee_tbl GROUP BY shen_id HAVING shen_id IS NOT null;
SELECT COUNT(*) FROM shen GROUP BY LENGTH(name);
select MAX(singin),AVG(singin),SUM(singin) FROM employee_tbl GROUP BY id ORDER BY id;
SELECT NAME
FROM
employee_tbl shen;
-- 内连接
SELECT e.id, e.name, e.date, e.singin, s.id, s.name as a FROM employee_tbl as e INNER JOIN shen as s on e.shen_id=s.id ;
-- 左连接
SELECT e.id, e.name, e.date, e.singin, s.id, s.name as a FROM employee_tbl as e LEFT JOIN shen as s on e.shen_id=s.id ;
SELECT e.id, e.name, e.date, e.singin, s.id, s.name as a FROM employee_tbl as e LEFT JOIN shen as s on e.shen_id=s.id WHERE s.id is NULL ;
-- 右连接
SELECT e.id, e.name, e.date, e.singin, s.id, s.name as a FROM employee_tbl as e RIGHT JOIN shen as s on e.shen_id=s.id ;
-- 交叉连接
SELECT e.*,s.* FROM
employee_tbl e cross JOIN shen s;
-- 子查询
SELECT * from employee_tbl WHERE shen_id in (SELECT id FROM shen WHERE name="影");
select e.id,e.name, sh.id,sh.name from employee_tbl as e left join (select id,name from shen) as sh on e.shen_id=sh.id;
-- 仅仅复制表的结构
create table copy like employee_tbl;
-- 复制表的结构和数据
create table copy2
select * from employee_tbl;
select * from copy2;
use test
-- 添加外键
ALTER TABLE copy ADD foreign key(shen_id) references shen(id) ;
ALTER TABLE copy drop foreign key shen_id ;
use test
-- 开启事务 先禁用自带提交功能
set autocommit=0;
start transaction;
INSERT INTO copy (id,name,date,singin,shen_id)VALUES(4, '34', '2021-09-28 16:30:03', 02, 12);
-- 回滚
ROLLBACK;
-- 提交
COMMIT;
-- 查看事务隔离级别
SELECT @@tx_isolation;
-- 查询全局事务隔离级别
SELECT @@global.tx_isolation;
-- 查询会话事务隔离级别
SELECT @@session.tx_isolation;
-- 设置read uncommitted级别：
set session transaction isolation level read uncommitted;
-- 设置read committed级别：
set session transaction isolation level read committed;
-- 设置repeatable read级别：
set session transaction isolation level repeatable read;
-- 设置serializable级别：
set session transaction isolation level serializable;
-- savepoint a 保存点，设置节点
-- rollback to a 回滚到保存点
-- 视图 sql 逻辑
select e.id,e.name,e.date,s.id as d,s.name,s.sex FROM employee_tbl as e INNER JOIN shen as s ON e.shen_id=s.id;
-- 创建视图就是保存查询sql逻辑
create VIEW v1
as
select e.id,e.name,e.date,s.id as d,s.name as n,s.sex FROM employee_tbl as e INNER JOIN shen as s ON e.shen_id=s.id;
-- 查询视图
SELECT * FROM v1;
-- 视图更新
-- 方式一 create or replace view 视图名 as 查询语句
create OR replace view v1 as SELECT id, name, date FROM employee_tbl;
SELECT * FROM v1;
-- 方式二 alter view 视图名 as 查询语句
alter view v1 as SELECT id, name, date FROM employee_tbl;
-- 删除是视图名 drop view 视图名，视图名。。。
drop view v1;
-- 查看视图
show CREATE view v1;
desc v1;
-- 系统变量：全局变量、会话变量
-- 自定义变量： 用户变量、局部变量
SHOW global VARIABLES;
show session variables;
show global VARIABLES like '%char%';
-- 查看指定的某个系统变量的值 select @@global | session .系统变量
-- 为某个系统变量赋值 set @@global | session .系统变量名=值
select @@tx_isolation;
-- 用户变量等于会话变量 声明病初始化 = 或 :=
-- set @用户变量名=值; select @用户变量名:=值或；
set @name='john';
-- 赋值（更新用户变量的值）
-- 方式一 通过set或select
-- 方式二 通过 select 字段 into 变量名 from 表；
-- 查看变量名 select @name;
select @john;
-- 局部变量
-- 作用域：仅仅定义在他的begin end中有效
-- DECLARE 变量名 类型 default 值
-- DECLARE 变量名 类型
-- 存储过程和函数 1、提高代码的重用性 2、简化操作 存储过程即函数
-- 存储过程 一组预先编译好的sql语句的集合，理解成批处理语句
-- 1、提高代码重用性 2、简化代码操作 3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
-- 1、创建语法
-- create PROCEDURE 存储过程名（参数列表）方法体
BEGIN
-- 存储过程体（一组合法的sql语句）
END
-- 参数模式
-- IN：该参数可以作为输入，也就是说该参数需要调用方传入值
-- OUT：该参数可以作为输出，也就是说该参数可以作为返回值
-- INOUT：该参数既可以作为输入又可以作为输出
-- 如果存储过程只有一句话，BEGIN END 可以省略
in name VARCHAR(20)
-- delimiter 接收标记
delimiter $
-- 2、调用语法
-- CALL 存储过程名（实参列表）
delimiter $
create PROCEDURE myv1()
BEGIN
INSERT INTO test_1(name)VALUES("温迪"),("钟离"),("影");
END $
-- 调用
CALL myv1;
-- 测试in
delimiter $
CREATE PROCEDURE myv2(in names VARCHAR(255))
BEGIN
select * FROM test_1 WHERE name=names;
END $
CALL myv2("影")$;
delimiter $
CREATE PROCEDURE myv6(in names VARCHAR(255))
BEGIN
-- 声明并初始化
DECLARE id int ;
DECLARE result VARCHAR(255) DEFAULT ' ';
select * FROM test_1 WHERE name=names;
SELECT id,result;
END $
CALL myv5("影")$;
-- 测试out
delimiter $
create procedure myv9(in id int(11),out name VARCHAR(255))
BEGIN
SELECT name into name FROM test_1
where id=id;
END $
set @bName$
CALL myv9(2,@d)
select @d;
-- 删除存储过程
DROP PROCEDURE myv1;
DROP PROCEDURE myv8;
-- 查看存储过程
SHOW CREATE PROCEDURE myv3;
-- 函数 区别：有且仅有一个返回
create FUNCTION myv11() RETURNs INT(11)
BEGIN
DECLARE count INT(11) DEFAULT 0;
SELECT count(*) INTO count FROM test_1;
return count;
END
SELECT myv11()$
