LY'Blog

fatSheep大约 3 分钟

mysql

建表/建库/插入数据

建库

create database if not exists `jiudian`;
use `jiudian`;

效果 ![[Pasted image 20231225180030.png]]

建表

-- 创建管理员表
CREATE TABLE if not exists Manager (
    Mno INT PRIMARY KEY,
    Sname VARCHAR(50),
    Ssex VARCHAR(10)
);

-- 创建用户表
CREATE TABLE if not exists User (
    Uid INT PRIMARY KEY,
    Uname VARCHAR(50),
    Usex VARCHAR(10)
);

-- 创建房间表
CREATE TABLE if not exists Room (
    Rid INT PRIMARY KEY,
    Raddress VARCHAR(100),
    Rtype VARCHAR(50),
    Rprice DECIMAL(10, 2)
);

效果 ![[Pasted image 20231225180148.png]]

插入数据

-- 插入管理员表的数据
INSERT INTO Manager (Mno, Sname, Ssex) VALUES
(1, '张三', '男'),
(2, '李四', '男'),
(3, '王五', '男'),
(4, '赵六', '女'),
(5, '钱七', '女');

-- 插入用户表的数据
INSERT INTO User (Uid, Uname, Usex) VALUES
(1, 'dr', '女'),
(2, 'zxy', '男'),
(3, 'zfh', '女'),
(4, 'ak47', '男'),
(5, 'zz', '男');

-- 插入房间表的数据
INSERT INTO Room (Rid, Raddress, Rtype, Rprice) VALUES
(1, '123号主街', '标准间', 200.00),
(2, '456号东街', '豪华套房', 500.00),
(3, '789号南街', '单人间', 100.00),
(4, '101号西街', '双人间', 150.00),
(5, '111号北街', '三人间', 180.00);

效果 ![[Pasted image 20231225180517.png]]

增删改查

查询所有管理员数据

select * from jiudian.user;

![[Pasted image 20231225181104.png]]

查询所有价格在100-200之间的房间：

SELECT * FROM room where Rprice between 150 and 200;

![[Pasted image 20231225181156.png]]

更新管理员数据

UPDATE Manager
SET Sname = '李五'
WHERE Mno = 2;

![[Pasted image 20231225181315.png]]

删除一条管理员数据

DELETE FROM Manager
WHERE Mno = 5

![[Pasted image 20231225181416.png]]

修改Uname为 zz 的用户性别为女

update User
set Usex='女'
where Uname='zz';

![[Pasted image 20231225182250.png]] 6. 统计酒店男女人数

SELECT Usex, COUNT(*) as count
FROM User
GROUP BY Usex;

![[Pasted image 20231225181558.png]] 7. 更新用户数据：

UPDATE User
SET Uname = '李小红'
WHERE Uid = 2;

![[Pasted image 20231225183827.png]] 8. 删除一条用户数据：

DELETE
FROM User
WHERE Uid = 6;

查询管理员姓名中包含'张'的数据：

SELECT *
FROM Manager
WHERE Sname like '%张%';

![[Pasted image 20231225184032.png]] 10. 查询用户性别为'女'的数据：

SELECT *
FROM User
WHERE Usex = '女';

![[Pasted image 20231225184041.png]]

查询房间类型为'标准间'且价格小于300的数据：

SELECT *
FROM Room
WHERE Rtype = '标准间'
AND Rprice < 300.00;

![[Pasted image 20231225184122.png]] 12. 查询数据库当前用户

SELECT CURRENT_USER();

![[Pasted image 20231225184332.png]]

查询root权限

SHOW GRANTS FOR 'root'@'localhost';

![[Pasted image 20231225184356.png]] 14. 查询所有用户

SELECT User
FROM mysql.user;

![[Pasted image 20231225184527.png]] 15. 删除user表的usex字段

ALTER TABLE user DROP COLUMN Usex;

![[Pasted image 20231225184704.png]]

Redis

HMSET Manager:1 Mno 1 Sname "张三" Ssex "男"
HMSET Manager:2 Mno 2 Sname "李四" Ssex "男"
HMSET Manager:3 Mno 3 Sname "王五" Ssex "男"
HMSET Manager:4 Mno 4 Sname "赵六" Ssex "女"
HMSET Manager:5 Mno 5 Sname "钱七" Ssex "女"

HMSET User:1 Uid 1 Uname "dr" Usex "女"
HMSET User:2 Uid 2 Uname "zxy" Usex "男"
HMSET User:3 Uid 3 Uname "zfh" Usex "女"
HMSET User:4 Uid 4 Uname "ak47" Usex "男"
HMSET User:5 Uid 5 Uname "zz" Usex "男"


HMSET Room:1 Rid 1 Raddress "123号主街" Rtype "标准间" Rprice 200.00
HMSET Room:2 Rid 2 Raddress "456号东街" Rtype "豪华套房" Rprice 500.00
HMSET Room:3 Rid 3 Raddress "789号南街" Rtype "单人间" Rprice 100.00
HMSET Room:4 Rid 4 Raddress "101号西街" Rtype "双人间" Rprice 150.00
HMSET Room:5 Rid 5 Raddress "111号北街" Rtype "三人间" Rprice 180.00

![[Pasted image 20231225185024.png]]

MongoDB

db.Manager.insertMany([
  { Mno: 1, Sname: "张三", Ssex: "男" },
  { Mno: 2, Sname: "李四", Ssex: "男" },
  { Mno: 3, Sname: "王五", Ssex: "男" },
  { Mno: 4, Sname: "赵六", Ssex: "女" },
  { Mno: 5, Sname: "钱七", Ssex: "女" }
])

db.User.insertMany([
  { Uid: 1, Uname: "dr", Usex: "女" },
  { Uid: 2, Uname: "zxy", Usex: "男" },
  { Uid: 3, Uname: "zfh", Usex: "女" },
  { Uid: 4, Uname: "ak47", Usex: "男" },
  { Uid: 5, Uname: "zz", Usex: "男" }
])

db.Room.insertMany([
  { Rid: 1, Raddress: "123号主街", Rtype: "标准间", Rprice: 200.00 },
  { Rid: 2, Raddress: "456号东街", Rtype: "豪华套房", Rprice: 500.00 },
  { Rid: 3, Raddress: "789号南街", Rtype: "单人间", Rprice: 100.00 },
  { Rid: 4, Raddress: "101号西街", Rtype: "双人间", Rprice: 150.00 },
  { Rid: 5, Raddress: "111号北街", Rtype: "三人间", Rprice: 180.00 }
])

![[Pasted image 20231225185128.png]]