MySQL
SQL语句分类
- DDL(Data Definition Language) -- 数据库定义语言，增删改查库或表
- DML(Data Control Language) -- 数据库操作语言 操作数据 对表中数据的增删改查
- DCL(Data Manipulation Language) -- 数据库控制语言 创建用户，管理用户权限，事务控制
非标准分类：DQL(Data Query Language) -- 数据库查询，将数据库的增删改查中的“查”单独提出来，因为最重要也最难。
与服务器连接
先把MySQL配置到系统环境变量中，以为5.7为例，Path里增加C:\Program Files\MySQL\MySQL Server 5.7\bin。cmd里输入mysql -u root -p，其中u代表user，这里用root，p代表密码，不输入密码，回车后也会提示你输入。填入自己的密码就连接成功了。
数据库的CURD
1、添加新数据库
create database 库名称 [character set 码表名称 collate 字符校对集名称];，括号里面是可选项，一般不加也可，举个例子create database book;，注意每行语句结束，需要加;。show databases;指令可以显示全部数据库。
2、删除数据库
drop database 库名称;
3、修改数据库（可修改码表和字符校对集）
alter database 库名称 character set utf8 collate utf8_bin;
默认utf-8，该语句不常用。
4、使用一个库
use 库名称;成功会出现Database changed。
select database();可以显示当前正在使用的数据库。
5、显示创建数据库语句
show create database 数据库名称;
数据库中的数据类型
1、数字型
整型
| 整型类型 | 大小 | 对比Java |
|---|---|---|
| TINYINT | 1字节 | byte |
| SMALLINT | 2字节 | short |
| MEDIUMINT | 3字节 | ---- |
| （常用）INT | 4字节 | int |
| BIGINT | 8字节 | long |
浮点型
| 浮点类型 | 大小 | 对比Java |
|---|---|---|
| FLOAT | 单精度4字节 | float |
| （常用）DOUBLE | 8字节 | double |
| DECIMAL | -- | String保存的数字，没有精度损失 |
DOUBLE 和 DECIMAL 区别？
- DOUBLE类型在运算时会有精度的缺失。
- DECIMAL 就是解决精度缺失问题的。（底层使用字符串来保存数字）
单纯想表示小数属性时，使用double。
需要频繁参与运算的小数，使用decimal。
2、字符串
注意: 字符串类型要使用单引号包裹.
短字符串类型
CHAR/VARCHAR (最大长度255字节)
char和varchar有什么区别?
- char：定长字符串。
- varchar：变长字符串。
同时指定长度为10。当存储 abc时候
char =》 'abc ' 后面有7个空格
varchar => 'abc'
结论： 开发中varchar用的最多。 char只在表示固定长度的枚举中使用。例如 ：性别（用01,02表示）
长字符串类型(流类型)
TEXT
保存文本(字符流) --> 当要保存的内容超过255字节时使用。java中的writer 字符。TEXT只能存储字符数据.
BLOB
保存字节(字节流) --> 开发中用不到。 java中的stream 字节。
BLOB可以存储字符和多媒体信息(图片 声音 图像)
3、日期类型
| 类型 | 记录的数据 | 例子 |
|---|---|---|
| date | 只记录日期 | 2017-03-11 |
| time | 只记录时间 | 10:36:25 |
| year | 只记录年 | 2017 |
| （常用）datatime | 记录日期、时间 | 2017-03-11 10:36:25 |
| （常用）timestamp | 记录日期、时间 | 2017-03-11 11:36:25 |
datatime 和 timestamp 区别?
这两种类型记录的数据是一模一样。
-
区别在于插入的时候，如果插入datatime类型时，没有传值，那么该类型默认值就是
null；如果插入timestamp类型时，没有传值，那么该类型默认值就是当前时间。
表的CURD
1、创建表
create table t_user (
id int,
name varchar(20),
sal double(4,3),
birthday datetime,
hiredate timestamp
);
double(4, 3)中间是逗号，表示小数点前4位，小数点后面3位。
2、显示所有表、查看某个表的结构
显示所有表：show tables;
查看某个表的结构：desc 表名;
3、删除表
drop table 表名
4、添加一列
alter table 表名 add 列名 类型;
alter table t_user add age int;
5、修改列的类型
alter table 表名 modify 列名 类型;
alter table t_user modify age varchar(20);
6、修改列的名称
alter table 表名 change 旧列名 新列名 数据类型；
alter table t_user change age location varchar(20);
7、删除某一列
alter table 表名 drop 列名;
alter table t_user drop location;
8、修改表名
rename table 旧表名 to 新名;
rename table t_user to t_root;
列的约束
1.非空约束(not null) 指定非空约束的列, 在插入记录时 必须包含值.
2.唯一约束(unique) 该列的内容在表中. 值是唯一的.
3.主键约束(primary key) 当想要把某一列的值,作为该列的唯一标示符时,可以指定主键约束
(包含 非空约束和唯一约束). 一个表中只能指定一个主键约束列。主键约束 , 可以理解为非空
+唯一.
注意： 并且一张表中只能有一个主键约束.
主键自增
注意：
- 前提某个表的主键是数字. 我们可以将该主键设置为自增.
- 使用主键自增可能会造成主键的断层。（删除后数值不减，比如删除4、5行，下次新增行从6开始）
- mysql,sqlserver,sqllite这三个数据库具有该功能.
- 主键自增只能给主键约束的列加。
自增就是 每次插入记录时不需要指定值，该字段自己维护自己的值。维护方式就是每次加1。
语法如id int primary key auto_increment,
表中数据的增删改(DML)
1、为表添加记录
insert into 表名[(列名1,列名2...)] values (值1,值2...);
- 指定要插入哪些列
insert into t_user(name, email) values('tom','tom@163.com');
注意: 数据类型为字符串类型的，需要使用单引号包裹.
不指定插入哪些列，默认全部列，需要指定每一列的值。
insert into t_user values(null,'jerry','jerry@itcast.cn');id自增所以填写null。
``insert into t_user(name,email) values('汤姆','tom2@163.com');`
2、修改一条记录
update 表名 set 列名1 = 值 , 列名2 = 值 ....[where 条件1,条件2...]
UPDATE t_user SET NAME='rose' WHERE id=7;表示将id为7的数据name改为rose。
如果不指定id则是将所有列name都更新为rose。
3、删除某一条记录
DELETE FROM 表名 [WHERE 条件];
DELETE FROM t_user WHERE NAME='God';把name是God的记录删除。
DELETE FROM t_user;删除整张表中所有记录。
使用truncate删除表中记录。
TRUNCATE TABLE employee;
DELETE 删除 和 TRUNCATE删除(了解) 两者有什么区别?
首先,这两种都是删除表中的记录。
不同的是:
- delete 是逐行标记删除. TRUNCATE 是将整张表包括表结构都移除,然后将表重新创建.
- delete DML语句。 TRUNCATE DDL语句。
- delete 删除的记录可以被恢复，TRUNCATE 不能恢复。
- delete 不释放空间，TRUNCATE 释放空间。
- TRUNCATE 会提交事务。
拿自增的id来说，delete后id不会归为1，而是延续被删除的id继续自增。truncate后id又像新表一样从1开始。
4、查询
语法：
SELECT selection_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,age,gender from stu;
谁的效率更高?下面的效率更高。*需要运算。可以忽略不计，用*简便。
- 查询某一列
select sname from stu;只会查询sname这一行。
2.1 条件查询介绍
条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：
=、!=、<>、<、<=、>、>=；
BETWEEN…AND；
IN(SET)/NOT IN(SET)
IS NULL/IS NOT NULL
---条件连接符
AND； &&
OR； ||
NOT； !
查询例子
-- 查询性别为女，并且年龄小于50的记录
select * from stu where gender='female' and age<50;
-- 查询学号为S_1001，或者姓名为liSi的记录
select * from stu where sid='S_1001' or sname='liSi';
-- 数据库中,sql语句不区分大小写 ,但是数据区分大小写.
-- 查询学号为S_1001，S_1002，S_1003的记录
select * from stu where sid='S_1001' or sid='S_1002' or sid='S_1003';
select * from stu where sid in('S_1001','S_1002','S_1003');
-- 查询学号不是S_1001，S_1002，S_1003的记录
select * from stu where not (sid='S_1001' or sid='S_1002' or sid='S_1003');
select * from stu where sid not in('S_1001','S_1002','S_1003');
-- 查询年龄为null的记录
select * from stu where age=null;
-- null的特性: null不等于null 所以判断时应如下写法:
select * from stu where age is null;
-- 查询年龄在20到40之间的学生记录
select * from stu where age >= 20 and age <= 40;
select * from stu where age between 20 and 40;
-- 查询性别非男的学生记录
select * from stu where gender!= 'male';
select * from stu where not gender='male';
select * from stu where gender not in ('male');
-- 查询姓名不为null的学生记录
select * from stu where sname is not null;
select * from stu where not sname is null;
模糊查询
where 字段 like '表达式';
% 通配任意个字符.
_通配单个字符。
*通配任意多个字符。
-- 查询姓名由5个字母构成的学生记录
select * from stu where sname like '_____';
-- 查询姓名由5个字母构成，并且第5个字母为“i”的学生记录
select * from stu where sname like '____i';
-- 查询姓名以“z”开头的学生记录
-- 说明: "%"该通配符匹配任意长度的字符.
select * from stu where sname like 'z%';
-- 查询姓名中第2个字母为“i”的学生记录
select * from stu where sname like '_i%';
-- 查询姓名中包含“a”字母的学生记录
select * from stu where sname like '%a%';
去除重复的记录
-- 关键词: distinct 去除重复查询结果记录.
select gender from stu; -- 出现大量重复的记录
select distinct gender from stu; -- 去除重复的记录
查看雇员的月薪与佣金之和
select sal*12+comm from emp;
null与任何数字计算结果都是null.上面的写法是错误的.
使用IFNULL(参数1,参数2) 函数解决. 判断参数1的值是否为null,如果为null返回参数2的值.
select sal*12 + IFNULL(comm,0) from emp;
这个函数在所有数据库通用吗？不通用。
给列名添加别名
SELECT ename '姓名' FROM emp; -- 把ename重命名为姓名
select sal*12 + IFNULL(comm,0) as '年收入' from emp;
select sal*12 + IFNULL(comm,0) '年收入' from emp;
select sal*12 + IFNULL(comm,0) 年收入 from emp;
按顺序查询
-- 查询所有学生记录，按年龄升序排序
-- asc: 升序
-- desc:降序
select * from stu order by age asc;
-- 默认就是升序
select * from stu order by age;
-- 查询所有学生记录，按年龄降序排序
select * from stu order by age desc;
-- 查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序。前面一个优先，后面一个仅仅是前面的相同时才会比较
select * from emp order by sal desc , empno asc;
聚合函数
聚合函数是用来做纵向运算的函数：
COUNT()：统计指定列不为NULL的记录行数；
MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；
-- COUNT
-- 当需要纵向统计时可以使用COUNT()。
-- 查询emp表中记录数：
select count(*) from emp;
-- 查询emp表中有佣金的人数：
select count(*) from emp where comm is not null and comm >0;
-- 查询emp表中月薪大于2500的人数：
select count(*) from emp where sal > 2500;
-- 统计月薪与佣金之和大于2500元的人数：
select count(*) from emp where sal+IFNULL(comm,0) > 2500;
-- 查询有佣金的人数并且有领导的人数：
select count(*) from emp where comm > 0 and mgr is not null;
-- SUM(计算总和)和AVG(计算平均值)
-- 当需要纵向求和时使用sum()函数。
-- 查询所有雇员月薪和：
select sum(sal) from emp;
-- 查询所有雇员月薪和，以及所有雇员佣金和：
select sum(sal),sum(comm) from emp;
-- 查询所有雇员月薪+佣金和：
select sum(sal+IFNULL(comm,0)) from emp;
-- 统计所有员工平均工资：
select avg(sal) from emp;
-- MAX和MIN
-- 查询最高工资和最低工资：
select max(sal),min(sal) from emp;
分组查询
当需要分组查询时需要使用GROUP BY子句，例如查询每个部门的工资和，这说明要使用部分来分组。
-- 查询每个部门的部门编号和每个部门的工资和：
select deptno,sum(sal) from emp group by deptno;
-- 查询每个部门的部门编号以及每个部门的人数：
select deptno,count(ename) from emp group by deptno;
-- 查询每个部门的部门编号以及每个部门工资大于1500的人数：
select deptno,count(ename) from emp where sal>1500 group by deptno ;
-- HAVING子句
-- 查询工资总和大于9000的部门编号以及工资和：
select deptno,sum(sal) from emp group by deptno having sum(sal)>9000;
-- 使用having在分组之后加条件.
-- where和having都可以加条件?
-- where在分组之前加条件.
-- having在分组之后加条件.
-- where的效率要远远高于having. 分组本身消耗资源非常大.
LIMIT
LIMIT用来限定查询结果的起始行，以及总行数。
第一个是起始行，第二个是查几条。
-- 查询5行记录，起始行从0开始
select * from emp limit 0,5;
-- 查询10行记录，起始行从3开始
select * from emp limit 3,10;
如果一页记录为5条，希望查看第3页记录应该怎么查呢？
-- 第一页记录起始行为0，一共查询5行；
select * from emp limit 0,5;
-- 第二页记录起始行为5，一共查询5行；
select * from emp limit 5,5;
-- 第三页记录起始行为10，一共查询5行；
select * from emp limit 10,5;
by @sunhaiyu
2017.3.11
