mysql sql语句
示例:
三张表, posts, tags, posts_tags，
posts ---M:N--- tags
多对多， posts_tags为中间表
mysql> select * from posts;
+----+-------+
| id | title |
+----+-------+
| 1 | post1 |
| 2 | post2 |
| 3 | post3 |
| 4 | post4 |
+----+-------+
mysql> select * from tags;
+----+----------+
| id | tag_name |
+----+----------+
| 1 | java |
| 2 | mysql |
| 3 | python |
+----+----------+
mysql> select * from posts_tags;
+---------+--------+
| post_id | tag_id |
+---------+--------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 3 |
| 3 | 2 |
+---------+--------+
1. select
select [ distinct ] column1, column2...
from table1, table2...
where condition
group by col_name
having condition
order by col_name [asc | desc]
limit {[offset,] row_count | row_count OFFSET offset}]
a. group by
mysql> select * from posts p, tags t, posts_tags pt where p.id = pt.post_id and t.id = pt.tag_id and t.tag_name in ('java','python','mysql');
+----+-------+----+----------+---------+--------+
| id | title | id | tag_name | post_id | tag_id |
+----+-------+----+----------+---------+--------+
| 1 | post1 | 1 | java | 1 | 1 |
| 1 | post1 | 2 | mysql | 1 | 2 |
| 3 | post3 | 2 | mysql | 3 | 2 |
| 1 | post1 | 3 | python | 1 | 3 |
| 2 | post2 | 3 | python | 2 | 3 |
+----+-------+----+----------+---------+--------+
5 rows in set (0.00 sec)
select * from posts p, tags t, posts_tags pt
where p.id = pt.post_id and t.id = pt.tag_id and t.tag_name in ('java','python','mysql')
group by p.id
注意, 如果select中指明了group by 的字段，则除了group by column这个字段之外，别的columns必须在聚合函数中，不然会出错:
mysql> select p.id, * from posts p, tags t, posts_tags pt where p.id = pt.post_id and t.id = pt.tag_id and t.tag_name in ('java','python','mysql')grou
p by p.id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use n
ear '* from posts p, tags t, posts_tags pt where p.id = pt.post_id and t.id = pt.tag_' at line 1
mysql>
mysql> select p.id, count(*) from posts p, tags t, posts_tags pt where p.id = pt.post_id and t.id = pt.tag_id and t.tag_name in ('java','python','mysq
l')group by p.id;
+----+----------+
| id | count(*) |
+----+----------+
| 1 | 3 |
| 2 | 1 |
| 3 | 1 |
+----+----------+
3 rows in set (0.00 sec)
mysql> select p.id, count(*) from posts p, tags t, posts_tags pt where p.id = pt.post_id and t.id = pt.tag_id and t.tag_name in ('java','python','mysq
l')group by p.id having count(*) >0;
+----+----------+
| id | count(*) |
+----+----------+
| 1 | 3 |
| 2 | 1 |
| 3 | 1 |
+----+----------+
3 rows in set (0.00 sec)
+----+-------+----+----------+---------+--------+
| id | title | id | tag_name | post_id | tag_id |
+----+-------+----+----------+---------+--------+
| 1 | post1 | 1 | java | 1 | 1 |
| 2 | post2 | 3 | python | 2 | 3 |
| 3 | post3 | 2 | mysql | 3 | 2 |
+----+-------+----+----------+---------+--------+
3 rows in set (0.00 sec)
当用group by p.id时， p.id为1的三行数据就只显示第一行了，尽管别的字段不同
mysql> select * from posts p, tags t, posts_tags pt
-> where p.id = pt.post_id and t.id = pt.tag_id and t.tag_name in ('java','python','mysql')
-> group by p.id having count(*) =3;
+----+-------+----+----------+---------+--------+
| id | title | id | tag_name | post_id | tag_id |
+----+-------+----+----------+---------+--------+
| 1 | post1 | 1 | java | 1 | 1 |
+----+-------+----+----------+---------+--------+
1 row in set (0.00 sec)
用having 来获取有三个标签的文章
b. limit
后跟一个数字，是 row_count ， 如果是两个数字， 是offset, row_count , 就是获取第offset+1 之后的 row_count行数据
SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15
mysql> select * from tags;
+----+----------+
| id | tag_name |
+----+----------+
| 1 | java |
| 2 | mysql |
| 3 | python |
+----+----------+
3 rows in set (0.00 sec)
mysql> select * from tags limit 1;
+----+----------+
| id | tag_name |
+----+----------+
| 1 | java |
+----+----------+
1 row in set (0.00 sec)
mysql> select * from tags limit 1, 1;
+----+----------+
| id | tag_name |
+----+----------+
| 2 | mysql |
+----+----------+
1 row in set (0.00 sec)
mysql> select * from tags limit 1, 2;
+----+----------+
| id | tag_name |
+----+----------+
| 2 | mysql |
| 3 | python |
+----+----------+
2 rows in set (0.00 sec)
mysql行转列转换
mysql 行列转换 ，在项目中应用的极其频繁，尤其是一些金融项目里的报表。其中最为头痛的就是多行转多列，动态的列行转换。最近在研究这些行里转换，还是从最为简单的行列转换开始。
sql 脚本
-- 创建表 学生表
CREATE TABLE `student` (
`stuid` VARCHAR(16) NOT NULL COMMENT '学号',
`stunm` VARCHAR(20) NOT NULL COMMENT '学生姓名',
PRIMARY KEY (`stuid`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
-- 课程表
CREATE TABLE `courses` (
`courseno` VARCHAR(20) NOT NULL,
`coursenm` VARCHAR(100) NOT NULL,
PRIMARY KEY (`courseno`)
)
COMMENT='课程表'
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
-- 成绩表
CREATE TABLE `score` (
`stuid` VARCHAR(16) NOT NULL,
`courseno` VARCHAR(20) NOT NULL,
`scores` FLOAT NULL DEFAULT NULL,
PRIMARY KEY (`stuid`, `courseno`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
-- 插入数据
-- 学生表数据
Insert Into student (stuid, stunm) Values('1001', '张三');
Insert Into student (stuid, stunm) Values('1002', '李四');
Insert Into student (stuid, stunm) Values('1003', '赵二');
Insert Into student (stuid, stunm) Values('1004', '王五');
Insert Into student (stuid, stunm) Values('1005', '刘青');
Insert Into student (stuid, stunm) Values('1006', '周明');
-- 课程表数据
Insert Into courses (courseno, coursenm) Values('C001', '大学语文');
Insert Into courses (courseno, coursenm) Values('C002', '新视野英语');
Insert Into courses (courseno, coursenm) Values('C003', '离散数学');
Insert Into courses (courseno, coursenm) Values('C004', '概率论与数理统计');
Insert Into courses (courseno, coursenm) Values('C005', '线性代数');
Insert Into courses (courseno, coursenm) Values('C006', '高等数学(一)');
Insert Into courses (courseno, coursenm) Values('C007', '高等数学(二)');
-- 成绩表数据
Insert Into score(stuid, courseno, scores) Values('1001', 'C001', 67);
Insert Into score(stuid, courseno, scores) Values('1002', 'C001', 68);
Insert Into score(stuid, courseno, scores) Values('1003', 'C001', 69);
Insert Into score(stuid, courseno, scores) Values('1004', 'C001', 70);
Insert Into score(stuid, courseno, scores) Values('1005', 'C001', 71);
Insert Into score(stuid, courseno, scores) Values('1006', 'C001', 72);
Insert Into score(stuid, courseno, scores) Values('1001', 'C002', 87);
Insert Into score(stuid, courseno, scores) Values('1002', 'C002', 88);
Insert Into score(stuid, courseno, scores) Values('1003', 'C002', 89);
Insert Into score(stuid, courseno, scores) Values('1004', 'C002', 90);
Insert Into score(stuid, courseno, scores) Values('1005', 'C002', 91);
Insert Into score(stuid, courseno, scores) Values('1006', 'C002', 92);
Insert Into score(stuid, courseno, scores) Values('1001', 'C003', 83);