这里不会涉及一些mysql数据库安装之类的知识，但是会将自己学习以及使用mysql一些最常用的mysql语句总结到本篇文章，作为自己的学习记录和笔记。基本的环境为CentOS 6.5 数据库为mysql5.6.30。
1、启动和停止Mysql服务
|
1
2
3
4
5
6
7
|
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、加入开机自启动
|
1
2
3
|
chkconfig mysql on
#加入开机自启动
chkconfig --list |
grep
mysql 检查设置的开机自启动
|
3、连接数据库
|
1
2
3
4
5
6
7
8
|
mysql -h -P -u root -p -e
参数：
-h 要连接的主机
-P 数据库的端口 默认是3306 没更改的话可以不用写
-u 要连接数据库的用户名
-p 密码 可以直接无空格的加在-p参数后面，但是这种方法，在别人查看你的历史命令时
，会获得你的密码不太安全，一般是连接的时候，回车输入密码。
-e 你可以输入mysql语句但是不会进入客户端。
|
4、查看基础信息
|
1
2
3
4
5
6
7
8
9
10
|
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在初始化的时候提示我们输入密码。
|
1
2
3
|
cat /root/.mysql_secret
# The random
password
set
for
the root
user
at
Sun Jun 12 22:02:31 2016 (
local
time
):
nR7PKQyH5DU2zjKM 这一部分为初始密码，
|
|
1
|
mysqladmin -u root
password
'******'
#设置密码
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
|
更改密码
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、 刷新权限
|
1
2
|
mysql> flush
privileges
;
Query OK, 0
rows
affected (0.14 sec)
|
7、mysql 客户端技巧
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
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
2
3
|
（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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
|
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)
|
本文转自 tianya1993 51CTO博客，原文链接：http://blog.51cto.com/dreamlinux/1837470，如需转载请自行联系原作者
