MySQL数据库入门实战教程

目录

前言

MySQL数据库简介

MySQL是一个关系型数据库管理系统 ，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。
MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型和大型网站的开发都选择 MySQL 作为网站数据库。

温馨提示：以下是本篇文章正文内容，下面案例可供参考

一、创建建数据库、创建建数据表、查看数据库、查看数据表

01.查看mysql服务器中所有数据库

 show databases;

02.进入某一数据库（进入数据库后，才能操作库中的表和表记录）

use test;-- 查看已进入的库
    select database();

03.查看当前数据库中的所有表

 show tables;

04.删除mydb库( mydb数据库名，谨慎操作)

– 语法：drop database 库名;

drop database mydb;/* 思考：当删除的表不存在时，如何避免错误产生？ */
    drop database if exists mydb;-- 如果mydb库存在，则执行删除库的操作，否则就不执行。

05.重新创建mydb库，指定编码为utf8

– 语法：create database 库名 charset 编码;

    create database mydb charset utf8;-- 如果创建库，不指定编码，默认使用服务器默认的编码（有可能是latin1/iso8859-1,其中没有中文字符）
    
    -- 如果不存在则创建mydb;
    create database if not exists mydb charset utf8;

06.查看建库时的语句（并验证数据库库使用的编码）

– 语法：show create database 库名;
– 语法：show create table 表名;

    show create database mydb;

07.进入mydb库，删除student学生表(如果存在)

– 语法：drop table 表名;
– 语法：show create table 表名;

        use mydb;
    drop table if exists student;

08.创建student学生表（编号[数值类型]、姓名、性别、出生年月、考试成绩[浮点型]）

/* 建表的语法：
create table 表名(
列名 数据类型,
列名 数据类型,
…
); */

    drop table if exists student;
    create table student(
        id int primary key auto_increment,
        name varchar(50),
        gender varchar(2),
        birthday date,
        score double);

09.查看student学生表结构

    desc student;);

二、新增、修改、删除表记录

10.往学生表(student)中插入记录(数据)

代码如下（示例）：

-- 插入记录：insert into 表名(列1，列2，列3...)values(值1，值2，值3...);
    insert into student(name,gender,birthday,score)values('张三','男','1999-2-2',70);
    insert into student values(null,'翠花','女','1997-2-2',80);
    insert into student values(null,'李四','男','1989-2-2',75);

/* 提示：
设置编码：set names gbk;
查看MySQL数据库使用的编码：show variables like ‘char%’;
mysql --default-character-set=gbk -uroot -proot */

11.查询student表所有学生的信息

    select * from student;

– select：后面跟要查询的列，其中*表示查询所有列
– from：指定查询哪张表

12.修改student表中所有学生的成绩，加10分特长分

– 修改语法: update 表名 set 列=值,列=值,列=值…;

       update student set score=score+10;-- mysql不支持 += 符号

13.修改student表中张三的成绩，将成绩改为98分。

   update student set score=98 where name='张三';

– where对表中的记录进行筛选过滤，最终的结果是只保留符合条件的记录。
/* 提示：where子句用于对记录进行筛选过滤，
保留符合条件的记录，将不符合条件的记录剔除。*/

14.删除student表中所有的记录

– 删除记录语法: delete from 表名 [where条件]
– 仅删除符合条件的

   delete from student  where gender='女';
   delete from student;
   truncate student ;

三、基础查询、where子句查询

1.基础查询

15.查询emp表中的所有员工，显示姓名，薪资，奖金

   select name, sal, bonus
   from emp;

16.查询emp表中的所有员工，显示所有列

select * from emp;

/* 使用 *（星号）的缺点：把不必要的列也查询出来了，而且效率不如直接指定列名 */

17.查询emp表中的所有部门和职位

   select dept, job from emp;
   select distinct dept, job from emp;

/* 思考：如果查询的结果中，存在大量重复的记录，如何剔除重复记录，只保留一条？ */
– distinct 用于剔除重复的记录

2.WHERE子句查询

18.查询emp表中薪资大于5000的所有员工，显示员工姓名、薪资

   select name,sal from emp
   where sal>5000;

19.查询emp表中总薪资(薪资+奖金)大于6500的所有员工，显示员工姓名、总薪资

   select name, sal+bonus 
   from emp
   where sal+bonus >6500;

– ifnull(列, 值)函数: 判断指定的列是否包含null值, 如果有null值, 用第二个值替换null值

   select name, sal+ifnull(bonus,0) 
   from emp
   where sal+ifnull(bonus,0)>6500;

/* 注意查看上面查询结果中的表头，思考如何将表头中的 sal+bonus 修改为 “总薪资” */

   select name 姓名, sal+ifnull(bonus,0) 总薪资
   from emp
   where sal+ifnull(bonus,0)>6500;

20.查询emp表中薪资在7000和10000之间的员工，显示员工姓名和薪资

   select name, sal from emp
   where sal>=7000 and sal<=10000;/* 提示: between...and...：在...之间 */
   select name, sal from emp
   where sal between 7000 and 10000;

21.查询emp表中薪资为 3400、4600、6800的员工，显示员工姓名和薪资

   select name, sal from emp
   where sal=3400 or sal=4600 or sal=6800;
   select name, sal from emp
   where sal in(3400,4600,6800);

22.查询薪资不为3400、4600、6800的员工

   select name, sal from emp
   where not(sal=3400 or sal=4600 or sal=6800);
   select name, sal from emp
   where !(sal=3400 or sal=4600 or sal=6800);
   
   select name, sal from emp
   where sal not in(3400,4600,6800);

23.查询emp表中薪资大于4000和薪资小于2000的员工，显示员工姓名、薪资。

   select name, sal from emp
   where sal>4000 or sal<2000;

24.查询emp表中薪资大于3000并且奖金小于600的员工，显示员工姓名、薪资、奖金。

-- 处理null值
   select name,sal,ifnull(bonus,0) from emp
   where sal>3000 and ifnull(bonus,0)<600;

25.查询没有部门的员工（即部门列为null值）

   select * from emp
   where dept is null;/* 思考：如何查询有部门的员工（即部门列不为null值） */
   select * from emp
   where dept is not null;

3.Like模糊查询

26.查询emp表中姓名中以"甘"开头的员工，显示员工姓名。

   select name from emp
   where name like '甘%';
   select name from emp
   where name like '甘__';/* like进行模糊查询，"%" 表示通配，表示0或多个字符。"_"表示一个任意的字符 */

27.查询emp表中姓名中包含"远"员工，显示员工姓名。

   select name from emp
   where name like '%远%';

28.查询emp表中姓名以"甘"开头并且姓名为2个字的员工，显示员工姓名。

select name from emp
   where name like '甘_';

四、分组查询、聚合函数、排序查询

29.对emp表按照部门对员工进行分组，查看分组后效果

/* 分组的语法: select 查询的列 from 表名 group by 列名
       根据指定的列进行分组 */
   select * from emp
   group by dept;

   select count(*) from emp
   -- 如果没有分组,(符合条件的)所有员工默认是一组.--count(*)就是统计结果有多少条记录,即统计所有员工的人数
   select dept,count(*) from emp
   group by dept;-- 如果分了组,按组进行统计,统计每组有多少条记录,也就是统计每组有多少人

30.对emp表按照职位进行分组, 并统计每个职位的人数, 显示职位和对应人数

   select job,count(*) from emp
   group by job;

31.对emp表按照部门进行分组, 求每个部门的最高薪资(不包含奖金)，显示部门名称和最高薪资

-- select max(sal) from emp;
   select dept,max(sal) from emp
   group by dept;-- 查询每个部门的最高薪资,现实部门,员工姓名,最高薪资
   select emp.dept, name, t1.msal from emp,(select dept,max(sal) msal from emp group by dept) t1
   where emp.dept = t1.dept and emp.sal=t1.msal;-- 查询员工表, 显示部门,姓名,薪资
   select dept,name,sal from emp;-- 按照部门分组, 显示部门,最高薪资
   select dept,max(sal) from emp group by dept;

32.统计emp表中薪资大于3000的员工个数（- count(column)统计某列的行数）

   select * from emp
   where sal>3000;

   select count(id) from emp
   where sal>3000;-- 和count(*)效果一样.
   select count(bonus) from emp
   where sal>3000;-- 由于bonus列中有null值,count函数会剔除null值再统计,所有结果是错的!-- 注意:聚合函数在统计时会自动剔除null值(即null不参与统计)

33.统计emp表中所有员工的薪资总和(不包含奖金)（- sum(column)对某列的值求和）

   select sum(sal) from emp;

34.统计emp表员工的平均薪资(不包含奖金)（- avg(column)对某列的值求平均值）

   select sum(sal)/count(*) from emp;
   select avg(sal) from emp;

35.查询emp表中所有在1993和1995年之间出生的员工，显示姓名、出生日期。

   select name, birthday from emp
   where birthday between '1993-1-1' and '1995-12-31';

   select name, birthday from emp
   where year(birthday) between 1993 and 1995;--year(date) 提取日期中的年份
   --month(date) 提取日期中的月份
   --day(date) 提取日期中的天数

36.查询本月过生日的所有员工

   select * from emp
   where month(curdate())=month(birthday);/* 
   curdate() 获取当前日期 年月日
   curtime() 获取当前时间 时分秒
   sysdate() 获取当前日期+时间 年月日 时分秒 */

4.排序查询

37.对emp表中所有员工的薪资进行升序(从低到高)排序，显示员工姓名、薪资。

/* 默认就是升序，所以asc可以省略不写 */
   select name, sal from emp
   order by sal;

38.对emp表中所有员工奖金进行降序(从高到低)排序，显示员工姓名、奖金。

   select name, bonus from emp
   order by bonus desc;

5.分页查询

39.查询emp表中的所有记录，分页显示：每页显示3条记录，返回第 1 页。

--limit(页码-1)*每页显示条数, 每页显示条数
   select * from emp limit 0,3;-- select * from emp limit 3;
   select * from emp limit 3,3;
   select * from emp limit 6,3;
   select * from emp limit 9,3;

40.查询emp表中的所有记录，分页显示：每页显示3条记录，返回第 2 页。

   select * from emp limit 3,3;

五、关联查询、外连接查询

41.查询部门和部门对应的员工信息

   select * from dept,emp;-- 笛卡尔积查询:如果有两张表, 其中一张表有m条数据, 另外一张表有n条数据, 笛卡尔积查询的结果是m*n条
   -- 这种查询其中包含大量错误的数据,一般我们不会直接使用这种查询。
   select * from dept,emp
   where dept.id = emp.dept_id;-- 通过关联条件,提取大量错误的数据,保留正确的数据,这就是关联查询, 其中where后面的条件就是关联条件。

42.查询所有部门和部门下的员工，如果部门下没有员工，员工显示为null（一定要列出所有部门）

   select * from dept left join emp
   on dept.id = emp.dept_id;-- left join:左外连接查询,会将左边表中的所有数据都列出来, 右边表只显示符合条件的记录

43.查询部门和所有员工，如果员工没有所属部门，部门显示为null

   select * from dept right join emp
   on dept.id=emp.dept_id;-- 右外连接查询: 将右边表中的所有员工都列出来,而左边表只显示和员工对应的部门.

扩展: 查询出所有部门和所有员工,并列出部门和员工的对应关系,如果没有对应的,在另一方显示为null即可。

   select * from dept full join emp
   on dept.id=emp.dept_id;#注意：mysql不支持全外连接查询

   select * from dept left join emp
   on dept.id = emp.dept_id
   union
   select * from dept right join emp
   on dept.id=emp.dept_id;--union联合查询, 将两个查询结果合并在一起显示,并去除重复的记录。（模拟全外连接查询）
   --union all和union作用大致相同,union all不会去重!

六、子查询、多表查询

44.查询部门和所有员工，如果员工没有所属部门，部门显示为null

-- 假设翠花的薪资是3000, 列出薪资比3000高的所有员工，显示姓名、薪资
   select name,sal from emp where sal>3000;-- 查询翠花的薪资为:
   select sal from emp where name='翠花';-- 合并两个查询语句
   select name,sal from emp where sal>(select sal from emp where name='翠花');

45.列出与’李铁柱’从事相同职位的所有员工，显示姓名、职位、部门编号。

-- 假设李铁柱从事的职位是销售员, 列出职位为销售员的所有员工
   select name, job, dept_id from emp
   where job='销售员';-- 查询李铁柱从事的职位:
   select job from emp where name='李铁柱';-- 合并查询结果
   select name, job, dept_id from emp
   where job=(select job from emp where name='李铁柱');

46.列出薪资比’实施部’部门(已知部门编号为30)所有员工薪资都高的员工信息，显示员工姓名、薪资和部门名称。

-- 外连接查询, 查询所有员工和员工对应的部门
   select emp.name, sal, dept.name
   from emp left join dept
   on emp.dept_id=dept.id
   -- 假设实施部门的最高薪资为3000, 列出薪资比3000高的员工信息
   select emp.name, sal, dept.name
   from emp left join dept
   on emp.dept_id=dept.id
   where sal>3000;-- 求出实施部门的最高薪资(实施部的部门编号30)
   select max(sal) from emp where dept_id=30;-- 合并两条查询SQL
   select emp.name, sal, dept.name
   from emp left join dept
   on emp.dept_id=dept.id
   where sal>(select max(sal) from emp where dept_id=30);

47.列出在’技术部’任职的员工，假定不知道’技术部’的部门编号， 显示部门名称，员工名称。

-- 关联查询两张表
   select dept.name, emp.name
   from emp,dept
   where emp.dept_id=dept.id;-- 求出在技术部的员工
   select dept.name, emp.name
   from emp,dept
   where emp.dept_id=dept.id 
       and dept.name='技术部';

48.(自查询)列出所有员工及其直接上级，显示员工姓名、上级编号，上级姓名

-- 列: e1.name, e2.id, e2.name
   -- 表: emp e1 员工表 emp e2 上级表
   -- 关联条件: e1.topid=e2.id
   select e1.name, e2.id, e2.name
   from emp e1, emp e2
   where e1.topid=e2.id;-- 查询所有员工及其对应的上级(包含没有上级的员工)
   select e1.name, e2.id, e2.name
   from emp e1 left join emp e2
   on e1.topid=e2.id;

49.列出最低薪资大于1500的各种职位，显示职位和该职位最低薪资

-- 根据职位进行分组, 统计每个职位的最低薪资
   select job,min(sal) from emp
   group by job;-- 筛选出 最低薪资大于1500 的职位有哪些
   select job,min(sal) from emp
   group by job
   having min(sal)>1500;

50.列出在每个部门就职的员工数量、平均工资。显示部门编号、员工数量，平均薪资。

   select dept_id,count(*),avg(sal) 
   from emp group by dept_id;

51.查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。

-- 关联查询两张表(dept, emp)
   select d.id, d.name, d.loc, e.name
   from dept d, emp e
   where d.id=e.dept_id;-- 替换要显示的列和统计部门人数
   select d.id, d.name, d.loc,count(e.name)
   from dept d, emp e
   where d.id=e.dept_id
   group by d.name;

52.列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。

/*
   列: e1.id, e1.name, d.name
   表: emp e1 员工表, emp e2 上级表, dept d
   关联条件:
       e1.topid=e2.id
       e1.dept_id=d.id
   筛选条件:
       e1.hdate<e2.hdate
    */
   select e1.id, e1.name, d.name
   from emp e1, emp e2, dept d
   where e1.topid=e2.id
       and e1.dept_id=d.id
       and e1.hdate < e2.hdate;

53.列出每个部门薪资最高的员工信息，显示部门编号、员工姓名、薪资

-- 查询emp表中所有员工的部门编号、姓名、薪资
   select dept_id, name, sal from emp;-- 查询emp表中每个部门的最高薪资，显示部门编号、最高薪资
   select dept_id,max(sal) from emp
   group by dept_id;-- 第二次查询的结果作为一张临时表和第一次查询进行关联查询
   select emp.dept_id, name, sal from emp,(select dept_id,max(sal) maxsal from emp
   group by dept_id) t1
   where t1.dept_id=emp.dept_id
       and emp.sal=t1.maxsal;

扩展: 求每个部门的最高薪资,显示部门名称和最高薪资(不能显示员工姓名)

-- 查询emp表中所有员工的部门编号、姓名、薪资
   select dept_id, name, sal from emp;-- 查询emp表中每个部门的最高薪资，显示部门编号、最高薪资
   select dept_id,max(sal) from emp
   group by dept_id;-- 第二次查询的结果作为一张临时表和第一次查询进行关联查询
   select dept.name,max(sal)
   from dept, emp
   where dept.id=emp.dept_id
   group by dept.name;

总结

以上就是数据库的基础知识和操作了，觉得有用的小伙伴点个关注。还会带来更多有用的知识。 内容高达了 五十多项知识点，如果能把以上的知识全部掌握入门完全没有问题了。
最后再补充一些细节！
1、笛卡尔积查询
笛卡尔积查询：如果同时查询两张表，左边表有m条数据，右边表有n条数据，那么笛卡尔积查询是结果就是 m*n 条记录。这就是笛卡尔积查询。例如：
select * from dept,emp;
上面的查询中包含大量错误的数据, 一般不使用这种查询。

如果只想保留正确的记录，可以通过where条件进行筛选，将符合条件的保留下来，不符合条件的自然就会被剔除，例如：
select * from dept,emp
where dept.id=emp.dept_id;

2、左外连接和右外连接查询：

(1) 左外连接查询：是将左边表中所有数据都查询出来, 如果在右边表中没有对应的记录, 右边表显示为null即可。
(2) 右外连接查询：是将右边表中所有数据都查询出来, 如果在左边表中没有对应的记录, 左边表显示为null即可。

3、where和having都用于筛选过滤，但是：
(1) where用于在分组之前进行筛选, having用于在分组之后进行筛选
(2) 并且where中不能使用列别名, having中可以使用别名
(3) where子句中不能使用列别名(可以使用表别名), 因为where子句比select先执行!

4、SQL语句的书写顺序和执行顺序:*

SQL语句的书写顺序：
select…
from…
where…
group by…
order by…
…
SQL语句的执行顺序：
from… – 确定要查询的是哪张表 (定义表别名)
where… – 从整张表的数据中进行筛选过滤
select… – 确定要显示哪些列 (定义列别名)
group by… – 根据指定的列进行分组
order by… – 根据指定的列进行排序