MySQL语句学习

SQL 的学习

SQL 能做什么？

SQL 面向数据库执行查询

SQL 可从数据库取回数据

SQL 可在数据库中插入新的记录

SQL 可更新数据库中的数据

SQL 可从数据库删除记录

SQL 可创建新数据库

SQL 可在数据库中创建新表

SQL 可在数据库中创建存储过程

SQL 可在数据库中创建视图

SQL 可以设置表、存储过程和视图的权限

 

SQL 是一种标准 - 但是...

SQL 是一门 ANSI 的标准计算机语言，用来访问和操作数据库系统。SQL 语句用于取回和更新数据库中的数据。SQL 可与数据库程序协同工作，比如 MS Access、DB2、Informix、MS SQL Server、Oracle、Sybase 以及其他数据库系统。

不幸地是，存在着很多不同版本的 SQL 语言，但是为了与 ANSI 标准相兼容，它们必须以相似的方式共同地来支持一些主要的关键词（比如 SELECT、UPDATE、DELETE、INSERT、WHERE 等等）。

注释：除了 SQL 标准之外，大部分 SQL 数据库程序都拥有它们自己的私有扩展！

 

SQL基本语句

1、查询所有的字段；

Select *  from 表名；

2、查询所有字段，去除重复的值

select DISTINCT(字段名) 别名 from 表名;

3、Where 字句

下面的运算符可在 WHERE 子句中使用：

操作符	描述
=	等于
<>	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	在某个范围内
LIKE	模糊对应

 

4、引号的使用

SQL 使用单引号来环绕文本值（大部分数据库系统也接受双引号）。如果是数值，请不要使用引号。

从学生表中查询赵雷的数据

SELECT * FROM student WHERE s_name = '赵雷';

 

5、AND 和 OR 运算符用于基于一个以上的条件对记录进行过滤。

AND 和 OR 运算符

AND 和 OR 可在 WHERE 子语句中把两个或多个条件结合起来。

如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。

如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。

查询姓名是赵雷并且性别为男的学生

SELECT * FROM student WHERE s_name = '赵雷' and s_sex = '男';

查询姓名是赵雷或者性别是男的

SELECT * FROM student WHERE s_name = '赵雷' or s_sex = '男';

查询姓名是赵雷或者性别是男的数据并且生日是1990-12-21

SELECT * FROM student WHERE (s_name = '赵雷' or s_sex = '男') AND s_birth='1990-12-21';

6、ORDER BY 语句用于对结果集进行排序。

ORDER BY 语句

ORDER BY 语句用于根据指定的列对结果集进行排序。

ORDER BY 语句默认按照升序对记录进行排序。

如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。

 

INSERT INTO 语句

INSERT INTO 语句用于向表格中插入新的行。

语法

INSERT INTO 表名称 VALUES (值1, 值2,....)

我们也可以指定所要插入数据的列：

INSERT INTO 表名 (列1, 列2,...) VALUES (值1, 值2,....)

 

向表中插入数据

INSERT INTO student VALUES ('10', '媛媛', '1990-12-21', '女')

向指定字段插入数据

INSERT INTO student (s_id, s_name, s_birth, s_sex)VALUES ('11', '媛媛暖', '1990-12-21', '女')

 

Update 语句

Update 语句用于修改表中的数据。

语法：

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

 

更新表中符合条件的数据

update 表名set 字段名='旧数据' WHERE 字段名='新数据';

 

DELETE 语句

DELETE 语句用于删除表中的行。

语法

DELETE FROM 表名称 WHERE 列名称 = 值

 

删除学生表中的姓名为佩奇的信息

delete From 表名 where 字段名='条件';

 

删除所有行

可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的：

DELETE FROM table_name

或者：

DELETE * FROM table_name

 

高级语句

Limit

MySQL中limit语句，用于对数据进行分离查询，一般都是放到SQL语句的最后；

Like

MySQL中like用来做模糊查询，在where子句中使用；需要通配符的配合使用；

通配符	描述
%	代替一个或多个字符
_	仅代替一个字符
[charlist]	字符列中的任何单一字符
[^charlist]或者[!charlist]	不在字符列中的任何单一字符

 

IN 操作符

IN 操作符允许我们在 WHERE 子句中规定多个值。

查询符合条件1和条件2的所有数据

Select * From 表名 where 字段名 in （‘条件1’,’条件2’.......）

 

BETWEEN 运算符

BETWEEN运算符用于选取介于两个值之间的数据范围内的值。

BETWEEN运算符选择给定范围内的值。值可以是数字，文本或日期。

BETWEEN运算符是包含性的：包括开始和结束值，且开始值需小于结束值。

 

从表中查出符合在条件1和条件2之间的数据

SELECT * FROM 表名where 字段名 BETWEEN '条件1' AND '条件2';

从表中查出不符合在条件1和条件2之间的数据

SELECT * FROM 表名where 字段名 NOT BETWEEN '条件1' AND '条件2';

 

不同的 SQL JOIN

1. INNER JOIN：如果表中有至少一个匹配，则返回行
2. LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
3. RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
4. FULL JOIN：只要其中一个表中存在匹配，则返回行

SQL JOIN

SQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。

　简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。 

　最常见的 JOIN 类型：SQL INNER JOIN（简单的 JOIN）。 SQL INNER JOIN 从多个表中返回满足 JOIN 条件的所有行。

查询符合条件的表1和表2的所有数据

SELECT * FROM 表1 INNER  JOIN 表2 ON 表1对应字段 = 表2对应字段;

 

SQL LEFT JOIN

LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

查询符合条件的表1的所有数据，不符合条件的表1数据也会查出来

SELECT  * FROM 表1 LEFT JOIN 表2 ON 表1的字段 = 表2的字段;

 

SQL RIGHT JOIN

RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。

查询符合条件的表2的所有数据，不符合条件的表2数据也会查出来

SELECT * FROM 表1 RIGHT JOIN  表2 ON 表1字段= 表2字段;

SQL UNION

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

默认地，UNION 操作符选取不同的值。如果允许重复的值，使用 UNION ALL。

查询出符合条件表中所有的数据，去重选择默认的，不去重的话选择UNION ALL

SELECT * from 表 UNION (ALL) SELECT * from 表;

对数据库的话，并不会太深入，对创建库，表还有约束的SQL就不怎么研究了

数据库索引

文章转自 https://www.cnblogs.com/AK2012/archive/2013/01/04/2013-0104.html

什么是索引：索引的话主要分为聚集索引和非聚集索引，索引主要目的是提高了SQL Server系统的性能，加快数据的查询速度与减少系统的响应时间 ；

聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续。就像字段，聚集索引是连续的，a后面肯定是b，非聚集索引就不连续了，就像图书馆的某个作者的书，有可能在第1个货架上和第10个货架上。还有一个小知识点就是：聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个。

什么情况下设置索引 

动作描述	使用聚集索引	使用非聚集索引
外键列	应	应
主键列	应	应
列经常被分组排序(order by)	应	应
返回某范围内的数据	应	不应
小数目的不同值	应	不应
大数目的不同值	不应	应
频繁更新的列	不应	应
频繁修改索引列	不应	应
一个或极少不同值	不应	不应

 

建立索引的原则：

1) 定义主键的数据列一定要建立索引。

2) 定义有外键的数据列一定要建立索引。

3) 对于经常查询的数据列最好建立索引。

4) 对于需要在指定范围内的快速或频繁查询的数据列;

5) 经常用在WHERE子句中的数据列。

6) 经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。

7) 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

8) 对于定义为text、image和bit的数据类型的列不要建立索引。

9) 对于经常存取的列避免建立索引 

9) 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。

10) 对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。

如何创建索引

　创建索引的语法：

CREATE [UNIQUE][CLUSTERED | NONCLUSTERED]  INDEX  index_name  

ON {table_name | view_name} [WITH [index_property [,....n]]

说明：

UNIQUE: 建立唯一索引。

CLUSTERED: 建立聚集索引。

NONCLUSTERED: 建立非聚集索引。

Index_property: 索引属性。

 UNIQUE索引既可以采用聚集索引结构，也可以采用非聚集索引的结构，如果不指明采用的索引结构，则SQL Server系统默认为采用非聚集索引结构。

 删除索引语法：

DROP INDEX table_name.index_name[,table_name.index_name]

说明：table_name: 索引所在的表名称。

index_name : 要删除的索引名称。

 显示索引信息：

使用系统存储过程：sp_helpindex 查看指定表的索引信息。

执行代码如下：

Exec sp_helpindex book1;

SQL视图

SQL Server创建视图——视图的作用

视图简介：

视图可以看作定义在SQL Server上的虚拟表。视图正如其名字的含义一样，是另一种查看数据的入口。

常规视图本身并不存储实际的数据，而仅仅是由SELECT语句组成的查询定义的虚拟表 。

从数据库系统内部来看，视图是由一张或多张表中的数据组成的，从数据库系统外部来看，视图就如同一张表一样，对表能够进行的一般操作都可以应用于视图。

例如查询，插入，修改，删除操作等，但插入、修改、删除等的操作其实对于原始数据表的操作。

视图的作用：

1、视图隐藏了底层的表结构，简化了数据访问操作，客户端不再需要知道底层表的结构及其之间的关系。

2、视图提供了一个统一访问数据的接口。（即可以允许用户通过视图访问数据的安全机制，而不授予用户直接访问底层表的权限）

3、从而加强了安全性，使用户只能看到视图所显示的数据。

4、视图还可以被嵌套，一个视图中可以嵌套另一个视图。

SQL 日期（Dates）

MySQL Date 函数

下面的表格列出了 MySQL 中最重要的内建日期函数：

函数	描述
NOW()	返回当前的日期和时间
CURDATE()	返回当前的日期
CURTIME()	返回当前的时间
DATE()	提取日期或日期/时间表达式的日期部分
EXTRACT()	返回日期/时间的单独部分
DATE_ADD()	向日期添加指定的时间间隔
DATE_SUB()	从日期减去指定的时间间隔
DATEDIFF()	返回两个日期之间的天数
DATE_FORMAT()	用不同的格式显示日期/时间

 

MySQL 使用下列数据类型在数据库中存储日期或日期/时间值：

DATE - 格式：YYYY-MM-DD

DATETIME - 格式：YYYY-MM-DD HH:MM:SS

TIMESTAMP - 格式：YYYY-MM-DD HH:MM:SS

YEAR - 格式：YYYY 或 YY

SQL null值

在MySQL中null值是一个比较常见的值，也比较容易出错；

在SQL中主要有两种判断null的方法；

IS NULL

使用is null 可以判断字段为空；

is not null

Is not null 判断字段不为null的

MySQL 数据类型

在 MySQL 中，有三种主要的类型：Text（文本）、Number（数字）和 Date/Time（日期/时间）类型。

Text 类型：

数据类型	描述
CHAR(size)	保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多 255 个字符。
VARCHAR(size)	保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多 255 个字符。注释：如果值的长度大于 255，则被转换为 TEXT 类型。
TINYTEXT	存放最大长度为 255 个字符的字符串。
TEXT	存放最大长度为 65,535 个字符的字符串。
BLOB	用于 BLOBs（Binary Large OBjects）。存放最多 65,535 字节的数据。
MEDIUMTEXT	存放最大长度为 16,777,215 个字符的字符串。
MEDIUMBLOB	用于 BLOBs（Binary Large OBjects）。存放最多 16,777,215 字节的数据。
LONGTEXT	存放最大长度为 4,294,967,295 个字符的字符串。
LONGBLOB	用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。
ENUM(x,y,z,etc.)	允许您输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值，则插入空值。 注释：这些值是按照您输入的顺序排序的。 可以按照此格式输入可能的值： ENUM('X','Y','Z')
SET	与 ENUM 类似，不同的是，SET 最多只能包含 64 个列表项且 SET 可存储一个以上的选择。

Number 类型：

数据类型	描述
TINYINT(size)	带符号-128到127 ，无符号0到255。
SMALLINT(size)	带符号范围-32768到32767，无符号0到65535, size 默认为 6。
MEDIUMINT(size)	带符号范围-8388608到8388607，无符号的范围是0到16777215。 size 默认为9
INT(size)	带符号范围-2147483648到2147483647，无符号的范围是0到4294967295。 size 默认为 11
BIGINT(size)	带符号的范围是-9223372036854775808到9223372036854775807，无符号的范围是0到18446744073709551615。size 默认为 20
FLOAT(size,d)	带有浮动小数点的小数字。在 size 参数中规定显示最大位数。在 d 参数中规定小数点右侧的最大位数。
DOUBLE(size,d)	带有浮动小数点的大数字。在 size 参数中规显示定最大位数。在 d 参数中规定小数点右侧的最大位数。
DECIMAL(size,d)	作为字符串存储的 DOUBLE 类型，允许固定的小数点。在 size 参数中规定显示最大位数。在 d 参数中规定小数点右侧的最大位数。

注意：以上的 size 代表的并不是存储在数据库中的具体的长度，如 int(4) 并不是只能存储4个长度的数字。

实际上int(size)所占多少存储空间并无任何关系。int(3)、int(4)、int(8) 在磁盘上都是占用 4 btyes 的存储空间。就是在显示给用户的方式有点不同外，int(M) 跟 int 数据类型是相同的。

例如：

1、int的值为10 （指定zerofill）

int（9）显示结果为000000010int（3）显示结果为010

就是显示的长度不一样而已 都是占用四个字节的空间

Date 类型：

数据类型	描述
DATE()	日期。格式：YYYY-MM-DD 注释：支持的范围是从 '1000-01-01' 到 '9999-12-31'
DATETIME()	*日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS 注释：支持的范围是从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'
TIMESTAMP()	*时间戳。TIMESTAMP 值使用 Unix 纪元('1970-01-01 00:00:00' UTC) 至今的秒数来存储。格式：YYYY-MM-DD HH:MM:SS 注释：支持的范围是从 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC
TIME()	时间。格式：HH:MM:SS 注释：支持的范围是从 '-838:59:59' 到 '838:59:59'
YEAR()	2 位或 4 位格式的年。 注释：4 位格式所允许的值：1901 到 2155。2 位格式所允许的值：70 到 69，表示从 1970 到 2069。