mysql學習筆記

標籤：

1.資料庫的作用：儲存資料.
     資料庫儲存資料和其他方式儲存的不同之處：
          1.可以儲存大量資料
          2.資料不會丟失
          3.安全
          4.共用
          5.查詢資料方便
2.資料庫的發展曆史：
     1.檔案系統.
     2.資料庫系統：
           網狀結構
           層狀結構
           關係型結構:sqlserver,mysql
           關係-對象型資料庫: oracle(神喻 Scott)
                access,db2,sysbase
 3.名詞：
      DBMS: Database managerment system 資料庫管理系統
      DB: 資料庫
 4.安裝
 5.sql: structer query language :結構化查詢語言 (SQL)
      分類：
          1.DML: 資料操作語言 (insert,update,delete)
          2.DCL: data control language 資料控制語言 (grant ,revoke)
          3.DDL: data define language 資料定義語言 (Data Definition Language) (create,drop)
          4.DQL: data query language 資料查詢語言 (select)
          5.TPL: 交易處理語言
 6.sql中的注釋：#注釋.

 資料類型：
     int,smallint,long
     float,double
     char :固定長度的字串
     varchar :可變長度的字串.指定的長度是最大長度.
     date,datetime,timestamp(時間戳記,自動插入(1970.1.1-2037.12.31))
     blob: binary large object
     clob: character large objct

 #建立資料庫
 create database mydb ;
 #刪除資料庫
 drop database mydb ;
 #查看所有的資料庫
 show databases ;
 #更改當前的資料庫
 use mydb ;
 #查看建立資料庫的sql語句
 show create database mydb ;


 #建立表
 create table t1
 (
    id int ,
    name varchar(20) ,
    age int
 );
 #查詢表的所有資料
 select * from t1 ;

 #刪除表
 drop table t1 ;
 #查看建立表的sql語句
 show create table t1 ;
 #查看當前資料庫中所有表
 show tables ;

 #設定前端工具的字元編碼
 #設定插入字元的編碼
 set character_set_client=dbk;
 #設定結果集的編碼
 set character_set_results=gbk;
 #插入語句
 insert into t1(id,name,age) values(1,‘張三‘,20) ;
 insert t1(id,name,age) values(2,‘張無忌‘,21) ;
 insert t1 values(3,‘張三丰‘,25) ;
 #更新語句
 #修改一列的資料
 update  t1 set name = ‘張翠山‘ ;
 #修改某一行的資料
 update t1 set name = ‘張三丰‘ where id = 2 ;
 
 #刪除
 delete from t1 where id = 3 ;
 #刪除所有的資料
 delete from t1 ;

 #查詢
 #顯示所有的記錄
 select * from t1 ;  # *表示所有的欄位
 #顯示所有記錄的姓名
 select name from t1 ;
 #顯示id=3的記錄的姓名
 select name from t1 where id = 3 ;
 #顯示多個欄位，欄位之間用逗號隔開
 select name,age from t1 where id = 3 ;
 #顯示年齡大於等於23的人
 select * from t1 where age >=23 ;
 #顯示姓名是郭靖的人
 select * from t1 where name = ‘郭靖‘ ;
 #顯示年齡在23到35之間的人
 select * from t1 where age >=23 and age <=25 ;  # and or not
 select * from t1 where age between 23 and 25 ;
 #顯示年齡是21或者姓名叫令狐沖的人
 select * from t1 where age = 21 or name = ‘令狐沖‘;
 #顯示姓名不叫郭靖的人
 select * from t1 where name !=‘郭靖‘ ;
 select * from t1 where name <>‘郭靖‘ ;
 
 #模糊查詢
 #萬用字元
   % :代表任意多個字元(0~多個)
   _ :代表的是任意一個字元(必須的有)
   []: 代表的是在某個個區間(mysql不支援)
   [^] :代表的是不在某個個區間(mysql不支援)

 #查詢所有姓張的人
 select * from t1 where name like ‘張%‘ ;
 #查詢姓張，名字長度是2個字元的人
 select * from t1 where name like ‘張_‘ ;
 #查詢名字中包含一個張字的人
 select * from t1 where name like ‘%張%‘ ;

 #完整性 : 正確性+ 準確性=完整性.
    對資料施加約束.
    自訂完整性：自己定義的約束
    值域完整性： 由語言本身定義的約束
    實體完整性: 要求每一張表必須有一個主鍵.
         主鍵：唯一的去區別記錄的某一列或者幾列的值。
    參考完整性:針對兩張表。一張表中的某個欄位引用另一張表的主鍵.此欄位稱為外鍵。
       外鍵所在的表叫字表或從表。被引用的表叫主表.
         1.添加記錄時必須先添加主表中的記錄，再添加子表記錄.
         2.當存在參考關聯性的時候，不能修改主表中記錄的主鍵.
         3.當刪除記錄的時候，必須先刪除字表中的記錄，再刪除主表中的記錄.

#建立表，設定欄位operation
#timestamp類型可以主動插入也可以自動由系統插入值
create table t3(
    id int ,
    name varchar(20),
    operation timestamp
);

#插入一條記錄
insert into t3(id,name) values(1,‘張三‘) ;

#建立表student,並添加各種約束
create table student
(
    id int primary key ,   #主鍵約束
    name varchar(20)  ,  #唯一約束
    age int NOT NULL, #非空約束
    sex varchar(2) ,
    address varchar(20) default ‘重慶‘   #預設約束
) ;


insert into student(id,name,age,sex,address) values(1,‘張無忌‘,20,‘男‘,‘北京‘) ;
insert into student(id,name,age,sex,address) values(2,‘張三丰‘,20,‘男‘,‘北京‘) ;
insert into student(id,name,age,sex,address) values(3,‘小龍女‘,16,‘女‘,‘古墓‘) ;
insert into student(id,name,age,sex) values(4,‘黃蓉‘,18,‘女‘) ;
insert into student(id,name,age,sex,address) values(5,‘令狐沖‘,25,‘男‘,default) ;
insert into student(id,name,age,sex,address) values(6,‘郭靖‘,25,‘男‘,‘桃花島‘) ;


#示範別名的問題
#給欄位或者表可以起別名(用as關鍵字指定，as可以省略).
select id as 編號,name as 姓名, sex 性別,age 年齡,address 地址 from student ;

#添加修改欄位的問題
#添加一個欄位birthday date
alter table student add birthday date ;
#查看錶的結構
desc student ;
#刪除欄位 birthday
alter table student drop column birthday ;

#示範distinct(不同的)關鍵字
#distinct必須寫在所有欄位的前面，distinct指的是後面所有欄位都不同。
#查看student表中有幾個年齡
select distinct age from student ;
#查看姓名和不同的年齡
select distinct name,age from student ;

#查詢null的值
#查詢沒有名字的人
select * from student where name = null ;  #差不到
select  *from student where name is null ;
select  *from student where name is null or name = ‘‘;

#示範關鍵字in(表示在....裡面)
#查詢地址在桃花島，北京的人
select * from student where address = ‘桃花島‘ or address = ‘北京‘ ;
select * from student where address in(‘桃花島‘,‘北京‘) ;

#建立分數表
create table score
(
    id int primary key ,
    sid int ,
    china int ,
    history int,
    english int,
    constraint FK_sid foreign key(sid) references student(id)  
) ;

insert into score values(1,1,54,85,69) ;
insert into score values(2,3,71,60,89) ;
insert into score values(3,4,71,34,80) ;
insert into score values(4,6,87,79,81) ;

#查詢給每個人語文成績提高5後的成績
#欄位支援各種運算式
select china + 5 from score ;
#查詢一下每個人考試總分
select china + history + english 總分 from score ;

#示範in語句
#在一個 select語句中嵌套一個select語句，稱為子查詢.
#子查詢必須寫在where 條件中.
#查詢一下參加考試的人的姓名
select name from student where id in(select sid from score) ;
#查詢一下參加考試的人的姓名和成績
select name,china,history,english from student s,score c where  s.id = c.sid ;
#查詢一下參加考試的人的姓名和成績,總分
select name,china,history,english,china + history+english 總分 from student s,score c where  s.id = c.sid ;
#查詢一下沒有參加考試的人的姓名
select name from student where id not in(select sid from score) ;

#排序(order by)
#如果值一樣，則預設再按主鍵升序排。
#顯示語文成績降序排序
select * from score order by china desc ;
#按多個欄位排序
select * from score order by china desc,id desc;
select * from score order by china asc,id desc;
#按照運算式排序
#按照總分降序排序
select *,china + history + english 總分 from score order by china + history + english desc ;

#多表查詢
#分類：內聯，外聯(左外連結，右外連結),交叉串連
#查詢的原理：
   表是由頁組成，記錄存放在頁中。每頁會有一個索引。每個表有一個索引頁。頁的大小是8k.
#交叉查詢(cross join)
select * from student cross join score ;
#內聯查詢(用主外鍵的關係)(inner join)
#查詢參加考試人的姓名
select name from student s inner join score c on s.id = c.sid ;
#查詢沒有參加考試人的姓名
select name from student s inner join score c on s.id != c.sid ;  #交叉查詢

#內聯查詢和子查詢的關係：
     所有的內聯都可以用子查詢替換.子查詢不一定能用內聯查詢替換.

#左外連結是以左邊的表為基準（左邊的表有多少條記錄，結果一定有多少條記錄）
#(left outer join ,outer可以省略)
#顯示所有人的成績
select s.*,c.* from student s left outer join score c on s.id = c.sid ;
select s.*,c.* from student s left join score c on s.id = c.sid ;
select s.*,c.* from student s right join score c on s.id = c.sid ;

#示範關鍵字limit n,m , n是下標, m是截取幾條記錄
#limit關鍵字只適用於mysql
#顯示總成績前三名
select s.name,c.china,c.history,c.english, china + history + english 總分 from student s,score c
    where s.id = c.sid
    order by china + history + english desc limit 0,3 ;
#顯示年齡最大的3個人
select * from student order by age desc limit 0,3 ;

#示範auto_increment
#auto_increment的欄位類型必須是int或相關的類型
create table t4
(
    id int primary key auto_increment,
    name varchar(20)
) ;

#彙總函式
#max,min,sum,avg,count
#查詢語文最高分
select max(china) from score ;
#查詢年齡的總和
select sum(age) from student ;
#查詢總共有多少學生
select count(*) from student;

#分組
group by
#根據性別分組
select count(*) from student group by sex ;
#希望顯示男，女和分組的情況
select sex,count(*) 個數 from student group by sex;

注意：select關鍵字後的欄位除過彙總函式外只能寫根據分組的欄位
select name,sex,count(*) 個數 from student group by sex,name;

#根據條件進行分組
#having 分組條件
#條件是分組條件
#根據性別進行分組，要求組中成員的年齡大於17
select sex,count(*) from student where age > 17 group by sex ;
#根據性別進行分組，要求組中成員大於3個人
select sex,count(*) from student group by sex having count(*) >=3 ;

#where,on,having三個代表條件的區別：
 不能互換使用。where代表的是普通條件。on用在多表串連查詢中。having只能
 用於分組查詢中。
 where關鍵字一般跟欄位的運算式。
 having關鍵字後一般都是局和函數運算式。


總結select語句的完整的寫法.
select [欄位...] from [表名,表名] where  ...
   group by  欄位運算式... having ....
   order by  欄位1，欄位2...
   limit .....
 
  執行過程: 先執行where條件過濾. 再執行group by,在執行order by ..最後執行limit

 
 #函數
 #日期函數
 #查詢當前的時間
 select now() ;  #年月日，時分秒
 select current_date() ; #年月日
 select current_time() ; #時分秒
 year(日期) ;   #擷取年   SELECT YEAR(NOW());

 #字串函數
 left(字串，長度) 從字串的左邊截取多長
 right(字串，長度
 #數學函數

 #資料庫的備份與恢複
#備份(語句後不要加分號)
在dos視窗中敲入mysqldump -u root -proot mydb>d:/mydb.sql
#恢複(語句後不要加分號,先建立好資料庫)
1.在dbms(資料庫管理系統)的用戶端敲入：source d:/mydb.sql
2.在dos環境下敲入：mysql -u root -proot mydb<d:/mydb.sql

#註：備份的檔案的尾碼名可以使任意的。



#建立使用者zhangwuji,密碼123 (必須用root建立，root是超級使用者)
create user zhangwuji identified by ‘123‘ ;
#給使用者zhangwuji賦看到資料庫mydb的許可權
grant all on mydb to zhangwuji ;
#給使用者zhangwuji賦動作表t1的許可權
grant all on t1 to zhangwuji ;   #給zhuangwuji賦予了增刪改查四種許可權
grant select on t1 to zhuangwuji ; #給zhuangwuji賦予查詢四種許可權
grant select,insert on t1 to zhuanguji ; #給zhuangwuji賦予查詢和添加四種許可權

#回收zhangwuji對錶t1的增刪改查許可權
revoke all on t1 from zhangwuji ;

mysql學習筆記