26.2 SQL介绍、通用语法、SQL语句分类、DDL 操作数据库和表、DML:表实现增删改数据、DQL:条件查询、模糊查询、排序、聚合函数、分组、分页、数据库备份与还原

目录

1 SQL结构化查询语言

2 SQL通用语法

3 SQL 语句分类

4 DDL 操作数据库、表

4.1  DDL操作数据库(数据库CRUD)

DDL操作数据库-- C(Create):创建数据库

DDL操作数据库-- R(Retrieve):查询数据库

DDL操作数据库-- U(Update):修改数据库

DDL操作数据库-- D(Delete):删除数据库

DDL操作数据库-- 使用数据库

4.2  DDL操作表(数据表CRUD)

DDL操作表-- C(Create):创建表

DDL操作表-- R(Retrieve):查询表

DDL操作表-- U(Update):修改表

DDL操作表-- D(Delete):删除表

DDL操作表-- 复制表

5 DML:表实现增删改数据

5.1 DML-- 表添加数据

5.2 DML-- 表删除数据

5.3 DML-- 表修改数据

6 DQL:查询表中的记录

6.1 DQL-- 基础查询

6.2 DQL-- 查询结果去重:distinct

6.3 DQL-- 查询结果参与计算

6.4 DQL-- 条件查询

 DQL-- 条件查询之比较运算符

 DQL-- 条件查询之逻辑运算符

 DQL-- 条件查询之in 关键字

DQL-- 条件查询之范围查询

DQL-- 条件查询之模糊查询:like 关键字

6.5 DQL-- 排序 order by

6.6 DQL-- 聚合函数

6.7 DQL-- 分组 group by

 having 与 where 的区别

6.8 DQL-- 分页 limit 

7 数据库备份和还原

7.1 DOS下备份和还原

 7.2 图形化界面(SQLyog)下备份和还原


1 SQL结构化查询语言

Structured Query Language:结构化查询语言。其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方,称为“方言”。

2 SQL通用语法

    1. SQL 语句可以单行或多行书写,以分号结尾。
    2. 可使用空格和缩进来增强语句的可读性。
    3. MySQL 数据库的 SQL 语句不区分大小写,关键字建议使用大写。
    4. 3 种注释
        * 单行注释: -- 注释内容 或 # 注释内容(mysql 特有) 
        * 多行注释: /* 注释 */

3 SQL 语句分类

    1) DDL(Data Definition Language)数据定义语言
        用来定义数据库对象:数据库,表,列等。关键字:create, drop,alter 等
    2) DML(Data Manipulation Language)数据操作语言
        用来对数据库中表的数据进行增删改。关键字:insert, delete, update 等
    3) DQL(Data Query Language)数据查询语言
        用来查询数据库中表的记录(数据)。关键字:select, where 等
    4) DCL(Data Control Language)数据控制语言(了解)
        用来定义数据库的访问权限和安全级别,及创建用户。关键字:GRANT, REVOKE 等


4 DDL 操作数据库、表

4.1  DDL操作数据库(数据库CRUD)

DDL操作数据库-- C(Create):创建数据库

		-- 创建数据库:
		 create database 数据库名称;
		-- 创建数据库,判断不存在,再创建:
		 create database if not exists 数据库名称;
		-- 创建数据库,并指定字符集
		 create database 数据库名称 character set 字符集名;

练习: 创建db4数据库,判断是否存在,并制定字符集为gbk 

create database if not exists db4 character set gbk;

DDL操作数据库-- R(Retrieve):查询数据库

		-- 查询所有数据库的名称:
			 show databases;
		-- 查询某个数据库的字符集:查询某个数据库的创建语句
			 show create database 数据库名称;

DDL操作数据库-- U(Update):修改数据库

		-- 修改数据库的字符集
			 alter database 数据库名称 character set 字符集名称;

DDL操作数据库-- D(Delete):删除数据库

		-- 删除数据库
			 drop database 数据库名称;
		-- 判断数据库存在,存在再删除
			 drop database if exists 数据库名称;

DDL操作数据库-- 使用数据库

		-- 查询当前正在使用的数据库名称
			 select database();
		-- 使用数据库
			 use 数据库名称;

4.2  DDL操作表(数据表CRUD)

DDL操作表-- C(Create):创建表

 数据类型:

 数据类型描述
int整数类型   age int,
double小数类型   score double(5,2)
varchar字符串    name varchar(20):姓名最大20个字符
date:日期只包含年月日,yyyy-MM-dd
datetime:日期包含年月日时分秒     yyyy-MM-dd HH:mm:ss
timestamp:时间戳类型

包含年月日时分秒     yyyy-MM-dd HH:mm:ss    

如果将来不给这个字段赋值,或赋值为null,则默认使用当前的系统时间,来自动赋值

语法:最后一列,不需要加逗号(,)

			create table 表名(
				列名1 数据类型1,
				列名2 数据类型2,
				....
				列名n 数据类型n
			);

列:创建表 

CREATE TABLE student(
	id INT,
	NAME VARCHAR(20),
	age INT,
	score DOUBLE(4,2),
	birthday DATE,
	insert_time TIMESTAMP -- 时间戳类型
);

CREATE TABLE student3 (
	id INT, -- 编号
	NAME VARCHAR(20), -- 姓名
	age INT, -- 年龄
	sex VARCHAR(5), -- 性别
	address VARCHAR(100), -- 地址
	math INT, -- 数学
	english INT -- 英语
) DEFAULT CHARSET utf8;

 

DDL操作表-- R(Retrieve):查询表

        -- 查询某个数据库中所有的表名称
			 show tables;
		-- 查询表结构
			 desc 表名;

列:练习查询表结构

 

DDL操作表-- U(Update):修改表

1. 修改表名

alter table 表名 rename to 新的表名;  列:ALTER TABLE student  RENAME TO stu;

2. 修改表的字符集

alter table 表名 character set 字符集名称;   列:ALTER TABLE student  CHARACTER SET utf8;

3. 添加一列

alter table 表名 add 列名 数据类型;

4. 修改列名称 类型(重点)

alter table 表名 change 列名 新列名 新数据类型;    ALTER TABLE student CHANGE NAME myName VARCHAR(20);
alter table 表名 modify 列名 新数据类型;     ALTER TABLE student MODIFY id VARCHAR(2);-- 修改数据类型 

5. 删除列

alter table 表名 drop 列名;

DDL操作表-- D(Delete):删除表

drop table 表名;
drop table  if exists 表名 ;

DDL操作表-- 复制表

create table 表名 like 被复制的表名;

5 DML:表实现增删改数据

5.1 DML-- 表添加数据

除了数字类型,其他类型需要使用引号(单双都可以)引起来

insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);        INSERT INTO student (id,myName,age,score,birthday) VALUES(1,'老孙',19,88.00,'2019-07-7');    
insert into 表名 values(值1,值2,...值n);

 列:练习添加数据

-- 创建student表
CREATE TABLE student(
	id INT,
	myname VARCHAR(20),
	age INT,
	score DOUBLE(4,2),
	birthday DATE,
	insert_time TIMESTAMP -- 时间戳类型
);
-- 添加数据
INSERT INTO student (id,myName,age,score,birthday) VALUES(1,'老孙',19,88.00,'2019-07-7');
INSERT INTO student VALUES(2,'老孙',19,88.00,'2019-07-7',NULL);	

5.2 DML-- 表删除数据

delete from 表名 -- 删除表中所有数据
delete from 表名 where 字段名= 值   -- 带条件删除数据
truncate table 表名  -- 删除表中所有数据

 delete与truncate区别:truncate 相当于删除表的结构,再创建一张表

5.3 DML-- 表修改数据

1     update  表名 set  字段名= 值; --  修改所有的行
2     update  表名 set  字段名= 值 WHERE  字段名= 值
3     update 表名 set 列名1 = 值1, 列名2 = 值2,... 
4     update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件]
1.1     UPDATE student SET age=20;
2.1     UPDATE student SET age=333 WHERE age=11;
4.1     UPDATE student SET age=20,sex='女',myName='老李' WHERE id=3;

6 DQL:查询表中的记录

6.1 DQL-- 基础查询

1    select * from 表名; -- 查询所有字段
     select * from student;    -- 查询所有的学生

2    select 字段名1,字段名2,字段名3... from 表名;
     select name,age from student;    -- 查询 student 表中的 name 和 age 列

3    SELECT  字段名 1 AS  别名,  字段名 2 AS  别名... FROM  表名 AS  表别名;
     select name as 姓名,age as 年龄 from student;    -- 对列和表同时指定别名

4    - 表使用别名
     select st.name as 姓名,age as 年龄 from student as st

6.2 DQL-- 查询结果去重:distinct

select distinct 字段名 from 表名;
SELECT DISTINCT age FROM student;    -- 查询student表中的age并去重

6.3 DQL-- 查询结果参与计算

1 SELECT  列名 1 +  固定值 FROM ; 
SELECT math+5 FROM student;    -- 查询结果参与计算

2 SELECT  列名 1 +  列名 2 FROM  表名;
SELECT math+english FROM student;    -- 查询math与English的和
SELECT *,(math+english) AS 总成绩 FROM student;
SELECT *,(math+english)  总成绩 FROM student;    -- as 可以省略

6.4 DQL-- 条件查询

格式:

SELECT  字段名 FROM  表名 WHERE  条件;

准备数据

CREATE TABLE student3 (
	id INT, -- 编号
	NAME VARCHAR(20), -- 姓名
	age INT, -- 年龄
	sex VARCHAR(5), -- 性别
	address VARCHAR(100), -- 地址
	math INT, -- 数学
	english INT -- 英语
) DEFAULT CHARSET utf8;
INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES (1,'马云',55,'男','
杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),(4,'柳岩
',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港
',99,99),(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);

 DQL-- 条件查询之比较运算符

 

select * from student3 where math>80;
select * from student3 where english <=80;
select * from student3 where age = 20;

-- 查询 age 不等于 20 岁的学生,注:不等于有两种写法
select * from student3 where age <> 20;
select * from student3 where age != 20;

 DQL-- 条件查询之逻辑运算符

select * from student3 where age>35 and sex='男';-- 查询 age 大于 35 且性别为男的学生(两个条件同时满足)
select * from student3 where age>35 or sex='男';-- 查询 age 大于 35 或性别为男的学生(两个条件其中一个满足)
select * from student3 where id=1 or id=3 or id=5;-- 查询 id 是 1 或 3 或 5 的学生

 DQL-- 条件查询之in 关键字

SELECT  字段名 FROM  表名 WHERE  字段 in ( 数据 1,  数据 2...);-- in  里面的每个数据都会作为一次条件,只要满足条件的就会显示

select * from student3 where id in(1,3,5);-- 查询 id 是 1 或 3 或 5 的学生
select * from student3 where id not in(1,3,5);-- 查询 id 不是 1 或 3 或 5 的学生

DQL-- 条件查询之范围查询

SELECT  字段名 FROM  表名 WHERE BETWEEN 值 值 1 AND 值 值 2;

select * from student3 where english between 75 and 90;-- 查询 english 成绩大于等于 75,且小于等于 90 的学生

DQL-- 条件查询之模糊查询:like 关键字

SELECT * FROM  表名 WHERE  字段名 LIKE ' 通配符字符串';

--   _  匹配一个字符
SELECT * FROM student3 WHERE NAME LIKE '马_';-- 查询姓马,且姓名有两个字的学生
--   %  匹配任意多个字符串
SELECT * FROM student3 WHERE NAME LIKE '%马%';-- 查询姓名中包含'马'字的学生

6.5 DQL-- 排序 order by

-- 通过 ORDER BY 子句,可以将查询出的结果进行排序(排序只是显示方式,不会影响数据库中数据的顺序)
-- ASC: 升序,默认值    DESC: 降序
SELECT * FROM student3 ORDER BY math;-- 默认是升序排序
SELECT * FROM student3 ORDER BY math DESC;-- 按照数学成绩降序排序
SELECT * FROM student3 ORDER BY math DESC,english DESC;-- 按照数学成绩降序排序后在按照英语成绩降序排序

6.6 DQL-- 聚合函数

之前我们做的查询都是横向查询,它们都是根据条件一行一行的进行判断,而使用聚合函数查询是纵向查询, 它是对一列的值进行计算,然后返回一个结果值。聚合函数会忽略空值 NULL。 利用 IFNULL(列名,默认值)函数,记录为 NULL,给个默认值,这样统计的数据就不会遗漏

SQL中聚合函数
max(列名)
求这一列的最大值
 
min( 列名 )
求这一列的最小值
 
avg( 列名 )
求这一列的平均值
 
count( 列名 )
统计这一列有多少条记录
 
sum( 列名 )
对这一列求总和
 
-- 查询数学成绩最高分
SELECT MAX(math) FROM student3;
SELECT MAX(math) 最高分 FROM student3;

-- 英语成绩最低分
SELECT MIN(english) 最低分 FROM student3;-- 英语成绩用有null值时,不参与计算

-- 英语成绩平均分
SELECT AVG(english) 平均分 FROM student3;
SELECT AVG(IFNULL(english,0)) FROM student3;-- IFNULL(列名,默认值)如果记录为 NULL,给个默认值

-- 英语成绩总分
SELECT SUM(english) 总分 FROM student3;

-- 英语成绩有多少条数据
SELECT COUNT(english) FROM student3;
SELECT COUNT(*) FROM student3 WHERE english>60;-- 英语成绩大于60的有多少人

-- 计算总人数
SELECT COUNT(english) AS 总人数 FROM student3;  -- 因为有一个人成绩为null,所以计算人数不准确
SELECT COUNT(IFNULL(id,0)) AS 总人数 FROM student3;
SELECT COUNT(*) AS 总人数 FROM student3;

6.7 DQL-- 分组 group by

将分组字段结果中相同内容作为一组,如按性别将学生分成男生与女生 2 组。

-- 按性别进行分组,求男生和女生数学的平均分、总分
SELECT sex ,AVG(math),SUM(math) FROM  student3 GROUP BY sex;

 

SELECT sex,COUNT(*) FROM student3 GROUP BY sex;--  查询男女各多少人
SELECT sex,COUNT(*) FROM student3 WHERE age>25 GROUP BY sex;  -- 查询年龄大于 25 岁的人,按性别分组,统计每组的人数

-- group by 后面不能出现 where,使用 having
--查询年龄大于 25 岁的人,按性别分组,统计每组的人数,并只显示性别人数大于 2 的数据
SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex WHERE COUNT(*) >2;--错误写法
SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex having COUNT(*) >2;-- 正确写反

 having 与 where 的区别

where 子句:

  • 对查询结果进行分组前,将不符合 where 条件的行去掉,即在分组之前过滤数据,即先过滤 再分组。
  • where 后面不可以使用聚合函数

having 子句:

  • having 子句的作用是筛选满足条件的组,即在分组之后过滤数据,即先分组再过滤
  • having 后面可以使用聚合函数
  • group by 后面不能出现 where,使用 having

6.8 DQL-- 分页 limit 

limit 是一个MySQL"方言".  limit 的作用就是限制查询记录的条数。

-- 插入数据
INSERT INTO 
student3(id,NAME,age,sex,address,math,english) 
VALUES
	(9,'唐僧',25,'男','长安',87,78),
	(10,'孙悟空',18,'男','花果山',100,66),
	(11,'猪八戒',22,'男','高老庄',58,78),
	(12,'沙僧',50,'男','流沙河',77,88),
	(13,'白骨精',22,'女','白虎岭',66,66),
	(14,'蜘蛛精',23,'女','盘丝洞',88,88);

/* 
格式:limit offset,length
    offset:起始行数,从 0 开始计数,如果省略,默认就是 0  
    length: 返回的行数
*/
select *|字段列表 [as 别名] FROM 表名 [WHERE 子句] [GROUP BY 子句] [HAVING 子句] [ORDER BY 子 句] limit offset,length;

select * from student3 limit 5;-- 如果第一个参数是 0 可以省略写:
select * from student3 limit 2,6;-- 查询学生表中数据,从第 3 条开始显示,显示 6 条。
select * from student3 limit 10,5;-- 最后如果不够 5 条,有多少显示多少

数据库备份和还原

7.1 DOS下备份和还原

语法:
    备份: mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
    还原:
        1. 登录数据库
        2. 创建数据库
        3. 使用数据库
        4. 执行文件。source 文件路径

mysqldump -uroot -p123 db1 > d:/aaa.sql        -- 备份

-- 还原:(先登录数据库)
    mysql -uroot  -p123                        -- dos下登录数据库
    show databases;                            -- 查询所有数据库
    create database if not exists db2;         -- db2数据库是否存在,不存在则创建db2数据库
    use db2;                                   -- 使用db2 数据库
    source d:/aaa.sql;                         -- 导入备份的文件aaa.sql                                       

 7.2 图形化界面(SQLyog)下备份和还原

备份

还原数据库中的数据:首先删除SQLyog中 db2数据库,之后数据库列表区域右键执行 SQL 脚本, 指定要执行的 SQL 文件,执行即可

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

©️2020 CSDN 皮肤主题: 技术黑板 设计师:CSDN官方博客 返回首页