Sql语句中where,group by,order by及limit的顺序
where xxx,group by xxx,order by xxx,limit xxx
mysql> select * from students;
+----+--------+-------+----------+
| id | name | score | class_id |
+----+--------+-------+----------+
| 1 | Woson | 90 | 2 |
| 2 | Tom | 88 | 1 |
| 3 | Tom | 77 | 2 |
| 4 | Simon | 93 | 3 |
| 5 | Leo | 99 | 2 |
| 6 | Leo | 55 | 2 |
| 7 | Edon | 84 | 0 |
| 8 | Yonson | 76 | 2 |
+----+--------+-------+----------+
mysql> select * from classes;
+----+------------+
| id | name |
+----+------------+
| 1 | ClassOne |
| 2 | ClassTwo |
| 3 | ClassThree |
| 4 | ClassFour |
+----+------------+
students自连接:
1. select distinct a.* from students as a inner join students as b on a.id<>b.id and a.name = b.name ;
2. select distinct a.* from students a , students b where a.id<>b.id and a.name = b.name ;
+----+------+-------+----------+
| id | name | score | class_id |
+----+------+-------+----------+
| 3 | Tom | 77 | 2 |
| 2 | Tom | 88 | 1 |
| 6 | Leo | 55 | 2 |
| 5 | Leo | 99 | 2 |
+----+------+-------+----------+
内连接:
两个表中class_id的交集
1. select s.id,s.name,c.name from students as s inner join classes as c on s.class_id=c.id ;
2. select s.id,s.name,c.name from students s , classes c where s.class_id=c.id ;
+----+--------+------------+
| id | name | name |
+----+--------+------------+
| 2 | Tom | ClassOne |
| 1 | Woson | ClassTwo |
| 3 | Tom | ClassTwo |
| 5 | Leo | ClassTwo |
| 6 | Leo | ClassTwo |
| 8 | Yonson | ClassTwo |
| 4 | Simon | ClassThree |
+----+--------+------------+
左外连接:
select s.id,s.name,c.name from students as s left (outer) join classes as c on s.class_id=c.id ;
+----+--------+------------+
| id | name | name |
+----+--------+------------+
| 1 | Woson | ClassTwo |
| 2 | Tom | ClassOne |
| 3 | Tom | ClassTwo |
| 4 | Simon | ClassThree |
| 5 | Leo | ClassTwo |
| 6 | Leo | ClassTwo |
| 7 | Edon | NULL |
| 8 | Yonson | ClassTwo |
+----+--------+------------+
右外连接:
select s.id,s.name,c.name from students as s right (outer) join classes as c on s.class_id=c.id ;
+------+--------+------------+
| id | name | name |
+------+--------+------------+
| 2 | Tom | ClassOne |
| 1 | Woson | ClassTwo |
| 3 | Tom | ClassTwo |
| 5 | Leo | ClassTwo |
| 6 | Leo | ClassTwo |
| 8 | Yonson | ClassTwo |
| 4 | Simon | ClassThree |
| NULL | NULL | ClassFour |
+------+--------+------------+
全外连接:
两个表中class_id的并集
1. select s.id,s.name,c.name from students as s full (outer) join classes as c on s.class_id=c.id ;
mysql5.0.x不支持全外连接
2. select s.id,s.name,c.name from students as s left (outer) join classes as c on s.class_id=c.id union select s.id,s.name,c.name from students as s right (outer) join classes as c on s.class_id=c.id;
+------+--------+------------+
| id | name | name |
+------+--------+------------+
| 1 | Woson | ClassTwo |
| 2 | Tom | ClassOne |
| 3 | Tom | ClassTwo |
| 4 | Simon | ClassThree |
| 5 | Leo | ClassTwo |
| 6 | Leo | ClassTwo |
| 7 | Edon | NULL |
| 8 | Yonson | ClassTwo |
| NULL | NULL | ClassFour |
+------+--------+------------+
mysql> select c.name,sum(s.score) as total_score from students as s inner join classes as c on s.class_id=c.id group by s.class_id ;
+------------+-------------+
| name | total_score |
+------------+-------------+
| ClassOne | 88 |
| ClassTwo | 397 |
| ClassThree | 93 |
+------------+-------------+
3 rows in set (0.00 sec)
mysql> select c.name,sum(s.score) as total from students as s inner join classes as c on s.class_id=c.id group by s.class_id having total < 300;
+------------+-------+
| name | total |
+------------+-------+
| ClassOne | 88 |
| ClassThree | 93 |
+------------+-------+
mysql> select c.name,sum(s.score) as total from students as s inner join classes asc on s.class_id=c.id group by s.class_id having total < 300 order by total desc limit 1;
+------------+-------+
| name | total |
+------------+-------+
| ClassThree | 93 |
+------------+-------+
mysql> select c.name,avg(s.score) as average from students as s inner join classes as c on s.class_id=c.id group by s.class_id ;
+------------+---------+
| name | average |
+------------+---------+
| ClassOne | 88.0000 |
| ClassTwo | 79.4000 |
| ClassThree | 93.0000 |
+------------+---------+
3 rows in set (0.00 sec)
mysql> select c.name,max(s.score) as max_score from students as s inner join classes as c on s.class_id=c.id group by s.class_id ;
+------------+-----------+
| name | max_score |
+------------+-----------+
| ClassOne | 88 |
| ClassTwo | 99 |
| ClassThree | 93 |
+------------+-----------+
3 rows in set (0.00 sec)
mysql> select c.name,min(s.score) as min_score from students as s inner join classes as c on s.class_id=c.id group by s.class_id ;
+------------+-----------+
| name | min_score |
+------------+-----------+
| ClassOne | 88 |
| ClassTwo | 55 |
| ClassThree | 93 |
+------------+-----------+
---------------------------------------------------------------------------------------------------------------
mysql> select * from users;
+--------+
| qq_no |
+--------+
| 123456 |
| 123457 |
| 123458 |
| 123459 |
| 123460 |
+--------+
mysql> select * from users2;
+--------+
| qq_no |
+--------+
| 123458 |
| 123459 |
| 123460 |
| 123461 |
| 123462 |
| 123463 |
| 123464 |
+--------+
users - users2:
mysql> select qq_no from users where qq_no not in (select qq_no from users2);+--------+
| qq_no |
+--------+
| 123456 |
| 123457 |
+--------+
mysql> select users.qq_no from users left join users2 on users.qq_no=users2.qq_no where users2.qq_no is null;
+--------+
| qq_no |
+--------+
| 123456 |
| 123457 |
+--------+
users2 - users:
mysql> select qq_no from users2 where qq_no not in (select qq_no from users);
+--------+
| qq_no |
+--------+
| 123461 |
| 123462 |
| 123463 |
| 123464 |
+--------+
mysql> select users2.qq_no from users2 left join users on users2.qq_no=users.qq_no where users.qq_no is null;
+--------+
| qq_no |
+--------+
| 123461 |
| 123462 |
| 123463 |
| 123464 |
+--------+
----------------------------------------------------------------------------------------------------
mysql> select * from student;
+----+--------+-------+----------+
| id | name | score | class_id |
+----+--------+-------+----------+
| 1 | Woson | 90 | 2 |
| 2 | Tom | 88 | 1 |
| 3 | Tom | 77 | 2 |
| 4 | Simon | 93 | 3 |
| 5 | Leo | 99 | 2 |
| 6 | Leo | 55 | 2 |
| 7 | Edon | 84 | 0 |
| 8 | Yonson | 76 | 2 |
+----+--------+-------+----------+
mysql> select name,score,class_id from student into outfile "/home/simon/student_bak.txt" lines terminated by "\r\n";
ERROR 1 (HY000): Can't create/write to file '/home/simon/student_bak.txt' (Errcode:13)
So I read again the documentation of MySQL
, and I found this:
The SELECT ... INTO OUTFILE 'file_name' form of SELECT
writes the selected rows to a file. The file is created on the server
host, so you must have the FILE privilege to use this syntax.
mysql> select name,score,class_id from student into outfile "student_bak.txt" lines terminated by "\r\n";
Query OK, 8 rows affected (0.00 sec)
(LINUX) By default, if you don't specify absolute path for OUTFILE in select ... into OUTFILE "..."
INSTALL_DIR = "/usr/local/mysql"
It creates the file in "INSTALL_DIR/data/"
Make sure current user has (NOT) a write permission in that directory.
mysql> load data infile "student_bak.txt" into table student;
Query OK, 9 rows affected, 6 warnings (0.02 sec)
Records: 9 Deleted: 0 Skipped: 0 Warnings: 5
如何在mysql从多个表中组合字段然后插入到一个新表中,通过一条sql语句实现。具体情形是:有三张表a、b、c,现在需要从表b和表c中分别查几个字段的值插入到表a中对应的字段。对于这种情况,我们可以使用如下的语句来实现:
INSERT INTO db1_name(field1,field2) SELECT field1,field2 FROM db2_name
当然,上面的语句比较适合两个表的数据互插,如果多个表就不适应了。对于多个表,我们可以先将需要查询的字段join起来,然后组成一个视图后再select from就可以了:
INSERT INTO a(field1,field2) SELECT * FROM(SELECT f1,f2 FROM b JOIN c) AS tb
其中f1是表b的字段,f2是表c的字段,通过join查询就将分别来自表b和表c的字段进行了组合,然后再通过select嵌套查询插入到表a中,这样就满足了我们这个场景了,如果需要不止2个表,那么可以多个join的形式来组合字段。需要注意的是嵌套查询部分最后一定要有设置表别名,如下:
SELECT * FROM(SELECT f1,f2 FROM b JOIN c) AS tb
即最后的as tb是必须的(当然tb这个名称可以随意取),即指定一个别名,否则在mysql中会报如下错误:
ERROR 1248 (42000): Every derived TABLE must have its own alias
分享到:
2012-02-17 11:25
浏览 1104
分类:数据库
评论