mysql語法使用詳細程式碼版

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：早期使用

	MYSAIM	INNODB
事務支援	不支援	支援
資料行鎖定	不支援	支援
外來鍵約束	不支援	支援
全文索引	支援	不支援
表空間大小	較小	較大約為2倍

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;

操作	描述
inner join	如果表中至少有一個匹配，就返回
left join	會從左表中返回所有的值，即使右表中沒有
right join	會從右表中返回所有的值，即使左表中沒有

自連線

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

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

聚合函式

函式名稱	描述
sum()	總和
avg()	平均值
max()	最大值
min()	最小值
count()	計數

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注入的本質就是，把傳遞進來的引數當做字元，如果存在轉義字元會被直接轉義！