1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
|
//创建数据库
create database test_wzh character set utf8mb4 collate utf8mb4_general_ci;
//utf8mb4_general_ci 不区分字符串大小写
//utf8mb4_unicode_ci 区分字符串大小写
//删除数据库
drop database test;
//选择数据库
USE database_name;
mysql -u your_username -p -D your_database
//创建数据库
CREATE TABLE IF NOT EXISTS `runoob_tbl`(
`runoob_id` INT UNSIGNED AUTO_INCREMENT,
`runoob_title` VARCHAR(100) NOT NULL,
`runoob_author` VARCHAR(40) NOT NULL,
`submission_date` DATE,
PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
show columns from user_list;
//插入数据
//显式插入数据
INSERT INTO user_list (id, name, years, is_auth)
VALUES (NULL, 'test', '2006-05-17', TRUE);
//必须提供所有数据
INSERT INTO user_list
VALUES (NULL, 'test', '2006-05-17', TRUE);
//可以同插入多条数据
INSERT INTO users (username, email, birthdate, is_active)
VALUES
('test1', '[email protected]', '1985-07-10', true),
('test2', '[email protected]', '1988-11-25', false),
('test3', '[email protected]', '1993-05-03', true);
//查询数据库
SELECT name, is_auth FROM user_list;
SELECT * FROM user_list;
-- 添加 ORDER BY 子句，按照某列的升序排序
SELECT * FROM users ORDER BY birthdate;
-- 添加 ORDER BY 子句，按照某列的降序排序
SELECT * FROM users ORDER BY birthdate DESC;
-- 添加 LIMIT 子句，限制返回的行数
SELECT * FROM users LIMIT 10;
//ASC 表示升序（默认），DESC 表示降序。
-- 使用 AND 运算符和通配符
SELECT * FROM users WHERE username LIKE 'j%' AND is_active = TRUE;
-- 使用 OR 运算符
SELECT * FROM users WHERE is_active = TRUE OR birthdate < '1990-01-01';
-- 使用 IN 子句
SELECT * FROM users WHERE birthdate IN ('1990-01-01', '1992-03-15', '1993-05-03');
//更新数据
update user_list set name = 'wzh' where name = 'aobara';
UPDATE customers
SET total_purchases = (
SELECT SUM(amount)
FROM orders
WHERE orders.customer_id = customers.customer_id
)
WHERE customer_type = 'Premium';
//删除数据
DELETE FROM students
WHERE graduation_year = 2021;
DELETE FROM students；
//UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合，并去除重复的行。
SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;
//不剔除重复项
SELECT country, name FROM Websites
WHERE country='CN'UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country;
//排序
多列排序：
SELECT * FROM employees
ORDER BY department_id ASC, hire_date DESC;
使用数字表示列的位置：
SELECT first_name, last_name, salary
FROM employees
ORDER BY 3 DESC, 1 ASC;
使用表达式排序：
SELECT product_name, price * discount_rate AS discounted_price
FROM products
ORDER BY discounted_price DESC;
//分组
按客户和产品分组，统计每个客户购买每种产品的次数
SELECT customer, product, COUNT(*) AS times_bought
FROM orders
GROUP BY customer, product;
aggregate_function()：常用的聚合函数有：
COUNT()：计数
SUM()：求和
AVG()：平均值
MAX()：最大值
MIN()：最小值
//事务
START TRANSACTION;
-- 执行一些SQL语句
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
-- 判断是否要提交还是回滚
IF (条件) THEN
COMMIT; -- 提交事务
ELSE
ROLLBACK; -- 回滚事务
END IF;-- 开始事务
START TRANSACTION;
-- 执行一些SQL语句
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
-- 判断是否要提交还是回滚
IF (条件) THEN
COMMIT; -- 提交事务
ELSE
ROLLBACK; -- 回滚事务
END IF;
//创建外键约束
ALTER TABLE blog_article
ADD FOREIGN KEY(user_id) REFERENCES blog_auth(id);
|