mysql

1.什么是資料庫

資料庫：（DB，DataBase）

作用：用來存盤資料，管理資料，Txt，Excel，word是在資料庫出現之前存盤資料的，

概念：資料倉庫，安裝在作業系統上的軟體，

資料庫是所有軟體體系中最核心的存在

2.資料庫分類

關系型資料庫（SQL）

• Mysql，oracle，sqlServer，Sqlite
• 通過表和表之間，行與列之間的關系進行資料的存盤……

非關系型資料庫（NoSql）

• redis，MongDb
• 物件存盤，通過物件的自身屬性來決定，

DBMS(資料庫管理系統)

• 資料庫的管理軟體，科學有效的管理我們的資料，維護和獲取資料，
• Mysql

3.資料庫連接

命令列連接：

mysql -uroot -p123123 --資料庫注釋

資料庫基本操作：

show databases; --顯示所有資料庫
 use mysql;     --切換資料庫
 show tables;  --顯示所有表
 describe user; --顯示表的具體資料
 exit;  --退出

3.資料庫的操作

資料庫的欄位屬性：

Unsigned：

• 無符號整數
• 宣告了該列值不能為負數

zeroFill:

• 0填充的
• 不足位數的，使用0來填充

自增:

• 自增
• 可以自定義增加的數量和步長

非空:

• NUll not null
• 設定為非空不給賦值就會報錯

默認:

• 如果不賦值則為默認設定的值

操作資料庫>操作資料庫的表>操作表中的資料

操作資料庫:

創建資料庫

create database westos;

洗掉資料庫

drop database westos;

使用資料庫

use shop;

查看所有資料庫

show databases;

操作資料庫的表：

創建表

create table if not exists `student`(
 
`id` int(4) not null auto_increment comment 'id',
 
`name` varchar(30) not null default '匿名' comment '姓名',
 
`pwd` varchar(20) not null default '123123' comment '密碼',
 
`sex` varchar(2) not null default 'nv' 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;

格式：

create table if not exists `表名`(
    `欄位名` 列型別 [屬性][索引][注釋],
    `欄位名` 列型別 [屬性][索引][注釋],
        ......
    `欄位名` 列型別 [屬性][索引][注釋]
);

洗掉表

drop table teacher if exists

修改表

修改表名: alter table 表名 as 新表名

alter table student as teacher

添加一個欄位：alter table 表名 add 欄位名 屬性

alter table teacher add age int(11)

修改表的欄位 型別：alter table 表名 modify 欄位名 列屬性

alter table teacher modify age varchar(10)

重名字：alter table 表名 change 舊名字 新名字

alter table teacher change age age1 int(1)

洗掉欄位

alter table teacher drop age1

查看表

desc 表名

資料表型別

資料庫引擎：

innodb：默認使用

myisam：早期使用

4.mysql資料管理

外鍵

create table `grade`(
 
 `gradeId` int(10) not null auto_increment comment '年級id',
 
`gradeName` varchar(20) not null comment '年級名稱',
 
primary key(`gradeId`)
 
)engine=innodb default charset=utf8;

--學生表的gradeId參考年級表的gradeId
--定義外鍵key
--給外鍵添加約束reference參考

create table if not exists `student`(
 
`id` int(4) not null auto_increment comment 'id',
 
`name` varchar(30) not null default '匿名' comment '姓名',
 
`pwd` varchar(20) not null default '123123' comment '密碼',
 
`sex` varchar(2) not null default 'nv' comment '性別',
 
`birthday` datetime default null comment '出生日期',
 
`address` varchar(100) default null comment '住址',
 
`email` varchar(50) default null comment '郵箱',
 
primary key(`id`)
 key `fk_gradeId` (`gradeId`),
constraint `fk_gradeId` foreign key(`gradeId`)references`grade`(`gradeId`)        
)engine=innodb default charset=utf8;

外鍵在開發中不建議使用，因為會在洗掉更新的時候產生級聯，造成錯誤！

DML語言

資料庫管理語言：

插入: insert into 表名(欄位名) values(屬性)

insert into grade(gradeName) values('大四');

insert into grade (gradeName) values('大二'),('大三');

更新：update set 表名 修改的欄位和值 where 條件，不指定條件會修改所有的列，

update grade set gradeName='大一' where gradeId=1;

洗掉：delete from 表名

delete from grade where gradeId=1;

清空：truncate 表名

truncate grade;

truncate和delete洗掉資料的區別：

• truncate會清空自增歸零，從1開始，delete不會（除非斷電重啟，因為mysql在記憶體中），
• 不會影響事務，

查詢：select 欄位 from 表名

-- 查詢表的所有欄位
select * from grade; 
-- 查詢id欄位
select gradeId from grade;
-- 起別名gradeid為id 
select gradeId as id from grade;
-- 在查出來的資料前面加上字串 
select concat('加上字串：',gradeName) as 名字 from grade;
-- 按照id條件查詢
select gradeName as 名字 from grade where gradeId=2;
-- 按照name條件進行查詢
select gradeName as 名字 from grade where gradeName='大二';
-- 去重
select distinct gradeId from grade;
-- 查詢系統版本
select version();
-- 用來計算
select 100*3-1 as jisuanjieguo;
-- 查詢自增的步長
select @@auto_increment_increment

模糊查詢

like結合: %代表0到任意個字符，結合_代表一個字符

select gradeName from grade where gradeName like '大%';

select gradeName from grade where gradeName like '%er';

select gradeName from grade where gradeName like 'er_';

in：查詢具體的一個或者多個值

select gradeName from grade where gradeName in('大er','大san');

聯表查詢

join（要連接的表）……on（等值判斷）

select gradeName,name,sex from student inner join grade on where grade.gradeId=student.id;

自連接

自己的表和自己的表連接，一張表拆為兩張表，

select a.categoryName as father ,b.categoryName as son from category as a,category as b where a.categoryid=b.pid

排序

order By通過哪個欄位排序，desc降序，asc升序，

select email from student order by email asc;

select email from student order by email desc;

分頁

limit 起始頁，頁面的大小，

將資料分頁顯示，使用戶體驗更好，

select email from student limit 1,1;

子查詢

在where陳述句中嵌套一個子查詢，

select gradeName from grade where gradeName=(select name from student where name='匿名');

group by

通過什么欄位來分組，

having

分組之后的條件

5.mysql函式

常用函式

--絕對值
select abs(-8);
--向上取整
select ceiling(9.4);
--向下取整
select floor(9.4);
--亂數
select rand();
--判斷正負數
select sign(-1);
--字串長度
select char_length('lllll');
--拼接字串
select concat('w','s');
--字串替換
select insert('woaibiancheng',1,4,'llll');
--轉換大小寫
select lower('SS');
select upper('hh');
--第一次出現的字串的索引
select replace('sss','ss','ww');
--回傳指定的字串 從第幾個開始截取幾個
select substr('ssssss',4,6);
--獲取當前時間
select current_date();
select now();
select localtime();
select current();
select year(now());
select month(now());
       ......

聚合函式

select count(*) from student;
select count(1) from student;
select count(name) from student;
select sum(name) from student;
select avg(name) from student;
select min(name) from student;
select max(name) from student;

資料庫級別的md5加密演算法

--建表
mysql> create table testmd5(
 
`id` int(4) not null,
 
`name` varchar(20) not null,
 
`pwd` varchar(50) not null,
 
primary key(`id`)
 
)engine=innodb default charset=utf8;

--插入資料
insert into testmd5 values(1,'zhangsan','123123'),(2,'lisi','123123'),(3,'wangwu','123123');

--更新密碼為MD5
update testmd5 set pwd=md5(pwd);

--插入密碼為MD5
insert into testmd5 values(4,'xixi',md5('123123'));

--查詢密碼為MD5
select * from testmd5 where name='xixi' and pwd=md5('123123');
+----+------+----------------------------------+
| id | name | pwd                              |
+----+------+----------------------------------+
|  4 | xixi | 4297f44b13955235245b2497399d7a93 |
+----+------+----------------------------------+
1 row in set

6.事務

要么都成功，要么都失敗，

1.sql執行 a給b轉賬 2.sql執行 b接受a的轉賬

同一事務一起執行，

事務的原則

• 原子性：要么都成功，要么就失敗
• 一致性：事務前后資料保持一致
• 持久性：事務一旦提交不可逆
• 隔離性：資料庫為每一個用戶開啟一個事務，不同事務之間要相互隔離，

臟讀：一個事務讀取了另一個事務未提交的資料，

虛讀：一個事務讀取到了另一個事務正在插入的事務，

事務執行流程

--關閉自動提交
set autocommit=0;
--事務開啟
start transaction;
     --update
     --delete
--提交持久化 成功
commit;
--回滾 回到原來的樣子 失敗
rollback
--事務結束
set autocommit=1;

模擬轉賬

--建表
create table account(
 
`id` int(10) not null auto_increment,
 
`name` varchar(10) not null,
 
`money` decimal(9,2) not null, 
 primary key(`id`)
)engine=innodb default charset=utf8;
--插入資料
insert into account(`name`,`money`) values('A',2000),('B',10000);
--關閉自動提交
set autocommit=0;
--開啟事務
start transaction;
--更新資料
update account set money=money-500 where `name`='A';
update account set money=money+500 where `name`='B';
--提交成功
commit;
--回滾失敗
rollback;
--開啟自動提交
set autocommit=1;

7.索引的分類

• 主鍵索引
  • 唯一的標識，主鍵不可重復，只有一個列作為主鍵
• 唯一索引
• 常規索引
• 全文索引

創建索引在查詢資料較多的時候可以大大提升查詢速度，

索引創建:create index 索引名 on 表(欄位)

create index id_app_user_name on app_user(name)

索引原則

• 索引不是越多越好
• 不要對資料進行變動
• 小資料的表不需要加索引
• 索引一般加在常用查詢的欄位

8.規范資料庫設計

當資料庫比較復雜的時候，就需要規范設計了，

糟糕的資料庫設計：

• 資料冗余，浪費空間
• 資料庫的洗掉和插入都會麻煩，例外
• 程式性能差

良好的資料庫

• 節省記憶體空間
• 保證資料庫的完整性
• 方便我們開發系統

軟體開發中關于資料庫的設計

• 分析需求：分析業務和需要處理的資料庫的需求
• 概要設計：設計關系圖e-r圖

三大范式

第一范式1NF

• 保證每一列不可再分

第二范式2NF

• 已達到第一范式
• 每張表的只描述一個事情

第三范式3NF

• 滿足第二范式
• 資料庫的每一列資料都和主鍵直接相關，而不能間接相關，

規范性和性能問題

在實際開發中也不能一味地追求滿足三大范式

關聯查詢的表不能超過三張

• 考慮商業化的需求和目標，資料庫的性能更加重要
• 在規范性能問題的時候，需要適當的考慮一下規范性
• 故意給某些表增加一些冗余的欄位，（使之從多表查詢變為單表查詢）

jdbc

資料庫驅動

不同的資料庫有不同的驅動，應用程式需要先連接到驅動，才能連接到資料庫，

jdbc

為了簡化開發人員對資料庫的操作，提供了一個java操作資料庫的規范，俗稱jdbc，

第一個jdbc程式

1.創建一個資料庫

create table account(
 
`id` int(10) not null auto_increment,
 
`name` varchar(10) not null,
 
`pwd` varchar(10) not null, 
 primary key(`id`)
)engine=innodb default charset=utf8;

2.添加資料庫驅動

3.撰寫代碼

package com.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * @author panglili
 * @create 2022-07-18-9:05
 */
//我的第一個jdbc程式
public class demo1 {
    public static void main(String[] args) throws Exception {
        //1.加載驅動
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.用戶資訊和url
        String url="jdbc:mysql://localhost:3306/shop?useUnicode=true&characterEncoding=utf-8&userSSL=false";
        String name="root";
        String pwd="123123";
        //3.連接成功
        Connection connection = DriverManager.getConnection(url, name, pwd);
        //4.執行sql物件
        Statement statement = connection.createStatement();
        //5.查看結果
        String sql="select * from account";
        ResultSet resultSet =statement.executeQuery(sql);
        while(resultSet.next()){
            System.out.println("id="+resultSet.getObject("id"));
            System.out.println("id="+resultSet.getObject("name"));
            System.out.println("id="+resultSet.getObject("money"));
        }
        //6.釋放連接
        resultSet.close();
        statement.close();
        connection.close();
    }


}

分析jdbc物件

• Class：加載驅動，固定寫法
• url：連接的統一資源定位符
• username：用戶名
• password：密碼
• connection：獲取資料庫物件，代表資料庫
• statement：執行sql的物件
• resultset：sql物件執行方法獲取回傳的集合

sql注入：

web應用程式對用戶輸入資料的合法性沒有判斷或者過濾不嚴，攻擊者可以在web應用程式中事先定義好的查詢陳述句的結尾添加額外的sql陳述句，在管理員不知情的情況下進行非法操作，以此實作欺騙資料庫服務器執行非授權的任意查詢，從而進一步得到相應的資料資訊，

java解決的方法：

• 使用prepareStatement

package com.sql;

import java.sql.*;

/**
 * @author panglili
 * @create 2022-07-18-9:57
 */
public class demo2 {
    public static void main(String[] args) throws Exception {
        //1.加載驅動
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.用戶資訊和url
        String url="jdbc:mysql://localhost:3306/shop?useUnicode=true&characterEncoding=utf-8&userSSL=false";
        String name="root";
        String pwd="123123";
        //3.連接成功
        Connection connection = DriverManager.getConnection(url, name, pwd);
        //4.預編譯sql先不賦值
        String sql="delete  from account where id=?";
        PreparedStatement st = connection.prepareStatement(sql);

        //5.手動給引數賦值
        st.setInt(1,2);
        //6.執行 洗掉陳述句會回傳受影響的行數
        int i = st.executeUpdate();

        if(i>0){
            System.out.println(i+"行洗掉成功");
        }
        //7.釋放連接
        st.close();
        connection.close();
    }
}

prepareStatement防止sql注入的本質就是，把傳遞進來的引數當做字符，如果存在轉義字符會被直接轉義！

標籤：MySQL

上一篇：資料庫設計案例

下一篇：Redis學習