一、数据库基本操作
1.1 命令行连接数据库
C:\Users\lizheying>mysql -uroot -p
Enter password: *********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.7.17-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
1.2 退出登录
quit与exit
mysql> exit
Bye
1.3 查询版本和当前时间
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.17-log |
+------------+
1 row in set (0.00 sec)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2019-11-19 12:04:31 |
+---------------------+
1 row in set (0.00 sec)
1.4 修改输入提示符
prompt python>
pronpt \U-\D>
- \U使用用户
- \D完整日期
mysql> prompt python>
PROMPT set to 'python>'
python>prompt \U
PROMPT set to '\U'
root@localhostprompt \D
PROMPT set to '\D'
Tue Nov 19 12:07:15 2019prompt \U-\D:
PROMPT set to '\U-\D:'
root@localhost-Tue Nov 19 12:07:32 2019:prompt mysql>
PROMPT set to 'mysql>'
mysql>
1.5 查看创建数据库
mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bank |
| demo |
| kaige |
| mysql |
| performance_schema |
| stutest |
| sys |
| test |
| test1 |
| testjoin |
+--------------------+
11 rows in set (0.01 sec)
1.6 查看当前使用的数据库
mysql>select database();
+------------+
| database() |
+------------+
| NULL |
+------------+
1 row in set (0.01 sec)
1.7 使用数据库
mysql>use kaige;
Database changed
1.8 创建数据库
mysql>create database demo1;
Query OK, 1 row affected (0.01 sec)
mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bank |
| demo |
| demo1 |
| kaige |
| mysql |
| performance_schema |
| stutest |
| sys |
| test |
| test1 |
| testjoin |
+--------------------+
12 rows in set (0.00 sec)
1.9 查看数据库的创建语句
mysql>show create database bank;
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| bank | CREATE DATABASE `bank` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)
1.10 删除数据库
mysql>drop database demo;
Query OK, 1 row affected (0.09 sec)
mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bank |
| demo1 |
| kaige |
| mysql |
| performance_schema |
| stutest |
| sys |
| test |
| test1 |
| testjoin |
+--------------------+
11 rows in set (0.00 sec)
1.11 创建数据库时指定字符集
mysql>create database demo charset=utf8;
Query OK, 1 row affected (0.01 sec)
mysql>show create database demo;
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| demo | CREATE DATABASE `demo` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)
二、数据表的操作
2.1 查看当前数据库中所有表
在使用的时候最好将制表符去掉shift+tab
mysql>use demo;
Database changed
mysql>show tables;
Empty set (0.00 sec)
2.2 创建表
create table students (字段名 字段类型 字段约束)
mysql>create table students(
-> id int unsigned primary key auto_increment,
-> name varchar(15) not null,
-> age tinyint unsigned default 0,
-> high decimal(5,2) default 0,
-> gender enum("男","女","中性","保密") default "保密",默认从1开始
-> cls_id int unsigned not null
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>show tables;
+----------------+
| Tables_in_demo |
+----------------+
| students |
+----------------+
1 row in set (0.00 sec)
2.3 查看表的创建语句
mysql>show create table students;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(15) NOT NULL,
`age` tinyint(3) unsigned DEFAULT '0',
`high` decimal(5,2) DEFAULT '0.00',
`gender` enum('男','女','中性','保密') DEFAULT '保密',
`cls_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
2.4 查看表结构
+--------+-------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(15) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | 0 | |
| high | decimal(5,2) | YES | | 0.00 | |
| gender | enum('男','女','中性','保密') | YES | | 保密 | |
| cls_id | int(10) unsigned | NO | | NULL | |
+--------+-------------------------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)
2.5 修改表结构
alter add/modify/change
mysql>alter table students add birthday date default "2000-01-01";
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>desc students;
+----------+-------------------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------------------------+------+-----+------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(15) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | 0 | |
| high | decimal(5,2) | YES | | 0.00 | |
| gender | enum('男','女','中性','保密') | YES | | 保密 | |
| cls_id | int(10) unsigned | NO | | NULL | |
| birthday | date | YES | | 2000-01-01 | |
+----------+-------------------------------+------+-----+------------+----------------+
7 rows in set (0.00 sec)
修改字段类型使用modify,
修改字段名和类型使用change
mysql>alter table students change birthday birth date default "2000-01-01";
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>desc students;
+--------+-------------------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------------------+------+-----+------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(15) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | 0 | |
| high | decimal(5,2) | YES | | 0.00 | |
| gender | enum('男','女','中性','保密') | YES | | 保密 | |
| cls_id | int(10) unsigned | NO | | NULL | |
| birth | date | YES | | 2000-01-01 | |
+--------+-------------------------------+------+-----+------------+----------------+
7 rows in set (0.00 sec)
2.6 删除字段
mysql>alter table students drop birth;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>desc students;
+--------+-------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(15) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | 0 | |
| high | decimal(5,2) | YES | | 0.00 | |
| gender | enum('男','女','中性','保密') | YES | | 保密 | |
| cls_id | int(10) unsigned | NO | | NULL | |
+--------+-------------------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
2.7 删除表
drop table students;
三、增删改
3.1 增加 insert
占位符:只有主键字段才有占位符的概念0,default,NULL
- 全列插入
全列插入在实际开发过程中用的不多,如果表结构一旦发生变化,全列插入就会报错
mysql>insert into classes values(0,"python5期"),(0,"python6期"),(0,"python7期");
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>select * from classes;
+----+-----------+
| id | name |
+----+-----------+
| 1 | python5期 |
| 2 | python6期 |
| 3 | python7期 |
+----+-----------+
3 rows in set (0.01 sec)
mysql>insert into students values (0,"小乔",18,180,"女",2);
Query OK, 1 row affected (0.01 sec)
mysql>select * from students;
+----+------+------+--------+--------+--------+
| id | name | age | high | gender | cls_id |
+----+------+------+--------+--------+--------+
| 1 | 小乔 | 18 | 180.00 | 女 | 2 |
+----+------+------+--------+--------+--------+
1 row in set (0.00 sec)
mysql>insert into students values (0,"大乔",19,180,"女",2);
Query OK, 1 row affected (0.01 sec)
- 指定列插入
mysql>insert into students (name, high, gender, cls_id) values ("张飞", 190, "保密", 1);
Query OK, 1 row affected (0.01 sec)
mysql>select * from students;
+----+------+------+--------+--------+--------+
| id | name | age | high | gender | cls_id |
+----+------+------+--------+--------+--------+
| 1 | 小乔 | 18 | 180.00 | 女 | 2 |
| 2 | 张飞 | 0 | 190.00 | 保密 | 1 |
| 3 | 大乔 | 19 | 180.00 | 女 | 2 |
+----+------+------+--------+--------+--------+
3 rows in set (0.00 sec)
利用枚举插入
mysql>insert into students (name, high, gender, cls_id) values ("鲁班", 150, 1, 1);
Query OK, 1 row affected (0.01 sec)
mysql>select * from students;
+----+------+------+--------+--------+--------+
| id | name | age | high | gender | cls_id |
+----+------+------+--------+--------+--------+
| 1 | 小乔 | 18 | 180.00 | 女 | 2 |
| 2 | 张飞 | 0 | 190.00 | 保密 | 1 |
| 3 | 大乔 | 19 | 180.00 | 女 | 2 |
| 4 | 鲁班 | 0 | 150.00 | 男 | 1 |
+----+------+------+--------+--------+--------+
4 rows in set (0.00 sec)
mysql>insert into students (name, high, gender, cls_id) values ("静香", 150, 2, 1);
Query OK, 1 row affected (0.01 sec)
mysql>select * from students;
+----+------+------+--------+--------+--------+
| id | name | age | high | gender | cls_id |
+----+------+------+--------+--------+--------+
| 1 | 小乔 | 18 | 180.00 | 女 | 2 |
| 2 | 张飞 | 0 | 190.00 | 保密 | 1 |
| 3 | 大乔 | 19 | 180.00 | 女 | 2 |
| 4 | 鲁班 | 0 | 150.00 | 男 | 1 |
| 5 | 静香 | 0 | 150.00 | 女 | 1 |
+----+------+------+--------+--------+--------+
5 rows in set (0.00 sec)
- 多行插入
mysql>insert into students values (0,"孙尚香", 18,170,2,2),(0,"甄姬", 20,165,2,3);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>select * from students;
+----+--------+------+--------+--------+--------+
| id | name | age | high | gender | cls_id |
+----+--------+------+--------+--------+--------+
| 1 | 小乔 | 18 | 180.00 | 女 | 2 |
| 2 | 张飞 | 0 | 190.00 | 保密 | 1 |
| 3 | 大乔 | 19 | 180.00 | 女 | 2 |
| 4 | 鲁班 | 0 | 150.00 | 男 | 1 |
| 5 | 静香 | 0 | 150.00 | 女 | 1 |
| 6 | 孙尚香 | 18 | 170.00 | 女 | 2 |
| 7 | 甄姬 | 20 | 165.00 | 女 | 3 |
+----+--------+------+--------+--------+--------+
7 rows in set (0.01 sec)
mysql>insert into students (name, high, gender, cls_id) values ("金莲", 150, 2, 1),("关羽",190,1,3);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>select * from students;
+----+--------+------+--------+--------+--------+
| id | name | age | high | gender | cls_id |
+----+--------+------+--------+--------+--------+
| 1 | 小乔 | 18 | 180.00 | 女 | 2 |
| 2 | 张飞 | 0 | 190.00 | 保密 | 1 |
| 3 | 大乔 | 19 | 180.00 | 女 | 2 |
| 4 | 鲁班 | 0 | 150.00 | 男 | 1 |
| 5 | 静香 | 0 | 150.00 | 女 | 1 |
| 6 | 孙尚香 | 18 | 170.00 | 女 | 2 |
| 7 | 甄姬 | 20 | 165.00 | 女 | 3 |
| 8 | 金莲 | 0 | 150.00 | 女 | 1 |
| 9 | 关羽 | 0 | 190.00 | 男 | 3 |
+----+--------+------+--------+--------+--------+
9 rows in set (0.01 sec)
3.2 修改update
没有where进行条件限制就是全表更新
mysql>update students set age = 20;
Query OK, 8 rows affected (0.01 sec)
Rows matched: 9 Changed: 8 Warnings: 0
mysql>select * from students;
+----+--------+------+--------+--------+--------+
| id | name | age | high | gender | cls_id |
+----+--------+------+--------+--------+--------+
| 1 | 小乔 | 20 | 180.00 | 女 | 2 |
| 2 | 张飞 | 20 | 190.00 | 保密 | 1 |
| 3 | 大乔 | 20 | 180.00 | 女 | 2 |
| 4 | 鲁班 | 20 | 150.00 | 男 | 1 |
| 5 | 静香 | 20 | 150.00 | 女 | 1 |
| 6 | 孙尚香 | 20 | 170.00 | 女 | 2 |
| 7 | 甄姬 | 20 | 165.00 | 女 | 3 |
| 8 | 金莲 | 20 | 150.00 | 女 | 1 |
| 9 | 关羽 | 20 | 190.00 | 男 | 3 |
+----+--------+------+--------+--------+--------+
9 rows in set (0.00 sec)
update 表名 set 列1=值1,列2=值2…[where 条件]
mysql>update students set age = 25 where id = 4;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>select * from students;
+----+--------+------+--------+--------+--------+
| id | name | age | high | gender | cls_id |
+----+--------+------+--------+--------+--------+
| 1 | 小乔 | 20 | 180.00 | 女 | 2 |
| 2 | 张飞 | 20 | 190.00 | 保密 | 1 |
| 3 | 大乔 | 20 | 180.00 | 女 | 2 |
| 4 | 鲁班 | 25 | 150.00 | 男 | 1 |
| 5 | 静香 | 20 | 150.00 | 女 | 1 |
| 6 | 孙尚香 | 20 | 170.00 | 女 | 2 |
| 7 | 甄姬 | 20 | 165.00 | 女 | 3 |
| 8 | 金莲 | 20 | 150.00 | 女 | 1 |
| 9 | 关羽 | 20 | 190.00 | 男 | 3 |
+----+--------+------+--------+--------+--------+
9 rows in set (0.00 sec)
-- 在sql中,通过一个等号表示相等,-- 表示注释
mysql>update students set age = 30 where id = 5;-- 在sql中,通过一个等号表示相等,-- 表示注释
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>select * from students;
+----+--------+------+--------+--------+--------+
| id | name | age | high | gender | cls_id |
+----+--------+------+--------+--------+--------+
| 1 | 小乔 | 20 | 180.00 | 女 | 2 |
| 2 | 张飞 | 30 | 190.00 | 保密 | 1 |
| 3 | 大乔 | 20 | 180.00 | 女 | 2 |
| 4 | 鲁班 | 25 | 150.00 | 男 | 1 |
| 5 | 静香 | 30 | 150.00 | 女 | 1 |
| 6 | 孙尚香 | 20 | 170.00 | 女 | 2 |
| 7 | 甄姬 | 20 | 165.00 | 女 | 3 |
| 8 | 金莲 | 20 | 150.00 | 女 | 1 |
| 9 | 关羽 | 20 | 190.00 | 男 | 3 |
+----+--------+------+--------+--------+--------+
9 rows in set (0.00 sec)
3.3 删除 delete
物理删除
mysql>delete from students where id = 5;
Query OK, 1 row affected (0.01 sec)
mysql>select * from students;
+----+--------+------+--------+--------+--------+
| id | name | age | high | gender | cls_id |
+----+--------+------+--------+--------+--------+
| 1 | 小乔 | 20 | 180.00 | 女 | 2 |
| 2 | 张飞 | 30 | 190.00 | 保密 | 1 |
| 3 | 大乔 | 20 | 180.00 | 女 | 2 |
| 4 | 鲁班 | 25 | 150.00 | 男 | 1 |
| 6 | 孙尚香 | 20 | 170.00 | 女 | 2 |
| 7 | 甄姬 | 20 | 165.00 | 女 | 3 |
| 8 | 金莲 | 20 | 150.00 | 女 | 1 |
| 9 | 关羽 | 20 | 190.00 | 男 | 3 |
+----+--------+------+--------+--------+--------+
8 rows in set (0.00 sec)
四、查询select
4.1 数据准备
mysql>create database python_test_1 charset=utf8;
Query OK, 1 row affected (0.02 sec)
mysql>use python_test_1;
Database changed
mysql>create table students(
-> id int unsigned primary key auto_increment not null,
-> name varchar(20) default "",
-> age tinyint unsigned default 0,
-> height decimal(5,2),
-> gender enum("男","女","中性","保密") default "保密",
-> cls_id int unsigned default 0,
-> is_delete bit default 0
-> );
Query OK, 0 rows affected (0.05 sec)
mysql>create table classes (
-> id int unsigned auto_increment primary key,
-> name varchar(30) not null
-> );
Query OK, 0 rows affected (0.02 sec)
mysql>insert into students values
-> (0,"小明",18,180,2,1,0),
-> (0,"小月月",18,180,2,2,1),
-> (0,"彭于晏",29,185,1,1,0),
-> (0,"刘德华",59,175,1,2,1),
-> (0,"黄蓉",38,160,2,1,0),
-> (0,"凤姐",28,150,4,2,1),
-> (0,"王祖贤",18,172,2,1,1),
-> (0,"周杰伦",36,NULL,1,1,0),
-> (0,"陈坤",27,181,1,2,0),
-> (0,"刘亦菲",25,166,2,2,0),
-> (0,"金星",33,162,3,3,1),
-> (0,"静香",12,180,2,4,0),
-> (0,"郭靖",12,170,1,4,0),
-> (0,"周杰",34,176,2,5,0);
Query OK, 14 rows affected (0.01 sec)
Records: 14 Duplicates: 0 Warnings: 0
mysql>insert into classes values (0,"python_01期"), (0,"python_02期");
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>select * from students;
+----+--------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+--------+------+--------+--------+--------+-----------+
| 1 | 小明 | 18 | 180.00 | 女 | 1 | |
| 2 | 小月月 | 18 | 180.00 | 女 | 2 | |
| 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | |
| 4 | 刘德华 | 59 | 175.00 | 男 | 2 | |
| 5 | 黄蓉 | 38 | 160.00 | 女 | 1 | |
| 6 | 凤姐 | 28 | 150.00 | 保密 | 2 | |
| 7 | 王祖贤 | 18 | 172.00 | 女 | 1 | |
| 8 | 周杰伦 | 36 | NULL | 男 | 1 | |
| 9 | 陈坤 | 27 | 181.00 | 男 | 2 | |
| 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | |
| 11 | 金星 | 33 | 162.00 | 中性 | 3 | |
| 12 | 静香 | 12 | 180.00 | 女 | 4 | |
| 13 | 郭靖 | 12 | 170.00 | 男 | 4 | |
| 14 | 周杰 | 34 | 176.00 | 女 | 5 | |
+----+--------+------+--------+--------+--------+-----------+
14 rows in set (0.01 sec)
mysql>select * from classes;
+----+-------------+
| id | name |
+----+-------------+
| 1 | python_01期 |
| 2 | python_02期 |
+----+-------------+
2 rows in set (0.00 sec)
4.2 查询指定字段
mysql>select name, height from students;
+--------+--------+
| name | height |
+--------+--------+
| 小明 | 180.00 |
| 小月月 | 180.00 |
| 彭于晏 | 185.00 |
| 刘德华 | 175.00 |
| 黄蓉 | 160.00 |
| 凤姐 | 150.00 |
| 王祖贤 | 172.00 |
| 周杰伦 | NULL |
| 陈坤 | 181.00 |
| 刘亦菲 | 166.00 |
| 金星 | 162.00 |
| 静香 | 180.00 |
| 郭靖 | 170.00 |
| 周杰 | 176.00 |
+--------+--------+
14 rows in set (0.00 sec)
4.3 使用as给字段起别名
mysql>select name as 姓名, height as 身高 from students;
+--------+--------+
| 姓名 | 身高 |
+--------+--------+
| 小明 | 180.00 |
| 小月月 | 180.00 |
| 彭于晏 | 185.00 |
| 刘德华 | 175.00 |
| 黄蓉 | 160.00 |
| 凤姐 | 150.00 |
| 王祖贤 | 172.00 |
| 周杰伦 | NULL |
| 陈坤 | 181.00 |
| 刘亦菲 | 166.00 |
| 金星 | 162.00 |
| 静香 | 180.00 |
| 郭靖 | 170.00 |
| 周杰 | 176.00 |
+--------+--------+
14 rows in set (0.01 sec)
as也可以省略
mysql>select name 姓名, height 身高 from students;
+--------+--------+
| 姓名 | 身高 |
+--------+--------+
| 小明 | 180.00 |
| 小月月 | 180.00 |
| 彭于晏 | 185.00 |
| 刘德华 | 175.00 |
| 黄蓉 | 160.00 |
| 凤姐 | 150.00 |
| 王祖贤 | 172.00 |
| 周杰伦 | NULL |
| 陈坤 | 181.00 |
| 刘亦菲 | 166.00 |
| 金星 | 162.00 |
| 静香 | 180.00 |
| 郭靖 | 170.00 |
| 周杰 | 176.00 |
+--------+--------+
14 rows in set (0.00 sec)
4.4 sql语句完整的形式
多表查询的时候就需要将表名带上
mysql>select students.name,students.height from students;
+--------+--------+
| name | height |
+--------+--------+
| 小明 | 180.00 |
| 小月月 | 180.00 |
| 彭于晏 | 185.00 |
| 刘德华 | 175.00 |
| 黄蓉 | 160.00 |
| 凤姐 | 150.00 |
| 王祖贤 | 172.00 |
| 周杰伦 | NULL |
| 陈坤 | 181.00 |
| 刘亦菲 | 166.00 |
| 金星 | 162.00 |
| 静香 | 180.00 |
| 郭靖 | 170.00 |
| 周杰 | 176.00 |
+--------+--------+
14 rows in set (0.00 sec)
还可以在表前面加上数据库名
4.5 使用as给表起别名
mysql>select s.name, s.height from students as s;
+--------+--------+
| name | height |
+--------+--------+
| 小明 | 180.00 |
| 小月月 | 180.00 |
| 彭于晏 | 185.00 |
| 刘德华 | 175.00 |
| 黄蓉 | 160.00 |
| 凤姐 | 150.00 |
| 王祖贤 | 172.00 |
| 周杰伦 | NULL |
| 陈坤 | 181.00 |
| 刘亦菲 | 166.00 |
| 金星 | 162.00 |
| 静香 | 180.00 |
| 郭靖 | 170.00 |
| 周杰 | 176.00 |
+--------+--------+
14 rows in set (0.00 sec)
4.6 消除重复行
查询班级学生的性别
mysql>select gender from students;
+--------+
| gender |
+--------+
| 女 |
| 女 |
| 男 |
| 男 |
| 女 |
| 保密 |
| 女 |
| 男 |
| 男 |
| 女 |
| 中性 |
| 女 |
| 男 |
| 女 |
+--------+
14 rows in set (0.00 sec)
mysql>select distinct gender from students;
+--------+
| gender |
+--------+
| 女 |
| 男 |
| 保密 |
| 中性 |
+--------+
4 rows in set (0.01 sec)
4.7 条件查询where
- 比较运算符
查询大于18岁的学生信息
mysql>select * from students where age > 18;
+----+--------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+--------+------+--------+--------+--------+-----------+
| 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | |
| 4 | 刘德华 | 59 | 175.00 | 男 | 2 | |
| 5 | 黄蓉 | 38 | 160.00 | 女 | 1 | |
| 6 | 凤姐 | 28 | 150.00 | 保密 | 2 | |
| 8 | 周杰伦 | 36 | NULL | 男 | 1 | |
| 9 | 陈坤 | 27 | 181.00 | 男 | 2 | |
| 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | |
| 11 | 金星 | 33 | 162.00 | 中性 | 3 | |
| 14 | 周杰 | 34 | 176.00 | 女 | 5 | |
+----+--------+------+--------+--------+--------+-----------+
9 rows in set (0.01 sec)
查询不等于18岁的学生信息
mysql>select * from students where age != 18;
+----+--------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+--------+------+--------+--------+--------+-----------+
| 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | |
| 4 | 刘德华 | 59 | 175.00 | 男 | 2 | |
| 5 | 黄蓉 | 38 | 160.00 | 女 | 1 | |
| 6 | 凤姐 | 28 | 150.00 | 保密 | 2 | |
| 8 | 周杰伦 | 36 | NULL | 男 | 1 | |
| 9 | 陈坤 | 27 | 181.00 | 男 | 2 | |
| 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | |
| 11 | 金星 | 33 | 162.00 | 中性 | 3 | |
| 12 | 静香 | 12 | 180.00 | 女 | 4 | |
| 13 | 郭靖 | 12 | 170.00 | 男 | 4 | |
| 14 | 周杰 | 34 | 176.00 | 女 | 5 | |
+----+--------+------+--------+--------+--------+-----------+
11 rows in set (0.00 sec)
- 逻辑运算符
查询18岁以上的女性
mysql>select * from students where age > 18 and gender = 2;
+----+--------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+--------+------+--------+--------+--------+-----------+
| 5 | 黄蓉 | 38 | 160.00 | 女 | 1 | |
| 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | |
| 14 | 周杰 | 34 | 176.00 | 女 | 5 | |
+----+--------+------+--------+--------+--------+-----------+
3 rows in set (0.01 sec)
查询18岁以上或者身高超过180(包含)的学生信息
mysql>select * from students where age > 18 or height >= 180;
+----+--------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+--------+------+--------+--------+--------+-----------+
| 1 | 小明 | 18 | 180.00 | 女 | 1 | |
| 2 | 小月月 | 18 | 180.00 | 女 | 2 | |
| 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | |
| 4 | 刘德华 | 59 | 175.00 | 男 | 2 | |
| 5 | 黄蓉 | 38 | 160.00 | 女 | 1 | |
| 6 | 凤姐 | 28 | 150.00 | 保密 | 2 | |
| 8 | 周杰伦 | 36 | NULL | 男 | 1 | |
| 9 | 陈坤 | 27 | 181.00 | 男 | 2 | |
| 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | |
| 11 | 金星 | 33 | 162.00 | 中性 | 3 | |
| 12 | 静香 | 12 | 180.00 | 女 | 4 | |
| 14 | 周杰 | 34 | 176.00 | 女 | 5 | |
+----+--------+------+--------+--------+--------+-----------+
12 rows in set (0.01 sec)
查询年龄不是18岁的学生
mysql>select * from students where age != 18;
+----+--------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+--------+------+--------+--------+--------+-----------+
| 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | |
| 4 | 刘德华 | 59 | 175.00 | 男 | 2 | |
| 5 | 黄蓉 | 38 | 160.00 | 女 | 1 | |
| 6 | 凤姐 | 28 | 150.00 | 保密 | 2 | |
| 8 | 周杰伦 | 36 | NULL | 男 | 1 | |
| 9 | 陈坤 | 27 | 181.00 | 男 | 2 | |
| 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | |
| 11 | 金星 | 33 | 162.00 | 中性 | 3 | |
| 12 | 静香 | 12 | 180.00 | 女 | 4 | |
| 13 | 郭靖 | 12 | 170.00 | 男 | 4 | |
| 14 | 周杰 | 34 | 176.00 | 女 | 5 | |
+----+--------+------+--------+--------+--------+-----------+
11 rows in set (0.00 sec)
mysql>select * from students where not age = 18;
+----+--------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+--------+------+--------+--------+--------+-----------+
| 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | |
| 4 | 刘德华 | 59 | 175.00 | 男 | 2 | |
| 5 | 黄蓉 | 38 | 160.00 | 女 | 1 | |
| 6 | 凤姐 | 28 | 150.00 | 保密 | 2 | |
| 8 | 周杰伦 | 36 | NULL | 男 | 1 | |
| 9 | 陈坤 | 27 | 181.00 | 男 | 2 | |
| 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | |
| 11 | 金星 | 33 | 162.00 | 中性 | 3 | |
| 12 | 静香 | 12 | 180.00 | 女 | 4 | |
| 13 | 郭靖 | 12 | 170.00 | 男 | 4 | |
| 14 | 周杰 | 34 | 176.00 | 女 | 5 | |
+----+--------+------+--------+--------+--------+-----------+
11 rows in set (0.01 sec)
- 模糊查询
like
%任意字符可有可无
_表示任意一个字符
查询姓名中以"小"开始的学生信息
mysql>select * from students where name like "小%";
+----+--------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+--------+------+--------+--------+--------+-----------+
| 1 | 小明 | 18 | 180.00 | 女 | 1 | |
| 2 | 小月月 | 18 | 180.00 | 女 | 2 | |
+----+--------+------+--------+--------+--------+-----------+
2 rows in set (0.01 sec)
名字中包含"杰"的学生信息
mysql>select * from students where name like "%杰%";
+----+--------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+--------+------+--------+--------+--------+-----------+
| 8 | 周杰伦 | 36 | NULL | 男 | 1 | |
| 14 | 周杰 | 34 | 176.00 | 女 | 5 | |
+----+--------+------+--------+--------+--------+-----------+
2 rows in set (0.00 sec)
查询名字是两个字的学生信息
mysql>select * from students where name like "__";
+----+------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+------+------+--------+--------+--------+-----------+
| 1 | 小明 | 18 | 180.00 | 女 | 1 | |
| 5 | 黄蓉 | 38 | 160.00 | 女 | 1 | |
| 6 | 凤姐 | 28 | 150.00 | 保密 | 2 | |
| 9 | 陈坤 | 27 | 181.00 | 男 | 2 | |
| 11 | 金星 | 33 | 162.00 | 中性 | 3 | |
| 12 | 静香 | 12 | 180.00 | 女 | 4 | |
| 13 | 郭靖 | 12 | 170.00 | 男 | 4 | |
| 14 | 周杰 | 34 | 176.00 | 女 | 5 | |
+----+------+------+--------+--------+--------+-----------+
8 rows in set (0.00 sec)
查询名字是三个字的学生信息
mysql>select * from students where name like "___";
+----+--------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+--------+------+--------+--------+--------+-----------+
| 2 | 小月月 | 18 | 180.00 | 女 | 2 | |
| 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | |
| 4 | 刘德华 | 59 | 175.00 | 男 | 2 | |
| 7 | 王祖贤 | 18 | 172.00 | 女 | 1 | |
| 8 | 周杰伦 | 36 | NULL | 男 | 1 | |
| 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | |
+----+--------+------+--------+--------+--------+-----------+
6 rows in set (0.00 sec)
sql中可以使用正则表达式完成查询
select * from students where name rlike "^周";
- 范围查询
查询年龄为18、34的学生
mysql>select * from students where age in (18,34);
+----+--------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+--------+------+--------+--------+--------+-----------+
| 1 | 小明 | 18 | 180.00 | 女 | 1 | |
| 2 | 小月月 | 18 | 180.00 | 女 | 2 | |
| 7 | 王祖贤 | 18 | 172.00 | 女 | 1 | |
| 14 | 周杰 | 34 | 176.00 | 女 | 5 | |
+----+--------+------+--------+--------+--------+-----------+
4 rows in set (0.00 sec)
年龄不是18、34的学生信息
mysql>select * from students where age not in (18,34);
+----+--------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+--------+------+--------+--------+--------+-----------+
| 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | |
| 4 | 刘德华 | 59 | 175.00 | 男 | 2 | |
| 5 | 黄蓉 | 38 | 160.00 | 女 | 1 | |
| 6 | 凤姐 | 28 | 150.00 | 保密 | 2 | |
| 8 | 周杰伦 | 36 | NULL | 男 | 1 | |
| 9 | 陈坤 | 27 | 181.00 | 男 | 2 | |
| 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | |
| 11 | 金星 | 33 | 162.00 | 中性 | 3 | |
| 12 | 静香 | 12 | 180.00 | 女 | 4 | |
| 13 | 郭靖 | 12 | 170.00 | 男 | 4 | |
+----+--------+------+--------+--------+--------+-----------+
10 rows in set (0.00 sec)
18~34岁之间
mysql>select * from students where age>18 and age<34;
+----+--------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+--------+------+--------+--------+--------+-----------+
| 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | |
| 6 | 凤姐 | 28 | 150.00 | 保密 | 2 | |
| 9 | 陈坤 | 27 | 181.00 | 男 | 2 | |
| 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | |
| 11 | 金星 | 33 | 162.00 | 中性 | 3 | |
+----+--------+------+--------+--------+--------+-----------+
5 rows in set (0.00 sec)
-- between and 是包含首尾的
mysql>select * from students where age between 18 and 34;
+----+--------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+--------+------+--------+--------+--------+-----------+
| 1 | 小明 | 18 | 180.00 | 女 | 1 | |
| 2 | 小月月 | 18 | 180.00 | 女 | 2 | |
| 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | |
| 6 | 凤姐 | 28 | 150.00 | 保密 | 2 | |
| 7 | 王祖贤 | 18 | 172.00 | 女 | 1 | |
| 9 | 陈坤 | 27 | 181.00 | 男 | 2 | |
| 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | |
| 11 | 金星 | 33 | 162.00 | 中性 | 3 | |
| 14 | 周杰 | 34 | 176.00 | 女 | 5 | |
+----+--------+------+--------+--------+--------+-----------+
9 rows in set (0.00 sec)
年龄不在18~34之间
mysql>select * from students where age not between 18 and 34;
+----+--------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+--------+------+--------+--------+--------+-----------+
| 4 | 刘德华 | 59 | 175.00 | 男 | 2 | |
| 5 | 黄蓉 | 38 | 160.00 | 女 | 1 | |
| 8 | 周杰伦 | 36 | NULL | 男 | 1 | |
| 12 | 静香 | 12 | 180.00 | 女 | 4 | |
| 13 | 郭靖 | 12 | 170.00 | 男 | 4 | |
+----+--------+------+--------+--------+--------+-----------+
5 rows in set (0.00 sec)
- 空判断
查询身高为空的信息(这个时候不能用=来判断)
mysql>select * from students where height=NULL;--写法错误
Empty set (0.00 sec)
正确写法:
mysql>select * from students where height is NULL;
+----+--------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+--------+------+--------+--------+--------+-----------+
| 8 | 周杰伦 | 36 | NULL | 男 | 1 | |
+----+--------+------+--------+--------+--------+-----------+
1 row in set (0.01 sec)
查询身高不为空的学生
mysql>select * from students where height is not NULL;
+----+--------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+--------+------+--------+--------+--------+-----------+
| 1 | 小明 | 18 | 180.00 | 女 | 1 | |
| 2 | 小月月 | 18 | 180.00 | 女 | 2 | |
| 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | |
| 4 | 刘德华 | 59 | 175.00 | 男 | 2 | |
| 5 | 黄蓉 | 38 | 160.00 | 女 | 1 | |
| 6 | 凤姐 | 28 | 150.00 | 保密 | 2 | |
| 7 | 王祖贤 | 18 | 172.00 | 女 | 1 | |
| 9 | 陈坤 | 27 | 181.00 | 男 | 2 | |
| 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | |
| 11 | 金星 | 33 | 162.00 | 中性 | 3 | |
| 12 | 静香 | 12 | 180.00 | 女 | 4 | |
| 13 | 郭靖 | 12 | 170.00 | 男 | 4 | |
| 14 | 周杰 | 34 | 176.00 | 女 | 5 | |
+----+--------+------+--------+--------+--------+-----------+
13 rows in set (0.00 sec)
另一种写法:
mysql>select * from students where not height is NULL;
+----+--------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+--------+------+--------+--------+--------+-----------+
| 1 | 小明 | 18 | 180.00 | 女 | 1 | |
| 2 | 小月月 | 18 | 180.00 | 女 | 2 | |
| 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | |
| 4 | 刘德华 | 59 | 175.00 | 男 | 2 | |
| 5 | 黄蓉 | 38 | 160.00 | 女 | 1 | |
| 6 | 凤姐 | 28 | 150.00 | 保密 | 2 | |
| 7 | 王祖贤 | 18 | 172.00 | 女 | 1 | |
| 9 | 陈坤 | 27 | 181.00 | 男 | 2 | |
| 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | |
| 11 | 金星 | 33 | 162.00 | 中性 | 3 | |
| 12 | 静香 | 12 | 180.00 | 女 | 4 | |
| 13 | 郭靖 | 12 | 170.00 | 男 | 4 | |
| 14 | 周杰 | 34 | 176.00 | 女 | 5 | |
+----+--------+------+--------+--------+--------+-----------+
13 rows in set (0.00 sec)
4.8 排序
order by 默认升序asc,可以省略
查询年龄在18~34岁之间的男性,按照年龄从小到大排序
mysql>select * from students where age between 18 and 34 and gender = 1 order by age;
+----+--------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+--------+------+--------+--------+--------+-----------+
| 9 | 陈坤 | 27 | 181.00 | 男 | 2 | |
| 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | |
+----+--------+------+--------+--------+--------+-----------+
2 rows in set (0.00 sec)
查询年龄在18~34岁之间的女性,身高从高到矮进行排序
mysql>select * from students where age between 18 and 34 and gender = 2 order by height desc;
+----+--------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+--------+------+--------+--------+--------+-----------+
| 1 | 小明 | 18 | 180.00 | 女 | 1 | |
| 2 | 小月月 | 18 | 180.00 | 女 | 2 | |
| 14 | 周杰 | 34 | 176.00 | 女 | 5 | |
| 7 | 王祖贤 | 18 | 172.00 | 女 | 1 | |
| 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | |
+----+--------+------+--------+--------+--------+-----------+
5 rows in set (0.00 sec)
查询年龄在18~34岁之间的女性,身高从高到矮排序,如果身高相同的情况下按年龄从小到大排序
mysql>select * from students where age between 18 and 34 and gender = 2 order by height desc,age asc;
+----+--------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+--------+------+--------+--------+--------+-----------+
| 1 | 小明 | 18 | 180.00 | 女 | 1 | |
| 2 | 小月月 | 18 | 180.00 | 女 | 2 | |
| 14 | 周杰 | 34 | 176.00 | 女 | 5 | |
| 7 | 王祖贤 | 18 | 172.00 | 女 | 1 | |
| 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | |
+----+--------+------+--------+--------+--------+-----------+
5 rows in set (0.00 sec)
接上,如果年龄也相同,那么按照id从大到小排序
mysql>select * from students where age between 18 and 34 and gender = 2 order by height desc,age asc,id desc;
+----+--------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+--------+------+--------+--------+--------+-----------+
| 2 | 小月月 | 18 | 180.00 | 女 | 2 | |
| 1 | 小明 | 18 | 180.00 | 女 | 1 | |
| 14 | 周杰 | 34 | 176.00 | 女 | 5 | |
| 7 | 王祖贤 | 18 | 172.00 | 女 | 1 | |
| 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | |
+----+--------+------+--------+--------+--------+-----------+
5 rows in set (0.00 sec)
按照年龄从小到大,身高从高到矮来排序
mysql>select * from students order by age,height desc;
+----+--------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+--------+------+--------+--------+--------+-----------+
| 12 | 静香 | 12 | 180.00 | 女 | 4 | |
| 13 | 郭靖 | 12 | 170.00 | 男 | 4 | |
| 1 | 小明 | 18 | 180.00 | 女 | 1 | |
| 2 | 小月月 | 18 | 180.00 | 女 | 2 | |
| 7 | 王祖贤 | 18 | 172.00 | 女 | 1 | |
| 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | |
| 9 | 陈坤 | 27 | 181.00 | 男 | 2 | |
| 6 | 凤姐 | 28 | 150.00 | 保密 | 2 | |
| 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | |
| 11 | 金星 | 33 | 162.00 | 中性 | 3 | |
| 14 | 周杰 | 34 | 176.00 | 女 | 5 | |
| 8 | 周杰伦 | 36 | NULL | 男 | 1 | |
| 5 | 黄蓉 | 38 | 160.00 | 女 | 1 | |
| 4 | 刘德华 | 59 | 175.00 | 男 | 2 | |
+----+--------+------+--------+--------+--------+-----------+
14 rows in set (0.00 sec)
4.9 聚合函数(统计)
查询班级有多少人
mysql>select count(*) from students;
+----------+
| count(*) |
+----------+
| 14 |
+----------+
1 row in set (0.01 sec)
mysql>select count(id) from students;
+-----------+
| count(id) |
+-----------+
| 14 |
+-----------+
1 row in set (0.01 sec)
查询男性有多少人,女性有多少人
mysql>select gender, count(*) from students group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男 | 5 |
| 女 | 7 |
| 中性 | 1 |
| 保密 | 1 |
+--------+----------+
4 rows in set (0.00 sec)
查询最大的年龄
mysql>select max(age) from students;
+----------+
| max(age) |
+----------+
| 59 |
+----------+
1 row in set (0.01 sec)
查询女性的最高身高
mysql>select max(height) from students where gender = 2;
+-------------+
| max(height) |
+-------------+
| 180.00 |
+-------------+
1 row in set (0.01 sec)
查询最高身高的学生的名字
mysql>select name from students where height = (select max(height) from students);
+--------+
| name |
+--------+
| 彭于晏 |
+--------+
1 row in set (0.01 sec)
计算所有人的年龄总和
mysql>select sum(age) from students;
+----------+
| sum(age) |
+----------+
| 387 |
+----------+
1 row in set (0.01 sec)
计算平均年龄
mysql>select avg(age) from students;
+----------+
| avg(age) |
+----------+
| 27.6429 |
+----------+
1 row in set (0.00 sec)
计算平均身高
mysql>select avg(height) from students;
+-------------+
| avg(height) |
+-------------+
| 172.076923 |
+-------------+
1 row in set (0.00 sec)
计算所有人的平均年龄,保留2位小数
mysql>select round(avg(age),2) from students;
+-------------------+
| round(avg(age),2) |
+-------------------+
| 27.64 |
+-------------------+
1 row in set (0.01 sec)
4.10 分组,分组的目的是为了聚合统计
查询班级学生的性别
mysql>select gender from students group by gender;
+--------+
| gender |
+--------+
| 男 |
| 女 |
| 中性 |
| 保密 |
+--------+
4 rows in set (0.02 sec)
计算每种性别的人数
mysql>select gender, count(*) from students group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男 | 5 |
| 女 | 7 |
| 中性 | 1 |
| 保密 | 1 |
+--------+----------+
4 rows in set (0.00 sec)
查询分组数据中人的姓名
mysql>select gender, group_concat(name) from students group by gender;
+--------+------------------------------------------+
| gender | group_concat(name) |
+--------+------------------------------------------+
| 男 | 彭于晏,刘德华,周杰伦,陈坤,郭靖 |
| 女 | 小明,小月月,黄蓉,王祖贤,刘亦菲,静香,周杰 |
| 中性 | 金星 |
| 保密 | 凤姐 |
+--------+------------------------------------------+
4 rows in set (0.01 sec)
计算男性的人数
mysql>select gender, count(*) from students group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男 | 5 |
| 女 | 7 |
| 中性 | 1 |
| 保密 | 1 |
+--------+----------+
4 rows in set (0.00 sec)
需要在分组之后的数据中做进一步的筛选操作
having 条件筛选,表示对于已经分组的数据做进一步的筛选
对分组之后的数据做进一步的筛选操作,只能够使用having
mysql>select gender, count(*) from students group by gender having gender = 1;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男 | 5 |
+--------+----------+
1 row in set (0.01 sec)
除了男生以外的分组的人数
mysql>select gender, count(*) from students group by gender having not gender = 1;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 女 | 7 |
| 中性 | 1 |
| 保密 | 1 |
+--------+----------+
3 rows in set (0.01 sec)
having表示对于已经分组的数据做进一步的筛选,有having就一定有group by,有group by 不一定有having;
where 是对原数据做筛选操作
查询每种性别中的平均年龄
mysql>select gender, avg(age) from students group by gender;
+--------+----------+
| gender | avg(age) |
+--------+----------+
| 男 | 32.6000 |
| 女 | 23.2857 |
| 中性 | 33.0000 |
| 保密 | 28.0000 |
+--------+----------+
4 rows in set (0.01 sec)
查询每种性别中的平均年龄avg(age),最大年龄,平均身高,最高身高,分组是为了更好的统计
mysql>select gender, avg(age), max(age), avg(height), max(height) from students group by gender;
+--------+----------+----------+-------------+-------------+
| gender | avg(age) | max(age) | avg(height) | max(height) |
+--------+----------+----------+-------------+-------------+
| 男 | 32.6000 | 59 | 177.750000 | 185.00 |
| 女 | 23.2857 | 38 | 173.428571 | 180.00 |
| 中性 | 33.0000 | 33 | 162.000000 | 162.00 |
| 保密 | 28.0000 | 28 | 150.000000 | 150.00 |
+--------+----------+----------+-------------+-------------+
4 rows in set (0.01 sec)
查询平均年龄超过30岁的性别,以及姓名
mysql>select gender, group_concat(name) from students group by gender having avg(age) > 30;
+--------+--------------------------------+
| gender | group_concat(name) |
+--------+--------------------------------+
| 男 | 彭于晏,刘德华,周杰伦,陈坤,郭靖 |
| 中性 | 金星 |
+--------+--------------------------------+
2 rows in set (0.01 sec)
插入一道面试题
mysql>create table info(
-> id int unsigned primary key auto_increment,
-> name varchar(15) not null,
-> kecheng varchar(15) not null,
-> fenshu tinyint unsigned
-> );
Query OK, 0 rows affected (0.05 sec)
mysql>insert into info values
-> (0,"张三","语文",81),
-> (0,"张三","数学",75),
-> (0,"李四","语文",76),
-> (0,"李四","数学",90),
-> (0,"王五","语文",81),
-> (0,"王五","语文",81),
-> (0,"王五","数学",100),
-> (0,"王五","英语",90);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql>select * from info;
+----+------+---------+--------+
| id | name | kecheng | fenshu |
+----+------+---------+--------+
| 1 | 张三 | 语文 | 81 |
| 2 | 张三 | 数学 | 75 |
| 3 | 李四 | 语文 | 76 |
| 4 | 李四 | 数学 | 90 |
| 5 | 王五 | 语文 | 81 |
| 6 | 王五 | 语文 | 81 |
| 7 | 王五 | 数学 | 100 |
| 8 | 王五 | 英语 | 90 |
+----+------+---------+--------+
8 rows in set (0.00 sec)
用一条sql语句查询出每门课都大于80分的学生姓名
mysql>select name from info group by name having min(fenshu) > 80;
+------+
| name |
+------+
| 王五 |
+------+
1 row in set (0.00 sec)
4.11 分页
limit start,count
start:表示从哪里开始查询,start 默认值为0,可以省略,表示跳过多少条数据
count:查询多少条
获取第一页,每页显示4条数据
mysql>select * from students limit 0,4;
+----+--------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+--------+------+--------+--------+--------+-----------+
| 1 | 小明 | 18 | 180.00 | 女 | 1 | |
| 2 | 小月月 | 18 | 180.00 | 女 | 2 | |
| 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | |
| 4 | 刘德华 | 59 | 175.00 | 男 | 2 | |
+----+--------+------+--------+--------+--------+-----------+
4 rows in set (0.02 sec)
mysql>select * from students limit 4,4;
+----+--------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+--------+------+--------+--------+--------+-----------+
| 5 | 黄蓉 | 38 | 160.00 | 女 | 1 | |
| 6 | 凤姐 | 28 | 150.00 | 保密 | 2 | |
| 7 | 王祖贤 | 18 | 172.00 | 女 | 1 | |
| 8 | 周杰伦 | 36 | NULL | 男 | 1 | |
+----+--------+------+--------+--------+--------+-----------+
4 rows in set (0.01 sec)
mysql>select * from students limit 12,4;
+----+------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+------+------+--------+--------+--------+-----------+
| 13 | 郭靖 | 12 | 170.00 | 男 | 4 | |
| 14 | 周杰 | 34 | 176.00 | 女 | 5 | |
+----+------+------+--------+--------+--------+-----------+
2 rows in set (0.00 sec)
start 和id没有关系,表示跳过多少条数据
插入一道练习题:
每页显示4个,显示第三页的信息,按照年龄从小到大排序
mysql>select * from (select * from students limit 8,4) as a order by age;
+----+--------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+--------+------+--------+--------+--------+-----------+
| 12 | 静香 | 12 | 180.00 | 女 | 4 | |
| 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | |
| 9 | 陈坤 | 27 | 181.00 | 男 | 2 | |
| 11 | 金星 | 33 | 162.00 | 中性 | 3 | |
+----+--------+------+--------+--------+--------+-----------+
4 rows in set (0.00 sec)
先排序,后分页
mysql>select * from students order by age limit 8,4;
+----+--------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+--------+------+--------+--------+--------+-----------+
| 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | |
| 11 | 金星 | 33 | 162.00 | 中性 | 3 | |
| 14 | 周杰 | 34 | 176.00 | 女 | 5 | |
| 8 | 周杰伦 | 36 | NULL | 男 | 1 | |
+----+--------+------+--------+--------+--------+-----------+
4 rows in set (0.02 sec)
完整的sql语句
select distinct *
from 表名
where ...
group by ... having ...
order by ...
limit start,count
4.12 连接查询
笛卡尔积查询(实际开发过程中基本不用,很可能产生庞大的中间表)
mysql>select * from students,classes;
+----+--------+------+--------+--------+--------+-----------+----+-------------+
| id | name | age | height | gender | cls_id | is_delete | id | name |
+----+--------+------+--------+--------+--------+-----------+----+-------------+
| 1 | 小明 | 18 | 180.00 | 女 | 1 | | 1 | python_01期 |
| 1 | 小明 | 18 | 180.00 | 女 | 1 | | 2 | python_02期 |
| 2 | 小月月 | 18 | 180.00 | 女 | 2 | | 1 | python_01期 |
| 2 | 小月月 | 18 | 180.00 | 女 | 2 | | 2 | python_02期 |
| 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | | 1 | python_01期 |
| 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | | 2 | python_02期 |
| 4 | 刘德华 | 59 | 175.00 | 男 | 2 | | 1 | python_01期 |
| 4 | 刘德华 | 59 | 175.00 | 男 | 2 | | 2 | python_02期 |
| 5 | 黄蓉 | 38 | 160.00 | 女 | 1 | | 1 | python_01期 |
| 5 | 黄蓉 | 38 | 160.00 | 女 | 1 | | 2 | python_02期 |
| 6 | 凤姐 | 28 | 150.00 | 保密 | 2 | | 1 | python_01期 |
| 6 | 凤姐 | 28 | 150.00 | 保密 | 2 | | 2 | python_02期 |
| 7 | 王祖贤 | 18 | 172.00 | 女 | 1 | | 1 | python_01期 |
| 7 | 王祖贤 | 18 | 172.00 | 女 | 1 | | 2 | python_02期 |
| 8 | 周杰伦 | 36 | NULL | 男 | 1 | | 1 | python_01期 |
| 8 | 周杰伦 | 36 | NULL | 男 | 1 | | 2 | python_02期 |
| 9 | 陈坤 | 27 | 181.00 | 男 | 2 | | 1 | python_01期 |
| 9 | 陈坤 | 27 | 181.00 | 男 | 2 | | 2 | python_02期 |
| 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | | 1 | python_01期 |
| 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | | 2 | python_02期 |
| 11 | 金星 | 33 | 162.00 | 中性 | 3 | | 1 | python_01期 |
| 11 | 金星 | 33 | 162.00 | 中性 | 3 | | 2 | python_02期 |
| 12 | 静香 | 12 | 180.00 | 女 | 4 | | 1 | python_01期 |
| 12 | 静香 | 12 | 180.00 | 女 | 4 | | 2 | python_02期 |
| 13 | 郭靖 | 12 | 170.00 | 男 | 4 | | 1 | python_01期 |
| 13 | 郭靖 | 12 | 170.00 | 男 | 4 | | 2 | python_02期 |
| 14 | 周杰 | 34 | 176.00 | 女 | 5 | | 1 | python_01期 |
| 14 | 周杰 | 34 | 176.00 | 女 | 5 | | 2 | python_02期 |
+----+--------+------+--------+--------+--------+-----------+----+-------------+
28 rows in set (0.01 sec)
查询学生的名字和学生对应的班级名字
mysql>select * from students,classes where students.cls_id = classes.id;
+----+--------+------+--------+--------+--------+-----------+----+-------------+
| id | name | age | height | gender | cls_id | is_delete | id | name |
+----+--------+------+--------+--------+--------+-----------+----+-------------+
| 1 | 小明 | 18 | 180.00 | 女 | 1 | | 1 | python_01期 |
| 2 | 小月月 | 18 | 180.00 | 女 | 2 | | 2 | python_02期 |
| 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | | 1 | python_01期 |
| 4 | 刘德华 | 59 | 175.00 | 男 | 2 | | 2 | python_02期 |
| 5 | 黄蓉 | 38 | 160.00 | 女 | 1 | | 1 | python_01期 |
| 6 | 凤姐 | 28 | 150.00 | 保密 | 2 | | 2 | python_02期 |
| 7 | 王祖贤 | 18 | 172.00 | 女 | 1 | | 1 | python_01期 |
| 8 | 周杰伦 | 36 | NULL | 男 | 1 | | 1 | python_01期 |
| 9 | 陈坤 | 27 | 181.00 | 男 | 2 | | 2 | python_02期 |
| 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | | 2 | python_02期 |
+----+--------+------+--------+--------+--------+-----------+----+-------------+
10 rows in set (0.01 sec)
内连接查询(将满足连接条件的数据合成到一张表中)
mysql>select s.name, c.name from students as s inner join classes as c on s.cls_id = c.id;
+--------+-------------+
| name | name |
+--------+-------------+
| 小明 | python_01期 |
| 小月月 | python_02期 |
| 彭于晏 | python_01期 |
| 刘德华 | python_02期 |
| 黄蓉 | python_01期 |
| 凤姐 | python_02期 |
| 王祖贤 | python_01期 |
| 周杰伦 | python_01期 |
| 陈坤 | python_02期 |
| 刘亦菲 | python_02期 |
+--------+-------------+
10 rows in set (0.00 sec)
外链接(分主表和次表)
- 左外连接(常用)
左外连接时左边的表示主表,主表的数据全部显示(满足连接条件+不满足连接条件,不满足连接条件的以NULL填充)
mysql>select s.*, c.* from students as s left join classes as c on s.cls_id = c.id;
+----+--------+------+--------+--------+--------+-----------+------+-------------+
| id | name | age | height | gender | cls_id | is_delete | id | name |
+----+--------+------+--------+--------+--------+-----------+------+-------------+
| 1 | 小明 | 18 | 180.00 | 女 | 1 | | 1 | python_01期 |
| 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | | 1 | python_01期 |
| 5 | 黄蓉 | 38 | 160.00 | 女 | 1 | | 1 | python_01期 |
| 7 | 王祖贤 | 18 | 172.00 | 女 | 1 | | 1 | python_01期 |
| 8 | 周杰伦 | 36 | NULL | 男 | 1 | | 1 | python_01期 |
| 2 | 小月月 | 18 | 180.00 | 女 | 2 | | 2 | python_02期 |
| 4 | 刘德华 | 59 | 175.00 | 男 | 2 | | 2 | python_02期 |
| 6 | 凤姐 | 28 | 150.00 | 保密 | 2 | | 2 | python_02期 |
| 9 | 陈坤 | 27 | 181.00 | 男 | 2 | | 2 | python_02期 |
| 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | | 2 | python_02期 |
| 11 | 金星 | 33 | 162.00 | 中性 | 3 | | NULL | NULL |
| 12 | 静香 | 12 | 180.00 | 女 | 4 | | NULL | NULL |
| 13 | 郭靖 | 12 | 170.00 | 男 | 4 | | NULL | NULL |
| 14 | 周杰 | 34 | 176.00 | 女 | 5 | | NULL | NULL |
+----+--------+------+--------+--------+--------+-----------+------+-------------+
- 右外连接(不常用)
右外连接时右边的表示主表,主表的数据全部显示(满足连接条件+不满足连接条件,不满足连接条件的以NULL填充)
mysql>insert into classes values(6,"python8期");
Query OK, 1 row affected (0.01 sec)
mysql>select s.*, c.* from students as s right join classes as c on s.cls_id = c.id;
+------+--------+------+--------+--------+--------+-----------+----+-------------+
| id | name | age | height | gender | cls_id | is_delete | id | name |
+------+--------+------+--------+--------+--------+-----------+----+-------------+
| 1 | 小明 | 18 | 180.00 | 女 | 1 | | 1 | python_01期 |
| 2 | 小月月 | 18 | 180.00 | 女 | 2 | | 2 | python_02期 |
| 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | | 1 | python_01期 |
| 4 | 刘德华 | 59 | 175.00 | 男 | 2 | | 2 | python_02期 |
| 5 | 黄蓉 | 38 | 160.00 | 女 | 1 | | 1 | python_01期 |
| 6 | 凤姐 | 28 | 150.00 | 保密 | 2 | | 2 | python_02期 |
| 7 | 王祖贤 | 18 | 172.00 | 女 | 1 | | 1 | python_01期 |
| 8 | 周杰伦 | 36 | NULL | 男 | 1 | | 1 | python_01期 |
| 9 | 陈坤 | 27 | 181.00 | 男 | 2 | | 2 | python_02期 |
| 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | | 2 | python_02期 |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | 6 | python8期 |
+------+--------+------+--------+--------+--------+-----------+----+-------------+
11 rows in set (0.00 sec)
根据人的阅读习惯,一般使用左外连接,不希望看到左边部分出现NULL
查询学生所在的班级,按照班级进行排序
mysql>select c.name, s.name from students as s inner join classes as c on s.cls_id = c.id order by c.name;
+-------------+--------+
| name | name |
+-------------+--------+
| python_01期 | 小明 |
| python_01期 | 彭于晏 |
| python_01期 | 周杰伦 |
| python_01期 | 黄蓉 |
| python_01期 | 王祖贤 |
| python_02期 | 陈坤 |
| python_02期 | 凤姐 |
| python_02期 | 小月月 |
| python_02期 | 刘亦菲 |
| python_02期 | 刘德华 |
+-------------+--------+
10 rows in set (0.00 sec)
内连接的其他写法
mysql>select s.*, c.* from students as s inner join classes as c on s.cls_id = c.id;
mysql>select s.*, c.* from students as s join classes as c on s.cls_id = c.id;
mysql>select s.*, c.* from students as s cross join classes as c on s.cls_id = c.id;
外连接的其他写法
mysql>select s.*, c.* from students as s left outer join classes as c on s.cls_id = c.id;
mysql>select s.*, c.* from students as s right outer join classes as c on s.cls_id = c.id;
390
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
