关于数据库的学习记录1-CSDN博客

本文链接：https://blog.csdn.net/qq_62859708/article/details/130298925
学习记录
DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
  `userid` varchar(80) NOT NULL,
  `email` varchar(80) NOT NULL,
  `firstname` varchar(80) NOT NULL,
  `lastname` varchar(80) NOT NULL,
  `status` varchar(2) DEFAULT NULL,
  `addr1` varchar(80) NOT NULL,
  `addr2` varchar(40) DEFAULT NULL,
  `city` varchar(80) NOT NULL,
  `state` varchar(80) NOT NULL,
  `zip` varchar(20) NOT NULL,
  `country` varchar(20) NOT NULL,
  `phone` varchar(80) NOT NULL,
  PRIMARY KEY (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of account
-- ----------------------------
INSERT INTO `account` VALUES ('bookstore', '823160453@qq.com', 'Duo', 'shilin', '1', '河南科技大学', '河南科技大学', '涧西区', '洛阳市', '河南', '中国', '17080035676');
INSERT INTO `account` VALUES ('Burson', 'burson@live.cn', '张', '博雅', '1', '1#101', '郑州', '中原区', '郑州市', '河南省', '中国', '13613808138');
INSERT INTO `account` VALUES ('Duosl', '823160453@qq.com', '多', '世林', '1', '河南科技大学', 'a河南科技大学', '涧西区', '洛阳市', '河南', '中国', '17080035676');

DROP TABLE IF EXISTS `category`;
CREATE TABLE `category` (
  `catid` varchar(10) NOT NULL,
  `name` varchar(80) DEFAULT NULL,
  `descn` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`catid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of category
-- ----------------------------
INSERT INTO `category` VALUES ('02', '编程类', '关于编程类的书籍');
INSERT INTO `category` VALUES ('03', '文学类', '关于文学类的书籍');
INSERT INTO `category` VALUES ('04', '艺术类', '关于艺术类的书籍');
INSERT INTO `category` VALUES ('05', '农业技术类', '关于农业技术类的书籍');
INSERT INTO `category` VALUES ('06', '航空航天类', '关于航空航天类的书籍');
INSERT INTO `category` VALUES ('07', '电子图书类', '关于电子图书类的书籍');
INSERT INTO `category` VALUES ('08', '交通运输类', '关于交通运输类的书籍');
INSERT INTO `category` VALUES ('09', '工业技术类', '关于工业技术类的书籍');
INSERT INTO `category` VALUES ('10', '环境科学类', '关于环境科学类的书籍');

DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
  `productid` varchar(10) NOT NULL,
  `category` varchar(10) NOT NULL,
  `name` varchar(80) DEFAULT NULL,
  `descn` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`productid`),
  KEY `productCat` (`category`),
  KEY `productName` (`name`),
  CONSTRAINT `fk_product_1` FOREIGN KEY (`category`) REFERENCES `category` (`catid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of product
-- ----------------------------
INSERT INTO `product` VALUES ('1002', '02', 'java web开发', 'null');
INSERT INTO `product` VALUES ('1003', '03', '文学基础课程', 'null');
INSERT INTO `product` VALUES ('1004', '04', '艺术基础课程', 'null');
INSERT INTO `product` VALUES ('1005', '05', '由浅入深的了解农业技术', 'null');
INSERT INTO `product` VALUES ('1006', '06', '如何做一个优秀的宇航员', 'null');
INSERT INTO `product` VALUES ('1007', '07', '大主宰', 'null');
INSERT INTO `product` VALUES ('1008', '08', '驾考宝典', 'null');
INSERT INTO `product` VALUES ('1009', '09', '工业技术基础课程', 'null');
INSERT INTO `product` VALUES ('1010', '10', '环境保护', 'null');
INSERT INTO `product` VALUES ('1011', '03', '中国艺术', 'null');
INSERT INTO `product` VALUES ('1013', '03', '中国艺术', 'null');

select * from category,product where category.catid=product.category;

select product.name,category.name from product,category where product.category=category.catid;

select p.name,c.name from product p,category c where p.category=c.catid;

select p.name,c.name from product p inner join category c on p.category = c.catid;

select p.name,c.name from product p join category c on p.category = c.catid;

select * from category c left join product p on c.catid = p.category;

select * from product where category in (select catid from category where category.name='文学类' or category.name='编程类');

show engines ;

show index from category;

create index idx_category_name on category(name);

show global status like 'Com_______';

create or replace view st1 as select  catid,name from category where catid>=2;

show create view st1;

select * from st1;

create procedure p1()
begin
    select count(*) from account;
end;

call p1();

show session variables like 'auto%';

show global variables like 'auto%';

select @@session.autocommit;

set session autocommit=0;

set @myname = 'zzz';

set @myage := 10;

set @myxb := '男',@myhobby:='java';

select @mycolor :='red';
select count(*) into @mycount from category;

select  @myname,@myage,@myxb,@myhobby;

select @mycount;

create procedure p2()
begin
    declare a1 int default 0;
    select count(*) into a1 from category;
    select a1;
end;

call p2();

create procedure p3()
begin

    declare a2 int default 11;
    declare c1 varchar(10);

    if a2 >= 10 then
        set c1:='大号';
    elseif a2>=5 then
        set c1='中号';
    else
        set c1='小号';
    end if;

    select c1;

end;

call p3();

create procedure p4(in a3 int , out c1 varchar(10))
begin

    if a3 >= 10 then
        set c1:='大号';
    elseif a3>=5 then
        set c1='中号';
    else
        set c1='小号';
    end if;

end;

call p4(11,@result);
select @result;

create procedure  p5(inout a double)
begin
    set a := a/2;
end;

set @ebfz=78;

call p5(@ebfz);

select @ebfz;

create
    definer = root@localhost procedure p6(IN x1 int)
begin
    declare result varchar(10);

    case
        when x1>=1 and x1 <=3 then
            set result:='第1季度';
        when x1>=4 and x1 <=6 then
            set result:='第2季度';
        when x1>=7 and x1 <=9 then
            set result:='第3季度';
        when x1>=10 and x1 <=12 then
            set result:='第4季度';
        else
            set result:='非法函数';
    end case ;
    select concat('您输入的月份为：',x1,',季度为：',result);
end;


call p6(7);


create procedure p7(in n int)
begin
    declare t int default 0;

    while n>0 do

        set t := t + n;

        set n=n-1;

        end while;

    select t;

end;

call p7(100);

create procedure p8(in n int)
begin

    declare  t int default 0;

    repeat
        set t =t+n;
        set n:=n-1;
    until n<=0
        end repeat;

    select t;
end;

call p8(9);

create procedure p9(in n int)
begin
    declare  total int default 0;

    sum:loop
        if n<=0 then
            leave sum;
        end if;

        set total := total + n;

        set n := n-1;

    end loop sum;

    select total;

end;

call p9(10);


create procedure p10(in n int)
begin
    declare  total int default 0;

    sum:loop
        if n<=0 then
            leave sum;
        end if;

        if n%2 = 1 then
            set n := n-1;
            iterate sum;
        end if;

        set total := total + n;

        set n := n-1;

    end loop sum;

    select total;

end;