sql 常见语法

where having区别

Column 1	where	having
功能	过滤数据行	过滤分组
聚合函数	不可以使用	可以使用
group by	前过滤	后过滤
字段别名	不可用	可用
group执行顺序	前执行	后 执行

with用法

WITH语句是一种在SQL中常用的语法，用于创建临时表或视图，以便在后续的查询中使用。它可以简化复杂查询的编写，并提高查询性能。以下是一些WITH语句的示例：
创建临时表

WITH temp_table AS (
    SELECT column1, column2
    FROM some_table
    WHERE column3 = 'value'
)
SELECT *
FROM temp_table

decimal()函数的使用

decimal(10,2)中的“2”表示小数部分的位数，如果插入的值未指定小数部分或者小数部分不足两位则会自动补到2位小数，若插入的值小数部分超过了2为则会发生截断，截取前2位小数。
“10”指的是整数部分加小数部分的总长度，也即插入的数字整数部分不能超过“10-2”位，否则不能成功插入，会报超出范围的错误。

CAST

CAST(expr AS type) 是将某种数据类型的表达式显式转换为另一种数据类型

order by

升序（ASC），降序（DESC）排序，默认升序
使用 NULLS FIRST 或 NULLS LAST 处理 NULL 值：
将 NULL 值排在最后。

SELECT product_name, price
FROM products
ORDER BY price DESC NULLS LAST;

WITH ROLLUP和coalesce

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)

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)

ALTER

以下 SQL 语句在 employees 表中添加了一个名为 birth_date 的日期列：

ALTER TABLE employees ADD COLUMN birth_date DATE;

修改列的数据类型

ALTER TABLE TABLE_NAME MODIFY COLUMN column_name new_datatype;

修改列名

ALTER TABLE employees CHANGE COLUMN old_column_name new_column_name VARCHAR(255);

删除列

ALTER TABLE employees DROP COLUMN birth_date;

添加 PRIMARY KEY

ALTER TABLE employees
ADD PRIMARY KEY (employee_id);

添加 FOREIGN KEY

ALTER TABLE orders ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers (customer_id);

修改表名

ALTER TABLE old_table_name RENAME TO new_table_name;

事务

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
BEGIN 或 START TRANSACTION 显式地开启一个事务；

COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；

ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；

RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

ROLLBACK TO identifier 把事务回滚到标记点；

mysql> use RUNOOB;
Database changed
mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb;  # 创建数据表
Query OK, 0 rows affected (0.04 sec)
 
mysql> select * from runoob_transaction_test;
Empty set (0.01 sec)
 
mysql> begin;  # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into runoob_transaction_test value(5);
Query OK, 1 rows affected (0.01 sec)
 
mysql> insert into runoob_transaction_test value(6);
Query OK, 1 rows affected (0.00 sec)
 
mysql> commit; # 提交事务
Query OK, 0 rows affected (0.01 sec)
 
mysql>  select * from runoob_transaction_test;
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)
 
mysql> begin;    # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql>  insert into runoob_transaction_test values(7);
Query OK, 1 rows affected (0.00 sec)
 
mysql> rollback;   # 回滚
Query OK, 0 rows affected (0.00 sec)
 
mysql>   select * from runoob_transaction_test;   # 因为回滚所以数据没有插入
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)

（tip：来自菜鸟教程）

索引

创建索引

CREATE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

唯一索引

CREATE UNIQUE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

修改表结构(添加索引)

ALTER TABLE table_name
ADD INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

创建表的时候直接指定

CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  ...,
  INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...)
);

删除索引的语法

DROP INDEX index_name ON table_name;

临时表

CREATE TEMPORARY TABLE temp_table_name (
  column1 datatype,
  column2 datatype,
  ...
);

或者简写为：

CREATE TEMPORARY TABLE temp_table_name AS
SELECT column1, column2, ...
FROM source_table WHERE condition;

复制表

SHOW CREATE TABLE runoob_tbl;

mysqldump 命令

mysqldump 命令可以用于备份和还原 MySQL 数据库。

mysqldump -u username -p dbname old_table > old_table_dump.sql

这将导出名为 old_table 的表的结构和数据到一个名为 old_table_dump.sql 的 SQL 文件。

mysql -u username -p new_dbname < old_table_dump.sql

这将在新的数据库（new_dbname）中创建一个名为 old_table 的表，并将之前导出的结构和数据导入到新表中。

MySQL 元数据

查看所有数据库：

SHOW DATABASES;

选择数据库：

USE database_name;

查看数据库中的所有表：

SHOW TABLES;

查看表的结构：

DESC table_name;

查看表的索引：

SHOW INDEX FROM table_name;

查看表的创建语句：

SHOW CREATE TABLE table_name;

查看表的行数：

SELECT COUNT(*) FROM table_name;

MySQL 序列使用（AUTO_INCREMENT）

CREATE TABLE example_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

导出数据

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

导入数据

LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;