Mysql基础命令

1.mysql命令分类

数据定义语言（DDL）

DDL全称是Data Definition Language，即数据定义语言，定义语言就是定义关系模式、删除关系、修改关系模式以及创建数据库中的各种对象，比如表、聚簇、索引、视图、函数、存储过程和触发器等等。

数据操纵语言(DML)

数据操纵语言全程是Data Manipulation Language，主要是进行插入元组、删除元组、修改元组的操作。主要有insert、update、delete语法组成。

数据查询语言(DQL)

数据查询语言全称是Data Query Language，所以是用来进行数据库中数据的查询的，即最常用的select语句

数据控制语言(DCL)

数据控制语言：Data Control Language。用来授权或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，能够对数据库进行监视。

比如常见的授权、取消授权、回滚、提交等等操作。

SQL语言：DDL、DML、DQL、DCL详解

2. mysql 数据类型

2.1 数值类型

MySQL 支持所有标准 SQL 数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。

作为 SQL 标准的扩展，MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

2.2 日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

2.3 字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

MySQL 数据类型 | 菜鸟教程 (runoob.com)

2.4 数据类型的属性

mysql 数据类型 - 心随所遇 - 博客园

主键与唯一键的区别：

共同点：字段值唯一，不允许有重复的值

不同点：一个表中只能有一个主键，但可以有多个唯一键。

主键字段中不允许有NULL值，唯一键是允许有多个null值

3. mysql数据库的基础操作

在使用sql语句的时候，注意每个语句后必须有分号。

3.1 查询库与表

##1.查看已有数据库
mysql> show databases;            
+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
##2.查看数据库中的表
##先切换库
mysql> use employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
##在查看表
mysql> show tables;
+----------------------+
| Tables_in_employees  |
+----------------------+
| current_dept_emp     |
| departments          |
| dept_emp             |
| dept_emp_latest_date |
| dept_manager         |
| employees            |
| salaries             |
| titles               |
+----------------------+
8 rows in set (0.00 sec)
##3.查询表结构
##以表格形式展示表结构
mysql> describe employees;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int(11)       | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
##以建表语句显示表结构（信息更加详细）\G，表示将查询结果进行按列打印
mysql> show create table employees\G;
*************************** 1. row ***************************
       Table: employees
Create Table: CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

3.2 Create 创建操作

##1.创建新的数据库
mysql> create database test1;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| performance_schema |
| test1              |
+--------------------+
5 rows in set (0.00 sec)

##2.创建新表
mysql> use test1;
Database changed
mysql> create table test_tb1(
    -> test_id INT NOT NULL AUTO_INCREMENT,
    -> test_title VARCHAR(100) NOT NULL,
    -> test_author VARCHAR(40) NOT NULL,
    -> create_date DATE,
    -> PRIMARY KEY( test_id )
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.20 sec)

##如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
##AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
##PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。
##ENGINE 设置存储引擎，CHARSET 设置编码。
注意：MySQL命令终止符为分号 ; 。
注意： -> 是换行符标识，不要复制。

建表的通用格式为

CREATE TABLE table_name (column_name column_type);

创建MySQL数据表需要以下信息：

• 表名
• 表字段名
• 定义每个表字段

3.3 Update 更新操作（插入和修改）

3.3.1 表数据的插入与修改

以下为向MySQL数据表插入数据通用的 INSERT INTO SQL语法：

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

如果数据是字符型，必须使用单引号或者双引号，如："value"。

##1.单条逐个插入
mysql> INSERT INTO test_tb1 
    -> (test_title,test_author,create_date)
    -> VALUES
    -> ("通信原理", "江晓林", NOW());
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO test_tb1
    -> (test_title, test_author, create_date)
    -> VALUES
    ->  ("计算机网络", "谭浩强", NOW());
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO test_tb1
    -> (test_title, test_author, create_date)
    ->  VALUES
    -> ("mysql基础教程", "菜鸟教程", '2016-05-06');
Query OK, 1 row affected (0.00 sec)
##NOW()获取当前时间，由于test_id为自增键，所以可以在添加数据的时候，不选择插入它的值
mysql> select * from test_tb1;
+---------+-------------------+--------------+-------------+
| test_id | test_title        | test_author  | create_date |
+---------+-------------------+--------------+-------------+
|       1 | 通信原理          | 江晓林       | 2022-05-24  |
|       2 | 计算机网络        | 谭浩强       | 2022-05-24  |
|       3 | mysql基础教程     | 菜鸟教程     | 2016-05-06  |
+---------+-------------------+--------------+-------------+
3 rows in set (0.00 sec)

##2.批量插入数据，不同数据用逗号隔开
mysql> INSERT INTO test_tb1
    -> (test_title,test_author,create_date)
    -> VALUES
    -> ("斗破苍穹","天蚕土豆",'2009-10-01'),
    -> ("冒牌大英雄","七十二编",'2008-9-22');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test_tb1;
+---------+-------------------+--------------+-------------+
| test_id | test_title        | test_author  | create_date |
+---------+-------------------+--------------+-------------+
|       1 | 通信原理          | 江晓林       | 2022-05-24  |
|       2 | 计算机网络        | 谭浩强       | 2022-05-24  |
|       3 | mysql基础教程     | 菜鸟教程     | 2016-05-06  |
|       4 | 斗破苍穹          | 天蚕土豆     | 2009-10-01  |
|       5 | 冒牌大英雄        | 七十二编     | 2008-09-22  |
+---------+-------------------+--------------+-------------+
5 rows in set (0.00 sec)

以下是 UPDATE 命令修改 MySQL 数据表数据的通用 SQL 语法：

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

• 你可以同时更新一个或多个字段。
• 你可以在 WHERE 子句中指定任何条件。
• 你可以在一个单独表中同时更新数据。

当你需要更新数据表中指定行的数据时 WHERE 子句是非常有用的。如果不使用where语句进行条件限制，使用UPDATE语句将会修改该字段的所有数据。

mysql> UPDATE test_tb1 SET test_author='龙光利' WHERE test_id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_tb1;
+---------+-------------------+--------------+-------------+
| test_id | test_title        | test_author  | create_date |
+---------+-------------------+--------------+-------------+
|       1 | 通信原理          | 龙光利       | 2022-05-24  |
|       2 | 计算机网络        | 谭浩强       | 2022-05-24  |
|       3 | mysql基础教程     | 菜鸟教程     | 2016-05-06  |
|       4 | 斗破苍穹          | 天蚕土豆     | 2009-10-01  |
|       5 | 冒牌大英雄        | 七十二编     | 2008-09-22  |
+---------+-------------------+--------------+-------------+
5 rows in set (0.00 sec)

3.3.2 表结构的修改

利用alter可修改表的结构。

首先创建一个测试表alter_tbl。

mysql> create table alter_tbl(
    -> first varchar(10),
    -> second varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql> desc alter_tbl;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| first  | varchar(10) | YES  |     | NULL    |       |
| second | varchar(10) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

使用drop可以删除一个字段。

mysql> ALTER TABLE alter_tbl DROP first;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc alter_tbl;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| second | varchar(10) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

使用add可以增加一个字段。但默认新加的字段会添加刀数据表字段的末尾。

mysql> ALTER TABLE alter_tbl ADD first varchar(15);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc alter_tbl;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| second | varchar(10) | YES  |     | NULL    |       |
| first  | varchar(15) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

如果需要指定新增字段的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）。

mysql> ALTER TABLE alter_tbl ADD id INT FIRST;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc alter_tbl;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| second | varchar(10) | YES  |     | NULL    |       |
| first  | varchar(15) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

使用change字句，可以修改字段名称，并指定新字段名称和类型。

mysql> ALTER TABLE alter_tbl change first phone INT;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc alter_tbl;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| second | varchar(10) | YES  |     | NULL    |       |
| phone  | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

使用rename可以修改表名。

mysql> ALTER TABLE alter_tbl RENAME TO test_tbl2;
Query OK, 0 rows affected (0.20 sec)

mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| test_tb1        |
| test_tbl2       |
+-----------------+
2 rows in set (0.00 sec）

3.4 数据查询select

select是mysql当中最重要的操作，这里仅介绍简单的用法。

##1.查询一个表的所有数据
mysql> select * from test_tb1;
+---------+-------------------+--------------+-------------+
| test_id | test_title        | test_author  | create_date |
+---------+-------------------+--------------+-------------+
|       1 | 通信原理          | 龙光利       | 2022-05-24  |
|       2 | 计算机网络        | 谭浩强       | 2022-05-24  |
|       3 | mysql基础教程     | 菜鸟教程     | 2016-05-06  |
|       4 | 斗破苍穹          | 天蚕土豆     | 2009-10-01  |
|       5 | 冒牌大英雄        | 七十二编     | 2008-09-22  |
+---------+-------------------+--------------+-------------+
5 rows in set (0.00 sec)
##2.使用where语句可以限定查询条件
mysql> select * from test_tb1 where test_id=1;
+---------+--------------+-------------+-------------+
| test_id | test_title   | test_author | create_date |
+---------+--------------+-------------+-------------+
|       1 | 通信原理     | 龙光利      | 2022-05-24  |
+---------+--------------+-------------+-------------+
1 row in set (0.00 sec)
##3.使用and和or可以更加具体的限定条件
mysql> select * from test_tb1 where test_id<4 and test_author='菜鸟教程';
+---------+-------------------+--------------+-------------+
| test_id | test_title        | test_author  | create_date |
+---------+-------------------+--------------+-------------+
|       3 | mysql基础教程     | 菜鸟教程     | 2016-05-06  |
+---------+-------------------+--------------+-------------+
1 row in set (0.00 sec)

mysql> select * from test_tb1 where test_id<3 or test_id =5;
+---------+-----------------+--------------+-------------+
| test_id | test_title      | test_author  | create_date |
+---------+-----------------+--------------+-------------+
|       1 | 通信原理        | 龙光利       | 2022-05-24  |
|       2 | 计算机网络      | 谭浩强       | 2022-05-24  |
|       5 | 冒牌大英雄      | 七十二编     | 2008-09-22  |
+---------+-----------------+--------------+-------------+
3 rows in set (0.00 sec)