数据库常用基础语法这是我参与更文挑战的第13天，活动详情查看：更文挑战基础概念什么是数据库？数据库是程序用来有组织

这是我参与更文挑战的第13天，活动详情查看：更文挑战

基础概念

什么是数据库？

数据库是程序用来有组织的方式存储的数据集合。

创建称呼：

模式
行/列
主键/主码

检索数据

SELECT

SELECT column_name FROM table_name;/*对关键字大写，其他字符小写，有利于阅读*/
SELECT * FROM table_name;
SELECT DISINCT key FROM table_name;
SELECT key_name 
FROM table_name 
LIMIT NUMBER;/*返回不多于5行*/
SELECT key_name FROM database.table_name;
SELECT key_name FROM database.table_name ORDER BY key_name;
SELECT key_name FROM database.table_name ORDER BY key_name,key1_name;
SELECT key_name FROM database.table_name ORDER BY key_name DESC/ASC;/*升序是默认的*/
SELECT key_name FROM database.table_name 
ORDER BY key_name DESC
LIMIT 1;
SELECT key_name FROM database.table_name WHERE key_name=xxx;
SELECT key_name FROM database.table_name WHERE key_name<>xxx;
SELECT key_name FROM database.table_name WHERE key_name!=xxx;
SELECT key_name FROM database.table_name WHERE key_num BETWEEN 5 AND 10;
SELECT key_name FROM database.table_name WHERE key_name IS NULL;
SELECT key_name FROM database.table_name 
WHERE key_num = 100 AND key <= 10;
SELECT key_name FROM database.table_name 
WHERE key_num = 100 OR key <= 10;
SELECT key_name FROM database.table_name 
WHERE (key_num = 100 OR vend_id = 1003) AND key <= 10;
SELECT key_name FROM database.table_name 
WHERE key_num IN (1002,1003)
ORDER BY key_num;
SELECT key_name FROM database.table_name 
WHERE key_num NOT IN (1002,1003)
ORDER BY key_num;

通配符

LIKE

SELECT prod_id, prod_name
FROM product_table
WHERE prod_name LIKE 'jet%';

SELECT prod_id, prod_name
FROM product_table
WHERE prod_name LIKE '_jet%';

不要过度使用通配符，优先使用其他操作符
不到万不得已，不要把通配符用在搜索模式的开始处，此时搜索速度是最慢的。

正则表达式

基本字符匹配

SELECT prod_id, prod_name
FROM product_table
WHERE prod_name REGEXP BINARY '[^123a].1000|2000'
ORDER BY prod_name;
SELECT prod_id, prod_name
FROM product_table
WHERE prod_name REGEXP BINARY '[0-9][a-z]\\-\\.\\|\\f\\\'
ORDER BY prod_name;

重复元字符

元字符	说明
`*`	0个或者多个匹配
`+`	1个或多个匹配(等于{1,})
`?`	`？`匹配它前面的任何字符的0或1次出现
`{n}`	指定数目的匹配
`{n,}`	不少于指定数目的匹配
`{n,m}`	匹配数目的范围（m不超过255）

SELECT prod_name FORM product_table
WHERE prod_name REGEXP '\\([0-9] sticks?\\)'/*s背后的 ? 使得 s 可选*/
ORDER BY prod_name;
SELECT prod_name FORM product_table
WHERE prod_name REGEXP '[[:digit:]]{4}'/*s背后的 ? 使得 s 可选*/
ORDER BY prod_name;

定位符

元字符	说明
`^`	文本的开始
`$`	文本的结尾
`[[:<:]]`	词的开始
`{{:>:}}`	词的结尾

SELECT prod_name FORM product_table
WHERE prod_name REGEXP '^[0-9\\.]'/*s背后的 ? 使得 s 可选*/
ORDER BY prod_name;

REGEXP和LIKE的区别

LIKE匹配整个串，而REGEXP匹配子串。

通过使用^开头，&结尾表达式，可以使得REGEXP的作用和LIKE一样。

数据处理函数

文本函数：处理文本串
数值函数：算术操作
时间函数：处理日期
系统函数：返回DBMS的特殊信息

文本处理函数

函数	说明
`RTrim()`	去掉串右边的空格
`Left()`	返回串左边的字符
`Length()`	返回串的长度
`Lower()`	转换为小写
`LTrim()`	去掉串左边的空格
`Righy()`	返回串右边的字符
`Soundex()`	返回串的SOUNDEX值
`SubString()`	返回子串的字符
`Upper()`	转换为大写
`Locate()`	找出串的一个子串

时间函数

函数	说明
`AddDate()`
`AddTIme()`
`CurDate()`
`CurTime()`
`Date()`
`DateDiff()`
`Date_Add()`
`Date_Format()`
`Day()`
`DauOfWeek()`
`Hour()`
`Minute()`
`Month()`
`Now()`
`Second()`
`Time()`
`Yeat()`

SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) = '2020-9-01'

数值处理函数

函数	说明
`Abs()`
`Cos()`
`Exp()`
`Mod()`
`Pi()`
`Rand()`
`Sin()`
`Sqrt()`
`Tan()`

汇总数据

聚集函数

运行在行组上，计算和返回单个值的函数

确定表中行数
表中行组的和
找出表列的最大值、最小值、平均值

函数	说明
`AVG()`
`COUNT()`
`MAX()`
`MIN()`
`SUN()`

SELECT AVG(prod_price) AS avg_price FROM products
WHERE vend_id = 1003; 
/*avg_price是一个别名*/
SELECT COUNT(prod_price) AS avg_price FROM products
WHERE vend_id = 1003; 
/*avg_price是一个别名*/



SELECT AVG( DISTINCT prod_price) AS avg_price FROM products
WHERE vend_id = 1003; 
/*avg_price是一个别名*/
SELECT COUNT(*) AS num_item,
	AVG(prod_price) AS avg_price,
	MAX(prod_price) AS price_max,
	MIN(prod_price) AS price_min
FROM products; 
/*avg_price是一个别名*/

分组数据

数据分组

当需要返回多个行的分别的数据的时候，就需要分组把数据分为多个逻辑组。

创建分组

==GROUP BY==

GROUP BY 子句可以包含任意数目的列。（使得能对分组进行嵌套）
如果嵌套了分组，数据将在最后规定的分组上进行汇总，换句话说，建立分组的时候，制定的所有列都一起计算，所以不能从个别的列取回数据。
列出的每个列都必须是检索列或者有效的表达式（但不能是聚集函数），如果在SELECT中使用表达式，则必须在GROUP BY 中指定相同的表达式。
SELECT中的每个列都必须在GROUP BY子句中给出，除了聚集计算语句。
如果分组列中有NULL值，则NULL将作为一个分组返回

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id
HAVING COUNT(*) >= 2;

使用子查询

任何SQL语句都是查询，但此术语一般指SELECT语句。

SELECT cust_idFROM ordersWHERE order_num IN (SELECT order_num                   FROM oderitems                   WHERE prod_id = 'TNT2');

联结表

等值连接

SELECT vend_name, prod_name, prod_price
FROM venders, products
WHERE venders.vend_id = products.vend_id
AND orderitems.prod_id = priducts.prod_id
AND order_num = 2005;

内部连接

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

别名

SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS vend_title
FROM vendors
ORDER BY vend_name;


SELECT vend_name, prod_name
FROM venders AS v, products AS p, orderitems AS oi
WHERE c.cust_id = o.cust_id
AND oi.order_num = o.order_num
AND prod_id = 'TNTS';

自联结

SELECT pro_id ,prod_name
FROM products
WHERE vend_id = (SELECT vend_id
                FROM products
                WHERE prod_id - 'DTNTR');
                
SELECT pro_id ,prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
AND p2.prod_id = 'DTNTR';

自然联结

SELECT c.*, o.order_num, o.order_date,
			oi.prod_id, oi.quantitym OI.item_price
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
	AND oi.order_nu, = o.oder_num
	AND prod_id = 'EB';

外部联结

/*内部链接*/
SELECT customers.cust_id, orders.order_num
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id;

/*外部链接*/
SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER  JOIN orders
ON customers.cust_id = orders.cust_id;

组合查询

MYSQL允许执行多个查询，将结果作为单个结果集返回。

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

UNION中的没给查询必须包含相同的列，表达式或聚集函数。

插入数据

INSERT

INSERT INTO Custimers
VALUES('Pep E.dd',
      'CA',
      NULL,
      NULL),
 VALUES('Pep E.dd',
      'CA',
      NULL,
      NULL);
      
INSERT INTO Custimers(cust_name,
                     cust_city
                    cust_contact,
                    cust_email )
VALUES('Pep E.dd',
      'CA',
      NULL,
      NULL);
      
 INSERT INTO Custimers(cust_name,
                     cust_city
                    cust_contact,
                    cust_email)
             SELECT cust_name,
                     cust_city
                    cust_contact,
                    cust_email
             FROM custnew;

更新与删除

UPDATE

UPDATE IGNOGE customers
SET cust_name = 'THe YDYK'
cust_eamil = 'elter@qq.com'
cust_address = NULL/*删除*/
WHERE cust_id = 1005;

DELETE FROM customers
WHERE cust_id = 1006;

创建和操作表

CREATE TABLE customers
(
    cust_id	int		NOT NULL AUTO_INCREMENT,
    cust_name char(50) NOT NULL,
    PRIMARY KEY (cust_id, cust_name)
) ENGINE = InnoDB;

视图

视图的作用：

重用SQL语句
简化SQL操作，可以方便地重用它而不必知道其细节。（封装）
使用表的组成部分而不是整个表。
保护数据。
更改数据格式和表示。斯图克返回与底层表的表示和格式不同的数据。

创建视图

CREATE VIEW productCustomers AS
SELECT cust_num, cust_contact, prod_id
FROM customers, orders, orderitems
WHERE customers.cust_id = orderitems.cust_id
AND orderitems.order_id = orders.order_id;


CREATE VIEW vendorlocations AS
SELECT Contact(RTrim(vend_num), '(', RTrim(vend_country), ')')
FROM vendors
ORDER BY vend_num;

使用视图

SELECT cust_name, cust_contact
FROM prodictcustomers
WHERE prod_id = 'TNT2';


SELECT *
FROM vendorlocations;

存储过程

经常会有一个完整的操作需要多条语句才能完成，所以我们可以创造存储过程，为以后的使用保存的一条或者多条的MYSQL语句的集合，可以把它看作批文件。

封装
复用
完整
安全

优点：

简单
安全
高性能

缺点：

复杂
权限问题

创建存储过程

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

使用存储过程

CALL productpricing();

CALL productpricing(@pricelowm,
                   @pricehigh,
                  @priceaverage);

删除存储过程

DROP PROCEDURE productpricing;

常用语法

ALTER TABLE

用来更新已存在的表的模式

ALTER TABLE tablename
(
    ADD column	datatype [NULL|NOT NULL] [CONSTRAINTS],
     CHANGE column columns datatype [NULL|NOT NULL] [CONSTRAINTS],
    DROP colmn,
);

COMMIT

CREATE INDEX

用于在一个或多个列上创建索引

CRRAT INDEX indexname
on tablename (column[ASC|DESC]);

CREATE PROCEDURE

创建存储过程

个人总结： 看过上面这些内容之后，相信都会发现，数据库的常用语法说多不多，说少不少，但作为容易的编程语言，可以相信这是一个编程人员必须了解的内容，不管是平时的云隐维护，还是后台数据的链接，了解和掌握这些知识，都是你我必备的。