On the shoulders of giants.

MySQL 简单使用

操作环境

  • Windows10 64-bit
  • mysql Ver 8.0.18 for Win64 on x86_64 (MySQL Community Server – GPL)

常用命令

数据库操作

登录数据库服务器:

C:\Windows\system32>mysql -uNAME -pPASSWORD

查看数据库服务器中所有的数据库:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| text               |
+--------------------+
5 rows in set (0.02 sec)

使用(要进行操作)某一个数据库:

mysql> use mysql
Database changed

退出数据库服务器:

mysql> exit;
Bye

在数据库服务器中创建数据库:

mysql> create database NAME;
Query OK, 1 row affected (0.07 sec)

在数据库服务器中删除数据库:

mysql> drop database NAME;
Query OK, 1 row affected (0.01 sec)
数据表操作

在数据库中创建数据表:

mysql> create table pet (
    -> name VARCHAR(20),
    -> owner VARCHAR(20),
    -> species VARCHAR(20),
    -> sex CHAR(1),
    -> birth DATE,
    -> death DATE);
Query OK, 0 rows affected (0.07 sec)

在数据库中查看数据表:

mysql> show tables;
+----------------+
| Tables_in_text |
+----------------+
| pet            |
+----------------+
1 row in set (0.00 sec)

查看数据表的结构:

mysql> describe pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

删除数据库中的数据表:

mysql> drop table pet;
Query OK, 0 rows affected (0.03 sec)

查看表中的记录:

mysql> select * from pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Buffy    | Harold | dog     | f    | 1983-03-14 | NULL       |
| Fang     | Benny  | dog     | m    | 1999-08-11 | NULL       |
| Bowser   | Diane  | bird    | f    | 1993-02-04 | 1995-07-29 |
| Whistler | Gwen   | cat     | m    | 1993-02-04 | NULL       |
| Slim     | Gwen   | snake   | NULL | 1993-02-04 | NULL       |
| Claws    | Benny  | hamster | f    | 1993-02-04 | NULL       |
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
+----------+--------+---------+------+------------+------------+

在数据表中添加记录:

mysql> insert into pet
    -> values('Puffball', 'Diane', 'hamster', 'f', '1999-03-30', NULL);
Query OK, 1 row affected (0.01 sec)

在数据表中删除记录:

mysql> delete from pet where name='Slim';
Query OK, 1 row affected (0.01 sec)

在数据表中更新数据:

mysql> update pet set name='Funely' where name='Fang';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
数据类型

数值类型:

类型 大小范围(有符号) 范围(无符号)用途
TINYINT1 字节(-128,127)(0,255)小整数值
SMALLINT2 字节(-32 768,32 767)(0,65 535)大整数值
MEDIUMINT3 字节(-8 388 608,8 388 607)(0,16 777 215)大整数值
INT或INTEGER4 字节(-2 147 483 648,2 147 483 647)(0,4 294 967 295) 大整数值
BIGINT8 字节(-9,223,372,036,854,775,808,9 223 372 036 854 775 807)(0,18 446 744 073 709 551 615)极大整数值
FLOAT4 字节(-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) 单精度浮点数值 
DOUBLE8 字节(-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的值 小数值

时间与日期类型:

类型大小 (字节)范围格式用途
DATE31000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME3‘-838:59:59’/’838:59:59’HH:MM:SS时间值或持续时间
YEAR11901/2155YYYY年份值
DATETIME81000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值
TIMESTAMP41970-01-01 00:00:00/2038
结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07
YYYYMMDD HHMMSS混合日期和时间值,时间戳

字符串类型:

类型大小用途
CHAR0-255字节定长字符串
VARCHAR0-65535字节变长字符串
TINYBLOB0-255字节不超过 255 个字符的二进制字符串
TINYTEXT0-255字节短文本字符串
BLOB0-65 535字节二进制形式的长文本数据
TEXT0-65 535字节长文本数据
MEDIUMBLOB0-16 777 215字节二进制形式的中等长度文本数据
MEDIUMTEXT0-16 777 215字节中等长度文本数据
LONGBLOB0-4 294 967 295字节二进制形式的极大文本数据
LONGTEXT0-4 294 967 295字节极大文本数据

建表约束

主键约束

它能够唯一确定一张表中的一条记录,给某个字段添加约束,使得该字段的值不可重复且不为空。

mysql> create table user(
    -> id INT primary key,
    -> name VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> describe user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into user values(1, 'Lrvin');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user values(1, 'Oerth');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into user values(NULL, 'Oerth');
ERROR 1048 (23000): Column 'id' cannot be null
联合主键

与主键约束类似,当元组中约束字段 \((d1_1,d1_2,\cdots,d1_n)= (d2_1,d2_2,\cdots,d2_n) \) 时,就是重复了,每个元组内的字段值都不能为空。

mysql> create table user(
    -> id INT,
    -> name VARCHAR(20),
    -> password VARCHAR(20),
    -> primary key(id, name)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> describe user;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(20) | NO   | PRI | NULL    |       |
| password | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into user values(1,'Lrvin','123');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user values(1,'Oerth','123');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user values(1,'Lrvin','qwe');
ERROR 1062 (23000): Duplicate entry '1-Lrvin' for key 'PRIMARY'
mysql> insert into user values(NULL,'Lrvin','qwe');
ERROR 1048 (23000): Column 'id' cannot be null
自增约束
mysql> create table user(
    -> id INT primary key auto_increment,
    -> name VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> describe user;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> insert into user (name) value('Lrvin');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user (name) value('Lrvin');
Query OK, 1 row affected (0.01 sec)

mysql> select * from user;
+----+-------+
| id | name  |
+----+-------+
|  1 | Lrvin |
|  2 | Lrvin |
+----+-------+
2 rows in set (0.01 sec)
主键约束的更改
  • 增加:alter table user add primary key(id);
  • 删除:alter table user drop primary key;
  • 修改:alter table user modify id int primary key;
mysql> create table user(
    -> id INT,
    -> name VARCHAR(20),
    -> password VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> describe user;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| password | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table user add primary key(id);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| password | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table user drop primary key;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe user;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   |     | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| password | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table user modify id int primary key;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe user;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| password | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
唯一约束

约束修饰的字段的值不可以重复。

mysql> create table user(
    -> id INT,
    -> name VARCHAR(20),
    -> unique(name)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> describe user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

或者是创建表后添加约束。

mysql> create table user(
    -> id INT,
    -> name VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table user add unique(name);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into user value(1, 'Lrvin');
Query OK, 1 row affected (0.02 sec)

mysql> insert into user value(1, 'Oerth');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user value(2, 'Lrvin');
ERROR 1062 (23000): Duplicate entry 'Lrvin' for key 'name'
mysql> select * from user;
+------+-------+
| id   | name  |
+------+-------+
|    1 | Lrvin |
|    1 | Oerth |
+------+-------+
2 rows in set (0.00 sec)

删除唯一约束:

  • 修改:alter table user modify name VARCHAR(20) unique;
  • 删除:alter table user drop index name;
非空约束

修饰的字段不能为空。

mysql> create table user(
    -> id INT,
    -> name VARCHAR(20) not null
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into user value(1, 'Lvrin');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user value(2, NULL);
ERROR 1048 (23000): Column 'name' cannot be null

mysql> insert into user (name) value('Oerth');
Query OK, 1 row affected (0.01 sec)

mysql> select * from user;
+------+-------+
| id   | name  |
+------+-------+
|    1 | Lvrin |
| NULL | Oerth |
+------+-------+
2 rows in set (0.00 sec)
默认约束

插入字段值时,如果没有传值,那么使用默认值。

mysql> create table user(
    -> id INT,
    -> name VARCHAR(20),
    -> age INT default 10
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | 10      |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into user (id, name) values(1, 'Lrvin');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user value(2, 'Oerth', 18);
Query OK, 1 row affected (0.01 sec)

mysql> select * from user;
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | Lrvin |   10 |
|    2 | Oerth |   18 |
+------+-------+------+
2 rows in set (0.00 sec)
外键约束

两张数据表之间的链接约束。

mysql> create table classes(
    -> id INT primary key,
    -> name VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> desc classes;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> create table students(
    -> id INT primary key,
    -> name VARCHAR(20),
    -> class_id INT,
    -> foreign key(class_id) references classes(id)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> desc students;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| class_id | int(11)     | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into classes value(1, '一班');
Query OK, 1 row affected (0.01 sec)

mysql> insert into classes value(2, '二班');
Query OK, 1 row affected (0.01 sec)

mysql> insert into classes value(3, '三班');
Query OK, 1 row affected (0.00 sec)

mysql> insert into classes value(4, '四班');
Query OK, 1 row affected (0.01 sec)

mysql> select * from classes;
+----+--------+
| id | name   |
+----+--------+
|  1 | 一班   |
|  2 | 二班   |
|  3 | 三班   |
|  4 | 四班   |
+----+--------+
4 rows in set (0.00 sec)

mysql> insert into students value(1001, '张三', 1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into students value(1002, '李四', 3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into students value(1003, '王五', 6);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`text`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))
mysql> select * from students;
+------+--------+----------+
| id   | name   | class_id |
+------+--------+----------+
| 1001 | 张三   |        1 |
| 1002 | 李四   |        3 |
+------+--------+----------+
2 rows in set (0.00 sec)

mysql> delete from classes where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`text`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))

设计范式

第一范式

数据表中的所有字段都是不可分割的原子值。

下面的地址不符合第一范式:

mysql> create table user(
    -> id INT primary key,
    -> name VARCHAR(20),
    -> address VARCHAR(30)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> insert into user value(1, '张三', '中国四川成都武侯大道100号');
Query OK, 1 row affected (0.02 sec)

mysql> insert into user value(2, '李四', '中国四川成都武侯大道200号');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user value(3, '王五', '中国四川成都武侯大道106号');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----+--------+--------------------------------------+
| id | name   | address                              |
+----+--------+--------------------------------------+
|  1 | 张三   | 中国四川成都武侯大道100号            |
|  2 | 李四   | 中国四川成都武侯大道200号            |
|  3 | 王五   | 中国四川成都武侯大道106号            |
+----+--------+--------------------------------------+
3 rows in set (0.00 sec)

下面的则符合第一范式:

mysql> create table user(
    -> id INT primary key,
    -> name VARCHAR(20),
    -> cuntry VARCHAR(30),
    -> privence VARCHAR(30),
    -> city VARCHAR(30),
    -> details VARCHAR(30)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> desc user;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| cuntry   | varchar(30) | YES  |     | NULL    |       |
| privence | varchar(30) | YES  |     | NULL    |       |
| city     | varchar(30) | YES  |     | NULL    |       |
| details  | varchar(30) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

mysql> insert into user value(1, '张三', '中国', '四川', '成都', '武侯大道100号');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user value(2, '李四', '中国', '四川', '成都', '武侯大道200号');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user value(3, '王五', '中国', '四川', '成都', '武侯大道106号');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> select * from user;
+----+--------+--------+----------+--------+--------------------+
| id | name   | cuntry | privence | city   | details            |
+----+--------+--------+----------+--------+--------------------+
|  1 | 张三   | 中国   | 四川     | 成都   | 武侯大道100号      |
|  2 | 李四   | 中国   | 四川     | 成都   | 武侯大道200号      |
|  3 | 王五   | 中国   | 四川     | 成都   | 武侯大道106号      |
+----+--------+--------+----------+--------+--------------------+
3 rows in set (0.00 sec)
第二范式

在满足第一范式的前提下,第二范式要求除主键外的每一列都必须完全依赖于主键。

下面创建的订单表不符合第二范式:

mysql> create table myorder(
    -> product_id INT,
    -> customer_id INT,
    -> product_name VARCHAR(20),
    -> customer_name VARCHAR(20),
    -> primary key(product_id, customer_id)
    -> );
Query OK, 0 rows affected (0.04 sec)

下面的一些表则满足第二范式:

mysql> create table myorder(
    -> order_id INT primary key,
    -> product_id INT,
    -> customer_id INT
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> create table product(
    -> id INT primary key,
    -> name VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> create table customer(
    -> id INT primary key,
    -> name VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.03 sec)
第三范式

必须先满足第二范式,除主键列的其它列之间不能有传递依赖关系。

下面的 customer_phone 则不满足第三范式:

mysql> create table myorder(
    -> order_id INT primary key,
    -> product_id INT,
    -> customer_id INT,
    -> customer_phone VARCHAR(15)
    -> );
Query OK, 0 rows affected (0.02 sec)

下面的一系列创建表则满足第三范式:

mysql> create table myorder(
    -> order_id INT primary key,
    -> product_id INT,
    -> customer_id INT
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> create table customer(
    -> id INT primary key,
    -> name VARCHAR(20),
    -> phone VARCHAR(15)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> create table product(
    -> id INT primary key,
    -> name VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.03 sec)

数据查询

简单查询

查询表中的所有记录:

mysql> select * from student;
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 101 | 张三   | 男   | 1977-09-01 00:00:00 | 95033 |
| 102 | 李四   | 男   | 1987-01-21 00:00:00 | 95041 |
| 103 | 王五   | 男   | 1997-09-14 00:00:00 | 95023 |
| 104 | 赵六   | 男   | 1977-11-11 00:00:00 | 95033 |
| 105 | 李棋   | 女   | 1987-08-21 00:00:00 | 95046 |
| 106 | 倪贵   | 女   | 1997-03-18 00:00:00 | 95053 |
| 107 | 马克   | 男   | 1989-05-25 00:00:00 | 95032 |
| 108 | 一贯   | 男   | 1977-01-08 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+
8 rows in set (0.00 sec)

查询表中的给定字段:

mysql> select sno, sname, ssex from student;
+-----+--------+------+
| sno | sname  | ssex |
+-----+--------+------+
| 101 | 张三   | 男   |
| 102 | 李四   | 男   |
| 103 | 王五   | 男   |
| 104 | 赵六   | 男   |
| 105 | 李棋   | 女   |
| 106 | 倪贵   | 女   |
| 107 | 马克   | 男   |
| 108 | 一贯   | 男   |
+-----+--------+------+
8 rows in set (0.00 sec)

查询不重复的列:

mysql> select depart from teacher;
+-----------------+
| depart          |
+-----------------+
| 计算机系        |
| 计算机系        |
| 电子工程系      |
| 电子工程系      |
+-----------------+
4 rows in set (0.00 sec)

mysql> select distinct depart from teacher;
+-----------------+
| depart          |
+-----------------+
| 计算机系        |
| 电子工程系      |
+-----------------+
2 rows in set (0.00 sec)

查询区间的所有记录:

mysql> select * from score where degree between 60 and 80;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 105 | 3-105 |     78 |
| 105 | 3-245 |     75 |
+-----+-------+--------+
2 rows in set (0.01 sec)

mysql> select * from score where 60 < degree and degree < 80;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 105 | 3-105 |     78 |
| 105 | 3-245 |     75 |
+-----+-------+--------+
2 rows in set (0.00 sec)

查询或关系的记录:

mysql> select * from score where degree in (78, 81, 84);
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 6-166 |     81 |
| 105 | 3-105 |     78 |
+-----+-------+--------+
2 rows in set (0.01 sec)
mysql> select * from student where class='95033' or ssex='女';
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 101 | 张三   | 男   | 1977-09-01 00:00:00 | 95033 |
| 104 | 赵六   | 男   | 1977-11-11 00:00:00 | 95033 |
| 105 | 李棋   | 女   | 1987-08-21 00:00:00 | 95046 |
| 106 | 倪贵   | 女   | 1997-03-18 00:00:00 | 95053 |
+-----+--------+------+---------------------+-------+
4 rows in set (0.00 sec)

升序、降序、组合序查询所有记录:

mysql> select * from student order by class desc;
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 106 | 倪贵   | 女   | 1997-03-18 00:00:00 | 95053 |
| 105 | 李棋   | 女   | 1987-08-21 00:00:00 | 95046 |
| 102 | 李四   | 男   | 1987-01-21 00:00:00 | 95041 |
| 101 | 张三   | 男   | 1977-09-01 00:00:00 | 95033 |
| 104 | 赵六   | 男   | 1977-11-11 00:00:00 | 95033 |
| 107 | 马克   | 男   | 1989-05-25 00:00:00 | 95032 |
| 108 | 一贯   | 男   | 1977-01-08 00:00:00 | 95031 |
| 103 | 王五   | 男   | 1997-09-14 00:00:00 | 95023 |
+-----+--------+------+---------------------+-------+
8 rows in set (0.00 sec)

mysql> select * from student order by class;
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 103 | 王五   | 男   | 1997-09-14 00:00:00 | 95023 |
| 108 | 一贯   | 男   | 1977-01-08 00:00:00 | 95031 |
| 107 | 马克   | 男   | 1989-05-25 00:00:00 | 95032 |
| 101 | 张三   | 男   | 1977-09-01 00:00:00 | 95033 |
| 104 | 赵六   | 男   | 1977-11-11 00:00:00 | 95033 |
| 102 | 李四   | 男   | 1987-01-21 00:00:00 | 95041 |
| 105 | 李棋   | 女   | 1987-08-21 00:00:00 | 95046 |
| 106 | 倪贵   | 女   | 1997-03-18 00:00:00 | 95053 |
+-----+--------+------+---------------------+-------+
8 rows in set (0.00 sec)

mysql> select * from score order by cno asc, degree desc;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 105 | 3-105 |     78 |
| 103 | 3-105 |     43 |
| 103 | 3-245 |     86 |
| 105 | 3-245 |     75 |
| 103 | 6-166 |     81 |
| 105 | 6-166 |     49 |
+-----+-------+--------+
6 rows in set (0.00 sec)

统计要求记录个数:

mysql>  select count(*) from student where class='95033';
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

查询最值的记录:

mysql> select * from score where degree=(select min(degree) from score);
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     43 |
+-----+-------+--------+
1 row in set (0.00 sec)

mysql> select * from score where degree=(select max(degree) from score);
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-245 |     86 |
+-----+-------+--------+
1 row in set (0.00 sec)

mysql> select * from score order by degree desc limit 0, 1;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-245 |     86 |
+-----+-------+--------+
1 row in set (0.00 sec)

mysql> select * from score order by degree limit 0, 1;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     43 |
+-----+-------+--------+
1 row in set (0.00 sec)

计算平均值(单一、分组):

mysql> select avg(degree) from score where cno='3-105';
+-------------+
| avg(degree) |
+-------------+
|     60.5000 |
+-------------+
1 row in set (0.00 sec)

mysql> select cno, avg(degree) from score group by cno;
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     60.5000 |
| 3-245 |     80.5000 |
| 6-166 |     65.0000 |
+-------+-------------+
3 rows in set (0.01 sec)

多表查询:

mysql> select * from student;
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 101 | 张三   | 男   | 1977-09-01 00:00:00 | 95033 |
| 102 | 李四   | 男   | 1987-01-21 00:00:00 | 95041 |
| 103 | 王五   | 男   | 1997-09-14 00:00:00 | 95023 |
| 104 | 赵六   | 男   | 1977-11-11 00:00:00 | 95033 |
| 105 | 李棋   | 女   | 1987-08-21 00:00:00 | 95046 |
| 106 | 倪贵   | 女   | 1997-03-18 00:00:00 | 95053 |
| 107 | 马克   | 男   | 1989-05-25 00:00:00 | 95032 |
| 108 | 一贯   | 男   | 1977-01-08 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+
8 rows in set (0.00 sec)

mysql> select * from score;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     43 |
| 103 | 3-245 |     86 |
| 103 | 6-166 |     81 |
| 105 | 3-105 |     78 |
| 105 | 3-245 |     75 |
| 105 | 6-166 |     49 |
+-----+-------+--------+
6 rows in set (0.00 sec)

mysql> select sname, cno, degree from student, score where student.sno=score.sno;
+--------+-------+--------+
| sname  | cno   | degree |
+--------+-------+--------+
| 王五   | 3-105 |     43 |
| 王五   | 3-245 |     86 |
| 王五   | 6-166 |     81 |
| 李棋   | 3-105 |     78 |
| 李棋   | 3-245 |     75 |
| 李棋   | 6-166 |     49 |
+--------+-------+--------+
6 rows in set (0.00 sec)

mysql> select sname, cname, degree from student, course, score
    -> where student.sno=score.sno and course.cno=score.cno;
+--------+-----------------+--------+
| sname  | cname           | degree |
+--------+-----------------+--------+
| 王五   | 计算机导论      |     43 |
| 王五   | 操作系统        |     86 |
| 王五   | 数字电路        |     81 |
| 李棋   | 计算机导论      |     78 |
| 李棋   | 操作系统        |     75 |
| 李棋   | 数字电路        |     49 |
+--------+-----------------+--------+
6 rows in set (0.00 sec)

查询某一年中的记录:

mysql> select year(sbirthday) from student where sno in (108, 103);
+-----------------+
| year(sbirthday) |
+-----------------+
|            1997 |
|            1977 |
+-----------------+
2 rows in set (0.00 sec)

查询和某同学同一年出生的记录:

mysql> select * from student
    -> where year(sbirthday) in(select year(sbirthday) from student where sno in(108, 103));
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 103 | 王五   | 男   | 1997-09-14 00:00:00 | 95023 |
| 106 | 倪贵   | 女   | 1997-03-18 00:00:00 | 95053 |
| 101 | 张三   | 男   | 1977-09-01 00:00:00 | 95033 |
| 104 | 赵六   | 男   | 1977-11-11 00:00:00 | 95033 |
| 108 | 一贯   | 男   | 1977-01-08 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+
5 rows in set (0.01 sec)

多层嵌套查询(查询某个老师任课的学生成绩):

mysql> select * from teacher where tname = '张久';
+-----+--------+------+---------------------+--------+-----------------+
| tno | tname  | tsex | tbirthday           | prof   | depart          |
+-----+--------+------+---------------------+--------+-----------------+
| 856 | 张久   | 男   | 1969-03-12 00:00:00 | 讲师   | 电子工程系      |
+-----+--------+------+---------------------+--------+-----------------+
1 row in set (0.01 sec)

mysql> select * from course
    -> where tno = (select tno from teacher where tname = '张久');
+-------+--------------+-----+
| cno   | cname        | tno |
+-------+--------------+-----+
| 6-166 | 数字电路     | 856 |
+-------+--------------+-----+
1 row in set (0.01 sec)

mysql> select * from score
    -> where cno = (select cno from course
    -> where tno = (select tno from teacher
    -> where tname = '张久'));
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 6-166 |     81 |
| 105 | 6-166 |     49 |
+-----+-------+--------+
2 rows in set (0.00 sec)
链接查询
内连接

两张表中无外键关系下,可以通过有关系的字段相关查询。

mysql> desc person;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| cardID | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> desc card;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> select * from person inner join card on person.cardID = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardID | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农商卡    |
+------+--------+--------+------+-----------+
2 rows in set (0.00 sec)
外连接

左连接:

把左边表中的记录全部打印,如果右边表中有左边表的信息,那么将在右边显示出来。

mysql> select * from person left join card on person.cardID = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardID | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农商卡    |
|    3 | 王五   |      6 | NULL | NULL      |
+------+--------+--------+------+-----------+
3 rows in set (0.00 sec)

右连接:

把右边表中的记录全部打印,如果左边表中有右边表的信息,那么将在左边显示出来。

mysql> select * from person right join card on person.cardID = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardID | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农商卡    |
| NULL | NULL   |   NULL |    2 | 建行卡    |
| NULL | NULL   |   NULL |    4 | 工商卡    |
| NULL | NULL   |   NULL |    5 | 邮政卡    |
+------+--------+--------+------+-----------+
5 rows in set (0.00 sec)

完全外连接:

mysql> select * from person left join card on person.cardID = card.id
    -> union
    -> select * from person right join card on person.cardID = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardID | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农商卡    |
|    3 | 王五   |      6 | NULL | NULL      |
| NULL | NULL   |   NULL |    2 | 建行卡    |
| NULL | NULL   |   NULL |    4 | 工商卡    |
| NULL | NULL   |   NULL |    5 | 邮政卡    |
+------+--------+--------+------+-----------+
6 rows in set (0.00 sec)

事务

默认 autocommit = 1 ,没有开启事务,不具有回滚效果。

mysql> select @@autocommit;
+--------------+
| @@c |
+--------------+
|            1 |
+--------------+
1 row in set (0.01 sec)

mysql> insert into user values(1, 'a', 1000);
Query OK, 1 row affected (0.01 sec)

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+
1 row in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+
1 row in set (0.00 sec)

autocommit = 0 时,则具有回滚效果。

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

mysql> insert into user values(2, 'b', 1000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+
2 rows in set (0.00 sec)

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

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+
1 row in set (0.00 sec)

autocommit = 0 时,通过 commit 语句可以提交数据,使其不具有回滚效果。

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+
1 row in set (0.00 sec)

mysql> insert into user values(2, 'b', 1000);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

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

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+
2 rows in set (0.00 sec)
手动开启事务

在执行命令前,使用 begin 或者 start transaction 同样的,使用 commit 后则不能回滚。

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update user set money = money - 100 where name = 'a';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update user set money = money + 100 where name = 'b';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+
2 rows in set (0.00 sec)

事务的四大特征
  • A:原子性,事务时最小的单位,不可以在分割。
  • C:一致性,同一事务中的 sql 语句,必须保证同时成功或者同时失败。
  • I:隔离性,两事务之间时具有隔离性的。
  • D:持久性,事务一旦结束,就不可以返回。

Share

You may also like...

发表评论

电子邮件地址不会被公开。 必填项已用*标注