mysql

下载链接

下载链接

环境

5.7 版本,压缩包安装

安装

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

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

  1. 新建mysql配置文件 my.ini
  2. 管理员模式的cmd运行安装的命令安装数据库服务

    mysqld -install
  3. 初始化配置文件
    mysqld --initialize-insecure --user=mysql
    可以看到自动创建了data目录
  4. 启动mysql net start mysql
  5. 进入mysql 通过命令行
    mysql
  6. 修改密码
update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';
flush privileges
  1. 将配置文件中,取消密码登陆关闭

    然后重启服务 net stop mysql;net start mysql
  2. 其他
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();


    }
}

posted @ 2022-07-15 09:55  lifei888  阅读(81)  评论(0)    收藏  举报