MySQL的基本操作可以包括两个方面:MySQL常用语句如高频率使用的增删改查(CRUD)语句和MySQL高级功能,如存储过程,触发器,事务处理等。而这两个方面又可以细分如下:
1. 表(或数据库)操作语句
1.1. 查询表(或数据库)
获取所有可用的数据库:SHOW DATABASES;选择数据库:USE customers;用于显示数据库服务器的状态信息:SHOW STATUS;用来显示授权用户的安全权限:SHOW GRANTS;用来显示数据库服务器或警告信息:SHOW ERRORS 或者 SHOW WARNINGS;用于显示创建数据库时的创建语句:SHOW CREATE DATABASE customers;用于显示创建表时的创建语句:SHOW CREATE TABLE customers;获取当前所选的数据库中所有可用的表:SHOW TABLES;获取表中所有列的信息:SHOW COLUMNS FROM tableName;同时DESCRIBE语句有相同的效果:DESCRIBE tableName;1.2. 新建表(或)数据库
新建数据库1.3 删除表(或数据库)
删除数据库:DROP DATABASE customers;删除表,使用DROP TABLE子句:DROP TABLE customers。1.4 更新表
更新表结构信息可以使用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) 重命名表,使用RENAME子句。RENAME TABLE backup_customers TO customers, backup_vendors TO vendors;更改多个表名,之间用逗号间隔2 表数据操作语句
2.1 查询表数据
基本查询语句
根据过滤条件查询表中的单列或者多列或者全部列的信息SELECT FROM WEHERE:SELECT cust_id,cust_name FROM customers WHERE cust_id=10086;其中过滤条件操作符有:=,<>,!=,,>=,BETWEEN AND,IS NULL;为查询出的某一列信息去重DISTINCT:SELECT DISTINCT cust_name FROM customers;限制单列查询结果的行数:SELECT cust_name FROM customers LIMIT 5;LIMIT后跟一个数值,表示从第0行开始取,共取5行数据;如果LIMIT 5,5表示从第5行(数据库中实际第6行记录)开始取,共取5行数据。注意:数据是从第0行开始计数的;ORDER BY联结查询
内联结用又称之为内部联结,是基于两个表 之间的的相等测试。如果不加过滤条件,会造成“笛卡尔积”。SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id=products.vend_id;同样可以使用WHERE进行多表联结查询,但是更推荐使用INNER JOIN等联结方式;外部联结包括左外联结LEFT JOIN和右外联结RIGHT JOIN和全连接FULL JOIN。例如查询每个客户的订单数:SELECT customers.cust_id,orders.orders_num FROM customers LEFT JOIN orders ON orders.cust_id =customers.cust_id;LEFT JOIN 会全部返回左表数据,RIGHT JOIN会全部返回右表数据,FULL JOIN会将左右两个表的数据全部返回;联结查询与聚集函数一起使用。如查询每个客户的订单数:SELECT customers.cust_name,customers.cust_id,COUNT(orders.order_num) AS num_ord FROM customers INNER JOIN orders ON customers.cust_id=orders.cust_id GROUP BY customers.cust_id;组合查询
多个查询(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);UNION返回的是去重后的结果,如果不需要去重则可以使用UNION ALL;可以多组合查询使用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使用函数对数据进行处理
拼接列名:SELECT Concat (vendName,'(',vendCountry,')') FROM vendors ORDER BY vendName;执行算术表达式计算:SELECT prodId, quantity,price, quantity*price AS expandedPrice FROM orderItems;文本处理函数如Upper(),LTrim(),RTrim()等函数。比如使用Upper函数将文本转换成大写:SELECT vendName, Upper(vendName) FROM vendors ORDER BY vendName;时间和日期处理函数,如Date(),Day()等。SELECT custId, orderNum FROM orders WHERE Date(orderDate)='2015-09-01';数值处理函数,如Abs(),Cos()等;常用的聚集函数。如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 插入表数据
向表中插入行数据可以使用INSERT INTO子句,更安全的方式是指定列名。INSERT INTO customers (cust_name, cust_email) VALUES('happy','happy@gmail.com');在INSERT INTO子句中能够省略列名的前提条件是:该列可以允许定义为NULL值或者在定义该列时给出去了默认值;如果插入多行数据可以将多组值用逗号进行分隔即可。INSERT INTO customers (cust_name, cust_email) VALUES('happy','happy@gmail.com'),('smart','smart@gmail.com');将查询出来的数据插入表中,可以使用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 更新表数据
如果要更新表数据的话,使用UPDATE子句:UPDATE customers SET cust_name ='happy',cust_email='happy@gmail.com' WHERE cust_id = 1001;注意:如果不加WHERE条件指定到某一行的话,会更新表中某一列全部的数据。2.4 删除表数据
如果从表中删除数据的话,可以使用DELETE子句。DELETE FROM customers WHERE cust_id = 10086;删除的数据必定是表中行数据,而不是某一列。因此,与UPDATE子句相比,DELETE子句并不需要指定是哪一列,而仅仅只需要指定具体的表名即可;注意:如果不添加WHERE指定条件的话,会将整个表中所有行数据全部删除。另外,DELETE只是删除表中的数据,而不会删除表结构信息;3. SQL中关键字执行顺序
在SQL语句中每个关键字都会按照顺序往下执行,而每一步操作,会生成一个虚拟表,最后产生的虚拟表会作为执行的最终结果返回。下面的是常用的关键字的执行顺序:
<code>(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/<code>FROM:对FROM左边的表和右边的表计算笛卡尔积,产生虚表VT1;ON:对虚拟表VT1进行ON筛选,只有那些符合条件的行才会被记录在虚拟表VT2中;JOIN:如果是OUT JOIN,那么将保留表中(如左表或者右表)未匹配的行作为外部行添加到虚拟表VT2中,从而产生虚拟表VT3;WHERE:对虚拟表VT3进行WHERE条件过滤,只有符合的记录才会被放入到虚拟表VT4;GROUP BY:根据GROUP BY子句中的列,对虚拟表VT4进行分组操作,产生虚拟表VT5;CUBE|ROLLUP:对虚拟表VT5进行CUBE或者ROLLUP操作,产生虚拟表VT6;HAVING:对虚拟表VT6进行HAVING条件过滤,只有符合的记录才会被插入到虚拟表VT7中;
4. 索引
MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,而组合索引,即一个索引包含多个列。
4.1 创建索引
创建索引有两种方式,一种是直接利用CREATE INDEX进行创建,另外一种则是通过修改表结构来进行添加,则是利用ALTER TABLE语句。
使用CREATE INDEX 语法为: CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] ON table_name (index_col_name,...) 其中对应的语法变量信息如下: [UNIQUE|FULLTEXT|SPATIAL] 其中括号中的这三个关键字表示创建的索引类型,它们分别表示唯一索引、全文索引、空间索引4.2 删除索引
删除指定表中指定名称的索引,语法为:
<code>ALTER TABLE table_name DROP INDEX index_name;/<code>
例如删除名称为idx_cust_name的索引,其SQL语句为:
<code>ALTER TABLE customers DROP INDEX idx_cust_name;/<code>
4.3 修改索引
在MySQL中并没有提供修改索引的直接指令,一般情况下,我们需要先删除掉原索引,再根据需要创建一个同名的索引,从而变相地实现修改索引操作。
<code>--先删除 ALTER TABLE user DROP INDEX idx_user_username; --再以修改后的内容创建同名索引 CREATE INDEX idx_cust_name ON customers (cust_name(8));/<code>
4.4 查看索引
在MySQL中,要查看某个数据库表中的索引也非常简单,只需要使用以下两个命令中的任意一种即可。
<code>--如果查看索引前,没有使用user db_name等命令指定具体的数据库,则必须加上FROM db_name SHOW INDEX FROM table_name [FROM db_name] --如果查看索引前,没有使用user db_name等命令指定具体的数据库,则必须加上db_name.前缀 SHOW INDEX FROM [db_name.]table_name/<code>
5. 存储过程
什么是存储过程?存储过程简单来说,就是为了复用性或者实现复杂的业务功能,而保存的一条或多条MySQL语句的集合,可将其视为批文件;为什么使用存储过程?(1)通过把处理封装在容易使用的单元中,简化复杂的操作;(2)由于不要求反复建立一系列处理步骤,这保证了数据的完整性,如果所有的开发人员和应用程序都使用同一存储过程,则所使用的代码都是相同的;(3)简化对变动的管理。如果表名、列名或业务逻辑有变化,只需要更改存储过程的代码,使用它的开发人员甚至不需要知道这些变化,也就是具备了安全性;(4)提高了性能,因为使用存储过程比单独使用SQL语句要快;(5)存储过程可用来编写功能更灵活的代码。因此,存储过程的具备三个特性:创建存储过程
创建存储过程。如需要统计用户订单总金额,如果该用户需要交税的话,订单总金额则需要再加上税费 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
执行存储过程
使用CALL子句执行存储过程,CALL子句接受存储过程的名称以及需要传递的参数。 CALL ordertotal(1,TRUE,@total); SELECT @total;如果存储过程中定义了OUT类型的输入参数,那么在执行存储过程时需要传入变量,如这里@total,并且变量都是用@开始的。如果存储过程中没有参数的话,就用空圆括号表示即可,CALL ordertotal();
删除存储过程
删除存储过程,可以使用DROP PROCEDURE子句。如DROP PROCEDURE ordertotal;查询存储过程
显示创建一个存储过程的语句,可以使用SHOW CREATE PROCEDURE。如SHOW CREATE PROCEDURE ordertotal;查询所有存储过程的状态,如果在定义存储过程中使用COMMENT添加注释,可以查看。同时可以LIKE进行过滤结果。如SHOW PROCEDURE STATUS LIKE '%order%';6. 事务处理
什么是事务? 事务处理是用来维护数据库的完整性,它保证成批的MySQL操作要么完全执行,要么完全不执行。事务处理是一种机制,用来管理必须成批执行的MySQL操作,它们要么时作为整体执行或者完全不执行。关键概念: 事务:是指一组SQL语句; 回退:是指撤销指定的SQL语句的过程; 提交:指将未存储的SQL语句的结果写入数据库表中; 保留点:指事务处理中设置的临时占位符,可以对它发布回退;如何创建执行事务? START TRANSACTION; INSERT INTO customers (cust_name,item_price,item_quantity) VALUES ('1',5,18); SELECT * FROM customers; SAVEPOINT insertinto; INSERT INTO customers (cust_name,item_price,item_quantity) VALUES ('2',5,18); ROLLBACK TO insertinto; 执行结果为:插入数据('1',5,18)有效,因为,只会从保留点SAFEPOINT之后开始回退,也就是说保留点SAFEPOINT之前的SQL语句执行的结果仍然有效。 有这样一些细节: STAET TRANSACTION用来表示下面的SQL语句集为一段事务; SAFEPOINT 用于指定保留点insertinto; ROLLBACK TO表示从指定保留点开始回退,也就是说保留点之前的SQL语句执行结果依然有效。如果仅仅使用ROLLBACK进行回退的话就表示从STAET TRANSACTION之后所有的SQL语句执行效果都会撤销;MySQL提交(写或保存)操作是自动进行的,这称之为隐含提交。但是在事务处理块中,提交不会隐含进行,要使用COMMIT子句进行提交。如: START TRANSACTION; INSERT INTO customers (cust_name,item_price,item_quantity) VALUES ('1',5,18); INSERT INTO customers (cust_name,item_price,item_quantity) VALUES ('2',5,18); COMMIT; 采用COMMIT提交事务,如果两条SQL语句都执行成功,才会将数据都写入表中。