DBA成長之路---mysql數據庫服務基礎（二）

管理表記錄 

增加

insert into 庫.表 values(字段值列表);

insert into 庫.表(字段值列表) values(字段值列表);

insert into 庫.表 values(字段值列表),(字段值列表);

查詢

單表查詢

select 字段名列表 from 庫.表 where 條件；

條件匹配的表示方法

數值比較

        字段名 符號 值 符號：>  >=  <  <=  =  !=

        字符比較

        字段名 符號 "值"  符號： =  !=

        范圍內匹配

        字段名 in (值列表)在...里

        select id,name from user where name in ("apache","root","bob");

        select id,name,uid from user where uid in (1,2,3,4);

        字段名 not in(值類表)不在...里

        select name from user where uid not in(0,1,2,3,4,5);

        字段名 between 值  and 值 在...之間(數值類型)

        select * from user where id between 10 and 15;

        select name from user where uid between 1 and 10;

        匹配空  is null

        字段名 is null

        匹配非空  is not null

        字段名  is not null

        select id from user where name is null;

空的定義

        insert into user(name) values (""),("null"),(null);

        select id,name from user where name="";

        select id,name from user where name="null";

        select id,name from user where name is null;

        mysql> select id,name from user where id between 45 and 47;

        +-------+----------+

        | id      | name    |

        +-------+----------+

        |  45    |               |

        | 46     | null       |

        | 47     | NULL   |

        +-------+-----------+

        3 rows in set (0.00 sec)

        不顯示重復值

        distinct 字段名

        select shell from user;

        select distinct shell from user;

        mysql> select distinct shell from user;

        +----------------------------+

        | shell                           |

        +----------------------------+

        | /bin/bash                   |

        | /sbin/nologin             |

        | /bin/sync                    |

        | /sbin/shutdown         |

        | /sbin/halt                    |

        | /bin/false                    |

        | NULL                          |

        +----------------------------+

        7 rows in set (0.00 sec)

邏輯匹配： 有多個條件時

        邏輯與  and  多個條件必須都成立

        邏輯或or多個條件有一個成立即可

        邏輯非  !取反

        select id,name from user where name="zhangsan"and uid=500 and shell="/bin/bash";

運算操作  +  -  *  /  %

        字段名 符號  字段名

        select uid+gid as heid from user where name='root';

        select uid+gid  heid from user where name='root';

模糊查詢

        where 字段名 like '表達式'

        _匹配任意一個字符 % 0個或多個字符

        select name from user where name like '____' and uid <= 10;

        select name from user where name like '%a%';

正則匹配

        where 字段名 regexp '正則表達式';

        . ^  $   [ ]  *

        mysql> select name,uid from user where uid regexp '^..$';

函數

        簡單篩選/統計

        avg() 集合平均值

        sum()對集合的各參數求和

        min() 集合中的最小值

        max() 集合中的最大值

        count() 記錄的各數

查詢排序 

        sql查詢 order by 字段名 asc/desc(降序)

        select name,uid from user where uid between 10 and 50 order by uid ;

查詢分組 

        sql查詢 group by 字段名

        select shell from user where uid between 10 and 50 group by shell;

        和不顯示重復相似

查詢限制顯示行數 limit

        select shell from user where uid between 10 and 50 limit 1;

        select * from user limit 1;#顯示查詢的前一行

        select * from user limit 2,3;#設置顯示行范圍  從第2行顯示（行數開始為0行） 顯示3行

多表查詢

        select 字段名列表 from 表名列表；笛卡爾集

        select 字段名列表 from 表名列表 where 條件；

        create table studb.t1 select name,uid,shell from user limit 3;

        create table studb.t2 select name,uid,homedir from user limit 4;

        select t1.*,t2.homedir from t1,t2 where t1.uid =t2.uid;

嵌套查詢

        where 嵌套查詢：把內層的查詢結果做為外層查詢的查詢條件

        select 字段名列表 from 表名 where 條件 (select 字段名列表 from 表名 where 條件)

        select name,uid from user where uid > (select avg(uid) from user);

        select name from user where name in (select user from mysql.user);

復制表：作用：快速建表，備份表

        create table 庫.表 sql查詢；

        復制表

        create database dbbak;

        create table dbbak.user2 select * from user;

        復制表沒有源表的屬性和鍵值

復制表結構

        create table dbbak.user3 select * from user where 1=2;

連接查詢

        左連接查詢

        select 字段列表 from  表A left join 表B on 條件

        右連接查詢

        select 字段列表 from  表A right join 表B on 條件

        create table studb.t3 select name,uid,shell from user limit 3;

        create table studb.t4 select name,uid,shell from user limit 5;

        mysql> select * from t3 left join t4 on t3.uid=t4.uid;#以左為主 顯示

        mysql> select * from t3 right join t4 on t3.uid=t4.uid; #以右為主 顯示

修改

    批量修改

        update 庫.表 set 字段名=值，字段名='值'

        mysql> update user set age="18";

        修改指定記錄字段的值

        update 庫.表 set 字段名=值，字段名='值' where 條件

        mysql> update user set name="zhangsan" where id=48;

刪除

        以行為刪除單位

        delete from 庫.表 where 條件；

        mysql> delete from user where shell is NULL;

mysql 鍵值（限制如何給字段賦值）

普通索引 index

        什么是索引 : 類似“一個書的目錄” 樹型目錄結構

        索引的優點 : 加快查詢的速度

        索引的缺點 : 減慢寫的速度 （insert update delete）;占用物理存儲空間

使用普通索引  索引index

        索引的使用規則

        默認可以重復，可以賦NULL值

        可以由多個index字段

        把查詢條件做為索引

        查看decs 表名；

        show index from 表名；

        標志 MUL

        創建

        建表時創建索引：

        mysql> create table t25(

            -> name char(10),

            -> age int,

            -> sex enum("boy","girl"),

            -> index(sex)#索引名 默認和字段名相同

            -> index(name)

            -> );

        在已有表創建索引 create index 索引名 on 表名（被賦索引的字段名）

        mysql> create index age on t21(age);

        mysql> show index from t21\G;

        Table: t21

           Non_unique: 1

             Key_name: age

         Seq_in_index: 1

          Column_name: age

            Collation: A

          Cardinality: 4

             Sub_part: NULL

               Packed: NULL

         Null: YES

           Index_type: BTREE

              Comment: 

        Index_comment: 

        默認使用的索引類型（Index_type）：BTREE（二叉樹）

        還支持 hash B+TREE

        刪除 drop index 索引名 on 表名；

        mysql> drop index a1 on t21;

fulltext 全文索引

unique 唯一索引

一個表中有多個unique字段

可以為空 但是有值不能重復

mysql> create table t211( stu_id char(9), name char(10), sex enum('boy','girl'), unique(stu_id) );

mysql> desc t211;

key標識是UNI 

mysql> alter table t211 modify stu_id char(9) not null;

mysql> desc t211;

key標志是PRI 但是不是主鍵

mysql> drop index stu_id on t211;

創建 unique index

mysql> create unique index stu on t211(stu_id);

主鍵

        主鍵使用規則

        一個表中只能有一個primary key

        不允許重復 不能為空

        查看 decs 表名；

        標志 PRI

        創建 建表時創建主鍵：

        mysql> create table t26(

            -> name char(10),

            -> age int,

            -> likes set("a","b","c"),

            -> primary key(name)

            -> );

        mysql> create table t22(

            -> id int primary key,

            -> name char(10)

            -> );

        在已有表創建主鍵：

        mysql> alter table t25 add primary key(name);

        刪除 alter table 表名 drop primary key;

        mysql> alter table t25 drop primary key;

        復合主鍵 多個字段一起做主鍵 字段的值不允許同時重復

        查看

        mysql> desc t28;

        建表時創建主鍵：

        mysql> create table t28(

            -> cip char(15),

            -> port smallint,

            -> status enum("allow","deny") defualt "deny",

            -> primary key(cip,port)

            -> );

        在已有表創建主鍵：

        mysql> alter table t28 add primary key (cip,port);

        刪除

        mysql> alter table t28 drop primary key;

        主鍵一般 與auto_increment 連用

            字段值自動增長

        滿足條件 主鍵 數值類型

        創建表

        mysql> create table t27(

            -> id int(2) zerofill primary key auto_increment,

            -> name char(10),

            -> class char(4),

            -> index (name)

            -> );

        刪除自動增長的主鍵

        mysql> alter table t27 modify id int(2) unsigned zerofill not null;

        mysql> alter table t27 drop primary key;

外鍵 

        作用：限制給字段賦值的。值必須在指定表中指定字段值的范圍里選擇

        表的存儲引擎必須是 innodb

        字段類型要一致

        被參照字段必須要是索引類型的一種

        創建命令

        foreign key(字段名) references 表名(字段名) 

        on update cascade同步更新

        on delete cascade同步刪除

        update 表名 set 字段名=值  where 條件;

        delete from 表名 where 條件

        刪除外鍵

        mysql> show create table xsb;#查看建表命令

        可以查看外鍵名

        alter table 表名 drop foreign key 外鍵名

        在已經創建的表上添加外鍵

        alter table 表名 add foreign key(字段名) references 表名(字段名) 

        on update cascade同步更新

        on delete cascade同步刪除

mysql 服務的體系結構：（8個功能模塊）

        連接池：檢查是否可以連接mysql

       sql接口： 執行的命令 傳遞給mysqld

        分析器：分析語法錯誤

        優化器：優化執行命令

        查詢緩存：數據庫的物理內存劃分出的 每次查詢 先找查詢緩存

        存儲引擎：

        文件系統：

      管理工具：安裝mysql給提供的一些軟件工具

mysql存儲引擎:

存儲引擎介紹

        mysql 數據庫服務軟件自帶的程序。

        不同的存儲引擎有不同的功能和數據存儲方式

        查看數據庫服務支持的存儲引擎

        mysql> show engines;

        | InnoDB             | DEFAULT |#default 默認存儲引擎

        | MyISAM             | YES     |

常用的存儲引擎

    myisam

        表.frm 表結構

        表.MYI索引信息

        表.MYD數據

        支持表級鎖（鎖一張表）

        不支持事務 不支持事務回滾

   innodb

        表.frm 表結構

        表.ibd表結構 索引信息

        支持行級鎖（只給當前被訪問的行加鎖）

        支持事務 事務回滾

        事務日志文件 ：記錄對innodb存儲引擎的表執行過的操作

        /var/lib/mysql/ib_logfile*

   鎖：

        鎖類型：讀鎖 select 

         寫鎖insert delete update

        鎖粒度：行級鎖  表級鎖

        鎖作用：解決并發訪問沖突問題

 事務：一次從開始訪問到訪問結束的過程

        事務回滾：一次數據訪問 任意一步執行失敗，恢復所有操作。

        事務的特性：一致性，原子性，隔離性

        最典型的事務操作：銀行轉賬

        工作如何決定表使用的存儲引擎

        接收寫操作多的表適合使用innodb存儲引擎。（并發訪問大）

        接收讀操作多的表適合使用myisam存儲引擎。(節省資源)

設置數據庫服務的存儲引擎

        設置服務的默認存儲引擎

        [mysqld]

        defaulf-storage-engine=myisam

        mysql> create table tt1(id int(2));

        mysql> show create table tt1;

        ...

        | tt1   | CREATE TABLE `tt1` (

          `id` int(2) DEFAULT NULL

        ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

        ...

        修改表的存儲引擎

        alter table 表名 engine=存儲引擎；

        設置表的存儲引擎

        creat table 表名（...）engine=存儲引擎;