数据类型

MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串（字符）类型

数值类型

日期和时间类型

字符串类型

整型

tinyint,占 1 字节，有符号：-128~127，无符号位：0~255
smallint,占 2 字节，有符号：-32768~32767，无符号位：0~65535
mediumint,占 3 字节，有符号：-8388608~8388607，无符号位：0~16777215
int,占 4 字节，有符号：-2147483648~2147483647，无符号位：0~4284967295
bigint,占 8 字节
bool 等价于 tinyint(1) 布尔型

浮点型

float([m[,d]]) 占 4 字节，1.17E-38~3.4E+38
double([m[,d]]) 占 8 字节
decimal([m[,d]]) 以字符串形式表示的浮点数

字符型

char([m])：固定长度的字符,占用 m 字节
varchar[(m)]：可变长度的字符,占用 m+1 字节，大于 255 个字符：占用 m+2
tinytext,255 个字符(2 的 8 次方)
text,65535 个字符(2 的 16 次方)
mediumtext,16777215 字符(2 的 24 次方)
longtext,(2 的 32 次方)
enum(value,value,...)占 1/2 个字节 最多可以有 65535 个成员
set(value,value,...)占 1/2/3/4/8 个字节，最多可以有 64 个成员
http://www.runoob.com/mysql/mysql-select-query.html

常用 select 命令

-- 打印当前的日期和时间
select now();

-- 打印当前的日期
select curdate();

-- 打印当前的时间
select curtime();

-- 打印当前数据库
select database();

-- 打印 MySQL 版本
select version();

-- 打印当前用户
select user();

--查看系统信息
show variables\G     
显示变量

show global variables\G
显示全局变量

show global variables like '%version%';
显示全局变量中包含version的

show variables like '%storage_engine%';

 

导出、导入数据库

导入数据库

导入数据库前必须创建一个空数据库
mysql -e 'create database book' -u root -p123456
创建一个book数据库
mysql -uroot -p123456 book < /opt/book.sql
导入创建表sql

或者登陆 MySQL
create database book;
use book;
source /tmp/book.sql
show tables;

导出数据库

导出数据库：mysqldump -u 用户名 -p 数据库名 > 导出的文件名
mysqldump -uroot -p123456 book > /tmp/book2.sql
mysqldump - u root -p123456 -B 库名 >文件名.sql
mysqldump -uroot -p123456 -A > /tmp/all.sql
-B : 导出整个库包含建库语句
-A：导出全部数据库， 包括mysql系统库

如何把一个 select 的结果导出到文本，其实就是备份数据库

use book;
select * from books into outfile '/tmp/123.txt';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
出现这个报错之后需要修改 /etc/my.cnf 加上 secure-file-priv="/"

systemctl restart mysqld
mysql -uroot -p123456
use book;
select * from books into outfile '/tmp/123.txt';

SQL 查询语句进阶

查看表的内容

select * from category;

查看字段类型

desc books;

逻辑运算符

and 且
or 或
not 非
选择出书籍价格为（30,40,50,60）的记录,只显示书籍名称，出版社，价格
mysql> select bName,publishing,price from books where price=30 or price=40 or price=50 or price=60;

算术运算符

=  等于
<>  不等于 !=
>  大于
<  小于
>=  大于等于
<=  小于等于

in  运算符

Not in 与 in 相反
当 IN 前面加上 NOT 运算符时，表示与 IN 相反的意思，即不在这些列表项内选择。

select bName,price from books where price>60; 找出价格大于 60 的记录
select bName,price from books where price=60; 
select bName,price from books where price<>60; 找出价格不等于 60 的
select bName,price from books where price in (50,60,70);  找出价格是 50，60，70 的记录。
select bName,price from books where price not in (50,60,70);  找出价格不是60，50，70 的记录

排序

升序：order by “排序的字段” asc 默认
降序：oredr by “排序的字段” desc

select bName,price from books where price in (50,60,70) order by price asc;
select bName,price from books where price in (50,60,70) order by price desc;

select bName,price from books where price in (50,60,70) order by price desc,bName desc; #多个字段排序。

范围运算

between ....and...

Between and 可以使用大于小于的方式来代替,并且使用大于小于意义表述更明确
查找价格不在 30 到 60 之间的书名和价格
select bName,price from books where price not between 30 and 60 order by price desc;  

>30 and <60   不包含30和60
select bName,price from books where price >30 and price <60 order by price desc;
>=30 and <=60  包含了30和60 相当于 between 30 and 60
select bName,price from books where price >=30 and price <=60 order by price desc;

模糊匹配查询

like ' 通配符'
查找书名中包括"程序"字样记录
select bName from books where bName like '%程序%';
百分比（%）表示通配符允许匹配任何字符串的零个或多个字符。
下划线（_）表示通配符允许匹配任何单个字符。

查找书名中不包括“程序”字样记录
select bName from books where bName not like '%程序%';

MySQL 子查询

在 select 的 where 条件中又出现了 select，查询中嵌套着查询

选择 类型名为“网络技术”的图书：
select bName,bTypeId from books where bTypeId=(select bTypeId from category where bTypeName='网络技术');

 select bName,bTypeId from books where bTypeId=(select bTypeId from category where bTypeName='平面');

Limit 限定显示的条目

SELECT * FROM table LIMIT [offset,] rows

偏移量 行数

初始记录行的偏移量是 0（而不是 1）

比如 select * from table limit m,n 语句
表示其中 m 是指记录开始的 index，从 0 开始，表示第一条记录，取 n 条

只有一个参数的时候，默认为n，取n条 

查看所有书籍中价格中最低的三条记录
select bname,price from books order by price asc limit 0,3;

找出价格比电子工业出版社出版的书中最便宜还要便宜的书
先找出电子工业出版社出版中最便宜的书
select bname,price from books where publishing='电子工业出版社' order by price asc limit 1;

然后把嵌套的查询结果当作条件
select bname,price from books where price <(select price from books where publishing='电子工业出版社' order by price asc limit 1);

多行子查询： all 表示小于子查询中返回全部值中的最小值 


select bname,price from books where price <all(select price from books where publishing='电子工业出版社');

连接查询

以一个共同的字段，求两张表当中符合条件的并集。通过共同字段把这两张表连接起来

内连接：根据表中的共同字段进行匹配

select 字段 from 表 1 inner join 表 2 on 表 1.字段=表 2.字段
select a.字段,b.字段 from 表1 as a,表2 as b where a.字段=b.字段

select a.bname,a.price,b.btypename from books a inner join category b on a.btypeid=b.btypeid;


select a.bname,a.price,b.btypename from books as a,category as b where a.btypeid=b.btypeid;
实际使用中，inner join 可以用，代替
on 用where代替

select a.bname,a.price,b.btypename from books a,category b where a.btypeid=b.btypeid;
as 是给表命名别名，as可以省略，直接写别名，如from books a,category b where

a表和b表前4条记录aid与bid相等，第5条不相等。内连接只显示两个表符合条件的记录
内连接select * from a c,b d where c.aid=d.bid; 省略a as c,b as d

外连接 (分为左外连接；右外连接)
左外连接： select 字段 from a 表 left join b 表 on 连接条件
主表内容全都有，从表内没有的显示 null
select * from a left join b on a.aid=b.bid;

右外连接：
select * from a right join b on a.aid=b.bid;

聚合函数

算数运算函数

Sum()求和
显示所有图书单价的总合
select sum(price) as 图书总价 from books;

avg()平均值
求书籍 Id 小于等于 3 的所有书籍的平均价格
select avg(price) from books where bid<=3;

max() 最大值
求所有图书中价格最贵的书籍
select bName,max(price) from books; 这种方法是错误的
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'book.books.bName'; this is incompatible with sql_mode=only_full_group_by

select bname,price from books where price=(select max(price) from books); 正确的做法是把嵌套的查询结果当作条件。

min()最小值
求所有图书中价格便宜的书籍

mysql> select bname,price from books where price in (select min(price) from books);

count()统计记录数
统计价格大于 40 的书籍数量
select count(*) from books where price>40;

Count（）中还可以增加你需要的内容，比如增加 distinct 来配合使用
select count(distinct price) from books where price>40;

算数运算 + - * /
给所有价格小于 40 元的书籍，涨价 5 元
update books set price=price+5 where price<40;
给所有价格高于 70 元的书籍打 8 折
update books set price=price*0.8 where price>70;

字符串函数

substr(string ,start,len)
从start开始，截取len的长度
mysql> select substr(btypename,1,7) from category where btypeid=10;

 

concat(str1,str2,str3.....)
拼接，把多个字段拼成一个字段输出
select concat(bname,publishing) from books limit 3;

大小写转换
upper()大写 : 转为大写输出
select upper(bname) from books where bid=9;

lower()小写：转为小写输出
select lower(bname) from books where bid=9;

总结：
数据类型

常用 select 命令

导出、导入数据库

SQL 查询语句进阶