mysql表复制
mysql表结构复制+mysql表数据复制
mysql> create table t2 like t1;
mysql> insert into t2 select * from t1;
mysql> select * from t1 into outfile '/tmp/a.txt';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
mysql> show variables like '%secure%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| require_secure_transport | OFF |
| secure_auth | ON |
| secure_file_priv | NULL |
+--------------------------+-------+
3 rows in set (0.01 sec)
当secure_file_priv的值为null ，表示限制mysqld 不允许导入|导出
当secure_file_priv的值为/tmp/ ，表示限制mysqld 的导入|导出只能发生在/tmp/目录下
当secure_file_priv的值没有具体值时，表示不对mysqld 的导入|导出做限制
查看数据库当前该参数的值 root@localhost:mysql.sock 00:14:52 [(none)]>show global variables like '%secure%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| secure_auth | ON |
| secure_file_priv | NULL |
+------------------+-------+
2 rows in set (0.00 sec)
清楚地看到secure_file_priv 的值是NULL，说明此时限制导入导出的
所以应该改变该参数
可是查看了mysql.cnf中居然没有对这个参数进行设定，就说明这个参数默认便是null
所以再mysql.cnf中的[mysqld]加入secure_file_priv =
再重启mysql服务
然后再查一下此时参数的值
root@localhost:mysql.sock 00:28:30 [(none)]>show global variables like '%secure%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| secure_auth | ON |
| secure_file_priv | |
+------------------+-------+
2 rows in set (0.00 sec)
mysql> select * from t1 where id in(1,2,3) order by id desc limit 0,2;
+------+-------+
| id | name |
+------+-------+
| 3 | zhang |
| 2 | liu |
+------+-------+
2 rows in set (0.00 sec)
mysql> select * from t1 where id=1 or id=2 or id=3 order by id desc limit 0,2;
+------+-------+
| id | name |
+------+-------+
| 3 | zhang |
| 2 | liu |
+------+-------+
2 rows in set (0.00 sec)
mysql> select * from t1 where id between 1 and 3 limit 0,2;
+------+------+
| id | name |
+------+------+
| 1 | feng |
| 2 | liu |
+------+------+
2 rows in set (0.00 sec)
mysql> select * from t1 where id>=1 and id <=3 limit 0,2;
+------+------+
| id | name |
+------+------+
| 1 | feng |
| 2 | liu |
+------+------+
2 rows in set (0.00 sec)
mysql> select bName,bTypeId from books where bTypeId=(select bTypeId from category where bTypeName='黑客');
+--------------------------+---------+
| bName | bTypeId |
+--------------------------+---------+
| 黑客与网络安全 | 6 |
| 黑客攻击防范秘笈 | 6 |
+--------------------------+---------+
2 rows in set (0.01 sec)
mysql> select bName,price from books where price<(select price from books where publishing="电子工业出版社" order by price asc limit 0,1);
+--------------------------------------------------------+-------+
| bName | price |
+--------------------------------------------------------+-------+
| 网站制作直通车 | 34 |
| 黑客与网络安全 | 41 |
内链接查询
mysql> Select a.bname,a.price,b.btypename from books a inner join category b on a.btypeid=b.btypeid;
+---------------------------------------------------------+-------+---------------+
| bname | price | btypename |
+---------------------------------------------------------+-------+---------------+
| 网站制作直通车 | 34 | 网站 |
| 黑客与网络安全 | 41 | 黑客 |
实际使用中inner可省略掉，跟WHERE 子句结果一样
mysql> Select a.bname,a.price,b.btypename from books a, category b where a.btypeid=b.btypeid;
+---------------------------------------------------------+-------+---------------+
| bname | price | btypename |
+---------------------------------------------------------+-------+---------------+
| 网站制作直通车 | 34 | 网站 |
| 黑客与网络安全 | 41 | 黑客 |
| 网络程序与设计－asp | 43 | 网站 |
外连接 (分为左外连接；右外连接)
1.左连接： select 字段 from a表 left join b表 on 连接条件
a表是主表，都显示。
b表从表
统计价格小于50的书籍数量
mysql> select count(*) from books where price < 50;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec)
Count（）中还可以增加你需要的内容，比如增加distinct来配合使用
mysql> select count(distinct price) from books where price < 50;
+-----------------------+
| count(distinct price) |
+-----------------------+
| 4 |
+-----------------------+
1 row in set (0.01 sec)
求书籍Id小于3的所有书籍的平均价格
mysql> select avg(price) from books where bId < 3;
+------------+
| avg(price) |
+------------+
| 61.3864 |
+------------+
1 row in set (0.00 sec)
mysql> select max(price) from books;
+------------+
| max(price) |
+------------+
| 104 |
+------------+
1 row in set (0.00 sec)
显示所有图书单价的总合
mysql> select sum(price) from books;
+------------+
| sum(price) |
+------------+
| 2701 |
+------------+
1 row in set (0.00 sec)
求所有图书中价格便宜的书籍
mysql> select bName,price from books where price=(select min(price) from books);
+-----------------------+-------+
| bName | price |
+-----------------------+-------+
| 网站制作直通车 | 39 |
+-----------------------+-------+
1 row in set (0.00 sec)
算数运算：
+ - * /
mysql> update books set price=price+5 where price < 50;
Query OK, 15 rows affected (0.00 sec)
Rows matched: 15 Changed: 15 Warnings: 0
mysql> update books set price=price-5 where price=(select price where price < 50);
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6 Changed: 6 Warnings: 0
concat(str1,str2,str3.....) 拼接。 把多个字段拼成一个字段输出
mysql> select * from books limit 2;
+-----+-----------------------+---------+--------------------------+-------+------------+-----------+------------+
| bId | bName | bTypeId | publishing | price | pubDate | author | ISBN |
+-----+-----------------------+---------+--------------------------+-------+------------+-----------+------------+
| 1 | 网站制作直通车 | 2 | 电脑爱好者杂志社 | 34 | 2004-10-01 | 苗壮 | 7505380796 |
| 2 | 黑客与网络安全 | 6 | 航空工业出版社 | 41 | 2002-07-01 | 白立超 | 7121010925 |
+-----+-----------------------+---------+--------------------------+-------+------------+-----------+------------+
2 rows in set (0.00 sec)
mysql> select concat(bName,publishing) from books limit 2;
+-----------------------------------------------+
| concat(bName,publishing) |
+-----------------------------------------------+
| 网站制作直通车电脑爱好者杂志社 |
| 黑客与网络安全航空工业出版社 |
+-----------------------------------------------+
2 rows in set (0.00 sec)
mysql> select concat(bName," ",publishing) from books limit 2;
+-------------------------------------------------------------------+
| concat(bName," ",publishing) |
+-------------------------------------------------------------------+
| 网站制作直通车 电脑爱好者杂志社 |
| 黑客与网络安全 航空工业出版社 |
+-------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select concat(bName," ",publishing) as a from books limit 2;
+-------------------------------------------------------------------+
| a |
+-------------------------------------------------------------------+
| 网站制作直通车 电脑爱好者杂志社 |
| 黑客与网络安全 航空工业出版社 |
+-------------------------------------------------------------------+
2 rows in set (0.05 sec)
mysql> show variables like '%character%';
+--------------------------+-----------------------------------------+
| Variable_name | Value |
+--------------------------+-----------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-5.7.25/share/charsets/ |
+--------------------------+-----------------------------------------+
8 rows in set (0.00 sec)
mysql> set character_set_database=utf8;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show variables like '%character%';
+--------------------------+-----------------------------------------+
| Variable_name | Value |
+--------------------------+-----------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-5.7.25/share/charsets/ |
+--------------------------+-----------------------------------------+
8 rows in set (0.00 sec)
mysql> set character_set_server=utf8;
Query OK, 0 rows affected (0.00 sec)
set临时修改字符集，永久修改，编辑vim /etc/my.cnf,在mysqld中添加character_set_server=utf8，重启mysqld服务即可。
导出指定表的表结构
[root@localhost test]# mysqldump -uroot -p -d HA（库名） books（表名） > books.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
导出库的所有表结构
[root@localhost test]# mysqldump -uroot -p -d HA（库名） >HA.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
导出库的所有表数据
[root@localhost test]# mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=utf8 HA >HA_data.sql
Enter password:
导出指定表的表数据
[root@localhost test]# mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=utf8 HA books > books_data.sql
Enter password:
--quick 用于转储大的表，强制mysqldump从服务器一次一行的检索数据而不是检索所有行。
--no-create-info 不要创建create table语句
--extended-insert 使用包括几个values列表的多行insert语法，导入数据速度快
--default-character-set 按照原有字符集导出数据，这样不会保存乱码
建库时，强制指定默认字符集
mysql> create database book default charset utf8;
Query OK, 1 row affected (0.03 sec)
mysql> show create database book;
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| book | CREATE DATABASE `book` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)
