论坛 › 物联网 › 学习测试8-数据库mysql操作

盛世宏图 发表于 2024-7-12 23:41:25

学习测试8-数据库mysql操作

下载设置mysql

网络博客
使用

在Linux里

1 service mysql start 启动服务
2 在Navicatt 中毗连Linux服务器
https://i-blog.csdnimg.cn/direct/54ffbe07aa924eef9298a62b68efe1e9.png
3
第一步
将所有文件上传到/opt目录下
第二步
chmod 777 deploy-mysql.sh
第三步
./deploy-mysql.sh
4 service mysql status 查看状态是否安装乐成
https://i-blog.csdnimg.cn/direct/bba54dc808264e558ad61d6e569de89d.png
5 重启mysql服务
service mysql restart
https://i-blog.csdnimg.cn/direct/9143a8ffe3ee48fa85283c10c29c0c2b.png
– 关闭mysql服务 这个命令，同1的server命令
/etc/init.d/mysql stop
– 启动mysql服务
/etc/init.d/mysql start
sql语句

创建表

create table tabname(col1 type1 ,col2 type2 ,..)
#创建银行客户信息表
use bankDB;
CREATE TABLE IF NOT EXISTS userInfo(
        customerID INT(8) PRIMARY KEY AUTO_INCREMENT COMMENT '客户编号',
        customerName VARCHAR(10) NOT NULL COMMENT '开户姓名',
        PID CHAR(18) NOT NULL COMMENT '身份证编号',
        telephone CHAR(11) NOT NULL COMMENT '手机号码',
        address VARCHAR(50) COMMENT'地址'
)ENGINE=INNODB CHARSET=utf8 COMMENT='银行客户信息表';
简朴sql - 查询

-- 查表
SELECT * FROM userInfo;
--查address是上海
SELECT * FROM userInfo WHERE address ='上海';

--查id是4
SELECT * FROM userInfo WHERE id=4;
--查不是4
SELECT * FROM userInfo WHERE id<>4;
--查address不是上海，<>可以用于数字，也可以是字符
SELECT * FROM userInfo WHERE address<>'上海';

-- between ..and..   查id在3和5之间
SELECT * FROM userInfo WHERE id between 3 and 5;

-- in包含多个无规律的
SELECT * FROM userInfo WHERE customerIDIN (1,3,4);
SELECT * FROM userInfo WHERE customerNameIN ('刘流','丁强');

-- 模糊查询 查以上海开头
SELECT * FROM userInfo WHERE address like '上海%';
-- 模糊查询 查以海开头,两个字的   _为占位符
SELECT * FROM userInfo WHERE address like '海_';
-- 模糊查询 查以开头第二个字是海的   _为占位符
SELECT * FROM userInfo WHERE address like '_海%';
-- 模糊查询 查以倒数第二个字是海结尾的   _为占位符
SELECT * FROM userInfo WHERE address like '%海_';

-- 模糊查询 查以浦东结尾
SELECT * FROM userInfo WHERE address like '%浦东';
-- 模糊查询 查带海字的
SELECT * FROM userInfo WHERE address like '%海%';
逻辑运算符 and or not

--查address是上海，同时id 大于 2       and用于多条件
SELECT * FROM userInfo WHERE address='上海' and id > 2;

--查address是带海字的，或者 id 大于 1       or是或
SELECT * FROM userInfo WHERE address like '%海%' or customerID > 1;

--查id不等于4
SELECT * FROM userInfo WHERE not id=4;
排序 order by asc 正序 desc 倒序

-- 将表以money 的正序，从小到大排序   asc可以不写
select * from tradeInfo order by money asc

-- 将表以money 的逆序，从大到小排序   asc可以不写
select * from tradeInfo order by money desc

-- 将表以多列排序    先对第一列排列，有相同的再第二列排，，
select * from tradeInfo order by transMoney ASC,cardID DESC;

-- 查询1010开头，1130结尾的银行卡的交易记录，结果按照交易时间降序排序
SELECT * FROM tradeInfo WHERE tradeInfo.cardID like '1010%' AND cardID LIKE '%1130' ORDER BY transDate ;
聚合函数 MAX MIN SUM AVG COUNT

-- MAX()函数返回指定列中的最大值
SELECT max(transMoney) FROM tradeInfo;

-- MIN()函数返回指定列中的最小值
SELECT MIN(transMoney) FROM tradeInfo;

-- SUM()是一个求总和的函数，返回指定列值的总和
SELECT SUM(transMoney) FROM tradeInfo;

-- AVG()指定列数据的平均值
SELECT AVG(transMoney) FROM tradeInfo;

-- COUNT()函数统计表中包含的记录行的总数，或根据查询结果返回列中包含的数据行数
SELECT COUNT(transMoney) FROM tradeInfo;
单组函数 YEAR MONTH DAY REPLACE SUBSTR IFNULL

-- YEAR()取单列的年月日
SELECT YEAR(transDate) FROM tradeInfo;
SELECT MONTH(transDate) FROM tradeInfo;
SELECT DAY(transDate) FROM tradeInfo;

-- replace()替换   字段名要替换的   替换成
-- 并未真的改动表，只是改显示
SELECT REPLACE(tradeInfo.transType,'存入','存钱') FROM tradeInfo

--SUBSTR() 截取字段   1 是从第一位开始，5 是截取长度为5位
SELECT SUBSTR(cardInfo.cardID ,1,5) FROM tradeInfo;

-- IFNULL 判断为空替换值   字段名，空被替换成
SELECT IFNULL(openmoney,10) FROM cardInfo;
分页 limit

-- limitn查询前多少行
SELECT * FROM tradeInfo LIMIT 5;

-- limit 查询4到9行   3是从第三行后开始，6是取六行
SELECT * FROM tradeInfo LIMIT 3,6;
先排序再分页 where order by limit

-- 查询1130结尾的银行卡的交易记录，结果按照交易时间降序排序,显示4行
SELECT * FROM user WHERE userId LIKE '%30' ORDER BY transDatelimit 4 ;
练习

-- 练习：
-- 1）统计有多少个用户
SELECT COUNT(userInfo.customerID) FROM userInfo;

-- 2）统计银行卡信息表中有多少张银行卡
SELECT COUNT(cardInfo.cardID) FROM cardInfo;

-- 3）统计交易信息表中总共有多少笔交易
SELECT COUNT(tradeInfo.transDate) FROM tradeInfo;

-- 4）统计存款种类表中有多少中存款类型
SELECT COUNT(deposit.savingID) FROM deposit;

-- 1）查询所有9月份的交易记录
SELECT * FROM tradeInfo WHERE MONTH(transDate) = 9 ;

-- 2）将姓名中带有强的用户姓名替换成强强
SELECT REPLACE(userInfo.customerName,'强','强强') FROM userInfo;

-- 6）查询交易信息表中，所有交易时间（只要返回几月几号）比如2020-12-11 12:00:00 只需要返回12-11
SELECT MONTH(transDate),DAY(transDate) FROM tradeInfo ;

-- 7）查询丁强和王旺两个客户手机号后4位
SELECT SUBSTR(userInfo.telephone,7,4) FROM userInfo WHERE userInfo.customerName IN("丁强","王旺");

-- 1)查询交易金额最高的银行卡号（用limit）
SELECT tradeInfo.cardID FROM tradeInfo ORDER BY transMoney DESC LIMIT 1;

-- 2)查询存款余额前三名的银行卡信息
SELECT * FROM cardInfo ORDER BY cardInfo.balance DESC LIMIT 3;

-- 3)查询做的支取交易，按照交易金额排序，最小的三个银行卡号
SELECT cardID FROM tradeInfo WHERE tradeInfo.transType = '支取' ORDER BY transMoney LIMIT 3;

-- 4)查询交易信息表中，存入金额是第2高到第5高的交易
SELECT * FROM tradeInfo WHERE transType = '存入' ORDER BY transMoney DESC LIMIT 1,4;
分组 对某一类分组 group by 配合函数一起用

只能查询体现到分组的那一组
-- 对存取类型transType 分组，只能查看分组类型这一列
SELECT transType FROM tradeInfo GROUP BY transType;

--查询每张卡交易的最大金额、最小金额
SELECT cardID,MAX(transMoney),min(transMoney) FROM tradeInfo GROUP by tradeInfo.cardID;

--查询存入交易的总金额
SELECT cardID,SUM(transMoney) FROM tradeInfo WHERE transType = '存入' GROUP by tradeInfo.cardID;

--查询2023-05-06日支取交易金额的最大值和和最小值   先筛，再分
-- WHERE transDate like '2023-05-06%' 也可以
SELECT cardID,MAX(transMoney),MIN(transMoney)FROM tradeInfo WHERE SUBSTR(transDate,0,10) ='2023-05-06' AND transType ='支取' GROUP by cardID;

先分组，再进行筛选 group by 列名 having 过滤条件

https://i-blog.csdnimg.cn/direct/1d941f775fdf42c09959f6d971e18697.png
-- 所有成绩都及格的学生
SELECT stuName FROM stu GROUP by stuName HAVING min(score) >= 60;

-- 没有学生不及格的科目
SELECT 科目 FROM stu GROUP by 科目 HAVING score >= 60;

-- 成绩最低都是80 的学生
SELECT stuName FROM stu GROUP by stuName HAVING min(score) >= 80;
多重分组 GROUP by x,x2

https://i-blog.csdnimg.cn/direct/9230d72b830f44048f598d96b249e64a.png
-- 每轮所有成绩都及格的学生   先以姓名分组，再以轮次分组
SELECT 姓名 FROM stu GROUP by 姓名,轮次 HAVING min(score) >= 60;
多表联查 嵌套

https://i-blog.csdnimg.cn/direct/3fd0b19602054cfbb8718364fc536a29.png#pic_center
-- 1）查询交易金额大于2000的用户姓名和手机号
-- 方法1
-- SELECT userInfo.cardID FROM userInfo WHERE userInfo = (SELECT cardID FROM tradeInfo WHERE tradeInfo.transMoney > 2000)
-- 方法2
SELECT customerName,telephone FROM cardInfo,userInfo,tradeInfo WHERE cardInfo.customerID = userInfo.customerID AND cardInfo.cardID = tradeInfo.cardID AND tradeInfo.transMoney > 2000;


-- 2）查询支取交易金额大于400的用户姓名和手机号
SELECT customerName,telephone FROM cardInfo,userInfo,tradeInfo WHERE cardInfo.customerID = userInfo.customerID AND cardInfo.cardID = tradeInfo.cardID AND transType = '支取' AND transMoney >400;


--查询账户余额大于5000的客户姓名
SELECT userInfo.customerName FROM userInfo WHERE userInfo.customerID = (SELECT customerID FROM cardInfo WHERE cardInfo.balance > 5000);

--存款方式为1的账户的交易金额
SELECT transMoney FROM tradeInfo WHERE tradeInfo.cardID IN (SELECT cardID FROM cardInfo WHERE cardInfo.savingID =1);

-- 4）查询不同的存款类型的银行卡余额最大值大于500的银行卡卡号和余额
select cardID,balance from cardInfowhere savingid in (selectsavingidfrom cardInfo GROUP BY savingid having max(balance)>500);
连表 左毗连 右毗连

左毗连称之为左外毗连 右毗连称之为右外毗连 这俩个毗连都是属于"外毗连"
左毗连关键字：left join 表名 on 条件 / left outer 表名 join on 条件
右毗连关键字：right join 表名 on 条件/ right outer 表名 join on 条件
左毗连说明：left join 是left outer join的简写，左(外)毗连，左表(a_table)的记录将会全部表示出来， 而右表(b_table)只会体现符合搜刮条件的记录。右表记录不敷的地方均为NULL。
右毗连说明：right join是right outer join的简写，与左(外)毗连相反，右(外)毗连，左表(a_table)只会体现符合搜刮条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不敷的地方均为NULL。
下方为左毗连的体现
https://i-blog.csdnimg.cn/direct/598ad7f50f7547fb927fc9e591fa464c.png#pic_center
SELECT * FROM cardInfo left join tradeInfo oncardInfo.cardID = tradeInfo.cardID ;

练习

-- 3）查询交易总额超过10000的月份
SELECT month(transDate)FROM tradeInfo group bymonth (transDate) having SUM(transMoney) > 10000;

-- 2）查询手机号是135开头的客户号和银行卡余额
SELECT cardInfo.customerID,cardInfo.balance FROM cardInfo WHERE cardInfo.customerID IN (SELECT customerID FROM userInfo WHERE userInfo.telephone LIKE '135%');

-- 1）查询存入和支取的总额分别是多少
SELECT transType,SUM(tradeInfo.transMoney) FROM tradeInfo GROUP by tradeInfo.transType;

增长字段

insert into 表名 values (值1，值2，值3··) 除了自增，其他的必须全部字段名全都输入
insert into 表名(列名，列名2，列名3··) values (值1，值2，值3··) 字段名对应值，逐一对应
INSERT INTO userInfo VALUES(6,"王佳",675365343497420574,16543268749,"湖北荆门");
-- Column count doesn’t match value count at row 1 意思是列数目跟值数目不匹配。

修改表

-- UPDATE userInfo SET customerName = '新值' WHERE customerName = '旧值'
UPDATE userInfo SET customerName = '王天' WHERE customerName = '王佳'
删除表

-- 删除整张表
drop table tabname

--DELETE FROM 表名 WHERE 筛选条件 （列名 = '值'）
DELETE FROM userInfo WHERE customerName = '乔天真'

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

查看完整版本: 学习测试8-数据库mysql操作