数据库基本操作

发表于 更新于 分类于

MySQL 创建数据库

我们可以在登陆 MySQL 服务后，使用 create 命令创建数据库，语法如下:

1
CREATE DATABASE 数据库名;

以下命令简单的演示了创建数据库的过程，数据名为 SXT:

1
2
3
4
[root@host]# mysql -u root -p
Enter password:******  # 登录后进入终端
​
mysql> create DATABASE SXT;

使用 mysqladmin 创建数据库

使用普通用户，你可能需要特定的权限来创建或者删除 MySQL 数据库。

所以我们这边使用 root 用户登录，root 用户拥有最高权限，可以使用 mysql mysqladmin 命令来创建数据库。

以下命令简单的演示了创建数据库的过程，数据名为 SXT:

1
2
[root@host]# mysqladmin -u root -p create SXT
Enter password:******

以上命令执行成功后会创建 MySQL 数据库 SXT。

MySQL 删除数据库

使用普通用户登陆 MySQL 服务器，你可能需要特定的权限来创建或者删除 MySQL 数据库，所以我们这边使用 root 用户登录，root 用户拥有最高权限。

在删除数据库过程中，务必要十分谨慎，因为在执行删除命令后，所有数据将会消失。

drop 命令删除数据库

drop 命令格式：

1
drop database <数据库名>;

例如删除名为 SXT 的数据库：

1
mysql> drop database SXT;

MySQL 创建数据表

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

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

语法

以下为创建 MySQL 数据表的 SQL 通用语法：

1
CREATE TABLE table_name (column_name column_type);

以下例子中我们将在 sxt 数据库中创建数据表 sxt_tbl：

1
2
3
4
5
6
7
CREATE TABLE IF NOT EXISTS `sxt_tbl`(
   `sxt_id` INT UNSIGNED AUTO_INCREMENT,
   `sxt_title` VARCHAR(100) NOT NULL,
   `sxt_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `sxt_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

实例解析：

  • 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为 NULL ，就会报错。
  • AUTO_INCREMENT 定义列为自增的属性，一般用于主键，数值会自动加 1。
  • PRIMARY KEY 关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。
  • ENGINE 设置存储引擎，CHARSET 设置编码。

MySQL 删除数据表

MySQL 中删除数据表是非常容易操作的， 但是你再进行删除表操作时要非常小心，因为执行删除命令后所有数据都会消失。

语法

以下为删除 MySQL 数据表的通用语法：

1
DROP TABLE table_name ;

在命令提示窗口中删除数据表

在 mysql>命令提示窗口中删除数据表 SQL 语句为 DROP TABLE ：

实例

以下实例删除了数据表 runoob_tbl:

1
2
3
4
5
6
7
root@host# mysql -u root -p
Enter password:*******
mysql> use RUNOOB;
Database changed
mysql> DROP TABLE sxt_tbl
Query OK, 0 rows affected (0.8 sec)
mysql>

MySQL 选择数据库

在你连接到 MySQL 数据库后，可能有多个可以操作的数据库，所以你需要选择你要操作的数据库。

从命令提示窗口中选择 MySQL 数据库

在 mysql> 提示窗口中可以很简单的选择特定的数据库。你可以使用 SQL 命令来选择指定的数据库。

实例

以下实例选取了数据库 SXT:

1
2
3
4
5
[root@host]# mysql -u root -p
Enter password:******
mysql> use SXT;
Database changed
mysql>

执行以上命令后，你就已经成功选择了 SXT 数据库，在后续的操作中都会在 SXT 数据库中执行。

**注意:**所有的数据库名，表名，表字段都是区分大小写的。所以你在使用 SQL 命令时需要输入正确的名称。

MySQL 数据类型

MySQL 中定义数据字段的类型对你数据库的优化是非常重要的。

MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

数值类型

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

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

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

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

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

类型大小范围（有符号）范围（无符号）用途
TINYINT1 字节(-128，127)(0，255)小整数值
SMALLINT2 字节(-32 768，32 767)(0，65 535)大整数值
MEDIUMINT3 字节(-8 388 608，8 388 607)(0，16 777 215)大整数值
INT 或 INTEGER4 字节(-2 147 483 648，2 147 483 647)(0，4 294 967 295)大整数值
BIGINT8 字节(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)(0，18 446 744 073 709 551 615)极大整数值
FLOAT4 字节(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)0，(1.175 494 351 E-38，3.402 823 466 E+38)单精度 浮点数值
DOUBLE8 字节(-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)双精度 浮点数值
DECIMAL对 DECIMAL(M,D) ，如果 M>D，为 M+2 否则为 D+2依赖于 M 和 D 的值依赖于 M 和 D 的值小数值

日期和时间类型

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

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

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

类型大小 (字节)范围格式用途
DATE31000-01-01/9999-12-31YYYY-MM-DD日期值
TIME3‘-838:59:59’/‘838:59:59’HH:MM:SS时间值或持续时间
YEAR11901/2155YYYY年份值
DATETIME81000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值
TIMESTAMP41970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038 年 1 月 19 日 凌晨 03:14:07YYYYMMDD HHMMSS混合日期和时间值，时间戳

字符串类型

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

类型大小用途
CHAR0-255 字节定长字符串
VARCHAR0-65535 字节变长字符串
TINYBLOB0-255 字节不超过 255 个字符的二进制字符串
TINYTEXT0-255 字节短文本字符串
BLOB0-65 535 字节二进制形式的长文本数据
TEXT0-65 535 字节长文本数据
MEDIUMBLOB0-16 777 215 字节二进制形式的中等长度文本数据
MEDIUMTEXT0-16 777 215 字节中等长度文本数据
LONGBLOB0-4 294 967 295 字节二进制形式的极大文本数据
LONGTEXT0-4 294 967 295 字节极大文本数据

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

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

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

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

SQL 插入数据

MySQL 表中使用 INSERT INTO SQL 语句来插入数据。

你可以通过 mysql> 命令提示窗口中向数据表中插入数据，或者通过 PHP 脚本来插入数据。

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

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

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

通过命令提示窗口插入数据

以下我们将使用 SQL INSERT INTO 语句向 MySQL 数据表 SXT 插入数据

实例

以下实例中我们将向 SXT 表插入三条数据:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
root@host# mysql -u root -p password;
Enter password:*******
mysql> use SXT;
Database changed
mysql> INSERT INTO SXTl
    -> (runoob_title, runoob_author, submission_date)
    -> VALUES
    -> ("学习 PHP", "SXT", NOW());
Query OK, 1 rows affected, 1 warnings (0.01 sec)
mysql> INSERT INTO runoob_tbl
    -> (runoob_title, SXT, submission_date)
    -> VALUES
    -> ("学习 MySQL", "SXT", NOW());
Query OK, 1 rows affected, 1 warnings (0.01 sec)
mysql> INSERT INTO runoob_tbl
    -> (runoob_title, SXT_author, submission_date)
    -> VALUES
    -> ("JAVA 教程", "SXT.COM", '2016-05-06');
Query OK, 1 rows affected (0.00 sec)
mysql>

注意： 使用箭头标记 -> 不是 SQL
语句的一部分，它仅仅表示一个新行，如果一条 SQL 语句太长，我们可以通过回车键来创建一个新行来编写 SQL 语句，SQL 语句的命令结束符为分号;。

MySQL UPDATE 更新

如果我们需要修改或更新 MySQL 中的数据，我们可以使用 SQL UPDATE 命令来操作。

语法

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

1
2
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
  • 你可以同时更新一个或多个字段。
  • 你可以在 WHERE 子句中指定任何条件。
  • 你可以在一个单独表中同时更新数据。

当你需要更新数据表中指定行的数据时 WHERE 子句是非常有用的。

通过命令提示符更新数据

以下我们将在 SQL UPDATE 命令使用 WHERE 子句来更新 runoob_tbl 表中指定的数据：

MySQL DELETE 语句

你可以使用 SQL 的 DELETE FROM 命令来删除 MySQL 数据表中的记录。

你可以在 mysql> 命令提示符或 PHP 脚本中执行该命令。

语法

以下是 SQL DELETE 语句从 MySQL 数据表中删除数据的通用语法：

1
DELETE FROM table_name [WHERE Clause]
  • 如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
  • 你可以在 WHERE 子句中指定任何条件
  • 您可以在单个表中一次性删除记录。

当你想删除数据表中指定的记录时 WHERE 子句是非常有用的。

从命令行中删除数据

这里我们将在 SQL DELETE 命令中使用 WHERE 子句来删除 MySQL 数据表 runoob_tbl 所选的数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 查询视图内容等同于查询表操作
SELECT * from bookallinfo where cataory = '历史传记';
-- 视图实现模糊查找
SELECT * from bookallinfo where bookname like "%小%";
​
-- 更新多字段
UPDATE booktable SET bookname = "小时代1",authorid=0,score = NULL  where id = 2;
​
​
-- 删除
-- 逻辑删除和物理删除
​
-- 逻辑删除小时代书
UPDATE booktable set isdelete = "true" where id = 2
​
-- 物理删除
-- DELETE from booktable WHERE bookname = "鬼吹灯"
DELETE from booktable