mysql語法使用詳細代碼版

来源:https://www.cnblogs.com/lumanmanqixiuyuanxi/archive/2022/07/18/16489702.html
-Advertisement-
Play Games

mysql 1.什麼是資料庫 資料庫：（DB，DataBase） 作用：用來存儲數據，管理數據。Txt，Excel，word是在資料庫出現之前存儲數據的。 概念：數據倉庫，安裝在操作系統上的軟體。 資料庫是所有軟體體系中最核心的存在 2.資料庫分類 關係型資料庫（SQL） Mysql，oracle， ...


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


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Termius for Mac(SSH客戶端)
    Termius Mac版是適用於macOS， Windows 和Linux的現代SSH 來組織，訪問和連接到您的伺服器。Termius允許您將主機組織成組。組允許您共用設置，但每個主機可以有自己獨立的首選項。這些數據以及連接和命令歷史記錄可以安全地同步到您的所有設備。Termius使用端到端加密來確 ...
  • Ubuntu搭建Pytorch，就這一篇就夠了
    鏡像下載、功能變數名稱解析、時間同步請點擊 阿裡雲開源鏡像站 第一步：配置鏡像源，安裝必要環境 更換鏡像 第二步：安裝anaconda 1 進入官網 https://www.anaconda.com/ 2 下載linux的sh版 3 在對應位置通過命令輸入 sh 文件名.sh 4 選擇 YES 5 選擇 N ...
  • Vim 可視化模式及其用法
    Vim 可視化模式及其用法 背景 相信大家都使用過帶圖形界面的操作系統中的文字編輯器，用戶可以使用滑鼠來選擇要操作的文本，非常方便。在 Vim 編輯器中也有類似的功能，但不是通過滑鼠，而是通過鍵盤來選擇要操作的文本。 Vim可視化模式 在 Vim 中，如果想選中目標文本，就需要調整 Vim 進入可視 ...
  • Axure RP 9 for Mac(原型設計軟體)中文
    axure mac 是專為UX專業人員和業務分析師設計的專業網站原型設計工具！可以幫助他們快速創建應用程式和網站的線框，原型和規格！新功能包括一系列廣泛的改進：全面的 UI，新的設計和文檔特征，以及將引擎優化到前所未有的程度。Axure RP將原型設計的成品交付給產品經理，業務分析師和用戶體驗專業人 ...
  • 極品飛車14:熱力追蹤 for Mac(競速賽車游戲)附完美存檔
    極品飛車14:熱力追蹤是一款非常有趣的競速賽車游戲，現為大家帶來的是內附完美存檔版，游戲將在全新的開放地圖中解鎖新的跑車及裝備，快來游戲中體驗賽車的樂趣吧，游戲非常炫酷，喜歡賽車游戲的朋友一定不要錯過！ 詳情：極品飛車14:熱力追蹤 for Mac(競速賽車游戲)附完美存檔 游戲模式 生涯模式 《極 ...
  • 地鐵衝浪者Subway Surfers Tag for mac(地鐵跑酷游戲)
    喜歡玩地鐵酷跑的玩家，可以試試這款Subway Surfers Tag Mac，又名地鐵衝浪，是一款頗受歡迎的休閑跑酷類游戲，您可以選擇在鐵路場、公園、碼頭和地下等挑戰酷跑衝浪，超炫酷，喜歡的朋友不要錯過哦~ 詳情：Subway Surfers Tag for mac(地鐵跑酷游戲) 游戲介紹 在鐵 ...
  • apache編譯安裝
    鏡像下載、功能變數名稱解析、時間同步請點擊 阿裡雲開源鏡像站 源碼包編譯實例 下麵通過編譯安裝httpd來深入理解源碼包安裝(httpd-2.4.54) 下載編譯工具,httpd以及其兩個依賴包的源碼包 //源碼包建議到官方網站下載 [root@lnh ~]# mkdir xbz [root@lnh ~]# ...
  • Oracle數據泵不同用戶導入導出（表級）
    更多技術交流、求職機會、試用福利，歡迎關註位元組跳動數據平臺微信公眾號，回覆【1】進入官方交流群 需求背景 數據探查上線之前，數據驗證都是通過寫 SQL 方式進行查詢的，從編寫 SQL，到解析運行出結果，不僅時間長，還會反覆消耗計算資源，探查上線後，只需要一次探查，就可以得到整張表的探查報告，但後續我 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類