目录
数据的存储方式
一般分为:内存、文件、数据库。
在Java中创建对象:Student s = new Student(1, "张三") 这个对象数据是存在内存中的。
Java的IO流是将数据保存在文件中。
那么对于数据库,以下有和上述两个方式的比较
数据库的基本概念
(1)DataBase简称DB,是用于存储和管理数据的仓库。
(2)本质上是一个文件系统,还是以文件的方式存在服务器的电脑上的。
(3)所有的关系型数据库都可以使用通用的SQL(结构化查询语言(Structured Query Language)简称SQL,是一种特殊目的的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统)语句进行管理 DBMS DataBase Management System。
常见的数据库和特点
MySQL:开源免费的数据库,小型的数据库,已经被Oracle收购了。MySQL6.x版本也开始收费。后来Sun公司收购了MySQL,而Sun公司又被Oracle收购。
Oracle:收费的大型数据库,Oracle公司的产品。
DB2 :IBM公司的数据库产品,收费的。常应用在银行系统中。
SQL Server:MicroSoft 公司收费的中型的数据库。C#、.net等语言常使用。
SQLite: 嵌入式的小型数据库,应用在手机端,如:Android。
MySQL数据库的安装
下载地址是官网,安装可参考免安装版的Mysql
MySQL数据库的卸载
(1)停止Window的MySQL服务。位置在“服务”,停止MySQL后台服务。
(2)卸载MySQL安装程序。位置在“程序与功能”,卸载MySQL程序。
(3)删除MySQL安装目录下的所有文件。
(4)删除C盘ProgramData目录中关于MySQL的目录。路径为:C:\ProgramData\MySQL
MySQL数据库的启动与登录
启动方式有两种:
(1)通过服务的方式自动启动:在“服务”找到MySQL右键启动或者停止。
(2)手动启动:终端(也称为控制台)输入,
windows: 启动命令net start mysql,停止命令net stop mysql
ubuntu: sudo service mysql start, sudo service mysql stop, sudo service mysql status, 包含的全部的命令是:start | stop | restart | reload | force-reload | status
centos8:systemctl start mysqld.service,包含stop、restart
控制台连接数据库(命令行连接本地数据库和远程数据库)
MySQL是一个需要账户名密码登录的数据库,登陆后使用,它提供了一个默认的root账号,使用安装时设置的密码即可登录。
登录MySQL和远程数据库MySQL:
# 格式一:
mysql -u用户名 -p密码 # u和p后面没有空格,前输入方式,密码可见
mysql -u用户名 -p # 后输入密码方式,密码不可见
# 格式二:完整的简写格式,这里可以指定host即远程数据库的ip地址,这样就能登上去
mysql -hip地址 -u用户名 -p密码 -P端口号 # 其中-h紧跟ip地址,-P大写是端口,如果端口号是3306可省略不写
# 格式三:完整格式
mysql --host=ip地址 --user=用户名 --password=密码 --port=3306
特别注意的是,cmd命令行,只有对password字段,如果有密码在命令行中-p和密码之间不能有空格;对于powershell,除了要满足cmd的条件之外,还必须满足host字段,-h和ip地址之间必须要有空格。否则将会报错!
# cmd允许的命令
mysql -ujupy -h192.168.153.129 -P3306 -p1234 # 表示输入了密码了
mysql -u jupy -h 192.168.153.129 -P 3306 -p # 需要输入密码,注意此处不能-p 1234,密码特殊,不能加空格隔开,其他都可以,可加可不加空格
mysql -u jupy -h 192.168.153.129 -P 3306 -p1234
# powershell允许的命令
允许字段和值之间有空格或者没有空格,特殊的在两个字段:对于host,-h必需要和ip地址用空格隔开; 对于password,如果命令中含有密码,-p必须和密码相连,不能存在空格。
mysql -u jupy -h 192.168.153.129 -p -P 3306 # 需要输密码
mysql -u jupy -h 192.168.153.129 -p1234 -P 3306 # 不需要输密码,-p一定不能有空格
mysql -ujupy -h 192.168.153.129 -p1234 -P3306 # -h一定要有空格
查看当前用户和创建用户:
-- 查看当前用户:有三种指令
select current_user();
select current_user;
select user();
-- 查mysql数据库的user表得到所有用户信息:只有root用户才有权限操作该命令。
-- 20220810 mysql8.0没有password字段
select user,host,password,plugin from mysql.user;
-- 创建用户
create user 'jupy'@'localhost' identified by '123456';
create user 'jupy'@'192.168.1.%' identified by '123456'; -- 匹配192.168.1.xxx的主机访问
create user 'jupy'@'%' identified by '123456'; -- 通配符,匹配任何ip主机都可访问
create user 'jupy'@'localhost'; -- 不设密码
-- 删除用户
drop user 'jupy'@'host';
-- 修改密码
set password = password("123"); -- 修改当前用户的密码
set password for 'jupy'@'host' = password('newpassword'); -- 更改用户密码
alter user user() identified by '1234' -- user()表示当前用户,其他用户可以如'jupy'@'%'替换当前用户位置
--修改host项
--(1)改表法 首先进入root用户 mysql -u root -p
update mysql.user set host = '%' where user = 'root'; -- 更改user表,这里是对root的host字段进行修改,因为默认是localhost,外网就不能访问了
flush privileges; -- 刷新
select host, user from user; --查看
--(2)授权法
-- jupy用户使用默认的密码可以从任何主机连接到mysql服务器
grant all privileges on *.* to 'jupy'@'%' with grant option;
-- jupy用户使用新设置的密码只允许从ip为192.168.1.3的主机连接到mysql服务器
GRANT ALL PRIVILEGES ON *.* TO 'jupy'@'192.168.1.101' IDENTIFIED BY 'newpassword' WITH GRANT OPTION;
flush privileges;
-- 查看权限
SELECT * FROM mysql.user WHERE user='jupy'\G -- \G使每个字段打印到单独的行,也有’;'的作用
退出MySQL:quit或exit
Navicat图形化工具----客户端
官方操作手册
“Navicat”是一套可创建多个连接的数据库管理工具,用以方便管理MySQL、Oracle、PostgreSQL、SQLite、SQL Server、MariaDB 和/或MongoDB 等不同类型的数据库,并支持管理某些云数据库,例如阿里云、腾讯云。Navicat 的功能足以符合专业开发人员的所有需求,但是对数据库服务器初学者来说又相当容易学习。Navicat 的用户界面(GUI) 设计良好,让你以安全且简单的方法创建、组织、访问和共享信息。
Navicat 提供了三种平台的版本-Microsoft Windows、macOS 和Linux。它可以让用户连接到本地或远程服务器,并提供一些实用的数据库工具以协助用户管理数据,包括Navicat Cloud 协同合作、数据建模、数据传输、数据同步、结构同步、导入、导出、备份、还原、图表和自动运行。
数据库管理系统(DBMS)介绍、数据库、表之间的关系
数据库管理系统(DataBase Management System,DBMS):指一种操作和管理数据库的大型软件,用于建立、使用和维护数据库,对数据库进行统一管理和控制,以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中表内的数据。
数据库管理程序(DBMS)可以管理多个数据库,一般开发人员会针对每一个应用创建一个数据库。为保存应用中实体的数据,一般会在数据库创建多个表,以保存程序中实体User的数据。
即:一个数据库服务器包含多个库;一个数据库包含多张表;一张表包含多条记录。
SQL的相关概念、作用,以及语句4大分类
定义:Structured Query Language 结构化查询语言
作用:1)是一种所有关系型数据库的查询规范,不同的数据库都支持。
2)通用的数据库操作语言,可以用在不同的数据库中。
3)不同的数据库SQL语句有一些区别。
语句分类:1)Data Definition Language (DDL数据定义语言) 如:建库,建表。关键字:create、drop、alter等。
2)Data Manipulation Language(DML数据操纵语言),如:对表中的记录操作增删改。关键字:insert、delete、update等。
3)Data Query Language(DQL 数据查询语言),如:对表中的查询操作。关键字:select、where等。
4)Data Control Language(DCL 数据控制语言),如:对用户权限的设置。关键字:grant、revoke等。
MySQL的语法:1)每条语句以分号结尾,如果在Navicat中不是必须加的。
2)SQL中不区分大小写,关键字中认为大写和小写是一样的
3)3种注释。单行注释使用--,多行注释使用/* */,MySQL特有的注释方式#。
DDL操作数据库:CRUD
(1)创建数据库(C:create)
-- 1.创建数据库
CREATE DATABASE 数据库名; -- 例如 创建数据库db1 create database db1;
-- 2.判断数据库是否已经存在,不存在则创建数据库
CREATE DATABASE IF NOT EXISTS 数据库名; -- 例如 create database if not exists db2;
-- 3.创建数据库并指定字符集
CREATE DATABASE 数据库名 CHARACTER SET 字符集; -- 例如 create database db3 default character set gbk;
(2)查看数据库(R:retrieve)
-- 1.查看所有的数据库
show databases;
--2.查看某个数据库的定义信息
show create database db1;
(3)修改数据库(U:update)
--1.修改数据库默认的字符集
ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集;
--eg;将将db3数据库的字符集改成utf8
alter database db3 character set utf8;
(4)删除数据库(D:delete)
--1.删除数据库的语法
DROP DATABASE 数据库名;
--eg:删除db2数据库
drop database db2;
(5)使用数据库
--1.查看正在使用的数据库
SELECT DATABASE();
--eg:
select database();
--2.使用/切换数据库
USE 数据库名;
--eg:改变要使用的数据库切换为db4
use db4;
DDL操作表结构:CRUD
前提先使用某个数据库。要使用上面的use 数据库名;这个命令。
(1)创建表格式(C:create)
--1.创建表的格式
CREATE TABLE 表名 (
字段名1 字段类型1,
字段名2 字段类型2
);
其中的关键字说明:
(2)MySQL数据类型
常用数据类型如下:
详细的数据类型如下:
MySQL创建表举例:
--创建student表包含id,name,birthday字段
create table student (
id int, -- 整数
name varchar(20), -- 字符串
birthday date -- 生日,最后没有逗号
);
(3)查看表(R:retrieve)
--1.查看某个数据库中的所有表
SHOW TABLES;
--2.查看表结构
DESC 表名;
--3.查看创建表的SQL语句
SHOW CREATE TABLE 表名;
(4)快速创建一个表结构相同的表
--1.语法
CREATE TABLE 新表名 LIKE 旧表名;
--eg:创建一个s1的表与student结构相同
create table s1 like student;
(5)修改表结构(U:update)
--1.添加表列,使用ADD,
-- 1.1 默认是添加到最后一列
ALTER TABLE 表名 ADD 列名 类型; -- 常用插入语句
-- 1.2 添加到某一列之前,比如name列之前
ALTER TABLE 表名 ADD 列名 类型 AFTER 要插入的列名;
-- 1.3 添加作为第一列
ALTER TABLE 表名 ADD 列名 类型 FIRST;
--eg:为学生表添加一个新的字段remark,类型为varchar(20)
alter table student add remark varchar(20);
--2.修改列类型,使用MODIFY
ALTER TABLE 表名 MODIFY列名 新的类型;
--eg:将student表中的remark字段的改成varchar(100)
alter table student modify remark varchar(100);
--3.修改列名 使用CHANGE
ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;
--eg:将student表中的remark字段名改成intro,类型varchar(30)
alter table student change remark intro varchar(30);
--4.删除列 使用DROP
ALTER TABLE 表名 DROP 列名;
--eg:删除student表中的字段intro
alter table student drop intro;
--5.修改字符集 使用character set
ALTER TABLE 表名 character set 字符集;
--eg:将student2表的编码修改成gbk
alter table student2 character set gbk;
--6.修改表名
RENAME TABLE 表名 TO 新表名;
--eg:将学生表student改名成student2
rename table student to student2;
(6)删除表(D:delete)
--1.直接删除表
DROP TABLE 表名;
--eg:直接删除表s1表
drop table s1;
--2.判断表是否存在,如果存在则删除表
DROP TABLE IF EXISTS 表名;
--eg:与直接删除的区别在于如果表不存在,不删除,存在则删除
drop table if exists s1;
DML操作表中的数据
用于对表中的记录进行增删改操作。
(1)插入/添加记录
--1.插入全部字段
--1.1所有字段名都写出来
INSERT INTO 表名 (字段名1, 字段名2, 字段名3) VALUES (值1, 值2, 值3);
--1.2不写字段名
INSERT INTO 表名 VALUES (值1, 值2, 值3);
--2.插入部分字段。注意:没有添加数据的字段会使用NULL
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);
(2)对上述insert插入操作的注意事项
1)插入的数据应与字段的数据类型相同。
2)数据的大小应在列的规定范围内,例如:不能将一个长度为80的字符串加入到长度为40的列中。
3)在values中列出的数据位置必须与被加入的列的排列位置相对应。
4)字符和日期型数据应包含在单引号中。MySQL中也可以使用双引号做为分隔符。就是说,除了数字类型的值,其他类型的值都需要使用引号(单双都可)引起来。
5)不指定列或使用null,表示插入空值。
(3)蠕虫复制:将一张已经存在的表中的数据复制到另一张表中
--1.将表名2的全部列复制到表名1中
INSERT INTO 表名1 SELECT * FROM 表名2;
--2.只复制部分列
INSERT INTO 表名1(列1, 列2) SELECT 列1, 列2 FROM student;
例子如下:
--删除student2表
drop table student2;
--创建student2表,student2结构和student表结构一样
create table student2 like student;
--将student表中的数据添加到student2表中
insert into student2 select * from student;
--如果只想复制student表中name,age字段数据到student2表中,两张表都写出相应的列名
insert into student2 (name,age) select name,age from student;
--展示student2表信息
select * from student2;
(4)更新/修改表记录
--1.不带条件修改更新数据,将所有行该字段的值都修改
UPDATE 表名 SET 字段名=值;
-- eg:将所有的性别改成女
update student set sex = '女';
--2.带条件修改数据,只有符合Where条件的才修改目标字段
UPDATE 表名 SET 字段名=值 WHERE 字段名=值;
--eg:将id号为2的学生性别改成男
update student set sex='男' where id=2;
--3.修改的目标字段可以有多个,用逗号分开;条件也可以有许多个,也用逗号分开。
UPDATE 表名 SET 字段名1=值1, 字段名2=值2 WHERE 条件1, 条件2;
--eg:把id为3的学生,年龄改成26岁,address改成北京
update student set age=26, address='北京' where id=3;
(5)删除表记录
--1.不带条件删除数据,该表的所有数据将会被删除
DELETE FROM 表名; --不推荐使用。有多少条记录就会执行多少次删除操作。
--2.带条件删除数据,可以指定任何条件。只删除符合条件的记录数据
DELETE FROM 表名 WHERE 字段名=值;
--3.使用truncate删除表中所有记录。和delete区别在于,truncate相当于删除表的结构,再创建一张表。
TRUNCATE TABLE 表名; --更推荐使用,效率更高 先删除表,然后再创建一张一样的表。
DQL查询表中的数据
查询不会对数据库中的数据进行修改.只是一种显示数据的方式
--查询语法
select
字段列表
from
表名列表
where
条件列表
group by
分组字段
having
分组之后的条件
order by
排序
limit
分页限定
(1)简单查询
--1.用*表示查询所有列
SELECT * FROM 表名;
--2.查询指定列,多个列之间以逗号分隔
SELECT 字段名1, 字段名2, 字段名3, ... FROM 表名;
--eg:查询student表中的name和age列
select name,age from student;
(2)指定列的别名进行查询,使用关键字AS。
好处是:显示的时候使用新的名字,并不修改表的结构。注意:as也可以省略。
--1.对列指定别名,多个列使用逗号分隔
SELECT 字段名1 AS 别名, 字段名2 AS 别名... FROM 表名;
--eg:
select name as 姓名,age as 年龄 from student;
--2.对列和表同时指定别名。表使用别名的原因:用于多表查询操作
SELECT 字段名1 AS 别名, 字段名2 AS 别名... FROM 表名 AS 表别名;
--eg:表使用别名的原因:用于多表查询操作
select st.name as 姓名,age as 年龄 from student as st
(3)清除重复值
字段(或者多个)要完全一样才能去重。
--查询指定列并且结果不出现重复数据
SELECT DISTINCT 字段名 FROM 表名; --eg:select distinct address from student;查询学生来自于哪些地方,去掉重复的值。
(4)查询结果参与运算
注意参与运算的必须是数值类型。一般可以使用四则运算计算一些列的值,并且会自动添加列。注意:如果null参与的运算,计算结果都为null。
--1.某列数据和固定值运算
SELECT 列名1 + 固定值 FROM 表名;
--eg:给所有的数学加5分
select math+5 from student;
--2.某列数据和其他列数据参与运算
SELECT 列名1 + 列名2 FROM 表名;
--eg:
select *,(math+english) as 总成绩 from student;查询math + english的和,as可以省略:select *,(math+english) 总成绩 from student;
--3.默认值表达式
ifnull(表达式1,表达式2); --先判断表达式1是不是null,如果是则用表达式2替换。
--如果没有人考英语,null,那么直接相加的值是null,使用ifnull,变成默认值0。
select name,math,english,math+ifnull(english,0) from student;
--使用别名,省略as
select name,math 数学,english 英语,math+ifnull(english,0) 总分 from student;
(5)条件查询
为什么要条件查询:如果没有查询条件,则每次查询所有的行。实际应用中,一般要指定查询的条件。对记录进行过滤。
--条件查询的语法,流程:取出表中的每条数据,满足条件的记录就返回,不满足条件的记录不返回
SELECT 字段名 FROM 表名 WHERE 条件;
数据表准备:
--创建一个表
create table student3 (
id int, -- 编号
name varchar(20), -- 姓名
age int, -- 年龄
sex varchar(5), -- 性别
address varchar(100), -- 地址
math int, -- 数学
english int -- 英语
);
--添加了8个数据
INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES
(1,'马云',55,'男','杭州',66,78),
(2,'马化腾',45,'女','深圳',98,87),
(3,'马景涛',55,'男','香港',56,77),
(4,'柳岩',20,'女','湖南',76,65),
(5,'柳青',20,'男','湖南',86,NULL),
(6,'刘德华',57,'男','香港',99,99),
(7,'马德',22,'女','香港',99,99),
(8,'德玛西亚',18,'男','南京',56,65);
条件中使用到的运算符:
1)比较运算符
-- 查询math分数大于80分的学生
select * from student3 where math>80;
-- 查询english分数小于或等于80分的学生
select * from student3 where english <=80;
-- 查询age等于20岁的学生
select * from student3 where age = 20;
-- 查询age不等于20岁的学生,注:不等于有两种写法
select * from student3 where age <> 20;
select * from student3 where age != 20; --MySQL中也可使用!=
2)逻辑运算符
-- 查询age大于35且性别为男的学生(两个条件同时满足)
select * from student3 where age>35 and sex='男';
-- 查询age大于35或性别为男的学生(两个条件其中一个满足)
select * from student3 where age>35 or sex='男';
-- 查询id是1或3或5的学生
select * from student3 where id=1 or id=3 or id=5;
3)in关键字
--in里面的每个数据都会作为一次条件,只要满足条件的就会显示
SELECT 字段名 FROM 表名 WHERE 字段 in (数据1, 数据2...);
-- 查询id是1或3或5的学生
select * from student3 where id in(1,3,5);
-- 查询id不是1或3或5的学生
select * from student3 where id not in(1,3,5);
4)范围查询
--表示从值1到值2范围,包头又包尾
BETWEEN 值1 AND 值2
--比如:age BETWEEN 80 AND 100 相当于: age>=80 && age<=100
--查询english成绩大于等于75,且小于等于90的学生
select * from student3 where english between 75 and 90;
select * from student3 where english >= 75 and english <= 90;
5)like关键字和通配符
--LIKE表示模糊查询
SELECT * FROM 表名 WHERE 字段名 LIKE '通配符字符串';
6)IS NULL
-- 查询英语成绩为null
SELECT * FROM student WHERE english = NULL; -- 不对的。null值不能使用 = (!=) 判断
SELECT * FROM student WHERE english IS NULL;--这个才是正确的
-- 查询英语成绩不为null
SELECT * FROM student WHERE english IS NOT NULL;
MySQL通配符
--查询姓马的学生
select * from student3 where name like '马%';
--查询姓名第二个字是化的人
SELECT * FROM student WHERE NAME LIKE "_化%";
-- 查询姓名是3个字的人
SELECT * FROM student WHERE NAME LIKE '___';
-- 查询姓名中包含德的人
SELECT * FROM student WHERE NAME LIKE '%德%';
-- 查询姓马,且姓名有两个字的学生
select * from student3 where name like '马_';
遇到的问题以及解决方案
(1)DOS命令窗口操作数据乱码问题
当使用命令窗口进行SQL语句操作时中文出现乱码
产生原因:
解决:1)查看包含character开头的全局变量:命令show variables like 'character%'
2)修改client、connection、results的编码为gbk,保证和DOS命令行编码保持一致。注意:退出DOS命令行就失效了,需要每次都配置。