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

下载设置mysql

网络博客
使用

在Linux里

1 service mysql start 启动服务
2 在Navicatt 中毗连Linux服务器

3

1. 第一步
2. 将所有文件上传到/opt目录下
3. 第二步
4. chmod 777 deploy-mysql.sh
5. 第三步
6. ./deploy-mysql.sh

复制代码

4 service mysql status 查看状态是否安装乐成

5 重启mysql服务
service mysql restart

– 关闭mysql服务 这个命令，同1的server命令
/etc/init.d/mysql stop
– 启动mysql服务
/etc/init.d/mysql start

---

sql语句

创建表

1. create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

复制代码

1. #创建银行客户信息表
2. use bankDB;
3. CREATE TABLE IF NOT EXISTS userInfo(
4.         customerID INT(8) PRIMARY KEY AUTO_INCREMENT COMMENT '客户编号',
5.         customerName VARCHAR(10) NOT NULL COMMENT '开户姓名',
6.         PID CHAR(18) NOT NULL COMMENT '身份证编号',
7.         telephone CHAR(11) NOT NULL COMMENT '手机号码',
8.         address VARCHAR(50) COMMENT'地址'
9. )ENGINE=INNODB CHARSET=utf8 COMMENT='银行客户信息表';

复制代码

简朴sql - 查询

1. -- 查表
2. SELECT * FROM userInfo;
3. --查address是上海
4. SELECT * FROM userInfo WHERE address ='上海';
6. --查id是4
7. SELECT * FROM userInfo WHERE id=4;
8. --查不是4
9. SELECT * FROM userInfo WHERE id<>4;
10. --查address不是上海，<>可以用于数字，也可以是字符
11. SELECT * FROM userInfo WHERE address<>'上海';
13. -- between ..and  ..   查id在3和5之间
14. SELECT * FROM userInfo WHERE id between 3 and 5;
16. -- in  包含多个无规律的
17. SELECT * FROM userInfo WHERE customerID  IN (1,3,4);
18. SELECT * FROM userInfo WHERE customerName  IN ('刘流','丁强');
20. -- 模糊查询 查以上海开头
21. SELECT * FROM userInfo WHERE address like '上海%';
22. -- 模糊查询 查以海开头,两个字的     _为占位符
23. SELECT * FROM userInfo WHERE address like '海_';
24. -- 模糊查询 查以开头第二个字是海的     _为占位符
25. SELECT * FROM userInfo WHERE address like '_海%';
26. -- 模糊查询 查以倒数第二个字是海结尾的     _为占位符
27. SELECT * FROM userInfo WHERE address like '%海_';
29. -- 模糊查询 查以浦东结尾
30. SELECT * FROM userInfo WHERE address like '%浦东';
31. -- 模糊查询 查带海字的
32. SELECT * FROM userInfo WHERE address like '%海%';

复制代码

---

逻辑运算符 and or not

1. --查address是上海，同时id 大于 2       and用于多条件
2. SELECT * FROM userInfo WHERE address='上海' and id > 2;
4. --查address是带海字的，或者 id 大于 1       or是或
5. SELECT * FROM userInfo WHERE address like '%海%' or customerID > 1;
7. --查id不等于4
8. SELECT * FROM userInfo WHERE not id=4;

复制代码

---

排序 order by asc 正序 desc 倒序

1. -- 将表以money 的正序，从小到大排序     asc可以不写
2. select * from tradeInfo order by money asc
4. -- 将表以money 的逆序，从大到小排序     asc可以不写
5. select * from tradeInfo order by money desc
7. -- 将表以多列排序    先对第一列排列，有相同的再第二列排，，
8. select * from tradeInfo order by transMoney ASC,cardID DESC;
10. -- 查询1010开头，1130结尾的银行卡的交易记录，结果按照交易时间降序排序
11. SELECT * FROM tradeInfo WHERE tradeInfo.cardID like '1010%' AND cardID LIKE '%1130' ORDER BY transDate ;

复制代码

---

聚合函数 MAX MIN SUM AVG COUNT

1. -- MAX()函数返回指定列中的最大值
2. SELECT max(transMoney) FROM tradeInfo;
4. -- MIN()函数返回指定列中的最小值
5. SELECT MIN(transMoney) FROM tradeInfo;
7. -- SUM()是一个求总和的函数，返回指定列值的总和
8. SELECT SUM(transMoney) FROM tradeInfo;
10. -- AVG()指定列数据的平均值
11. SELECT AVG(transMoney) FROM tradeInfo;
13. -- COUNT()函数统计表中包含的记录行的总数，或根据查询结果返回列中包含的数据行数
14. SELECT COUNT(transMoney) FROM tradeInfo;

复制代码

---

单组函数 YEAR MONTH DAY REPLACE SUBSTR IFNULL

1. -- YEAR()取单列的年月日
2. SELECT YEAR(transDate) FROM tradeInfo;
3. SELECT MONTH(transDate) FROM tradeInfo;
4. SELECT DAY(transDate) FROM tradeInfo;
6. -- replace()替换   字段名  要替换的   替换成
7. -- 并未真的改动表，只是改显示
8. SELECT REPLACE(tradeInfo.transType,'存入','存钱') FROM tradeInfo
10. --  SUBSTR() 截取字段   1 是从第一位开始，5 是截取长度为5位
11. SELECT SUBSTR(cardInfo.cardID ,1,5) FROM tradeInfo;
13. -- IFNULL 判断为空替换值   字段名，空被替换成
14. SELECT IFNULL(openmoney,10) FROM cardInfo;

复制代码

---

分页 limit

1. -- limit  n  查询前多少行
2. SELECT * FROM tradeInfo LIMIT 5;
4. -- limit 查询4到9行     3是从第三行后开始，6是取六行
5. SELECT * FROM tradeInfo LIMIT 3,6;

复制代码

先排序再分页 where order by limit

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

复制代码

练习

1. -- 练习：
2. -- 1）统计有多少个用户
3. SELECT COUNT(userInfo.customerID) FROM userInfo;
5. -- 2）统计银行卡信息表中有多少张银行卡
6. SELECT COUNT(cardInfo.cardID) FROM cardInfo;
8. -- 3）统计交易信息表中总共有多少笔交易
9. SELECT COUNT(tradeInfo.transDate) FROM tradeInfo;
11. -- 4）统计存款种类表中有多少中存款类型  
12. SELECT COUNT(deposit.savingID) FROM deposit;
14. -- 1）查询所有9月份的交易记录  
15. SELECT * FROM tradeInfo WHERE MONTH(transDate) = 9 ;
17. -- 2）将姓名中带有强的用户姓名替换成强强
18. SELECT REPLACE(userInfo.customerName,'强','强强') FROM userInfo;
20. -- 6）查询交易信息表中，所有交易时间（只要返回几月几号）比如2020-12-11 12:00:00 只需要返回12-11
21. SELECT MONTH(transDate),DAY(transDate) FROM tradeInfo ;
23. -- 7）查询丁强和王旺两个客户手机号后4位
24. SELECT SUBSTR(userInfo.telephone,7,4) FROM userInfo WHERE userInfo.customerName IN("丁强","王旺");
26. -- 1)查询交易金额最高的银行卡号（用limit）
27. SELECT tradeInfo.cardID FROM tradeInfo ORDER BY transMoney DESC LIMIT 1;
29. -- 2)查询存款余额前三名的银行卡信息
30. SELECT * FROM cardInfo ORDER BY cardInfo.balance DESC LIMIT 3;
32. -- 3)查询做的支取交易，按照交易金额排序，最小的三个银行卡号
33. SELECT cardID FROM tradeInfo WHERE tradeInfo.transType = '支取' ORDER BY transMoney LIMIT 3;
35. -- 4)查询交易信息表中，存入金额是第2高到第5高的交易
36. SELECT * FROM tradeInfo WHERE transType = '存入' ORDER BY transMoney DESC LIMIT 1,4;

复制代码

---

分组 对某一类分组 group by 配合函数一起用

只能查询体现到分组的那一组

1. -- 对存取类型transType 分组，只能查看分组类型这一列
2. SELECT transType FROM tradeInfo GROUP BY transType;
4. --  查询每张卡交易的最大金额、最小金额
5. SELECT cardID,MAX(transMoney),min(transMoney) FROM tradeInfo GROUP by tradeInfo.cardID;
7. --  查询存入交易的总金额
8. SELECT cardID,SUM(transMoney) FROM tradeInfo WHERE transType = '存入' GROUP by tradeInfo.cardID;
10. --  查询2023-05-06日支取交易金额的最大值和和最小值     先筛，再分
11. -- WHERE transDate like '2023-05-06%' 也可以
12. SELECT cardID,MAX(transMoney),MIN(transMoney)FROM tradeInfo WHERE SUBSTR(transDate,0,10) ='2023-05-06' AND transType ='支取' GROUP by cardID;

复制代码

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

1. -- 所有成绩都及格的学生
2. SELECT stuName FROM stu GROUP by stuName HAVING min(score) >= 60;
4. -- 没有学生不及格的科目
5. SELECT 科目 FROM stu GROUP by 科目 HAVING score >= 60;
7. -- 成绩最低都是80 的学生
8. SELECT stuName FROM stu GROUP by stuName HAVING min(score) >= 80;

复制代码

多重分组 GROUP by x,x2

1. -- 每轮所有成绩都及格的学生   先以姓名分组，再以轮次分组
2. SELECT 姓名 FROM stu GROUP by 姓名,轮次 HAVING min(score) >= 60;

复制代码

---

多表联查 嵌套

1. -- 1）查询交易金额大于2000的用户姓名和手机号
2. -- 方法1
3. -- SELECT userInfo.cardID FROM userInfo WHERE userInfo = (SELECT cardID FROM tradeInfo WHERE tradeInfo.transMoney > 2000)
4. -- 方法2
5. SELECT customerName,telephone FROM cardInfo,userInfo,tradeInfo WHERE cardInfo.customerID = userInfo.customerID AND cardInfo.cardID = tradeInfo.cardID AND tradeInfo.transMoney > 2000;
8. -- 2）查询支取交易金额大于400的用户姓名和手机号
9. SELECT customerName,telephone FROM cardInfo,userInfo,tradeInfo WHERE cardInfo.customerID = userInfo.customerID AND cardInfo.cardID = tradeInfo.cardID AND transType = '支取' AND transMoney >400;
12. --  查询账户余额大于5000的客户姓名
13. SELECT userInfo.customerName FROM userInfo WHERE userInfo.customerID = (SELECT customerID FROM cardInfo WHERE cardInfo.balance > 5000);
15. --  存款方式为1的账户的交易金额
16. SELECT transMoney FROM tradeInfo WHERE tradeInfo.cardID IN (SELECT cardID FROM cardInfo WHERE cardInfo.savingID =1);
18. -- 4）查询不同的存款类型的银行卡余额最大值大于500的银行卡卡号和余额
19. select cardID,balance from cardInfo  where savingid in (select  savingid  from 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。
下方为左毗连的体现

1. SELECT * FROM cardInfo left join tradeInfo on  cardInfo.cardID = tradeInfo.cardID ;

复制代码

练习

1. -- 3）查询交易总额超过10000的月份
2. SELECT month(transDate)  FROM tradeInfo group by  month (transDate) having SUM(transMoney) > 10000;
4. -- 2）查询手机号是135开头的客户号和银行卡余额
5. SELECT cardInfo.customerID,cardInfo.balance FROM cardInfo WHERE cardInfo.customerID IN (SELECT customerID FROM userInfo WHERE userInfo.telephone LIKE '135%');
7. -- 1）查询存入和支取的总额分别是多少
8. 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··) 字段名对应值，逐一对应

1. INSERT INTO userInfo VALUES(6,"王佳",675365343497420574,16543268749,"湖北荆门");
2. -- Column count doesn’t match value count at row 1 意思是列数目跟值数目不匹配。

复制代码

修改表

1. -- UPDATE userInfo SET customerName = '新值' WHERE customerName = '旧值'
2. UPDATE userInfo SET customerName = '王天' WHERE customerName = '王佳'

复制代码

删除表

1. -- 删除整张表
2. drop table tabname
4. --DELETE FROM 表名 WHERE 筛选条件 （列名 = '值'）
5. DELETE FROM userInfo WHERE customerName = '乔天真'

复制代码

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