数据库介绍和使用之单表和多表
单表建表
步骤:
第一步 :新建一个库 createdatabase 库名 ;
第二步:use 库名; 库(linux界面)或点击选择navicat中一个库
第三步:建createtable 表名(字段名1 字符类型1(字符长度1),字段名2字符类型2(字符长度2));
第四步:insertinto 插入数据;
==============================================================
知识点:
一个汉字 占多少长度和编码有关(utf-8,gbk)
utf-8:一个汉字=3个字节
gbk:一个汉字=2个字节
认识字符类型:
int数值类型
char 或varchar字符类型
float 浮点型数据类型
data 时间类型
数值格式:
int 整数 2的负31次方~2的31次方 字节4
bigint 超大整数 字节:8
loat 浮点型数据 字节 :4
字符类型:
char 固定长度的字符串 最大字符255
varchar 具有最大限制的可变长量 最大65535 字符
时间格式类型:
date yyyy--mmmm--dddd 格式的日期 字节3
time hh:mm:ss 格式的时间 字节3
datetime 日期+时间 字节:8
year 年 字节1
建表:
建表语句:
createtable表名(字 段名1字符类型1(字符长度),字段名2 字符类型2(字符长度),字段名3字符类型3(字符长度))defaultcharset=utf8 ;
defaultcharset=utf8 ;默认编码格式,当插入中文数值显示???解决
案例:createtablehz10(idint(10),name char(20),ageint(10))defaultcharset=utf8 ;
desc 表名 查看结构 ;
建表约束:
表中的限制条件
作用:表在的时候加入约束的目的是为了保证表中的记录完整和有效。
(1)非空约束 (not null) 非空,用于保证字段的值不能为空,
(2)唯一约束(unique) 保证字段值具有唯一性(不能重复),可以为空,唯一约束可以有多个
(3)主键约束(primarykey) 保证字段值具有唯一性(不能重复),不能为空 ,一个表中只有一个主键
(4)外键约束 (forrignkey) 两个表之间的主键建立关系
(5)默认 :(default) 当字段不填数据,默认数据
(6)自增长 :auto _increment 当添加一条数据,自动增长+1
(1)与主键约束一起使用,一般针对id
(3)每插入一条数据,指定的字段值+1
select *from表名 查看表
drop table 表名 删除表 ;
例如:DROP table aa;
表字段:
添加字段:add
格式:altertable 表名add 字段名 字符类型(字符长度);
altertable hz10addfs int(10);
修改字段:change
格式:altertable 表名 change原字段名 新字段名 字符类型(字符长度);
altertable hz10changefstz int(10);
删除字段:drop
格式:altertable表名 drop 字段名 ;
altertable hz10 drop tz ;
重命名:rename
格式:altertable原表名 rename 新表名;
altertable hz10 rename hzdcs10 ;
调换字段顺序:modify after 在......后面
格式:altertable 表名 MODIFY移动的字段名 字段类型(字符长度)after 字段名 ;
altertable hzdcs10 MODIFYage int(10)after id ;
将字段添加到首行:
格式:altertable 表名 add字段名 字符类型(字符长度) first ;
altertable hzdcs10 addnoint(10) first ;
将字段添加到指定字段的后面:
格式:altertable 表名 add新字段名 字符类型(字符长度) AFTER 原表名 ;
altertable hzdcs10 addtzint(10) AFTER age ;
表中插入数据:
(1)表中插入数据:insertinto 表名 values(插入值1,插入值2) 注入:插入的值,只有数值不需要加引号,字符要加上引号
如:INSERTinto hz10 VALUES(1,"文琪",18);
(2)表中插入数据:insertinto 表名 (id) values(插入值1)
INSERTinto hz10(id) VALUES(2);
========================================================
单表语句
(1)查询一个表中的所有的数据
格式 : select *from表名 查看表
注意:* 表示所有,每个语句后要有;
(2)查询表中具体的字段(指定的字段)
格式:select 字段名1,字段名2 from 表名
(3)查询表中具体的字段取别名(别名是在结果查看,原表不变)
格式: select 表中的名字as “别名”,表中的名字 “别名” from表名
select name as “姓名”,age “年龄” from表名 注:as可以省略
(4)查询指定内容
格式:select *from表名 where+条件;
如:select *from表名 where 字段名=字段;
where条件使用注意
比较运算符
(1)where+条件((= )等于 、(! =)不等于、(<>)不等于 、(>)大于 (<)小于、(>=)大于等于、(<=)小于等于)
(2)and (与) 同时满足所有条件
(3)or(或) 当有多个条件时满足其中任意一个条件
(4)between..... and...... 在什么范围之间 包含本身
(5)in 在一组数据种匹配:
select* from表名 where 字段名in(数组)
select* from emp where dept2in(101,102,110);
(6)is null 为空 is not null非空
多行注释: ctrl+/
取消多行注释: ctrl+shift+/
单行注释: #
排序
对表中的数据进行排序
对表中的数据排序:order by
acs升序
select* from 表名order by字段 acs;
select* from 表名order by字段 ;
可以不填asc
desc降序select* from 表名order by字段 desc;
二次排序:select* from 表名order by字段 desc ,字段 acs;
模糊匹配like%:匹配0个字符或多个字符
_:表示一个字符
select* from 表名where字段名 like“字符 %”; 匹配开头包含字符的
select* from 表名where字段名 like“%字符%”; 匹配中间包含字符的
select* from 表名where字段名 like“% 字符”; 匹配结尾包含字符的
select* from 表名where字段名 like“_ 字符”; 匹配固定位置字符包含字符
限制查询limit 后面接两个值,第一个值表示下标(索引),第二个值时步长
备注:一个表中索引是从0开始
格式:select* from 表名limit开始行数(不包括本行),需查看行数数量;
select* from 表名limit2,3;
格式:select* from 表名limit需查看行数数量;默认从零开始
select* from 表名limit3;从零开始查看3行
函数:max最大值 selectmax(age)from 表名 ;
min最小值 selectmin(age)from 表名 ;
avg平均值 selectavg(age)from 表名 ;
count统计数 select count(age)from 表名 ; 有年龄的数据有多少个
sum求和 selectsum(age)from 表名 ; 年龄的总和
distinct去重 selectdistinct(dept2)from 表名 ;去除重复值
分组查询group by
一般不单独使用,通常和函数组合使用
selectmax(age)from 表名group by 字段名;
group by 后查询出来的结果,在需要的条件下可以接having
SELECTdept2 ,max(age)ass from empgroupbydept2 HAVINGs>50 ;
having+条件与where+条件 用法一样,但是场景不一样,一般group by后面接having
备注:函数求出后需要设置别名,然后进行语句的操作
改数据
update.....set
update表名 set name=“成全”where sid=123;
update表名 set 修改字段名=“字段新值”where田间;
删除数据
delete删 可以恢复
truncate 快速删除表内数据 无法恢复
drop 删除 删除表和数据
删除速度drop>truncate >delete
delete from 表名; 删除表中所有数据
delete from 表名 where 条件; 删除表中指定条件的数据
truncate表名; 快速删除表中所有数据表取别名:
备份表结构:
create table表名like 表名1; 备份一个与表名1结构相同的新表;
备份数据:insert into 新表(有表结构)select *from 备份表1;备份表1 的数据备份部分数据
先备份一个表,然后备份数据
insert into 新表(有表结构)(字段1,字段2)select 字段1,字段2
from 备份表1;备份表1的部分数据
备份表结构和数据
creat table 表名 as(select *from 表名1);备份表1 的结构和数据===================================================================
linux中备份数据库
mysqldump -u root -p 原数据库>home/数据库脚本.sql
mysqldump -u root -p hz10>home/hz10bf.sql 备份数据
还原数据
mysql -u root -p
1.先建立一个新的空库
2.在linux中还原
mysql -u root -p 新库名<hz10bf.sql
=========================================================
多表:
什么是多表关联查询?
定义:查询数据来源于多张表
主要讲
1、内连接(基本内连接和隐藏内连接)
2、左连接(左外连接)
3、右连接(右外连接)
4、全连接(全外连接)
========================================
1、先建两个表 dept和emp
========================================================
1、笛卡尔积查询(了解):根据两张表相乘得到的结果:比如表一有4条数据,表二有6条数据,查询出来就是24条且包含大量错误,通常不使用
格式:select * from 表一,表二;
2、内连接(普通内连接、隐藏内连接)
查询两个表共有的关联数据
2.1、普通内连接
格式 select * from 表一 inner join 表二 on表一. 关联字段=表二.关联字段;
2.2、隐藏内连接
格式 select * from 表一,表二 where 表一. 关联字段=表二.关联字段;
3、左连接(以左表为主,右表有关联的数据显示,没有的显示null)
格式: select * from 表一 left join 表二 on表一. 关联字段=表二.关联字段
4、右连接(以右表数据为准,左表有关联的数据显示,没有的显示nul)
格式:select * from 表一rightjoin 表二 on表一. 关联字段=表二.关联字段
5、左独有数据(方法,左独有,以右表字段为空)
结构select * from 表一 left join 表二 on表一. 关联字段=表二.关联字段 where 右表字符 is null
6、右独有的数据(方法,右独有,以左表字段为空)
结构select * from 表一 right join 表二 on表一. 关联字段=表二.关联字段 where 左表字符 is null
7、左独有+右独有
union 拼接
结构:左独有 union 右独有
8、全连接
8.1、内连接+左独有+右独有
8.2、右连接+左独有
8.3、左连接+又独有
==========================================
子查询分类:
1、标量子查询
2、列子查询
3、行子查询
4、表子查询
1、标量子查询(返回一个值)
标量子查询 把一个sql执行返回的一个值,作为另一sql执行的一个条件,得到的结果是一行一列,一般出现在where 之后
案例:
selectdept1from deptwhere dept_name="IT技术" ;
标量子查询:selectname,sid from empwhere dept2=(selectdept1from deptwhere dept_name="IT技术" )
注意点:标量子查询允许使用符号:= ,!=(不等于),>,<, >=,<=,<> (不等于)
2、列子查询(返回一个列)
定义:返回的是一列值
注意:通常在where 条件后使用,in或not in ,不能使用=,<,>等比较运算符。
案例: selectdept1from deptwhere dept_name="财务"or dept_name="IT技术" ;
selectname,sid from empwhere dept2in (selectdept1from deptwhere dept_name="财务"or dept_name="IT技术")
3、行子查询:(返回一行多列)
指子查询返回的结果集是一行多列,一般出现在where后面
案例:selectage,incoming fromemp where name="马十二" ;
select* from empwhere (age,incoming) =any (selectage,incoming fromemp where name="马十二" ) 或select* from empwhere (age,incoming) in (selectage,incoming fromemp where name="马十二" )
4、表子查询(重点)(返回一个表)
一般都是接from后面
select * fromdept,emp where dept1=dept2 ;
selectname,sid from( select * fromdept,emp where dept1=dept2)s where age=64;
临时表:给表取一个别名 聚合函数 最好取别名
案例:求财务部门的收入总和;
解法一:
子查询方法:selectsum(incoming) fromempwhere dept2=(selectdept1fromdept where dept_name="财务") ;
解法二:
SELECT SUM(incoming) FROM dept inner joinempondept.dept1=emp.dept2 WHERE dept_name="财务";
解法三:
select sum(s.incoming) from(select*fromemp ,deptwhere dept1=dept2)s where dept_name="财务" ;
==================================
三表
多测师肖sir___软件测试___mysql之三表_wx6039f25744874的技术博客_51CTO博客
三表连接:
方法一:三表隐藏内连接
格式:select*from 表1 , 表2 ,表3 where表1.关联的字段=表3.关联字段and 表2.关联字段=表3.表3字段
select*fromstudent as a , course as b , scasc wherea.stu_no=c.stu_no and b.c_no=c.c_no
方法二:三表普通内连接
格式:
select*from表1 INNER JOIN表3 on 表1.关联的字段=表3.关联字段 inner join 表2on 表2.关联字段=表3.表3字段
select*fromstudent as a INNER JOINscasc on a.stu_no=c.stu_noinner joincourse asbon b.c_no=c.c_no
方法三:三表普通左连接
格式:select*from表1 left JOIN表3 on 表1.关联的字段=表3.关联字段 left join 表2on 表2.关联字段=表3.表3字段
select*fromstudent as a left JOINscasc on a.stu_no=c.stu_noleft joincourse asbon b.c_no=c.c_no
方法四:三表普通右连接
格式:select*from表1 rightJOIN表3 on 表1.关联的字段=表3.关联字段 right join 表2on 表2.关联字段=表3.表3字段
select*fromstudent as a right JOINscasc on a.stu_no=c.stu_noright joincourse asbon b.c_no=c.c_no
方法五:先合两表,在两表合并成一表与第三个表合并
selects.stu_no ,s.stu_name from ( selecta.stu_no ,stu_name,c_nofromstudent a,sc c wherea.stu_no=c.stu_no )s,courseas bwheres.c_no=b.c_no
</hz10bf.sql
页:
[1]