MySQL入门到精通

一、创建数据库

CREATE DATABASE 数据库名称;

如果数据库存在，则会提示报错。

二、选择数据库

USE 数据库名称;

三、创建数据表

CREATE TABLE 数据表名称;

四、MySQL数据类型

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串类型

4.1 数值类型

数值类型

类型	大小	用途
INT	4Bytes	整数值
FLOAT	4Bytes	单精度
DOUBLE	8Bytes	双精度

4.2 字符串类型

字符串类型

类型	用途
CHAR	定长字符串
VARCHAR	变长字符串

4.3 日期和时间类型

五、MySQL插入数据

INSERT INTO table_name (column1,column2,column3)

VALUES (value1,value2,value3)

六、查询数据库

SHOW DATABASES;

七、查询数据表

SELECT * FROM users;

八、MySQL WHERE子句

语法：

SELECT column1,column2

FROM table_name

WHERE condition;

参数说明：

column1,column2是显示列的名称

table_name是你选择查询数据的表的名称

WHERE condition是用于指定过滤条件的子句

WHERE简单实例：

1、等于条件

SELECT * FROM users WHERE username = 'test';

2、不等于条件

SELECT * FROM users WHERE username != 'test';

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、组合条件

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、模糊匹配条件

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-32';

12、IS NULL条件

SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

13、IS NOT NULL条件

SELECT * FROM customers WHERE email IS NOT NULL;

九、MySQL UPDATE更新

UPDATE table_name

SET column1 = value1,column2 = value2

WHERE condition

参数说明：

table_name是更细数据的表的名称

column1,column2是更新的列的名称

value1,value2是新的值，用于替换旧的值

WHERE condition是一个可选的子句

实例

1、更新单个列的值：

UPDATE employees

SET salary = 60000

WHERE employee_id = 101

2、更新多个列的值：

UPDATE orders 

STE status = 'Shipped', ship_date = '2023-03-01'

WHERE order_id = 1001;

3、使用表达式更新值

UPDATE products

SET price = price * 1.1

WHERE category = 'Electronics'

4、更新使用子查询的值

UPDATE customers 

SET total_purchases = (

        SELECT SUM(amount)

        FROM orders

        WHERE orders.customer_id = customers.customer_id

)

WHERE customer_type = 'Premium';

十、MySQL DELETE语句

语法：

DELETE FROM table_name

WHERE condition;

参数说明：

table_name是删除数据的表的名称

WHERE condition是一个可选的子句。

实例

1、删除复合条件的行

DELETE FROM students

WHERE graduation_year = 2021;

2、删除所有行

DELETE FROM orders;

3、使用子查询删除复合条件的行

DELETE FROM customers

WHERE customer_id IN(

              SELECT customer_id

              FROM orders 

              WHERE order_date < '2023-01-01'

);

十一、MySQL LIKE子句

%通配符表示零个或多个字符；_通配符表示一个字符；不区分大小写的匹配

实例：

SELECT * FROM customers WHERE last_name LIKE 'S%';

SELECT * FROM products WHERE product_name LIKE '_a%';

十二、MySQL ORDER BY(排序）语句

语法：

SELECT column1,column2

FROM table_name

ORDER BY column1 [ASC | DESC], column2 [ASC | DESC]

1.单列排序：

SELECT * FROM products

ORDER BY products_name ASC;

2.多列排序：

SELECT * FROM employees

ORDER BY department_id ASC,hire_date DESC;

3.使用数字表列列的位置

ORDER BY 3 DESC, 1 ASC;

十三、MySQL GROUP BY语句

GROUP BY语句根据一个或多个列对结果集进行分组

SELECT customer_id, SUM(order_amount) AS total_amount

FROM orders 

GROUP BY customer_id;

十四、MySQL UNION操作符

描述

MySQL UNION操作符用于连接两个以上的SELECT语句的结果组合到一个结果集合，并去除重复的行。

语法

SELECT column1,column2

FROM table1

WHERE condition1

UNION

SELECT column1,column2

FROM table2

WHERE condition2

ORDER BY column1,column2

实例

1、基本的UNION操作

SELECT city FROM customers

UNION

SELECT city FROM suppliers

ORDER BY city;

2、使用过滤条件的UNION

SELECT product_name FROM products WHERE category = 'Electronics'

UNION

SELECT product_name FROM products WHERE category = 'Clothing'

ORDER BY product_name

3、UNION操作中的列数和数据类型必须相同

SELECT first_name,last_name FROM employees

UNION

SELECT department_name,NULL FROM departments

ORDER BY first_name;

4、使用UNION ALL不去除重复行

SELECT city FROM customers

UNION ALL

SELECT city FROM suppliers

ORDER BY city;

十五、MySQL连接的使用

INNER JOIN(内连接)：获取两个表中字段匹配关系的记录

LEFT JOIN(左连接)：获取左表所有记录

RIGHT JOIN(有连接)：获取右表所有记录

INNER JOIN

1、简单的INNER JOIN:

SELECT orders.order_id,customers.customer_name

FROM orders

INNER JOIN customers ON orders.customer_id = customers.customer_id;

2、多表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;

3、使用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';

LEFT JOIN

1、简单的LEFT JOIN:

SELECT orders.order_id,customers.customer_name

FROM orders

LEFT JOIN customers ON orders.customer_id = customers.customer_id;

2、多表LEFT JOIN:

SELECT orders.order_id,customers.customer_name,products.product_name

FROM orders

LEFT JOIN customers ON orders.customer_id = customers.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;

3、使用WHERE子句进行过滤

SELECT orders.order_id,customers.customer_name

FROM orders

LEFT JOIN customers ON orders.customer_id = customers.customer_id

WHERE orders.order_date >= '2023-01-01';

RIGHT JOIN和LEFT JOIN使用方法是一样的。

十六、MySQL ALTER命令

描述：当我们需要修改数据表名或者数据表字段时，就需要使用到MySQL ALTER命令

1、添加列

ALTER TABLE table_name

ADD COLUMN new_column_name datatype;

实例

ALTER TABLE employees

ADD COLUMN birth_date DATE;

2、修改列的数据类型

ALTER TABLE TABLE_NAME

MODIFY COLUMN column_name new_datatype

实例

ALTER TABLE employees

MODIFY COLUM salary DECIMAL(10,2);

3、修改列名

ALTER TABLE table_name

CHANGE COLUMN old_column_name new_column_name datatype;

实例

ALTER TABLE employees

CHANGE COLUMN old_column_name new_column_name VARCHAR(255);

4、删除列

ALTER TABLE table_name

DROP COLUMN column_name;

实例

ALTER TABLE employees

DROP COLUMN birth_date;

5、添加PRIMARY KEY

ALTER TABLE table_name

ADD PRIMARY KEY (column_name);

实例

ALTER TABLE employees

ADD PRIMARY KEY (employee_id)

6、添加FOREIGN KEY

ALTER TABLE child_table

ADD CONSTRAINT fk_name

FOREIGN KEY (column_name)

REFERENCES parent_table (column_name)

实例

ALTER TABLE orders

ADD CONSTRAINT fk_customer

FOREIGN KEY (customer_id)

REFERENCES customers (customer_id)

7、修改表名

ALTER TABLE old_table_name

RENAME TO new_table_name;

实例ALTER TABLE employees

RENAME TO staff;

十七、MySQL处理重复数据

SELECT DISTINCT last_name,first_name

FROM person_tb1;

十八、MySQL删除数据库

drop命令删除数据库

DROP DATABE <datatbase_name>

十九、MySQL删除数据表

DROP TABLE table_name;