MySQL复习笔记

复习的时候的一些记录（自用）

连接MySQL
    mysql -u root -p;
    SHOW DATABASES;
    USE your_database;
    SHOW TABLES;
    QUIT;

创建数据库
    CREATE DATABASE [IF NOT EXISTS] database_name
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_general_ci;
    mysqladmin -u root -p create database_name;

删除数据库
    DROP DATABASE [IF EXISTS] datase_name;
    mysqladmin -u root -p drop database_name

选择数据库
    USE  database_name;
    mysql -u root -p -D RUNOOB

创建数据表
    CREATE TABLE table_name (
    column1 type.
    column2 type.
        ......
    );
    //实例1
    CREATE TABLE [IF NOT EXISTS] users (
        id INT AUTO_INCREMENT PRIMARY KEY, 
        username VARCHAR(50) NOT NULL,
        email VARCHAR(100) NOT NULL,
        birthdate DATE,
        is_active BOOLEAN DEFAULT TRUE
    );CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

删除数据表
    DROP TABLE  [IF EXISTS] table_name;
    mysqladmin -u root -p drop table_name

插入数据行
    INSERT INTO table_name ( column1,column2,column3,...)
    VALUES( value1,value2,value3,...)； //如果数据是字符型要使用单or双引号
    //实例1
    INSERT INTO users 
    (username, email, birthdate, is_active)
    VALUES 
    'ze', '111@outlook.com', '2000-01-01', true);

查询数据行
    SELETE * FROM table_name
    [WHERE condition]
    ORDER BY column_name [ASC | DESC]]
    LIMIT number];
    //实例
    SELETE user_name,birthday,email FROM users
    where user_name LIKE 'z%' or birthday >= '2000-01-01'
    RDER Y user_name DESC
    LIMIT 20;

WHERE 子句
    //查询语句中你可以使用一个或者多个表，表之间使用逗号, 分割，并使用WHERE语句来设定查询条件。
    //可以在 WHERE 子句中指定任何条件。
    //可以使用 AND 或者 OR 指定一个或多个条件。
    //WHERERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
    //     =  !=  <  >  <=  >= 
    //    AND  OR   LIKE   IN  NOT  
    //    IS  NOLL | IS NOT NULL
    //    BETWEEN AND

修改数据行
    UPDATE table_name
    SET column1 = value1, column2 = value2, column3 = value3,...
    [WHERE condition]
    //实例1
    UPDATE customers
    SET total_purchases = (
           SELECT SUM(amount)
           FROM orders
            WHERE orders.customer_id = customers.customer_id
    ）
    WHERE customer_type = 'Premium';
    //实例2
    UPDATE products
    SET price = price * 1.1
    WHERE category = 'Electronics';

删除数据行
    DELETE FROM table_name
    [WHERE  condition];
    //实例
    DELETE FROM customers
    WHERE customer_id IN (
       SELECT customer_id
      FROM orders
        WHERE order_date < '2023-01-01'
    );
    //实例2（删除所有行）
    DELETE FORM customers;

LIKE关键词
    //LIKE 是在 MySQL 中用于在 WHERE 中进行模糊匹配的关键字。它通常与通配符一起使用，用于搜索符合某种模式的字符串。
    // %：表示任意 0 个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。
    //   _：表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句。
    // [ ]：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。
    // [^] ：表示不在括号所列之内的单个字符。其取值和 [ ] 相同，但它要求所匹配对象为指定字符以外的任一个字符。
    // 可以使用 AND 或者 OR 指定一个或多个条件。
    SELETE * FROM table_name
    Where column_name LIKE pattern;
    //实例1
    SELECT * FROM employees 
    WHERE last_name LIKE 'smi%' 
    COLLATE utf8mb4_general_ci;

UNION关键词
    //MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合，并去除重复的行。
    //UNION 操作符必须由两个或多个 SELECT 语句组成，每个 SELECT 语句的列数和对应位置的数据类型必须相同。
    //UNION 操作符在合并结果集时会去除重复行，而 UNION ALL 不会去除重复行
    SELECT column1, column2, ...  FROM table_name
    WHERE condition1
    UNION
    SELECT column1, column2, ...  FROM table2
    WHERE condition2
    [ORDER BY column1, column2, ...];
    //实例1
    SELECT country FROM Websites
    UNION
    SELECT country FROM apps
    ORDER BY country;
    //实例2
    SELECT country, name FROM Websites
    WHERE country='CN'
    UNION ALL
    SELECT country, app_name FROM apps
    WHERE country='CN'
    ORDER BY country;

排序语句（ORDER BY）
    SELECT * FROM table_name
    ORDER BY column1 [ASC | DESC], column2  [ASC | DESC],    ...;
    //实例1（使用数字表示列的位置）
    SELECT first_name, last_name, salary
    FROM employees
    ORDER BY 3 DESC, 1 ASC;
    //实例2（使用表达式排序）
    SELECT product_name, price * discount_rate AS discounted_price
    FROM products
    ORDER BY discounted_price DESC;
    //实例3（使用 NULLS FIRST 或 NULLS LAST 处理 NULL 值）
    SELECT price, product_name 
    FROM orders
    ORDER BY product_name DESC NULLS LAST;

分组语句（GROUP BY）
    //GROUP BY 语句根据一个或多个列对结果集进行分组。
    //在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
    SELECT column1, column2, aggregate_function(column3)
    FROM TABLE_NAME
    [WHERE condition]
    GROUP BY column1, column2;
    //实例1
    SELECT customer_id, SUM(oder_amout) AS total_amout
    FROM oders    
    WHERE customer_id LIKE '1%'
    GROUP BY total_amout;
    //WITH ROLLUP
    //WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。
    //实例2
    SELECT name, SUM(signin) as signin_count 
    FROM  employee_tbl 
    GROUP BY name WITH ROLLUP;
    +--------+--------------+
    | name   | signin_count |
    +--------+--------------+
    | 小丽 |            2      |
    | 小明 |            7     |
    | 小王 |            7    |
    | NULL   |           16 |
    +--------+--------------+
    //若使用coalesce 来设置一个可以取代 NUll 的名称（IFNULL函数是coalesce函数的MySQL特定版）
    SELECT coalesce(name, '总数'), SUM(signin) AS signin_count 
    FROM  employee_tbl 
    GROUP BY name WITH ROLLUP;
    +--------------------------+--------------+
    | coalesce(name, '总数') | signin_count |
    +--------------------------+--------------+
    | 小丽                   |            2 |
    | 小明                   |            7 |
    | 小王                   |            7 |
    | 总数                   |           16 |
    +--------------------------+--------------+

连接
    //内连接
    SELECT column1, column2, ...
    FROM table_name1
    JION table_name2 ON table_information1 = table_information2
    [WHERE condition];
    //实例1
    SELECT orders.order_id, customers.customer_name, products.product_name
    FROM orders
    INNER JOIN customers ON orders.customer_id = customers.customer_id
    INNER JOIN order_items ON orders.order_id = order_items.order_id
    INNER JOIN products ON order_items.product_id = products.product_id;
    //左连接|右连接: LEFT JOIN 返回左表的所有行，并包括右表中匹配的行，如果右表中没有匹配的行，将返回 NULL 值
    SELETE * FROM table_name1 
    LEFT JOIN table_name2 ON  table_information1 = table_information2
    [WHERE condition];

NULL值处理
    //<=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。
    //关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。
    //NULL 值与任何其它值的比较（即使是 NULL）永远返回 NULL，即 NULL = NULL 返回 NULL

    // IS NULL | IS NOT NULL
    SELETE * FROM table_name WHERE oders_id IS NOT NULL;
    SELETE * FROM table_name WHERE orders_id IS NULL;

    //COALESCE函数（可以设置多个参数，返回参数列表中的第一个非 NULL 值），IFNULL函数（COALESCE的特定版本，它能接受两个参数）
    SELECT COALESCE(column1 , column2, ...)  FROM table_name;
    SELECT IFNULL(column1 , column2) FROM table_name;
    
    //NULL值排序
    SELECT * FROM table_name
    [WHERE condition]
    ORDER BY orders_id [ASC|DESC] [NULLS FRIST | NULLS LAST]

    //注意聚合函数对 NULL 的处理
    //在使用聚合函数（如 COUNT, SUM, AVG）时，它们会忽略 NULL 值，因此可能会得到不同于预期的结果。
    //如果希望将 NULL 视为 0，可以使用 COALESCE 或 IFNULL。
    SELECT SUM(COALESCE(column1)), column2, ... FROM table_name;

正则表达式的使用
    .：匹配任意单个字符。
    ^：匹配字符串的开始。
    $：匹配字符串的结束。
    *：匹配零个或多个前面的元素。
    +：匹配一个或多个前面的元素。
    ?：匹配零个或一个前面的元素。
    [abc]：匹配字符集中的任意一个字符。
    [^abc]：匹配除了字符集中的任意一个字符以外的字符。
    [a-z]：匹配范围内的任意一个小写字母。
    \d：匹配一个数字字符。
    \w：匹配一个字母数字字符（包括下划线）。
    \s：匹配一个空白字符。

    //使用REGEXP | RELIKE关键字（RLIKE 和 REGEXP 可以互换使用，没有区别）
    SELECT column1, column2, ...
    FROM table_name
    WHERE column_name REGEXP 'pattern';

    //查找 name 字段中以 'st' 为开头的所有数据：
    SELECT name FROM person_tbl WHERE name REGEXP '^st';

    //查找 name 字段中以 'ok' 为结尾的所有数据：
    SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

    //查找 name 字段中包含 'mar' 字符串的所有数据：
    SELECT name FROM person_tbl WHERE name REGEXP 'mar';

    //查找 name 字段中以元音字符开头或以 'ok' 字符串结尾的所有数据：
    SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou] | ok$';

    //选择订单表中描述中包含 "item" 后跟一个或多个数字的记录。
    SELECT * FROM orders WHERE order_description REGEXP 'item[0-9]+';

    //使用 BINARY 关键字，使得匹配区分大小写：
    SELECT * FROM products WHERE product_name REGEXP BINARY 'apple';

    //使用 OR 进行多个匹配条件，以下将选择姓氏为 "Smith" 或 "Johnson" 的员工记录：
    SELECT * FROM employees WHERE last_name REGEXP 'Smith|Johnson';
    
事务
    //BEGIN或者START TRANSACTION开启一个事务
    //COMMIT提交事务
    //ROLLBACK回滚事务
    //SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT
    //RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常
    //ROLLBACK TO SAVEPOINT identifier 把事务回滚到标记点
    START TRANSACTION;
    UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
    //判断是否要提交还是回滚
    IF (条件) THEN
        COMMIT; 
    ELSE
        ROLLBACK;
    END IF;

ALTER*
    //字段是列的别称
    //添加列（如果你需要指定新增列的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 列名（设定位于某列之后）
    //FIRST 和 AFTER 关键字可用于 ADD 与 MODIFY ，如果你想重置数据表列的位置就需要先使用 DROP 删除列然后使用 ADD 来添加列并设置位置
    ALTER TABLE table_name 
    ADD COLUMN column_name datatype;
    //实例1
    ALTER TABLE oders ADD i INT FIRST
    ALTER TABLE oders ADD i INT AFTER c;
    //修改列的数据类型（当你修改字段时，你可以指定是否包含值或者是否设置默认值）
    ALTER TABLE table_name
    MODIFY COLIMN column_name datatype;
    //指定列 j 为 NOT NULL 且默认值为 1(如果你不设置默认值，MySQL会自动设置该字段默认为 NULL)
    ALTER TABLE oders
    MODIFY COLUMN j INT NOT NULL DEFAULT 1; 
    //修改列的默认值
    ALTER TABLE oders ALTER i SET DEFAULT 1000;
    //删除列的默认值
    ALTER TABLE oders ALTER i DROP DEFAULT;
    //修改列名,并且可以同时修改数据类型
    ALTER TABLE table_name
    CHANGE COLUMN column_name_old column_name_new datatype; 
    //删除列
    ALTER TABLE table_name 
    DROP COLUMN column_name;
    //增加PRIMARY KEY
    ALTER TABLE table_name
    ADD PRIMARY KET (column_name);
    //增加FOREIGN KEY
    ALTER TABLE table_name
    ADD CONSTRAINT foreignkey_name
    FOREIGN KEY (column_name)
    REFERENCES parent_table (column_name);
    //修改表名
    ALTER TABLE table_name_old
    RENAME TO table_name_new;
    //修改数据表类型(将表 oders 的类型修改为 MYISAM)
    ALTER TABLE oders ENGINE = MYISAM;
    
AUTO_INVREMENT(自增序列)
    //因为一张数据表只能有一个字段自增主键，MySQLAUTO_INCREMENT 来模拟序列的行为。
    //通常 AUTO_INCREMENT 属性用于指定表中某一列的自增性。
    //当你插入一行数据时，可以不指定 自增列的值，数据库会自动为其分配一个唯一的、自增的值
    //使用 AUTO_INCREMENT 属性的列只能是整数类型（通常是 INT 或 BIGINT）
    //可以使用 LAST_INSERT_ID() 函数来获取刚刚插入的行的自增值 : SELECT LAST_INSERT_ID();
    CREATE TABLE ODERS(
    id INT AUTO_INVREMENT PRIMARY KET,
    name VARCHAR(50)
    );
    //重置序列：若删除了数据表中的多条记录，并要对剩下数据的 AUTO_INCREMENT 列进行重新排列，可以通过删除自增的列，然后重新添加来实现。 
    ALTER TABLE insect DROP id;
    ALTER TABLE insect
        ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
        ADD PRIMARY KEY (id);
    //设置序列的开始值：般情况下序列的开始值为 1，但如果你需要指定一个开始值时（如100）：
    CREATE TABLE orders(
        id INT UNSIGNED NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (id),
        name VARCHAR(30) NOT NULL, 
      )engine=innodb auto_increment=100 charset=utf8;
    //表创建成功后，也可以通过以下语句来实现：
    ALTER TABLE t AUTO_INCREMENT = 100r;