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: 0mysql> 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: 0mysql>  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: 0mysql> 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.txtEnter password: [root@localhost ~]# cat  test.txt id      names   deptId  salary1       Lucy    NULL    10002       Lura    NULL    12003       Kevin   NULL    15004       Lucy    NULL    10005       Lura    NULL    12006       Kevin   NULL    15007       Lucy    NULL    10008       Lura    NULL    12009       Kevin   NULL    150010      Lucy    NULL    100011      Lura    NULL    120012      Kevin   NULL    150013      Lucy    NULL    100014      Lura    NULL    1200方法2mysql -u  root -p  -e "select *  from test_db.tb_emp8;">test2.txtEnter password: [root@localhost ~]# cat  test2.txt id      names   deptId  salary1       Lucy    NULL    10002       Lura    NULL    12003       Kevin   NULL    15004       Lucy    NULL    10005       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 34Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 34Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> \T test.logmysql> 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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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)