MySQL入门

一、查看信息

1.基本信息

1.服务器版本信息

SELECT VERSION()  

2.当前数据库名 (或者返回空)

SELECT DATABASE()

3.当前用户名

SELECT USER()

4.服务器状态

SHOW STATUS

5.服务器配置变量

SHOW VARIABLES  

2.数据表信息

查看表结构

DESC table_name;

查看表的创建语句

SHOW CREATE TABLE table_name;

查看表的行数

SELECT COUNT(*) FROM table_name;

二、对表的简单操作

1.基本操作

1.创建表

CREATE TABLE table_name (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    column3 VARCHAR(50),
    column4 DECIMAL(10, 2) -- 列名 + 数据类型
);

2.添加数据

INSERT INTO table_name (column1, column2, column3, ...) VALUES -- 对应前一个括号的值
(value1, value2, value3, ...),
(value1, value2, value3, ...);

3.更改数据

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

4.删除表

DROP table_name;

5.复制表结构（不包括数据）

CREATE TABLE new_table_name LIKE original_table_name;

6.复制表结构和数据

CREATE TABLE new_table_name AS SELECT * FROM original_table_name;

CREATE TABLE new_table_name LIKE original_table_name;
INSERT INTO new_table_name SELECT * FROM original_table_name;

7.将表移动到另一个数据库并重命名

RENAME TABLE database_name.old_table_name TO new_database_name.new_table_name;

8.更改表名

RENAME TABLE old_table_name TO new_table_name;

2.对列操作

1.添加列名

ALTER TABLE table_name ADD new_column_name data_type;

2.修改列信息

ALTER TABLE table_name MODIFY COLUMN old_column_name new_data_type;

3.修改列名

ALTER TABLE table_name CHANGE old_column_name new_column_name column_definition;

4.删除列

ALTER TABLE table_name DROP COLUMN column_name;

5.更改自增列下一个数据

ALTER TABLE table_name AUTO_INCREMENT = start_value;

3.对行操作

删除行

DELETE FROM table_name
WHERE condition; -- 没有该句删除所有行

4.对信息操作

1.添加主键

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

2.删除主键

ALTER TABLE table_name DROP PRIMARY KEY;

3.添加外键

ALTER TABLE table_name ADD CONSTRAINT fk_constraint_name FOREIGN KEY (column_name)
REFERENCES other_table(other_column);

4.删除外键

ALTER TABLE table_name DROP FOREIGN KEY fk_constraint_name;

5.连接操作

左连接、右连接、内连接

SELECT * FROM table_name1
LEFT JOIN table_name2
ON table_name1.column = table_name2.columnion;

SELECT column FROM table_name1
UNION SELECT column FROM table_name2;

6.组合操作

重点看各个语句书写顺序

SELECT DISTINCT
    AVG(column)  new_column1,
    MIN(column) AS new_column2,
    COALESCE(column, 0), -- 将该列null值显示为0
    SUM(CASE WHEN gender = 'Male' THEN 1 ELSE 0 END)  num_male_employees
FROM table_name t1
LEFT JOIN departments ON t1.department_id = t1.department_id
WHERE hire_date >= '2024-01-01' AND column LIKE 'c%' AND ... -- and连接条件
GROUP BY department, job_title, ... -- ,连接列名
HAVING AVG(salary) > 50000 AND COUNT(employee_id) > 3 AND ...
ORDER BY department, job_title, ... ;

三、对表的其他操作

1.去重

distinct：

SELECT DISTINCT column1, column2, ... FROM table_name
WHERE condition;

分组：

SELECT country, SUM(deaths) FROM covid_month 
GROUP BY country;

 

2.查找

rlike：后面是正则表达式

SELECT * FROM table_name1 WHERE column RLIKE '^[0-2]'; -- 0,1,2

regexp：

SELECT column1, column2, ... FROM table_name
WHERE column_name REGEXP 'pattern';

3.条件

case：例子

SELECT 
    column1,
    column2,
    CASE
        WHEN score >= 90 THEN 'A'-- 若列条件则输出
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        WHEN score >= 60 THEN 'D'
        ELSE 'F'
    END AS new_column1
FROM
    table_name
HAVING score >= 60; -- 只输出符合条件的项

4.with

和直接select差不多，比它快 

WITH w1 AS (
select name, value from diction where code = 's_model'
), w2 AS (
select name,value from diction where code = 'd_model'
), w3 AS (
select project_name,id from project
)
select * w1
left join w2 pd on w1.id = pd.device_id
left join w3 on w3.id = pd.project_id
where w1.device_type in (1,2);

5.分表区

1.创建分区 

ALTER TABLE table_name
PARTITION BY RANGE (year(sale_date))
(
    PARTITION p0 VALUES LESS THAN (2010),
    PARTITION p1 VALUES LESS THAN (2015),
    PARTITION p2 VALUES LESS THAN (2020),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

2.删除某个分区

ALTER TABLE table_name DROP PARTITION partition_name;

3.删除所有分区

ALTER TABLE table_name REMOVE PARTITIONING;

6.临时表

1.创建临时表

CREATE TEMPORARY TABLE temp_orders_name AS
SELECT * FROM table_name WHERE condition;

2.查询临时表

SELECT * FROM temp_orders_name;

3.插入数据到临时表

INSERT INTO temp_orders_name (student_id,name,score)
VALUES (9, Kitty, '92');

4.删除临时表

DROP TEMPORARY TABLE IF EXISTS temp_orders_name;

7.事务

大例子

-- 事务 例子
drop table accounts;
drop table transactions;

CREATE TABLE accounts (
    account_id INT AUTO_INCREMENT PRIMARY KEY,
    balance DECIMAL(10, 2) NOT NULL DEFAULT 0.00
);

CREATE TABLE transactions (
    transaction_id INT AUTO_INCREMENT PRIMARY KEY,
    from_account INT NOT NULL,
    to_account INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (from_account) REFERENCES accounts(account_id),
    FOREIGN KEY (to_account) REFERENCES accounts(account_id)
);

-- 插入第一个账户
INSERT INTO accounts (balance) VALUES (1000.00);

-- 插入第二个账户
INSERT INTO accounts (balance) VALUES (500.00);

-- 假设账户1和账户2已经存在
INSERT INTO transactions (from_account, to_account, amount) 
VALUES (1, 2, 200.00);

select * from accounts;
select * from transactions;

-- 开始事务
START TRANSACTION;

-- 从账户A减少金额
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- 向账户B增加金额
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

-- 将转账记录插入到transactions表中
INSERT INTO transactions (from_account, to_account, amount) VALUES (1, 2, 100);

-- 检查是否有错误，如果没有，提交事务
-- 如果有错误，例如某个账户不存在，那么MySQL会自动回滚事务
COMMIT;

select * from accounts;
select * from transactions;