常用SQL语句（笔记）

1.数据库定义语句

（1）创建数据库

CREARE DATABASE 数据库名

[[DEFAULT]CHARACTER SET 字符集],[[DEFAULT] CPLLATE 校对规则]

创建一个名为pet的数据库：

CREATE DATABASE pet;

创建一个名为plant的数据库，设置其字符集为utf8:

CREATE DATABASE plant CHARACTER SET utf8;

(2)打开数据库

USE 数据库名;

USE pet;--将pet设定为当前数据库，后续SQL命令才可以针对该数据库进行操作

（3）删除数据库

DROP DATABASE 数据库名;

删除数据库plant

DROP DATABASE plant;

（4）创建表

CREATE TABLE 表名(

字段名 字段数据类型 字段约束，

...

Constraint 约束说明

);

在pet数据库中建立猫表cat：

USE pet

CREATE TABLE 'cat'(

'CatCode' int(4) NOT NULL AUTO_INCREMENT,

'CatName' varchar(16) NOT NULL,

'Gender' char(1) NOT NULL DEFAULT'母',

'Birthday' date NOT NULL,

'Photo' varchar(30) DEFAULT NULL,

'Introduction' text,

'RegTime' timestamp DEFALUT CURRENT_TIMESTAPE,

PRIMARY KEY('CatCode')

);

提示：语句中字段名单引号可以去掉

（5）删除表

DROP TABLE数据表名;

删除cat表

DROP TABLE cat;

（6）建立索引

CREATE [UNIQUE|FULLTEXT] INDEX 索引名 ON 数据表名（字段 ASC|DESC);--ASC升序，DESC降序

在猫表的猫名上建立唯一索引

CREATE UNIQUE INDEX IX_Name ON Cat(CatName DESC);

（7）删除索引

DROP INDEX 索引名 ON 数据表;

删除索引IX_Name

DROP INDEX IX_Name ON Cat;

2.查询单表数据

SELECT 字段列表

FROM 表名或视图

[WHERE 查询条件]

[GROUP BY 分组字段 [HAVING 分组条件]]

[GROUP BY 字段名[ASC/DESC]]

[LIMIT 起始位置，记录数];

（1）FROM语句

SELECT 字段名1，字段名2...

FROM 表1，表2...

查询student表中所有学生的姓名和性别信息。

SELECT StudentName,Gender

FROM student;

（2）SELECT字段列表

        1）可以选择任意多个字段

        2）可以使用通配符"*"表示表中所有字段

查询student表中全部字段信息：

SELECT *

FROM student;

        3）“字段名 AS 别名”：AS可省略，用空格隔开

        4）DISTINCT:消除查询结果中的重复记录，只保留一条

        5）计算字段和新增字段：字段可以是表中字段名，也可以是常量和表达式，表达式中可以使用聚合函数进行统计计算

        常用聚合函数：

                AVG([ALL|DISTINCT]expression):数值型计算平均值

                COUNT([ALL|DISTINCT]expression):统计某个字段个数

                MAX([ALL|DISTINCT]expression):最大值

                MIN([ALL|DISTINCT]expression):最小值

                SUM([ALL|DISTINCT]expression):数值型求和

查询coursenroll表，统计所有学生选课的平均分、最高分、最低分及总选课人次：

SELECT AVG(Score) AS 平均分,MAX(Score) AS 最高分,MIN(Score) 最低分,Count(StudentCode) AS 总人次,'成绩调整' 说明

FROM coursenroll;

（3）WHERE 语句（必须紧跟在FROM子句之后）

WHERE 查询条件

查询条件是一个关系式或逻辑表达式

MySQL中常用关系和逻辑运算

比较：>、<、=<、>=、=、<>或！=、！>、！<

确定范围：BETWEEN...AND、NOT BETWEEN...AND

确定集合：IN、NOT IN、EXISTS

字符匹配：LIKE、NOT LIKE

空值：IS NULL、IS NOT NULL

否定：NOT或!

逻辑运算：AND或&&、OR或||

1）比较和逻辑运算

查询student表中学号为1003的学生信息:

SELECT*FROM student WHERE StudentCode=1003;

查询student表中所有上海女生的信息：

SELECT*FROM student

WHERE Gender='女' AND Location='上海'; 

2）界定范围的BETWEEN..AND运算

BETWEEN 值1 AND 值2;（包括值1和值2）

NOT BETWEEN 值1 AND 值2;

在coursenroll表中查询选修了课程号C001、成绩在70~90分之间的所有学生学号、课程号及成绩信息。

SELECT StudentCode,CourseCode,Score

FROM courseenroll

WHERE CourseCode='C001' AND Score BETWEEN 70 AND 90;

或者也可以：

WHERE CourseCode='C001'AND Score>=70 AND Score<=90;

查询student表中不在1970-2004年之间出生的学生学号、姓名和生日

SELECT StudentCode,StudentName,Birthday

FROM student

WHERE Birthday NOT BETWEEN '1970-01-01- AND '2004-12-31';

注意：日期型常量要用完整格式'年-月-日'

或者也可以：使用Year()函数：

WHERE Year(Birthday) NOT BETWEEN 1970 AND 2004;

        3）判断是否在集合中的IN运算

IN(值1，值2...)表示在集合中，NOT IN(值1，值2...）表示不再集合中

查询student表中来自东北三省的学生信息。

SELECT *

FROM student

WHERE Location IN ('黑龙江','吉林','辽宁');

或者也可以：

WHERE Location='黑龙江' OR Location='吉林' OR Location='辽宁'

        4)匹配字符串模式的LIKE 运算

使用通配符：“%”匹配0个或多个任意字符；“_”匹配1个任意字符

查询course表中课程名中包含“技术”两个字的课程号及课程名

SELECT CourseCode,CourseName

FROM course

WHERE CourseName LIKE '%技术%';

查询student表中不姓刘的学生的学号和姓名

SELECT StudentCode,StudentName

FROM student

WHERE StudentName NOT LIKE '刘%';

（4）GROUP BY子句

按指定字段把具有相同值的记录通过汇总计算合并成一条记录

GROUP BY 分组字段[HAVING 分组条件]

HAVING子句是在分组汇总之后，对查询结果进一步过滤

统计每门课程的选课人数和平均分

SELECT CourseCode,COUNT(StudentCode) AS 选课人数,AVG(Score) AS 平均分

FROM courseenroll

GROUP BY CourseCode;

若句尾增加“HAVING AVG(Score)>=80 ”或“HAVING 平均分>=80”，则可筛选每门课平均分大于80的选课人数和平均分

（5）OREDER BY子句

ORDER BY 字段名 [ASC/DESC]

多个排序限制按“，”分隔

查询“零零后”学生，并按年龄从小到大排序

SELECT StudentCode,StudentName,Birthday

FROM student

WHERE Year(Birthday)>=2000

ORDER BY Birthday DESC;

注意年龄从小到大排序是日期降序而非升序

按生源地升序和学号降序查询学生电话信息

SELECT Location,StudentCode,SudentName,Phone

FROM student

ORDER BY   convert(Location using gbk) ,StudentCode DESC;

汉字按拼音字母排序：convert(字段名 using gbk)函数将字段转为gbk字符集（一种汉字编码）

（6）LIMIT子句

LIMIT 起始位置，记录数

若“起始位置”为0，可省略；若“记录数”大于查询结果记录总数，则显示所有记录。

查询平均成绩前三名的学生：

SELECT StudentCode,StudentName,AVG(Score) 平均成绩

FROM courseroll

GROUP BY StudentCode

ORDER BY 平均成绩 DESC

LIMIT 3;

3.查询多表数据

涉及两个或两个以上表的查询，需要说明表之间的连接关系

（1）内连接（自然连接）

用比较运算符对两个表中的数据进行比较，所有匹配的行连接在一起作为查询结果。

1）在WHERE子句中说明连接条件

FROM 表1,表2

WHERE 表1.字段名1<比较运算符>表2.字段名2

查询各学科开设的课程，显示学科号、学科名和课程名。

SELECT subject.SubjectCode,SubjectName,CourseName

FROM subject,course

WHERE subject.SubjectCode=course.SubjectCode;

注意：第一行subject.SubjectCode不可写为SubjectCode，会报错

2）JOIN...ON

FROM 表1 JOIN 表2 ON 表一.字段名1<比较运算符>表2.字段名2

使用关键词JOIN...ON查询各学科开设的课程，显示学科号、学科名和课程名。

SELECT subject.SubjectCode,SubjectName,CourseName

FROM subject JION course

ON subject.SubjectCode=course.SubjectCode;

（2）外连接

查询结果不仅包含内连接匹配的行，还包含某个表等多信息

FROM 表1 LEFT|RIGHT[OUTER]JOIN 表2

ON 表1.字段名1<比较运算符>表2.字段名2 --OUTER可省略

1）LEFT JOIN：左表全部，右表匹配

2）RIGHT JOIN：右表全部，左表匹配

分别用内连接、左外连接和右外连接查询女生选课信息：

内连接：

SELECT student.StudentCode,StudentName,CourseCode,Score

FROM student JOIN courseenroll

ON student.StudentCode=course.StudentCode

WHERE Gender='女';

左连接：

SELECT student.StudentCode,StudentName,CourseCode,Score

FROM student LEFT JOIN courseenroll

ON student.StudentCode=course.StudentCode

WHERE Gender='女';

 右连接：

SELECT student.StudentCode,StudentName,CourseCode,Score

FROM student RIGHT JOIN courseenroll

ON student.StudentCode=course.StudentCode

WHERE Gender='女';

（3）多表连接查询：

如果查询所涉及的表的数量在3个以上，则形成多表连接查询。

FROM 表1 JOIN 表2 ON 表1.字段名i<比较运算符>表2.字段j

JOIN 表3 ON 表x.字段k<比较运算符>表3.字段l

...

[JOIN 表n ON 表y.字段m<比较运算符>表n.字段q]     x<=2,y<=n-1

查询所有女生的选课信息，显示学生名、课程名和成绩

SELECT StudentName,CourseName,Score

FROM student JOIN courseenroll ON student.StudentCode=courseenroll.StudentCode

JOIN course ON courseenroll.CourseCode=course.CourseCode

WHERE Gender='女';

(4)子查询

查询与“章咪咪”来自同一地区的学生的学号和姓名

SELECT StudentCode,StudentName

FROM student

WHERE Location=

(SELECT Location FROM student WHERE StudentName='章咪咪');

查询选修了课程C001的学生的学号和姓名

SELECT StudentCode,StudentName

FROM student 

WHERE StudentCode IN 

(SELECT StudentCode FROM courseenroll WHERE CourseCode='C001');

或者也可以：

SELECT StudentCode,StudentName

FROM student JOIN courseenroll

ON student.StudentCode=courseenroll.StudentCode

WHERE CourseCode='C001';

提示：多数情况下，包含子查询的语句可以用连接表示；但子查询可以计算一个变化的聚集函数值，并返回主查询进行比较，而连接是做不到的。

查询年龄最大的学生的学号和姓名

SELECT StudentCode,StudentName,Birthday

FROM student

WHERE Birthday IN

(SELECT MIN(Birthday) FROM student);

或者

SELECT StudentCode,StudentName,Birthday

FROM student

ORDER BY Year(Birthday) ASC

LIMIT 1;

#最后两行可以直接WHERE MIN(Birthday)吗？

查询所有未选修任何课程的学生

SELECT StudentCode,StudentName

FROM student

WHERE NOT EXISTS 

(SELECT *FROM courseenroll

WHERE student.StudentCode=courseenroll.StudentCode);

4.数据更新

（1）数据插入

INSERT INTO

1）向表中插入一条数据记录

INSERT INTO 表[(字段1，字段2，...)]

VALUES(表达式1,表达式2,...);

向subject表中插入一条记录，学科号为S10、学科名为“哲学”

INSERT INTO subject

VALUES('S10','哲学');

提示：对于非空字段，若未设默认值，则必须插入语句中给值

若字段设为整型且“自动递增”，则添加新纪录时，可以不给它赋值

2）可以将查询结果复制到一个新表中

将student表中所有男生的数据复制到一个新表malestudent中

CREATE TABLE malestudent

SELECT *

FROM student

WHERE Gender='男';

3）从其他表中提取一组记录插到目标表中

INSERT INTO 表[(字段名1,字段名2...)]

SELECT语句;

向malestudent表中插入记录，数据为student表中所有女生的信息

INSERT INTO malestudent

SELECT * FROM student

WHERE Gender='女';

（2）数据修改

使用UPDATE语句：

UPDATE 表 SET 字段1=表达式1[,字段2=表达式2,...]

[WHERE 更新条件]

修改student表中学号为1018的联系电话为18931000978

UPDATE student SET Phone='18931000978'

WHERE StudentCode=1018

修改course表，将学科名为“计算机”的学分增加0.5，学时增加10%

UPDATE course SET Credit=Credit+0.5,Hours=Hours*(1+10%)

WHERE CourseCode=

(SELECT CourseCode FROM subject WHERE SubjectName='计算机');

(3)数据删除

DELETE语句：

DELETE FROM 表

[WHERE 删除条件]

删除subject表中的“哲学”记录

DELETE FROM subject

WHERE SubjectName='哲学';

从subject表中删除没有开过课的学科记录

DELETE FROM subject 

WHERE SubjectCode NOT IN

(SELECT SubjectCode FROM course);--子查询

5.可编程对象

SQL运算和常用函数

1.标识符、常量和变量

变量：1）用户变量：用户定义的变量：@+标识符，默认值为NULL，类型为字符串类型，赋值后变量类型与值的类型一致。

用SET或SELECT语句为其赋值

SET @City='上海';

SELECT @City='上海';

2）局部变量：一般用在SQL语句中，在该语句块执行完毕后，局部变量就会消失。局部变量名称前无@，得先DECLARE声明

DECLARE 局部变量名 数据类型 [DEFAULT 默认值];

例子：

DECLARE Score DEFAULT 60;

3）默认系统变量：

@@可用SHOW 查看当前值。

SELECT @@version；

SHOW status like 'Max_used_connections';

2.运算符和表达式

3.常用函数

SQL流程控制语句

6.视图

1.创建视图

不能跨数据库创建视图

视图名称不能与数据库中任何其他对象重名

CREATE VIEW 视图名称

AS

SELECT 查询语句

创建男生视图view_studentmale，包括学生学号、姓名、性别、出生日期

CREATE VIEW view_studentmale

AS

SELECT StudentCode 学号,StudentName 姓名,Gender 性别,Birthday 出生日期

FROM student WHERE Gender='男';

2.使用视图

1）查询操作

使用视图view_studentscore统计”多媒体技术及应用“课程的平均分

SELECT  课程名,AVG(成绩) 平均分

FROM view_studentscore

GROUP BY 课程名

WHERE 课程名='多媒体技术及应用';

2）更新操作

使用了运算或聚合函数，以及DISTINCT、GROUP BY 等语句的视图不可进行更新操作

无论插入、修改和删除操作，一次只能操作一个基本表中的数据

插入操作必须包含视图应用的基本表的所有不能为空的列；删除操作只能在基于单表定义的视图上进行。

例子：

INSERT INTO view_studentmale VALUE (1104,"赵谦","男","2000-12-12");

3.维护视图

7.存储过程

完成一定数据访问和处理功能的SQL语句块，是一种数据库对象。

优点：

1）在一个存储过程中执行多条SQL语句；

2）可以带有输入参数调用存储过程动态执行

3）存储过程在创建时就在服务器端进行了编译，节省SQL语句的运行时间

4）提供了一种安全机制，可以限制用户执行SQL语句，只允许其访问存储过程

缺点：

移植性差

1.创建和调用存储过程

1）使用SQL语句创建存储过程

CREATE PROCEDURE 存储过程名([形式参数列表])

SQL语句段;

调用：

CALL 存储过程名（实参值|@变量）;

CREATE PROCEDURE proc_student();

SELECT * FROM student;

CREATE PROCEDURE proc_searchstudent(IN stname varchar(16))

SELECT * FROM student WHERE StudentName=stname;

2.储存过程的创建和应用实例

CREATE PROCEDURE proc_countstudent(IN cscode char(4),OUT stnumber int)

SELECT COUNT(StudentCode) into stnumber FROM courseenroll

WHERE Score IS NOT NULL AND CourseCode=cscode;

CALL proc_countstudent('C001',@st);

SELECT @st AS '选修C001课程的学生人数：';

CREATE PROCEDURE proc_insertsubject(sjcode char(3),sjname varchar(10))

INSERT INTO subject VALUES(sjcode,sjname);

CALL proc_insertsubject('S18','医学');

CREATE PROCEDURE proc_updatescore(stcode int,cscode char(4),sc float)

UPDATE courseenroll;

SET Score=sc

WHERE StudentCode=stcode AND CourseCode=cscode;

3.维护存储过程

SELECT * FROM student WHERE StudentName LIKE sname;

8.触发器

9.数据控制语言

1.新建用户

 --创建了一个名为：test 密码为：1234 的用户
 create user 'test'@'localhost' identified by '1234';

注意：
此处的"localhost"，是指该用户只能在本地登录，不能在另外一台机器上远程登录。如果想远程登录的话，将"localhost"改为"%"，表示在任何一台电脑上都可以登录。也可以指定某台机器可以远程登录。

2.查询用户

--查询用户
select user,host from mysql.user;

3.删除用户

--删除用户“test”
drop user test@localhost ;
--若创建的用户允许任何电脑登陆，删除用户如下
drop user test@'%';

4.更改密码

--方法1，密码实时更新；修改用户“test”的密码为“1122”
set password for test =password('1122');
--方法2，需要刷新；修改用户“test”的密码为“1234”
update  mysql.user set  password=password('1234')  where user='test'
--刷新
flush privileges;

5.用户分配权限

--授予用户test通过外网IP对数据库“testdb”的全部权限
grant all privileges on 'testdb'.* to 'test'@'%' identified by '1234';  

--刷新权限
flush privileges; 

--授予用户“test”通过外网IP对于该数据库“testdb”中表的创建、修改、删除权限,以及表数据的增删查改权限
grant create,alter,drop,select,insert,update,delete on testdb.* to test@'%';	 

6.查看用户权限

--查看用户“test”
show grants for test;

注意：修改完权限以后 一定要刷新服务，或者重启服务，刷新服务用：flush privileges;

参考资料：

刘晓强，李东方，黄雅萍，李柏岩，冯珍妮. 数据库应用系统技术. 电子工业出版社. ISBN：9787121355097。

mysql 创建数据库,添加用户,用户授权 - 山中自有人 - 博客园 (cnblogs.com)