mysql学习
2017-10-15 本文已影响25人
薛落花随泪绽放
#yum -y install mariadb-server mariadb //安装
#systemctl start mariadb //启动Mariadb
//#systemctl stop mariadb //停止Mariadb
//#systemctl restart mariadb //重启Mariadb
#systemctl enable mariadb //设置开机启动
mysql_secure_installation //设置初始密码
//#exit //退出
#ps -ef | grep mysqld //检查mysql是否启动
#mysqladmin -u root -p create RUNOOB //创建数据库
#mysqladmin -u root -p drop RUNOOB //删除数据库
#mysql -uroot -p //进入mysql
use mysql ; //使用mysql
show databases ; //查看数据库
create database pap;
show databases;
use pap;
show tables;
创建数据表
create table class (
stu int,
name varchar(20),
agr int,
area varchar(20)
);
create table score (
stu int,
name varchar(20),
ke varchar(10),
fen int
);
create table msg (
id int,
title varchar(60) not null,
name varchar(10) not null,
content varchar(1000) not null
)engine=innodb default charset=utf8;
插入数据
insert into msg
(id,title,name,content)
values
(1,'初来乍到','张三','刚来能不能当老大');
insert into msg
(id,title,name,content)
values
(2,'又来了','李四','你只能千年老二');
insert into msg
(id,title,name,content)
values
(3,'3标题','刘备','雌雄双剑'),
(4,'4标题','关羽','青龙偃月刀'),
(5,'5标题','张飞','丈八蛇矛');
修改数据
update msg
set
id=2,
content='偏要当老大'
where
name='李四';
insert into msg
(id,title,name,content)
vlaues
();
删除某行数据
delete from msg where id=2;
查找数据
select id,title from msg;
select * from msg where id>2;
select name,content
from msg
where id>2;
重命名
rename ... to ...
删除表
drop table class;
create table class (
id int primary key arto_increment,
name varchar(10),
age tinyint
)charset utf8;
insert into class
(name,age)
values
('zhangsan',25);
insert into class
(name,age)
values
('zhangsan',127);
insert into class
(name,age)
values
('zhangsan',-128);
插入行
alter table class add age2 int unsigned;
insert into class
(name,age,age2)
values
('lisi',25,0)
alter table class add age3 tinyint(1);
create table goods ( //浮点型
name varchar(10) not null default '',
price float(6,2) not null default 0.00
)charset=utf8;
insert into goods
(name,price)
values
('跑步机',688.896);
alter table goods
add
bigprice float(9,2) not null default 0.0;
alter table goods
add
deciprice decimal(9,2) not null default 0.0;
insert into goods
(name,bigprice,deciprice)
values
('',1234567.23,1234567.23);
select * from goods;
create table stu (
name char(8) not null default '',
waihao varchar(10) not null default ''
)charset utf8;
year类型
create table y (
ya year(4)
);
insert into y
values
('1901');
insert into y
values
('2200');
insert into y values ('97');
insert into y values ('12');
date类型
create table d (
title varchar(30),
dt tate
)charset utf8;
insert into d
values
('开国大典','1949-10-01');
insert into d
values
('世界末日','2012-02-30');
time类型
create table t (
tm time
);
insert into t values ('13:34:56');
insert into t values ('13:60:00');
create table user ( //日期时间类型
name varchar(20)not null default '',
regtime datatime not null default '1000-01-01 00:00:00'
)charset utf8;
insert into user
(name)
values
('张三');
insert into user
values
('李四','2012-02-22 14:28:36');
create table teacher (
name varchar(20),
gender tinyint
)engine=innodb default charset=utf8;
insert into teacher values ('张',1);
create table t2 (
gender enum('男','女')
）charset=utf8;
insert into t2 values ('男');
insert into t2 values ('女');
insert into t2 values ('春哥');
//创建表
create table wl163 (
id int primary key auto_increment,
name char(3) not null default '',
age tinyint unsigned not null default 0,
email varchar(30) not null default '',
tel char(11) not null default '',
salary decimal(7,2) not null default '1900.79',
riqi date not null default '2012-03-13'
)charset=utf8;
insert into wl163
(name,age,email,tel,riqi)
values
('陈心宇',21,'chenxinyu@yz.com','18912499740','2016-09-10');
insert into wl163
values
(3,'段宗郃',21,'duanzonghe@ah.com','18951312252',1234.56,'2016-09-10');
#### 插入数据
insert into wl163
(name,age,email)
values
('薛怀',21,'xuehuai@ha.com'),
('杨其龙',21,'yangqilong@sh.com'),
('张迁',21,'zhangqian@sy.com'),
('王欣',21,'wangxin@ah.com');
修改数据
update wl163
set
tel='15161737044',
salary=3999.99
where id=4;
关于where的知识
select 列名称，列名称 from 表名 ;
select goods_id,goods_name from goods;
select goods_id,goods_name,shop_price,market_price from goods;
select 列名称，列名称 from 表名 where 列名称>?;
select goods_id,goods_name from goods where goods_id >20;
select goods_id,goods_name,shop_price,market_price from goods where shop_price >3000;
select 列名称，列名称 from 表名 where 列名称-列名称>?;
select goods_id,goods_name,shop_price,market_price from goods where market_price-shop_price >200;
select 列名称，列名称 from 表名 where 列名称<?;
select goods_id,goods_name,shop_price from goods where shop_price <3000;
select 列名称，列名称 from 表名 where 列名称<=?;
select goods_id,goods_name,shop_price from goods where shop_price <=2000;
select 列名称，列名称 from 表名 where 列名称>=?;
select goods_id,goods_name,shop_price from goods where shop_price >=3000;
select 列名称，列名称 from 表名 where 列名称!=?;
select goods_id,cat_id,goods_name from goods where cat_id !=3;
select 列名称，列名称 from 表名 where 列名称 in(?,?);
select goods_id,cat_id,goods_name from goods where cat_id in (4,5);
select 列名称，列名称 from 表名 where 列名称 between ? and ?;
select goods_id,goods_name,shop_price from goods where shop_price between 2000 and 3000;
select 列名称，列名称 from 表名 where 列名称 >=? and 列名称<=?;
select goods_id,goods_name,shop_price from goods where shop_price >=3000 and shop_price<=5000
select 列名称，列名称 from 表名 where 列名称 >=? and 列名称<=? or 列名称 >=? and 列名称<=?;
select goods_id,goods_name,shop_price from goods where shop_price >=3000 and shop_price<=5000 or shop_price >=500 and shop_price<=1000;
select 列名称，列名称 from 表名 where 列名称 !=? and 列名称 ！=？；
select goods_id,cat_id,goods_name from goods where cat_id !=4 cat_id !=5;
select 列名称，列名称 from 表名 where 列名称 not in (?,?);
select goods_id,cat_id,goods_name from where cat_id not in (4,5);
select 列名称，列名称 from 表名 where 列名称 like ?%;
select goods_id,goods_name from goods like '诺基亚%';
select 列名称，列名称 from 表名 where 列名称 like ?_;
select goods_id,goods_name from goods like '诺基亚___';
%->通配任意字符
'_'单个字符
group学习.
select max(列名称）from 表名；
select max(shop_price) from goods;
select goods_id,goods_name,max(shop_price) from goods;
select 列名称,max(列名称) from 表名 group by 列名称；
select cat_id,max(shop_price) from goods group by cat_id;
select min(列名称) from 表名;
select min(shop_price) from goods;
select goods_id,goods_name,goods_number from goods;
select sum(列名称) from 表名;
select sum(goods_number) from goods;
select avg(列名称) from 表名；
select avg(shop_price) from goods;
select count(列名称) from 表名;
select count(shop_price) from goods;
select 列_id,max(列名称) from 表名 group by 列_id;
select goods_id,cat_id,max(shop_price) from goods group by cat_id;
select 列_id,min(列名称) from 表名 group by 列_id;
select cat_id,min(shop_price) from goods group by cat_id;
select 列_id,avg(列名称) from 表名 group by 列_id;
select cat_id,avg(shop_price) from goods group cat_id;
select 列_id,count(列名称) from 表名 group by 列_id;
select cat_id,count(*) from goods group by cat_id;
select 列名称,列名称，列名称-列名称 from 表名；
select goods_id,goods_name,market_price-shop_price from goods;
select 列_id，sum(列名称*列名称) from 表名 group by 列_id;
select cat_id,sum(shop_price*goods_number) from goods group by cat_id;
select 列_id,sum(列名称*列名称) as 名字 from 表名 group by 列_id;
select cat_id,sum(shop_price*goods_number) as hk from goods group by cat_id;
hiving学习
hiving 筛选
select 列名称,列名称,列名称-列名称 as 名字 from 表名;
select goods_id,goods_name,market_price-shop_price as sheng from goods;
select 列名称,列名称,列名称-列名称 as 名字 from 表名 where 列名称-列名称 >?;
select goods_id,goods_name,market_price-shop_price as sheng from goods where market_price-shop_price >200;
select 列名称,列名称,列名称-列名称 as 名字 from 表名 having 名字 >?;
select goods_id,goods_name,market_price-shop_price as sheng from goods having sheng >200;
select 列名称,列_id,列名称-列名称 as 名字 from 表名 where 列_id=? having 名字>?;
select goods_id,cat_id,market_price-shop_price as sheng from goods where cat_id =3 having sheng >200;
select 列_id,sum(列名称*列名称) as 名字 from 表名 group by 列_id;
select cat_id,sum(shop_price*goods_number) as hk from goods group by cat_id;
select 列_id，sum(列名称*列名称) as 名字 from 表名 group by 列_id having 名字 > ?;
select cat_id,sum(shop_price*goods_number) as hk from goods group by cat_id having hk >20000;
order by 排序
select 列_id,列_id，,列名称，列名称 from goods where 列_id =? order by 列名称;
select goods_id,cat_id,goods_name,shop_price from goods where cat_id=3 order by shop_price;
select 列_id,列_id,列名称，列名称 from goods where 列_id =? order by 列名称 asc; //升序排序
select goods_id,cat_id,goods_name,shop_price from goods where cat_id=3 order by shop_price asc;
select 列_id,，列_id,列名称，列名称 from 表名
where 列_id <> 3
order by 列_id；
select goods_id,cat_id,goods_name,shop_price from goods
where cat_id <>3
order by cat_id;
select 列_id，列_id,列名称，列名称 from 表名
where 列_id <> 3
order by 列_id,列名称 desc;
select goods_id,cat_id,goods_name,shop_price from goods
where cat_id <>3
order by cat_id,shop_price desc;
select 列_id,列_id，列名称,列名称 from 表名 order by 列名称 desc；
select goods_id,cat_id,goods_name,shop_price from goods where cat_id=3 order by shop_price desc;
limit在语句的最后，起到限制条目的作用。
limit[offset],[N]
offset:偏移量
N:取出条目
select 列_id，列名称，列名称 from 表名 order by 列名称 desc limit ?,?;
select goods_id,goods_name,shop_price from goods order by shop_price desc limit 3,3;
子查询
select goods_id,cat_id,goods_name from goods order by cat_id,asc,goods_id desc;
select max(goods_id) ,cat_id from goods group by cat_id;
select goods_id,cat_id,goods_name from goods where goods_id in (select max(goods_id) from goods group by cat_id); //where型子查询
select * from (select goods_id,cat_id,goods_name from goods order by cat_id desc) as tmp group by cat_id; //from型子查询
用子查询查出挂科两门及以上同学的平均分，where型，from型。
use php;
select * from stu;
select name,count(*) from stu where score <60 group by name;
select name,count(*) as gk from stu where score <60 group by name having gk >=2;
select name from (select name,count(*) as gk from stu where score < 60 group by name having gk >=2) as tmp;
select name,avg(score) from stu where name in (select name from (select name,count(*) as gk from stu where score < 60 group by name having gk >=2) as tmp) group by name;
exists
select * from category;
select goods_id,cat_id,goods_name from goods;
查有商品的栏目
select cat_id,cat_name from category where exists (select * from goods where goods.cat_id=category.cat_id);
select cat_id,cat_name from category where exists (select * from goods);
union
select goods_id,goods_name,shop_price from goods where shop_price >5000;
select goods_id,goods_name,shop_price from goods where shop_price <50;
select goods_id,goods_name,shop_price from goods where shop_price <20 or shop_price>5000;
select goods_id,goods_name,shop_price from goods where shop_price >5000 union select goods_id,goods_name,shop_price from goods where shop_price<50;
create table ta (
->id char(1),
->num int
);
insert into ta
values
->('a',5),
->('b',10),
->('c',15),
->('d',10);
create table tb (
->id char(1),
->num int
);
insert into tb
values
->('b',5),
->('c',10),
->('d',20),
->('e',99);
select id,sum(num) from (select * from ta union select * from tb) as tmp group by id;
id | sum(num) |
+------+----------+
| a | 5 |
| b | 15 |
| c | 30 |
| d | 30 |
| e | 99 |
+------+----------
update tb set num =15 where id ='c';
select * from ta
-> union
-> select * from tb;
select * from ta
-> union all
-> select * from tb;
(select goods_id,cat_id,goods_name,shop_price from goods where cat_id =4 order by shop_price desc) union (select goods_id,cat_id,goods_name,shop_price from goods where cat_id =5 order by shop_price desc);
(select goods_id,cat_id,goods_name,shop_price from goods where cat_id =4 order by shop_price desc) union (select goods_id,cat_id,goods_name,shop_price from goods where cat_id =5 order by shop_price desc)order by shop_price desc;
(select goods_id,cat_id,goods_name,shop_price from goods where cat_id =3 order by shop_price desc limit 3) union (select goods_id,cat_id,goods_name,shop_price from goods where cat_id =4 order by shop_price desc limit 2);
select goods_id,cat_id,goods_name from goods limit 5;
select cat_id,cat_name from category;
select * from ta;
id | num |
+------+------+
| a | 5 |
| b | 10 |
| c | 15 |
| d | 10
select * from tb;
id | num |
+------+------+
| b | 5 |
| c | 15 |
| d | 20 |
| e | 99
select * from ta,tb;
id | num | id | num |
+------+------+------+------+
| a | 5 | b | 5 |
| b | 10 | b | 5 |
| c | 15 | b | 5 |
| d | 10 | b | 5 |
| a | 5 | c | 15 |
| b | 10 | c | 15 |
| c | 15 | c | 15 |
| d | 10 | c | 15 |
| a | 5 | d | 20 |
| b | 10 | d | 20 |
| c | 15 | d | 20 |
| d | 10 | d | 20 |
| a | 5 | e | 99 |
| b | 10 | e | 99 |
| c | 15 | e | 99 |
| d | 10 | e | 99 |
+------+------+------+------+
左连接：
select
列1,,列2,列N from
tableA left join tableB
on tableA 列=tableB;
右连接
select
列1,,列2,列N from
tableA right join tableB
on tableA 列=tableB;
内连接
select
列1,,列2,列N from
tableA inner join tableB
on tableA 列=tableB;
create table boy (
name char(3),
flower char(5)
)charset=utf8;
insert into boy
values
('林书豪','玫瑰'),
('刘翔','桃花'),
('周杰伦','茉莉花'),
('犀利哥','荷花'),
('刘德华','狗尾巴');
create table girl (
name char(3),
flower char(5)
)charset=utf8;
insert into girl
values
('艾薇尔','玫瑰'),
('居里夫','桃花'),
('芙蓉姐','茉莉花'),
('凤姐','茉莉花'),
('林志玲','荷花');
select boy.*,girl.*
-> from
-> boy left join girl
-> on boy.flower = girl.flower;
name | flower | name | flower |
+-----------+-----------+-----------+-----------+
| 林书豪 | 玫瑰 | 艾薇尔 | 玫瑰 |
| 刘翔 | 桃花 | 居里夫 | 桃花 |
| 周杰伦 | 茉莉花 | 芙蓉姐 | 茉莉花 |
| 周杰伦 | 茉莉花 | 凤姐 | 茉莉花 |
| 犀利哥 | 荷花 | 林志玲 | 荷花 |
| 刘德华 | 狗尾巴 | NULL | NULL
左连接：以左侧为准，去右表找匹配数据，找不到匹配用null补齐。
select boy.*,girl.*
-> from
-> girl left join boy
-> on boy.flower=girl.flower;
name | flower | name | flower |
+-----------+-----------+-----------+-----------+
| 林书豪 | 玫瑰 | 艾薇尔 | 玫瑰 |
| 刘翔 | 桃花 | 居里夫 | 桃花 |
| 周杰伦 | 茉莉花 | 芙蓉姐 | 茉莉花 |
| 周杰伦 | 茉莉花 | 凤姐 | 茉莉花 |
| 犀利哥 | 荷花 | 林志玲 | 荷花
insert into girl
-> values
-> ('大S','火爆腰花');
name | flower |
+-----------+--------------+
| 艾薇尔 | 玫瑰 |
| 居里夫 | 桃花 |
| 芙蓉姐 | 茉莉花 |
| 凤姐 | 茉莉花 |
| 林志玲 | 荷花 |
| 大S | 火爆腰花
select boy.*,girl.*
-> from
-> girl left join boy
-> on girl.flower=boy.flower;
name | flower | name | flower |
+-----------+-----------+-----------+--------------+
| 林书豪 | 玫瑰 | 艾薇尔 | 玫瑰 |
| 刘翔 | 桃花 | 居里夫 | 桃花 |
| 周杰伦 | 茉莉花 | 芙蓉姐 | 茉莉花 |
| 周杰伦 | 茉莉花 | 凤姐 | 茉莉花 |
| 犀利哥 | 荷花 | 林志玲 | 荷花 |
| NULL | NULL | 大S | 火爆腰花
select girl.*,boy.*
-> from
->boy right join girl
-> on girl.flower=boy.flower;
+-----------+--------------+-----------+-----------+
| name | flower | name | flower |
+-----------+--------------+-----------+-----------+
| 艾薇尔 | 玫瑰 | 林书豪 | 玫瑰 |
| 居里夫 | 桃花 | 刘翔 | 桃花 |
| 芙蓉姐 | 茉莉花 | 周杰伦 | 茉莉花 |
| 凤姐 | 茉莉花 | 周杰伦 | 茉莉花 |
| 林志玲 | 荷花 | 犀利哥 | 荷花 |
| 大S | 火爆腰花 | NULL | NULL
select boy.*,girl.*
-> from
-> girl inner join boy
-> on girl.flower=boy.flower;
+-----------+-----------+-----------+-----------+
| name | flower | name | flower |
+-----------+-----------+-----------+-----------+
| 林书豪 | 玫瑰 | 艾薇尔 | 玫瑰 |
| 刘翔 | 桃花 | 居里夫 | 桃花 |
| 周杰伦 | 茉莉花 | 芙蓉姐 | 茉莉花 |
| 周杰伦 | 茉莉花 | 凤姐 | 茉莉花 |
| 犀利哥 | 荷花 | 林志玲 | 荷花 |
+-----------+-----------+-----------+-----------+
内连接：相当于左右连接的交集。
select goods_id,goods.cat_id,cat_name,goods_name
-> from
-> goods left join category
-> on goods.cat_id=category.cat_id;
select goods_id,goods.cat_id,cat_name,goods.brand_id,brand_name,goods_name
-> from
-> goods left join category
-> on goods.cat_id=category.cat_id
-> left join brand
-> on goods.brand_id=brand.brand_id;
use php;
create table m (
mid int primary key auto_increment,
hid int,
gid int,
mres varchar(10),
matime date
)charset=utf8;
insert into m
(hid,gid,mres,matime)
values
(1,2,'2:0','2006-05-21'),
(2,3,'1:2','2006-06-21'),
(3,1,'2:5','2006-06-25'),
(2,1,'3:2','2006-07-21');
create table t (
tid int,
tname varchar(10)
)charset=utf8;
insert into t
values
(1,'国安'),
(2,'申花'),
(3,'传智连队');
select hid,mres,gid,matime from m;
hid | mres | gid | matime |
+------+------+------+------------+
| 1 | 2:0 | 2 | 2006-05-21 |
| 2 | 1:2 | 3 | 2006-06-21 |
| 3 | 2:5 | 1 | 2006-06-25 |
| 2 | 3:2 | 1 | 2006-07-21
select hid,t1.tname as hname,mres,gid,t2.tname as gname,matime
from
-m left join t as t1
on m.hid=t1.tid
left join t as t2
on m.gid=t2.tid;
hid | hname | mres | gid | gname | matime |
+------+--------------+------+------+--------------+------------+
| 2 | 申花 | 3:2 | 1 | 国安 | 2006-07-21 |
| 3 | 传智连队 | 2:5 | 1 | 国安 | 2006-06-25 |
| 1 | 国安 | 2:0 | 2 | 申花 | 2006-05-21 |
| 2 | 申花 | 1:2 | 3 | 传智连队 | 2006-06-21
select hid,t1.tname as hname,mres,gid,t2.tname as gname,matime
from
m left join t as t1
on m.hid=t1.tid
left join t as t2
on m.gid=t2.tid
where matime between '2006-06-01' and '2006-07-01';
hid | hname | mres | gid | gname | matime |
+------+--------------+------+------+--------------+------------+
| 3 | 传智连队 | 2:5 | 1 | 国安 | 2006-06-25 |
| 2 | 申花 | 1:2 | 3 | 传智连队 | 2006-06-21
