实训内容二

1. 使用算数运算符-, 查询最低借阅量与最高借阅量的差值

   select max(borrowsum)-min(borrowsum) as 差值 from book;复制代码

2. 使用比较运算符(>)查询book表中借阅量大于10的书籍信息

   select * from book where borrowsum > 10;复制代码

3. 使用right()函数返回从字符串loveMySQL右边开始的5个字符

   select right('ILoveMySQL',5);复制代码

4. 查询数据表bookborrow中2011年的借阅信息

   -- 第1种方法 SELECT 	*  FROM 	bookborrow  WHERE     ( DATEDIFF( borrowtime, '2011-01-01' ) > 0 AND DATEDIFF( borrowtime, '2011-01-01' ) < 365 )  	OR      ( DATEDIFF( returntime, '2011-01-01' ) > 0 AND DATEDIFF( returntime, '2011-01-01' ) < 365 );      -- 第2种方法     SELECT 	*  FROM 	bookborrow  WHERE     ( borrowtime >= '2011-01-01' AND borrowtime <= '2011-12-31' )  	OR      ( returntime >= '2011-01-01' AND returntime <= '2011-12-31' );  -- 第3种方法     SELECT 	*  FROM 	bookborrow  WHERE     ( borrowtime BETWEEN '2011-01-01' AND '2011-12-31' )  	OR      ( returntime BETWEEN '2011-01-01' AND '2011-12-31' );  -- 第4种方法     SELECT 	*  FROM 	bookborrow  WHERE     borrowtime LIKE '2011%'  	OR      returntime LIKE '2011%';  -- 第5种方法     SELECT 	*  FROM 	bookborrow  WHERE     borrowtime REGEXP '^2011'  	OR      returntime REGEXP '^2011';  -- 第6种方法     SELECT 	*  FROM 	bookborrow  WHERE     YEAR ( borrowtime ) = '2011'  	OR      YEAR ( returntime ) = '2011';复制代码

5. 使用concat()函数连接两个字符串

   select concat('ILove','MySQL');复制代码

操作题三

1. 查询选修了C1或C2且分数大于或等于85分的学生学号

   SELECT	student.sid FROM	student,	scourse WHERE	student.sid = scourse.sid 	AND scourse.cid IN ( 'C1', 'C2' ) 	AND score >= 85;复制代码

2. 查询工资不在1500~2000之间的教师号,姓名,职称

   SELECT	tname,	tid,	title FROM	teacher WHERE	salary NOT BETWEEN 1500 	AND 2000;复制代码

3. 从bookinfo中查询书的名称和单价, 使书的单价精确到个位即可

   select bname,TRUNCATE(bprice,1) from bookinfo;复制代码

4. 从bookinfo表中查询所有的书名, 数量以及单价信息, 并要求所有书名用大写字母表示

   SELECT	upper( bname ),	quantity,	bprice FROM	bookinfo;复制代码

5. 从bookinfo表中查询所有以processing结尾的书名, 数量, 单价信息

   SELECT	bname,	quantity,	bprice FROM	bookinfo WHERE	bname LIKE '%processing';复制代码

   SELECT	bname,	quantity,	bprice FROM	bookinfo WHERE	RIGHT ( bname, 10 ) = 'processing';复制代码

   SELECT	bname,	quantity,	bprice FROM	bookinfo WHERE	bname REGEXP 'processing$' = 1;复制代码

6. 从bookinfo表中查询所有的书名, 单价信息以及将书名中的字符串processing替换为pro.后的结果

   SELECT	bname,	REPLACE ( lower( bname ), 'processing', 'pro.' ) AS 转换后,	bprice FROM	bookinfo;复制代码

快速跳转