操作MySQL¶

一. 数据库相关的语句¶

1. 登录MySQL¶

Ⅰ. 格式：

mysql -u root -p [-h 数据库服务器的IP地址] [-P 端口]

Ⅱ. 说明：

-p：要求输入密码

-P：指定MySQL服务器监听的端口，默认使用3306端口。

例如：

[root@3 ~]# mysql -u root -p

[root@3 ~]# mysql -uroot -p -h192.168.100.101

2. 退出MySQL¶

mysql> exit

或

mysql> \q

3. MySQL的结构¶

C/S结构

其中C为客户端

自带的命令行工具，如MySQL、MySQL admin、MySQL dump、MySQL binlog等。
图形化工具，如SQLyog、Navicat等。

S为服务端，一般使用mysqld的服务启动。

4. 客户端连接mysqld的方式¶

第一种：socket连接，通常是客户端与服务端位于同一台服务器。

如：

[root@3 ~]# mysql -uroot -p
[root@3 ~]# mysql -uroot -p -hlocalhost
[root@3 ~]# mysql -uroot -p -S /usr/local/mysql/mysql.sock

验证方法

[root@3 ~]# ss -anp|grep mysql

第二种：tcp连接，客户端与服务器可以不处于同一台服务器

如：

[root@3 ~]# mysql -uroot -p -h127.0.0.1                 #位于同一台服务器
[root@3 ~]# mysql -uroot -p -h数据库服务器的IP地址       #指定对应服务器

验证方法

[root@3 ~]# ss -tanp|grep mysql

5. 查看数据库¶

mysql> show databases;

6.创建数据库¶

Ⅰ. 格式：

create database [if not exists] 数据库名；

create schema [if not exists] 数据库名 [default character set 字符集名];

Ⅱ. 示例

a. 创建名为test的数据库

mysql> create database if not exists test;
Query OK, 1 row affected (0.00 sec)

mysql> show create database test \G
*************************** 1. row ***************************
       Database: test
Create Database: CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
1 row in set (0.00 sec)

b. 创建名为school的数据库

mysql> create schema if not exists school default character set utf8;
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show create database school;
+----------+-------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                       |
+----------+-------------------------------------------------------------------------------------------------------+
| school   | CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8mb3 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

7.进入某个数据库¶

use 数据库名

例如

mysql> use school \G
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| school     |
+------------+
1 row in set (0.00 sec)

8. 删除数据库-------谨慎¶

格式：

drop database [if exists] 数据库名;

例如删除test数据库

mysql> drop database if exists test;
Query OK, 0 rows affected (0.00 sec)

mysql> drop database if exists test;
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

二. 关于表的操作¶

1. 二维表¶

表由行和列组成

行又称为记录/元组；列又被称为字段/属性。

2. 查看库中有哪些表¶

格式：

进入数据库中：

show tables;

mysql> use school;
Database changed
mysql> show tables;
Empty set (0.00 sec)

3. 查看表结构，即表头¶

desc [库名.]表名

例如查看mysql库中user表的结构

mysql> desc mysql.user;
#或
mysql> use mysql -A
Database changed
mysql> desc user
    -> ;

4. 创建表¶

格式：

create table [库名.]表名(字段名1 数据类型,字段名2 数据类型,字段名3，数据类型...)

例如创建一个学生信息表，包括学号、姓名、性别、年龄、电话号码和住址字段，每个字段都要有注释描述

mysql> create table student(
    -> 学号 int unsigned not null primary key comment '学生学号',
    -> 姓名 char(20) not null comment '学生姓名',
    -> 性别 enum('男','女') not null default '男' comment '学生性别',
    -> 年龄 tinyint unsigned not null default 20 comment '学生年龄',
    -> 电话号码 char(11) comment '学生联系方式',
    -> 住址 varchar(100) not null comment '学生家庭住址'
    ->  );
Query OK, 0 rows affected (0.01 sec)

*** 注：

int：数据类型为整数
unsigned：没有符号，即数据为非负数
not null：不能为空，必须输入数据
primary：主键，唯一标识每一行的记录，自动具备唯一+非空特性
comment：后接注释
char(20)：定长字符串，固定站20个字符，不够的用空格补齐
varchar(100)：变长字符串，存多少占多少，最多100个字符。
enum('男','女')：枚举类型，只能从指定列表中选一个值，此处只能选男或女。
default：默认值，如果不填手动数据自动将该处数据填写为X

查看验证：

mysql> show create table student \G
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `学号` int unsigned NOT NULL COMMENT '学生学号',
  `姓名` char(20) NOT NULL COMMENT '学生姓名',
  `性别` enum('男','女') NOT NULL DEFAULT '男' COMMENT '学生性别',
  `年龄` tinyint unsigned NOT NULL DEFAULT '20' COMMENT '学生年龄',
  `电话号码` char(11) DEFAULT NULL COMMENT '学生联系方式',
  `住址` varchar(100) NOT NULL COMMENT '学生家庭住址',
  PRIMARY KEY (`学号`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)

说明：

创建表时不指定存储引擎，会继承MySQL服务器的默认存储引擎。
创建表时不指定字符集，会继承数据库默认的字符集；建数据库也不指定字符集会继承MySQL服务器的默认字符集。

5. 删除表¶

格式：drop table if exists 表名;

例如，删除student表

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student          |
+------------------+
1 row in set (0.00 sec)

mysql> drop table if exists student;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> quit
Bye

三. 数据类型¶

Ⅰ.数值类型

类型	大小	范围(有符号)	范围(无符号)	用途
TINYINT	1字节	(-128,127)	(0,255)	极小整数值
SMALLINT	2字节	(-32768,32767)	(0,65535)	小整数值
MEDIUMINT	3字节	(-8 388 608,8 388 607)	(0,16 777 215)	中整数值
INT或INTEGER	4字节	(-2 147 483 648,2 147 483 647)	(0,4 294 967 295)	整数值
BIGINT	8字节	(-9 223 372 036 854 775 808,9 223 372 036 854 775 807)	(0, 18 446 744 073 709 551 615)	大整数值
FLOAT	4字节	(-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 E+38)	0,(1.175 494 351 E-38,3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8字节	(-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)	0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)	双精度浮点数值

Ⅱ.字符串类型

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535字节	变长字符串

Ⅲ. 日期和时间类型

类型	大小(字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	5	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:01/2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	混合日期和时间值，时间戳

Ⅳ. 枚举类型

枚举类型字段的值，必须从预先定义好的字符串集合中选取。

ENUM(value1,value2,value3)

ENUM(枚举)，只能取一个，用于互斥，例如性别字段只能为男或女。

四. 练习¶

按照以下要求创建数据库即表

建库：

库名：linux50 字符集：utf8mb4 校验规则：默认

建表：

表一

表名：student(学生表)

字段数据类型要求是否为空注释

sno 最多20位否学号(主键)

sname 可变长否学生姓名

sage 最小整数非负数否学生年龄

ssex 0,1 否学生性别(1为男0为女)默认为男

sbirthday 时间类型默认为空学生生日

class 可变长否学生班级

表二

表名：course(课程表)

字段数据类型要求是否为空注释

cno 最多为20位否课程号(主键)

cname 可变长否课程名称

tno 可变长否教师编号

表三

表名：score(成绩表)

字段数据类型要求是否为空注释

sno 最多20位否学号(主键)

cno 最多20位否课程号(主键)

mark 浮点数(4,1) 否成绩

注意：sno和cno在另外两个表中是主键，在这里应该是外键。了解即可。

表四

表名：teacher(教师表)

字段数据类型要求是否为空注释

tno 最多20位否教师编号(主键)

tname 可变长否教师姓名

tage 最小整数，非负数否教师年龄

tsex 0,1 否教师性别(1男，0女)默认为男

prof 可变长是教师职称

depart 可变长否教师部门

表1：

[root@3 ~]# mysql -uroot -p -hlocalhost
Enter password:
...
mysql> create database if not exists student;
Query OK, 1 row affected (0.01 sec)

mysql> use student;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql>  create table student(
    -> sno char(20) NOT NULL primary key comment '学生学号',
    -> sname VARCHAR(20) not null comment '学生姓名',
    -> sage TINYINT unsigned not null comment '学生年龄',
    -> ssex enum('0','1') not null default '1' comment '学生性别(1男0女)',
    -> sbirthday DATE DEFAULT NULL comment '学生生日',
    -> class  VARCHAR(60) not null comment '学生班级'
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> show create table student \G
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `sno` char(20) NOT NULL COMMENT '学生学号',
  `sname` varchar(20) NOT NULL COMMENT '学生姓名',
  `sage` tinyint unsigned NOT NULL COMMENT '学生年龄',
  `ssex` enum('0','1') NOT NULL DEFAULT '1' COMMENT '学生性别(1男0女)',
  `sbirthday` date DEFAULT NULL COMMENT '学生生日',
  `class` varchar(60) NOT NULL COMMENT '学生班级',
  PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

表2：

mysql> create database if not exists course;
Query OK, 1 row affected (0.00 sec)

mysql> use course;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table course(
    -> cno char(20) NOT NULL primary key comment '课程号',
    ->  cname varchar(20) not null comment '课程名称',
    ->  tno varchar(20) not null comment '教师编号'
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> show create table course \G
*************************** 1. row ***************************
       Table: course
Create Table: CREATE TABLE `course` (
  `cno` char(20) NOT NULL COMMENT '课程号',
  `cname` varchar(20) NOT NULL COMMENT '课程名称',
  `tno` varchar(20) NOT NULL COMMENT '教师编号',
  PRIMARY KEY (`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

表3：

mysql> create database if not exists score;
Query OK, 1 row affected (0.01 sec)

mysql> use score;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table score(
    -> sno char(20) not null comment '学号',
    -> cno char(20) not null comment '课程号',
    -> mark DECIMAL(4,1) not null comment '成绩',
    -> primary key (sno, cno)    -- 联合主键，sno + cno 组合唯一
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>  show create table score \G
*************************** 1. row ***************************
       Table: score
Create Table: CREATE TABLE `score` (
  `sno` char(20) NOT NULL COMMENT '学号',
  `cno` char(20) NOT NULL COMMENT '课程号',
  `mark` decimal(4,1) NOT NULL COMMENT '成绩',
  PRIMARY KEY (`sno`,`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

*** 注：

float(4,1)在新版MySQL中已被废弃，虽能被使用但不建议，可以替代为DECIMAL(4,1)更合适
两个主键要写成联合主键的形式

表4

mysql> create database if not exists teacher;
Query OK, 1 row affected (0.00 sec)

mysql> use teacher;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table teacher(
    -> tno char(20) not null primary key comment '教师编号',
    ->  tname varchar(20) not null comment '教师姓名',
    ->  tage tinyint unsigned NOT NULL COMMENT '教师年龄',
    -> tsex enum('0','1') NOT NULL DEFAULT '1' COMMENT '教师性别(1男0女)',
    -> prof varchar(20) default null comment '教师职称',
    -> depart varchar(20) not null comment '教师部门'
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> show create table teacher \G
*************************** 1. row ***************************
       Table: teacher
Create Table: CREATE TABLE `teacher` (
  `tno` char(20) NOT NULL COMMENT '教师编号',
  `tname` varchar(20) NOT NULL COMMENT '教师姓名',
  `tage` tinyint unsigned NOT NULL COMMENT '教师年龄',
  `tsex` enum('0','1') NOT NULL DEFAULT '1' COMMENT '教师性别(1男0女)',
  `prof` varchar(20) DEFAULT NULL COMMENT '教师职称',
  `depart` varchar(20) NOT NULL COMMENT '教师部门',
  PRIMARY KEY (`tno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

字段	数据类型	要求是否为空	注释
sno	最多20位	否	学号(主键)
sname	可变长	否	学生姓名
sage	最小整数非负数	否	学生年龄
ssex	0,1	否	学生性别(1为男0为女)默认为男
sbirthday	时间类型	默认为空	学生生日
class	可变长	否	学生班级

字段	数据类型要求	是否为空	注释
cno	最多为20位	否	课程号(主键)
cname	可变长	否	课程名称
tno	可变长	否	教师编号

字段	数据类型要求	是否为空	注释
tno	最多20位	否	教师编号(主键)
tname	可变长	否	教师姓名
tage	最小整数，非负数	否	教师年龄
tsex	0,1	否	教师性别(1男，0女)默认为男
prof	可变长	是	教师职称
depart	可变长	否	教师部门