MySQL教程-优快云博客

本文链接：https://blog.youkuaiyun.com/l218623/article/details/112585603

MySQL连接

从命令行中连接mysql服务器：

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

解释：以上使用了root用户登录到mysql服务器，root是为了保证用户权限足够，root用户拥有最高权限（当然也可以使用其他任何用户登录，前提是该用户权限足够）。
以上命令执行后会出现：

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2854760 to server version: 5.0.9

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

举例：
在这里插入图片描述
当没有密码时，也可以：

登录成功后：

解释：出现了 mysql> 命令提示窗口，可以在上面执行任何SQL语句。
注意：所有的数据库名，表名，表字段都是区分大小写的，所以你在使用SQL命令时需要输入正确的名称。
退出 mysql> 命令提示窗口：

mysql> exit

输出：

Bye

举例：
在这里插入图片描述

MySQL创建数据库

使用 create 命令创建数据库

[root@host]# mysql -u root -p   
Enter password:******  # 登录后进入终端

mysql> create DATABASE 数据库名;

举例：
在这里插入图片描述

也可以使用 mysqladmin 创建数据库：

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

举例：
在这里插入图片描述

MySQL删除数据库

使用 drop 命令删除数据库：

[root@host]# mysql -u root -p   
Enter password:******  # 登录后进入终端

mysql> DROP DATABASE 数据库名;

举例：
在这里插入图片描述

使用 mysqladmin 删除数据库：

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

执行后：

Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'RUNOOB' database [y/N] y
Database "RUNOOB" dropped

解释：这是一个提示框，来确认是否真的删除数据库，因为执行删除命令后，所有数据将会消失，所以务必谨慎删除。
举例：
在这里插入图片描述

MySQL选择数据库

使用 use 命令选择数据库：

[root@host]# mysql -u root -p
Enter password:******
mysql> use RUNOOB;
Database changed
mysql>

举例：
在这里插入图片描述
*解释：*在连接到MySQL数据库后，可能有多个可以操作的数据库，所以你需要选择你要操作的数据库，执行以上命令后，你就已经成功的选择了RUNOOB数据库，在后续的操作中都会在RUNOOB数据库中会执行。

MySQL数据类型

MySQL支持多种类型，大致可以分为三类：数值，日期/时间和字符串（字符）类型。
数值类型：
MySQL支持所有标准SQL数值数据类型，包括严格数值数据类型和近似数值数据类型。
在这里插入图片描述
日期和时间类型：

字符串类型：

注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如char(30) 就可以存储 30 个字符。
char 和 varchar 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
binary 和 varbinary 类似于 char 和 varchar，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

MySQL创建数据表

创建数据表需要：

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

使用 create table 创建数据表：

root@host# mysql -u root -p
Enter password:*******
mysql> use RUNOOB;
Database changed
mysql> CREATE TABLE runoob_a1(
   -> runoob_id INT NOT NULL AUTO_INCREMENT,
   -> runoob_title VARCHAR(100) NOT NULL,
   -> runoob_author VARCHAR(40) NOT NULL,
   -> submission_date DATE,
   -> PRIMARY KEY ( runoob_id )
   -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.16 sec)
mysql>

解释：
1.AUTO_INCREMENT 定义列为自增的属性，一般用于主键，数值会自动加1。
2.PRIMARY KEY 关键字用于定义列为主键，可以使用多列来定义主键，列间以逗号分隔。
3.ENGINE 设置存储引擎，CHARSET 设置编码。
*注意：
<1 *MySQL命令终止符为分号。
<2 ->是换行符标识。
举例：

在RUNOOB数据库中创建数据表runoob_a1。

在这里插入图片描述

MySQL删除数据表

使用 drop table 删除数据表：

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

举例：

删除数据表runoob_a3。

在这里插入图片描述

MySQL插入数据

使用 insert into 命令插入数据：

root@host# mysql -u root -p password;
Enter password:*******
mysql> use RUNOOB;
Database changed
mysql> INSERT INTO runoob_tbl 
    -> (runoob_title, runoob_author, submission_date)
    -> VALUES
    -> ("学习 PHP", "菜鸟教程", NOW());
Query OK, 1 rows affected, 1 warnings (0.01 sec)
mysql> INSERT INTO runoob_tbl
    -> (runoob_title, runoob_author, submission_date)
    -> VALUES
    -> ("学习 MySQL", "菜鸟教程", NOW());
Query OK, 1 rows affected, 1 warnings (0.01 sec)
mysql> INSERT INTO runoob_tbl
    -> (runoob_title, runoob_author, submission_date)
    -> VALUES
    -> ("JAVA 教程", "RUNOOB.COM", '2016-05-06');
Query OK, 1 rows affected (0.00 sec)
mysql>

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

可以一条一条的插入

在这里插入图片描述

也可以多条记录一起插入

在这里插入图片描述

MySQL查询数据

使用 select 查询数据：

SELECT column_name,column_name    //列名（字段名）
FROM table_name                  //表名
[WHERE Clause]
[LIMIT n][ OFFSET m]

解释：

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

举例：
在这里插入图片描述

MySQL WHERE子句

使用 where子句设置条件：

SELECT column_name,column_name 
FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

解释：

查询时可以使用多个表，用逗号间隔。
可以在 where子句中指定任何条件。
可以使用 and 或者 or 指定一个或多个条件。
类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。
如果给定条件在表中没有任何匹配的记录，那么返回空。

举例：

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

在这里插入图片描述
以上例子中使用了 BINARY 关键字，是区分大小写的，所以 runoob_author=‘runoob.com’ 的查询条件是没有数据的。

MySQL UPDATE 更新

使用 update 命令更新MySQL中的数据：

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

解释：

可以同时更新一个或多个字段。
可以使用 where子句指定任何条件。
可以在一个单独表中同时更新数据。

举例：
在这里插入图片描述
从结果上看，如 runoob_id 为 3 的 runoob_title 已经被修改。

MySQL DELETE 语句

使用 delete from 命令来删除MySQL数据表中的记录：

DELETE FROM table_name [WHERE Clause]

解释：

如果没有指定 where 子句，MySQL表中的所有记录将被删除。
可以在单个表中一次性删除记录。

举例：
在这里插入图片描述
从结果可知，runoob_author 为 FK 的记录已经被删除。

MySQL LIKE 子句

使用 like子句从数据表中读取指定字符的记录：

LIKE 子句中使用百分号 %字符来表示任意字符，类似于UNIX或正则表达式中的星号 *。
如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。

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

解释：

可以使用 like子句代替等号 = 。
like 通常与 % 一同使用，类似于一个元字符的搜索。
可以使用 and 或者 or 指定一个或多个条件。
也可以在 DELETE 或 UPDATE 命令中使用 WHERE…LIKE子句来指定条件。

举例：
在这里插入图片描述

MySQL 排序

使用 order by子句将查询数据排序后再返回数据：

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

解释：

可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
可以设定多个字段来排序。
可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。默认情况下，是按升序排列。
可以添加 WHERE…LIKE子句来设置条件。

举例：
在这里插入图片描述

MySQL GROUP BY 语句

使用 GROUP BY 语句根据一个或多个列对结果集进行分组。

SELECT column_name, function(column_name)   //function 表示在分组的列上我们可以使用的 COUNT、SUM、AVG 等函数。
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

举例：
先创建表：
在这里插入图片描述
再进行分组：

将数据表按名字进行分组，并统计每个人有多少条记录（每个名字出现的次数）：

在这里插入图片描述
使用 WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）

举例：

我们将以上的数据表按名字进行分组，再统计每个人登录的次数

其中记录 NULL 表示所有人的登录次数。

可以使用 coalesce 来设置一个可以取代 NULL 的名称。
coalesce 语法：

select coalesce(a,b,c);

参数说明：如果a == null,则选择b；如果b == null,则选择c；如果a != null,则选择a；如果a b c 都为null ，则返回为null（没意义）。
举例：

以下例子中如果名字为空我们使用总数代替：

MySQL 连接的使用

由于在真正的应用中我们经常需要从多个数据表中读取数据。所以下面将介绍如何使用MySQL的 JOIN 在两个或多个表中查询数据。
可以在 SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。
JOIN 按照功能大致分为如下三类：

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。

LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。

RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

首先先创建两张表：
表runoob_a1：
在这里插入图片描述
表runoob_a2：

在命令提示符中使用 INNER JOIN (也可以省略 INNER 使用 JOIN，效果一样)

两张表内连接后：

等价于：

在命令提示符中使用 LEFT JOIN
MySQL LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。

在这里插入图片描述
两张表左连接后：

在命令提示符中使用 RIGHT JOIN
MySQL RIGHT JOIN 会读取右边数据表的全部数据，即便左边边表无对应数据。

两张表右连接后：

MySQL NULL 值处理

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

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

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

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

先创建一个表 runoob_a3：
在这里插入图片描述
使用=和！=判断NULL值

我们可以看到 = 和 != 运算符是不起作用的
注意：

不能使用 = NULL 或 != NULL 在列中查找 NULL 值。

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

用is和is not 判断NULL值：
在这里插入图片描述

MySQL 正则表达式

查找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 事务处理主要有两种方法：

用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认

直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

举例：

初始表为空

mysql> begin;  # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into runoob_transaction_test value(5);   
//插入数据5
Query OK, 1 rows affected (0.01 sec)
 
mysql> insert into runoob_transaction_test value(6);
//插入数据6
Query OK, 1 rows affected (0.00 sec)

mysql> commit; # 提交事务
Query OK, 0 rows affected (0.01 sec)
 
mysql>  select * from runoob_transaction_test;
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)

mysql> begin;    # 开始事务
Query OK, 0 rows affected (0.00 sec)

mysql>  insert into runoob_transaction_test values(7);   //插入数据7
Query OK, 1 rows affected (0.00 sec)

mysql> rollback;   # 回滚
Query OK, 0 rows affected (0.00 sec)

mysql>   select * from runoob_transaction_test;   # 因为回滚所以数据7没有插入
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)

MySQL ALTER命令

使用 ALTER 命令来修改数据表名或者修改数据表字段：

首先创建一个只含表字段的空表：
在这里插入图片描述
查看其表结构（表字段）：

删除，添加或修改表字段
（使用 ADD 或 DROP 子句）

删除表中的i字段

在这里插入图片描述

在表中添加i字段

在这里插入图片描述

指定新增字段位置

first 设定为第一列
after 设定为某个字段之后

如果你想重置数据表字段的位置就需要先使用 DROP 删除字段然后使用 ADD 来添加字段并设置位置。
修改字段类型及名称
（使用 MODIFY 或 CHANGE 子句）

把字段 c 的类型从 char(1) 改为 int(11)

mysql> ALTER TABLE runoob_c MODIFY c INT;

在这里插入图片描述

把字段 c 的类型从 int(11) 改为 char(10)

mysql> ALTER TABLE runoob_c CHANGE c c CHAR(10);

在这里插入图片描述

修改 p 的名称为 h 并把其类型 int(11) 改成 varchar(2)

mysql> ALTER TABLE runoob_c CHANGE  p h VARCHAR(2);

在这里插入图片描述

ALTER TABLE 对 Null 值和默认值的影响
当你修改字段时，你可以指定是否包含值或者是否设置默认值。（如果不设置默认值，MySQL会自动设置该字段默认为NULL）

mysql> ALTER TABLE runoob_c 
    -> MODIFY h BIGINT NOT NULL DEFAULT 100;

举例：

在修改字段 h 的类型为 bigint(20) 时指定字段 h 为 NOT NULL 且默认值为100 。

在这里插入图片描述

修改和删除字段的默认值

使用 ALTER SET 来修改字段的默认值

在这里插入图片描述

使用 ALTER 命令及 DROP子句来删除字段的默认值

在这里插入图片描述

修改数据表类型

使用 ALTER 命令及 TYPE 子句

在这里插入图片描述

修改表名

可以在ALTER TABLE 语句中使用 RENAME 子句

在这里插入图片描述

MySQL 基础语法

MySQL连接

MySQL创建数据库

MySQL删除数据库

MySQL选择数据库

MySQL数据类型

MySQL创建数据表

MySQL删除数据表

MySQL插入数据

MySQL查询数据

MySQL WHERE子句

MySQL UPDATE 更新

MySQL DELETE 语句

MySQL LIKE 子句

MySQL 排序

MySQL GROUP BY 语句

MySQL 连接的使用

MySQL NULL 值处理

MySQL 正则表达式

MySQL 事务

MySQL ALTER命令