Mysql黑马笔记
一、数据定义DDL
对数据库的常用操作
--查看所有表
show databases;
--创建数据库mydb1
create database mydb1；
--创建数据库mydb1，没有就创建，有则不报错
create database if not exists mydb1;
--删除数据库mydb1
drop database mydb1;
--删除数据库mydb1，有就删除，没有不报错
drop database if exists mydb1；
--更改数据库编码
alter database mydb1 character set utf8;
对表结构的常用操作
修改表结构
二、数据增删改查 DML
三、数据查询DQL
mysql> create table product(
-> pid int primary key auto_increment,
-> pname varchar(20) not null,
-> price double,
-> category_id varchar(20)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> desc product;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| pid | int(11) | NO | PRI | NULL | auto_increment |
| pname | varchar(20) | NO | | NULL | |
| price | double | YES | | NULL | |
| category_id | varchar(20) | YES | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
4 rows in set (0.03 sec)
mysql> insert into product values(NULL,'海尔洗衣机',5000,'c001');
Query OK, 1 row affected (0.00 sec)
mysql> insert into product values(NULL,'美的冰箱',3000,'c001');
Query OK, 1 row affected (0.00 sec)
mysql> insert into product values(NULL,'格力空调',5000,'c001');
Query OK, 1 row affected (0.01 sec)
mysql> insert into product values(NULL,'九阳电饭煲',5000,'c001');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into product values(NULL,'啄木鸟衬衣',300,'c002');
Query OK, 1 row affected (0.00 sec)
mysql> insert into product values(NULL,'恒源祥西裤',800,'c002');
Query OK, 1 row affected (0.00 sec)
mysql> insert into product values(NULL,'花花公子夹克',440,'c002');
Query OK, 1 row affected (0.00 sec)
mysql> insert into product values(NULL,'劲霸休闲裤',266,'c002');
Query OK, 1 row affected (0.00 sec)
mysql> insert into product values(NULL,'海澜之家卫衣',180,'c002');
Query OK, 1 row affected (0.00 sec)
mysql> insert into product values(NULL,'杰克琼斯运动裤',430,'c002');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into product values(NULL,'兰蔻面霜',300,'c003');
Query OK, 1 row affected (0.01 sec)
mysql> insert into product values(NULL,'雅诗兰黛精华水',200,'c003');
Query OK, 1 row affected (0.00 sec)
mysql> insert into product values(NULL,'香奈儿香水',350,'c003');
Query OK, 1 row affected (0.00 sec)
mysql> insert into product values(NULL,'SK-II神仙水',350,'c003');
Query OK, 1 row affected (0.00 sec)
mysql> insert into product values(NULL,'资生堂粉底液',180,'c003');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into product values(NULL,'老北京方便面',56,'c004');
Query OK, 1 row affected (0.00 sec)
mysql> insert into product values(NULL,'良品铺子海带丝',17,'c004');
Query OK, 1 row affected (0.00 sec)
mysql> insert into product values(NULL,'三只松鼠坚果',88,NULL);
Query OK, 1 row affected (0.01 sec)
mysql> select * from prompt;
ERROR 1146 (42S02): Table 'mydb1.prompt' doesn't exist
mysql> show tables;
+-----------------+
| Tables_in_mydb1 |
+-----------------+
| product |
+-----------------+
1 row in set (0.00 sec)
mysql> select * from product;
+-----+-----------------------+-------+-------------+
| pid | pname | price | category_id |
+-----+-----------------------+-------+-------------+
| 1 | 海尔洗衣机 | 5000 | c001 |
| 2 | 美的冰箱 | 3000 | c001 |
| 3 | 格力空调 | 5000 | c001 |
| 4 | 九阳电饭煲 | 5000 | c001 |
| 5 | 啄木鸟衬衣 | 300 | c002 |
| 6 | 恒源祥西裤 | 800 | c002 |
| 7 | 花花公子夹克 | 440 | c002 |
| 8 | 劲霸休闲裤 | 266 | c002 |
| 9 | 海澜之家卫衣 | 180 | c002 |
| 10 | 杰克琼斯运动裤 | 430 | c002 |
| 11 | 兰蔻面霜 | 300 | c003 |
| 12 | 雅诗兰黛精华水 | 200 | c003 |
| 13 | 香奈儿香水 | 350 | c003 |
| 14 | SK-II神仙水 | 350 | c003 |
| 15 | 资生堂粉底液 | 180 | c003 |
| 16 | 老北京方便面 | 56 | c004 |
| 17 | 良品铺子海带丝 | 17 | c004 |
| 18 | 三只松鼠坚果 | 88 | NULL |
+-----+-----------------------+-------+-------------+
18 rows in set (0.00 sec)
--简单查询
--1.查询所有商品
mysql> select * from product;
+-----+-----------------------+-------+-------------+
| pid | pname | price | category_id |
+-----+-----------------------+-------+-------------+
| 1 | 海尔洗衣机 | 5000 | c001 |
| 2 | 美的冰箱 | 3000 | c001 |
| 3 | 格力空调 | 5000 | c001 |
| 4 | 九阳电饭煲 | 5000 | c001 |
| 5 | 啄木鸟衬衣 | 300 | c002 |
| 6 | 恒源祥西裤 | 800 | c002 |
| 7 | 花花公子夹克 | 440 | c002 |
| 8 | 劲霸休闲裤 | 266 | c002 |
| 9 | 海澜之家卫衣 | 180 | c002 |
| 10 | 杰克琼斯运动裤 | 430 | c002 |
| 11 | 兰蔻面霜 | 300 | c003 |
| 12 | 雅诗兰黛精华水 | 200 | c003 |
| 13 | 香奈儿香水 | 350 | c003 |
| 14 | SK-II神仙水 | 350 | c003 |
| 15 | 资生堂粉底液 | 180 | c003 |
| 16 | 老北京方便面 | 56 | c004 |
| 17 | 良品铺子海带丝 | 17 | c004 |
| 18 | 三只松鼠坚果 | 88 | NULL |
+-----+-----------------------+-------+-------------+
18 rows in set (0.00 sec)
--2.查询商品名和商品价格
mysql> select pname,price from product;
+-----------------------+-------+
| pname | price |
+-----------------------+-------+
| 海尔洗衣机 | 5000 |
| 美的冰箱 | 3000 |
| 格力空调 | 5000 |
| 九阳电饭煲 | 5000 |
| 啄木鸟衬衣 | 300 |
| 恒源祥西裤 | 800 |
| 花花公子夹克 | 440 |
| 劲霸休闲裤 | 266 |
| 海澜之家卫衣 | 180 |
| 杰克琼斯运动裤 | 430 |
| 兰蔻面霜 | 300 |
| 雅诗兰黛精华水 | 200 |
| 香奈儿香水 | 350 |
| SK-II神仙水 | 350 |
| 资生堂粉底液 | 180 |
| 老北京方便面 | 56 |
| 良品铺子海带丝 | 17 |
| 三只松鼠坚果 | 88 |
+-----------------------+-------+
18 rows in set (0.00 sec)
--3.别名查询。使用关键字as（as可以省略）
--3.1表别名
--3.mysql> select pname as 商品名 from product;
+-----------------------+
| 商品名 |
+-----------------------+
| 海尔洗衣机 |
| 美的冰箱 |
| 格力空调 |
| 九阳电饭煲 |
| 啄木鸟衬衣 |
| 恒源祥西裤 |
| 花花公子夹克 |
| 劲霸休闲裤 |
| 海澜之家卫衣 |
| 杰克琼斯运动裤 |
| 兰蔻面霜 |
| 雅诗兰黛精华水 |
| 香奈儿香水 |
| SK-II神仙水 |
| 资生堂粉底液 |
| 老北京方便面 |
| 良品铺子海带丝 |
| 三只松鼠坚果 |
+-----------------------+
18 rows in set (0.00 sec)
--4.去掉重复值-distinct（商品价格去重）
mysql> select distinct(price) from product;
+-------+
| price |
+-------+
| 5000 |
| 3000 |
| 300 |
| 800 |
| 440 |
| 266 |
| 180 |
| 430 |
| 200 |
| 350 |
| 56 |
| 17 |
| 88 |
+-------+
13 rows in set (0.00 sec)
--5.查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示
mysql> select pname,price+10 as new_price from product;
+-----------------------+-----------+
| pname | new_price |
+-----------------------+-----------+
| 海尔洗衣机 | 5010 |
| 美的冰箱 | 3010 |
| 格力空调 | 5010 |
| 九阳电饭煲 | 5010 |
| 啄木鸟衬衣 | 310 |
| 恒源祥西裤 | 810 |
| 花花公子夹克 | 450 |
| 劲霸休闲裤 | 276 |
| 海澜之家卫衣 | 190 |
| 杰克琼斯运动裤 | 440 |
| 兰蔻面霜 | 310 |
| 雅诗兰黛精华水 | 210 |
| 香奈儿香水 | 360 |
| SK-II神仙水 | 360 |
| 资生堂粉底液 | 190 |
| 老北京方便面 | 66 |
| 良品铺子海带丝 | 27 |
| 三只松鼠坚果 | 98 |
+-----------------------+-----------+
18 rows in set (0.00 sec)
--运算符操作
--1.算数运算符
+ - x / %
--将每件商品的价格加10
mysql> select pname,price+10 as new_price from product;
+-----------------------+-----------+
| pname | new_price |
+-----------------------+-----------+
| 海尔洗衣机 | 5010 |
| 美的冰箱 | 3010 |
| 格力空调 | 5010 |
| 九阳电饭煲 | 5010 |
| 啄木鸟衬衣 | 310 |
| 恒源祥西裤 | 810 |
| 花花公子夹克 | 450 |
| 劲霸休闲裤 | 276 |
| 海澜之家卫衣 | 190 |
| 杰克琼斯运动裤 | 440 |
| 兰蔻面霜 | 310 |
| 雅诗兰黛精华水 | 210 |
| 香奈儿香水 | 360 |
| SK-II神仙水 | 360 |
| 资生堂粉底液 | 190 |
| 老北京方便面 | 66 |
| 良品铺子海带丝 | 27 |
| 三只松鼠坚果 | 98 |
+-----------------------+-----------+
18 rows in set (0.00 sec)
--将所有商品的价格上调10%
mysql> select pname,price*1.1 as new_price from product;
+-----------------------+--------------------+
| pname | new_price |
+-----------------------+--------------------+
| 海尔洗衣机 | 5500 |
| 美的冰箱 | 3300.0000000000005 |
| 格力空调 | 5500 |
| 九阳电饭煲 | 5500 |
| 啄木鸟衬衣 | 330 |
| 恒源祥西裤 | 880.0000000000001 |
| 花花公子夹克 | 484.00000000000006 |
| 劲霸休闲裤 | 292.6 |
| 海澜之家卫衣 | 198.00000000000003 |
| 杰克琼斯运动裤 | 473.00000000000006 |
| 兰蔻面霜 | 330 |
| 雅诗兰黛精华水 | 220.00000000000003 |
| 香奈儿香水 | 385.00000000000006 |
| SK-II神仙水 | 385.00000000000006 |
| 资生堂粉底液 | 198.00000000000003 |
| 老北京方便面 | 61.60000000000001 |
| 良品铺子海带丝 | 18.700000000000003 |
| 三只松鼠坚果 | 96.80000000000001 |
+-----------------------+--------------------+
18 rows in set (0.00 sec)
--2.比较运算符
--3.逻辑运算符
--查询商品名称为“海尔洗衣机”的商品所有信息
mysql> select * from product where pname='海尔洗衣机';
+-----+-----------------+-------+-------------+
| pid | pname | price | category_id |
+-----+-----------------+-------+-------------+
| 1 | 海尔洗衣机 | 5000 | c001 |
+-----+-----------------+-------+-------------+
1 row in set (0.10 sec)
--查询价格为800的商品
mysql> select pname,price from product where price=800;
+-----------------+-------+
| pname | price |
+-----------------+-------+
| 恒源祥西裤 | 800 |
+-----------------+-------+
1 row in set (0.00 sec)
--查询商品价格大于500元的所有商品信息
mysql> select * from product where price>500;
+-----+-----------------+-------+-------------+
| pid | pname | price | category_id |
+-----+-----------------+-------+-------------+
| 1 | 海尔洗衣机 | 5000 | c001 |
| 2 | 美的冰箱 | 3000 | c001 |
| 3 | 格力空调 | 5000 | c001 |
| 4 | 九阳电饭煲 | 5000 | c001 |
| 6 | 恒源祥西裤 | 800 | c002 |
+-----+-----------------+-------+-------------+
5 rows in set (0.00 sec)
--查询商品价格在200-1000之间的所有商品
mysql> select pname,price from product where price between 200 and 800;
+-----------------------+-------+
| pname | price |
+-----------------------+-------+
| 啄木鸟衬衣 | 300 |
| 恒源祥西裤 | 800 |
| 花花公子夹克 | 440 |
| 劲霸休闲裤 | 266 |
| 杰克琼斯运动裤 | 430 |
| 兰蔻面霜 | 300 |
| 雅诗兰黛精华水 | 200 |
| 香奈儿香水 | 350 |
| SK-II神仙水 | 350 |
+-----------------------+-------+
9 rows in set (0.00 sec)
mysql> select pname,price from product where price>=200 and price<=800;
+-----------------------+-------+
| pname | price |
+-----------------------+-------+
| 啄木鸟衬衣 | 300 |
| 恒源祥西裤 | 800 |
| 花花公子夹克 | 440 |
| 劲霸休闲裤 | 266 |
| 杰克琼斯运动裤 | 430 |
| 兰蔻面霜 | 300 |
| 雅诗兰黛精华水 | 200 |
| 香奈儿香水 | 350 |
| SK-II神仙水 | 350 |
+-----------------------+-------+
9 rows in set (0.00 sec)
mysql> select pname,price from product where price>=200 && price<=800;
+-----------------------+-------+
| pname | price |
+-----------------------+-------+
| 啄木鸟衬衣 | 300 |
| 恒源祥西裤 | 800 |
| 花花公子夹克 | 440 |
| 劲霸休闲裤 | 266 |
| 杰克琼斯运动裤 | 430 |
| 兰蔻面霜 | 300 |
| 雅诗兰黛精华水 | 200 |
| 香奈儿香水 | 350 |
| SK-II神仙水 | 350 |
+-----------------------+-------+
9 rows in set (0.00 sec)
--查询商品价格是200或800的所有商品
mysql> select pname,price from product where price=200 or price=800;
+-----------------------+-------+
| pname | price |
+-----------------------+-------+
| 恒源祥西裤 | 800 |
| 雅诗兰黛精华水 | 200 |
+-----------------------+-------+
2 rows in set (0.00 sec)
mysql> select pname,price from product where price=200 || price=800;
+-----------------------+-------+
| pname | price |
+-----------------------+-------+
| 恒源祥西裤 | 800 |
| 雅诗兰黛精华水 | 200 |
+-----------------------+-------+
2 rows in set (0.00 sec)
mysql> select pname,price from product where price in (200,800);
+-----------------------+-------+
| pname | price |
+-----------------------+-------+
| 恒源祥西裤 | 800 |
| 雅诗兰黛精华水 | 200 |
+-----------------------+-------+
2 rows in set (0.00 sec)
--查询含有‘裤’子的商品
mysql> select * from product where pname like '裤%';
Empty set (0.00 sec)
mysql> select * from product where pname like '%裤';
+-----+-----------------------+-------+-------------+
| pid | pname | price | category_id |
+-----+-----------------------+-------+-------------+
| 6 | 恒源祥西裤 | 800 | c002 |
| 8 | 劲霸休闲裤 | 266 | c002 |
| 10 | 杰克琼斯运动裤 | 430 | c002 |
+-----+-----------------------+-------+-------------+
3 rows in set (0.00 sec)
mysql> select * from product where pname like '%裤%';
+-----+-----------------------+-------+-------------+
| pid | pname | price | category_id |
+-----+-----------------------+-------+-------------+
| 6 | 恒源祥西裤 | 800 | c002 |
| 8 | 劲霸休闲裤 | 266 | c002 |
| 10 | 杰克琼斯运动裤 | 430 | c002 |
+-----+-----------------------+-------+-------------+
3 rows in set (0.00 sec)
--查询以‘海’开头的所有商品
mysql> select * from product where pname like '海%';
+-----+--------------------+-------+-------------+
| pid | pname | price | category_id |
+-----+--------------------+-------+-------------+
| 1 | 海尔洗衣机 | 5000 | c001 |
| 9 | 海澜之家卫衣 | 180 | c002 |
+-----+--------------------+-------+-------------+
2 rows in set (0.00 sec)
--查询第二个字为‘蔻’的所有商品
mysql> select * from product where pname like '_蔻%';
+-----+--------------+-------+-------------+
| pid | pname | price | category_id |
+-----+--------------+-------+-------------+
| 11 | 兰蔻面霜 | 300 | c003 |
+-----+--------------+-------+-------------+
1 row in set (0.00 sec)
--查询category_id为null分类的商品
mysql> select * from product where category_id is NULL;
+-----+--------------------+-------+-------------+
| pid | pname | price | category_id |
+-----+--------------------+-------+-------------+
| 18 | 三只松鼠坚果 | 88 | NULL |
+-----+--------------------+-------+-------------+
1 row in set (0.00 sec)
--查询category_id不为null分类的商品
mysql> select * from product where category_id is not NULL;
+-----+-----------------------+-------+-------------+
| pid | pname | price | category_id |
+-----+-----------------------+-------+-------------+
| 1 | 海尔洗衣机 | 5000 | c001 |
| 2 | 美的冰箱 | 3000 | c001 |
| 3 | 格力空调 | 5000 | c001 |
| 4 | 九阳电饭煲 | 5000 | c001 |
| 5 | 啄木鸟衬衣 | 300 | c002 |
| 6 | 恒源祥西裤 | 800 | c002 |
| 7 | 花花公子夹克 | 440 | c002 |
| 8 | 劲霸休闲裤 | 266 | c002 |
| 9 | 海澜之家卫衣 | 180 | c002 |
| 10 | 杰克琼斯运动裤 | 430 | c002 |
| 11 | 兰蔻面霜 | 300 | c003 |
| 12 | 雅诗兰黛精华水 | 200 | c003 |
| 13 | 香奈儿香水 | 350 | c003 |
| 14 | SK-II神仙水 | 350 | c003 |
| 15 | 资生堂粉底液 | 180 | c003 |
| 16 | 老北京方便面 | 56 | c004 |
| 17 | 良品铺子海带丝 | 17 | c004 |
+-----+-----------------------+-------+-------------+
17 rows in set (0.00 sec)
--使用least求最小值
mysql> select least(10,30);
+--------------+
| least(10,30) |
+--------------+
| 10 |
+--------------+
1 row in set (0.00 sec)
--使用greatest求最大值
mysql> select greatest(10,30);
+-----------------+
| greatest(10,30) |
+-----------------+
| 30 |
+-----------------+
1 row in set (0.00 sec)
--4.位运算符
--更改数据
mysql> update product set category_id='c004' where pid=18;
Query OK, 1 row affected (0.11 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from product;
+-----+-----------------------+-------+-------------+
| pid | pname | price | category_id |
+-----+-----------------------+-------+-------------+
| 1 | 海尔洗衣机 | 5000 | c001 |
| 2 | 美的冰箱 | 3000 | c001 |
| 3 | 格力空调 | 5000 | c001 |
| 4 | 九阳电饭煲 | 5000 | c001 |
| 5 | 啄木鸟衬衣 | 300 | c002 |
| 6 | 恒源祥西裤 | 800 | c002 |
| 7 | 花花公子夹克 | 440 | c002 |
| 8 | 劲霸休闲裤 | 266 | c002 |
| 9 | 海澜之家卫衣 | 180 | c002 |
| 10 | 杰克琼斯运动裤 | 430 | c002 |
| 11 | 兰蔻面霜 | 300 | c003 |
| 12 | 雅诗兰黛精华水 | 200 | c003 |
| 13 | 香奈儿香水 | 350 | c003 |
| 14 | SK-II神仙水 | 350 | c003 |
| 15 | 资生堂粉底液 | 180 | c003 |
| 16 | 老北京方便面 | 56 | c004 |
| 17 | 良品铺子海带丝 | 17 | c004 |
| 18 | 三只松鼠坚果 | 88 | c004 |
+-----+-----------------------+-------+-------------+
18 rows in set (0.00 sec)
--排序查询
--使用价格排序（降序）--order by 默认是升序
mysql> select * from product order by price desc;
+-----+-----------------------+-------+-------------+
| pid | pname | price | category_id |
+-----+-----------------------+-------+-------------+
| 3 | 格力空调 | 5000 | c001 |
| 4 | 九阳电饭煲 | 5000 | c001 |
| 1 | 海尔洗衣机 | 5000 | c001 |
| 2 | 美的冰箱 | 3000 | c001 |
| 6 | 恒源祥西裤 | 800 | c002 |
| 7 | 花花公子夹克 | 440 | c002 |
| 10 | 杰克琼斯运动裤 | 430 | c002 |
| 13 | 香奈儿香水 | 350 | c003 |
| 14 | SK-II神仙水 | 350 | c003 |
| 5 | 啄木鸟衬衣 | 300 | c002 |
| 11 | 兰蔻面霜 | 300 | c003 |
| 8 | 劲霸休闲裤 | 266 | c002 |
| 12 | 雅诗兰黛精华水 | 200 | c003 |
| 9 | 海澜之家卫衣 | 180 | c002 |
| 15 | 资生堂粉底液 | 180 | c003 |
| 18 | 三只松鼠坚果 | 88 | coo4 |
| 16 | 老北京方便面 | 56 | c004 |
| 17 | 良品铺子海带丝 | 17 | c004 |
+-----+-----------------------+-------+-------------+
18 rows in set (0.00 sec)
--在价格排序（降序）的基础上，以分类排序（降序）
mysql> select * from product order by category_id desc,price desc;
+-----+-----------------------+-------+-------------+
| pid | pname | price | category_id |
+-----+-----------------------+-------+-------------+
| 18 | 三只松鼠坚果 | 88 | c004 |
| 16 | 老北京方便面 | 56 | c004 |
| 17 | 良品铺子海带丝 | 17 | c004 |
| 14 | SK-II神仙水 | 350 | c003 |
| 13 | 香奈儿香水 | 350 | c003 |
| 11 | 兰蔻面霜 | 300 | c003 |
| 12 | 雅诗兰黛精华水 | 200 | c003 |
| 15 | 资生堂粉底液 | 180 | c003 |
| 6 | 恒源祥西裤 | 800 | c002 |
| 7 | 花花公子夹克 | 440 | c002 |
| 10 | 杰克琼斯运动裤 | 430 | c002 |
| 5 | 啄木鸟衬衣 | 300 | c002 |
| 8 | 劲霸休闲裤 | 266 | c002 |
| 9 | 海澜之家卫衣 | 180 | c002 |
| 1 | 海尔洗衣机 | 5000 | c001 |
| 4 | 九阳电饭煲 | 5000 | c001 |
| 3 | 格力空调 | 5000 | c001 |
| 2 | 美的冰箱 | 3000 | c001 |
+-----+-----------------------+-------+-------------+
18 rows in set (0.00 sec)
--显示商品的价格（去重），并排序
mysql> select distinct(price) from product order by price desc;
+-------+
| price |
+-------+
| 5000 |
| 3000 |
| 800 |
| 440 |
| 430 |
| 350 |
| 300 |
| 266 |
| 200 |
| 180 |
| 88 |
| 56 |
| 17 |
+-------+
13 rows in set (0.00 sec)
--聚合查询
--查询商品的总条数
mysql> select count(pid) as 商品总数 from product;
+--------------+
| 商品总数 |
+--------------+
| 18 |
+--------------+
1 row in set (0.00 sec)
--查询价格大于200商品的总条数
mysql> select count(price) from product where price>200;
+--------------+
| count(price) |
+--------------+
| 12 |
+--------------+
1 row in set (0.00 sec)
--查询分类为‘c001’的所有商品的总和
mysql> select count(category_id) from product where category_id='c001';
+--------------------+
| count(category_id) |
+--------------------+
| 4 |
+--------------------+
1 row in set (0.00 sec)
--查询商品的最大价格
mysql> select max(price) from product;;
+------------+
| max(price) |
+------------+
| 5000 |
+------------+
1 row in set (0.00 sec)
--查询商品最小价格
mysql> select min(price) from product;;
+------------+
| min(price) |
+------------+
| 17 |
+------------+
1 row in set (0.00 sec)
--查询分类为‘c002‘所有商品的平均价格
mysql> select avg(price) from product where category_id='c002';
+-------------------+
| avg(price) |
+-------------------+
| 402.6666666666667 |
+-------------------+
1 row in set (0.00 sec)
--null在聚合查询中查询行数省略不计，但是如果只查平均数，这里null会相当于一行，且为0
--分组查询
--统计各个category_id商品的个数
mysql> select count(pid),category_id from product group by category_id;
+------------+-------------+
| count(pid) | category_id |
+------------+-------------+
| 4 | c001 |
| 6 | c002 |
| 5 | c003 |
| 3 | c004 |
+------------+-------------+
4 rows in set (0.00 sec)
--group by分组后再条件查询用having
--统计各个category_id商品的个数，且只显示大于4的信息
mysql> select count(pid),category_id from product group by category_id having count(pid)>4;
+------------+-------------+
| count(pid) | category_id |
+------------+-------------+
| 6 | c002 |
| 5 | c003 |
+------------+-------------+
2 rows in set (0.00 sec)
--分页查询-limit
--查询product表的前5条记录
mysql> select * from product limit 5;
+-----+-----------------+-------+-------------+
| pid | pname | price | category_id |
+-----+-----------------+-------+-------------+
| 1 | 海尔洗衣机 | 5000 | c001 |
| 2 | 美的冰箱 | 3000 | c001 |
| 3 | 格力空调 | 5000 | c001 |
| 4 | 九阳电饭煲 | 5000 | c001 |
| 5 | 啄木鸟衬衣 | 300 | c002 |
+-----+-----------------+-------+-------------+
5 rows in set (0.00 sec)
--从第4条开始显示，显示5条
mysql> select * from product limit 4,5;
+-----+--------------------+-------+-------------+
| pid | pname | price | category_id |
+-----+--------------------+-------+-------------+
| 5 | 啄木鸟衬衣 | 300 | c002 |
| 6 | 恒源祥西裤 | 800 | c002 |
| 7 | 花花公子夹克 | 440 | c002 |
| 8 | 劲霸休闲裤 | 266 | c002 |
| 9 | 海澜之家卫衣 | 180 | c002 |
+-----+--------------------+-------+-------------+
5 rows in set (0.01 sec)
--insert into select语句
--数据导入
mysql> create table student(
-> id int,
-> name varchar(20),
-> gender varchar(20),
-> chinese int,
-> english int,
-> math int
-> );
Query OK, 0 rows affected (0.32 sec)
mysql> insert into student (id, name, gender, chinese, english, math) VALUES (1,'张明','男',89,78,90);
Query OK, 1 row affected (0.00 sec)
mysql> insert into student (id, name, gender, chinese, english, math) VALUES (2,'李进','男',67,53,95);
Query OK, 1 row affected (0.00 sec)
mysql> insert into student (id, name, gender, chinese, english, math) VALUES (3,'王五','女',87,78,77);
Query OK, 1 row affected (0.00 sec)
mysql> insert into student (id, name, gender, chinese, english, math) VALUES (4,'李一','女',88,98,92);
Query OK, 1 row affected (0.01 sec)
mysql> insert into student (id, name, gender, chinese, english, math) VALUES (5,'李财','男',82,84,67);
Query OK, 1 row affected (0.01 sec)
mysql> insert into student (id, name, gender, chinese, english, math) VALUES (6,'张宝','男',55,85,45);
Query OK, 1 row affected (0.01 sec)
mysql> insert into student (id, name, gender, chinese, english, math) VALUES (7,'黄蓉','女',75,65,30);
Query OK, 1 row affected (0.01 sec)
mysql> insert into student (id, name, gender, chinese, english, math) VALUES (7,'黄蓉','女',75,65,30);
Query OK, 1 row affected (0.00 sec)
mysql> desc studnt;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| gender | varchar(20) | YES | | NULL | |
| chinese | int(11) | YES | | NULL | |
| english | int(11) | YES | | NULL | |
| math | int(11) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> select * from student;
+------+--------+--------+---------+---------+------+
| id | name | gender | chinese | english | math |
+------+--------+--------+---------+---------+------+
| 1 | 张明 | 男 | 89 | 78 | 90 |
| 2 | 李进 | 男 | 67 | 53 | 95 |
| 3 | 王五 | 女 | 87 | 78 | 77 |
| 4 | 李一 | 女 | 88 | 98 | 92 |
| 5 | 李财 | 男 | 82 | 84 | 67 |
| 6 | 张宝 | 男 | 55 | 85 | 45 |
| 7 | 黄蓉 | 女 | 75 | 65 | 30 |
| 7 | 黄蓉 | 女 | 75 | 65 | 30 |
+------+--------+--------+---------+---------+------+
8 rows in set (0.00 sec)
--练习
--查询表中的所有学生信息
mysql> select * from student;
+------+--------+--------+---------+---------+------+
| id | name | gender | chinese | english | math |
+------+--------+--------+---------+---------+------+
| 1 | 张明 | 男 | 89 | 78 | 90 |
| 2 | 李进 | 男 | 67 | 53 | 95 |
| 3 | 王五 | 女 | 87 | 78 | 77 |
| 4 | 李一 | 女 | 88 | 98 | 92 |
| 5 | 李财 | 男 | 82 | 84 | 67 |
| 6 | 张宝 | 男 | 55 | 85 | 45 |
| 7 | 黄蓉 | 女 | 75 | 65 | 30 |
| 7 | 黄蓉 | 女 | 75 | 65 | 30 |
+------+--------+--------+---------+---------+------+
8 rows in set (0.00 sec)
--过滤表中重复数据
mysql> select distinct(id),name,gender,chinese english,math from student;
+------+--------+--------+---------+------+
| id | name | gender | english | math |
+------+--------+--------+---------+------+
| 1 | 张明 | 男 | 89 | 90 |
| 2 | 李进 | 男 | 67 | 95 |
| 3 | 王五 | 女 | 87 | 77 |
| 4 | 李一 | 女 | 88 | 92 |
| 5 | 李财 | 男 | 82 | 67 |
| 6 | 张宝 | 男 | 55 | 45 |
| 7 | 黄蓉 | 女 | 75 | 30 |
+------+--------+--------+---------+------+
7 rows in set (0.00 sec)
--统计每个学生的总分
mysql> select id,name,gender,(chinese+english+math) from student;
+------+--------+--------+------------------------+
| id | name | gender | (chinese+english+math) |
+------+--------+--------+------------------------+
| 1 | 张明 | 男 | 257 |
| 2 | 李进 | 男 | 215 |
| 3 | 王五 | 女 | 242 |
| 4 | 李一 | 女 | 278 |
| 5 | 李财 | 男 | 233 |
| 6 | 张宝 | 男 | 185 |
| 7 | 黄蓉 | 女 | 170 |
| 7 | 黄蓉 | 女 | 170 |
+------+--------+--------+------------------------+
8 rows in set (0.00 sec)
--在所有学生总分数上加10分特长分
mysql> select id,name,gender,(chinese+english+math+10) from student;
+------+--------+--------+---------------------------+
| id | name | gender | (chinese+english+math+10) |
+------+--------+--------+---------------------------+
| 1 | 张明 | 男 | 267 |
| 2 | 李进 | 男 | 225 |
| 3 | 王五 | 女 | 252 |
| 4 | 李一 | 女 | 288 |
| 5 | 李财 | 男 | 243 |
| 6 | 张宝 | 男 | 195 |
| 7 | 黄蓉 | 女 | 180 |
| 7 | 黄蓉 | 女 | 180 |
+------+--------+--------+---------------------------+
--使用别名表示学生分数
mysql> select id,name,gender,(chinese+english+math+10) as count from student;
+------+--------+--------+-------+
| id | name | gender | count |
+------+--------+--------+-------+
| 1 | 张明 | 男 | 267 |
| 2 | 李进 | 男 | 225 |
| 3 | 王五 | 女 | 252 |
| 4 | 李一 | 女 | 288 |
| 5 | 李财 | 男 | 243 |
| 6 | 张宝 | 男 | 195 |
| 7 | 黄蓉 | 女 | 180 |
| 7 | 黄蓉 | 女 | 180 |
+------+--------+--------+-------+
8 rows in set (0.00 sec)
--查询英语成绩大于90分的同学
mysql> select * from student where english > 90;
+------+--------+--------+---------+---------+------+
| id | name | gender | chinese | english | math |
+------+--------+--------+---------+---------+------+
| 4 | 李一 | 女 | 88 | 98 | 92 |
+------+--------+--------+---------+---------+------+
1 row in set (0.00 sec)
--查询总分大于200分的所有同学
mysql> select id,name,gender,(chinese+english+math) as count from student
where (chinese+english+math) > 200;
+------+--------+--------+-------+
| id | name | gender | count |
+------+--------+--------+-------+
| 1 | 张明 | 男 | 257 |
| 2 | 李进 | 男 | 215 |
| 3 | 王五 | 女 | 242 |
| 4 | 李一 | 女 | 278 |
| 5 | 李财 | 男 | 233 |
+------+--------+--------+-------+
5 rows in set (0.00 sec)
--查询英语分数在80-90之间的同学
mysql> select * from student where english between 80 and 90;
+------+--------+--------+---------+---------+------+
| id | name | gender | chinese | english | math |
+------+--------+--------+---------+---------+------+
| 5 | 李财 | 男 | 82 | 84 | 67 |
| 6 | 张宝 | 男 | 55 | 85 | 45 |
+------+--------+--------+---------+---------+------+
2 rows in set (0.00 sec)
mysql> select * from student where english > 80 && english < 90;
+------+--------+--------+---------+---------+------+
| id | name | gender | chinese | english | math |
+------+--------+--------+---------+---------+------+
| 5 | 李财 | 男 | 82 | 84 | 67 |
| 6 | 张宝 | 男 | 55 | 85 | 45 |
+------+--------+--------+---------+---------+------+
2 rows in set (0.00 sec)
mysql> select * from student where english > 80 and english < 90;
+------+--------+--------+---------+---------+------+
| id | name | gender | chinese | english | math |
+------+--------+--------+---------+---------+------+
| 5 | 李财 | 男 | 82 | 84 | 67 |
| 6 | 张宝 | 男 | 55 | 85 | 45 |
+------+--------+--------+---------+---------+------+
2 rows in set (0.00 sec)
--查询英语分数不在80-90之间的同学
mysql> select * from student where english not between 80 and 90;
+------+--------+--------+---------+---------+------+
| id | name | gender | chinese | english | math |
+------+--------+--------+---------+---------+------+
| 1 | 张明 | 男 | 89 | 78 | 90 |
| 2 | 李进 | 男 | 67 | 53 | 95 |
| 3 | 王五 | 女 | 87 | 78 | 77 |
| 4 | 李一 | 女 | 88 | 98 | 92 |
| 7 | 黄蓉 | 女 | 75 | 65 | 30 |
| 7 | 黄蓉 | 女 | 75 | 65 | 30 |
+------+--------+--------+---------+---------+------+
6 rows in set (0.00 sec)
mysql> select * from student where not (english > 80 && english < 90);
+------+--------+--------+---------+---------+------+
| id | name | gender | chinese | english | math |
+------+--------+--------+---------+---------+------+
| 1 | 张明 | 男 | 89 | 78 | 90 |
| 2 | 李进 | 男 | 67 | 53 | 95 |
| 3 | 王五 | 女 | 87 | 78 | 77 |
| 4 | 李一 | 女 | 88 | 98 | 92 |
| 7 | 黄蓉 | 女 | 75 | 65 | 30 |
| 7 | 黄蓉 | 女 | 75 | 65 | 30 |
+------+--------+--------+---------+---------+------+
6 rows in set (0.00 sec)
mysql> select * from student where not (english > 80 and english < 90);
+------+--------+--------+---------+---------+------+
| id | name | gender | chinese | english | math |
+------+--------+--------+---------+---------+------+
| 1 | 张明 | 男 | 89 | 78 | 90 |
| 2 | 李进 | 男 | 67 | 53 | 95 |
| 3 | 王五 | 女 | 87 | 78 | 77 |
| 4 | 李一 | 女 | 88 | 98 | 92 |
| 7 | 黄蓉 | 女 | 75 | 65 | 30 |
| 7 | 黄蓉 | 女 | 75 | 65 | 30 |
+------+--------+--------+---------+---------+------+
6 rows in set (0.00 sec)
--查询数学分数为89,90,91的同学
mysql> select * from student where math in (89,90,91);
+------+--------+--------+---------+---------+------+
| id | name | gender | chinese | english | math |
+------+--------+--------+---------+---------+------+
| 1 | 张明 | 男 | 89 | 78 | 90 |
+------+--------+--------+---------+---------+------+
1 row in set (0.00 sec)
mysql> select * from student where math=89 || math=90 || math=91;
+------+--------+--------+---------+---------+------+
| id | name | gender | chinese | english | math |
+------+--------+--------+---------+---------+------+
| 1 | 张明 | 男 | 89 | 78 | 90 |
+------+--------+--------+---------+---------+------+
1 row in set (0.00 sec)
mysql> select * from student where math=89 or math=90 or math=91;
+------+--------+--------+---------+---------+------+
| id | name | gender | chinese | english | math |
+------+--------+--------+---------+---------+------+
| 1 | 张明 | 男 | 89 | 78 | 90 |
+------+--------+--------+---------+---------+------+
1 row in set (0.00 sec)
--查询所有姓李的学生的英语成绩
mysql> select name,english from student where name like '李%';
+--------+---------+
| name | english |
+--------+---------+
| 李进 | 53 |
| 李一 | 98 |
| 李财 | 84 |
+--------+---------+
3 rows in set (0.00 sec)
--mysql> select * from student where math>80 and chinese>80;
+------+--------+--------+---------+---------+------+
| id | name | gender | chinese | english | math |
+------+--------+--------+---------+---------+------+
| 1 | 张明 | 男 | 89 | 78 | 90 |
| 4 | 李一 | 女 | 88 | 98 | 92 |
+------+--------+--------+---------+---------+------+
2 rows in set (0.00 sec)
mysql> select * from student where math>80 && chinese>80;
+------+--------+--------+---------+---------+------+
| id | name | gender | chinese | english | math |
+------+--------+--------+---------+---------+------+
| 1 | 张明 | 男 | 89 | 78 | 90 |
| 4 | 李一 | 女 | 88 | 98 | 92 |
+------+--------+--------+---------+---------+------+
2 rows in set (0.00 sec)
--查询英语大于80或者总分大于200的同学
mysql> select * from student where english>80 or (chinese+english+math)>200;
+------+--------+--------+---------+---------+------+
| id | name | gender | chinese | english | math |
+------+--------+--------+---------+---------+------+
| 1 | 张明 | 男 | 89 | 78 | 90 |
| 2 | 李进 | 男 | 67 | 53 | 95 |
| 3 | 王五 | 女 | 87 | 78 | 77 |
| 4 | 李一 | 女 | 88 | 98 | 92 |
| 5 | 李财 | 男 | 82 | 84 | 67 |
| 6 | 张宝 | 男 | 55 | 85 | 45 |
+------+--------+--------+---------+---------+------+
6 rows in set (0.00 sec)
mysql> select * from student where english>80 || (chinese+english+math)>200;
+------+--------+--------+---------+---------+------+
| id | name | gender | chinese | english | math |
+------+--------+--------+---------+---------+------+
| 1 | 张明 | 男 | 89 | 78 | 90 |
| 2 | 李进 | 男 | 67 | 53 | 95 |
| 3 | 王五 | 女 | 87 | 78 | 77 |
| 4 | 李一 | 女 | 88 | 98 | 92 |
| 5 | 李财 | 男 | 82 | 84 | 67 |
| 6 | 张宝 | 男 | 55 | 85 | 45 |
+------+--------+--------+---------+---------+------+
6 rows in set (0.00 sec)
--mysql> select * from student order by math desc;
+------+--------+--------+---------+---------+------+
| id | name | gender | chinese | english | math |
+------+--------+--------+---------+---------+------+
| 2 | 李进 | 男 | 67 | 53 | 95 |
| 4 | 李一 | 女 | 88 | 98 | 92 |
| 1 | 张明 | 男 | 89 | 78 | 90 |
| 3 | 王五 | 女 | 87 | 78 | 77 |
| 5 | 李财 | 男 | 82 | 84 | 67 |
| 6 | 张宝 | 男 | 55 | 85 | 45 |
| 7 | 黄蓉 | 女 | 75 | 65 | 30 |
| 7 | 黄蓉 | 女 | 75 | 65 | 30 |
+------+--------+--------+---------+---------+------+
8 rows in set (0.00 sec)
--对总分排序后输出，然后再按从高到低的顺序输出
mysql> select id,name,gender,(chinese+english+math) as count from student order by (chinese+english+math) desc;
+------+--------+--------+-------+
| id | name | gender | count |
+------+--------+--------+-------+
| 4 | 李一 | 女 | 278 |
| 1 | 张明 | 男 | 257 |
| 3 | 王五 | 女 | 242 |
| 5 | 李财 | 男 | 233 |
| 2 | 李进 | 男 | 215 |
| 6 | 张宝 | 男 | 185 |
| 7 | 黄蓉 | 女 | 170 |
| 7 | 黄蓉 | 女 | 170 |
+------+--------+--------+-------+
8 rows in set (0.00 sec)
--对姓李的学生总成绩排序输出
--查询男生和女士分别有多少人，并将人数降序排序输出
--多表查询
--数据导入
mysql> create table if not exists dept3(
-> deptno varchar(20)primary key,
-> name varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql> create table if not exists emp3(
-> eid varchar(20) primary key,
-> ename varchar(20),
-> age int,
-> dept_id varchar(20));
insert into dept3 values ('1001','研发部'),
('1002','销售部'),
('1003','财务部'),
('1004','人事部');
insert into emp3 values ('1','乔峰',20,'1001'),
('2','段誉',21,'1001'),
('3','虚竹',23,'1001'),
('4','阿紫',18,'1001'),
('5','扫地僧',85,'1002'),
('6','李秋水',33,'1002'),
('7','鸠摩智',50,'1002'),
('8','天山童姥',60,'1003'),
('9','慕容博',58,'1003'),
('10','丁春秋',71,'1005');
--交叉连接查询（笛卡尔积）
--格式：select * from 表1，表2;
mysql> select * from dept3,emp3;
+--------+-----------+-----+--------------+------+---------+
| deptno | name | eid | ename | age | dept_id |
+--------+-----------+-----+--------------+------+---------+
| 1001 | 研发部 | 1 | 乔峰 | 20 | 1001 |
| 1002 | 销售部 | 1 | 乔峰 | 20 | 1001 |
| 1003 | 财务部 | 1 | 乔峰 | 20 | 1001 |
| 1004 | 人事部 | 1 | 乔峰 | 20 | 1001 |
| 1001 | 研发部 | 10 | 丁春秋 | 71 | 1005 |
| 1002 | 销售部 | 10 | 丁春秋 | 71 | 1005 |
| 1003 | 财务部 | 10 | 丁春秋 | 71 | 1005 |
| 1004 | 人事部 | 10 | 丁春秋 | 71 | 1005 |
| 1001 | 研发部 | 2 | 段誉 | 21 | 1001 |
| 1002 | 销售部 | 2 | 段誉 | 21 | 1001 |
| 1003 | 财务部 | 2 | 段誉 | 21 | 1001 |
| 1004 | 人事部 | 2 | 段誉 | 21 | 1001 |
| 1001 | 研发部 | 3 | 虚竹 | 23 | 1001 |
| 1002 | 销售部 | 3 | 虚竹 | 23 | 1001 |
| 1003 | 财务部 | 3 | 虚竹 | 23 | 1001 |
| 1004 | 人事部 | 3 | 虚竹 | 23 | 1001 |
| 1001 | 研发部 | 4 | 阿紫 | 18 | 1001 |
| 1002 | 销售部 | 4 | 阿紫 | 18 | 1001 |
| 1003 | 财务部 | 4 | 阿紫 | 18 | 1001 |
| 1004 | 人事部 | 4 | 阿紫 | 18 | 1001 |
| 1001 | 研发部 | 5 | 扫地僧 | 85 | 1002 |
| 1002 | 销售部 | 5 | 扫地僧 | 85 | 1002 |
| 1003 | 财务部 | 5 | 扫地僧 | 85 | 1002 |
| 1004 | 人事部 | 5 | 扫地僧 | 85 | 1002 |
| 1001 | 研发部 | 6 | 李秋水 | 33 | 1002 |
| 1002 | 销售部 | 6 | 李秋水 | 33 | 1002 |
| 1003 | 财务部 | 6 | 李秋水 | 33 | 1002 |
| 1004 | 人事部 | 6 | 李秋水 | 33 | 1002 |
| 1001 | 研发部 | 7 | 鸠摩智 | 50 | 1002 |
| 1002 | 销售部 | 7 | 鸠摩智 | 50 | 1002 |
| 1003 | 财务部 | 7 | 鸠摩智 | 50 | 1002 |
| 1004 | 人事部 | 7 | 鸠摩智 | 50 | 1002 |
| 1001 | 研发部 | 8 | 天山童姥 | 60 | 1003 |
| 1002 | 销售部 | 8 | 天山童姥 | 60 | 1003 |
| 1003 | 财务部 | 8 | 天山童姥 | 60 | 1003 |
| 1004 | 人事部 | 8 | 天山童姥 | 60 | 1003 |
| 1001 | 研发部 | 9 | 慕容博 | 58 | 1003 |
| 1002 | 销售部 | 9 | 慕容博 | 58 | 1003 |
| 1003 | 财务部 | 9 | 慕容博 | 58 | 1003 |
| 1004 | 人事部 | 9 | 慕容博 | 58 | 1003 |
+--------+-----------+-----+--------------+------+---------+
40 rows in set (0.00 sec)
--内连接查询 （交集）
--格式：隐式内连接(92标准)：select * from 表1,表2 where 条件；
显示内连接(99标准)：select * from 表1 [inner] join 表2 条件;（inner可省略）
--查询每个部门的所属员工
mysql> select * from emp3,dept3 where emp3.dept_id=dept3.deptno;
+-----+--------------+------+---------+--------+-----------+
| eid | ename | age | dept_id | deptno | name |
+-----+--------------+------+---------+--------+-----------+
| 1 | 乔峰 | 20 | 1001 | 1001 | 研发部 |
| 2 | 段誉 | 21 | 1001 | 1001 | 研发部 |
| 3 | 虚竹 | 23 | 1001 | 1001 | 研发部 |
| 4 | 阿紫 | 18 | 1001 | 1001 | 研发部 |
| 5 | 扫地僧 | 85 | 1002 | 1002 | 销售部 |
| 6 | 李秋水 | 33 | 1002 | 1002 | 销售部 |
| 7 | 鸠摩智 | 50 | 1002 | 1002 | 销售部 |
| 8 | 天山童姥 | 60 | 1003 | 1003 | 财务部 |
| 9 | 慕容博 | 58 | 1003 | 1003 | 财务部 |
+-----+--------------+------+---------+--------+-----------+
9 rows in set (0.44 sec)
mysql> select * from emp3 join dept3 on emp3.dept_id=dept3.deptno;
+-----+--------------+------+---------+--------+-----------+
| eid | ename | age | dept_id | deptno | name |
+-----+--------------+------+---------+--------+-----------+
| 1 | 乔峰 | 20 | 1001 | 1001 | 研发部 |
| 2 | 段誉 | 21 | 1001 | 1001 | 研发部 |
| 3 | 虚竹 | 23 | 1001 | 1001 | 研发部 |
| 4 | 阿紫 | 18 | 1001 | 1001 | 研发部 |
| 5 | 扫地僧 | 85 | 1002 | 1002 | 销售部 |
| 6 | 李秋水 | 33 | 1002 | 1002 | 销售部 |
| 7 | 鸠摩智 | 50 | 1002 | 1002 | 销售部 |
| 8 | 天山童姥 | 60 | 1003 | 1003 | 财务部 |
| 9 | 慕容博 | 58 | 1003 | 1003 | 财务部 |
+-----+--------------+------+---------+--------+-----------+
9 rows in set (0.00 sec)
--查询研发部和销售部的所属员工
mysql> select * from emp3 a,dept3 b where a.dept_id=b.deptno and b.name in('研发部','销售部');
+-----+-----------+------+---------+--------+-----------+
| eid | ename | age | dept_id | deptno | name |
+-----+-----------+------+---------+--------+-----------+
| 1 | 乔峰 | 20 | 1001 | 1001 | 研发部 |
| 2 | 段誉 | 21 | 1001 | 1001 | 研发部 |
| 3 | 虚竹 | 23 | 1001 | 1001 | 研发部 |
| 4 | 阿紫 | 18 | 1001 | 1001 | 研发部 |
| 5 | 扫地僧 | 85 | 1002 | 1002 | 销售部 |
| 6 | 李秋水 | 33 | 1002 | 1002 | 销售部 |
| 7 | 鸠摩智 | 50 | 1002 | 1002 | 销售部 |
+-----+-----------+------+---------+--------+-----------+
7 rows in set (0.00 sec)
mysql> select * from emp3 a join dept3 b on a.dept_id=b.deptno and b.name in('研发部','销售部');
+-----+-----------+------+---------+--------+-----------+
| eid | ename | age | dept_id | deptno | name |
+-----+-----------+------+---------+--------+-----------+
| 1 | 乔峰 | 20 | 1001 | 1001 | 研发部 |
| 2 | 段誉 | 21 | 1001 | 1001 | 研发部 |
| 3 | 虚竹 | 23 | 1001 | 1001 | 研发部 |
| 4 | 阿紫 | 18 | 1001 | 1001 | 研发部 |
| 5 | 扫地僧 | 85 | 1002 | 1002 | 销售部 |
| 6 | 李秋水 | 33 | 1002 | 1002 | 销售部 |
| 7 | 鸠摩智 | 50 | 1002 | 1002 | 销售部 |
+-----+-----------+------+---------+--------+-----------+
7 rows in set (0.00 sec)
--查询每个部门的员工数，并升序排序
mysql> select name,count(dept_id) c from emp3 a,dept3 b where a.dept_id=b.deptno group by name order by c;
+-----------+---+
| name | c |
+-----------+---+
| 财务部 | 2 |
| 销售部 | 3 |
| 研发部 | 4 |
+-----------+---+
3 rows in set (0.00 sec)
mysql> select name,count(dept_id) c from emp3 a join dept3 b on a.dept_id=b.deptno group by name order by c;
+-----------+---+
| name | c |
+-----------+---+
| 财务部 | 2 |
| 销售部 | 3 |
| 研发部 | 4 |
+-----------+---+
3 rows in set (0.00 sec)
--查询人数大于大于2的部门，并按人数降序排序
mysql> select name,count(dept_id) c from emp3 a,dept3 b where a.dept_id=b.deptno group by name having c>2 order by c desc;
+-----------+---+
| name | c |
+-----------+---+
| 研发部 | 4 |
| 销售部 | 3 |
+-----------+---+
2 rows in set (0.00 sec)
mysql> select name,count(dept_id) c from emp3 a join dept3 b on a.dept_id=b.deptno group by name having c>2 order by c desc;
+-----------+---+
| name | c |
+-----------+---+
| 研发部 | 4 |
| 销售部 | 3 |
+-----------+---+
2 rows in set (0.00 sec)
--外连接查询
--格式 outer可省略
--左外连接：select * from 表1 left [outer] join 表2 on 条件;
--右外连接：select * from 表1 right [outer] join 表2 on 条件;
--满外连接：select * from 表1 full [outer] join 表2 on 条件;
--查询哪些部门有员工
mysql> select * from emp3 a right join dept3 b on a.dept_id=b.deptno;
+------+--------------+------+---------+--------+-----------+
| eid | ename | age | dept_id | deptno | name |
+------+--------------+------+---------+--------+-----------+
| 1 | 乔峰 | 20 | 1001 | 1001 | 研发部 |
| 2 | 段誉 | 21 | 1001 | 1001 | 研发部 |
| 3 | 虚竹 | 23 | 1001 | 1001 | 研发部 |
| 4 | 阿紫 | 18 | 1001 | 1001 | 研发部 |
| 5 | 扫地僧 | 85 | 1002 | 1002 | 销售部 |
| 6 | 李秋水 | 33 | 1002 | 1002 | 销售部 |
| 7 | 鸠摩智 | 50 | 1002 | 1002 | 销售部 |
| 8 | 天山童姥 | 60 | 1003 | 1003 | 财务部 |
| 9 | 慕容博 | 58 | 1003 | 1003 | 财务部 |
| NULL | NULL | NULL | NULL | 1004 | 人事部 |
+------+--------------+------+---------+--------+-----------+
10 rows in set (0.00 sec)
--查询员工有对应的部门，哪些没有
mysql> select * from emp3 a left join dept3 b on a.dept_id=b.deptno;
+-----+--------------+------+---------+--------+-----------+
| eid | ename | age | dept_id | deptno | name |
+-----+--------------+------+---------+--------+-----------+
| 1 | 乔峰 | 20 | 1001 | 1001 | 研发部 |
| 2 | 段誉 | 21 | 1001 | 1001 | 研发部 |
| 3 | 虚竹 | 23 | 1001 | 1001 | 研发部 |
| 4 | 阿紫 | 18 | 1001 | 1001 | 研发部 |
| 5 | 扫地僧 | 85 | 1002 | 1002 | 销售部 |
| 6 | 李秋水 | 33 | 1002 | 1002 | 销售部 |
| 7 | 鸠摩智 | 50 | 1002 | 1002 | 销售部 |
| 8 | 天山童姥 | 60 | 1003 | 1003 | 财务部 |
| 9 | 慕容博 | 58 | 1003 | 1003 | 财务部 |
| 10 | 丁春秋 | 71 | 1005 | NULL | NULL |
+-----+--------------+------+---------+--------+-----------+
10 rows in set (0.01 sec)
--使用union关键字实现左外连接和右外连接的并集
--union是将左右外连接相连接再去重
mysql> select * from emp3 a left join dept3 b on a.dept_id=b.deptno
-> union
-> select * from emp3 a right join dept3 b on a.dept_id=b.deptno
-> ;
+------+--------------+------+---------+--------+-----------+
| eid | ename | age | dept_id | deptno | name |
+------+--------------+------+---------+--------+-----------+
| 1 | 乔峰 | 20 | 1001 | 1001 | 研发部 |
| 2 | 段誉 | 21 | 1001 | 1001 | 研发部 |
| 3 | 虚竹 | 23 | 1001 | 1001 | 研发部 |
| 4 | 阿紫 | 18 | 1001 | 1001 | 研发部 |
| 5 | 扫地僧 | 85 | 1002 | 1002 | 销售部 |
| 6 | 李秋水 | 33 | 1002 | 1002 | 销售部 |
| 7 | 鸠摩智 | 50 | 1002 | 1002 | 销售部 |
| 8 | 天山童姥 | 60 | 1003 | 1003 | 财务部 |
| 9 | 慕容博 | 58 | 1003 | 1003 | 财务部 |
| 10 | 丁春秋 | 71 | 1005 | NULL | NULL |
| NULL | NULL | NULL | NULL | 1004 | 人事部 |
+------+--------------+------+---------+--------+-----------+
11 rows in set (0.01 sec)
--union all是将左右外连接相连接不去重
mysql> select * from emp3 a left join dept3 b on a.dept_id=b.deptno
-> union all
-> select * from emp3 a right join dept3 b on a.dept_id=b.deptno;
+------+--------------+------+---------+--------+-----------+
| eid | ename | age | dept_id | deptno | name |
+------+--------------+------+---------+--------+-----------+
| 1 | 乔峰 | 20 | 1001 | 1001 | 研发部 |
| 2 | 段誉 | 21 | 1001 | 1001 | 研发部 |
| 3 | 虚竹 | 23 | 1001 | 1001 | 研发部 |
| 4 | 阿紫 | 18 | 1001 | 1001 | 研发部 |
| 5 | 扫地僧 | 85 | 1002 | 1002 | 销售部 |
| 6 | 李秋水 | 33 | 1002 | 1002 | 销售部 |
| 7 | 鸠摩智 | 50 | 1002 | 1002 | 销售部 |
| 8 | 天山童姥 | 60 | 1003 | 1003 | 财务部 |
| 9 | 慕容博 | 58 | 1003 | 1003 | 财务部 |
| 10 | 丁春秋 | 71 | 1005 | NULL | NULL |
| 1 | 乔峰 | 20 | 1001 | 1001 | 研发部 |
| 2 | 段誉 | 21 | 1001 | 1001 | 研发部 |
| 3 | 虚竹 | 23 | 1001 | 1001 | 研发部 |
| 4 | 阿紫 | 18 | 1001 | 1001 | 研发部 |
| 5 | 扫地僧 | 85 | 1002 | 1002 | 销售部 |
| 6 | 李秋水 | 33 | 1002 | 1002 | 销售部 |
| 7 | 鸠摩智 | 50 | 1002 | 1002 | 销售部 |
| 8 | 天山童姥 | 60 | 1003 | 1003 | 财务部 |
| 9 | 慕容博 | 58 | 1003 | 1003 | 财务部 |
| NULL | NULL | NULL | NULL | 1004 | 人事部 |
+------+--------------+------+---------+--------+-----------+
20 rows in set (0.00 sec)
--子查询
--就是selec嵌套
--分为四种：
--1.单行单列：返回的是一个具体的内容，可以理解为一个单值数据
--2.单行多列：返回一行数据中多个列的内容
--3.多行单列：返回多行记录之中同一列的内容，相当于给出了一个操作范围
--4.多行多列：查询返回的结果是一张临时表
--查询年龄最大的员工信息，显示信息包含员工号、员工名字、员工年龄
mysql> select * from emp3 where age=(select max(age) from emp3);
+-----+-----------+------+---------+
| eid | ename | age | dept_id |
+-----+-----------+------+---------+
| 5 | 扫地僧 | 85 | 1002 |
+-----+-----------+------+---------+
1 row in set (0.01 sec)
--查询研发部和销售部的员工信息，包含员工号、员工名字
mysql> select * from emp3 where dept_id in(select deptno from dept3 where name in('研发部','销售部'));
+-----+-----------+------+---------+
| eid | ename | age | dept_id |
+-----+-----------+------+---------+
| 1 | 乔峰 | 20 | 1001 |
| 2 | 段誉 | 21 | 1001 |
| 3 | 虚竹 | 23 | 1001 |
| 4 | 阿紫 | 18 | 1001 |
| 5 | 扫地僧 | 85 | 1002 |
| 6 | 李秋水 | 33 | 1002 |
| 7 | 鸠摩智 | 50 | 1002 |
+-----+-----------+------+---------+
7 rows in set (0.00 sec)
--查询研发部20岁以下的员工信息，包括员工号、员工名字、部门名字
mysql> select * from emp3 a join dept3 b on a.dept_id=b.deptno and eid=(select eid from emp3 where age<20);
+-----+--------+------+---------+--------+-----------+
| eid | ename | age | dept_id | deptno | name |
+-----+--------+------+---------+--------+-----------+
| 4 | 阿紫 | 18 | 1001 | 1001 | 研发部 |
+-----+--------+------+---------+--------+-----------+
1 row in set (0.00 sec)
--子查询关键字
--1.all关键字
--格式 select ... from ... where c > all(查询语句)
--查询年龄大于‘1003’部门所有年龄的员工信息
mysql> select * from emp3 where age > all(select age from emp3 where dept_id='1003');
+-----+-----------+------+---------+
| eid | ename | age | dept_id |
+-----+-----------+------+---------+
| 10 | 丁春秋 | 71 | 1005 |
| 5 | 扫地僧 | 85 | 1002 |
+-----+-----------+------+---------+
2 rows in set (0.00 sec)
--查询不属于任何员工部门的员工信息
mysql> select * from emp3 where dept_id > all(select deptno from dept3);
+-----+-----------+------+---------+
| eid | ename | age | dept_id |
+-----+-----------+------+---------+
| 10 | 丁春秋 | 71 | 1005 |
+-----+-----------+------+---------+
1 row in set (0.00 sec)
--2.any关键字
--格式 select ... from ... where c > any(查询语句)
--查询年龄大于‘1003’ 部门任意员工员工年龄的员工信息
mysql> select * from emp3 where age > any(select age from emp3 where dept_id='1003') and dept_id <>'1003';
+-----+-----------+------+---------+
| eid | ename | age | dept_id |
+-----+-----------+------+---------+
| 10 | 丁春秋 | 71 | 1005 |
| 5 | 扫地僧 | 85 | 1002 |
+-----+-----------+------+---------+
2 rows in set (0.00 sec)
--3.some关键字，等同于any关键字
--4.in关键字，前边还可加not
--格式 select ... from ... where c in (查询语句)
--查询研发部和销售部的员工信息，包含员工号、员工信息
mysql> select * from emp3 where dept_id in(select deptno from dept3 where name in('研发部','销售部'));
+-----+-----------+------+---------+
| eid | ename | age | dept_id |
+-----+-----------+------+---------+
| 1 | 乔峰 | 20 | 1001 |
| 2 | 段誉 | 21 | 1001 |
| 3 | 虚竹 | 23 | 1001 |
| 4 | 阿紫 | 18 | 1001 |
| 5 | 扫地僧 | 85 | 1002 |
| 6 | 李秋水 | 33 | 1002 |
| 7 | 鸠摩智 | 50 | 1002 |
+-----+-----------+------+---------+
7 rows in set (0.00 sec)
--5.exists关键字（(查询语句)有结果，就执行前面的查询语句，如果没有结果，则不执行前面语句）
--格式 select ... from ... where exists(查询语句)
--查询公司是否有大于60岁的员工，有则输出
mysql> select * from emp3 a where exists (select age from emp3 where a.age>60);
+-----+-----------+------+---------+
| eid | ename | age | dept_id |
+-----+-----------+------+---------+
| 10 | 丁春秋 | 71 | 1005 |
| 5 | 扫地僧 | 85 | 1002 |
+-----+-----------+------+---------+
2 rows in set (0.00 sec)
--查询有所属部门的员工信息
mysql> select * from emp3 where dept_id not in (select deptno from dept3 where deptno);
+-----+-----------+------+---------+
| eid | ename | age | dept_id |
+-----+-----------+------+---------+
| 10 | 丁春秋 | 71 | 1005 |
+-----+-----------+------+---------+
1 row in set (0.00 sec)
--自关联查询
--概念：mysql有时在信息查询时需要进行对表自身进行关联查询，即一张表自己和自己关联，一张表当成多张表来用。注意自关联时表必须给表其别名。
--格式：
/*select 字段列表 from 表1 a ，表1 b where 条件;
或者
select 字段列表 from 表1 a [left] join 表1 b on 条件;
*/
--插入数据
mysql> create database mydb4;
Query OK, 1 row affected (0.00 sec)
mysql> use mydb4;
Database changed
mysql> CREATE TABLE t_sanguo(
-> eid int PRIMARY key,
-> ename varchar(20),
-> manager_id int, -- 外键列
-> foreign key (manager_id) REFERENCES t_sanguo (eid)
-> );
Query OK, 0 rows affected (0.39 sec)
mysql> INSERT INTO t_sanguo VALUES (1,"刘协", null);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t_sanguo VALUES (2,"刘备",1);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t_sanguo VALUES (3,"关羽",2);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t_sanguo VALUES (4,"张飞",2);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t_sanguo VALUES (5,"曹操",1);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t_sanguo VALUES (6,"许诸",5);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO t_sanguo VALUES (7,"典韦",5);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t_sanguo VALUES (8,"孙权",1);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t_sanguo VALUES (9,"周瑜",8);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t_sanguo VALUES (10,"鲁肃",8);
Query OK, 1 row affected (0.00 sec)
--进行关联查询
--查询每个三国人物及他的上级信息，如： 关羽 刘备
--查询所有人物及上级
mysql> select a.ename,b.ename from t_sanguo a join t_sanguo b on a.manager_id=b.eid;
+--------+--------+
| ename | ename |
+--------+--------+
| 刘备 | 刘协 |
| 关羽 | 刘备 |
| 张飞 | 刘备 |
| 曹操 | 刘协 |
| 许诸 | 曹操 |
| 典韦 | 曹操 |
| 孙权 | 刘协 |
| 周瑜 | 孙权 |
| 鲁肃 | 孙权 |
+--------+--------+
9 rows in set (0.00 sec)
--包括没有上级的信息
mysql> select a.ename,b.ename from t_sanguo a left join t_sanguo b on a.manager_id=b.eid;
+--------+--------+
| ename | ename |
+--------+--------+
| 刘协 | NULL |
| 刘备 | 刘协 |
| 关羽 | 刘备 |
| 张飞 | 刘备 |
| 曹操 | 刘协 |
| 许诸 | 曹操 |
| 典韦 | 曹操 |
| 孙权 | 刘协 |
| 周瑜 | 孙权 |
| 鲁肃 | 孙权 |
+--------+--------+
10 rows in set (0.00 sec)
--查询所有人物、上级、上上级 比如： 张飞 刘备 刘协
mysql> select a.ename,b.ename,c.ename from t_sanguo a join t_sanguo b join t_sanguo c on a.manager_id=b.eid and c.eid=b.manager_id;
+--------+--------+--------+
| ename | ename | ename |
+--------+--------+--------+
| 关羽 | 刘备 | 刘协 |
| 张飞 | 刘备 | 刘协 |
| 许诸 | 曹操 | 刘协 |
| 典韦 | 曹操 | 刘协 |
| 周瑜 | 孙权 | 刘协 |
| 鲁肃 | 孙权 | 刘协 |
+--------+--------+--------+
6 rows in set (0.00 sec)
--多表、子查询练习
--准备数据
mysql> use mydb5;
Database changed
mysql> create table dept(
-> deptno int primary key comment '部门编号',
-> dname varchar(14) comment '部门名称',
-> loc varchar(13) comment '部门地址'
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into dept values(10,'accounting','new york'),
-> (20,'research','dallas'),
-> (30,'sales','chicago'),
-> (40,'operations','boston');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> create table emp(
-> empno int primary key comment '员工编号',
-> ename varchar(10) comment '员工名',
-> job varchar(9) comment '员工工作',
-> mgr int comment '员工直属领导编号',
-> hiredate date comment '入职时间',
-> sal double comment '工资',
-> comm double comment '奖金',
-> deptno int comment '对应dept表的外键'
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> alter table emp add constraint foreign key emp(deptno) references dept (deptno);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create table salgrade(
-> grand int comment '等级',
-> losal double comment '最低工资',
-> hisal double comment '最高工资'
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> insert into salgrade values (1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> insert into emp values
-> (7369,'smith','clerk' ,7902,'1980-12-17',800,null,20),
-> (7499,'allen','salesman',7698,'1981-02-20',1600,300,30),
-> (7521,'ward','salesman',7698,'1981-02-22',1250,500,30),
-> (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20),
-> (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30),
-> (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30),
-> (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10),
-> (7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20),
-> (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10),
-> (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30),
-> (7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20),
-> (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30),
-> (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20),
-> (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
Query OK, 14 rows affected (0.10 sec)
Records: 14 Duplicates: 0 Warnings: 0
--练习
--1.返回拥有员工的部门、部门号
mysql> select deptno,dname from dept where deptno in(select deptno from emp);
+--------+------------+
| deptno | dname |
+--------+------------+
| 10 | accounting |
| 20 | research |
| 30 | sales |
+--------+------------+
3 rows in set (0.00 sec)
--2.工资水平多于smith的员工信息
mysql> select * from emp where sal>(select sal from emp where ename='smith');
+-------+--------+-----------+------+------------+------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+------+------+--------+
| 7499 | allen | salesman | 7698 | 1981-02-20 | 1600 | 300 | 30 |
| 7521 | ward | salesman | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | 10 |
+-------+--------+-----------+------+------------+------+------+--------+
13 rows in set (0.01 sec)
--3.返回员工和所属经理的姓名
mysql> select a.ename,b.ename from emp a,emp b where a.mgr=b.empno;
+--------+-------+
| ename | ename |
+--------+-------+
| smith | FORD |
| allen | BLAKE |
| ward | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+-------+
13 rows in set (0.01 sec)
--4.返回雇佣日期早于其经理雇佣日期的员工及其经理姓名
mysql> select a.ename,b.ename from emp a,emp b where a.mgr=b.empno and a.hiredate < b.hiredate;
+-------+-------+
| ename | ename |
+-------+-------+
| smith | FORD |
| allen | BLAKE |
| ward | BLAKE |
| JONES | KING |
| BLAKE | KING |
| CLARK | KING |
+-------+-------+
6 rows in set (0.00 sec)
--5.返回员工姓名及其所在的部门名称
mysql> select ename,dname from emp a,dept b where a.deptno=b.deptno;
+--------+------------+
| ename | dname |
+--------+------------+
| CLARK | accounting |
| KING | accounting |
| MILLER | accounting |
| smith | research |
| JONES | research |
| SCOTT | research |
| ADAMS | research |
| FORD | research |
| allen | sales |
| ward | sales |
| MARTIN | sales |
| BLAKE | sales |
| TURNER | sales |
| JAMES | sales |
+--------+------------+
14 rows in set (0.00 sec)
--6.返回从事clerk工作的员工姓名和所在部门名称
mysql> select * from emp a,dept b where a.deptno=b.deptno and a.job in('clerk');
+-------+--------+-------+------+------------+------+------+--------+--------+------------+----------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | dname | loc |
+-------+--------+-------+------+------------+------+------+--------+--------+------------+----------+
| 7369 | smith | clerk | 7902 | 1980-12-17 | 800 | NULL | 20 | 20 | research | dallas |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | NULL | 20 | 20 | research | dallas |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | 30 | sales | chicago |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | 10 | 10 | accounting | new york |
+-------+--------+-------+------+------------+------+------+--------+--------+------------+----------+
4 rows in set (0.00 sec)
mysql> select * from emp a,dept b where a.deptno=b.deptno and a.job='clerk';
+-------+--------+-------+------+------------+------+------+--------+--------+------------+----------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | dname | loc |
+-------+--------+-------+------+------------+------+------+--------+--------+------------+----------+
| 7369 | smith | clerk | 7902 | 1980-12-17 | 800 | NULL | 20 | 20 | research | dallas |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | NULL | 20 | 20 | research | dallas |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | 30 | sales | chicago |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | 10 | 10 | accounting | new york |
+-------+--------+-------+------+------------+------+------+--------+--------+------------+----------+
4 rows in set (0.00 sec)
--7.返回部门号及其本部门的最低工资
mysql> select a.deptno,dname,min(sal) from emp a,dept b where a.deptno=b.deptno group by deptno;
+--------+------------+----------+
| deptno | dname | min(sal) |
+--------+------------+----------+
| 10 | accounting | 1300 |
| 20 | research | 800 |
| 30 | sales | 950 |
+--------+------------+----------+
3 rows in set (0.00 sec)
--8.返回销售部（sales）所有员工的姓名
mysql> select deptno,ename from emp where deptno in(select deptno from dept where dname='sales');
+--------+--------+
| deptno | ename |
+--------+--------+
| 30 | allen |
| 30 | ward |
| 30 | MARTIN |
| 30 | BLAKE |
| 30 | TURNER |
| 30 | JAMES |
+--------+--------+
6 rows in set (0.00 sec)
--9.返回工资水平多于平均工资的员工
mysql> select * from emp where sal>(select avg(sal) from emp);
+-------+-------+-----------+------+------------+------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+-----------+------+------------+------+------+--------+
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
+-------+-------+-----------+------+------------+------+------+--------+
6 rows in set (0.00 sec)
--10.返回与scott从事相同工作的员工
mysql> select * from emp where job in(select job from emp where ename='SCOTT') and ename <> 'SCOTT';
+-------+-------+---------+------+------------+------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+---------+------+------------+------+------+--------+
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
+-------+-------+---------+------+------------+------+------+--------+
1 row in set (0.00 sec)
--11.返回与30部门员工工资水平相同的员工姓名与工资
--12.返回员工工作及其从事此工作的最低工资
mysql> select job,min(sal) from emp a,dept b where a.deptno=b.deptno group by job ;
+-----------+----------+
| job | min(sal) |
+-----------+----------+
| ANALYST | 3000 |
| CLERK | 800 |
| MANAGER | 2450 |
| PRESIDENT | 5000 |
| salesman | 1250 |
+-----------+----------+
5 rows in set (0.00 sec)
--13.计算出员工的年薪，并且以年薪排序
mysql> select ename,(sal*12) all_sal from emp order by all_sal;
+--------+---------+
| ename | all_sal |
+--------+---------+
| smith | 9600 |
| JAMES | 11400 |
| ADAMS | 13200 |
| ward | 15000 |
| MARTIN | 15000 |
| MILLER | 15600 |
| TURNER | 18000 |
| allen | 19200 |
| CLARK | 29400 |
| BLAKE | 34200 |
| JONES | 35700 |
| SCOTT | 36000 |
| FORD | 36000 |
| KING | 60000 |
+--------+---------+
14 rows in set (0.00 sec)
--14.返回工资处于第四级别的员工的姓名
mysql> select ename,sal from emp where sal>=(select losal from salgrade where grand=4) && sal<=(select hisal from salgrade where grand=4);
+-------+------+
| ename | sal |
+-------+------+
| JONES | 2975 |
| BLAKE | 2850 |
| CLARK | 2450 |
| SCOTT | 3000 |
| FORD | 3000 |
+-------+------+
5 rows in set (0.00 sec)
--15.返回工资为二等级的职员名字、部门所在地
mysql> select a.ename,b.loc from (select ename,deptno from emp where sal>=(select losal from salgrade where grand=2) and sal <=(select hisal from salgrade where grand=2))
+--------+----------+
| ename | loc |
+--------+----------+
| ward | chicago |
| MARTIN | chicago |
| MILLER | new york |
+--------+----------+
3 rows in set (0.00 sec)
五、视图
介绍
- 视图(view)是一个虚拟表，非真实存在，其本质是根据SQL语句获取动态的数据，并为其命名，用户使用时只需要使用视图名称即可获取结果集，并可以将其当做表来使用；它相当于一个快捷方式，当原表变动时，视图也跟着变动。
作用
- 简化代码，可以把重复使用的查询封装成视图重复使用，同时可以使复杂的查询易于理解和使用。
- 安全原因，如果一张表中有很多数据，很多信息不希望让所有人看到，此时可以使用视图，如：社会保险基金表，可以用视图只显示名字，地址，而不显示社会保险号和工资数等，可以对不同的用户，设定不同的视图。
视图的创建
格式：create [or replace] [algorithm = {undefined | merge |tmptable}] view view_name [(colume_list)] as 查询语句 [with [cascaded | local] check option]
主要格式：create [or replace] view 视图名 as 查询语句；
参数说明：
or replace：有这个视图则替换，没有则创建
视图的修改
格式1：利用创建相同名字视图覆盖原视图
create [or replace] view 视图名 as 查询语句；
格式2：alter view 视图名 as查询语句；
视图的更新
更新：修改update、插入inster、删除delect
有以下不可更新：
- 包含聚合函数（sum(),max(),min(),count()）等不可更新
- 包含去重:distinct不可更新
- 视图包含group by、having不可更新
- 视图包含union 或 union all不可更新
- 视图包含子查询不可更新
- 视图包含join不可更新
- 视图包含不可更改的文字值（常量）不可更新
视图重命名与删除
-
重命名
格式：rename table 视图名 to 新视图名；
-
删除视图
格式：drop view [if exists] 视图名;
练习
--1.查询部门平均薪水最高的部门
mysql> create or replace view view1_avg as select dname,avg(sal) avgsal from emp a,dept b where a.deptno=b.deptno group by dname;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from view1_avg;
+-----------+--------------+
| dname | avgsal |
+-----------+--------------+
| 学工部 | 21750.000000 |
| 教研部 | 29166.666667 |
| 销售部 | 15666.666667 |
+-----------+--------------+
3 rows in set (0.00 sec)
mysql> select dname from view1_avg where avgsal=(select max(avgsal) from view1_avg);
+-----------+
| dname |
+-----------+
| 教研部 |
+-----------+
1 row in set (0.34 sec)
--2.查询员工比所属领导薪资高的部门名、员工名、员工领导编号
--3.查询工资等级为4级，2000年以后入职的工作地点为北京的员工编姓名和工资，并查询出薪资在前三名的员工信息
六、触发器
介绍
- 只有执行insert、delectable、update操作时才能触发触发器的执行
- 只支持对行级触发，不支持语句级触发
- mysql中定义了new和old，用来表示触发器的所在表，触发了触发器的那一行数据，来引用触发器中发生变化的记录内容
特性
- 什么条件会触发：inster、delect、update
- 什么时候触发：在增删改前或者后
- 触发频率：针对每一行执行
- 触发器定义在表上，附着在表上
格式
- 创建只有一个执行语句的触发器（before：之前|after：之后）
create trigger 触发器名 before|after 触发事件
on 表名 for each row
执行语句
- 创建有多个执行语句的触发器
create trigger 触发器名 before|after 触发事件
on 表名 for each row
begin
执行语句列表
end;
--数据导入
mysql> use mydb10_trigger;
Database changed
mysql> create table user(
-> uid int primary key,
-> username varchar(50) not null,
-> password varchar(50) not null
-> );
mysql> create table user_logs(
id int primary key auto_increment,
time timestamp,
log_text varchar(255)
);
Query OK, 0 rows affected (0.01 sec)
--需求1：当user表添加一行数据，则会自动在user_log添加日志记录
--定义触发器：trigger_test1
--在user表添加数据，让触发器自动执行
--需求2：当user表数据被修改时，则会自动在user_log添加日志记录
--在user表中修改数据，让触发器自动执行
操作-new与old
格式：
触发器类型 触发器类型new和old的使用
insert型触发器 new表示将要或者已经新增的数据
update型触发器 old表示修改之前的数据，new表示将要或已经修改后的数据
delect型触发器 old表示将要或者已经删除的数据
使用方法：new.columnName（columnName为相应数据表某一列名）
--insert类型的触发器
--new
--update类型的触发器
--old
--new
--delect类型的触发器
--old
操作
-
操作-查看触发器
show trigger; -
操作-删除触发器
dorp trigger [if exists] 触发器名;
注意事项
- mysql中触发器中不能对本表进行insert、update、delect操作，以免递归循环触发
- 尽量少使用触发器，假设触发器触发每次执行1s，insert table 500条数据，那么就需要触发500次触发器，光是触发器执行的时间就花费了500s，而insert 500条数据一个是1s，那么这个insert的效率就非常低了。
- 触发器是针对每一行的；对增删改非常频繁的表上切记不要使用触发器，因为它会非常消耗资源。
七、索引
mysql索引分为：1.单列索引、2.组合索引、3.全文索引、4、空间索引
单列索引
单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。
- 普通索引：mysql中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点
- 唯一索引：
- 主键索引
普通索引：B+树
--方式1：创建的时候直接指定
create table student(
sid int primary key,
card_id varchar(20),
name varchar(20),
gender varchar(20),
age int,
birth date,
phone_num varchar(20),
score double,
index index_name(name) --给name列创建索引
);
--方式2：直接创建
create index index_name on tablename(columnname：);
--tablename:表名
--columnname:列名
--方式3：修改表结构
create table student add index indexname(columnname);
--indexname:索引名字
--columnname:列名
--1.查看数据库所有索引
--select * from mysql.'innodb index stats' a where a.'database name' = '数据库名';
select * from mysql.'innodb index stats' a where a.'database name' = 'mydb5';
--查看表中的所有索引
--select * from mysql.'innodb index stats' a where a.'database name' = '数据库名' and a.table_name like '%表名%';
select * from mysql.'innodb index stats' a where a.'database name' = 'mydb5' and a.table_name like '%student%';
--查看表中索引
show index from表名;
--索引的操作-删除
--格式：
drop index 索引名 on 表
--或
alter table 表名 drop index 索引名;
唯一索引：唯一索引与前面的普通索引类似，不同的就是:索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合唯一。
--方式1-创建表的时候直接指定
create table student(
sid int primary key,
card_id varchar(20),
name varchar(20),
gender varchar(20),
age int,
birth date,
phone_num varchar(20),
score double,
unique index 索引名(列名) --给name列创建索引
);
--方式2-直接创建
create unique index 索引名 on 表名(列名)
--方式3-修改表结构(添加索引)
alter table 表名 add unique [索引名](列名)
--删除索引
drop index 索引名 on 表名
--或
alter table 表名 drop index 索引名
--主键索引
--介绍：每张表一般都会有自己的主键，当我们在创建表时，mysql会自动在主键列上建立一个索引，这就是主键索引。主键索引具有唯一性并且不允许null，所以他是一种特殊的索引。
组合索引
介绍：
- 组合索引也叫复合索引，指的是我们在建立索引的时候使用多个字段，例如同时是哟你身份证和手机号建立索引，同样的同时建立为普通索引或者是唯一索引
- 复合索引的使用复合最原则
格式：
--创建索引的基本语法-普通索引
create index 索引名 on 表名(列名),(列名)
--创建索引的基本语法-唯一索引
create unique index 索引名 on 表名(列名),(列名)
全文索引
概述：
- 全文索引关键字是fulltext
- 全文索引主要用于查找文本中的关键字，而不是直接与索引中的值相比较，它更像是一个搜索引擎，基于相识度的查询，而不是简单的where语句的参数匹配
- 用like+%就可以实现模糊匹配了，为什么还要全文索引呢？like+%在文本比较少时是适合的，但是对于大量的文本数据检索，是不可想象的。全文索引在大量的数据面前，能比like+快N倍，速度不是一个数量级，但是全文索引可能存在精度问题。
- char、varchar、text及其系列才可以建全文索引
- 先建表，再放数据，然后再建索引会比较快。
--数据准备
create table 表名(
id int primary key auto_increment,
title varchar(255),
content varchar(1000),
writing_date date,
fulltext (content) --创建全文索引
);
--使用全文索引
select * from 表名 where match(列名) against(关键字);
空间索引
介绍：
- 5.7之后，支持几何数据类型
- 空间索引是对空间数据类型的字段建立的索引，mysql中的空间数据类型有4种，分别是geometry、point、linestring、polygon。
- mysql使用spatial关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引
- 创建空间索引的列，必须将其声明为not null
- 空间索引一般是用的比较少，了解即可
类型 含义 说明
geometry 空间数据 任何一种空间类型
point 点 坐标值
linestring 线 有一系列点连接而成
polygon 多边形 由多条线组成
create table 表名(
id int primary key auto——increment comment 'id';
shop_name varchar(64) not null comment '门店名称';
geom_point geometry not null comment '经纬度';
spatial key geom_index(geom_point) --创建空间索引spatial key 索引名(列名)
)
八、事务
事务的操作
事务的操作
-
开启事务：任何一条DML语句(insert、update、delecte)执行，标志事务的开启
begin 或 start transaction -
提交事务：成功的结束，将所有的DML语句操作历史记录和底层硬盘数据来一次同步
commit -
回滚事务：失败的结束，将所有的DML语句操作历史记录全部清空
rollback
事务的自动提交
--禁止自动提交
set autocommit=0
--开启自动提交
set autocommit
事务的特性
- 原子性：事务是一个不可分割的整体，事务开始后的所有操作，要么全部完成，要么全部不做。
- 一致性：系统从一个正确的状态，迁移到另一个正确的状态。
- 隔离性：每个事务的对象对其他事务的操作对象互相分离，事务提交前对其他事务不见。
- 持久性：事务一旦提交，则其结果是永久性的。
事务的隔离级别
--读未提交
--一个事务可以读另一个为提交的事务，最低级别，任何情况都无法保证，会造成脏读。
read uncommitted
--读提交
--一个事务等另一个事务提后才能读取数据，可避免脏读的发生，会造成不可重复度。
read committed
--可重复读-mysql默认
--就是在开始读数据(事务开启)时，不再允许修改操作，可避免脏读、不可重复度的发生，但是会造成幻读
repeateable read
--序列化
--是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复度、幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。
serializable
事务的隔离级别-操作
--查看隔离级别
show variables like '%isolation%';
--设置隔离级别
set session transaction isolation level 级别字符串;
九、mysql锁机制
概述
概述：相当于火车上上厕所，一个车厢只有一个厕所，进去一个人上厕所，另外的人不能进去。
从对数据的粒度分:
1)表锁：操作时，会锁定整个表。
2)行锁：操作时，会锁定当前操作。
从对数据操作的类型分:
1)读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
2)写锁（排它锁）：当前操作没有完成之前，它会阻断其他写锁和读锁。
存储引擎:
存储引擎 表级锁 行级锁
MylSAM 支持 不支持
InnoDB 支持 支持
MEMORY 支持 不支持
BOB 支持 不支持
锁类型特点：
1）表级锁：偏向MylSAM存储引擎，开销小，加锁快，不会出现死锁；锁定粒度大，发生冲突和概率最高，并发度最低。适合以查询为主，只有少量按索引条件更新数据的应用，如web应用。
2）行级锁：偏向InnoDB引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。适合有大量索引条件并发更新少量不同数据，同时又有查询应用，如一些在线事务处理（OLTP）系统。
MylSAM引擎
如何加锁
--加读锁:
lock table 表名 read;
--加写锁:
lock table 表名 write;
--解锁:
unlock table;
加读锁（共享锁）：加读锁后，本用户可查看表，但不可修改表，别的用户也可加读锁，同样也可一样的操作。不能查看、操作其他表
加写锁（排他锁 / 独享锁）：加写锁后，本用户可查询和修改，别的用户不能修改，也不能查看，且也不能加写锁。许本用户解锁后才可加写锁。
InnoDB引擎
表锁：与MylSAM引擎的表锁一致。
InnoDB与MylSAM的最大不同有两点：一是支持事务；是采用了行级锁。
InnoDB实现了以下两种类型的行锁：
共享锁（s）：又称为读锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
排它锁（x）：有称为写锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。
对于update、delect和insert语句，InnoDBhi自动给涉及数据集加排它锁；
对于普通的select语句，InnoDB不会加如何锁。
3952
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
