MySQL数据库知识点整理,保姆级教程
- MySQL数据库存储方式
- sql简介
- SQL分类
-
- DDL: 数据定义语言
- DCL: 数据控制语言
- DML:数据操控语言
- DQL: 数据查询语言
- SQL的使用
-
- SQL对数据库的操作
-
- 创建数据库
-
- 语法
- 查看数据库
- 语法
-
- 查看数据库服务器中所有数据库
- 查看某个数据库的定义信息
- 修改数据库
-
- 语法
- 删除数据库
-
- 语法
- 切换数据库/使用某一个数据库
-
- 语法
- 查看当前正在使用的数据库
-
- 语法
- SQL对数据库的表进行操作
-
- SQL创建表
- 查看表
-
- 查看某个数据库下的所有表
- 在test数据库中查看mysql数据库中的表
- 查看某个表的结构信息
- 删除表
- 修改表
-
- 添加列
- 修改列类型,长度和约束
- 删除列
- 修改列名称
- 修改表名
- 修改表的字符集
- SQL对数据库表的记录进行操作
-
- 添加表的记录
- 添加中文记录
- 修改表的记录
-
- 修改某一列的所有值
- 按条件修改数据
- 按条件修改多个列
- 删除表的记录
-
- 删除某一条记录
- 删除表中所有记录
- 查看表的记录
-
- 基本查询
-
- 查询所有学生考试成绩信息
- 查询所有学生的姓名和英语成绩
- 查询英语成绩,不显示重复值
- 查看学生的姓名和学生总成绩
- 给考试成绩综合取别名
- 条件查询
-
- 使用where子句
- 查询学生李四的成绩
- 查询名字叫李四的同学,并且英语成绩大于90分---条件查询
- 查询姓李的学生信息---模糊查询
- 查询英语成绩是69,75,89的学生信息---范围查询
- 查询英语成绩大于等于75分并且成绩为75分和89分的两位同学---and和or
- 排序查询
-
- 查询学生信息,按照语文成绩升序显示
- 查询学生信息,按照语文成绩降序显示
- 查询学生信息,先按照语文成绩降序,如果语文成绩相同,再按照英语成绩升序排序
- 查询姓李的学生的信息,按照英语成绩降序
- 分组统计查询
-
- 聚合函数的使用
-
- sum()
-
- 获取所有学生英语成绩的总和
- 获取所有学生英语成绩和数学成绩的总和
- 获取姓李的学生英语成绩总和
- 获取所有学生各科的总成绩
- count()
- 获得所有学生的总个数
- 获得姓李的学生个数
- max()
- 获取数学成绩的最高分
- min()
- 获取数学成绩最低分
- avg()
- 求语文成绩平均值
- 语法
- 按商品名称统计,每类商品所购买的次数
- 统计每一类商品所花费的总金额
- 统计总金额花费在5000以上的商品
- 按商品名称统计,统计总金额花费在5000以上的商品,并且按照总金额升序排序
- 总结
MySQL数据库存储方式
一台数据库服务器中会创建很多数据库(一个项目会创建一个数据库),在数据库中会创建很多张表(一个实体会创建一个表),在表中会有很多记录(一个对象实例回添加一条新的记录)
sql简介
- SQL全称是“结构化查询语言是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统。
- SQL同时也是数据库脚本文件的扩展名。
SQL分类
DDL: 数据定义语言
DCL: 数据控制语言
DML:数据操控语言
DQL: 数据查询语言
SQL的使用
SQL对数据库的操作
创建数据库
语法
creat database 数据库名称 [character set 字符集 collate 字符集校对规则];
中括号里面的可以省略不写,这样会采用默认的字符集和gbk校对规则
注意SQL语言写完后,要加分号
演示:
查看数据库
语法
查看数据库服务器中所有数据库
show databases;
查看某个数据库的定义信息
show create database 数据库名称;
修改数据库
语法
alter database 数据库名称 character set 字符集 collate 校对规则;
删除数据库
语法
drop database 数据库名称;
切换数据库/使用某一个数据库
语法
use 数据库名称;
查看当前正在使用的数据库
语法
select database();
SQL对数据库的表进行操作
SQL创建表
语法:
creat table 表名称(字段名称 字段类型(长度) 约束, 字段名称 字段类型(长度) 约束...)
注意:
一个实体对应一个表,一个实体属性对应一个表中的字段
java中基本变量类型对mysql中的类型:
约束:
- 约束作用: 保证数据完整性
- 单表约束分类:
- 主键约束: primary key 主键约束默认就是唯一 非空的
- 唯一约束:unique
- 非空约束: not null
建表语句:
create database web_test1;
use web_test1;
create table user(
id int primary key auto_increment,//自动增长
username varchar(20) unique,
password varchar(20) not null,
agr int,
birthday date
);
注意:在数据库中创建表的前提是要切换到要使用的数据库中
查看表
查看某个数据库下的所有表
语法:
show tables;
在test数据库中查看mysql数据库中的表
语法
show tables from mysql;
这样做,省去了切换数据库的麻烦
查看某个表的结构信息
语法:
desc 表名;
删除表
语法:
drop table 表名;
修改表
添加列
语法:
alter table 表名 add 列名 类型(长度) 约束;
修改列类型,长度和约束
语法:
alter table 表名 modify 列名 类型(长度) 约束;
删除列
语法:
alter table 表名 drop 列名;
修改列名称
语法:
alter table change 旧列名 新列名 类型长度 约束;
修改表名
语法:
rename table 表名 to 新的表名;
修改表的字符集
语法:
alter table 表名 character set 字符集;
SQL对数据库表的记录进行操作
添加表的记录
语法:
- 向表中插入某些列
insert into 表名(列名1,列名2,列名3...)values(值1,值2,值3..);
- 向表中插入所有列
insert inot 表名 values(值1,值2,值3...);
注意事项:
- 值的类型与数据库中表列类型一致
- 值的顺序与数据库中表列的顺序一致
- 值的最大长度不能超过列设置的最大长度
- 值的类型是字符串或者日期类型,使用单引号引起来
添加记录:
- 添加某几列:
insert into user (id,username,password) values(null,'aaa','123');
- 添加所有列:
insert into user values(null,'bbb','123',23,'1997-2-1');
展示添加的效果:
添加中文记录
按照上面的思路,下面的插入代码应该这样写:
insert into user values(null,'大忽悠','123',23,'2002-1-2');
直接向数据库插入中文记录会出现错误!!!
解决方法:
show variables like '%character%'; 查看数据库中与字符集相关的参数
需要将MySql数据库客户端服务器部分的字符集改为gbk
找到MySql的安装路径:my.ini文件,修改文件中[client]下的字符集
修改完后,重新启动MySql服务
修改后,再次查看数据库中与字符集相关的参数
重新再次插入中文:
修改表的记录
语法:
update 表名 set 列名=值,列名=值 [where 条件];
注意事项:
- 值的类型与列的类型一致
- 值的最大长度不超过列设置的最大长度
- 字符串类型和日期类型要添加单引号
修改某一列的所有值
update user set password="5201314";
按条件修改数据
这里字符串用双引号其实也可以
update user set password="123456" where username="大忽悠";
按条件修改多个列
update user set password="654321",agr="100" where username="aaa";
同时修改两个对象的值:
update user set password="654321",agr="100" where username="aaa" or username="大忽悠";
删除表的记录
语法:
delete from 表名 [where 条件]
注意事项:
- 删除表的记录,指的是删除表中一行记录
- 删除的时候,如果没有条件,默认删除表中所有记录
删除某一条记录
delete from user where username="aaa";
删除表中所有记录
delete from user;
另一种写法:
truncate table user;
两者写法的区别:
delete from user;
删除所有记录,属于DML语句,一条记录一条记录的删除。事务可以作用在DML语句上。
truncate table user;
删除表中所有记录,属于DDL语句,将表删除,然后重新创建一个结构一样的表。事务不能控制DDL的
查看表的记录
基本查询
语法:
select [distinct] * from 表 [条件]; //*是查看所有列,distinct去除重复记录
或者
select [distinct] 列名1,列名2... from 表 [条件];
环境准备:
create table exam(
id int primary key auto_increment,
name varchar(20),
english int,
chinese int,
math int
);
insert into exam values (null,'张三',85,74,91);
insert into exam values (null,'李四',95,90,83);
insert into exam values (null,'王五',85,84,59);
insert into exam values (null,'赵六',75,79,76);
insert into exam values (null,'田七',69,63,98);
insert into exam values (null,'李老八',89,90,83);
查询所有学生考试成绩信息
select *from exam;
查询所有学生的姓名和英语成绩
select name,english from exam;
查询英语成绩,不显示重复值
select distinct english from exam;
查看学生的姓名和学生总成绩
select name,english+chinese+math from exam;
给考试成绩综合取别名
select name,english+chinese+math as sum from exam;
或者
select name,english+chinese+math sum from exam;
条件查询
使用where子句
-
,<,>=,<=,< >(不等于),=(等于)
-
like 模糊查询
-
in 范围查询
-
条件关联::and,or,not
查询学生李四的成绩
select *from exam where name="李四";
查询名字叫李四的同学,并且英语成绩大于90分—条件查询
select *from exam where name="李四" and english>90;
查询姓李的学生信息—模糊查询
like可以进行模糊查询,在like字句中可以使用_或者%作为占位符号,_只能代表一个字符,%代表任意个字符。
like '李_' :名字必须是两个字,必须姓李
like '李%': 必须是姓李的学生,李字后面可以跟任意个字符
like '%四': 名字中以四结尾的
like '%王%': 只要名称中包含这个王字即可
代码:
select *from exam where name like '李_';
select *from exam where name like '李%';
查询英语成绩是69,75,89的学生信息—范围查询
select *from exam where english in (69,75,89);
查询英语成绩大于等于75分并且成绩为75分和89分的两位同学—and和or
select *from exam where english>=75 and (english=75 or english=89);
排序查询
语法:
order by 字段名称 asc/desc;//asc升序 desc降序 默认升序
查询学生信息,按照语文成绩升序显示
select *from exam order by chinese;
或者
select *from exam order by chinese asc;
查询学生信息,按照语文成绩降序显示
select *from exam order by chinese desc;
查询学生信息,先按照语文成绩降序,如果语文成绩相同,再按照英语成绩升序排序
逗号分隔条件:按照多个条件进行排序,总是先执行第一个条件,再执行第二个条件,即第一个条件都满足时,走第二个条件
select *from exam order by chinese desc,english asc;//按照多个条件进行排序,总是先执行第一个条件,再执行第二个条件,即第一个条件都满足时,走第二个条件
查询姓李的学生的信息,按照英语成绩降序
select *from exam where name like '李%' order by english desc;
分组统计查询
聚合函数的使用
sum()
获取所有学生英语成绩的总和
select sum(english) from exam;
或者
select sum(english) as englishSum from exam;
获取所有学生英语成绩和数学成绩的总和
select sum(english),sum(math) from exam;
获取姓李的学生英语成绩总和
select sum(english) from exam where name='李%';
获取所有学生各科的总成绩
select sum(english)+sum(math)+sum(chinese) from exam;//按照列的方式统计,先计算所有学生的英语成绩总和,然后是语文成绩总和,最后计算数学成绩总和
或者
select sum(english+math+chinese)from exam;//计算完当前学生的英语,语文,数学成绩之和,再计算下一个学生的,最后累加所有学生的成绩总和
注意:在sql中null加上任何值都为null,因此上面两种写法,在遇到null数据时,计算结果会不同
数据中存在null的值:
下面来看存在null时,两种方法得到的结果:
1.对应竖排统计:从上到下,遇到null值时就停止累加
2.横排统计:从左到右,遇到null值时就停止累加
解决方法
使用ifnull函数:
select sum(ifnull(english,0)+chinese+math) from exam;
count()
获得所有学生的总个数
select count(*) from exam;
获得姓李的学生个数
select count(*) from exam where name like "李%";
max()
获取数学成绩的最高分
select max(math) from exam;
min()
获取数学成绩最低分
select min(math) from exam;
avg()
求语文成绩平均值
select avg(chinese) from exam;
语法
group by 字段名称
环境准备:
create table orderitem
(
id int primary key auto_increment,
product varchar(20),
price double
);
insert into orderitem values (null,'电视机',2999);
insert into orderitem values (null,'电视机',2999);
insert into orderitem values (null,'洗衣机',1000);
insert into orderitem values (null,'洗衣机',1000);
insert into orderitem values (null,'洗衣机',1000);
insert into orderitem values (null,'冰箱',3999);
insert into orderitem values (null,'冰箱',3999);
insert into orderitem values (null,'空调',1999);
按商品名称统计,每类商品所购买的次数
select product,count(*) from orderitem group by product;
统计每一类商品所花费的总金额
select product,sum(price) from orderitem group by product;
统计总金额花费在5000以上的商品
where子句后面不能跟聚合函数,如果要使用带有聚合函数的条件过滤(分组后条件过滤),需要使用一个关键字having
select product,sum(price) from orderitem group by product having sum(price)>5000;
按商品名称统计,统计总金额花费在5000以上的商品,并且按照总金额升序排序
select product,sum(price) from orderitem group by product having sum(price)>5000 order by sum(price) asc;
总结
Sql操作表的基本范式:
S(select)...F(from)...W(where)...G(group by)...H(having)...O(order by);
条件顺序不能颠倒,例如*where子句不能放在group by 后面
每一部分如果不需要可以省略,例如:如果不需要where子句可以不写,以此类推