学习记录
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;