mysql客戶端工具的使用

mysql客戶端工具

mysql

mysqldump

mysqladmin

mysqlcheck

mysqlimport

[client]

-u USERNAME

-h host

-p 

--protocol {tcp|socket|pipe|memory}

mysql非客戶端工具

myisamchk

myisampack

MyISAM：每表三個文件，無事務功能，支持表鎖

.frm：表結構

.MYD：表數(shù)據(jù)

.MYI：表索引

InnoDB:支持事務，行鎖

所有表共享一個表空間文件：

建議：每表一個獨享的表空間文件：編輯/etc/my.cnf 添加innodb_file_per_table=1

.frm：表結構

.ibd：表空間（表數(shù)據(jù)和表索引）

mysql

--user, -u

--host, -h

--password ,-p

--port

--protocol

--database database, -d 指定登錄以后默認的數(shù)據(jù)庫

mysql>

交互模式

批處理模式（腳本模式）

# mysql < test.sql

< source test.sql

mysql>

命令兩類：

客戶端命令

服務器語句：有語句結束符，默認；

delimiter ，\d :定義語句結束符

客戶端命令：

\C ,clear : 提前終止語句執(zhí)行

\g ,gO：無論語句結束符是什么，直接將此語句送至服務端執(zhí)行

\G ,ego：無論語句結束符是什么，直接將此語句送至服務端執(zhí)行，而且結果以豎排方式顯示

\! ，system : 執(zhí)行shell命令

\W ，warnings :語句結束后顯示警告信息

\# ，rehash : 對于新建的對象，支持補全功能

補全：

名稱補全

輸出格式：

#mysql -uroot -p --html

#mysql -uroot -p --xml

服務器端命令獲取幫助：

help keyword ：help create table

# mysqladmin [options] command [arg] [command  [arg] ]...

# mysqladmin -uroot -p password 'new_passwd'

create database

drop database

ping：查看是否在線

processlist ：查看鏈接數(shù)

status

--sleep N：顯示狀態(tài)

--count N : 顯示多少個狀態(tài)

extended-status： 顯示狀態(tài)變量

veriables：顯示服務器變量

flush-privileges：讓mysqld重讀授權表，等同于reload

flush-stastus：重置大多數(shù)的服務器狀態(tài)變量

flush-logs：二進制和中繼日志滾動

flush-hosts：

refresh : 相當于同時執(zhí)行flush-hosts和flush-logs

shutdown : 關閉mysql服務器進程

version：服務器版本及當前狀態(tài)信息

start-slave :啟動復制，啟動從服務器復制線程

SQL thread

IO thread

stop-slave : 關閉復制

mysqldump，mysqlimport ，mysqlcheck

show engines;

show tables status [like ...]

# mysql --help --verbos  查看mysql支持的指令

hostname.err :錯誤日志

無法啟動mysql

1、此前服務未關閉

2、數(shù)據(jù)初始化失敗

3、數(shù)據(jù)目錄位置錯誤

4、數(shù)據(jù)目錄權限問題

DBA:

開發(fā)DBA : 數(shù)據(jù)庫設計、SQL語句、存儲過程、存儲函數(shù)、觸發(fā)器

管理DBA：安裝、升級、備份、恢復、用戶管理、權限管理、監(jiān)控、性能分析、基準測試

mysql數(shù)據(jù)庫類型和sql模型

數(shù)據(jù)類型

數(shù)值型

精確數(shù)值

int

近似數(shù)值

float

double

real

字符型

定長：char, binary（區(qū)分大小寫）

變長：varchar , varbinary

text , blob

enum  , set

日期時間型

date , time , datetime ,timestamp

數(shù)據(jù)類型：

1、存入的值類型

2、占據(jù)的存儲空間

3、定長還是變長

4、如何比較及排序

5、是否能夠索引

修飾屬性：

not null 不允許為空

null   允許為空

default 默認值

character set 字符集

collation 排序規(guī)則

>show character set; 顯示數(shù)據(jù)庫支持的所有字符集

>show collation;顯示字符集的排序規(guī)則

auto_increment ：必須是整形（int）、非空（not null）、無符號（unsigned）、主鍵（primary key）或唯一鍵

create table test(id int unsigned auto_increment not null primary key,name char(20))

> SELECT LAST_INSERT_ID(); 查詢上一次的id結果

ENUM

DNS

RRtype CHAR(5)

A, PTR, CNAME, AAAA, MX, NS, SOA, SRV

RRtype ENUM('A','PTR')

SQL模型

CHAR(3)

abc, abcdefg

mysql服務器變量

作用域，分為兩類

全局變量

show global variables

會話變量

show [session] variables

生效時間，分為兩類

動態(tài)：可即時修改，

靜態(tài)：

寫在配置文件中

通過參數(shù)傳遞給mysqld

動態(tài)調(diào)整參數(shù)的生效方式：

全局：對當前會話無效，只對新建立會話有效

會話：即時生效，但只對當前會話有效

服務器變量：@@變量名

顯示：select

設定：set global|session變量名='value'

SQL語句:

數(shù)據(jù)庫

表

索引

視圖

DML

數(shù)據(jù)庫：

create database| schema [if noet exists] db_name [character set=][collate=] 新建數(shù)據(jù)庫

create schema if not exists class character set'gbk' collate 'gbk_chinese_ci';

alter {database | schema}  [db_name].. 修改數(shù)據(jù)庫
drop {database | schema} [if exists] db_name 刪除數(shù)據(jù)庫

表創(chuàng)建

1、直接定義一張空表

create table myclass( class_id int(2) not null auto_increment primary key,class_name varchar(40) not null,teacher varchar(16) not null);

insert into myclass(class_name,teacher)values('yuwen','lihua'),('huaxue','like'),('dili','zhanghua'),('english','anhong');

2、從其他表中查詢出數(shù)據(jù)，并以之創(chuàng)建新表

create table test_class select * from myclass where class_id <=3;

3、以其他表為模板創(chuàng)建一個空表

create table test2_class like myclass;

CREATE TABLE [IF NOT EXISTS] tb_name (col_name col_defination, constraint )

CREATE TABLE testtb (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, Name CHAR(20) NOT NULL, Age TINYINT NOT NULL)  ENGINE [=] engine_name

CREATE TABLE testtb (id INT UNSIGNED NOT NULL AUTO_INCREMENT, Name CHAR(20) NOT NULL, Age TINYINT NOT NULL, PRIMARY KEY(id),UNIQUE KEY(name),INDEX(age))

單字段：

primary key 主鍵

unique key  唯一鍵

單或多字段：

pramary key(col,....)

unique key(col,...)

index (col,...)

鍵也稱作約束，可用作索引，屬于特殊索引(有特殊限定)：B+Tree
CREATE INDEX創(chuàng)建索引
SHOW INDEXES FROM tb_name：顯示指定表上的索引

show table status like 'table_name'\G :顯示表屬性

修改表定義：

alter table

添加、刪除、修改字段

alter table myclass add starttime date default'2016-09-01';

添加、刪除、修改索引

alter table myclass add unique key (teacher);

改表名

alter table test_class rename to test;

rename table test to test_class;

修改表屬性

alter tables testtb engine=[InnoDB|MyISAM]

刪除表：

InnoDB支持外鍵

索引創(chuàng)建：

create index index_name on table_name(col_name,...)

col_name [(length)] [asc | desc]

刪除索引：

DROP [ONLINE|OFFLINE] INDEX index_name ON tbl_name

DML：

select

insert into

delete

update

查詢語句類型：select

簡單查詢

多表查詢
子查詢

SELECT * FROM tb_name;

SELECT field1,field2 FROM tb_name; 投影

SELECT [DISTINCT] * FROM tb_name WHERE qualification; 選擇

DISTINCT：相同的值只顯示一次

from子句： 要查詢的關系         表、多個表、其它select語句

where子句：布爾關系表達式

=、>、>=、<=、<

邏輯關系：and, or , not

between...and ...

like ''

%: 任意長度任意字符

_：任意單個字符

rlike 支持正則表達式

in (列表)：

is null

is not null

order by field_name {asc|desc} 排序

字段別名：as  select name as test_name from class

limit子句：limit [offset(偏移量),]Count（取值）

聚合：sum(), min(), max(), avg(), count()

group by：分組，一般用于聚合場景

having ：過濾group by的結果

select conut(cid) as persons,cid from students group by cid having persons>=2

多表查詢：

連接：

交叉連接：笛卡爾乘積

自然連接：

select s.name,c.cname from students as s  where class as c on s.cid=c.cid

外連接：

左外鏈接：... left join ...on ....

select s.name,c.cname from students as s left join class as c on s.cid=c.cid

右外鏈接 : ..right join ...on ...

select s.name,c.cname from students as s right join class as c on s.cid=c.cid

自鏈接：

子查詢：

比較操作中使用子查詢：子查詢只能返回單個值

select name from students where age >(select avg(age) from students);

in():使用子查詢

select name from students where age in (select age from teacher);

在from中使用子查詢

select name,age from (select name,age from students) as t where t.age >20;

聯(lián)合查詢：union

(select name,age from students) union (select name,age from teacher)

練習：

1、挑選出courses表中沒有被students中的CID2學習的課程的課程名稱；

select cname from coureses where cid not in(select distinct cid2 from students where cid2 is not null)

附加：挑選出沒有教授任何課程的老師，每個老師及其所教授課程的對應關系在courses表中；

select tname from tutors where tid not in (select distinct tid from courses);

找出students表中CID1有兩個或兩個以上同學學習了的同一個門課程的課程名稱；

select cname from courses where cid in (select cid1 from students group by cid1 having count(cid1)>=2);

2、顯示每一位老師及其所教授的課程；沒有教授的課程的保持為NULL；

select t.tname,c.cname from tutors as t left join courses as c on t.tid=c.tid;

3、顯示每一個課程及其相關的老師，沒有老師教授的課程將其老師顯示為空；

select t.tname,c.cname from tutors as t right join courses as c on t.tid=c.tid;

4、顯示每位同學CID1課程的課程名及其講授了相關課程的老師的名稱；

select name,cname,tname from studengts,courses,tutors where students.cid1=courses.cid and courses.tid=tutors.tid;

視圖：存儲下來的select語句

基于基表的查詢結果

create view view_name as

drop view view_name

show create :查看創(chuàng)建對象時，使用的語句

mysql -e ：mysql -e "select * from class"

添加語句insert：

insert into tb_name (col1,col2,...) values （val1.val2,....）[(val1,val2,...)...]

insert into tb_name(col1,col2,col3) select col1,col2,col3 from tb_name2 where ....

字符型，單引號3

數(shù)值型，不需要引號

日期時間型

空值，null，''

replace into 插入的內(nèi)容重復就替換

刪除語句delete

delete from tb_name where condition;

truncate tb_name :清空表，并重置auto_increment計數(shù)器

更新語句update

update tb_name set col1=...  , col2=... where condition;

連接管理器：

接受請求

創(chuàng)建線程

認證用戶

建立安全連接

并發(fā)控制：

mbox：MDA

C/S： 100

10分鐘：

多版本并發(fā)控制： MVCC

鎖：

讀鎖：共享鎖

寫鎖：獨占鎖

LOCK TABLES tb_name {READ|WRITE};

UNLOCK TABLES

鎖粒度：從大到小，mysql服務器僅支持表級鎖，行鎖需要由儲存引擎完成

表鎖

頁鎖

行鎖