mysql系列：mysql基础+select5中子句+子查询
mysql查询模型
mysql基础语句+数据类型实例
1、笛卡儿积是什么
# 先建表，有数据再说
create table test10(id int,sname varchar(20)
)engine myisam charset utf8;create table test11(cat_id int,cname varchar(20)
)engine myisam charset utf8;insert into test10
values
(1, '云彩'),
(2,'月亮'),
(3,'星星');insert into test11
values
(95,'猴子'),
(96,'老虎');复制代码1.1、笛卡儿积
现在，我们有两个集合A和B。
A = {0,1} B = {2,3,4}
集合 A×B 和 B×A的结果集就可以分别表示为以下这种形式：
A×B = {（0，2），（1，2），（0，3），（1，3），（0，4），（1，4）}；
B×A = {（2，0），（2，1），（3，0），（3，1），（4，0），（4，1）}；
以上A×B和B×A的结果就可以叫做两个集合相乘的‘笛卡尔积’。
select * from test10,test11;复制代码2、利用笛卡儿积筛选数据
# 建立新的表
create table goods (goods_id mediumint(8) unsigned primary key auto_increment,goods_name varchar(120) not null default '',cat_id smallint(5) unsigned not null default '0',brand_id smallint(5) unsigned not null default '0',goods_sn char(15) not null default '',goods_number smallint(5) unsigned not null default '0',shop_price decimal(10,2) unsigned not null default '0.00',market_price decimal(10,2) unsigned not null default '0.00',click_count int(10) unsigned not null default '0'
) engine=myisam default charset=utf8复制代码复制代码插入数据
insert into `goods` values (1,'kd876',4,8,'ecs000000',1,1388.00,1665.60,9),
(4,'诺基亚n85原装充电器',8,1,'ecs000004',17,58.00,69.60,0),
(3,'诺基亚原装5800耳机',8,1,'ecs000002',24,68.00,81.60,3),
(5,'索爱原装m2卡读卡器',11,7,'ecs000005',8,20.00,24.00,3),
(6,'胜创kingmax内存卡',11,0,'ecs000006',15,42.00,50.40,0),
(7,'诺基亚n85原装立体声耳机hs-82',8,1,'ecs000007',20,100.00,120.00,0),
(8,'飞利浦9@9v',3,4,'ecs000008',1,399.00,478.79,10),
(9,'诺基亚e66',3,1,'ecs000009',4,2298.00,2757.60,20),
(10,'索爱c702c',3,7,'ecs000010',7,1328.00,1593.60,11),
(11,'索爱c702c',3,7,'ecs000011',1,1300.00,0.00,0),
(12,'摩托罗拉a810',3,2,'ecs000012',8,983.00,1179.60,13),
(13,'诺基亚5320 xpressmusic',3,1,'ecs000013',8,1311.00,1573.20,13),
(14,'诺基亚5800xm',4,1,'ecs000014',1,2625.00,3150.00,6),
(15,'摩托罗拉a810',3,2,'ecs000015',3,788.00,945.60,8),
(16,'恒基伟业g101',2,11,'ecs000016',0,823.33,988.00,3),
(17,'夏新n7',3,5,'ecs000017',1,2300.00,2760.00,2),
(18,'夏新t5',4,5,'ecs000018',1,2878.00,3453.60,0),
(19,'三星sgh-f258',3,6,'ecs000019',12,858.00,1029.60,7),
(20,'三星bc01',3,6,'ecs000020',12,280.00,336.00,14),
(21,'金立 a30',3,10,'ecs000021',40,2000.00,2400.00,4),
(22,'多普达touch hd',3,3,'ecs000022',1,5999.00,7198.80,16),
(23,'诺基亚n96',5,1,'ecs000023',8,3700.00,4440.00,17),
(24,'p806',3,9,'ecs000024',100,2000.00,2400.00,35),
(25,'小灵通/固话50元充值卡',13,0,'ecs000025',2,48.00,57.59,0),
(26,'小灵通/固话20元充值卡',13,0,'ecs000026',2,19.00,22.80,0),
(27,'联通100元充值卡',15,0,'ecs000027',2,95.00,100.00,0),
(28,'联通50元充值卡',15,0,'ecs000028',0,45.00,50.00,0),
(29,'移动100元充值卡',14,0,'ecs000029',0,90.00,0.00,0),
(30,'移动20元充值卡',14,0,'ecs000030',9,18.00,21.00,1),
(31,'摩托罗拉e8 ',3,2,'ecs000031',1,1337.00,1604.39,5),
(32,'诺基亚n85',3,1,'ecs000032',4,3010.00,3612.00,9);复制代码复制代码还需要一张表
# 需要再建一张表，结合上面的goods表练习
create table category(cat_id int auto_increment primary key,cat_name varchar(20) not null default ''
)engine myisam charset utf8;复制代码复制代码# 插入数据
insert into category
(cat_name)
values
('手机类型'),
('CDMA手机'),
('GSM手机'),
('3G手机'),
('双模手机'),
('手机配件'),
('充电器'),
('耳机'),
('电池'),
('读卡器和内存'),
('充值卡'),
('小灵通/固话充值卡'),
('移动手机充值卡'),
('联通手机充值卡');
复制代码复制代码2.1 筛选出goods表中cat_id跟category表中cat_id一样的商品
select goods_id,goods_name,goods.cat_id,cat_name,category.cat_id
from goods, category
where goods.cat_id = category.cat_id;复制代码3、左连接
上面那种方法太low了，只是为了引出左连接
左连接语法： A表 left join B表 on 条件
实际上就是A表和B表做了笛卡儿积，然后条件就是来筛选这个笛卡儿积里的数据，这样是不是就很容易理解左连接了呢
3.1 用左连接筛选出goods表中cat_id跟category表中cat_id一样的商品
select goods_id,goods_name,goods.cat_id
from goods left join category
on goods.cat_id = category.cat_id;复制代码是不是结果跟上面用笛卡儿积差不多呢，但是左连接效率更高
3.2 用左连之后还可以用where等子句吗?
肯定的是可以的，案例如下：
取出第四个栏目下商品，以及商品的栏目名称
select goods_id,goods_name,cat_name, goods.cat_id
from goods left join category
on goods.cat_id = category.cat_id
where goods.cat_id = 4;复制代码结论：把左连接的结果就当成一个表
3.3 稍微复杂的左连接
先建表
create table boy(bname varchar(20),other char(1)
)engine myisam charset utf8;insert into boy
values
('屌丝','A'),
('李四','B'),
('王五','C'),
('高富帅','D'),
('郑七','E');create table girl(gname varchar(20),other char(1)
)engine myisam charset utf8;insert into girl
values
('空姐','B'),
('大s','C'),
('阿娇','D'),
('张柏芝','D'),
('林黛玉','E'),
('宝钗','F');复制代码需要注意的如果这里boy left join girl ，boy第一项屌丝是没有匹配对象的，并且高富帅同时匹配了两个人，一个是阿娇，一个是张柏芝，所以我们看下这两个表左连接会出现什么情况
select boy.*,girl.* from boy left join girl on boy.other=girl.other;复制代码4、右连接
右连接语法： A表 right join B表 on 条件
右连接其实就是左连接，上面的语法相当于
B表 left join A表 on 条件
select boy.*,girl.* from boy right join girl on boy.other=girl.other;复制代码5、内链接
内连接语法： A表 innerjoin B表 on 条件
select boy.*,girl.* from boy inner join girl on boy.other = girl.other;复制代码内链接相当于左右连接的交集
6、union(合并表)
我们将上面建好的两张表用来union
注意了，他们的列名称不一样，我们union看看
select * from boy union select * from girl;复制代码union的结果集就当作一张表，可以继续用子句，比如用order by子句
select * from boy union select * from girl order by other;
复制代码7、常用函数
7.1 数学函数
abs(x) 返回绝对值函数
bin(x)返回x的二进制
floor(x)对x小数取整
rand()返回0到1内的随机数
ceiling(x)x向上取整
7.2 字符串函数
函数返回一个字符串结果，该结果由分组中的值连接组合而成
group_concat（）
length（x）返回x字节长度
char_length(x) 返回x的字符数
reverse(x) 反转字符串
right(a,b) 把a字符串的后b个字符返回
position(a in b)返回a在b字符串中的位置
7.3 日期函数
now() 返回当前日期。datetime格式
dayofweek(date) 返回date所代表的一星期中的第几天（1-7）
dayofmonth(date) 返回date是一个月的第几天（1-31）
dayofyear(date)返回date是一年的第几天（1-366）同理
week(date)是第几周
7.4 流程控制语句
case 值 when 某种可能 then 返回值 记住最后一行要写end
if(表达式，表达式为true的值, 表达式为false的值)
select user();
查看当前用户