目录
CREATE
INSERT, DELETE, UPDATE
SELECT
GROUP BY:
多表联查
内连接
外连接
自连接
子查询
union合并查询
CREATE
//创建数据库和表
CREATE DATABASE myDatabase1;
USE myDatabase1;
CREATE TABLE myTable(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20),
sage INT
);
//PRIMARY KEY 主键
//AUTO_INCREMENT 主键自增长
INSERT, DELETE, UPDATE
//增
INSERT INTO mytable (sname,sphone) VALUES('zhangsan',1232313213);
INSERT INTO mytable VALUES(NULL,'zhangsan',1232313213);
//删
DELETE FROM mytable WHERE saddress='zhangsandizhi';
//删除(清空)表的内容,保留结构
//TRUNCATE 和DELETE只删除数据, DROP则删除整个表(结构和数据)
TRUNCATE TABLE mytable;
//改
UPDATE mytable SET sname='lisi' ,sage=14 WHERE sname='zhangsan';
SELECT
//查
SELECT * FROM mytable WHERE sid=1;
SELECT sname FROM mytable WHERE saddress='dizhi';
//distinct,对查询结果去重
SELECT DISTINCT sphone FROM mytable WHERE saddress='dizhi';
/*
LIMIT 限制返回的行数。
也可以有两个参数,第一个参数为起始行,从 0 开始;
第二个参数 为返回的总行数。
*/
//返回前 5 行:
SELECT * FROM mytable LIMIT 5;
SELECT * FROM mytable LIMIT 0, 5;
//返回第 3 ~ 5 行:
SELECT * FROM mytable LIMIT 2, 3;
SELECT * FROM mytable LIMIT 3 offset 2;
/*
通过limit和offset 或只通过limit可以实现分页功能。
假设 numperpage 表示每页要显示的条数,pagenum表示页码,那么 返回第pagenum页,每页条数为numperpage的sql语句:
*/
//代码示例:
语句3:select * from studnet limit (pagenum-1)*numperpage,numperpage
//排序
SELECT * FROM mytable ORDER BY col1 DESC, col2 ASC;
GROUP BY:
//GROUP BY 如果只GROUP BY不加GROUP_CONCAT(column),每个group只会显示一条;参考yAngrUiLin啊
SELECT GROUP_CONCAT(username),GROUP_CONCAT(age),sex FROM testusers GROUP BY sex
//COUNT()函数:统计记录总数(含null) 不想统计null,*可以写成具体字段名
SELECT COUNT(*) AS totalUsers, GROUP_CONCAT(username) AS userDetail ,GROUP_CONCAT(age) AS userAge,sex FROM testusers GROUP BY sex;
//HAVING
SELECT COUNT(*) AS totalUsers,GROUP_CONCAT(age) AS userAge,GROUP_CONCAT(username) AS userDetail,sex FROM testusers GROUP BY sex HAVING totalUsers>=5;
//聚合函数
SELECT COUNT(*) AS totalUsers,GROUP_CONCAT(username) AS userDetail,sex,SUM(salary) AS sum_salary,MAX(salary) AS max_salary,MAX(salary) AS max_salary,MIN(salary) AS min_salary,AVG(salary) AS avg_salary FROM testusers GROUP BY sex;
多表联查
内连接、外连接、自连接
我们介绍过,在关系型数据库中,我们通常为了减少数据的冗余量将对数据表进行规范,将数据分割到不同的表中。当我们需要将这些数据重新合成一条时,就需要用到我们介绍来将要说到的表连接。
基本定义:
left join (左连接):返回包括左表中的所有记录和右表中连接字段相等的记录。
right join (右连接):返回包括右表中的所有记录和左表中连接字段相等的记录。
inner join (等值连接或者叫内连接):只返回两个表中连接字段相等的行。
full join (全外连接):返回左右表中所有的记录和左右表中连接字段相等的记录。1、内联接(典型的联接运算,使用像 = 或 <> 之类的比较运算符)。包括相等联接和自然联接。
内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如,检索 students和courses表中学生标识号相同的所有行。
2、外联接。外联接可以是左向外联接、右向外联接或完整外部联接。
在 FROM子句中指定外联接时,可以由下列几组关键字中的一组指定:1)LEFT JOIN或LEFT OUTER JOIN
左向外联接的结果集包括 LEFT OUTER子句中指定的左表的所有行,而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列表列均为空值。2)RIGHT JOIN 或 RIGHT OUTER JOIN
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行,则将为左表返回空值。
3)FULL JOIN 或 FULL OUTER JOIN
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值。如果表之间有匹配行,则整个结果集行包含基表的数据值。
student表
表结构:
字段 | 解释 |
studentId | 学号(主键) |
name | 姓名 |
phone | 电话 |
collegeId | 学生所在学院ID(外键) |
CREATE TABLE `student` (
`studentId` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`phone` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`collegeId` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`studentId`),
KEY `collegeId` (`collegeId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
数据:
college表
表结构:
字段 | 解释 |
collegeId | 学院ID(主键) |
collegeName | 学院名 |
CREATE TABLE `college` (
`collegeId` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`collegeName` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`collegeId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
数据:
内连接
内连接就是表间的主键与外键相连,只取得键值一致的,可以获取双方表中的数据连接方式。语法如下:
SELECT col1,col2 FROM table1 INNER JOIN table2 ON table1.外键=table2.主键 WHERE 条件语句;
//也可写成:
SELECT col1,col2 FROM table1,table2 WHERE table1.外键=table2.主键
运行结果:
mysql> SELECT student.name,college.collegeName FROM student INNER JOIN college ON
student.collegeId = college.collegeId;
+——+————-+
| name | collegeName |
+——+————-+
| 张三 | 清华 |
| 李四 | 北大 |
| 王五 | 浙大 |
+——+————-+
这样,我们就成功将【student】表中的【name】和【college】表中的【collegeName】进行了重新结合,并检索出来。
外连接
与取得双方表中数据的内连接相比,外连接只能取得其中一方存在的数据,外连接又分为左连接和右连接两种情况。接下来,我们将介绍这两种连接方式。
左外连接
左连接是以左表为标准,只查询在左边表中存在的数据,当然需要两个表中的键值一致。语法如下:
SELECT col1 FROM table1 LEFT OUTER JOIN table2 ON table1.外键=table2.主键 WHERE 条件语句;
运行结果:
mysql> SELECT student.name,college.collegeName FROM student LEFT OUTER JOIN college ON
student.collegeId = college.collegeId;
+——+————-+
| name | collegeName |
+——+————-+
| 张三 | 清华 |
| 李四 | 北大 |
| 王五 | 浙大 |
| 赵六 | NULL |
| 钱七 | NULL |
+——+————-+
我们可以看出,与内连接查询结果不同的是:【赵六】、【钱七】这两个学生虽然没有学校ID但是也被查出来了,这就是我们所说的,他会以左连接中的左表的全部数据作为基准进行查询。
右外连接
同理,右连接将会以右边作为基准,进行检索。语法如下:
SELECT col1 FROM table1 RIGHT OUTER JOIN table2 ON table1.外键=table2.主键 WHERE 条件语句;
运行结果:
mysql> SELECT student.name,college.collegeName FROM student RIGHT OUTER JOIN college ON
student.collegeId = college.collegeId;
+——+————-+
| name | collegeName |
+——+————-+
| 张三 | 清华 |
| 李四 | 北大 |
| 王五 | 浙大 |
| NULL | 厦大 |
+——+————-+
我们可以看出,这里就是以右边的表【college】为基准进行了检索,因为【student】中并没有【厦大】的学生,所以检索出来的为【NULL】
注意事项:
- 内连接是抽取两表间键值一致的数据,而外连接(左,右)时,是以其中一个表的全部记录作为基准进行检索。
- 左连接和右连接只有数据基准的区别,本质上是一样的,具体使用哪一种连接,根据实际的需求所决定。
自连接
我们将【student】的数据改为下图:
运行结果如图:
mysql> SELECT * FROM student s ,student a where
a.collegeId=s.collegeId AND a.name <> s.name ORDER BY a.collegeId;
+———–+——+——-+———–+———–+——+——-+———–+
| studentId | name | phone | collegeId | studentId | name | phone | collegeId |
+———–+——+——-+———–+———–+——+——-+———–+
| 4 | 赵六 | 136 | 11 | 1 | 张三 | 139 | 11 |
| 1 | 张三 | 139 | 11 | 4 | 赵六 | 136 | 11 |
| 5 | 钱七 | 135 | 22 | 2 | 李四 | 130 | 22 |
| 2 | 李四 | 130 | 22 | 5 | 钱七 | 135 | 22 |
+———–+——+——-+———–+———–+——+——-+———–+
可以看出,我们就将【student】表中在同一个学校的学生查出来了。
语句释义:
【student s】和【student a】的就是给我们的【student】表取了两个不同的别名;
【a.collegeId = s.collegeId AND a.name <> s.name 】的含义是找出【collegeId】相同,但是【name】不同的人.
【ORDER BY a.collegeId;】将结果顺序输出;
自连接的使用情况还是很多的,比如当我们找某个站点所经过的所有公交等,都可以采用自连接的方式进行检索;
子查询
通常我们在查询的SQL中嵌套查询,称为子查询。子查询通常会使复杂的查询变得简单,但是相关的子查询要对基础表的每一条数据都进行子查询的动作,所以当表单中数据过大时,一定要慎重选择。基本语法如下:
例如:我们利用上面的内连接的例子,在它的基础上查出学校为【清华】的学生的姓名
mysql> SELECT * FROM (SELECT student.name,college.collegeName FROM student INNER JOIN
college ON student.collegeId = college.collegeId)b WHERE b.collegeName = ‘清华’;
+——+————-+
| name | collegeName |
+——+————-+
| 张三 | 清华 |
| 赵六 | 清华 |
+——+————-+
查询成功。
union
适合两张结构一样的表合并查询
select * from table1 union all select * from table2
//union all 是所有的都显示出来;
select * from table1 union select * from table2
//union 是只显示不重复的,如果两个表的字段部分相同,把查询的字段写出来亦可