前提须知
SQL是什么?
是Structured Query Language的缩写,意思为结构化查询语句
SQL主要分为:
DDL(操作数据库、表)、DML(增删改表中数据)、DQL(查询表中的记录)
一、DDL(操作数据库、表)
1、创建库,删除库,修改库的字符集语法
-- 创建库
create database 库名 ;
create database if not exists 库名;
-- 查询所有的库
show databases ;
-- 删除库
drop database 库名;
drop database if exists 库名;
-- 修改库的字符集
alter database 库名 default character set 字符集名称;
-- 查询指定库的字符集
show create database 库名;
2、创建表,修改字段类型,修改表的字段名,添加字段,删除字段,删除表的语法
-- 创建表之前,一定使用库
use 库名;
create table 表名(
字段名称1 字段类型,
字段名称2 字段类型,
....
字段名称n 字段类型n
);
-- 查询表的结构:查看这个表的字段信息(表中列的名称)
desc 表名;
-- 查询库中有哪些表
show tables ;
-- 修改的表的字段名称
alter table 表名 change 以前的字段名 现在新的字段名称 以前的字段类型;
-- 修改的表的字段类型
alter table 表名 modify 字段名称 新的字段类型;
-- 给表添加一个新字段
alter table 表名 add 字段名称 字段类型;
-- 删除表的某个字段
alter table 表名 drop 字段名称;
-- 修改的表的名称--重命名
alter table 以前的表名 rename to 新的表名;
-- 删除表
drop table 表名;
drop table if exists 表名
二、DML(增删改表中数据)
1、插入,修改,删除语法
-- 插入数据,插入全表字段
-- 插入一条
insert into 表名 values(值1,值2,....值n) ;
-- 插入多条
insert into 表名 values(值1,值2,....值n) ,(值1,值2,....值n) .... ;
-- 插入数据,插入部分字段,没有插入字段为null
-- 插入一条
insert into 表名(部分字段名称) values(部分字段对应的值) ;
-- 插入多条
insert into 表名(部分字段名称) values(部分字段对应的值),(部分字段对应的值 2).... ;
-- 修改表的记录
-- 带单个条件修改,修改某条记录
-- where后面字段一般非 业务字段(每一张表会有id,后面加入约束非空并且唯一)
update 表名 set 字段名称 = 值 where 字段名称2= 值2;
-- 带多个条件, 并列关系----and关键字
update 表名 set 字段名名称 = 值 ,字段名称2= 值2 where 条件1 and 条件2 ...;
-- 不带条件,属于批量修改
-- 很少用
update 表名 set 字段名称= 值 ;
-- 删除表的记录
-- 带条件删除(带多个条件或者单个条件)
delete from 表名 where 字段名称 = 值 and 字段名称2 = 值 ;
-- 删除全表数据
delete from 表名;
三、DQL(数据库查询语句)
1、基本查询
-- 查询全表数据
-- 实际开发中,禁用* ,查询全表,查询全部字段
select * from 包名;
-- 查询全表字段,以student为例
SELECT
id '学号',
NAME '姓名',
gender '性别',
age '年龄',
address '地址',
birthday '出生日期'
FROM
student ;
-- 查询指定的字段
-- 查询指定的字段的时候,指定别名
2、条件查询
基本格式:select 字段列表 from 表名 where 条件;
条件:1)里面使用基本运算符,比较运算符:<,>=,<=,==,!=(mysql的不等于可以使用这个<>)
赋值运算符: =
逻辑运算符:java中的&&,|| mysql提供:and并列 , or或
某个范围的数据: 字段名称>=值1 and 字段名称<=值2;
mysql 提供了between 值1 and 值2;
查询或的关系:字段名称 = 值1 or 字段名称= 值2 or 字段名称= 值3
mysql提供:字段名称 in(值1,值2,值3...):相当于字段名称是值1或值2的或者值3的
2)模糊查询
3)聚合函数
4)排序查询
5)分组查询
6)筛选查询
7)分页查询
-- 创建学生表
CREATE TABLE student (
id INT, -- 编号
NAME VARCHAR(20), -- 姓名
age INT, -- 年龄
sex VARCHAR(5), -- 性别
address VARCHAR(100), -- 地址
math INT, -- 数学
english INT -- 英语
);
INSERT INTO student(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);
-- 需求:查询学生的年龄大于25的学生学号,姓名,年龄,性别,和住址信息
SELECT
id '学号',
NAME '姓名',
age '年龄',
sex '性别',
address '住址'
FROM
student
WHERE age > 25 ;
-- 需求:查询学生年龄在18到22之间所有的学生信息
-- 方式1:
SELECT
*
FROM
student
WHERE
age >= 18 && age <= 22 ;
-- 方式2
SELECT
*
FROM
student
WHERE
age >= 18 AND age <= 22 ; -- Mysql提供多个条件并列是and关键字
-- 方式3:mysql提供 字段名称 between 值1 and 值2 ; 两者之间
SELECT
*
FROM
student
WHERE
age
BETWEEN 18 AND 22 ;
需求4):查询学生年龄在是18岁或者22岁或者55岁的学生的所有信息
-- 方式1:---java的||
SELECT
*
FROM
student
WHERE
age = 18 || age = 22 || age = 55;
-- 方式2: mysql提供表示或的关系的关键字:or
SELECT
*
FROM
student
WHERE age = 18
OR age = 22
OR age = 55 ;
-- 方式3:mysql提供的语法
-- select 字段列表 from 表名 字段名称 in(值1,值2,值3....值n): 和上面的等价
SELECT
*
FROM
student
WHERE age IN (18, 22, 55) ;
-- 需求:查询英语成绩不是null的学生所有信息
/*
select
*
from
student
where
english != null ;-- 查询某个字段不等于null, mysql语言中 is not null
*/
SELECT
*
FROM
student
WHERE
english IS NOT NULL ;
2)模糊查询 like
-- select 字段列表 from 表名 where 字段名称 like '模糊的语法' ;
-- a) like 后面使用 '%其他字符%' 或者 '字符%' %:代表任意多个或者单个字符(开发中模糊搜索最频繁的)
-- b)_: 一个下划线 代表单个字符,
-- 需求:查询学生中所有姓马的学生所有信息
SELECT
*
FROM student
WHERE
NAME
LIKE '%马%' ;
-- 或者下面
SELECT
*
FROM student
WHERE
NAME
LIKE '马%' ;
-- 查询学生中姓名是三个字符的学生所有信息
SELECT
*
FROM
student
WHERE NAME LIKE '___' ;
-- 查询mysql数据库中的所有带character 字符集的变量信息;
SHOW VARIABLES LIKE '%character%' ;
-- 3)聚合函数 --->查询出的结果是一个单行单列的数据
-- count(非业务字段:比如id字段,在实际开发中非空并且唯一) 查询(统计)总条数
-- count(业务字段:比如 具体信息字段):可能是null值,不会统计
-- avg(字段名称):求平均分
-- max(字段名称):求这列的最大值
-- min(字段名称):求这列最小值
-- sum(字段名称):这列求和
-- 需求:统计student2表总记录数
/*
select
count(english) '总条数' -- english 业务字段,可能有null,不会统计这条数据
from
student ;
*/
SELECT
COUNT(id) '总条数'
FROM
student ;
-- 需求:查询数学成绩最高分
SELECT
MAX(math)
FROM
student; -- 99分
-- 需求: 查询数学成绩最高分的学生信息
-- 1)查询数学最高分
-- select max(math) from student2;
-- 2)查询出数学成绩是99分的学生信息
select * from student where math = 99 ;
-- 类似于Java中 int a= 20 ; int b = 30 ; int x = a+b ;
SELECT
*
FROM
student2
WHERE math =
(SELECT MAX(math) FROM student);
-- 需求:查询数学平均分
SELECT AVG(math) FROM student;
-- 需求:查询出数学成绩大于数学平均成绩的学生信息;
-- 1)先查询出来数学平均分是多少
-- SELECT AVG(math) FROM student2;
-- 2) 查询数学成绩大于 79.5的学生所有信息
/*
select
*
from
student
where
math > 79.5;
*/
SELECT
*
FROM
student
WHERE math >
(SELECT
AVG(math)
FROM
student) ;
-- select 嵌套 select 执行效率非常低!
-- 需求:求出 英语总成绩,(求和)
SELECT SUM(IFNULL(english,0)) '英语总成绩' FROM student;
INSERT INTO student VALUES(9,'高圆圆',43,'女','西安',97,87) ;
-- 4)排序查询 order by
-- select 字段列表 from order by 字段名称 排序规则;
-- 排序规则不写:默认asc 升序 ,desc 降序
-- 需求: 按照学生成绩的升序排序
SELECT
*
FROM
student
ORDER BY
math DESC ; -- 字段名称后面没有携带排序规则,默认就是asc升序
-- 需求:数学成绩大于70的学生,按照学生成绩升序排序
-- 携带条件排序,where条件必须放在order by 前面,不能放后面
SELECT
*
FROM
student
WHERE math > 70
ORDER BY
math ASC ;
-- 针对多个字段同时排序,前面字段如果它的值相同,应该按照后面的字段来进行排序
-- select 字段列表 from order by 字段名称 排序规则,字段名称2 排序规则2... ;
-- 需求:学生的数学成绩降序排序,英语成绩升序排序,查询所有学生信息
SELECT
*
FROM
student
ORDER BY
math DESC , -- 数学降序排序
english ASC ; -- 英语升序排序
-- 5)分组查询 group by
-- 基本语法: select 字段列表 from 表名 group by 分组字段;
-- 分组字段可以在select后面查询的 ,group by的后面不能使用聚合函数
-- 需求:按照性别分组,统计每个组的总人数
SELECT
sex '性别',
-- 查询分组字段
COUNT(id) '总人数'
FROM
student
GROUP BY sex ;-- 按性别分组
-- 带条件进行分组查询, where关键字,还有group by 关键字
-- goup by 后面不能使用where条件,where 条件必须放在group by 前面,先满足条件,再参与分组
-- 需求:按照性别分组,统计每个组的总人数,条件:数学成绩不大于70分的不参与分组
-- 错误的写法
/*
select
sex '性别',
count(id) '总人数'
from
student
group by sex
where math > 70 ;
*/
SELECT
sex '性别',
COUNT(id) '总人数'
FROM
student
WHERE math > 70
GROUP BY sex ;
-- 6)筛选having
-- where,group by,having :先是满足条件,然后参与分组,在进行筛选!
-- 在上面的基础上
-- 需求
-- 按照性别分组,统计每个组的总人数,条件:数学成绩不大于70分的不参与分组, 筛选出总人数大于2的这一组
SELECT
sex '性别',
COUNT(id) '总人数'
FROM
student
WHERE math > 70
GROUP BY sex
HAVING COUNT(id)>2 ;
-- 上面这个格式优化
SELECT
sex '性别',
COUNT(id) 总人数 -- 别名
FROM
student
WHERE math > 70
GROUP BY sex
HAVING 总人数 > 2 ;
-- 分页查询 limit mysql数据库的分页查询关键字limit
-- 语法格式:select 字段列表 from 表名 limit 起始行数,每页显示的条数;
-- 起始行数从0开始
-- 起始行数=(当前页码-1)*每页显示的条数
-- 现在每页显示两条, 查询第一页数据
SELECT * FROM student LIMIT 0,2;
-- 查询第二页数据
SELECT * FROM student LIMIT 2,2;
-- 查询第三页数据
SELECT * FROM student LIMIT 4,2;
-- 查询第四页数据
SELECT * FROM student LIMIT 6,2;
-- 查询第五页数据
SELECT * FROM student LIMIT 8,2 ;
-- 查询这个student表中,限制查询4条数据
SELECT * FROM student LIMIT 4 ; -- select 字段列表 from 表名 limit 值;限制查询多少条记录
-- 查询student表
SELECT * FROM student;
四、数据库的备份与还原
1、图形界面化方式(sqlyog)
备份:
鼠标选中备份的库名, 右键-----> backup/export---->
选中sql脚本 backupdatabase as sql dump
最上面选中structure and data以及选中备份本地磁盘路径----执行即可还原:
新建库---->use 库名----> 在库上面右键 ---->import导入---选择要执行的sql脚本文件
2、命令行方式
备份:
以管理员身份进入dos窗口,不需要登录mysql
输入指令mysqldump -uroot -p密码 要备份的库名 > 指定本地磁盘上的路径D:\EE_2208\day33\code\指定sql脚本文件名称.sql
还原:
在dos窗口,要登录mysql
mysql -uroot -p ---回车 输入密码
登录成功之后
将之前的库删除
新建库
使用库
source 本地磁盘上的备份的sql脚本文件,自动会执行!
五、数据库的约束
约束操作数据库的一种行为,插入null,重复数据(非业务字段)等等---(不符合规范的数据)
默认约束 default
非空约束 not null
唯一约束 unique
主键约束 primary key
自增长约束 auto_increment
外键约束 foreign key
级联操作cascade ,必须前提是存在外键
(级联删除on delete cascade,级联修改 on update cascade)
-- 1)默认约束default
CREATE TABLE stu(
id INT, -- 编号
NAME VARCHAR(10), -- 姓名
gender VARCHAR(3) DEFAULT '女' -- 性别 --加入默认约束
);
INSERT INTO stu VALUES(1,'张三丰','男'),(2,'文章','男') ;
-- 注意事项
-- 默认约束对于直接插入null值不起作用的,只有在没有插入字段的值才起作用
-- null值在数据库中没有值,没有意义;不合法数据;这个时候默认约束起作用,
-- 当如果直接插入部分字段的时候,没有插入字段的值,默认约束
-- 2)非空约束not null
-- 字段值不能为空,不能直接插入null值
CREATE TABLE stu(
id INT,
NAME VARCHAR(10) NOT NULL, -- 非空约束
age INT
);
-- 实际注册业务中 邮箱,电话,身份证信息(真实有效的信息必须唯一的,通过真实有效的信息查询用户的信息)
-- 3)唯一约束
CREATE TABLE stu(
id INT,
NAME VARCHAR(10),
telephone VARCHAR(11) UNIQUE -- 加入唯一约束
) ;
-- 4)主键约束 primary key , 一般情况都是作用在非业务字段上 (每一张表的id字段都主键)
-- 特点:非空且唯一
CREATE TABLE stu(
id INT PRIMARY KEY, -- 加入主键约束
NAME VARCHAR(10)
);
-- 通过sql语句修改表:添加主键约束
-- alter table 表名 add CONSTRAINT(这个声明) 主键约束名 PRIMARY KEY(字段名称) ;
-- CONSTRAINT(这个声明) 主键约束名 这个可以省略
-- 5)自增长约束auto_increment, 它一般都是和主键一块用(非业务字段id,一定是非空且唯一,还自增)
-- id字段默认不插入值,那么从0开始一直逐次递增1
CREATE TABLE stu(
id INT PRIMARY KEY AUTO_INCREMENT,-- id字段主键并且是自增长的
NAME VARCHAR(10),
age INT
);
-- 6)外键约束 foreign key
-- 部门表---描述部门信息 id部门编号,dept_name 部门名称
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT, -- 部门编号
dept_name VARCHAR(10) -- 部门名称
) ;
INSERT INTO dept(dept_name) VALUES('开发部'),("测试部"),('运维部') ;
-- 创建员工表
-- id员工编号,name员工姓名,gender员工性别,dept_id 部门id号
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT , -- 员工编号
NAME VARCHAR(10), -- 姓名
gender VARCHAR(3), -- 性别
dept_id INT -- 员工所在的部门的编号
);
-- 加入级联修改和级联删除
-- 在修改或者删除主表的时候,和主表相关联的从表数据随着改动!
-- 创建员工表
-- id员工编号,name员工姓名,gender员工性别,dept_id 部门id号
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT , -- 员工编号
NAME VARCHAR(10), -- 姓名
gender VARCHAR(3), -- 性别
dept_id INT, -- 员工所在的部门的编号
CONSTRAINT -- 声明
dept_emp_fk -- 外键约束名
FOREIGN KEY (dept_id) -- 作用在从表的指定字段上
REFERENCES -- 关联
dept(id) -- 主表名(主键字段名) ;
ON DELETE CASCADE -- 级联删除
ON UPDATE CASCADE -- 级联修改
);
INSERT INTO dept(id,dept_name) VALUES(5,'运维部') ;
INSERT INTO employee (NAME,gender,dept_id)
VALUES('张三','男',1),
('李四','女',5) ,
('王五','男',4) ,
('文章','男',1),
('赵六','女',4) ;
-- 删除5号部门
DELETE FROM dept WHERE id = 5 ;
-- 直接将4号部门改为2号部门了
UPDATE dept SET id = 2 WHERE id= 4;
-- 查询部门表信息
SELECT * FROM dept ;
SELECT * FROM employee ;
六、数据库的范式(是设计数据库的一种规范要求)
1、三大范式
三大范式(规范):每个范式呈递次规范,范式级别越大,数据库中字段冗余度小
1NF:表中的每一列是不能再拆分原子数据项(最简单单独的一列,不能有复合列), 最基本的要求
2NF:在1NF基础之上
特点1)每一张表只能描述一件事情;不能一张表描述多个事情
特点2)非主键字段必须完全依赖于主键字段3NF:再2NF的继承上
1.核心思想:非主键字段中间不能产生传递依赖
2.C字段依赖于B字段,B字段依赖于A字段--->c依赖于A
七、总结问题
1、delete from 表名和truncate table 表名的区别
前者:
1)仅仅只是删除表的数据,表的结构孩子,不会删除表
2)针对带有自增长约束的字段,不影响这个字段的值(如 id字段),下次再插入数据的时候,在之前id字段继续自增;
后者:
1)不仅仅是将表的数据删除了,而且还将表给删了,自动创建一张一模一样的空表
2)针对带有自增主键的约束的字段,直接影响字段的值(id字段),下次插入数据的时候,值默认从1开始自增
2、group by 分组和having筛选的区别
1)以后是否能否使用聚合函数
分组group by的不能使用聚合函数
筛选having后面可以使用聚合函数
2)先后顺序使用不同
group by 必须在where的后面使用(如果存在条件),having是放在group by的后面
3)
group by后面都是分组字段,可以select查询的时候查询分组字段
having 后面可以聚合函数(筛选条件)