create database databasename;
drop database mysqltest;
mysql数据类型：
整数：
tinyint ,1个字节 -128—+127;
sallint,2个字节 -32768—+32767；
int,4个字节
bigint,8个字节
小数：
定点数：位置是固定的
decimal(p,s)p表示所占的总位数。s小数点的位数
浮点数：位置是不固定的
float（p,s）4字节 单精度
double(p,s) 8字节 双精度
日期/时间
date:
time:
datetime:
文本
char
varchar
text
二进制
bit
数据表的设计及ER图
使用范式检查数据表设计合理性
使用客户端创建数据表
使用命令窗口创建数据表
数据库设计基本步骤：
需求分析阶段：
概要设计阶段：
详细设计阶段：范式进行审核
代码编写阶段；
范式：规范
第一范式（1st NF）：
如果每列都是不可再分的最小数据单元（也称为最小的原子单元），则满足第一范式（1NF）。
第一范式的目标是确保每列的原子性。
第二范式（2nd NF）：
如果一个关系满足1NF，并且除了主键以外的其他列，都完全依赖于该主键，则满足第二范式（2NF）。
第二范式要求每个表只描述一件事情。
第三范式（3rd NF）
如果一个关系满足2NF，并且除了主键以外的其他列都不传递依赖于主键列，则满足第三范式（3NF）。
第三范式要求表中不存在冗余字段。
使用客户端创建数据库
步骤：
打开链接，选择数据库。
右键选择新建表。
填写表各个字段的名称、数据类型、数据类型、勾选是否允许空
保存表
使用命令窗口创建数据表
步骤：
从命令窗口登录mysql
使用create table语句创建表
create table table_name
(
col_name1 col_type [not null],
col_name1 col_type [not null],
)
use test; 切换数据库
create table table t2(
r1 int not null,
r2 varchar(5) not null);
列表添加约束：
主键约束，外键约束，检查约束，默认约束，自动增长；
约束的概念和作用：数据的正确性
主键的约束作用：保证实体的完整性
primary key,不能为空
create table users（
user_qq varchar(20) not null primary key,
user_name varchar(50) not null,
user_sex char(2) not null;
user_birthday date not null;
user_mobile char(11) not null;
外键约束：
外键约束作用：保证引用完整性；
为分数表添加外键约束：
create table scores(
user_qq varchar(20) not null
references users(user_qq),
gno int not null;
score int not null;
)
检查约束：
检查约束作用：保证域完整性
create table games(
gno int not null check(gno>0),
gname varchar(50) not null,
gtype varchar(20) not null
)
默认约束：
作用：保证域完整性
create table users(
user_sex char(2) not null default'男'，
)
自增列
作用：保证实体完整性
create table games(
gno int not null auto_increment,
gname varchar(50) not null,
gtype varchar(20) not null
)
查看数据表
查看表的基本结构，查看表的详细结构
查看表的基本结构
describe/desc table_name
describe(desc) users
field:表示字段 type：字段类型 null：是否可以为空 key:是否编制索引 default:默认值 extra:附加信息，如自增列
查看表的详细结构
show create table table_name
show create table users;
show create table users\G ;（格式化结果，排列规范）
修改数据表
修改表名
修改字段名
修改字段的数据类型
添加和删除字段
增补约束
修改表名：
alter table old_name rename to new_name
alter table games rename to qq_game;
修改字段名
alter table table_name
change old_name new_name new_type
alter table games
change gno(字段的旧的名称) game_id(字段的新的名称) varchar(20)(新的字段的类型);
修改字段数据类型
alter table table_name
modify col_name new_type
示列
alter table games
modify gno varchar(20);
添加字段语法
alter table table_name
add new_col_name new_type
示列
alter table games
add a int;
删除字段语法
alter table table_name
drop col_name
示列
alter table games
drop a;
增补约束
添加主键语法
alter table table_name
add constraint con_name
primary key(col_name)
示列
alter table users
add constraint pk_users_userqq
primary key(userqq)
添加外键约束
alter table table_name
add constraint con_name
foreign key(f_col)references m_table(m_col)
示列
alter table scores
add constranint fk_scores_games
foreign key(gno)references games(gno)
添加检查约束语法
alter table table_name
add constraint con_name
check_(exp)
示列
alter table games
add constraint ck_games_gno
check(gno>0)
添加默认值语法
alter table table_name
alter col_name set default value
示列
alter table users
alter user_sex set default'男'
alter table users
alter user_sex set default'男'
查看数据表：desc users
添加自动增长语法
alter table table_name
modify column col_name...auto_increment
示列
alter table 'games'
modify column 'gno' int not null auto_increment
primary key
删除数据表
删除无关联的数据表
删除有关联的数据表；
删除无关联的数据表
drop table[if exists] table_name1,table_name2
示列
drop table scores
drop table a,b;(同时删掉两张表)
删除有关联的数据表
先删除外键，再删主键表
先解除关联关系
alter table f_table_name drop foreign key con_name
drop table table1,table2...
插入数据
所有列都插入值
为特定列插入值
一次性插入多条记录
所有列都插入值
语法：
insert[into] table_name valuse(v1,v2...vn)
特点一列值同数，列值同序
insert into users
values
('20020101','周天','男','1982-02-01'，‘1381111222’)
insert into users
valuse('12301','周天'，‘男’，'1982-02-01'，‘1381111222’)
insert into users
values
('20020101','周天',default（关键词不用单引号）,'1982-02-01'，‘1381111222’)
为特定列插入值
insert[into] table_name(col1,col2...coln)
valuse(v1,v2...vn)
特点—指定顺序，列值对应
示列
insert into users
（users_qq,user_name,user_mobile）
values('20020106','叮当'，‘13834561278’)
insert into users（user_qq,user_mobile,user_name）
values('12302'，‘13811112222’,'叮当')
一次性插入多条记录
语法
insert[into] table_name [col1,col2...coln]
values(v11,v12..v1n),(v21,v22...v2n),(v31,v32...v3n)....
insert into users
(user_qq,user_name,user_birthday,user_mobile)
values
('20020206','周天'，‘1980-07-08’，‘13834561278’)
('20020132','李飞'，‘1983-12-05’，‘15987654321’)
修改数据：修改全部数据 ，修改特定数据
修改全部数据
update table_name
set{col_name=expression}[,...n]
示列1：把所有玩家都性别都改为男性
update users
set user_sex='男'
示列2：把所有玩家的所有游戏分数都增加100分
update scores
set score=score+100
修改特定的数据
update table_name
set{col_name=expression}[,...n]
where condition_expression
示列1：把qq为‘12302’的玩家性别都改为女性
update users
set user_sex='女'
where user_qq='12302'
删除数据：
使用delete命令删除数据 使用truncate table删除数据
使用delete命令删除数据
delete [from] table_name
[where condition_expression]
示列：删除玩家表中所有女性玩家的信息
delete from users where user_sex='女'
使用truncate table删除数据
truncate table table_name
示列：清空分数表中所有信息
truncatte table scores
查询基础
查询和提取数据的过程 select语句简介
查询和提取数据的过程
客户端和服务器交互
select col1,col2,....coln(列的名称)
from table1,table2...TABLEn
[where conditions]
[group by group_by_list]
[having conditions]
[order by order_list[asc|desc]]
简单查询
查询表的全部行和列
查询表的部分列
别名的使用
distinct关键字
limit关键字
查询表的全部行和列
示列:查询玩家表中全部的行和列
select user_qq ,user_name,user_sex,
user_birthday,user_mobile from users
select * from users
查询表的部分列
示列：从玩家表中查询玩家QQ和昵称
select user_qq,user_name from users
别名的使用
示列：从玩家表中查询玩家QQ和昵称，并显示为“玩家QQ”和“玩家昵称”
select user_qq as ‘玩家qq’,user_name as '玩家姓名' from users
select user_qq (加个空格)‘玩家qq’，user_name '玩家姓名'from users
distinct关键字
作用：消除结果集中的重复行
示列：显示参与了游戏的玩家qq，要求参与了多个游戏的玩家不重复显示qq号
select distinct user_qq from scores
limit关键字
作用：指定结果集中数据的显示范围
示列：显示玩家表中第3至第5条数据
select * from users limit 2,3
条件查询
普通条件查询 模糊查询 查询空值的运算符
普通条件查询
语法
select col_list from table_name
[where conditio_expression]
示列：查询qq号为12301的玩家信息
select * from users where user_qq='12301'
示列：查询分数大于2500分的数据
select * from scores where score>2500
普通条件查询
比较运算符
含义 符号
等于 =
不等于 <>
大于 >
大于等于 >=
小于 <
小于等于 <=
普通条件查询
示列：查询游戏编号为1且分数大于4000的分数信息
select * from scores where gno=1 and score>4000
逻辑运算符
含义 符号
并且 and
或者 or
非 not
示列：查询游戏编号为1和2的分数信息
select * from scores where gno=1 or gno=2
模糊查询
示列：查询分数在2500（含）到3000（含）的分数信息
select * from scores where score>2500 and score<=3000
select * from scores where score between 2500 and 3000
示列：查询分数不在2500（含）到3000（含）的分数信息
select * from scores where score not between 2500 and 3000
示列：查询1987年1月1日到1992年7月31日出生的玩家
select * from users where user_birthday between '1987-01-01'and '1992-07-31'
通配符 解释 列子
'_' 一个字符 Branch Like‘L_’
% 任意长度 Route_Code Like'AMS-%'
[] 指定范围 Airbusno Like'AB0[1-5]'
[^] 不在括号内 Airbusno Like‘ABO’[^1-5]
示列：查询所有姓孙的玩家信息
select * from users
where user_name like '孙%'
示例:查询所有非姓孙的玩家信息
select * from users
where user_name not like '孙%'
查询空值的运算符
示列：查询生日为null的玩家信息
select * from users where user_birthday is null
示例：查询生日为null的玩家信息
select * from users
where user_birthday is not null
对查询结果排序
对指定列进行排序 多列排序
对指定列进行排序
单列排序要素
排序依据
排序方式
语法
select col_list from table_name
order by order_by_list[asc|desc]
示例：查询分数表中编号为1的所有分数信息，并按照分数升序排序
select * from scores
where gno=1
order by score asc
示例：查询分数表中编号为1的所有分数信息，并按照分数降序排序
select * from scores
where gno=1
order by score desc
多列排序
多列排序要点
排序依据
排序方式
优先级
示例：查询分数表所有信息，并按照游戏编号的升序和分数的降序进行排序
select * from scores
order by gno asc,score desc
汇总和分组数据
聚合函数 在结果集内分组 筛选分组结果 select语句的执行顺序
聚合函数 支持的数据类型 描述
sum() 数字 对指定列中的所有非空值求总和
avg() 数字 对指定列中的所有非空值求平均值
min() 数字、字符、datetime 返回指定列中的最小数字、最早的日期或者最小的字符串
max() 数字、字符、datetime 返回指定列中的最大数字、最近的日期或者最大的字符串
count() 任意基于行的数据类型 统计结果集合中的全部记录行的数量
示例：查询玩家表中一共有多少名玩家信息
select count(user_qq)from users
select count(*)from users
示例：查询QQ号是12301的玩家游戏的总分数
select sum(score)as '总分数' from scores
where user_qq='12301'
示例：查询QQ号是12302玩家的平均数
select avg(score)as'平均数'from scores
where user_qq='12302'
示例：查询游戏编号是1的游戏的最高分
select max(score)as'最高分数'
from scores where gno=1
示例：查询QQ号是12302玩家的总分、平均分和最高分
select
sum（score）as'总分'
avg (score) as'平均分'
max（score）as'最高分'
from scores
where user_qq='12302'
在结果集内分组
使用group by 分组
示例：查询每个玩家的总分数、平均分数、最高分数
select
sum（score）as'总分'
avg (score) as'平均分'
max（score）as'最高分'
from scores
group by user_qq
示例：查询每个玩家的平均分数，并显示玩家的qq号和平均分数
select user_qq,avg(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,
avg(score)as'平均分'，
sum(score)as'总分数'
from scores
group by user_qq
order by avg(score)desc
select 语句的执行顺序。
from 子句指定数据源。
where子句基于指定的条件对记录进行筛选。
group by 子句将数据划分为你多个分组。
使用聚合函数进行计算
使用having子句筛选分组。
使用order by子句对结果集进行排序。
连接查询
from子句进行多表查询 内连接 外连接
from子句进行多表查询
多表连接
示例:查询分数信息，显示玩家昵称、游戏名称和分数
select user_name as '昵称'
games as '游戏名称',
score as '分数'
from users,games,scores
where users.user_qq=scores.user_qq
and games,gno=scores.gno
内连接
连接查询分为内连接和外连接两种
内连接特点
相连接的两张表地位平等
如果一张表中在另一张表中不存在对应数据，则不做连接
select user_name ,score
from users,scores
where users.user_qq=scores.user_qq
from子句后面直接出现多个表名,这种连接方式即属于内连接，是隐式内连接。
显示内连接格式
select col_list from table1[inner]join table2
on table1.col=table2.col
示例：查询分数信息，显示玩家昵称、游戏名称和分数
select user_name as'昵称'，
G_name as '游戏名称',
score as '分数'
from games inner join scores
on games.gno=scores.gno
inner join users
on scores.user_qq=users.user_qq
select user_name,gname,score
from games inner join scores
on games.gno=scores.gno
inner join users
on scores.user_qq=users.user_qq
示例：查询每个玩家的昵称、总分和平均分
select user_name as'昵称',
sum(scores)as'总分',
avg(scores)as'平均分'
from users u
inner join scores s
on s.user_qq=u.user_qq
group by u.user_qq,user_name
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补齐
左外连接时左表是基础表，右外连接时右表是基础表
语句中先出现的表为“左表”，“后出现的表为右表”
外连接格式
select col_list
from table1 left|right[outer] join table2
on table1.col=table2.col
示例：查询所有玩家关于5号游戏的分数信息
select user_name as '昵称'，
gno as ‘游戏编号’,score as '分数'
from users u left join scores s
on u.user_qq=s.user_qq
and s.gno=5
select user_name,gno,score
from users left join scores
on users.user_qq=scores.user_qq
and gno=5
子查询
使用in关键字的子查询 使用exists关键字的子查询
使用in关键字的子查询
问题：查询游戏类型是“棋牌类”的游戏的分数信息
游戏分数表中并未包含游戏类型信息
思路一：采用连接查询
思路二：分两步进行，首先找到所有"棋牌类"游戏的编号，再以这一组编号为查询依据完成查询
select * from games where gtype='棋牌类'
select gno（棋牌游戏的编号） from games where gtype='棋牌类'
select * from scores where gno in
(select gno from games where gtype='棋牌类')
注意：子查询一般不写成select * from...
示例：查询没有参与5号游戏的玩家QQ
select user_qq from users
where user_qq not in
(select user_qq from scores where gno=5)
使用exists关键字的子查询
示例：如果存在昵称为"孙悟空"，则查询分数表中数据
select * from scores
where exists
(select * from users where user_name='孙悟空')
联合查询
使用联合查询
联合查询语法格式
select_statement
union[all]select_statement
[union[all]select_statement]
[,...n]
作用与特点
可以把多条查询语句所产生的结果集纵向连接为一体
有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
第二种方法
select * from users where user_sex='女'or user_birthday is null
示例：查询QQ号是“12301”的玩家所有分数并计算出总分数和平均分数，并显示到同一结果集中
select user_qq,gno,score 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'
日期与时间函数
函数的概念：按指定格式输入参数，返回正确结果的运算单元。
返回当前日期
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 now() as '此时此刻'，sysdate() as '此时此刻2'
获取月份
month(date)返回参数指定时间的月份
monthname(date)返回参数指定时间的月份名字
select month('2015-07-22')
select monthname('2015-07-22')指定的英文名字
获取星期
dayname(date)
dayofweek(date)指定返回的那一天在这个星期处于第几天
weekday(date)指定返回的那一天在这个星期处于第几天
week(date)指定返回的这一年的第几个星期 从0开始算
weekofyear(date)指定返回的这一年的第几个星期 从1开始算
select dayname('2015-09-22')指定的星期英文名字
select dayofweek('2015-09-22')指定那一行的第几天
select weejday('2015-09-20')指定那一个星期的第几天（从0-6天数）
select week('2015-09-22'),weekofyear('2015-09-22')
获取天数
dayofyear(date)参数指定日期返回的这一年的第几天
dayofmonth(date)参数指定日期返回的这一月的第几天
select dayofyear('2015-01-01'),dayofmonth('2015-01-01')
获取年份、季度、小时、分钟、秒钟
year（date）
quarter(date)
hour(time)
minute(time)
second(time)
select year('2015-09-22')
select quarter('2015-09-22')
数学函数
绝对值函数
ABS（x）
select abs(-8)8
select ass(7)7
符号函数（判断一个数值）
sign（x）
select sign(7)1
select sign(-7)-1
select sign(0)0
获取随机数函数
rand()不带参数
rand(x)带有参数称为种子
select rand()0.61342332 0-1半开半闭区间的
select rand(3)0.9356565 产生一个固定数
获取整数的函数
ceil（x）/ceiling(x)
floor(x)
select ceil(3.5) 大于3.5的最小的整数
select floor(3.5)不大于3.5的最大整数
四舍五入函数
round（x）
round（x,y）
truncate(x,y)
select round(3.4)
select round(3.14,1)保留小数点后面的位数，和可以保留负数
select truncate(3.15,1)保留一位小数，3.1后面的数字都会截取调
求余数函数
mod(x,y)
select mod(31,8)
幂运算函数
pow(x,y)/power(x,y)作用是一样的，求x的y次幂
exp(x)求自然对数的x次幂,2.7多数字以它为底的x次幂
sqrt(x)求某一个数的平方根,负数是没有结果的
select sqrt(2)1.414
角度弧度互换函数
raduans(x)角度变化成弧度
degrees(x)弧度变化成角度
select raduans(180)3.1415926
select degrees(3.14)179.908747671度
圆周率函数
pi()
三角函数
sin(x)正弦值
asin(x)反正弦值
cos(x)余弦值
acos(x)反余弦值
tan(x)正切
atan(x)反正切
cot(x)余切
字符串函数
计算字符数和字符串长度的函数(都是以字符串作为参数)
char_length(s)计算这个字符串有几个字符（字符的个数）
lenghth(s)计算这个字符串在内存当中占有几个字节（字节的个数）
select char_length(‘abc’)运行结果是3
select char_length(‘你好吗’)运行结果是3
select length('abc')3
select length(‘你好吗’)9 每个汉字占有3个字节
合并字符数函数
concat(s1,s2...)
concat_ws(x,s1,s2...)可以指定的连接符
select concat('你好吗',‘abc’,'world')
select concat('你好吗',null,'world')运行结果也为空
select concat_ws('#','你好吗','abc','world')
替换字符串函数
insert(s1,x,len,s2)s1原始字符串，从x开始，len替换字符串的长度,s2替换的字符
replace(s,s1,s2)s原始的字符串中找到是s1,把s1用s2去替换，无论多少个
select insert('abcdef',2,3,'##')运行结果a##ef
select replace('abcdcefab','ab','%%') 运行结果%%cdef%%
截取字符串的函数
截取长度不够了全部显示出来
left(s,n)从左边截取n位数，截取s参数
right(s,n)
select left('abcdefab',3)运行结果abc
select right('abcdefab',3)运行结果fab
重复生成字符串函数
repeat(s,n)把s指定的字符串重复显示n遍
select repeat('abc',3)运行结果abcabcabc
大小写转换函数
lower(x)/lcase(x)字母转换成小写
upper(x)/ucase(x)字母转换成大写
select lcase('ABCd')运行结果abcd同理大写
填充字符串的函数
lpad(s1,len,s2)左边开始填充字符串，长度以第二个参数为标准,以s2参数开始填充
rpad(s1,len,s2)右边开始填充字符串
select lpad('ABCd',10,'#')运行结果######ABCd
select rpad('ABCd',10,'#')运行结果ABCd######
删除空格函数
ltrim(s)删除左边的空格/rtrim(s)删除右边的空格
trim(s)删除两边的空格(中间的去不掉)
select Rtrim(' ABCd ')
删除指定字符串
trim(s1 from s)
select trim('a'from 'abcade')运行结果bcade(只能删除靠着边的)
获取子符串(从s字符串当中从n这个位置截取len这个长度)两个作用相同的
substring(s,n,len)
mid(s,n,len)
select substring('abcade',3,2)运行结果ca
select mid('abcade',3,2)运行结果ca
返回指定位置字符串函数
elt(n,s1,s2)第一个参数指定的要返回哪一个字符串，后面的字符串是各种字符串的排列
select elt(2,'abc','def','mysql') 运行结果def 从后面的三个字符串中找到第二个字符串
返回指定字符串位置
field(s,s1,s2...)s以第一个参数作为一个标准找以它匹配的字符串恰好与它相同位置返回过来
select field('hi','hi','ho','he','hu')运行结果1
select field('ho','hi','ho','he','hu')运行结果2
系统函数
获取mysql版本号的函数
version()
select version()
查看当前用户的连接数
connection_id()
select connection_id 运行结果19
查看当前所用的数据库函数
datebase()
schema()
select database() 运行结果：当前所在的数据库
获取用户名的函数
user()
current_user()
system_user()
session_user()
select user(),current_user(),system_user(),session_user()运行结果：root(用户名)@localhost（所登录的主机）