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,test@126.com" >> /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，就会报语法错误）