MySQL基础-SQL命令和语言(二)
目录
一.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）右外连接
两个表只显示又边表的内容
