MySQL数据库基本操作

MySQL基本操作

我们开始吧
Let us go！！

连接数据库

以下是从命令行中连接mysql服务器的简单实例：

[root@host]# mysql -u root -p
Enter password:******

创建数据库

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

[root@host]# mysqladmin -u root -p create TUTORIALS
Enter password:******

删除数据库

以下实例删除数据库TUTORIALS

[root@host]# mysqladmin -u root -p drop TUTORIALS
Enter password:******

选择数据库

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

use TUTORIALS;

创建数据表

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

CREATE TABLE table_name (column_name column_type);

CREATE TABLE tutorials_tbl(
    tutorial_id INT NOT NULL AUTO_INCREMENT,
    tutorial_title VARCHAR(100) NOT NULL,
    tutorial_author VARCHAR(40) NOT NULL,
    submission_date DATE,
    PRIMARY KEY ( tutorial_id )
    ); )
);

删除数据表

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

root@host# mysql -u root -p
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> DROP TABLE tutorials_tbl
Query OK, 0 rows affected (0.8 sec)
mysql>

插入数据

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

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

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

查询数据

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

SELECT field1, field2,...fieldN table_name1, table_name2...
[WHERE Clause]
[OFFSET M ][LIMIT N]

• 查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。

• SELECT 命令可以读取一条或者多条记录。

• 你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据

• 你可以使用 WHERE 语句来包含任何条件。

• 你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

• 你可以使用 LIMIT 属性来设定返回的记录数。

where 子句

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

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

• 查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。

• 你可以在WHERE子句中指定任何条件。

• 你可以使用AND或者OR指定一个或多个条件。

• WHERE子句也可以运用于SQL的 DELETE 或者 UPDATE 命令。

• WHERE 子句类似于程序语言中的if条件，根据 MySQL 表中的字段值来读取指定的数据。

UPDATE 查询

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

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

• 你可以同时更新一个或多个字段。

• 你可以在 WHERE 子句中指定任何条件。

• 你可以在一个单独表中同时更新数据。

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

DELETE 语句

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

DELETE FROM table_name [WHERE Clause]

• 如果没有指定 WHERE 子句，MySQL表中的所有记录将被删除。

• 你可以在 WHERE 子句中指定任何条件

• 您可以在单个表中一次性删除记录。

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

LIKE 子句(%a%)

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

SELECT field1, field2,...fieldN table_name1, table_name2...
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

• 你可以在WHERE子句中指定任何条件。

• 你可以在WHERE子句中使用LIKE子句。

• 你可以使用LIKE子句代替等号(=)。

• LIKE 通常与 % 一同使用，类似于一个元字符的搜索。

• 你可以使用AND或者OR指定一个或多个条件。

• 你可以在 DELETE 或 UPDATE 命令中使用 WHERE…LIKE 子句来指定条件。

排序

以下是SQL SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据：

SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]

• 你可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。

• 你可以设定多个字段来排序。

• 你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升排列。

• 你可以添加 WHERE…LIKE 子句来设置条件。

Join

你可以在SELECT, UPDATE 和 DELETE 语句中使用Mysql 的 join 来联合多表查询。

接下来我们就使用MySQL的JOIN来连接以上两张表来读取tutorials_tbl表中所有tutorial_author字段在tcount_tbl表对应的tutorial_count字段值：

SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
     FROM tutorials_tbl a, tcount_tbl b
     WHERE a.tutorial_author = b.tutorial_author;

INNER JOIN关键字

INNER JOIN 关键字在表中存在至少一个匹配时返回行。

SQL INNER JOIN 语法

SELECT column_name(s)
FROM table1  
INNER JOIN table2 
ON table1.column_name = table2.column_name;

或：

SELECT column_name(s) 
FROM table1  
JOIN table2 
ON table1.column_name = table2.column_name;

注释 : INNER JOIN 与 JOIN 是相同的。

LEFT JOIN

MySQL left join 与 join 有所不同。 MySQL LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。

mysql> SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
    -> FROM tutorials_tbl a LEFT JOIN tcount_tbl b
    -> ON a.tutorial_author = b.tutorial_author;
+-------------+-----------------+----------------+
| tutorial_id | tutorial_author | tutorial_count |
+-------------+-----------------+----------------+
|           1 | John Poul       |              1 |
|           2 | Abdul S         |           NULL |
|           3 | Sanjay          |              1 |
+-------------+-----------------+----------------+

以上实例中使用了LEFT JOIN，该语句会读取左边的数据表tutorials_tbl的所有选取的字段数据，即便在右侧表tcount_tbl中没有对应的tutorial_author字段值。

NULL 值处理

我们已经知道MySQL使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。

为了处理这种情况，MySQL提供了三大运算符:

• IS NULL: 当列的值是NULL,此运算符返回true。

• IS NOT NULL: 当列的值不为NULL, 运算符返回true。

• <=>: 比较操作符（不同于=运算符），当比较的的两个值为NULL时返回true。

关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。

在MySQL中，NULL值与任何其它值的比较（即使是NULL）永远返回false，即 NULL = NULL 返回false 。

MySQL中处理NULL使用IS NULL和IS NOT NULL运算符。

查找数据表中 tutorial_count 列是否为 NULL，必须使用IS NULL和IS NOT NULL，如下实例：

mysql> SELECT * FROM tcount_tbl 
    -> WHERE tutorial_count IS NULL;
+-----------------+----------------+
| tutorial_author | tutorial_count |
+-----------------+----------------+
| mahnaz          |           NULL |
| Jen             |           NULL |
+-----------------+----------------+
2 rows in set (0.00 sec)
mysql> SELECT * from tcount_tbl 
    -> WHERE tutorial_count IS NOT NULL;
+-----------------+----------------+
| tutorial_author | tutorial_count |
+-----------------+----------------+
| mahran          |             20 |
| Gill            |             20 |
+-----------------+----------------+
2 rows in set (0.00 sec)

正则表达式

MySQL可以通过 LIKE …% 来进行模糊匹配。

MySQL 同样也支持其他正则表达式的匹配， MySQL中使用 REGEXP 操作符来进行正则表达式匹配。

^ $ . [...] [^...] p1|p2|p3 * + {n} {m,n}

查找name字段中以’st’为开头的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

查找name字段中以’ok’为结尾的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

查找name字段中包含’mar’字符串的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';

查找name字段中以元音字符开头且以’ok’字符串结尾的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

• 在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务

• 事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行

• 事务用来管理insert,update,delete语句

一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）

• 1、事务的原子性: 一组事务，要么成功；要么撤回。

• 2、稳定性： 有非法数据（外键约束之类），事务撤回。

• 3、隔离性: 事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。

• 4、可靠性: 软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。

在Mysql控制台使用事务来操作

1，开始一个事务

start transaction

2, 做保存点

save point 保存点名称

3, 操作

4，可以回滚，可以提交，没有问题，就提交，有问题就回滚。

ALTER命令

当我们需要修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令。

如下命令使用了 ALTER 命令及 DROP 子句来删除以上创建表的 i 字段：

mysql> ALTER TABLE testalter_tbl  DROP i;

如果数据表中只剩余一个字段则无法使用DROP来删除字段。

MySQL 中使用 ADD 子句来想数据表中添加列，如下实例在表 testalter_tbl 中添加 i 字段，并定义数据类型:

mysql> ALTER TABLE testalter_tbl ADD i INT;

执行以上命令后，i 字段会自动添加到数据表字段的末尾。

如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 。

把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，可以执行以下命令:

mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);

使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段的类型及名称。尝试如下实例：

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;

<p如果你现在想把字段 j=“” 从=“” bigint=“” 修改为=“” int，sql语句如下：<=“” p=“”>

mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

修改表名

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

约束

NOT NULL约束SQL NOT NULL 约束 | 菜鸟教程 (runoob.com)

UNIQUE约束SQL UNIQUE 约束 | 菜鸟教程 (runoob.com)

PRIMARY KEY 约束SQL PRIMARY KEY 约束 | 菜鸟教程 (runoob.com)

FOREIGN KEY 约束SQL FOREIGN KEY 约束 | 菜鸟教程 (runoob.com)

CHECK 约束 [SQL CHECK 约束 | 菜鸟教程 (runoob.com)]

DEFAULT 约束SQL DEFAULT 约束 | 菜鸟教程 (runoob.com)

AUTO INCREMENT 字段

我们通常希望在每次插入新记录时，自动地创建主键字段的值。

我们可以在表中创建一个 auto-increment 字段。

下面的 SQL 语句把 “Persons” 表中的 “ID” 列定义为 auto-increment 主键字段：

CREATE TABLE Persons
(
    ID int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255),
    PRIMARY KEY (ID)
)

默认地，AUTO_INCREMENT 的开始值是 1，每条新记录递增 1。

要让 AUTO_INCREMENT 序列以其他的值起始，请使用下面的 SQL 语法：

ALTER TABLE Persons AUTO_INCREMENT=100

要在 “Persons” 表中插入新记录，我们不必为 “ID” 列规定值（会自动添加一个唯一的值）

INSERT INTO Persons (FirstName,LastName) VALUES ('Lars','Monsen')

MySQL多表操作(一对一 一对多 多对多)