mysql基本案例

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()$