鏈接數據庫 MySQL -uroot -hlocalhost-p 密碼

一、DDL語句(對數據庫內部對象進行創建、刪除、修改等操作的語言) 1、創建數據庫 test1 語法: CREATE DATABASE dbname 例子:create database test1

2、查看所有庫 show databases;

3、使用庫 use dbname;

4、查看庫中的表 show tables;

5、刪除庫 drop database dbname; 例子:drop database test1;

6、創建表 語法：create table tablename(column_name1 column_type1 constraints1, column_name2 column_type2 constraints2); (column_name1(名字) column_type1 (數據類型) constraints1(約束條件)) 例子：create table emp(ename varchar(10), hiredate date, sal decimal(10,2), deptno int(2)); create table u_c_t_ab(id int(11) PRimary key auto_increment, uin int(11) not null , tid int(11) not null , addtime int(11) not null, c_t_status tinyint(1) not null default 1) ;

7、查看表的定義(列、數據類型、約束條件等) desc tablename; 更詳細的可用:show create table emp /G; 其中/G代表按字段豎向排列 engine 代表迎請 charset代表字符集

8、刪除表 drop table tablename；

9、修改表 column 可以不寫 a、修改表類型 語法:alter table tablename modify [column] column_definition[first|after col_name] 例子:alter table emp modify ename varchar(20); 將表emp中的ename 的類型改為varchar(20)

b、增加字段 語法:alter table tablename add[column] column_definition[first|after col_name] 例子:alter table emp add column age int(3); 在表emp中增加age，類型為int(3) 例子:ALTER TABLE market_goods_list ADD limit_buy TINYINT( 2 ) NOT NULL DEFAULT ‘0’ COMMENT ‘0不限購,1限購’ AFTER remain_count

c.添加自增字段 ALTER TABLE user_relation_aa ADD id INT( 11 ) PRIMARY KEY AUTO_INCREMENT FIRST ; ALTER TABLE test_lz ADD unickname VARCHAR( 32 ) UNIQUE ; ALTER TABLE test_lz ADD UNIQUE (unickname);

一次添加多個字段 alter table tinfo_aa add t_flower_count int(11) not null default 0 COMMENT ‘鮮花數量’, add t_official_mark int(11) not null default 0 COMMENT ‘1官方收錄’, add t_reward_flower int(11) not null default 0 COMMENT ‘0 無，正數為懸賞數量’, add t_is_rewarded tinyint(2) not null default 0 COMMENT ‘1 已懸賞’, add t_popular_buy int(11) not null default 0 COMMENT ‘推廣位’

c、刪除表字段 語法:alter table tablename drop[column] col_name 例子:alter table emp drop column age; 刪除表emp中的 age字段

d、字段改名 語法:alter table tablename change [column] old_column_name column_definition[first|after col_name] 例子:alter table emp change age age1 int(4); 將表emp中的age改名為age并修改字段為int(4)

e、修改字段排列順序 [first|after col_name]就是用來排序的 兩種排序方式 first最前面 after ename 在ename后面 例子:alter table emp modify age1 varchar(20) first; 將age1排序到最前 alter table emp modify age1 varchar(20) after ename;將age1排序到ename后面

f、修改表名 語法:alter table tablename rename[to] new_name 例子:alter table emp rename emp1; 將emp改名為emp1

二、DDL語句(對表記錄的操作語句) 1、插入記錄 語法:insert into tablename(field1, field2, field3,…,fieldn) values(value1,value2,value3,…,valuen); 例子:insert into emp(ename,hiredate,sal,deptno) values(‘lizuhi’,’1013-05-15’,’2000’,2); 可以只對部分字段插入值： insert into emp(ename,sal) values(‘lizuhi’,’2000’); 可以一次插入多個值(以逗號隔開)：insert into emp(ename,hiredate,sal,deptno) values(‘lizuhi’,’1013-05-15’,’2000’,2),(‘lizuhi2’,’1013-05-15’,’2000’,2) ,(‘lizuhi3’,’1013-05-15’,’2000’,2);

如果在INSERT語句末尾指定了ON DUPLICATE KEY UPDATE，并且插入行后會導致在一個UNIQUE索引或PRIMARY KEY中出現重復值，則執行舊行UPDATE；如果不會導致唯一值列重復的問題，則插入新行。例如，如果列a被定義為UNIQUE，并且包含值1，則以下 兩個語句具有相同的效果： //先執行插入，如果對應的行存在，就改為更新 INSERT INTO TABLE (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;

2、更新記錄 語法:update tablename set file1=value1,file2=value2,…,filen=valuen [where condition] 例子:update emp set ename=’haofei888’ where sal=’2000’; 將sal=’2000’的行對應的ename改名為 haofei888

同時更新多個表 update tablename1, tablename2,…,tablenamen set tablename1.file1=value1, tablename2.file1=value1,.. [where condition] 例子:update emp,dept set emp.ename=’liuzhi’,emp.sal=100,dept.depton=3; or update emp a,dept b set a.ename=’liu’,a.sal=99,b.depton=7;

3、刪除： 語法:delete from tablename [where condition] 例子:delete from dept where deptname=’aa’; 刪除dept表中deptname=’aa’的記錄

可以一次刪除多個表中的多個記錄 語法:delete tablename1,tablename2 from tablename1,tablename2 [where condition] delete emp,dept from emp,dept where deptname=’bb’ and depton=2; 刪除表emp 和 dept表中符合 deptname=’bb’ and depton=2 條件的記錄

4、查詢 語法:select * from tablename [where condition] 或者 select column1,column2..,columnn from tablename [where condition]

a、查詢不重復的記錄 select distinct ename from emp; select distinct ename,deptno from emp;

b、條件查詢 where condition 例如 select * from emp where deptno=6; select * from emp where deptno=6 or ename=’liuzhi’; 支持 < > <= >= != or and 多個條件

c、排序和限制 select * from tablename [where condition] [order by field1 [DESC | ASC],…, fieldn [DESC | ASC]] DESC 表示降序排列 ASC 表示升序排列(默認) 部分顯示 limit select * from tablename [where condition] [order by field1 [DESC | ASC],…, fieldn [DESC | ASC]] [linit offset_start,row_count] offset_start 其實偏移量 row_count 顯示行數 例如:select * from emp order by sal limit 3; 按sal 升序排序后 顯示前3行； select * from emp order by sal limit 3,3;按sal 升序排序后 從第三條起，顯示3行也就是 4~6行

d、聚合操作 ①統計 select [field1, field2..,fieldn] fun_name from tablename [where where_contition] [group by field1, field2..,fieldn [with rollup] ] [ having where_condition] 解釋: fun_name 表示要做的聚合操作，也就是聚合函數 常用的有 sum (求和)、 count(*) (記錄數) 、max(最大值)、 min (最小值) group by 要分類聚合的字段 with rollup 對分類后的結果在匯總(相當于沒用group by而得到的結果) having 對分類后的結果再過濾 例如:select count(1) from emp;統計emp的總行數數量 select deptno,count(1) from emp group by deptno; 統計deptno值相同的數量，并顯示deptno select deptno,count(1) from emp group by deptno with rollup;統計deptno值相同的數量，并統計deptno總的數量 select deptno,count(1) from emp group by deptno having count(1)>1;統計deptno值相同的數量，并篩選出deptno值大于1的項

②求和 最大值 最小值 select sum(sal),max(sal),min(sal) from emp; 計算sal的總和，得出最大值 最小值

e、表連接 內連接:內連接僅僅選出兩張表中互相匹配的記錄； 43頁 例如：select ename, deptname from emp , dept , where emp.deptno=dept.deptmo;

外連接:外連接會選出其他不匹配的記錄。 常用的是內連接 外連接又分為： 左連接:包含左邊表中的記錄（右邊表中沒有的也包含） 例子:select ename,deptname from emp left join dept on emp.deptno=dept.deptno; (emp是左表)會將emp中的所有項列出，就算dept中沒有對應的，也會列出 右連接:包含右邊表中的記錄（左邊表中沒有的也包含） 例子:select ename,deptname from dept right join emp on emp.deptno=dept.deptno; (emp是右表)會將emp中的所有項列出，就算dept中沒有對應的，也會列出

f、子查詢(一個select的結果是另一個select的條件時使用) 子查詢關鍵字 in、not in、 =、 !=、 exists、not exists 例如 select * from emp where deptno in (select depton from dept where depton=1);

g、記錄聯合 語法:select * from tablename1 union|union all select * from tablename2 union|union all … select * from tablenamen ; 例子:select deptno from emp union all select depton from dept; union all 把所有結果輸出 union 把結果去掉重復之后輸出

三、DCL語句(用戶權限管理語句) 添加權限grant(給權限的同時可以創建用戶) 移除權限revoke

給用戶添加select/insert權限 grant select,insert on testdb.* to ‘rick’@’localhost’ identified by ‘123’; 給用戶 rick 添加數據庫testdb 的查詢和插入權限，密碼是123 移除insert的權限 revoke insert on testdb.* from ‘rick’@’localhost’ ; 移除用戶rick在數據庫testdb中的insert權限

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ mysql創建本地登陸用戶 rick 密碼rick GRANT ALL ON . TO rick@localhost IDENTIFIED BY ‘rick’; (第一個是庫，第二個是表)

創建一個能通過 wiley.com登陸的用戶 GRANT ALL ON . TO rick@’%.wiley.com’ IDENTIFIED BY ‘root’;

創建ip都能登陸的用戶 GRANT ALL ON . TO rick@’192.168.0.0/255.255.255.0’ identified by ‘root’; GRANT權限(select/insert之類的) ON 庫名.表名 TO 用戶名@允許的ip或者用戶名 identified by ‘密碼’; 例如：GRANT SELECT, INSERT, UPDATE, DELETE ON GPAnalytics.* TO gpanalytics@’192.168.2.244’ identified by ‘gpanalytics123!’;

GRANT ALL ON .創建的用戶權限很大，慎用

flush privileges; 添加用戶之后，刷新權限表

//給某個用戶添加某個庫的權限 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, ALTER ON TaskList.* TO ‘gpbbs’@’localhost’; 四、 1、刪除數據庫test1下所有前綴為 tmp的表 select concat(‘drop table test.’, table_name, ‘;’) from tables where table_schema=’test1’ and table_name like ‘tmp%’;

2、將數據庫test1 下所有存儲引擎為myisam 的表改為innodb。 alter table table_name engine=innodb;

五、將txt文件導入數據庫 use database; load data local infile ‘/tmp/user_base.txt’ into table user_base; 批量將txt文件導入數據庫 ls |awk -F ‘.’ ‘{print $1}’| xargs -i -t mysql -uroot -p0ad1b3ab9e6c164b -D GPBbs -e “load data local infile ‘/tmp/2016-02-19_GPBbs/{}.txt’ into table {}”

六、REPLACE INTO REPLACE INTO table_name(col_name, …) VALUES (…); REPLACE INTO table_name (col_name, …) SELECT …; REPLACE INTO table_name SET col_name=’value’, …算法說明： REPLACE的運行與INSERT很相像,但是如果舊記錄與新記錄有相同的值，則在新記錄被插入之前，舊記錄被刪除，即： 嘗試把新行插入到表中 當因為對于主鍵或唯一關鍵字出現重復關鍵字錯誤而造成插入失敗時： 從表中刪除含有重復關鍵字值的沖突行 再次嘗試把新行插入到表中 舊記錄與新記錄有相同的值的判斷標準就是： 表有一個PRIMARY KEY或UNIQUE索引，否則，使用一個REPLACE語句沒有意義。該語句會與INSERT相同，因為沒有索引被用于確定是否新行復制了其它的行。 返回值： REPLACE語句會返回一個數，來指示受影響的行的數目。該數是被刪除和被插入的行數的和 受影響的行數可以容易地確定是否REPLACE只添加了一行，或者是否REPLACE也替換了其它行：檢查該數是否為1（添加）或更大（替換）。

第三章：mysql支持的數據類型 1、定點數類型：如果小數部分長度超出范圍，直接截斷如1.234如果小數后只能存兩位，就直接截斷為1.23 DEC(M,D) DECIMAL(M,D)

2、浮點數類型：如果指點了小數位數，超出部分會按實際的保存下來。1.234如果小數后只能保存2位，實際會存1.234

3、char和varchar char會把前后的空格去掉后保存，varchar會把空格保存下來

第四章 mysql中的運算符 1、<=> NULL安全的等于 2、regexp或rlike 正則表達式匹配 3、XOR邏輯異或 比如 0 XOR 1則為真

第五章 字符串函數 1、concat(s1, s2, s3, …) 字符串拼接 2、rand() 返回0-1隨機數 3、IF(value, t, f) 如果value為真，就返回t，否則返回f 4、IFNULL(value1, value2 ) 如果value1不為空，返回value1，否則返回value2 5、case when [value1] then [result1] … else [default] end 如果value1是真，返回result，否則趕回default 或者：case [expr] when [value1] then [result1] …else [default] end 如果expr等于value1，返回result，否則返回default

第七章 表類型 1、查看數據庫支持的存儲引擎 show engines； show variables like ‘have%’; //DISABLED的記錄表示支持但在數據庫啟動的時候被禁止了。

3、各常用存儲引擎比較

4、myisam介紹 myisam是mysql默認的存儲引擎; myisam不支持事務、不支持外鍵，其優勢是訪問速度快; 數據文件和索引文件可以放置在不同的目錄，平均分配IO，獲得更快的速度(創建表的時候用DATA DIRECTORY和INDEX DIRECTORY指定目錄); 可以使用check table語句來檢查myisam表的健康; 可以用repair table語句修復一個損壞的myisam表; myisam表支持3中不同的存儲格式： 靜態(固定長度)表：默認，各個字段為非變長字段，存儲非常迅速、容易緩存、出現故障容易恢復，保存的內容后面的空格會被去掉。 動態表：占用空間比較少，頻繁地更新和刪除會產生碎片，故障恢復比較困難。 壓縮表：占用非常小的磁盤空間、訪問開支非常小。

5、innodb 自動增長列：innodb的自動增長列可以手動插入，但如果插入的是0或者空，就為自動增長；自動增長值保存在內存中，重啟數據庫會丟失； 外鍵約束：創建外鍵的時候，夫表必須有對應的索引，子表在創建外鍵的時候也自動創建對應的索引。 存儲方式： 使用共享表空間存儲，表結構保存于.frm文件中，數據和索引保存在innodb_data_home_dir 和 innodb_data_file_path定義的表空間中，可以是多個文件； 使用多表空間存儲，這種方式創建的表的結構保存在.frm中，但每個表的數據和索引單獨保存在.ibd中，如果是分區表，則每個分區表對應單獨的.ibd文件。多表空間的數據文件沒有大小限制，不需要設置初始大小，也不需要設置文件的大小限制和擴張大小等參數。

第30章 mysql常見問題和應用技巧 1.忘記mysql root密碼 a. kill掉mysql進程 kill -9 mysql的pid b.用–skip-grant-tables選項來重啟mysql服務（–skip-grant-tables是跳過權限表認證的意思） mysqld_safe –skip-grant-tables –user=mysql c.用空密碼的root用戶連接mysql，并修改密碼 mysql -uroot #登陸 update user set passWord=password(‘123’) where user=’root’ and host=’localhost’; #修改密碼 flush privileges #刷新權限

2.如何處理myisam存儲引擎的表損壞 a. mysql表損壞的表現： tbl_name.frm 被鎖定不能更改 不能找到文件 tbl_name.MYI（Errcode：nnn） 文件意外結束 記錄文件被損壞 從表處理器得到錯誤 nnn

b. 處理方法1 myisamchk -r tablename -r參數的含義是recover，這個命令幾乎可以解決所有問題，如果不行就使用 myisamchk -o tablename -o 參數的含義是–safe-recover，可以進行更安全的修復

c. 處理方法2 使用CHECK TABLE(檢查表是否損壞) 和 REPAIR TABLE(修復順壞的表)命令一起進行修復 CHECK TABLE tbl_name1 [, tal_name2]… [option]… option = {QUICK|FAST|MEDIUM|EXTENDED|CHANGED} REPAIR [LOCAL|NO_WRITE_TI_BINLOG] TABLE tbl_name [,tbl_name]… [QUICK][EXTENDED][USE_FRM]

3.數據目錄磁盤空間不足的問題(也就是將數據和索引分開) a.myisam表的處理方法 create table t1( id int, name varchar(11) ) data directory=’目錄路徑’,index directory=’目錄路徑’; 這種方法的實際處理方式是在操作系統上創建軟連接。

b.innodb表的處理方法 innodb的表，其數據和索引是在同個文件的，索引不能分離了，但可以增加一個新的數據文件。 innodb_data_file_path = /home/ibdata1:2000M; /home1/ibdata2:2000M:autoextend; 需要重啟后才會生效。

4.mysql.sock丟失后連接數據庫 使用localhost作為主機名來鏈接數據庫時，默認使用unix套接字來鏈接，我們可以使用–protocol=TCP|SOCKET|PIPE|MEMORY來指定連接方式 mysql –protocal=TCP -uroot -p -hlocalhost