`
TRAMP_ZZY
  • 浏览: 140001 次
社区版块
存档分类
最新评论

SQL 笔记(三)

    博客分类:
  • SQL
阅读更多
1. 组合查询
	包含或取消重复的行(使用UNION 时,重复的行被自动取消)
	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);
	+---------+---------+------------+
	| vend_id | prod_id | prod_price |
	+---------+---------+------------+
	|    1003 | FC      | 2.5        |
	|    1002 | FU1     | 3.42       |
	|    1003 | SLING   | 4.49       |
	|    1003 | TNT1    | 2.5        |
	|    1001 | ANV01   | 5.99       |
	|    1001 | ANV02   | 9.99       |
	|    1001 | ANV03   | 14.99      |
	|    1002 | FU1     | 3.42       |
	|    1002 | OL1     | 8.99       |
	+---------+---------+------------+
	
	对组合查询拍讯
	在UNION 组合查询时,只能使用一条ORDER BY子句,它必须出现在最后一条SELECT语句之后。
	
2. 全文本搜索
	MySQL 支持几种基本的数据库引擎。两个最常使用的为MyISAMySQL和InnoDB,前者支持全文本搜索,
	而后者不支持。
	为了使用全文本搜索,必须索引被搜索的列,而且要随着数据的改变不断地重新索引。在对表进行适当
	的设计后,MySQL 会自动进行所有的索引和重新索引。
	在索引之后,SELECT可与Match() 和 Against() 一起使用以实际执行搜索。
	
	启用全文本搜索支持
	在创建表时启用,create table 语句时接受 FULL TEXT 子句,它给出被索引列的一个逗号分隔的列表。
	
	create table sample (
    -> note_id int not null auto_increment,
    -> prod_id char(10) not null,
    -> note_date datetime not null,
    -> note_text text null,
    -> primary key(note_id),
    -> FULLTEXT(note_text)	// 在定义后MySQL 自动维护该索引
    -> )ENGINE=MyISAM;
	
	进行全文搜索
	Match() 指定被搜索的列,Against() 指定要使用的搜索表达式。
	select note_text from productnotes where Match(note_text) Against('rabbit');
	+---------------------------------------------------------------------------------------------------------------------+
	| note_text                                                                                                           |
	+---------------------------------------------------------------------------------------------------------------------+
	| Customer complaint: rabbit has been able to detect trap, food apparently less effective now.                        |
	| Quantity varies, sold by the sack load.
	All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
	+---------------------------------------------------------------------------------------------------------------------+
	
	查看计算的权重
	select note_id, note_text , Match(note_text) Against('rabbit') as rank from productnotes;
	
	使用查询扩展
	select note_text from productnotes where Match(note_text) against('anvils');
	select note_text from productnotes where Match(note_text) against('anvils' WITH QUERY EXPANSION);
	 
	布尔文本搜索
	及时没有FULLTEXT 索引也可以使用 
	为了匹配heavy 但不包含rope 开始的词的行
	select note_text from productnotes where
    -> Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);
	
	全文本boolean 操作符
	+	包含
	-	排除
	>	包含,而且增加等级值
	<	包含,且减少等级值
	()	把词组成子表达式
	~	取消一个词的排序值
	*	词尾的通配符
	""	定义一个短语(与单个词的列表不一样,它匹配整个短语以便包含或排除这个短语)
	
3. 插入数据
	如果数据检索式最重要的,则可以通过
	insert low_priority into 降低 insert 的优先级。
	
	插入多条数据,只要insert 语句中的列名(和次序)相同,可以如下组合各语句:
	insert into customers(cust_name,
		cust_address,
		cust_city,
		cust_state)
	values(
		'Pep',
		'',
		''
	),
	(
	'Pep',
		'',
		''
	)
	
	插入检索出来的数据
	insert into customers() select cust_id,.... from custnew;
	
4. 更新和删除数据
	如果想从表中删除所有行,不要使用DELETE。可以使用TRUNCATE TABLE。
	该语句不是逐行删除,而是删除表,再构建一个空表。
	
	更新和删除的知道原则:
	
5. 创建和操纵表
	主键: primary key() 多个值由逗号隔开。
	PRIMARY KEY(order_num, order_item)
	
	AUTO_INCREMENT 每个表只允许一个auto_increment,而且它必须被索引。
	
	如何获得 auto_increment 的值?
	select last_insert_id();
	
	默认值:
	MySQL 与大多数DBMS 不一样,MySQL不允许使用函数作为默认值,它只支持常量。
	
	混用存储引擎一个大缺陷是外键不能跨引擎。
	
6. 更新表
	添加列
	alter table vendors add vend_phone char(20);
	
	删除列
	alter table vendors drop column vend_phone,
	
	ALTER TABLE 的一种常见用途是定义外键。
	alter table orderitems add constraint fk_orderitems_orders foreign key(order_num) 
	refferences order (order_num)
	
7. 删除表
	drop table customers2;
	
8. 重命名表
	rename table customers2 to customers;
	
9. 使用视图(view)

10. 存储过程
	可以将存储过程视为批处理文件。
	
	执行存储过程
	call productpricing(@pricelow, @pricehigh, @priceaverage);
	
	创建存储过程
	create procedure productpricing()
    -> begin
    -> select Avg(prod_price) as priceaverage from products;
    -> end;
	
	改变命令行客户机的分隔符
	delimiter //
	create procedure productpricing()
	begin
		select Avg(prod_price) as priceagerage from products;
	end //
	
	// 恢复分隔符
	delimiter ;
	
	调用 call productpricing();
	call productpricing();
	+--------------+
	| priceaverage |
	+--------------+
	| 16.133571    |
	+--------------+
	
	删除存储过程, 删除的时候只给出存储过程名
	drop procedure productpricing;
	drop procedure if exists
	
	创建带参数的存储过程
	 create procedure productpricing(
    -> out pl decimal(8,2),
    -> out ph decimal(8,2),
    -> out pa decimal(8,2))
    -> begin
    -> select min(prod_price) into pl from products;
    -> select max(prod_price) into ph from products;
    -> select avg(prod_price) into pa from products;
    -> end;
	
	参数类型
	IN OUT INOUT
	
11. 游标的使用(MySQL的游标只能用于存储过程)
	a. 在能够使用之前,必须定义它。
	b. 一旦声明之后,必须打开游标以供使用。
	c. 对于填有数据的游标,根据需要取出检索各行。
	d. 在结束游标使用时,必须关闭游标。

	create PROCEDURE processorders()
	BEGIN
		Declare ordernumbers CURSOR
		FOR 
		SELECT order_num FROM orders;
	END;
	
	打开游标
	OPEN ordernumbers;
	
	关闭游标
	CLOSE ordernumbers;
	
	使用游标数据
	
12. 触发器
	触发器是MySQL响应DELETE,  INSERT, UPDATE 语句而自动执行的一条MySQL语句。
	CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added';
	
	只有表才支持触发器,视图不支持。(临时表也不支持)
	
	触发器按每个表每个事件每次地定义,每个表每个事件每次只允许一个出发器。因此,每个表最多支持
	6个触发器(每条insert update 和 delete 的之前和之后)。单一触发器不能与多个事件或多个表关联。
	
	删除触发器
	drop trigger newproducts;
	触发器不能更新或覆盖。为了修改一个触发器,必须先删除它,然后再重新创建。
	在insert 触发器代码内,可以引用一个名为NEW 的虚拟表,访问被插入的行。
	create trigger neworder after insert on orders for each row select NEW.order_num;
	
	DELETE 触发器代码内。可以引用一个名为OLD的虚拟表。访问被删除的行。
	
13. 管理事物
	事物处理用来管理insert、update和delete 语句。不能回退select 语句。事物处理中可以使用
	create 或drop 操作。但是也不能回退。
	
14. 更改默认的提交行为。
	set autocommit = 0;
	
15. 全球化和本地化
	SHOW CHARACTER SET; 
	显示系统所有可用的字符集。
	
	查看所支持校对的完整列表。使用如下语句:
	SHOW COLLATION;
	
	显示所用的字符集和校对:
	show variables like 'character%';
	show variables like 'collation%';
	
	创建表时指定字符集和校对:
	create table mytable(
		column1 INT,
		column2 varchar(10)
	) DEFAULT CHARACTER SET hebrew 
	COLLATE hebrew_general_ci;
	
	为列指定默认的字符集
	create table mytable(
		column1 INT,
		column2 varchar(10) character set latin1 collate latin1_general_ci
	) DEFAULT CHARACTER SET hebrew 
	COLLATE hebrew_general_ci;
	
	可以再select 语句中使用collate 指定一个备用的校对顺序。
	select * from customers order by lastname, firstname collater latin1_general_cs;
	(一种临时区分大小写的技术。)
	
16. 安全管理
	管理用户 MySQL 数据库有一个名为user 的表,它包含所有的用户账号。user 表有一个名为user 的列。
	它存储用户登录名。
	
	创建用户账号
	create user ben identified by 'zhang';
	重命名用户
	rename user ben to bforta;
	
	删除用户
	drop user bforta;
	
	设置访问权限,查看用户账号的权限
	GRANT 要求你至少给出以下信息:
	要授予的权限、被授予访问权限的数据库或表、用户名
	grant select on carshcouse.* to bforta;
	show grants for bforta;
	
	取消权限的语句
	REVOKE select on crashcourse.* from beforta;
	
	GRANT 和REVOKE可在几个层次上控制访问权限:
	整个服务器,使用GRANT ALL 和 REVOKE ALL
	整个数据库,使用 ON database.*
	特定的表,使用 ON database.table
	特定的列
	特定的存储过程
	
	简化多次授权:
	GRANT SELECT, INSERT ON crashcourse.* TO bforta;
	
	更改口令:
	SET PASSWORD FOR bforta = PASSWORD('zzy');
	在不指定用户名时,SET PASSWORD 是更新当前登录用户的口令。
	
17. 数据库维护
	进行数据库维护 ANALYZE TABLE 
	 analyze table orders;
	+-----------------+---------+----------+----------+
	| Table           | Op      | Msg_type | Msg_text |
	+-----------------+---------+----------+----------+
	| sqltest2.orders | analyze | status   | OK       |
	+-----------------+---------+----------+----------+
	
	check table 用来针对许多问题对表进行检查。
	changed 检查最后几次检查依赖改动过的表。
	extended 执行最彻底的检查。
	fast 只检查未正常关闭的表。
	medium 检查所有被删除的链接并进行键检验。
	quick 只进行快速扫面。
	
	MyISAM 表访问产生不正确和不一致的结果,可能需要用repair table来修复相应的表。
	如果从一个表中删除大量的数据,应该使用optimize table 来回收所用的空间。从而优化
	表的性能。
	
	诊断启动问题:
	MySQL 服务器自身通过在命令行执行mysqld 启动。
	
18. 改善性能
	like 很慢,一半来说最好使用fulltext 而不是 like。
	
分享到:
评论

相关推荐

    sql笔记.md

    sql笔记.md

    oracle_sql笔记

    Oracle SQL是数据库管理员和开发人员在Oracle数据库系统中进行数据查询和管理的重要工具。这篇笔记主要涵盖了Oracle SQL的...这两份“Oracle SQL笔记”文档应包含了上述各个方面的详细解释和实例,值得仔细阅读和学习。

    20170909学习sql笔记

    标题“20170909学习sql笔记”表明这是一个关于SQL学习的资料,可能包含了一天的学习记录或者一个教程的集合。SQL,全称Structured Query Language,是用于管理和处理关系数据库的标准语言。这个标题暗示我们将探讨...

    JAVA 与 Sql学习笔记

    【JAVA与Sql学习笔记】 在Java编程中,与SQL数据库的交互是不可或缺的一部分。这篇学习笔记主要关注如何在Oracle数据库中使用PL/SQL的FORALL语句进行批量操作,以及如何利用批绑定(Bulk Binding)来提升性能。此外...

    SQL笔记!很详细的!

    根据提供的文件信息,我们可以整理出以下关于SQL Server 2005的相关知识点: ### SQL Server 2005概述 SQL Server 2005是一款由微软公司开发的关系型数据库管理系统,是SQL Server系列中的一个重要版本。它在SQL ...

    SQL Server 笔记.docx

    SQL Server 数据库管理笔记 SQL Server 是一种关系型数据库管理系统,由 Microsoft 公司开发,广泛应用于各种行业和领域。作为一名 ITIndustry 大师,我将根据提供的文件信息,总结出相关的知识点,帮助您快速了解 ...

    郝斌Sql2005的笔记

    郝斌老师的笔记主要涵盖了数据库的基础概念、操作以及约束等方面的知识,旨在帮助学习者更好地理解和记忆SQL Server 2005的关键点。 首先,数据库是通过字段、记录、表和约束来存储数据的。字段是数据的基本单位,...

    最全的ORACLE-SQL笔记

    【Oracle SQL笔记详解】 Oracle SQL是用于访问和操作Oracle数据库的强大工具,涵盖了各种查询、更新和管理数据的方法。以下是对笔记中提及的一些关键知识点的详细解释: 1. **登录Oracle数据库**:通常以超级管理...

    OracleSQL笔记

    ### Oracle SQL 笔记知识点详解 #### 一、SQLPlus 命令及环境变量 Oracle_sid - **SQLPlus 命令位置**:在 Oracle 安装目录下的 `bin` 文件夹中,可以通过 SQLPlus 来执行 SQL 命令。 - **Oracle_sid 环境变量**:...

    pl/sql个人笔记.

    ### PL/SQL 个人笔记详解 #### 一、PL/SQL 块中可嵌入的 SQL 语句类型 PL/SQL(程序化SQL)是Oracle数据库的标准编程语言,它扩展了SQL的功能,允许在数据库环境中编写过程化的业务逻辑。在PL/SQL中,可以嵌入多种...

    hivesql笔记.sql

    hivesql笔记.sql

    B站Mosh老师sql三小时的课程笔记

    B站Mosh老师SQL三小时的课程笔记 本文是对B站Mosh老师SQL三小时的课程笔记的总结,涵盖了SQL基础知识、SELECT语句、WHERE子句、逻辑操作符、IN操作符、BETWEEN操作符、LIKE操作符和REGEXP操作符等内容。 SQL基础...

    PL/SQL笔记pl/sql笔记

    本篇笔记主要涵盖了PL/SQL的基础语法和常用操作,包括检索数据、操纵数据以及SQL游标的应用。 在PL/SQL块中,可以直接嵌入的数据操作语句包括SELECT、DML(INSERT、UPDATE、DELETE)以及事务控制语句(COMMIT、...

    SQL笔记下载

    除了这些基础操作,SQL笔记可能还会涉及更复杂的查询技术,比如联接(JOIN)、子查询、视图(VIEW)的创建和使用,以及事务处理(TRANSACTION)等。联接允许你从多个表中合并数据,子查询可以在主查询内部执行查询,...

    SQL2005学习笔记

    《SQL2005学习笔记》是一份深入探讨SQL Server 2005核心概念、功能及优化策略的宝贵资料。SQL Server 2005是微软推出的一款强大的关系型数据库管理系统,它在数据存储、处理和分析方面具有广泛的应用。这份笔记旨在...

    SQL server2005笔记

    SQLserver 数据库学习笔记 欢迎大家下载学习,共同进步啊

    SQL课堂培训笔记疯汉三版【Oracle+Sqlserver+Mysql】

    SQL课堂培训笔记疯汉三版【Oracle+Sqlserver+Mysql】 根据韩顺平老视的视频教程整理的笔记,PDF格式

    SQL学习笔记(pdf)

    这份"SQL学习笔记"涵盖了SQL的基础概念、语法以及高级特性,是学习数据库管理和数据分析的宝贵资料。 1. **SQL基础** - 数据库概念:了解什么是数据库,它的作用以及数据库管理系统(DBMS)如何工作。 - SQL简介...

    sql笔记sql笔记sql笔记sql笔记sql笔记sql笔记

    sql笔记sql笔记sql笔记sql笔记sql笔记sql笔记

    oracle sql 读书笔记

    sql fundament 读书笔记 oracle 原厂 培训 金领DBA

Global site tag (gtag.js) - Google Analytics