-
select 15 between 1 and 22;输出1 select 35 between 1 and 22;输出0 select 35 not between 1 and 22;输出1 select 10 in(5,10,15,20);输出1 select null is null; 输出1 select 0 is null;输出0 select * from test where first_name is null;查看全部
-
SELECT CEIL(3.01);输出4 进一取整（向上取整） SELECT FLOOR(3.99);输出3 舍一取整 SELECT 3 DIV 4;输出0 整数除法 SELECT MOD(5,3) 输出2 小数也可以取余数 SELECT POWER(3,3);输出27 幂运算 SELECT ROUND(3.145,2);输出3.15 四舍五入 SELECT TRUNCATE(125.89,1)输出125.8 数字截取 SELECT TRUNCATE(125.89,-1)输出120 把5截掉 数字截取查看全部
-
ltrim(): select ltrim(' mysql ');删除前导空格 select length(ltrim(' mysql '));输出9 trim(): select trim(' mysql ');删除后导空格 select length(trim(' mysql '));输出7 select trim(leading '?'from '??mysql???');输出 mysql??? select trim(trailing '?'from '??mysql???');输出 ??mysql select trim(both '?'from '??mysql???');输出 mysql substring():进行字符串的截取 select substring('mysql',1,2);输出my从1开始编号 select substring('mysql',3);输出sql从第三位起到结尾 select substring('mysql',-1);输出l replace(): select replace('??my??sql???','?','');输出mysql [not] like():模式匹配 select 'mysql' like 'm%';输出1 select * from test where first_name like '%1%%' escape'1';查到tom% %(百分号)代表任意个字符 _(下划线）代表任意一个字符查看全部
-
字符函数 concat(): select concat('zhouyingying','-','mysql');输出zhouyingying-mysql select concat(first_name,last_name) as full_name from test; 合并名字 concat_ws(): select concat_ws('-','zhouyingying','mysql');输出zhouyingying-mysql select concat_ws('|','A','B','C');输出A|B|C format(): select format(12560.75,1);输出12560.8 lower(): select lower(‘MYSQL’);输出mysql upper(): select upper('mysql');输出MYSQL left(): select left('mysql',2);输出my select lower(left('MYSQL'，2))； right(): select right('mysql',3);输出sql length(): select length('mysql');输出5查看全部
-
mysql 函数分类查看全部
-
单表模拟多表来删除指定的记录 delete t1 from tdb_goods as t1 left join (select gods_id,goods_name from tdb_goods group by goods_name having count(goods_name)>=2) as t2 on t1.goos_name=t2.goods_name where t1.goods_id>t2.goods_id;（删除重复记录，保留id较小的记录）查看全部
-
无限级分类表设计（自身连接）:同一个数据表对其自身进行连接 查找显示父级id对应的名称 select s.type_id ,s.type_name,p.type_name As parent_id from tdb_goods_types s left join tdb_goods_types p on s.parent_id=p.type_id; 查找子级对应的名称 select p.type_id ,p.type_name,s.type_name from tdb_goods_types p left join tdb_goods_types s on p.type_id=s.parent_id; 查找有多少子级 select p.type_id ,p.type_name,COUNT(s.type_name) from tdb_goods_types p left join tdb_goods_types s on p.type_id=s.parent_id GROUP BY p.type_name ORDER BY p.type_id;查看全部
-
主键确定唯一性查看全部
-
http://img1.sycdn.imooc.com//5485ba75000135df12000530-120-68.jpg查看全部
-
增加约束查看全部
-
外键约束查看全部
-
默认查看全部
-
唯一约束可以存在多个查看全部
-
只能有一个主键查看全部
-
auto-increment查看全部
举报
0/150
提交
取消