當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MySQL基本语法

發布時間：2023/12/20 数据库 20 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL基本语法小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

mysql數據庫的操作

數據庫操作

1.創建數據庫

(1).character set：指定數據庫采用的字符集，如果不指定字符集，默認時候是utf8

(2).collate：指定數據庫字符集的校對規則（常用utf8_bin、utf8_general_ci注意默認是utf8_general_ci)

create database mysql01;//默認創建數據庫表mysql01create database mysql01 default character set utf8;//創建指定默認字符集為交的數據庫show character set;//查看可用字符集名稱show collation;//查看可用的排序規則名稱create database student character set utf8 default collate utf8_bin; //創建指定的默認字符集utf8，排序規則為utf8_bin的數據庫（student）

2.查看、刪除和修改數據庫

(1).使用數據庫：use 數據庫名稱;

(2).修改數據庫：使用alter database語句可以修改一個已有的數據庫的默認字符集和排序規則。

(3)刪除數據庫：drop database mysql01;

? 每次只能刪除一個數據庫。

show databases;//查看所有數據庫show create databases;//查看數據庫屬性use mysql01;//使用數據庫alter database mysql01 character set gbk; //將指定的utf8的數據庫修改為gbk的數據庫alter database student character set gb2312 default collate gb2312_chinese_ci; //將student數據庫的默認字符集修改為gb2312，排序規則修改為gb2312_chinese_cidrop database mysql01;//刪除數據庫

Mysql常用數據類型

1.數值類型

int(4個字節) float(單精度 4個字節) double(雙精度 8個字節) decimal(M,D)(大小不確定)

decimal(M,D)精度高可以用于財務和貨幣內存和分配不一樣

int not null default 0不允許就空默認為0

create table student(id int(5),name varchar(10),age int not null default 20); //創建一個學生表編號為整型5個字節，名字為變長字符串類型10個字節，年齡不允許為空默認為20

2.字符串類型

char(定長字符串 0-255) Varchar( 變長字符串0-65535) text(0-2^16-1)

char和varchar的區別：

char：定長字符串在分配數據的存儲空間是固定的

varchar：它會根據數據的實際長度動態的改變存儲值的長度，所以不能確定字段需要多少字符

就可以使用varchar大大節省了磁盤空間。

3.日期和時間類型

date[日期年月日] time[時間時分秒] datetime[年月日時分秒 YYYY-MM-DD HH:mm:ss]

timestamp[時間戳] year[年]

注意：沒有在time類型加冒號，理解為持續的的時間，而不是一個時間段

系統在年份中，將00-69范圍內的年份轉化為2000-2069

把70-99范圍內的年份值轉化為1970-1999

4.復合類型

enum 枚舉類型

表示：從一個集合選取一個值，猶似一個單選題

比如性別enum（“男”，“女”）

集合中每一個值都是字符串類型的，他們都是都好隔開

set可以預定義的集合中取得任意數量的值

set(“值1”，“值2”…)多項選擇器可以選擇多個或者一個，set不可以包含兩個相同的元素

表操作

1.創建表

create table shop（id int(10),goods_name varchar(10),price double(10));創建表

2.查看表

show tables;

3.查看表結構

desc 表名;

4.查看建表語句

show create table;

5.快速創建一個表結構相同的表

create table 新表名 like 舊表名;

6.刪除表

drop table 表名;

7.刪除表，如果表結構存在的話

drop table if exits 表名;

修改表結構

8.添加字段

alter table 表名 add [column] 字段名類型;

9.刪除字段

alter table 表名 drop 字段名;

10.修改字段名

alter table 表名 change 舊字段名新字段名類型;

11、修改字段類型

alter table 表名 modify 字段新字段類型;

12.修改表名

rename table 表名 to 新表名;

13.修改表的字符集

alter table 表名 character set 字符集;

增刪改查

insert(添加數據)

插入全部數據

insert into 表名 values (值1,值2);

值的順序一定要與字段對應

insert into user values('張三','30','133324','男');

插入部分數據

insert into 表名 (字段名1，字段名2，字段名3,...)values(值1,值2,值3,...); insert into user(name,age,modile) values('小艾',20,'243252'); insert into 表名(字段名1,字段名2,字段名3,...)values(值1,值2,值3,...),(值1,值2,值3,...)...;

復制表

目標表必須存在，并且表結構要與源表一致。

insert into 目標表名 select*from 源表名;

update(修改數據)

更新表記錄

update 表名 set 字段名1 = 值1，字段名2 = 值2，......

更新全部記錄

update user1 set gender = '男';

更新時加入運算

update user1 set age = age+1;

部分更新：

update 表名 set 字段名1 = 值1，字段名2 = 值2，...where 字段條件 update user1 set sex='女' where name = '張三';

delete(刪除數據)

刪除記錄

delete from 表名; 刪除全部記錄 delete from 表名 where 字段條件; 按條件刪除 delete from user1 where name = '張三'; delete from user1 where age<20; delte from user1 where age<23 and name = '小天'; delete from user1; 刪除表內全部數據

刪除全部記錄

truncate 表名; truncate user1;

delete與truncate的區別

delete是將表中的數據逐行清除。如果表里的數據過多速度會很慢，但是它支持按條件刪除

truncate是直接將表中的數據物理刪除，刪除速度快和表里有多少數據無關，它不支持條件刪除

查看mysql的字符集設置

show varables like '%character%';

設置字符集編碼集編碼：

set character_set_client = 字符集名稱 set character_set_connection = 字符集名稱 set character_set_ results = 字符集名稱set character_set_client = gbk; set character_set_connection = gbk; set character_set_results = gbk;

select(查找數據)

查詢語句

select 關鍵字用來查詢取出表中的數據

簡單查詢：

select *from 表名；取出表中的所有數據

查詢指定列：

select 字段名1，字段名2，.... from 表名

查詢指定列帶別名：

select 字段名1 as 別名，字段名2 as 別名，.....

消除重復值：

distinct關鍵字用來去除重復值只能寫道select后面，一個select只能有一個distinct

distinct 會根據查詢字段的數據做組合操作，如果合并后的數據和前面的數據沒有沖突，那就顯示。

如果有沖突。那就不顯示。

distinct并沒有去掉表中的重復數據。只是去掉了結果集中的重復數據。

如果去除表中的重復數據，步驟如下、

? 1.先創建一個和原表結構相同的表

? 2.用insert into 目標表 select distinct from 原表

? 3.刪除原表或為安全起見將原表改名

? 4.將目標表的名字改為源表名

? 這樣就完成了一個對表的去重

查詢結果參與運算：

CREATE TABLE student (id int,name varchar(20),age int,sex varchar(5),address varchar(100),math int,english int );INSERT INTO student (id, NAME, age, sex, address , math, english) VALUES (1, '馬云', 55, '男', '杭州' , 66, 78),(2, '馬化騰', 45, '女', '深圳' , 98, 87),(3, '馬景濤', 55, '男', '香港' , 56, 77),(4, '柳巖', 20, '女', '湖南' , 76, 65),(5, '柳青', 20, '男', '湖南' , 86, NULL),(6, '劉德華', 57, '男', '香港' , 99, 99),(7, '馬德', 22, '女', '香港' , 99, 99),(8, '德瑪西亞', 18, '男', '南京' , 56, 65);用到的表 select name,english+10 from student; 注意，這里面的null值是不參與運算的，但也不會報錯，會被忽略 select name,ifnull(esnglish ,0) as 英語 from student;

where 關鍵字按條件查詢

相關運算符： < 小于 = 等于 >大于 >=大于等于 <=小于等于 != 或 <>不等于

英語不等于77 select * from student where english !=77; math和英語都大于60的 select *from student where math>60 and ehglish>60; 數學大于60 或者英語大于 80 select * from student where math>60 or english>80;

邏輯運算符： and (&&)與并且 or(||)或 not(!)非

select * from student where math not in(66,76);

范圍關鍵字 between and

between 值1 and 值2 取出來的結果集范圍包含值1 和值2

select * from student where math between 56 and 66；

等同于

select *from student where math >= 56 and math <=66;

模糊查詢關鍵字：

%通配符代表任意長度的任意字符

like like ‘馬%’

select * from student where name like '馬%'; select * from student where name like '%德'; select * from student where name like '%德%';

_通配符代表一個字符長度的任意字符用法與%一樣，只不過只代表一個字符

select * from student where name like ‘馬_’; select * from student where name like ‘馬__’; select * from student where name like ‘_巖’;

排序關鍵字

order by [asc] [desc] asc 按升排序 desc 降序排序

單列排序

select * from student order by math; asc默認可以不寫 select * from student order by math desc; desc 必須寫上

組合排序

order by 字段1 [asc|desc], 字段2 [asc|desc]

select * from student order by math desc, english desc;

聚合函數：

? count 統計數量

? count(*) 統計共有多少行

? count(字段名)

select count(*) as 行數 from student;

min(字段名) 函數：

? 求字段的最小值。

select min(math) from student;

max(字段名) 函數

? 求字段的最大值。

select max(math) from student;

avg 求某列的平均值

select avg(ifnull(english,0)) as 平均分 from student; --因為NULL不計入計算，所必須給null一個0值select avg(math) from student; --沒有空的列可以直接進行計算。

sum(字段名) 求合

select sum(english) from student; select sum(ifnull(english,0)) from student;

分組關鍵字

group by 將數據進行分組

select sum(sex) from student group by sex; 按性別進行分組計算。

執行順序是先進行分組，然后再按組對字段進行計算

按條件進行篩選

having關鍵字

select math,count(*) from student group by math having count(*)>1;

執行順序：

where 在group by 之前執行

having 在group by 之后執行

先分組，在count()統計，再按條件顯示

分頁

分頁關鍵字limit

limit 一個數字，限制條數

limit 數字1，數字2，數字1加上1是開始的行位置，數字2要取的記錄條數

注意查詢時候列的起始值為0，不是為1

select *from student limit 0,2;

主鍵

表中值不重復的字段，可以作為記錄的唯一標識

create table user2(id int primary key,name varchar(10));

自增 auto_increment

create table user3(id int primary key auto_increment,name varchar(10));

給沒有主鍵的表添加主鍵

alter table user add id int primary key auto_increment;

約束

? 唯一

? 關鍵字 unique

create table user5 (id int primary key auto_increment，name varchar(10),mobile varchar(11) unique --此字段值唯一 )

? 非空

? 關鍵字 not null

create table user5 (id int primary key auto_increment，name varchar(10),mobile varchar(11) unique, --此字段值唯一sex varchar(2) not null )

? 默認值

? 關鍵字 default

create table user5 (id int primary key auto_increment，name varchar(10),mobile varchar(11) unique, --此字段值唯一sex varchar(2) not null,age int default 18 )

嵌套查詢

select 語句可以把語句查詢出來的結果集當成表來再次查詢

select name from (select * from user_db where sex = ‘女’)as nigu;

union 關鍵字

將兩個結果集上下拼接起來。要求兩個結果集的表結構一致

select *from student where sex ='女' union select * from student where name = '馬化騰';

表之間的關系

一對多：

? 用戶表：購物記錄表：

? id name id userid spname

? 100 張三 1 100 小米手機

? 101 李四 2 100 蘋果手機

? 3 101 巧克力

? 4 100 華為手機

多對多：

雙向一對多，就是多對多。

? 用戶表：中間表購物記錄表：

? id name id userid gwid id userid spname

? 100 張三 1 100 1 1 100 小米手機

? 101 李四 2 101 3 2 100 蘋果手機

? 3 100 4 3 101 巧克力

? 4 100 2 4 100 華為手機

一對一：

? 一張表里的一條記錄，對應另一張表里的一條記錄

? 用戶表：用戶頭像表：

? id name id userid Picture

? 100 張三 1 100 1.png

? 200 李四 2 200 2.png

外鍵

一個表的某個字段，是另一張表的主鍵

外鍵約束

外鍵表在入更新數據時，會從主檢查表是否存在，如果不存在，則插入更新失敗。

如何創建外鍵：

constraint：約束

? froeign ：外鍵

? references: 引用

? constraint：外鍵名稱 foreign key 當前表的外鍵字段 references 主鍵表主鍵字段

? 創建主表：數據復制自 testdb.student表

create table user6 (id int primary key auto_increment,---主鍵name varchar(10) );

? 從表：

create table sp (id int primary key auto_increment,---主鍵spname varchar(30),userid int,constraint spuserid foreign key (userid) references user6(id) );

將某鍵改為主鍵：

alter table user add primary key（id）

將某鍵改為主鍵并自增：

alter table user change id id int not null auto_increment;

外鍵約束：

從表里如果插入的數據不包含在主表的主鍵里，那就插入失敗

如果從表數據已經包含了主表的信息，那主表刪除對應主鍵的記錄時會失敗

如果要刪除主表的數據，必須先將從表里的關聯的數據全部刪除之后才能刪除

刪除外鍵方法

alter to table 表名 drop forrign key 外鍵名alter to table sp drop foreign key spuserid

刪除外鍵就是刪除兩個之間的關系

多表查詢

笛卡爾積：

進行多表查詢時，數據庫會將兩張表的數據進行組合。笛卡爾積會生成無意義的數據。

如果要必免生成笛卡爾積，那就要在確認關聯字段，使用條件過濾。

內連接：

內連接，查詢兩張表的交集數據

? 1.隱式內連接

select a.name,b.spname from user6 as a,sp as b where a.id=b.userid;

? 2.顯示內連接

select * from sp as a inner join user6 as b on a.userid=b.id;

外連接

? 1.左外連接

select 字段名1,字段名2，... from 表1 left [outer] join 表2 on 條件

? 2.右外連接

select 字段名1,字段名2，... from 表1 right [outer] join 表2 on 條件

grant 賦權語句

revoke 收回用戶權限語句

備份還原

mysqldump 備份數據庫

登錄之前在命令提示抽口執行

mysqldump -u root -p 數據庫名 > 備份出的文件要保存的位置

source 還原是數據庫

登錄數據庫后執行

source D:/testdb.sql

范式

1.什么是范式

? 范式就是數據庫建表的規范

? 常用的范式有一范式，二范式，三范式

一范式

? 每個字段儲存的數據都是不可再分的原子數據

二范式

一張表只能描述一件事，如果不滿足就需要進行拆分

所有的字段都必須依賴于主鍵，也就是說，每行都有一個唯一標識

三范式

從表的外鍵必須使用主表的主鍵

視圖

1.什么是視圖

? 視圖是一句select查詢語句。是一張虛擬表

2.如何創建視圖

? 語法：

create [ or replace][algorithm] = {undefinde| merge | temptable} view 視圖名稱 [列名1，列名2，列名3......] as select 語句[with check option]

整個表作為視圖

部分表作為視圖

指定視圖的列名

3.對視圖進行操作時的注意事項

視圖可以當作表來用，支持用戶對視圖中的數據進行查詢操作，DML操作

不是所有的視圖都可以進行dml操作

例如創建視圖的語句包含有下列關鍵字，那就無法將其當作表來進行更新操作

聚合函數

DISTINCT關鍵字

group by 關鍵字

having 關鍵字

union關鍵字

from 子句中包含多個表

select 子句中引用了不可更新的視圖

where 子句中的子查詢引用from子句中的表

algorithm 選項指定為temptable

位于選擇列表中的子查詢

4.刪除視圖

drop view 視圖名稱;

表與視圖的區別

? 視圖已編譯好的sql語句，而表不是

2.視圖沒有實際的物理記錄，而表有

3.表是內容，視圖是窗口

4.表占用物理空間，而視圖不占用，它只是邏輯存在

5.表可以及時修改，而視圖只能由創建語句來進行修改

6.視圖是查看數據表的方法從安全角度來看，視圖可以只給用戶看到表的部分字段，

從而不知道表的結構

7.表是全局中的表，是實表，視圖局部模式，是虛表

8.視圖的創建和刪除只影響視圖本身，而不會影響到基本表

視圖的優缺點

? 優點：

? 1.使用視圖可以定制用戶數據，聚焦特定數據

? 2.使用視圖可以簡化數據庫的操作

? 3.使用視圖對基表有一定的安全性

? 4.使用視圖可以合并分離數據，也可以創建分區視圖

? 缺點：

? 1.視圖有修改限制

? 2.視圖的性能差

索引

1.什么是索引及索引的作用

? 索引相當于字典中的目錄，使用索引可以提高數據查詢的速度

? 索引本質是一張地址表，它里面報訊了主鍵與索引字段的地址

? 經常變動的列不適合建立索引

? 數據分布均勻的列不適合建立索引

? 記錄太少的表不適合建立索引

2.創建普通索引

create index 索引名字 on 表名(列名)

3.創建唯一索引

create unique index 索引名字 on 表名(列名);

4.創建復合索引

index 索引名字 on 表名 (列名,列名2);

5.使用alter語句也可以給表添加或刪除索引

6.刪除索引

drop index [索引名] on 表名

7.顯示表中有那些索引

show index from 表名\G

索引基礎使用

普通索引查詢條件里加上建立索引的列

select *from student where name = '馬云'

復合索引查詢條件里的字段順序要和創建索引時的順序一致

select * from student where name =’馬云‘ and math = 80;

如果選擇要顯示的字段，那順序也要一致

select name , math from student where name = '馬云' and math = 80;

復合索引創建時排一位的字段叫先導列，先導列的順序要排前面

查詢時如果條件上面有建立索引，那就會自動走索引

系統函數

字符串函數

合并字符串函數 concat(s1,s2,…)

返回字符串長度 char_length(str);

返回字節長度 length(str);

將小寫字母轉為大寫字母 ucase(str) 或 upper(str)

將大寫字母轉為小寫 lcase(str) 或 lower(str)

去空格 trim(str) ltrim() rtrim()

字符替換 replace(s,s1,s2); s要操作的字符串，s1要替換的字符串，s2要替換成的字符串

字符串截取 substr(s,strat,length); s要操作的字符串，start開始位置，length長度

字符串比較 strcmp(str1,str2); str1大返回1 str1小返回-1

日期時間函數

now() 或curdate() curtime()

now()返回日期和時間

curdate()返回年月日

curtime()返回時分秒

year(now()或字段名) month(now()或字段名) day(now()或字段名)

last_day(now()或字段名) 返回一個月中的最后一天

adddate(日期,天數) 返回當前日期+天數后的日期

subdate(日期,天數) 返回當前日期-天數后的日期

quarter(日期) 返回日期為一年中的第幾個季度

datediff(日期1,日期2) 返回兩個日期之間差的天數 (注意日期一定要用引號引起來)

date_format(日期,’%Y-%m-%d’) 按指定格式顯示日期。

數字相關的函數

ABS(X) 求絕對值

ceil(x) 向上取整

floor(x)向下取整

mod(x,y) 取余數

RAND() 返回0到1之間的隨機數。

Round(任意小數) 四舍五入

truncate(x,y) 返回數值x 保留到小數點后y位的值。

高級函數

selectcase 字段when 判斷條件1then 希望得到值1when 判斷條件then 希望得到的值2........else前面的條件都不滿足時的值endfrom 表 select case name when '馬云' then '阿里巴巴' when '馬化騰' then '騰訊'else ‘其它’ end，math,english from student;

trim(str) ltrim() rtrim()

字符替換 replace(s,s1,s2); s要操作的字符串，s1要替換的字符串，s2要替換成的字符串字符串截取 substr(s,strat,length); s要操作的字符串，start開始位置，length長度字符串比較 strcmp(str1,str2); str1大返回1 str1小返回-1