MySQL basicSQL语句

1. SQL Introduction

SQL (Structured Query Language, structure化querylanguage) is 用于managementrelationships型datalibrary 标准language. 它可以用来执行各种datalibraryoperation, includingquerydata, 插入data, updatedata and deletedataetc..

SQL语句主要分 for 以 under 几class:

• DQL (dataquerylanguage) : 用于querydata, 主要includingSELECT语句
• DML (dataoperationlanguage) : 用于operationdata, includingINSERT, UPDATE, DELETE语句
• DDL (data定义language) : 用于定义datalibrarystructure, includingCREATE, ALTER, DROP语句
• DCL (data控制language) : 用于控制data访问permission, includingGRANT, REVOKE语句

2. datalibrary and 表 creation

2.1 creationdatalibrary

usingCREATE DATABASE语句creationdatalibrary:

CREATE DATABASE IF NOT EXISTS test_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

选择datalibrary:

USE test_db;

2.2 creation表

usingCREATE TABLE语句creation表:

CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

3. dataquery (SELECT)

3.1 basicquery

usingSELECT语句querydata:

-- query所 has 列
SELECT * FROM users;

-- queryspecific列
SELECT id, username, email FROM users;

-- 给列起别名
SELECT id AS user_id, username AS user_name FROM users;

-- 去重query
SELECT DISTINCT email FROM users;

3.2 条件query

usingWHERE子句添加query条件:

-- basic条件query
SELECT * FROM users WHERE id = 1;

--  many 个条件 (AND) 
SELECT * FROM users WHERE id > 5 AND username LIKE '%admin%';

--  many 个条件 (OR) 
SELECT * FROM users WHERE id < 3 OR email LIKE '%example.com';

-- 范围query (BETWEEN) 
SELECT * FROM users WHERE id BETWEEN 3 AND 8;

-- collectionquery (IN) 
SELECT * FROM users WHERE id IN (1, 3, 5, 7);

-- nullquery (IS NULL) 
SELECT * FROM users WHERE email IS NULL;

-- 非空query (IS NOT NULL) 
SELECT * FROM users WHERE email IS NOT NULL;

3.3 sortquery

usingORDER BY子句 for 结果sort:

-- 升序sort (默认) 
SELECT * FROM users ORDER BY id;

-- 显式升序sort
SELECT * FROM users ORDER BY id ASC;

-- 降序sort
SELECT * FROM users ORDER BY id DESC;

--  many 列sort
SELECT * FROM users ORDER BY created_at DESC, username ASC;

3.4 限制query

usingLIMIT子句限制结果数量:

-- 限制结果数量
SELECT * FROM users LIMIT 10;

-- 偏移量query (分页) 
SELECT * FROM users LIMIT 0, 10; -- 第1-10条
SELECT * FROM users LIMIT 10, 10; -- 第11-20条

4. data插入 (INSERT)

4.1 插入单行data

-- 完整插入
INSERT INTO users (username, email, password) VALUES ('admin', 'admin@example.com', 'password123');

-- 省略列名 (按表structure顺序) 
INSERT INTO users VALUES (NULL, 'user1', 'user1@example.com', 'password123', NULL, NULL);

4.2 插入 many 行data

INSERT INTO users (username, email, password) VALUES
('user2', 'user2@example.com', 'password123'),
('user3', 'user3@example.com', 'password123'),
('user4', 'user4@example.com', 'password123');

4.3 from other表插入data

INSERT INTO users_backup (id, username, email, password) 
SELECT id, username, email, password FROM users WHERE id > 5;

5. dataupdate (UPDATE)

5.1 basicupdate

-- update单列
UPDATE users SET password = 'newpassword123' WHERE id = 1;

-- update many 列
UPDATE users SET username = 'newadmin', email = 'newadmin@example.com' WHERE id = 1;

-- using表达式update
UPDATE users SET password = CONCAT(password, '_updated') WHERE id = 2;

5.2 Notes

updateoperation时一定要添加WHERE子句, 否则会update表in 所 has 行:

--  dangerous ！会update所 has user password
UPDATE users SET password = 'newpassword';

6. datadelete (DELETE)

6.1 basicdelete

-- delete指定行
DELETE FROM users WHERE id = 1;

-- delete符合条件  many 行
DELETE FROM users WHERE id > 10 AND id < 20;

6.2 清空表

-- 清空表 (保留表structure) 
DELETE FROM users;

-- 截断表 (更 high 效) 
TRUNCATE TABLE users;

6.3 Notes

deleteoperation时一定要添加WHERE子句, 否则会delete表in 所 has data:

--  dangerous ！会delete所 has userdata
DELETE FROM users;

7. datamodifyexample

7.1 完整example

-- creationdatalibrary
CREATE DATABASE IF NOT EXISTS company_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- usingdatalibrary
USE company_db;

-- creation员工表
CREATE TABLE IF NOT EXISTS employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(50) NOT NULL,
    salary DECIMAL(10, 2) NOT NULL,
    hire_date DATE NOT NULL
);

-- 插入data
INSERT INTO employees (name, department, salary, hire_date) VALUES
('张三', 'techniques部', 8000.00, '2023-01-15'),
('李四', '销售部', 6000.00, '2023-02-20'),
('王五', 'techniques部', 9000.00, '2023-03-10'),
('赵六', '人力resource部', 5000.00, '2023-04-05'),
('钱七', 'techniques部', 10000.00, '2023-05-18');

-- query所 has 员工
SELECT * FROM employees;

-- querytechniques部员工
SELECT * FROM employees WHERE department = 'techniques部';

-- query工资 big 于8000 员工
SELECT * FROM employees WHERE salary > 8000;

-- 按工资降序sort
SELECT * FROM employees ORDER BY salary DESC;

-- update李四 工资
UPDATE employees SET salary = 6500.00 WHERE name = '李四';

-- delete赵六 记录
DELETE FROM employees WHERE name = '赵六';

-- 最终结果
SELECT * FROM employees;