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 很慢
    以上每个建议都可能在某个条件下打破

发表评论

电子邮件地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据