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等等

  • 17
    点赞
  • 8
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值