Mysql平台下的SQL交互操作(1)
一、数据库
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)
由于学号为自增型,因此插入空值系统默认为已有最大值+1,即100006+1=100007,同时birthday字段没有插入值因此值为空。
4).另一种插入数据的方法:
语法格式:
REPLACE INTO tb_name(column_list) VALUES(value_list);
例:
当前表已存在这样的数据记录:100007 | Peiqi | girl | NULL | US ,且id为该表的主键,现在要向表中再次插入数据:100007 | Geoge | boy | 2011-06-30 | US。
首先使用INSERT语句插入记录:
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)
可以看出联合查询自动去除了重复项。