一.字段类型: 1.数字类型:tinyint,smallint,mediumint,int,bigint,float,double,decimal 2.字符串类型:char、varchar,blob,enum,set 3.日期类型:date,time,datetime,timestamp,year 4.null类型 二.数据字段属性: 1.unsigend单词意思 无正负之分:该属性作用是,不允许数据列出负数 2.zerofill:自动增量属性,设置字段属性为zerofill时,保存为5的时候从数据库输出的效果为005 int(3) 3.auto_increment:自增 4.null和not null 5.default 三.索引: 1.主键索引:primary KEY 2.唯一索引:unique 3.一般索引:index、key 4.全文索引:fulltext 三.创建表 create table if not exists table 字段名 1 列类型 [属性][索引] 省去as 就可以看到字段名被as过的 SELECT goods_name '名字',description '描述' FROM ecm_goods; in嵌套查询语句 select goods_name,description from ecm_goods where id in(select * from ecm_goodstype where isopen=1); 联表查询:等值查询、join on、left join on、right join on、inner join on、cross join on、STRAIGHT_JOIN on 解析join on 等等例子链接: 1. FROM table1 LEFT JOIN table2 ON table1.field1 compopr table2.field2 2. compopr参数指定关系比较运算符:"=", "", "<=", ">=" 或 "<>" 先关联查询在去排序 select * from t_people p left join t_team t on p.team_id=t.id order by p.pname limit 10; 先排序在关联查询 select * from (select * from t_people p order by p.pname limit 10) p left join t_team t on p.team_id=t.id limit 10; 利用order by SQL语句 SELECT * FROM ecm_demandtype WHERE parent_id=6 ORDER BY sort_order DESC; 利用 MAX、MIN、AVG、COUNT、SUM 的SQL语句 SELECT MAX(original_price) 'maxprice',MIN(original_price) 'minproce',AVG(original_price) 'avgprice',SUM(original_price) 'sumprice' FROM ecm_goods_spec WHERE stock=0 利用group by SQL语句 SELECT MAX(sort_order) 'maxprice',MIN(sort_order) 'minproce',AVG(sort_order) 'avgprice',SUM(sort_order) 'sumprice' FROM ecm_demandtype GROUP BY parent_id 利用group by having 语句 SELECT MAX(sort_order) 'maxprice',MIN(sort_order) 'minproce',AVG(sort_order) 'avgprice',SUM(sort_order) 'sumprice' FROM ecm_demandtype GROUP BY parent_id HAVING parent_id=6
mysql建表逗号分隔的语句_mysql建表查询语句总结
最新推荐文章于 2023-03-21 10:49:49 发布