创建表:
create table course( id INT auto_increment, title TEXT NOT NULL, period INT, description TEXT, primary key(id) ) ENGINE=INNODB CHARSET=utf8;
向表中插入数据(三种方式):
id字段为自增长,可以不给值:
INSERT INTO course(title,period,description) VALUES('经济基础学', 320, '没啥用,爱学不学');
没有给出具体字段,所以values里要逐个写,包括id:
INSERT INTO course VALUE(2, '马克思主义哲学', 330, '这个考试能忽悠就行');
字段顺序也是可以颠倒的:
INSERT INTO course(period, description, title) VALUE(340, '这个考试难啊', '高等数学');
如果没有给某一字段设置值,而且也没有指定默认值的话,那么数据库会将其值设置为null:
INSERT INTO course(title,period) VALUE('必修课', 350);
删除表中数据
语法:
DELETE FROM table_name WHERE 条件表达式
条件表达式如下表:
操作符 | 描述 | 针对表course的举例 |
= | 等于 | title=’高等数学’ |
<> | 不等于 | period<>30 |
> | 大于 | period>300 |
< | 小于 | period<300 |
>= | 大于等于 | period>=320 |
<= | 小于等于 | period<=320 |
between | 在两个数之间 | period between 200 and 400 |
like | 模糊匹配 | title like ‘大学’ |
in | 是否在集合中 | title in (‘经济基础学’, ‘马克思主义哲学’) |
is null | 判断是否为空 | description is null |
and | 并,用于连接多个条件表达式 | period>300 and description like ‘%必修课%’ |
or | 或,用于连接多个条件表达式 | title=’高等数学’ or title=’经济基础学’ |
多条件表达式的优先级:
and运算的优先级高于or, 即先运算and再运算or。 也可以用括号来指定优先级,括号里的表达式先运算。
删除id为1的课程:
DELETE FROM course WHERE id=1;
删除名字以’经济’开头且学时小于200的课程, 同时删除学时大于600的课程:
DELETE FROM course WHERE title like '经济%' and period<200 or period>600;
删除名字以‘经济’开头,且学时小于200或者大于600的课程:
DELETE FROM course WHERE title like '经济%' and (period<200 or period>600);
修改表中数据
语法:
UPDATE table_name SET 字段名1=新值1, 字段名2=新值2 ... WHERE 条件表达式
修改所有学生的年龄为18:
注意,这个操作在实际应用中是非常危险的,所以实际中我们必须对其指定条件;
UPDATE student SET age=18;
将所有parent为null的学生年龄加1, 并将parent设置为 ‘未知’:
UPDATE student SET age=age+1 WHERE parent IS NULL;
查询数据
语法:
top用于指定返回的最大行数,distinct只用于在只返回一列时指明排除重复项
SELECT [distinct|top] 字段名1, 字段名2..... FROM table_name [WHERE 条件表达式] [GROUP BY 分组列 [HAVING 分组筛选表达式] ] [ORDER BY 字段名1 [ASC|DESC],字段名2 [ASC|DESC]
查询course表中所有记录:
SELECT * FROM course;
查询所有课时,并且排除重复的数字:
SELECT distinct period FROM course;
查询课时大于200的课程,最多返回2个课程:
SELECT top 2 * FROM course WHERE period>200;
注意:top语法在postgreSQL中存在;在mysql中是没有top语法的, MySQL中可以使用limit:
SELECT * FROM course WHERE period>200 LIMIT 1,2;
GROUP BY用于对数据进行分组以便汇总计算,HAVING是GROUP BY的可选项,用于对汇总结果进行筛选;汇总计算是指统计个数,计算平均值等,
统计所有课程的平均课时:
SELECT AVG(period) FROM course;
按课时period进行分组,统计每个课时的课程个数,并且只返回课程个数大于3的课时:
SELECT period, COUNT(*) FROM course GROUP BY period HAVING count(*) >3;
常用的聚集函数:
聚集函数 | 描述 |
COUNT(*) | 统计记录个数 |
AVG(column) | 计算某列的平均值 |
MAX(column) | 找出某列的最大值 |
MIN(column) | 找出某列的最小值 |
VAR(column) | 计算某列方差 |
FIRST(column) | 返回某列的第1个值 |
LAST(column) | 返回某列的最后1个值 |
ORDER BY 用于指定返回的结果按照某个或几个字段值大小排序, ASC升序默认, DESC降序:
SELECT * FROM course ORDER BY id desc;
多表连接的SQL语句
先多创建几张表:
create table student( id INT auto_increment, name TEXT NOT NULL, age INT, parent TEXT, primary key(id) ) ENGINE=INNODB CHARSET=utf8; create table enroll( student_id INT, course_id INT, primary key(student_id, course_id), CONSTRAINT FOREIGN KEY(student_id) REFERENCES student(id), CONSTRAINT FOREIGN KEY(course_id) REFERENCES course(id) ); create table teacher( id INT auto_increment, name TEXT NOT NULL, gender BOOLEAN, address TEXT, course_id INT, primary key(id), CONSTRAINT FOREIGN KEY(course_id) REFERENCES course(id) );
因为整个系统的数据是分布在不同的表中,所以很多时候为了得到完整的结果,开发者需要从两个或多个表中查询数据,这时需要在FROM子句中用JOIN关键字连接多个表,相关语法为:
SELECT 列名1, 列名2... FROM table_name1 JOIN table_name2 ON 连接条件表达式 WHERE ...
JOIN本身有多种类型,见下表:
关键字 | 含义 |
INNER JOIN | 获取两个表中满足查询关键字的连接记录 |
LEFT JOIN | 在INNER JOIN返回记录的基础上, 返回 所有左表未被连接记录到的记录 |
RIGHT JOIN | 在INNER JOIN返回记录的基础上, 返回 所有右表未被连接记录到的记录 |
FULL JOIN | 返回INNER JOIN, LEFT JOIN, RIGHT JOIN结果的合集 |
虽然一个JOIN只能连接两个表,但可以同时使用多个JOIN以达到连接多个表的目的:
查询所有教’高等数学’的男老师:
SELECT teacher.* FROM teacher INNER JOIN course on teacher.course_id = course.id WHERE teacher.gender = True and course.title='高等数学'
查询所有18岁的学生选择的课程
SELECT distinct course.title FROM course INNER JOIN enroll ON course.id=enroll.course_id INNER JOIN student ON enroll.student_id=student.id WHERE student.age=18;
其它问题
前面提到在update数据时,如果没有指定where条件,那是很危险的事情,因为这它会将整个表所有记录的值都改掉,为了防止此事件发生,我们可以在启动mysql时使用-U参数