mysql數據庫的具體介紹和使用

1 數據庫操作 （所以命令可大小寫，規范為大寫）

創建數據庫：(數據庫名開頭不能為數字)

create? database? db2；（注意添加結尾的分號）

刪除數據庫：

drop database? db1;

查看數據庫列表:

show databases;

進入其中的某個數據庫：

use db1;

進入某個數據庫后查看此庫里有那些表：
show? tables;

在數據庫中使用bsah的命令：stem/\!? +bash命令? （stem? ls /etc /或者\!? ls /etc/）

數據庫的終端命令可查看幫助：> help

查看支持所有字符集：SHOW CHARACTER SET; ?
查看支持所有排序規則：SHOW COLLATION; ?
獲取命令使用幫助： mysql> HELP KEYWORD;

 

2 .創建表? （數據庫下存放各種表）

創建表：CREATE TABLE

(1) 直接創建 新表?：

create table students ( id tinyint unsigned not null primary key,name char(10) not null,sex char(1),phone char(11) );

（第一項為創建的id，采用最小的整數，不為空，且為主鍵；第二項為姓名；給定十個字符；第四個為性別，給一個字符；第五項為電話號碼，設定為11個字符）

create table emp ( id int unsigned auto_increment primary key,name char(4) not null ,sex char(1) default ‘m’,adress char(20) );

auto_increment：數字自動遞增；unsigned 放在int定義數字的后面

小結：一個漢字，字母數字在char里都算一個字符。primary key放在最后；添加各種修飾符要注意前后順序，如上例中，如果將修飾符的順序更改則會報錯不能生成此表；

(2) 通過查詢現存表創建新的表，連數據一起創建了。

create table user select user,host,password from mysql.user;

mysql.user:mysql是一個庫，user是其庫里的一個表

(3) 通過復制現存的表的表結構創建，但不復制數據

create table user1 like mysql.user;

 

3 .表操作

查看創建的表結構：

desc? students；

查看庫里的表；(首先進入到此庫中然后查看)

show tables;

刪除庫里的某個表: (首先還是要進入到存放此表的庫里，查看后刪除)

drop table students

級聯刪除；

如果某兩個表之間是相互關連的關系，是不能刪除其中一個的，會報提示錯誤。

查看單個表狀態：

show table status like ‘students’\G; ? （表名必須要加引號）

查看庫中所有表的狀態：
show table status from db1\G; ? （G 為豎列查看）

 

對創建好的表進行修改：（一般表創建好后不建議去修改它，會造成數據的丟失）

對已經創建好的表添加一個字段：

alter table students add address char (11) after name;

對students表添加一個地址的字段，定義地址字段的格式，在name 字段后添加；

對已經創建好的表刪除某個字段：

alter table students drop address

對students表刪除一個 字段

ALTER TABLE s1 CHANGE COLUMN phone mobile char(11); （對phone字段改名并重新定義數據類型）

 

 

數據類型 ：

選擇正確的數據類型對于獲得高性能至關重要，

三大原則： ?

更小的通常更好，盡量使用可正確存儲數據的最小數據類型 ?

簡單就好，簡單數據類型的操作通常需要更少的CPU周期 ?

盡量避免NULL，包含為NULL的列，對MySQL更難優化

具體內容查看30天的第二個視頻

1、整數的范圍（找自己表合適的使用,使用越大的范圍占用的內存空間越大）

tinyint ? ? ? ? ?? 1個字節 范圍(-128~127) ?

smallint ? ? ? ? 2個字節 范圍(-32768~32767) ?

mediumint ?? 3個字節 范圍(-8388608~8388607) ?

int ? ? ? ? ? ? ? ? ? 4個字節 范圍(-2147483648~2147483647) ?（無論是幾都要占用4個字節）

bigint ? ? ? ? ? ?? 8個字節 范圍(+-9.22*10的18次方) ? （無論是幾都要占用8個字節）

取值范圍如果加了unsigned，則最大值翻倍，如tinyint unsigned的取值范圍為(0~255) 取正整數。

 

2、浮點型(float和double)，近似值 ?

float(m,d) 單精度浮點型 8位精度(4字節) m總個數，d小數位 ?

double(m,d) 雙精度浮點型16位精度(8字節) m總個數，d小數位 ?

設一個字段定義為float(6,3)，如果插入一個數123.45678,實際數據庫里存的是 123.457，但總個數還以實際為準，即6位

3、定點數 ?

在數據庫中存放的是精確值,存為十進制 ? decimal(m,d) 參數m<65 是總個數，d<30且 d<m 是小數位

4 .字符串(char,varchar,_text)

char(n) 固定長度，最多255個字符 （常用的定義表字符的長度最少時4個字符，與下面的比效率高）

varchar(n)可變長度，最多65535個字符 ? （同上最少可以為一個字符減少空間的使用，但效率低）

tinytext 可變長度，最多255個字符 ?

text 可變長度，最多65535個字符 ?

mediumtext 可變長度，最多2的24次方-1個字符 ?

longtext 可變長度，最多2的32次方-1個字符 ?

BINARY(M) 固定長度，可存二進制或字符，長度為0-M字節 ?

VARBINARY(M) 可變長度，可存二進制或字符，允許長度為0-M字節

修飾符
?所有類型：

? NULL 數據列可包含NULL值

? NOT NULL 數據列不允許包含NULL值

? DEFAULT 默認值 ? PRIMARY KEY 主鍵

? UNIQUE KEY 唯一鍵

? CHARACTER SET name 指定一個字符集 ?數值型

? AUTO_INCREMENT 自動遞增，適用于整數類型 ? （數字自動增長不需要手動在添加了）

? UNSIGNED 無符號

 

 

4? .對表進行添加信息，刪除信息，等操作

1 . 向表里添加記錄;

insert students values(1,’bai’,’beijing’,’m’,’100233′);

字符要加引號。

添加后的表：select ? * ? from students ? (*代表查看表里的所有的內容)

另一種方式添加記錄：

insert students(id,name) values(2,’wang’);對指定的某個字段進行添加，字段和要賦值的內容要相對應。

其他沒有指定的字段內容默認為null

也可同時添加多行記錄：

insert students values(3,’li’,’shanghai’,’f’,’100010′),(4,’zhang’,’hefei’,’f’,’121212′);

 

最后一種批量將舊表中的某些數據插入到新表的指定的字段當中：

insert emp(name,adress) select user,host from user2;

emp為新表，user2為所查的表。將user2表的兩項內容，user和host賦值到要插入表emp的name和user2兩個字段中。

2 . update; 更改表里的某行記錄的某一個字段：

update students set sex=’m’,phone=’44444′ where id=2;

where id=2 （修改的指定范圍；既將id=2的哪一行的兩個字段更改掉）

如果where不寫系統會默認更改，所有行的sex和phone的字段。

如果where查詢的字段有多個相同的，但只想更改其中的兩個可以增加限制，但此限制只能按順序選定，

update student? set sex=’m’ where sex=’f’ limit2;? (按順序只更改前兩個后面的滿足條件也不更改了)

 

3 . 對表里的內容進行刪除：

truncate table students? （快速刪除整個表，且不可恢復）

delete from students where id=4; （刪除id=4的哪一行）

delete from students；如果不寫where限定條件，默認會刪除此表的所有內容，如果在配置文件里面添加

vim /etc/my.cnf.d/mysql-clients.cnf

[mysql]

safe-updates

添加此行內容就不會刪除所有的內容了。

 

 

 

 

5? .SELECT （搜索查詢）

select * from students where sex=’m’;（查詢表里的所有字段，限定條件為，sex=m）

select id,name from students where sex=’m’;? (查看表里的某些特定的字段，限定條件為sex=m)

限定條件可以加多個：

select * from students where sex=’m’? and ? id=2

WHERE子句：

指明過濾條件以實現“選擇”的功能：

過濾條件：布爾型表達式 算術操作符：+, -, *, /, % 比較操作符：=, !=, <>, <=>, >, >=, <, <=

BETWEEN min_num AND max_num? (在兩者之間)select * from students where id between 2 and 4 ;

IN (element1, element2, …) ? ?select * from students where sex in (‘f’,’m’,null);

IS NULL ? （表示的為空，查找是遇到null的不能寫=號只能用is）

select * from students where sex is null;

IS NOT NULL ? （表示的為非空，查找是也不能用=號只能用is）

select * from students where sex is not null;

邏輯操作符：

NOT? （取反）

AND ? （并且）

OR? （或者）

XOR （異或）

select * from students where id>2;? （查找id號大于2的行）

select * from students where not id>2;（id>2并取反，即是查找id<=2的行）

模糊搜索查詢：

LIKE:

%: 任意長度的任意字符

_：任意一個個字符

select * from students where name like ‘%w%’? （name字段包含有w的）

 

 

字段顯示可以使用別名

格式；字段名as字段的別名 （前面為字段的名稱后面為定義的別名，只是用于顯示）

select name as 姓名,sex as 性別 from students where sex is not null;

 

 

 

6 .分組：

GROUP：根據指定的條件把查詢結果進行“分組”以用于做“聚合”運算

avg(), max(), min(), count(), sum()? （平均值；最大值；最小值；組里成員的個數；總和；）

下面是此表的內容：

select sex from students group by sex;? （一旦做分組前后的sex應該是相互關連的，不能隨便寫了）

select sex,max(address) from students group by sex; （以性別為分組統計address中的最大值）

max(address) 把max換成avg統計以性別為分組的平均成績；換成sum 為男女組的總成績；等等；

 

統計上圖中一班和二班男女生的平均成績：

select class,sex,avg(address) from students group by sex,class;? （group分組根據多個條件）

統計效果如下圖所示：

HAVING: 對分組聚合運算后的結果指定的限定條件 ：

select sex,class,avg(address) from students group by class,sex having avg(address) > 80;（一班和二班男女生的平均成績：并加限定條件，大于80分的，此處的限定條件就不能用where了，語法要求在分組后加限定條件只能用having ）

ORDER BY: 根據指定的字段對查詢結果進行排序

升序：ASC

降序：DESC

select * from students order by age asc; (對表中對其age組進行升序排列)

select * from students order by age desc;(對表中對其age組進行降序排列)

select sex,class,avg(address) from students group by class,sex asc; (分組時可以如此使用升序排列)

select sex,class,avg(address) from students group by class,sex desc;? （分組時可以如此使用降序排列）

小結：如果有null的想要將其排在最后：可以在分的組名上添加—

例如：select sex,class,avg(address) from students group by -sex desc ；（此實例中只按性別進行分組）

 

 

7 .多表查詢 (多個表關連進行查詢)

 

上圖為多表聯系的7中結構：

如何將寫的數據庫程序導入到mysql表中：

第一種方法：mysql < /hellodb_innodb.sql? （沒有進入數據庫前）

第二種方法：source hellodb_innodb.sql ?? （在進入數據庫后）

上圖中的結構分析：

第三種為：內連接

select s.name,t.name from students as s,teachers as t where s.teacherid=t.tid;? （取交集老舊的寫法）

（s.name中，s為后面students的別名，組和使用為了確保它是students的name的字段的唯一性，以免其他表中有相同的字段，t.name也是此意思；where后面的限制條件為：students表中的teacherid=teachers表中的tid;要顯示的內容為：students表中的name和teachers表中的tid）

現在一般采用較新的寫法：

select s.name as student_name,t.name as teacher_nameb from students as s inner join teachers as t on s.teacherid=t.tid;

用inner join …on 代替where;

其中inner join前面是一個表后面是一個表然后在on后面添加條件。

inner join 可以取多個表的交集 ；

例如：表A inner join 表B on 條件1? inner join 表C? on? 條件2 ；（等依次類推）

（將A和B先查詢后在和C進行內連接的查詢）

select * from students cross join teachers;（笛卡爾乘積）：兩張表的記錄完全組和一遍類似于{1，2，3}{4，5，，6} （交叉組和各種可能）

子查詢：（將一個查詢的結果定義為一個限定的條件）

select * from students where age > (select avg(age) from students);（括號里的為查詢平均年齡作為限定條件來使用）

 

第一種為；左外連接 （左邊的全部都要，右邊只要交集的部分；交集的部分按條件規則來取）

select s.name as student_name,t.name as teacher_name from students as s left outer join teachers as t on s.teacherid=t.tid; ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?? （此為左邊的表全部留下來） ? ?? （右邊的表只留交集）

第二種為：右外連接（右邊的全部都要，左邊的只要交集）

select s.name as student_name,t.name as teacher_name from students as s right outer join teachers as t on s.teacherid=t.tid; ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?? （此為左邊表只要交集） ? ? ? ?? （此為表的右邊全部都要）

 

第四種為：左內連接

第五種為；右內連接

第六種為：

第七種為：

 

練習 （單表查詢的）
導入hellodb.sql生成數據庫 ?

兩種方法：

第一種：mysql < /hellodb_innodb.sql （沒有進入數據庫前）
第二種：source hellodb_innodb.sql （在進入數據庫后）
(1) 在students表中，查詢年齡大于25歲，且為男性的同學的名字和年齡

select name,age from students where age>25 and gender=’m’;

select name,age from students where age>25 and gender=’m’ order by age; （對查詢的結果在進行年齡的排序）

(2) 以ClassID為分組依據，顯示每組的平均年齡

?select avg(age),classid from students group by classid ;

(3) 顯示第2題中平均年齡大于30的分組及平均年齡

?elect avg(age),classid from students group by classid having avg(age) > 30;

（group分組時如果需要添加限定條件只能用having ）

(4) 顯示以L開頭的名字的同學的信息 ?

select * from students where name like ‘l%’；

(5) 顯示TeacherID非空的同學的相關信息 ?

select * from students where teacherid is not null;

（當遇到null時必須使用is 不能用=號了）

select * from students where not teacherid is null; (反向使用也是可以的)

(6) 以年齡排序后，顯示年齡最大的前10位同學的信息 ?

select * from students order by age desc limit 10;

（先反向排序后在限定只查找前十位的信息）

(7) 查詢年齡大于等于20歲，小于等于25歲的同學的信息

select * from students where age>20 and age<=25;

（并且的限定條件用and）

小結：對于select搜索查詢；題目要得到的顯示結果應該寫在select……….from students 之間；

搜所的限定條件寫在where 后面；做題的思路如是；

練習：（多表查詢）

導入hellodb.sql，以下操作在students表上執行 ?

1、以ClassID分組，顯示每班的同學的人數 ?

select count(classid) as sum ,classid from students group by classid;

（as后面為定義的別名）

2、以Gender分組，顯示其年齡之和 ?

select sum(age),gender from students group by gender;

3、以ClassID分組，顯示其平均年齡大于25的班級 ?

elect avg(age) as ping,classid from students group by classid having ping > 25 and classid is not null;

4、以Gender分組，顯示各組中年齡大于25的學員的年齡之和

select gender,sum(age) from (select * from students where age > 25) as t group by gender;

（第一次搜索的表作為第二次查詢的表必須要添加別名，否則無法執行）

5、顯示前5位同學的姓名、課程及成績 ?

select name,score,course from students as st inner join scores as sc inner join courses as co on st.stuid=sc.stuid and co.courseid=sc.courseid limit 10;

(因為每個名字都是重復的所以xuan)

6、顯示其成績高于80的同學的名稱及課程； ?

select st.name as student_name,sc.score as score,course from students as st inner join scores as sc inner join courses as co on st.stuid=sc.stuid and co.courseid=sc.courseid and score > 80;

7、求前8位同學每位同學自己兩門課的平均成績，并按降序排列 ?

8、顯示每門課程課程名稱及學習了這門課的同學的個數 ?

9、如何顯示其年齡大于平均年齡的同學的名字 ?

10、如何顯示其學習的課程為第1、2，4或第7門課的同學的名字 ?

11、如何顯示其成員數最少為3個的班級的同學中年齡大于同班同學平均年齡的同學 ?

12、統計各班級中年齡大于全校同學平均年齡的同學