SQL语句笔记

什么是SQL

SQL：Structure Query Language 结构化查询语言，它是一种关系型数据库通用的语言。用来对数据库进行管理，主要分成以下四大类

SQL语句分类

1.Data Definition Language：简称为DDL，数据定义语言

Definition：n. 定义

用途：创建数据库 创建表 对数据库和表进行操作

2.Data Manipulation Language：简称为DML语言，数据操作语言

Manipulation：n. 操纵；操作；处理；篡改

用途：对记录进行增删改的操作

3.Data Query Language：简称为DQL，数据查询语言

用途：对记录进行查询的操作，用得最多（重点）

4.Data Control Language：简称为DCL，数据控制语言

用途：对用户和权限进行管理，用得比较少，一般给DBA使用

MySQL的语法

1. 每一行代码都以分好结尾，一行代码可以拆分多行用一个分好结尾。
2. SQL语句不区分大小写，行业的规范是全部使用大写
3. 注释： # 单行注释
   -- 单行注释 规范写法，推荐使用my
   /*
   多行注释
   */

DDL：创建数据库

创建数据库

-- 创建数据库 CREATE DATABASE 数据库名;

创建的数据库默认字符集是安装数据库指定的字符集格式（通常都是UTF-8）

 -- 创建指定字符集的数据库 CREATE DATABASE 数据库名 CHARACTER SET gbk;

-- 查看创建数据库的脚本 SHOW CREATE DATABASE 数据库名;

SQLyog客户端返回结果

可以从中获取到默认的字符集是utf8

如果数据库的名字被使用，在SQL中使用被使用的名字会报错，所以在创建数据库前需要判断数据库是否存在，不存在再创建数据库

 -- 判读数据库是否存在，不存在则创建数据库 CREATE DATABASE IF NOT EXISTS  数据库名;

删除数据库

DROP DATABASE 数据库名;

 -- 判读数据库是否存在，存在则删除 DROP DATABASE IF EXISTS 数据库名;

查看数据库

-- 查看所有的数据库 SHOW DATABASES;

 -- 查看创建数据库的脚本 SHOW CREATE DATABASE 数据库名;

SQLyog客户端返回结果

修改数据库

-- 将数据库的字符集修改为指定字符集 注意在MySQL中不可以是UTF-8只能写成utf8 ALTER DATABASE 数据库名 CHARACTER SET utf8;

 -- 可以通过查看创建数据库脚本命令 确定字符集是否修改成功 SHOW CREATE DATABASE 数据库名; 

使用数据库

查看当前使用的数据库

SELECT DATABASE();

使用指定的数据库

 USE 数据库名;

DDL：创建表结构（重点）

创建表

创建表之前需要先使用

USE 数据库名;

 -- 创建表  字段名 最好使用 ` `转义包裹起来 防止和数据库关键字冲突 CREATE TABLE 表名(     `字段名` 字段类型,     `字段名` 字段类型 );

MySQL支持的数据类型 红色为常用类型

查看表结构

desc 表名;

查看创建表的SQL语句

 SHOW CREATE TABLE 表名;

创建相同的表结构（复制一个相同结构的表）

-- 创建表1，表1结构和原始表结构相同 CREATE TABLE 表1 LIKE 原始表名;

删除表

 DROP TABLE 表名;

判断表是否存在再删除表

DROP TABLE IF EXISTS 表名;

练习

 /* 表操作练习 */  -- 创建数据库名为mydb的数据库 CREATE DATABASE mydb; -- 使用数据库名为mydb的数据库 USE mydb; -- 在mydb数据库中创建一个student表 -- 包含id编号 整数, -- name 长字符串长20, -- sex性别 定长型字符串1, -- birthday 字段日期类型 -- 使用反引号 `引起来，可以转义  CREATE TABLE student(      `id` INT COMMENT '编号',  -- 字段的说明 保存在数据表中  可以不写      `name` VARCHAR(20) COMMENT '姓名',  -- 姓名      `sex` CHAR(1),  -- 性别      `birthday` DATE   -- 生日，注：最后一个字段没有逗号 );  -- 查看表的结构 DESC student;

SQLyog客户端返回结果

修改表结构

给表添加字段（添加列）ADD

ALTER TABLE 表名 ADD 字段名 字段类型;

修改字段类型（修改列的类型）MODIFY

 ALTER TABLE 表名 MODIFY 字段名 要修改成的字段类型;

修改字段名和字段类型 CHANGE

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新字段类型;

删除列 DROP

 ALTER TABLE 表名 DROP 字段名;

修改表名

RENAME TABLE 旧的表名 TO 新的表名;

练习

 -- 如果表中已经有数据，删除表结构会导致表中所有的数据全部丢失 -- 可以修改表结构，不会影响数据，只会影响表结构  -- 查看表结构 DESC student; -- 为学生表添加一个新的字段remark,类型为char(20) ALTER TABLE student ADD remark CHAR(20);  -- 将student表中的remark字段的改成varchar(100) ALTER TABLE student MODIFY remark VARCHAR(100);  -- 将student表中的remark字段名改成info，类型varchar(30)  ALTER TABLE student CHANGE remark info VARCHAR(30);  -- 删除student表中的字段info ALTER TABLE student DROP info;  -- 将学生表student改名成student2 RENAME TABLE student TO student2;

DML:添加数据

插入全部字段

增加一条数据，所有的字段都要给值

INSERT INTO 表名 VALUES(字段1的值,字段2的值,字段3的值....); 1.VALUES关键字也可以替换为VALUE但是不规范 2.字段的值的类型一定要与字段的类型一致，否则会报错 3.字符串和日期使用单引号包裹起来 4.如果字段类型是字符串，那么给值的时候不能超出字段类型中字符串限定的长度 如：varchar(5)字符串的长度不能超过5个字符

插入部分字段

 INSERT INTO 表名 (字段1的名,字段2的名....) VALUES (字段1的值,字段2的值....);

插入多条记录

INSERT INTO 表名 VALUES(字段1的值,字段2的值,字段3的值....),(字段1的值,字段2的值,字段3的值....),(字段1的值,字段2的值,字段3的值....);

练习

 -- 向学生表中插入所有字段，不写字段名 INSERT INTO student VALUES (100, '学生1', '男', '1999-02-18');  INSERT INTO student VALUES (200, '学生2', '女', '1999-02-18');  -- 添加的字段补全  错误代码： 1136  Column count doesn't match value count at row 1 INSERT INTO student VALUES ('学生1 ', '男', '1999-02-18');  -- 添加的字段超出 字符串长度闲置 错误代码： 1406 Data too long for column 'sex' at row 1 INSERT INTO student VALUES (100, '学生1', '男1号', '1999-02-18');  -- 插入部分数据，往学生表中添加id, name数据 INSERT INTO student (id,NAME) VALUES (300,'学生3'); INSERT INTO student (id,NAME) VALUES (300,'学生3'),(400,'学生4');  -- 一条insert语句插入多条记录 INSERT INTO student VALUES (400, '学生4', '女', '1994-02-18'),(500, '学生5', '男', '1999-02-11'),(600, '学生6', '男', '1993-02-18');

DML：修改记录

不带条件修改数据

-- 不带条件修改数据，表中的所有数据都会被修改，一般不适用 UPDATE 表名 SET 字段名=值,字段名=值;

带条件修改数据

 -- 只会更新符合条件的记录 UPDATE 表名 SET 字段名=值,字段名=值 WHERE 条件;

练习

-- 不带条件修改数据，将student表中的所有数据的性别改为女 UPDATE student SET sex='女'; -- 带条件修改数据，将id号为300的学生性别改成男 UPDATE student SET sex='男' WHERE id=300; -- 一次修改多个列，把id为600的学生，修改性别为男，生日：2000-11-11 UPDATE student SET sex='男',birthday='2000-11-11' WHERE id=600;

DML：删除表记录

不带条件删除

不带条件删除会删除表中所有的数据

 -- 基本不用，要记住慎用 DELETE FROM 表名;   -- 删除表的全部记录，但是表还在 DROP TABLE 表名;  -- 表会被删除 表中的数据也会随之删除  -- 如果是事务 可以进行回滚

带条件删除

-- 在开发中通长会给数据一个删除标记而不是完全的从数据库中删除 DELETE FROM 表名 WHERE 条件;

删除表中所有的记录

 TRUNCATE 表名;  -- 相当于删除表 DROP TABLE 表名; -- 再创建表 CREATE DATABASE 表名;  -- 这个操作不可被回滚

总结

-- 添加记录 INSERT INTO 表名 VALUES(值,值.....); INSERT INTO 表名 (字段1,字段2....) VALUES(值1,值2....); -- 删除记录 DELETE FROM 表名 WHERE 条件; -- 修改记录 UPDATE 表名 SET 字段1=修改的值,字段2=修改的值 WHERE 条件;

DQL：查询数据

查询全部

 SELECT * FROM 表名;

从所有信息中查询指定的字段

SELECT 字段1,字段2 FROM 表名;

条件查询

 SELECT * FROM 表名 WHERE 条件;

指定列的别名

使用关键字AS （也可以省略）

SELECT 字段1 AS 别名1,字段2 AS 别名2 FROM 表名; -- 也可以省略AS SELECT 字段1 别名1,字段2 别名2 FROM 表名;

在查询结果中取出重复的记录

DISTINCT作用：去掉后面所有列都相同的行，如果后面有几列，必须是这几列都相同才会被去除

 SELECT DISTINCT 字段 FROM 表名;

-- 查询学生来自于哪些地方,并且去掉重复行 SELECT * FROM student; -- 修改表结构，添加一个名为address的字段，并且添加数据 ALTER TABLE student ADD address VARCHAR(100); -- 只查询地址这一列 SELECT address FROM student; -- 去除重复的数据 -- DISTINCT作用：去掉后面所有列都相同的行，如果后面有几列，必须是这几列都相同才会被去除 SELECT DISTINCT address FROM student; -- 查询学生的姓名和地址,去掉重复行。必须是这几列都相同才会被去除 SELECT DISTINCT NAME, address FROM student;

列数据和固定值运算

 select 列+数值 from 表名; -- 显示的结果是列的值加上数值以后的结果，列必须是数值类型

列数据和其他列数据参与运算

select 列1+列2 from 表名; 列1和列2都是表中存在的字段

注意: 参与运算的必须是字段的数值类型

练习

 -- 修改student表结构,添加数学和英语成绩列 ALTER TABLE student ADD math INT, ADD english INT;  -- 查询姓名、数学，将数学每个减10分。查询的结果不一定是表中存在的列，也有可能是计算的结果 SELECT NAME,math, math-10 FROM student;  -- 查询所有列与math + english的和并使用别名”总成绩” SELECT *, math+english 总成绩 FROM student;

DQL：包含运算符的查询条件

创建一个表并插入数据

CREATE TABLE student3 ( id int, -- 编号 name varchar(20), -- 姓名 age int, -- 年龄 sex varchar(5), -- 性别 address varchar(100), -- 地址 math int, -- 数学 english int -- 英语 ); 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);

比较运算符

 >、大于 <、小于 <=、小于等于 >=、大于等于 =、等于 <> 、!=、不等于

逻辑运算符

AND 或 && 与操作 OR 或 || 或操作 NOT 或 ! 非操作

 SELECT * FROM student3 WHERE math > 80;  -- 查询english分数小于或等于80分的学生 SELECT * FROM student3 WHERE english <=80;   -- 查询age等于20岁的学生，注：没有== SELECT * FROM student3 WHERE age=20;  -- 查询age不等于20岁的学生，注：不等于有两种写法 SELECT * FROM student3 WHERE age<>20;  -- SQL语句中使用<>方式比较多 SELECT * FROM student3 WHERE age!=20;

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

DQL：条件中的关键字（重点）

关键字IN

 SELECT * FROM 表名 WHERE 字段名 IN (值1,值2...);

用途：返回满足括号内给定值的记录

-- 使用in关键字 SELECT * FROM student3 WHERE id IN (2,4,6); -- 删除多条记录 SELECT * FROM student; DELETE FROM student WHERE id IN (100,200,300);

BETWEEN...AND... 范围查询

 SELECT 列名 FROM 表名 WHERE 列名 BETWEEN 小值 AND 大值

用途：查询列值在小值和大值之间，相当于 >=小值 而且 <=大值

注：between后面一定是小值， and后面一定是大值包头又包尾

-- 查询english成绩大于等于77，且小于等于87的学生 SELECT * FROM student3 WHERE english BETWEEN 77 AND 87; -- 注：反过来写是不行的，查询不到任何的记录 SELECT * FROM student3 WHERE english BETWEEN 87 AND 77;

LIKE关键字

 SELECT 列名 FROM表名 WHERE 列名 LIKE 值;

用途：用于模糊查询

注：如果没有使用通配符默认是按等于查询

MySQL通配符

% 匹配多个字符

_ 匹配一个字符

-- 查询姓马的学生 SELECT * FROM student3 WHERE NAME LIKE '马'; -- 注：没有任何一个学生的名字叫马 -- 模糊查询必须使用通配符 SELECT * FROM student3 WHERE NAME LIKE '马%'; -- 查询姓名中包含'德'字的学生 SELECT * FROM student3 WHERE NAME LIKE '%德%'; -- 匹配0~n个字符 -- 查询姓马，且姓名有2个字的学生 SELECT * FROM student3 WHERE NAME LIKE '马_'; -- 下划线是匹配1个字符 SELECT * FROM student3 WHERE NAME LIKE '马__'; -- 查询3个字的学生

查询为空的列

• 为空：IS NULL
• 不为空：IS NOT NULL
• 注：不要写成 = NULL，这是查询不出任何记录的

 -- 查询英文成绩为NULL的学生 SELECT * FROM student3 WHERE english = NULL;  -- 查询不到任何的记录 SELECT * FROM student3 WHERE english IS NULL;   -- 正确写法  -- 查询英语成绩不为NULL的学生 SELECT * FROM student3 WHERE english IS NOT NULL;  -- 查询姓名和英语成绩，如果英语为null，则显示为0分 SELECT NAME, english FROM student3;  -- 使用函数：ifnull(字段名, 默认值) 如果这个字段有值就显示它原来的值，如果为NULL，就显示后面的默认值 SELECT NAME 姓名, IFNULL(english,0) 英语 FROM student3

DQL：查询结果排序

单列排序

SELECT 列名 FROM 表名 ORDER BY 列名 [ASC/DESC], 列名 [ASC/DESC]; -- ASC是升序 默认就是升序 可以不写 -- DESC是降序 可以多列一同使用排序

 -- 查询所有数据,使用年龄降序排序，没有排序前默认按添加到数据库中记录的顺序显示 SELECT * FROM student3 ORDER BY age;  -- 默认是按升序 SELECT * FROM student3 ORDER BY age DESC; 

多列排序

-- 查询所有年龄大于20岁的学生,在年龄降序排序的基础上，如果年龄相同再以数学成绩升序排序 SELECT * FROM student3 WHERE age >20 ORDER BY age DESC, math ASC;

MySQL笔记（二）

聚合函数

概述

所有的关系型数据库都支持聚合函数，使用聚合函数查询是列向查询，它是对一列的值进行计算，并返回结果。

注：所有的聚合函数会忽略空值。

练习

 -- 修改表student3为student RENAME TABLE student3 TO student;  SELECT * FROM student;  -- 创建表 CREATE TABLE student(     id INT,     `name` VARCHAR(20),     `math` int,     `chinese` int,     `english` int );  -- 查询学生总数，星号表示所有的列 SELECT COUNT(*) 总人数 FROM student; -- 也可以使用COUNT(1),效果等同于COUNT(*) SELECT COUNT(1) 总人数 FROM student; -- 如果某个字段的所有记录都不存在NULL值 可以使用COUNT(字段名) SELECT COUNT(id) 总人数 FROM student;  -- 如果这一列中存在空值，聚合函数会忽略空值 SELECT COUNT(english) 总人数 FROM student;  -- 查询年龄大于40的总数(可以在聚合函数中使用条件) SELECT COUNT(*) 总人数 FROM student WHERE age > 40;   -- 查询数学成绩的总和，针对一列操作 SELECT SUM(math) 数学总分 FROM student;  -- 对非数值类型求和没有含义 不会报错但是会返回值 SELECT SUM(`name`) 数学总分 FROM student;  -- 查询数学成绩平均分，整数的平均得到的是小数 SELECT AVG(math) 数据平均分 FROM student;  -- 查询数学成绩最高分 SELECT MAX(math) 最高分 FROM student;  -- 查询数学成绩最低分 SELECT MIN(math) 最低分 FROM student;

分组查询

SELECT 列名 FROM 表名 GROUP BY 分组列 HAVING 过滤条件;

用途：

1. 可以指定列根据列中的内用对数据进行分组操作
2. HAVING可以对分组后的内容进行过滤

直接分组

将分组字段结果中相同内容作为一组，如按性别将学生分成2组。返回每组的第一条数据，所以单独分组没什么用处。分组的目的就是为了统计，一般分组会跟聚合函数一起使用。

 -- 删除已经存在的student表 DROP TABLE IF EXISTS student ; -- 创建新的student表 CREATE TABLE student(     sid INT PRIMARY KEY,     `name` VARCHAR(20),     `sex` CHAR(1) ); -- 在student表中插入数据 INSERT INTO student VALUES (1,'小明','男'),(2,'小红','女'),(3,'小刚','男'); -- 用sex字段对studen分组 返回所有字段的值 SELECT * FROM student GROUP BY sex; 

执行结果

只返回了每组的第一条数据

结合聚合函数使用分组

-- 删除已经存在的student表 DROP TABLE IF EXISTS student ; -- 创建新的student表 CREATE TABLE student( sid INT PRIMARY KEY, `name` VARCHAR(20), `sex` CHAR(1) ); -- 在student表中插入数据 INSERT INTO student VALUES (1,'小明','男'),(2,'小红','女'),(3,'小刚','男'); -- 用sex字段对studen分组 SELECT sex 性别,COUNT(sex) 人数 FROM student GROUP BY sex;

执行结果

结合聚合函数与条件使用

查询数学成绩大于等于60分的学生，按性别分组，统计每组的人数，只显示性别人数大于2的数据。

 -- 删除已经存在的student表 DROP TABLE IF EXISTS student ; -- 创建新的student表 CREATE TABLE student(     sid INT PRIMARY KEY,     `name` VARCHAR(20),     `sex` CHAR(1),     `math` INT,     `chinese` INT,     `english` INT ); -- 在student表中插入数据 INSERT INTO student VALUES (1,'小明','男',56,55,88),(2,'小红','女',66,78,55),(3,'小刚','男',100,99,98),(4,'小黄','男',60,79,88),(5,'小梅','女',60,79,88),(6,'小丽','女',60,79,88);  -- 查询数学成绩大于等于60分的学生，按性别分组，统计每组的人数，只显示性别人数大于2的数据。 SELECT sex 性别 , COUNT(*) FROM student WHERE math >= 60 GROUP BY sex HAVING COUNT(*) > 2;

执行结果

HAVING与WHERE的区别

DQL：LIMIT分页操作(重点)

用途：限制从表中返回的查询记录，通常称为分页的操作

使用场景：比如我们登录京东，淘宝，返回的商品信息可能有几万条，不是一次全部显示出来。是一页显示固定的条数。

语法：

limit 起始行号(从0开始), 返回的数据记录数; -- 使用方式：放在WHERE后面

 -- 删除已经存在的student表 DROP TABLE IF EXISTS student ; -- 创建新的student表 CREATE TABLE student(     sid INT PRIMARY KEY,     `name` VARCHAR(20),     `sex` CHAR(1),     `math` INT,     `chinese` INT,     `english` INT ); -- 在student表中插入数据 INSERT INTO student VALUES  (1,'小明','男',56,55,88), (2,'小红','女',66,78,55), (3,'小刚','男',100,99,98), (4,'小黄','男',60,79,88), (5,'小梅','女',60,79,88), (6,'小丽','女',60,79,88), (7,'小美','女',60,79,88), (8,'小花','女',60,79,88), (9,'小蕾','女',60,79,88); -- 每页显示5条 -- 第一页：从第0行开始，显示5条 SELECT * FROM student LIMIT 0,5; 

执行结果

-- 第二页：从第5行开始，显示5条 SELECT * FROM student LIMIT 5,5;

执行结果

小总结：

SELECT中的关键字顺序

 SELECT 字段 FROM 表 WHERE 条件 GROUP BY 分组列 HAVING 过滤条件 ORDER BY 排序列 LIMIT 开始行, 返回行的数量;

增删改查关键字总结

DCL：创建用户，授权，撤销权限(了解，用的时候查就可以)

DCL (Data Control Language)：数据库安装后默认使用的是root用户就是超级管理员，拥有全部的权限。一个公司里面的数据库服务器上面可能同时运行着很多个项目的数据库。所以，我们应该可以根据不同的项目建立不同的用户，分配不同的权限来管理和维护数据库。

创建用户：

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'; CREATE USER '用户名'@'%' IDENTIFIED BY '密码'; -- 在任意主机上登陆 CREATE USER '用户名'@'主机名'; -- 一些版本的数据可可以不设置密码，使用用户名直接登陆

用户名：登录用户的名字

主机名：这个用户可以在哪台机器上登录，如果是本机服务器，使用localhost如果可以在任何一台主机上登录可以使用'%'

注：创建的用户名都在mysql数据库中的user表中可以查看到，密码经过了加密。

给用户授权

创建的新用户默认是没有任何权限的，需要使用root账户授权

 GRANT 权限 ON 数据库.表名 TO '用户名'@'主机名';

权限:操作权限：CREATE, ALTER, INSERT,DELETE,UPDATE,SELECT等，如果要设置所有的权限可以使用ALL

数据库.表名:对哪个数据库的哪个表设置上面的权限，可以使用通配符，如果要设置所有的库所有的表，可以使用*.*

'用户名'@'主机名':给哪个用户设置权限，名字必须与创建的时候写法相同

GRANT CREATE,ALTER,INSERT,UPDATE,SELECT ON test.* to user1'@'localhost'; GRANT ALL ON *.* TO 'user2'@'%';

取消用户权限

 REVOKE 权限 ON 数据库.表名 FROM '用户名'@'主机名';

权限：权限用户的操作权限：CREATE, ALTER, INSERT,DELETE,UPDATE,SELECT等，如果要设置所有的权限可以使用ALL

数据库名.表名： 对哪个数据库的哪个表设置上面的权限，可以使用通配符，如果要设置所有的库所有的表，可以使用*.*

'用户名'@'主机名'：给哪个用户撤销权限，名字必须与创建的时候写法相同

DCL：删除用户，修改密码(了解，用的时候查就可以)

删除用户

DROP USER '用户名'@'主机名' -- 用户名必须与创建的时候相同

修改管理员密码

这条命令不在MySQL中执行，要在系统的CMD中执行

安装目录下：C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqladmin.exe文件

子用户修改自己的密码

 SET PASSWORD=PASSWORD('密码')

注：用户登录后操作，在mysql中已经登录的情况下使用这条命令

数据库备份和还原

备份

备份命令是在系统的CMD中执行的不是在MySQL中执行的

C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe 格式：mysqldump -u用户名 -p密码 数据库名>文件名 将指定的数据库备份到一个SQL文件中

还原

注：这个操作需要登录，它是mysql中命令

 USE 数据库名; SOURCE 文件名;