数据库Mysql的学习(六)-子查询和多表操作

SELECT store,store+1,store-1,store/2,store*2,store%2 FROM bookinfo;//加减乘除取余

SELECT * FROM readerinfo WHERE balance>200//余额大于200

SELECT * FROM readerinfo WHERE balance<>200//余额不等于200

SELECT * FROM readerinfo WHERE age IS NOT NULL//年龄不为空

SELECT * FROM readerinfo WHERE balance BETWEEN 200 AND 300//余额200到300

SELECT * FROM readerinfo WHERE NAME IN ('zhangfei','guanyu','liubei')//查询这几个读者的信息

SELECT * FROM readerinfo WHERE NAME LIKE '张_'//名字是两个字,第一个字是张

SELECT * FROM readerinfo WHERE WHERE tel LIKE '135%';//手机号为135开头的

//and与,or或,not非

SELECT * FROM readerinfo WHERE price NOT BETWEEN 50 AND 100//不在50到100之间的

//各种函数

SELECT CEIL(28.55)//29向上取整

SELECT FLOOR(28.55)//28

SELECT ROUND(28.55)//29 四舍五入

SELECT ROUND(28.55,1),ROUND(28.55,0),ROUND(28055,-1);//28.6  29  90(x,y)返回最接近x的数,保留小数点y位

SELECT TRUNCATE(28.55,1),TRUNCATE(28.55,0),TRUNCATE(28.55,-1);//28.5  28  20截断函数

SELECT MOD(11,2)//1  (X,Y)x被y处后的余数

SELECT * FROM bookinfo WHERE MOD(book_id,2)=0;//图书编号为偶数的

SELECT CONCAT('-','hello','word');//连接函数,第一个参数为连接符,不加为空.结果为hello-word

SELECT LOWER('helloWord');//

SELECT UPPER('helloWord');//大小写转换

SELECT LENGTH('xxx');//长度

LTRIM(s),RTRIM(s),TRIM(s)//删除左侧,右侧,两侧空格

SELECT LEFT('helloworld',5)//hello

SELECT RIGHT('helloworld',5)//world

SELECT REPLACE(str,from_str,to_str)//替换函数

SELECT FORMAT(1234.5678,2),FORMAT(1234.5,2),FORMAT(1234.5678,0)//1234.57  1234.50  1235

SELECT CURDATE();//2018-06-19返回当前日期,最后+0则为20180619

SELECT CURTIME();//当前时间

SELECT NOW();//当前日期和时间

SELECT DATE_ADD('2018-06-19',INTERVAL 5 MONTH)//2018-11-19

SELECT DATEDIFF('2017-02-01','2017-01-01');//间隔天数

//实例表为图书标号,身份证好,借书日期,归还日期,是否归还插入信息

INSERT INTO borrowinfo VALUES(20150301,'11111111111111',CURDATE(),DATE_ADD(CURDATE(),INTERVAL 1 motnth),'no');

UPDATE bookinfo SET store=store-1 WHERE book_id=20150301;

UPDATE readerinfo SET balance=balance-69*0.05 WHERE card_id='11111111111111';

SELECT AVG(price) FROM bookinfo//所有图书的平均价格

SELECT SUM(price) FROM bookinfo//总价格

SELECT MAX(price) FROM bookinfo

SELECT MIN(price) FROM bookinfo

SELECT book_category AS '图书类别' ,COUNT(book_id) AS '图书种类' ,SUM(store) AS '库存总和' FROM bookinfo GROUP BY book_category_id;

//按类别分组查询每种类别下有多少中图书以及每种类别图书的库存总和。as为起个别名

SELECT VERSION();//当前服务器版本号

SELECT CONNECTION_ID();//连接次数

SELECT DATABASE();//当前数据库名

SELECT USER();//当前用户名

//MD5加密方式,介绍为下面补图

CREATE TABLE myuesr(
    username VARCHAR(20),
    passwordd VARCHAR(20)
);
INSERT INTO myuser VALUES('usel',MD5('pwd1'));//表中密码信息被MD5加密

SELECT * FROM myuesr//这样是看不到的

SELECT * FROM myuser WHERE username = 'usel' AND passwordd=MD5('pwd1');//要通过这样知道,如果查找到了说明密码正确

SET PASSWORD =PASSWORD('xxxxx');//通过password修改密码,这也是加密后的

SELECT SUBSTRING("hello world',1,5);//hello
SELECT SUBSTRING("hello world',-3,2);//截断字符串 rl

 

先补一张日期函数格式化的图

UPDATE readerinfo SET balance = balance-(SELECT price FROM bookinfo WHERE book_id=20150301)*0.05 WHERE card_id ='20121xxxxxx';
//子查询就是一个嵌套先计算子查询

SELECT * FROM borrow WHERE book_id =(SELECT book_id FROM bookinfo book_name ='xxxxxx')

SELECT * FROM bookinfo WHERE price <(SELECT ROUND(AVG(price),2) FROM bookinfo);//显示小于平均图书价格的图书信息

SELECT * FROM bookinfo WHERE book_category_id <> (SELECT category_id FROM boiokcategory WHERE category='数据库');//显示不是数据库的图书信息

SELECT * FROM bookinfo WHERE book_category_id = ANY(SELECT category_id FROM bookcategory WHERE parent_id =1);//        

SELECT * FROM bookinfo WHERE price > ANY (SELECT price FROM bookinfo WHERE book_category_id=4);//any为大于他的最小值

SELECT * FROM bookinfo WHERE price > SOME (SELECT price FROM bookinfo WHERE book_category_id=4);//some为大于他的最大值

SELECT * FROM bookinfo WHERE book_category_id  IN (SELECT category_id FROM bookcategory WHERE parent_id=2)//这时

SELECT * FROM bookinfo WHERE book_category_id = ANY (SELECT category_id FROM bookcategory WHERE parent_id=2)//两句等效内层查询语句返回的是一个数据列,供外层查询语句比较操作

SELECT * FROM table1 WHERE EXISTS(子查询)//exist判断是否存在,存在就执行外查询


CREATE TABLE readerfee(
    book_id INT,
    card_id CHAR(18),
    actul_return_date DATE,
    book_fee DECIMAL(7,3),
    PRIMARY KEY(book_id,card_id)
);
SELECT book_id ,card_id,return_id FROM borrowinfo WHERE DATEDIFF(SYSDATE(),return_date) >0 AND statue='否';

SELECT INTO readerfee(book_id,card_id,return_date) SELECT book_id ,card_id,return_id FROM borrowinfo WHERE DATEDIFF(SYSDATE(),return_date) >0 AND statue='否';
//将一个表中的记录插入到另一个表中


//练习
UPDATE borrowinfo SET STATUS ='是' WHERE book_id =20151101 AND card_id ='20120xxxxx';

UPDATE readerfee SET actual_return_date=SYSDATE(),book_fee=DATEDIFF(SYSDATE(),return_date)*0.2 WHERE book_id =20151101 AND card_id='3213100.0xxxx';




//多表查询
SELECT book_id ,book_name,category FROM bookinfo INNER JOIN bookcategory ON bookinfo.book_category_id=bookcategory.category_id;
//有内链接  外连接和自连接

//内链接
内链接为两个表都满足条件的
SELECT borrowinfo.book_id,book_name,borrowinfo.card_id ,NAME,tel,return_date,STATUS FROM borrowinfo
INNER JOIN bookinfo ON borrowinfo.book_id = bookinfo.book_id
INNER JOIN readerinfo ON borrowinfo.card_id=readerinfo.card_id
WHERE borrowinfo.status='否';    


SELECT t1.book_id,book_namet1.card_id ,NAME,tel,return_date,STATUS FROM borrowinfo t1
JOIN bookinfo t2 ON t1.book_id = t2.book_id
JOIN readerinfo t3 ON t1.card_id=t3.card_id
WHERE t1.status='否';    //起个别名这样也是可以的,inner可以省略。
//ON 后面为内连接的条件

//外连接 有左连接和右连接
左连接 显示左表的全部记录  右表满足条件的记录,右连接同理

SELECT book_id ,book_name,category FROM bookcategory
LEFT JOIN bookinfo ON bookcategory.category_id =bookinfo.book_category_id;
WHERE parent_id<>0;


SELECT book_id ,book_name,category FROM bookcategory
RIGHT JOIN bookinfo ON bookcategory.category_id =bookinfo.book_category_id;
WHERE parent_id<>0;

//自连接
SELECT * FROM bookcategory

SELECT s.category_id AS '图书类别编号' ,s.category AS '图书类别名称' ,p.category AS '图书上级分类名称' FROM bookcategory s
LEFT JOIN bookcategory p ON s.parent_id =p.category_id;

//多表更新
首先需要把表连接起来
UPDATE readerfee t1 JOIN readerinfo t2 ON t1.card_id=t2.card_id
SET actual_return_date =SYSDATE() , bookfee=DATEDIFF(SYSDATE(),return_date)*0.2 , balance=balance-book_fee
WHERE t1.book_id =20151101 AND t1.card_id='2002xxxxxxxx';
//表的复制
CREATE TABLE bookcategory_bak
AS
SELECT * FROM bookcategory;
//多表删除
多表删除的话要用到多表连接
DELETE xx,xx,FROM (多表连接的东西)

金沙官网线上 1

 

 

UPDATE readerinfo SET balance = balance-(SELECT price FROM bookinfo WHERE book_id=20150301)*0.05 WHERE card_id ='20121xxxxxx';
//子查询就是一个嵌套先计算子查询

然后是函数加密

SELECT * FROM borrow WHERE book_id =(SELECT book_id FROM bookinfo book_name ='xxxxxx')

金沙官网线上 2

SELECT * FROM bookinfo WHERE price <(SELECT ROUND(AVG(price),2) FROM bookinfo);//显示小于平均图书价格的图书信息

 

SELECT * FROM bookinfo WHERE book_category_id <> (SELECT category_id FROM boiokcategory WHERE category='数据库');//显示不是数据库的图书信息

SELECT store,store+1,store-1,store/2,store*2,store%2 FROM bookinfo;//加减乘除取余
SELECT * FROM readerinfo WHERE balance>200//余额大于200
SELECT * FROM readerinfo WHERE balance<>200//余额不等于200
SELECT * FROM readerinfo WHERE age IS NOT NULL//年龄不为空
SELECT * FROM readerinfo WHERE balance BETWEEN 200 AND 300//余额200到300
SELECT * FROM readerinfo WHERE NAME IN ('zhangfei','guanyu','liubei')//查询这几个读者的信息
SELECT * FROM readerinfo WHERE NAME LIKE '张_'//名字是两个字,第一个字是张
SELECT * FROM readerinfo WHERE WHERE tel LIKE '135%';//手机号为135开头的
//and与,or或,not非
SELECT * FROM readerinfo WHERE price NOT BETWEEN 50 AND 100//不在50到100之间的
//各种函数
SELECT CEIL(28.55)//29向上取整
SELECT FLOOR(28.55)//28
SELECT ROUND(28.55)//29 四舍五入
SELECT ROUND(28.55,1),ROUND(28.55,0),ROUND(28055,-1);//28.6 29 90(x,y)返回最接近x的数,保留小数点y位
SELECT TRUNCATE(28.55,1),TRUNCATE(28.55,0),TRUNCATE(28.55,-1);//28.5 28 20截断函数
SELECT MOD(11,2)//1 (X,Y)x被y处后的余数
SELECT * FROM bookinfo WHERE MOD(book_id,2)=0;//图书编号为偶数的
SELECT CONCAT('-','hello','word');//连接函数,第一个参数为连接符,不加为空.结果为hello-word
SELECT LOWER('helloWord');//
SELECT UPPER('helloWord');//大小写转换
SELECT LENGTH('xxx');//长度
LTRIM(s),RTRIM(s),TRIM(s)//删除左侧,右侧,两侧空格
SELECT LEFT('helloworld',5)//hello
SELECT RIGHT('helloworld',5)//world
SELECT REPLACE(str,from_str,to_str)//替换函数
SELECT FORMAT(1234.5678,2),FORMAT(1234.5,2),FORMAT(1234.5678,0)//1234.57 1234.50 1235

SELECT * FROM bookinfo WHERE book_category_id = ANY(SELECT category_id FROM bookcategory WHERE parent_id =1);//

SELECT CURDATE();//2018-06-19返回当前日期,最后+0则为20180619
SELECT CURTIME();//当前时间
SELECT NOW();//当前日期和时间
SELECT DATE_ADD('2018-06-19',INTERVAL 5 MONTH)//2018-11-19
SELECT DATEDIFF('2017-02-01','2017-01-01');//间隔天数

SELECT * FROM bookinfo WHERE price > ANY (SELECT price FROM bookinfo WHERE book_category_id=4);//any为大于他的最小值

//实例表为图书标号,身份证好,借书日期,归还日期,是否归还插入信息
INSERT INTO borrowinfo VALUES(20150301,'11111111111111',CURDATE(),DATE_ADD(CURDATE(),INTERVAL 1 motnth),'no');
UPDATE bookinfo SET store=store-1 WHERE book_id=20150301;
UPDATE readerinfo SET balance=balance-69*0.05 WHERE card_id='11111111111111';

SELECT * FROM bookinfo WHERE price > SOME (SELECT price FROM bookinfo WHERE book_category_id=4);//some为大于他的最大值

SELECT AVG(price) FROM bookinfo//所有图书的平均价格
SELECT SUM(price) FROM bookinfo//总价格
SELECT MAX(price) FROM bookinfo
SELECT MIN(price) FROM bookinfo
SELECT book_category AS '图书类别' ,COUNT(book_id) AS '图书种类' ,SUM(store) AS '库存总和' FROM bookinfo GROUP BY book_category_id;
//按类别分组查询每种类别下有多少中图书以及每种类别图书的库存总和。as为起个别名

SELECT * FROM bookinfo WHERE book_category_id IN (SELECT category_id FROM bookcategory WHERE parent_id=2)//这时

SELECT VERSION();//当前服务器版本号
SELECT CONNECTION_ID();//连接次数
SELECT DATABASE();//当前数据库名
SELECT USER();//当前用户名

SELECT * FROM bookinfo WHERE book_金沙官网线上,category_id = ANY (SELECT category_id FROM bookcategory WHERE parent_id=2)//两句等效内层查询语句返回的是一个数据列,供外层查询语句比较操作

//MD5加密方式,介绍为下面补图
CREATE TABLE myuesr(
username VARCHAR(20),
passwordd VARCHAR(20)
);
INSERT INTO myuser VALUES('usel',MD5('pwd1'));//表中密码信息被MD5加密
SELECT * FROM myuesr//这样是看不到的
SELECT * FROM myuser WHERE username = 'usel' AND passwordd=MD5('pwd1');//要通过这样知道,如果查找到了说明密码正确

SELECT * FROM table1 WHERE EXISTS(子查询)//exist判断是否存在,存在就执行外查询

SET PASSWORD =PASSWORD('xxxxx');//通过password修改密码,这也是加密后的

CREATE TABLE readerfee(
book_id INT,
card_id CHAR(18),
actul_return_date DATE,
book_fee DECIMAL(7,3),
PRIMARY KEY(book_id,card_id)
);
SELECT book_id ,card_id,return_id FROM borrowinfo WHERE DATEDIFF(SYSDATE(),return_date) >0 AND statue='否';

本文由金沙官网线上发布于数据库,转载请注明出处:数据库Mysql的学习(六)-子查询和多表操作

您可能还会对下面的文章感兴趣: