Mysql之SQL语句初级用法

前言
本文通过简单的示例去了解Mysql的DDL、DML、DCL的语句用法。

一、DDL语句

DDL（Data Definition Language）语句： 数据定义语言，主要是进行定义/改变表的结构、数据类型、表之间的链接等操作。常用的语句关键字有 CREATE、DROP、ALTER 等。

  • 获取帮助信息
mysql> HELP CREATE TABLE;
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [IGNORE | REPLACE]
    [AS] query_expression
...
  • 创建数据库
mysql> CREATE DATABASE [IF NOT EXISTS] 'db_name'; 
mysql> SHOW DATABASES;

mysql> CREATE DATABASE IF NOT EXISTS dbtest;
Query OK, 1 row affected (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| dbtest             |
| mysql              |
| performance_schema |
| sys                |
| test               |
| testdb             |
+--------------------+
7 rows in set (0.00 sec)
  • 创建表
CREATE TABLE [IF NOT EXISTS] 'tbl_name' (col1 type1, col2 type2, ...) 
				col type1 
				PRIMARY KEY(col1,...)
				INDEX(col1, ...)
				UNIQUE KEY(col1, ...)
  • 创建一个表，id为主键

UNSIGNED:数值专用修饰符，表示无符号，不能表示负数，仅用于表示正数。

mysql> CREATE TABLE students (id int UNSIGNED NOT NULL primary key,name VARCHAR(20) NOT NULL,age tinyint UNSIGNED);
Query OK, 0 rows affected (0.02 sec)

mysql> DESC students;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | int(10) unsigned    | NO   | PRI | NULL    |       |
| name  | varchar(20)         | NO   |     | NULL    |       |
| age   | tinyint(3) unsigned | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
  • 创建tbl2表，把id和name定义成主键
mysql> CREATE TABLE tbl2 (id int UNSIGNED NOT NULL,name VARCHAR(20) NOT NULL,age tinyint UNSIGNED,PRIMARY KEY(id,name));
Query OK, 0 rows affected (0.01 sec)

mysql> DESC tbl2;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | int(10) unsigned    | NO   | PRI | NULL    |       |
| name  | varchar(20)         | NO   | PRI | NULL    |       |
| age   | tinyint(3) unsigned | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
  • 查看创建表时的命令
mysql> SHOW CREATE TABLE students;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                   |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
  `id` int(10) unsigned NOT NULL,
  `name` varchar(20) NOT NULL,
  `age` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
  • 查看表的状态
mysql> SHOW TABLE STATUS LIKE 'students';
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name     | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| students | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2024-03-03 21:47:36 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS LIKE 'students'\G
*************************** 1. row ***************************
           Name: students
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2024-03-03 21:47:36
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)
  • 删除表
    DROP TABLE [IF EXISTS] 'tbl_name';
  • 修改表

查看帮助信息

mysql> HELP ALTER TABLE;
Name: 'ALTER TABLE'
Description:
Syntax:
ALTER TABLE tbl_name
    [alter_specification [, alter_specification] ...]
    [partition_options]

alter_specification:
    table_options
  | ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | ADD [COLUMN] (col_name column_definition,...)
  | ADD {INDEX|KEY} [index_name]
        [index_type] (key_part,...) [index_option] ...
  | ADD {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name]
        (key_part,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]] PRIMARY KEY
        [index_type] (key_part,...)
        [index_option] ...

 ...

ALTER TABLE 'tbl_name' 
  字段：
	 添加字段：add
		ADD col1 data_type [FIRST|AFTER col_name]
	 删除字段：drop
	 修改字段：alter, change, modify
  索引:
	 添加索引：add
	 删除索引: drop
  表选项
	 修改:
  • 为students增加字段gender(性别)为枚举类型，放在最后
mysql> ALTER TABLE students ADD gender ENUM('m','f');
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC students;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| id     | int(10) unsigned    | NO   | PRI | NULL    |       |
| name   | varchar(20)         | NO   |     | NULL    |       |
| age    | tinyint(3) unsigned | YES  |     | NULL    |       |
| gender | enum('m','f')       | YES  |     | NULL    |       |
+--------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
  • 修改students表的id字段名称为sid，修改名称一定要定义字段属性否则会使用默认值
mysql> ALTER TABLE students CHANGE id sid int UNSIGNED NOT NULL;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC students;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| sid    | int(10) unsigned    | NO   | PRI | NULL    |       |
| name   | varchar(20)         | NO   |     | NULL    |       |
| age    | tinyint(3) unsigned | YES  |     | NULL    |       |
| gender | enum('m','f')       | YES  |     | NULL    |       |
+--------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
  • 修改name字段为唯一键
mysql>  ALTER TABLE students ADD UNIQUE KEY(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC students;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| sid    | int(10) unsigned    | NO   | PRI | NULL    |       |
| name   | varchar(20)         | NO   | UNI | NULL    |       |
| age    | tinyint(3) unsigned | YES  |     | NULL    |       |
| gender | enum('m','f')       | YES  |     | NULL    |       |
+--------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
  • 添加索引
mysql> ALTER TABLE students ADD  INDEX(age);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC students;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| sid    | int(10) unsigned    | NO   | PRI | NULL    |       |
| name   | varchar(20)         | NO   | UNI | NULL    |       |
| age    | tinyint(3) unsigned | YES  | MUL | NULL    |       |
| gender | enum('m','f')       | YES  |     | NULL    |       |
+--------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
  • 查看表上的索引
    students表中有三个索引
  • 主键：sid
  • 唯一键：name
  • 普通索引：age
mysql> SHOW INDEXES FROM students;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students |          0 | PRIMARY  |            1 | sid         | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| students |          0 | name     |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| students |          1 | age      |            1 | age         | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
  • 删除索引
mysql> ALTER TABLE students DROP age;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEXES FROM students;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students |          0 | PRIMARY  |            1 | sid         | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| students |          0 | name     |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)
  • 创建索引可以在表中定义也可以单独创建
创建索引：
  CREATE INDEX index_name ON tbl_name (index_col_name,...);
删除索引：
  DROP INDEX index_name ON tbl_name;

mysql> DROP INDEX name ON students;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

二、DML语句

DML（Data Manipulation Language）语句: 数据操纵语言，主要是对数据进行增加、删除、修改操作。常用的语句关键字有 INSERT、UPDATE、DELETE 等。
INSERT(REPLACE)、DELETE、UPDATE、SELECT

  • 查看帮助信息
mysql> HELP INSERT;
Name: 'INSERT'
Description:
Syntax:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    {VALUES | VALUE} (value_list) [, (value_list)] ...
    [ON DUPLICATE KEY UPDATE assignment_list]
...
  • 向表中插入数据
mysql> INSERT INTO students VALUES (1,'Yang Guo','m'),(2,'Guo xiang','f');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM students;
+-----+-----------+--------+
| sid | name      | gender |
+-----+-----------+--------+
|   1 | Yang Guo  | m      |
|   2 | Guo xiang | f      |
+-----+-----------+--------+
2 rows in set (0.00 sec)

mysql> INSERT INTO students (sid,name) VALUES (3,'ZHANG Wuji'),(4,'Zhao Min');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM students;                                                                                                                           
+-----+------------+--------+
| sid | name       | gender |
+-----+------------+--------+
|   1 | Yang Guo   | m      |
|   2 | Guo xiang  | f      |
|   3 | ZHANG Wuji | NULL   |
|   4 | Zhao Min   | NULL   |
+-----+------------+--------+
4 rows in set (0.00 sec)
  • 查询数据通过条件
mysql> mysql> SELECT * FROM students WHERE sid<3;
+-----+-----------+--------+
| sid | name      | gender |
+-----+-----------+--------+
|   1 | Yang Guo  | m      |
|   2 | Guo xiang | f      |
+-----+-----------+--------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM students WHERE gender='m';
+-----+----------+--------+
| sid | name     | gender |
+-----+----------+--------+
|   1 | Yang Guo | m      |
+-----+----------+--------+
1 row in set (0.00 sec)

mysql> SELECT * FROM students WHERE gender='';
Empty set (0.00 sec)

mysql> SELECT * FROM students WHERE gender IS NOT NULL;
+-----+-----------+--------+
| sid | name      | gender |
+-----+-----------+--------+
|   1 | Yang Guo  | m      |
|   2 | Guo xiang | f      |
+-----+-----------+--------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM students WHERE gender IS  NULL;
+-----+------------+--------+
| sid | name       | gender |
+-----+------------+--------+
|   3 | ZHANG Wuji | NULL   |
|   4 | Zhao Min   | NULL   |
+-----+------------+--------+
2 rows in set (0.00 sec)
  • 查找数据并排序
mysql> SELECT * FROM students ORDER BY name;
+-----+------------+--------+
| sid | name       | gender |
+-----+------------+--------+
|   2 | Guo xiang  | f      |
|   1 | Yang Guo   | m      |
|   3 | ZHANG Wuji | NULL   |
|   4 | Zhao Min   | NULL   |
+-----+------------+--------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM students ORDER BY name DESC;
+-----+------------+--------+
| sid | name       | gender |
+-----+------------+--------+
|   4 | Zhao Min   | NULL   |
|   3 | ZHANG Wuji | NULL   |
|   1 | Yang Guo   | m      |
|   2 | Guo xiang  | f      |
+-----+------------+--------+
4 rows in set (0.00 sec)
  • 查找数据并限制数量
mysql> SELECT * FROM students;
+-----+------------+--------+
| sid | name       | gender |
+-----+------------+--------+
|   1 | Yang Guo   | m      |
|   2 | Guo xiang  | f      |
|   3 | ZHANG Wuji | NULL   |
|   4 | Zhao Min   | NULL   |
+-----+------------+--------+
4 rows in set (0.00 sec)
  • 查找2个
mysql> SELECT * FROM students LIMIT 2;
+-----+-----------+--------+
| sid | name      | gender |
+-----+-----------+--------+
|   1 | Yang Guo  | m      |
|   2 | Guo xiang | f      |
+-----+-----------+--------+
2 rows in set (0.00 sec)
  • 1,2 把第一个略过，查找2个
mysql> SELECT * FROM students   LIMIT 1,2;
+-----+------------+--------+
| sid | name       | gender |
+-----+------------+--------+
|   2 | Guo xiang  | f      |
|   3 | ZHANG Wuji | NULL   |
+-----+------------+--------+
2 rows in set (0.00 sec)
  • 查询大于等于2小于等于4
mysql> SELECT * FROM students WHERE sid>=2 and sid<=4;
+-----+------------+--------+
| sid | name       | gender |
+-----+------------+--------+
|   2 | Guo xiang  | f      |
|   3 | ZHANG Wuji | NULL   |
|   4 | Zhao Min   | NULL   |
+-----+------------+--------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM students WHERE sid BETWEEN 2 AND 4;
+-----+------------+--------+
| sid | name       | gender |
+-----+------------+--------+
|   2 | Guo xiang  | f      |
|   3 | ZHANG Wuji | NULL   |
|   4 | Zhao Min   | NULL   |
+-----+------------+--------+
3 rows in set (0.00 sec)
  • Like查找数据
mysql> SELECT * FROM students WHERE name LIKE 'Z%';
+-----+------------+--------+
| sid | name       | gender |
+-----+------------+--------+
|   3 | ZHANG Wuji | NULL   |
|   4 | Zhao Min   | NULL   |
+-----+------------+--------+
2 rows in set (0.00 sec)
  • 基于正则表达式模式匹配
mysql> SELECT * FROM students WHERE name RLIKE '.*u.*';
+-----+------------+--------+
| sid | name       | gender |
+-----+------------+--------+
|   1 | Yang Guo   | m      |
|   2 | Guo xiang  | f      |
|   3 | ZHANG Wuji | NULL   |
+-----+------------+--------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM students WHERE name RLIKE '.*[A-G]u.*';
+-----+-----------+--------+
| sid | name      | gender |
+-----+-----------+--------+
|   1 | Yang Guo  | m      |
|   2 | Guo xiang | f      |
+-----+-----------+--------+
2 rows in set (0.00 sec)
  • 字段别名
mysql> SELECT sid as stuid,name as stuname FROM students;
+-------+------------+
| stuid | stuname    |
+-------+------------+
|     1 | Yang Guo   |
|     2 | Guo xiang  |
|     3 | ZHANG Wuji |
|     4 | Zhao Min   |
+-------+------------+
4 rows in set (0.00 sec)
  • 删除数据
    删除数据的时候一定指定条件，否则所有字段全部删除
mysql> DELETE FROM students WHERE sid=3;
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM students;
+-----+-----------+--------+
| sid | name      | gender |
+-----+-----------+--------+
|   1 | Yang Guo  | m      |
|   2 | Guo xiang | f      |
|   4 | Zhao Min  | NULL   |
+-----+-----------+--------+
3 rows in set (0.00 sec)
  • 更新表中的数据
mysql> UPDATE students SET gender='f' WHERE sid=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM students;
+-----+-----------+--------+
| sid | name      | gender |
+-----+-----------+--------+
|   1 | Yang Guo  | m      |
|   2 | Guo xiang | f      |
|   4 | Zhao Min  | f      |
+-----+-----------+--------+
3 rows in set (0.00 sec)

三、DCL

DCL（Data Control Language）语句： 数据控制语言，主要是用来设置/更改数据库用户权限。常用关键字有 GRANT、REVOKE 等。

  • 创建用户账号
创建用户账号：
  CREATE USER 'username'@'host' [IDENTIFIED BY 'password'];

删除用户：
  DROP USER 'username'@'host';

mysql> CREATE USER 'wpuwer'@'%' IDENTIFIED BY 'wppass';
Query OK, 0 rows affected (0.00 sec)

mysql> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

mysql> SELECT User,Host FROM user;
+---------------+-----------+
| User          | Host      |
+---------------+-----------+
| cdbsync       | %         |
| dsmart        | %         |
| root          | %         |
| wpuwer        | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
7 rows in set (0.00 sec)
  • 授权用户testuser访问testdb的所有表，并创建用户testuser
mysql> GRANT SELECT,DELETE ON testdb.* TO 'testuser'@'%' IDENTIFIED BY 'testpass';
Query OK, 0 rows affected, 1 warning (0.01 sec)
  • 查看用户授权
SHOW GRANTS FOR 'user'@'host'; 


mysql> SHOW GRANTS FOR 'wpuwer'@'%';
+------------------------------------+
| Grants for wpuwer@%                |
+------------------------------------+
| GRANT USAGE ON *.* TO 'wpuwer'@'%' |
+------------------------------------+
1 row in set (0.00 sec)
  • 查看当前用户授权
mysql> SHOW GRANTS FOR CURRENT_USER;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
  • 回收用户testuser在testdb的所有表的delete权限
mysql> REVOKE DELETE ON testdb.* FROM 'testuser'@'%';
Query OK, 0 rows affected (0.00 sec)
posted @   *一炁化三清*  阅读(15)  评论(0)  编辑  收藏  举报
相关博文：
· Mysql之索引
· Mysql之查询语句
· MySQL语句
· 第八章 SQL语句
· SQL语句
阅读排行：
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
点击右上角即可分享
微信分享提示