常用SQL语句

2018-06-17 23:21:06来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

创建表:

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参数

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:PHP之MYSQL数据库

下一篇:MySQL的sql_mode模式