mysql

常用mysql集群解决方案：1 heartbeat+drbd+mysql的复制功能 2、共享存储+mysql复制功能   第一种用的比较多 共享存储用的少 ，还有mysql标准版电信版的付费解决方案

oracle的RAC方案用的是共享存储的结构方式

下图两个vip地址实现mysql读写分离 ：

　　写：as把数据写到node1，node1再主从复制到slave，node1故障后通过keepalived转移到node2 （ip漂移，开启drbd故障转移和mysql故障转移）

　　读：as请求到达keep1，keep通过lvs分别从salve读取数据，keep故障后转移到keep2

 

 

mysql主从

视图view

MySQL 5.1参考手册

mysql日期

centos7下快速安装mysql 

阿铭mysql

Mysql常用命令行大全

 mysql重装

Centos下MySQL使用总结 按教程更改目录失败

mysql grant命令

mysqlcheck修复数据库命令

mysql的distinct用法

mysql中engine=innodb和engine=myisam的区别

mysql 的主键和唯一  

添加rownum

sql> SELECT @rownum:=@rownum+1 AS rownum, zzx.*  FROM (SELECT @rownum:=0) r, zzx;

每一个派生出来的表必须有一个别名，当在做嵌套查询的时候，做例如select * form (select * from ...)之类的查询的时候会出现此异常

此时只需要在select * form (select * from ...)后面再加一个yourtablename，yourtablename为自己取的表名，就可以查询出结果

 mysql> SELECT * FROM (SELECT * FROM zzx WHERE gender='m') as mytable WHERE mytable.name='zzx1'; # 

 mysql> select MIn(age) as zuixiao ,MAX(age) as zuida from zzx;

(select t_organization.name as name

from t_organization)

union

(select t_department.name  as name

from t_department ) 
只要两个查询语句查询出来的字段数目以及标题一致，便可将查询结果合并成一个结果集

select concat(t_organization.name,'-',t_department.name) as 'name'
from t_organization,t_department

利用concat可以将查询结果的不同列合并成一个列



[root@localhost ~]# mysqlcheck -Aao -auto-repair -uroot -p > /dev/null 2>&1              mysqlcheck -ao -auto-repair DATABASE_name  -uroot -p

mysql> select * from zzx where course RLIKE '^[hw].*$';  #rlike支持正则表达式    查询开头以h或w开头的course  
mysql> select * from zzx where age in (18,20,21);   # 等于mysql> select * from zzx where age=18 or age=20 or age=21; 

mysql>  select * from zzx where haha is null; 或者mysql>  select * from zzx where haha <=> null;      #查空值时要用 is null  is not null
mysql>  select * from zzx order by name desc ;   # asc|desc   asc默认   排序
mysql>  select name as st_name from zzx order by name desc ;  #别名
mysql> select 2+1 as sum;
+-----+
| sum |
+-----+
|   3 |
+-----+
1 row in set (0.00 sec)
mysql> select avg(age) as avg_age from zzx;  #求平均年龄     avg|max|min|sum|count    平均 最大 最小 求和   个数
 
mysql> select avg(age) as avg_age,gender from zzx group by gender;     #根据性别分组 求平均值
+---------+--------+
| avg_age | gender |
+---------+--------+
| 26.5000 | f      |
| 18.2000 | m      |
+---------+--------+

mysql> select count(*),gender from zzx group by gender;    #统计不同性别有多少人 
+----------+--------+
| count(*) | gender |
+----------+--------+
|        2 | f      |
|        5 | m      |
+----------+--------+
 

mysql> select count(age) as age_count,gender from zzx group by gender ;       
+-----------+--------+
| age_count | gender |
+-----------+--------+
|         2 | f      |
|         5 | m      |
+-----------+--------+
mysql> select count(age) as age_count,gender from zzx group by gender having age_count >2;   #having 跟在group by 后面
+-----------+--------+
| age_count | gender |
+-----------+--------+
|         5 | m      |
+-----------+--------+
mysql> select student.sid,courses.couse from student,courses where student.cid=courses.cid;  #多表查询   mysql> select s.sid,c.couse from student as s ,courses as c where s.cid=c.cid;  用别名

mysql> select couse,cid from courses where cid not in (select cid from student); #找出没人选修的课的课名

 

/etc/my.cnf 这个位置，如果没有的话请先copy一份预设的设定档过去），然后在当中加上如下一行设定即大功告成，之后MySQL都会自动忽略所有的SQL语法中大小写的差别啰！  lower_case_table_names = 1

安装mysql客户端：

          yum install mysql

 

安装mysql 服务器端：

          yum install mysql-server

 

mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15 一个10条

mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.

　mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行　　//换句话说，LIMIT n 等价于 LIMIT 0,n。

SELECT * FROM table WHERE id BETWEEN 1000000 AND 1000010; 替代SELECT * FROM table WHERE id >= (SELECT id FROM table LIMIT 1000000, 1) LIMIT 10;

 mysql> show create table helloo;  #查询建表语句  可以查表字段等

mysql> show index from zzx.z;  等于 show index from z from zzx;   from 表 from 数据库

# wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm

# rpm -ivh mysql-community-release-el7-5.noarch.rpm

# yum install mysql-community-server

成功安装之后启动mysql服务   启动前/var/lib/mysql/下没有文件  所以第一次启动需要生产文件 速度就比较慢了   启动后能看到有一个文件mysql.sock     不启动mysqld服务就直接账号密码登入mysql会提示错误 ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

1	# service mysqld restart

centos6.5无法用service 只能[root@localhost ~]# /etc/init.d/mysqld start

初次安装mysql是root账户是没有密码的

设置密码的方法

1 2 3

# mysql -uroot mysql> set password for ‘root’@‘localhost’ = password('密码'); mysql> exit

重新进入mysql

　　

mysql -uroot -p123456

mysql> select user();  #查看当前是登入的是哪个用户
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.06 sec)

查看当前数据库版本

mysql> select version();

 1]查看mysql中都有哪些数据库？

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.01 sec)

 显示有3个数据库 information_schema | mysql | performance_schema

4]如何创建和删除一个数据库？  CREATE DATABASE|SCHEMA [IF NOT EXISTS] db_name  [SHARACTER SET=]  [COLLATE=]

                                         DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

帮助命令：

help create table;和help alter table;和help drop database;help create table;

mysql> create database zzx; 
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| zzx                |
+--------------------+
4 rows in set (0.00 sec)

mysql> drop database zzx;

创建mysql数据库的时候指定某种固定的编码方式

mysql> show character set; #列出可用的字符集  例如gbk   gbk_b
mysql> show collation;    #某个字符集的排序规则 可以SHOW COLLATION LIKE 'latin1%'; 
mysql> CREATE SCHEMA IF NOT EXISTS students CHARACTER SET 'gbk' COLLATE 'gbk_chinese_ci';

备份数据库       linux下mysql自动备份脚本      (MySQL、ORACLE)简单的异地备份方案

[root@localhost ~]# mysqldump -uroot -p zzx1 > /root/zzx.sql      # 注意mysqldump不是在mysql中进行     
Enter password: 

或如下：

导入数据库   要导入存在的数据表   不能导入不存在的表

[root@localhost ~]# mysql -uroot -p zzx2 </root/zzx.sql
Enter password: 

增加一个只能在（localhost指本地主机，即MySQL数据库所在的那台主机）localhost上登入，对数据库aaa进行查询、插入、修改、删除的操作

mysql>grant select,insert,update,delete on aaa.* to user_2@localhost identified by "123";

添加一个可以在网络上操作的用户，这样不安全

mysql> grant select,insert,update,delete on *.* to user_1@"%" Identified by "123";

建表  查有几个表    use某个表之后 可以查当前使用的是哪个库  mysql> select database();

三个建表：1、直接定义一张空表； CREATE  TABLE  [IF NOT EXISTS] tbl_name (COL_NAME   COL_DEFINATION)

mysql> CREATE TABLE tb1 (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Name CHAR(20) NOT NULL,Age TINYINT NOT NULL);#id为 ：整形，非空，无符号，自增长，主键 ；Name 字符型，非空;Age 短整型，非空

或者把primary key放后面 mysql> CREATE TABLE tb2 (id INT UNSIGNED NOT NULL AUTO_INCREMENT,Name CHAR(20) NOT NULL,Age TINYINT NOT NULL,PRIMARY KEY(id));

               2、从其他表中查询出数据，并以之创建新表； CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

              3、以其他表为模板创建一个空表 CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name    { LIKE old_tbl_name | (LIKE old_tbl_name) }

mysql> use zzx
Database changed
mysql> create table z1(id int(3) auto_increment not null primary key,xm char(8),xb char(2),csny date);
Query OK, 0 rows affected (0.26 sec)
mysql> describe z1;    # 可以缩写成  desc z1 ;  等于mysql> show columns from z1 from zzx;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(3)  | NO   | PRI | NULL    | auto_increment |
| xm    | char(8) | YES  |     | NULL    |                |
| xb    | char(2) | YES  |     | NULL    |                |
| csny  | date    | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)




mysql> show tables;
+---------------+
| Tables_in_zzx |
+---------------+
| z1            |
+---------------+
1 row in set (0.00 sec)

模仿其他表创建表
mysql> create table kongbiao like zzx;  #创建一个结构与表zzx一样的空表
mysql> create table testcourses1 select * from courses where cid <=2;   #创建一个courses表中cid小于等于2的表    写规范点mysql> create table testcourses1 as (select * from courses where cid <=2);

create table student222  select * from student  where ……;//用于复制前未创建新表dust的情况下 

insert into dust select * from student;//已经创建了新表dust的情况下 

插入数据

mysql> insert into z1 values("1",'zhansan','n','1989-05-28');
Query OK, 1 row affected (0.00 sec)
mysql> select * from z1;
+----+---------+------+------------+
| id | xm      | xb   | csny       |
+----+---------+------+------------+
|  1 | zhansan | n    | 1989-05-28 |
+----+---------+------+------------+
1 row in set (0.00 sec)

 修改记录

mysql> update z1 set csny='1988-05-28' where xm="zhansan";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from z1;
+----+---------+------+------------+
| id | xm      | xb   | csny       |
+----+---------+------+------------+
|  1 | zhansan | n    | 1988-05-28 |
|  3 | lisi    | n    | 1989-05-28 |
+----+---------+------+------------+
2 rows in set (0.00 sec)

 删除记录

mysql> delete from z1 where xm='lisi';
Query OK, 1 row affected (0.00 sec)

mysql> select * from z1;
+----+---------+------+------------+
| id | xm      | xb   | csny       |
+----+---------+------+------------+
|  1 | zhansan | n    | 1988-05-28 |
+----+---------+------+------------+
1 row in set (0.00 sec)

10、删库和删表
drop database 库名;
drop table 表名；

 修改表 ALTRT

mysql> ALTER TABLE zzx.z ADD course VARCHAR(100);  #增加一个course   删除mysql> ALTER TABLE zzx.z DROP Course;

修改命令:ALTER DATABASE Test_DB DEFAULT CHARACTER SET utf8; 查看命令:SHOW CREATE DATABASE Test_DB;
mysql> alter table zzx add column dte int not null; #添加列
mysql> alter table zzx drop column dte ;  #删除列
mysql> alter table zzx add column dte int not null after name;  #在name后添加列名dte

mysql> alter table zzx add column dteee int not null  first;  # 排在第一列

修改字段名

mysql> ALTER TABLE z CHANGE course Course varchar(100); #course改成Course      或者用modify   

 mysql> alter table zzx change id id int unsigned  auto_increment ;

mysql> alter table  hello modify  Name char(3);  #modify只能改类型 不能改名字

改表名

mysql> alter table hello rename helloo;

还可以移动位置  mysql> ALTER TABLE z CHANGE course Course varchar(100) AFTER name;

添加唯一键

插入insert

mysql> INSERT INTO  z (name,gender) VALUE('zzx1','m'),('xiaolongnv','f'); #注意让course为空

mysql> select * from z;
+------------+--------+--------+
| name       | gender | course |
+------------+--------+--------+
| zzx1       | m      | NULL   |
| xiaolongnv | f      | NULL   |
+------------+--------+--------+
2 rows in set (0.00 sec)

 完全插入 不为空

 mysql> insert into z values ('xiao','m','hamagong');

查看mysql进程--show processlist

 mysql的主键问题： 添加主键 删除主键
Mysql的两种主键。Primary key 和not null auto_incriment
在建立mysql表时，给一个字段添加了主键primary key 
在insert数据时可以不用insert主键，mysql会自动添加0，但是在第二次insert时没有填写值mysql数据库还是默认添加0，会导致有重复的主键，这是不可以的。所以在定义了primary key时，在insert数据时要给主键填写值。
在建立mysql表时，给一个字段添加了主键not null auto_increment=1;
这也是一个主键。是自增长的以1为开始。这个字段是可以不用填写值的，mysql数据库会自动给填写值，不会出现重复primary key的状况。
Alter table tb add primary key(id);
Alter table tb change id id int(10) not null auto_increment=1;
4 删除自增长的主键id
先删除自增长再删除主键
Alter table tb change id id int(10);//删除自增长

Alter table tb drop primary key;//删除主建  # 先删除自增长才能删主键  就像添加自增长时必须添加键一样  不能让自增长一个在那

删除唯一键呢

添加索引 删除索引 

先mysql> show index from zzx;  #查询索引  可能一个column有好几个唯一键

mysql> alter table zzx drop index id_3 ;  #id_3是其中的一个索引名
mysql> alter table zzx add index id_4(id) ;  #为 id 添加一个索引 索引名为id_4

 Non_unique

如果索引不能包括重复词，则为0。如果可以，则为1。

自增长一定要是主键吗？    不一定  是key就行 

there can be only one auto column and it must be defined as a key

每张表只能设置一个字段为自增长字段，这个字段可以是主键，也可以不是主键，如果不是主键，则必须设置为一种“键(key)”，其实，主键(primary key)也是键(key)的一种，key还包括外键(foreign key)、唯一键(unique key)等，

应该说是 key 属性
1. 如果Key是空的, 那么该列值的可以重复, 表示该列没有索引, 或者是一个非唯一的复合索引的非前导列
2. 如果Key是PRI, 那么该列是主键的组成部分
3. 如果Key是UNI, 那么该列是一个唯一值索引的第一列(前导列),并别不能含有空值(NULL)
4. 如果Key是MUL, 那么该列的值可以重复, 该列是一个非唯一索引的前导列(第一列)或者是一个唯一性索引的组成部分但是可以含有空值NULL
如果对于一个列的定义，同时满足上述4种情况的多种，比如一个列既是PRI,又是UNI
那么"desc 表名"的时候，显示的Key值按照优先级来显示 PRI->UNI->MUL

那么此时，显示PRI

一个唯一性索引列可以显示为PRI,并且该列不能含有空值，同时该表没有主键

一个唯一性索引列可以显示为MUL, 如果多列构成了一个唯一性复合索引

因为虽然索引的多列组合是唯一的，比如ID+NAME是唯一的，但是没一个单独的列依然可以有重复的值

只要ID+NAME是唯一的即可

mysql> alter table z add index suoyin(xm);    alter table 表名 add index 索引名（列名）;

mysql 添加索引 mysql 如何创建索引

-- 创建无索引的表格
create table testNoPK (
id int not null,
name varchar(10)
);
-- 创建普通索引
create index IDX_testNoPK_Name on testNoPK (name);

1.添加PRIMARY KEY（主键索引）
mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
2.添加UNIQUE(唯一索引)
mysql>ALTER TABLE `table_name` ADD UNIQUE (
`column`
)
3.添加INDEX(普通索引)
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
4.添加FULLTEXT(全文索引)
mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
5.添加多列索引
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

13. 创建一个普通用户并授权  GRANT 参考

创建用户并查询

mysql> CREATE USER 'zzx'@'%' IDENTIFIED BY '1';             #创建用户  zzx 密码 1   这时候用户没有任何权限，要接着grant权限
mysql> SHOW GRANTS FOR 'zzx'@'%';    # 显示用户授权

 GRANT ALL PRIVILEGES ON *.* TO 'nihao'@'localhost' WITH GRANT OPTION

mysql> show grants for nihao@'localhost';   查其他用户权限

mysql> show grants ;  #查本用户权限

mysql> grant all on *.* to user1 identified by '123456';  #创建用户并授权   用户不存在会自动创建用户 并给与全部权限  如果用户存在就修改权限并设置密码
      Query OK, 0 rows affected (0.01 sec)

all 表示所有的权限（读、写、查询、删除等等操作）， *.* 前面的 * 表示所有的数据库，后面的 * 表示所有的表，identified by 后面跟密码，用单引号括起来。这里的user1指的是localhost上的user1，如果是给网络上的其他机器上的某个用户授权则这样:

mysql> grant all on db1.* to 'user2'@'10.0.2.100' identified by '111222';
Query OK, 0 rows affected (0.01 sec)

用户和主机的IP之间有一个@，另外主机IP那里可以用%替代，表示所有主机，例如:

mysql> grant all on db1.* to 'user3'@'%' identified by '231222';
Query OK, 0 rows affected (0.00 sec)

查看MYSQL数据库中所有用户
mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
mysql> select user();  #查看当前是登入的是哪个用户
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.06 sec)
mysql> select distinct user from mysql.user;       #查到底有多少用户
+-------+
| user  |
+-------+
| nihao |
| root  |
|       |
| qq    |
| zzx   |
+-------+
5 rows in set (0.00 sec)

删除重复记录

SQL>create table temp_emp as (select distinct * from employee)

SQL>删除或者rename原来的表                                     ############truncate table employee; (清空employee表的数据）                             
SQL>rename table temp_emp to employee; (再将表重命名）

 查询字段出现次数

 mysql> select name,count(*) from zzx group by name;
+------------+----------+
| name       | count(*) |
+------------+----------+
| fsadf      |        1 |
| xiao       |        3 |
| xiaolongnv |        1 |
| zhangsan   |        1 |
+------------+----------+

查询出现三次的name

mysql> select name from zzx group by name having count(*)=3;
+------+
| name |
+------+
| xiao |
+------+
1 row in set (0.00 sec)