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:MAMPbinphpMyAdminconfig.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
  

最后

以上就是狂野豌豆为你收集整理的SQL基础语句SQL基础语句的全部内容，希望文章能够帮你解决SQL基础语句SQL基础语句所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。