MySQL 创建数据库及简单增删改查

程序员文章站 2022-06-04 11:06:50

...

MySQL 创建数据库及简单增删改查

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

CREATE DATABASE 数据库名;

登入：

Enter password: ***********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.22-log 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> CREATE DATABASE IF NOT EXISTS CODES DEFAULT CHARSET utf8 COLLATE utf8_general_ci;//创建数据库

输出结果：

mysql> CREATE DATABASE IF NOT EXISTS CODES DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql>

创建数据库，该命令的作用：

1. 如果数据库不存在则创建，存在则不创建。
1. 创建CODES数据库，并设定编码集为utf8

MySQL 删除数据库

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

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

drop 命令删除数据库

drop 命令格式：

drop database <数据库名>;

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

mysql> drop database CODES;

执行结果：

mysql> drop database CODES;
Query OK, 0 rows affected (0.01 sec)

mysql>

MySQL 选择数据库

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

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

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

实例

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

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| codes              |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| tuberonion         |
| world              |
+--------------------+
8 rows in set (0.00 sec)

mysql> USE CODES;
Database changed
mysql>

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

注意:所有的数据库名，表名，表字段都是区分大小写的。所以你在使用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。下面的表显示了需要的每个整数类型的存储和范围。

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
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 233 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 351 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)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

日期和时间类型

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

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

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

类型	大小 (字节)	范围	格式	用途
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	8	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:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

字符串类型

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

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-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 类型，可存储的最大长度不同，可根据实际情况选择。

MySQL关键字	含义
NULL	数据列可包含NULL值
NOT NULL	数据列不允许包含NULL值
DEFAULT	默认值
PRIMARY KEY	主键
AUTO_INCREMENT	自动递增，适用于整数类型
UNSIGNED	无符号
CHARACTER SET name	指定一个字符集

MySQL 5.0 以上的版本：

1、一个汉字占多少长度与编码有关：

UTF－8：一个汉字＝3个字节

GBK：一个汉字＝2个字节

2、varchar(n) 表示 n 个字符，无论汉字和英文，Mysql 都能存入 n 个字符，仅是实际字节长度有所区别

3、MySQL 检查长度，可用 SQL 语言来查看：

select LENGTH(fieldname) from tablename

例如，我查询我自己数据库中(此数据库，我并未给出)：

mysql> SELECT * FROM ht_app;
+----------------------+---------------------+---------------------+-------------+----------------------------------+--------------------------------------------------------+----------------+---------------------------------------------------+------------------+---------------------------------------------------+
| app_id               | gmt_create          | gmt_modified        | app_version | app_md5                          | app_url                                                | app_picture_id | app_picture_url                                   | login_picture_id | login_picture_url                                 |
+----------------------+---------------------+---------------------+-------------+----------------------------------+--------------------------------------------------------+----------------+---------------------------------------------------+------------------+---------------------------------------------------+
| com.hengtong.henchat | 2018-06-14 13:53:50 | 2018-06-28 13:53:54 | 1.0.1       | 29F85D405C47544B69CDC8948878BB5E | http://oo94hcyew.bkt.clouddn.com/app-version-1.0.1.apk | 20180622.png   | G:\git\TransactionHT\src\main\resources\pictures\ | 20180621.png     | G:\git\TransactionHT\src\main\resources\pictures\ |
+----------------------+---------------------+---------------------+-------------+----------------------------------+--------------------------------------------------------+----------------+---------------------------------------------------+------------------+---------------------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT LENGTH(app_id) FROM ht_app;
+----------------+
| LENGTH(app_id) |
+----------------+
|             20 |
+----------------+
1 row in set (0.00 sec)

mysql>

MySQL 创建数据表

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

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

语法

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

CREATE TABLE table_name (column_name column_type);

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

CREATE TABLE IF NOT EXISTS `codes_user`(
   `user_id` INT UNSIGNED AUTO_INCREMENT,
   `user_title` VARCHAR(100) NOT NULL,
   `user_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `user_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

实例解析：

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

通过命令提示符创建表

通过 mysql> 命令窗口可以很简单的创建MySQL数据表。你可以使用 SQL 语句 CREATE TABLE 来创建数据表。

实例

以下为创建数据表 codes_user实例:

mysql> USE CODES;
Database changed
mysql> SHOW TABLES;
Empty set (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `codes_user`(
    ->    `user_id` INT UNSIGNED AUTO_INCREMENT,
    ->    `user_title` VARCHAR(100) NOT NULL,
    ->    `user_author` VARCHAR(40) NOT NULL,
    ->    `submission_date` DATE,
    ->    PRIMARY KEY ( `user_id` )
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

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

mysql>

注意：MySQL命令终止符为分号 (;) 。

MySQL 删除数据表

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

语法

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

DROP TABLE table_name ;

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

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

实例

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


Enter password:*******
mysql> use RUNOOB;
Database changed
mysql> DROP TABLE codes_user
Query OK, 0 rows affected (0.0 sec)
mysql>

删除表内数据，用 delete。格式为：

delete from 表名 where 删除条件;

实例：删除学生表内姓名为张三的记录。

delete from  student where  T_name = "张三";

清除表内数据，保存表结构，用 truncate。格式为：

truncate table 表名;

实例：清除学生表内的所有数据。

truncate  table  student;

删除表用 drop，就是啥都没了。格式为：

drop  table  表名;

实例：删除学生表。

drop table student;

1、当你不再需要该表时，用 drop;

2、当你仍要保留该表，但要删除所有记录时，用 truncate;

3、当你要删除部分记录时，用 delete。

MySQL 删除表的几种情况:

1、drop table table_name : 删除表全部数据和表结构，立刻释放磁盘空间，不管是 Innodb 和 MyISAM;

实例，删除学生表：

drop table student;

2、truncate table table_name : 删除表全部数据，保留表结构，立刻释放磁盘空间，不管是 Innodb 和 MyISAM;

实例，删除学生表：

truncate table student;

3、delete from table_name : 删除表全部数据，表结构不变，对于 MyISAM 会立刻释放磁盘空间，InnoDB 不会释放磁盘空间;

实例，删除学生表：

delete from student;

4、delete from table_name where xxx : 带条件的删除，表结构不变，不管是 innodb 还是 MyISAM 都不会释放磁盘空间;

实例，删除学生表中姓名为 "张三" 的数据：

delete from student where T_name = "张三";

5、delete 操作以后，使用 optimize table table_name 会立刻释放磁盘空间，不管是 innodb 还是 myisam;

实例，删除学生表中姓名为 "张三" 的数据：

delete from student where T_name = "张三";

实例，释放学生表的表空间：

optimize table student;

6、delete from 表以后虽然未释放磁盘空间，但是下次插入数据的时候，仍然可以使用这部分空间。

MySQL 插入数据

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

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

语法

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

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

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

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

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

实例

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

mysql> INSERT INTO codes_user  (user_title, user_author, submission_date) VALUES ("学习 雷锋", "好榜样", NOW());
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO codes_user  (user_title, user_author, submission_date) VALUES ("学习 刘胡兰", "好榜样", NOW());
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO codes_user  (user_title, user_author, submission_date) VALUES ("JAVA JDBC", "好棒羊", '2014-08-06');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT *FROM codes_user;
+---------+------------------+-------------+-----------------+
| user_id | user_title       | user_author | submission_date |
+---------+------------------+-------------+-----------------+
|       1 | 学习 雷锋        | 好榜样      | 2019-01-11      |
|       2 | 学习 刘胡兰      | 好榜样      | 2019-01-11      |
|       3 | JAVA JDBC        | 好棒羊      | 2014-08-06      |
+---------+------------------+-------------+-----------------+
3 rows in set (0.00 sec)

mysql>

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

在以上实例中，我们并没有提供 user_id的数据，因为该字段我们在创建表的时候已经设置它为 AUTO_INCREMENT(自动增加) 属性。 所以，该字段会自动递增而不需要我们去设置。实例中 NOW() 是一个 MySQL 函数，该函数返回日期和时间。

INSERT 插入多条数据

INSERT INTO table_name  (field1, field2,...fieldN)  VALUES  (valueA1,valueA2,...valueAN),(valueB1,valueB2,...valueBN),(valueC1,valueC2,...valueCN)......;

MySQL 查询数据

MySQL 数据库使用SQL SELECT语句来查询数据。

你可以通过 mysql> 命令提示窗口中在数据库中查询数据，或者通过PHP脚本来查询数据。

语法

以下为在MySQL数据库中查询数据通用的 SELECT 语法：

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
SELECT 命令可以读取一条或者多条记录。
你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
你可以使用 WHERE 语句来包含任何条件。
你可以使用 LIMIT 属性来设定返回的记录数。
你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

通过命令提示符获取数据

以下实例我们将通过 SQL SELECT 命令来获取 MySQL 数据表 runoob_tbl 的数据：

实例

以下实例将返回数据表 codes_user的部分记录:

读取数据表：

mysql> SELECT user_author,user_title,submission_date  FROM codes_user LIMIT 1 OFFSET 1;
+-------------+------------------+-----------------+
| user_author | user_title       | submission_date |
+-------------+------------------+-----------------+
| 好榜样      | 学习 刘胡兰      | 2019-01-11      |
+-------------+------------------+-----------------+
1 row in set (0.00 sec)

mysql>

MySQL WHERE 子句

我们知道从 MySQL 表中使用 SQL SELECT 语句来读取数据。

如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句中。

语法

以下是 SQL SELECT 语句使用 WHERE 子句从数据表中读取数据的通用语法：

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

查询语句中你可以使用一个或者多个表，表之间使用逗号, 分割，并使用WHERE语句来设定查询条件。
你可以在 WHERE 子句中指定任何条件。
你可以使用 AND 或者 OR 指定一个或多个条件。
WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。

以下为操作符列表，可用于 WHERE 子句中。

下表中实例假定 A 为 10, B 为 20

操作符	描述	实例
=	等号，检测两个值是否相等，如果相等返回true	(A = B) 返回false。
<>, !=	不等于，检测两个值是否相等，如果不相等返回true	(A != B) 返回 true。
>	大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true	(A > B) 返回false。
<	小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true	(A < B) 返回 true。
>=	大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true	(A >= B) 返回false。
<=	小于等于号，检测左边的值是否小于于或等于右边的值, 如果左边的值小于或等于右边的值返回true	(A <= B) 返回 true。

如果我们想在 MySQL 数据表中读取指定的数据，WHERE 子句是非常有用的。

使用主键来作为 WHERE 子句的条件查询是非常快速的。

如果给定的条件在表中没有任何匹配的记录，那么查询不会返回任何数据。

从命令提示符中读取数据

我们将在SQL SELECT语句使用WHERE子句来读取MySQL数据表 runoob_tbl 中的数据：

实例

以下实例将读取 runoob_tbl 表中 runoob_author 字段值为 Sanjay 的所有记录：

SQL SELECT WHERE 子句

mysql> SELECT user_title,user_author ,submission_date  FROM codes_user WHERE user_author ='好榜样' LIMIT 1 OFFSET 1;
+------------------+-------------+-----------------+
| user_title       | user_author | submission_date |
+------------------+-------------+-----------------+
| 学习 刘胡兰      | 好榜样      | 2019-01-11      |
+------------------+-------------+-----------------+
1 row in set (0.00 sec)

mysql>

MySQL 的 WHERE 子句的字符串比较是不区分大小写的。 你可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。

如下实例:

BINARY 关键字

mysql> SELECT user_title,user_author ,submission_date  FROM codes_user WHERE  BINARY  user_author ='好榜样' LIMIT 1 OFFSET 1;
+------------------+-------------+-----------------+
| user_title       | user_author | submission_date |
+------------------+-------------+-----------------+
| 学习 刘胡兰      | 好榜样      | 2019-01-11      |
+------------------+-------------+-----------------+
1 row in set (0.00 sec)

mysql>

上一篇：各种数据库的连接语句

下一篇： add attribute group and create new attribute to this group

MySQL 创建数据库及简单增删改查

MySQL 创建数据库及简单增删改查

MySQL 删除数据库

drop 命令删除数据库

MySQL 选择数据库

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

实例

MySQL 数据类型

数值类型

日期和时间类型

字符串类型

MySQL 创建数据表

语法

通过命令提示符创建表

实例

MySQL 删除数据表

语法

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

实例

MySQL 插入数据

语法

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

实例

MySQL 查询数据

语法

通过命令提示符获取数据

实例

读取数据表：

MySQL WHERE 子句

语法

从命令提示符中读取数据

SQL SELECT WHERE 子句

BINARY 关键字

PHP简单数据库操作类实例【支持增删改查及链式操作】

PHP简单数据库操作类实例【支持增删改查及链式操作】

MySQL数据库：基本操作及增删改查语句

Windows 8 Metro用C#连接SQLite及创建数据库,数据表的增删改查的实现

Windows 8 Metro用C#连接SQLite及创建数据库,数据表的增删改查的实现

mysql数据库表的创建以及字段的增删改查操作及一些常用的查询命令介绍

Python实现连接MySql数据库及增删改查操作详解

scalikejdbc框架进行mysql数据库增删改查及事务创建

Python连接mysql数据库及简单增删改查操作示例代码

介绍几种关于Mysql数据库增删改查的最简单写法