MySql-Day-01

MySql

  • 能夠理解資料庫的概念
  • 能夠安裝MySQL資料庫
  • 能夠啟動,關閉及登錄MySQL
  • 能夠使用SQL語句操作資料庫
  • 能夠使用SQL語句操作表結構
  • 能夠使用SQL語句進行數據的添加修改和刪除的操作
  • 能夠使用SQL語句添加約束

一、資料庫介紹

1.1、資料庫概念

  • 什麼是資料庫

    ​ 資料庫就是存儲數據的倉庫，其本質是一個文件系統，數據按照特定的格式將數據存儲起來，用戶可以對資料庫中的數據進行增加，修改，刪除及查詢操作。

  • 什麼是資料庫管理系統

    ​ 資料庫管理系統（DataBase Management System，DBMS）：指一種操作和管理資料庫的大型軟體，用於建立、使用和維護資料庫，對資料庫進行統一管理和控制，以保證資料庫的安全性和完整性。用戶通過資料庫管理系統訪問資料庫中表內的數據。

  • 資料庫與資料庫管理系統的關係

1.2、資料庫表

​ 資料庫中以表為組織單位存儲數據。

​ 表類似Java類，每個欄位都有對應的數據類型。

用Java程式來與關係型數據對比，就會發現以下對應關係。

  • 類———-表
  • 類中屬性———-表中欄位
  • 對象———-記錄

1.3、表數據

​ 根據表欄位所規定的數據類型，我們可以向其中填入一條條的數據，而表中的每條數據類似類的實例對象。表中的一行一行的資訊我們稱之為記錄。

  • 表記錄與Java類對象的對應關係

1.4、常見資料庫

​ 常見的資料庫管理系統

  • MYSQL：開源免費的資料庫，小型的資料庫。已經被Oracle收購了。MySQL6.x版本也開始收費。
  • Oracle：收費的大型資料庫，Oracle公司的產品。Oracle收購SUN公司，收購MYSQL。
  • DB2：IBM公司的資料庫產品,收費的。常應用在銀行系統中。
  • SQLServer：MicroSoft公司收費的中型的資料庫。C#、.net等語言常使用。
  • SyBase：已經淡出歷史舞台。提供了一個非常專業數據建模的工具PowerDesigner。
  • SQLite：嵌入式的小型資料庫，應用在手機端。

​ 常用資料庫：MYSQL、Oracle。

​ 這裡使用MySQL資料庫。MySQL中可以有多個資料庫，資料庫是真正存儲數據的地方。

二、MySql資料庫

2.1、MySql安裝

  • 安裝（參考MySQL安裝圖解.doc）

​ 安裝後，MySQL會以windows服務的方式為我們提供數據存儲功能。開啟和關閉服務的操作：

​ 右鍵點擊我的電腦 → 管理 → 服務 → 找到MySQL服務開啟或停止。

​ 也可以在DOS窗口，通過命令完成MySQL服務的啟動和停止（必須以管理員身份運行cmd命令窗口）。

2.2、登錄MySQL資料庫

​ MySQL是一個需要賬戶名密碼登錄的資料庫，登陸後使用，它提供了一個默認的root帳號，使用安裝時設置的密碼即可登錄。

格式1：cmd>mysql –u用戶名 –p密碼

例如：mysql -uroot –proot

格式2：cmd>mysql--host=ip地址--user=用戶名--password=密碼

例如：mysql--host=127.0.0.1--user=root--password=root

三、SQL語句

3.1 、SQL概述

SQL語句介紹

​ 資料庫是不認識Java語言的，但是我們同樣要與資料庫交互，這時需要使用到資料庫認識的語言SQL語句，它是資料庫的程式碼。結構化查詢語言(Structured Query Language)簡稱SQL，是關係型資料庫管理系統都需要遵循的規範。不同的資料庫生產廠商都支援SQL語句，但都有特有內容。

SQL語句分類

  • SQL分類：
    • 數據定義語言：簡稱DDL(Data Definition Language)，用來定義資料庫對象：資料庫、表、列等。關鍵字：create、alter、drop等。
    • 數據操作語言：簡稱DML(Data Manipulation Language)，用來對資料庫中表的記錄進行更新。關鍵字：insert、delete、update等。
    • 數據控制語言：簡稱DCL(Data Control Language)，用來定義資料庫的訪問許可權和安全級別及創建用戶。
    • 數據查詢語言：簡稱DQL(Data Query Language)，用來查詢資料庫中表的記錄。關鍵字：select、from、where等。

SQL通用語法

  • SQL語句可以單行或多行書寫，以分號結尾。
  • 可使用空格和縮進來增強語句的可讀性。
  • MySQL資料庫的SQL語句不區分大小寫，關鍵字建議使用大寫。
    • 例如：SELECT * FROM user。
  • 同樣可以使用/**/的方式完成注釋。
  • MySQL中的我們常使用的數據類型如下：
類型名稱 說明
int(Integer) 整數類型.
double 小數類型.
decimal(m,d) 指定整數位與小數位長度的小數類型.
date 日期類型,格式為yyyy-MM-dd,包含年月日,不包含時分秒.
datetime 日期類型,格式為YYYY-MM-DD HH:MM:SS,包含時分秒.
timestamp 日期格式,時間戳.
varchar(M) 文本類型,M為0 ~ 65535 之間的整數.

3.2、DDL之資料庫操作：database

創建資料庫

格式：

create database 資料庫名;
create database 資料庫名 character set 字符集;

例如：

#創建資料庫資料庫中數據的編碼採用的是安裝資料庫時指定的默認編碼utf8
CREATE DATABASE webdb_1;
#創建資料庫並指定資料庫中數據的編碼
CREATE DATABASE webdb_2 CHARACTER SET utf8;

查看資料庫

查看資料庫MySQL伺服器中的所有的資料庫:

show databases;

查看某個資料庫的定義的資訊:

show create database 資料庫名;

例如：

show create database webdb_1;

刪除資料庫

drop database 資料庫名稱;

例如：

drop database webdb_2;

使用資料庫

查看正在使用的資料庫:

select database();

其他的資料庫操作命令切換資料庫：

use 資料庫名;

例如：

use webdb_1;

3.3、 DDL之表操作：table

創建表

格式：

create table 表名(
	欄位名類型(長度)[約束],
	欄位名類型(長度)[約束],
	...
);
類型：
	varchar(n)	字元串
	int			整形
	double		浮點
	date		時間
	timestamp	時間戳
約束：(詳情查看第四章)
	primary key 主鍵，被主鍵修飾欄位中的數據，不能重複、不能為null。

例如：創建分類表

CREATE TABLE category(
	cid INT primary key, #分類ID
	cname VARCHAR(100) #分類名稱
);

查看錶

  • 查看資料庫中的所有表：
格式： show tables;
  • 查看錶結構：
格式： desc 表名;
例如： desc category;

刪除表

  • 格式：drop table 表名;
例如： drop table category;

修改表結構格式

  • alter table 表名 add 列名類型(長度) [約束];
    作用：修改表添加列。
例如：#1、為分類表添加一個新的欄位為分類描述 varchar(20)
ALTER TABLE category ADD 'desc' VARCHAR(20);
  • alter table 表名 modify 列名類型(長度)約束;
    作用：修改表修改列的類型長度及約束。
例如：#2、為分類表的描述欄位進行修改，類型 varchar(50) 添加約束notnull
ALTER TABLE category MODIFY 'desc' VARCHAR(50) NOT NULL;

  • alter table 表名 change 舊列名 新列名 類型(長度)約束;

    作用：修改表修改列名。

例如：#3、為分類表的分類名稱欄位進行更換更換為 description varchar(30)
ALTER TABLE category CHANGE 'desc' description VARCHAR(30);
  • alter table 表名 drop 列名;
    作用：修改表刪除列。
例如：#4、刪除分類表中description這列
ALTER TABLE category DROP description;
  • rename table 表名 to 新表名;
    作用：修改表名。
例如：#5、為分類表 category 改名成 category2
RENAME TABLE category TO category2;
  • alter table 表名 character set 字符集(了解);
    作用：修改表的字符集。
例如：#6、為分類表 category 的編碼表進行修改，修改成gbk
ALTER TABLE category CHARACTER SET gbk;

3.4、DML數據操作語言

插入表記錄：insert

  • 語法：
--向表中插入某些欄位
insert into 表 (欄位1,欄位2,欄位3..) values (值1,值2,值3..);
--向表中插入所有欄位,欄位的順序為創建表時的順序。
insert into 表 values (值1,值2,值3..);
  • 注意：
  • 值與欄位必須對應，個數相同，類型相同
  • 值的數據大小必須在欄位的長度範圍內
  • 除了數值類型外，其它的欄位類型的值必須使用引號引起。（建議單引號）
  • 如果要插入空值，可以不寫欄位，或者插入null。
  • 例如：
INSERT INTO category (cid,cname) VALUES ('c001','電器');
INSERT INTO category (cid,cname) VALUES ('c002','服飾');
INSERT INTO category (cid,cname) VALUES ('c003','化妝品');
INSERT INTO category (cid,cname) VALUES ('c004','書籍');
INSERT INTO category (cid) VALUES ('c005');
INSERT INTO category (cname,cid) VALUES ('耗材','c006');

更新表記錄：update

​ 用來修改指定條件的數據，將滿足條件的記錄指定列修改為指定值。

  • 語法：
    更新所有記錄的指定欄位
update 表名 set 欄位名=值,欄位名=值,...;

​ 更新符合條件記錄的指定欄位

update 表名 set 欄位名=值,欄位名=值,...where條件;
  • 注意：
    • 列名的類型與修改的值要一致.
    • 修改值得時候不能超過最大長度.
    • 除了數值類型外，其它的欄位類型的值必須使用引號引起

刪除記錄：delete

  • 語法：

    delete from 表名 [where條件];

3.5、DOS操作數據亂碼解決

我們在dos命令行操作中文時，會報錯

insert into category (cid,cname) values (『c010』,』中文』);
ERROR1366 (HY000): Incorrect string value:'\xB7\xFE\xD7\xB0' for column 'cname' at row 1

錯誤原因:因為 mysql 的客戶端設置編碼是 utf8,而系統的 cmd 窗口編碼是 gbk
1、 查看MySQL內部設置的編碼

show variables like 'character%'; 查看所有mysql的編碼

2、需要修改client、connection、results的編碼一致（GBK編碼）
解決方案1：在cmd命令窗口中輸入命令，此操作當前窗口有效，為臨時方案。

set names gbk;

解決方案2：安裝目錄下修改my.ini文件，重啟服務所有地方生效。

四、SQL約束

4.1 主鍵約束

PRIMARY KEY 約束唯一標識資料庫表中的每條記錄。

  • 主鍵必須包含唯一的值。
  • 主鍵列不能包含 NULL 值。
  • 每個表都應該有一個主鍵，並且每個表只能有一個主鍵。

添加主鍵約束

方式一：創建表時，在欄位描述處，聲明指定欄位為主鍵 :

CREATE TABLE persons
(
	id_p int PRIMARY KEY,
	lastname varchar(255),
	firstname varchar(255),
	address varchar(255),
	city varchar(255)
) ;

方式二：創建表時，在constraint約束區域，聲明指定欄位為主鍵：

  • 格式： [constraint 名稱] primary key (欄位列表)
  • 關鍵字constraint可以省略，如果需要為主鍵命名，constraint不能省略，主鍵名稱一般沒用。
  • 欄位列表需要使用小括弧括住，如果有多欄位需要使用逗號分隔。聲明兩個以上欄位為主鍵，我們稱為聯合主鍵。
CREATE TABLE persons
( 
    firstname varchar(255),
	lastname varchar(255),
	address varchar(255),
	city varchar(255),
	CONSTRAINT pk_personID PRIMARY KEY (firstname,lastname)
) ;

方式三：創建表之後，通過修改表結構，聲明指定欄位為主鍵：

  • 格式： ALTER TABLE persons ADD [CONSTRAINT 名稱] PRIMARY KEY (欄位列表)
CREATE TABLE persons
(
	firstname varchar(255),
	lastname varchar(255),
	address varchar(255),
	city varchar(255)
)
ALTER TABLE persons ADD PRIMARY KEY (firstname,lastname)

刪除主鍵約束

如需撤銷 PRIMARY KEY 約束，請使用下面的 SQL：

ALTER TABLE persons DROP PRIMARY KEY;

4.2 自動增長列

我們通常希望在每次插入新記錄時，資料庫自動生成欄位的值。
我們可以在表中使用 auto_increment（自動增長列）關鍵字，自動增長列類型必須是整形，自動增長列必須為鍵(一般是主鍵)。

  • 下列 SQL 語句把 “persons” 表中的 “p_id” 列定義為 auto_increment 主鍵
CREATE TABLE persons
(
	p_id int PRIMARY KEY AUTO_INCREMENT,
	lastname varchar(255),
	firstname varchar(255),
	address varchar(255),
	city varchar(255)
);
  • 向persons添加數據時，可以不為p_id欄位設置值，也可以設置成null，資料庫將自動維護主鍵值：
INSERT INTO persons (firstname,lastname) VALUES ('Bill','Gates')
INSERT INTO persons (p_id,firstname,lastname) VALUES (NULL,'Bill','Gates');
  • 擴展：默認AUTO_INCREMENT 的開始值是 1，如果希望修改起始值，請使用下列 SQL 語法：
ALTER TABLE persons AUTO_INCREMENT=100;
  • 面試題

問：針對auto_increment ，刪除表中所有記錄使用 delete from 表名 或使用 truncate table 表名，二者有什麼區別？

刪除方式：
delete 一條一條刪除，不清空auto_increment記錄數。
truncate 直接將表刪除，重新建表，auto_increment將置為零，從新開始。

4.3 非空約束

NOT NULL 約束強制列不接受 NULL 值。
NOT NULL 約束強制欄位始終包含值。這意味著，如果不向欄位添加值，就無法插入新記錄或者更新記錄。

  • 下面的 SQL 語句強制 “id_p” 列和 “lastname” 列不接受 NULL 值：
CREATE TABLE persons
(
	id_p int NOT NULL,
	lastname varchar(255) NOT NULL,
	firstname varchar(255),
	address varchar(255),
	city varchar(255)
);

4.4 唯一約束

UNIQUE 約束唯一標識資料庫表中的每條記錄。 UNIQUE 和 PRIMARY KEY 約束均為列或列集合提供了唯一性的保證。 PRIMARY KEY 擁有自動定義的 UNIQUE 約束。 請注意，每個表可以有多個 UNIQUE 約束，但是每個表只能
有一個 PRIMARY KEY 約束。

添加唯一約束

與主鍵添加方式相同，共有3種

  • 方式一：創建表時，在欄位描述處，聲明唯一：
CREATE TABLE persons
(
	id_p int NOT NULL,
	lastname varchar(255) NOT NULL,
	firstname varchar(255),
	address varchar(255),
	city varchar(255)
);
  • 方式二：創建表時，在約束區域，聲明唯一：
CREATE TABLE persons
( 
	id_p int,
	lastname varchar(255) NOT NULL,
	firstname varchar(255),
	address varchar(255),
	city varchar(255),
	CONSTRAINT 名稱UNIQUE (Id_P)
) ;
  • 方式三：創建表後，修改表結構，聲明欄位唯一：
ALTER TABLE persons ADD [CONSTRAINT 名稱] UNIQUE (Id_P);

刪除唯一約束

  • 如需撤銷 UNIQUE 約束，請使用下面的 SQL：
ALTER TABLE persons DROP INDEX 名稱;
  • 如果添加唯一約束時，沒有設置約束名稱，默認是當前欄位的欄位名。

第5章 MySQL資料庫密碼重置(擴展)

1、停止mysql伺服器運行輸入services.msc 停止mysql服務。

2、 在cmd下,輸入mysqld –console –skip-grant-tables 啟動伺服器,出現一下頁面,不要關閉該窗口。

3、 新打開cmd,輸入mysql -uroot 不需要密碼。

use mysql;
update user set password=password('abc') WHERE user='root';

4、關閉兩個cmd窗口。

Tags:

VirMach 便宜 VPS

QNews

QNews