1.什么是SQL
我们将大量数据保存起来,通过计算机加工而成的可以进行高效访问的数据集合称为数据库(Database,DB),用来管理数据库的计算机系统叫数据库管理系统(Database Management System,DBMS)。其中,数据库根据结构可以分为三类(从左至右):层次模型、网状模型和关系模型。
关系模型将数据都看作是一个二维的表格,所有的数据都能根据行+列来唯一确定。例如,我们可以用一个表格来存储一个班级的学生信息,用一个表格来存储老师和班级的对应关系,从而将老师和学生对应起来。
SQL,英文全称为Structured Query Language,它是对关系型数据库的操作语言,用于查询、添加、更新和删除数据库中的数据,并进行管理和维护。SQL语法具备一些要求,如:SQL 语句可以单行或多行书写,以分号结尾;关键字不区别大小写(但建议大写);可以用空格和缩进来来增强语句的可读性;用-- 来注释语句等等。
SQL的常用数据类型如下:
2.SQL语句的分类
根据指令类别的不同,SQL语句可以分为以下四类:
DDL:全称为Data Definition Language,数据定义语言,用来创建或删除数据库对象:库、表、列等;
DML:全称为Data Manipulation Language,数据操作语言,用来定义数据库记录(数据);
DQL:全称为Data Query Language,数据查询语言,用来查询记录(数据);
DCL:全称为Data Control Language,数据控制语言,用来定义访问权限和安全级别。
3.DDL(Data Definition Language):数据定义语言
3.1 数据库
创建数据库:CREATE DATABASE mydatabase1(如果已经有一个mydatabase1存在,会报错,为了避免报错,可以修改语句为 CREATE DATABASE IF NOT EXISTS mydatabase1;)。
删除数据库:DROP DATABASE mydatabase1(如果已经有一个mydatabase1存在,会报错,为了避免报错,可以修改语句为 DROP DATABASE IF EXISTS mydatabase1;)。
修改数据库编码:ALTER DATABASE mydatabase1 CHARACTER SET utf8,将mydatabase1数据库的编码修改为utf8。
3.2 表
创建表:CREATE TABLE mytable1(列名 列类型,列名 列类型,...);
查看表的结构:DESC mytable1;
删除表:DROP TABLE mytable1;
修改表:
修改表的名称:ALTER TABLE mytable1 RENAME TO mytable2;
添加列:ALTER TABLE mytable1 ADD (列名 列类型);
删除列:ALTER TABLE mytable1 DROP (列名);
修改列名:ALTER TABLE mytable1 CHANGE 原列名 新列名 列类型;
修改列数据类型:ALTER TABLE mytable1 MODIFY 列名 想修改的列的类型。
4.DML(Data Manipulation Language):数据操作语言
4.1 插入数据
INSERT INTO mytable1(列名1,列名2,...) VALUES(值1,值2,...);
其中,如果没有指定列名,则插入的值按照创建表时列的顺序进行插入列值。此外,所有字符串数据需要使用单引号。
4.2 删除数据
DELETE FROM mytable1 WHERE 条件1;
即删除mytable1中满足条件1的相关记录,如果不加WHERE 条件1,则意味着清空表。
4.3 更新数据
UPDATE mytable1 SET 列名1=值1,列名2=值2,... 列名n=值n WHERE 条件1;
即将mytable1中满足条件1的列名1改成值1,列名2改成值2...如果不加WHERE,则意味着对整个表进行修改。
5.DQL(Data Query Language):数据查询语言
DQL不会对数据库中的数据进行改变,而只会将查到的目标数据发送到客户端。DQL的基本语句如下:
要查询的列名:SELECT 列名1,列名2,...列名n;(如果需要查询所有列,则用SELECT *)
要查询的表名:FROM 表名1;
要查询的条件:WHERE 条件1;
对查询结果进行限制:ORDER BY 排序列;GROUP BY 分组列;LIMIT 起始行,行数;HAVING 分组条件
为了直观理解,创建一个具体的数据库school_info,该数据库下有两个不同的表:
学生表stu:
列名 | 数据类型 | 意义 |
---|---|---|
stu_id | char(5) | 学生的学号 |
stu_name | varchar(20) | 学生姓名 |
stu_age | int | 学生年龄 |
stu_gender | varchar(20) | 学生性别 |
class_id | char(3) | 班级号 |
班级表class:
列名 | 数据类型 | 意义 |
---|---|---|
class_id | char(3) | 班级号 |
tea_name | varchar(20) | 老师姓名 |
class_name | char(50) | 班级名称 |
5.1 基础查询
查询某表所有列:SELECT * FROM stu;
查询某表特定列:SELECT stu_name,stu_gender FROM stu;
查询时,还可以增加一些新列:SELECT stu_name,stu_gender,class_id + 1 AS new_clss_id FROM stu;
5.2 条件查询
条件查询会使用WHERE子句,子句的条件可以用一些运算符或关键字,如:
①=、!=、<>、<、<=、>、>=;(查询数据是/不是某个值的记录)
②BETWEEN ... AND ...;(查询数据处于值1和值2之间的记录)
③IN 某个集;(查询数据在某个集的记录)
④IS NULL;(查询数据为空的记录)
⑤AND、OR、NOT;(查询满足多个条件的记录)
具体例子:
①查询stu表中年龄16-20之间的记录:
SELECT * FROM stu WHERE stu_age BETWEEN 16 AND 20;
②查询stu表中性别为女,且属于201班的记录:
SELECT * FROM stu WHERE stu_gender = 'female' AND class_id = 201;
或者SELECT * FROM stu WHERE stu_gender != 'male' AND class_id = 201;
③查询stu表中学号为S1001,S1002,S1003的记录:
SELECT * FROM stu WHERE stu_id IN ('S1001','S1002','S1003');
或者SELECT * FROM stu WHERE stu_id NOT IN ('S1004','S1005','S1006');
5.3 模糊查询
MySQL中模糊查询可以用LIKE和REGEXP两种,REGEXP更有效简洁。REGEXP的参数类型有:
参数类型 | 作用 |
---|---|
(^) | ^a表示以字母a开头的字符串 |
($) | a$表示以字母a结尾的字符串 |
(.) | 它匹配任意一个字符,包括回车或者换行 |
(*) | 匹配0个或者多个字符,在它之前必须有内容 |
(+) | 匹配1个或者多个字符,在它之前必须有内容 |
(?) | 匹配0次或1次 |
| | 表示‘或者’ |
具体例子:
①查询stu表中姓名以ge结尾的记录:
SELECT * FROM stu WHERE stu_name REGEXP 'ge$';
②查询stu表中姓名含有z的记录:
SELECT * FROM stu WHERE stu_name REGEXP 'z';
③查询stu表中姓名以z开头或以e结尾的记录:
SELECT * FROM stu WHERE stu_name REGEXP '^z|e$';
5.4 排序、分组和限定行数查询
排序查询:将查询结果按照某列升序(ASC)或者降序(DESC)排列,一般用ORDER BY语句。
具体例子:
①将查询到的学生按照年龄升序排序:
SELECT stu_name,stu_gender,stu_age FROM stu ORDER BY stu_age ASC;
②将查询到的学生按照年龄降序排序,如果年龄一样,按照班级班号升序排序:
SELECT stu_name,stu_gender,stu_age FROM stu ORDER BY stu_age DESC, class_id ASC;
分组查询:将查询到的结果按照分组展现出来,一般用GRUOP BY语句。
具体例子:
①查询每个班的班号及班级人数:
SELECT class_id,COUNT(*) FROM stu GROUP BY class_id;
②HAVING语句的用法:
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;
限定行数查询:用于限定查询的总行数,及起始行数
查询stu表中的三行记录,从第一行开始:
SELECT * FROM stu LIMIT 1,3;
5.5 聚合函数
聚合函数包括如下:
使用方法为:SELECT COUNT(*) AS cnt FROM stu;
5.6 多表联查
实际应用大多数场景会涉及到多张表的查询,多张表的查询方法有三类:
内连接:JOIN, INNER JOIN
外连接:LEFT JOIN, LEFT OUTER JOIN, RIGHT JOIN, RIGHT OUTER JOIN, UNION
交叉连接:CROSS JOIN
①内连接
选取来自stu和class两个表中class_id相同的记录
SELECT * FROM stu, class WHERE stu.class_id=class.class_id;
②外连接
左连接:包含左边表中所有的记录,右边表中没有匹配的记录显示为 NULL
SELECT stu.*, class.* FROM stu LEFT JOIN class ON stu.class_id = class.class_id;
右连接:包含右边表中所有的记录,左边表中没有匹配的记录显示为 NULL
SELECT stu.*, class.* FROM stu RIGHT JOIN class ON stu.class_id = class.class_id;
外连接过程中,仍然可以在末尾加上WHERE语句用于附加一些条件。
③交叉连接
想要得到两个表记录的排列组合,即笛卡尔积,得到的行数是两张表行数之积。
SELECT stu.*,class.* FROM stu CROSS JOIN class;
6.DCL(Data Control Language):数据控制语言
6.1 用户
创建用户:
修改用户:
修改密码:
删除用户:
6.2 权限
授权:
查看权限:
SHOW grants FOR 'alian'@'%';
回收权限: