MySQL基本用法



  • MySQL 基本命令

    创建数据库

    • 示例

    创建一个名为 swift_mic_test,且 Character Set 为 utf8,Collation为 utf8_general_ci 的数据库。

    CREATE DATABASE swift_mic_test DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
    

    创建表

    • 示例
    CREATE TABLE t_sys_user(
    	id BIGINT AUTO_INCREMENT PRIMARY KEY,
    	username VARCHAR(10) NOT NULL COMMENT '用户昵称',
    	gender INT NULL COMMENT '性别(1: 男, 2: 女)',
    	age INT NULL COMMENT '年龄'
    );
    

    删除表

    • 格式
    DROP TABLE <表名>
    
    • 示例
    DROP TABLE t_sys_user;
    

    插入数据(INSERT)

    • 格式
    INSERT INTO <表名>(<filed-name1>...) VALUES(<value1>...)
    
    • 示例
    INSERT INTO t_sys_user(username, gender, age) VALUES('John', '1', 20);
    INSERT INTO t_sys_user(username, gender, age) VALUES('Cary', '1', 24);
    INSERT INTO t_sys_user(username, gender, age) VALUES('LilyW', '2', 18);
    INSERT INTO t_sys_user(username, gender, age) VALUES('LilyH', '2', 30);
    

    查询数据(SELECT)

    • 示例
    -- 查询数据(所有 t_sys_user 表所有数据)
    SELECT * FROM t_sys_user;
    -- 根据条件查询某一个用户的所有信息
    SELECT * FROM t_sys_user WHERE username = 'John';
    -- 根据条件查询某一个用户的指定信息(username, age)
    SELECT username, age FROM t_sys_user WHERE username = 'John';
    -- 模糊查询(所有Lily开头的用户昵称)
    SELECT username, age FROM t_sys_user WHERE username LIKE 'Lily%';
    

    更新数据(UPDATE)

    • 格式
    UPDATE <表名> SET <filed1>=<new-value1>,<filed2>=<new-value2>... [WHERE 语句]
    
    • 示例

    将 username 为 LilyH 的用户的 age 修改为 31。

    UPDATE t_sys_user SET age = 31 WHERE username = 'LilyH'
    

    删除表中数据(DELETE)

    • 格式
    DELETE FROM <表名> [where 语句]
    
    • 示例

    删除 t_sys_user 表中 username 为 Cary 的数据。

    DELETE FROM t_sys_user WHERE username = 'Cary';
    

    排序(ORDER BY)

    -- 排序(根据年龄降序排列)
    SELECT * FROM t_sys_user ORDER BY age DESC;
    -- 排序(根据年龄升序排列)
    SELECT * FROM t_sys_user ORDER BY age ASC;
    

    分组(GROUP BY)

    • 构造测试数据
    -- 创建用户登录信息表
    CREATE TABLE t_sys_user_sigin_info(
    	id BIGINT AUTO_INCREMENT PRIMARY KEY,
    	user_id BIGINT NOT NULL COMMENT '用户id',
    	signin_time DATETIME COMMENT '登录时间',
    	signin_terminal VARCHAR(10)	COMMENT '登录终端'
    );
    
    -- 插入测试数据
    INSERT INTO t_sys_user_sigin_info(user_id, signin_time, signin_terminal) VALUES(1, '2019-09-10 15:43:22', 'ios');
    INSERT INTO t_sys_user_sigin_info(user_id, signin_time, signin_terminal) VALUES(1, '2019-09-11 10:00:01', 'ios');
    INSERT INTO t_sys_user_sigin_info(user_id, signin_time, signin_terminal) VALUES(1, '2019-09-12 11:50:00', 'web');
    INSERT INTO t_sys_user_sigin_info(user_id, signin_time, signin_terminal) VALUES(3, '2019-09-12 11:20:00', 'android');
    INSERT INTO t_sys_user_sigin_info(user_id, signin_time, signin_terminal) VALUES(3, '2019-09-13 14:40:05', 'android');
    INSERT INTO t_sys_user_sigin_info(user_id, signin_time, signin_terminal) VALUES(4, '2019-09-10 13:43:08', 'android');
    

    测试数据如下

    id user_id signin_time signin_terminal
    1 1 2019-09-10 15:43:22 ios
    2 1 2019-09-11 10:00:01 ios
    3 1 2019-09-12 11:50:00 web
    4 3 2019-09-12 11:20:00 android
    5 3 2019-09-13 14:40:05 android
    6 4 2019-09-10 13:43:08 android
    • 示例

    查询 user_id 为 1 的用户的登录次数

    SELECT user_id, count(*) AS signin_count FROM t_sys_user_sigin_info WHERE user_id = 1 GROUP BY user_id;
    

    查询结果如下

    user_id signin_count
    1 3

    JOIN

    INNER JOIN

    获取两个表中字段匹配关系的数据记录。

    当前 t_sys_user 表数据如下

    id username gender age
    1 John 1 20
    3 LilyW 2 18
    4 LilyH 2 31

    当前 t_sys_user_sigin_info 表数据如下

    id user_id signin_time signin_terminal
    1 1 2019-09-10 15:43:22 ios
    2 1 2019-09-11 10:00:01 ios
    3 1 2019-09-12 11:50:00 web
    4 3 2019-09-12 11:20:00 android
    5 3 2019-09-13 14:40:05 android
    6 4 2019-09-10 13:43:08 android
    • 示例

    查询 t_sys_user 和 t_sys_user_sigin_info 表中具有相同 user_id 的记录

    SELECT a.id AS user_id, a.username, a.gender, a.age, b.signin_time, b.signin_terminal FROM t_sys_user a INNER JOIN t_sys_user_sigin_info b ON a.id = b.user_id;
    

    查询结果如下

    user_id username gender age signin_time signin_terminal
    1 John 1 20 2019-09-10 15:43:22 ios
    1 John 1 20 2019-09-11 10:00:01 ios
    1 John 1 20 2019-09-12 11:50:00 web
    3 LilyW 2 18 2019-09-12 11:20:00 android
    3 LilyW 2 18 2019-09-13 14:40:05 android
    4 LilyH 2 31 2019-09-10 13:43:08 android

    LEFT JOIN

    获取左表所有记录,即使右表没有对应匹配的记录。

    • 构造测试数据
    INSERT INTO t_sys_user(username, gender, age) VALUES('Han', '1', 50);
    
    • 示例
    SELECT a.id AS user_id, a.username, a.gender, a.age, b.signin_time, b.signin_terminal FROM t_sys_user a LEFT JOIN t_sys_user_sigin_info b ON a.id = b.user_id;
    

    查询结果如下

    user_id username gender age signin_time signin_terminal
    1 John 1 20 2019-09-10 15:43:22 ios
    1 John 1 20 2019-09-11 10:00:01 ios
    1 John 1 20 2019-09-12 11:50:00 web
    3 LilyW 2 18 2019-09-12 11:20:00 android
    3 LilyW 2 18 2019-09-13 14:40:05 android
    4 LilyH 2 31 2019-09-10 13:43:08 android
    5 Han 1 50 NULL NULL

    RIGHT JOIN

    与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录。

    • 构造测试数据
    INSERT INTO t_sys_user_sigin_info(user_id, signin_time, signin_terminal) VALUES(6, '2019-09-16 11:20:43', 'web');
    
    • 示例
    SELECT a.id AS user_id, a.username, a.gender, a.age, b.signin_time, b.signin_terminal FROM t_sys_user a RIGHT JOIN t_sys_user_sigin_info b ON a.id = b.user_id;
    

    查询结果如下

    user_id username gender age signin_time signin_terminal
    1 John 1 20 2019-09-10 15:43:22 ios
    1 John 1 20 2019-09-11 10:00:01 ios
    1 John 1 20 2019-09-12 11:50:00 web
    3 LilyW 2 18 2019-09-12 11:20:00 android
    3 LilyW 2 18 2019-09-13 14:40:05 android
    4 LilyH 2 31 2019-09-10 13:43:08 android
    NULL NULL NULL NULL 2019-09-16 11:20:43 web

    事务

    事务必须满足 4 个条件(ACID)

    1. 原子性(Atomicity)
    2. 一致性(Consistency)
    3. 隔离性(Isolation)
    4. 持久性(Durability)
    
    • 示例

    COMMIT:提交事务,所有修改将成为永久性的。
    ROLLBACK:回滚事务,撤销所有未提交的修改。

    -- 事务(commit)
    BEGIN;
    INSERT INTO t_sys_user(username, gender, age) VALUES('Emily', '2', 29);
    INSERT INTO t_sys_user(username, gender, age) VALUES('kiyo', '2', 25);
    INSERT INTO t_sys_user(username, gender, age) VALUES('Luorita', '2', 38);
    COMMIT;
    

    COMMIT 后,t_sys_user 表将新增 3 条记录。

    -- 事务(rollback)
    BEGIN;
    INSERT INTO t_sys_user(username, gender, age) VALUES('Emily2', '2', 29);
    INSERT INTO t_sys_user(username, gender, age) VALUES('kiyo2', '2', 25);
    INSERT INTO t_sys_user(username, gender, age) VALUES('Luorita2', '2', 38);
    ROLLBACK;
    

    ROLLBACK 后,t_sys_user 表将不发生任何变化。

    ALTER

    添加表字段

    • 格式
    ALTER TABLE <表名> ADD <字段名> <字段类型>;
    
    • 示例

    t_sys_user_sigin_info 表增加 sign_country 字段,类型为 VARCHAR(20)。

    ALTER TABLE t_sys_user_sigin_info ADD sign_country VARCHAR(20);
    

    修改表字段类型

    • 格式
    ALTER TABLE <表名> MODIFY <字段名> <字段类型>;
    
    • 示例

    将 t_sys_user_sigin_info 表 sign_country 字段类型修改为 INT 类型。

    ALTER TABLE t_sys_user_sigin_info MODIFY sign_country INT;
    

    修改表字段类型及名称

    • 格式
    ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新字段类型>;
    
    • 示例

    将 t_sys_user_sigin_info 表中 sign_country 字段名更改为 sign_country_new。

    ALTER TABLE t_sys_user_sigin_info CHANGE sign_country sign_country_new INT;
    

    修改表名

    • 格式
    ALTER TABLE <旧表名> RENAME TO <新表名>;
    
    • 示例

    将 t_sys_user_sigin_info 表名修改为 t_sys_user_sigin_info_new。

    ALTER TABLE t_sys_user_sigin_info RENAME TO t_sys_user_sigin_info_new;
    

    删除表字段

    • 格式
    ALTER TABLE <表名> DROP <字段名>;
    
    • 示例

    删除 t_sys_user_sigin_info_new 表中的 sign_country_new 字段。

    ALTER TABLE t_sys_user_sigin_info_new DROP sign_country_new;
    

    索引

    合适的索引可大大加速查询速度,但过多的索引一定程度上会降低数据库的写入速度。

    普通索引

    • 格式
    ALTER TABLE <表名> ADD INDEX <索引名>(<字段名>...)
    
    • 示例

    给 t_sys_user 表中的 username 字段增加普通索引。

    ALTER TABLE t_sys_user ADD INDEX normal_index_username(username);
    

    查询 t_sys_user 表索引结构。

    mysql> SHOW INDEX FROM t_sys_user;
    +------------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | Table      | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
    +------------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | t_sys_user |          0 | PRIMARY               |            1 | id          | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
    | t_sys_user |          1 | normal_index_username |            1 | username    | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
    +------------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    2 rows in set (0.00 sec)
    

    删除索引

    • 示例

    删除 t_sys_user 表中的 normal_index_username 索引。

    DROP INDEX normal_index_username ON t_sys_user;
    

    查询 t_sys_user 表索引结构。

    mysql> SHOW INDEX FROM t_sys_user;
    +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
    +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | t_sys_user |          0 | PRIMARY  |            1 | id          | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
    +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    1 row in set (0.00 sec)
    

    唯一索引

    • 格式
    ALTER TABLE <表名> ADD UNIQUE(<字段名>);
    
    • 示例

    给 t_sys_user 表中的 username 字段增加 唯一索引。

    ALTER TABLE t_sys_user ADD UNIQUE(username);
    

    查询 t_sys_user 表索引结构。

    mysql> SHOW INDEX FROM t_sys_user;
    +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
    +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | t_sys_user |          0 | PRIMARY  |            1 | id          | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
    | t_sys_user |          0 | username |            1 | username    | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
    +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    2 rows in set (0.01 sec)
    

    相关

    更多讯息,可关注微信公众号:SwiftMic

    微信公众号推广Logo_min.png


Log in to reply