mysql

下载链接

下载链接

环境

5.7 版本，压缩包安装

安装

1. 下载
2. 解压到自己的安装目录
3. 添加环境变量

右击我的电脑——>属性——>高级——>环境变量——>系统变量——>path 里面添加mysql执行文件路径。
C:\lifei\mysql-5.7.19\bin

4. 新建mysql配置文件 my.ini
   
5. 管理员模式的cmd运行安装的命令安装数据库服务
   
   mysqld -install
6. 初始化配置文件
   mysqld --initialize-insecure --user=mysql
   可以看到自动创建了data目录
   
7. 启动mysql net start mysql
8. 进入mysql 通过命令行
   mysql
9. 修改密码

update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';
flush privileges

10. 将配置文件中，取消密码登陆关闭
    
    然后重启服务 net stop mysql;net start mysql
11. 其他

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

规范数据库设计
为什么需要设计
当数据比较复杂时，就需要设计
糟糕的设计

• 数据冗余，浪费空间
• 数据库插入和删除也会麻烦，导[屏蔽使用物理外键]

良好的设计

• 节省空间
• 保证数据的完整性
• 方便我们开发系统

软件开发中，关于数据库的设计

1. 分析需求，分析业务和需要处理的数据库需求
2. 概要设计，设计关系图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');

连接步骤:

1. 新建一个空项目
2. 新建 lib 目录 ，并将新下载的安装包放入
3. 将lib 目录添加为库
4. 编写代码

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();


    }
}