一、SQL
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 等
二、DDL:操作数据库、表
1. 操作数据库：CRUD
1. C(Create):创建
* 创建数据库：
* create database 数据库名称;
* 创建数据库，判断不存在，再创建：
* create database if not exists 数据库名称;
* 创建数据库，并指定字符集
* create database 数据库名称 character set 字符集名;
* 练习： 创建db4数据库，判断是否存在，并制定字符集为gbk
* create database if not exists db4 character set gbk;
2. R(Retrieve)：查询
* 查询所有数据库的名称:
* show databases;
* 查询某个数据库的字符集:查询某个数据库的创建语句
* show create database 数据库名称;
3. U(Update):修改
* 修改数据库的字符集
* alter database 数据库名称 character set 字符集名称;
4. D(Delete):删除
* 删除数据库
* drop database 数据库名称;
* 判断数据库存在，存在再删除
* drop database if exists 数据库名称;
5. 使用数据库
* 查询当前正在使用的数据库名称
* select database();
* 使用数据库
* use 数据库名称;
2. 操作表
1. C(Create):创建
1. 语法：
create table 表名(
列名1 数据类型1,
列名2 数据类型2,
....
列名n 数据类型n
);
* 注意：最后一列，不需要加逗号（,）
* 数据库类型：
1. int：整数类型
* age int,
2. double:小数类型
* score double(5,2)
3. date:日期，只包含年月日，yyyy-MM-dd
4. datetime:日期，包含年月日时分秒 yyyy-MM-dd HH:mm:ss
5. timestamp:时间错类型 包含年月日时分秒 yyyy-MM-dd HH:mm:ss
* 如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
6. varchar：字符串
* name varchar(20):姓名最大20个字符
* zhangsan 8个字符 张三 2个字符
* 创建表
create table student(
id int,
name varchar(32),
age int ,
score double(4,1),
birthday date,
insert_time timestamp
);
* 复制表：
* create table 表名 like 被复制的表名;
2. R(Retrieve)：查询
* 查询某个数据库中所有的表名称
* show tables;
* 查询表结构
* desc 表名;
3. U(Update):修改
1. 修改表名
alter table 表名 rename to 新的表名;
2. 修改表的字符集
alter table 表名 character set 字符集名称;
3. 添加一列
alter table 表名 add 列名 数据类型;
4. 修改列名称 类型
alter table 表名 change 列名 新列别 新数据类型;
alter table 表名 modify 列名 新数据类型;
5. 删除列
alter table 表名 drop 列名;
4. D(Delete):删除
* drop table 表名;
* drop table if exists 表名 ;
-
客户端图形化工具：SQLYog
三、DML：增删改表中数据
1. 添加数据：
* 语法：
* insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
* 注意：
1. 列名和值要一一对应。
2. 如果表名后，不定义列名，则默认给所有列添加值
insert into 表名 values(值1,值2,...值n);
3. 除了数字类型，其他类型需要使用引号(单双都可以)引起来
2. 删除数据：
* 语法：
* delete from 表名 [where 条件]
* 注意：
1. 如果不加条件，则删除表中所有记录。
2. 如果要删除所有记录
1. delete from 表名; -- 不推荐使用。有多少条记录就会执行多少次删除操作
2. TRUNCATE TABLE 表名; -- 推荐使用，效率更高 先删除表，然后再创建一张一样的表。
3. 修改数据：
* 语法：
* update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];
* 注意：
1. 如果不加任何条件，则会将表中所有记录全部修改。
总结：
-
启动,关闭及登录MySQL的方式？
net start servername
net stop sername
mysql -uroot -proot -
使用SQL语句创建数据库 DDL
create database if not exists 数据库名称 character set utf8; -
使用SQL语句操作表结构（创建表、修改表、删除表） DDL
create table tb1 (
列名1 数据类型,
.....
);
alter table 源表名 rename to 新表名
alter table tab1 change name newname 数据类型
alter table tab1 modify name 数据类型
alter table tab1 add name 数据类型
alter table tab1 drop name
drop table 表名 -
使用SQL语句进行数据的添加、修改和删除的操作 DML
insert into 表名 (col1,col2) values (v1,v2);
insert into 表名 values (v1,v2);
insert into 表名 (col1,col2) values (v1,v2),(v1,v2),(v1,v2)
update 表名 set col1=v1,col2=v2 where
delete from 表名 where
delete from 表名
truncat 表名 -
使用SQL语句简单查询并使用模糊查询进行条件查询 DQL
select col1,col2,......from 表 where > = < in between and or and like '_ %'
四、
1_DQL_基础查询
-
语法结构
SELECT 字段名称 FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段 HAVING 分组之后的条件 ORDER BY 排序规则 LIMIT 分页限定;
-
基础查询
-
查询所有列数据
SELECT * FROM 表名;
-
查询指定列的数据
SELECT 列名1,列名2...列明n From 表名;
-
查询记录去重
SELECT DISTINCT 列名,列名2...列名n FROM 表名;
-
计算列数据
#计算student表中的MATH与ENGLISH值的和 SELECT name,math+english FROM student;
如果参与计算的列有NULL参与运算,计算结果都为NULL.
-
如果想要为NULL的值按照某种值进行计算,那么可以使用IFNULL函数进行判断.
IFNULL(可能出现NULL值的列,值)
#计算student表中的MATH与ENGLISH值的和,如果有数据为NULL,则按照0分计算. SELECT name, math, english, IFNULL(math, 0) + IFNULL(english, 0) FROM student;
-
查询结果别名，关键字AS
#计算student表中的MATH与ENGLISH值的和,如果有数据为NULL,则按照0分计算,按照别名查询(AS版) SELECT name AS "姓名", math AS "数学成绩", english as "英语成绩", IFNULL(math, 0) + IFNULL(english, 0) AS "总成绩" FROM student;
AS 关键子可以省略
-
2_DQL_条件查询
-
WHERE字句后跟条件
-
比较运算符
>、<、<=、>=、=、<> <>在 SQL 中表示不等于，在 mysql 中也可以使用!= 没有== BETWEEN...AND 在一个范围之内，如：between 100 and 200 相当于条件在 100 到 200 之间，包头又包尾 IN(集合) 集合表示多个值，使用逗号分隔 AND、&& 并且 OR、|| 或者 IS NULL 查询某一列为 NULL 的值，注：不能写=NULL LIKE '张%' 模糊查询，配合占位符"_","%"使用 -
2.1.<、>、<=、>=、=、<>
- 查询年龄大于20岁 SELECT * FROM student WHERE age > 20; SELECT * FROM student WHERE age >= 20; -- 查询年龄小于20岁 SELECT * FROM student WHERE age < 20; SELECT * FROM student WHERE age <= 20; -- 查询年龄等于20岁 SELECT * FROM student WHERE age = 20; -- 查询所有SEX不是男的数据信息(!=) SELECT * FROM student WHERE sex != "男"; -- 查询所有SEX不是男的数据信息(<>) SELECT * FROM student WHERE sex <> "男";
-
2.2.BETWEEN...AND 包括头部数据也包括尾部数据.
-- 查询MATH位于80-100之间的数据信息. SELECT * FROM student WHERE math BETWEEN 80 AND 100;
-
2.3.AND、OR、&&、||
-- 查询MATH位于80-100之间的数据信息. SELECT * FROM student WHERE math>= 80 AND math<=100; SELECT * FROM student WHERE math>= 80 && math<=100; -- 查询AGE是20或者22的数据信息. SELECT * FROM student WHERE age = 22 OR age = 20; SELECT * FROM student WHERE age = 22 || age = 20;
-
2.4.IN
-- 查询AGE是20或者22的数据信息. SELECT * FROM student WHERE age IN (20,30);
-
2.5.IS NULL、IS NOT NULL
-- 查询英语成绩为NULL的数据信息. SELECT * FROM student WHERE english IS NULL; -- 查询英语成绩不为NULL的数据信息. SELECT * FROM student WHERE english IS NOT NULL;
null值不能使用 = 和 != 判断
3_DQL_条件查询-模糊查询
-
占位符
-
_ 表示任意单个字符
-
% 表示任意多个字符
-
-
LIKE 模糊查询关键字
-- 查询所有NAME中姓马的数据信息 SELECT * FROM student WHERE name LIKE "马%"; -- 查询所有NAME中第二个字是化的数据信息. SELECT * FROM student WHERE name LIKE "_化%"; -- 查询所有NAME中是三个字的数据信息,此处的""中是三个_. SELECT * FROM student WHERE name LIKE "___"; -- 查询所有NAME中带有德字的数据信息. SELECT * FROM student WHERE name LIKE "%德%";
4_DQL_排序查询
-
语法格式
ORDER BY 子句; ORDER BY 排序字段1 排序方式1（ASC/DESC）,排序字段2 排序方式2...
-
排序方式
-
ASC 升序排列 如果不书写排序方式,默认按照ASC的方式进行排序.
-
DESC 降序排列
-
-
示例
-- 按照MATH列对数据进行排序(升序) SELECT * FROM student ORDER BY math ASC; -- 按照MATH列对数据进行排序(降序) SELECT * FROM student ORDER BY math DESC; -- 按照MATH列队数据进行排序,如果MATH列数据相同,按照ENGLISH列队数据进行排序(升序) SELECT * FROM student ORDER BY math DESC ,english ASC;
-
注意： 如果有多个排序条件，则当前边的条件值一样时，才会按下一个条件进行排序
5_DQL_聚合函数
-
将一列数据作为一个整体，进行纵向的计算。
-
5.1.COUNT 统计记录数
-
语法
SELECT COUNT(条件) FROM 表名;
-
示例代码
-- 查询student表中的NAME数据非NULL的条数. SELECT COUNT(name) FROM student; -- 查询student表中的ENGLISH数据非NULL的条数. SELECT COUNT(english) FROM student; -- 查询student表中的ENGLISH的条数,如果为NULL,按照0计算. SELECT COUNT(IFNULL(english,0)) FROM student;
-
- MYSQL是忽略NULL值的,不会把NULL记录为有效数据.
-
一般选择非空的列：主键
-
count(*) count(主键) 或者count(1)
-
-
-
5.2.MAX 最大值
-
语法
SELECT MAX(条件) FROM 表名;
-
示例代码
-- 查询MATH列的最大值 SELECT MXA(math) FROM student;
-
-
5.3.MIN 最小值
-
语法
SELECT MIN(条件) FROM 表名;
-
示例代码
-- 查询MATH列的最小值 SELECT MIN(math) FROM student;
-
-
5.4.SUM 和
-
语法
SELECT SUM(条件) FROM 表名;
-
示例代码
#查询MATH列的总和 SELECT SUM(math) FROM student;
-
-
5.5.AVG 平均值
-
语法
SELECT AVG(条件) FROM 表名;
-
示例
#查询MATH列的平均值 SELECT AVG(IFNULL(math,0)) FROM student;
-
-
总结
-
聚合函数是将一列作为一个整体去进行纵向的计算的.
-
COUNT计算个数一般选择非NULL的列进行计算.
-
-
练习
-
求数学60分以上的总人数
-
求男生的数学总分
-
求男生的英语平均成绩，注意null
-
求男生年龄最大
-
求女生年龄最小
-
6_DQL_分组查询
-
语法格式
GROUP BY 分组字段;
-
注意事项
-
使用分组查询的时候,查询的字段要么是聚合函数字段，要么是分组字段,写其他的没有意义.
-
-
示例代码
-- 按照性别分组。分别查询男、女同学的平均分 SELECT sex, AVG(math) FROM student GROUP BY sex; -- 按照性别分组。分别查询男、女同学的平均分,人数 SELECT sex, AVG(math),COUNT(id) FROM student GROUP BY sex; -- 按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组 SELECT sex, AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex; -- 按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组,分组之后。人数要大于2个人 SELECT sex, AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2; SELECT sex, AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING 人数 > 2;
-
总结
-
分组之后查询的字段必须是分组字段、聚合函数计算的字段
-
where 和 having 的区别？面试题
-
where 在分组之前进行限定，如果不满足条件，则不参与分组。
-
having在分组之后进行限定，如果不满足结果，则不会被查询出来
-
where 后不可以跟聚合函数，having可以进行聚合函数的判断。
写的顺序：
select ... from... where.... group by... having... order by.. limit [offset,] (rows)
读的顺序：
执行顺序：from... where...group by... having.... select ... order by... limit
-
-
7_DQL_分页查询
-
语法格式
LIMIT 开始的索引,每页查询的条数
-
公式
-
开始的索引 = （当前的页码 - 1） * 每页显示的条数
-
因为索引从0开始
-
-
示例代码
-- 分页查询,从索引为0的数据开始查询,查询3条数据(第1页) SELECT * FROM student LIMIT 0,3; -- 分页查询,从索引为3的数据开始查询,查询3条数据(第2页) SELECT * FROM student LIMIT 3,3; -- 分页查询,从索引为3的数据开始查询,查询3条数据(第3页) SELECT * FROM student LIMIT 6,3;
8_约束-概述（理解）
-
概念： 对表中的数据进行限定，保证数据的正确性、有效性和完整性。
-
分类：
-
主键约束：primary key
-
非空约束：not null
-
唯一约束：unique
-
外键约束：foreign key
-
9_约束-非空约束
非空约束用于确保当前列的值不为空值.
当一张表的某一列设置为非空约束之后,添加NULL值到表中的时候,会报错.
-
创建表的时候指定列非空约束
CREATE TABLE stu ( id INT, NAME VARCHAR(20) NOT NULL -- 非空约束 );
-
删除非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20);
-
修改表，给指定列添加非空约束
ALTER TABLE stu MODIFY id INT NOT NULL;
-
练习
-
创建一张表，针对某个字段设置非空约束
-
测试增加null数据
-
删除约束
-
测试增加null数据
-
修改表增加约束
-
测试增加null数据
-
10_约束-唯一约束
unique 唯一约束是指定表中的列不能重复,保证数据的唯一性。
-
创建表的时候指定列唯一约束
CREATE TABLE stu ( id INT NOT NULL, phone_number VARCHAR(5) UNIQUE -- 唯一约束 )
-
删除列的唯一约束
ALTER TABLE stu DROP INDEX phone_number
-
修改列增加唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR(5) UNIQUE
-
注意mysql中，唯一约束限定的列的值可以有多个null
11_约束-主键约束
-
PRIMARY KEY
-
主键约束修饰的列，非空且唯一
-
一张表只能有一个主键
-
主键就是表中记录的唯一标识
-
-
创建表的时候指定列主键约束
CREATE TABLE stu ( id INT PRIMARY KEY, -- 主键约束 NAME VARCHAR(30) );
-
删除列的主键约束
ALTER TABLE stu DROP PRIMARY KEY;
-
修改列增加主键约束
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
12_约束-主键约束-自动增长
如果某一个列是数值类型的,使用AUTO_INCREMENT可以来完成值的自动增长
-
创建表时，添加主键约束，并且完成主键自增长
CREATE TABLE stu ( id INT PRIMARY KEY AUTO_INCREMENT, -- 主键，自增 NAME VARCHAR(20) );
-
删除自动增长
ALTER TABLE stu MODIFY id INT;
-
删除了自动增长之后,主键约束依然存在.
-
-
修改列，增加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
-
注意事项
-
如果列是自动增长,那么可以赋值为NULL,MYSQL会自动增长.
-
赋值为NULL的时候,生成的数据是根据表中的最后一条数据进行判断的,不具有连续性.
-
13_约束-外键约束
-
什么是外键（FOREIGN KEY）：在从表中与主表主键对应的那一列，如：员工表中的 dep_id
主表： 一方，用来约束别人的表
从表： 多方，被别人约束的表
-
注意事项
-
当主表和从表存在外键关系的时候,主表删除的时候从表内有数据引用,是无法直接删除的.
-
当从表添加的数据的时候,如果外键列的数据主表中不存在的话,是无法直接添加的.
-
外键的名称不可以重复.
-
-
外键的好处
-
可以让表与表之间产生关系,来保证数据的正确性.
-
-
语法
CONSTRAINT 外键约束名称 FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
-
在创建表时，添加外键
CREATE TABLE 表名( .... 外键列， CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 主表名称(主表列名称) );
CREATE TABLE employee ( id INT, name VARCHAR(20), dep_id INT, CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES department (id) -- 外键约束 ); -
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
-
创建表后，添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
-- employee表dep_id增加外键映射department表id ALTER TABLE employee ADD CONSTRAINT emp_dep_foreign_key FOREIGN KEY (dep_id) REFERENCES department (id);
14_约束-外键约束-级联操作（理解）
-
在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作
级联操作语法 描述 ON UPDATE CASCADE 级联更新,只能是创建外键的时候创建级联关系。更新主表中的主键，从表中的外键 列也自动同步更新 ON DELETE CASCADE 级联删除 -
示例代码
#添加外键约束到EMPLOYEE表中的DEP_ID列并设计级联更新 ALTER TABLE EMPLOYEE ADD CONSTRAINT EMP_DEFT_FK FOREIGN KEY (DEP_ID) REFERENCES DEPARTMENT(ID) ON UPDATE CASCADE ; #添加外键约束到EMPLOYEE表中的DEP_ID列并设计级联更新/级联删除 ALTER TABLE EMPLOYEE ADD CONSTRAINT EMP_DEFT_FK FOREIGN KEY (DEP_ID) REFERENCES DEPARTMENT(ID) ON UPDATE CASCADE ON DELETE CASCADE;
-
数据约束小结
约束名 关键字 说明 主键 primary key 唯一、非空 默认 default 如果一列没有值，使用默认值 非空 not null 这一列必须有值 唯一 unique 这一列不能有重复值 外键 foreign key 主表中主键列，在从表中外键列