mysql
下载链接
环境
5.7 版本,压缩包安装
安装
- 下载
- 解压到自己的安装目录
- 添加环境变量
右击我的电脑——>属性——>高级——>环境变量——>系统变量——>path 里面添加mysql执行文件路径。
C:\lifei\mysql-5.7.19\bin
- 新建mysql配置文件 my.ini
- 管理员模式的cmd运行安装的命令安装数据库服务
mysqld -install
- 初始化配置文件
mysqld --initialize-insecure --user=mysql
可以看到自动创建了data目录
- 启动mysql
net start mysql
- 进入mysql 通过命令行
mysql
- 修改密码
update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';
flush privileges
- 将配置文件中,取消密码登陆关闭
然后重启服务net stop mysql;net start mysql
- 其他
sc delete mysql #清空mysql服务
sqlyog安装
视图操作
1.创建数据库
2.创建表
常见语句
CREATE DATABASE `school`CHARACTER SET utf8 COLLATE utf8_general_ci; --图形化操作见上图
create database lifei default charset utf8 COLLATE utf8_general_ci;
CREATE TABLE `school`.`student` ( `id` INT(10) NOT NULL COMMENT '学员id', `name` VARCHAR(100) NOT NULL COMMENT '学员姓名', `age` INT(3) NOT NULL COMMENT '学员年龄', PRIMARY KEY (`id`) ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;
ALTER TABLE `school`.`student` CHANGE `id` `id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '学员id'; -- id 自增
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '姓别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY(`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
show create database school; --查看创建数据库语句
show create table student; --查看创建数据表语句
CREATE TABLE IF NOT EXISTS `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY(`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT `grade`(`gradename`) VALUES ('大一'),('大二'),('大三'),('大四')
注:mysql 单号注释 -- 。mysql多行注释 /**/。
数据库引擎类型
MYISAM | INNODB | |
---|---|---|
事物的支持 | 不支持 | 支持 |
数据行锁定(单行) | 不支持 | 支持 |
外键 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间大小 | 比较小 | 较大,约为MYISAM的2倍 |
常规使用操作:
MYISAM 节约空间,速度较快
INNODB 安全性高,事物的处理,多表多用户操作
删除数据
delete from 表名
delete from 表名 where 条件
truncate table 表名 --完全清空数据库,表的结果和索引约束都不会变
delete truncate
- 相同点,都能删除数据,都不会影响表结构
- 不同点,truncate 命令会重新设置自增列,计数器规零,不会影响事物。
delete 删除的问题,重启数据库,现像
- INNODB 自增列会从一开始(存在内存中,断电即失)
- MYISAM 继续从上一个自增量开始(存在文件中的,不会丢失)
select 语句
select * from `student ` --查询全部
select `StudentNo` as 学号,`StudentName` as 姓名 from `student`;
select `StudentNo` as 学号,`StudentName` as 姓名 from `student` as S; -- 可以给字段起别名,也可以给表起别名
-- concat(a,b) 拼接查询
SELECT CONCAT('姓名:',`name`) AS 姓名 FROM `student`;
SELECT CONCAT(`name`,`pwd`) AS 记录 FROM `student`;
SELECT distinct `student` from `result`;
SELECT VERSION() -- 查看版本(函数)
SELECT 3*9+20 -- 计算(计算表达式)
SELECT @@auto_increment_increment; -- 查看自增的步长(变量)
数据库中的表达式,文本值、列、null、函数、计算表达式、系统变量...
where 操作符
SELECT * FROM student WHERE id !=2
SELECT * FROM student WHERE NOT id = 2 --和上条语句等价
模糊查询
运算符 | 讲法 | 描述 |
---|---|---|
IS NULL | a is null | 如果操作符为null,则结果为真 |
IS NOT NULL | a is not null | 如果操作符不为null,则结果为真 |
between | a between b and c | 如果a的值在b和c中,则结果为真 |
line | a like b | sql匹配,如果a 能够匹配到b则结果为真 |
in | a in | 假设a在a1、a2、a3...中的某一个值中,则结果为真 |
like 结合% 代表0到任意个字符,_ 代表一个字符。
select `StudentNo`,`StudentName` from `Student` where `StudentName` line '刘%' --查看姓刘的同学
select `StudentNo`,`StudentName` from `Student` where `StudentName` line '刘_'
联表查询
select S.studentNo,StudentName,SubjectNo,StudentResult
from Student AS S
inner join Result AS R
where S.studentNo=R.studentNo
select S.studentNo,StudentName,SubjectNo,StudentResult
from Student AS S
left join Result AS R
ON S.studentNo=R.studentNo
select S.studentNo,StudentName,SubjectNo,StudentResult
from Student AS S
right join Result AS R
ON S.studentNo=R.studentNo
select S.studentNo,StudentName,SubjectNo,StudentResult
from Student AS S
right join Result AS R
ON S.studentNo=R.studentNo
where StudentResult is null
select S.studentNO,StudentName,SubjectName,`StudentResult` from Student s
right join result R
on S.studentNO=R.studentNO
inner join `subject` sub
on sub.SubjectNO=R.SubjectNO
select 完整语法 (注意顺序)
分组,分页
select S.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
from `student` S
inner join result R
on S.StudentNO==R.StudentNO
inner join subject sub
on sub.SubjectNo==R.SubjectNO
where subjectName='JAVA第一学年' and StudentResult>80
order by StudentResult desc
limit 0,10
子查询
在where 里面再嵌套一个select 语句
where (select * from )
select * from `StudentNO`,`SubjectNo`,`StudentResult`
from Result
where SubjectNO=(
select SubjectNO from subject where SubjectName='数据库结构-1')
常用函数
-- 常用函数
SELECT ABS(-8); -- 绝对值
SELECT FLOOR(3.8); -- 向上取整
SELECT CEILING(9.2); -- 向下取整
SELECT SIGN(-10); -- 判断一个数的符号,正数为1,负数为-1,0为0
SELECT RAND(); -- 返回一个0-1 的随机小数
-- 字符串函数
SELECT CHARACTER_LENGTH('即使再小的帆也能远航');
SELECT CONCAT('我','爱','你们');
SELECT INSERT('我爱编程helloword',1,2,'超级热爱'); --返回指定的字符串(源字符串,开始位置,长度,替换文字)
SELECT LOWER('Lifei');
SELECT UPPER('Lifei');
SELECT REPLACE('狂神说坚持就能成功','坚持','努力');
SELECT SUBSTR('狂神说坚持就能成功',4,6);--返回指定的子字符串(源字符串,截取的位置,截取的长度)
SELECT REVERSE('清晨我上马');
select replace(`studentname`,'周','邹') from student where studentname like '周%'
常用的时间和日期函数
SELECT CURRENT_DATE; -- 当前日期
SELECT CURRENT_TIME; -- 当前时间
SELECT CURDATE(); -- 当前日期
SELECT NOW(); -- 日期+时间
SELECT LOCALTIME(); -- 日期+时间
SELECT SYSDATE(); -- 日期+时间
SELECT YEAR(NOW()) -- 年份
SELECT MONTH(NOW()) -- 月份
系统
SELECT USER();
SELECT SYSTEM_USER();
聚合函数
函数名称 | 描述 |
---|---|
count | 计数 |
sum | 求和 |
avg | 平均值 |
max | 最大值 |
min | 最小值 |
select count(studentname) from student; -- 会忽略所有的null值
select count(*) from student; -- 不会忽略null 值 ,本质计算行数
select count(1) from student; -- 不会忽略所有null 值 ,本质计算行数
select sum(`StudentResult`) as 总和 from Result;
分组和过滤
select `subjectname`,avg(`studentresult`) as 平均分,max(`studentresult`) as 最高分,min(`studentresult`) as 最低分
from result r
inner join `subject` sub
on r.`subjectno`=sub.`subjectno`
having 平均分>80
group by r.subjectno; -- 通过什么字段来排
md5 加密
CREATE TABLE testmd5(`id` INT(5) NOT NULL,
`username` VARCHAR(20) NOT NULL,
`password` VARCHAR(40) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
SELECT * FROM testmd5;
INSERT testmd5(`id`,`username`,`password`) VALUES(1,'lifei',MD5('123456')),(2,'geaiqing',MD5('123456'))
UPDATE testmd5 SET PASSWORD=MD5('123456') WHERE id=1;
SELECT * FROM testmd5;
事务
要么都成功,要么都失败
A 给B 转帐
A 钱变少
B 钱变多
将一组sql 放在一个批次性去执行
ACID 原则: 原子性 一致性 隔离性 持久性
原子性:要么都成功,要么都失败
一致性:事务前面的数据完整性要保证一致
持久性:事物一旦提交就不可逆了。
隔离性:互不干扰
事务的隔离性是多个用户并发访问数据时,数据库为每一个用户开启事务,不能被其他任务的操作数据所干扰。事务之间要相互割离
隔离所导致的问题
做不到后可能会产生脏读 幻读 不可重复读
脏读:一个事物读取了另一个事务没有提交的数据
幻读:多了一行
不可重复读:在一个事务内读取读取表中的某一行数据,多次读取结果不同
set autocommit=0 --关团事务自动提交,mysql 默认开启事务自动提交
start transaction --开启事务
commit -- 提交
rollback -- 回滚
set autocommit=0 --开启事务自动提交
-- 了解
savepoint 保存点名称 --设置一个事务的保存点
rollback to savepoint 保存点名称 -- 回滚到保存点名称
release savepoint 保存点名称 -- 撤销保存点名称
索引
mysql官方对索引的定义为:索引(index)是帮助mysql高效获取数据的数据结构。
提取句子主干,就可以得到事务的本质,索引是数据结构。
在一个表中主键索引只能有一个,唯一索引能有多个
分类
- 主键(primary key)
- 维一的标识,主键不可重复,只能有一个列做为主键。
- 唯一索引 (unicode key)
- 避免重复的列出现,唯一索引可以重复,多个列都可以标识为唯一索引。
- 常规索引(key/index)
- 默认的,index 或key 来设置
- 全文索引(FullText)
- 在特定的数据库引擎下支持,MYISAM
- 快速定位数据
基础讲法
show index from student; -- 显示索引信息
alert table school.student add fulltext index `studentname`(`studentname`); -- 增加了一个全文索引
-- explain 分析执行结果
explain select * from student;
explain select * from student where match(studentname) against('刘')
批量插入数据
delimiter $$ -- 写函数之前必须要写,标志
create function mock_data()
returns int
begin
declare num int default 100000 ;
declare i int default 0;
while i < num do
insert into app_user(`name`,`email`,`phone`,`gender`,`password`,`age`) values (concat('用户',i),'lifei@aa.com',floor(concat('18',rand()*(999999999-100000000)+100000000))),floor(rand()*2),uuid(),floor(rand()*2);
)
set i=i+1;
end while ;
return i;
end;
select mock_data();
create index id_app_user_name on app_user(`name`) ;--给name 字段添加 名为 id_app_user_name 的索引
explain create index id_app_user_name on app_user(`name`) ; -->能看到查询时遍历的数据记录
索引在小数据量的时候,用处不在,但是在大数据的时候,区别十分明显。
索引原则
- 过去不是越多越好
- 不要对经常变动的数据添加索引
- 小数据量的表不需要添加索引
- 索引一般加在常用来查询的字段上
索引的数据结构
Btree innodb 默认的数据结构
参考连接
系统用户表
mysql/user
-- 创建用户
create user lifei identified by 'lifei'
-- 授权用户
grant all privileges on *.* to lifei2@localhost -- all privileges 除了给别人授权,其他都能干
-- 修改密码
set password=password('111111'); --修改当前用户密码
set password for lifei = password('111111') ; --修改指定用户密码
-- 重命名
rename user lifei to lifei2 ;
-- 查看权限
show grants for lifei2; -- 查看lifei2的权限
show grants for lifei2@localhost ;
-- 撤销权限
revoke all privileges on *.* from lifei2;
-- 删除用户
drop user lifei2;
mysql 备案
为什么要备份
- 保证重要数据不丢失
- 数据转移
mysql 数据库备份的方式
- 直接拷贝物理文件
- sqlyog 可视化工具中手动导出
- 使用命令行导出 mysqldump
mysqldump -hhost -uroot -p123456 school student > d:/a.sql ;
mysqldump -hhost -uroot -p123456 school student1 student2 > d:/a.sql ;
mysql导入
- 登陆后待定数据库后执行 source < a.sql
- mysql -ulifei -plifei databasename < a.sql
规范数据库设计
为什么需要设计
当数据比较复杂时,就需要设计
糟糕的设计
- 数据冗余,浪费空间
- 数据库插入和删除也会麻烦,导[屏蔽使用物理外键]
良好的设计
- 节省空间
- 保证数据的完整性
- 方便我们开发系统
软件开发中,关于数据库的设计
- 分析需求,分析业务和需要处理的数据库需求
- 概要设计,设计关系图ER图
三大范式
- 原子性,保证每一列不可再分
- 满足第一范式,每张表只描述一件事情
- 满足第一范式和第二范式,消除依赖的传递性。要求每列与主键直接相关,而不能间接相关。
用于规范数据库的设计,可能会影响性能,二者不可兼得。考虑商业的性质和需求
JDBC
SUN公司为减化开发人员的(对数据库的统一操作),提供了一个JAVA数据库的规范,俗称JDBC。
这些规范的实体由具体的厂商去做
对于开发人员来说,我们只需要掌握JDBC接口的操作即可
安装包
- mysql-connector-java-5.1.47.jar 链接
- java.sql
- javax.sql
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;
USE jdbcStudy;
CREATE TABLE users(
id INT PRIMARY KEY ,
NAME VARCHAR(40),
PASSWORD VARCHAR(40),
email VARCHAR(60),
birthday DATE
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT users(id,NAME,PASSWORD,email,birthday) VALUES (1,'zhangsan','123567','zhangsan@test.cn','1990-12-05');
SELECT * FROM users;
INSERT users(id,NAME,PASSWORD,email,birthday) VALUES (2,'lisi','123567','lisi@test.cn','1990-12-05'),(3,'wangwu','123aaa','wangwu@test.cn','1984-12-03');
连接步骤:
- 新建一个空项目
- 新建 lib 目录 ,并将新下载的安装包放入
- 将lib 目录添加为库
- 编写代码
package com.kuang.lesson01;
import java.sql.*;
//我的第一个JDBC程序
public class JdbcFirstDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//用户信息和url
String url="jdbc:mysql://127.0.0.1:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&userSSL=true";
String username="root";
String password="123456";
//连接成功
Connection connection = DriverManager.getConnection(url, username, password);
//执行sqlserver对象
Statement statement = connection.createStatement();
//执行sql 的对象执行sql
String sql="select * from users";
ResultSet resultSet = statement.executeQuery(sql); //返回的结果集,列表形式.封装了我们全部的查询结果
while(resultSet.next()){
System.out.println("id="+ resultSet.getObject("id"));
System.out.println("name="+resultSet.getObject("name"));
System.out.println("password="+resultSet.getObject("password"));
System.out.println("====================================================");
}
//释放连接
resultSet.close();
statement.close();
connection.close();
}
}