传奇免费网站模板下载,网站开发新技术探索,西宁做网站最好的公司哪家好,百度业务推广文章目录 约束空属性约束默认值约束zerofill主键约束自增长约束唯一键约束外键约束 查询select的执行顺序单表查询排序 updatedelete整张表的拷贝复合语句group by分组查询 函数日期函数字符串函数数学函数其他函数 复合查询合并查询union 约束
空属性约束
两个值#xff1a… 文章目录 约束空属性约束默认值约束zerofill主键约束自增长约束唯一键约束外键约束 查询select的执行顺序单表查询排序 updatedelete整张表的拷贝复合语句group by分组查询 函数日期函数字符串函数数学函数其他函数 复合查询合并查询union 约束
空属性约束
两个值null和not null 属性的默认字段基本为空数据为空时无法参数运算 用not null限制列属性不能为空约束即无法插入默认值 注意空串和NULL不一样
默认值约束
default约束保证数据的完整性 建表时为列属性添加默认值忽略该列属性时自动填充默认值 default vs not null default和not null可以一起设置表示不允许显式的插入null数据但是可以忽略该数据
设置列值的具体情况
插入 插入具体值插入NULL此时not null将进行约束 不插入和default有关
列描述 comment字段没有实际意义作为描述语句会保存在创建语句中 执行以下语句会显示建表的具体语句能看到comment字段
show create table 表名 \Gzerofill
int和unsigned int的区别
默认情况下int类型后面括号数字为10unsigned后为11 也可以在建表时显示声明
zerofill作为一种格式化输出关键字需要在创建列属性时声明当数字小于类型后的括号数字时这些位置将用0填充 int最大的值为42亿多一共是10位因为int有符号还需要使用一个符号位
主键约束
表中的一行信息叫做记录一列叫做属性 primary key一张表中最多只有一个主键主键不能为空不能相同通常是整数类型 主键保证每条记录的唯一性
创建表时直接指定主键primary
主键不允许为空所以自带not null属性可以看到Key属性中id记录为PRI
主键不允许相同除了主键之外的其他数据都可以相同 建表时可以在属性定义后再声明主键
创建好表后追加主键
alter table 表名 add primary key(列名);追加主键时需要满足该字段的数据唯一
删除主键
alter table 表名 drop primary key;复合主键 定义复合多个主键后只有所有主键冲突数据才会无法插入任一主键冲突都是允许插入的 可以将复合主键看成一种特殊的主键
如何选择主键
选择与业务有关的主键根据业务的具体信息选择选择与业务无关的主键主键值与业务无关业务调整不影响表结构实现解耦。比如qq号
自增长约束
表中有一个属性可以被设置为自增长auto_increment一旦被设置为自增长就成为主键 通常和主键配合使用作为逻辑主键自增长的属性一般是整数类型
整数类型一张表最多只有一个自增长
使用auto_increment时必须和primary key配合使用否则会报错
插入数据时自增长默认从1开始每插入就1
mysql会记录auto_increment的值
show create table 表名 \G;如果你插入了一个打乱规则的值那么auto_increment的值将从它开始
auto_increment的值可以在建表的最后右括号后指定
唯一键约束
主键更多的是为了保证查找时除了null值之外能查找到唯一的记录 唯一键更多的是为了保证在表中某一属性的数据互不相同 生活中有很多具有唯一性的属性主键只是从这些唯一性属性中选择一个属性成为主键 当需要维护剩下属性的唯一性时可以将其设置为唯一键 主键和唯一键互补 建表时添加unique字段
唯一键使得数据库的约束更强更符合现实世界的要求 把唯一键设置为not null在插入时的属性与主键一样等价于主键
外键约束
在建表时添加外键约束
foreign key(从表属性) references 主表名(主表属性)外键约束的Key列值为MUL
外键维护表与表之间的关系 现实世界中一个学生隶属于某个班级一个班级内部有多个学生 学生班级 n 1。因此学生表与班级表之间存在关系这就是关系型数据库
先有业务场景再有这样的关系 在班级表出现学生信息成本太大所以选择在学生表中出现班级信息 学生表是从表因为它有一个属性用来索引班级表班级表是主表。外键定义在从表中 class_id作为外键定义在从表中。外键列的数据必须在主表存在且唯一 所以外键通常是主表的主键或者唯一键 总之一个表中的列信息域另一个表中的列信息出现关联这就是外键 更新学生表的数据时如果更新了外键且该数据在主表中不存在那么更新不被允许插入数据时也同理
不使用外键将带来的问题 将班级表中的某个班级删除时学生表中不应该存在学生隶属于该班级 当学生记录存在时无法删除对应班级的记录 外键约束对于非法操作的拦截
在从表中创建外键主表必须先存在该属性且两者的类型必须一致
现实的业务逻辑中不同业务之间可能具有外键mysql中的表与表之间也可能具有外键两者之间存在关联。因此mysql必须约束外键以保证数据之间的完整性与一致性这被称作外键约束 具有约束的关联字段叫做外键 区分外键vs外键约束 查询
select * from 表名使用通配符*进行全列查找但通常情况下数据库的数据庞大不建议使用全列查找 将*替换成列属性的名称如select id, name form exam_result就能只显示id和name两列的内容
select的执行顺序
select * from exam_result where ...
根据表名(exam_result)确定要搜索的表根据where条件遍历表将所有符合条件的记录筛选出来最后根据*或者指定的列将所有记录中的对应列呈现
单表查询 select支持使用表达式
表达式的计算结果可以起别名
其中as可以省略命名也可以是中文
distinct可以对查询结果进行去重按照记录是否完全一致去重
select结果可以用where进行进一步的筛选mysql中不支持对于等于的判断需要使用
NULL vs ‘’NULL表示不存在表示存在但没有值并且NULL一般不参与计算 如果想要筛出列属性不为NULL的记录需要用where 属性 is (not) NULL来判断 或者用来判断
用between来进行区间筛选左右区间为闭区间 用and逻辑与也能实现该操作or逻辑或 in查询某条记录是否在集合中
筛选条件与需要呈现出来的列可以不同
(not) like进行模糊匹配查询姓孙的同学%表示多个字符_表示一个字符 select的where条件中不仅可以限制列属性为具体的值还能将列属性中的值做比较
查找总分小于200的记录 为什么这样是错的根据select的执行顺序重命名total之前就需要将total200作为筛选条件进行筛选显然此时的select不知道total是什么 并且因为执行顺序不能在where中使用as重命名
排序
用order by 属性根据该属性值进行排序默认升序 在最后加asc和desc进行升序/降序排序 若排序的属性有多个则表示按照第一个关键字排序时若相等则按照第二个关键字排序若相等则按照第三个关键字排序… 这里可以用total进行排序是因为排序的执行顺序在重命名total后
limit x只显示前x行的数据limit必须写在最后
limit s offset n从n1行开始显示s行
update
对查询的结果进行列值更新
update 表名 set 属性值属性值 where...使用update时一般都要跟上where限制否则将更新所有记录 将总分倒数前三的人的数学成绩30 update将修改存在于表中的列信息不能修改总分因为总分不存在只能修改存在的数学英语和语文
delete
删除记录
delete from 表名 where...不用where限制将删除整张表 删除总分前两名的同学
delete删除时自增auto_increment的值不会被影响 但截断truncate 表名将删除整张表删除所有记录保留表的原始结果自增变量将被重置
整张表的拷贝
用like指令创建一个结构相同的表数据不会拷贝
用insert和select复合指令将原表的数据拷贝到新表
加上distinct实现去重拷贝
用alter和rename对表进行重命名重命名操作是原子的本质是mv操作mv操作本身就是原子的
复合语句
用count统计结果的行数NULL不会被统计 NULL vs ‘’
可以将distinct作为count的参数调用count 以下语句的执行结果为什么不同执行顺序的问题后者先执行count再执行distinct此时distinct不起作用因为count后没有完全相同的记录 用sum函数统计总分
聚合函数 一定是直接或者间接统计列方向的数据这些数据具有相同属性 用avg计算英语平均成绩 用max求数学成绩最大值
group by分组查询
之前的查询都是将所有数据看成一组分组查询顾名思义将所有数据分组后再进行查询 按部门号分组后查询部门号平均工资最高工资
以上语句中group by优先执行 使用group by时除了聚合函数只有分组的依据可以进行显示 如按照deptno分组后只能显示deptno的列信息无法显示ename列信息
where和聚合函数是矛盾的聚合函数需要先拿到所有数据聚合才能得到结果而where的筛选是在拿到数据时进行筛选where先执行聚合后执行 显然拿到数据时无法拿到所有的数据因此以下语句是错误的 筛选平均工资小于2000的部门 用having解决此问题 where的执行顺序一定是靠前的在遍历完所有数据时where的筛选也随之结束 而聚合函数的执行一定是在遍历完所有数据时 而having的执行顺序靠后将对聚合后的结果进行条件筛选 having通常与group by一起使用不能单独使用
从工资大于1000的员工中筛选平均工资小于2000的部门wherehaving聚合函数一起使用 where和聚合函数不能一起使用指的是where的筛选条件不能是聚合函数
总结
group by通过分组的手段为未来进行聚合统计提供支持即group by一定是配合聚合统计使用的group by后跟的一定是分组的依据只有在group by后出现的字段才能在select后出现where vs having 函数
日期函数
current_date()current_time()current_timestamp() 分别返回时间日期日期时间 以time函数的结果为数据进行插入
用date函数也可以mysql会根据列属性提取需要的时间
now timestamp 日期还有加减函数 用datetime作为类型now()作为值进行数据的插入
通过日期的加减函数筛选2分钟内发布的信息
字符串函数 使用charset查看记录中的字符串使用的编码集 一般用来解决乱码问题通过判断列属性使用的编码集和show variables like char%显示的数据是否对应 concat(…)可以拼接多个字符串 length(str)求字符串长度 substring(str, i, len)从第i个字符截取开始长度为len的字符串是字符不是字节 replace(str, str1, str2)将str中的所有str1替换成str2
以首字母小写的方式显示名字mysql中的函数可以嵌套使用
instr(str, str1)返回str1的首字母在str中第一次出现的位置没有返回0 也可以做到like的模糊查找函数可以出现在where筛选中 strcmp和c语言的接口一样大于返回1相等为0不等为-1 trim(str)清洗字符串首尾的空格
数学函数 rand返回随机浮点数可以通过运算返回指定范围中的数 利用format格式化函数输出整数 也能保留特定位数的小数 format函数为执行的最后一步将数据进行格式化输出给format的结果取别名没有意义
其他函数
mysql中有一个mysql数据库其中有一张user表里面记录了所有用户的权限信息修改用户的权限本质上就是对这张表做修改 user()函数本质上是提取user表中的内容 md5()通常用来进行数据摘要在insert语句中若存在md5()函数字段mysql不会保存该语句为历史语句一般无法查询该语句为了保证数据安全 在表中保存的敏感数据通常都是用md5摘要过后的数据当需要用原始数据进行比较筛选时需要对原始数据使用md5函数 md5()形成的摘要长度为定长 password()为mysql自己的摘要函数 ifnull(val1, val2)类似于三目运算符若第一个参数为空返回第二个参数否则返回第一个参数
显示工资最高的员工的姓名以及岗位select嵌套查询
工资大于平均工资的员工信息
每种岗位的平均工资和人数count(*)为一种运算技巧
复合查询
select from 表1, 表2结果为两张表记录的笛卡尔积可以将结果也看成一张表 当两张表存在相同的列属性时用表名.属性名区分两者
自链接 表自身与自身做笛卡尔积需要对表进行重命名否则无法执行语句自链接 查询员工’FORD’的领导的信息名字职工号 自链接时所有的属性都要限制表名否则将像上图一样报错
查询和10号部门的岗位相同的员工信息名字岗位工资部门号但是这些雇员不属于10号部门 当可选条件有多个时使用in关键字用于多行记录的子查询
查询工资大于30号部门所有员工的员工信息名字工资部门号 all关键字将筛选出的数据单列多行看成一个集合也可以用max完成
多个条件比较的技巧可以通过括号进行多个数据的比较不需要用and括号比较类似于c11的初始化列表
还能将查询结果作为一张表与其他表进行笛卡尔积不过查询结果需要重命名 合并查询union
将工资大于2500或者岗位为MANAGER的人找出 用where … or … 能解决该问题用union也能解决其中union all可以不对结果进行去重 union默认对结果进行去重 使用union的前提是表结构相同否则运算结果没有意义 union相当于并集运算
