mysql常见笔试题及答案_java笔试题大全带答案

【举例：】
select @@transaction_isolation; -- 用于查看当前数据库的隔离级别（8.0版本）
 set session transaction isolation level read committed; --用于设置隔离级别为 read committed

代码100分

代码100分【举例：】
员工与部门间的关系。
一个部门可以有多个员工，而一个员工属于一个部门。此时部门与员工间为 一对多 的关系。
部门表为主表，员工表为从表。外键建立在 员工表（从表）上。

CREATE TABLE dept (
    -- 此处的 primary key 为 列级约束。
    deptId int primary key auto_increment,
    deptName varchar(20) not null
);

CREATE TABLE emp (
    id int primary key auto_increment,
    name varchar(32),
    age int,
    deptId int,
    -- 此处的 foreign key 为表级约束。
    foreign key(deptId) references dept(deptId)
);

drop table 表名;   -- 用于删除数据表。
truncate table 表名;  -- 用于删除数据表的数据，但保留表结构。
delete from 表名 [where 条件];  -- 用于删除数据标的数据，但保留表结构，可回滚。

代码100分delete 可以添加删除条件，truncate 不可以。
delete 删除后可以回滚，truncate 不可以。
delete 效率较低，truncate 效率较高。
delete 可以返回受影响的行数，truncate 没有返回值。
delete 删除数据后再次插入数据时，标识列从断点处开始，truncate 标识列从 1 开始。

show variables like "autocommit"; -- 用于查看自动提交功能是否打开
set autocommit=1; -- 用于打开自动提交功能
set autocommit=0; -- 用于关闭自动提交功能

【显式事务步骤：】
Step1：开启事务，关闭自动提交功能。
    set autocommit=0;
    
Step2：编写事务语句。
    select、insert、delete、update。
    SAVEPOINT A;  -- 可以设置回滚点
    
Step3：结束事务。
    commit; -- 提交事务
    rollback; -- 回滚事务
    rollback to A;  -- 回滚到回滚点

【创建视图】
    CREATE VIEW 视图名
    AS
    查询语句;
    
【使用视图（与使用普通表类似）】
    SELECT *
    FROM 视图名

【举例：】
    CREATE VIEW testView
    AS
    SELECT * 
    FROM DEPT;
    
    SELECT *
    FROM testView;
    
【修改视图：（方式一）】
    -- 若视图存在则修改，若视图不存在则创建
    CREATE OR REPLACE VIEW 视图名
    AS
    查询语句;
    
【修改视图：（方式二）】
    ALTER VIEW 视图名
    AS
    查询语句;
    
【删除视图：】
    drop view 视图名;

【查看当前所有的变量：】
    show [global | session] variables;

【查看部分变量：】
    show [global | session] variables like "%transaction%";

【查看具体的变量：】
     select @@[global | session].变量名;
 
 【设置具体的变量】
     set [global | session] 变量名 = 值;
     或者
     set @@[global | session].变量名;

========================用户变量===========================
【声明用户变量并赋值：（三种方式）】
    set @变量名=值;
    set @变量名:=值;
    select @变量名:=值;

【赋值给用户变量：（通过select into）】
    select 字段 into @变量名
    from 表;

【查看用户变量值：】
select @变量名;

【举例：】
    select count(*) into @count
    from dept;
    
    select @count;
    
========================局部变量===========================
【声明局部变量：】
    declare 变量名 类型;
    declare 变量名 类型 default 值;

【局部变量赋值：（先声明再赋值，直接赋值会出错）】
    set 变量名=值;
    set 变量名:=值;
    
    select 字段 into 变量名
    from 表;
    
【查看用户变量值：】
    select 变量名;

【创建存储过程：】
    DELIMITER $
    CREATE PROCEDURE 存储过程名(参数列表)
    BEGIN
        存储过程体（一组合法的 sql 语句）
    END $
    DELIMITER ;
注：
    参数列表分三个部分，分别为 参数模式、参数名、参数类型
    参数模式：IN、OUT、INOUT。
        IN：指该参数可以作为输入，即接收值（默认）。
        OUT：指该参数可以作为输出，即返回值。
       INOUT：指该参数即可作为输入、又可作为输出。
   
    存储过程体中每条语句必须以分号 ; 结尾。
    DELIMITER 用于设置结束标记，用于存储过程末尾，执行到标记处则存储过程结束。
    
【调用存储过程：】
    CALL 存储过程名(参数列表);
    
【删除存储过程：】
    DROP PROCEDURE 存储过程名;

【查看存储过程结构：】
    SHOW CREATE PROCEDURE 存储过程名;

# 创建一个 user 表，若已经存在该表，先删除
DROP TABLE IF EXISTS user;
CREATE TABLE user(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    password VARCHAR(20)
);

# 用于设置结束标记，此处 sql 结束标记有 ; 改为 $
DELIMITER $

# 创建存储过程(无参)，用于向 user 表中插入 5 个数据
DROP PROCEDURE IF EXISTS user_no_parameter $
CREATE PROCEDURE user_no_parameter()
BEGIN
    INSERT INTO USER(name, password) VALUES("tom", "tom123"),("jarry", "jarry123"),("jack", "jack123"),("tim", "tim123"),("rose", "rose123");
END $

# 创建有参存储过程，根据输入的用户名，找到相应的密码，并返回该用户的 id。
DROP PROCEDURE IF EXISTS user_parameter $
CREATE PROCEDURE user_parameter(IN name VARCHAR(20), OUT password VARCHAR(20), INOUT id INT)
BEGIN
    SELECT user.password, user.id INTO password, id
    FROM user
    WHERE user.name = name;
END $

# 用于设置结束标记，此处 sql 结束标记有 $ 改为 ;
DELIMITER ;

# 调用无参存储过程
CALL user_no_parameter();

# 查看当前表数据
SELECT * FROM user;

# 调用有参存储过程
set @id = 0;
CALL user_parameter("jack", @password, @id);

# 查看有参存储过程执行后的结果
SELECT @password, @id;

【创建函数：】
    DELIMITER $
    CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
    BEGIN
        函数体（一组合法的 sql 语句）
    END $
    DELIMITER ;
注：
    参数列表分两个部分，分别为 参数名、参数类型。
    函数体必须包含 return 语句。
    
【调用函数：】
    SELECT 函数名(参数列表);
    
【查看函数：】
    DROP FUNCTION 函数名;

【删除函数：】
    SHOW CREATE FUNCTION 函数名;

# 用于设置结束标记，此处 sql 结束标记有 ; 改为 $
DELIMITER $

# 创建无参函数，若函数已存在，则先删除再创建
DROP FUNCTION IF EXISTS test1 $
CREATE FUNCTION test1() RETURNS INT
BEGIN
    DECLARE a INT DEFAULT 10;
    DECLARE b INT DEFAULT 10;
    return  a + b;
END $

# 创建有参函数，若函数已存在，则先删除再创建
DROP FUNCTION IF EXISTS test2 $
CREATE FUNCTION test2(a INT, b INT) RETURNS INT
BEGIN
    RETURN a - b;
END $

# 用于设置结束标记，此处 sql 结束标记有 $ 改为 ;
DELIMITER ;

# 调用无参函数
SELECT test1();

# 调用有参函数
SELECT test2(20, 10);

【错误：】
    ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

【解决：】
    SET GLOBAL log_bin_trust_function_creators = 1;

【语法1：(IF 函数)】
    IF(表达式1, 结果1, 结果2);
注：
    表达式 1 成立，则返回 结果1.
    表达式 1 不成立，则返回 结果2.
    
【语法2：需要写在 BEGIN - END 中】
    BEGIN
        IF 表达式 1 THEN 结果 1;
        ELSEIF 表达式 2 THEN 结果 2;
        ELSE 结果 3;
        END IF;
    END
    
【举例：】
    DELIMITER $
    DROP FUNCTION IF EXISTS test3 $
    CREATE FUNCTION test3() RETURNS INT
    BEGIN
        IF 2 > 3 THEN RETURN 3;
        ELSEIF 2 > 4 THEN RETURN 4;
        ELSE RETURN 5;
        END IF;    
    END $
    DELIMITER ;
    
    SELECT test3() value1, IF(2 > 3, 2, 3) value2;

【语法1：相当于 Java 中的 switch 语句】
    CASE 表达式 | 变量
    WHEN 值 1 THEN 返回的结果 1 
    WHEN 值 2 THEN 返回的结果 2
    ELSE 返回的结果 3
    END
    
【语法2：相当于 Java 中的 IF - ELSE 语句】
    CASE 
    WHEN 表达式 1 THEN 返回的结果 1
    WHEN 表达式 2 THEN 返回的结果 2
    ELSE 返回的结果 3
    END
    
【举例：】
SELECT (
    CASE 2 + 3
    WHEN 2 THEN 2
    WHEN 3 THEN 3
    ELSE 4
    END
) value1,
(
    CASE 
    WHEN 2 > 3 THEN 2
    WHEN 2 < 3 THEN 3
    ELSE 4
    END
) value2;

【分类：需要放在 BEGIN - END 里】
    while、loop、repeat

【循环标志：】
    iterate： 类似于 Java 中的 continue，结束本次循环，进行下一次循环。
    leave： 类似于 Java 中的 break，结束当前所有的循环。
    
【while 语法：（先判断再执行循环）】
BEGIN
   [标签：] WHILE 循环条件 DO
        循环体;
    END WHILE [标签];
END
    
【loop 语法：（没有条件的死循环，需使用 leave 退出）】
BEGIN
   [标签：] LOOP
        循环体;
    END LOOP [标签];
END
    
【repeat 语法：（先执行循环再判断）】
BEGIN
   [标签：] REPEAT
        循环体;
    UNTIL 结束循环的条件
    END REPEAT [标签];
END

【举例：】
DELIMITER $

DROP PROCEDURE IF EXISTS test1 $
CREATE PROCEDURE test1(OUT a INT, OUT b INT, OUT c INT)
BEGIN
    # 测试 while 循环，temp >= 10 时退出循环。
    DECLARE temp INT DEFAULT 0;
    testWhile: WHILE temp < 10 DO
        SET temp = temp + 1;
        # 当 temp = 8 时，给 a 赋值并退出 while 循环
        IF temp = 8 THEN SET a = temp; LEAVE testWhile;
        END IF;
    END WHILE testWhile;
    
    # 测试 repeat 循环， temp >= 10 时退出循环。
    SET temp = 0;
    testRepeat: REPEAT 
        SET temp = temp + 1;
        # 当 temp = 7 时，给 b 赋值并退出 repeat 循环
        IF temp = 7 THEN SET b = temp; LEAVE testRepeat;
        END IF;
        # 注意 until 是循环结束条件
        UNTIL temp >= 10
    END REPEAT testRepeat;
    
    # 测试 loop 循环
    SET temp = 0;
    testLoop: LOOP
        SET temp = temp + 1;
        # 当 temp = 6 时，给 c 赋值并退出 loop 循环
        IF temp = 6 THEN SET c = temp; LEAVE testLoop;
        END IF;
    END LOOP testLoop;
END $ 

DELIMITER ;

CALL test1(@a, @b, @c);

SELECT @a, @b, @c;