mysql基础命令学习笔记-创新互联
这里不会涉及一些mysql数据库安装之类的知识，但是会将自己学习以及使用mysql一些最常用的mysql语句总结到本篇文章，作为自己的学习记录和笔记。基本的环境为CentOS 6.5 数据库为mysql5.6.30。
1、启动和停止Mysql服务
1、 /etc/init.d/mysql restar #重启 2、 /etc/init.d/mysql stop #停止 3、 /etc/init.d/mysql start #启动 4、 /etc/init.d/mysql reload #平滑重启 5、 service mysql reload #平滑重启 6、 service mysql stop #停止 7、 service mysql start #启动2、加入开机自启动
chkconfig mysql on #加入开机自启动 chkconfig --list |grep mysql 检查设置的开机自启动3、连接数据库
mysql -h -P -u root -p -e 参数： -h 要连接的主机 -P 数据库的端口 默认是3306 没更改的话可以不用写 -u 要连接数据库的用户名 -p 密码 可以直接无空格的加在-p参数后面，但是这种方法，在别人查看你的历史命令时 ，会获得你的密码不×××全，一般是连接的时候，回车输入密码。 -e 你可以输入mysql语句但是不会进入客户端。4、查看基础信息
select user(),version(),database(),now(); # 查看当前用户、版本、 当前数据库、当前时间等信息 mysql> select user(),version(),database(),now(); +----------------+-----------+------------+---------------------+ | user() | version() | database() | now() | +----------------+-----------+------------+---------------------+ | root@localhost | 5.6.30 | NULL | 2016-06-16 10:08:01 | +----------------+-----------+------------+---------------------+ 1 row in set (0.11 sec)5、为root设置密码与设置密码
mysql数据库是默认给root没有设置密码的，本次实验数据库rpm包安装的，有初始密码，mariadb在初始化的时候提示我们输入密码。
cat /root/.mysql_secret # The random password set for the root user at Sun Jun 12 22:02:31 2016 (local time): nR7PKQyH5DU2zjKM 这一部分为初始密码，mysqladmin -u root password '******' #设置密码 更改密码 select host,user,password from mysql.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 -u winner password "123" -p [root@localhost ~]# mysqladmin -u winner password "123" -p Enter password: Warning: Using a password on the command line interface can be insecure. （2）登陆到数据库后用set password命令 格式：SET password for user@host =password("")；注意加密函数 例如： set password for root@'::1' =password("123"); Query OK, 0 rows affected (0.05 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> set password for tomcat@'192.168.1.%' =password("123123"); Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) （3）登陆后用update直接操作user表 注意：这里要使用加密函数以及限制条件，不注意限制条件有可能会更改所有的用户密码。如下面的内容 直接更改所有的内容以及明文密码。 update user set password=("123123"); Query OK, 6 rows affected (0.03 sec)， Rows matched: 6 Changed: 6 Warnings: 0 mysql> select host,user,password from mysql.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 | +-----------------------+------------+----------+ 正确更改的方式： update user set password=password("123123"); Query OK, 6 rows affected (0.02 sec) Rows matched: 6 Changed: 6 Warnings: 0 mysql> select host,user,password from mysql.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 | +-----------------------+------------+-------------------------------------------+ 6 rows in set (0.00 sec) 使用where字句 添加限制条件 mysql> update user set password=password("123") where user='tomcat'; Query OK, 1 row affected (0.05 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select host,user,password from mysql.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 | +-----------------------+------------+-------------------------------------------+ 6 rows in set (0.00 sec)6、 刷新权限
mysql> flush privileges; Query OK, 0 rows affected (0.14 sec)7、mysql 客户端技巧
echo "select * from tb_emp8;" |mysql -u root -p test_db >>test.txt [root@localhost ~]# echo "select * from tb_emp8;" |mysql -u root -p test_db >test.txt Enter password: [root@localhost ~]# cat test.txt id names deptId salary 1 Lucy NULL 1000 2 Lura NULL 1200 3 Kevin NULL 1500 4 Lucy NULL 1000 5 Lura NULL 1200 6 Kevin NULL 1500 7 Lucy NULL 1000 8 Lura NULL 1200 9 Kevin NULL 1500 10 Lucy NULL 1000 11 Lura NULL 1200 12 Kevin NULL 1500 13 Lucy NULL 1000 14 Lura NULL 1200 方法2 mysql -u root -p -e "select * from test_db.tb_emp8;">test2.txt Enter password: [root@localhost ~]# cat test2.txt id names deptId salary 1 Lucy NULL 1000 2 Lura NULL 1200 3 Kevin NULL 1500 4 Lucy NULL 1000 5 Lura NULL 1200执行sql文件的方法
（1）mysql -u root -p test_db < /root/test.sql （2）cat /root/test.sql |mysql -u root -p test_db （3）登录数据库后source 加载8、创建交互文件日志 可以用来评估和考量操作过程中出现哪些操作，可以用tee
mysql --tee=test.log -u root -p # 创建一个test.log日志文件 Logging to file 'test.log' Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 34 Server version: 5.6.30 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> \T test.log #开始记录日志文件 Logging to file 'test.log' mysql> select user(),version(),now(); +----------------+-----------+---------------------+ | user() | version() | now() | +----------------+-----------+---------------------+ | root@localhost | 5.6.30 | 2016-10-07 17:14:25 | +----------------+-----------+---------------------+ 1 row in set (0.11 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | booksdb | | company | | mysql | | performance_schema | | sakila | | team | | test | | test_db | | winner | | world | +--------------------+ 11 rows in set (0.00 sec) mysql> \t #结束记录 检查日志文件： [root@localhost ~]# cat test.log Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 34 Server version: 5.6.30 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> \T test.log mysql> select user(),version(),now(); +----------------+-----------+---------------------+ | user() | version() | now() | +----------------+-----------+---------------------+ | root@localhost | 5.6.30 | 2016-10-07 17:14:25 | +----------------+-----------+---------------------+ 1 row in set (0.11 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | booksdb | | company | | mysql | | performance_schema | | sakila | | team | | test | | test_db | | winner | | world | +--------------------+ 11 rows in set (0.00 sec) mysql> use world; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +-----------------+ | Tables_in_world | +-----------------+ | city | | country | | countrylanguage | +-----------------+ 3 rows in set (0.00 sec) mysql> desc city; +-------------+----------+------+-----+---------+----------------+ | 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 | | +-------------+----------+------+-----+---------+----------------+ 5 rows in set (0.51 sec) mysql> select count(*) from city; +----------+ | count(*) | +----------+ | 4079 | +----------+ 1 row in set (0.00 sec)另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。
新闻名称：mysql基础命令学习笔记-创新互联
地址分享：http://ybzwz.com/article/ccseeo.html
