Java基础86 MySQL数据库，基础知识

本文知识点（目录）：

    1、MySQL数据库的概述
    2、MySQL数据库的管理【对数据库的操作】（查询、删除、创建数据库，以及查询和修改数据库的编码模式）
    3、表的管理【对数据库 表的操作】 （创建、查询、删除、修改表名，以及添加、删除、查询和修改表的字段、字段类型、字段名称）
    4、增删改查数据【对表里面的数据的操作】（添加、删除、修改数据，以及各种查询：模糊、去重、聚合、分页、分组、排序、关联查询等等）
    5、附录（本文的所有sql操作语句集）

---

---

1、概述                                                  

  1.1、市面上最流行的数据库

      1、MySQL数据库：免费产品，java语言兼容性非常好，适合中小型企业小管理系统、中小型应用软件。
      2、Oracle数据库：收费产品，java语言兼容性非常好，适合中大型企业管理系统，中大型应用软件。

      数据存储的结构：先有数据库，再有表（数据表），最后才有数据。

  1.2、命令行连接MySQL数据库

     

   或者一起输入账号和密码：mysql -uroot -p1234   回车即可，注意：-u和-p后面是紧接着账号或密码，中间不需要空格，如有空格，则还需输入密码。

  1.3、MySQL命令行工具连接数据库

     

2、MySQL数据库的管理（对数据库的操作） 

  2.1、查询所有的数据库

mysql> show databases; -- 查询所有数据库

 

  2.2、创建数据库

mysql> create database student; -- 创建数据库

  2.3、删除数据库

mysql> drop database student; -- 删除数据库

  2.4、查看数据库的默认字符集（编码）

mysql> show create database student; -- 查看数据库的默认字符集

 

  2.5、修改字符集（编码）

mysql> alter database student default character set gbk; -- 修改字符集（修改了编码）

 

3、表的管理（对数据库 表的操作）                     

  3.1、创建表

    注：创建表前，先指定该表要创建在哪个数据库下；比如我创建在school数据库下面，即：use school;

mysql> use school; -- 使用school数据库

mysql> create table student( -- 创建student表
    -> sid int,
    -> sname varchar(20),
    -> sage int
    -> );

如下图所示：

  3.2、查询所有表（school数据库下的所有表）

mysql> show tables;

  3.3、查询表的结构

mysql> desc student;

  3.4、删除表

mysql> drop table student;

  3.5、修改表

    1> 添加字段

mysql> alter table student add column sex varchar(2); -- 向指定表中添加sex字段  column：纵列  

    2> 删除字段

mysql> alter table student drop column sex; -- 删除表中指定的字段

    3> 修改字段类型

mysql> alter table student modify column sage int; -- 修改表中指定的字段

    4> 修改字段名称

mysql> alter table student change column sage sage2 int; -- 修改表中指定字段的名称

    5> 修改表的名称

mysql> alter table student rename to teacher;

4、增删改查数据（对表里面的数据的操作）      

  4.1、添加数据

mysql> insert into teacher values(2,'张三',23); -- 向指定表中添加数据  注意：一定要依次按顺序插入，不能多或者少字段值。
mysql> insert into teacher (sid,sname) values(3,'李四');-- 向指定表中插入部分字段的数据

查询表中的所有数据（teacher是表名）：select * from teacher;

  4.2、删除数据

mysql> delete from teacher; -- 删除所有数据（建议少用，以免误删数据）
mysql> delete from teacher where sid = 1; -- 带条件删除数据（推荐使用）

  4.3、修改数据

mysql> update teacher set sage=20; -- 修改所有的数据(建议少用，以免误改其他不该改的数据)
mysql> update teacher set sage = 18 where sid = 2;  -- 带条件的修改（推荐使用）
mysql> update teacher set sage = 30,sname = 'java' where sid = 3; -- 带条件，修改多个字段，注意set 字段名 = 值,字段名 = 值,…

  4.4、查询数据（重点）

    4.4.1、查询所有列

mysql> select * from teacher; -- 查询指定表中所有的数据

    4.4.2、查询指定列

mysql> select sname,sex from teacher; -- 查询指定列

     

    4.4.3、查询时添加常量列

mysql> select sname,sage,sex,'优秀教师' as '评级' from teacher; -- 在查询时 添加一个常量列

    4.4.4、查询时指定别名

mysql> select sid as '编号',sname as '姓名',sex as '性别',sage as '年龄' from teacher; -- 用as给每个字段取个别名

    4.4.5、查询时合并列

mysql> select sid,sname,sex,(monthExam+yearExam) as '总考核分数' from teacher; -- 查询时将monthExam和yearExam总考核分数算出来

    4.4.6、条件查询

        逻辑条件查询： and   or

mysql> select * from teacher where sname = 'java' and yearExam = 90; -- 查询yearExam考核分数为90，同时满足名字为Java的老师

mysql> select * from teacher where sid = 3 or sname = '王五'; -- 查询id为3，或者名字为“王五”的老师

        比较条件查询：  >，<，>=，<=，<> [不等于]，between and [在....之间，等价于 >= 且 <=]

mysql> select * from teacher where monthExam >= 80 and yearExam > 89; -- 查询monthExam的考核分数大于等于80且yearExam考核分数大于89

-- 查询monthExam的分数在78和79之间的人
mysql> select * from teacher where monthExam >= 78 and monthExam <= 79;
-- 或者
mysql> select * from teacher where monthExam between 78 and 79;

-- 查询性别不为男性的人
mysql> select * from teacher where sex <> '男'; -- 正常情况下，应该有3条数据（sid为2,4,5），值为null的数据查不出来，是因为NULL不能直接用算术运算符进行比较值

    4.4.7、判断空条件（NULL  vs  空字符串）

mysql> select * from teacher where sex is null; -- 查询性别为NULL的所有老师

mysql> select * from teacher where sex =  ''; -- 查询性别为空字符串的老师

mysql> select * from teacher where sex is null or sex = ''; -- 查询性别为空的老师

    4.4.8、模糊查询

-- 百分号 % 表示任意个字符，下划线 _ 表示一个字符
mysql> select * from teacher where sname like '张%';
mysql> select * from teacher where sname like '张_';
mysql> select * from teacher where sname like '_三';

    4.4.9、查询时去除重复后的记录

mysql> select distinct sage from teacher;
mysql> select distinct(sex) from teacher;

         

如果想同时查询两个字段或多个字段去重后的记录，不能直接 select distinct sage,sex from teacher; 这样查询，因为mysql 会认为要过滤掉sage和sex两个字段都重复的记录。而单个字段重复 则去除不了。

但是，可以使用 count(distinct sage,sex) 查询不重复的记录条数。

    4.4.10、聚合查询  【常用的聚合函数：sum()求和、avg()平均值、max()最大值、min()最小值、count()记录条数】

mysql> select sum(monthExam) as '所有老师月考核分数' from teacher; -- 查询所有老师月考核分数
mysql> select sum(monthExam) as '所有老师月考核总分数',(select sum(yearExam) from teacher) as '所有老师年考核总分数' from teacher; -- 同时查询所有老师月考核与年考核的各总分数

mysql> select avg(monthExam) as '所有老师月考核分的平均值' from teacher; -- 查询所有老师月考核分数的平均值

mysql> select max(monthExam) as '月考核最高分' from teacher; -- 查询所有老师中月考核最高的分数
mysql> select sname,max(monthExam) as '月考核分数最高的老师' from teacher where monthExam = (select max(monthExam) from teacher); -- 查询月考核分数最高的老师

mysql> select min(monthExam) as '月考核最小分' from teacher; -- 查询所有老师中月考核最小的分数

mysql> select count(*) as '本表所有记录条数' from teacher; -- 可用作查询本校有多位（所有）老师

注意：count()函数统计数量时，会过滤掉空白（什么都没有）的数据
mysql> select count(sex) from teacher;

    4.4.11、分页查询 ( limit 起始行,每页显示多少条数据)  
    Java代码实现的分页：https://www.cnblogs.com/dshore123/p/10597898.html

使用分页查询，查询当前页的数据。语法：select * from 表名 limit (当前页-1)*每页显示的数据条数,每页显示多少条数据;

SELECT * FROM teacher LIMIT 0,3; -- 第一页（1-1）*3,3  起始行从0开始

SELECT * FROM teacher LIMIT 3,3; -- 第二页（2-1）*3,3

SELECT * FROM teacher LIMIT 6,3; -- 第三页（3-1）*3,3

    4.4.12、排序（order by）

 语法：order by 字段名 asc/desc
 asc：顺序，正序；数值递增，字母/汉字按自然顺序递增(a-z)
 desc:倒序，反序；数值递减，字母/汉字按自然顺序递减(z-a)

SELECT * FROM teacher ORDER BY sid; -- 默认是正序排序

SELECT * FROM teacher ORDER BY sid DESC; -- 反序排序

SELECT * FROM teacher ORDER BY sname ASC; -- 正序排序

    4.4.13、分组查询(group by)

 

SELECT sex AS '性别' ,COUNT(*) AS '总人数' FROM teacher GROUP BY sex; -- 把老师按照性别分组，并且统计每组人数

-- 分组之前的条件使用where关键字，分组之后的条件使用having关键字;
SELECT sex AS '性别',COUNT(*) AS '总人数' FROM teacher GROUP BY sex HAVING COUNT(*)>3; -- 分组之后筛选；查询总人数大于3的性别

    4.4.14、关联查询（多表查询）

原表中所有数据：

         

多表查询规则：1、确定查询哪些表；2、确定查询哪些字段；3、表与表之间的连接条件(规律：连接条件数量是表的数量-1)

        4.4.14.1、交叉连接查询 (不推荐使用。因为产生笛卡尔乘积现象：n*m，如上表，即4*4，两个表均4调数据，且有些是重复记录)

SELECT emplName,deptName FROM employee,department; -- 查询员工以及所在的部门

      不推荐这样查询

        4.4.14.2、内连接查询：只有满足条件的才会显示(使用最频繁)

方法一：
SELECT emplName,deptName   -- 2、确定查询哪些字段
  FROM employee,department  -- 1、确定要查询哪些表
 WHERE employee.deptId = department.id;  -- 3、表与表之间的连接条件

方法二：
SELECT emplName,deptName 
  FROM employee INNER JOIN department  -- inner join ... on
    ON employee.deptId = department.id;

-- 使用内连接，并使用别名查询
SELECT e.emplName,d.deptName FROM employee AS e INNER JOIN department AS d ON e.deptId = d.id;
-- 或
SELECT e.emplName,d.deptName FROM employee e INNER JOIN department d ON e.deptId = d.id;

        4.4.14.3、左[外]连接查询：使用左边的表的数据去匹配右边表的数据，如果符合连接条件则显示，如果不符合连接条件则显示null

 (注意：左连接:左表的数据一定会完全显示)

    SELECT  d.deptName,e.emplName  
       FROM department d LEFT OUTER JOIN employee e
       ON d.id = e.deptId; -- 左连接  left outer join ... on

        4.4.14.4、右[外]连接查询：使用右边的表的数据去匹配左边表的数据,如果符合连接条件则显示,如果不符合连接条件则显示null

 (注意：右连接:右表的数据一定会完全显示)

SELECT  d.deptName,e.emplName  
  FROM employee e RIGHT OUTER JOIN department d  -- 既然是右连接，所以部门表放在了右边 department 
    ON d.id = e.deptId; -- 右连接  right outer join ... on

        4.4.14.5、自连接查询

SELECT e.emplName,m.emplName  
  FROM employee e LEFT OUTER JOIN employee m  -- 左连接
    ON e.managerId = m.id; -- 查询员工及其上司

5、附录（本文的所有sql操作语句集）

-- ***********************************************对数据库的操作***********************************************
SHOW DATABASES; -- 查询所有数据库
CREATE DATABASE school; -- 创建数据库
DROP DATABASE school; -- 删除数据库
SHOW CREATE DATABASE school; -- 查看数据库的默认字符集
ALTER DATABASE school DEFAULT CHARACTER SET gbk; -- 修改字符集（修改了编码）

-- ***********************************************对表的操作***********************************************
CREATE DATABASE school; -- 1、创建数据库
USE school; -- 2、使用school数据库

CREATE TABLE student( -- 3、创建表
 sid INT, 
 sname VARCHAR(20), -- sname:字段名，varchar()：字段类型，20:该字段的长度
 sage INT
 );
 
SHOW TABLES; -- 查看所有的表
DESC student; -- 查询指定的表的结构
DROP TABLE student; -- 删除指定的表

ALTER TABLE student ADD COLUMN sex VARCHAR(2); -- 添加一个sex字段
ALTER TABLE student DROP COLUMN sex; -- 删除sex字段
ALTER TABLE student MODIFY COLUMN sage INT; -- 修改sage字段类型varchar(10)为int类型
ALTER TABLE student CHANGE COLUMN sage sage2 INT; -- 修改sage字段的名称为sage2
ALTER TABLE student RENAME TO teacher; -- 修改表的名称
DESC teacher; -- 查询指定的表的结构

-- ***********************************************对表里面的数据的操作***********************************************
INSERT INTO teacher VALUES(2,'张三',23); -- 向指定表中添加数据
INSERT INTO teacher(sid,sname) VALUES(3,'李四');-- 向指定表中插入部分字段的数据

DELETE FROM teacher; -- 删除所有数据（建议少用，以免误删数据）
DELETE FROM teacher WHERE sid = 1; -- 带条件删除数据（推荐使用）

UPDATE teacher SET sage = 20; -- 修改所有的数据（建议少用，以免误该数据）
UPDATE teacher SET sage = 18 WHERE sid = 2; -- 带条件的修改（推荐使用）
UPDATE teacher SET sage = 30,sname = 'java' WHERE sid = 3; -- 带条件，修改多个字段，注意set 字段名 = 值,字段名 = 值,…

SELECT * FROM teacher; -- 查询指定表里面的数据
SELECT sname,sex FROM teacher; -- 查询指定列
SELECT sid AS '编号',sname AS '姓名',sex AS '性别',sage AS '年龄' FROM teacher; -- 查询时指定别名
SELECT sname,sage,sex,'优秀教师' AS '评级' FROM teacher; -- 在查询时 添加一个常量列
SELECT sid,sname,sex,(monthExam+yearExam) AS '总考核分数' FROM teacher; -- 查询时将monthExam和yearExam总考核分数算出来
SELECT * FROM teacher WHERE sname = 'java' AND yearExam = 90; -- 查询yearExam考核分数为90，同时满足名字为Java的老师
SELECT * FROM teacher WHERE sid = 3 OR sname = '王五'; -- 查询id为3，或名字为“王五”的老师
SELECT * FROM teacher WHERE monthExam >= 80 AND yearExam > 89; -- 查询monthExam的考核分数大于等于80且yearExam考核分数大于89
SELECT * FROM teacher WHERE monthExam >= 78 AND monthExam <= 79; -- 查询monthExam的分数在78和79之间的人
SELECT * FROM teacher WHERE monthExam BETWEEN 78 AND 79;; -- 查询monthExam的分数在78和79之间的人
SELECT * FROM teacher WHERE sex <> '男'; -- 查询性别不为男性的人。（正常情况下，应该有3条数据，值为null的数据查不出来，是因为NULL不能直接用算术运算符进行比较值）
SELECT * FROM teacher WHERE sex IS NULL; -- 查询性别为NULL的所有老师
SELECT * FROM teacher WHERE sex =  ''; -- 查询性别为空字符串的老师
SELECT * FROM teacher WHERE sex IS NULL OR sex = ''; -- 查询性别为空的老师
-- 聚合查询
SELECT SUM(monthExam) AS '所有老师月考核分数' FROM teacher; -- 查询所有老师月考核总分数
SELECT SUM(monthExam) AS '所有老师月考核分数',(SELECT SUM(yearExam) FROM teacher) AS '所有老师年考核分数' FROM teacher; -- 同时查询所有老师月考核与年考核的各总分数
SELECT AVG(monthExam) AS '所有老师月考核分的平均值' FROM teacher; -- 查询所有老师月考核分数的平均值
SELECT MAX(monthExam) AS '月考核最高分' FROM teacher; -- 查询所有老师中月考核最高的分数
SELECT sname,MAX(monthExam) AS '月考核分数最高的老师' FROM teacher WHERE monthExam = (SELECT MAX(monthExam) FROM teacher); -- 查询月考核分数最高的老师
SELECT MIN(monthExam) AS '月考核最小分' FROM teacher; -- 查询所有老师中月考核最小的分数
SELECT COUNT(sex) FROM teacher; -- 注意：count()函数统计数量时，会过滤掉（不包含)含有null的数据
-- 分页查询（limit 起始行,查询几行）
-- 分页查询当前的数据：select * from teacher limit (当前页-1)*每页显示的条数,每页显示多少条数据;
SELECT * FROM teacher LIMIT 0,3; -- 第一页（1-1）*3，起始行从0开始
SELECT * FROM teacher LIMIT 3,3; -- 第二页（2-1）*3
SELECT * FROM teacher LIMIT 6,3; -- 第三页（3-1）*3
-- 语法：order by 字段 asc/desc
-- asc：顺序，正序；数值递增，字母/汉字按自然顺序递增(a-z)
-- desc:倒序，反序；数值递减，字母/汉字按自然顺序递减(z-a)
SELECT * FROM teacher ORDER BY sid; -- 默认是正序排序
SELECT * FROM teacher ORDER BY sid DESC; -- 反序排序
SELECT * FROM teacher ORDER BY sname ASC; -- 正序排序
-- 分组查询(group by)
SELECT sex AS '性别' ,COUNT(*) AS '总人数' FROM teacher GROUP BY sex; -- 把老师按照性别分组，并且统计每组人数
-- 分组之后筛选
-- 分组之前的条件使用where关键字，分组之后的条件使用having关键字;
SELECT sex AS '性别',COUNT(*) AS '总人数' FROM teacher GROUP BY sex HAVING COUNT(*)>3; -- 分组之后筛选；查询总人数大于3的性别
SELECT * FROM teacher; 

-- 关联查询(多表查询)
-- 主表(部门表)
CREATE TABLE department
(
 id INT PRIMARY KEY,
 deptName VARCHAR(32)
); -- 先创建主表，再创建从表
-- 从表/副表（员工表）
CREATE TABLE employee
(
 id INT PRIMARY KEY,
 emplName VARCHAR(32),
 deptId INT, -- 部门id
 managerId INT, -- 上级主管id                                                      级联修改           级联删除
 CONSTRAINT employee_department_fk FOREIGN KEY(deptId) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE
--                 外键名称                      外键              参考表(参考的字段)
); -- 注意：级联操作必须在外键基础上使用

INSERT INTO department VALUES(1,'软件开发部');
INSERT INTO department VALUES(2,'软件测试部');
INSERT INTO department VALUES(3,'销售部');
INSERT INTO department VALUES(4,'总经办');
SELECT * FROM department;
INSERT INTO employee(id,emplName,deptId) VALUES(1,'张三',2);
INSERT INTO employee VALUES(2,'李四',3,1);
INSERT INTO employee VALUES(3,'王五',1,2);
INSERT INTO employee VALUES(4,'赵六',2,3);
SELECT * FROM employee;

-- 多表查询规则：1>确定查询哪些表；2>确定查询哪些字段；3>表与表之间的连接条件(规律：连接条件数量是表的数量-1)
-- 交叉连接查询(不推荐使用.产生笛卡尔乘积现象：n*m，即4*4，两个表均4调数据，且有些是重复记录)
SELECT emplName,deptName FROM employee,department; -- 查询员工以及所在的部门
-- 内连接查询：只有满足条件的才会显示(使用最频繁)
SELECT emplName,deptName   -- 2、确定查询哪些字段
  FROM employee,department  -- 1、确定要查询哪些表
 WHERE employee.deptId = department.id;  -- 3、表与表之间的连接条件
-- 内连接的另一种语法
SELECT emplName,deptName 
  FROM employee INNER JOIN department 
    ON employee.deptId = department.id;
-- 使用别名
SELECT e.emplName,d.deptName FROM employee AS e INNER JOIN department AS d ON e.deptId = d.id;
-- 或
SELECT e.emplName,d.deptName FROM employee e INNER JOIN department d ON e.deptId = d.id;

-- 左[外]连接查询：使用左边的表的数据去匹配右边表的数据，如果符合连接条件则显示，如果不符合连接条件则显示null
-- (注意：左连接:左表的数据一定会完全显示)
SELECT  d.deptName,e.emplName  
  FROM department d LEFT OUTER JOIN employee e
    ON d.id = e.deptId;
-- 右[外]连接查询：使用右边的表的数据去匹配左边表的数据,如果符合连接条件则显示,如果不符合连接条件则显示null
-- (注意：右连接:右表的数据一定会完全显示)
SELECT  d.deptName,e.emplName  
  FROM employee e RIGHT OUTER JOIN department d
    ON d.id = e.deptId;

-- 自连接查询
SELECT e.emplName,m.emplName  
  FROM employee e LEFT OUTER JOIN employee m
    ON e.managerId = m.id; -- 查询员工及其上司
    

原创作者：DSHORE

作者主页：http://www.cnblogs.com/dshore123/

原文出自：https://www.cnblogs.com/dshore123/p/10544241.html

版权声明：欢迎转载，转载务必说明出处。（如果本文对您有帮助，可以点击一下右下角的 推荐，或评论，谢谢！）