mysql> show databases; mysql> show databases \G #以行的方式显示 mysql> show table status from test where name='student' \G
mysql> create database HA;
mysql> drop database <code>HA-test</code>
mysql> show tables;
mysql> create table student(id int(20),name char(40),age int);语法:create table 表名 (字段名 类型, 字段名 类型, 字段名 类型);
mysql> desc student;
mysql> show create table student \G
mysql> create table student2(id int(20),name char(40),age int)ENGINE=MyISAM DEFAULT CHARSET=utf8;
mysql> drop table student2;
mysql>alter table student rename students;
mysql> alter table students change name stname char(20);
mysql> alter table students add sex enum('M','W');语法:alter table 表名 add 字段名 字段类型;
mysql> alter table students add uid int(10) first;在第一列添加一个字段; mysql> alter table students add address char(40) after age;在age后面添加一个address字段:
mysql> alter table students drop address;语法:alter table 表名 drop 字段名 ;
mysql> select * from students;*表示所有
mysql> select name from student;
mysql> select *from HA.student; 数据库名.表名 语法:SELECT 字段 FROM 数据库名.表名;
mysql> insert into students values(1,'zhangs',21);语法:insert into 表名values (字段值1,字段值2, 字段值3);
mysql> insert into student values(2,'lis',24),(3,'wange',26);
mysql> delete from students where id=3;删除id为3的行
mysql> update students set sex='M' where id=2; 从id 为2 的 sex 改成M mysql> update students set age=10 where id=2 ; 从id为2 的 age 改成10 mysql> update students set id=2; 所有的都变为2
mysql> update students set stname='zhangsan',age=21 where uid=1; 从uid 为1 的
mysql> select name,age from students;
mysql> select id,name,age from students where id>3 and age>25;
mysql> select * from students where name='lis' and (age=21 or age=10);
mysql> select * from student where name like 'li%';
mysql>select * from student where name like '%is%' and name like '%a%'; +------+------------+------+ | id | name | age | +------+------------+------+ | 7 | siswaisaaa | 20 | +------+------------+------+ 1 row in set (0.00 sec)
mysql> select name from students where binary name='ZHANGS';
mysql> select distinct id from students order by id;
mysql> select distinct id,name from students order by id desc;### 查询id 降序查询
方法1; mysql> set password for root@localhost = password('123456');
方法2:用mysqladmin 格式:mysqladmin -u用户名 -p旧密码 password 新密码 例子:mysqladmin -uroot -p123456 password 123
方法3:用UPDATE直接编辑user表 首先登录MySQL。 mysql> use mysql; mysql> update user set password=password('123') where user='root' and host='localhost'; mysql> flush privileges;刷新
[root@xuegod210 ~]# mysqldump -uroot -p123456 HA > HA.dump 备份完整数据
[root@xuegod210 ~]# mysqldump -uroot -p123456 --no-data --databases HA > ha.dump 备份表结构(没有完整数据)
mysql> source /root/books.sql; ##导入表时,不需要重新,创建表。 先use 库
mysql> show engines;
mysql> show variables like '%storage_engine%'; alter table student engine=innodb;
mysql> alter table student engine=innodb; 修改表的引擎
整体上来说,复制有3 个步骤:
(1) master 将改发记录到二迚制日志(binary log)中(这些记录叨做二迚制日志事件,binarylog events);
(2) slave 将master 的binary log events 拷贝到它的中继日志(relay log);
(3) slave 重做中继日志中的事件,修改salve 上的数据;
本文转自 Innocence_caosm 51CTO博客,原文链接:http://blog.51cto.com/innocence/1954772,如需转载请自行联系原作者