mysql基础速通

速通

拿数据库客户端啥的跑一下就行

# 创建数据库
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;