MySQL DAY2 基础教程

最新推荐文章于 2024-09-25 12:48:48 发布

dragonxcn

最新推荐文章于 2024-09-25 12:48:48 发布

阅读量245

点赞数 1

分类专栏： OpenStack学前基础数据库

本文链接：https://blog.csdn.net/dragonxcn/article/details/63253631

版权

数据库同时被 2 个专栏收录

2 篇文章 0 订阅

订阅专栏

OpenStack学前基础

1 篇文章 0 订阅

订阅专栏

表内容

MariaDB [company]> SELECT * FROM employee_info;
+-----+-------+------+--------+---------+--------+
| id  | name  | age  | salary | address | sex    |
+-----+-------+------+--------+---------+--------+
| 001 | Alex  |   30 |  13000 | NULL    | male   |
| 002 | Jack  |   27 |   8500 | NULL    | male   |
| 003 | Cathy |   23 |   3000 | NULL    | female |
| 004 | John  |   36 |  15000 | NULL    | male   |
| 005 | Bruce |   42 |  30000 | NULL    | male   |
+-----+-------+------+--------+---------+--------+
5 rows in set (0.00 sec)

DISTINCT

作用：重复数据只显示一次

MariaDB [company]> SELECT sex FROM employee_info;
+--------+
| sex    |
+--------+
| male   |
| male   |
| female |
| male   |
| male   |
+--------+
5 rows in set (0.00 sec)

MariaDB [company]> SELECT DISTINCT sex FROM employee_info;
+--------+
| sex    |
+--------+
| male   |
| female |
+--------+
2 rows in set (0.00 sec)

WHERE

操作符	描述
=	等于
!=	不等于
>	大于
<	小于
=	大于等于
<=	小于等于
BETWEEN	在某个范围内
LIKE	搜索某种模式

MariaDB [company]> SELECT * FROM employee_info WHERE age!=23;
+-----+-------+------+--------+---------+------+
| id  | name  | age  | salary | address | sex  |
+-----+-------+------+--------+---------+------+
| 001 | Alex  |   30 |  13000 | NULL    | male |
| 002 | Jack  |   27 |   8500 | NULL    | male |
| 004 | John  |   36 |  15000 | NULL    | male |
| 005 | Bruce |   42 |  30000 | NULL    | male |
+-----+-------+------+--------+---------+------+
4 rows in set (0.00 sec)

AND / OR

AND 逻辑与 / OR 逻辑非

MariaDB [company]> SELECT * FROM employee_info WHERE name='Alex' AND sex='male';
+-----+------+------+--------+---------+------+
| id  | name | age  | salary | address | sex  |
+-----+------+------+--------+---------+------+
| 001 | Alex |   30 |  13000 | NULL    | male |
+-----+------+------+--------+---------+------+
1 row in set (0.00 sec)

MariaDB [company]> SELECT * FROM employee_info WHERE age<=25 OR age >=40;
+-----+-------+------+--------+---------+--------+
| id  | name  | age  | salary | address | sex    |
+-----+-------+------+--------+---------+--------+
| 003 | Cathy |   23 |   3000 | NULL    | female |
| 005 | Bruce |   42 |  30000 | NULL    | male   |
+-----+-------+------+--------+---------+--------+
2 rows in set (0.00 sec)

# AND OR 可以混用
MariaDB [company]> SELECT * FROM employee_info WHERE (name='Alex' AND sex='male') OR age=23;
+-----+-------+------+--------+---------+--------+
| id  | name  | age  | salary | address | sex    |
+-----+-------+------+--------+---------+--------+
| 001 | Alex  |   30 |  13000 | NULL    | male   |
| 003 | Cathy |   23 |   3000 | NULL    | female |
+-----+-------+------+--------+---------+--------+
2 rows in set (0.00 sec)

ORDER BY

默认为升序(ASC)。降序用 DESC。

# 等同于 MariaDB [company]> SELECT * FROM employee_info ORDER BY age ASC;
MariaDB [company]> SELECT * FROM employee_info ORDER BY age;
+-----+-------+------+--------+---------+--------+
| id  | name  | age  | salary | address | sex    |
+-----+-------+------+--------+---------+--------+
| 003 | Cathy |   23 |   3000 | NULL    | female |
| 002 | Jack  |   27 |   8500 | NULL    | male   |
| 001 | Alex  |   30 |  13000 | NULL    | male   |
| 004 | John  |   36 |  15000 | NULL    | male   |
| 005 | Bruce |   42 |  30000 | NULL    | male   |
+-----+-------+------+--------+---------+--------+
5 rows in set (0.00 sec)

# 先按 sex 列升序排列，再按 salary 列降序排列
MariaDB [company]> SELECT * FROM employee_info ORDER BY sex ASC, salary DESC;
+-----+-------+------+--------+---------+--------+
| id  | name  | age  | salary | address | sex    |
+-----+-------+------+--------+---------+--------+
| 005 | Bruce |   42 |  30000 | NULL    | male   |
| 004 | John  |   36 |  15000 | NULL    | male   |
| 001 | Alex  |   30 |  13000 | NULL    | male   |
| 002 | Jack  |   27 |   8500 | NULL    | male   |
| 003 | Cathy |   23 |   3000 | NULL    | female |
+-----+-------+------+--------+---------+--------+
5 rows in set (0.00 sec)

TOP

只显示 top n 行数据，对大型数据表特别有用
MySQL 语法， LIMIT <number>

MariaDB [company]> SELECT * FROM employee_info LIMIT 3;
+-----+-------+------+--------+---------+--------+
| id  | name  | age  | salary | address | sex    |
+-----+-------+------+--------+---------+--------+
| 001 | Alex  |   30 |  13000 | NULL    | male   |
| 002 | Jack  |   27 |   8500 | NULL    | male   |
| 003 | Cathy |   23 |   3000 | NULL    | female |
+-----+-------+------+--------+---------+--------+
3 rows in set (0.00 sec)

# limit 默认从 0 开始计数，默认的 0 可以省略
# 从第 1 行计数，显示 4 行
MariaDB [company]> SELECT * FROM employee_info LIMIT 1, 4;
+-----+-------+------+--------+---------+--------+
| id  | name  | age  | salary | address | sex    |
+-----+-------+------+--------+---------+--------+
| 002 | Jack  |   27 |   8500 | NULL    | male   |
| 003 | Cathy |   23 |   3000 | NULL    | female |
| 004 | John  |   36 |  15000 | NULL    | male   |
| 005 | Bruce |   42 |  30000 | NULL    | male   |
+-----+-------+------+--------+---------+--------+
4 rows in set (0.00 sec)

LIKE

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

# 搜索名字中 'B' 开头的，再一次强调 SQL 不区分大小写。
MariaDB [company]> SELECT * FROM employee_info WHERE name LIKE 'J%';
+-----+------+------+--------+---------+------+
| id  | name | age  | salary | address | sex  |
+-----+------+------+--------+---------+------+
| 002 | Jack |   27 |   8500 | NULL    | male |
| 004 | John |   36 |  15000 | NULL    | male |
+-----+------+------+--------+---------+------+
2 rows in set (0.00 sec)

MariaDB [company]> SELECT * FROM employee_info WHERE name LIKE '%A%';
+-----+-------+------+--------+---------+--------+
| id  | name  | age  | salary | address | sex    |
+-----+-------+------+--------+---------+--------+
| 001 | Alex  |   30 |  13000 | NULL    | male   |
| 002 | Jack  |   27 |   8500 | NULL    | male   |
| 003 | Cathy |   23 |   3000 | NULL    | female |
+-----+-------+------+--------+---------+--------+
3 rows in set (0.00 sec)

# 取反
MariaDB [company]> SELECT * FROM employee_info WHERE name NOT LIKE '%A%';
+-----+-------+------+--------+---------+------+
| id  | name  | age  | salary | address | sex  |
+-----+-------+------+--------+---------+------+
| 004 | John  |   36 |  15000 | NULL    | male |
| 005 | Bruce |   42 |  30000 | NULL    | male |
+-----+-------+------+--------+---------+------+
2 rows in set (0.00 sec)

MySQL 通配符

在搜索数据库中的数据时，SQL 通配符可以替代一个或多个字符。
SQL 通配符必须与 LIKE 运算符一起使用。
在 SQL 中，可使用以下通配符：

通配符	描述
%	替代一个或多个字符
_	仅替代一个字符

MariaDB [company]> SELECT * FROM employee_info WHERE salary LIKE '_0000';
+-----+-------+------+--------+---------+------+
| id  | name  | age  | salary | address | sex  |
+-----+-------+------+--------+---------+------+
| 005 | Bruce |   42 |  30000 | NULL    | male |
+-----+-------+------+--------+---------+------+
1 row in set (0.00 sec)

正则表达式匹配

REGEXP / NOT REGEXP

# 查询 name 以 A 或 B 开头的。仍然大小写不敏感。
MariaDB [company]> SELECT * FROM employee_info WHERE name REGEXP '^[AB]';
+-----+-------+------+--------+---------+------+
| id  | name  | age  | salary | address | sex  |
+-----+-------+------+--------+---------+------+
| 001 | Alex  |   30 |  13000 | NULL    | male |
| 005 | Bruce |   42 |  30000 | NULL    | male |
+-----+-------+------+--------+---------+------+
2 rows in set (0.00 sec)

# 开头 name 不以 A 或 B 开头的。
MariaDB [company]> SELECT * FROM employee_info WHERE name REGEXP '^[^AB]';
+-----+-------+------+--------+---------+--------+
| id  | name  | age  | salary | address | sex    |
+-----+-------+------+--------+---------+--------+
| 002 | Jack  |   27 |   8500 | NULL    | male   |
| 003 | Cathy |   23 |   3000 | NULL    | female |
| 004 | John  |   36 |  15000 | NULL    | male   |
+-----+-------+------+--------+---------+--------+
3 rows in set (0.00 sec)
# 或
MariaDB [company]> SELECT * FROM employee_info WHERE name NOT REGEXP '^[AB]';

IN

IN 操作符允许我们在 WHERE 子句中规定多个值。

MariaDB [company]> SELECT * FROM employee_info WHERE name IN ('Alex', 'Jack');
+-----+------+------+--------+---------+------+
| id  | name | age  | salary | address | sex  |
+-----+------+------+--------+---------+------+
| 001 | Alex |   30 |  13000 | NULL    | male |
| 002 | Jack |   27 |   8500 | NULL    | male |
+-----+------+------+--------+---------+------+
2 rows in set (0.00 sec)

BETWEEN … AND …

操作符 BETWEEN ... AND... 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。

MariaDB [company]> SELECT * FROM employee_info WHERE salary BETWEEN 10000 AND 20000;
+-----+------+------+--------+---------+------+
| id  | name | age  | salary | address | sex  |
+-----+------+------+--------+---------+------+
| 001 | Alex |   30 |  13000 | NULL    | male |
| 004 | John |   36 |  15000 | NULL    | male |
+-----+------+------+--------+---------+------+
2 rows in set (0.00 sec)

AUTO INCREMENT

我们通常希望在每次插入新记录时，自动地创建主键字段的值。
我们可以在表中创建一个 auto-increment 字段。
MySQL 中 auto increment 列必须为 PRIMARY KEY。默认从 1 开始计数。

MariaDB [company]> CREATE TABLE test_table5
    -> (
    -> id int PRIMARY KEY AUTO_INCREMENT,
    -> name varchar(255) NOT NULL
    -> );
Query OK, 0 rows affected (0.07 sec)

MariaDB [company]> INSERT INTO test_table5 (name) VALUE('Alex');
Query OK, 1 row affected (0.05 sec)

MariaDB [company]> INSERT INTO test_table5 (name) VALUE('Bruce');
Query OK, 1 row affected (0.01 sec)

MariaDB [company]> SELECT * FROM test_table5;
+----+-------+
| id | name  |
+----+-------+
|  1 | Alex  |
|  2 | Bruce |
+----+-------+
2 rows in set (0.00 sec)

# 从 100 开始计数
MariaDB [company]> CREATE TABLE test_table6
    -> (
    -> id int PRIMARY KEY AUTO_INCREMENT,
    -> name varchar(255) NOT NULL
    -> )AUTO_INCREMENT=100;
Query OK, 0 rows affected (0.07 sec)

SQL 约束 (Constraints)

约束用于限制加入表的数据的类型。
可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后也可以（通过 ALTER TABLE 语句）。
我们将主要探讨以下几种约束：
* NOT NULL
* UNIQUE
* PRIMARY KEY
* FOREIGN KEY
* CHECK MySQL 不支持 CHECK
* DEFAULT

SQL NOT NULL 约束

NOT NULL 约束强制列不接受 NULL 值。
NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。
下面的 SQL 语句强制 “id” 列和 “name” 列不接受 NULL 值：

MariaDB [company]> CREATE TABLE test_table
    -> (
    -> id varchar(10) NOT NULL,
    -> name varchar(255) NOT NULL,
    -> age int
    -> );
Query OK, 0 rows affected (0.07 sec)

MariaDB [company]> INSERT INTO test_table VALUE('001',null,24);
ERROR 1048 (23000): Column 'name' cannot be null

SQL UNIQUE 约束

UNIQUE 约束唯一标识数据库表中的每条记录。
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
PRIMARY KEY 拥有自动定义的 UNIQUE 约束。
请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

MariaDB [company]> CREATE TABLE test_table2
    -> (
    -> id varchar(20) NOT NULL UNIQUE,
    -> name varchar(255)
    -> );
Query OK, 0 rows affected (0.06 sec)
# 或
MariaDB [company]> CREATE TABLE test_table3
    -> (
    -> id varchar(20) NOT NULL,
    -> name varchar(255),
    -> UNIQUE(id)
    -> );
Query OK, 0 rows affected (0.08 sec)

MariaDB [company]> INSERT INTO test_table2 VALUE('001','Alex');
Query OK, 1 row affected (0.19 sec)

MariaDB [company]> INSERT INTO test_table2 VALUE('001','Bruce');
ERROR 1062 (23000): Duplicate entry '001' for key 'id'

当表已被创建时，如需在 “age” 列创建 UNIQUE 约束，请使用下列 SQL：

MariaDB [company]> CREATE UNIQUE INDEX uni_age ON employee_info(age);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [company]> describe employee_info;
+---------+-----------------------+------+-----+---------+-------+
| Field   | Type                  | Null | Key | Default | Extra |
+---------+-----------------------+------+-----+---------+-------+
| id      | varchar(20)           | NO   | PRI |         |       |
| name    | varchar(255)          | NO   |     | NULL    |       |
| age     | int(11)               | YES  | UNI | NULL    |       |
| salary  | int(11)               | NO   |     | NULL    |       |
| address | varchar(255)          | YES  |     | NULL    |       |
| sex     | enum('male','female') | YES  |     | NULL    |       |
+---------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

撤销 UNIQUE 约束

MariaDB [company]> ALTER TABLE employee_info DROP INDEX uni_age;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [company]> describe employee_info;
+---------+-----------------------+------+-----+---------+-------+
| Field   | Type                  | Null | Key | Default | Extra |
+---------+-----------------------+------+-----+---------+-------+
| id      | varchar(20)           | NO   | PRI |         |       |
| name    | varchar(255)          | NO   |     | NULL    |       |
| age     | int(11)               | YES  |     | NULL    |       |
| salary  | int(11)               | NO   |     | NULL    |       |
| address | varchar(255)          | YES  |     | NULL    |       |
| sex     | enum('male','female') | YES  |     | NULL    |       |
+---------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

多列的 UNIQUE 约束请自行 google。

PRIMARY KEY

PRIMARY KEY 约束唯一标识数据库表中的每条记录。
主键必须包含唯一的值。
主键列不能包含 NULL 值。
每个表都应该有一个主键，并且每个表只能有一个主键。
下面的 SQL 在 “Persons” 表创建时在 “Id_P” 列创建 PRIMARY KEY 约束：

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (Id_P)
)
# 或
CREATE TABLE Persons
(
Id_P int PRIMARY KEY NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
)

多列的 PRIMARY KEY 请自行 google。

FOREIGN KEY

DEFAULT

DEFAULT 约束用于向列中插入默认值。
如果没有规定其他的值，那么会将默认值添加到所有的新记录。

MariaDB [company]> CREATE TABLE test_table4
    -> (
    -> id varchar(20),
    -> name varchar(255),
    -> graduate_age int DEFAULT 22
    -> );
Query OK, 0 rows affected (0.05 sec)

MariaDB [company]> INSERT INTO test_table4 (id,name) VALUE('001','Alex');
Query OK, 1 row affected (0.04 sec)

MariaDB [company]> SELECT * FROM test_table4;
+------+------+--------------+
| id   | name | graduate_age |
+------+------+--------------+
| 001  | Alex |           22 |
+------+------+--------------+
1 row in set (0.00 sec)

增加一个已经存在的表的列 DEFAULT 约束

MariaDB [company]> ALTER TABLE test_table4 ALTER name SET DEFAULT 'Unknown';
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [company]> describe test_table4;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id           | varchar(20)  | YES  |     | NULL    |       |
| name         | varchar(255) | YES  |     | Unknown |       |
| graduate_age | int(11)      | YES  |     | 22      |       |
+--------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

撤销 DEFAULT 约束

MariaDB [company]> ALTER TABLE test_table4 ALTER name DROP DEFAULT;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [company]> describe test_table4;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id           | varchar(20)  | YES  |     | NULL    |       |
| name         | varchar(255) | YES  |     | NULL    |       |
| graduate_age | int(11)      | YES  |     | 22      |       |
+--------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)