MySQL笔记

1.数据的存储方式
内存：临时存储
文件：不适合存储大量数据
第三方服务器：适合绝大多数使用
数据库服务器：
浏览器 请求 web服务器，web提取文件服务器和数据库服务器
一起反馈 响应 浏览器
2.什么是数据库
数据库是按照一定的形式来组织、存储数据，目的是为了对数据操作
——增删改查
1.数据库的发展历史
网状数据库:
层次型数据库：80年代前
关系型数据库：
非关系型数据库（nosql）：
2.关系型数据库逻辑结构
Server->database->table（表）->row（行）->column（列）

3.使用MySQL数据库
Orade分支：MySQL
Martin分支：MariaDB
XAMPP
服务器套装，包含有多个服务器MySQL、Apache……
https：//www.apachefriends.org/downioad.html
（1）MySQL部署结构
服务器端：负责存储/维护数据——银行总行机房
C:/xampp/mysql/bin/mysqld.exe 启动文件
d->deamon精灵/守护者
确保3306端口不能被占用
客户端：负责向服务器端发起增删改查——ATM机
C:/ /xampp/mysql/bin/mysql.exe 客户端工具
（2）使用客户端连接服务器端
mysql.exe -h127.0.0.1 -P3306 -uroot -p
-h host 主机
-P port 端口
- u user 用户
-p password 密码
mysql –uroot 简写形式
4.mysql常用管理命令
quit； 退出服务器连接
show databases； 显示服务器上当前所有的数据库
use 数据库名字；进入指定的数据库
show tables；显示当前数据库中所有的数据表
desc 表名； 描述表中都有哪些列（表头）
5.SQL语句
SQL：structured query language，结构化查询语言，用于操作关系型数据库服务器，对数据执行增删改查
SQL命令的两种执行方式
（1）交互模式：客户端输入一行，点击回车，服务器执行。适用于临时性的查看数据。
（2）脚本模式：客户端把要执行的多行命令编写在一个文件中，一次性提交给服务器执行。适用于批量的操作数据。
Mysql –uroot <D:\CL\MySQL\02.sql
SQL语法规范
（1）每条语句必须以英文的分号作为结尾，一条语句可以跨越多行。
（2）若某一条语句出现语法错误，则此条语句以及后边所有的语句不会再执行
（3）SQL命令不区分大小写，习惯上数据库关键字用大写，非关键字用小写。
（4）SQL命令中可以使用单行注释（#…）和多行注释（/…/），注释的内容不会被服务器所执行。
常用的SQL命令
（1）丢弃指定的数据库，如果存在的话
DROP DATABASE IF EXISTS jd；/drop database if exists jd
（2）创建新的数据库
CREATE DATABASE jd ; /create database jd
（3）进入创建的数据库
USE jd; /use jd
（4）创建保存数据的表
CREATE TABLE student( /create table student
sid INT,
name VARCHAR(8),
sex VARCHAR(1)，
score INT
);
（5）向数据表中插入数据
INSERT INTO student VALUSES(‘1’,’tom’,’f’,’80’);
Insert into student valuses(‘1’,’tom’,’f’.’80’);
（6）查询数据表中所有的数据
SELECT * FROM student;
Select * from student;
（7）修改数据
UPDATE student SET score=’x’ sex=‘m’ WHERE sid=‘5’；
Update student set score=’x’sex=’m’ weher sid=’5’;
（8）删除数据
DELETE FROM student WHERE sid=‘3’；
Delete from student where sid=’3’;

练习：编写脚本文件05_dang.sql，先丢弃在创建数据库dang，进入到该数据库， 创建保存图书的表book，包含编号bid，标题title，价格price，出版社publish；插入4条数据，删除编号为3的数据，修改编号为2的数据，查询所有数据

使用脚本模式（03.sql）向服务器中提交多行命令 SQL 命令，尝试把其中一行写错
练习：查看phpmyadmin中以下表都有哪些列
Pma__recent
Pma__history
Pma__favorite
课后任务
（1）复习今天内容
（2）练习
编写脚本文件xz_sql,先丢弃在创建数据库xz，进入发数据库，创建保存用户的表user，包含uid，uname，upwd，email，phone，sex，username（真实姓名）；regtime注册时间，isonline是否在线
插入5条输数据，删除1条，更改一条，查询数据。

Drop database if exists jd;
Create database jd;
Use jd;
Create table student(
Sid INT,
Sex varchar(8),
);
Insert into student values(‘1’,’f’);
Insert into student values(‘2’,’m’);
Updata student set sid=’3’,where sex=’m’;
Delect from student where sid=’1’;
Select * from student;
===========================================================================================day02
复习
Mysql
服务器端：开启服务器
客户端：连接
连接
Mysql.exe -h127.0.0.1 –P3306 –iroot –p
Mysql –uroot
Mysql –uroot
Mysql –uroot<路径
交互模式
脚本模式
常用命令
Show database
Use 名字
Desc 表名
Quit
Sql 命令
增删改查
Drop database if exists jd
Create database jd
Use jd
Create table book(

)
Insert into book values(‘’)
Updata book set –= where=
Delect from book _ where __
Select * from _

标准sql语句分类
DDL：data define language定义数据结构
Create/drop/alter
DML:data manipulate language操作数据
Insert/update/delete
DQR：data query language查询数据
SELECT
DCL:data control language 控制用户权限
GRANT授权（grant）/REVOKE(收权)revoke

1.计算机存储字符
（1）如何存储英文字符
ASCII总共有128个，对所有的英文字母和符号进行编码。
Hel 104101108
Latin-1：总共有256，兼容ASCII码，同时对欧洲符号进行了编码。Mysql默认使用这种编码。
（2）如何存储中文字符
GB2312:对常用6千多汉字进行了编码，兼容AXCII
GBK:对2万多汉字进行了编码，兼容GB2312
BIG5：台湾繁体字编码，兼容ASCII
Unicode：对世界上主流国家常用的语言进行了编码，兼容ASCII,不兼容GB2312,GBK,BIG5,具体分为UTF-8,UTF-16,UTF-32存储方案。
（3）解决mysql存储中文乱码
Sql脚本文件另存为的编码
客户端连接服务器的编码（set names UTF8）
服务器端创建数据库使用的编码（charset=UTF8）
2.mysql中的列类型
创建数据表的时候，指定的列可以存储的数据类型
CREATE TABLE t1(nid 列类型)；
（1）数值型 引号可加可不加
TINYINT（tinyint） 微整型,占一个字节 范围-128~127
SMALLINT（smallint） 小整型，占2个字节，范围-32768~32767
INT（int）整型，占4个字节，范围-2147483648~2147483647
BIGINT(bigint) 大整型，占8个字节，
FLOAT（float） 单精度浮点型，占4个字节, 最多3.4E38，可能产生计算误差。
DOUBLE（double） 双精度浮点型，占8个字节，范围比BIGINT大的多，也可能产生计算误差
DECIMAL（decimal）（M,D） 定点小数，不会产生计算误差
M代表总的有效位数，D代表小数点后的有效位数
BOOL 布尔型，只有两个值TRUE/1、FALSE/0，TRUE和FALSE不能加引号；真正存储数据的时候，会变成TINYINT,数据是1和0
（2）日期时间 必须加引号
DATE 日期型 ‘2018-10-31’
TIME 时间型 ‘14:30:50’
DATETIME 日期时间型 ‘2018-10-31 14:30:50’
（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
abcde abcde abcde
一二三 一二三\0\0 一二三\0

1TB->GB->MB->KB->BYTE->BIT
1BYTE=8BIT
3.列约束
Mysql可以对要插入的数据进行特定的验证，只有满足条件才允许插入到数据表中，否则被认为非法的插入
列如：一个人的性别只能是男或者女，一个人的年龄0~100
（1）主键约束——PRIMARY KEY (primary key
声明了主键约束的列上的值不能出现重复，一个表中只能有一个主键，通常加在编号列，设置了主键约束就不能使用NULL值
表中查询的记录会按照主键有小到大的顺序排列——加快查找速度
NULL 表示空，在插入数据时，无法确定要保存的数据
例如：无法确定员工的工资，生日都可以使用NULL
（2）非空约束——NOT NULL
声明了非空约束的列上不能插入NULL值

练习：编写脚本文件01_sina.sql，创建数据库sina。设置存储编码为utf8，进入该数据库，创建数据表news保存新闻数据，包含nid，title标题，ctime发表时间，content内容，author作者，cfrom来源，插入若干条数据。在交互模式下查询数据。

练习：编写02_xuezi.sql文件。县丢弃在创建数据库xuezi，设置存储的编码为UTF8，进入该数据库，创建保存笔记本数据的表laptop，包含lid，title标题，price价格，stockcount库存量，shelftime上架时间，isindex是否显示在首页；插入4条数据

练习：编写脚本文件03_tedu.sql,创建数据库tedu。设置存储的编码为UTD8，创建部门数据的表dept，包含did，dname部门名称，empcount员工数量，插入以下数据
10 研发部 3
20 运营部 2
30 市场部 2
创建保存员工数据的表emp，包含eid，ename姓名，sex性别，birthday生日，salary工资
插入若干条数据

课后任务：
（1）复习
（2）练习
编写脚本文件xz.sql，创建数据库xz，进入该数据库；
创建保存笔记本家族的表laptop_family，包含fid，fname，laptopCount笔记本数量
10 联想 2 20 戴尔 2 30 小米 3
创建保存笔记本数据的表laptop,包含lid，title，price，spec规格，detail商品介绍，shelftime，isOnsale是否在售，familyid所属编号；插入若干条数据
（3）预习列约束
=============================================================================================day03
-复习
ASCII/
脚本/客户端连接/服务器端
列类型
数值型 tinyint/smallint/int/bigint
Float/double/decimal/bool
日期时间型
Date/time/datetime
字符串型
Varchar/char/text
列约束主键约束——primary key
非空约束——not null

1.列约束
（1）主键约束——PRIMARY KEY
（2）非空约束——NOT NULL
（3）唯一约束——UNIQUE (unique)
声明了唯一约束的列上不能插入重复的值，允许插入NULL，而且允许插入多个NULL
（4）检查约束——CHECK
检查约束可以对插入的数据进行自定义验证
CREATE TABLE student(
TINYINT CHECK(score>0 AND score<=100
);
Mysql 不支持检查约束，会降低数据的插入速度。
（5）默认值约束——DEFAULT default
可以使用DEFAULT关键字声明默认值，有两种方式可以用默认 值
INSERT INTO family VALUES（50，’华硕’，DEFAYLT）;
INSERT INTO family(fid，fname) VALUES（60，‘荣耀’）;
（6）外键约束——FOREIGN KEY foreign key
声明了外键约束的列，取值必须在另一表的主键列上出现过。两者的列类型要保持一致。允许使用NULL或者多个NULL
FOREIGN KEY（列） REFERENCES 数据表（主键列）
FOREIGN KEY(deptId) REFERENCES dept(did)
foreign key (…)references…(…)
2.mysql中的自增列
AUTO_INCREMENT:（auto_increment）自动增长，加入一个列生命了自增列，无序手动赋值，直接赋值为NULL，会获取当前的最大值，然后加1插入。
注意：
只适用于整数型的主键列上
自增列允许手动赋值
3.简单查询
（1）查询特定的列
示例：查询所有员工的姓名、工资
SELECT ename，salary FROME emp；
select eid,ename,sex,birthday from emp
（2）查询所有的列
SELECT * FROM emp；
（3）给列起别名
示例：查询所有员工的姓名和工资，使用汉字别名
SELECT ename AS 姓名，salary AS工资 FROM emp；
（4）显示不同的记录/合并相同的记录distinct
示例：查询员工都在哪些部门
SELECT DISTINCT deptid FROM emp
（5）在查询时执行计算
示例：计算2+35+7.43.5
select ename 姓,salary*12 AS 年薪 from emp;
select ename 姓名,（salary+500）*12 AS 年薪 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;
ORDER BY 可以按照数值、日期/时间、字符串来排序，默认按照ASC升序排列
查询所有员工，结果集按照工资排序，如果工资相同，按照姓名排序
SELECT * FROM emp ORDER BY salary DESC,ename;
(7)条件查询
示例：查询出编号为5的员工所有列
SELECT * FROM emp WHERE eid=5;
比较运算符：> < >= <= = !=(不等于)

select eid as 编号,ename as 名字,sex as 性别,birthday as 生日 from emp;

练习：创建脚本文件01_tedu.sql，创建数据库tedu；进入该数据
创建部门表dept，包含（did，dname）
10 研发部20市场部30运营部40测试部 列约束
创建员工表emp，包含（eid，ename，sex，birthday，salary，deptld所属部门编号）
插入15条记录
练习：查询所有员工，结果按照性别升序排列，如果性别形态，生日降序排列
SELECT * FROM emp ORDER BY sex ASC,birthday DESC;
练习：查询姓名将King的员工编号，工资，姓名
SELECT eid,salary,ename FROM emp WHERE ename=‘King’;
SELECT * FROM emp WHERE deptid=20;
SELECT* FROM emp WHERE sex=0;
SELECT * FROM emp WHERE salary>5000;
SELECT * FROM emp WHERE salary>=5000
查询出199-1-1后出生的
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;
查询工资6000以上男员工所有列
SELECT * FROM emp WHERE salary>6000 AND sex=1;
查询出工资在5000-7000之间员工所有列
SELECT * FROM emp WHERE salary<=7000 AND salary>=5000;
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;
查询出1990年之前和1993年之后出生的员工
SELECT * FROM emp WHERE birthday NOT BETWEEN ‘1990-1-1’ AND ‘1993-12-31’;
SELECT * FROM emp WHERE birthday <‘1990-1-1’ OR birthday >‘1993-12-31’;
查询1993年出生的说有员工列
SELECT * FROM emp WHERE birthday<=‘1993-12-31’ AND birthday>=‘1993-1-1’;
SELECT * FROM emp WHERE birthday BETWEEN ‘1993-1-1’ AND ‘1993-12-31’;
查询出20号部门和30号部门的员工所有列
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);
IS NULL/IS NOT NULL
AND/OR
BETWEEN…AND…/NOT BETWEEN…AND…
IN()/NOT IN()
(8)模糊条件查询
示例：查询姓名中含有字母e的员工所有列
SELECT * FROM emp WHERE ename LIKE’%e%’;
查询姓名中以e结尾的员工所有列
SELECT * FROM emp WHERE ename LIKE’%e’;
查询姓名中倒数第二个字符为e的员工所有列
SELECT * FROM emp WHERE ename LIKE’%e_’;
%可以匹配任意多个字符 >=0
_可以匹配任意1个字符 =1
以上两个匹配必须使用LIKE 关键字
（9）分页查询
加入查询的结果集中有太多的数据，一次显示不完，可以分页显示。
需要有两个条件：当前的页码/每页的数据量
SELECT * FROM emp LIMIT start,count; limit
star:是一个数字，表示从哪一条开始读取；
star=（当前的页码-1）*每页的数据量
count：是一个数字，表示每页的数据量
注意：start和count的值必须是正式，不能是字符串形式。
假设每页显示5条记录
第1页：SELECT * FROM emp LIMIT 0,5;
第2页：SELECT * FROM emp LIMIT 5,5;
第3页：SELECT * FROM emp LIMIT 10,5;
第4页：SELECT * FROM emp LIMIT 15,5;
第5页：SELECT * FROM emp LIMIT 20,5;

课后任务：
（1）复习今天内容，删除代码保留注释，重新编写sql语句
（2）查出工资在8000以上的女员工的姓名/性别/生日，结果集按照按照工资降序排列，取前三人
SELECT ename,sex,birthday,salary from emp WHERE salary>8000 AND sex=0
ORDER BY salary DESC
LIMIT 0,3;
（3）查看学子商城资料，编写数据库（见文件）。

1.复杂查询
（1）聚合查询/分组查询
示例：查询所有员工的数量
SELECT COUNT(eid) FROM emp;
SELECT COUNT(*) FROM emp;
聚合函数
函数是一个功能体，提供若干个数据，产出结果——饺子机
COUNT（…）/SUM(…)/AVG(…)/MAX(…)/MIN(…)

练习：使用员工的姓名来查询数量
SELECT COUNT(ename) FROM emp;
练习：使用员工的部门编号来查询数量
SELECT COUNT(deptId) FROM emp;
练习：查询所有女员工的数量
SELECT COUNT() FROM emp WHERE sex=0;
练习：查询所有员工的工资总和
SELECT SUM(salary) FROM emp;
练习：查询所有男员工的平均工资
SELECT AVG(salary) FROM emp WHERE sex=1;
SELECT SUM(salary)/COUNT() FROM emp WHERE sex=1;
练习：查询出工资最高的员工
SELECT MAX(salary) FROM emp;
练习：查询出工资最低的员工
SELECT MIN(salary) FROM emp;
练习：查询出年龄最大的员工生日
SELECT MIN(birthday) FROM emp;
分组查询：只能查询分组条件和聚合
示例：查询出每个部门工资最高的员工的工资
SELECT MAX(salary) FROM emp GROUP BY deptId; #group by
SELECT deptId,MAX(salary) FROM emp GROUP BY deptId;
练习：查询出男女员工的数量，最高工资，平均工资
SELECT sex,COUNT(*),MAX(salary),AVG(salary) FROM emp GROUP BY sex;
YEAR(…) 获取日期中的年份
MONTH(…) 获取日期中的月份
练习：查询出1991年出生的员工的所有列
SELECT * FROM emp WHERE YEAR(birthday)=1991;
练习：查询3月份出生的员工所有列
SELECT * FROM emp WHERE MONTH(birthday)=3;
（2）子查询
把一个SQL语句的查询结果作为另一个sql语句的查询条件

示例：查询出所有研发部所有的员工
步骤1：查询出研发部门的部门编号
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高的员工有哪些
SElECT salary FROM emp WHERE ename=‘tom’;
SELECT * FROM emp WHERE salary>6000;
SELECT * FROM emp WHERE salary>( SElECT salary FROM emp WHERE ename=‘tom’);
练习：查询出和tom同一年出生的员工有哪些
SElECT YEAR(birthday) FROM emp WHERE ename=‘tom’;
SELECT * FROM emp WHERE YEAR(birthday)=1990;
SELECT * FROM emp WHERE YEAR(birthday)=( SElECT YEAR(birthday) FROM emp WHERE ename=‘tom’);

（3）多表查询
示例：查询出所有的员工姓名及其部门名称
SELECT ename,dname FROM emp,dept;
错误：产生笛卡尔积
解决方法：添加查询条件
SELECT ename,dname FROM emp,dept WHERE did=deptId;
查询结果是SQL-92的查询语法，无法查询出没有部门的员工，没有员工的部门。
QL-99中提出了新的夺标查询语法。

(1).内连接——和SQL-92查询结果一样
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)右外链接
SELECT ename,dname FROM emp RIGHT OUTER JOIN dept ON deptId=did;
（4）全连接
显示左侧和右侧表中所有的记录——FULL JOIN。
mysql 不支持全连接
UNION 合并相同的记录
UNION ALL 不合并相同的记录
(SELECT ename FROM emp_us)
INION
(SELECT ename FROM emp_cn)
把左外链接的结果和右外链接的结果合并
(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);