文章目录
1.DDL
DDL(Data Definition Language):数据定义语言,用来定义数据库对象:库、表、列等;
常用的语句关键字有 CREATE(创建)、DROP(删除)、ALTER (修改)等**
1.1操作数据库
创建数据库:
#语法:CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARACTER SET 字符集 COLLATE 排序规则字符集];
#创建数据库:
CREATE DATABASE [IF NOT EXISTS] mydb1;
这里的 IF NOT EXISTS是判断数据库是否存在;
DEFAULT CHARACTER: 默认的字符集, 如果没有设置,默认是utf8;推荐使用utf8mb4;
COLLATE: 排序规则, 如果没有设置,默认是: utf8_general_ci;推荐使用uft8mb4_bin.
标准创建数据库语句:
CREATE DATABASE IF NOT EXISTS mydb2 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
删除数据库语句:DROP DATABASE [IF EXISTS] mydb1;
修改数据库语句 :ALTER DATABASE mydb1 CHARACTER SET utf8;
这里修改数据库mydb1的编码为utf8。
要注意的是,在MySQL中所有的UTF-8编码都不能使用中间的“-”,即UTF-8要书写为UTF8。
1.2操作表
创建表语句:
CREATE TABLE 表名(
列名 列类型,
列名 列类型,
......
);
创建学生表:
CREATE TABLE tb_stu (
sid CHAR(6),
sname VARCHAR (20),
age INT,
gender VARCHAR (10)
) ENGINE=INNODB CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT '学生表' ;
上面我建了一个名为tb_stu的表.
ENGINE=INNODB CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT ‘学生表’; 语句是用来设置表的使用的数据库引擎, 默认字符集,默认排序规则:
2.DML
DML(Data Manipulation Language):数据操作语言,用来定义数据库记录(数据);
要是对数据进行增加、删除、修改操作。常用的语句关键字有 INSERT(插入)、UPDATE(更新)、
DELETE()删除 等。
2.1插入数据
插入数据的指定语法:
INSERT INTO 表名(列名1,列名2, …) VALUES(值1, 值2);
下面我们来对tb_stu插入数据:
INSERT INTO tb_stu (sid, sname, age, gender)
VALUES
('s_1001', 'zhangSan', 23, 'male');
这里要注意的是要记得自己创建表所设置各个列名的数据类型;
2.2修改数据
修改数据的指定语法:
UPDATE 表名 SET 列名1=值1, … 列名n=值n [WHERE 条件];
下面我们来对tb_stu修改数据:
UPDATE tb_ stu SET sname='zhangSanSan', age='32', gender='female' WHERE sid='s_1001';
UPDATE tb_stu SET sname='wangWu', age='30' WHERE age>60 OR gender='female';
UPDATE tb_stu SET sname='liSi', age='20' WHERE age>50 AND gender='male';
UPDATE tb_stu SET gender='female' WHERE gender IS NULL;
UPDATE tb_stu SET age=age+1 WHERE sname='zhaoLiu';
2.3删除数据
删除数据的指定语法:
DELETE FROM 表名 [WHERE 条件];
下面我们来对tb_stu删除数据:
DELETE FROM tb_stu WHERE sid='s_1001';
DELETE FROM tb_stu WHERE sname='chenQi' OR age > 30;
--删除所有数据 慎用
DELETE FROM stu;
3.DCL
DCL(Data Control Language):数据控制语言,用来定义访问权限和安全级别;
主要是用来设置/更改数据库用户权限。常用关键字有 GRANT(授权)、REVOKE(撤销) 等。
一般人员很少用到DCL语句。所有这里就不多讲DCL
4.DQL
DQL(Data Query Language):数据查询语言,用来查询记录(数据)。
主要是对数据进行查询操作。常用关键字有 SELECT、FROM、WHERE 等。
4.1基础查询
查询所有的列:
SELECT * FROM stu;
查询指定的列:
SELECT 字段1, 字段2, 字段3 ... FROM 表名 ;
SELECT sid, sname, age FROM stu;
4.2条件查询
实际开发运用中,条件查询可以说是是用最多的查询.
总的来说,条件查询就是在查询时给出WHERE子句,在WHERE子句中可以使用如下运算符及关键字:
关键字
- =、!=、<>(不等于)、<、<=、>、>=
- BETWEEN…AND
- IN(set)
- IS NULL //null与任何值比较计算,结果永远为空
- AND
- OR
- NOT
逻辑运算符
AND 或 && 并且 (多个条件同时成立)
OR 或 || 或者 (多个条件任意一个成立)
NOT 或 ! 非 , 不是
下面我们来写几条条件查询语句:
1.查询性别为女,并且年龄50的记录:
SELECT * FROM tb_stu WHERE gender = 'female' AND ge < 50 ;
2.查询学号不是S_1001,S_1002,S_1003的记录:
SELECT * FROM tb_student WHERE s_number NOT IN ('S_1001','S_1002','S_1003');
3.查询年龄在20到40之间的学生记录:
SELECT * FROM tb_stu WHERE age BETWEEN 20 AND 40;
4.3模糊查询
1.查询姓名由5个字母构成,并且第5个字母为“i”的学生记录
SELECT * FROM tb_stu WHERE sname LIKE '____i';
2.查询姓名以“z”开头的学生记录
SELECT * FROM tb_stu WHERE sname LIKE 'z%';
这里要注意的是"%"的运用是匹配0~n个任何字母
3.查询姓名中包含“a”字母的学生记录
SELECT * FROM tb_stu WHERE sname LIKE '%a%';
4.4字段控制查询
1.去除重复记录
当出现两行或两行以上记录中系列的上的数据都相同,我们可以用DISTINCT来进行去除重复,进行查询
SELECT DISTINCT 列名 FROM 表名;
2.当表中的列名有数据类型相同都是数值类型时,我们可以对数据类型相同都是数值类型的列名进行运算,查看雇员的月薪与奖金之和
SELECT *,sal+IFNULL(comm,0) FROM emp;
这里要注意的是,实际中并不是每个人都有comm(奖金),任何东西与NULL相加结果还是NULL,所以结算结果可能会出现NULL。所以使用了把NULL转换成数值0的函数IFNULL进行相加
4.5排序
排序语法:
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ,...;
排序方式
- ASC : 升序(默认值)
- DESC: 降序
注意事项:
• 如果是升序, 可以不指定排序方式ASC ;
• 如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序 ;
1.查询所有学生记录,按年龄升序排序
SELECT * FROM stu ORDER BY sage ASC;
2.查询所有学生记录,按年龄降序排序
SELECT * FROM stu ORDER BY age DESC;
3.查询所有雇员,按月薪降序排序,如果月薪相同时,按编号升序排序
SELECT * FROM emp ORDER BY sal DESC,empno ASC;
4.6聚合函数
函数 | 描述 |
---|---|
COUNT() | 统计指定列不为NULL的记录行数 |
MAX() | 计算指定列的最大值,如果指定列是字符串类型,那么使用字符串排序运算 |
MIN() | 计算指定列的最小值,如果指定列是字符串类型,那么使用字符串排序运算 |
SUM() | 计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0 |
AVG() | 计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0 |
聚合语法:
SELECT 聚合函数(字段列表) FROM 表名 ;
注意:NULL是不参与所有聚合函数的运算,所有实际中我们要用IFNULL对NULL进行处理;
聚合函数里面不能再嵌套聚合函数,比如不能SUM(MIN());
1.查询tb_stu表中记录数
SELECT COUNT(*) AS cnt FROM tb_stu;
这里AS后面的cnt是一个别名,即对查询表中记录数的结果起的别名;
4.7分组查询
当需要分组查询时需要使用GROUP BY子句,例如查询每个部门的工资和,这说明要使用部分来分组。
语法:
SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组 后过滤条件 ];
1.查询学生的编号以及每个学生年龄大于20的人数:
SELECT sid FROM tb_stu WHERE age > 20 GROUP BY sid;
2.查询学生年龄大于20的学生编号以及性别为女
SELECT sid,gender FROM tb_stu GROUP BY age>20 HAVING gender='female';
where与having区别
执行时机不同:where是分组之前进行过滤(即整张表进行筛选,位于group by前面,独立存在),不满足where条件,不参与分组;而having是分组后的筛选(位于group by后面且必须跟着group by)之后对结果进行过滤。
判断条件不同:where不能对聚合函数进行判断,而having可以。
注意事项:
分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义。
执行顺序: where > 聚合函数 > having 。
支持多字段分组, 具体语法为 : group by columnA,columnB
WHERE是对分组前记录的条件,如果某行记录没有满足WHERE子句的条件,那么这行记录不会参加分组;而HAVING是对分组后数据的约束。
4.8分页查询
分页操作在业务系统开发时,也是非常常见的一个功能,我们在网站中看到的各种各样的分页条,后台
都需要借助于数据库的分页操作。MySQL使用LIMIT用来限定查询结果的起始行,以及总行数。
语法:
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;
注意事项:
起始索引从0开始,起始索引 = (查询页码 - 1)* 每页显示记录数。
分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT。
如果查询的是第一页数据,起始索引可以省略,直接简写为 limit 10。
1.查询5行记录,起始行从0开始
SELECT * FROM emp LIMIT 0, 5;
或者
SELECT * FROM emp LIMIT 5;
2.查询10行记录,起始行从3开始
SELECT * FROM emp LIMIT 3, 10;