sql简介
sql:sql是一种结构化查询语言,数据库顾名思义就是存储数据。
简单来说数据库就是表的集合,每张表都有自己的字段和属性,表与表之间也可以有联系。下面我就开始说数据库表的建立与如何建立。
表你可以把他想成excel表,简单来说所以的表都是有自己的字段,每个字段有自己的类型,然后再往表里添加数据
常见的数据类型
数值类型:一般常见的是用int(整型) float(浮点型) decimal(M,D)(自定义来确定,比如decima(5,2)表示长度为5位小数点后两位的数 123.45)来表示数值.。对于bit占的空间比较小,如果你用的数字范围比较,用bit替代是比较高效的(比如,男女分别用0,1 代替,这时候可以用bit)
文本 二进制型:char() 和varchar()都是比较常用的字符串类型,char是固定长度,如果输入的字符串不够长用空格补齐(适合用于手机号),varchar可变长度字符串,可以根据自己想要的来选择。blob:二进制型,多用于存放图片,text:与char和varchar不同的是,text不可以有默认值,其最大长度是2的16次方-1。
时间日期:时间日期的格式看上面的图,这里想说的是这几个日期的细微区别 DateTime:如果需要精确到小时就用Datetime ,更主要的是datetime类型不需要指定时区,也就是说世界上任何地方都是实用的。Date:所有时间类型里面占空间最小的一个。TimeStamp:时间戳,类型依赖于所指定的时区。
enum(枚举)跟set(集合)类型,用的不多,有兴趣的可以自己了解一下。
接下来讲一下关于sql的健值类型:
index:普通索引:是对记录集的多个字段进行排序的方法,类似于书的目录。他的优点是可以加快数据的检索速度,缺点是降低了数据的维护速度(修改表,索引与需要动态维护)
unique:唯一索引:于index类似,只不过其字段内容不允许重复
primary key:主键: 一个表中只能有一个primary字段并且不允许有重复和空值,通常于auto_increment一起使用。
foreign key 外键:把一个表的一个字段(必须索引类型eg:primary key)作为另外一个表的字段,使两个表联系起来。并且表的存储引擎必须是innodb(大多数都是)
fulltext:全文索引,对文本进行指定的索引,效率比like快得多
字段约束
null:允许为空 ,not null:不允许为空, key:索引类型, default:默认值, extra:字段是否设置为自动增长
范式
第一范式:确保每列的原子性(强调的是列的原子性,即列不能够再分成其他几列).
如果每列(或者每个属性)都是不可再分的最小数据单元(也称为最小的原子单元),则满足第 一范式.
例如:顾客表(姓名、编号、地址、……)其中"地址"列还可以细分为国家、省、市、区等。
第二范式: 在第一范式的基础上更进一层,目标是确保表中的每列都和主键相关
(一是 必须有一个主键;二是没有包含在主键中的列必须完全依赖于主键,
而不能只依赖于主键的部分)
如果一个关系满足第一范式,并且除了主键以外的其它列,都依赖于该主键,则满足第二范式.
例如:订单表(订单编号、产品编号、定购日期、价格、……),"订单编号"为主键,"产品 编号"和主键列没有直接的关系,即"产品编号"列不依赖于主键列,应删除该列。
第三范式: 在第二范式的基础上更进一层,目标是确保每列都和主键列直接相关,而不是间接相关(另 外非主键列必须直接依赖于主键,不能存在传递依赖).
如果一个关系满足第二范式,并且除了主键以外的其它列都不依赖于主键列,则满足第三 范式.
解决方案: 将存在传递依赖的字段,以及依赖的字段本身单独取出,形成一个单独的表, 然后在需 要对应的信息的时候, 使用对应的实体表的主键加进来.
例如:订单表(订单编号,定购日期,顾客编号,顾客姓名,……),初看该表没有问题,满 足第二范式,每列都和主键列"订单编号"相关,再细看你会发现"顾客姓名"和"顾客
编号"相关,"顾客编号"和"订单编号"又相关,最后经过传递依赖,"顾客姓名"也和"订单编 号"相关。为了满足第三范式,应去掉"顾客姓名"列,放入客户表中。
对于一个字段该如何确定是新增一张表去添加还是加到现有的表中(比如外键):
1 确定实体之间是否有关系
2 确定是几对几的关系
3 确定在哪个实体中建立字段
记住当关系为一对一或者多对多时,在哪个实体建立字段都可以。要是关系字段为一对多时,字 段(外键)只能建立在n的哪个实体中
比如商品分类跟商品是1:n的关系,那么把商品分类的id作为商品的外键
对表的操作:
show tables()显示所有的表
create table 表名(
id int auto_increment primary key,
score decimal(3,1)
);创建表
desc 表名:查看表
alter table 表名 add | modify(不能改列名) [default value][null/not null],….)|drop 列名
eg: alter table students modify (name varchar2(16) default ‘unknown’);
alter table students add (score float default 0 not null)
show create table 显示创建表的所有sql语句
rename 原表名 to 新表名
对表的内容的操作:
增删改查:
insert into 表名(col1,col2...) values(value1,value2...)
delete from 表名 where ....
update 表名 set col1=value1,.....where ....
select * from 表名 where .....
group by 对数据集进行分组
order by排序 对数据集进行排序 默认是升序,降序为 desc
limit()获取返回数据集的部分数据,limit 100代表返回前100条
limit start,mcount获取start+1行下的count行数据
sql的执行顺序:from (join on)where,groupby,聚合函数 having,select order by,limit
eg: select *from table1 where id=001 group by name having count(*)>2
order by price desc limit 2,5
很简单,我们自己想一些我们一般是处理好数据再拿出我们想要的内容,要拿到数据首先从表入手,所以是from(可能有join on 有执行完from就执行join on),然后再是过滤出符合条件的数据所以是where,再把数据分组所以是group by,,拿到要想的数据之后才开始操作,所以是having(再次过滤),再选择自己想要的行select最后把自己想要的数据进行排序,order,最后取出自己想要的行 ,limit
运算符
比较运算符 :>,< ,= ,<>(不等于)
逻辑运算符 ,与或非 or and not
范围查询: in , not in , between...and...
空判断: isnull, notnull
外键的级联操作
定义:在修改和删除主表的主键时,同时更新或删除副表的外键值,称为级联操作
级联操作有以下几个
连接操作:
常用的连接操作,inner join 内连接,left join左连接,right join右连接,full join全连接。笛卡儿积
eg:select * from table1 a inner join table2 b on a.id=b.id
笛卡儿积:表示为X×Y,第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员 当我们select * from a,b时,系统就把a,b以笛卡尔积进行处理
sql里面有很多内置函数,熟悉函数能给我们带来很大的便利,下面说一下sql常用的函数:
数学函数:min(),max(), count(),sum(),ave()这几个函数很简单,看到就可以认识函数的什么意思
,abs() sqrt(),mod(x,y),pow(x,y)也一样分别是,绝对值,开方,取余,返回x的y次方
rand(x)与rand() .rand(x)返回一个0-1之间随机浮点型值,x成为种子,
当种子相同产生的 随机数也相同,rand()每次产生不同的0-1的随机数
ceil(x)返回不小于x的最小整数值,返回值转为整形
floor(x)返回不大于x的最小整数值,返回值转为整形
所有的数学函数用法都很简单,想试试直接select (数学函数名()) 。比如 selectsqrt(4)
集合的差集补集全集 except,intersect,union
例子 select name from table1
except
select Name from table2
字符串函数的总结:
lower(str)把字符串转为小写,upper(str)把字符串转为大写
concat(str1,str2....)把多个字符串进行首位相接 select concat('a','b','cd')结果为abcd
需要注意的是concat()可以把数字转成字符串类型
|| 管道连接符,类似于linux的管道连接
select sname || ssex || sage from students 结果为合并的三个列
substr(str,pos,[,len]):截取字符串,pos 为截的位置,len为长度,当pos为负数时则代表反过来
select substr('hello world',-5,2) 结果为wo
char_length(str)返回字符换的字符个数
length(str),返回字符串的存储长度
instr(str1,str2)返回源字符串中,str2出现的位置
trim()从源字符串str中去掉两端 前缀 后缀 字符remstr然后胡返回;如果不指定remstr则默认为空 格和去掉两端的值
repeat(str,count) 将字符串返回count次后返回
reverse(str)将字符串反转后返回
strcmp(str1,str2)比较两个字符串,相同返回1,str1<str2返回-1,其他返回1
format()格式化字符串 format(now(),'yyyy-mm-dd')
convert(int,字段名),cast(字段名 as int)把字符串转成数字
日期函数:
str_to_date(str,format)把字符串转成日期 SELECT STR_TO_DATE('10/20/2019', '%m/%d/%Y');
date_to_str(str,format)把日期转成字符 SELECT DATE_FORMAT('2019-10-20','%Y/%m/%d')
date_add(date,interval 1 day)日期增加函数,date为日期,day 也可以是,hour,week,month
date_sub()用法与date_add()类似
datediff(date1,date2)两个日期差
timediff(time1,time2)两个时间差
if(expr,a,b)判断条件为真返回a,否则返回b select if(1=2,1,2)返回2
case when一样是判断语句,类似于C语言的switch函数
简单函数:case col when values then...else[default] end
搜索函数:case when (expr) then results...else[default] end