Mysql命令

一、表的连接：

连接:－－－－>内连接（相当于取交集）

mysql> select *from users;

+------+------+

| uid | name |

+------+------+

| 500 | u1 |

| 501 | u2 |

| 503 | u3 |

| 504 | u4 |

+------+------+

4 rows in set (0.00 sec)

mysql> select *from groups;

+------+------+

| gid | name |

+------+------+

| 600 | g1 |

| 601 | g2 |

| 602 | g3 |

| 603 | g4 |

+------+------+

4 rows in set (0.00 sec)

mysql> select *from u_g;

+------+------+

| uid | gid |

+------+------+

| 500 | 600 |

| 500 | 601 |

| 501 | 601 |

| 503 | 603 |

+------+------+

mysql> select users.name,groups.name from users,groups,u_g where users.uid=u_g.uid and groups.gid=u_g.gid;条件过滤

－－－－>外连接

mysql> select *from users left join u_g on users.uid=u_g.uid;左连接（会显示左边表的全部数据）

+------+------+------+------+

| uid | name | uid | gid |

+------+------+------+------+

| 500 | u1 | 500 | 600 |

| 500 | u1 | 500 | 601 |

| 501 | u2 | 501 | 601 |

| 503 | u3 | 503 | 603 |

| 504 | u4 | NULL | NULL |

+------+------+------+------+

mysql> select *from users left join u_g using(uid) where gid is null;重复的字段不显示

+------+------+------+

| uid | name | gid |

+------+------+------+

| 504 | u4 | NULL |

+------+------+------+

mysql> select *from u_g right join groups on u_g.gid=groups.gid;右连接（会显示右边表的全部数据）

+------+------+------+------+

| uid | gid | gid | name |

+------+------+------+------+

| 500 | 600 | 600 | g1 |

| 500 | 601 | 601 | g2 |

| 501 | 601 | 601 | g2 |

| NULL | NULL | 602 | g3 |

| 503 | 603 | 603 | g4 |

+------+------+------+------+

mysql> select *from u_g right join groups on u_g.gid=groups.gid where uid is not null;

+------+------+------+------+

| uid | gid | gid | name |

+------+------+------+------+

| 500 | 600 | 600 | g1 |

| 500 | 601 | 601 | g2 |

| 501 | 601 | 601 | g2 |

| 503 | 603 | 603 | g4 |

+------+------+------+------+

mysql> select *from u_g right join groups on u_g.gid=groups.gid where uid is not null group by name;分组

+------+------+------+------+

| uid | gid | gid | name |

+------+------+------+------+

| 500 | 600 | 600 | g1 |

| 500 | 601 | 601 | g2 |

| 503 | 603 | 603 | g4 |

+------+------+------+------+

mysql> select *,count(*) from u_g right join groups on u_g.gid=groups.gid where uid is not null group by name;统计

+------+------+------+------+----------+

| uid | gid | gid | name | count(*) |

+------+------+------+------+----------+

| 500 | 600 | 600 | g1 | 1 |

| 500 | 601 | 601 | g2 | 2 |

| 503 | 603 | 603 | g4 | 1 |

+------+------+------+------+----------+

3 rows in set (0.02 sec)

mysql> select *,count(*) from u_g right join groups on u_g.gid=groups.gid where uid is not null group by name having count(*)>=2;连用

+------+------+------+------+----------+

| uid | gid | gid | name | count(*) |

+------+------+------+------+----------+

| 500 | 601 | 601 | g2 | 2 |

+------+------+------+------+----------+

练习：统计每个地区的平均成绩

mysql> select *from tests;

+------+------+---------+------+

| name | sys | network | addr |

+------+------+---------+------+

| fly | 88 | 99 | bj |

| moon | 99 | 99 | bj |

| star | 78 | 100 | sy |

| sky | 100 | 100 | bj |

| join | 59 | 88 | sy |

+------+------+---------+------+

5 rows in set (0.00 sec)

mysql> select addr,avg(sys) as avg from tests group by addr;

+------+---------+

| addr | avg |

+------+---------+

| bj | 95.6667 |

| sy | 68.5000 |

+------+---------+

mysql> select addr,avg(sys+network) as avg from tests group by addr;

+------+----------+

| addr | avg |

+------+----------+

| bj | 195.0000 |

| sy | 162.5000 |

+------+----------+

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

二、数据的导入与导出－－－－>数据备份

mysql> load data infile '/tem/11.txt' into table u_g fields terminated by ',' lines terminated by '\n'; 默认分割符为TAB键

mysql> create table passwd(

-> name char(20),

-> pass char(5),

-> uid int,

-> gid int,

-> des char(20),

-> home char(20),

-> bash char(20));

Query OK, 0 rows affected (0.01 sec)

mysql> load data infile '/tmp/passwd' into table passwd fields terminated by ':' enclosed by '"';双引号除外

mysql> select name ,count(name) from passwd where bash!='/bin/bash';

+------+-------------+

| name | count(name) |

+------+-------------+

| bin | 36 |

+------+-------------+

mysql> select name ,count(name) from passwd where bash!='/bin/bash' into outfile '/tmp/pass' fields terminated by ':';数据的导出

练习一百万条记录的查询：

[root@stu65 tmp]# for i in {1..1000000}

> do

> echo "$i,test$i,[e-mail]" >> /tmp/name.txt

> done

mysql> create table name(

-> id int,

-> name char(20),

-> email char(30),

-> index idx_name(name));

Query OK, 0 rows affected (0.00 sec)

mysql> load data infile '/tmp/name.txt' into table name fields terminated by ',';

Query OK, 1000000 rows affected (10.56 sec)

Records: 1000000 Deleted: 0 Skipped: 0 Warnings: 0

mysql> explain select * from name where id='100'\G跟踪命令记录详细信息

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: name

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 1000000

Extra: Using where

1 row in set (0.00 sec)

mysql> explain select * from name where name='test100'\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: name

type: ref

possible_keys: idx_name

key: idx_name

key_len: 21

ref: const

rows: 1

Extra: Using where

1 row in set (0.00 sec)

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

三、记录复制

mysql> insert into users select uid,name from passwd;表数据导入另一个表中

mysql> alter table users add id int primary key auto_increment first;

mysql> insert into users(uid,name) select uid,name from passwd order by uid;指定列插入

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

四、表复制

mysql> create table db2 select *from users;表的复制－－－－>但是不复制表的索引

mysql> create table db3 select *from users where 0=1;复制表的框架（后面接不成立的条件即可）

mysql> create table db3 like users;复制表的框架也复制主键之类

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

五、表的备份和恢复－－－－>备份工具Mysqldump（逻辑备份）

[root@stu65 tmp]# mysqldump test -uroot -p123456 > /root/test.bak.sql备份

[root@stu65 tmp]# mysql -l -uroot -p123456 < /root/test.bak.sql恢复（需要手动创建和恢复数据库同名的数据库）参数：锁定数据库

[root@stu65 tmp]# mysqldump --all-databases -uroot -p123456 > /root/alldata.bak.mysql＆＆ gzip /root/alldata.bak备份所有数据库

[root@stu65 ~]# mysqldump -B test clusz > db.sql -uroot -p123456对两个库进行备份

[root@stu65 ~]# mysqldump test passwd > users.bak.sql -uroot -p123456备份一个表

[root@stu65 ~]# mysqldump test passwd users > two.bak.sql -uroot -p123456备份两个表

[root@stu65 ~]# cat two.bak.sql | mysql test -uroot -p123456表的恢复或者在mysql中执行语句source ./two.bak.sql;

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

六、用户管理

GRANT ALL PRIVILEGES ON *.* TO'myuser'@'%'IDENTIFIED BY 'mypassword' WITH GRANT OPTION; 授予全部权限

mysql> grant select,insert on test.* to user1@'192.168.1.%' identified by '123456';授权

mysql> grant select(uid,gid),update(id) on db1.uses_groups to user2@loaclhost;具体到表的授权

mysql> grant usage on test.* to user5;＝＝＝＝create user user6对表没有权限

mysql> drop user user3;删除用户

mysql> drop user user3@localhost;

mysql> revoke insert on test.* from user1@'192.168.1.%';撤销user1@'192.168.1.%'的insert'权限

mysql> show grants for user1@'192.168.1.%'\G查看权限

*************************** 1. row ***************************

Grants for user1@192.168.1.%: GRANT USAGE ON *.* TO 'user1'@'192.168.1.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'

*************************** 2. row ***************************

Grants for user1@192.168.1.%: GRANT SELECT ON `test`.* TO 'user1'@'192.168.1.%'

2 rows in set (0.00 sec)

mysql> insert into mysql.user (host,user,password,select_priv) values('1921.68.1.%','user2',password('123456'),'y');手动创建mysql用户

[root@stu65 conf]# /etc/init.d/mysql修改root密码

[root@stu65 htdocs]# mysql_safe --skip-grant-table;

[root@localhost Desktop]# mysql -u root

mysql> update user set password=PASSWORD(新密码) where user='root';修改密码

mysql> flush privileges;刷新授权表

MySQL导入导出命令

1.导出整个数据库

　　mysqldump -u 用户名 -p 数据库名 > 导出的文件名

　　mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql

2.导出一个表

　　mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名

　　mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql

3.导出一个数据库结构

　　mysqldump -u wcnc -p -d --add-drop-table smgp_apps_wcnc >d:wcnc_db.sql

　　-d 没有数据 --add-drop-table 在每个create语句之前增加一个drop table

4.导入数据库

　　常用source 命令

　　进入mysql数据库控制台，

　　如mysql -u root -p

　　mysql>use 数据库

　　然后使用source命令，后面参数为脚本文件(如这里用到的.sql)

　　mysql>source d:wcnc_db.sql （注：如果写成source d:\wcnc_db.sql，就会报语法错误）