MySQL基礎2——常用命令

lmandcc發表於2021-09-05

注意：MySQL在centos中安裝的是5.7版本的，編輯MySQL時會有個報錯，需要執行：

set @@global.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

1. SQL語句:每個命令執行結束加分號結束　　　　　

　　　　　　查詢所有資料庫:show databases;

　　　　　　切換資料庫:use 庫命名;

　　　　　　建立資料庫:create database [IF NOT EXISTS] 庫名;

　　　　　　刪除資料庫:drop database [IF EXISTS] 庫名;

　　　　　　查詢資料庫建立:show 建庫語句;

　　　　　　指定資料庫採用的字符集:CHARACTER SET

　　　　　　修改資料庫的編碼集:alter database 資料庫名 CHARACTER SET 編碼集;

　　　　　　注意:不要修改mysql伺服器的編碼集,表的編碼集預設和庫一致

2. 建表

　　　　格式:

　　　　　　create table [if not exists] 表名(

　　　　　　欄位1 資料型別欄位屬性,

　　　　　　欄位2 資料型別欄位屬性,
　　　　　　...

　　　　　　欄位N 資料型別欄位屬性

　　　　　　)engine=引擎 default charset=編碼集;

　　　　　　檢視當前資料庫:select database();

　　　　　　檢視建表語句:show create table 表名;

　　　　　　檢視錶結構:desc 表名;

　　　　　　刪除:drop table [if exists] 表名;

3.欄位屬性:

　　　　　　not null:沒給值資料為預設值(varchar預設值為空

　　　　　　AUTO_INCREMENT定義列為自增的屬性,一般用於主鍵,數值會自動加1

　　　　　　PRIMARY KEY關鍵字用於定義列為主鍵,您可以使用多列來定義主鍵，列間以逗號分隔

　　　　　　ENGINE 設定儲存引擎，CHARSET 設定編碼

　　　　　　default null:沒給值資料就是null

　　　　　　default 值:設定欄位的預設值

　　　　　　注意:主鍵不重複的列
這裡我們建立一個student表：

1 create table if not EXISTS student (
2         id int auto_increment,
3         `name` VARCHAR(32),
4         age int,
5         sex char(1),
6         clazz VARCHAR(32)) charset utf8;

1 insert into student values (1001,'zs',18,'男','一班');
2 insert into student values (1002,'ls',19,'女','二班');
3 insert into student(`name`,age,sex,clazz) values ('ww',69,'男','一班');
4 insert into student(`name`,age,sex,clazz) values ('we',21,'女','二班');
5 insert into student(`name`,age,sex,clazz) values ('ld ',23,'男','一班');
6 insert into student(`name`,age,sex,clazz) values ('lq',45,'女','二班');
7 insert into student(`name`,age,sex,clazz) values ('lwq',23,'男','一班');
8 insert into student(`name`,age,sex,clazz) values ('ld',12,'女','二班');

4.修改表:alter table

修改表名:alter(rename) table 舊錶名 to 新表名;

rename table student1 TO `student`;

新增欄位:alter table 表名 add 欄位欄位資料型別屬性;

1 alter table student add job varchar(32) default '沒有工作' ;
2 insert into student (job) VALUES('a');
3 insert into student (job) VALUES('b');
4 insert into student (job) VALUES('c');
5 insert into student (job) VALUES('a');
6 insert into student (job) VALUES('b');

修改欄位:alter table 表名 change 舊欄位新欄位資料型別屬性;

1 alter table student change clazz clazz varchar(255);
2 alter table student change age score double;

修改欄位:alter table 表名 modify 欄位資料型別屬性;

alter table student MODIFY varchar(356); #這裡不能比之前的空間小

注意:

　　　　change:修改所有(欄位名,資料型別,屬性)

　　　　modify:修改一部分(資料型別,屬性)

　　　　修改資料型別時,varchar->int後設資料會變為0

5. 增刪改查:字串全部使用''包起來
1)增:

 1 格式:
 2     insert into 表名(欄位) values(值),(值)...(值);
 3 insert into student values (1001,'zs',18,'男','一班');
 4 insert into student values (1002,'ls',19,'女','二班');
 5 insert into student(`name`,age,sex,clazz) values ('ww',69,'男','一班');
 6 insert into student(`name`,age,sex,clazz) values ('we',21,'女','二班');
 7 insert into student(`name`,age,sex,clazz) values ('ld ',23,'男','一班');
 8 insert into student(`name`,age,sex,clazz) values ('lq',45,'女','二班');
 9 insert into student(`name`,age,sex,clazz) values ('lwq',23,'男','一班');
10 insert into student(`name`,age,sex,clazz) values ('ld',12,'女','二班');

2) 刪

1 -- 刪除delete from 表名 where 子句;
2 delete from student where job='c';

3)改

1 -- 改update 表名 set 欄位1=值1,欄位2=值2...欄位N=值N where 子句;
2 update student set job='b'where name ='ls';

4) 查

1 -- 查select 欄位 from 表名 where 子句;
2 select * from student ; #查詢全部
3 SELECT id as di,name,job,score from student where score>18; #特定查詢，並且展示特定的表 as:表示改欄位名稱（原來的表不發生變化）

注意:
　　*表示所有欄位

6. 子句:

　　　　> < <= >= = <> 大於、小於、大於(小於)等於、不等於

　　　　between ...and... 顯示在某一區間的值(含頭含尾)

　　　　in(set) 顯示在in列表中的值，例：in(100,200)只能匹配100或200

　　　　like '張_' 模糊查詢使用% 和 _(%表示匹配所有 _匹配一個)

　　　　Is null 判斷是否為空

　　　　and 多個條件同時成立

　　　　or 多個條件任一成立

　　　　not 不成立，例：where not(expection>10000);

 1 -- >   <   <=   >=   =    !=    大於、小於、大於(小於)等於、不等於
 2 SELECT * from student WHERE id>1006;
 3 SELECT * from student WHERE id!=1006;
 4 
 5 --between  ...and...    顯示在某一區間的值(含頭含尾)
 6 select id,name,job from student  where id BETWEEN  1002 and 1005;
 7 select * from student where job BETWEEN 'a' and 'b';
 8 
 9 -- in(set)    顯示在in列表中的值，例：in(100,200)只能匹配100或200
10 select * from student where job in('a','b');
11 
12 -- like '張_'    模糊查詢  使用% 和 _(%表示匹配所有 _匹配一個)
13 SELECT * from student where name like 'l%';
14 SELECT * from student where name like 'l_';
15 
16 -- Is null    判斷是否為空
17 select * from student where name is not null;

7.limit分頁
格式:
　　語句 limit 開始下標,長度;

1 -- limit分頁    語句 limit 開始下標,長度;注意：沒有where
2 select * from student LIMIT 1,2;
3 select * from student LIMIT 0,2;
4 select * from student LIMIT  2;

注意:
　　如果資料量不夠,顯示全部

8.去重
格式:
　　DISTINCT 欄位1,欄位2...欄位N

1 -- 去重 DISTINCT 欄位1,欄位2...欄位N
2 select DISTINCT name from student;
3 select count(DISTINCT name) from student;

注意:

　　欄位不能在DISTINCT之前,只能在DISTINCT後面

　　DISTINCT之後有多個欄位,按照所有欄位進行去重

9.聚合函式:

　　　　　　count(欄位):求多少行資料

　　　　　　sum(欄位):求和

　　　　　　avg(欄位):平均數

　　　　　　max(欄位):最大值

　　　　　　min(欄位):最小值

注意:
　　　　　　varchar能比較大小,不能獲取avg(沒有任何意義)
　　　　　　如果值為Null不參與計算
　　　　　　sum和avg欄位的資料不是數值,結果都是0

 1 -- count(欄位):求多少行資料
 2 select count(*) from student;
 3 select count(name) from student;
 4 
 5 -- sum(欄位):求和
 6 select sum(score) from student;
 7 select sum(job) FROM student;
 8 select name+score as sum FROM student; #score的值
 9 SELECT name*score as cheng FROM student; #0
10 
11 -- avg(欄位):平均數
12 SELECT avg(score) FROM student;
13 
14 -- max(欄位):最大值
15 SELECT max(score) FROM student;
16 SELECT max(job) FROM student; #c
17 
18 -- min(欄位):最小值
19 SELECT min(score) FROM student;

10.拼接:

　　格式1:

　　　　concat(str1,str2...)

　　格式2:

　　　　concat_WS(separator,str1,str2,...)

1 -- 格式一:concat(str1,str2...)
2 select CONCAT(id,'-',name) as pj FROM student;
3 -- 格式二:concat_WS(str1,str2...)
4 SELECT CONCAT_WS('~',id,name,score,job)FROM student; #中間以~隔開

11.日期函式

　　　　獲取當前日期:

　　　　　　　　　　current_timestamp;--所有

　　　　　　　　　　current_timestamp();--所有

　　　　　　　　　CURRENT_DATE();-- 年月日

　　　　　　　　　CURRENT_DATE;-- 年月日

　　　　　　　　　CURRENT_TIME();-- 時分秒

　　　　　　　　　CURRENT_TIME;-- 時分秒

 1 -- 獲取當前日期:
 2 --         current_timestamp;--所有
 3 SELECT CURRENT_TIMESTAMP from student;
 4 --         current_timestamp();--所有
 5 SELECT CURRENT_TIMESTAMP() from student;
 6 --         CURRENT_DATE();-- 年月日
 7 select CURRENT_DATE() from student;
 8 --         CURRENT_DATE;-- 年月日
 9 select CURRENT_DATE from student;
10 --         CURRENT_TIME();-- 時分秒
11 SELECT CURRENT_TIME() FROM student;
12 --         CURRENT_TIME;-- 時分秒
13 SELECT CURRENT_TIME FROM student;

時間轉str
　　　　格式:
　　　　　　　　date_format(date,format)
　　　　　　　　date:時間
　　　　　　　　format:格式
str轉日期
　　　　格式:
　　　　　　　　str_to_date(str,formaat)

 1 SELECT * FROM date;
 2 -- 時間轉str
 3 --         格式:
 4 --             date_format(date,format)
 5 --             date:時間
 6 --             format:格式
 7 select DATE_FORMAT('2021-09-01','%Y~%m~%d');
 8 --     str轉日期
 9 --         格式:
10 --             str_to_date(str,formaat)
11 SELECT STR_TO_DATE('2021-09-01','%Y-%m-%d');

日期相減
　　　　格式:
　　　　　　　　datediff(expr1,expr2);
　　　　注意:只能相減年月日,時分秒參與運算結果為null

1 --         datediff(expr1,expr2);
2 --     注意:只能相減年月日,時分秒參與運算結果為null
3 SELECT DATEDIFF('2021-09-09','2021-09-01');

函式向日期新增指定的時間間隔
　　　格式:
　　　　　　DATE_ADD(date,INTERVAL expr unit);
　　　　　　date:時間
　　　　　　INTERVAL:關鍵字
　　　　　　expr:間隔的數值
　　　　　　unit:年月日時分秒(..,...,day,..,..,..)

1 SELECT DATE_ADD('2021-09-09',INTERVAL +10 YEAR);
2 SELECT DATE_ADD('2021-09-09',INTERVAL +10 DAY);

12. 陣列計算
　　round(x,d):四捨五入
　　　　x:值
　　　　d:保留幾位小數點

　　ceil(x):向上取整
　　floor(x):向下取整
　　rand():隨機數(0-1之間)

 1 -- 陣列計算
 2 --     round(x,d):四捨五入
 3 --         x:值
 4 --         d:保留幾位小數點
 5 SELECT ROUND(1.3,2); #2表示保留幾位小數
 6 
 7 --     ceil(x):向上取整
 8 SELECT ceil(1.2);
 9 
10 --     floor(x):向下取整
11 SELECT floor(1.2);
12 --     rand():隨機數(0-1之間)
13 SELECT rand();

13.排序

　　　　格式:
　　　　　　　　order by 欄位1 asc|desc,欄位2 asc|desc...欄位n asc|desc;

1 SELECT * from student ORDER BY score,job;
2 SELECT * from student ORDER BY score desc, job desc;

　　　　注意:
　　　　　　　　預設升序asc,降序desc
　　　　　　　　如果有多個欄位,按照先後順序依次排序

14. group by 分組
　　格式:
　　　　group by 欄位1,欄位2...欄位n;
　　注意:
　　　　多個欄位,按照所有欄位進行分組(一起分組)
　　　　有多少組顯示多少條資料(預設情況下,沒有經過條件篩選)
　　　　每組顯示的資料為每組中預設第一條資料
　　　　gruop by 通常和聚合函式一起使用

 1  select max(score) as c from student where score=c;
 2  select max(score) as c from student having score=c;
 3  兩個都不能執行
 4 
 5 
 6 SELECT count(*),job,`name`,id as c from student GROUP BY sex where c>2; #錯誤
 7 SELECT count(*) as c,job,`name`,id from student GROUP BY sex HAVING c>2;
 8 
 9 
10 -- select id,name,sex from student where job='a'; # 可以執行
11 -- select id,name,sex from student having job='a'; #不能執行(顯示了之後就沒有job)
12 -- 執行過程是 from-where-select-having
13 -- select count(*) c from student where c>1; -- 不行
14 -- select count(*) c from student having c>1;-- 行
15 select count(*) c,sex from student group by sex where sex='男';
16 select count(*) c,sex from student group by sex having sex='男';
17 
18 
19 --where having 一起使用
20 SELECT count(*)as c,name,id FROM student where sex='男' HAVING c>3;
21 where 是對錶中from到的資料進行篩選；
22 having是對錶中selec顯示資料進行曬選；

MySQL基礎2——常用命令

相關文章