一、基础
1.数据库相关命令
a>.创建数据库
- CREATE DATABASE 数据库名
b>.创建数据库并设置默认字符集
- CREATE DATABASE 数据库名 DEFAULT CHARACTER SET utf8;
c>.删除数据库
- DROP DATABASE 数据库名
2.数据表相关命令
a>.删除表
- DROP TABLE 表名;
b>.新建表
- DROP TABLE IF EXISTS 表名;
- CREATE TABLE 表名 (
- `id` INT NOT NULL AUTO_INCREMENT,
- `username` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '登录名',
- `password` CHAR(32) NOT NULL DEFAULT '' COMMENT '登录密码',
- PRIMARY KEY (`id`), -- 主键
- INDEX (`username`) -- 索引
- ) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='新建表';
c>.修改表名
- ALTER TABLE 表名1 RENAME TO 表名2;
d>.添加列
- ALTER TABLE 表名 ADD COLUMN 列名 列类型(如:VARCHAR(30));
e>.删除列
- ALTER TABLE 表名 DROP COLUMN 列名;
f>.几条简单的sql语句
- 选择:SELECT * FROM 表名 WHERE 范围;
- 插入:INSERT INTO 表名(字段名1,字段名2) VALUES(字段值1,字段值2);
- 删除:DELETE FROM 表名 WHERE 范围;
- 更新:UPDATE 表名 SET 字段名=字段值 WHERE 范围;
- 查找:SELECT * FROM 表名 WHERE 字段名 LIKE ’% 字段值 %’ ;
- 排序:SELECT * FROM 表名 ORDER BY 字段名1,字段名2 [DESC];
- 总数:SELECT COUNT AS totalcount FROM 表名;
- 求和:SELECT SUM(字段值) AS sumvalue FROM 表名;
- 平均:SELECT AVG(字段名) AS avgvalue FROM 表名;
- 最大:SELECT MAX(字段名) AS maxvalue FROM 表名;
- 最小:SELECT MIN(字段名) AS minvalue FROM 表名;
g>.赋予数据库用户权限(每个数据库都创建单独的用户)
- CREATE USER '项目用户'@'localhost' IDENTIFIED BY '项目用户密码';
- GRANT ALL PRIVILEGES ON 数据库名称.* TO 项目用户@'localhost' ;
或者
- GRANT ALL PRIVILEGES ON 数据库名称.* TO 用户名@'%' IDENTIFIED BY '用户密码';(用’%'表示从任何地址连接)
h>.添加主键
- ALTER TABLE 表名 ADD PRIMARY KEY(列名);
i>.创建索引
- CREATE INDEX 字段名 ON 表名;
j>.删除索引
- DROP INDEX 字段名;
k>.创建视图
- CREATE VIEW 视图名 AS
- SELECT 语句;
l>.删除视图
- DROP VIEW 视图名;
m>.联合查询(这块有很多内容,我这里只提出基本的)
结果集列联合:JOIN ,这里我常用的是LEFT JOIN ,格式如下:
- SELECT a.a, a.b, b.c, b.d FROM a LEFT OUT JOIN b ON a.a = b.c;
- SELECT a.a as col1, a.b as col2 from a
- UNION ALL
- SELECT b.c as col1, b.d as col2 from b;
n>.分组:GROUP BY 一般与count,sum,max,min,avg联合使用
- SELECT SUM(字段名1) FROM 表名 GROUP BY 字段名2;
o>.前10条记录
- SELECT * FROM 表名 LIMIT 0,9;(mysql没有top语法,limit一般常用来分页)
p>.排序(desc降序;asc升序;默认是升序)
- SELECT * FROM 表名 ORDER BY 列名 DESC;
二、进阶
a>.去重,一般常用 DISTINCT
- SELECT DISTINCT * FROM 表名;
- SELECT DISTINCT(列名) FROM 表名;
b>.复制表
- CREATE TABLE 表名2 AS
- (
- SELECT * FROM 表名1
- )
c>.查询分析器:在sql前面加上EXPLAIN或者DESC,查看该语句执行情况
- EXPLAIN SELECT * FROM 表名;
三、贴一下做报表时写的一些恶心的sql
1.根据查询条件查询出符合条件的项目编号
- SELECT DISTINCT(xp.Id)
- FROM xmgl_finance_plan xfp LEFT JOIN xmgl_project xp ON xfp.proId = xp.Id WHERE 1=1
- AND xp.proCategory IN ('ITER973')
- AND xp.Id IN(0,1,2);
2.-- 查询项目计划表里对应的所有的预算科目对应的合计金额
- SELECT
- t.Pid,
- xsb2.subjectName,
- SUM(t.planTotalAmount) planTotalAmount,
- SUM(t.planCurrentAmount) planCurrentAmount,
- SUM(t.total_amount) total_amount,
- SUM(t.payedMoney) payedMoney,
- SUM(t.finalPayment) finalPayment,
- SUM(t.CurYearPayedMoney) CurYearPayedMoney
- FROM (SELECT
- xsb.Id,
- xsb.subjectName,
- xsb.Pid,
- SUM(xfp.planTotalAmount) planTotalAmount,
- SUM(xfp.planCurrentAmount) planCurrentAmount,
- SUM(xfp.total_amount) total_amount,
- SUM(xfp.payedMoney) payedMoney,
- SUM(xfp.finalPayment) finalPayment,
- SUM(xfp.CurYearPayedMoney) CurYearPayedMoney
- FROM xmgl_subject_budget xsb
- LEFT JOIN xmgl_project xp
- ON xsb.type = xp.subjectType
- LEFT JOIN xmgl_finance_plan xfp
- ON xsb.Id = xfp.subjectId
- WHERE xp.Id = 1
- GROUP BY xfp.subjectId,xsb.Id
- ORDER BY xsb.Id) AS t
- LEFT JOIN xmgl_subject_budget xsb2
- ON t.Pid = xsb2.Id
- WHERE t.Pid <> 0
- GROUP BY t.Pid;
3.-- 查询各个部门在特定科目下的控制额度及支出信息(优化)
- SELECT DISTINCT(xcs1.dept),xsc.subjectName,xcs2.amount,COALESCE(SUM(xfa.amount),0) payedMoney
- ,xcs2.amount-SUM(xfa.amount) leftAmount
- FROM xmgl_control_subject xcs1
- LEFT JOIN xmgl_control_subject xcs2 ON xcs1.dept = xcs2.dept AND xcs2.detailId = 3
- LEFT JOIN xmgl_subject_control xsc ON xcs2.detailId = xsc.Id
- LEFT JOIN xmgl_finance_activity xfa ON xcs2.dept = xfa.department AND xfa.detailId IN('22','23')
- GROUP BY xcs1.dept,xcs1.detailId;
4.类似于QQ空间的消息列表
- -- 赞了我
- SELECT tf.id,1 AS typeId,c.avatar AS avatar, c.name,ct.catalog AS catalog, ct.id AS messageId, ct.content AS trendsContent,NULL AS replyContent, tf.create_time AS createTime,tf.read AS isRead
- FROM trends_fork tf LEFT JOIN cust_trends ct ON tf.trends_id = ct.id LEFT JOIN customer c ON c.id = tf.customer_id
- WHERE tf.master_id = 1
- UNION ALL
- -- 给我留言
- SELECT cb.id,2 AS typeId,c.avatar AS avatar,c.name, 3 AS catalog,cb.id AS messageId,NULL AS trendsContent,cb.content AS replyContent,cb.create_time AS createTime,cb.read AS isRead
- FROM cust_board cb LEFT JOIN customer c ON cb.customer_id = c.id WHERE cb.master_id = 1
- UNION ALL
- -- 评论我的动态
- SELECT tr.id,3 AS typeId, c.avatar AS avatar, c.name, ct.catalog AS catalog, ct.id AS messageId, ct.content AS trendsContent, tr.content AS replyContent,tr.create_time AS createTime,tr.read AS isRead
- FROM trends_reply tr LEFT JOIN cust_trends ct ON tr.trends_id = ct.id LEFT JOIN customer c ON c.id = tr.customer_id
- WHERE tr.master_id = 1
- UNION ALL
- -- 回复了我评论的消息(包括直接回复我的评论、在我的动态下评论别人的回复)
- SELECT tra.id,4 AS typeId,c.avatar AS avatar, c.name, ct.catalog AS catalog, tra.trends_id AS messageId,ct.content AS trendsContent, tra.content AS replyContent, tra.create_time AS createTime,tra.read AS isRead
- FROM trends_replyat tra LEFT JOIN trends_reply tr ON tra.reply_id = tr.id LEFT JOIN customer c ON c.id = tra.customer_id LEFT JOIN cust_trends ct ON ct.id = tr.trends_id
- WHERE tra.at = 1 OR (tra.master_id = 1 AND tra.customer_id != 1)
- UNION ALL
- -- 留言板中回复我的消息
- SELECT cbr.id,5 AS typeId,c.avatar AS avatar, c.name, 3 AS catalog, cb.id AS messageId,cb.content AS trendsContent,cbr.content AS replyContent, cbr.create_time AS createTime,cbr.read AS isRead
- FROM cust_board_reply cbr LEFT JOIN cust_board cb ON cbr.board_id = cb.id LEFT JOIN customer c ON cbr.customer_id = c.id
- WHERE (cbr.board_customer_id = 1 AND cbr.customer_id != 1 ) OR (cbr.master_id = 1 AND cbr.customer_id != 1) ORDER BY createTime DESC
- LIMIT 2,10