第1章?Mysql用戶管理: 1.1?用戶的定義:??用戶名+主機(jī)域?? mysql>?select?user,host,password?from?mysql.user; +------+-----------+----------+ |?user?|?host??????|?password?| +------+-----------+----------+ |?root?|?localhost?|??????????| |?root?|?db01??????|??????????| |?root?|?127.0.0.1?|??????????| 1.2?用戶的作用: 1.??????用來(lái)登錄數(shù)據(jù)庫(kù) 2.??????管理數(shù)據(jù)的對(duì)象(表和庫(kù)) 說(shuō)明:類比:Linux中創(chuàng)建用戶,更改某個(gè)目錄或者文件的權(quán)限來(lái)對(duì)數(shù)據(jù)進(jìn)行管理 1.2.1?創(chuàng)建一個(gè)用戶 權(quán)限設(shè)定:?grant?權(quán)限?on?權(quán)限范圍?to?用戶??identified?by?‘密碼’; 1.2.2?權(quán)限管理: 對(duì)數(shù)據(jù)庫(kù)的讀寫操作等,權(quán)限可用來(lái)管理某個(gè)用戶可以對(duì)數(shù)據(jù)庫(kù)做什么 （insert?update、select、delete、drop、create等） 1.2.3?角色: 對(duì)數(shù)據(jù)庫(kù)讀,寫等操作(insert;update;select) 1.2.4?權(quán)限范圍: 1.??????全庫(kù)級(jí)別:?*.* 2.??????單庫(kù)級(jí)別:?test.* 3.??????單表級(jí)別:?test.table_name 1.2.5?用戶:'clsn'@'localhost'??本地'clsn'@'192.168.66.149''clsn'@'192.168.66.%' 'clsn'@'192.168.66.14%' 1.3?練習(xí):按照要求創(chuàng)建一個(gè)用戶 用戶只能通過(guò)10.0.0.0/24網(wǎng)段訪問(wèn),用戶名為jiang,密碼為123 jiang用戶只能對(duì)jiang數(shù)據(jù)庫(kù)下的對(duì)象進(jìn)行增insert;create;改update;查select 1.3.1?創(chuàng)建用戶并授權(quán) grant?insert,select,create,update?on?jiang.*?to?‘jiang@10.0.0.%’?identified?by?‘123’; 1.3.2?查看用戶的權(quán)限: mysql>?show?grants?for?root@'10.0.0.%'\G ***************************?1.?row?*************************** Grants?for?root@10.0.0.%:?GRANT?ALL?PRIVILEGES?ON?*.*?TO?'root'@'10.0.0.%'?IDENTIFIED?BY?PASSWORD?'*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' 1?row?in?set?(0.01?sec) 1.3.3?標(biāo)準(zhǔn)創(chuàng)建用戶的方法: create?user?'web'@'172.16.1.%'?identified?by?'web123';???這樣創(chuàng)建的用戶只有連接的權(quán)限 1.3.4?刪除用戶: mysql>?drop?user?root@'10.0.0.%'; 1.3.5?回收權(quán)限 revoke?insert?on?*.*?from?root@’localhos’; 1.4?Mysql忘記密碼的修改辦法: 1.??????停掉數(shù)據(jù)庫(kù) /etc/init.d/mysqld?stop 2.??????停掉連接層的授權(quán)功能和遠(yuǎn)程登錄動(dòng)能,并啟動(dòng) cd?/application/mysql/bin/ mysqld_safe?--skip-grant-table?--user=mysql?--skip-networking?& 說(shuō)明:這種啟動(dòng)模式下,無(wú)密碼登錄,網(wǎng)絡(luò)用戶無(wú)法登錄,只能本地登錄,和授權(quán)有關(guān)的命令都無(wú)法執(zhí)行 3.??????直接修改密碼 mysql>?update?mysql.user?set?password=password('123')?where?user='root'?and?host='localhost'; mysql>?flush?privileges; MySQL5.7版本修改密碼的修改字段為:?authentication_string 4.??????退出,重啟服務(wù),正常restart就可以,我這里失敗了,所以先停止在啟動(dòng) /etc/init.d/mysqld?stop /etc/init.d/mysqld?start 5.??????登錄數(shù)據(jù)庫(kù)進(jìn)行驗(yàn)證 mysql?-uroot?-p123 第2章?Mysql客戶端工具及SQL入門 2.1?MySQL客戶端命令有哪些? 1.??????mysql?????---用來(lái)連接數(shù)據(jù)庫(kù) ---將用戶的SQL語(yǔ)句發(fā)送到服務(wù)端 2.??????mysqladmin---命令行管理工具 3.??????mysqldump---備份數(shù)據(jù)庫(kù)和表的內(nèi)容 2.2?mysql命令功能詳解: 1.??????用戶連接數(shù)據(jù)庫(kù) 2.??????用于管理數(shù)據(jù)庫(kù) 2.2.1?mysql命令接口自帶功能: 命令????命令說(shuō)明???? \h?或?help?或???????獲取幫助???? \G????格式化輸出（行轉(zhuǎn)列）???? \T?或?tee????記錄操作日志??tee?/tmp/mysql.log???? \c?或?CTRL+c????退出mysql???? \s?或?status????查看數(shù)據(jù)庫(kù)狀態(tài)信息???? \.?或?source????mysql>?source???/tmp/world.sql???? \!????使用shell中的命令?mysql>?\!???cat?/etc/redhat-releaseCentOS???release?6.9?(Final)????? \u?或use???????use??worldshow???databases??看當(dāng)前所有數(shù)據(jù)庫(kù)的名字show???tables???查看當(dāng)前use到的數(shù)據(jù)庫(kù)所有的表show??tables???from?world???查看目標(biāo)數(shù)據(jù)庫(kù)下的表???? 快捷鍵????上下翻頁(yè)、TAB鍵、ctrl???+C?、ctrl?+L???? 2.2.2?mysql命令中help幫助說(shuō)明: contents查看完整的sql類別列表幫助 mysql>?help?contents 查看特定sql類別或語(yǔ)句的幫助 mysql>?help?Account?Management; 查看grant幫助 mysql>?help?grant 與狀態(tài)相關(guān)的sql語(yǔ)句幫助 mysql>?help?status mysqladmin命令說(shuō)明: 功能選項(xiàng)????說(shuō)明???? mysqladmin?-u用戶?-p密碼?ping????“強(qiáng)制回應(yīng)?(Ping)”服務(wù)器。???? mysqladmin?-u用戶?-p密碼?shutdown????關(guān)閉服務(wù)器。???? mysqladmin?-u用戶?-p密碼?create???databasename????創(chuàng)建數(shù)據(jù)庫(kù)。???? mysqladmin?-u用戶?-p密碼drop???databasename????刪除數(shù)據(jù)庫(kù)???? mysqladmin?-u用戶?-p密碼?version????顯示服務(wù)器和版本信息???? mysqladmin?-u用戶?-p密碼?status????顯示或重置服務(wù)器狀態(tài)變量???? mysqladmin?-u用戶?-p密碼?password????設(shè)置口令???? mysqladmin?-u用戶?-p密碼?flush-privileges????重新刷新授權(quán)表。???? mysqladmin?-u用戶?-p密碼?flush-logs????刷新日志文件和高速緩存。????2.3?SQL語(yǔ)句入門: 2.3.1?DDL:???數(shù)據(jù)定義語(yǔ)言 定義范圍:對(duì)庫(kù)名和庫(kù)的特性??????對(duì)表名和表中的列 查看數(shù)據(jù)庫(kù) 查看所有數(shù)據(jù)庫(kù) mysql>?show?databases; +--------------------+ |?Database???????????| +--------------------+ |?information_schema?| |?mysql??????????????| |?performance_schema?| |?test???????????????| |?world??????????????| |?zabbix?????????????| +--------------------+ 查看當(dāng)前所在數(shù)據(jù)庫(kù) mysql>?select?database(); +------------+ |?database()?| +------------+ |?zabbix?????| +------------+ 1?row?in?set?(0.00?sec) 對(duì)數(shù)據(jù)庫(kù)的操作 創(chuàng)建一個(gè)庫(kù) mysql>?create?database?zabbix?character?set?utf8; Query?OK,?1?row?affected?(0.01?sec) 查看庫(kù)中的表 mysql>?show?tables; +------------------+ |?Tables_in_zabbix?| +------------------+ |?stu??????????????| +------------------+ 1?row?in?set?(0.00?sec) 查看庫(kù)的創(chuàng)建語(yǔ)句 mysql>?show?create?database?zabbix;?? +----------+-----------------------------------------------------------------+ |?Database?|?Create?Database?????????????????????????????????????????????????| +----------+-----------------------------------------------------------------+ |?zabbix???|?CREATE?DATABASE?`zabbix`?/*!40100?DEFAULT?CHARACTER?SET?utf8?*/?| +----------+-----------------------------------------------------------------+ 1?row?in?set?(0.00?sec) 修改庫(kù)的屬性:只能修改字符集和校對(duì)規(guī)則 mysql>?alter?database?zabbix?charset?utf8mb4; 刪除一個(gè)庫(kù): mysql>?drop?database?zabbix; 切庫(kù) mysql>?use?zabbix; 對(duì)表的操作 創(chuàng)建一個(gè)表 mysql>?create?table?stu?(id?int,name?varchar(20),age?int?,gender?int); 查看表的創(chuàng)建語(yǔ)句 mysql>?show?create?table?stu; 在表最后一列添加: mysql>?alter?table?stu?add?addr?varchar(20); 在表的頭部添加: mysql>?alter?table?stu?add?stu_id?int?first; 在某一列后面添加: mysql>?alter?table?stu?add?qq?int?after?name; 在age后添加tel_num,在最后一行添加email mysql>?alter?table?stu?add?tel_num?int?after?age,add?email?varchar(20); 刪除某一列: mysql>?alter?table?stu?drop?email; 修改列名字: mysql>?alter?table?stu?change?qq?QQ?int; 修改列的數(shù)據(jù)類型: mysql>?alter?table?stu?modify?gender?varchar(20); 創(chuàng)建一個(gè)表結(jié)構(gòu)相同的空表 mysql>?create?table?stu_0?like?stu; mysql>?show?tables; +------------------+ |?Tables_in_zabbix?| +------------------+ |?stu??????????????| |?stu_0????????????| +------------------+ 2?rows?in?set?(0.00?sec) 創(chuàng)建一個(gè)表結(jié)構(gòu)相同的備份表 mysql>?create?table?stu_0?as?select?*?from?stu; 2.3.2?DCL??????數(shù)據(jù)庫(kù)控制語(yǔ)言 用戶授權(quán) GRANT?ALL?ON?*.*?TO?'jiang'@'localhost'; SHOW?GRANTS?FOR?'jiang'@'localhost'\G 創(chuàng)建用戶的同時(shí)進(jìn)行授權(quán) grant?insert,select,create,update?on?jiang.*?to?jiang@'10.0.0.%'?identified?by?'123'; 回收權(quán)限 REVOKE?INSERT?ON?*.*?FROM?jiang@localhost; 2.3.3?DML??????數(shù)據(jù)行操作語(yǔ)言(增刪改) insert語(yǔ)句 指定列插入 mysql>?insert?into?stu?(stu_id,QQ)?values(1,777); 所有列插入 mysql>?insert?into?stu?values(2,777,'j',56,'hao',2,'r','q',1); insert復(fù)制表結(jié)構(gòu)及內(nèi)容---表已經(jīng)存在的情況下,在可以復(fù)制 mysql>?create?table?stu_1?like?stu; mysql>?insert?into?stu_1?select?*?from?stu; 修改和刪除操作 刪除數(shù)據(jù): mysql>?delete?from?stu?where?stu_id=1????????邏輯刪除 mysql>?truncate?table?stu_3;?????????????????物理刪除 修改數(shù)據(jù),要加上where條件 mysql>?update?stu_3?set?QQ=5656?where?age='hao'; delete????刪除之后可以用二進(jìn)制日志反解insert命令把數(shù)據(jù)找回來(lái) truncate???物理刪除,數(shù)據(jù)找不回來(lái) 一般刪除大表的時(shí)候,先truncate然后在drop整個(gè)表,效率會(huì)比較高 使用update替代delete,偽刪除 mysql>?alter?table?stu?add?state?int?default?1; Query?OK,?0?rows?affected?(0.54?sec) Records:?0??Duplicates:?0??Warnings:?0mysql>?select?*?from?stu; +--------+------+-------+-----------+------+---------+--------+------+-------+ |?stu_id?|?id???|?name??|?QQ????????|?age??|?tel_num?|?gender?|?addr?|?state?| +--------+------+-------+-----------+------+---------+--------+------+-------+ |??????1?|??123?|?jiang?|???????222?|?ni???|???15555?|?boy????|?hao??|?????1?| |??????1?|??123?|?jiang?|???????222?|?ni???|???15555?|?boy????|?hao??|?????1?| |??????1?|??123?|?jiang?|???????222?|?ni???|???15555?|?boy????|?hao??|?????1?| |??????7?|?NULL?|?da?ya?|?850144102?|?NULL?|????NULL?|?NULL???|?NULL?|?????1?| +--------+------+-------+-----------+------+---------+--------+------+-------+ 4?rows?in?set?(0.00?sec) 修改state值,等于0即為刪除, mysql>?update?stu?set?state=0?where?name='jiang'; mysql>?select?*?from?stu?where?state=1; +--------+------+-------+-----------+------+---------+--------+------+-------+ |?stu_id?|?id???|?name??|?QQ????????|?age??|?tel_num?|?gender?|?addr?|?state?| +--------+------+-------+-----------+------+---------+--------+------+-------+ |??????7?|?NULL?|?da?ya?|?850144102?|?NULL?|????NULL?|?NULL???|?NULL?|?????1?| +--------+------+-------+-----------+------+---------+--------+------+-------+ 1?row?in?set?(0.01?sec) 防止誤刪除:-U,?--safe-updates??Only?allow?UPDATE?and?DELETE?that?uses?keys. mysql命令加上-U后???在執(zhí)行update和delete語(yǔ)句時(shí),不加where條件不會(huì)執(zhí)行 2.3.4?DQL:數(shù)據(jù)行查詢語(yǔ)言 select查詢語(yǔ)句 查看stu表中所有信息 mysql>?select?*?from?stu;???? 查看某一列的信息 mysql>?select?name?from?stu;?????多個(gè)列用逗號(hào)分隔 查看id號(hào)碼為777的qq號(hào)碼 mysql>?select?QQ?from?stu?where?id=777; where子句使用:?將數(shù)據(jù)按照指定條件,進(jìn)行過(guò)濾處理 等值過(guò)濾: mysql>?select?stu_id?from?stu?where?QQ=777; 比較過(guò)濾: mysql>?select?stu_id?from?stu?where?QQ<777; SQL過(guò)濾練習(xí): 排序:按照人口從少到多的順序排列顯示: SELECT?NAME,Population?FROM?city WHERE?CountryCode='chn'?ORDER?BY?Population; 按照從多到少進(jìn)行排序顯示: SELECT?NAME,Population?FROM?city WHERE?CountryCode='chn'?ORDER?BY?Population?DESC; 顯示人口排名最多的前10名: SELECT?NAME,Population?FROM?city WHERE?CountryCode='chn' ORDER?BY?Population?DESC?LIMIT?10; 顯示人口排名?第50行到第60行 SELECT?NAME,Population?FROM?city WHERE?CountryCode='chn' ORDER?BY?Population?DESC?LIMIT?50,10; 顯示世界上人口小于100的城市: SELECT?NAME,PopulationFROM?city?WHERE?Population<1000; 顯示PCN是那個(gè)國(guó)家: SELECT?NAME?FROM?city?WHERE?country='PCN'; 顯示世界上人口小于100的城市所在國(guó)家的國(guó)家名字 SELECT?country.name?FROM?country,city WHERE?city.Population<100?AND?country.Code=city.countrycode; 以上命令可以簡(jiǎn)寫成: SELECT??co.name?FROM?city?AS??ci?,country?AS?co WHERE ci.population<100 AND?co.code=ci.CountryCode;

轉(zhuǎn)載于:https://blog.51cto.com/13520772/2094425

總結(jié)

以上是生活随笔為你收集整理的MySQL用户管理及SQL入门的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。