sql复习1

最新推荐文章于 2024-05-15 17:31:51 发布

壮壮是个智障啊

最新推荐文章于 2024-05-15 17:31:51 发布

阅读量104

点赞数

分类专栏： sql

本文链接：https://blog.csdn.net/qq_26607531/article/details/104129813

版权

sql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

create DATABASE user CHARACTER set utf8
SHOW create database user
drop database user

use user
SELECT database()

create table `category`(
	`cid` int(32) not null AUTO_INCREMENT,
	`cname` varchar(100) DEFAULT NULL UNIQUE COMMENT '商品名',
	primary key (`cid`)
)ENGINE = INNODB auto_increment=1 DEFAULT CHARSET=utf8

show tables

desc category

drop table category

alter table category add `time` datetime DEFAULT null

alter table category modify `time` date not null

alter table category change `time` `new_time` datetime default null

alter table category drop `new_time`

rename table category to new_category

alter table new_category character set utf8


insert into new_category (cid,cname) values('11','洗衣机')

insert into new_category values('12','录音机')

update new_category set cid='2' where cid='12'

delete from new_category where cid= '2'

create table `product`(
	`pid` int(2) auto_increment,
	`pname` VARCHAR(20) default null,
	`price` double not null,
	`pdate` datetime default null,
	primary key (`pid`)
)ENGINE = INNODB auto_increment=1 DEFAULT CHARSET=utf8

insert into product values(null,'西西',99.99,null)
insert into product values(null,'心心',9.99,null)
insert into product values(null,'瓜瓜',999.99,null)
insert into product values(null,'蛋蛋',9999.99,null)
insert into product values(null,'傻傻',99.909,null)

SELECT pname,count(*) from product group by pname HAVING count(*)>=2

select distinct pname from product 

select * from product where pname like '_西'
select * from product where pname like '%西'

select * from product where pid in (1,3,5) ORDER BY price asc
select * from product where pid in (1,3,5) ORDER BY price desc


//修改表增加category_id字段
alter table product add `category_id` VARCHAR(32) not null 

//给表product添加外键
alter table product add CONSTRAINT `product_fk` FOREIGN key (`category_id`)  REFERENCES `new_category`(`cid`)

//修改表字段唯一
alter table product add  unique(`catefory_id`)

//添加主键
alter table product add PRIMARY key (pid)

select * from new_category left join product on product.pid = new_category.cid

select * from new_category right join product on product.pid = new_category.cid 

select * from new_category right join product on product.pid = new_category.cid LIMIT 2,2