MySQL資料庫的部分基礎操作

一、表和表字段的操作

1、修改、删除表名

drop table  student  //删除表
alter table student rename as st //修改表名
           

2、修改和增加、删除表字段

//在表的最後添加字段，必須添加資料類型
alter table student add name varchar(11)

//删除表字段，不要添加資料類型，字段名即可 
alter table student drop name 
 
//批量删除表字段
alter table student drop name,drop gender,drop hobby 

//修改表名（及資料類型），必須要添加資料類型，前面的是被修改的，後面是最後結果的字段
alter table student change gender sex varchar(11)  

//修改表字段資料類型
alter table student modify name varchar(20)  
           

3、增加、删除索引

alter table student add  index name(name)  //建立普通索引
alter table student drop  index name  //删除索引
           

二、資料查詢操作

1、查詢結果去重和不去重

去重：使用distinct或者group by

select distinct name from score
select name from score group by name
           

不去重：使用 Union all

SELECT coures , name ，gender from score where name = '李四' or gender = 'female'

SELECT coures , name ，gender from score where name = '李四' 
union all
SELECT coures , name ，gender from score where gender = 'female'
           

很多時候這兩種查詢的結果是一緻的，但如果需要資料不去重的時候可以使用union all

2、查詢結果排序

select * from score ORDER BY coures  //預設是升序
select * from score ORDER BY coures desc //desc 降序
select * from score ORDER BY coures，grade  //根據多條件排序，放在前的先排序
           

3、條件查詢

3.1 等值判斷

select * from score where grade = 100
           

3.2 不等值判斷

> 、 < 、>= 、<= 、 !=(<>)

select * from score where grade > 97
select * from score where grade >= 97
select * from score where grade < 97
select * from score where grade <= 97
select * from score where grade != 97
select * from score where grade <> 97
           

3.3 邏輯判斷

and or not

select * from score where grade = 97 and name = '李四'
select * from score where grade = 97 or name = '李四'
not    //主要用于判斷空值或者判斷存在的時候
           

3.4 區間判斷

//一般使用範圍的時候可以使用拼接的不等值判斷
select * from score where grade < 98 and grade > 95  //可以得到（95~98）之間的數
select * from score where grade between  95 and 98  //等價上面的語句，小值必須在前
           

3.5 空值判斷

is null 、 is not null

select * from score where name is null  //傳回name是空值的結果
select * from score where name is not null  //傳回name不是空值的結果
           

3.6 枚舉查詢

當查詢的條件是等值判斷，且值為有限個數時，可以使用枚舉查詢

select * from score where grade in (95,96,98)
           

注：枚舉 查詢雖然SQL語句簡便，但是查詢的效率較低，可通過多條件拼接。 3.7 模糊查詢

當想要使用某個字段作為查找條件，但是對這個字段又記的不是很清楚的時候，可以使用模糊查詢

// %代表任意個字元，_代表一個字元，一位
select * from score where name like '張%' //表示以’張‘開頭，後面任意個字元
select * from score where name like '張_' //表示以’張‘開頭，後面1個字元
select * from score where name like '%四' //表示以’四‘結尾，前面任意個字元
select * from score where name like '_四' //表示以’四‘結尾，前面1個字元
           

3.8 分支結構查詢

SELECT name ,coures,
case 
when grade >= 98 then '優'
when grade < 98 then '良'
end '成績水準'
from score  
           

結果：

 分支結構查詢，主要用于操作同一個字段的不同取值範圍

4、時間查詢

select SYSDATE() //擷取目前系統時間（日、月、年、時、分、秒）
select CURDATE() //擷取目前日期
select CURTIME() //擷取目前時間 
select WEEK(DATE) //擷取指定日期為一年中的第幾周
select MONTH(DATE) //擷取指定日期的月份
select YEAR(DATE) //擷取指定日期的年份 
select HOUR(TIME) //擷取指定時間的小時值 
select MINUTE(TIME) //擷取時間的分鐘值 
select DATEDIFF(DATE1,DATE2) //擷取DATE1 和 DATE2 之間相隔的天數 
select ADDDATE(DATE,N) //計算DATE 加上 N 天後的日期 
           

5、字元串查詢

SELECT CONCAT('My','S','QL')  //将多個字元串連接配接
SELECT INSERT('這是一個資料庫',3,2,'MySql') //結果為這是 MySql 資料庫 
SELECT LOWER('MYSQL') //将指定字元串轉換為小寫
SELECT UPPER('mysql') //将指定字元串轉換為大寫
SELECT SUBSTRING('JavaMySQL',5,5) //将JavaMySQL從第5個字元開始截取 5 個内容 
           

6、聚合函數

sum() 求和        avg() 求平均數       max() 求最大數       min() 求最小數        count() 求總行數

SELECT sum(grade) from socre //擷取總成績
SELECT avg(grade) from socre //擷取平均成績
SELECT max(grade) from socre //擷取最高成績
SELECT min(grade) from socre //擷取最低成績
SELECT count(grade) from socre //擷取成績的總行數
           

7、分組查詢

SELECT coures,max(grade)  from score GROUP BY coures
           

8、過濾查詢

SELECT coures ,grade from score  HAVING  grade > 97
           

我們可以發現有些時候，having和where比較類似，實際上差別還是很大的

having和where比較：

1. 資料的來源不同。where是資料從磁盤讀入記憶體的時候進行判斷, 而having是磁盤讀入記憶體後再判斷。如果一個字段沒有被查詢過，也就是不存在記憶體中，這個時候having就不能使用這個字段做判斷，隻能使用where。
2. SQL語句中的位置不同，若SQL語句中存在from、where、group by、having等關鍵字時，where必須要在from關鍵詞之後，group by關鍵詞之前，而having必須在group by之後。
3. 聚合函數的使用。若查詢的條件是聚合函數，則不能使用where而使用having。

9、限定查詢

SELECT grade from score LIMIT 2  //實際是0~2的行數
SELECT grade from score LIMIT 2，5 // 傳回2~5行數的資料
           

三、SQL語句實操

1、查詢每個學生的各科成績及總分和平均分

select st.name as '姓名' ,v1.grade as '國文' ,
v2.grade as '數學',v3.grade as '英語' ,
sum(v1.grade +v2.grade +v3.grade) as '總分',
avg((v1.grade +v2.grade +v3.grade)/3) as '平均分' 
from student st
        left join 
        //查詢出學科為國文的學生和成績，當作v1表
        (SELECT name ,grade  from score where coures = '國文') as v1 on st.name = v1.name
        left join 
        //查詢出學科為數學的學生和成績，當作v2表
        (SELECT name ,grade  from score where coures = '數學') as v2 on v1.name = v2.name 
        left join 
        //查詢出學科為英語的學生和成績，當作v3表
        (SELECT name ,grade  from score where coures = '英語') as v3 on v2.name = v3.name 
        如果沒有分組，在平均分和總分的結果隻會有一條
        GROUP BY st.name 
           

 結果：

解析：

查詢每個學生的每科成績，首先各科成績不是字段，是以需要把單科的分數設定為字段，是以

國文.grade = 國文，數學.grade = 數學，英語.grade = 英語 是我們查找的字段。

.号前是表的名字，是以我們需要語數外三張表。

(SELECT name ,grade  from score where coures = '國文') as v1
(SELECT name ,grade  from score where coures = '數學') as v2
(SELECT name ,grade  from score where coures = '英語') as v3
           

使用左連接配接的方式将四張表聯合查詢就能得到結果

2、查詢每個學科最高分的成績及學生

表格是上面的表

SELECT score.coures as '科目',score.grade as '最高分',score.name as '姓名' 
from (SELECT coures,max(grade) m from score GROUP BY coures) a ,score 
where a.coures = score.coures and a.m = score.grade