使用SQL命令创建数据库及表
数据库和表的概念思考: 你我贷网站上战士的标信息是存放在哪的?
• 数据库的概念
存储数据的容器(对比仓库去理解)
• 表的概念
真正的存储单元
• 数据库与表的关系
一对多的关系,一个库里通常维护了多个表(货架)
SQL
• 概念
• SQL: 结构化查询语言, Structured Query Language
• SQL是用于访问和处理数据库的标准计算机语言
• SQL能做什么?
• 创建数据库、表等数据库对象
• 数据库记录的增、删、改、查
创建数据库
语法:
create database 数据库名称;(utf-8 表示支持中文数据的存储)
示例 : 创建你我贷的数据库
create database future;
创建表• 思考: 如何将下面excel表的数据保存到数据库中的某个表里去
• 分析: 为了将数据放进表里, 我们需要创建表, 必须跟excel的数据的结构是一样的
• 语法
create table 表名 (字段名1 类型1, 字段名2 类型2 ... 字段名n 类型n) DEFAULT CHARSET=utf-8;
• 解释
• 字段名: 用来表示表的一列
• 字段类型: 用来声明每一个字段存储数据的类型, 因为在MySQL中,数据是有类型的(整数, 小数,字符,时间)
• 字符集: utf8, 用来指定表支持中文数据存储
• 示例: 创建上面student表的SQL
create table student(
id int not null PRIMARY key auto_increment,
name varchar(12),
sex varchar(12),
class_id int(11),
address varchar(20),
create_data datetime,
update_data datetime
) DEFAULT CHARSET=utf-8;
常见字段类型每种字段都有自己的类型, 如整数型, 字符型, 时间类型, 日期类型, 浮点型等. 下面介绍常用的数据类型
• 整型===整数
• tinyint: 占一个字节, 取值范围为-128~127
• int: 占四个字节 -2147483648~ 2147483648
• 字符串型
• char(n): n个字符, 最多255个字符, 固定长度
• varchar(n): n个字符, 最多65535个字符, 可变长度
• 时间和日期
• date: 日期, 格式(yyyy-mm-dd, 例如: 2019-12-30)
• time: 时间, 格式(hh:mm:ss, 例如: 18:40:37)
• datetime: 日期时间, 格式(yyyy-mm-dd hh:mm:ss, 例如: 2019-12-30 18:40:37)
• 浮点型(小数)
• float(m,d) : 单精度浮点型, 32bit, m代表总位数, d小数位, 如99.99声明类型为: float(4,2)
• double(m,d) : 双精度浮点型, 64bit, m代表总位数, d小数位
• decimal(m,d) : m代表总位数, d小数位, decimal数据类型用于精度非常高的计算中, 比如语句
decimal(7,3) 规定了存储的值不会超过7位数字, 并且小数点后不超过3位(一般用于金融行业)
• 区别: 双精度类型能表示的小数的精确度更高
主键• 主键是什么? 它有什么特征
• 主键字段存储的值不能重复, 因此主键可以唯一表示一行数据
• 一个表只能有一个主键
• 主键是非必需的, 也就是说一个表可以不声明主键字段
• 语法
• primary key;
思考: 上面student表哪个字段适合做主键?
• 主键值生成策略
• 自动增长 auto_increment
总结• 创建表声明字段的时候, 字段之间以什么符号分隔? 符号用中文的可以吗?
• 建表声明字段的时候, 最后一个字段后面需要注意什么?
• 主键的特征是什么?
• 如果表需要支持中文数据存储, 需要怎么做?
• 创建了表, 发现表没有出现, 可以刷新一下表
• Navicat里SQL写完丢失了, 其实是因为没保存
• 学会分析错误: Err
• 以上关于创建表的SQL语句能看懂就ok, 真实工作中不要求去创建
掌握数据库操作之增删改
插入表数据(insert)插入一条数据, 对所有字段都赋值
• 语法
• insert into <表名> values (值A, 值B, 值C … , 值F)
• 示例
• insert into member values(1, ‘tommy’, ‘123456’, ‘13500000001’, 1, 5.00, sysdate());
插入一条数据, 且只对一部分字段赋值
• 语法
• insert into <表名>(字段A, 字段B, 字段C) values (值A, 值B, 值C);
• 示例
• insert into member(RegName, Pwd, MobilePhone, Type, LeaveAmount)
values(‘nick’, ‘123456’, ‘13500000002’, 1, 20.00);
总结
如果插入数据时, 只对部分字段进行初始化, 则需要声明要插入的字段.
修改表数据(insert)修改一个表里的所有数据
• 语法
• update 表名 set 字段名1 = 新值1, 字段名2 = 新值2 ...
• 示例
• 将用户(member)表里所有用户的可用余额修改为100000.00
• update member set LeaveAmount = 100000.00;
修改一个表里满足某些条件的那一部分数据
• 语法
• update 表名 set 字段名1 = 新值1, 字段名2 = 新值2 .. where 条件表达式1 and(or) 条件表达式2 ...;
• 示例
• 将用户(member)表里RegName为’nick’的用户可用余额修改为1000000.00
• update member set LeaveAmount = 1000000.00 where RegName = ‘nick’;
• 总结:
• 如果要更新一个表里满足某个条件的数据, 需要用到关键字’where’来进行过滤找出那一部分数据. 然后针对这一部分数据做更新操作
删除数据库或者表(drop)删除数据库:
• 语法
• drop database 数据库名称;
• 示例
• 删除数据库 testdb à drop database testdb;
删除数据表:
• 语法
• drop table 表名;
• 示例
• 删除表 testable à drop table testable;
• 总结
• drop database A; 会把数据库A 删掉, 因此库里的所有表和数据都会一并删除
• drop table B; 会把表B删掉, 因此B表的额数据也删掉了
• drop作为一种删除命令, 它不仅会删数据结构(库/表), 而且会删数据
简单点讲: drop 删结构企鹅删数据, 所以这两个命令要慎用
删除表数据(delete)删除表里的所有数据
• 语法
• delete from 表名;
• 示例
• 删除会员表里的所有数据 delete from member;
删除一个表里的满足某些条件的数据
• 语法
• delete from 表名 where 条件1 and(or) 条件2 ... ;
• 示例
• 删除会员表里名字为’tommy’的数据
• delete from member where RegName = ‘tommy’;
总结• 插入数据时, 只初始化部分字段与初始化所有字段时SQL的不同点体现在哪里? 在表后面加不加字段名
• 赋值时, 对于varchar类型的字段, 赋值时应注意什么?
• 字段声明了长度, 赋值的时候应该注意什么? 插入数据的长度超长了会有问题吗?
• 插入一条数据和多条数据的语法区别是? 多条数据时, 中间以什么符号做分割? 每一条数据之间用逗号分隔
掌握数据库操作之查询
单表查询不加条件的查询:
• 查询特定字段, 语法:
• select <字段1, 字段2, …> from < 表名>;
• 示例
• 查询会员表里所有用户的手机号码和可用余额
• select MobilePhone,LeaveAmount from member;
查询所有字段:
• 语法
• select * from <表名>;
• 示例
• 查询会员表的所有用户信息
• select * from member;
按条件进行查询:
• 多个条件需要同时成立, 语法
• select … from <表名> where 表达式A and 表达式B and … and 表达式N;
• 示例
• 查询会员表中类型为普通用户, 且可用余额大于100000的用户
• select * from member where type=1 and LeaveAmount>100000.00
• 多个条件不需要同时成立, 只需满足其中一个或多个, 语法
• select … from 表名 where 字段a = 值a or 字段b = 值b or 字段c = 值c;
• 示例:
• 查询会员表里用户id为1或者2或用户名为Nick的用户信息
• select * from member where id=1 or id=2 or RegName=‘Nick’;
多表查询问题一:
• 对于不同类型的信息该怎么存储呢? 是放一个表呢, 还是不同类型数据放不同表.(结合仓库去理解)
• 放在一个表的缺点:
• 字段过多
• 难以维护
• 表数据庞大
• 数据冗余, 重复数据多
问题二:
• 既然不同类型的数据放在了不同的表, 那原本有联系的数据怎么保持原有的联系呢?
• 解决方案:
• 设计表的时候两个表之间维持一个关联即可
• member表中的id字段与loan表的借款人编号memberid构成一个关联, 如下图所示:
• 语法:
• select 要查询的字段 from 表1,表2... where 关联条件和过滤条件;
• 示例
• 查询id为1001的用户的用户名, 手机号码, 可用余额以及该用户的借款项目的编号,标题,借款金额
• 分析:
• 涉及表: 用户表(member), 项目表(loan);
• 查询的字段: 会员表(RegName, MobilePhone,LeaveAmount), 项目表(id, title, amount)
• 关联条件: member.id = loan.membered
• 过滤条件: member.id = 1001
• 连接查询
INNER JOIN
LEFT JOIN
RIGTH JOIN
对结果集进行排序
• 语法
• select … from … order by 字段A asc(desc);
• select … from … order by 字段A asc(desc), 字段B asc(desc)...;
• asc: 升序
• desc: 降序
• 示例:
• 查询用户表里所有普通用户信息, 按可用余额升序, 降序排列.
• select * from member where type=1 order by LeaveAmount;
• select * from member where type=1 order by LeaveAmount desc;
掌握数据库其他高级查询的用法
查询-in的用法使用场景: 查询的时候, 条件字段的值存在于某个数据集
• 语法
• select … from 表名 where 条件字段 in (数据集)
• 数据集可以是具体的某几个值: 值a, 值b, …, 值n: 也可以是通过一个子查询得到的数据集
• 示例
• 查询用户id为1001, 1002, 1003的用户信息
• select * from member where id in (1001,1002,1003);
• 拓展
• not in的用法: 条件字段的值不存在于某个数据集
• 查询用户id不为1001, 1002, 1003的用户
模糊查询-like使用场景: 查询的时候, 条件字段的值不完整的情况
• 语法:
• %用来匹配任意长度的字符串, 而且%出现的位置不同所代表的意义不同
• 条件字段的值以任意字符串开头, 以XX结尾的值
• select from where <条件字段> like ‘%XX’
• 条件字段的值以XX开头, 以任意字符串结尾
• select from where <条件字段> like ‘XX%’
• 条件字段的值包含了XX
• select from where <条件字段> like ‘%XX%’
分组-group by使 景: group by 顾名思义就是按照某一个, 或者多个字段来分组, 一般是需要”聚合函数”来配合才能使用, 使用时至少需要一个分组字段. 某某信息来进行分
• 语法
• select 查询字段,聚合函数 from 查询涉及到的表 group by 分组字段 having 过滤条件;
• 语法解释:
• 聚合函数: 对一组值执行计算并返回单一的值的函数. 聚合函数经常与select 语句的 group by 字句一同使用, 常见的聚合函数有: sum(), count(), avg(), min(), max()等
• having: 在分完组以后如果想在这个分组结果的基础上继续过滤的话就必须把过滤条件写在having后面
• 请按项目分组, 统计投资表中各个项目的投资次数
• select LoanID, count() from invest group by LoanID
需求统计每个项目中的平均投资额:
• SELECT *, AVG(amount) FROM invest GROUP BY LoanID;
每个项目的累计投资次数
• select loanid, count(*) from invest group by loaned;
统计每个投资人的累计投资额:
• select * sum(amount) “累计投资额” from invest group by memberID;
统计每个投资人的累计投资额, 并且还要查询这个投资人的用户名和可用余
• select member.regname, member.leavemount, sum(invest.amount) from invest,member
where invest.membered = member.id group by invest.membered;
查询-between使用场景: 条件字段的取值处于两个数据范围内的情况
• 语法
• select ... from 表名 where 条件字段 between 数值A and 数值B;
示例
• 找出用户表可用余额在100000到400000的用户信息.(包含边界值)
• select * from member where LeaveAmount between 100000.00 and 400000.00
查询-去重(distinct)使用场景: 去除查询结果中的重复数据
• 语法
• select distinct 字段名1 from 表名
• 示例
• 查询所有有投资的用户id
• select distinct memberID from invest;
查询-分页(limit)使用场景: 取查询结果的前n条
• 语法
• select … from 表名 limit m,n;
• m指的索引值是从m开始, n表示每页要取多少条
• 示例
• 每页取十条展示
• 第一页为: limit 0, 10 表示取索引从0开始取10条记录
• 第二页为: limit 10, 10 表示取索引从10开始取10条记录
• 第三页为: limit 20, 10 表示取索引从20开始取10条记录
• 思考
• 若每页展示x条数据, 则取第y页时, 分页语句中的m和n值分别代表什么
• 拓展
• 请用SQL将member表数据按照id降序排列后取前三条数据
• 子查询
• 概念: 可以把一条SQL语句的结果作为新数据 ! ! !
• 嵌套查询
select语句查询结构SELECT FROM [WHERE] [GROUP BY] [HAVING] [ORDER BY] [LIMIT]
扫一扫
453
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
