MySQL DAY2 基础教程

来源：互联网发布：切尔西靴知乎编辑：程序博客网时间：2024/06/05 08:51

表内容

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: 0MariaDB [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: 0MariaDB [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: 0MariaDB [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: 0MariaDB [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)

0 0