作者/来源：yixinu.com
栏目：运维/编程
日期：2012-05-27 11:15:10
1、安装mysql数据库
# yum install mysql-server
2、 设置mysql数据库root 初始密码
[root@localhost ~]# mysqladmin -u root -p password root
mysqladmin -u 用户名 -p password 新密码
3、连接mysql数据库，并显示当前数据库中所有的数据库。。
mysql -h host -u username -p
-h 主机名或IP地址 -u 用户名 -p 交互式提示输入密码
[root@localhost ~]# mysql -uroot -proot Welcome to the MySQL monitor. Commands end with ; or g. …… …… mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | | wordpress | +--------------------+ 4 rows in set (0.03 sec)
4、使用mysql数据库
1） 使用数据 库
mysql> use test; Database changed
2） mysql>quit //退出数据库
mysql> select version(); //查看当前数据库版本
3） mysql>select sysdate(); //查询系统日期时间
4） mysql>select current_date; //查询现在日期
5） mysql>create database caiwu; //创建名为caiwu 的数据库
6） 创建表
mysql> create table pet (
-> name varchar(20),
-> owner varchar(20),
-> species varchar(20),
-> sex char(1),
-> birth date,
-> death date
-> );
Query OK, 0 rows affected (0.28 sec)
7） 显示数据库中所有 的表，并查询pet表的信息
mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | pet | +----------------+ 1 row in set (0.02 sec) mysql> desc pet; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | owner | varchar(20) | YES | | NULL | | | species | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | | death | date | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 6 rows in set (0.11 sec) mysql>
8） mysql>drop table `pet`; //删除表
9）加载本地文件到数据库
文本文件，N 表示数据库中为NULL
[root@testserver002 ~]# cat pet.database.txt
Fluffy Harold cat f 1993-02-04 N
Claws Gwen cat m 1994-03-17 N
Buffy Harold dog f 1989-05-13 N
Fang Benny dog m 1990-08-27 N
Bowser Diane dog m 1979-08-31 1995-07-29
Chirpy Gwen bird f 1998-09-11 N
Whistler Gwen bird N 1997-12-09 N
Slim Benny snake m 1996-04-29 N
创建表
mysql> create table pet (
-> name varchar(20),
-> owner varchar(20),
-> species varchar(20),
-> sex char(1),
-> birth date,
-> death date
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| pet |
+----------------+
1 row in set (0.00 sec)
mysql> desc pet;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
从文件导入到表
mysql> load data local infile '/root/pet.database.txt'
-> into table pet;
Query OK, 8 rows affected (0.00 sec)
Records: 8 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from pet;
+----------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+--------+---------+------+------------+------------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
+----------+--------+---------+------+------------+------------+
8 rows in set (0.00 sec)
mysql>
10） 使用条件操作符
使用 and 、 or mysql> select * from pet where sex='m' and species='dog'; +--------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+-------+---------+------+------------+------------+ | Fang | Benny | dog | m | 1990-08-27 | NULL | | Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 | +--------+-------+---------+------+------------+------------+ 2 rows in set (0.00 sec) mysql>
查询 性别为m的狗 和 性别为f的猫
mysql> select * from pet where (sex='m' and species='dog') or (sex='f' and species='cat'); +--------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+------------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Fang | Benny | dog | m | 1990-08-27 | NULL | | Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 | +--------+--------+---------+------+------------+------------+ 3 rows in set (0.00 sec) mysql>
11） 过虑重复的数据 distinct
未过虑之前：
mysql> select owner as zhu_ren from pet; +---------+ | zhu_ren | +---------+ | Harold | | Gwen | | Harold | | Benny | | Diane | | Gwen | | Gwen | | Benny | +---------+ 8 rows in set (0.00 sec)
过虑之后：
mysql> select distinct owner as zhu_ren from pet; +---------+ | zhu_ren | +---------+ | Harold | | Gwen | | Benny | | Diane | +---------+ 4 rows in set (0.00 sec)
12） 对行排序 order by
mysql> select * from pet order by birth; +----------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+--------+---------+------+------------+------------+ | Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 | | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Fang | Benny | dog | m | 1990-08-27 | NULL | | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | +----------+--------+---------+------+------------+------------+ 8 rows in set (0.00 sec) mysql> select name,species,birth from pet order by birth; +----------+---------+------------+ | name | species | birth | +----------+---------+------------+ | Bowser | dog | 1979-08-31 | | Buffy | dog | 1989-05-13 | | Fang | dog | 1990-08-27 | | Fluffy | cat | 1993-02-04 | | Claws | cat | 1994-03-17 | | Slim | snake | 1996-04-29 | | Whistler | bird | 1997-12-09 | | Chirpy | bird | 1998-09-11 | +----------+---------+------------+ 8 rows in set (0.00 sec) mysql>
逆向排序 desc
mysql> select name,species,birth from pet order by birth desc; +----------+---------+------------+ | name | species | birth | +----------+---------+------------+ | Chirpy | bird | 1998-09-11 | | Whistler | bird | 1997-12-09 | | Slim | snake | 1996-04-29 | | Claws | cat | 1994-03-17 | | Fluffy | cat | 1993-02-04 | | Fang | dog | 1990-08-27 | | Buffy | dog | 1989-05-13 | | Bowser | dog | 1979-08-31 | +----------+---------+------------+ 8 rows in set (0.00 sec)
13） 空值的使用
判断非空值 is not null
mysql> select * from pet where death is not null; +--------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+-------+---------+------+------------+------------+ | Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 | +--------+-------+---------+------+------------+------------+ 1 row in set (0.00 sec)
判断空值 is null
mysql> select * from pet where death is null; +----------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+--------+---------+------+------------+-------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Fang | Benny | dog | m | 1990-08-27 | NULL | | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | +----------+--------+---------+------+------------+-------+ 7 rows in set (0.00 sec)
------------------
14）查询中使用正则表达式
模糊搜索 like
% 表示任何长度的字符
_ 表示任何单个字符
mysql> select option_id,blog_id,option_value,autoload from wp_options where option_value like 'http://localhost%' limit 0,10; +-----------+---------+------------------+----------+ | option_id | blog_id | option_value | autoload | +-----------+---------+------------------+----------+ | 3 | 0 | http://localhost | yes | | 39 | 0 | http://localhost | yes | +-----------+---------+------------------+----------+ 2 rows in set (0.00 sec) mysql>
mysql> select * from pet where birth not like '1989%' and birth not like '1998%'; +----------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+--------+---------+------+------------+------------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Fang | Benny | dog | m | 1990-08-27 | NULL | | Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | +----------+--------+---------+------+------------+------------+ 6 rows in set, 2 warnings (0.00 sec)
正则表达式补充
rlike 、 not rlike
查询 name 字段为 四个字符的数据
mysql> select * from pet where name rlike '^....$'; +------+-------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +------+-------+---------+------+------------+-------+ | Fang | Benny | dog | m | 1990-08-27 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | +------+-------+---------+------+------------+-------+ 2 rows in set (0.00 sec)
15）统计查询结果
查询有多少条数据
mysql> select count(*) as total from pet; +-------+ | total | +-------+ | 8 | +-------+ 1 row in set (0.00 sec)
对列做分组 group by
mysql> select owner,count(name) as total from pet group by owner; +--------+-------+ | owner | total | +--------+-------+ | Benny | 2 | | Diane | 1 | | Gwen | 3 | | Harold | 2 | +--------+-------+ 4 rows in set (0.00 sec)
16） 插入数据
按字段默认的排列顺序
mysql> insert into pet values ('Buffy','Harold','dog','f',current_date,NULL);
current_date 是函数，表示当前日期
指定字段顺序
mysql> insert into pet (name,owner,species,sex,birth,death) values ('Buffy','Harold','dog','f',current_date,NULL);
Query OK, 1 row affected (0.00 sec)
5、备份数据库
1） mysqldump
[root@testserver002 ~]# mysqldump -p test > ./test.bak
备份 test 数据库到 test.bak文件
[root@testserver002 ~]# mysqldump -p test pet > ./test.pet.sql Enter password:
备份 test 数据库中的 pet 表到 test.pet.sql 文件
2）拷贝数据库文件
/var/lib/mysql [root@localhost mysql]# ls caiwu ibdata1 ib_logfile0 ib_logfile1 mysql mysql.sock test wordpress [root@localhost mysql]#
在这个目录 下，所有的数据文件在这，只要将它们复制出来，备份就OK了
6、shell模式下导入sql文件到数据库
[root@localhost ~]# mysql -u root -p wordpress < ~/wordpress.sql Enter password:
将备份的 wordpress.sql 数据导入到 wordpress 数据库中
7、忘记 ROOT 用户密码解决方案
a) 编写一个bash 脚本
[root@localhost ~]# ps -aux | grep mysql Warning: bad syntax, perhaps a bogus '-'? See /usr/share/doc/procps-3.2.8/FAQ root 3872 1.5 0.2 5020 1348 pts/0 S 09:07 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql mysql 3961 3.0 2.9 135224 15032 pts/0 Sl 09:07 0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock root 3979 0.0 0.1 4308 732 pts/0 S+ 09:07 0:00 grep mysql
[root@localhost ~]# ps -aux | grep mysql > mysql_safe.sh
root 3872 0.0 0.2 5020 1348 pts/0 S 09:07 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql mysql 3961 0.0 2.9 135224 15032 pts/0 Sl 09:07 0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock root 3984 0.0 0.1 4308 760 pts/0 S+ 09:09 0:00 grep mysql
[root@localhost ~]# vim mysql_safe.sh #!/bin/bash /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock --port=3306 --skip-grant-tables
b) 停止mysql服务
[root@localhost ~]# /etc/init.d/mysqld stop Stopping mysqld: [ OK ]
c) 赋予执行权限，运行脚本，并保持脚本的运行状态，不要ctrl+c , 也不要 ctrl+z
[root@localhost ~]# chmod a+x ./mysql_safe.sh [root@localhost ~]# ./mysql_safe.sh 120528 09:12:20 mysqld_safe Logging to '/var/log/mysqld.log'. 120528 09:12:20 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
d) 开启另一个终端 ， 进入 mysql ，发出已经不需要密码
[root@localhost ~]# mysql Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 1 Server version: 5.1.52 Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql>
e) 直接 修改 root 密码
mysql> use mysql;
mysql> select Host,User,Password from user;
+-----------+------+-------------------------------------------+
| Host | User | Password |
+-----------+------+-------------------------------------------+
| localhost | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| bogon | root | |
| 127.0.0.1 | root | |
| localhost | | |
| bogon | | |
+-----------+------+-------------------------------------------+
5 rows in set (0.00 sec)
mysql>
mysql> update user set Password=PASSWORD('root') where User='root' and Host='localhost';
Query OK, 0 rows affected (0.05 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql>
f ) 退出 mysql
mysql> quit Bye [root@localhost ~]# service mysqld stop Stopping mysqld: [ OK ] [root@localhost ~]#
去刚刚那个运行脚本的终端看，脚本已经自己退出来了
g ) 重新进入 开启 mysql 服务 ，进入 ，已经需要密码了
[root@localhost ~]# /etc/init.d/mysqld start Starting mysqld: [ OK ] [root@localhost ~]# mysql -uroot -p Enter password:
用户与权限的设置
在mysql提示符下使用 grant 命令可建立并设置账户的权限 语法如下
grant 权限 on 对象数据 to 完整账户 identified by '密码'
例如：grant all privileges on *.* to 'admin'@'localhost' identified by '123456'
刷新操作 flush privileges;
其中权限可以是 all create insert 等，可同时设置多种权限，以逗号分开； 对象数据可为所有数据库（*.*）、指定的数据库（mysql.*）、数据库中的数据表（mysql.user）; 完整账户格式为 账户名＠位置 ，若为本地账户，格式为 ＠localhost，若账户不存在，则自动创建。
添加 用户的第二种操作方法
CREATE USER 'admin2'@'localhost' IDENTIFIED BY '***';
GRANT SELECT, INSERT, UPDATE, DELETE, FILE ON *.* TO 'admin2'@'localhost' IDENTIFIED BY '***' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
一些 mysql 常用 语句
