mariadb

---

yum -y install mariadb mariadb-server

 

systemctl start mariadb

 

systemctl enable mariadb

 

mysql_secure_installation

 

mysql -uroot -ppassword

 

grant all on test.* to yaya@’localhost’ identified by ‘123qqq...A’;

 

grant all on test.* to yaya@'192.168.0.%' identified by '123qqq...A';

 

flush privileges;

 

quit;

 

mysql -h192.168.0.180 -P3306 -uyaya -p123qqq...A;

 

use test;

create table shop(id int(11) not null primary key, name varchar(50) not null )default charset=utf8;

show tables;

select * from shop;

desc shop;

create table commodity( id int not null primary key, name varchar(50) not null, madein varchar(50) not null, type int not null, inprice int not null, outprice int default null, num int not null default 100, constraint `fk_1` foreign key (`type`) references `shop` (`id`)) default charset=utf8;

 

 create table `customer`(

    -> id int not null primary key,

    -> name varchar(50) not null,

    -> phone varchar(50) not null,

    -> gender int not null default '1',

    -> address varchar(100) not null

-> ) default charset=utf8;

 

create table `order`( `id` int not null primary key auto_increment, `cuid` int not null, `num` int not null, constraint `fk_2` foreign key (`cuid`) references `customer` (`id`), constraint `fk_3` foreign key (`num`) references `commodity` (`id`) ) default charset=utf8;

 

insert into `shop` (id ,name) values (1,'玩具'),(2,'文具'),(3,'书籍');

 

delete from `commodity` where id=3;

 

update `commodity` set outprice=123 where name='铅笔';

 

select id, name ,madein from `commodity` where id =1;

 

select name as '商品名称' ,madein as '产地' from `commodity`;

 

select DISTINCT type from commodity;

 

select name as '商品名称',outprice-inprice as '单件利润' from commodity;

 

#null参与运算的结果为空；

 

select name,inprice from commodity where inprice <=20;

 

select name,type from commodity where type=1 and inprice >20;

 

select * from commodity order by num desc;

 

select * from commodity where outprice is not null order by outprice desc;

 

select * from commodity where outprice is not null order by outprice desc limit 0,1;

 

select avg(inprice) as '平均值' from commodity;

 

select max(inprice) as '最高值' from commodity;

 

select type,count(*) from commodity group by type;

#跟where一样的条件；分组后的筛选，where用不了，用having, （分组后筛选用having）

select type,avg(inprice) from commodity group by type having avg(inprice) >1;

 

#内连接

select * from commodity a inner join shop b on a.type = b.id;

 

select * from commodity inner join shop on commodity.type = shop.id;

 

select * from commodity as a inner join shop as b on a.type = b.id;

#外连接

 

#左连接

select * from commodity c left join shop s on c.type=s.id;

#右链接

select * from commodity c right join shop s on c.type=s.id; 右边的字段作参照；

#多表查询

select * from commodity c right join shop s on c.type=s.id where c.name like '%形%' and s.name='玩具';

#子查询 一般情况下和连接查询可以互换的

select c.name from commodity c where c.type=(select s.id from shop s where s.name='玩具');

 

---