mysql学习笔记——增删改查基本数据库操作指南

简介

数据库（database）是按照一定的数据结构来组织、存储和管理数据的仓库。mysql是常用的关系型数据库（将数据保存在不同表中）之一。其数据库特点：

• MySQL 是开源的，所以你不需要支付额外的费用。
• MySQL 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
• MySQL 使用标准的 SQL 数据语言形式。
• MySQL 可以运行于多个系统上，并且支持多种语言。这些编程语言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
• MySQL 对PHP有很好的支持，PHP 是目前最流行的 Web 开发语言。
• MySQL 支持大型数据库，支持 5000 万条记录的数据仓库，32 位系统表文件最大可支持 4GB，64 位系统支持最大的表文件为8TB。
• MySQL 是可以定制的，采用了 GPL 协议，你可以修改源码来开发自己的 MySQL 系统。

注：文章中的部分代码示例参考自“参考文献”中的实例

操作数据库

注意:

① 所有的数据库名，表名，表字段都是区分大小写的。所以你在使用SQL命令时需要输入正确的名称。

② 所有执行语句末尾必须加英文分号 ; 表示语句写完，否则系统认为语句没有结束，不执行该语句。

 

登录mysql

当 MySQL 服务已经运行时, 我们可以通过 MySQL 自带的客户端工具登录到 MySQL 数据库中, 首先打开命令提示符, 输入以下格式的命名:

mysql -h 主机名 -u 用户名 -p

参数说明：

• -h : 指定客户端所要登录的 MySQL 主机名, 登录本机(localhost 或 127.0.0.1)该参数可以省略;
• -u : 登录的用户名;
• -p : 告诉服务器将会使用一个密码来登录, 如果所要登录的用户名密码为空, 可以忽略此选项。

 

当我们的客户端与服务器在同一台机器上时，输入命令：

mysql -h localhost -u root -p

参数说明：

• mysql是登录命令
• -h 后面是服务器的主机地址，因为我们的客户端与服务器在同一台机器上，所以输入localhost
• -u 后面是登录的用户名称，这里默认是root
• -p 表示后面要输入password（登录密码）

 

如果我们要登录本机的 MySQL 数据库，只需要输入以下命令即可：

[root@host]# mysql -u root -p   
Enter password:******  # 登录后输入密码，进入终端
mysql>XXX    # 开始输入你想执行的命令

登录数据库

• SHOW DATABASES: 列出 MySQL 数据库管理系统的数据库列表。

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| RUNOOB             |
| cdcol              |
| mysql              |
| onethink           |
| performance_schema |
| phpmyadmin         |
| test               |
| wecenter           |
| wordpress          |
+--------------------+
10 rows in set (0.02 sec)

• USE 数据库名 :选择要操作的Mysql数据库，使用该命令后所有Mysql命令都只针对该数据库。

mysql> use RUNOOB;
Database changed

• SHOW TABLES: 显示指定数据库的所有表，使用该命令前需要使用 use 命令来选择要操作的数据库。

mysql> use RUNOOB;
Database changed
mysql> SHOW TABLES;
+------------------+
| Tables_in_runoob |
+------------------+
| employee_tbl     |
| runoob_tbl       |
| tcount_tbl       |
+------------------+
3 rows in set (0.00 sec)

• SHOW COLUMNS FROM 数据表: 显示数据表的属性，属性类型，主键信息 ，是否为 NULL，默认值等其他信息。
• SHOW INDEX FROM 数据表: 显示数据表的详细索引信息，包括PRIMARY KEY（主键）。
• SHOW TABLE STATUS LIKE [FROM db_name] [LIKE 'pattern'] \G:  该命令将输出Mysql数据库管理系统的性能及统计信息。

退出mysql

退出 mysql> 命令提示窗口可以使用 exit 命令，如下所示：

mysql> exit
Bye

增（创建数据库）

CREATE DATABASE <数据库名>;
CREATE DATABASE IF NOT EXISTS <数据库名> DEFAULT CHARSET <字符编码> COLLATE <编码格式>;

例：
create DATABASE RUNOOB;    # 创建名为RUNOOB的数据库
CREATE DATABASE IF NOT EXISTS RUNOOB DEFAULT CHARSET utf8 COLLATE utf8_general_ci; # 创建名为RUNOOB的数据库，指定编码格式utf-8
注：
utf8_general_ci表示编码格式，此编码格式对字母大小写不敏感，不严格区分大小写，与这个对应的有个utf8_bin就是严格区分大小写的编码格式

删（删除数据库）

drop database <数据库名>;

例：
drop database RUNOOB;

改（修改数据库编码和格式）

alter database <数据库名> charset <字符编码>;
alter database <数据库名> CHARACTER SET <字符编码> COLLATE <编码格式>;
例：
alter database RUNOOB charset gbk;
alter database RUNOOB  CHARACTER SET GBK COLLATE gbk_chinese_ci;

 查（查看数据库）

show databases; # 查看所有的数据库

例：
show database RUNOOB; # 查看RUNOOB数据库

选择数据库 

use <数据库名>;
Database changed
例：
use RUNOOB;    # 选择了 RUNOOB 数据库，在后续的操作中都会在 RUNOOB 数据库中执行。
Database changed

操作表

select 查询结果1,查询结果2 as XXX 
from 从哪张表中查找数据
where 查询条件1 and 查询条件2 
group by 分组 
having 对分组结果指定条件
order by 对查询结果排序 XX desc（降序）, XXX asc（升序）;

创建数据表 CREATE TABLE

•  CREATE TABLE table_name (column_name column_type);

# 在 RUNOOB 数据库中创建数据表runoob_tbl
CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

 实例解析：

• 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
• AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
• PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。
• ENGINE 设置存储引擎，CHARSET 设置编码。

# DROP TABLE IF EXISTS `base_privilege`;
CREATE TABLE `base_privilege` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `route` varchar(255) DEFAULT NULL,
  `type` varchar(255) DEFAULT NULL,
  `icon` varchar(255) DEFAULT NULL,
  `parent_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_privilege_privilege` (`parent_id`),
  CONSTRAINT `fk_privilege_privilege` FOREIGN KEY (`parent_id`) REFERENCES `base_privilege` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8;

表中增加数据 insert into

insert into <数据表名称>(字段1,字段2,字段3,……) 
values('值1' , '值2' , '值3' , ……);

例：
# 向表student中增加2条数据
insert into student(学号,姓名,出生日期,性别) 
values('0001' , '猴子' , '1989-01-01' , '男');

insert into student(学号,姓名,出生日期,性别) 
values('0002' , '猴子' , '1990-12-21' , '女');


DROP TABLE IF EXISTS `employee_tbl`;
CREATE TABLE `employee_tbl` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL DEFAULT '',
  `date` datetime NOT NULL,
  `singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `employee_tbl` VALUES 
('1', '小明', '2016-04-22 15:25:33', '1'), 
('2', '小王', '2016-04-20 15:25:47', '3'), 
('3', '小丽', '2016-04-19 15:26:02', '2'), 
('4', '小王', '2016-04-07 15:26:14', '4'), 
('5', '小明', '2016-04-11 15:26:40', '4'), 
('6', '小明', '2016-04-04 15:26:54', '2');

基础查询

查询数据通用的 SELECT 语法：

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

• 查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
• SELECT 命令可以读取一条或者多条记录。
• 你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
• 你可以使用 WHERE 语句来包含任何条件。
• 你可以使用 LIMIT 属性来设定返回的记录数。
• 你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

SELECT 语句使用 WHERE 子句从数据表中读取数据的通用语法：

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

• 查询语句中你可以使用一个或者多个表，表之间使用逗号, 分割，并使用WHERE语句来设定查询条件。
• 你可以在 WHERE 子句中指定任何条件。
• 你可以使用 AND 或者 OR 指定一个或多个条件。
• WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
• WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。

区分大小写BINARY

MySQL 的 WHERE 子句的字符串比较是不区分大小写的。 你可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。

如下实例: 

mysql> SELECT * from runoob_tbl WHERE BINARY runoob_author='runoob.com';
Empty set (0.01 sec)

修改更新UPDATE

UPDATE 命令修改 MySQL 数据表数据的通用 SQL 语法：

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

• 你可以同时更新一个或多个字段。
• 你可以在 WHERE 子句中指定任何条件。
• 你可以在一个单独表中同时更新数据。

增加/修改/删除表字段ALTER

# 添加字段（字段类型、长度、默认值、注释）
alter table <tabel_name> add <字段1> <字段类型> <字段格式>...;
例：
alter table Websites add alexa int not Null;
alter table Websites add country varchar(100) not Null;

# 修改字段（修改字段名、字段类型、长度）
a、修改字段名：
alter table 表名 change 旧字段名  新字段名
b、修改字段类型
alter table 表名 modify 字段名 字段类型(字段长度)

# 删除字段
alter table 表名 drop 字段名
例：
alter table Websites drop alexa;

删除数据DELETE

DELETE 语句从 MySQL 数据表中删除数据的通用语法：

DELETE FROM table_name [WHERE Clause]

•  如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
• 你可以在 WHERE 子句中指定任何条件
• 您可以在单个表中一次性删除记录。 

查询字段包含某个字符——% 表示任意字符串

SELECT 语句使用 LIKE 子句从数据表中读取数据的通用语法：

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

• 你可以在 WHERE 子句中指定任何条件。
• 你可以在 WHERE 子句中使用LIKE子句。
• 你可以使用LIKE子句代替等号 =。
• LIKE 通常与 % 一同使用，类似于一个元字符的搜索。
• 你可以使用 AND 或者 OR 指定一个或多个条件。
• 你可以在 DELETE 或 UPDATE 命令中使用 WHERE...LIKE 子句来指定条件。 

SELECT * FROM <数据表名> WHERE <字段> like 'X%X';

例：
# 查询姓猴的学生名单
SELECT * FROM student WHERE 姓名 like '猴%';

# 查询姓名最后一个字是猴的学生名单
SELECT * FROM student WHERE 姓名 like '%猴';

# 查询姓名包含猴字的学生名单
SELECT * FROM student WHERE 姓名 like '%猴%';

查询字段包含某个字符的个数——计数count、条件检索where

计算总个数：count(*)

计算某字段总数：count(字段)

条件查询：where 条件1 and 条件2 and ……（字段在表格中）

select count(字段，需为主键) from <数据表名> where 字段 like '%X%';

例：
# 查询姓“猴”学生的个数
select count(学号) from student where 姓名 like '猴%';

汇总分析——汇总sum、去重distinct

汇总求和：sum(字段)

去重：distinct 字段

select 查询结果 [总成绩:汇总函数sum]
from 从哪张表中查找数据[成绩表score]
where 查询条件 [课程号是0002];

例：
select sum(成绩) from score where 课程号='0002';

# 去重
select count(distinct 学号) as 学生人数 from score;

分组统计——分组group by

按某字段分组统计：group by 字段

GROUP BY 语句根据一个或多个列对结果集进行分组。

在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

select 查询结果 [课程ID：是课程号的别名,最高分：max(成绩) ,最低分：min(成绩)]
from 从哪张表中查找数据 [成绩表score]
where 查询条件 [没有]
group by 分组 [各科成绩：也就是每门课程的成绩，需要按课程号分组];

例：
# 查询各科成绩最高和最低的分， 以如下的形式显示：课程号，最高分，最低分
select 课程号,max(成绩) as 最高分,min(成绩) as 最低分
from score
group by 课程号;

# 查询每门课程被选修的学生数
select 课程号, count(学号)
from score
group by 课程号;

# 查询男生、女生人数
select 性别,count(*)
from student
group by 性别;

汇总统计WITH ROLLUP

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。在group by的结果上再汇总所有数据。

SELECT name, SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;


SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;


select coalesce(a,b,c);
参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

分组结果指定条件查询——条件having、平均值avg、排序order by

条件查询：having 条件1 （字段可以是自定义新字段）

求平均值：avg(字段)

排序：order by 字段 ，desc（降序，从大到小），asc（升序，由小到大）

select 查询结果 [学号，平均成绩：汇总函数avg(成绩)]
from 从哪张表中查找数据 [成绩在成绩表中，所以查找的是成绩表score]
where 查询条件 [没有]
group by 分组 [平均成绩：先按学号分组，再计算平均成绩]
having 对分组结果指定条件 [平均成绩大于60分]

例：
# 查询平均成绩大于60分学生的学号和平均成绩
select 学号, avg(成绩) as 平均成绩
from score
group by 学号
having avg(成绩)>60;

# 查询至少选修两门课程的学生学号
select 学号, count(课程号) as 选修课程数目
from score 
group by 学号
having count(课程号)>=2;

# 查询同名同姓学生名单并统计同名人数
select 姓名, count(*) as 同名同姓人数
from student 
group by 姓名
having count(*)>=2;

# 查询不及格的课程并按课程号从大到小排列
select 课程号
from score 
where 成绩<60
order by 课程号 desc;

# 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
select 课程号, avg(成绩) as 平均成绩
from score
group by 课程号
order by 平均成绩 asc,课程号 desc;

# 检索课程编号为“0004”且分数小于60的学生学号，结果按按分数降序排列
select 学号
from score
where 课程号='04' and 成绩 <60
order by 成绩 desc;

复杂查询

子查询

先写子查询，再写父查询，where XX in (子查询)，XX对应子查询中的XX，相当于两表进行相同字段映射

# 查询所有课程成绩小于60分学生的学号、姓名
select 学号,姓名
from student 
where 学号 in (
SELECT 学号
from score
where 成绩<60
);
（参考案例中代码有误，此处已修改）

日期查询

关联子查询——limit、union all

UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

• expression1, expression2, ... expression_n: 要检索的列。
• tables: 要检索的数据表。
• WHERE conditions: 可选， 检索条件。
• DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
• ALL: 可选，返回所有结果集，包含重复数据。

# UNION 不能用于列出两个表中所有的country。如果一些网站和APP来自同一个国家，每个国家只会列出一次。# UNION 只会选取不同的值。请使用 UNION ALL 来选取重复的值！
SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;


# 使用 UNION ALL 从 "Websites" 和 "apps" 表中选取所有的country（也有重复的值）
SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country;


# 使用 UNION ALL 从 "Websites" 和 "apps" 表中选取所有的中国(CN)的数据（也有重复的值）
SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country;

 

max只能返回最大值数据，无法得到最大值所在行数据，需要使用关联子查询where

# 按课程号分组取成绩最大值所在行的数据

# 我们可以使用分组（group by）和汇总函数得到每个组里的一个值（最大值，最小值，平均值等）。但是无法得到成绩最大值所在行的数据。
select 课程号,max(成绩) as 最大成绩
from score 
group by 课程号;

# 我们可以使用关联子查询来实现：
select * 
from score as a 
where 成绩 = (
select max(成绩) 
from score as b 
where b.课程号 = a.课程号);

 查询前TOP N 数据，先查询有哪些分组，再根据每组查询前TOP N ，使用order by按顺序排列，再用limit N函数限制返回条数，然后对每组复制相同表达式，使用union all合并连接

# 查询各科成绩前两名的记录
# 第1步，查出有哪些组
select 课程号,max(成绩) as 最大成绩
from score 
group by 课程号;


# 第2步：先使用order by子句按成绩降序排序（desc），然后使用limt子句返回topN（对应这个问题返回的成绩前两名）
#-- 课程号'0001' 这一组里成绩前2名
select * 
from score 
where 课程号 = '0001' 
order by 成绩  desc 
limit 2;

# 第3步，使用union all 将每组选出的数据合并到一起
(select * from score where 课程号='0001' order by 成绩 desc limit 2)
UNION ALL
(select * from score where 课程号='0002' order by 成绩 desc limit 2)
UNION ALL
(select * from score where 课程号='0003' order by 成绩 desc limit 2);

多表查询

联结join

 JOIN 在两个或多个表中查询数据

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录，等价于求交集，inner可省略。
• LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

left join …… on……

两表联结

from 表1 as a left join 表2 as b

on a.字段=b.字段

# 查询所有学生的学号、姓名、选课数、总成绩
select a.学号,a.姓名,count(b.课程号) as 选课数,sum(b.成绩) as 总成绩
from student as a left join score as b
on a.学号 = b.学号
group by a.学号;

 两表联结+分组结果条件查询

having

# 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select a.学号,a.姓名, avg(b.成绩) as 平均成绩
from student as a left join score as b
on a.学号 = b.学号
group by a.学号
having avg(b.成绩)>85;

多表联结 （as可省略）

from 表1 as a inner join 表2 as b on a.字段=b.字段

inner join 表3 as c on b.字段=c.字段

# 查询学生的选课情况：学号，姓名，课程号，课程名称
select a.学号, a.姓名, c.课程号,c.课程名称
from student a inner join score b on a.学号=b.学号
inner join course c on b.课程号=c.课程号;

# 查询出每门课程的及格人数和不及格人数

topn查询

需求示例：查询每门课程前两名的学生以及成绩

实现方式：使用union all、自身左连接、子查询、用户变量等方式实现

1、使用union all实现

(select name,course,score from test1 where course='语文' order by score desc limit 2)
union all
(select name,course,score from test1 where course='数学' order by score desc limit 2)
union all
(select name,course,score from test1 where course='英语' order by score desc limit 2);

2、使用自身左连接

select a.name,a.course,a.score 
from test1 a left join test1 b on a.course=b.course and a.score<b.score
group by a.name,a.course,a.score
having count(b.id)<2
order by a.course,a.score desc;

 3、使用子查询

select *
from test1 a
where 2>(select count(*) from test1 where course=a.course and score>a.score)
order by a.course,a.score desc;

4、使用用户变量

set @num := 0, @course := '';

select name, course, score
from (
select name, course, score,
@num := if(@course = course, @num + 1, 1) as row_number,
@course := course as dummy
from test1
order by course, score desc
) as x where x.row_number <= 2;

5、使用窗口函数

按照各科课程成绩排名：row_number () over(partition by 课程号 order by 成绩 desc)

select a.课程号 ,b.姓名 ,a.成绩,a.ranking​ from (
select 课程号 ,学号 ,成绩 ,
row_number () over(partition by 课程号 order by 成绩 desc) as ranking​
from  score) as a 
inner join student b on a.学号 =b.学号 
where a.ranking​ <3

 case when自定义分组

case 
   when 条件1 then 结果1 
   when 条件2 then 结果2
   ...... 
   else 结果n
end

between and

时间范围

• datetime类型，参数带时分秒（yyyy-MM-dd HH:mm:ss） 

若datetime类型时，查询参数带时分秒，BETWEEN AND相当于  x >= y && x<=z。即包含头尾。

• datetime类型，参数不带时分秒（yyyy-MM-dd）

若datetime类型时，查询参数不带时分秒，BETWEEN AND相当于  x >= y && x<z。即只包含头。

• date类型，参数不带时分秒（yyyy-MM-dd）

若date类型时，查询参数不带时分秒，BETWEEN AND相当于  x >= y && x<=z。即包含头尾。

数字范围

• BETWEEN y AND z

包含头尾，相当于：x>=y && x<=z

row_number窗口函数排名

select 学号 ,avg(成绩),
row_number () over(order by avg(成绩) desc)
from score
group by 学号

NULL 值处理

• IS NULL: 当列的值是 NULL,此运算符返回 true。
• IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
• <=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。

关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。

在 MySQL 中，NULL 值与任何其它值的比较（即使是 NULL）永远返回 NULL，即 NULL = NULL 返回 NULL 。

MySQL 中处理 NULL 使用 IS NULL 和 IS NOT NULL 运算符。

参考文献

1. mysql教程 | 菜鸟教程
2. MySQL之数据库之增删改查操作
3. 数据库、表基本操作增删改查
4. 常见的SQL面试题：经典50题

5. mysql实现topN top1