MySql

SQL 语句

结构化查询语言，用于操作关系型数据库服务器，包括对数据的增删改查。
(1)SQL 语句执行方式
交互模式
在客户端输入一行命令，回车后会执行一行命令。适用于临时性的查看数据
脚本模式
客户端把要执行的多行命令写在一个脚本文件(.sql)中，然后一次性的提交给服务器执行； 适用于批量的操作数据——增删改查

 mysql  -uroot<C:/xampp/..../01.sql    回车

练习：编写脚本文件 02.sql，显示所有的数据库有哪些，进入到数据库 phpmyadmin，显示所有的数据表有哪些，描述表 pma\_\_recent 有哪些列；最后提交给服务器执行
(2)SQL 语句的规范
假设某一条语句出现了错误，则此条数据往后所有语句不再执行
一条 SQL 语句可以跨越多行，以英文的分号作为结尾
SQL 语句不区分大小写，习惯上关键字大写，非关键字小写
分为单行注释(#...)和多行注释(/_..._/)，注释的代码不被服务器执行

(3)常用的 SQL 语句

 丢弃数据库，如果存在
  DROP  DATABASE  IF  EXISTS  jd;
 创建新的数据库
  CREATE   DATABASE  jd;
 进入创建的数据库
  USE  jd;
 创建保存数据的表
  CREATE  TABLE  student(
    sid  INT,
    name  VARCHAR(8),
    sex  VARCHAR(1),
    score  INT
  );
插入数据
 INSERT  INTO  student  VALUES('1', 'range', 'g', '0');
查询数据
 SELECT * FROM  student;

练习：编写脚本文件 04_xuexi_table.sql，先丢弃数据库 xuexi_table，如果存在；创建一个新的数据库 tabel; 最后在交互模式下查看是否创建。

1.SQL语句
修改数据
UPDATE  user  SET  upwd='123456',isOnline='y' WHERE  uid='1';
删除数据
DELETE  FROM  user  WHERE  uid='3';

(1)存储英文字符
ASCII 对 128 个英文字母及其字符进行了编码
Latin-1 对欧洲字符及符号进行编码，总共有 256 个，兼容 ASCII 码
(2)存储中文字符
GB2312 对 6 千多常用的汉字进行了编码，兼容 ASCII 码
GBK 对 2 万多汉字进行了编码，兼容 GB2312
BIG5 台湾繁体字编码，兼容 ASCII
Unicode 对世界上主流国家常用的语言进行了编码，具体的存储方案 utf-8，utf-16，utf-32；兼容 ASCII，其它的不兼容。
(3)mysql 的默认存储编码
默认使用使用 Latin-1 编码
(4)解决 mysql 中文乱码

 脚本文件另存为的编码为UTF8
 客户端连接服务器端的编码为UTF8
    SET  NAMES  UTF8;
 服务器端创建数据库使用的编码为UTF8
    CREATE  DATABASE  xz  CHARSET=UTF8;
 练习：编写脚本文件01_sina.sql，先丢弃再创建数据库sina，设置存储的编码为UTF8，创建保存新闻数据的表news，包含编号nid，标题title，内容cont，作者author，添加若干条数据，删除1条，修改1条。

3.mysql 中的列类型

在创建数据表的时候，指定的列存储的数据类型
CREATE TABLE t1( nid 列类型 );
(1)数值型——引号可加可不加
tinyint 微整型，占 1 个字节，范围-128~127
smallint 小整型，占 2 个字节，范围-32768~32767
int 整型，占 4 个字节，范围-2147483648~2147483647
bigint 大整型，占 8 个字节
float 单精度浮点型，占 4 个字节，最大是 3.4e38，可能产生计算误差
double 双精度浮点型，占 8 个字节，范围比 bigint 大的多，可能产生计算误差
decimal(M,D) 定点小数，小数点不会，几乎不会产生误差，M 代表总的有效位数，D 代表小数点后的有效位数
boolean/bool 布尔型，只有两个值，分别是 true（真）和 false（假），这两个值不能加引号。使用的时候会自动转为 tinyint，true 变为了 1，false 变为了 0；也可以直接使用 1 或者 0。往往用于存储只有两个结果的数据，例如性别、是否在线、是否为会员...
(2)日期时间型——必须加引号
date 日期型 2020-12-25
time 时间型 15:20:30
datetime 日期时间型 2020-12-25 15:20:30
(3)字符串型——必须加引号
varchar(M) 变长字符串，操作速度相对慢，M 最大值是 65535
char(M) 定长字符串，操作速度相对快，M 最大值是 255，往往存储长度固定的数据，例如身份证号码，手机号码可以使用；可能产生空间浪费
text(M) 大型变长字符串，M 最大值是 2G
char(5) varchar(5)
a a\0\0\0\0 a\0
ab ab\0\0\0 ab\0
一二三 一二三\0\0 一二三\0

浮点型
12345.6789
1234.56789*10
123.456789*10^2
12.3456789*10^3
1.23456789*10^4
定点
25.8
5000.00
1TB=1024GB
1GB=1024MB
1MB=1024KB
1KB=1024BYTE 字节
1BYTE=8BIT 位
1010001001111 计算机底层只认识 2 进制数字
CREATE TABLE t1(
id int,
age tinyint,
phone char(11),
price decimal(6,2), #9999.99
sex boolean, # 1->男 0->女
birthday date, #1997-5-12
detail varchar(5000)
)

4.列约束

CREATE TABLE t1( lid INT 列约束 );
(1)主键约束——primary key
声明了主键约束的列上不允许插入重复的值，一个表中只能有一个主键约束，通常加在编号列上，查询的时候会按照编号从小到大显示，会加快查询速度。
NULL 空 表示一个无法确定的值，例如无法确定一个员工的姓名，工资，生日都可以使用 NULL 表示
声明了主键约束的列上不允许插入 NULL
(2)唯一约束——unique
声明了唯一约束的列上不允许插入重复的值，允许插入 NULL，而且是多个 NULL。
NULL 代表不确定的值（未知的值），两个 NULL 不能划等号。
练习：设置笔记本表的 title 列为唯一约束，并插入数据测试。
(3)非空约束——not null
声明了非空约束的列上禁止插入 null
练习：设置笔记本表的 title 为唯一约束和非空约束，并插入数据测试
(4)默认值约束——default
可以通过 default 给列设置默认值，具体有两种应用方式
insert into laptop values(5,'小米 Air',default,...);
insert into laptop(lid,title) values(6,'apple');
练习：设置默认的笔记本的规格为 'web 开发版'。
(5)检查约束——check
检查约束可以对要插入的数据进行自定义的验证
CREATE TABLE t1(
price DECIMAL(7,2) check(price>=0)
);
但是，mysql 不支持检查约束，影响数据的插入速度。
(6)外键约束
外键的取值范围必须到另一个表的主键列中去找。
两者的列类型要保持一致。
foreign key(外键列) references 另一个表(主键列)

2.自增列

auto_increment: 自动增长，自动获取当前的最大值，然后加 1 插入
自增列添加在主键列上
注意：只适用于整数型的列上，同时允许手动赋值，如果要想实现自增，需要赋值 NULL
练习：应用列约束和自增列
编写脚本文件 01_xuexi_table.sql，先丢弃再创建数据库 xuexi_table，设置编码为 UTF8,进入数据库，创建保存部门分类的表 dept，包含编号 did 和部门名称 dname，插入一下数据
10 研发部 20 市场部 30 运营部 40 测试部
创建保存员工数据的表 emp，包含编号 eid，姓名 ename，性别 sex，生日 birthday，工资 salary，所属部门编号 deptId，插入若干条数据。

3.简单查询

(1)查询特定的列
示例：查询所有员工的编号和姓名
SELECT eid,ename FROM emp;
练习：查询出所有员工的姓名，性别，工资，生日
SELECT ename,sex,salary,birthday FROM emp;
(2)查询所有的列
练习：查询出员工所有的列
SELECT * FROM emp;
SELECT eid,ename,sex,birthday,salary,deptId FROM emp;
(3)给列起别名
示例：查询出所有员工的编号和姓名，使用汉字别名
SELECT eid AS 编号,ename AS 姓名 FROM emp;
练习：查询出所有员工的姓名，性别，生日，使用汉字别名
SELECT ename AS 姓名,sex 性别,birthday 生日 FROM emp;
练习：查询出所有员工的姓名和工资，分别使用一个字母作为别名
SELECT ename a,salary b FROM emp;
AS 关键字可以省略
(4)显示不同的记录
示例：查询出员工所属的部门编号有哪些
SELECT DISTINCT deptId FROM emp;
练习：查询出都有哪些性别的员工
SELECT DISTINCT sex FROM emp;
(5)查询时执行计算
示例：计算 1+2+3-5*8.3+9.5*7
SELECT 1+2+3-5*8.3+9.5*7;
示例：查询出所有员工的姓名及其年薪
SELECT ename,salary*12 FROM emp;
练习：假设每个员工的工资增长 500 元，年终奖 10000，查询所有员工的姓名及其年薪，使用汉字别名
SELECT ename 姓名,(salary+500)_12+10000 年薪 FROM emp;
(6)对结果集排序
示例：查询所有的部门，结果集按照编号升序排列
SELECT _ FROM dept ORDER BY did ASC; #ascendant 升序
示例：查询所有的部门，结果集按照编号降序排列
SELECT _ FROM dept ORDER BY did DESC; #descendant 降序
练习：查询所有的员工，结果集按照工资的降序排列
SELECT _ FROM emp ORDER BY salary DESC;
练习：查询所有的员工，结果集按照年龄从大到小排列（生日从小到大）
SELECT _ FROM emp ORDER BY birthday;
如果不加排序规则，默认是按照升序排列(ASC)
练习：查询所有的员工，结果集按照姓名升序排列(按照 Unicode 码排)
SELECT _ FROM emp ORDER BY ename;
练习：查询所有的员工，结果集按照工资的降序排列，如果工资相同按照姓名排列
SELECT _ FROM emp ORDER BY salary DESC,ename;
练习：查询所有的员工，结果集要求女员工显示在前，如果性别相同按照年龄从大到小排序
SELECT _ FROM emp ORDER BY sex ASC,birthday ASC;

(7)条件查询

示例：查询出编号为 5 的员工的所有列
SELECT _ FROM emp WHERE eid=5;
练习：查询出姓名为 king 的员工所有列
SELECT _ FROM emp WHERE ename='king';
练习：查询出 20 号部门下的员工有哪些
SELECT _ FROM emp WHERE deptId=20;
练习：查询出工资在 5000 以上的员工所有列
SELECT _ FROM emp WHERE salary>5000;
练习：查询出 1991 年后出生的员工有哪些
SELECT \* FROM emp WHERE birthday>='1991-1-1';

> > = < <= = !=(不等于)
> > 练习：查询出不在 10 号部门的员工有哪些
> > SELECT _ FROM emp WHERE deptId!=10;
> > 练习：查询出没有明确部门的员工有哪些
> > SELECT _ FROM emp WHERE deptId IS NULL;
> > 练习：查询出有明确部门的员工有哪些
> > SELECT _ FROM emp WHERE deptId IS NOT NULL;
> > 练习：查询出工资在 5000 以上的男员工有哪些  
> >  SELECT _ FROM emp WHERE salary>5000 AND sex=1;
> > 练习：查询出工资在 5000~7000 之间的员工有哪些
> > SELECT _ FROM emp WHERE salary>=5000 AND salary<=7000;
> > SELECT _ FROM emp WHERE salary BETWEEN 5000 AND 7000;
> > 练习：查询出工资不在 5000~7000 之间的员工有哪些  
> >  SELECT _ FROM emp WHERE salary<5000 OR salary>7000;
> > SELECT _ FROM emp WHERE salary NOT BETWEEN 5000 AND 7000;
> > 练习：查询出 1993 年出生员工有哪些
> > SELECT _ FROM emp WHERE birthday>='1993-1-1' AND birthday<='1993-12-31';
> > 练习：查询出 20 号部门和 30 号部门的员工有哪些 满足其一 or
> > SELECT _ FROM emp WHERE deptId=20 OR deptId=30;
> > SELECT _ FROM emp WHERE deptId IN(20,30);
> > 练习：查询出不在 20 号部门和 30 号部门的员工有哪些
> > SELECT _ FROM emp WHERE deptId NOT IN(20,30);

1.简单查询

(1)模糊条件查询
示例：查询员工姓名中含有字母 e 的员工有哪些
SELECT _ FROM emp WHERE ename LIKE '%e%';
练习：查询姓名中以 e 结尾的员工有哪些
SELECT _ FROM emp WHERE ename LIKE '%e';
练习：查询姓名中倒数第 2 个字符为 e 的员工有哪些
SELECT * FROM emp WHERE ename LIKE '%e*';
% 匹配任意 0 个或者多个字符 >=0
* 匹配任意 1 个字符 =1
以上两个匹配的符号只能结合 LIKE 使用

(2)分页查询

查询的结果集有太多的数据，一次显示不完可以做成分页显示
需要有两个已知的条件：当前的页码和每页的数据量
开始查询的值 = （当前的页码-1）*每页的数据量
语法：
SELECT _ FROM emp LIMIT 开始查询的值, 每页的数据量;
假设每页显示 5 条数据
第 1 页：SELECT _ FROM emp LIMIT 0,5;
第 2 页：SELECT _ FROM emp LIMIT 5,5;
第 3 页：SELECT _ FROM emp LIMIT 10,5;
分页查询中的开始查询的值和每页的数据量必须是数值型，不能添加引号。 2.复杂查询

(1)聚合查询和分组查询

示例：查询出所有员工的数量
SELECT count(eid) FROM emp; #推荐使用主键列
练习：通过部门编号这一列查询员工的数量
SELECT count(deptId) FROM emp;
练习：通过姓名查询所有男员工的数量
SELECT count(ename) FROM emp WHERE sex=1;
聚合函数，函数是一个功能体，可以接收若干个数据，返回处理的结果——饺子机
count()/sum()/avg()/max()/min()
数量 总和 平均 最大 最小
练习：查询出所有员工工资的总和
SELECT sum(salary) FROM emp;
练习：查询出所有员工的平均工资
SELECT avg(salary) FROM emp;
练习：查询出男员工的最低工资
SELECT min(salary) FROM emp WHERE sex=1;
练习：查询出年龄最小的员工的生日
SELECT max(birthday) FROM emp;
分组查询中只能查询聚合函数和分组条件
示例：查询出每个部门员工的最高工资，最低工资，平均工资
SELECT max(salary),min(salary),avg(salary),deptId,ename FROM emp GROUP BY deptId;
练习：查询出男女员工的数量，工资总和，平均工资
SELECT count(eid),sum(salary),avg(salary),sex FROM emp GROUP BY sex;

(2)子查询

示例：查询研发部的员工有哪些
步骤 1：查询出研发部的部门编号是多少——10
SELECT did FROM dept WHERE dname='研发部';
步骤 2：查询出部门编号为 10 的员工有哪些
SELECT _ FROM emp WHERE deptId=10;
综合：
SELECT _ FROM emp WHERE deptId=(SELECT did FROM dept WHERE dname='研发部');
练习：查询出比 tom 工资高的员工有哪些
步骤 1：查询出 tom 的工资——6000
SELECT salary FROM emp WHERE ename='tom';
步骤 2：查询出工资大于 6000 的员工
SELECT _ FROM emp WHERE salary>6000;
综合：
SELECT _ FROM emp WHERE salary>(SELECT salary FROM emp WHERE ename='tom');
练习：查询出和 tom 同一年出生的员工有哪些
步骤 1：查询出 tom 出生的年份——1990
SELECT year(birthday) FROM emp WHERE ename='tom';
步骤 2：查询出 1990 年出生的员工有哪些
SELECT _ FROM emp WHERE year(birthday)=1990;
综合：
SELECT _ FROM emp WHERE year(birthday)=(SELECT year(birthday) FROM emp WHERE ename='tom') AND ename!='tom';
year() 获取日期中的年份
示例：查询所有员工出生的年份
SELECT year(birthday) FROM emp;

(3)多表查询

示例：查询所有的员工姓名及其部门名称
SELECT ename,dname FROM emp,dept;
产生笛卡尔积，需要添加查询的条件才可以避免
SELECT ename,dname FROM emp,dept WHERE deptId=did;
存在的问题：无法查询出没有部门的员工，也无法查询出没有员工的部门。
新增的多表查询语法
(1)内连接
SELECT ename,dname FROM emp inner join dept on deptId=did;
(2)左外连接
先写哪一个表，哪一个就是左，显示里边所有的记录，即使没有对应的
SELECT ename,dname FROM emp left outer join dept on deptId=did;
(3)右外连接 right outer join
后写哪一个表，哪一个就是右，显示里边所有的记录，即使没有对应的
SELECT ename,dname FROM emp right outer join dept on deptId=did;
左外和右外连接中的 outer 关键字可以省略
(4)全连接
full join  
 mysql 不支持这种连接

左外和右外的结果组合起来
union 合并相同的记录
union all 不合并相同的记录

(SELECT ename,dname FROM emp left outer join dept on deptId=did)
union
(SELECT ename,dname FROM emp right outer join dept on deptId=did);

项目中的日期时间
时间戳：距离计算机元年(1970-1-1)的毫秒数
1 秒=1000 毫秒
2020-1-1 2020 年 1 月 1 日 2020/1/1
50*365*24*60*60\*1000 = 1,576,800,000,000‬
使用 bigint 才可以存的下
性别 1/0 男/女 man/woman 男孩/女孩