Mysql平台下的SQL交互操作(1)

最新推荐文章于 2022-05-06 23:25:37 发布

恰恰·

最新推荐文章于 2022-05-06 23:25:37 发布

阅读量653

点赞数 1

分类专栏： Mysql 文章标签： MySQL SQL语句

本文链接：https://blog.csdn.net/qq_43659281/article/details/88844812

版权

Mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

一、数据库
1.创建数据库：
基本语法格式：
CREATE DATABASE (IF NOT EXISTS) db_name #创建数据库
(DEFAULT) CHARACTER SET (=) charset_name #指定数据库字符集
(DEFAULT) COLLATE (=) collation_name; #校对规则
例：创建一个名为db_student的数据库：

mysql> CREATE DATABASE db_student
    -> DEFAULT CHARACTER SET utf8
    -> DEFAULT COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

这样就创建了一个名为db_student的数据库
2.选择数据库：
语法格式：
USE db_name;
例：选择刚才创建的数据库：

mysql> USE db_student;
Database changed

3.查看数据库：
语法格式：
SHOW DATABASES;
例：查看当前用户(root)可查看的数据库列表：

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db_school          |
| db_sp              |
| db_student         |
| moviedata          |
| mysql              |
| performance_schema |
| runoob             |
| test               |
+--------------------+
9 rows in set (0.00 sec)

可以看到刚刚创建的db_student数据库在列表中。
4.修改数据库：
语法格式：
ALTER DATABASE (db_name)
(DEFAULT) CHARACTER SET (=) charset_name
(DEFAULT) COLLATE (=) collation_name;
除CREATE变为ALTER关键字外，其他与创建数据库语法类似，同时db_name也可省略，表示修改的是当前数据库。
5.删除数据库：
语法格式：
DROP DATABASE (IF EXISTS) db_name;
可选项IF EXISTS子句可以避免删除不存在的数据库时出现Mysql错误信息。

二、数据表
1.创建表：
语法格式：
CREATE TABLE tbl_name
( #这个括号是必须加的
字段名1 数据类型 (列级完整性约束条件) (默认值),
字段名2 数据类型 (列级完整性约束条件) (默认值),
… … ,
(表级完整性约束条件)
）(ENGINE=引擎类型);
例：在已有数据库db_student中定义学生表tb_student，其结构如下所示，并用InnoDB引擎存储表数据。

mysql> USE db_student;
Database changed
mysql> CREATE TABLE tb_student
    -> (
    -> id INT(10) NOT NULL UNIQUE AUTO_INCREMENT,
    -> name VARCHAR(15) NOT NULL,
    -> sex VARCHAR(5),
    -> birthday DATE,
    -> nation VARCHAR(15)
    -> )ENGINE=InnoDB;
Query OK, 0 rows affected (0.38 sec)

上述语句执行成功后会创建一个名为tb_student的数据表。其中，NOT NULL表示不接受该列没有值的记录，即在插入或更新数据时，该列必须有值，而系统默认设置为NULL；UNIQUE表示候选键约束，也可用PRIMARY KEY表示主键约束，这是为了给学号添加自增属性AUTO_INCREMENT,它的默认初始值为1，当往该列中插入NULL或0时，该列的值会被设置为value+1，value表示当前表中该列的最大值；ENGINE=InnoDB语句为该表指定了一个类型为InnoDB的存储引擎，可以使用SHOW ENGINES语句查看系统所支持的引擎类型和默认引擎。

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.01 sec)

2.查看表：
1).查看表的名称：
语法格式：
SHOW TABLES (FROM/IN db_name);
若查看当前数据库中表的名称可以省略括号中的内容。

mysql> SHOW TABLES FROM db_student;
+----------------------+
| Tables_in_db_student |
+----------------------+
| tb_student           |
+----------------------+
1 row in set (0.00 sec)

2).查看表的基本结构：
语法格式：
SHOW COLUMNS FROM/IN tb_name (FROM/IN db_name);
或
DESCRIBE/DESC tb_name;
例：

mysql> DESC tb_student;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(10)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(15) | NO   |     | NULL    |                |
| sex      | varchar(5)  | YES  |     | NULL    |                |
| birthday | date        | YES  |     | NULL    |                |
| nation   | varchar(15) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

3).查看数据表的详细结构：
语法格式：
SHOW CREATE TABLE tb_name;
例：

mysql> SHOW CREATE TABLE tb_student;
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                           |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_student | CREATE TABLE `tb_student` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(15) NOT NULL,
  `sex` varchar(5) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `nation` varchar(15) DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

或：

mysql> SHOW CREATE TABLE tb_student\G;
*************************** 1. row ***************************
       Table: tb_student
Create Table: CREATE TABLE `tb_student` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(15) NOT NULL,
  `sex` varchar(5) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `nation` varchar(15) DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.36 sec)

ERROR:
No query specified

末尾加\G更直观。
3.修改表
1).添加字段：
语法格式：
ALTER TABLE tb_name ADD (COLUMN) 新字段名数据类型 (约束条件) （FIRST/AFTER 已有字段名）
例：添加一个CHAR型字段class，要求其不能为空值，并将该字段添加到表的第一个字段，然后查看结果。

mysql> ALTER TABLE tb_student ADD COLUMN class CHAR(10) NOT NULL FIRST;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC tb_student;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| class    | char(10)    | NO   |     | NULL    |                |
| id       | int(10)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(15) | NO   |     | NULL    |                |
| sex      | varchar(5)  | YES  |     | NULL    |                |
| birthday | date        | YES  |     | NULL    |                |
| nation   | varchar(15) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

2).修改字段：
语法格式：
ALTER TABLE tb_name CHANGE (COLUMN) 原字段名新字段名数据类型 (约束条件);
或：
ALTER TABLE tb_name ALTER (COLUMN) 字段名 SET/DROP DEFAULT;
或：
ALTER TABLE tb_name MODIFY (COLUMN) 字段名数据类型 (约束条件) (FIRST/AFTER 已有字段名)；
例：将字段class重命名为job，数据类型更改为VARCHAR,允许其为NULL，默认值为(student).

mysql> ALTER TABLE db_student.tb_student
    -> CHANGE COLUMN class job VARCHAR(10) NULL DEFAULT 'student';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC tb_student;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| job      | varchar(10) | YES  |     | student |                |
| id       | int(10)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(15) | NO   |     | NULL    |                |
| sex      | varchar(5)  | YES  |     | NULL    |                |
| birthday | date        | YES  |     | NULL    |                |
| nation   | varchar(15) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

3).删除字段：
语法格式：
ALTER TABLE tb_name DROP COLUMN 字段名;
例：删除字段job。

mysql> ALTER TABLE tb_student DROP COLUMN job;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC tb_student;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(10)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(15) | NO   |     | NULL    |                |
| sex      | varchar(5)  | YES  |     | NULL    |                |
| birthday | date        | YES  |     | NULL    |                |
| nation   | varchar(15) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

该字段已经被删除了。
4.重命名表：
语法格式：
ALTER TABLE 原表名 RENAME (TO) 新表名;
或：
RENAME TABLE 原表名1 TO 新表名1(,原表名2 TO 新表名2… …);
5.删除表：
语法格式：
DROP TABLE (IF EXISTS) 表1(,表2… …);

三、数据更新
1.插入数据:
假设往之前创建的tb_student表中插入如下类型数据：

1).插入完整的数据记录：
语法格式：
INSERT INTO tb_name(column_list) VALUES(value_list);
其中，column_list指定要插入数据的字段，value_list指定每个字段对应插入的数据。
例：向表中插入第一行记录。

mysql> USE db_student;
Database changed
mysql> INSERT INTO tb_student
    -> VALUES('100001','Bob','boy','1980-03-15','US');
Query OK, 1 row affected (0.35 sec)

mysql> SELECT * FROM tb_student;#查询语句
+--------+------+------+------------+--------+
| id     | name | sex  | birthday   | nation |
+--------+------+------+------------+--------+
| 100001 | Bob  | boy  | 1980-03-15 | US     |
+--------+------+------+------------+--------+
1 row in set (0.00 sec)

利用查询语句可以看到数据被插入表中。
2).同时插入多条数据记录：
语法格式：
INSERT INTO tb_name (column_list) VALUES(value_list1),(value_list2),…,(value_listn);
例：插入其余记录：

mysql> INSERT INTO tb_student
    -> VALUES('100002','LiMing','boy','1982-11-16','China'),
    -> ('100003','Jack','boy','1981-06-24','US'),
    -> ('100004','Rose','girl','1982-09-07','UK'),
    -> ('100005','Tony','boy','1983-04-17','UK'),
    -> ('100006','Obama','boy','1982-02-03','US');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tb_student;
+--------+--------+------+------------+--------+
| id     | name   | sex  | birthday   | nation |
+--------+--------+------+------------+--------+
| 100001 | Bob    | boy  | 1980-03-15 | US     |
| 100002 | LiMing | boy  | 1982-11-16 | China  |
| 100003 | Jack   | boy  | 1981-06-24 | US     |
| 100004 | Rose   | girl | 1982-09-07 | UK     |
| 100005 | Tony   | boy  | 1983-04-17 | UK     |
| 100006 | Obama  | boy  | 1982-02-03 | US     |
+--------+--------+------+------------+--------+
6 rows in set (0.00 sec)

数据均被导入。
3).为表的指定字段插入数据：
例：插入新记录，学号为100007，姓名为Peiqi，性别为女，国籍为US。

mysql> INSERT INTO tb_student(id,name,sex,nation)
    -> VALUES(NULL,'Peiqi','girl','US');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM tb_student;
+--------+--------+------+------------+--------+
| id     | name   | sex  | birthday   | nation |
+--------+--------+------+------------+--------+
| 100001 | Bob    | boy  | 1980-03-15 | US     |
| 100002 | LiMing | boy  | 1982-11-16 | China  |
| 100003 | Jack   | boy  | 1981-06-24 | US     |
| 100004 | Rose   | girl | 1982-09-07 | UK     |
| 100005 | Tony   | boy  | 1983-04-17 | UK     |
| 100006 | Obama  | boy  | 1982-02-03 | US     |
| 100007 | Peiqi  | girl | NULL       | US     |
+--------+--------+------+------------+--------+
7 rows in set (0.00 sec)

mysql> INSERT INTO tb_student
    -> VALUES(100007,'Geoge','boy','2011-06-30','US');
ERROR 1062 (23000): Duplicate entry '100007' for key 'id'

可以看到该语句不能成功执行，这是由于新记录的主键与原有记录的主键重复了。
然后使用REPLACE语句再次插入该记录：

mysql> REPLACE INTO tb_student
    -> VALUES(100007,'Geoge','boy','2011-06-30','US');
Query OK, 2 rows affected (0.01 sec)

mysql> SELECT * FROM tb_student;
+--------+--------+------+------------+--------+
| id     | name   | sex  | birthday   | nation |
+--------+--------+------+------------+--------+
| 100001 | Bob    | boy  | 1980-03-15 | US     |
| 100002 | LiMing | boy  | 1982-11-16 | China  |
| 100003 | Jack   | boy  | 1981-06-24 | US     |
| 100004 | Rose   | girl | 1982-09-07 | UK     |
| 100005 | Tony   | boy  | 1983-04-17 | UK     |
| 100006 | Obama  | boy  | 1982-02-03 | US     |
| 100007 | Geoge  | boy  | 2011-06-30 | US     |
+--------+--------+------+------------+--------+
7 rows in set (0.00 sec)

可以看到该语句被成功执行且原记录被覆盖掉。
需要注意的是：如果数据表的某个字段上定义了外码，使用REPLACE INTO 插入数据时仍然会出错。
2.修改数据记录：
语法格式：
UPDATE tb_name
SET column1=value1,column2=value2,…,columnn=valuen
(WHERE condiions);
其中，SET子句用于修改的字段名及其值，WHERE子句为可选项，用于限定表中要修改的行，conditons指定修改记录所要满足的条件。若不指定WHERE子句，UPDATE语句会修改表中所有的数据行。
1).修改特定数据记录：
例：将表中学号为100007的学生姓名改为Peiqi，性别改为girl。

mysql> UPDATE tb_student
    -> SET name='Peiqi',sex='girl'
    -> WHERE id='100007';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM tb_student WHERE id='100007';#带有限制条件的查询语句
+--------+-------+------+------------+--------+
| id     | name  | sex  | birthday   | nation |
+--------+-------+------+------------+--------+
| 100007 | Peiqi | girl | 2011-06-30 | US     |
+--------+-------+------+------------+--------+
1 row in set (0.00 sec)

2).修改所有数据记录：
例：将表中所有学生的学号加10。

mysql> UPDATE tb_student
    -> SET id=id+10;
Query OK, 7 rows affected (0.01 sec)
Rows matched: 7  Changed: 7  Warnings: 0

mysql> SELECT * FROM tb_student;
+--------+--------+------+------------+--------+
| id     | name   | sex  | birthday   | nation |
+--------+--------+------+------------+--------+
| 100011 | Bob    | boy  | 1980-03-15 | US     |
| 100012 | LiMing | boy  | 1982-11-16 | China  |
| 100013 | Jack   | boy  | 1981-06-24 | US     |
| 100014 | Rose   | girl | 1982-09-07 | UK     |
| 100015 | Tony   | boy  | 1983-04-17 | UK     |
| 100016 | Obama  | boy  | 1982-02-03 | US     |
| 100017 | Peiqi  | girl | 2011-06-30 | US     |
+--------+--------+------+------------+--------+
7 rows in set (0.00 sec)

语句执行成功，所有学生的学号都在原来基础上加10。
3).带子查询的修改：
在这之前先创建另外两张表
一个是tb_course表

+----------+------------+
| courseid | coursename |
+----------+------------+
|     2001 | PE         |
|     2002 | Math       |
|     2003 | English    |
|     2004 | Science    |

一个是tb_score表

+--------+----------+-------+
| id     | courseid | score |
+--------+----------+-------+
| 100001 |     2001 |    89 |
| 100001 |     2002 |    80 |
| 100002 |     2001 |    95 |
| 100002 |     2002 |    90 |
| 100003 |     2001 |    84 |
| 100003 |     2002 |    78 |
| 100004 |     2003 |    65 |
| 100004 |     2004 |    89 |
| 100005 |     2003 |    87 |
| 100006 |     2004 |    85 |
+--------+----------+-------+

且tb_score表的前两个属性依照前两张表

+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| id       | int(10) | YES  | MUL | NULL    |       |
| courseid | int(10) | YES  | MUL | NULL    |       |
| score    | float   | NO   |     | 0       |
mysql> SHOW CREATE TABLE tb_score\G;
*************************** 1. row ***************************
       Table: tb_score
Create Table: CREATE TABLE `tb_score` (
  `id` int(10) DEFAULT NULL,
  `courseid` int(10) DEFAULT NULL,
  `score` float NOT NULL DEFAULT '0',
  KEY `id` (`id`),
  KEY `courseid` (`courseid`),
  CONSTRAINT `tb_score_ibfk_1` FOREIGN KEY (`id`) REFERENCES `tb_student` (`id`),
  CONSTRAINT `tb_score_ibfk_2` FOREIGN KEY (`courseid`) REFERENCES `tb_course` (`courseid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

例：将Math课程的学生成绩清零

mysql> UPDATE tb_score
    -> SET score=0
    -> WHERE courseid=(SELECT courseid FROM tb_course WHERE coursename='Math');
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT id,tb_score.courseid,coursename,score
    -> FROM tb_course,tb_score
    -> WHERE tb_course.courseid=tb_score.courseid AND coursename='Math';
+--------+----------+------------+-------+
| id     | courseid | coursename | score |
+--------+----------+------------+-------+
| 100001 |     2002 | Math       |     0 |
| 100002 |     2002 | Math       |     0 |
| 100003 |     2002 | Math       |     0 |
+--------+----------+------------+-------+
3 rows in set (0.00 sec)

这里仍然使用了SELECT查询语句，以后会详细介绍。
3.删除数据记录
语法格式：
DELETE FROM tb_name (WHERE conditions);
1).删除特定数据记录
例：删除姓名为Peiqi的数据记录

mysql> SELECT * FROM tb_student;#先查询原数据
+--------+--------+------+------------+--------+
| id     | name   | sex  | birthday   | nation |
+--------+--------+------+------------+--------+
| 100001 | Bob    | boy  | 1980-03-15 | US     |
| 100002 | LiMing | boy  | 1982-11-16 | China  |
| 100003 | Jack   | boy  | 1981-06-24 | US     |
| 100004 | Rose   | girl | 1982-09-07 | UK     |
| 100005 | Tony   | boy  | 1983-04-17 | UK     |
| 100006 | Obama  | boy  | 1982-02-03 | US     |
| 100007 | Peiqi  | girl | 2011-06-30 | US     |
+--------+--------+------+------------+--------+
7 rows in set (0.00 sec)

mysql> DELETE FROM tb_student
    -> WHERE name='Peiqi';
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM tb_student;#再次查询
+--------+--------+------+------------+--------+
| id     | name   | sex  | birthday   | nation |
+--------+--------+------+------------+--------+
| 100001 | Bob    | boy  | 1980-03-15 | US     |
| 100002 | LiMing | boy  | 1982-11-16 | China  |
| 100003 | Jack   | boy  | 1981-06-24 | US     |
| 100004 | Rose   | girl | 1982-09-07 | UK     |
| 100005 | Tony   | boy  | 1983-04-17 | UK     |
| 100006 | Obama  | boy  | 1982-02-03 | US     |
+--------+--------+------+------------+--------+
6 rows in set (0.00 sec)

可见数据已被删除。
2).带子查询的删除
例：删除’Math‘课程的所有选课记录

mysql> DELETE FROM tb_score
    -> WHERE courseid=(SELECT courseid FROM tb_course WHERE coursename='Math');
Query OK, 3 rows affected (0.01 sec)

mysql> SELECT id,tb_score.courseid,coursename,score
    -> FROM tb_course,tb_score
    -> WHERE tb_course.courseid=tb_score.courseid AND coursename='Math';
Empty set (0.00 sec)

可见，所有’Math‘课程记录已被删除。
3).删除所有记录
例：删除tb_score表中所有记录

mysql> DELETE FROM tb_score;

4).TRUNCATE语句：
语法格式：
TRUNCATE (TABLE) tb_name;
TRUNCATE语句也可以清除表中所有记录，但与DELETE语句逐行删除不同的是，该语句直接删除整个表然后新建一个空表，因此执行速度稍快一些。

四、数据查询
1.SELECT语句
语法格式：
SELECT (ALL/DISTINCT/DISTINCTROW) 目标表达式1(,目标表达式2…)
FROM 表名1或视图名1(,表名2或视图名2)
(WHERE 条件表达式)
(GROUP BY 列名1 (HAVING 条件表达式))
(ORDER BY 列名2 (ASC/DESC))
(LIMIT (m,)n);
说明：
ALL/DISTINCT/DISTINCTROW为可选项，用于指定是否返回结果集中的重复行，默认为ALL包括重复行。
SELECT：指定显示的字段或表达式
其他选项后续介绍。
2.单表查询
1).选择字段：
例1：查询tb_student表中所有的学号、姓名、国籍。

mysql> SELECT id,name,nation FROM tb_student;
+--------+--------+--------+
| id     | name   | nation |
+--------+--------+--------+
| 100001 | Bob    | US     |
| 100002 | LiMing | China  |
| 100003 | Jack   | US     |
| 100004 | Rose   | UK     |
| 100005 | Tony   | UK     |
| 100006 | Obama  | US     |
+--------+--------+--------+
6 rows in set (0.00 sec)

例2：查询所有人的姓名、性别、年龄

mysql> SELECT name,sex,'age:',YEAR(NOW())-YEAR(birthday) FROM tb_student;
+--------+------+------+----------------------------+
| name   | sex  | age: | YEAR(NOW())-YEAR(birthday) |
+--------+------+------+----------------------------+
| Bob    | boy  | age: |                         39 |
| LiMing | boy  | age: |                         37 |
| Jack   | boy  | age: |                         38 |
| Rose   | girl | age: |                         37 |
| Tony   | boy  | age: |                         36 |
| Obama  | boy  | age: |                         37 |
+--------+------+------+----------------------------+
6 rows in set (0.00 sec)

这里使用了YEAR函数求得具体年龄，同时返回经过计算后的值。
2).利用WHERE语句选择指定记录
例1：查询tb_student表中所有性别为boy的人的学号、姓名及生日

mysql> SELECT id,name,birthday
    -> FROM tb_student
    -> WHERE sex='boy';
+--------+--------+------------+
| id     | name   | birthday   |
+--------+--------+------------+
| 100001 | Bob    | 1980-03-15 |
| 100002 | LiMing | 1982-11-16 |
| 100003 | Jack   | 1981-06-24 |
| 100005 | Tony   | 1983-04-17 |
| 100006 | Obama  | 1982-02-03 |
+--------+--------+------------+
5 rows in set (0.00 sec)

例2：查询生日在1981年和1982年之间的姓名、性别及生日

mysql> SELECT name,sex,birthday
    -> FROM tb_student
    -> WHERE birthday BETWEEN '1981-01-01' AND '1982-12-31';
+--------+------+------------+
| name   | sex  | birthday   |
+--------+------+------------+
| LiMing | boy  | 1982-11-16 |
| Jack   | boy  | 1981-06-24 |
| Rose   | girl | 1982-09-07 |
| Obama  | boy  | 1982-02-03 |
+--------+------+------------+
4 rows in set (0.00 sec)

除此之外还有许多常用的查询条件，这里不一一概述了。
3).利用ORDER BY子句对查询结果进行排序
例：查询tb_score表中成绩大于等于85的详细信息并将结果按照成绩降序排列

mysql> SELECT * FROM tb_score
    -> WHERE score>=85
    -> ORDER BY score DESC;
+--------+----------+-------+
| id     | courseid | score |
+--------+----------+-------+
| 100002 |     2001 |    95 |
| 100001 |     2001 |    89 |
| 100004 |     2004 |    89 |
| 100005 |     2003 |    87 |
| 100006 |     2004 |    85 |
+--------+----------+-------+
5 rows in set (0.00 sec)

其中DESC关键字表示降序排列，系统默认为ASC升序排列。
4).利用LIMIT语句限制查询结果的数量
语法格式：
LIMIT (位置偏移量m,)行数n
例：查询成绩排名2到4位的具体信息

mysql> SELECT * FROM tb_score
    -> ORDER BY score DESC
    -> LIMIT 1,3;
+--------+----------+-------+
| id     | courseid | score |
+--------+----------+-------+
| 100001 |     2001 |    89 |
| 100004 |     2004 |    89 |
| 100005 |     2003 |    87 |
+--------+----------+-------+
3 rows in set (0.00 sec)

3.分组聚合查询
1).使用聚合函数查询：
常用聚合函数如下(盗的图)：
在这里插入图片描述
例：查询tb_score表中学号为100004的学生的平均成绩

mysql> SELECT AVG(score) FROM tb_score
    -> WHERE id='100004';
+------------+
| AVG(score) |
+------------+
|         77 |
+------------+
1 row in set (0.10 sec)

2).分组聚合查询
语法格式：
GROUP BY 字段列表 (HAVING 条件表达式)
例：查询平均分在80以上的学生的学号、平均分

mysql> SELECT id,AVG(score)
    -> FROM tb_score
    -> GROUP BY id
    -> HAVING AVG(score)>80;
+--------+------------+
| id     | AVG(score) |
+--------+------------+
| 100001 |         89 |
| 100002 |         95 |
| 100003 |         84 |
| 100005 |         87 |
| 100006 |         85 |
+--------+------------+
5 rows in set (0.00 sec)

3).连接查询
交叉连接(笛卡尔积)：返回两张表每一行连接后的所有可能结果
SELECT * FROM 表1 CROSS JOIN 表2;
或：
SELECT * FROM 表1,表2;
例如，表1中有10条记录，表2中有15条记录，则它们的笛卡尔积就有10×15=150条记录。
内连接：通过在查询中设置条件从而移除交叉连接结果集中的某些数据行。
SELECT 表达式1,表达式2,…,表达式n
FROM table1 (INNER) JOIN table2
ON 连接条件
WHERE 过滤条件;
例：查询学习了PE课程的学生学号、姓名及该课的成绩。

mysql> SELECT a.id,name,score
    -> FROM tb_student AS a,tb_course b,tb_score c
    -> WHERE a.id=c.id AND b.courseid=c.courseid AND coursename='PE';
+--------+--------+-------+
| id     | name   | score |
+--------+--------+-------+
| 100001 | Bob    |    89 |
| 100002 | LiMing |    95 |
| 100003 | Jack   |    84 |
+--------+--------+-------+
3 rows in set (0.00 sec)

或：

mysql> SELECT a.id,name,score
    -> FROM tb_student AS a JOIN tb_course b JOIN tb_score c
    -> ON a.id=c.id AND b.courseid=c.courseid
    -> WHERE coursename='PE';
+--------+--------+-------+
| id     | name   | score |
+--------+--------+-------+
| 100001 | Bob    |    89 |
| 100002 | LiMing |    95 |
| 100003 | Jack   |    84 |
+--------+--------+-------+
3 rows in set (0.00 sec)

也能达到相同效果。
外连接：分为左外连接和右外连接，同时能显示不满足连接条件的记录(不满足的地方显示为NULL值)。
例：
首先向tb_student表中新插入一条数据：

mysql> INSERT INTO tb_student(id,name,sex)
    -> VALUES(NULL,'Peiqi','girl');
Query OK, 1 row affected (0.01 sec)

然后利用左外连接查询所有学生及学习的课程情况：

mysql> SELECT a.id,name,sex,courseid,score
    -> FROM tb_student a LEFT OUTER JOIN tb_score b
    -> ON a.id=b.id;
+--------+--------+------+----------+-------+
| id     | name   | sex  | courseid | score |
+--------+--------+------+----------+-------+
| 100001 | Bob    | boy  |     2001 |    89 |
| 100002 | LiMing | boy  |     2001 |    95 |
| 100003 | Jack   | boy  |     2001 |    84 |
| 100004 | Rose   | girl |     2003 |    65 |
| 100004 | Rose   | girl |     2004 |    89 |
| 100005 | Tony   | boy  |     2003 |    87 |
| 100006 | Obama  | boy  |     2004 |    85 |
| 100008 | Peiqi  | girl |     NULL |  NULL |
+--------+--------+------+----------+-------+
8 rows in set (0.00 sec)

由于只是tb_student表中插入记录，tb_score表中并没有新插入学生的课程情况，因此查询结果显示为NULL值。
4).子查询
带IN关键字的子查询：
例：查询有课程记录的学生姓名

mysql> SELECT name FROM tb_student
    -> WHERE tb_student.id IN
    -> (SELECT DISTINCT tb_score.id FROM tb_score);#DISTINCT关键字去除重复项
+--------+
| name   |
+--------+
| Bob    |
| LiMing |
| Jack   |
| Rose   |
| Tony   |
| Obama  |
+--------+
6 rows in set (0.00 sec)

带比较运算符的子查询：
例：查询成绩为65分的学生学号及姓名

mysql> SELECT id,name FROM tb_student
    -> WHERE id=
    -> (SELECT id from tb_score WHERE score=65);
+--------+------+
| id     | name |
+--------+------+
| 100004 | Rose |
+--------+------+
1 row in set (0.00 sec)

带EXISTS关键字的查询：
例：查询学习了课程编号为2001的学生姓名

mysql> SELECT name FROM tb_student a
    -> WHERE EXISTS
    -> (SELECT * FROM tb_score b WHERE a.id=b.id AND courseid='2001');
+--------+
| name   |
+--------+
| Bob    |
| LiMing |
| Jack   |
+--------+
3 rows in set (0.00 sec)

5).联合查询
例：查询学了English或Science的学生学号

mysql> SELECT id FROM tb_score,tb_course
    -> WHERE tb_score.courseid=tb_course.courseid AND coursename='English'
    -> UNION
    -> SELECT id FROM tb_score,tb_course
    -> WHERE tb_score.courseid=tb_course.courseid AND coursename='Science';
+--------+
| id     |
+--------+
| 100004 |
| 100005 |
| 100006 |
+--------+
3 rows in set (0.00 sec)

该语句也等价于：

mysql> SELECT DISTINCT id FROM tb_score,tb_course
    -> WHERE tb_score.courseid=tb_course.courseid
    -> AND(coursename='English' OR coursename='Science');
+--------+
| id     |
+--------+
| 100004 |
| 100005 |
| 100006 |
+--------+
3 rows in set (0.00 sec)

可以看出联合查询自动去除了重复项。

恰恰·

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Mysql平台下的SQL交互操作(1)

一、数据库1.创建数据库：基本语法格式：CREATE DATABASE (IF NOT EXISTS) db_name #创建数据库(DEFAULT) CHARACTER SET (=) charset_name #指定数据库字符集(DEFAULT) COLLATE (=) collation_name; #校对规则例：创建一个名为db_student的数据库：mysql> C...
复制链接

扫一扫