MYSQL 必知必会

  • 基本语句
    use test; --使用某一个数据库

DESCRIBE customers; --查看表的结构
show status; --查看数据库状态
show ERRORS; --结果为空
show grants; --结果如 GRANT ALL PRIVILEGES ON . TO '632'@'%' WITH GRANT OPTION

show tables; --列出当前数据库所有的表

  • 基本的查找语句
    SELECT * FROM products; --查找语句

SELECT * FROM products limit 0,2; --分页,前2条数据(从 0开始)
SELECT * FROM products limit 2 OFFSET 1; --分页,前两条数据,2表示共2条记录,1表示重第一条记录开始(从1开始)

SELECT * FROM products ORDER BY prod_price DESC; --降序排序

  • 简单查询条件
    SELECT * FROM products where prod_price BETWEEN 10 AND 20; -- 区间查找
    SELECT * FROM products where prod_price IN (10,20); -- in条件限制
    SELECT * FROM products where prod_price IS NOT NULL; -- 不为 NULL的记录
  • 通配符
    通配符查询时间更长,不要在搜索模式的开始处使用通配符,这是最慢的
    _ 匹配单个字符 % 匹配任意字符
    SELECT * FROM products where prod_name LIKE '_e%'; --查找第二个字母为e的产品名

  • 正则表达式
    使用关键字 REGEXP,MYSQL只是实现了正则表达式的一部分
    注意:REGEXP和LIKE的区别在于,like是匹配整个列,完全匹配,而REGEXP匹配的是子串,如果有子串符合就匹配成功。
    如果要使用REGEXP匹配像LIKE的效果,在匹配串前后分别加上 '^' 和 '$'即可。
    --匹配子串,'.'表示任意一个字符
    SELECT * FROM products where prod_name REGEXP '.000';

--或OR 匹配
SELECT * FROM products where prod_name REGEXP BINARY '1000|2000';

--默认匹配是不区分大小写,需要区分使用 binary
SELECT * FROM products where prod_name REGEXP BINARY 'JET';

--[123]表示 1或2或3。 1或2或3连接一个任意字符再加上3个0
SELECT * FROM products where prod_name REGEXP BINARY '[123]000';

--效果和上面的一样,1或2或3连接一个任意字符再加上3个0
SELECT * FROM products where prod_name REGEXP BINARY '(1|2|3)000';

--范围匹配,上面的[123]可以写成[1-3],当然字母也可以[a-z]
SELECT * FROM products where prod_name REGEXP BINARY '[1-3]000';

--^表示在[]里面表示的否定,除这些之外的意思。下面是是指包含除2000和3000的列
SELECT * FROM products where prod_name REGEXP BINARY '[^2-3]000';

--特殊字符匹配需要加上 \,例如 \.匹配. ,\-匹配-
SELECT * FROM products where prod_name REGEXP BINARY '\.';

--还有一些通用的字符集的特殊符号
如 [:alnum:],[:alpha:],[:digit:],[:xdigit:],[:lower:],[:upper:]
SELECT * FROM products where prod_name REGEXP '[[:digit:]]000';

--匹配多个实例
* 0-n, + 1-n, ? 0-1, {n} 指定n个, {n,} , {n,m} n-m个
SELECT * FROM products where prod_name REGEXP '[[:digit:]]+'; --包含一个数字的
SELECT * FROM products where prod_name REGEXP '[[:digit:]]{3}'; --至少3个数字的

--不涉及数据库记录,用字符串测试正则表达式。返回0不匹配,1匹配
SELECT 'hello' REGEXP '[0-9]'

  • 拼接多个列
    MYSQL使用 concat函数拼接,但是其他数据库可能使用+或者||等符号,AS使用别名
    SELECT CONCAT(prod_id,'(',vend_id,')') AS id_vend, prod_name FROM products;
  • 计算
    基本的+-/都可以直接在select使用
    SELECT products.
    , prod_price +1 FROM products;

  • MYSQL函数使用

    • 文本处理函数

函数有很多如left(),right(),locate(),substring()
--转为大写字母
SELECT UPPER(prod_name) FROM products;

--子串,计数从1开始,从第二个字符开始,截取长度为3
SELECT prod_name, SUBSTRING(prod_name,2,3) FROM products;

--返回remark字段后面2个字符,发音类似函数 soundex,可以发现 返回了 jianye的列,因为mysql认为 jianye和jienye读音相似
SELECT * FROM products WHERE SOUNDEX(prod_name) = SOUNDEX('1 ton anvil'); --会发现出现一些相同读音的记录

* 日期和时间处理

date() 返回日期时间的日期部分,同理time返回的是时间部分,day()返回日期的天数,还有year,month等
SELECT CURDATE(),date(note_date),time(note_date),day(note_date) from productnotes;

查找 8月的记录
SELECT CURDATE(),date(note_date),time(note_date),day(note_date) from productnotes where month(note_date) = 8;

* 数值处理函数

对于各个DBMS来说比较统一的函数
常用函数有 abs(), cos(),exp(),mod(),pi(),rand(),rand(种子),sin(),sqrt(),tan()等
select pi(), mod(9,3),exp(2),rand(),rand(10);

  • 聚集函数 aggregate function
    avg(),count(),max(),min(),sum(),除了这些函数,还有一些标准偏差聚类函数
    --avg(id) 返回id的平均数,只能统计一列,而且会忽略NULL值(max(),min(),sum()也一样
    SELECT AVG(ALL prod_price) FROM products; --ALL是默认可以不加
    SELECT AVG(DISTINCT prod_price) FROM products; --只包含不同的值平均

--avg,sum等 里面可以进行计算合并,可以多列运算之后调用函数
SELECT AVG(prod_price * 2) FROM products;

--max(),min() 如果用在文本就返回默认排序中,对应的最后一行和第一行的该列数据
SELECT MIN(prod_name), MAX(prod_name) FROM products; ORDER BY prod_name

--count(),方法有两种用法count() 统计行数,或者count(column)统计非NULL的行数。
SELECT COUNT(
) FROM products; --返回记录数,总行数
SELECT COUNT(prod_name) FROM products; --返回prod_name不为NULL的数目

--还可以多个聚集函数结合使用
SELECT AVG(DISTINCT prod_price),SUM(prod_price) FROM products;

  • 分组数据
    • 在select中使用表达式,则Gruop by中指定相同的表达式

    除聚集计算外,select中的每个列都要在group by中给出
    NULL值也会作为一个分组返回
    SELECT vend_id, COUNT(*) FROM products GROUP BY vend_id; --统计wend_id的products数量

    • 过滤分组 having

having 支持where的所有操作符,可以同时使用。区别在于where过滤行(分组前过滤),having过滤分组(分组后过滤)
SELECT vend_id, COUNT() FROM products GROUP BY vend_id HAVING COUNT() > 2;

使用order by 进行分组排序
SELECT vend_id, COUNT() FROM products GROUP BY vend_id HAVING COUNT() > 2 ORDER BY vend_id desc;

  • 子查询
    MYSQL4.1后引入,嵌套的数量没有限制,但性能会有所影响
    推荐写法是先保证内层查询正确后再编写外层查询
    SELECT * FROM orders WHERE order_num IN (
    SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

--作为计算字段的子查询,返回用户的订单数量
SELECT cust_name,
(SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id )
FROM customers ;

  • 联结表
    联结多少个表没有限定,但是关联太多表会影响性能
    --等值联结,返回笛卡尔积结果,假设两个表的行是m和n则返回m x n的记录,每一行两两联结
    SELECT * FROM vendors, products;

--内部联结INNER JOIN
SELECT * FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;

  • 高级联结
    • 自联结

--联结同样可以使用聚集函数
--自联结,顾名思义就是联结同一个表
--譬如要找出生产‘DTNTR’产品的厂商的其他产品,我们可以使用子查询
SELECT prod_id,prod_name FROM products WHERE vend_id = (SELECT vend_id FROM products where prod_id = 'DTNTR');

--也可以使用自联结,注意使用别名 AS,区分不同的表
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'

* 外部联结

--左联结 LEFT OUTER JOIN
SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;

--右联结 RIGHT OUTER JOIN
SELECT customers.cust_id, orders.order_num FROM customers RIGHT OUTER JOIN orders ON customers.cust_id = orders.cust_id;

  • 组合查询 UNION
    --组合两个查询结果,ALL 表示不过滤重复,默认会过滤重复的行
    SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <=5
    UNION ALL
    SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001,1002);
    order by排序,只能使用一条order by,且必须出现在最后的一条查询语句中
    SELECT vend_id, prod_id, prod_price, prod_name FROM products WHERE prod_price <=5
    UNION
    SELECT vend_id, prod_id, prod_price,prod_name FROM products WHERE vend_id IN (1001,1002)
    ORDER BY vend_id, prod_price ;
  • 全文搜索
    InnoDB不支持全文搜索,MyISAM支持
    使用的时候主要该表设置为MyISAM引擎,然后设置字段为 FULLTXT
    建表语句大概如此
    CREATE TABLE productnotes
    (
    note_id int NOT NULL AUTO_INCREMENT,
    note_text text NULL ,
    PRIMARY KEY(note_id),
    FULLTEXT(note_text)
    ) ENGINE=MyISAM;
    主要函数 Match() 指定搜索的列, Against()指定搜索的表达式。默认不区分大小写,可以使用Binary
    SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST('rabbit');

  • 数据插入
    INSERT INTO customers(cust_name) VALUES ('jam');
    --连续插入多条记录
    INSERT INTO customers(cust_name) VALUES ('jam'), ('haha');
    插入检索出来的记录
    INSERT INTO customers(cust_name) SELECT vendors.vend_name FROM vendors LIMIT 2 OFFSET 1; --插入两条记录

  • 更新数据
    UPDATE customers SET cust_email = 'new@111.com' WHERE cust_id = '10005';
    UPDATE customers SET cust_email = 'new@111.com', cust_city = 'China' WHERE cust_id = '10005'; --更新多列
    --默认有错会撤销所有的操作,加上IGNORE可以忽略个别错误
    UPDATE IGNORE customers SET cust_email = NULL, cust_city = 'USA' WHERE cust_id = '10005'; --set为NULL清空某一列

  • 删除数据
    DELETE FROM customers WHERE cust_id = '10013'; --请注意添加条件,不然会清空整个表

  • 表创建
    基本的建表语句 (IF NOT EXISTS 可选)
    PRIMARY KEY() 指定主键
    DEFAULT 后面跟默认值
    AUTO_INCREMENT 表示自增,该列必须被索引(例如设置它为主键,主键默认有索引)
    ENGINE 表引擎InnoDB,支持事务,但不支持FULLTEXT;MyISAM,不支持事务,但支持FULLTEXT;MEMORY 内存版的MyISAM,数据在内存,性能高。
    CREATE TABLE IF NOT EXISTS customers
    (
    cust_id int NOT NULL AUTO_INCREMENT,
    cust_name char(50) NOT NULL ,
    cust_email char(255) NULL DEFAULT 'haha',
    PRIMARY KEY (cust_id)
    ) ENGINE=InnoDB;

--增加外键,建立表关联
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
更新表 ALERT TABLE
ALTER TABLE vendors ADD vend_phone CHAR(20); --增加一列
ALTER TABLE vendors DROP vend_phone; --删除一列

  • 删除表
    DROP TABLE cust;
  • 重命名表
    RENAME TABLE customers to customers1;
    --多个多重命名
    RENAME TABLE customers to customers1, orders to orders1;

  • 视图
    MYSQL 5添加了视图的支持,使用视图的好处有
    1.重用SQL语句,视图本来就是有一些SQL组成

    1. 简化复杂的SQL操作, 隐藏实现细节
    2. 使用表的组成部分而不是使用整个表
    3. 保护数。可以让用户不能访问特定的表,但是可以通过视图访问部分数据
    4. 更改数据格式和表示

性能限制:因为视图相当于嵌套了查询。
注意:视图名唯一;必须有足够的权限;视图可以嵌套;视图有ORDER BY,但是select 检索该视图时,视图的ORDER BY会被覆盖;视图不能索引
--创建视图
CREATE VIEW myview AS SELECT * FROM customers ;

--像表一样使用,select的约束全部适用
SELECT * FROM myview;

--可以使用计算字段
CREATE VIEW OrderItemsExpanded AS
SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM OrderItems WHERE order_num = 20009;

  • 存储过程
    优点: 简单、安全、高性能。
    简化复杂的操作。
    封装一系列的SQL保证数据的安全的,简化变动的管理。
    存储过程通常以编译过的形式存储,提高性能。
    缺点: 不同DBMS中的存储过程语法有所不同。
    相对复杂,容易出错,需要更多的测试。
    --创建存储过程
    --DELIMITER $$ 表示重新定义结束符为$$,因为在命令行环境中我们的语句也是;号结束的,与存储过程的结束符冲突了,在图形界面环境则没这个问题
    --创建前先删除原来的,并且判断是存在才删除

DELIMITER $$
DROP PROCEDURE IF EXISTS MailingListCount;

CREATE PROCEDURE MailingListCount (IN src int, OUT s DOUBLE)
BEGIN
IF src = 1 THEN
SELECT COUNT(*) into s FROM Customers;
ELSEIF src = 2 THEN
SELECT pi() into s;
ELSE
SELECT pi()*pi() into s;
END IF;
END $$
DELIMITER ;

--调用,s为变量,mysql所有的变量都是以@开头,set设置变量的值
set @src = 3;
call MailingListCount(@src, @s);
select @s;
列出所有的存储过程
SHOW PROCEDURE STATUS;
SHOW PROCEDURE STATUS LIKE '%M%'; --支持条件查找
查看对应的存储过程
SHOW CREATE PROCEDURE MailingListCount;

  • 游标
    MYSQL游标在MYSQL5添加,而且只能用在存储过程中
    明确步骤:使用前必须先声明(定义)
    声明后,必须打开游标以供使用,这个过程会把定义的语句的检索出来
    填有数据的游标,根据需要检索出各行
    结束时必须关闭游标

DROP PROCEDURE IF EXISTS testCursor;
CREATE PROCEDURE testCursor (IN src int, OUT s DOUBLE)
BEGIN
DECLARE omCursor CURSOR
FOR
SELECT order_num FROM orders;

    DECLARE o INT; -- 定义变量

    OPEN omCursor; -- 打开游标
       FETCH omCursor INTO o; -- 获取单行游标数据
    CLOSE omCursor; -- 关闭游标

END;
循环遍历数据
存储过程部分省略,只给出变化的部分,从 DECLARE o INT; 后 面开始
-- DECLARE CONTINUE HANDLER 相当于终止条件 ,declare有特定的次序
-- 02000是一个未找到的条件,当SQLSTATE找不到行是就设置done=1,结束循环
DECLARE CONTINUE HANDLER FOR SQLSTATE '0200' SET done = 1;

    OPEN omCursor;        -- 打开游标
        REPEAT
            FETCH omCursor INTO o; -- 遍历游标数据
            -- 这里可以对o进行处理,可以调用存储过程之类的
        UNTIL done END REPEAT;
    CLOSE omCursor;-- 关闭游标
  • 触发器
    在某些改动的前后自动执行一些语句,例如这些改动 DELETE,INSERT,UPDATE
    用于保持数据的完整性或记录数据库操作信息方面,触发器不能够被直接调用,只能够在某些事件发生时被触发
    每个表每个事件只能存在一个触发器,因此每个表最多6个触发器
    触发器需要4条信息:触发器名;触发器关联的表;触发器相应的活动;触发器何时执行(before还是 after)
    PS:虽然可以不同表有相同名字的触发器,但是不推荐
    --创建触发器,在products表插入后 返回Product added
    --书本原句报错 CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added'
    --查找找问题后发现,必须要加变量以返回数据
    CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added' INTO @arg;

测试,可以发现arg=Product added
INSERT INTO products (prod_id,vend_id,prod_name,prod_price) VALUES ('1112',1001,'newPP',23);
SELECT @arg;
删除触发器
DROP TRIGGER newproduct;

*  instert触发器

如果我们需要获取新插入的数据的主键ID,是自增的。触发器提供一个NEW虚拟表,访问新插入的行
update after 也有 new关键字提供访问新的记录
CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num INTO @arg;

* delete触发器

delete 可以提供 OLD 虚拟表,访问删除行的数据

* update触发器

可以有 OLD也有 NEW 关键字

  • 事务
    是指作为单个逻辑工作单元执行的一系列操作,要么完全地执行,要么完全地不执行。
    并不是所有的数据库引擎都支持事务,例如MyISQM就不支持事务
    事务拥有相关属性(ACID):
    原子性(Atomicity):事务作为一个整体被执行,包含在其中的对数据库的操作要么全部被执行,要么都不执行。
    一致性(Consistency):事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
    隔离性(Isolation):多个事务并发执行时,一个事务的执行不应影响其他事务的执行。隔离又有四个级别(未提交读: 无读锁,允许“脏读”,事务可以看到其他事务“尚未提交”的修改;提交读: 读锁在SELECT操作完成后马上释放;可重复读: 在同一个事务内的查询都是事务开始时刻一致的, 读写锁一直保持到事务结束,读写不阻塞;可序列化: 读写相互都会阻塞,同时只允许一个事务提交。)
    隔离级别
    脏读 dirty reads

不可重复读幻影读未提交读可能发生可能发生可能发生提交读-可能发生可能发生可重复读--可能发生可序列化--

不可重复读:在一次事务中,当一行数据获取两遍得到不同的结果表示发生了“不可重复读”
InnoDB 默认是不可重复读的隔离级别
持久性(Durability):已被提交的事务对数据库的修改应该永久保存在数据库中。
基本的使用方式
START TRANSACTION;
INSERT INTO orders (order_date,cust_id) VALUES (CURDATE(),10001);
COMMIT; --提交结束事务,不能载回退
roolback 回退
只能回退instert,update, delete,事务中可以使用,但是不能回退 create和drop
START TRANSACTION;
INSERT INTO orders (order_date,cust_id) VALUES (CURDATE(),10001);
ROLLBACK; --rollback后发现没有插入数据
使用保留点,因为简单的rollback和commit是整个事务的,不够灵活,保留点可以rollback到指定保留点
没有保留点的数量限制,所以尽量多保留点使操作更灵活
START TRANSACTION;

INSERT INTO orders (order_date,cust_id) VALUES (CURDATE(),10002);
SAVEPOINT insert1;
INSERT INTO orders (order_date,cust_id) VALUES (CURDATE(),10003);
SAVEPOINT insert2;
ROLLBACK TO insert1; -- rollback到指定保留点
RELEASE SAVEPOINT insert2; -- MYSQL5以后可以手动释放保留点,commit自动释放还原点
COMMIT;

默认的SQL语句都是自动提交的
SET autocommit = 0; --设置为不自动提交,然后可以使用rollback回退或者commit提交

  • 字符集,全球化和本地化
    查看支持的字符集(校对集可以理解为排序规则等)如:
    ci: case insensitive,大小写不敏感,不区分大小写比较
    cs: case sensitive,大小写敏感,区分大小写比较
    bin: binary,二进制,使用二进制比较
    SHOW CHARACTER SET; (show charset;) --可用字符和默认校对,如 utf8
    SHOW COLLATION; --支持校对的完整列表,如 utf8_general_ci
    服务器默认字符集 -- 数据库默认字符集 -- 表默认字符集 -- 甚至到列默认字符集 ---某个级别没有指定,那么继承上级。
    查看数据库当前的字符集和校对规则
    show variables like '%character%';
    show variables like '%collation%';

--查看表的校对规则
show table status from db_name like '%table_name%' ;

--查看列的校对规则
show full columns from table_name;

注意获取长度的区别
select length('你'); /结果是3,3个字节,数据库采用的utf8编码/
select char_length('你'); /结果是1,1个字符/

order by 中指定 校对集 collate
SELECT * FROM customers ORDER BY cust_name COLLATE utf8_unicode_ci;

  • 安全管理
    查看用户
    USE mysql;
    SELECT user FROM user;
    创建用户 ben,密码是haha
    CREATE USER ben IDENTIFIED BY 'haha';
    重命名用户
    RENAME USER ben to ben1;
    删除用户
    DROP USER ben1;
    DROP USER IF EXISTS ben1; --可以先判断,一面出错

权限
SHOW GRANTS FOR ben; --查看ben的权限
分配权限GRANT(撤销权限为 REVOKE)
其中 整个服务器:GRANT ALL(REVOKE), 整个数据库: ON database.* , 特定表 ON database.table
权限较多,查对应的权限表
GRANT SELECT ON test.* to ben; --分配数据库test所有表的select权限给 ben

更改口令
SET PASSWORD FOR ben = PASSWORD('hahaha'); --PASSWORD('hahaha')表示加密

  • 数据库维护
    ANALYZE TABLE orders; --分析表是否正确
    CHECK TABLE orders;

导出
mysqldump -u 用户名 -p 数据库名 > 导出的文件名

mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名

导入
use 数据库
--set names utf8; --设置编码
source 导入的文件名

日志
位于 data目录下。一般日志名为hostname.err ,可以使用 --log-error 修改名字

  • 性能建议
    索引,检索快,但是其他操作会变慢
    正确的select
    减少嵌套关联
    可以尝试 UNION 代替 复杂的一系列OR
    like 很慢
    以上每个建议都可能在某个条件下打破

发表回复

您的电子邮箱地址不会被公开。

粤ICP备17041560号-2