(My)SQL 使用入门
这里用了(My)SQL 这样的标题,目的是在介绍标准SQL 的同时,也将一些MySQL 在标准SQL 上的扩展一同介绍给读者。希望读者看完本节后,能够对标准SQL 的基本语法和MySQL 的部分扩展语法有所了解。
2.2.1 SQL 分类
SQL 语句主要可以划分为以下3 个类别。
- DDL(Data Definition Language)语句:数据定义语言,这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象。常用的语句关键字主要包括create、drop、alter 等。
- DML(Data Manipulation Language)语句:数据操纵语句,用于添加、删除、更新和查询数据库记录,并检查数据完整性。常用的语句关键字主要包括insert、delete、update和select 等。
- DCL(Data Control Language)语句:数据控制语句,用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。常用的语句关键字主要包括grant、revoke 等。
2.2.2 DDL 语句
简单来说,DDL 就是对数据库内部的对象进行创建、删除、修改等操作的语言。它和DML 语句的最大区别是DML 只是操作表内部的数据,而不涉及表的定义、结构的修改,更不会涉及其他对象。DDL 语句更多地是由数据库管理员(DBA)使用,开发人员一般很少使用。
下面通过一些例子来介绍MySQL 中常用DDL 语句的使用方法。
1.创建数据库
启动MySQL 服务之后,输入以下命令连接到MySQL 服务器:
[root~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 31 Server version: 8.0.11 MySQL Community Server - GPL Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type "help;" or "\h" for help. Type "\c" to clear the current input statement. mysql>
在以上命令行中,mysql 代表客户端命令,“-u”后面跟连接的数据库用户,“-p”表示需要输入密码。 如果数据库设置正常,并输入了正确的密码,将看到上面一段欢迎界面和一个“mysql>”提示符。在欢迎界面中说明了以下几部分内容。
- 命令的结束符,用“;”或者“\g”结束。
- 客户端的连接ID,这个数字记录了MySQL 服务到目前为止的连接次数;每一个新连接都会自动加1,本例中是31。
- MySQL 服务器的版本和类型,本例中是“8.0.11 MySQL Community Server - GPL”,说明是8.0.11 的社区发行版。
- 通过“help;”或者“\h”命令来显示帮助内容,通过“\c”命令来清除命令行buffer。在mysql>提示符后面输入所要执行的SQL 语句,每个SQL 语句以分号(;)或者“\g”结束,按回车键执行。
因为所有的数据都存储在数据库中,因此需要学习的第一个命令是创建数据库,语法如下所示:
CREATE DATABASE dbname
例如,创建数据库test1,命令执行如下:
mysql> create database test1; Query OK, 1 row affected (0.00 sec)
可以发现,执行完创建命令后,下面有一行提示“Query OK, 1 row affected (0.00 sec)”,这段提示可以分为3 个部分。“Query OK”表示上面的命令执行成功。读者可能会觉得奇怪,又不是执行查询操作,为什么显示查询成功?其实这是MySQL 的一个特点,所有的DDL 和DML(不包括SELECT)操作执行成功后都显示“Query OK”,这里理解为执行成功就可以了。“1 rowaffected”表示操作只影响了数据库中一行的记录,“0.00 sec”则记录了操作执行的时间。
如果已经存在这个数据库,系统会提示:
mysql> create database test1; ERROR 1007 (HY000): Can"t create database "test1"; database exists
这时,如果需要知道系统中都存在哪些数据库,可以用以下命令来查看:
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test1 | +--------------------+ 5 rows in set (0.00 sec)
可以发现,在上面的列表中除了刚刚创建的test1 外,还有另外4 个数据库,它们都是安装MySQL 时系统自动创建的,其功能分别如下。
- information_schema:主要存储系统中的一些数据库对象信息,比如用户表信息、列信息、权限信息、字符集信息、分区信息等。每个用户都可以查看这个数据库,但根据权限的不同看到的内容不同。
- performance_schema:MySQL 5.5 引入的系统库,用于存储系统性能相关的动态参数表。
- sys:MySQL 5.7 引入的系统库,本身不记录系统数据,基于information_schema 和performance_schema 之上,封装了一层更加易于调优和诊断的系统视图。
- mysql:存储系统的用户权限信息。
在查看系统中已有的数据库后,可以用如下命令选择要操作的数据库:
USE dbname
例如,选择数据库test1:
mysql> use test1 Database changed
然后再用以下命令来查看test1 数据库中创建的所有数据表:
mysql> show tables; Empty set (0.00 sec)
由于test1 是刚创建的数据库,还没有表,所以显示为空。命令行下面的“Empty set”表示操作的结果集为空。如果查看一下mysql 数据库里面的表,则可以得到以下信息:
mysql> use mysql
mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | component | | db | | default_roles | | engine_cost | | func | | general_log | | global_grants | | gtid_executed | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | password_history | | plugin | | procs_priv | | proxies_priv | | role_edges | | server_cost | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +--------------------------+ 33 rows in set (0.00 sec)
2.删除数据库 删除数据库的语法很简单,如下所示:
drop database dbname;
例如,要删除test1 数据库可以使用以下语句:
mysql> drop database test1; Query OK, 0 rows affected (0.00 sec)
可以发现,提示操作成功后,后面却显示“0 rows affected”,这个提示的含义是前一次MySQL 操作所影响的记录行数,通常只对增删改操作生效,drop 等DDL 操作通常显示“0 rowsaffected”。
注意:数据库删除后,下面的所有表数据都会全部删除,所以删除前一定要仔细检查并做好相应备份。
3.创建表
在数据库中创建一张表的基本语法如下:
CREATE TABLE tablename ( column_name_1 column_type_1 constraints, column_name_2 column_type_2 constraints, … column_name_n column_type_n constraints)
因为MySQL 的表名是以文件的形式保存在磁盘上的,所以表名的字符可以用任何文件名允许的字符。column_name 是列的名字;column_type 是列的数据类型;constraints 是这个列的约束条件,在后面的章节中会详细介绍。
例如,创建一个名称为emp 的表。表中包括ename(姓名)、hiredate(雇用日期)和sal(薪水)3 个字段,字段类型分别为varchar(10)、date、int(2)(关于字段类型将会在第 3 章中介绍):
mysql> create table emp(ename varchar(10),hiredate date,sal decimal(10,2),deptno int(2)); Query OK, 0 rows affected (0.02 sec)
表创建完毕后,如果需要查看一下表的定义,可以使用如下命令:
DESC tablename
例如,查看emp 表,将输出以下信息:
mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(10) | YES | | | | | hiredate | date | YES | | | | | sal | decimal(10,2) | YES | | | | | deptno | int(2) | YES | | | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
虽然desc 命令可以查看表定义,但是其输出的信息还是不够全面。为了得到更全面的表定义信息,有时就需要查看创建表的SQL 语句,可以使用如下命令查看:
mysql> show create table emp \G; *************************** 1. row *************************** Table: emp Create Table: CREATE TABLE "emp" ( "ename" varchar(20) DEFAULT NULL, "hiredate" date DEFAULT NULL, "sal" decimal(10,2) DEFAULT NULL, "deptno" int(2) DEFAULT NULL, KEY "idx_emp_ename" ("ename") ) ENGINE=InnoDB DEFAULT CHARSET=gbk 1 row in set (0.02 sec) ERROR: No query specified mysql>
从上面创建表的SQL 语句中,除了可以看到表定义以外,还可以看到表的engine(存储引擎)和charset(字符集)等信息。“\G”选项的含义是使得记录能够按照字段竖向排列,以便更好地显示内容较长的记录。
4.删除表
表的删除命令如下:
DROP TABLE tablename
例如,要删除数据库emp 可以使用以下命令:
mysql> drop table emp; Qu ery OK, 0 rows affected (0.00 sec)
5.修改表 对于已经创建好的表,尤其是已经有大量数据的表,如果需要做一些结构上的改变,可以先将表删除(drop),然后再按照新的表定义重建表。这样做没有问题,但是必然要做一些额外的工作,比如数据的重新加载。而且,如果有服务在访问表,也会对服务产生影响。
因此,在大多数情况下,表结构的更改都使用alter table 语句,以下是一些常用的命令。
(1)修改表类型,语法如下:
ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
例如,修改表emp 的ename 字段定义,将varchar(10)改为varchar(20):
mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(10) | YES | | | | | hiredate | date | YES | | | | | sal | decimal(10,2) | YES | | | | | deptno | int(2) | YES | | | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> alter table emp modify ename varchar(20); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | | | | hiredate | date | YES | | | | | sal | decimal(10,2) | YES | | | | | deptno | int(2) | YES | | | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
(2)增加表字段,语法如下:
ALTER TABLE tablename ADD [COLUMN] column_definition [FIRST | AFTER col_name]
例如,在表emp 中新增加字段age,类型为int(3):
mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | | | | hiredate | date | YES | | | | | sal | decimal(10,2) | YES | | | | | deptno | int(2) | YES | | | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> alter table emp add column age int(3); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | | | | hiredate | date | YES | | | | | sal | decimal(10,2) | YES | | | | | deptno | int(2) | YES | | | | | age | int(3) | YES | | | | +----------+---------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
(3)删除表字段,语法如下:
ALTER TABLE tablename DROP [COLUMN] col_name
例如,将字段age 删除掉:
mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | | | | hiredate | date | YES | | | | | sal | decimal(10,2) | YES | | | | | deptno | int(2) | YES | | | | | age | int(3) | YES | | | | +----------+---------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> alter table emp drop column age; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | | | | hiredate | date | YES | | | | | sal | decimal(10,2) | YES | | | | | deptno | int(2) | YES | | | | +----------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
(4)字段改名,语法如下:
ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition [FIRST|AFTER col_name]
例如,将age 改名为age1,同时修改字段类型为int(4):
mysql> desc emp; +----------+-----