MySQL笔记 连接
来源:互联网 发布:怎么查看mac系统版本 编辑:程序博客网 时间:2024/05/17 01:49
创建数据表的同时给数据表添加记录:
CREATE TABLE tdb_goods_brands
(
brand_id INT PRIMARY KEY AUTO_INCREMENT,
brand_name CHAR(40) NOT NULL
)
SELECT brand_name FROM tdb_goods GROUP BY brand_name;
-- 查找所有分类及其父类
SELECT s.type_id,s.type_name,p.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p ON s.parent_id = p.type_id;
-- 查找所有分类及其子类
SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id;
-- 查找所有分类及其子类的数目
SELECT p.type_id,p.type_name,count(s.type_name) AS children_count FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id GROUP BY p.type_name ORDER BY p.type_id;
-- 为tdb_goods_types添加child_count字段
ALTER TABLE tdb_goods_types ADD child_count MEDIUMINT UNSIGNED NOT NULL DEFAULT 0;
-- 将刚才查询到的子类数量更新到tdb_goods_types数据表
UPDATE tdb_goods_types AS t1 INNER JOIN ( SELECT p.type_id,p.type_name,count(s.type_name) AS children_count FROM tdb_goods_types AS p
LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id
GROUP BY p.type_name
ORDER BY p.type_id ) AS t2
ON t1.type_id = t2.type_id
SET t1.child_count = t2.children_count;
-- 复制编号为12,20的两条记录
SELECT * FROM tdb_goods WHERE goods_id IN (19,20);
-- INSERT ... SELECT实现复制
INSERT tdb_goods(goods_name,cate_id,brand_id) SELECT goods_name,cate_id,brand_id FROM tdb_goods WHERE goods_id IN (19,20);
-- 查找重复记录
SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2;
-- 删除重复记录
DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2 ) AS t2 ON t1.goods_name = t2.goods_name WHERE t1.goods_id > t2.goods_id;
- ptlib连接MySql笔记
- VC连接mysql,笔记
- MySQL笔记 连接
- php连接mysql 笔记
- jdbc 连接mysql数据库笔记
- mysql笔记-- mysql命令札记11 --MySql 连接
- PHP学习笔记(三)连接MySQL
- IBatis.NET连接MySQL数据库配置笔记
- mysql笔记 四 左连接 视图 字符集
- 关于jsp 连接mysql的笔记
- MySQL学习笔记9:连接查询
- linux 环境 c 连接mysql笔记
- MySQL学习笔记之多表连接
- MySQL学习笔记9:连接查询
- Eclipse 利用JDBC连接MySQL步骤笔记
- 【Java学习笔记】JDBC连接mySql数据库
- Servlet学习笔记(二)---连接Mysql
- JavaWeb学习笔记 连接mysql操作
- 用递归实现厄密函数
- java学习笔记7对于将从控制台进行输入改为文件
- Java Tomcat SRC 下载地址
- hadoop 之DefaultStringifier
- App开发日报 2015-05-09 Google Sky开源项目:不使用JAVA开发120FPS安卓应用
- MySQL笔记 连接
- Criteria查询
- Java字节码资料4
- WinForm Show()和ShowDialog()的区别:
- BaseAnimation是基于开源的APP,致力于收集各种动画效果(最新版本1.3)
- Hadoop生成HFile直接入库HBase心得
- struct sockaddr与struct sockaddr_in ,struct sockaddr_un的区别和联系
- 使用 PartialFunction 链来扩展actor
- PHP判断请求是否为HTTPS