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参考手册
Centos下MySQL使用总结 按教程更改目录失败
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> 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 -urootmysql> 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的主键问题： 添加主键 删除主键
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
自增长一定要是主键吗？ 不一定 是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)
