MySQL 入门学习笔记(二) 基本操作

MySQL 入门学习笔记(二) 数据库和表的基本操作

我们把一些表的集合称之为数据库,一个服务器中可以存在多个数据库.每个数据库中包含多个表,每个表都有一个名字作为标识,数据表则包含带有数据的记录.
PS:SQL 语句对大小写不敏感.

操作数据库命令

在 MySQL 命令中,数据库用DATABASE表示.
当我门想查看服务器中都有什么数据库时,可以用以下命令:

 SHOW DATABASES;+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+

需要注意的是,每条完整的 SQL 语句后面都需要跟一个分号:“;”
分号是在数据库系统中分隔每条 SQL 语句的标准方法,这样就可以在对服务器的请求中执行多条 SQL 语句.

一些重要的操作数据库的命令:

CREATE DATABASE - 创建新数据库
USE DATABASE - 切换数据库
ALTER DATABASE - 修改数据库
DROP DATABASE - 删除数据库

创建数据库

首先是创建数据库命令:

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)

这种情况表示我们的数据库创建成功.

但是如果数据库中已经存在了同名的数据库,执行创建数据库命令就会报错:

mysql> CREATE DATABASE test;
ERROR 1007 (HY000): Can't create database 'test'; database exists

这个时候命令的执行结果是一个ERROR,并且显示已经存在同名的数据库了.
当并不清楚是否已经存在同名的数据库时,可以使用IF NOT EXISTS:

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

这个时候提示就变为了一个Warning.

Warning 的好处就是不影响语句的执行,而 ERROR 之后的语句是不能执行的.

切换数据库

当需要进入某个数据库的时候需要使用USE DATABASE命令进行切换

mysql> USE test;
Database changed

“Database changed”说明数据库切换成功.

修改数据库

当需要修改数据库的某些参数时,可以是用ALTER DATABASE命令,例如

mysql> ALTER DATABASE test-> DEFAULT CHARACTER SET gb2312-> DEFAULT COLLATE gb2312_chinese_ci;
Query OK, 1 row affected (0.00 sec)

以上时使用命令行工具将数据库 test 指定字符集修改为 gb2312,默认校对规则修改为 gb2312_chinese_ci.

删除数据库

当一些数据库废弃的时候可以使用DROP DATABASE命令删除:

mysql> DROP DATABASE demo;
ERROR 1008 (HY000): Can't drop database 'demo'; database doesn't exist

当我们删除数据库中不存在的数据库时,同样会报ERROR

如果想避免 ERROR,跟之前创建命令不同的是,需要使用IF EXISTS,再执行一次:

mysql> DROP DATABASE IF EXISTS demo;
Query OK, 0 rows affected, 1 warning (0.00 sec)

这个时候与创建命令类似,ERROR 变成了 Warning.

♣删除数据库命令是一个非常非常危险的操作,因为删除数据库意味着同时删除了数据库中的表结构,还有数据库中的数据.这意味着所有的数据都会丢失,所以在实际开发中删除数据库需要慎之又慎.

操作数据库表命令

数据库建好之后就可以创建表结构了.
数据库表的基本操作会用到几个命令:

SHOW TABLES; - 展示表
CREATE TABLE ; - 创建表
ALTER TABLE; - 修改表
DROP TABLE; - 删除表

展示数据表

在数据库中可以使用**SHOW TABLES;**查看数据库的数据表.

mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| student        |
+----------------+
1 row in set (0.00 sec)

从结果可以看到数据库中有几张表,还有每张表的名字.

另外,还可以在该命令后加上数据库的名称,这样就可以展示其他数据库中的表信息了:

mysql> SHOW TABLES FROM test_2;
+------------------+
| Tables_in_test_2 |
+------------------+
| first_table      |
+------------------+
1 row in set (0.00 sec)

创建表

创建表的命令是:CREATE TABLE;,但是在创建的过程中,需要指定一些参数:

• 数据表名称
• 数据表中列的名称和列的数据类型
• 数据表中列还有一些可选属性 比如"是否为 NULL 默认值" 等.
• 可以同时创建多个列

这里有一个主键的概念,简单来说就是:

表中经常有一列或者多列组合,他们能唯一的表示每一行.这样的列或者列的组合称为主键,通过主键可以增强表的实体完整性.

通过PRIMARY KEY约束来创建主键.一个表只能有一个 PRIMARY KEY 约束,且该约束的主键必须是非空(NOT NULL).

所以可以写成这样:

mysql> CREATE TABLE class(-> id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id',-> name VARCHAR(30)  NULL COMMENT 'name',-> PRIMARY KEY(`id`)-> ) COMMENT='class';
Query OK, 0 rows affected (0.02 sec)

上面的命令是创建一个名称为"class"的表,且备注信息为"class"表里面有两列的创建信息:

• 第一列的名称是"id",数据类型为 int,属性是"不为 NULL,自增,且备注信息为’id’"
• 第二列的名称是"name",数据类型为 varchar,属性是"可以为 NULL,备注信息为’name’"
• 同时,PRIMARY KEY指定了该表的主键为第一列

创建表命令有个需要注意的地方:

• 列信息放在()中,各个列之间用英文版,分隔.

如果已经存在同名的表,这时候再执行创建命令就会报错ERROR,提示已经存在相同名称的表.
为了避免 ERROR,和创建数据库时相同,可以使用IF NOT EXISTS.
例如:

mysql> CREATE TABLE IF NOT EXISTS class(-> name VARCHAR(30)-> ) COMMENT ='class';
Query OK, 0 rows affected, 1 warning (0.00 sec)

使用 IF NOT EXISTS 重新创建‘class’,不会报 ERROR,而是给出 warning 的提示.

修改表

查看表结构

修改表之前可能需要查看表结构,可以用下面的命令查看表结构:

DESCRIBE <表名>;
DESC <表名>;
EXPLAIN <表名>;
SHOW COLUMNS FROM <表名>;
SHOW FIELDS FROM <表名>;

例如:

mysql> SHOW FIELDS FROM class;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

上面几条命令的查询的结果时相同的,都会展示表中每一列的名称、数据类型和属性.
此外,还有一条命令SHOW CREATE TABLE会将数据库表结构信息,按照创建时的样子展示出来:

mysql> show create table class;
+-------+---------------------------------------------------+
| Table | Create Table                                      |
+-------+---------------------------------------------------+
| class | CREATE TABLE `class` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',`name` varchar(30) NOT NULL COMMENT 'name',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='class'      |
+-------+---------------------------------------------------+
1 row in set (0.00 sec)

上面展示的表信息中出现了ENGINE=InnoDB 和DEFAULT CHARSET=latin1,他的意思是:

• 该表指定的存储引擎是 InnoDB
• 该表默认的字符集是 latin1

修改表名

首先,如果想修改表名称,可以使用RENAME TABLE <旧表明> TO <新表名>,例如:

mysql> RENAME TABLE class TO class_1;
Query OK, 0 rows affected (0.00 sec)mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| class_1        |
| student        |
+----------------+
2 rows in set (0.00 sec)

查看结果,发现命令有效,修改成功了.

另外,修改表名也可以用ALTER TABLE <旧表名> RENAME <新表名>;或者使用ALTER TABLE <旧表名> RENAME TO <新表名>;.效果都是相同的.

在 SQL 语句中,ALTER TABLE语句也用于在已有的表中添加、删除、或者修改列.

增加列

使用 ALTER TABLE 在表中增加一列,可以使用命令:

ALTER TABLE <表名> ADD COLUMN  <列名> 数据类型 属性;

例如:

mysql> ALTER TABLE class ADD COLUMN `describe` VARCHAR(100) NULL COMMENT 'describe';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> DESC class;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| name     | varchar(30)      | NO   |     | NULL    |                |
| describe | varchar(100)     | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

可以看到,已经新增成功.

注意,上面的列名’describe’需要用反引号`标注起来,否则可能会出现修改不成功的情况.

有时为了数据更加直观,需要将有关描述放到一起,这个时候需要调整列的位置.
调整语句很简单,只需要在新增或者修改的时候在属性之后指定位置就可以.

例如:

 mysql> ALTER TABLE class ADD COLUMN `student_count` int(4) NOT NULL COMMENT 'count' FIRST;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc class;
+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| student_count | int(4)           | NO   |     | NULL    |                |
| id            | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| name          | varchar(30)      | NO   |     | NULL    |                |
| describe      | varchar(100)     | YES  |     | NULL    |                |
+---------------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

可以看到,新增的’student_count’列,被放到了最前面.

如果想添加到指定的列后面,可以将’FIRST’修改为 ‘AFTER <列名>’.

修改列

修改列的命令有三个,分别为 CHANGE COLUMN ALTER COLUMN MODIFY COLUMN.
这三个虽然都是在修改列,但是使用的场景不太相同

• CHANGE COLUMN

主要用于列的重命名,列的类型以及属性的变更和位置移动.

mysql> ALTER TABLE class CHANGE COLUMN `student_count` `count` INT(5) AFTER `id`;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> SHOW FIELDS * FROM class;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM class' at line 1
mysql> SHOW FIELDS FROM class;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| count    | int(5)           | YES  |     | NULL    |                |
| name     | varchar(30)      | NO   |     | NULL    |                |
| describe | varchar(100)     | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

• ALTER COLUMN

只用来设置或删除列的默认值

 mysql> ALTER TABLE class ALTER COLUMN `describe` SET DEFAULT 'this is default describe';
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> EXPLAIN class;
+----------+------------------+------+-----+--------------------------+----------------+
| Field    | Type             | Null | Key | Default                  | Extra          |
+----------+------------------+------+-----+--------------------------+----------------+
| id       | int(11) unsigned | NO   | PRI | NULL                     | auto_increment |
| count    | int(5)           | YES  |     | NULL                     |                |
| name     | varchar(30)      | NO   |     | NULL                     |                |
| describe | varchar(100)     | YES  |     | this is default describe |                |
+----------+------------------+------+-----+--------------------------+----------------+
4 rows in set (0.00 sec)

• MODIFY COLUMN

功能和 CHANGE COLUMN 相同,但是不能用来给一个列重命名.

mysql> ALTER TABLE class MODIFY COLUMN `name` VARCHAR(50);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> SHOW COLUMNS FROM class;
+----------+------------------+------+-----+--------------------------+----------------+
| Field    | Type             | Null | Key | Default                  | Extra          |
+----------+------------------+------+-----+--------------------------+----------------+
| id       | int(11) unsigned | NO   | PRI | NULL                     | auto_increment |
| count    | int(5)           | YES  |     | NULL                     |                |
| name     | varchar(50)      | YES  |     | NULL                     |                |
| describe | varchar(100)     | YES  |     | this is default describe |                |
+----------+------------------+------+-----+--------------------------+----------------+
4 rows in set (0.00 sec)

删除列

删除列的操作是和删除表或者数据库一样,都是用DROP.

mysql> ALTER TABLE class DROP count;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> SHOW FIELDS FROM class;
+----------+------------------+------+-----+--------------------------+----------------+
| Field    | Type             | Null | Key | Default                  | Extra          |
+----------+------------------+------+-----+--------------------------+----------------+
| id       | int(11) unsigned | NO   | PRI | NULL                     | auto_increment |
| name     | int(10)          | YES  |     | NULL                     |                |
| describe | varchar(100)     | YES  |     | this is default describe |                |
+----------+------------------+------+-----+--------------------------+----------------+
3 rows in set (0.00 sec)

可以看到 'count’列已经被删除了.

同样的,当删除一个表中不存在的列时,也会报ERROR.
但需要注意的是DROP COLUMN 是不支持 IF EXISTS 的.

所以,要想在删除是判断字段是否存在,需要一些复杂的操作.

删除表

删除表的语句是DROP TABLE

mysql> DROP TABLE test_table;
Query OK, 0 rows affected (0.01 sec)

当删除一个不存在的表时,也会报 ERROR,所以也需要进行IF EXISTS判断来规避 ERROR.确保后面的 SQL 语句正常执行:

mysql> DROP TABLE IF EXISTS test_table;
Query OK, 0 rows affected, 1 warning (0.00 sec)

DROP 语句支持同时删除多张表,表与表之间用逗号隔开即可.

♣ 删除表也是一个很谨慎的操作,正常开发中尽量不要使用删除操作.

操作合并

一条语句中可以包含多个操作,例如当修改表时:

mysql> ALTER TABLE class-> DROP COLUMN `describe`,-> CHANGE COLUMN `name` `new_name` VARCHAR(20) NOT NULL,-> ADD COLUMN `size` INT(4) NOT NULL COMMENT 'size';
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> explain class;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| new_name | varchar(20)      | NO   |     | NULL    |                |
| size     | int(4)           | NO   |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

语句正常执行.