技术-MySQL

1、mysql安装与启动

1.1 安装+启动

#卸载mysql：
sudo yum remove mysql 
#安装mysql：
sudo yum install mysql
sudo yum install mysql-server
#启动mysql
sudo service mysqld start

1.2 测试端口

端口是否成功可运行netstat看Mysql端口是否打开，如打开表示服务已经启动，安装成功。Mysql默认的端口是3306。

[xxx@xxx trunk]$ netstat -nat
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address               Foreign Address             State
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN

上面显示可以看出MySQL服务已经启动。

1.3 登录MySQL

登陆mysql的命令是mysql， mysql 的使用语法如下：

mysql [-u username] [-h host] [-p[password]] [dbname]

username 与 password 分别是 MySQL 的用户名与密码，mysql的初始管理帐号是root，没有密码，注意：这个root用户不是Linux的系统用户。MySQL默认用户是root，由于初始没有密码，第一次进时只需键入mysql即可。

[xxx@xxx trunk]$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, 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>

增加了密码后的登录格式如下：

mysql -u root -p　　 
Enter password: (输入密码)

其中-u后跟的是用户名，-p要求输入密码，回车后在输入密码处输入密码，默认没有密码，直接回车。

注意：这个mysql文件在/usr/bin目录下，与后面讲的启动文件/etc/init.d/mysql不是一个文件。

1.4 修改密码

MySQL默认没有密码，安装完毕增加密码的重要性是不言而喻的。

默认没有密码情况：mysqladmin -u root password #等待输入新密码
有密码后修改情况：mysqladmin -uxxx -pxxx password #等待输入新密码

例1：给root加个密码123456

[root@test1 local]# /usr/bin/mysqladmin -u root password 123456

注：因为开始时root没有密码，所以-p旧密码一项就可以省略了。
测试是否修改成功
1）不用密码登录

[root@test1 local]# mysql
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)

显示错误，说明密码已经修改。
2）用修改后的密码登录 

[root@test1 local]#mysql -u root -p
Enter password: (输入修改后的密码123456)
Welcome to the MySQL monitor.　Commands end with ; or /g.
Your MySQL connection id is 4 to server version: 4.0.16-standard
Type 'help;' or '/h' for help. Type '/c' to clear the buffer.
mysql>

成功！

这是通过mysqladmin命令修改口令，也可通过修改库来更改口令。

1.5 设置权限+创建用户

mysql的权限设置可以查看mysql数据库中的表mysql.user。mysql的权限设置：

mysql> select user ,host from user;
+--------+-----------+
| user   | host      |
+--------+-----------+
| aliyun | %         |
| root   | 127.0.0.1 |
| root   | ::1       |
|        | localhost |
| aliyun | localhost |
| root   | localhost |
+--------+-----------+
6 rows in set (0.00 sec)

其中 空白表示匿名用户，%表示所有IP地址，localhost表示本地访问，127.0.0.1表示本地访问，其中::1就是兼容支持ipv6的，表示同ipv4的127.0.0.1一样表示的localhost。

所以在安装伊始， 直接打mysql和打mysql -uroot -p实际上是两种用户，甚至你从不同的ip（本机或其他机器）登陆也是不同的用户，所以权限可能不同。
grant的同时也相当于在创建用户并分配权限。

这里的权限设置貌似很复杂，我测试的结果是，%并不包括localhost，如果你看不到什么表，一定是权限不够，并且一定是有某种账户可以登录的，一般匿名的用户权限很低，比如mysql直接登录是在localhost的匿名下，而不是root下。

mysql>grant select,insert,update,delete on test.user to mql@localhost identified by '123456';

给本地的用户mql分配可对数据库test的user表进行select,insert,update,delete操作的权限，并设定口令为123456。若mql用户不存在,则将自动创建此用户. 具体的权限控制在mysql.db表中可以查看到.也可直接对这个表进行更新操作进行权限的修改.

mysql>grant all privileges on test.* to mql@localhost identified by '123456';

给本地用户mql分配可对数据库test所有表进行所有操作的权限，并设定口令为123456。

mysql>grant all privileges on *.* to mql@'%' identified by '123456';

给来自任意ip的用户mql分配可对所有数据库的所有表进行所有操作的权限，并设定口令为123456。

mysql>grant all privileges on *.* to mql2@61.127.46.128 identified by '123456';

给来自61.127.46.128的用户mql2分配可对所有数据库的所有表进行所有操作的权限，并设定口令为123456。

1.6 启动与停止

1、启动
MySQL安装完成后启动文件mysql在/etc/init.d目录下，在需要启动时运行下面命令即可。

[root@test1 init.d]# /etc/init.d/mysql start

2、停止

/usr/bin/mysqladmin -u root -p shutdown

3、自动启动
1）察看mysql是否在自动启动列表中

[root@test1 local]#　/sbin/chkconfig --list

2）把MySQL添加到你系统的启动服务组里面去

[root@test1 local]#　/sbin/chkconfig　-- add　mysql

3）把MySQL从启动服务组里面删除。

[root@test1 local]#　/sbin/chkconfig　-- del　mysql

2、mysql的重要目录

MySQL安装完成后不象SQL Server默认安装在一个目录，它的数据库文件、配置文件和命令文件分别在不同的目录，

2.1、数据库目录　 

/var/lib/mysql/　　 

2.2、配置文件　　 

/usr/share/mysql（mysql.server命令及配置文件）　　 

2.3、相关命令　　 

/usr/bin(mysqladmin mysqldump等命令)　　 

2.4、启动脚本　　 

/etc/rc.d/init.d/（启动脚本文件mysql的目录）

3、登录

3.1 连接到本机上的MYSQL。

mysql-uroot -ppassword 

或者直接

mysql

或者有时候用root权限，但不需要密码

mysql -uroot -p

3.2 连接远程计算机 

mysql -h<hostname> -u<username> -p<password>
例如 mysql-h110.110.110.110-uroot-pabcd123

3.3 帮助

输入?

3.4 MySql如何执行sql文件

关键字: mysql source 
一些sql语句放在一个sql文件里面一起来执行，首先进入到数据 然后输入? 这样我们就能看到一些常用的命令，找到   source这个命令 ，输入你的文件地址 ，这样就会一起执行 ， 
Java代码 
如：/. D://bbs_sql//bbs.sql 不能要分号 或者 source D://bbs_sql//bbs.sql

3.5 查系统参数

版本号

SELECT VERSION();
查询当前时间
SELECT NOW(),CURRENT_DATE;
查询当前用户
SELECT USER();

4、MySQL的常用操作

注意：MySQL中每个命令后都要以分号；结尾。

4.1 显示数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

Mysql刚安装完有两个数据库：mysql和test。

mysql库非常重要，它里面有MySQL的系统信息，我们改密码和新增用户，实际上就是用这个库中的相关表进行操作。

4.2 显示数据库中的表

mysql> use test;
Database changed
mysql> show tables;

4.3 显示数据表的结构：

describe tablename;

4.4 显示表中的记录：

select * from tablename;

例如：显示mysql库中user表中的纪录。所有能对MySQL用户操作的用户都在此表中。

mysql> select * from user;

mysql竖列打印：

select * from sql_column\G;

mysql> select * from sql_column\G;
*************************** 1. row ***************************
          column_id: CID_2ba16354_c7e4_488a_ab75_44d9d7b45b61
      DISCRIMINATOR: com.aliyun.lineage.model.parser.SqlColumnExtend
           query_id: QID_ed8a929c_0ce4_4912_9c4c_4c11701bc4c3
          insert_id: IID_f0d5e1a0_2f34_4cd3_9449_27d74f147542
    target_table_id: TID_746cb28e_e402_413b_a0d0_8744dd956170
       target_table: gexing2
      source_column: gexing.value
       source_alias: value
        calculation: gexing.value
  final_calculation: alifinance.gexing.value
final_source_column: alifinance.gexing.value
               type: SELECT
             sql_id: 1
         project_id: NULL
         version_id: 0
*************************** 2. row ***************************
          column_id: CID_f22b0168_8066_4cb3_bd09_198f9b4b8ce9
      DISCRIMINATOR: com.aliyun.lineage.model.parser.SqlColumnExtend
           query_id: QID_ed8a929c_0ce4_4912_9c4c_4c11701bc4c3
          insert_id: IID_f0d5e1a0_2f34_4cd3_9449_27d74f147542
    target_table_id: TID_746cb28e_e402_413b_a0d0_8744dd956170
       target_table: gexing2
      source_column: gexing.key
       source_alias: key
        calculation: gexing.key
  final_calculation: alifinance.gexing.key
final_source_column: alifinance.gexing.key
               type: SELECT
             sql_id: 1
         project_id: NULL
         version_id: 0
2 rows in set (0.00 sec)

4.5 建库：

例如：创建一个名字为aaa的库

mysql> create database aaa;

4.6 建表：

例如：在刚创建的aaa库中建立表name,表中有id(序号，自动增长)，xm(姓名),xb(性别),csny(出身年月)四个字段

mysql> use aaa;
mysql> create table name (id int(3) auto_increment not null primary key, xm char(8),xb char(2),csny date);

4.7 增加记录

例如：增加几条相关纪录。

mysql> insert into name values('','Tom','ma','1971-10-01');
mysql> insert into name values('','Bob','fe','1972-05-20');

4.8 修改纪录

例如：将Tom的出生年月改为1971-01-10

mysql> update name set csny='1971-01-10' where xm='Tom';

4.9 删除纪录

例如：删除Tom的纪录。

mysql> delete from name where xm='Tom';

4.10 清空表

mysql> delete from name;

4.11 删表

mysql> drop table name;

4.12 删库

mysql> drop database aaa;

4.13 SQL常用数据操作：

(1) 数据记录筛选：

select * from 数据表 order by 字段名 [desc] limit 10;
select * from 数据表 where 字段名=字段值 order by 字段名 [desc];
select * from 数据表 where 字段名 like '%字段值%' order by 字段名 [desc];
select * from 数据表 where 字段名 in ('值1','值2','值3');
select * from 数据表 where 字段名 between 值1 and 值2;

(2) 更新数据记录：

update 数据表 set 字段名=字段值 where 条件表达式;
update 数据表 set 字段1=值1,字段2=值2 …… 字段n=值n where 条件表达式;

(3) 删除数据记录：

delete from 数据表 where 条件表达式;
delete from 数据表;  (将数据表所有记录删除)

(4) 添加数据记录：

insert into 数据表 (字段1,字段2,字段3 …) values (值1,值2,值3 …);
insert into 目标数据表 select * from 源数据表;  (把源数据表的记录添加到目标数据表)

(5) 数据记录统计函数：
AVG(字段名) 得出一个表格栏平均值
COUNT(*|字段名) 对数据行数的统计或对某一栏有值的数据行数统计
MAX(字段名) 取得一个表格栏最大的值
MIN(字段名) 取得一个表格栏最小的值
SUM(字段名) 把数据栏的值相加
引用以上函数的方法：

select sum(字段名) as 别名 from 数据表 where 条件表达式;

(5) 数据表的建立：

CREATE TABLE 数据表名称(字段1 类型1(长度),字段2 类型2(长度) …… );

例如：

CREATE TABLE tab01(name varchar(50),datetime default now());

4.14 mysql导出数据到xml

将数据库的查询结果保存在xml文件中:

mysql -X -uroot -hhostname -ppassword -Pport -e "select * from dbname.tablename;" | tee test.xml

以XML形式备份数据库:

mysqldump -uroot -ppassword -hhostname -Pport dbname --xml  > dbname.sql

将XML形式备份的数据库恢复:

mysqldump -uroot -ppassword -hhostname -Pport dbname --xml < dbname.sql

4.15 mysql导入数据

从sql文件中导入数据库到mysql：

mysql -uroot -ppassword < db.sql