目录
一、数据库的创建
CREATE DABABASE MySQLTest
二、删除数据库
DROP DATABASE MySQLTest
三、MySQL常用数据类型
整数
tinyint --> 一字节 -128 ~ 127
smallint --> 两字节 -32768 ~ 32767
int --> 四字节
bigint --> 八字节
定点数
（位置固定的）
优点：数据精度会得到保证
decimal(p,s) --> p+2字节
p-->数字所占的总位数 s-->小数点部分的位置
浮点数
（小数点位置不固定）
优点：有点表示范围更大
缺点：不能保证精度
float(p,s) -->4字节
double(p,s) -->8字节
日期
data 某一个日期
time 一天的某一个点数
datatime 连日期带时间
文本
char 固定长度的文本
varchar
text 存储大文本，不可以当作查询条件
二进制
bit
四、创建数据表
数据表的设计及ER图
数据库设计基本步骤
需求分析阶段:分析客户的业务和数据处理需求
概要设计阶段:设计数据库的E-R模型图，确认需求信息的正确和完整
详细设计阶段:将E-R图转换为多张表，进行逻辑设计，并应用数据库设计的三大范式进行审核
代码编写阶段:选择具体数据库进行物理实现，并编写代码实现前端应用
E-R（Entity-Relationship）实体关系图
范式
设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称
为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
Dr E.F.codd最初定义了规范化的几个级别，范式用以定义具有最小冗余的表结构。
第一范式（1st NF）
如果每列都是不可再分的最小数据单元(也称为最小的原子单元)则满足第一范式(1NF)。
第一范式的目标是确保每列的原子性。
第二范式（2nd NF）
如果一一个关系满足1NF,并且除了主键以外的其他列，都完全依赖于该主键，则满足第二范式(2NF)。
第二范式要求每个表只描述一件事情。
第三范式（3rd NF）
如果一个关系满足2NF，并且除了主键以外的其他列都不传递依赖于主键列，则满足第三范式(3NF)。
第三范式要求表中不存在冗余字段。
创建表
create table t2(
id int not null key,
name varchar(20) not null
);
主键约束
作用：保证实体完整性
create table t3(
id int not null primary key,
name varchar(10) not null,
birthday date not null,
phone char(11) not null
);外键约束
作用：保证引用完整性
create table t4(
id int not null references t2(id),
achievement int not null
)检查约束
作用：保证域完整性
create table t5(
id int not null check(id > 0),
name varchar(10) not null
)默认约束
作用：保证域完整性
自增列
作用：保证实体完整性
五、查看数据表
查看表基本结构
语法
DESCRIBE/DESC 表名
| Field | 字段名 |
| Type | 字段类型 |
| NULL | 是否可以为空 |
| Key | 是否编制索引 |
| Default | 默认值 |
| Extra | 附加信息：如自增列 |
DESC t1 
查看表的详细结构
SHOW CREATE TABLE 表命 \G(\G格式化结果)
六、修改数据表
修改表名
语法
ALTER TABLE 原表命 RENAME TO 新表名
ALTER TABLE t3 RENAME TO user;修改字段名
语法
ALTER TABLE 表明 CHANGE 旧的字段名 新名称 新类型
ALTER TABLE user CHANGE name user_name varchar(20);修改字段的数据类型
ALTER TABLE 表名 MODIFY 字段名 修改类型
ALTER TABLE user MODIFY user_name char(20);添加和删除字段
添加字段
ALTER TABLE 表名 ADD 新字段名 类型
ALTER TABLE user ADD user_password varchar(20);删除字段
ALTER TABLE 表名 DROP 字段名
ALTER TABLE user DROP password;增补约束
添加主键
ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY(列名)
约束名命名规则：约束类型_表名_列名
添加外键
ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOPELGN KEY
(列名) REFERENCES 引用表（应用列名）
添加检查约束
ALTER TABLE 表名 ADD CONSTRAINF 约束名 CHECK (表达式)
添加默认值
ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值
添加自增列
ALTER TABLE 表名 MODIFY COLUMN 列名 类型 是否为空 AUTO_INCREMENT
七、删除数据表
删除无关联数据表
DROP TABLE [IF EXISTS](存在就删除不存在就不做改变) 表名
DROP TABLE if exists t5;删除有关联数据表
解除关联关系
ALTER TABLE 从表名称 DROP 约束类型 名称
删除表
DROP TABLE 表名
八、插入数据
所有列都插入值
语法
INSERT [INTO] 表名 VALUES(V1,V2,DEFAULT......)
特点——列值同数，列值同序
insert into user values(1,'张三','1999-10-1','176099999','1234565');为特定列插入值
语法
INSERT [INTO] 表名 (列名1,列名2) VALUES (V1,V2)
特点——指定顺序，列值对应
insert into user (id,user_name,birthday,user_password)
values (3,'王五','1999-08-15','12334');一次性插入多条记录
INSERT TABLE 表名 [(列名1,列名2)] VALUES (V11,V12),(V21,V22)....
九、修改数据
修改全部数据
UPDATE 表名 SET {列名=表达式}[n]
修改特定数据
UPDATE 表名 SET {列名=表达式}[n] WHERE 条件表达式
十、删除数据
使用DELETE删除数据
DELETE [FROM] 表名 [WHERE 表达式]
使用TRUNCATE TABLE删除数据
TRUNCATE TABLE 表名
清空表中所有的的信息
十一、查询
简单查询
查询表的全部行和列
select * from user查询表的部分列
select user_name,user_password from user别名的使用
select user_name AS '用户',user_password AS '密码' from userselect user_name 用户,user_password 密码 from userDISTINCT关键字
作用：消除结果集中的重复行
select distinct user_name from userLIMIT关键字
作用：指定结果集中数据的显示范围
select * from user limit 2,42-->第三行
4-->第五行
表示3~5行数据
指定一个参数表示前n行的数据
条件查询
普通条件查询
select * from user
where user_name = "张三"| 含义 | 符号 |
| 等于 | = |
| 不等于 | <> |
| 大于 | > |
| 小于 | < |
| 大于等于 | >= |
| 小于等于 | <= |
| 含义 | 符号 |
| 并且 | AND |
| 或者 | OR |
| 非 | NOT |
模糊查询
select * from user
where birthday BETWEEN "2022-01-01" AND "2023-01-01"表示 2022年1月1日~2023年1月1日之间的数据
所设置边界也会包含到数据中去
select * from user
where birthday NOT BETWEEN "2022-01-01" AND "2023-01-01"表示不在 2022年1月1日~2023年1月1日之间的数据
| 通配符 | 解释 |
| ‘_’ | 一个字符 |
| % | 任意长度 |
| [] | 指定范围内 |
| [^] | 不在括号中 |
查询姓王的所有信息
select * from user where user_name like '王%'查询非姓王的所有信息
select * from user where user_name not like '王%'查询空值的运算符
查询电话为空的信息
select * from user where phone is null查询电话不为空的信息
select * from user where phone is not null对查询结果排序
单列排序要素
ASC-->升序（默认方式）
DESC-->降序
查询姓名为“王五”的数据按照密码大小降序
select * from user where user_name = "王五" ORDER BY user_password desc多列排序
排列依据
排列方式
优先级
order by
查询所有用户信息，按照生日的的升序和密码的降序进行排列
select * from user
order by birthday asc,user_password desc十二、汇总和分组数据
聚合函数
作用：对多条数据做统计功能
| 聚合函数 | 支持的数据类型 | 描述 |
| sum() | 数字 | 对指定列中的所有非空值求总和 |
| avg() | 数字 | 对指定列中的所有非空值求平均值 |
| min() | 数字、字符、datetime | 返回指定列中的最小数字，最早的日期或者最小的字符串 |
| man() | 数字、字符、datetime | 返回指定列中的最大数字，最近的日期或者最大的字符串 |
| count() | 任意基于行的数据类型 | 统计结果集合中全部记录行的数量 |
查询一共有多少条用户信息
*代表有的字段
select count(*) from user
求12303玩家的总分、平均分、最高分
select sum(score) as "总分",
avg(score) as "平均分",
max(score) as "最高分"
from scores
where user_qq = '12302'在结果集内分组
GROUP BY分组
查询每个玩家的总分数、平均分、最高分
select user_qq,
sum(score) as "总分",
avg(score) as "平均分",
max(score) as "最高分"
from scores
group by user_qq 
筛选分组结果
在使用GROUP BY子句时，可用HAVING子句为分组统计进一步设置统计条件，HAVING子句与
GROUP BY子句的关系相当于WHERE子句与SELECT子句之间的关系
与WHERE子句的区别是，在HAVING子句中是以聚合函数函数的统计结果为筛选条件。
查询平均分大于4000的玩家qq号、总分数、平均分
select user_qq,
sum(score) as "总分",
avg(score) as "平均分"
from scores
group by user_qq
having avg(score) > 4000 
查询所有用户的平均分、总分、并按平均分倒序排列
select user_qq,
sum(score) as "总分",
avg(score) as "平均分"
from scores
group by user_qq
order by avg(score) desc 
SELECT语句的执行顺序
from子句指定数据源。
where子句基于指定的条件对记录进行筛选。
group by子句将数据划分为多个分组。
使用聚合函数进行计算。
使用having子句筛选分组。
使用order by子句对结果集进行排序。
十三、连接查询
FROM子句进行多表查询
查询分数信息，显示玩家的昵称、游戏名称和分数
select user_name as "昵称",gname as "游戏名称",score as "分数"
from users,games,scores
where users.user_qq = scores.user_qq
and games.gno = scores.gno 
内连接
特点：
相连接的两张表地位平等
如果一张表中在另一张表中不存在对应数据，则不做连接
FROM子句后面直接出现多个表名，这种连接方式即属于内连接，是隐式内连接。
查询分数信息，显示玩家的昵称、游戏名称和分数
select user_name as "昵称",gname as "游戏名称",score as "分数"
from users inner join scores
on users.user_qq = scores.user_qq
inner join games
on scores.gno = games.gno 
查询每个玩家的昵称、总分和平均分
select user_name as "昵称",sum(score) as "总分",avg(score) as "平均分"
from users inner join scores
on users.user_qq = scores.user_qq
group by users.user_qq,users.user_name 
查询平均分数大于3500的分数信息，显示玩家昵称、总分数、平均分数，并按平均分数降序排列
select user_name as "昵称",sum(score) as "总分",avg(score) as "平均分"
from users inner join scores
on users.user_qq = scores.user_qq
group by users.user_qq,users.user_name
having avg(score) > 3500
order by avg(score) desc 
外连接
特点
做连接的两个表地位不平等，其中有一张是基础表
基础表中的每条数据必须出现，即使另一张表中没有数据与之匹配，也要用NULL补齐
左外连接时左表是基础表，右外连接时右表是基础表
语句中先出现的表为“左表”，后出现的表为“右表”
查询所有玩家关于5号游戏的分数信息
select user_name as "昵称",gno as "游戏编号",score as "分数"
from users left join scores
on users.user_qq = scores.user_qq
and gno = 5
十四、子查询
IN关键字的子查询
查询游戏类型是“棋牌”的游戏分数信息
select * from scores where gno in
(select gno from games where gtype = "棋牌") 
注意：子查询一般不写成select *
查询没有参与5号游戏的玩家QQ
select user_qq from users where user_qq in
(select user_qq from scores where gno <> 5) 
EXISTS关键字的子查询
如果存在昵称“孙悟空”，则查询分数表中数据
select * from scores where exists
(select user_name from users where user_name = "孙悟空")
十五、联合查询
作用与特点
可以把多条查询语句所产生的结果集纵向连接为一体
有ALL关键字可以显示全部数据(即重复的也显示出来)
列的数量与类型都要兼容
select user_name from users
union
select gname from games
查询玩家表中所有女性玩家和生日为空的玩家
select * from users where user_sex = "女"
union
select * from users where user_birthday is null 
查询QQ号是“12301”的玩家所有分数并计算出总分数和平均分，并显示到同一结果集中
select * from scores where user_qq = "12301"
union all
select "总分"," " ,sum(score) from scores where user_qq = "12301"
union all
select "平均分"," " ,avg(score) from scores where user_qq = "12301"
十六、MySQL函数
日期和时间函数
返回当前日期
CURDATE()
CURRENT_DATE()
CURDATE()+0 可以将当前日期值转换为数值型
select curdate(),current_date(),curdate()+0 
返回当前时间
CURTIME()
CURRENT_TIME()
CURTIME() + 0 可以将当前时间值转换为数值型
select curtime(),current_time(),curtime()+0 
返回当前日期和时间
CURRENT_TIMESTAMP()
LOCALTIME()
NOW()
SYSDATE()
select current_timestamp() as "当前时间",localtime() as "当前时间",
now() as "当前时间",sysdate() as "当前时间"
获取月份
MONTH(DATE)
MONTHNAME(DATE)
SELECT MONTH(NOW()),MONTHNAME(SYSDATE()) 
获取星期
DAYNAME(DATE)
DAYOFWEEK(DATE)
WEEKDAY(DATE)
WEEK(DATE)
WEELOFYEAR(DATE)
SELECT DAYNAME(NOW()),DAYOFWEEK(SYSDATE()),WEEKDAY(LOCALTIME()),
WEEK("2015-1-1"),WEEKOFYEAR("2015-1-1")
获取天数
DAYOFYEAR(DATE)
DAYOFMONTH(DATE)
SELECT DAYOFYEAR(NOW()) AS "年天数",DAYOFMONTH(NOW()) AS "月天数"
获取年份、季度、小时、分钟、秒钟
YEAR(DATE)
QUARTER(DATE)
HOUR(TIME)
MINUTE(TIME)
SECOND(TIME)
SELECT YEAR(NOW()) AS "年",QUARTER(NOW()) AS "季度",
HOUR(NOW()) AS "时",MINUTE(NOW()) AS "分",SECOND(NOW()) AS "秒" 
数学函数
绝对值函数
ABS(X)
SELECT ABS(-9) 
符号函数
判断一个数字时正数、负数还是0；若是正数返回值为1，若是负数返回值为-1，若是0返回值为0
SIGN(X)
SELECT SIGN(9),SIGN(-9),SIGN(0) 
获取随机数函数
RAND()
RAND(X)
SELECT RAND(),RAND(3) 
获取整数的函数
CEIL(X)/CEILING(X) 不小于当前参数最小的那个数
FLOOR(X) 不大于当前参数最大的那个数
SELECT CEIL(3.5),CEILING(3.5),FLOOR(3.5) 
四舍五入函数
ROUND(X)
ROUND(X,Y)
TRUNCATE(X,Y)
SELECT ROUND(3.5),ROUND(3.4),ROUND(3.14,1),ROUND(235.234,-2),TRUNCATE(3.123,2)
求余函数
MOD(X,Y)
以X为被除数，以Y为除数
SELECT MOD(31,6) 
幂运算
POW(X,Y)/POWER(X,Y)
X的Y次幂
EXP(X)自然对数的某次幂
SQRT(X)某一个数的平方根
SELECT POW(2,3),POWER(3,2),SQRT(16),SQRT(-16) 
角度弧度互换函数
RADIANS(X) 角度变换成弧度
DEGREES(X) 弧度变换成角度
SELECT RADIANS(180),DEGREES(PI()) 
圆周率
PI()
三角函数
SIN(X)
ASIN(X)
COS(X)
ACOS(X)
TAN(X)
ATAN(X)
COT(X)
字符串函数
计算字符数和字符串长度
CHAR_LENTH(S)计算字符串中有多少个字符
LENGTH(S)计算字符串在内存中占多少个字节
SELECT CHAR_LENGTH("ABCD"),CHAR_LENGTH("你好"),LENGTH("你好") 
合并字符串函数
CONCAT(S1,S2)
CONCAT(X,S1,S2)
SELECT CONCAT("HELLO",",","WORD"),CONCAT("HELL",NULL,"WORD")
,CONCAT_WS("-","HELLO","WORD") 
替换字符串
INSERT(S1,X,LEN,S2)
REPLACE(S,S1,S2)
SELECT INSERT("ABCDEFG",2,5,"bcdef"),REPLACE("ABCDEFGCD","CD","rrrrrr") 
截取字符串函数
LEFT(S,N)
RIGHT(S,N)
SELECT LEFT("ABCDEFAB",4),RIGHT("ABCDEFAB",4) 
重复生成字符串函数
REPEAT(S,N)
SELECT REPEAT("ABCD",3) 
大小写转换函数
LOWER(X)/LCASE(X) 变成小写
UPPER(X)/UCASE(X) 变成大写
SELECT LOWER("AAAA"),LCASE("AAAA"),UPPER("aaa"),UCASE("aaa")
填充字符串函数
LPAD(S1,LEN,S2)
RPAD(S1,LEN,S2)
SELECT LPAD("ABCD",11,"123"),RPAD("ABCD",11,"123") 
删除空白函数
LTRIM(S)/RTRIM(S)
TRIM(S)
SELECT LTRIM(" ABCD ") AS "TEST1",RTRIM(" ABCD ") AS "TEST2"
,TRIM(" ABCD ") AS "TEST3" 
删除指定字符串
TRIM(S1 FROM S)
SELECT TRIM("A" FROM "ABCD"),TRIM("B" FROM "ABCD") 
获取子字符串
SUBSTRING(S,N,LENG)
MID(S,N,LEN)
SELECT SUBSTRING("ABCDEF",2,3),MID("ABCDEF",2,5) 
返回指定位置字符串函数
ELT(N,S1,S2)
SELECT ELT(3,"ABC","DEF","123","456") 
返回指定字符串位置
FIELD(S,S1,S2,....)
SELECT FIELD("123","ABC","DEF","123","456") 
系统函数
获取MySQL版本号
VERSION()
查看当前用户的连接数
CONNECTION_ID()
查看当前所用数据库函数
DATABASE()
SCHEMA()
获取用户名的函数
USER()
CURRENT_USER()
SYSTEM_USER()
SESSION_USER()
SELECT VERSION(),CONNECTION_ID(),DATABASE(),SCHEMA(),USER(),CURRENT_USER()
,SYSTEM_USER(),SESSION_USER()
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
