速通
拿数据库客户端啥的跑一下就行
# 创建数据库
create database if not exists mydatabase;
# 选择数据库
use mydatabase;
# 建立表 要有主键 自增 唯一索引
-- 用户表
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY comment '这是主键,自增id',
username VARCHAR(50) NOT NULL COMMENT '用户名字',
email VARCHAR(100) NOT NULL COMMENT '邮箱',
age INT COMMENT '年龄'
);
# 删除表
DROP TABLE IF EXISTS users;
# 在建立另外一张表 要有外键
-- 订单表
CREATE TABLE IF NOT EXISTS orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
-- 总位数 10 小数位数 2
total_amount DECIMAL(10, 2),
FOREIGN KEY (user_id) REFERENCES users(id)
);
# 测试表
CREATE TABLE IF NOT EXISTS datas (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
-- 总位数 10 小数位数 2
total_amount DECIMAL(10, 2),
score INT,
age INT
);
# 增加新数据
-- 插入用户数据
-- 单条插入用户数据
INSERT INTO users (id,username, email, age) VALUES (1,'Fcw', 'fcw@example.com', 66);# 主键可以修改
INSERT INTO users (username, email, age) VALUES ('Alice', 'alice@example.com', 28);
INSERT INTO users (username, email, age) VALUES ('Bob', 'bob@example.com', 35);
INSERT INTO users (username, email, age) VALUES ('David', 'david@example.com', 30);
INSERT INTO users (username, email, age) VALUES ('Eva', 'eva@example.com', 25);
INSERT INTO users (username, email, age) VALUES ('Frank', 'frank@example.com', 40);
INSERT INTO users (username, email, age) VALUES ('Grace', 'grace@example.com', 33);
INSERT INTO users (username, email, age) VALUES ('Hank', 'hank@example.com', 27);
INSERT INTO users (username, email, age) VALUES ('Ivy', 'ivy@example.com', 38);
INSERT INTO users (username, email, age) VALUES ('Jack', 'jack@example.com', 29);
-- 插入订单数据
-- 单条插入订单数据
INSERT INTO orders (user_id, total_amount) VALUES (1, 100.00);
INSERT INTO orders (user_id, total_amount) VALUES (2, 150.50);
INSERT INTO orders (user_id, total_amount) VALUES (3, 75.20);
INSERT INTO orders (user_id, total_amount) VALUES (4, 200.80);
INSERT INTO orders (user_id, total_amount) VALUES (5, 50.50);
INSERT INTO orders (user_id, total_amount) VALUES (6, 300.00);
INSERT INTO orders (user_id, total_amount) VALUES (7, 120.75);
INSERT INTO orders (user_id, total_amount) VALUES (8, 80.90);
INSERT INTO orders (user_id, total_amount) VALUES (9, 180.25);
INSERT INTO orders (user_id, total_amount) VALUES (10, 95.60);
-- 插入测试表数据
INSERT INTO datas (user_id, total_amount, score, age) VALUES (22, 40.50, 95, 15);
INSERT INTO datas (user_id, total_amount, score, age) VALUES (22, 35.75, 88, 12);
INSERT INTO datas (user_id, total_amount, score, age) VALUES (22, 42.20, 92, 18);
INSERT INTO datas (user_id, total_amount, score, age) VALUES (22, 37.80, 91, 16);
INSERT INTO datas (user_id, total_amount, score, age) VALUES (22, 41.30, 94, 14);
INSERT INTO datas (user_id, total_amount, score, age) VALUES (22, 38.60, 89, 13);
INSERT INTO datas (user_id, total_amount, score, age) VALUES (22, 39.90, 93, 17);
INSERT INTO datas (user_id, total_amount, score, age) VALUES (22, 36.45, 87, 11);
INSERT INTO datas (user_id, total_amount, score, age) VALUES (22, 40.00, 96, 19);
INSERT INTO datas (user_id, total_amount, score, age) VALUES (22, 37.10, 90, 10);
INSERT INTO datas (user_id, total_amount, score, age) VALUES (22, 43.80, 98, 20);
INSERT INTO datas (user_id, total_amount, score, age) VALUES (22, 39.20, 91, 16);
# 删除数据
-- 方法一:由于有外键约束,需要先删除orders中的相关数据
DELETE FROM orders WHERE orders.user_id=1;
DELETE FROM users WHERE users.id=1;
-- 方法二:与一类似,不过在创建外键索引的时候,后面加上下面内容,删除users中的行数据时,有关表的数据也会被删除、
# FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
# 修改数据
UPDATE users SET age = 888 WHERE users.id = 2;
# 查找数据
SELECT username, email,age FROM users WHERE age > 35;
# 排序
-- 默认升序
SELECT age
FROM datas
ORDER BY age;
-- 降序
SELECT username, age
FROM users
ORDER BY age DESC;
-- 多列排序
SELECT username, age
FROM users
ORDER BY age,email DESC;
# 单条数据插入
-- 插入一条用户数据
INSERT INTO users (username, email, age) VALUES
('John', 'john@example.com', 32);
# 接下来 我要学习where having 语句
-- 查询年龄大于 25 岁的用户
SELECT * FROM users WHERE age > 25;
-- 查询年龄大于 25 岁的用户,并计算其平均年龄
SELECT username, AVG(age) as average_age
FROM users
GROUP BY username
HAVING AVG(age) > 25;
# 写一个死锁案例
-- 事务1
START TRANSACTION;
UPDATE users SET age = 30 WHERE id = 1; -- 试图更新用户1的信息
-- 事务2
START TRANSACTION;
UPDATE users SET age = 25 WHERE id = 2; -- 试图更新用户2的信息
-- 事务1
-- 尝试获取用户2的锁
UPDATE users SET age = 35 WHERE id = 2;
-- 结束事务
COMMIT ;
-- 事务2
-- 尝试获取用户1的锁,但由于被事务1占用,造成死锁
UPDATE users SET age = 28 WHERE id = 1;
COMMIT ;
# alter 语句,修改数据库的表结构
-- 添加
ALTER TABLE datas
ADD COLUMN new_column INT;
-- 删除
ALTER TABLE datas
DROP COLUMN new_column;
-- 修改
ALTER TABLE datas
MODIFY COLUMN new_column char(20);
-- 添加主键
ALTER TABLE datas
ADD PRIMARY KEY (user_id);
-- 删除主键
ALTER TABLE datas
DROP PRIMARY KEY;