SQL基础语句

SQL基础语句

说明

文章内容基于《MySQL基础教程》整理，文中提到的所有SQL语句，均是以MySQL为基础

博文链接：伊地知虾饺的博客-SQL基础语句

1、关于中文及字符编码的设置

大部分Web程序默认使用UTF-8，而Windows系统中命令行的默认字符编码是GBK。也就是说，数据库中的字符编码和控制台上的字符编码不一样，因此在使用前需要修改配置文件：

[mysqld]
port		= 3306
socket		= mysql
skip-external-locking
......
datadir = D:/MAMP/db/mysql/
character-set-server=utf8
......

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
default-character-set=gbk
......

或者可以修改命令提示符使用的字符编码。修改为UTF-8：

chcp 65001

2、有关MySQL监视器的操作

启动MySQL监视器

在命令提示符输入：

`mysql -u 用户名 -p密码`

或

mysql -u 用户名 -p

并在回车后输入密码（更安全）

查看数据库状态

在监视器启动状态下，输入status并回车

例：

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.24, for Win64 (x86_64)

Connection id:          4
Current database:
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.7.24 MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    gbk
Conn.  characterset:    gbk
TCP port:               3306
Uptime:                 14 sec

Threads: 1  Questions: 5  Slow queries: 0  Opens: 110  Flush tables: 1  Open tables: 4  Queries per second avg: 0.357
--------------

退出监视器

在MySQL监视器启动状态下，输入exit并回车，或直接叉掉

使用历史命令

按F7，或方向键上和方向键下

修改MySQL管理员root的密码

在MySQL监视器启动状态下，输入：

`SET PASSWORD FOR root@localhost=PASSWORD('1234');`

如果使用MAMP而不是自己配置的MySQL，还需要对应的修改 MAMP.dll.config和 MAMP.exe.config 中的密码

这两个文件存放于MAMP的安装目录

如果要使用phpMyAdmin，还需一并修改 D:\MAMP\bin\phpMyAdmin\config.inc.php 中的配置：

$cfg['Servers'][$i]['password'] = '1234'; // MySQL password 

创建新用户

在MySQL监视器启动状态下，输入：

CREATE USER 新用户名@主机名 IDENTIFIED BY '密码';

设置用户权限

在MySQL监视器启动状态下，输入：

GRANT 权限 ON 数据库名.表名 TO 用户名@主机名;

如果是所有权限，就设置为 all ，如果只允许select和update，就设置为 select,update

如果是数据库中所有的表，就设置为 *.*

删除用户

使用root用户权限执行

DROP USER 用户名@主机名;

修改提示符字符串

prompt XXX

3、SQL语句

创建与删除

数据库

CREATE DATABASE db; //创建数据库db

DROP DATABASE db; //删除数据库db

注意：数据库不能重名

表

CREATE TABLE 表名（列1 数据类型1, 列2 数据类型2...); //使用指定列结构创建表

CREATE TABLE tb1 SELECT * FROM tb; //复制tb到一个新的表tb1

CREATE TABLE tb1 LIKE tb; //创建与tb有相同列结构的表tb1

CREATE TABLE 表名（列1 数据类型1, 列2 数据类型2...) CHARSET=UTF-8; //使用指定字符编码创建表


DROP TABLE tb; //删除表tb

DROP TABLE IF EXISTS tb; //如果表tb存在。将其删除

索引

CREATE INDEX my_ind ON tb (col); //为表tb中的列col创建名为my_ind的索引

DROP INDEX my_ind ON tb; //删除表tb上的索引my_ind

视图

CREATE VIEW v1 AS SELECT name, age FROM tb; //从指定列创建视图

CREATE VIEW v1 AS
	SELECT tb.empid, tb1.name, tb.sales
		FROM tb
	JOIN tb1
		USING (empid)
	WHERE tb.sales >= 100;       //使用指定条件创建视图
	
CREATE VIEW v1 AS SELECT name, age FROM tb WITH CHECK OPTION; //创建“不接受不符合条件的记录”的视图


DROP VIEW v1; //删除视图

存储过程

mysql> delimiter //                 //创建前，修改分隔符
mysql> CREATE PROCEDURE pr()        //创建存储过程，有括号
    -> BEGIN
    -> SELECT * FROM tb;            //存储过程主体
    -> SELECT * FROM tb1;
    -> END//
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;               //将分隔符修改回;



DROP PROCEDURE pr;   //删除存储过程

存储函数

set global log_bin_trust_function_creators=1;

mysql> delimiter //
mysql> CREATE FUNCTION fu() RETURNS DOUBLE      //注明返回值数据类型
    -> BEGIN
    -> DECLARE r DOUBLE;                        //声明变量
    -> SELECT AVG(sales) INTO r FROM tb;
    -> RETURN r;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;



DROP TRIGGER tr1;    //删除触发器

触发器

mysql> delimiter //
mysql> CREATE TRIGGER tr1 BEFORE DELETE ON tb1 FOR EACH ROW       /*创建名为tr1的触发器，在对tb1进行删除操作前，
                                                                    对要删除的每行执行以下操作*/
    -> BEGIN                                                        
    -> INSERT INTO tb1_from VALUES(OLD.empid,OLD.name,OLD.age);
    -> END
    -> //
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;

显示

显示数据库

SHOW DATABASES; //显示所有数据库

SELECT DATABASE(); //显示当前使用的数据库（调用函数)

SELECT USER(); //显示当前用户

SELECT VERSION(); //显示当前sql版本

显示表

SHOW TABLES; //显示当前数据库中的所有表和视图

DESC tb; //显示表的列结构

SHOW CREATE TABLE tb； //显示表的详细信息


SELECT * FROM tb; //显示表中的所有数据

SELECT DISTINCT empid FROM tb; //显示列empid中所有不重复的数据

SELECT empid,name FROM tb; //按顺序显示指定列中的数据

SHOW INDEX FROM tb; //显示表tb中的索引

显示视图

SHOW TABLES; //显示数据库中的所有表和视图

DESC v1; //显示视图的列结构

SHOW CREATE VIEW v1; //显示视图的详细信息

显示存储过程及存储函数

SHOW CREATE PROCEDURE pr;  //显示存储过程的详细信息

SHOW CREATE FUNCTION fu();

显示触发器

SHOW TRIGGERS;   //显示所有触发器

插入

向表中插入数据

INSERT INTO 表名 VALUES(数据1, 数据2...); //数据顺序与表的列结构一致

INSERT INTO tb (empid, age, name) VALUES('A101', 28, 'XXX'); //按照指定的顺序插入数据

INSERT INTO 表名 VALUES(数据1, 数据2...),(数据1, 数据2...),(数据1, 数据2...); //一次性插入多条数据

INSERT INTO tb1 SELECT * FROM tb; //复制表tb中的所有记录到表tb1

INSERT INTO tb1 (empid) SELECT empid FROM tb; //复制表tb中指定列的记录到表tb1

修改

修改表的列结构

ALTER TABLE tb MODIFY name VARCHAR(10); //将name的数据类型修改为VARCHAR(10)

ALTER TABLE tb MODIFY birth DATETIME FIRST; //修改列的位置（FIRST 可替换为AFTER XXX等关键字）


ALTER TABLE tb ADD birth DATETIME; //在末尾添加列

ALTER TABLE tb ADD birth DATETIME FIRST; //在开头添加列

ALTER TABLE tb ADD birth DATETIME AFTER empid; //在任意位置添加列


ALTER TABLE tb CHANGE birth birthday DATE; //修改列的数据类型或位置的同时也修改列名


ALTER TABLE tb DROP birthday; //删除列

列的特殊关键字

PRIMARY KEY - 主键

主键用于创建唯一记录时，给列设置一个用于和其他列区分的特殊属性

默认没有重复的值，且不允许输入空值（NULL）

创建表或修改表的列结构时，在列的数据类型后添加 PRIMARY KEY 关键字，可将其设为主键

在设置了主键的情况下，索引会自动创建

UNIQUE - 唯一键

不允许有重复，但是允许NULL

创建表或修改表的列结构时，在列的数据类型后添加 UNIQUE 关键字，将其设为唯一键

AUTO_INCREMENT - 自动编号

具有自动连续编号功能的列，其数据类型必须是INT, TINYINT, SMALLINT中的一种

创建表或修改表的列结构时，在列的数据类型后添加 AUTO_INCREMENT 关键字，使其在插入数据时自动增加

设置AUTO_INCREMENT初始值时，使用下面代码块中的语句。编号会从现有值+1开始分配

ALTER TABLE tb AUTO_INCREMENT = 1; // 设置初始值

CREATE TABLE tb (a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(10)); //创建表，其中列a是具有自动连续编号功能的主键

DEFAULT - 默认值

创建表或修改表的列结构时，在列的数据类型后添加 DEFAULT default_value ，为列设置默认值

修改表中的数据

UPDATE tb SET remark = 'A' WHERE sales >= 100; //将表tb中sales大于等于100的记录的remark值修改为'A'


DELETE FROM tb; //删除表tb中的所有记录（但不删除表）

DELETE FROM tb WHERE age<=30; //按照指定条件删除表中记录

修改视图结构

ALTER VIEW v1 AS SELECT name, age FROM tb;

使用条件提取

函数

SUM(), AVG(), COUNT(), PI(), NOW() 较简单，不多赘述。

字符串处理中常用的函数：CONCAT(), LEFT(), RIGHT(), SUBSTRING(), REPEAT(), REVERSE()

SELECT CONCAT(empid, name, '先生') FROM tb; //CONCAT() 用于连接字符串

SELECT LEFT(empid, 2) FROM tb; //显示empid最左边的两个字符，RIGHT()同理

SELECT SUBSTRING(empid, 2, 3); FROM tb; //从empid的第2个字符开始连续显示3个字符

SELECT REPEAT('-', age) FROM tb; //重复显示字符'-'，重复次数为列age的值

SELECT REVERSE(name) FROM tb; //将name中的字符串倒序显示

条件关键字

LIMIT

用于限制选中/显示的记录数

在语句末添加 LIMIT 对应数字 即可

WHERE

用于为选择的记录添加条件

SELECT * FROM tb WHERE sales NOT BETWEEN 50 AND 200; //选择表tb中sales值不在[50, 200]范围内的记录

SELECT * FROM tb WHERE month IN (5, 6); //选择表tb中month为5或6的所有记录

SELECT * FROM tb WHERE empid = 'A101';

SELECT * FROM tb WHERE empid LIKE '%1';


SELECT * FROM tb WHERE age IS NULL;

SELECT * FROM tb WHERE age IS NOT NULL;

代码块中第四条，对字符串模糊查询，类比正则表达式。'%‘表示任意字符，’_'表示单个字符

当列值为NULL或不为NULL时，不能用’='， 应使用 IS NULL 或 IS NOT NULL 关键字

条件可用 AND 和 OR 关键字连接。当多个AND和OR连用时，最好使用括号，否则将优先处理所有的 AND

CASE WHEN

根据条件改变输入值

mysql> SELECT
    -> CASE
    ->     WHEN sales >= 100 THEN 'HIGH'
    ->     WHEN sales >= 50 THEN 'MID'
    ->     ELSE 'LOW'
    -> END
    -> FROM tb;
+-------------------------------------------------------------------------------------------+
| CASE
    WHEN sales >= 100 THEN 'HIGH'
    WHEN sales >= 50 THEN 'MID'
    ELSE 'LOW'
END |
+-------------------------------------------------------------------------------------------+
| HIGH                                                                                      |
| MID                                                                                       |
| HIGH                                                                                      |
| HIGH                                                                                      |
| LOW                                                                                       |
| HIGH                                                                                      |
| HIGH                                                                                      |
| MID                                                                                       |
| LOW                                                                                       |
| MID                                                                                       |
+-------------------------------------------------------------------------------------------+
10 rows in set (0.01 sec)

ORDER BY

按照指定顺序选择记录

SELECT * FROM tb ORDER BY sales; //默认升序

SELECT * FROM tb ORDER BY sales DESC;//降序

OFFSET

在使用 LIMIT 开始显示记录数的偏移量关键字时，使用OFFSET将开始显示的位置向后移动指定位置。

mysql> SELECT * FROM tb ORDER BY sales LIMIT 3 OFFSET 2;
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A102  |    54 |     5 |
| A107  |    87 |     6 |
| A104  |    93 |     5 |
+-------+-------+-------+
3 rows in set (0.00 sec)

GROUP BY

将记录按照指定条件分组

mysql> SELECT empid, SUM(sales) FROM tb GROUP BY empid;
+-------+------------+
| empid | SUM(sales) |
+-------+------------+
| A101  |        484 |
| A102  |        259 |
| A103  |        130 |
| A104  |        274 |
| A107  |         87 |
+-------+------------+
5 rows in set (0.00 sec)

HAVING

对分组后的记录设置条件

mysql> SELECT empid, SUM(sales) FROM tb GROUP BY empid HAVING SUM(sales) >= 100;
+-------+------------+
| empid | SUM(sales) |
+-------+------------+
| A101  |        484 |
| A102  |        259 |
| A103  |        130 |
| A104  |        274 |
+-------+------------+
4 rows in set (0.00 sec)

多表操作

UNION

将两个或多个表合并（纵向）

mysql> (SELECT empid FROM tb WHERE sales >= 200)
    -> UNION
    -> (SELECT empid FROM tb1 WHERE age >= 25);
+-------+
| empid |
+-------+
| A101  |
| A102  |
| A105  |
+-------+
3 rows in set (0.00 sec)

• 可使用条件关键字
• UNION 默认去除重复。使用 UNION ALL 显示所有记录

JOIN

将两个或多个表横向连接。区分内连接，外连接，自连接

mysql> SELECT x.empid, y.name, x.sales    //内连接，只会显示两边都有的记录
    -> FROM TB AS x
    -> JOIN tb1 as y
    -> USING(empid);
+-------+------+-------+
| empid | name | sales |
+-------+------+-------+
| A101  | 佐藤 |   184 |
| A101  | 佐藤 |   300 |
| A102  | 高桥 |    54 |
......
| A104  | 渡边 |   181 |
| A104  | 渡边 |    93 |
+-------+------+-------+
9 rows in set (0.00 sec)


mysql> SELECT tb.empid, tb1.name
    -> FROM tb
    -> LEFT JOIN tb1          //左外连接，显示左表（tb）中的全部记录
    -> USING(empid);          //右外连接同理
+-------+------+
| empid | name |
+-------+------+
| A101  | 佐藤 |
......
| A104  | 渡边 |
| A107  | NULL |
+-------+------+
10 rows in set (0.00 sec)


mysql> SELECT * FROM tb1 AS a                        //自连接，必须要有别名
    -> JOIN tb1 AS b;                                //可以把自连接看做一种全排列，可用于排序
+-------+------+------+-------+------+------+
| empid | name | age  | empid | name | age  |
+-------+------+------+-------+------+------+
| A101  | 佐藤 |   40 | A101  | 佐藤 |   40 |
| A102  | 高桥 |   28 | A101  | 佐藤 |   40 |
......
| A105  | 西泽 |   35 | A105  | 西泽 |   35 |
+-------+------+------+-------+------+------+
25 rows in set (0.00 sec)

子查询

SELECT * FROM tb WHERE sales IN (SELECT MAX(sales) FROM tb); //括号内为子查询内容

• 当子查询结果仅有一条时，可以使用 = ，其余情况均应使用 IN 关键字
• 与 IN 相对的是 NOT EXISTS ，提取不存在与子查询中的记录

视图

• “虚表”，视图不存储信息，只显示信息
• 修改视图中的记录（语句与表的一样），会影响基表；同样，修改基表中的记录，也会影响视图中显示的记录
• 创建视图的方法参考“创建与删除”中的视图部分
• 向视图中插入不符合视图条件的记录，会写入基表，但是不会显示在视图中。可在创建视图时，在语句末添加 WITH CHECK OPTION ，将该视图设置为“不接受与条件不匹配的记录”

存储过程与存储函数

SQL中的存储过程和存储函数可类比java中的静态方法。其中，存储过程返回void，而存储函数返回指定数据类型的值

存储过程

mysql> delimiter //                 //创建前，修改分隔符
mysql> CREATE PROCEDURE pr()        //创建存储过程，有括号
    -> BEGIN
    -> SELECT * FROM tb;            //存储过程主体
    -> SELECT * FROM tb1;
    -> END//
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;               //将分隔符修改回;

mysql> CALL pr;                  //调用存储过程，调用时不用括号
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
......
| A105  | 西泽 |   35 |
+-------+------+------+
5 rows in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

存储函数

mysql> delimiter //
mysql> CREATE FUNCTION fu() RETURNS DOUBLE      //注明返回值数据类型
    -> BEGIN
    -> DECLARE r DOUBLE;                        //声明变量
    -> SELECT AVG(sales) INTO r FROM tb;
    -> RETURN r;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> SELECT fu();        //调用存储函数，即使没有参数也要有括号
+-------+
| fu()  |
+-------+
| 123.4 |
+-------+
1 row in set (0.00 sec)

触发器

触发器可以在对表进行 INSERT / UPDATE /DELETE 等命令前或后被调用或执行

可以选择前BEFORE或后AFTER，对表处理之前的列值OLD和之后的列值NEW进行操作

• 创建触发器：

mysql> delimiter //
mysql> CREATE TRIGGER tr1 BEFORE DELETE ON tb1 FOR EACH ROW       /*创建名为tr1的触发器，在对tb1进行删除操作前，
                                                                    对要删除的每行执行以下操作*/
    -> BEGIN                                                        
    -> INSERT INTO tb1_from VALUES(OLD.empid,OLD.name,OLD.age);
    -> END
    -> //
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;

事务

• 在所有存储引擎中，只有InnoDB支持事务

• 将多个操作整合为一个逻辑工作单元处理得功能称为事务

• 将事务开始之后的处理结果反映到数据库中的操作成为提交（commit），不反映到数据库中而是恢复为原来状态的操作称为回滚（rollback）

• 使用事务：

  START TRANSACTION;
  
  ......进行操作
  
  COMMIT;  /*执行COMMIT将结果提交给数据库
  或：执行ROLLBACK撤回操作*/
  ROLLBACK;
  

• 事务的自动提交，默认开启。可以关闭自动提交功能，每次操作后自行选择提交或回滚

  SET AUTOCOMMIT=0; //关闭自动提交
  
  SET AUTOCOMMIT=1; //开启自动提交
  

• 以下操作，无论是否启用事务，都会被自动提交：

  DROP DATABASE
  DROP TABLE
  DROP VIEW
  ALTER TABLE
  

文件操作

LOAD DATA INFILE '文件路径' INTO TABLE tb FIELDS TERMINATED BY ',';    //从csv文件中读取

SELECT * INTO OUTFILE '文件路径' FIELDS TERMINATED BY ',' FROM tb;   //导出为csv文件

SOURCE 文件路径;  //从txt文件中读取并执行SQL语句

• 备份整个数据库时，可以通过mysqldump导出（在命令行中执行）

  mysqldump -u root -proot db > db_out.txt
  

• 从转储的文件恢复数据库（须事先创建数据库）（在命令行中执行）

  mysql -u root -proot db1 < db_out.txt