MySQL 创建数据库
CREATE DATABASE 数据库名;
MySQL 删除数据库
DROP DATABASE <database_name>; -- 直接删除数据库,不检查是否存在
或
DROP DATABASE [IF EXISTS] <database_name>;
参数说明:
IF EXISTS
是一个可选的子句,表示如果数据库存在才执行删除操作,避免因为数据库不存在而引发错误。database_name
是你要删除的数据库的名称。
MySQL 选择数据库
USE database_name;
MySQL 数据类型
1.数值类型
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
TINYINT | 1 Bytes | (-128,127) | (0,255) | 小整数值 |
SMALLINT | 2 Bytes | (-32 768,32 767) | (0,65 535) | 大整数值 |
MEDIUMINT | 3 Bytes | (-8 388 608,8 388 607) | (0,16 777 215) | 大整数值 |
INT或INTEGER | 4 Bytes | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) | 大整数值 |
BIGINT | 8 Bytes | (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | 极大整数值 |
FLOAT | 4 Bytes | (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) | 0,(1.175 494 351 E-38,3.402 823 466 E+38) | 单精度 浮点数值 |
DOUBLE | 8 Bytes | (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
DECIMAL | 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
2.日期和时间类型
类型 | 大小 ( bytes) | 范围 | 格式 | 用途 |
---|---|---|---|---|
DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 1901/2155 | YYYY | 年份值 |
DATETIME | 8 | '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59' | YYYY-MM-DD hh:mm:ss | 混合日期和时间值 |
TIMESTAMP | 4 | '1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC 结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYY-MM-DD hh:mm:ss | 混合日期和时间值,时间戳 |
3.字符串类型
类型 | 大小 | 用途 |
---|---|---|
CHAR | 0-255 bytes | 定长字符串 |
VARCHAR | 0-65535 bytes | 变长字符串 |
TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
TINYTEXT | 0-255 bytes | 短文本字符串 |
BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
TEXT | 0-65 535 bytes | 长文本数据 |
MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
MySQL 创建数据表
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
...
);
参数说明:
table_name
是你要创建的表的名称。column1
,column2
, ... 是表中的列名。datatype
是每个列的数据类型。- AUTO_INCREMENT 定义列为自增的属性,一般用于主键,数值会自动加 1。
- PRIMARY KEY 关键字用于定义列为主键。 您可以使用多列来定义主键,列间以逗号 , 分隔。
- ENGINE 设置存储引擎,CHARSET 设置编码。
- 如果你不想字段为空可以设置字段的属性为 NOT NULL
-- 删除表,如果存在的话
DROP TABLE IF EXISTS mytable;
-- 直接删除表,不检查是否存在
DROP TABLE mytable;
参数说明:
table_name
是要删除的表的名称。IF EXISTS
是一个可选的子句,表示如果表存在才执行删除操作,避免因为表不存在而引发错误。
MySQL 插入数据
INSERT INTO users (username, email, birthdate, is_active)
VALUES ('test', 'test@runoob.com', '1990-01-01', true);
INSERT INTO users
VALUES (NULL,'test', 'test@runoob.com', '1990-01-01', true);
INSERT INTO users (username, email, birthdate, is_active)
VALUES
('test1', 'test1@runoob.com', '1985-07-10', true),
('test2', 'test2@runoob.com', '1988-11-25', false),
('test3', 'test3@runoob.com', '1993-05-03', true);
username
: 用户名,字符串类型。email
: 邮箱地址,字符串类型。birthdate
: 用户生日, 日期类型。is_active
: 是否已激活,布尔类型。- 这里,NULL 是用于自增长列的占位符,表示系统将为 id 列生成一个唯一的值。
- 以上代码将在 users 表中插入三行数据。
MySQL 查询数据
1.MySQL WHERE 子句
WHERE 子句用于在 MySQL 中过滤查询结果,只返回满足特定条件的行。
操作符 | 描述 | 实例 |
---|---|---|
= | 等号,检测两个值是否相等,如果相等返回true | (A = B) 返回false。 |
<>, != | 不等于,检测两个值是否相等,如果不相等返回true | (A != B) 返回 true。 |
> | 大于号,检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true | (A > B) 返回false。 |
< | 小于号,检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true | (A < B) 返回 true。 |
>= | 大于等于号,检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true | (A >= B) 返回false。 |
<= | 小于等于号,检测左边的值是否小于或等于右边的值, 如果左边的值小于或等于右边的值返回true | (A <= B) 返回 true。 |
1. 等于条件:
SELECT * FROM users WHERE username = 'test';
2. 不等于条件:
SELECT * FROM users WHERE username != 'runoob';
3. 大于条件:
SELECT * FROM products WHERE price > 50.00;
4. 小于条件:
SELECT * FROM orders WHERE order_date < '2023-01-01';
5. 大于等于条件:
SELECT * FROM employees WHERE salary >= 50000;
6. 小于等于条件:
SELECT * FROM students WHERE age <= 21;
7. 组合条件(AND、OR):
SELECT * FROM products WHERE category = 'Electronics' AND price > 100.00;
SELECT * FROM orders WHERE order_date >= '2023-01-01' OR total_amount > 1000.00;
8. 模糊匹配条件(LIKE):
SELECT * FROM customers WHERE first_name LIKE 'J%';
9. IN 条件:
SELECT * FROM countries WHERE country_code IN ('US', 'CA', 'MX');
10. NOT 条件:
SELECT * FROM products WHERE NOT category = 'Clothing';
11. BETWEEN 条件:
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';
12. IS NULL 条件
SELECT * FROM employees WHERE department IS NULL;
13. IS NOT NULL 条件:
SELECT * FROM customers WHERE email IS NOT NULL;
1.MySQL LIKE 子句
LIKE 子句是在 MySQL 中用于在 WHERE 子句中进行模糊匹配的关键字。它通常与通配符一 起 使用,用于搜索符合某种模式的字符串。
1. 百分号通配符 %:
% 通配符表示零个或多个字符。例如,'a%' 匹配以字母 'a' 开头的任何字符串。
SELECT * FROM customers WHERE last_name LIKE 'S%';
以上 SQL 语句将选择所有姓氏以 'S' 开头的客户。
2. 下划线通配符 _:
_ 通配符表示一个字符。例如,'_r%' 匹配第二个字母为 'r' 的任何字符串。
SELECT * FROM products WHERE product_name LIKE '_a%';
以上 SQL 语句将选择产品名称的第二个字符为 'a' 的所有产品。
3. 组合使用 % 和 _:
SELECT * FROM users WHERE username LIKE 'a%o_';
以上 SQL 语句将匹配以字母 'a' 开头,然后是零个或多个字符,接着是 'o',最后是一个任意字符的字符串,如 'aaron'、'apol'。
4. 不区分大小写的匹配:
SELECT * FROM employees WHERE last_name LIKE 'smi%' COLLATE utf8mb4_general_ci;
以上 SQL 语句将选择姓氏以 'smi' 开头的所有员工,不区分大小写。
2.MySQL ORDER BY语句
MySQL ORDER BY(排序) 语句可以按照一个或多个列的值进行升序(ASC)或降序(DESC)排序。
1. 单列排序: ASC 排序。
SELECT * FROM products
ORDER BY product_name ASC;
2. 多列排序:DESC降序。
SELECT * FROM employees
ORDER BY department_id ASC, hire_date DESC;
3. 使用数字表示列的位置:
SELECT first_name, last_name, salary
FROM employees
ORDER BY 3 DESC, 1 ASC;
以上 SQL 语句将选择员工表 employees 中的名字和工资列,并按第三列(salary)
降序 DESC 排序,然后按第一列(first_name)升序 ASC 排序。
4. 使用表达式排序:
SELECT product_name, price * discount_rate AS discounted_price
FROM products
ORDER BY discounted_price DESC;
以上 SQL 语句将选择产品表 products 中的产品名称和根据折扣率计算的折扣后价格
,并按折扣后价格降序 DESC 排序。
5. 使用 NULLS FIRST 或 NULLS LAST 处理 NULL 值:
SELECT product_name, price
FROM products
ORDER BY price DESC NULLS LAST;
MySQL 更新数据
1. 更新单个列的值:
UPDATE employees
SET salary = 60000
WHERE employee_id = 101;
2. 更新多个列的值:
UPDATE orders
SET status = 'Shipped', ship_date = '2023-03-01'
WHERE order_id = 1001;
3. 使用表达式更新值:
UPDATE products
SET price = price * 1.1
WHERE category = 'Electronics';
以上 SQL 语句将每个属于 'Electronics' 类别的产品的价格都增加了 10%。
4. 更新符合条件的所有行:
UPDATE students
SET status = 'Graduated';
以上 SQL 语句将所有学生的状态更新为 'Graduated'。
5. 更新使用子查询的值:
UPDATE customers
SET total_purchases = (
SELECT SUM(amount)
FROM orders
WHERE orders.customer_id = customers.customer_id
)
WHERE customer_type = 'Premium';
以上 SQL 语句通过子查询计算每个 'Premium' 类型客户的总购买金额,并将该值更新
到 total_purchases 列中。
MySQL 删除数据
1. 删除符合条件的行:
DELETE FROM students
WHERE graduation_year = 2021;
以上 SQL 语句删除了 students 表中所有 graduation_year 为 2021 的学生的记录。
2. 删除所有行:
DELETE FROM orders;
以上 SQL 语句删除了 orders 表中的所有记录,但表结构保持不变。
3. 使用子查询删除符合条件的行:
DELETE FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
WHERE order_date < '2023-01-01'
);
以上 SQL 语句通过子查询删除了 orders 表中在 '2023-01-01' 之前下的订单对应的客户。
MySQL UNION 操作符
MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合,并去除重复的行。
UNION 操作符必须由两个或多个 SELECT 语句组成,每个 SELECT 语句的列数和对应位置的数据类型必须相同。
1. 基本的 UNION 操作:
SELECT city FROM customers
UNION
SELECT city FROM suppliers
ORDER BY city;
以上 SQL 语句将选择客户表和供应商表中所有城市的唯一值,并按城市名称升序排序。
2. 使用过滤条件的 UNION:
SELECT product_name FROM products WHERE category = 'Electronics'
UNION
SELECT product_name FROM products WHERE category = 'Clothing'
ORDER BY product_name;
以上 SQL 语句将选择电子产品和服装类别的产品名称,并按产品名称升序排序。
3. UNION 操作中的列数和数据类型必须相同:
SELECT first_name, last_name FROM employees
UNION
SELECT department_name, NULL FROM departments
ORDER BY first_name;
以上 SQL 语句中,departments 表中的 department_name 列被映射到了
employees 表中的 last_name 列,但是列数和数据类型必须相同。
4. 使用 UNION ALL 不去除重复行:
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers
ORDER BY city;
以上 SQL 语句使用 UNION ALL 将客户表和供应商表中的所有城市合并在一起,不去除重复行。
MySQL GROUP BY 语句
GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
mysql> set names utf8;
mysql> SELECT * FROM employee_tbl;
+----+--------+---------------------+--------+
| id | name | date | signin |
+----+--------+---------------------+--------+
| 1 | 小明 | 2016-04-22 15:25:33 | 1 |
| 2 | 小王 | 2016-04-20 15:25:47 | 3 |
| 3 | 小丽 | 2016-04-19 15:26:02 | 2 |
| 4 | 小王 | 2016-04-07 15:26:14 | 4 |
| 5 | 小明 | 2016-04-11 15:26:40 | 4 |
| 6 | 小明 | 2016-04-04 15:26:54 | 2 |
+----+--------+---------------------+--------+
6 rows in set (0.00 sec)
接下来我们使用 GROUP BY 语句 将数据表按名字进行分组,并统计每个人有多少条记录:
mysql> SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;
+--------+----------+
| name | COUNT(*) |
+--------+----------+
| 小丽 | 1 |
| 小明 | 3 |
| 小王 | 2 |
+--------+----------+
3 rows in set (0.01 sec)
使用 WITH ROLLUP
WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计(SUM,AVG,COUNT…)。
例如我们将以上的数据表按名字进行分组,再统计每个人登录的次数:
mysql> SELECT name, SUM(signin) as signin_count FROM employee_tbl
GROUP BY name WITH ROLLUP;
+--------+--------------+
| name | signin_count |
+--------+--------------+
| 小丽 | 2 |
| 小明 | 7 |
| 小王 | 7 |
| NULL | 16 |
+--------+--------------+
4 rows in set (0.00 sec)
其中记录 NULL 表示所有人的登录次数。
我们可以使用 coalesce 来设置一个可以取代 NUll 的名称,coalesce 语法:
select coalesce(a,b,c);
参数说明:如果 a==null,则选择 b;如果 b==null,则选择 c;如果 a!=null,
则选择 a;如果 a b c 都为 null ,则返回为 null(没意义)。
以下实例中如果名字为空我们使用总数代替:
mysql> SELECT coalesce(name, '总数'), SUM(signin) as signin_count FROM
employee_tbl GROUP BY name WITH ROLLUP;
+--------------------------+--------------+
| coalesce(name, '总数') | signin_count |
+--------------------------+--------------+
| 小丽 | 2 |
| 小明 | 7 |
| 小王 | 7 |
| 总数 | 16 |
+--------------------------+--------------+
4 rows in set (0.01 sec)
MySQL 连接的使用
你可以在 SELECT, UPDATE 和 DELETE 语句中使用 MySQL 的 JOIN 来联合多表查询。
JOIN 按照功能大致分为如下三类:
- INNER JOIN(内连接,或等值连接):获取两个表中字段匹配关系的记录。
- LEFT JOIN(左连接):获取左表所有记录,即使右表没有对应匹配的记录。
- RIGHT JOIN(右连接): 与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录。
简单的 INNER JOIN: SELECT orders.order_id, customers.customer_name FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id; 以上 SQL 语句将选择 orders 表和 customers 表中满足连接条件的订单 ID 和客户名称。 2. 使用表别名: SELECT o.order_id, c.customer_name FROM orders AS o INNER JOIN customers AS c ON o.customer_id = c.customer_id; 以上 SQL 语句使用表别名 o 和 c 作为 orders 和 customers 表的别名。 3. 多表 INNER JOIN: SELECT orders.order_id, customers.customer_name, products.product _name FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id INNER JOIN order_items ON orders.order_id = order_items.order_id INNER JOIN products ON order_items.product_id = products.product_id; 以上 SQL 语句涉及了 orders、customers、order_items 和 products 四个表的连接。 它选择了订单 ID、客户名称和产品名称,连接了这些表的关联列。 4. 使用 WHERE 子句进行过滤: SELECT orders.order_id, customers.customer_name FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id WHERE orders.order_date >= '2023-01-01'; 以上 SQL 语句在 INNER JOIN 后使用 WHERE 子句,过滤了订单日期在 '2023-01-01' 及以后的订单。 LEFT JOIN LEFT JOIN 返回左表的所有行,并包括右表中匹配的行,如果右表中没有匹配的行,将返回 NULL 值, 以下是 LEFT JOIN 语句的基本语法: SELECT column1, column2, ... FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name; 1. 简单的 LEFT JOIN: SELECT customers.customer_id, customers.customer_name, orders.order_id FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id; 以上 SQL 语句将选择客户表中的客户 ID 和客户名称,并包括左表 customers 中的所有行, 以及匹配的订单 ID(如果有的话)。 2. 使用表别名: SELECT c.customer_id, c.customer_name, o.order_id FROM customers AS c LEFT JOIN orders AS o ON c.customer_id = o.customer_id; 以上 SQL 语句使用表别名 c 和 o 分别代替 customers 和 orders 表的名称。 3. 多表 LEFT JOIN: SELECT customers.customer_id, customers.customer_name, orders.order_id, products.product_name FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id LEFT JOIN order_items ON orders.order_id = order_items.order_id LEFT JOIN products ON order_items.product_id = products.product_id; 以上 SQL 语句连接了 customers、orders、order_items 和 products 四个表,并选择了 客户 ID、客户名称、订单 ID 和产品名称。左连接保证了即使在 order_items 或 products 中没有匹配的行,仍然会返回客户和订单的信息。 4. 使用 WHERE 子句进行过滤: SELECT customers.customer_id, customers.customer_name, orders.order_id FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id WHERE orders.order_date >= '2023-01-01' OR orders.order_id IS NULL; 以上 SQL 语句在 LEFT JOIN 后使用 WHERE 子句,过滤了订单日期在 '2023-01-01' 及以后的订单,以及没有匹配订单的客户。 LEFT JOIN 是一种常用的连接类型,尤其在需要返回左表中所有行的情况下。当右表中没 有匹配的行时,相关列将显示为 NULL。在使用 LEFT JOIN 时,请确保理解连接条件并根据 需求过滤结果。 RIGHT JOIN RIGHT JOIN 返回右表的所有行,并包括左表中匹配的行,如果左表中没有匹配的行,将 返回 NULL 值,以下是 RIGHT JOIN 语句的基本语法:: SELECT column1, column2, ... FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name; 以下是一个简单的 RIGHT JOIN 实例: SELECT customers.customer_id, orders.order_id FROM customers RIGHT JOIN orders ON customers.customer_id = orders.customer_id; 以上 SQL 语句将选择右表 orders 中的所有订单 ID,并包括左表 customers 中匹配的 客户 ID。如果在 customers 表中没有匹配的客户 ID,相关列将显示为 NULL。 在开发过程中中,RIGHT JOIN 并不经常使用,因为它可以用 LEFT JOIN 和表的顺序交换 来实现相同的效果。例如,上面的查询可以通过使用 LEFT JOIN 改写为: SELECT customers.customer_id, orders.order_id FROM orders LEFT JOIN customers ON orders.customer_id = customers.customer_id; 以上 SQL 语句返回相同的结果,因为 LEFT JOIN 与 RIGHT JOIN 是对称的。在实际使用 中,你可以根据个人偏好或组织规范选择使用哪种形式。