mysql語法使用詳細程式碼版

路漫漫qixiuyuanxi 發表於 2022-07-18
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.新增資料庫驅動

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片儲存下來直接上傳(img-rJ96yyY1-1658113311722)(../AppData/Roaming/Typora/typora-user-images/image-20220718090403041.png)]

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