首页 > > 网络编程 > Mysql >

常用SQL语句

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

创建表：

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　列名１, 列名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;