sql
2018-08-13 本文已影响11人
Coding_530
1 .创建表语句
CREATE DATABASE studysql ; 用于创建数据库。
CREATE TABLE 表名称(列名称1 数据类型, 列名称2 数据类型,列名称3 数据类型);
//
CREATE TABLE `person` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
AUTO_INCREMENT： 自增
PRIMARY KEY ：id设置为主键
主键必须包含唯一的值。
主键列不能包含 NULL 值。
每个表都应该有一个主键，并且每个表只能有一个主键。
NOT NULL ：不为null
NOT NULL 约束强制列不接受 NULL 值。
NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。
DEFAULT NULL ：默认为null
ENGINE=InnoDB DEFAULT CHARSET=utf8; 引擎为mysql的InnoDB ，字符集是utf8
2.查询语句
2.1 select 列名称 from 表名称
select name,age from person;
2.2 select * from 表名称
select * from person;
提示：星号（*）是选取所有列的快捷方式，一般都要配合where语句来进行查询。
3.插入语句
3.1
INSERT INTO 表名称 VALUES (值1, 值2,....)
INSERT INTO person VALUES ('lyf', '23', '男');
INSERT INTO person VALUES (2,'lyh', '26', '男');//如果表中有id，前面需要加上
3.2
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....);
INSERT INTO person (name, age,sex) VALUES ('lyf', '23', '男');
- 更新语句
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
update person set age= '18' where name= 'lyf';
update person set age= '19', sex= '男。' where name= 'lyf';
5 .删除语句
DELETE FROM 表名称 WHERE 列名称 = 值
delete from person where name= 'lyh' ; //删除这条语句
delete from person ; //删除整个表所有数据，表的结构、属性和索引都是完整
delete * from person;
6.WHERE 语句
语法：
SELECT 列名称 FROM 表名称 WHERE 列 运算符 值;
Tisp
SQL 使用单引号来环绕文本值（大部分数据库系统也接受双引号）。
如果是数值，请不要使用引号。
WHERE语句中可以使用的操作符
= 等于
<> 不等于
> 大于
< 小于
>= 大于等于
<= 小于等于
BETWEEN 在某个范围内
LIKE 搜索某种模式
SELECT * FROM Persons WHERE name='lyf';//选择名字叫lyf的数据。
SELECT * FROM Persons WHERE age>20; //选择大于20的数据。
7 .AND OR 语句
INSERT INTO person (name, age,sex) VALUES ('l', '23', '男');id=7
INSERT INTO person (name, age,sex) VALUES ('y', '23', '男');
INSERT INTO person (name, age,sex) VALUES ('f', '23', '男');
INSERT INTO person (name, age,sex) VALUES ('l', '23', '男');id=10
AND 查找条件都符合的数据。
SELECT * FROM Person WHERE name='l' AND age='23' and id =7;//只有id是7
这一条数据
OR, 只要有一个条件成立，都查找该所有的数据
SELECT * FROM Person WHERE name='l' OR id='7';//找出di是7，10两条数据
AND 和 OR组合（使用圆括号来组成复杂的表达式)
SELECT * FROM Person WHERE (name='l' OR name='y') AND sex='女';
8.ORDER BY 语句
SELECT name, age FROM Person ORDER BY name;//升序
SELECT name, age FROM Person ORDER BY name DESC //降序
9.DISTINCT 语句
根据表中某个列字段筛选， 如果多条相同的数据，就会筛选为1条（去除重复）
SELECT DISTINCT name FROM Person;
10.COUNT语句
SELECT COUNT(sex) FROM person;//返回person表的条数（不包括NULL）
SELECT COUNT(*) FROM person;//返回person表的全部条数
SELECT COUNT(DISTINCT age) FROM person;//返回person表的全部条数（重复的算1条）
11.TOP 、LIMIT 、ROWNUM 语句 ：用于规定要返回的记录的数目。
mysql ：
SELECT * FROM Person LIMIT 5; //返回前面5个
Oracel:
select * from customers where ROWNUM <= 5;
12.LIKE
LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。
//表中选取以 "N" 开始的数据
SELECT * FROM Person WHERE name LIKE 'N%';
// 表中选取以 "8" 结尾的数据
SELECT * FROM Person WHERE name LIKE '%8';
//表中选取包含 "f6" 的的数据
SELECT * FROM Person WHERE name LIKE '%f6%';
//表中选取不包含 "f6" 的的数据
SELECT * FROM Person WHERE name NOT LIKE '%f6%';
提示："_" 、"%" 可用于定义通配符（缺少的字母）。
13.SQL 通配符
在搜索数据库中的数据时，SQL 通配符可以替代一个或多个字符。
SQL 通配符必须与 LIKE 运算符一起使用。
% ：替代一个或多个字符
_ ：仅替代一个字符
[charlist]、[^charlist]、[!charlist] :字符列中的任何单一字符
13.1 使用 % 通配符：
//表中选取以 "N" 开始的数据
SELECT * FROM Person WHERE name LIKE 'N%';
// 表中选取以 "8" 结尾的数据
SELECT * FROM Person WHERE name LIKE '%8';
//表中选取包含 "f6" 的的数据
SELECT * FROM Person WHERE name LIKE '%f6%';
//表中选取不包含 "f6" 的的数据
SELECT * FROM Person WHERE name NOT LIKE '%f6%';
13.2使用 _ 通配符：
//表中选取名字的第一个字符之后是 "yf1" 的数据
SELECT * FROM Person WHERE name LIKE '_yf1';
//表中选取的这条记录的名字以 "C" 开头，然后是一个任意字符，然后是 "r"，然
后是任意字符，然后是 "er"：
SELECT * FROM Person WHERE name LIKE 'C_r_er'
14.IN 语句
IN 操作符允许我们在 WHERE 子句中规定多个值。
//表中选取名字为 ANBA和 NBA的数据，如果没有该名字，就不会返回
SELECT * FROM Person WHERE name IN ('ANBA','NBA');
15.BETWEEN 语句
between操作符在 where子句中使用，作用是选取介于两个值之间的数据
范围。
操作符 between... and会选取介于两个值之间的数据范围。这些值可以是数
值、文本或者日期。
SELECT column_name(s) FROM table_name WHERE column_name
BETWEEN value1 AND value2;
16.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
//2008-12-26
SELECT * FROM Orders WHERE OrderDate='2008-12-26';
//2008-12-26 16:23:55
SELECT * FROM Orders WHERE OrderDate='2008-12-26';
如果这样不能查找出改时间，这是由于该查询不含有时间部分的日期。
应该：SELECT * FROM Orders WHERE OrderDate='2008-12-26 16:23:55';
提示：如果您希望使查询简单且更易维护，那么请不要在日期中使用时间部分！
17.NULL 值
NULL 值是遗漏的未知数据。默认地，表的列可以存放 NULL 值。
操作符:
is null :
is not null
如果表中的某个列是可选的，那么我们可以在不向该列添加值的情况下插入新记录或更新已有的记录。这意味着该字段将以 NULL 值保存。
NULL 值的处理方式与其他值不同。
NULL 用作未知的或不适用的值的占位符。
注释：无法比较 NULL 和 0；它们是不等价的。
select name,age,sex from person where name IS NULL;//找出为null的数据。
提示：请始终使用 IS NULL 来查找 NULL 值。
select name,age,sex from person where name IS not NULL;//找出不为null的数据
MySQL
MySQL 也拥有类似 ISNULL() 的函数。不过它的工作方式与微软的 ISNULL() 函数有点不同。
在 MySQL 中，我们可以使用 IFNULL() 函数，就像这样：
SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products
或者我们可以使用 COALESCE() 函数，就像这样：
SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))
FROM Products
18.MySQL 数据类型
在 MySQL 中，有三种主要的类型：文本、数字和日期/时间类型。
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 无符号*。在括号中规定最大位数。
MEDIUMINT(size) -8388608 到 8388607 普通。0 to 16777215 无符号*。在括号中规定最大位数。
INT(size) -2147483648 到 2147483647 常规。0 到 4294967295 无符号*。在括号中规定最大位数。
BIGINT(size) -9223372036854775808 到 9223372036854775807 常规。0 到 18446744073709551615 无符号*。在括号中规定最大位数。
FLOAT(size,d) 带有浮动小数点的小数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DOUBLE(size,d) 带有浮动小数点的大数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DECIMAL(size,d) 作为字符串存储的 DOUBLE 类型，允许固定的小数点。
* 这些整数类型拥有额外的选项 UNSIGNED。通常，整数可以是负数或正数。如果添加 UNSIGNED 属性，那么范围将从 0 开始，而不是某个负数。
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。
* 即便 DATETIME 和 TIMESTAMP 返回相同的格式，它们的工作方式很不同。在 INSERT 或 UPDATE 查询中，TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。
19.Alias 语句（别名）
SELECT column_name(s) FROM table_name AS alias_name
使用别名：
假设我们有两个表分别是："Persons" 和 "Product_Orders"。我们分别为它们指定别名 "p" 和 "po"。
SELECT po.OrderID, p.LastName, p.FirstName
FROM Persons AS p, Product_Orders AS po
WHERE p.LastName='Adams' AND p.FirstName='John'
不使用别名：
SELECT Product_Orders.OrderID, Persons.LastName, Persons.FirstName
FROM Persons, Product_Orders
WHERE Persons.LastName='Adams' AND Persons.FirstName='John'
别名使查询程序更易阅读和书写。
别名还有一种写法是：
update f set f.user_identify_label =2 from user f where f.user_id in (122345) ;
f 是user这张表的别名，可以通过这个表的别名来获取对应的字段，和java 的对象差不多
一个列名别名：
表如下
Id LastName FirstName Address City
1 o 3 8
2 k 4 09
SELECT LastName AS Family, FirstName AS Name FROM Persons；
LastName （列）的别名就是Family
FirstName （列）的别名就是Name
就是查找出改别名列下面的所有字段
Family =LastName Name=FirstName
1 o
2 k
- INNER JOIN 语句，注释：INNER JOIN 与 JOIN 是相同的。
SELECT column_name(s) FROM table_name1 INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
在2表中存在至少一个匹配时，INNER JOIN 才可以查询到结果（交集）。
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons INNER JOIN Orders ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName；
通过2表的Id_P字段，如果匹配相同的，就根据LastName升序返回查找的
join on 多表联查
内连接:取2个表的交集 tbl_dept（表）和 tbl_emp（表） 的交集
select a.id, a.deptName,b.`name` ,b.deptId from tbl_dept a inner join tbl_emp b on a.id=b.deptId;
左外连接: tbl_dept（表）的全集 和 tbl_dept（表）和 tbl_emp（表） 的交集 （没有的交集就是Null）
select a.id, a.deptName,b.`name` ,b.deptId from tbl_dept a left join tbl_emp b on a.id=b.deptId;
右外连接: tbl_emp（表）的全集 和 tbl_dept（表）和 tbl_emp（表） 的交集 （没有的交集就是Null）
select a.id, a.deptName,b.`name` ,b.deptId from tbl_dept a right join tbl_emp b on a.id=b.deptId;
左连接 返回tbl_emp表的补集 （没有的交集就是Null）
select * from tbl_dept a left join tbl_emp b on a.id=b.deptId where b.deptId is null;
右连接 返回tbl_dept表的补集 （没有的交集就是Null）
select * from tbl_dept a right join tbl_emp b on a.id=b.deptId where a.id is null;
全连接：2个表的所有
select * from tbl_dept a right join tbl_emp b on a.id=b.deptId
union
select * from tbl_dept a left join tbl_emp b on a.id=b.deptId;
两张表中都没有出现的数据集，（各自表的补集）
SELECT * FROM tbl_dept a RIGHT JOIN tbl_emp b ON a.id = b.deptId WHERE a.id IS NULL
UNION
SELECT * FROM tbl_dept a LEFT JOIN tbl_emp b ON a.id = b.deptId WHERE b.deptId IS NULL;
