Sql整理

数据库

• 查询当前有哪些数据库

SHOW DATABASES;

• 切换数据库

USE 数据库名

• 创建数据库

CREATE DATABASE [IF NOT EXISTS] 数据库名 [CHARSET=utf8];

• 删除数据库

DROP DATABASE [IF NOT EXISTS] 数据库名;

• 修改数据库编码

ALTER DATABASE 数据库名 CHARACTER SET utf8

数据类型

* `int`  整型
* `double`  浮点型, 例如`doule(5, 2)`表示最多5位, 其实小数为2位
* `decimal`  浮点型, 在表单钱方面使用该类型, 不会出现精度缺失的问题
* `char`  固定长度字符串类型, `char(255)`
* `varchar`  可变长度字符串类型, `varchar(65535)`
* `text(clob)` 字符串类型:  `Mysql方言, 标准为clob`
     `>  很小`
     `>  小`
     `>  中`
     `>  大`
* `blob` 字节类型: 
     `>  很小`
     `>  小`
     `>  中`
     `>  大`
* `date`  日期类型, 格式为: `yyyy-MM-dd`
* `time`  时间类型, 格式为: `hh:mm:ss`
* `timestamp`  时间戳类型

表

• 创建表

CREATE TABLE [IF NOT EXISTS] 表名(
    列名 列类型,
    列名 列类型,
    列名 列类型,
    ...
    列名 列类型
);

mysql> CREATE TABLE it_stu(
    -> number char(11),
    -> name varchar(50),
    -> age int,
    -> gender varchar(10)
    -> );

• 查看当前数据库中所有的表名称

SHOW TABLES;

• 查看表结构

DESC 表名

mysql> DESC tb_stu
    -> ;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| number | char(11)    | YES  |     | NULL    |       |
| name   | varchar(50) | YES  |     | NULL    |       |
| age    | int(11)     | YES  |     | NULL    |       |
| gender | varchar(10) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

• 删除表

DROP TABLE 表名

• 给表添加一列

ALTER TABLE 表名ADD (
    列名 列类型,
    ...
    列名 列类型
);

mysql> ALTER TABLE it_stu
    -> ADD (
    ->  education varchar(500)
    -> );

+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| number    | char(11)     | YES  |     | NULL    |       |
| name      | varchar(50)  | YES  |     | NULL    |       |
| age       | int(11)      | YES  |     | NULL    |       |
| gender    | varchar(10)  | YES  |     | NULL    |       |
| education | varchar(500) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+

• 修改列类型(如果被修改的列已存在数据, 那么新的类型会影响到已存在数据)

ALTER TABLE 表名  MODIFY 列名 列类型;

ALTER TABLE it_stu MODIFY education int;

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| number    | char(11)    | YES  |     | NULL    |       |
| name      | varchar(50) | YES  |     | NULL    |       |
| age       | int(11)     | YES  |     | NULL    |       |
| gender    | varchar(10) | YES  |     | NULL    |       |
| education | int(11)     | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

• 修改列名

ALTER TABLE 表名 CHANGE 原列名 新列名 列类型;

mysql> ALTER TABLE it_stu
    -> CHANGE
    -> education
    -> edu
    -> varchar(100);

+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| number | char(11)     | YES  |     | NULL    |       |
| name   | varchar(50)  | YES  |     | NULL    |       |
| age    | int(11)      | YES  |     | NULL    |       |
| gender | varchar(10)  | YES  |     | NULL    |       |
| edu    | varchar(100) | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+

• 删除列

ALTER TABLE 表名 DROP 列名;

ALTER TABLE it_stu DROP edu;

+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| number | char(11)    | YES  |     | NULL    |       |
| name   | varchar(50) | YES  |     | NULL    |       |
| age    | int(11)     | YES  |     | NULL    |       |
| gender | varchar(10) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

• 修改表名称

ALTER TABLE 原表名 RENAME TO 新表名;

ALTER TABLE it_stu RENAME TO IT_stu1;

+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| number | char(11)    | YES  |     | NULL    |       |
| name   | varchar(50) | YES  |     | NULL    |       |
| age    | int(11)     | YES  |     | NULL    |       |
| gender | varchar(10) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

• 查询表的内容

SELECT * FROM 表名

SELECT * FROM MMALL_USER;

+----+--------------+----------------------------------+-----------------------+-------------+----------+--------+------+---------------------+---------------------+
| id | username     | password                         | email                 | phone       | question | answer | role | create_time         | update_time         |
+----+--------------+----------------------------------+-----------------------+-------------+----------+--------+------+---------------------+---------------------+
|  1 | admin        | 427338237BD929443EC5D48E24FD2B1A | admin@happymmall.com  | 13800138000 | 闂     | 绛旀   |    1 | 2016-11-06 16:56:45 | 2017-04-04 19:27:36 |
| 13 | geely        | 08E9A6EA287E70E7E3F7C982BF7923AC | geely@happymmall.com  | 13800138000 | 闂     | 绛旀   |    0 | 2016-11-19 22:19:25 | 2016-11-19 22:19:25 |
| 17 | rosen        | 095AC193FE2212EEC7A93E8FEFF11902 | rosen1@happymmall.com | 13800138000 | 闂     | 绛旀   |    0 | 2017-03-17 10:51:33 | 2017-04-09 23:13:26 |
| 21 | soonerbetter | DE6D76FE7C40D5A1A8F04213F2BEFBEE | test06@happymmall.com | 13800138000 | 105204   | 105204 |    0 | 2017-04-13 21:26:22 | 2017-04-13 21:26:22 |
+----+--------------+----------------------------------+-----------------------+-------------+----------+--------+------+---------------------+---------------------+

• 插入数据
  • 注意: 在数据库中所有的字符串类型, 必须使用单引号, 不能使用双引号. 日期类型也要使用单引号
  • 插入分为插入全部列和插入部分列, 没有插入的列, 为默认值NULL

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

mysql> INSERT INTO IT_STU1
    -> (NUMBER, NAME, AGE, GENDER)
    -> VALUES
    -> ('oi-001','ruirui', 10, 'nv');

+--------+--------+------+--------+
| number | name   | age  | gender |
+--------+--------+------+--------+
| oi-001 | ruirui |   10 | nv     |
+--------+--------+------+--------+

//  没有插入的列, 为默认值NUL
mysql> INSERT INTO IT_STU1
    -> (NUMBER, NAME)
    -> VALUES
    -> ('oi-001','ruirui'');

// 不给插入列, 那么默认为插入所有列, 值的顺序要与创建表时列的顺序相同
INSERT INTO IT_STU1 VALUES (
    'IT_STU_0001', 'wanger', 82, 'fill'
)

• 修改表的记录

UPDATE 表名 SET 列名1=列值1, 列名2=列值2, ... [WHERE 条件]

+--------+--------+------+--------+
| number | name   | age  | gender |
+--------+--------+------+--------+
| oi-001 | ruirui |   10 | nv     |
| oi-001 | ruirui |   10 | NULL   |
| OI-002 | NULL   | NULL | NULL   |
+--------+--------+------+--------+

mysql> UPDATE IT_STU1 SET NUMBER='OI-003'
    -> WHERE GENDER='NV';

+--------+--------+------+--------+
| number | name   | age  | gender |
+--------+--------+------+--------+
| OI-003 | ruirui |   10 | nv     |
| oi-001 | ruirui |   10 | NULL   |
| OI-002 | NULL   | NULL | NULL   |
+--------+--------+------+--------+
3 rows in set (0.00 sec)

mysql> UPDATE IT_STU1 SET NAME='XXX'
    -> WHERE GENDER='NV' OR NUMBER='OI-002';

+--------+--------+------+--------+
| number | name   | age  | gender |
+--------+--------+------+--------+
| OI-003 | XXX    |   10 | nv     |
| oi-001 | ruirui |   10 | NULL   |
| OI-002 | XXX    | NULL | NULL   |
+--------+--------+------+--------+

UPDATE的运算符

=, !=, <>, >, <, >=, <=, BETWEEN...AND, IN(...), IS NULL, NOT, OR, AND

// between 5 and 10 相当于在大于等于5且小于等于10的区间内
UPDATE IT_STU1 SET NAME='UPCC' WHERE AGE BETWEEN 5 AND 20;

+--------+------+------+--------+
| number | name | age  | gender |
+--------+------+------+--------+
| OI-003 | UPCC |   10 | nv     |
| oi-001 | UPCC |   10 | NULL   |
| OI-002 | XXX  | NULL | NULL   |
+--------+------+------+--------+

// IN相当于满足里面的某一个条件就成立
UPDATE IT_STU1 SET AGE=100 WHERE NAME IN('UPCC', 'XXX');

+--------+------+------+--------+
| number | name | age  | gender |
+--------+------+------+--------+
| OI-003 | UPCC |  100 | nv     |
| oi-001 | UPCC |  100 | NULL   |
| OI-002 | XXX  |  100 | NULL   |
+--------+------+------+--------+

// 判断一个属性是否为空, 不能够直接使用GENDER=NULL, 要用GENDER IS NULL
UPDATE IT_STU1 SET AGE=30 WHERE GENDER IS NULL;

删除

// 不加条件删除整个表
DELETE FROM 表名 [WHERE 条件];

mysql> DELETE FROM TEST WHERE no = 0;

查询

SELECT sellection_list /*要查询的列名称*/
FROM table_list /*要查询的表名称*/
WHERE condition /*行条件*/
GROUP BY grouping_columns /*对结果分组*/
HAVING condition /*分组以后的行结果*/
ORDER BY sorting_columns /*对结果进行排序*/
LIMIT offset_start, row_count /*结果限定*/

// 查询全部的列
SELECT * FROM stu;

// 查询姓名和年龄列
SELECT sid, sname FROM stu;

条件查询后面也是跟WHERE,

// 查询年纪大于50且性别为女性 
SELECT * FROM stu WHERE age > 50 AND gender = 'female';

// 查询sid, 在'S_1001', 'S_1002', 'S_1003'的范围内的所有数据
SELECT * FROM stu WHERE sid IN ('S_1001', 'S_1002', 'S_1003');

// 查询sid, 不在'S_1001', 'S_1002', 'S_1003'的范围内的所有数据
SELECT * FROM stu WHERE sid NOT IN ('S_1001', 'S_1002', 'S_1003');

模糊查询

模糊查询需要用到关键字LIKE,
通配符

• _ : 任意一个字符
• %: 任意0~n个字符

// 查询sname, 由5个字母组成的数据
SELECT * FROM stu WHERE sname LIKE '_____';

// 查询sname有5个字母组成, 且最后一个字母为i
SELECT * FROM stu WHERE sname LIKE '____i';

// 查询sname以z开头的数据
SELECT * 
FROM stu 
WHERE sname LIKE 'z%';

// 查询sname第二字字母为i的数据
SELECT * FROM stu WHERE sname LIKE '_i%';

// 查询sname第二字字母为i的数据
SELECT * FROM stu WHERE sname LIKE '_i%';

// 查询sname最后一个字母是i的数据
SELECT * FROM stu WHERE sname LIKE '%i';

// 查询sname包含字母是a的数据
SELECT * FROM stu WHERE sname LIKE '%a%';

字段控制查询

• 去重复记录: DISTINCT, 且DISTINCT不能和多个字段使用

// 查询sal, 且去重
SELECT DISTINCT sal FROM emp;

// 查询计算sal和comm的和, 且如果comm为空, 那么默认值为0计算
SELECT sal + IFNULL(comm, 0) FROM emp;

// 给列添加别名 `AS`, AS可以省略
SELECT sal + IFNULL(comm, 0) AS '总金额' FROM emp;

排序

ORDER BY 列名 ASC(默认) 升序 DESC 降序

// 降序查询所有学生的年龄
SELECT * FROM stu ORDER BY age DESC;

// 升序查询所有学生的年龄
SELECT * FROM stu ORDER BY age;

// 按照sal降序查询, 如果sal相同则按照empno升序排列
SELECT * FROM emp ORDER BY sal DESC, empno ASC;

聚合函数

聚合函数是用来做纵向运算的函数:

• COUNT(): 统计指定列不为NULL的记录行数;
• MAX(): 计算指定列的最大值, 如果指定列是字符串类型, 那么使用字符串排序运算;
• MIN(): 计算指定列的最小值, 如果指定列是字符串类型, 那么使用字符串排序运算;
• SUM(): 计算指定列的数值和, 如果指定列类型不是数值类型, 那么计算结果为0;
• AVG(): 计算指定列的平均值, 如果指定列类型不是数值类型, 那么计算结果为0;

// 查询emp表里有多少数据
SELECT COUNT(*) FROM emp;

// 查询emp表里comm有数据的人数
SELECT COUNT(*) FROM emp WHERE comm IS NOT NULL; 

// 查询comm + sal > 2500的人数
SELECT COUNT(*) FROM emp WHERE sal + IFNULL(comm, 0) > 2500;

// 查询有comm的人数
SELECT COUNT(comm) FROM emp;

// 查询有comm 且mgr不为0的人数
SELECT COUNT(comm) FROM emp WHERE mgr IS NOT NULL;

// 查询sal的和
SELECT SUM(sal) FROM emp;

// 查询sal的平均值
SELECT AVG(sal) FROM emp;

// 求sal的最大值和最小值
SELECT MAX(sal), MIN(sal) FROM emp;

分组查询

• 按组查询deptno里sal的总和

 SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;

• 查询每个deptno的人数

SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;

• 查询每个deptno里sal大于1500的人数

SELECT deptno, COUNT(*) FROM emp WHERE sal > 1500 GROUP BY deptno;

HAVING 子句

HAVING子句, HAVING是对数据的过滤, WHERE也是, 但是区别在于WHERE是对分组前的数据进行过滤, 而HAVING是对分组以后的数据进行过滤.

• 查询工资总和大于9000的部门编号和工资总和

SELECT deptno, SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal) > 9000;

LIMIT 方言（mysql独有）

用于分页显示数据.

• 查询前5条数据

SELECT * FROM emp LIMIT 0, 5;

主键约束(primary key)

特点: 数据唯一且不能为空

• 生成一个带主键的表

CREATE TABLE student (
    id int PRIMARY KEY,
    name VARCHAR(50)
)

CREATE TABLE student (
    id int,
    name VARCHAR(50),
    PRIMARY KEY(id)
)

CREATE TABLE student (
    id int,
    name VARCHAR(50),
)
ALTER TABLE student ADD PRIMARY KEY(name) 

• 联合主键

CREATE TABLE student (
    id int,
    name VARCHAR(50),
    PRIMARY KEY(id, name)
)

联合主键是指两个属性相加唯一, 两个加起来不能重复.

唯一约束(unique)

CREATE TABLE student1 (
    id int PRIMARY KEY, // 主键
    name VARCHAR(50) UNIQUE  // name不能重复
)

主键自增长(auto_increment)

给主键添加会自动增长的数值, 列只能是整数类型, 但是如果删除之前增长的序号, 后面再添加的时候序号不会重新开始, 而是会接着被删除的那一列的序号

• 创建一个自动增长的表

CREATE TABLE student (
    id int PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) UNIQUE
)

域完整性

域完整性的作用: 限制单元格的数据是否正确
域完整性约束: 数据类型 非空约束(not null) 默认值约束(default) Check 约束(mysql不支持) check()

非空约束 (not null)

CREATE TABLE student (
    id int PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) UNIQUE NOT NULL
)

默认值

CREATE TABLE student2 (
    id int PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) UNIQUE,
    gender VARCHAR(10) DEFAULT 'male'
)

如果执行

INSERT INTO student2 (name, gender) VALUES ('xjj', NULL)

结果
gender依旧为空, defalut是在INSERT的时候, 不对该属性进行修改, 那么数据库保存默认值

多表查询

多表查询有如下几种

• 合并结果集: UNION, UNION ALL
• 链接查询
  • 内连接 [INNER] JOIN ON
  • 外连接 OUTER JOIN ON
  • 左外连接 LEFT [OUTER ] JOIN ON
  • 右外连接 RIGHT [OUTER ] JOIN ON
  • 全外链接 (Mysql不支持) FULL JOIN
  • 自然链接 NATURAL JOIN
• 子查询

合并结果集

合并结果集就是把两个SELECT语句合并到一起
合并结果集的方式有2中

• UNION: 去除重复记录

SELECT * FROM 表名1 UNION SELECT * FROM 表名2

• UNIONALL: 不去除重复记录

SELECT * FROM 表名1 UNION ALL SELECT * FROM 表名2

其中被合并的结果, 列数和列类型必须相同

连接查询

连接查询就是求出多个表的乘积, 例如表1连接表2, 那么查询出的结果就是表1 * 表2.

SELECT * FROM 表1, 表2

• 笛卡尔积过滤

SELECT * FROM employee e, department d WHERE e.depno = d.id;

内连接

• 查询deptno和id相等的数据

SELECT * FROM emplyee e INNER JOIN department d ON e.deptno = d.id; 

外连接

可能存在不满足条件的可能, 不满足条件的显示NULL

• 左外连接: 右表不满足条件的显示NUll

SELECT e.id, e.name, e.deptno, d.name FROM employee e LEFT JOIN department d ON e.deptno ON e.dept = d.id;

• 右连接: 左右表不满足条件的时候, 左表显示NULL, 右边显示数据

SELECT e.id, e.name, e.deptno, d.name FROM employee e RightJOIN department d ON e.deptno ON e.dept = d.id;

自连接查询

自己连接自己， 起别名

• 求7369员工编号， 姓名， 经理编号和经理姓名

SELECT e1.empno '员工编号', e1.ename, e1.mgr '经理编号', e2.ename '经理姓名' 
FROM emp e1, emp e2
WHERE e1.mgr = e2.empno AND e1.empno = 7369; 

自然连接

连接查询会产生无用的笛卡尔积, 我们通常使用主外键关系等式来去除它们, 而自然链接无需给出主外键等式, 它自然会找到这一等式,
前提: 2张连接的表中, 名称和类型完全一致的列作为条件.

• 查询两张表中, deptno相同的数据

SELECT * FROM t_employee NATURAL JOIN t_department USING(deptno)

子查询

一个SELECT语句包含另一个完整的SELECT语句.
子查询就是嵌套查询, 即SELECT中包含SELECT, 如果一个语句中存在两个或者两个以上的SELECT, 那么就是子查询语句

• 子查询出现的位置
  • WHERE后, 作为查询条件的一部分
  • FROM后面, 作为表
• 当子查询出现在WHERE后面作为条件的时候, 还可以使用如下关键字
  • ANY
  • ALL
• 子查询的结果集的形式
  • 单行单列(用于条件)
  • 单行多列(用于条件)
  • 多行单列(用于条件)
  • 多行多列(用于表)

// 查询工资高于JONES的员工
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'JONES')

// 查询与SCOTT同一个部门的员工
SELECT * FROM emp WHERE deptno = (SELECT deptno WHERE ename = 'SCOTT')

// 查询工资大于30号部门所有人的工资的所有员工
SELECT * FROM emp WHERE sal > (SELECT MAX(sal) FROM deptno = 30)
// 查询工资大于30号部门所有人的工资的所有员工, 使用ALL关键字
// 子查询作为条件
// 子查询形式为多行单列（当子查询结果集形式为多行单列时可以使用ALL或ANY关键字）
SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30)

// 查询工作和工资与MARTIN完全相同的员工信息
SELECT * FROM emp WHERE (job, sal) 
IN 
(SELECT job, sal FROM emp WHERE ename='MARTIN');

// 查有两个以及2个以上的直接下属的员工信息
SELECT * FROM emp WHERE empno 
IN 
(SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr) >= 2)

//  查询员工编号为7788的员工名称、员工工资、部门名称、部门地址
SELECT e.ename,e.sal,d.name,d.location 
FROM emp e,depart d
WHERE e.depno = d.depno AND e.empno = '7788';


SELECT e.ename,e.sal,d.name,d.location 
FROM emp e,(SELECT depno,name,location FROM depart) d
WHERE e.depno = d.depno AND e.empno = '7788';

事务

事务指逻辑上的一组操作, 组成这组操作的各个单元, 要么全部成功, 要么全部不成功.

• 开启事务

start transaction 

• 回滚瑟事务

Rollback

• 提交事务

Commit

Mysql中的函数

• 时间日期函数

函数名	意义
ADDTIME (date2 ,time_interval )	将time_interval加到date2
CURRENT_DATE ( )	当前日期
CURRENT_TIME ( )	当前时间
CURRENT_TIMESTAMP ( )	当前时间戳
DATE (datetime )	返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type )	在date2中加上日期或时间
DATE_SUB (date2 , INTERVAL d_value d_type )	在date2上减去一个时间
DATEDIFF (date1 ,date2 )	两个日期差
NOW ( )	当前时间
YEAR-Month-Day(datetime )	年月日

字符串相关函数

函数名	意义
CHARSET(str)	返回字串字符集
CONCAT (string2 [,... ])	连接字串
INSTR (string ,substring )	返回substring在string中出现的位置,没有返回0
UCASE (string2 )	转换成大写
LCASE (string2 )	转换成小写
LEFT (string2 ,length )	从string2中的左边起取length个字符
LENGTH (string )	string长度
REPLACE (str ,search_str ,replace_str )	在str中用replace_str替换search_str
STRCMP (string1 ,string2 )	逐字符比较两字串大小,
SUBSTRING (str , position [,length ])	从str的position开始,取length个字符
LTRIM (string2 ) RTRIM (string2 )	trim去除前端空格或后端空格

数学函数

函数名	意义
ABS (number2 )	绝对值
BIN (decimal_number )	十进制转二进制
CEILING (number2 )	向上取整
CONV(number2,from_base,to_base)	进制转换
FLOOR (number2 )	向下取整
FORMAT (number,decimal_places )	保留小数位数
HEX (DecimalNumber )	转十六进制
LEAST (number , number2 [,..])	求最小值
MOD (numerator ,denominator )	求余
RAND([seed])	RAND([seed])

Mysql行锁和表锁(悲观锁)

product表, 有id和name, id是主键, 明确指定主键,

行锁

并且有结果集, 会产生行锁(Row_Level Lock).

SELECT * FROM product WHERE id = '66' FOR UPDATE

不会上锁

明确指定主键, 并且无结果集, 无LOCK, 即不会上锁

SELECT * FROM product WHERE id = '-100' FOR UPDATE

表锁

无主键 , 会产生表锁(Table-Level Lock)

SELECT * FROM product WHERE name = 'iphone' FOR UPDATE