mysql常用命令

  mysql -h主机地址 -u用户名 －p用户密码

建立一个名为xhkdb的数据库

mysql> create database xhkdb;

show databases ;（注意：最后有个s）

建立user表，设置时间戳和存储引擎

create table user (
 id int(11) not null auto_increment primary key,
 account varchar(20) not null,
 password varchar(32) default '',
 nickname varchar(20) default '',
 level int(4) default 1,
 create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

建立一个名为jishubu的表，建立hash分区。

mysql> create table jishubu(

   -> id int(4) not null primary key,

   -> name char(20),

   -> sex int(4),

   -> age int(2)

->)

Partition by hash (id) partitions 4;

建立一个名为jishubu的表，建立range分区。

mysql> create table jishubu(

   -> id int(4) not null primary key,

   -> name char(20),

    ->sex int(4),

   -> age int(2)

->)

Partitionby range （id） (

PartitionP0 values less than (10),

PatitionP1 values less than (20),

PartitionP2 values less than (30)

);

mysql> show create table aaa \ G;

 

mysql> insert into jishubu values(1,'Tom',96.45),(2,'Joan',82.99), (3,'Wang',96.59);

mysql> update jishubu set sex='man' whereid=1;

mysql> update aaa set id=id+1 whereid>=2;(把id=2空出来)

describe表名;  查看表结构

mysql> delete from member where id=1;

alter table 表名 add column 列名 varchar（20）；

alter table 表名 add column 列名 varchar（20）[first|after 列名]；

alter table 表名 drop column 列名；

alter table 表名 change id id int（4）；更改字段名

alter table 表名modify namevarchar(30)；更改字段数据类型

alter table 表名modify sex int（3）after id；更换字段位置

alter table 旧表名 rename 新表名；

alter table 表名 engine=myisam；

constraint 外键名 foreign key（id，name）refrerncesmember1（id，name）；建表的时候建外键

alter table 表名 drop foreign key外键名；

show create table 表名 \G；

alter table 表名 drop foreign key 外键别名；

alter table 表名 add primary key(id);   添加主键

alter table 表名 drop primary key；   删除主键

 

show grants；

软连接 In -s /opt/mysql/bin/mysqldump     /usr/bin

select * from member where ange > 20 and age < 30；

select * from member where phone like ‘%888%’；（查看前三位）

 select * from member order by age;  （升序）

select * from member order by age desc;   (降序)

 select * from member where age > 20 order by  age;

 select id，name，count（name） from member group by name；

 

mysql>optimize table famliy;  (优化famliy表)

 

MYSQL索引

create index 索引名 on表名（列名）；

例create index chen on member(name);

show index from member;

show keys from member；

drop index chen on member;

 

 

mysql备份与还原

[root@localhost~]#/usr/bin/mysqldump –uroot –proot  –l  -F  jishubu member famliy > /data/jishubu.dmp

[root@localhost~]#/usr/bin/mysql –uroot –proot jishubu < /data/jishubu.dmp

[root@localhost~]#mysqlbinlog mysql-bin.000001 | mysql –uroot –proot member

 

 

mysql触发器

一、创建只有一个执行语句的触发器

Create trigger 触发器名 before|after 触发事件

On 表名 for each row

执行语句

例mysql> create trigger aaa before insert

    -> on member for each row

-> insert into trigger name values(now());

 

二、创建有多个执行语句的触发器

Create trigger 触发器名 before|after 触发事件

On 表名 for each row

Begin

执行语句列表        （不用的执行语句之间用；号隔开）

End

例mysql> create trigger aaa after delete

    -> on member for each row

    -> begin

    -> insert into trigger time values('21:03:01');

    -> insert into trigger time values('12:23:12');

    -> end

    -> &&

 

查看触发器  

mysql>show triggers \G

mysql>select * from information_schema.Triggers  \G

mysql>select * from information_schema.triggers where trigger_name='aaa'  \G

 

mysql中触发器执行的顺序是before、表操作（insert、update、delete）、after触发器。

 

删除触发器

mysql>drop trigger aaa;

 

mysql>delimiter ;     （更改mysql结束符号）

 

 

创建视图

一、在单表上创建视图

Create view 视图表名 as select * from 表名；

例mysql>create view aaa as select * from member;

mysql> create view bbb (id,name,sex) as select id,name,sex from member;

Desc aaa；    查看视图表结构

二、在多表上创建视图

Create algorithm=merge view 视图表名 (字段1，字段2，字段3)

As select字段1，字段2，字段3      /这些字段是表下的字段

From 表1，表2 where 表1字段1=表2字段1

With local check option；

例mysql> create algorithm=merge view bbb (name,sex,age)

    -> as select member.name,member.age,famliy.sex
    -> from member,famliy where member.id=famliy.id
    -> with local check option;

修改视图（alter view修改）

Alter algorithm=undefined|merge|tempbable view 视图名（属性字段）

Asselect语句

With cascaded|local check potion;

 

例mysql> alter view aaa (dname,dsex,dage)

-> as select member.name,famliy.sex,famliy.age

-> from member,famliy where member.id=famliy.id

    ->with check option;

修改视图（create or replace view修改）

create or replace algorithm=undefined|merge|tempbable view 视图名（属性字段）

as select语句

Withcascaded|local check potion;

 

例mysql> create or replace algorithm=temptable

    -> view aaa (name,sex,age)

    -> as select  member.name,famliy.sex,famliy.age

-> from member,famliy;

 

查看视图

1 mysql> show  table status like 'aaa' \G;

2 mysql> show  create view aaa \G;

3 mysql> select * from information_schema.views \G;

 

删除视图

1 mysql> drop view if exists aaa;

2 mysql> drop view if exists aaa,bbb;

 

查看mysql是否有drop权限

Select drop_privfrom mysql.user where user=’root’;

 

 

查询语句：

（like相当于=号，not like相当于“不等”号）但使用通配符或_时不能使用=号

mysql>select * from member where address like 'beijing%';    （%为通配符）

mysql>select * from member where name like 'zhan_san';   （_为任意字符）

 

Is null为空值，is not null 是不能为空值

例mysql> select * from famliy where phone is null;

mysql> select * from famliy where phone is not null;

 

mysql>select * from member where id in(1,2);

mysql>select * from member where name not in('zhangsan','lisi');

 

between and 可以判断某个字段是否在指定范围内

mysql>select * from member where age between 30 and 50; （包含30和50）

mysql>select * from member where age not between 30 and 50;

个人认为between and就相当于>=  <=

 

And为‘并’，满足所有条件 ； or为‘或’，满足其中一个条件即可。

mysql>select * from famliy where name like 'chenb' and sex like 'man';

mysql>select * from famliy where name like 'chenb' or age like 34;

mysql>select * from famliy where age like 27 and sex like 'man' or age like 34;

 

groupby 分组查询

mysql>select name,id,age from member group by age;

mysql>select sex,count(name) from member group by sex;

mysql>select sex,count(name) from member group by sex having count(name) >5;

group_concat()和group by 一起使用

mysql>select sex,group_concat(name) from member group by sex;

groupby和with rollup一起使用    （with rollup求和）

mysql>select sex,count(name) from member group by sex with rollup;

mysql>select sex,group_concat(name) from member group by sex with rollup;

查询member表下所有记录，但只显示前两行

mysql>select * from member limit 2;

mysql>select * from member limit 2,3;  （2是起始位置，显示3行）

 

集合函数查询

Count()函数用来统计记录的条数

mysql>select count(name) from member;

mysql>select sex,count(name) from member group by sex;

sum()函数是用来求和的

mysql>select id,sum(score) from member where id;

mysql>select id,sum(score) from member group by id;

avg()函数是求平均值的函数

mysql>select avg(yuwen) from chengji;

max()函数是求最大值得函数

min()函数是求最小值的函数

 

连接查询

内连接查询方式

mysql>select member.name,famliy.sex,member.age from member,famliy where member.id=famliy.id;

外连接查询方式

Select属性名列表from 表1 left|right  join  表2

On 表1.属性名1=表2.属性名2；

mysql>select member.name,famliy.sex,member.age from member right join famliy on member.id=famliy.id;

 

子查询

 

 

 

mysql中更改root密码
一：[root@localhost~]# mysqladmin -uroot -p123456 password 'root'

二：mysql> update mysql.user set password=password('123456') where user='root' and host='localhost';

mysql> flush privileges;

三：mysql> set password=password('root');

mysql> flush privileges;

注意用户名前可以有空格也可以没有空格，但是密码前必须没有空格，否则让你重新输入密码。

 

Mysql用户管理

新建普通用户

mysql>create user 'chenbo'@'localhost' identified by 'chenbo123';

mysql>grant select on *.* to 'chenbu'@'localhost' identified by 'chenbu123';

查看用户

mysql>select * from mysql.user where user='chenbo' \G;

删除用户

mysql>drop user 'chenbo'@'localhost';

mysql>delete from mysql.user where host='localhost' and user='chenbu';

mysql>flush privileges;    （刷新权限表）

root给普通用户修改密码：

mysql>set password for 'chenbo'@'localhost'=password('123456');

mysql>grant select on *.* to 'chenbo'@'localhost' identified by '123456';

mysql>update mysql.user set password=password('chenbo') where user='chenbo' and host='localhost';

mysql>flush privileges;   (刷新权限表)

 

普通用户给自己修改密码

mysql>set password=password('chenbo');

 

 

 

权限管理：

MySQL 赋予用户权限命令的简单格式可概括为：
grant 权限 on数据库对象 to 用户

grant 权限 on数据库对象 to 用户identified by "密码"

 

//开放管理MySQL中所有数据库的权限
grant all on *.* to ‘chenbo’@'192.168.0.10'identified by "dbapasswd";

 

//开放管理MySQL中具体数据库的表(testdb.table1)的权限
grant all on testdb.teable1 to ‘chenbo’@'192.168.0.10' identified by "dbapasswd";

 

//开放管理MySQL中具体数据库的表(testdb.table1)的部分列的权限
grant select(host,user,password) on testdb.table1 to ‘chenbo’@'192.168.0.%' identified by "dbapasswd";

 

//开放管理操作指令
grant select, insert, update, delete on testdb to ‘chenbo’@'localhost' identified by "dbapasswd";

 

添加权限

mysql>grant select,update,insert on *.*      （*.*表示所有表）

    -> to 'chenbo'@'localhost' identified by 'chenbo'

-> with grant option;

mysql>grant all privileges on *.*

-> to 'chenbo'@'localhost' identified by 'chenbo';

//回收权限
revoke all on *.* from ‘chenbo’@’localhost’;

收回权限mysql> revoke select,update on *.* from 'chenbo'@'localhost';

mysql>revoke all privileges, grant option from 'chenbo'@'localhost';

 

//查看 MySQL 用户权限

show grants;
show grants for chenbo@localhost;

 

 

mysql存储过程和函数

创建存储过程和函数

mysql>create procedure aaa (in emp_id int,out count_num int)

    -> reads sql data

    -> begin

    -> select count(*) into count_num

    -> from member

    -> where id=emp_id ;

    -> end

-> &&

 

创建存储函数

mysql>create function aaa (emp_id int)

    -> returns varchar(20)

    -> begin

    -> return (select name from member where id=emp_id);

    -> end

-> &&

 

光标的使用

声明光标

mysql>declare cur_member cursor for select name,age from member;

打开光标

mysql>open cut_member;

使用光标

mysql>fetch cut_member into name,age;

关闭光标

mysql>close cur_member;

 

MYSQL分区

通过使用show variables like ‘%partition%’；来确定当前的mysql是否支持分区。

 

mysql>alter table aaa add partition partitions 6;

mysql>alter table member remove partitioning;（删除表的所有分区）

 

 

Mysql日志

日志的读取

[root@localhost~]# mysqlbinlog /var/lib/mysql/mysql-bin.000001

 

日志的删除

1.  mysql> reset master;

2.  mysql> purge master logs to "mysql-bin.000006";

3.  mysql> purge master logs before '2014-08-12 08:05:00';

4.  在my.cnf中设置参数expire_logs_days=7

查看mysql日志

mysql>system ls -ltr /var/lib/mysql/mysql-bin*

 

通过配置文件my.cnf中的log-slow-queries选项可以开启慢查询日志

[mysqld]

Log-bin=mysql-bin    开启二进制日志（binlog日志)

slow_query_log=1    开启慢查询日志

 

查询long_query_time的值

mysql> show variables like 'long%';

 

mysql> set long_query_time=2;        设置慢查询时间

mysql> set globallong_query_time=0.01;       设置微妙慢查询时间