mysql查询默认不区分大小写，如果需要区分大小写，使用binary
mysql>select * from teacher where binary name='niu';
mysql查询默认是升序的 asc
mysql>select distinct name from teacher order by id asc;
降序 desc
mysql>select distinct name from teacher order by id desc;
查看帮助 help
mysql>help select; #查看select的帮助信息
字符串类型char varchar
char为定长字符串，varchar为可变长字符串 ，假如char和varchar同为5个字符串长度，varchar能存储5个以上字符串长度的值，而char不能超过5个。
int整数中的unsigned 无符号整数修饰符表示字段只能使用正的数据，不能表示负整数。zerofill零填充，规定达不到要求长度的整数用零进行填充，防止mysql存储负值。
int(3)，int(4)，int(10)显示的长度不一样外，在硬盘中所占用的空间相同，可以使用的空间也一样。
例如：int(3) 010 int(4) 0010 int(10) 0000000010
浮点型数据类型 float(3,1) ，表示此字段有效位数为3位，小数点后面一位数字，小数点后超过一位，mysql会自动四舍五入。
查看当前的时间
mysql>select curtime();
查看当前的日期
mysql>select curdate();
查看当前的用户
mysql>select user();
查看当前的版本
mysql>select version();
查看当前的数据库
mysql>select database();
查看系统信息
show variables;
show global variables;
show processlist; #查看当前数据库的进程列表
show engines; #查看支持哪些存储引擎
show global variables like '%version%';
show variables like '%storage_engine%'; #默认的存储引擎，“%”表示通配符，代表任意
like模糊搜索，还可以用于where字句，例如；
select * from teacher where name like '%1%2%3%';
#备份数据库
mysql>create database book; #创建一个新的空库
mysql>use book; #切换到book新库
mysql>source /root/book_utf8.sql; #
mysql> select * from teacher into outfile '/usr/local/mysql/m.txt';
ERROR 1290 (HY000):The mysql server is running with the --secure-file-priv option so it can't 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.00 sec)
secure_file_priv的值为null，那么secure_file_priv这里都有什么设置呢
- secure_file_priv为null 表示不允许导入导出
- secure_file_priv指定文件夹时，表示mysql的导入导出只能发生在指定的文件夹
- secure_file_priv没有设置时，则表示没有任何限制
编辑/etc/my.conf文件，添加下面一行命令
secure_file_priv=
重启mysqld服务
mysql> show variables like '%secure%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| require_secure_transport | OFF |
| secure_auth | ON |
| secure_file_priv | |
+--------------------------+-------+
3 rows in set (0.00 sec)
mysql> select * from teacher into outfile '/usr/local/mysql/ms.txt'; #查询的结果导出到文本文件中
Query OK, 7 rows affected (0.00 sec)
[root@b ~]# ls /usr/local/mysql/ms.txt
/usr/local/mysql/ms.txt
and or 多条件逻辑匹配
mysql> select bName,publishing,price from books where price=30 or price=40 or price=50 or price=60;
+--------------------------------------+--------------------------+-------+
| bName | publishing | price |
+--------------------------------------+--------------------------+-------+
| Illustrator 10完全手册 | 科学出版社 | 50 |
| FreeHand 10基础教程 | 北京希望电子出版 | 50 |
| 网站设计全程教程 | 科学出版社 | 50 |
| ASP数据库系统开发实例导航 | 人民邮电出版社 | 60 |
| Delphi 5程序设计与控件参考 | 电子工业出版社 | 60 |
| ASP数据库系统开发实例导航 | 人民邮电出版社 | 60 |
+--------------------------------------+--------------------------+-------+
6 rows in set (0.00 sec)
<>=等运算符的使用
mysql> select bName,price from books where price>60;
+---------------------------------------------------------+-------+
| bName | price |
+---------------------------------------------------------+-------+
| 活学活用Delphi5 | 62 |
| Auto CAD 2002 中文版实用教程 | 63 |
| 3DS MAX 4横空出世 | 63 |
| 精通Javascript | 63 |
| 深入Flash 5教程 | 64 |
| Auto CAD R14 中文版实用教程 | 64 |
| Frontpage 2000＆ ASP 网页设计技巧与网站维护 | 71 |
| HTML设计实务 | 72 |
| ASP 3初级教程 | 104 |
| XML 完全探索 | 104 |
+---------------------------------------------------------+-------+
10 rows in set (0.00 sec)
mysql> select bName,price from books where price=60;
+--------------------------------------+-------+
| bName | price |
+--------------------------------------+-------+
| ASP数据库系统开发实例导航 | 60 |
| Delphi 5程序设计与控件参考 | 60 |
| ASP数据库系统开发实例导航 | 60 |
+--------------------------------------+-------+
3 rows in set (0.00 sec)
mysql> select bName,price from books where price<>60;
+---------------------------------------------------------+-------+
| bName | price |
+---------------------------------------------------------+-------+
| 网站制作直通车 | 34 |
| 黑客与网络安全 | 41 |
| 网络程序与设计－asp | 43 |
| pagemaker 7.0短期培训教程 | 43 |
| 黑客攻击防范秘笈 | 44 |
| Dreamweaver 4入门与提高 | 44 |
| 网页样式设计－CSS | 45 |
| Internet操作技术 | 45 |
| Dreamweaver 4网页制作 | 45 |
| 3D MAX 3.0 创作效果百例 | 45 |
| Auto CAD职业技能培训教程 | 47 |
+---------------------------------------------------------+-------+
11 rows in set (0.01 sec)
mysql> select bName,price from books where price!=60;
+---------------------------------------------------------+-------+
| bName | price |
+---------------------------------------------------------+-------+
| 网站制作直通车 | 34 |
| 黑客与网络安全 | 41 |
| 网络程序与设计－asp | 43 |
| pagemaker 7.0短期培训教程 | 43 |
| 黑客攻击防范秘笈 | 44 |
| Dreamweaver 4入门与提高 | 44 |
| 网页样式设计－CSS | 45 |
| Internet操作技术 | 45 |
| Dreamweaver 4网页制作 | 45 |
| 3D MAX 3.0 创作效果百例 | 45 |
| Auto CAD职业技能培训教程 | 47 |
+---------------------------------------------------------+-------+
11 rows in set (0.01 sec)
in和 not in 表示包含和不包含
mysql> select bName,price from books where price in (50,60,70);
+--------------------------------------+-------+
| bName | price |
+--------------------------------------+-------+
| Illustrator 10完全手册 | 50 |
| FreeHand 10基础教程 | 50 |
| 网站设计全程教程 | 50 |
| ASP数据库系统开发实例导航 | 60 |
| Delphi 5程序设计与控件参考 | 60 |
| ASP数据库系统开发实例导航 | 60 |
+--------------------------------------+-------+
6 rows in set (0.00 sec)
mysql> select bName,price from books where price not in (50,60,70);
+---------------------------------------------------------+-------+
| bName | price |
+---------------------------------------------------------+-------+
| 网站制作直通车 | 34 |
| 黑客与网络安全 | 41 |
| 网络程序与设计－asp | 43 |
| pagemaker 7.0短期培训教程 | 43 |
| 黑客攻击防范秘笈 | 44 |
| Dreamweaver 4入门与提高 | 44 |
| 网页样式设计－CSS | 45 |
| Internet操作技术 | 45 |
| Dreamweaver 4网页制作 | 45 |
+--------------------------------------+-------+
9 rows in set (0.00 sec)
order by 表示升序或是降序 默认为asc升序，desc为降序
mysql> select bName,price from books where price not in (50,60,70) order by price asc;
+---------------------------------------------------------+-------+
| bName | price |
+---------------------------------------------------------+-------+
| 网站制作直通车 | 34 |
| 黑客与网络安全 | 41 |
| 网络程序与设计－asp | 43 |
| 黑客攻击防范秘笈 | 44 |
| 网页样式设计－CSS | 45 |
+--------------------------------------+-------+
5 rows in set (0.00 sec)
mysql> select bName,price from books where price not in (50,60,70) order by price desc,bName asc;
like模糊查询，%代表任意匹配
mysql> select bName from books where bName like '%程序%';
+-------------------------------------+
| bName |
+-------------------------------------+
| 网络程序与设计－asp |
| Delphi 5程序设计与控件参考 |
+-------------------------------------+
2 rows in set (0.00 sec)
between and 等价与> < 在……和……之间
mysql> select bName,price from books where price between 30 and 40;
+-----------------------+-------+
| bName | price |
+-----------------------+-------+
| 网站制作直通车 | 34 |
+-----------------------+-------+
1 row in set (0.00 sec)
mysql子查询（在select的where条件中又出现select，又称嵌套查询）
mysql> select bName,bTypeId from books where bTypeId=7;
+----------------------+---------+
| bName | bTypeId |
+----------------------+---------+
| Internet操作技术 | 7 |
+----------------------+---------+
1 row in set (0.00 sec)
查找类型名为网络技术的图书
mysql> select bName,bTypeId from books where bTypeId=(select bTypeId from category where bTypeName='网络技术');
+----------------------+---------+
| bName | bTypeId |
+----------------------+---------+
| Internet操作技术 | 7 |
+----------------------+---------+
1 row 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.00 sec)
limit限制输出，limit m,n m代表起始值，偏移量为1，所以输出起始值为m+1；n代表输出的行数。
mysql> select * from category limit 1,3;
+---------+-------------+
| bTypeId | bTypeName |
+---------+-------------+
| 2 | 网站 |
| 3 | 3D动画 |
| 4 | linux学习 |
+---------+-------------+
3 rows in set (0.00 sec)
mysql> select * from category limit 1,4;
+---------+-------------+
| bTypeId | bTypeName |
+---------+-------------+
| 2 | 网站 |
| 3 | 3D动画 |
| 4 | linux学习 |
| 6 | 黑客 |
+---------+-------------+
3 rows in set (0.00 sec)
