mysql基础命令学习笔记

2025-04-14 技术教程

这里不会涉及一些mysql数据库安装之类的知识，但是会将自己学习以及使用mysql一些最常用的mysql语句总结到本篇文章，作为自己的学习记录和笔记。基本的环境为CentOS 6.5 数据库为mysql5.6.30。

1、启动和停止Mysql服务

1、/etc/init.d/mysqlrestar#重启2、/etc/init.d/mysqlstop#停止3、/etc/init.d/mysqlstart#启动4、/etc/init.d/mysqlreload#平滑重启5、servicemysqlreload#平滑重启6、servicemysqlstop#停止7、servicemysqlstart#启动

2、加入开机自启动

chkconfigmysqlon#加入开机自启动chkconfig--list|grepmysql检查设置的开机自启动

3、连接数据库

mysql-h-P-uroot-p-e参数：-h要连接的主机-P数据库的端口默认是3306没更改的话可以不用写-u要连接数据库的用户名-p密码可以直接无空格的加在-p参数后面，但是这种方法，在别人查看你的历史命令时，会获得你的密码不×××全，一般是连接的时候，回车输入密码。-e你可以输入mysql语句但是不会进入客户端。

4、查看基础信息

5、为root设置密码与设置密码

mysql数据库是默认给root没有设置密码的，本次实验数据库rpm包安装的，有初始密码，mariadb在初始化的时候提示我们输入密码。

cat/root/.mysql_secret#TherandompasswordsetfortherootuseratSunJun1222:02:312016(localtime):nR7PKQyH5DU2zjKM这一部分为初始密码，

mysqladmin-urootpassword'******'#设置密码

更改密码selecthost,user,passwordfrommysql.user;+-----------------------+------------+-------------------------------------------+|host|user|password|+-----------------------+------------+-------------------------------------------+|localhost|root|*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1||localhost.localdomain|root|*47F6BC7F709C7CCFCB1EAF451FFE6D89F1377D84||127.0.0.1|root|*47F6BC7F709C7CCFCB1EAF451FFE6D89F1377D84||::1|root|*23AE809DDACAF96AF0FD78ED04B6A265E05AA257||192.168.1.%|tomcat|*6FDD34EE54803B8CC847CC5D7158702BCC21FCF6||%|winnerlook|*23AE809DDACAF96AF0FD78ED04B6A265E05AA257|+-----------------------+------------+-------------------------------------------+（1）mysqladmin-u用户名-p旧密码password"******"例如：mysqladmin-uwinnerpassword"123"-p[root@localhost~]#mysqladmin-uwinnerpassword"123"-pEnterpassword:Warning:Usingapasswordonthecommandlineinterfacecanbeinsecure.（2）登陆到数据库后用setpassword命令格式：SETpasswordforuser@host=password("")；注意加密函数例如：setpasswordforroot@'::1'=password("123");QueryOK,0rowsaffected(0.05sec)mysql>flushprivileges;QueryOK,0rowsaffected(0.00sec)mysql>setpasswordfortomcat@'192.168.1.%'=password("123123");QueryOK,0rowsaffected(0.00sec)mysql>flushprivileges;QueryOK,0rowsaffected(0.00sec)（3）登陆后用update直接操作user表注意：这里要使用加密函数以及限制条件，不注意限制条件有可能会更改所有的用户密码。如下面的内容直接更改所有的内容以及明文密码。updateusersetpassword=("123123");QueryOK,6rowsaffected(0.03sec)，Rowsmatched:6Changed:6Warnings:0mysql>selecthost,user,passwordfrommysql.user;+-----------------------+------------+----------+|host|user|password|+-----------------------+------------+----------+|localhost|root|123123||localhost.localdomain|root|123123||127.0.0.1|root|123123||::1|root|123123||192.168.1.%|tomcat|123123||%|winnerlook|123123|+-----------------------+------------+----------+正确更改的方式：updateusersetpassword=password("123123");QueryOK,6rowsaffected(0.02sec)Rowsmatched:6Changed:6Warnings:0mysql>selecthost,user,passwordfrommysql.user;+-----------------------+------------+-------------------------------------------+|host|user|password|+-----------------------+------------+-------------------------------------------+|localhost|root|*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1||localhost.localdomain|root|*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1||127.0.0.1|root|*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1||::1|root|*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1||192.168.1.%|tomcat|*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1||%|winnerlook|*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1|+-----------------------+------------+-------------------------------------------+6rowsinset(0.00sec)使用where字句添加限制条件mysql>updateusersetpassword=password("123")whereuser='tomcat';QueryOK,1rowaffected(0.05sec)Rowsmatched:1Changed:1Warnings:0mysql>selecthost,user,passwordfrommysql.user;+-----------------------+------------+-------------------------------------------+|host|user|password|+-----------------------+------------+-------------------------------------------+|localhost|root|*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1||localhost.localdomain|root|*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1||127.0.0.1|root|*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1||::1|root|*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1||192.168.1.%|tomcat|*23AE809DDACAF96AF0FD78ED04B6A265E05AA257||%|winnerlook|*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1|+-----------------------+------------+-------------------------------------------+6rowsinset(0.00sec)

6、刷新权限

mysql>flushprivileges;QueryOK,0rowsaffected(0.14sec)

7、mysql 客户端技巧

echo"select*fromtb_emp8;"|mysql-uroot-ptest_db>>test.txt[root@localhost~]#echo"select*fromtb_emp8;"|mysql-uroot-ptest_db>test.txtEnterpassword:[root@localhost~]#cattest.txtidnamesdeptIdsalary1LucyNULL10002LuraNULL12003KevinNULL15004LucyNULL10005LuraNULL12006KevinNULL15007LucyNULL10008LuraNULL12009KevinNULL150010LucyNULL100011LuraNULL120012KevinNULL150013LucyNULL100014LuraNULL1200方法2mysql-uroot-p-e"select*fromtest_db.tb_emp8;">test2.txtEnterpassword:[root@localhost~]#cattest2.txtidnamesdeptIdsalary1LucyNULL10002LuraNULL12003KevinNULL15004LucyNULL10005LuraNULL1200

执行sql文件的方法

（1）mysql-uroot-ptest_db</root/test.sql（2）cat/root/test.sql|mysql-uroot-ptest_db（3）登录数据库后source加载

8、创建交互文件日志可以用来评估和考量操作过程中出现哪些操作，可以用tee

mysql--tee=test.log-uroot-p#创建一个test.log日志文件Loggingtofile'test.log'Enterpassword:WelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis34Serverversion:5.6.30MySQLCommunityServer(GPL)Copyright(c)2000,2016,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>\Ttest.log#开始记录日志文件Loggingtofile'test.log'mysql>selectuser(),version(),now();+----------------+-----------+---------------------+|user()|version()|now()|+----------------+-----------+---------------------+|root@localhost|5.6.30|2016-10-0717:14:25|+----------------+-----------+---------------------+1rowinset(0.11sec)mysql>showdatabases;+--------------------+|Database|+--------------------+|information_schema||booksdb||company||mysql||performance_schema||sakila||team||test||test_db||winner||world|+--------------------+11rowsinset(0.00sec)mysql>\t#结束记录检查日志文件：[root@localhost~]#cattest.logWelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis34Serverversion:5.6.30MySQLCommunityServer(GPL)Copyright(c)2000,2016,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>\Ttest.logmysql>selectuser(),version(),now();+----------------+-----------+---------------------+|user()|version()|now()|+----------------+-----------+---------------------+|root@localhost|5.6.30|2016-10-0717:14:25|+----------------+-----------+---------------------+1rowinset(0.11sec)mysql>showdatabases;+--------------------+|Database|+--------------------+|information_schema||booksdb||company||mysql||performance_schema||sakila||team||test||test_db||winner||world|+--------------------+11rowsinset(0.00sec)mysql>useworld;ReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql>showtables;+-----------------+|Tables_in_world|+-----------------+|city||country||countrylanguage|+-----------------+3rowsinset(0.00sec)mysql>desccity;+-------------+----------+------+-----+---------+----------------+|Field|Type|Null|Key|Default|Extra|+-------------+----------+------+-----+---------+----------------+|ID|int(11)|NO|PRI|NULL|auto_increment||Name|char(35)|NO|||||CountryCode|char(3)|NO|||||District|char(20)|NO|||||Population|int(11)|NO||0||+-------------+----------+------+-----+---------+----------------+5rowsinset(0.51sec)mysql>selectcount(*)fromcity;+----------+|count(*)|+----------+|4079|+----------+1rowinset(0.00sec)