导出库:dump(扔弃)
C:\Users\yemengdi>mysqldump -uroot -p php1802 > c:\sql\mysql.sql
Enter password:
导入库:host(主机地址)
C:\Users\yemengdi>mysql -h localhost -uroot -p php1802 <c:\sql\mysql.sql
Enter password:
导入数据:https://www.cnblogs.com/wangkongming/p/4037421.html
mysql>use dd;
mysql>set names utf8;
mysql>source c:\sql\mysql.sql
https://www.cnblogs.com/gaogaoxingxing/p/6066533.html
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
.......
导出单张表:
C:\Users\yemengdi>mysqldump -uroot -p dd hahaha > c:\sql\ha.sql
Enter password:
导出所有表结构:
-d -delete:在导出之前删除表中所有内容
C:\Users\yemengdi>mysqldump -uroot -p -d --add-drop-table dd>c:\sql\ddjiegou.sql
Enter password:
导出单张表结构:
C:\Users\yemengdi>mysqldump -uroot -p -d dd newmeng>c:\sql\danzhangjiegou.sql
Enter password:
C:\Users\yemengdi>
关于用户:在更改权限后记得刷新flush privileges;
grant revoke
1.创建用户:
mysql> create user 'md'@'localhost' identified by 'ymd';
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
查看用户:
mysql> select host,user from mysql.user;
+-----------+-----------+
| host | user |
+-----------+-----------+
| 127.0.0.1 | dd |
| 127.0.0.1 | f |
| 127.0.0.1 | md |
| 127.0.0.1 | ps |
| localhost | dd |
| localhost | meng |
| localhost | mysql.sys |
| localhost | root |
+-----------+-----------+
8 rows in set (0.00 sec)
2.删除用户:
mysql> drop user 'md'@'localhost';
Query OK, 0 rows affected (0.00 sec)
3.赋值权限:
mysql> grant select on *.* to 'md'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| apple_bbs |
| dd |
| meng |
| mysql |
| performance_schema |
| php1802 |
| sys |
| yemengdi |
+--------------------+
9 rows in set (0.00 sec)
查看权限:
mysql> grant select on *.* to 'meng'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for meng@'localhost';
+-------------------------------------------+
| Grants for meng@localhost |
+-------------------------------------------+
| GRANT SELECT ON *.* TO 'meng'@'localhost' |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> grant delete on *.* to 'meng'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for meng@'localhost';
+---------------------------------------------------+
| Grants for meng@localhost |
+---------------------------------------------------+
| GRANT SELECT, DELETE ON *.* TO 'meng'@'localhost' |
+---------------------------------------------------+
1 row in set (0.00 sec)
4.剥夺权限:
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> revoke select on *.* from 'md'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for meng@'localhost';
+-------------------------------------------+
| Grants for meng@localhost |
+-------------------------------------------+
| GRANT DELETE ON *.* TO 'meng'@'localhost' |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
隐式内连接:
mysql> select user.id,goods.price from user,goods where user.gid=goods.gid;
+----+---------+
| id | price |
+----+---------+
| 1 | 7 |
| 2 | 3 |
| 3 | 3 |
| 4 | 6 |
| 5 | 4 |
| 6 | 12 |
| 7 | 1000 |
| 8 | 1000000 |
| 9 | 6000000 |
| 10 | 15 |
+----+---------+
10 rows in set (0.00 sec)
mysql> select user.age,goods.gname from user,goods where user.gid=goods.gid;
+-----+--------+
| age | gname |
+-----+--------+
| 23 | 火龙果 |
| 37 | 苹果 |
| 32 | 香蕉 |
| 36 | 薯条 |
| 35 | 可乐 |
| 31 | 感冒灵 |
| 30 | 人参 |
| 21 | 宝马 |
| 32 | 法拉利 |
| 34 | 麻辣烫 |
+-----+--------+
10 rows in set (0.00 sec)
显示内连接:
mysql> select user.name as name ,goods.gname as gname from user inner join goods where user.gid=goods.gid;
+----------+--------+
| name | gname |
+----------+--------+
| 胡歌 | 火龙果 |
| 张杰 | 苹果 |
| 杨幂 | 香蕉 |
| 靳东 | 薯条 |
| 李易峰 | 可乐 |
| 迪丽热巴 | 感冒灵 |
| 陈伟霆 | 人参 |
| 叶梦迪 | 宝马 |
| 张三 | 法拉利 |
| 杨紫 | 麻辣烫 |
+----------+--------+
10 rows in set (0.00 sec)
mysql> select user.id,goods.gname from user inner join goods where user.gid=goods.gid;
+----+--------+
| id | gname |
+----+--------+
| 1 | 火龙果 |
| 2 | 苹果 |
| 3 | 香蕉 |
| 4 | 薯条 |
| 5 | 可乐 |
| 6 | 感冒灵 |
| 7 | 人参 |
| 8 | 宝马 |
| 9 | 法拉利 |
| 10 | 麻辣烫 |
+----+--------+
10 rows in set (0.00 sec)
左连接查询:
mysql> select user.name,goods.gname from goods left join user on user.gid=goods.gid;
+----------+--------+
| name | gname |
+----------+--------+
| 胡歌 | 火龙果 |
| 张杰 | 苹果 |
| 杨幂 | 香蕉 |
| 靳东 | 薯条 |
| 李易峰 | 可乐 |
| 迪丽热巴 | 感冒灵 |
| 陈伟霆 | 人参 |
| 叶梦迪 | 宝马 |
| 张三 | 法拉利 |
| 杨紫 | 麻辣烫 |
| NULL | 军刀 |
+----------+--------+
11 rows in set (0.01 sec)
mysql> select user.name,goods.gname from user left join goods on user.gid=goods.gid;
+----------+--------+
| name | gname |
+----------+--------+
| 胡歌 | 火龙果 |
| 张杰 | 苹果 |
| 杨幂 | 香蕉 |
| 靳东 | 薯条 |
| 李易峰 | 可乐 |
| 迪丽热巴 | 感冒灵 |
| 陈伟霆 | 人参 |
| 叶梦迪 | 宝马 |
| 张三 | 法拉利 |
| 杨紫 | 麻辣烫 |
| 秦始皇 | NULL |
+----------+--------+
11 rows in set (0.00 sec)
右连接查询:
mysql> select user.id,user.name,goods.gname,goods.price from user right join goods on user.gid=goods.gid;
+------+----------+--------+---------+
| id | name | gname | price |
+------+----------+--------+---------+
| 1 | 胡歌 | 火龙果 | 7 |
| 2 | 张杰 | 苹果 | 3 |
| 3 | 杨幂 | 香蕉 | 3 |
| 4 | 靳东 | 薯条 | 6 |
| 5 | 李易峰 | 可乐 | 4 |
| 6 | 迪丽热巴 | 感冒灵 | 12 |
| 7 | 陈伟霆 | 人参 | 1000 |
| 8 | 叶梦迪 | 宝马 | 1000000 |
| 9 | 张三 | 法拉利 | 6000000 |
| 10 | 杨紫 | 麻辣烫 | 15 |
| NULL | NULL | 军刀 | 1000 |
+------+----------+--------+---------+
11 rows in set (0.00 sec)
mysql> select user.id,goods.price,goods.gname from goods right join user on user.gid=goods.gid;
+----+---------+--------+
| id | price | gname |
+----+---------+--------+
| 1 | 7 | 火龙果 |
| 2 | 3 | 苹果 |
| 3 | 3 | 香蕉 |
| 4 | 6 | 薯条 |
| 5 | 4 | 可乐 |
| 6 | 12 | 感冒灵 |
| 7 | 1000 | 人参 |
| 8 | 1000000 | 宝马 |
| 9 | 6000000 | 法拉利 |
| 10 | 15 | 麻辣烫 |
| 11 | NULL | NULL |
+----+---------+--------+
11 rows in set (0.00 sec)
嵌套查询:
mysql> select name from user where gid in(select gid from goods);
+----------+
| name |
+----------+
| 胡歌 |
| 张杰 |
| 杨幂 |
| 靳东 |
| 李易峰 |
| 迪丽热巴 |
| 陈伟霆 |
| 叶梦迪 |
| 张三 |
| 杨紫 |
+----------+
10 rows in set (0.01 sec)
mysql>
mysql> select name from user where gid in(1,2,3,4);
+--------+
| name |
+--------+
| 张杰 |
| 杨幂 |
| 靳东 |
| 李易峰 |
+--------+
4 rows in set (0.01 sec)
union 联合查询(了解)
mysql> select *from user left join goods on user.gid=goods.gid
-> union
-> select *from user right join goods on user.gid=goods.gid;
+------+----------+-------+----------+------+------+------+--------+---------+------+
| id | name | money | province | age | gid | gid | gname | price | type |
+------+----------+-------+----------+------+------+------+--------+---------+------+
| 1 | 胡歌 | 666 | 上海 | 23 | 5 | 5 | 火龙果 | 7 | 水果 |
| 2 | 张杰 | 435 | 深圳 | 37 | 4 | 4 | 苹果 | 3 | 水果 |
| 3 | 杨幂 | 242 | 杭州 | 32 | 3 | 3 | 香蕉 | 3 | 水果 |
| 4 | 靳东 | 234 | 北京 | 36 | 2 | 2 | 薯条 | 6 | 零食 |
| 5 | 李易峰 | 254 | 杭州 | 35 | 1 | 1 | 可乐 | 4 | 饮料 |
| 6 | 迪丽热巴 | 322 | 香港 | 31 | 6 | 6 | 感冒灵 | 12 | 药 |
| 7 | 陈伟霆 | 325 | 北京 | 30 | 7 | 7 | 人参 | 1000 | 药 |
| 8 | 叶梦迪 | 888 | 北京 | 21 | 8 | 8 | 宝马 | 1000000 | 车 |
| 9 | 张三 | 456 | 上海 | 32 | 9 | 9 | 法拉利 | 6000000 | 车 |
| 10 | 杨紫 | 402 | 上海 | 34 | 10 | 10 | 麻辣烫 | 15 | 饭 |
| 11 | 秦始皇 | 453 | 北京 | 45 | 11 | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | 12 | 军刀 | 1000 | 武器 |
+------+----------+-------+----------+------+------+------+--------+---------+------+
12 rows in set (0.01 sec)
两张表同时修改:
mysql> update user as u,goods as g set u.name='张良',gname='兰博基尼' where u.gid=g.gid and u.id=9;
Query OK, 1 row affected (0.00 sec)
Rows matched: 2 Changed: 1 Warnings: 0
mysql> select *from user left join goods on user.gid=goods.gid
-> union
-> select *from user right join goods on user.gid=goods.gid;
+------+----------+-------+----------+------+------+------+----------+---------+------+
| id | name | money | province | age | gid | gid | gname | price | type |
+------+----------+-------+----------+------+------+------+----------+---------+------+
| 1 | 胡歌 | 666 | 上海 | 23 | 5 | 5 | 兰博基尼 | 7 | 水果 |
| 2 | 张杰 | 435 | 深圳 | 37 | 4 | 4 | 兰博基尼 | 3 | 水果 |
| 3 | 杨幂 | 242 | 杭州 | 32 | 3 | 3 | 兰博基尼 | 3 | 水果 |
| 4 | 靳东 | 234 | 北京 | 36 | 2 | 2 | 兰博基尼 | 6 | 零食 |
| 5 | 李易峰 | 254 | 杭州 | 35 | 1 | 1 | 兰博基尼 | 4 | 饮料 |
| 6 | 迪丽热巴 | 322 | 香港 | 31 | 6 | 6 | 兰博基尼 | 12 | 药 |
| 7 | 陈伟霆 | 325 | 北京 | 30 | 7 | 7 | 兰博基尼 | 1000 | 药 |
| 8 | 叶梦迪 | 888 | 北京 | 21 | 8 | 8 | 兰博基尼 | 1000000 | 车 |
| 9 | 张良 | 456 | 上海 | 32 | 9 | 9 | 兰博基尼 | 6000000 | 车 |
| 10 | 杨紫 | 402 | 上海 | 34 | 10 | 10 | 兰博基尼 | 15 | 饭 |
| 11 | 秦始皇 | 453 | 北京 | 45 | 11 | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | 12 | 军刀 | 1000 | 武器 |
+------+----------+-------+----------+------+------+------+----------+---------+------+
12 rows in set (0.00 sec)
事务:http://www.runoob.com/mysql/mysql-transaction.html
双向成功才成功
单项成功失败 回滚
注意:引擎必须是innodb
第一步:
把自动提交改成手动提交 mysql> set autocommit = 0;
第二步:
开启事务
第三步:
写sql语句
mysql> update shop_goods set price=2000 where gid=3;
第四步:
是提交还是回滚
rollback 回滚
commit 提交
mysql> select *from user;
+----+----------+------+
| id | name | pwd |
+----+----------+------+
| 1 | 胡歌 | 999 |
| 2 | 张杰 | 435 |
| 3 | 杨幂 | 242 |
| 4 | 靳东 | 234 |
| 5 | 李易峰 | 254 |
| 6 | 迪丽热巴 | 322 |
| 7 | 陈伟霆 | 325 |
| 8 | 叶梦迪 | 888 |
| 9 | 张良 | 456 |
| 10 | 杨紫 | 402 |
| 11 | 秦始皇 | 453 |
+----+----------+------+
11 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update user set pwd=666 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select *from user;
+----+----------+------+
| id | name | pwd |
+----+----------+------+
| 1 | 胡歌 | 999 |
| 2 | 张杰 | 435 |
| 3 | 杨幂 | 242 |
| 4 | 靳东 | 234 |
| 5 | 李易峰 | 254 |
| 6 | 迪丽热巴 | 322 |
| 7 | 陈伟霆 | 325 |
| 8 | 叶梦迪 | 888 |
| 9 | 张良 | 456 |
| 10 | 杨紫 | 402 |
| 11 | 秦始皇 | 453 |
+----+----------+------+
11 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update user set pwd=666 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> select *from user;
+----+----------+------+
| id | name | pwd |
+----+----------+------+
| 1 | 胡歌 | 666 |
| 2 | 张杰 | 435 |
| 3 | 杨幂 | 242 |
| 4 | 靳东 | 234 |
| 5 | 李易峰 | 254 |
| 6 | 迪丽热巴 | 322 |
| 7 | 陈伟霆 | 325 |
| 8 | 叶梦迪 | 888 |
| 9 | 张良 | 456 |
| 10 | 杨紫 | 402 |
| 11 | 秦始皇 | 453 |
+----+----------+------+
11 rows in set (0.00 sec)
多字段排序:先按照第一个字段进行排序,如果字段第一个字段的内容相同,在根据第二个字段进行排序
mysql> select *from user order by age desc,gid asc;
+----+----------+-------+----------+-----+-----+
| id | name | money | province | age | gid |
+----+----------+-------+----------+-----+-----+
| 11 | 秦始皇 | 453 | 北京 | 45 | 11 |
| 2 | 张杰 | 435 | 深圳 | 37 | 4 |
| 4 | 靳东 | 234 | 北京 | 36 | 2 |
| 5 | 李易峰 | 254 | 杭州 | 35 | 1 |
| 10 | 杨紫 | 402 | 上海 | 34 | 10 |
| 3 | 杨幂 | 242 | 杭州 | 32 | 3 |
| 9 | 张良 | 456 | 上海 | 32 | 9 |
| 6 | 迪丽热巴 | 322 | 香港 | 31 | 6 |
| 7 | 陈伟霆 | 325 | 北京 | 30 | 7 |
| 1 | 胡歌 | 999 | 上海 | 23 | 5 |
| 8 | 叶梦迪 | 888 | 北京 | 21 | 8 |
+----+----------+-------+----------+-----+-----+
11 rows in set (0.00 sec)
mysql> select *from user order by age desc,gid desc;
+----+----------+-------+----------+-----+-----+
| id | name | money | province | age | gid |
+----+----------+-------+----------+-----+-----+
| 11 | 秦始皇 | 453 | 北京 | 45 | 11 |
| 2 | 张杰 | 435 | 深圳 | 37 | 4 |
| 4 | 靳东 | 234 | 北京 | 36 | 2 |
| 5 | 李易峰 | 254 | 杭州 | 35 | 1 |
| 10 | 杨紫 | 402 | 上海 | 34 | 10 |
| 9 | 张良 | 456 | 上海 | 32 | 9 |
| 3 | 杨幂 | 242 | 杭州 | 32 | 3 |
| 6 | 迪丽热巴 | 322 | 香港 | 31 | 6 |
| 7 | 陈伟霆 | 325 | 北京 | 30 | 7 |
| 1 | 胡歌 | 999 | 上海 | 23 | 5 |
| 8 | 叶梦迪 | 888 | 北京 | 21 | 8 |
+----+----------+-------+----------+-----+-----+
11 rows in set (0.00 sec)
sum 求和
count 求总条数
max 求最大值
min 求最小值
avg 求平均值
mysql> select count(user.gid) as count, goods.gname,avg(goods.price) from
-> user inner join goods on user.gid=goods.gid group by
-> goods.gname order by count desc limit 0,1;
+-------+-------+------------------+
| count | gname | avg(goods.price) |
+-------+-------+------------------+
| 2 | 苹果 | 3.0000 |
+-------+-------+------------------+
mysql> select count(user.gid) as gid,goods.gname as name from
-> user right join goods on user.gid=goods.gid
-> group by goods.gname order by gid desc limit 0,1;
+-----+------+
| gid | name |
+-----+------+
| 2 | 苹果 |
+-----+------+
1 row in set (0.00 sec)
mysql> select count(user.gid) as ugid ,goods.gname as name from
-> user left join goods on user.gid=goods.gid
-> group by goods.gname order by ugid desc limit 0,1;
+------+------+
| ugid | name |
+------+------+
| 2 | 苹果 |
+------+------+
1 row in set (0.00 sec)