学习笔记10——Mysql的DDL语句

2023-12-13 05:44:20

学习笔记系列开头惯例发布一些寻亲消息

链接:https://baobeihuijia.com/bbhj/contents/3/197161.html
在这里插入图片描述

  • 数据库创建:

    CREATE DATABASE books;
    CREATE DATABASE IF NOT EXISTS books;
    
  • 更改字符集

    ALTER DATABASE books CHARACTER SET gbk;
    
  • 库的删除

    DROP DATABASE IF EXISTS books;
    
  • 表的创建

    CREATE TABLE book(
    	id INT,
    	bName VARCHAR(20),
    	price DOUBLE,
    	authorId INT,
    	publishDate DATETIME
    );
    
  • 表的修改

    # 改列名改列属性
    ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;
    # 改列属性
    ALTER TABLE book MODIFY COLUMN pubDate TIMESTAMP;
    # 改表名字
    ALTER TABLE book RENAME TO books;
    ALTER TABLE book ADD COLUMN pubDate TIMESTAMP 【first/after 字段名】;
    ALTER TABLE book DROP COLUMN pubDate;
    
  • 表的删除

    DROP TABLE IF EXISTS book;
    
  • 表的复制

    # 仅仅复制表的结构
    CREATE TABLE author_copy LIKE author;
    
    # 复制表的结构+数据
    CREATE TABLE author_copy2 SELECT * FROM author;
    
    # 仅仅复制某些字段
    CREATE TABLE copy4
    SELECT id,an_name
    FROM author
    WHERE 0;
    
    # 可以跨库,只要写成 库名.表名
    CREATE TABLE dept2
    SELECT department_id, department_name
    from my_employees.departments;
    
    
  • 常见数据类型

    # 整型 Tinyint/Smallint/Mediumint/Int/integer/bigint
    
    # 默认为有符号,大于范围则插入临界值
    CREATE TABLE tab_int(
    	t1 INT,
    	t2 INT UNSIGNED
    	# 长度不够7用0来填充
    	t3 INT(7) ZEROFILL
    );
    
    # 浮点小数 :MD都可以省略,随着插入的数据改变
    float(M,D):M代表整数+小数部分长度,D代表小数部分长度
    double(M,D)
    
    # 定点小数 :MD都可以省略,M默认为10,D默认为0,精度更高
    dec(M,D)
    
    # 短的字符型 
    char     不可变长,可以省略默认为1  	不可超过最大字符数  效率高
    varchar   可变					  不可超过最大字符数  效率低
    
    # ENUM:枚举,只能选择列表中一个插入
    e1 enum('a','b','c');
    # set :选择列表中一个或者多个插入
    s1 set('a','b','c','d')
    # binary和varbinary 保存较短的二进制
    # 长的字符型 text,blob(长的二进制)
    
    # 日期
    date 1001-01-01
    time 22:22:22
    year 1001
    datetime:1001-01-01 00:00:00,只插入年份会自动给时间
    timestamp:和datetime表示一样,但是会受当前的时区影响,更能反映真实时间
    
    INSERT INTO tab_date VALUES (NOW(),NOW());
    
  • 常见约束

    列级约束
    - NOT NULL
    - DEFAULT:保证字段有默认值
    - PRIMARY KEY:主键,唯一且非空
    - UNIQUE:唯一但是可以为空
    
    CREATE TABLE stuinfo1(
    	id INT PRIMARY KEY,
    	stuName VARCHAR(20) NOT NULL,
    	gender CHAR(1) CHECK(gender='男' OR gender ='女'),
    	age INT DEFAULT 18,
    	majorId INT,
    	seat INT UNIQUE
    );
    
    
    表级约束
    - CHECK:mysql没效果
    - PRIMARY KEY:主键,唯一且非空
    - UNIQUE:唯一但是可以为空
    - FOREIGN KEY:外键,该表格的该字段值来自于主表的关联列的值
    CREATE TABLE stuinfo1(
    	id INT ,
    	stuName VARCHAR(20) ,
    	gender CHAR(1) ,
    	age INT ,
    	majorId INT,
    	
    	CONSTRAINT pk PRIMARY KEY(id),
    	CONSTRAINT uq UNIQUE(seat),
    	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)	
    	
    	# 或者不起名
    	PRIMARY KEY(id),
    	UNIQUE(seat),
    	FOREIGN KEY(majorid) REFERENCES major(id)
    );
    
  • 主键和唯一的区别

  • 外键:

    • 关联列必须是主键/唯一键
    • 插入数据时,先插入主表再插入从表,删除的时候先删除从表再删主表
  • 修改约束

    # 主键的增删只需要一次,不需要每次motify都带着
    # 列级约束
    ALTER TABLE stuinfo1 MODIFY COLUMN id INT PRIMARY KEY;
    ALTER TABLE stuinfo1 MODIFY COLUMN stuName VARCHAR(10) NOT NULL;
    
    # 表级约束
    ALTER TABLE stuinfo1 ADD PRIMARY KEY(seat); # 必须先存在再添加
    ALTER TABLE stuinfo1 ADD UNIQUE(seat); # 必须先存在再添加
    ALTER TABLE stuinfo1 ADD FOREIGN KEY (majorId) REFERENCES major(id);
    ALTER TABLE stuinfo1 ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorId) REFERENCES major(id);
    
  • 删除约束

    ALTER TABLE stuinfo1 MODIFY COLUMN id INT;
    
    # 删除主键和唯一键的名字(主键起名也没有效果),列级约束无法删除主键
    ALTER TABLE stuinfo1 DROP PRIMARY KEY;
    ALTER TABLE stuinfo1 DROP INDEX seat;
    
    # 删除外键
    ALTER TABLE stuinfo1 DROP FOREIGN KEY fk_stuinfo_major;
    
  • 标识列

    # 只有key才能设置标识列、只有有一个、只能是数值类型的
    
    CREATE TABLE tab_identify(
    	id INT PRIMARY KEY AUTO_INCREMENT,
    	NAME VATCHAR(20)
    );
    # 无需手动增加
    INSERT INTO tab_identify values(NULL,'JOIN');
    
    # 所有库都会被修改
    SHOW VARIABLES LIKE '%auto_increment%';
    SET auto_increment_increment = 3;
    # 起始位置可以手动插入
    
    # 增删标识列
    ALTER TABLE tab MOTIFY COLUMN id INT PRIMARY KRY AUTO_INCREMENT;
    ALTER TABLE tab MOTIFY COLUMN id INT;
    
  • 事务

    要么全部执行、要么全部不执行
    # 四大特性:原子性、一致性、隔离性、持久性
    
    # 需要设置自动提交功能为OFF,只有当前事务设置有效
    set autocommit = 0;
    start transaction;
    仅限 sql语句不包含DDL语句
    commit/rollback; 二选一,上述sql只是提交到了内存中,如果要执行那么就commit,如果不执行那就rollback
    
    - 脏读:没有提交
    - 不可重复读:更新
    - 幻读:插入
    
    # 查看隔离级别
    select @@tx_isolation
    set session|global transaction isolation level read committed;
    
    # 四种隔离级别
    read uncommitted:事务尚未提交,库中的数据就已经修改了,当事务rollback的时候,这些临时修改且被读到的数据成为脏数据
    read committed:可以避免脏读(未提交就不会修改),但是该事务commit前后,另一个事务的读取不可重复
    repeatable_read:不管另一个事务是否提交,读到什么就一直是什么,避免不可重复读;但是另一个事务插入行之后,数据还是会变多
    serializable:串行化,另一个事务的修改都会被阻塞
    
    # 设置保存点
    SAVEPOINT a;
    ROLLBACK TO a;
    
  • 视图

    # 只保存了sql语句,没有保存真实的数据
    - 简化sql,不必了解查询细节
    - 保护数据,提高安全性
    
    # 创建
    CREATE VIEW avg_salary
    AS
    SELECT AVG(salary),department_id
    FROM employees
    GROUP BY department_id;
    
    # 使用
    SELECT * FROM avg_salary;
    
    # 修改1
    CREATE OR REPLACE VIEW myv3
    AS
    SELECT AVG(salary),job_id
    FROM employees
    GROUP BY job_id;
    
    # 修改2
    ALTER VIEW myv3
    AS 
    SELECT AVG(salary),job_id
    FROM employees
    GROUP BY job_id;
    
    # 删除
    DROP VIEW myv3;
    
    # 修改,原始表也会修改(要看视图定义方式,有的可以更新有的不能)
    INSERT INTO myv3 VALUES('张飞','qq,com');
    UPDATE myv3 SET NAME='吴京' WHERE email = 'qq,com';
    DELETE FROM myv3 WHERE email = 'qq,com';
    
  • 删除带外键的主表的方式

    - 级联删除,主表内容删除会把从表内容删除
    ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;
    
    - 级联置空
    ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;
    
  • 系统变量

    系统变量:系统提供,属于服务器层面
    - 全局变量服务器每次启动会将全局变量赋初始值,针对所有会话有效,重启无效
        SHOW GLOBAL【SESSION】 VARIABLES;
        SHOW GLOBAL【SESSION】 VARIBLIES LIKE '%char%';
        SELECT @@global|session.系统变量名
        SET global|session.系统变量名 = VALUE;
        SET @@global|session.系统变量名 = VALUE;
    
    	SELECT @@global.tx_isolation;
    	SET @@global.autocommit = 0;
    	
    - 会话变量:仅针对当前会话有效,换一个连接就无效了
    	SHOW SESSION VARIABLES;
    	SELECT @@SESSION.transaction_isolation;
    	SELECT @@SESSION.transaction_isolation = read uncommitted;
    
  • 自定义变量

    - 用户变量:仅当前会话有用,要加@,不需要限定类型
    SET @用户变量名:=值;
    SELECT 字段 INTO @变量名 FROM 表;
    SELECT COUNT(*) INTO @count FROM emplyees;
    # 使用
    SELECT @count;
    
    - 局部变量:作用于begin end中的第一句话,一般不用加@,需要限定类型
    声明: DECLARE 
    赋值:SET/SELECT
    使用:SELECT 
    
    
    SET @m = 1;
    SET @n = 2;
    SELECT @n: = 2;(加冒号)
    SET @sum= @m + @n;
    SELECT @sum;
    
    BEGIN
    DECLARE m INT DEFAULT 1;
    SET m = 2;
    SELECT @m = 3;
    SELECT m;
    END
    
  • 存储过程和函数

    - 提高代码的重用
    - 减少编译次数
    - 减少了逐步与服务器的连接次数
    
    # 创建
    CREATE PROCEDURE 名字(参数)
    BEGIN
    	一组合法的SQL语句
    END
    
    参数模式
    IN:参数可以作为输入
    OUT:参数作为返回值
    INOUT:既需要输入值,又可以返回值
    
    #### IN
    DELIMITER $
    CREATE PROCEDURE myp1(IN beautyName VARCHAR(20))
    BEGIN
    	SELECT bo.*
    	FROM boys bo
    	RIGHT JOIN beauty b
    	ON b.boyfriend_id  = bo.id
    	WHERE b.name = beautyName;
    END $
    
    CALL myp1('柳岩');
    
    #### OUT
    DELIMITER $
    CREATE PROCEDURE myp1(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT)
    BEGIN
    	SELECT bo.boyName, bo.userCP INTO boyName, INTO userCP
    	FROM boys bo
    	RIGHT JOIN beauty b
    	ON b.boyfriend_id  = bo.id
    	WHERE b.name = beautyName;
    END $
    
    SET @boyName$
    CALL myp1('小昭',@boyName)$
    
    select @boyName;
    
    #### INOUT
    SET @a=3;
    SET @b=4;
    
    DELIMITER $
    CREATE PROCEDURE myp1(INOUT a INT,INOUT b INT)
    BEGIN
    	SET a = a*2;
    	SET b = b*2;
    END $
    
    CALL myp1(@a,@b)$
    SELECT @a$
    
    # 删除
    DROP PROCEDURE myp1;
    
    # 查看
    SHOW CREATE PROCEDURE myp1;
    
    # 一般无法修改存储过程
    

    在这里插入图片描述

  • 函数和存储过程的区别

    • 存储过程可以0或多个返回、而函数必须有且仅有一个返回
    # 无参
    DELIMITER $
    CREATE FUNCTION myf1() RETURNS INT
    BEGIN
    	DECLARE c INT DEFAULT 0;
    	SELECT COUNT(*) INTO c
    	FROM employees;
    	RETURN c;
    END $
    SELECT myf1() $
    
    # 有参
    DELIMITER $
    CREATE FUNCTION myf1(empName VARCHAR(20)) RETURNS DOUBLE
    BEGIN
    	SET @SAL=0;
    	SELECT salary INTO @SAL
    	FROM employees
    	WHERE first_name = empName;
    	RETURN @SAL;
    END $
    SELECT myf1('cynthia') $
    
    # 实现两数相加
    DELIMITER $
    CREATE FUNCTION test_fun1(num1 FLOAT, num2 FLOAT) RETURNS FLOAT
    BEGIN
    	DECLARE s FLOAT DEFAULT 0;
    	SET s = num1 + num2;
    	RETURN s;
    END $
    SELECT test_fun1(2.3,1.7)$
    
    # 查看函数
    SHOW CREATE FUNCTION myf1;
    
    # 删除
    DROP FUNCTION myf1;
    
  • 流程控制结构(顺序/分支/循环)

    一、分支
    - if函数:任何位置
    IF(表达式1,表达式2,表达式3);
    
    - case:任何位置
    DELIMITER $
    CREATE PROCEDURE test(IN SCORE INT)
    BEGIN
    	CASE
    	WHEN score>=90 AND score <=100 THEN SELECT 'A';
    	WHEN score>=80 THEN SELECT 'B';
    	WHEN score>=60 THEN SELECT 'C';
    	ELSE SELECT 'D';
    	END CASE;
    END$
    SELECT test(20)$
    
    - if结构:只能放在begin end中
    DELIMITER $
    CREATE FUNCTION test(SCORE INT) RETURNS CHAR
    BEGIN
    	IF score>=90 AND score <=100 THEN RETURN 'A';
    	ELSEIF score>=80 THEN RETURN 'B';
    	ELSEIF score>=60 THEN RETURN 'C';
    	ELSE RETURN 'D';
    	END IF;
    END$
    SELECT test(20)$
    
    二、循环 while/loop/repeat,必须放在begin end之间
    CREATE PROCEDURE pro_while(IN insertCount INT)
    BEGIN
    	DECLARE i INT DEFAULT 1;
    	WHILE i<=insertCount DO
    	INSERT INTO boys VALUES(16,'zhangsan',200);
    	SET i = i+1;
    	END WHILE;
    END $
    CALL pro_while(2);
    
    # 如果添加leave或者iterate,就必须添加名称
    # 添加leave
    CREATE PROCEDURE pro_while(IN insertCount INT)
    BEGIN
    	DECLARE i INT DEFAULT 1;
    	a:WHILE i<=insertCount DO
    		INSERT INTO boys VALUES(16,'zhangsan',200);
    		SET i = i+1;
    		IF i>=20 THEN LEAVE a;
    		END IF;
    	END WHILE a;
    END $
    CALL pro_while(2)$
    
    # 添加iterate
    CREATE PROCEDURE pro_while(IN insertCount INT)
    BEGIN
    	DECLARE i INT DEFAULT 1;
    	a:WHILE i<=insertCount DO
    		IF MOD(i,2)!=0 THEN ITERATE a;
    		END IF;
    		INSERT INTO boys VALUES(16,'zhangsan',200);
    		SET i = i+1;
    	END WHILE a;
    END $
    CALL pro_while(2)$
    

文章来源:https://blog.csdn.net/CZY925323/article/details/134945638
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。