mysql基础常用语句

mysql基础常用语句

数据表的增删改

• 数据表的新建

数据库只能有一个主键。但是可以存在联合主键。

主键值必须唯一。即，表中的每个行必须具有唯一的主键值。如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。

DROP TABLE IF EXISTS `表名`;
CREATE TABLE 表名 (
	id INT AUTO_INCREMENT,
	name VARCHAR(32) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB;  
注意：表名和字段名可加 ` ` 也可不加，并且该符号不是单引号，而是重音符。
建议：统一不打任何符号。

• 数据表的修改

给原表添加列：
ALTER TABLE 表名 ADD 列名 列数据类型;
删除原表上的列
ALTER TABLE 表名 DROP 列名;

eg：
ALTER TABLE vendors ADD vend_phone CHAR(20); //表增加列
ALTER TABLE vendors DROP vend_phone CHAR(20); //表减少列

• 数据表的删除

删除表
DROP TABLE 表名;

eg:
DROP TABLE customer2; //删除表

基本的表数据的增删改查

INSERT INTO 表名(col1, col2) VALUES (val1, val2);

DELETE FROM 表名 WHERE id = 某值;

UPDATE 表名 SET 列名='新值' WHERE id = 某值;

SELECT * FROM 表名

注意：表名、列名不需要加重音符，值才需要。其实在任何地方只需要对【值】加重音符即可

排序检索数据ORDER BY

SELECT * FROM 表名 ORDER BY 列名  DESC; //降序排序
SELECT * FROM 表名 ORDER BY 列名  ASC; //升序排列，默认升序排列

eg:
SELECT prod_name,prod_price FROM products ORDER BY prod_price DESC;  
SELECT prod_name,prod_price FROM products ORDER BY prod_price ASC;  

限制返回数据量LIMIT

SELECT * FROM 表名 LIMIT 9;  // 返回前 9 条记录
SELECT * FROM 表名 LIMIT 0,5;  // 返回从第 0 行开始的 5 条记录
SELECT * FROM 表名 LIMIT 9,5;  // 返回从第 9 行开始的 5 条记录

过滤数据

WHERE字句操作符

操作符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN	在指定的两个值之间

SELECT prod_name,prod_price FROM products WHERE prod_price=2.50;     等于数字
SELECT prod_name,prod_price FROM products WHERE prod_name='fuses';   字符相同
SELECT prod_name,prod_price FROM products WHERE prod_price<50;       小于
SELECT prod_name,prod_price FROM products WHERE prod_price<>50;      不等于
SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;  两值之间
SELECT prod_name,prod_price FROM products WHERE prod_name IS NULL; 

单引号用来限定字符串。如果将值与串类型的列进行比较，则需要限定引号。用来与数值列进行比较的值不用引号。

数据过滤

• 组合WHERE字句

MySQL允许给出多个 WHERE 子句。这些子句可以两种方式使用：以 AND 子句的方式或 OR 子句的方式使用。

• 操作符AND

为了通过不止一个列进行过滤，可使用 AND 操作符给 WHERE 子句附加条件。

SELECT prod_name,prod_price FROM products ORDER BY prod_name='demo' AND prod_price<10;

• 操作符OR

OR 操作符，它指示MySQL检索匹配任一条件的行。

SELECT prod_name,prod_price FROM products ORDER BY vend_id=2 or prod_price=10;

• 计算次序

WHERE 可包含任意数目的 AND 和 OR 操作符。允许两者结合以进行复杂和高级的过滤。

AND 在计算次序中优先级更高。

可使用圆括号明确地分组相应的操作符。

在WHERE子句中使用圆括号： 任何时候使用具有 AND 和 OR 操作符的 WHERE 子句，都应该使用圆括号明确地分组操作符。不要过分依赖默认计算次序，即使它确实是你想要的东西也是如此。

SELECT prod_name,prod_price FROM products  WHERE  vend_id=2 OR vend_id=3  AND prod_price>=10;
SELECT prod_name,prod_price FROM products  WHERE  (vend_id=2 OR vend_id=3 )AND prod_price>=10;

• 操作符IN

IN 操作符用来指定条件范围，范围中的每个条件都可以进行匹配。 IN 取合法值的由逗号分隔的清单，全都括在圆括号中。

SELECT prod_name,prod_price FROM products WHERE vend_id In(2,3) ORDER BY prod_name;

• 操作符NOT

WHERE 子句中的 NOT 操作符有且只有一个功能，那就是否定它之后所跟的任何条件。

SELECT prod_name,prod_price FROM products WHERE vend_id NOT In(2,3) ;

MySQL中的NOT MySQL支持使用 NOT 对 IN 、 BETWEEN 和EXISTS子句取反，这与多数其他 DBMS允许使用 NOT 对各种条件取反有很大的差别。

通配符进行过滤

• 操作符LIKE

通配符（wildcard） 用来匹配值的一部分的特殊字符。
搜索模式（search pattern）由字面值、通配符或两者组合构成的搜索条件

通配符可在搜索模式中任意位置使用，并且可以使用多个通配符。

为在搜索子句中使用通配符，必须使用 LIKE 操作符。 LIKE 指示MySQL，后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。

• 通配符%

最常使用的通配符是百分号（ % ）。在搜索串中， % 表示任何字符出现任意次数。

注意:除了匹配一个或多个字符外， % 还能匹配0个字符。 %代表搜索模式中给定位置的0个、1个或多个字符。

SELECT prod_name,prod_price FROM products WHERE  prod_name LIKE 'hom%';
SELECT prod_name,prod_price FROM products WHERE  prod_name LIKE '%hom%';
SELECT prod_name,prod_price FROM products WHERE  prod_name LIKE 'h%m';

• 通配符__

下划线的用途与 % 一样，但下划线只匹配单个字符而不是多个字符。

SELECT prod_name,prod_price FROM products WHERE  prod_name LIKE 'hom_';

字段拼接Concat

用于连接两个字段
SELECT CONCAT(列名1 , 列名2) AS 新列名 FROM 表名;

eg：
SELECT  Concat(vend_name,‘(’,vend_country,')')   FROM products ORDER BY prod_name;
上面的语句连接以下4个元素
存储在 vend_name 列中的名字；
包含一个空格和一个左圆括号的串；
存储在 vend_country 列中的国家；
包含一个右圆括号的串。

去重 DISTINCT

distinct对其后面的所有列名都起作用 ，所以一般distinct用来查询不重复记录的条数

SELECT	DISTINCT  vend_id  FROM  products;
SELECT count(distinct vend_id) FROM  products;

• 不能部分使用 DISTINCT

DISTINCT 关键字应用于所有列而不仅是前置它的列。

函数

函 数	说 明
AVG()	返回某列的平均值
count()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

SELECT AVG(prod_price) AS avg_price FROM products;
SELECT COUNT(*) AS num_cust FROM customers;
SELECT MAX(prod_price) AS max_price FROM products;
SELECT MIN(prod_price) AS max_price FROM products;
SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;
SELECT AVG(DISTINCT prod_price) AS avg_price FROM products;

• AVG()函数

AVG() 通过对表中行数计数并计算特定列值之和，求得该列的平均值。 AVG() 可用来返回所有列的平均值，也可以用来返回特定列或行的平均值。

• MAX()函数

MAX() 返回指定列中的最大值。 MAX() 要求指定列名。

• MIN()函数

MIN() 返回指定列中的最小值。 MIN() 要求指定列名。

• SUM()函数

SUM() 用来返回指定列值的和（总计）。

• COUNT()函数

COUNT() 函数进行计数。可利用 COUNT() 确定表中行的数目或符合特定条件的行的数目。

使用 COUNT(*) 对表中行的数目进行计数，不管表列中包含的是空值（ NULL ）还是非空值。

• 注意

注意：以上5个聚集函数都可以如下使用

• 对所有的行执行计算，指定 ALL 参数或不给参数（因为 ALL 是默认行为）
• 只包含不同的值，指定 DISTINCT 参数。

分组GROUP BY

• 创建分组

GROUP BY 子句必须出现在 WHERE 子句之后， ORDER BY 子句之前

SELECT vend_id, COUNT(*)AS num_prods FROM products GROUP BY vend_id;

• 过滤分组

WHERE过滤行，HAVING过滤分组。

SELECT cust_id,COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*)>=2;

• 分组后排序

一般在使用 GROUP BY 子句时，应该也给出 ORDER BY 子句。这是保证数据正确排序的唯一方法。千万不要仅依赖 GROUP BY 排序数据。

SELECT order_num,SUM(quantity*item_price) AS ordertotal FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >=50
ORDER BY ordertotal ;

子查询

在一个 select 中包含另一个 select，里面的叫做子查询。

子查询中只能返回一个字段的数据。

子查询必须包含在括号里面

列必须匹配: 在 WHERE 子句中使用子查询（如这里所示），应该保证 SELECT 语句具有与 WHERE 子句中相同数目的列。通常，子查询将返回单个列并且与单个列匹配，但如果需要也可以使用多个列。

SELECT * FROM 表1 WHERE 列1 IN (SELECT 列2 FROM 表2);
注意：where 后面可以用的操作符 = 、>、 <、 >=、 <=、 <> ANY、 IN、 EXISTS

eg:
SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id ='TNT2');

连接

分为内部连接、外部连接（左连接、右连接）、全连接

• 联结多个表

SQL对一条 SELECT 语句中可以联结的表的数目没有限制。创建联结的基本规则也相同。

笛卡尔积：由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘
以第二个表中的行数。

SELECT vend_name,prod_name,prod_price,quantity
FROM orderitems,products,vendors
WHERE products.vend_id = vendors.vend_id
AND orderitems.prod_id = products.prod_id
AND order_num = 20005
ORDER BY vend_name,prod_name;

• 自联结

用自联结而不用子查询 自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的，但有时候处理联结远比处理子查询快得多。应该试一下两种方法，以确定哪一种的性能更好。

//子查询方式
SELECT prod_id ,prod_name FROM products 
WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id='DTNTR');
//自联结的方式
SELECT p1.prod_id,p1.prod_name
FROM products AS p1, products AS p2 
WHERE p1.vend_id  = p2.vend_id 
AND  p2.prod_id = 'DTNTR';

• 内部联结

联结称为等值联结（equijoin），它基于两个表之间的相等测试。这种联结也称为内部联结。

SELECT vend_name,prod_name,prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

• 外部联结

联结包含了那些在相关表中没有关联行的行。这种类型的联结称为外部联结。

SELECT vend_name,prod_name,prod_price
FROM vendors LEFT OUTER JOIN products
on vendors.vend_id = products.vend_id;

在使用 OUTER JOIN 语法时，必须使用 RIGHT 或 LEFT 关键字指定包括其所有行的表（ RIGHT 指出的是 OUTER JOIN 右边的表，而 LEFT指出的是 OUTER JOIN 左边的表）。

外部联结的类型 :存在两种基本的外部联结形式：左外部联结和右外部联结。它们之间的唯一差别是所关联的表的顺序不同。

关键字UNION

UNION 的使用很简单。所需做的只是给出每条 SELECT 语句，在各条语句之间放上关键字 UNION。

SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price <= 5
UNION
SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001,1002)
ORDER BY vend_id;

UNION使用规则

• UNION 必须由两条或两条以上的 SELECT 语句组成，语句之间用关键字 UNION 分隔（因此，如果组合4条 SELECT 语句，将要使用3个UNION 关键字）。
• UNION 中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。
• 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。
• UNION 默认取消重复的行，如果想返回所有匹配行，可使用 UNION ALL 。
• 在用 UNION 组合查询时，只能使用一条 ORDER BY 子句，它必须出现在最后一条 SELECT 语句之后。

视图VIEW

CREATE VIEW MYVIEW AS SELECT * FROM BBC;
SELECT * FROM MYVIEW;

eg：
//创建视图
(这条语句创建了一个名为 productcustomers的视图，它联结了三个表，以返回已订购了任何产品的所有客户的列表)
CREATE VIEW productcustomers AS SELECT cust_name,cust_contact,prod_id
FROM customers,orders,orderitems
WHERE customers.cust_id = orders.cust_id 
AND orderitems.order_num = orders.order_num;
//使用视图
SELECT cust_name,cust_contact FROM prouctcustomers  WHERE prod_id ='TNT2';

存储过程

DELIMITER $$
CREATE PROCEDURE `库名`.`存储过程名`(in/out/inout 参数名 参数类型)
	BEGIN
		写 SQL 语句，可以使用上方括号里面传入的参数；
	END$$
DELIMITER ;


call 存储过程名(参数)

注意：
IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）
实际用途：
in：用于传入参数
out：用于返回值
inout：最好不要用
即使不传参数，小括号也要写

存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。

• 执行存储过程

MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为 CALL 。 CALL 接受存储过程的名字以及需要传递给它的任意参数。

CALL productpricing (@pricelow,@pricehigh,@priceaverage)

其中，执行名为 productpricing 的存储过程，它计算并返回产品的最低、最高和平均价格。

• 创建存储过程

CREATE PROCEDURE productpricing()
BEGIN 
SELECT Avg(prod_price) AS priceaverage
FROM products;
END;

• 删除存储过程

DROP PROCEDURE productpricing;

• 参数的使用

一般，存储过程并不显示结果，而是把结果返回给你指定的变量。

CREATE PROCEDURE productpricing(
OUT p1 DECIMAL(8,2),
OUT ph DECIMAL(8,2),
OUT pa DECIMAL(8,2)
)
BEGIN 
  SELECT Min(prod_price) INTO p1  FROM products;
  SELECT Max(prod_price) INTO ph  FROM products;
  SELECT Avg(prod_price) INTO pa  FROM products;
END;

每个参数必须具有指定的类型，这里使用十进制值。

关键字 OUT 指出相应的参数用来从存储过程传出一个值（返回给调用者）。

MySQL支持 IN （传递给存储过程）、OUT （从存储过程传出，如这里所用）、INOUT （对存储过程传入和传出）类型的参数

存储过程的代码位于 BEGIN 和 END 语句内，如前所见，它们是一系列SELECT 语句，用来检索值，然后保存到相应的变量（通过指定 INTO 关键字）。

• 复杂的存储过程

-- Name:ordertotal
-- Parameters: onumber = order number
--             taxable = 0  if not taxable , 1 if taxable
--             ototal  = order total variable 

CREATE PROCEDURE ordertotal (
 IN number INT,
 IN taxable BOOLEAN,
 OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total ,optionally adding tax'
BEGIN

-- Declare  variable  for  total	
DECLARE total DECIMAL(8,2);
-- Declare  tax  percentage
DECLARE taxrate INT DEFAULT 6;

-- Get the order total
SELECT Sum(item_price*quantity)
FROM orderitems
WHERE order_num = onumber
INTO total;

-- IS this taxable
IF  taxable THEN 
--  YES ,so add taxrate to the total
SELECT total+(total/100*taxrate)  INTO  total;
END IF;

--And finally,save to out variable
SELECT total INTO total;

END;

此存储过程有很大的变动。首先，增加了注释（前面放置 – ）。在存储过程复杂性增加时，这样做特别重要。添加了另外一个参数 taxable ，它是一个布尔值（如果要增加税则为真，否则为假）。在存储过程体中，用 DECLARE 语句定义了两个局部变量。 DECLARE 要求指定变量名和数据类型，它也支持可选的默认值（这个例子中的 taxrate 的默认被设置为 6% ）。 SELECT 语句已经改变，因此其结果存储到 total （局部变量）而不是 ototal 。 IF 语句检查 taxable 是否为真，如果为真，则用另一 SELECT 语句增加营业税到局部变量 total 。最后，用另一 SELECT 语句将total （它增加或许不增加营业税）保存到 ototal 。

• 检查存储过程

为显示用来创建一个存储过程的 CREATE 语句，使用 SHOW CREATE PROCEDURE 语句

SHOW CREATE PROCEDURE ordertotal;

为了获得包括何时、由谁创建等详细信息的存储过程列表，使用 SHOW PROCEDURE STATUS

限制过程状态结果 SHOW PROCEDURE STATUS 列出所有存储过程。为限制其输出，可使用 LIKE 指定一个过滤模式，例如：

事物处理

利用事务处理，可以保证一组操作不会中途停止，它们或者作为整体执行，或者完全不执行（除非明确指示）。

• 在使用事务和事务处理时，有几个关键词汇反复出现。下面是关于事务处理需要知道的几个术语：

事务（ transaction ）指一组SQL语句；
回退（ rollback ）指撤销指定SQL语句的过程；
提交（ commit ）指将未存储的SQL语句结果写入数据库表；
保留点（ savepoint ）指事务处理中设置的临时占位符（place-holder），你可以对它发布回退（与回退整个事务处理不同）。

• 使用 ROLLBACK

使用ROLLBACK命令来回退MYSQL语句

SELECT * FROM ordetotals;
START TRANSACTION;
DELECT FROM ordertotals;
SELECT *FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;

显然， ROLLBACK 只能在一个事务处理内使用（在执行一条 START TRANSACTION 命令之后）。

• 使用 COMMIT

一般的MySQL语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交（implicit commit），即提交（写或保存）操作是自动进行的。
但是，在事务处理块中，提交不会隐含地进行。为进行明确的提交，使用 COMMIT 语句，如下所示：

START TRANSACTION;
DELECT FROM orderitems WHERE order_num = 20010;
DELECT FROM order WHERE order_num = 20010;
COMMIT;

最后的 COMMIT 语句仅在不出错时写出更改。如果第一条 DELETE 起作用，但第二条失败，则 DELETE 不会提交（实际上，它是被自动撤销的）。

注意：隐含事务关闭 当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭（将来的更改会隐含提交）。

• 使用保留点

为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符。这样，如果需要回退，可以回退到某个占位符。这些占位符称为保留点。为了创建占位符，可如下使用 SAVEPOINT语句：

SAVEPOINT deletel;

每个保留点都取标识它的唯一名字，以便在回退时，MySQL知道要回退到何处。为了回退到本例给出的保留点，可如下进行：

ROLLBACK TO deletel;

保留点越多越好 可以在MySQL代码中设置任意多的保留点，越多越好。为什么呢？因为保留点越多，你就越能按自己的意愿灵活地进行回退。

释放保留点 保留点在事务处理完成（执行一条 ROLLBACK 或COMMIT ）后自动释放。自MySQL 5以来，也可以用 RELEASE SAVEPOINT 明确地释放保留点。

• 更改默认的提交行为

触发器

触发器会在某个表执行以下语句时而自动执行：DELETE、INSERT、UPDATE。

触发器必须指定在语句执行之前还是之后自动执行，之前执行使用 BEFORE 关键字，之后执行使用 AFTER 关键字。

BEFORE 用于数据验证和净化，AFTER 用于审计跟踪，将修改记录到另外一张表中。

DELIMITER $$
CREATE TRIGGER `库名`.`触发器名` BEFORE/AFTER INSERT/UPDATE/DELETE
    ON `库名`.`表名`
    FOR EACH ROW BEGIN
		写 SQL 语句，如 insert into logs(time) values(now());
    END$$
DELIMITER ;

当  ON `库名`.`表名` 发生指定的事件后，变灰自动触发所写的 SQL

使用函数

文本处理函数、日期和时间处理函数、数值处理函数

• 文本处理函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-G98cOoxK-1617709005920)(数据库.assets/1617173760486.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kwKqT4AH-1617709005931)(数据库.assets/1617173776390.png)]

• 日期和时间处理函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-36JYYICK-1617709005944)(数据库.assets/1617173825560.png)]

• 数值处理函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9S0VBlCX-1617709005960)(数据库.assets/1617173861980.png)]

DELETE、INSERT、UPDATE。

触发器必须指定在语句执行之前还是之后自动执行，之前执行使用 BEFORE 关键字，之后执行使用 AFTER 关键字。

BEFORE 用于数据验证和净化，AFTER 用于审计跟踪，将修改记录到另外一张表中。

DELIMITER $$
CREATE TRIGGER `库名`.`触发器名` BEFORE/AFTER INSERT/UPDATE/DELETE
    ON `库名`.`表名`
    FOR EACH ROW BEGIN
		写 SQL 语句，如 insert into logs(time) values(now());
    END$$
DELIMITER ;

当  ON `库名`.`表名` 发生指定的事件后，变灰自动触发所写的 SQL

使用函数

文本处理函数、日期和时间处理函数、数值处理函数

• 文本处理函数

• 日期和时间处理函数
  

• 数值处理函数