mysql基础操作

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)