MySQL基础-SQL命令和语言(二)

目录

• 一.SQL语句
• 二.DML数据操作语言（insert,delete,update）
  • 1.insert命令
  • 2.update命令
  • 3.delete命令
  • 4.使用update代替delete
• 三.DQL数据查询语言(select,desc)
  • 1.desc查看
  • 2.select查询语句
  • 3.查询练习
  • 4.条件查询
• 四.select高级用法
  • 1.传统连接
  • 2.自连接
  • 3.内连接
  • 4.外连接(基本不用)

一.SQL语句

1.DDL	数据定义语言
2.DCL	数据控制语言
3.DML	数据操作语言
4.DQL	数据查询语言

#DDL和DCL在上一章讲了

二.DML数据操作语言（insert,delete,update）

1.insert命令

#1）先查看表结构
mysql> desc student;
+----------+---------------------+------+-----+-------------------+----------------+
| Field    | Type                | Null | Key | Default           | Extra          |
+----------+---------------------+------+-----+-------------------+----------------+
| id       | int(11)             | NO   | PRI | NULL              | auto_increment |
| name     | varchar(12)         | NO   |     | NULL              |                |
| age      | tinyint(3) unsigned | NO   |     | NULL              |                |
| gender   | enum('男','女')     | YES  |     | 男                |                |
| cometime | datetime            | YES  |     | CURRENT_TIMESTAMP |                |
+----------+---------------------+------+-----+-------------------+----------------+
5 rows in set (0.00 sec)

#2)插入数据（不规范的）
mysql> insert into student values(4,'张三',28,'男',now());
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+--------+-----+--------+---------------------+
| id | name   | age | gender | cometime            |
+----+--------+-----+--------+---------------------+
|  1 | 张三   |  28 | 男     | 2020-07-14 19:58:36 |
|  2 | 李四   |  39 | 男     | 2020-07-14 19:58:43 |
|  3 | 王五   |  30 | 女     | 2019-03-14 12:43:20 |
|  4 | 张三   |  28 | 男     | 2020-07-15 15:22:47 |
+----+--------+-----+--------+---------------------+
4 rows in set (0.00 sec)

#3）插入数据（规范的）
##插入指定列数据
mysql> insert student(name,age,cometime) values('狗蛋',46,'2017-12-06');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+--------+-----+--------+---------------------+
| id | name   | age | gender | cometime            |
+----+--------+-----+--------+---------------------+
|  1 | 张三   |  28 | 男     | 2020-07-14 19:58:36 |
|  2 | 李四   |  39 | 男     | 2020-07-14 19:58:43 |
|  3 | 王五   |  30 | 女     | 2019-03-14 12:43:20 |
|  4 | 张三   |  28 | 男     | 2020-07-15 15:22:47 |
|  5 | 狗蛋   |  46 | 男     | 2017-12-06 00:00:00 |
+----+--------+-----+--------+---------------------+
5 rows in set (0.00 sec)

#4）插入多行数据
mysql> insert student(name,age,cometime) values('钢蛋',77,'2017-12-06'),('笨蛋',66,'2017-12-06');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+--------+-----+--------+---------------------+
| id | name   | age | gender | cometime            |
+----+--------+-----+--------+---------------------+
|  1 | 张三   |  28 | 男     | 2020-07-14 19:58:36 |
|  2 | 李四   |  39 | 男     | 2020-07-14 19:58:43 |
|  3 | 王五   |  30 | 女     | 2019-03-14 12:43:20 |
|  4 | 张三   |  28 | 男     | 2020-07-15 15:22:47 |
|  5 | 狗蛋   |  46 | 男     | 2017-12-06 00:00:00 |
|  6 | 钢蛋   |  77 | 男     | 2017-12-06 00:00:00 |
|  7 | 笨蛋   |  66 | 男     | 2017-12-06 00:00:00 |
+----+--------+-----+--------+---------------------+
7 rows in set (0.00 sec)

2.update命令

#1)先查看数据
mysql> select * from student;
+----+--------+-----+--------+---------------------+
| id | name   | age | gender | cometime            |
+----+--------+-----+--------+---------------------+
|  1 | 张三   |  28 | 男     | 2020-07-14 19:58:36 |
|  2 | 李四   |  39 | 男     | 2020-07-14 19:58:43 |
|  3 | 王五   |  30 | 女     | 2019-03-14 12:43:20 |
|  4 | 张三   |  28 | 男     | 2020-07-15 15:22:47 |
|  5 | 狗蛋   |  46 | 男     | 2017-12-06 00:00:00 |
|  6 | 钢蛋   |  77 | 男     | 2017-12-06 00:00:00 |
|  7 | 笨蛋   |  66 | 男     | 2017-12-06 00:00:00 |
+----+--------+-----+--------+---------------------+
7 rows in set (0.00 sec)

#2）修改数据
##使用update语句必须要加where条件
##如果数据库有主键，一定使用主键

#修改整个表中dender是'男'的数据
mysql> update student set name='赵六' where gender='男'；

#修改指定的一条数据（加上条件后）
mysql> update student set name='赵六' where id=4;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+--------+-----+--------+---------------------+
| id | name   | age | gender | cometime            |
+----+--------+-----+--------+---------------------+
|  1 | 张三   |  28 | 男     | 2020-07-14 19:58:36 |
|  2 | 李四   |  39 | 男     | 2020-07-14 19:58:43 |
|  3 | 王五   |  30 | 女     | 2019-03-14 12:43:20 |
|  4 | 赵六   |  28 | 男     | 2020-07-15 15:22:47 |
|  5 | 狗蛋   |  46 | 男     | 2017-12-06 00:00:00 |
|  6 | 钢蛋   |  77 | 男     | 2017-12-06 00:00:00 |
|  7 | 笨蛋   |  66 | 男     | 2017-12-06 00:00:00 |
+----+--------+-----+--------+---------------------+
7 rows in set (0.00 sec)

3.delete命令

#1)删除数据
#1.先查看数据，确认要删除的数据
#2.使用delete语句也一定要加where条件

mysql> delete from student where id=5;
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+--------+-----+--------+---------------------+
| id | name   | age | gender | cometime            |
+----+--------+-----+--------+---------------------+
|  1 | 张三   |  28 | 男     | 2020-07-14 19:58:36 |
|  2 | 李四   |  39 | 男     | 2020-07-14 19:58:43 |
|  3 | 王五   |  30 | 女     | 2019-03-14 12:43:20 |
|  4 | 赵六   |  28 | 男     | 2020-07-15 15:22:47 |
|  6 | 钢蛋   |  77 | 男     | 2017-12-06 00:00:00 |
|  7 | 笨蛋   |  66 | 男     | 2017-12-06 00:00:00 |
+----+--------+-----+--------+---------------------+
6 rows in set (0.00 sec)

4.使用update代替delete

#1)添加状态字段
mysql> alter table student add status enum('1','0') default 1;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+--------+-----+--------+---------------------+--------+
| id | name   | age | gender | cometime            | status |
+----+--------+-----+--------+---------------------+--------+
|  1 | 张三   |  28 | 男     | 2020-07-14 19:58:36 | 1      |
|  2 | 李四   |  39 | 男     | 2020-07-14 19:58:43 | 1      |
|  3 | 王五   |  30 | 女     | 2019-03-14 12:43:20 | 1      |
|  4 | 赵六   |  28 | 男     | 2020-07-15 15:22:47 | 1      |
|  6 | 钢蛋   |  77 | 男     | 2017-12-06 00:00:00 | 1      |
|  7 | 笨蛋   |  66 | 男     | 2017-12-06 00:00:00 | 1      |
+----+--------+-----+--------+---------------------+--------+
6 rows in set (0.00 sec)

#2）使用update修改状态
#相当于删除了，查看时加个状态条件
mysql> update student set status='0' where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+--------+-----+--------+---------------------+--------+
| id | name   | age | gender | cometime            | status |
+----+--------+-----+--------+---------------------+--------+
|  1 | 张三   |  28 | 男     | 2020-07-14 19:58:36 | 1      |
|  2 | 李四   |  39 | 男     | 2020-07-14 19:58:43 | 1      |
|  3 | 王五   |  30 | 女     | 2019-03-14 12:43:20 | 1      |
|  4 | 赵六   |  28 | 男     | 2020-07-15 15:22:47 | 0      |
|  6 | 钢蛋   |  77 | 男     | 2017-12-06 00:00:00 | 1      |
|  7 | 笨蛋   |  66 | 男     | 2017-12-06 00:00:00 | 1      |
+----+--------+-----+--------+---------------------+--------+
6 rows in set (0.00 sec)

#3）查看数据（加上状态条件）
#不显示'赵六'了
mysql> select * from student where status=1;
+----+--------+-----+--------+---------------------+--------+
| id | name   | age | gender | cometime            | status |
+----+--------+-----+--------+---------------------+--------+
|  1 | 张三   |  28 | 男     | 2020-07-14 19:58:36 | 1      |
|  2 | 李四   |  39 | 男     | 2020-07-14 19:58:43 | 1      |
|  3 | 王五   |  30 | 女     | 2019-03-14 12:43:20 | 1      |
|  6 | 钢蛋   |  77 | 男     | 2017-12-06 00:00:00 | 1      |
|  7 | 笨蛋   |  66 | 男     | 2017-12-06 00:00:00 | 1      |
+----+--------+-----+--------+---------------------+--------+
5 rows in set (0.00 sec)

#4）把'赵六'的状态改回来，就又回来了
mysql> update student set status='1' where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

三.DQL数据查询语言(select,desc)

1.desc查看

mysql> desc student;
+----------+---------------------+------+-----+-------------------+----------------+
| Field    | Type                | Null | Key | Default           | Extra          |
+----------+---------------------+------+-----+-------------------+----------------+
| id       | int(11)             | NO   | PRI | NULL              | auto_increment |
| name     | varchar(12)         | NO   |     | NULL              |                |
| age      | tinyint(3) unsigned | NO   |     | NULL              |                |
| gender   | enum('男','女')     | YES  |     | 男                |                |
| cometime | datetime            | YES  |     | CURRENT_TIMESTAMP |                |
| status   | enum('1','0')       | YES  |     | 1                 |                |
+----------+---------------------+------+-----+-------------------+----------------+
6 rows in set (0.00 sec)

2.select查询语句

#1)查询表中所有数据
#很危险，如果数据量过大，容易导致down机，除非知道数据不大
mysql> select * from student;

#先查询数据总量，然后决定是否可以查询所有数据
mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

#2)查看指定列的数据
mysql> select name,age,gender from student;
+--------+-----+--------+
| name   | age | gender |
+--------+-----+--------+
| 张三   |  28 | 男     |
| 李四   |  39 | 男     |
| 王五   |  30 | 女     |
| 赵六   |  28 | 男     |
| 钢蛋   |  77 | 男     |
| 笨蛋   |  66 | 男     |
+--------+-----+--------+
6 rows in set (0.00 sec)

#3）按条件查询（找李四的信息）
mysql> select name,age,gender from student where name='李四';
+--------+-----+--------+
| name   | age | gender |
+--------+-----+--------+
| 李四   |  39 | 男     |
+--------+-----+--------+
1 row in set (0.00 sec)

3.查询练习

#1)数据库导入sql文件
一：
[root@db01 ~]# mysql -uroot -p <world.sql 
Enter password:

二：
mysql> source /root/world.sql;

三：
mysql> . /root/world.sql;

#2）查看数据
mysql> use world;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)

mysql> select count(*) from city;
+----------+
| count(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.00 sec)

#3)查询练习
#1.查看表结构
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

#2.查看所有数据
mysql> select * from city;

#3.查看指定列的数据
mysql> select Name,Population from city;

#4.查看数据时排序（按照人口数量）
#升序
mysql> select Name,Population from city order by Population;
#降序
mysql> select Name,Population from city order by Population desc;

#5.查询部分数据
#查看前十条数据
mysql> select Name,Population from city order by Population desc limit 10;

#6.按照步长查询数据
mysql> select id,Name,Population from city limit 0,50;      #0-50

mysql> select id,Name,Population from city limit 50,50;     #51-100
							    #50起始位置  50步长

4.条件查询

#1.条件查询就是使用where语句，where语句可以使用的符号
条件符号：= < > <= >= != <> or and like
	精确匹配：=
	范围匹配：< > <= >= != <>
	模糊匹配：like
	连接语句：or and
	
#2.查询中国的城市人口
mysql> select name,population from city where CountryCode='CHN';

#3.查询黑龙江人口数量
mysql> select name,population from city where countrycode='CHN' and District='heilongjiang';

#4.查询中国人口数量小于100000的城市
mysql> select name,population from city where countrycode='CHN' and population < 100000;

#5.模糊匹配
#匹配以N结尾的数据
mysql> select name,countrycode from city where countrycode like '%N';
#匹配以N开头的数据
mysql> select name,countrycode from city where countrycode like 'N%';
#匹配包含N的数据
mysql> select name,countrycode from city where countrycode like '%N%';

#6.查询中国或美国的人口数量
#使用or
mysql> select name,population from city where countrycode = 'CHN' or countrycode = 'USA';

#使用in
mysql> select name,population from city where countrycode in ('CHN','USA');

#使用union all
mysql> select name,population from city where countrycode = 'CHN' union all select name,population from city where countrycode = 'USA';

四.select高级用法

#多表联查，联表查询

1.传统连接

练习一：连表查询：世界上小于100人的城市在哪个国家？请列出城市名字，国家名字与人口数量

#1.确认我要查哪些内容
国家名字  城市名字  城市人口数量   小于100人

#2.确认在哪个表
country.name   city.name   city.population   

#3.找出两个表相关联的字段
city.countrycode   country.code

#4.编写语句
mysql> select country.name,city.name,city.population from country,city where city.countrycode=country.code and city.population < 100;
+----------+-----------+------------+
| name     | name      | population |
+----------+-----------+------------+
| Pitcairn | Adamstown |         42 |
+----------+-----------+------------+
1 row in set (0.01 sec)

练习二：世界上小于100人的城市在哪个国家，是用什么语言？请列出城市名字，国家名字与人口数量和国家语言

#1.确认我要查哪些内容
国家名字  城市名字  城市人口数量   国家使用的语言   小于100人

#2.确认在哪个表
country.name   city.name   city.population   countrylanguage.language

#3.找出三个表相关联的字段
country.code   city.countrycode   countrylanguage.countrycode

#4.写sql语句
mysql> select country.name,city.name,city.population,countrylanguage.language from country,city,countrylanguage where country.code=city.countrycode and city.countrycode=countrylanguage.countrycode and city.population < 100;
+----------+-----------+------------+-------------+
| name     | name      | population | language    |
+----------+-----------+------------+-------------+
| Pitcairn | Adamstown |         42 | Pitcairnese |
+----------+-----------+------------+-------------+
1 row in set (0.04 sec)

2.自连接

#自己查找相同字段，使用自连接，两个关联的表必须有相同字段和相同数据
SELECT city.name,city.countrycode,countrylanguage.language,city.population
FROM  city NATURAL JOIN countrylanguage 
WHERE population > 1000000
ORDER BY population;

#两个表中没有相同字段不行，字段相同值不同不行
SELECT country.name,city.name,city.population FROM city NATURAL JOIN country WHERE population < 100;

#注意：
1.自连接必须有相同字段和相同值
2.两个表中的数据必须完全相同

3.内连接

#1）语法格式


select * from 表1 join 表2 on 相关联的条件 where 条件;

#注意：命中率（驱动的概念）
	表1 小表
	表2 大表
	
select * from 表1 inner join 表2 on 相关联的条件 where 条件;


#2）例子1：两表联查


#小于100人的城市在哪个国家，国家代码是什么？
select city.name,city.population,city.countrycode,country.name 
from city join country on city.countrycode=country.code 
where city.population < 100;


#3）例子2：三表联查


#世界上小于100人的城市在哪个国家？是用什么语言？
select country.name,city.name,city.population,countrylanguage.language
from city join country on city.countrycode=country.code 
join countrylanguage on country.code=countrylanguage.countrycode
where city.population < 100;

4.外连接(基本不用)

1)左外连接
两个表只显示左边表的内容，类似于脱敏

2）右外连接
两个表只显示又边表的内容