mysql数据库操作练习篇

1）查询作者为“刘刚”的图书的数量。

SELECT COUNT(*) AS 数量  FROM `book`  WHERE `author`='刘刚'

2）查询出版单位为人民邮电出版社的图书明细。

SELECT `id`,`book_name`,`author`,`price`,`cd`,`publish`,`book_classify_id`,`account`,`isbn`,`create_time`,`remark` FROM `book` WHERE `publish`='人民邮电出版社';

3）检索2024年3月的借阅图书的读者人数。

SELECT COUNT( DISTINCT `user_id`) FROM `book_borrow` 

WHERE YEAR(`borrow_time`)='2024' AND MONTH(`borrow_time`)='3';

4）图书管理系统里需要新增一本刚采购的医药方面的书籍：书名为《疑难杂病临证手册（第2版）》、作者为余孟学、定价为158元、出版社为河南科技出版社、ISBN编号为9787534989230。要把它添加到图书表book里，图书分类选择“R 医药、卫生”。

INSERT INTO`book`VALUES('5','疑难杂病临证手册（第2版）','余孟学','158','1','河南科技出版社','4','1000','9787534989230',NOW(),NULL);

5）查询用户小影的借书记录，包括用户姓名、借阅图书名称、出版社、借书时间、归还时间。

SELECT u.user_name,b.book_name,b.publish,w.borrow_time,w.return_time FROM USER u, book b, book_borrow w WHERE w.book_id = b.id AND w.user_id = u.id AND u.login_name = '小影';

6）针对用户表user、图书表book、图书借阅表book_borrow建立一个用户借阅图书信息查询视图user_book_borrow_view，查询用户编号、登录名称、姓名、图书名称、出版社、借阅时间、归还时间。

CREATE OR REPLACE VIEW user_book_borrow_view

AS

SELECT u.id,u.login_name,u.user_name,b.book_name,b.publish,w.borrow_time,w.return_time

FROM USER u, book b, book_borrow w

WHERE w.book_id = b.id AND w.user_id = u.id ;

7）按book_name字段建立图书索引。索引名为：booknameindex;

CREATE INDEX booknameindex ON  `book`(`book_name`)

8）按publish，book_name字段建立图书表复合索引。索引名为：bookpublishnameindex ;

CREATE INDEX bookpublishnameindex ON  `book`(`publish`,`book_name`)

9）更新小刚用户的部门为运维部。

UPDATE `user` SET `dept_id`=(SELECT `id` FROM `dept` WHERE `dept_name`='运维部')

WHERE `user_name`='小刚'

10）删除图书名称包含：“经济学”的所有图书

delete from `book` where `book_name` like '%经济学%'

11)删除用户小影的全部借书记录。

DELETE FROM `book_borrow` WHERE`user_id`=(SELECT `id` FROM `user` WHERE `user_name`='小影')

12）查询所有用户丢失的图书名称。

SELECT `user_name`,`book_name` FROM `book`,`book_borrow`,`user`,`book_lose`

WHERE `book_lose`.`borrow_id`=`book_borrow`.`id` AND `book_borrow`.`user_id`=`user`.id AND `book_borrow`.`book_id`=`book`.`id`

函数

存储函数

(1)创建一个存储函数，返回员工的总人数。

CREATE FUNCTION EM NUM()

RETURNS INTEGER

RETURN(SELECT COUNT(*) FROM Employees);

调用该存储函数:

select EM_NUM();

(2)创建一个存储函数，删除在Salary表中有但在Employees表中不存在的员工号。若在Employees表中存在则返回FALSE,若不存在则删除该员工号并返回TRUE。

DELIMITER $$

CREATE FUNCTION DELETE_EM(EM_ID CHAR(6))

RETURNS BOOLEAN

BEGIN

DECLARE EM_NAME CHAR(10);

SELECT Name INTO EM_NAME FROM Employees WHERE EmployeelD=EM_ID;

IF EM_NAME IS NULL THEN

DELETE FROM Salary WHERE EmployeeID=EM_ID;

RETURN TRUE;

ELSE

RETURN FALSE;

END IF;

END$$

DELIMITER ;

调用该存储函数:

select DELETE_eM(‘000001’);

内置函数

MySQL的内置函数

1、数学函数

abs(x):绝对值

ceil(x)或者ceiling(x)：向上取整

Floor(x):向下取整

pow(x,y)：求指数，x的y次方

rand(),rand(N):返回0-1间的浮点数，使用不同的seed N可以获得不同的随机数

round(x, D)：四舍五入保留D位小数，D默认为0， 可以为负数， 如round(19, -1)返回20

2 字符串函数

char_length(str):返回str所包含的字符数，一个多字节字符算一个字符

length(str): 返回字符串的字节长度，如utf8中，一个汉字3字节，数字和字母算一个字节

concat(s1, s1, ...): 返回连接参数产生的字符串

lower（str)或者lcase(str):全部小写

upper(str)或者ucase(str):全部大写

left(s,n):返回字符串s最左边n个字符

right(s,n): 返回字符串最右边n个字符

ltrim(s):删除s左侧空格字符

SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len),MID(str,pos,len): 获取特定位置，特定长度的子字符串

INSTR(str,substr): 判断子字符串在目标字符串中是否存在，存在返回其位置，不存在，返回0。

3 日期和时间函数

CURDATE(), CURRENT_DATE, CURRENT_DATE():用于获取当前日期，格式为'YYYY-MM-DD'; 若+0则返回YYYYMMDD

CURTIME([fsp]), CURRENT_TIME, CURRENT_TIME([fsp]): 用于获取当前时间， 格式为'HH:MM:SS' 若+0则返回 HHMMSS

DATEDIFF(expr1,expr2):返回相差的天数,参数是必须使用字符串格式（用引号）。

TIMEDIFF(expr1,expr2)：返回相隔的时间

ADDDATE(date,INTERVAL expr unit):进行时间的增加。

Unit：day/hour/minute/second

MONTH(date)

MONTHNAME(date)

DAYNAME(date)

DAY(date)，DAYOFMONTH(date)：1-31或者0

DAYOFWEEK(date)：1-7==>星期天-星期六

DAYOFYEAR(date)： 1-365

4 系统信息函数

VERSION():返回mysql服务器的版本，是utf8编码的字符串

DATABASE()，SCHEMA()：显示当前使用的数据库

SESSION_USER(), SYSTEM_USER(), USER(), CURRENT_USER, CURRENT_USER():返回当前的用户名@主机，utf8编码字符串

CHARSET(str)

COLLATION(str)

LAST_INSERT_ID()：自动返回最后一个insert或者update查询， 为auto_increment列设置的第一个发生的值

5 其他函数

MD5(str):对数据进行MD5加密。

UUID：生成一个唯一标识符（自增长），是整数，数据唯一，空间唯一

6条件判断函数

IF(expr1,expr2,expr3)：如果expr1不为0或者NULL,则返回expr2的值，否则返回expr3的值

IFNULL(expr1,expr2)：如果expr1不为NULL,返回expr1,否则返回expr2

NULLIF(expr1,expr2): 如果expr1=expr2则返回NULL, 否则返回expr2

CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END

当compare_value=value时返回result

CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

当condition为TRUE时返回result

    SELECT CASE 1 WHEN 1 THEN 'one'

        WHEN 2 THEN 'two' ELSE 'more' END;##'one'

    SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;##'true'

    SELECT CASE BINARY 'B'

        WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;##NULL

7 格式或类型转化函数

FORMAT(X,D[,locale])：将数字X转化成'#,###,###.##'格式，D为保留的小数位数

CONV(N,from_base,to_base)：改变数字N的进制，返回值为该进制下的数字构成的字符串

INET_ATON(expr)：ip字符串转数字

INET_NTOA(expr)：数字转ip字符串

CAST(expr AS type)：转换数据类型

CONVERT(expr,type), CONVERT(expr USING transcoding_name)： type可以为BINARY[(N)]，CHAR[(N)]，DATE，DATETIME， DECIMAL[(M[,D])]，DECIMAL[(M[,D])]，TIME，UNSIGNED [INTEGER]等等。transcoding_name如utf8等等