MySQL常⽤语句 表(或者数据库)的CRUD 表数据的CRUD，其中表数据查询使⽤最多，也更复杂。查询可以按照单表还是多表可以分为：单表SELECT查询和多表的联结查询(INNER JOIN, LEFT JOIN, RIGHT JOIN和FULL JOIN)以及组合查询UNION和UNION ALL SQL语句中各个关键字的执⾏顺序
MySQL的⾼级功能 存储过程 事务处理 触发器
列的值； DEFAULT表⽰该列的默认值，在插⼊⾏数据时，若没有给出该列的值就会使⽤其指定的默认值； PRIMARY KEY⽤于指定主键，主键可以指定⼀列数据，⽽可以由多列数据组合构成，如PRIMARY KEY(cust_id,cust_name)； ENGINE⽤于指定引擎类型。常见的引擎类型有这些：(1)InnoDB是⼀个⽀持可靠的事务处理的引擎，但是不⽀持全⽂本搜索；(2)MyISAM是⼀个性能极⾼的引擎，它⽀持全⽂本搜索，但是不⽀持事务处理；(3)MEMORY在功能上等同于MyISAM，但由于数据存储在内存中，速度很快(特别适合于临时表)；
3. 在创建表的时候可以使⽤FOREIGN KEY来创建外键，即⼀个表中的FOREIGN KEY指向另⼀个表中PRIMARY KEY。外键FOREIGN
KEY⽤于约束破坏表的联结动作，保证两个表的数据完整性。同时也能防⽌⾮法数据插⼊外键列，因为该列值必须指向另⼀个表的主键。实例为： CREATE TABLE Orders ( Id_O int NOT NULL, OrderNo int NOT NULL, Id_P int, PRIMARY KEY (Id_O), FOREIGN KEY (Id_P) REFERENCES Persons(Id_P) )
1.3 删除表(或数据库)
1. 删除数据库：DROP DATABASE customers；
2. 删除表，使⽤DROP TABLE⼦句：DROP TABLE customers。
1.4 更新表
1. 更新表结构信息可以使⽤ALTER TABLE⼦句，如为表增加⼀列：ALTER TABLE vendors ADD vend_name CHAR(20)；另外经
常⽤于定义外键，如： ALTER TABLE customers ADD CONSTRAINT fk_custormer_orders FOREIGN KEY(cust_id)
REFERENCES orders (order_cust)
2. 重命名表，使⽤RENAME⼦句。RENAME TABLE backup_customers TO customers, backup_vendors TO vendors；更改多
个表名，之间⽤逗号间隔
2 表数据操作语句
2.1 查询表数据
基本查询语句
1. 根据过滤条件查询表中的单列或者多列或者全部列的信息SELECT FROM WEHERE：SELECT cust_id,cust_name FROM
customers WHERE cust_id=10086；其中过滤条件操作符有：=，<>,!=,,>=,BETWEEN AND,IS NULL；
2. 为查询出的某⼀列信息去重DISTINCT：SELECT DISTINCT cust_name FROM customers；
3. 限制单列查询结果的⾏数：SELECT cust_name FROM customers LIMIT 5；LIMIT后跟⼀个数值，表⽰从第0⾏开始取，共取5⾏
数据；如果LIMIT 5，5表⽰从第5⾏(数据库中实际第6⾏记录)开始取，共取5⾏数据。注意：数据是从第0⾏开始计数的；
4. ORDER BY⼦句取⼀个或者多个列，据此对输出进⾏排序：SELECT cust_id,cust_name FROM customers ORDER BY cust_id
DESC, cust_name;
5. IN操作符⽤来指定条件范围，范围中的每个条件都可以进⾏匹配：SELECT cust_id, cust_name FROM customers WHERE
cust_id IN (1000,2000)。另外，NOT操作符可以和IN操作符配合使⽤，⽤于表⽰检索出不符合条件的所有数据；
6. LIKE操作符⽤来表明模糊查询，与之配合使⽤的通配符有**%**，%表⽰任何字符出现任何次数；_，_表⽰只能匹配⼀个字符：
SELECT cust_id,cust_name FROM customers WHERE cust_name LIKE '%happy%'；
7. 使⽤分组查询并可以满⾜⼀定的分组过滤条件GROUP BY HAVING。如检索总计订单⾦额⼤于等于50的订单号和订单总⾦额，并按
总⾦额进⾏排序：SELECT order_num,SUM(quantity*item_price) AS order_total FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50 ORDER BY order_total
8. WHERE和HAVING的⽐较。WHERE是⾏级过滤，⽽HAVING是组级过滤。被WHERE过滤掉的数据不会出现在分组中。WHERE中
通配符以及多个WHERE⼦句的连接同样适⽤于HAVING⼦句；
9. GROUP BY的使⽤注意事项： (1)GROUP BY⼦句中可以嵌套分组(即通过多个列进⾏分组GROUP BY cust_id, cust_name)，但是
进⾏数据汇总时，是在最后规定的分组上进⾏；(2)GROUP BY⼦句中列出的每个列都必须是检索列或者是有效的表达式。(3)如果有NULL值，将值NULL作为⼀个分组进⾏返回，如果有多⾏NULL值，它们将分为⼀组
10. 嵌套其他查询中的查询，称之为⼦查询。执⾏过程由⾥向外，⾥层查询结果作为外层查询的条件：SELECT cust_id FROM orders
WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'happy')。当然，多表的查询可以是⽤联结查询。
联结查询
1. 内联结⽤⼜称之为内部联结，是基于两个表 之间的的相等测试。如果不加过滤条件，会造成“笛卡尔积”。SELECT
vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id=products.vend_id；同样可以使⽤WHERE进⾏多表联结查询，但是更推荐使⽤INNER JOIN等联结⽅式；
2. 外部联结包括左外联结LEFT JOIN和右外联结RIGHT JOIN和全连接FULL JOIN。例如查询每个客户的订单数：SELECT
customers.cust_ders_num FROM customers LEFT JOIN orders ON orders.cust_id =customers.cust_id；
LEFT JOIN 会全部返回左表数据，RIGHT JOIN会全部返回右表数据，FULL JOIN会将左右两个表的数据全部返回；
3. 联结查询与聚集函数⼀起使⽤。如查询每个客户的订单数：SELECT
customers.cust_name,customers.cust_id,der_num) AS num_ord FROM customers INNER JOIN orders ON customers.cust_id=orders.cust_id GROUP BY customers.cust_id；
组合查询
1. 多个查询(SELECT)可以使⽤UNION将多个查询结果进⾏合并成⼀个结果集返回，UNION必须包含两个及两个以上的SELECT查询，
并且每个传必须包含相同的列、表达式或聚集函数，数据类型不必完全相同，MySQL会进⾏隐式的类型转换。SELECT
vend_id,prod_id,prod_price FROM products WHERE prod_price>5 UINON SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001,1002);
2. UNION返回的是去重后的结果，如果不需要去重则可以使⽤UNION ALL；
3. 可以多组合查询使⽤ORDER BY进⾏排序，但是是针对的最终的结果集进⾏排序，⽽不是其中单个SELECT查询进⾏排序，因此对于
组合查询来说ORDER BY⼦句只有⼀个。SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price>5 UINON SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001,1002) ORDER BY vend_id
使⽤函数对数据进⾏处理
1. 拼接列名：SELECT Concat (vendName,'(',vendCountry,')') FROM vendors ORDER BY vendName；
2. 执⾏算术表达式计算：SELECT prodId, quantity,price, quantity*price AS expandedPrice FROM orderItems；
3. ⽂本处理函数如Upper(),LTrim(),RTrim()等函数。⽐如使⽤Upper函数将⽂本转换成⼤写：SELECT vendName,
Upper(vendName) FROM vendors ORDER BY vendName；
4. 时间和⽇期处理函数，如Date(),Day()等。SELECT custId, orderNum FROM orders WHERE Date(orderDate)='2015-09-
01'；
5. 数值处理函数，如Abs(),Cos()等；
6. 常⽤的聚集函数。如AVG(),COUNT(),MAX(),MIN()以及SUM()。SELECT COUNT(*) AS numbers, MIN(prod_price) AS
price_min, MAX(prod_price) AS price_max,AVG(prod_price) AS price_avg FROM products；
2.2 插⼊表数据
1. 向表中插⼊⾏数据可以使⽤INSERT INTO⼦句，更安全的⽅式是指定列名。INSERT INTO customers (cust_name, cust_email)
VALUES('happy','happy@gmail')；在INSERT INTO⼦句中能够省略列名的前提条件是：该列可以允许定义为NULL值或者在定义该列时给出去了默认值；
2. 如果插⼊多⾏数据可以将多组值⽤逗号进⾏分隔即可。INSERT INTO customers (cust_name, cust_email)
VALUES('happy','happy@gmail'),('smart','smart@gmail')；
sql left join 多表连接3. 将查询出来的数据插⼊表中，可以使⽤INSERT SELECT语句。INSERT INTO customers(cust_id,cust_contact) SELECT
cust_id, cust_contact FROM customers WHERE cust_id>5；其中SELECT中可以带WHERE过滤条件；INSERT SELECT通常被⽤于复制表数据
2.3 更新表数据
1. 如果要更新表数据的话，使⽤UPDATE⼦句：UPDATE customers SET cust_name ='happy',cust_email='happy@gmail'
WHERE cust_id = 1001；
2. 注意：如果不加WHERE条件指定到某⼀⾏的话，会更新表中某⼀列全部的数据。
2.4 删除表数据
1. 如果从表中删除数据的话，可以使⽤DELETE⼦句。DELETE FROM customers WHERE cust_id = 10086；删除的数据必定是表
中⾏数据，⽽不是某⼀列。因此，与UPDATE⼦句相⽐，DELETE⼦句并不需要指定是哪⼀列，⽽仅仅只需要指定具体的表名即可；
2. 注意：如果不添加WHERE指定条件的话，会将整个表中所有⾏数据全部删除。另外，DELETE只是删除表中的数据，⽽不会删除表结
构信息；
3. 如果想删除表中全部的数据，可以使⽤TRUNCATE，⽐DELETE删除效率更⾼；
3. SQL中关键字执⾏顺序
在SQL语句中每个关键字都会按照顺序往下执⾏，⽽每⼀步操作，会⽣成⼀个虚拟表，最后产⽣的虚拟表会作为执⾏的最终结果返回。下⾯的是常⽤的关键字的执⾏顺序：
(8)SELECT (9)DISTINCT(1)FROM (3) JOIN (2)            ON (4)WHERE (5)GROUP BY(6)WITH{CUBE|ROLLUP}(7)HAVING(10)ORDER BY(11)LIMIT
1. FROM：对FROM左边的表和右边的表计算笛卡尔积，产⽣虚表VT1；
2. ON：对虚拟表VT1进⾏ON筛选，只有那些符合条件的⾏才会被记录在虚拟表VT2中；
3. JOIN：如果是OUT JOIN，那么将保留表中(如左表或者右表)未匹配的⾏作为外部⾏添加到虚拟表VT2中，从⽽产⽣虚拟表VT3；
4. WHERE：对虚拟表VT3进⾏WHERE条件过滤，只有符合的记录才会被放⼊到虚拟表VT4；
5. GROUP BY：根据GROUP BY⼦句中的列，对虚拟表VT4进⾏分组操作，产⽣虚拟表VT5；
6. CUBE|ROLLUP：对虚拟表VT5进⾏CUBE或者ROLLUP操作，产⽣虚拟表VT6；
7. HAVING：对虚拟表VT6进⾏HAVING条件过滤，只有符合的记录才会被插⼊到虚拟表VT7中；
8. SELECT：执⾏SELECT操作，选择指定的列，插⼊到虚拟表VT8中；
9. DISTINCT：对虚拟表VT8中的记录进⾏去重，产⽣虚拟表VT9；
10. ORDER BY：将虚拟表VT9中的记录按照进⾏排序操作，产⽣虚拟表VT10；
11. LIMIT：取出指定⾏的记录，产⽣虚拟表VT11，并将结果返回。
4. 索引
MySQL索引的建⽴对于MySQL的⾼效运⾏是很重要的，索引可以⼤⼤提⾼MySQL的检索速度。索引分单列索引和组合索引。单列索引，即⼀个索引只包含单个列，⽽组合索引，即⼀个索引包含多个列。
4.1 创建索引
创建索引有两种⽅式，⼀种是直接利⽤CREATE INDEX进⾏创建，另外⼀种则是通过修改表结构来进
⾏添加，则是利⽤ALTER TABLE语句。
1. 使⽤CREATE INDEX 语法为： CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] ON
table_name (index_col_name,...) 其中对应的语法变量信息如下： [UNIQUE|FULLTEXT|SPATIAL] 其中括号中的这三个关键字表⽰创建的索引类型，它们分别表⽰唯⼀索引、全⽂索引、空间索引三种不同的索引类型。如果我们不指定任何关键字，则默认为普通索引。 index_name index_name表⽰索引的名称，由⽤户⾃⾏定义，以便于以后对该索引进⾏修改等管理操作。 index_type index_type表⽰索引的具体实现⽅式，在MySQL中，有两种不同形式的索引——BTREE索引和HASH索引。在存储引擎为MyISAM 和InnoDB的表中只能使⽤BTREE，其默认值就是BTREE；在存储引擎为MEMORY或者HEAP的表中可以使⽤HASH和BTREE两种类型的索引，其默认值为HASH。 index_colname index_col_name表⽰需要创建索引的字段名称，我们还可以针对多个字段创建复合索引，只需要在多个字段名称之间以英⽂逗号隔开即可。此外，对于CHAR或VARCHAR类型的字段，我们还可以只使⽤字段内容前⾯的⼀部分来创建索引，只需要在对应的字段名称后⾯加上形如(length)的指令即可，表⽰只需要使⽤字段内容前⾯的length个字符来创建索引。在这⾥，我们以customers表的cust_name字段(类型为VARCHAR(50))为例，使⽤cust_name字段的6个字符前缀来创建索引。 CREATE INDEX idx_cust_name ON user (cust_name(6));
2. 使⽤ALTER TABLE 语法为： ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
(index_col_name,...) [USING index_type]
4.2 删除索引
删除指定表中指定名称的索引，语法为：
ALTER TABLE table_nameDROP INDEX index_name;
例如删除名称为idx_cust_name的索引，其SQL语句为：
ALTER TABLE customersDROP INDEX idx_cust_name;
4.3 修改索引
在MySQL中并没有提供修改索引的直接指令，⼀般情况下，我们需要先删除掉原索引，再根据需要创建⼀个同名的索引，从⽽变相地实现
修改索引操作。
--先删除ALTER TABLE userDROP INDEX idx_user_username;--再以修改后的内容创建同名索引CREATE INDEX idx_cust_name ON customers (cust_name(8));
4.4 查看索引
在MySQL中，要查看某个数据库表中的索引也⾮常简单，只需要使⽤以下两个命令中的任意⼀种即可。
--如果查看索引前，没有使⽤user db_name等命令指定具体的数据库，则必须加上FROM db_nameSHOW INDEX FROM table_name [FROM db_name]--如果查看索
5. 存储过程
1. 什么是存储过程？存储过程简单来说，就是为了复⽤性或者实现复杂的业务功能，⽽保存的⼀条或多条MySQL语句的集合，可将其视
为批⽂件；
2. 为什么使⽤存储过程？(1)通过把处理封装在容易使⽤的单元中，简化复杂的操作；(2)由于不要求反复建⽴⼀系列处理步骤，这保证
了数据的完整性，如果所有的开发⼈员和应⽤程序都使⽤同⼀存储过程，则所使⽤的代码都是相同的；(3)简化对变动的管理。如果表
名、列名或业务逻辑有变化，只需要更改存储过程的代码，使⽤它的开发⼈员甚⾄不需要知道这些变化，也就是具备了安全性；(4)提
⾼了性能，因为使⽤存储过程⽐单独使⽤SQL语句要快；(5)存储过程可⽤来编写功能更灵活的代码。因此，存储过程的具备三个特
性：简单可复⽤、安全以及⾼性能；
3. 存储过程的缺点？(1)存储过程编写⽐基本的SQL语句更加复杂，需要更⾼的技能；(2)可能没有创建存储过程的权限，数据库管理员
可能会限制创建存储过程的权限，允许⽤户使⽤存储过程，⽽不允许⽤户⾃由创建存储过程；
创建存储过程
1. 创建存储过程。如需要统计⽤户订单总⾦额，如果该⽤户需要交税的话，订单总⾦额则需要再加上税费 DELIMITER // CREATE
PROCEDURE ordertotal( IN custid INT, IN taxable BOOLEAN, OUT ototal DECIMAL(8,2) )COMMENT 'obtain total order
price' BEGIN /*declare variable for total*/ DECLARE total DECIMAL(8,2); DECLARE taxrate INT DEFAULT 6; /*get the
order total*/ SELECT SUM(item_price*item_quantity) INTO total FROM customers WHERE cust_id = custid; /*is this
taxable?*/ IF taxable THEN SELECT total+(total/100*taxrate) INTO total; END IF; SELECT total INTO ototal; END //
有这样⼀些细节： 1. 使⽤CREATE PROCEDURE语句进⾏创建，()圆括号中为存储过程的参数，其中参数类型有：1. IN类型，表⽰传递
给存储过程；2. OUT 类型，表⽰存储过程返回的结果，在调⽤存储过程时需要传⼊@开始的变量；3. INOUT类型，表⽰在存储过程中可以
传⼊和传出； 2. DECLARE⽤来声明⼀个变量，如这⾥的total，taxrate。注意MySQL中定义变量时都是变量名在前，数据类型在后。 3.
存储过程具体逻辑写在BEGIN END之间； 4. 将值赋给变量使⽤INTO关键字； 5. 由于存储过程中每个SQL语句中⽤；作为分隔符，会和单
个SQL造成冲突，因此可使⽤DELIMITER重新定义分类符，如该例⼦中定义//为分隔符，⾃然存储过程结尾就⽤END //结尾，⽽不再是
END。同时，分隔符//成对出现后，恢复到默认的";"作为分隔符；
执⾏存储过程

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。