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;

1 mysql> use school; -- 使用school数据库
2 
3 mysql> create table student( -- 创建student表
4     -> sid int,
5     -> sname varchar(20),
6     -> sage int
7     -> );

如下图所示：

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、添加数据

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

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

4.2、删除数据

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

4.3、修改数据

1 mysql> update teacher set sage=20; -- 修改所有的数据(建议少用，以免误改其他不该改的数据)
2 mysql> update teacher set sage = 18 where sid = 2;  -- 带条件的修改（推荐使用）
3 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

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

1 -- 查询性别不为男性的人
2 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、模糊查询

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

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

1 mysql> select distinct sage from teacher;
2 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()记录条数】

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

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

1 mysql> select max(monthExam) as '月考核最高分' from teacher; -- 查询所有老师中月考核最高的分数
2 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; -- 可用作查询本校有多位（所有）老师

1 注意：count()函数统计数量时，会过滤掉空白（什么都没有）的数据
2 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; -- 把老师按照性别分组，并且统计每组人数

1 -- 分组之前的条件使用where关键字，分组之后的条件使用having关键字;
2 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、内连接查询：只有满足条件的才会显示(使用最频繁)

1 方法一：
2 SELECT emplName,deptName   -- 2、确定查询哪些字段
3   FROM employee,department  -- 1、确定要查询哪些表
4  WHERE employee.deptId = department.id;  -- 3、表与表之间的连接条件
5 
6 方法二：
7 SELECT emplName,deptName 
8   FROM employee INNER JOIN department  -- inner join ... on
9     ON employee.deptId = department.id;

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

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

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

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

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

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

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

4.4.14.5、自连接查询

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

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

  1 -- ***********************************************对数据库的操作***********************************************
  2 SHOW DATABASES; -- 查询所有数据库
  3 CREATE DATABASE school; -- 创建数据库
  4 DROP DATABASE school; -- 删除数据库
  5 SHOW CREATE DATABASE school; -- 查看数据库的默认字符集
  6 ALTER DATABASE school DEFAULT CHARACTER SET gbk; -- 修改字符集（修改了编码）
  7 
  8 -- ***********************************************对表的操作***********************************************
  9 CREATE DATABASE school; -- 1、创建数据库
 10 USE school; -- 2、使用school数据库
 11 
 12 CREATE TABLE student( -- 3、创建表
 13  sid INT, 
 14  sname VARCHAR(20), -- sname:字段名，varchar()：字段类型，20:该字段的长度
 15  sage INT
 16  );
 17  
 18 SHOW TABLES; -- 查看所有的表
 19 DESC student; -- 查询指定的表的结构
 20 DROP TABLE student; -- 删除指定的表
 21 
 22 ALTER TABLE student ADD COLUMN sex VARCHAR(2); -- 添加一个sex字段
 23 ALTER TABLE student DROP COLUMN sex; -- 删除sex字段
 24 ALTER TABLE student MODIFY COLUMN sage INT; -- 修改sage字段类型varchar(10)为int类型
 25 ALTER TABLE student CHANGE COLUMN sage sage2 INT; -- 修改sage字段的名称为sage2
 26 ALTER TABLE student RENAME TO teacher; -- 修改表的名称
 27 DESC teacher; -- 查询指定的表的结构
 28 
 29 -- ***********************************************对表里面的数据的操作***********************************************
 30 INSERT INTO teacher VALUES(2,'张三',23); -- 向指定表中添加数据
 31 INSERT INTO teacher(sid,sname) VALUES(3,'李四');-- 向指定表中插入部分字段的数据
 32 
 33 DELETE FROM teacher; -- 删除所有数据（建议少用，以免误删数据）
 34 DELETE FROM teacher WHERE sid = 1; -- 带条件删除数据（推荐使用）
 35 
 36 UPDATE teacher SET sage = 20; -- 修改所有的数据（建议少用，以免误该数据）
 37 UPDATE teacher SET sage = 18 WHERE sid = 2; -- 带条件的修改（推荐使用）
 38 UPDATE teacher SET sage = 30,sname = 'java' WHERE sid = 3; -- 带条件，修改多个字段，注意set 字段名 = 值,字段名 = 值,…
 39 
 40 SELECT * FROM teacher; -- 查询指定表里面的数据
 41 SELECT sname,sex FROM teacher; -- 查询指定列
 42 SELECT sid AS '编号',sname AS '姓名',sex AS '性别',sage AS '年龄' FROM teacher; -- 查询时指定别名
 43 SELECT sname,sage,sex,'优秀教师' AS '评级' FROM teacher; -- 在查询时 添加一个常量列
 44 SELECT sid,sname,sex,(monthExam+yearExam) AS '总考核分数' FROM teacher; -- 查询时将monthExam和yearExam总考核分数算出来
 45 SELECT * FROM teacher WHERE sname = 'java' AND yearExam = 90; -- 查询yearExam考核分数为90，同时满足名字为Java的老师
 46 SELECT * FROM teacher WHERE sid = 3 OR sname = '王五'; -- 查询id为3，或名字为“王五”的老师
 47 SELECT * FROM teacher WHERE monthExam >= 80 AND yearExam > 89; -- 查询monthExam的考核分数大于等于80且yearExam考核分数大于89
 48 SELECT * FROM teacher WHERE monthExam >= 78 AND monthExam <= 79; -- 查询monthExam的分数在78和79之间的人
 49 SELECT * FROM teacher WHERE monthExam BETWEEN 78 AND 79;; -- 查询monthExam的分数在78和79之间的人
 50 SELECT * FROM teacher WHERE sex <> '男'; -- 查询性别不为男性的人。（正常情况下，应该有3条数据，值为null的数据查不出来，是因为NULL不能直接用算术运算符进行比较值）
 51 SELECT * FROM teacher WHERE sex IS NULL; -- 查询性别为NULL的所有老师
 52 SELECT * FROM teacher WHERE sex =  ''; -- 查询性别为空字符串的老师
 53 SELECT * FROM teacher WHERE sex IS NULL OR sex = ''; -- 查询性别为空的老师
 54 -- 聚合查询
 55 SELECT SUM(monthExam) AS '所有老师月考核分数' FROM teacher; -- 查询所有老师月考核总分数
 56 SELECT SUM(monthExam) AS '所有老师月考核分数',(SELECT SUM(yearExam) FROM teacher) AS '所有老师年考核分数' FROM teacher; -- 同时查询所有老师月考核与年考核的各总分数
 57 SELECT AVG(monthExam) AS '所有老师月考核分的平均值' FROM teacher; -- 查询所有老师月考核分数的平均值
 58 SELECT MAX(monthExam) AS '月考核最高分' FROM teacher; -- 查询所有老师中月考核最高的分数
 59 SELECT sname,MAX(monthExam) AS '月考核分数最高的老师' FROM teacher WHERE monthExam = (SELECT MAX(monthExam) FROM teacher); -- 查询月考核分数最高的老师
 60 SELECT MIN(monthExam) AS '月考核最小分' FROM teacher; -- 查询所有老师中月考核最小的分数
 61 SELECT COUNT(sex) FROM teacher; -- 注意：count()函数统计数量时，会过滤掉（不包含)含有null的数据
 62 -- 分页查询（limit 起始行,查询几行）
 63 -- 分页查询当前的数据：select * from teacher limit (当前页-1)*每页显示的条数,每页显示多少条数据;
 64 SELECT * FROM teacher LIMIT 0,3; -- 第一页（1-1）*3，起始行从0开始
 65 SELECT * FROM teacher LIMIT 3,3; -- 第二页（2-1）*3
 66 SELECT * FROM teacher LIMIT 6,3; -- 第三页（3-1）*3
 67 -- 语法：order by 字段 asc/desc
 68 -- asc：顺序，正序；数值递增，字母/汉字按自然顺序递增(a-z)
 69 -- desc:倒序，反序；数值递减，字母/汉字按自然顺序递减(z-a)
 70 SELECT * FROM teacher ORDER BY sid; -- 默认是正序排序
 71 SELECT * FROM teacher ORDER BY sid DESC; -- 反序排序
 72 SELECT * FROM teacher ORDER BY sname ASC; -- 正序排序
 73 -- 分组查询(group by)
 74 SELECT sex AS '性别' ,COUNT(*) AS '总人数' FROM teacher GROUP BY sex; -- 把老师按照性别分组，并且统计每组人数
 75 -- 分组之后筛选
 76 -- 分组之前的条件使用where关键字，分组之后的条件使用having关键字;
 77 SELECT sex AS '性别',COUNT(*) AS '总人数' FROM teacher GROUP BY sex HAVING COUNT(*)>3; -- 分组之后筛选；查询总人数大于3的性别
 78 SELECT * FROM teacher; 
 79 
 80 -- 关联查询(多表查询)
 81 -- 主表(部门表)
 82 CREATE TABLE department
 83 (
 84  id INT PRIMARY KEY,
 85  deptName VARCHAR(32)
 86 ); -- 先创建主表，再创建从表
 87 -- 从表/副表（员工表）
 88 CREATE TABLE employee
 89 (
 90  id INT PRIMARY KEY,
 91  emplName VARCHAR(32),
 92  deptId INT, -- 部门id
 93  managerId INT, -- 上级主管id                                                      级联修改           级联删除
 94  CONSTRAINT employee_department_fk FOREIGN KEY(deptId) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE
 95 --                 外键名称                      外键              参考表(参考的字段)
 96 ); -- 注意：级联操作必须在外键基础上使用
 97 
 98 INSERT INTO department VALUES(1,'软件开发部');
 99 INSERT INTO department VALUES(2,'软件测试部');
100 INSERT INTO department VALUES(3,'销售部');
101 INSERT INTO department VALUES(4,'总经办');
102 SELECT * FROM department;
103 INSERT INTO employee(id,emplName,deptId) VALUES(1,'张三',2);
104 INSERT INTO employee VALUES(2,'李四',3,1);
105 INSERT INTO employee VALUES(3,'王五',1,2);
106 INSERT INTO employee VALUES(4,'赵六',2,3);
107 SELECT * FROM employee;
108 
109 -- 多表查询规则：1>确定查询哪些表；2>确定查询哪些字段；3>表与表之间的连接条件(规律：连接条件数量是表的数量-1)
110 -- 交叉连接查询(不推荐使用.产生笛卡尔乘积现象：n*m，即4*4，两个表均4调数据，且有些是重复记录)
111 SELECT emplName,deptName FROM employee,department; -- 查询员工以及所在的部门
112 -- 内连接查询：只有满足条件的才会显示(使用最频繁)
113 SELECT emplName,deptName   -- 2、确定查询哪些字段
114   FROM employee,department  -- 1、确定要查询哪些表
115  WHERE employee.deptId = department.id;  -- 3、表与表之间的连接条件
116 -- 内连接的另一种语法
117 SELECT emplName,deptName 
118   FROM employee INNER JOIN department 
119     ON employee.deptId = department.id;
120 -- 使用别名
121 SELECT e.emplName,d.deptName FROM employee AS e INNER JOIN department AS d ON e.deptId = d.id;
122 -- 或
123 SELECT e.emplName,d.deptName FROM employee e INNER JOIN department d ON e.deptId = d.id;
124 
125 -- 左[外]连接查询：使用左边的表的数据去匹配右边表的数据，如果符合连接条件则显示，如果不符合连接条件则显示null
126 -- (注意：左连接:左表的数据一定会完全显示)
127 SELECT  d.deptName,e.emplName  
128   FROM department d LEFT OUTER JOIN employee e
129     ON d.id = e.deptId;
130 -- 右[外]连接查询：使用右边的表的数据去匹配左边表的数据,如果符合连接条件则显示,如果不符合连接条件则显示null
131 -- (注意：右连接:右表的数据一定会完全显示)
132 SELECT  d.deptName,e.emplName  
133   FROM employee e RIGHT OUTER JOIN department d
134     ON d.id = e.deptId;
135 
136 -- 自连接查询
137 SELECT e.emplName,m.emplName  
138   FROM employee e LEFT OUTER JOIN employee m
139     ON e.managerId = m.id; -- 查询员工及其上司
140

原创作者：DSHORE

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

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

posted @ 2019-03-16 20:40 DSHORE 阅读(737) 评论(0) 编辑收藏举报

刷新页面返回顶部

DSHORE

攻城狮在东莞相亲，成功后即将上线！

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

1、概述

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

1.2、命令行连接MySQL数据库

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

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

2.1、查询所有的数据库

2.2、创建数据库

2.3、删除数据库

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

2.5、修改字符集（编码）

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

3.1、创建表

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

3.3、查询表的结构

3.4、删除表

3.5、修改表

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

4.1、添加数据

4.2、删除数据

4.3、修改数据

4.4、查询数据（重点）

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

公告

DSHORE

攻城狮在东莞相亲，成功后即将上线！

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

1、概述

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

1.2、命令行连接MySQL数据库

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

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

2.1、查询所有的数据库

2.2、创建数据库

2.3、删除数据库

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

2.5、修改字符集（编码）

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

3.1、创建表

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

3.3、查询表的结构

3.4、删除表

3.5、修改表

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

4.1、添加数据

4.2、删除数据

4.3、修改数据

4.4、查询数据（重点）

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

公告

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