MySQL的基本语法:
DDL语句:数据库的定义语句:
创建库,删除库,查询库,修改库的字符集
show databases; 查询有哪些库
create database 库名; 直接创建
create database if not exists 库名; 如果不存在这库名,则创建
show create database 库名; 查询指定数据库的信息(包含字符集信息)
alter database 库名 default character set 字符集名称; 修改数据库的默认的字符集
drop database 库名 ; 直接删除
drop database if exists 库名; 数据库的三大范式
1NF:数据库第一范式,
表中每一列都是不可拆分的原子数据项(每一列必须是独立的字段)
2NF:数据库第二范式,
在1NF基础上: 1.一张表描述一件事情
2. 非主键字段必须完全依赖于主键
3NF:数据库第三范式,
在2NF基础上: 非主键字段之间不能产生传递依赖
B-->C-->A-->B>--A字段(不能出现)
创建表之前:需要指定在哪个库创建
use 库名; ---- 选择指定的库; 意思进入到磁盘上的文件夹中(库)
mysql> use javaee_2208_mysql_01;
Database changed
创建表的语法:
create table 表名(
字段名称1 字段类型1,
字段名称2 字段类型2,
字段名称3 字段类型3,
...
...
...
字段名称n 字段类型n
) ;查询库中有哪些表:
show tables ;查询表结构:
desc 表名;修改表的名称:
alter table 表名 change 以前的字段名称 新的字段名称 以前的字段类型 ;
mysql> alter table student change gender sex varchar(3) ;修改表的字段类型:
alter table 表名 modify 字段名称 新的字段类型;
mysql> alter table student modify address varchar(100) ;给表添加一个新字段:
alter table 表名 add 字段名称 字段类型;
mysql> alter table student add birthday date;删除表中的某个字段:
alter table 表名 drop 字段名称;
mysql> alter table student drop socre;给表重命名:
alter table 以前的表名 rename to 新表名;
mysql> alter table student rename to stu;复制一张一模一样的表,新的表和以前的表字段都一样:
create table like 以前表名;
mysql> create table teacher like stu;删除表:
drop table 表名;
mysql> drop table teacher;drop table if exists 表名;MySQL 代码提现
CREATE DATABASE myee_2208;
-- 创建表
-- 使用库
USE myee_2208 ;
CREATE TABLE student(
id INT , -- 学生编号
NAME VARCHAR(10),-- 学生姓名
gender VARCHAR(5), -- 学生性别
age INT , -- 学生年龄
address VARCHAR(50) , -- 学生住址
birthday DATE -- 出生日期
);
-- 查询库有哪些表
SHOW TABLES student;
-- 查询表的结构
DESC student ;DML语句:
数据库的操作语句,操作表的记录,给表插入数据
-- 语法1:插入全表数据 ,插入的值要和字段类型匹配
-- insert into 表名 values(值1,值2,值3......值n) ;
INSERT INTO student VALUES(1,'高圆','女',43,'西安市','1990-02-10') ; -- 数据库语言:单引号或者双引号都可以作为字符串
-- 语法2:插入全表数据,一次插入多条
-- insert into 表名 values(值1,值2,值3......值n),(值1,值2,值3.....值n),(.....);
INSERT INTO student VALUES(2,'文文','男',35,'宝鸡市','1987-10-30'),
(3,'王宝','男',30,'渭南市','1988-11-20') ,
(4,'马伊','女',42,'上海市','1985-11-20') ;
-- 语法3:插入部分字段,没有插入的字段的值,默认值是null(没有值), 字段里面也没显示null,空字符
-- insert into 表名(字段名称1,字段名称2,...部分字段) values(值1,值2,...其他部分字段对应的值);
INSERT INTO student(id,NAME,gender,age) VALUES(5,'马保国','男',65) ;
INSERT INTO student(id,NAME,gender,age) VALUES(6,'李帅','',25) ;-- 性别有值,只是空串
-- 语法4:插入部分字段,也可以一次插入多条
-- insert into 表名(字段名称1,字段名称2,...部分字段)
-- values(值1,值2,...其他部分字段对应的值),(值1,值2,...其他部分字段对应的值),(值1,值2,...其他部分字段对应的值);
INSERT INTO student(id,NAME,gender,age,address) VALUES(6,'德玛西亚','男',20,'艾欧尼亚'),
(7,'盲僧','男',26,'祖安') ;
-- 插入表的记录语法注意事项
-- 1)如果全表数据,字段值一定要和类型匹配
-- 2)插入全表数据(或者插入部分字段),字段值总数量和表的字段数量对应上修改表的记录,(带条件修改)
-- 带条件修改 :修改单个字段 update 表名 set 字段名称1 = 值1 where 字段名称2 = 值2 ;
-- 需求:将id为7的学生姓名修改为 '瞎子'
UPDATE student SET NAME = '瞎子' WHERE id = 7 ;
-- 修改多个字段: update 表名 set 字段名称1 = 值1 ,字段名称2=值2 ,,, where 字段名称n =值n;
-- 需求:将id为3的学生他的姓名修改为'老王',年龄修改为32,地址修改为'南窑国际'
UPDATE
student
SET
NAME = '老王',
age = 32,
address = '南窑国际'
WHERE id = 3 ;
-- 不带条件修改--批量修改 (很少用)
-- 需求:修改name为"高圆圆"
UPDATE student SET NAME = '高圆圆' ;
-- drop table student ;
INSERT INTO student VALUES(8,'盲僧','女',20,'艾欧尼亚','1987-10-25') ;
-- 带条件修改: 条件和条件中间 and (并列关系)
-- 需求: 将性别是男,年龄是26的而且id是8(如果id也是8)的人的姓名修改为'瞎子'
UPDATE student SET NAME = '盲僧' WHERE id = 8 AND age = 26 AND gender ='男' ;删除表的记录:
-- 语法:delete from 表名 where 字段名称 = 值; 带条件删除
-- 带单个条件
-- 需求:删除id为8的学生
DELETE FROM student WHERE id = 8 ;
-- 带多个条件
-- 需求:删除性别是男并且它的姓名是王宝强的学生
DELETE FROM student WHERE gender = '男' AND NAME = '王宝' ;
-- 不带条件,删除全表数据(表的记录清了)
DELETE FROM student ;
-- 最基本的查询:查询表的所有记录 ,自己使用可以写*
SELECT * FROM student ;DELETE FROM 表名和 truncate table 表名两个不一样的用法
CREATE TABLE mytest(
id INT PRIMARY KEY AUTO_INCREMENT , -- 加入主键自增长约束 (数据库约束:约定用户操作数据的一种行为)
NAME VARCHAR(10),
age INT
) ;
-- 没有加入约束: 自己给设置id insert into mytest values(1,'张三丰',20),(2,'张佳宁',30) ;
-- 加入约束,不用给id
INSERT INTO mytest(NAME,age) VALUES('张三丰',20),('张佳宁',30),('李帅',25) ;
INSERT INTO mytest VALUES(15,'王宝',40) ;
-- update mytest set id = 17 where id = 15 ;
INSERT INTO mytest (NAME,age) VALUES('高圆',35) ;
-- insert into mytest values(3,'赵又',40) ;-- 重复数据就被PRIMARY KEY主键约束了
DROP TABLE mytest ;
DELETE FROM mytest ;
TRUNCATE TABLE mytest ;
-- delete from 表名和 truncate table 表名 有什么区别?(面试题)
-- 前者:
-- 1)仅仅只是删除全表数据,表还是存在---表的结构还在;
-- 2)针对非业务字段id 一般:主键(非空且唯一)自增长约束(字段会自动在上一次基础不断自增1)
-- 不会影响这个主键自增长约束的值;
-- 后者
-- 1)它不仅仅是删除全表数据,会将表干掉,而且同时会新建一模一样的空表
-- 2)直接会影响自增长主键 id值
SELECT * FROM mytest ;DQL语句:数据库查询语句
-- 基本查询
-- 查询全表数据 :自己玩的可以(是为了书写方便,可以使用*) select * from 包名
-- select * from student ;
-- 实际开发中,禁用* ,查询全表,查询全部字段
INSERT INTO student VALUES(2,'张建宁','女',30,'渭南市','1990-02-27'),
(3,'李桑','男',25,'南窑国际','1995-08-30') ;
SELECT
id, NAME,gender,age,address,birthday
FROM student
-- 查询全表字段,给定别名 as ,as可以省略
SELECT
id AS '学号',
NAME AS '姓名',
gender AS '性别',
age AS '年龄',
address AS '地址',
birthday AS '出生日期'
FROM
student ;
-- as可以省略
SELECT
id '学号',
NAME '姓名',
gender '性别',
age '年龄',
address '地址',
birthday '出生日期'
FROM
student ;
-- 查询指定字段
-- 需求:查询学生的所有学号和姓名,它的出生日期
SELECT
id '学号',
NAME '姓名',
birthday '出生日期'
FROM
student ;
-- 创建学生表2
CREATE TABLE student2 (
id INT, -- 编号
NAME VARCHAR(20), -- 姓名
age INT, -- 年龄
sex VARCHAR(5), -- 性别
address VARCHAR(100), -- 地址
math INT, -- 数学
english INT -- 英语
);
INSERT INTO student2(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);
-- 需求:查询student2这个表的中所有的字段信息同时起别名
SELECT
id '学号',
NAME '姓名',
age '年龄',
sex '性别',
address '地址',
math '学生成绩',
english '英语成绩'
FROM
student2 ;
-- 查询指定的字段
-- 需求:查询学生地址信息
SELECT
address '地址'
FROM
student2 ;
-- 基本查询:字段去重distinct
SELECT
DISTINCT address '地址'
FROM
student2;
-- 基本查询: 字段求和
-- 字段求和 ,字段类型一致
-- 需求:查询所有学生的姓名以及他们的对应的总成绩信息
SELECT
NAME '姓名',
(math+english) '总成绩'
FROM
student2;
-- 问题:数学成绩有内容的,英语成绩null ,两个相加的结果 null
-- 如果null值,mysql有自己的函数,ifnull(字段名称,给一个值); 如果这个字段名称是null,给一个固定值
SELECT
NAME '姓名',
(math + IFNULL(english, 0)) '总成绩'
FROM
student2 ;
-- 字段求和注意事项:保证这个字段类型一致!不能是int类型的字段 和varchar类型数据求和!
DQL语句: (带条件查询)
基本格式: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的
-- 需求1:查询学生的年龄是20岁的所有学生信息
SELECT
*
FROM
student2
WHERE
age = 20 ;
-- 需求2:查询学生的年龄大于25的学生学号,姓名,年龄,性别,和住址信息
SELECT
id '学号',
NAME '姓名',
age '年龄',
sex '性别',
address '住址'
FROM
student2
WHERE age > 25 ;
-- 需求3:查询学生年龄在18到22之间所有的学生信息
-- 方式1:
SELECT
*
FROM
student2
WHERE
age >= 18 && age <= 22 ;
-- 方式2
SELECT
*
FROM
student2
WHERE
age >= 18 AND age <= 22 ; -- Mysql提供多个条件并列是and关键字
-- 方式3:mysql提供 字段名称 between 值1 and 值2 ; 两者之间
SELECT
*
FROM
student2
WHERE
age
BETWEEN 18 AND 22 ;
-- 需求4):查询学生年龄在是18岁或者22岁或者55岁的学生的所有信息
-- 方式1:---java的||
SELECT
*
FROM
student2
WHERE
age = 18 || age = 22 || age = 55;
-- 方式2: mysql提供表示或的关系的关键字:or
SELECT
*
FROM
student2
WHERE age = 18
OR age = 22
OR age = 55 ;
-- 方式3:mysql提供的语法
-- select 字段列表 from 表名 字段名称 in(值1,值2,值3....值n): 和上面的等价
SELECT
*
FROM
student2
WHERE age IN (18, 22, 55) ;
-- 需求:查询学生年龄不是20岁的学生的信息
SELECT
*
FROM
student2
WHERE
age !=20 ;
-- mysql提供不等于<>
SELECT
*
FROM
student2
WHERE
age <> 20 ;
-- 需求:查询英语成绩不是null的学生所有信息
/*
select
*
from
student2
where
english != null ;-- 查询某个字段不等于null, mysql语言中 is not null
*/
SELECT
*
FROM
student2
WHERE
english IS NOT NULL ;
-- 查询某个字段为null的学生所有信息 mysql 某个字段为null 字段名称 is null
/*
SELECT
*
FROM
student2
WHERE
english = null ;
*/
SELECT
*
FROM
student2
WHERE
english IS NULL ;2.模糊查询 like
-- 2)模糊查询 like
-- select 字段列表 from 表名 where 字段名称 like '模糊的语法' ;
-- a) like 后面使用 '%其他字符%' 或者 '字符%' %:代表任意多个或者单个字符(开发中模糊搜索最频繁的)
-- b)_: 一个下划线 代表单个字符,
-- 需求:查询学生中所有姓马的学生所有信息
SELECT
*
FROM student2
WHERE
NAME
LIKE '%马%' ;
-- 或者下面
SELECT
*
FROM student2
WHERE
NAME
LIKE '马%' ;
-- 查询学生中姓名是三个字符的学生所有信息
SELECT
*
FROM
student2
WHERE NAME LIKE '___' ;
-- 查询mysql数据库中的所有带character 字符集的变量信息;
SHOW VARIABLES LIKE '%character%' ;
-- 全部变成utf8--->如果要在dos出窗口去查询表的数据,就出现乱码
-- 在dos窗口临时更改character_set_client /character_set_results/character_set_server改成gbk就可以在dos展示中文
3.聚合函数
-- 3)聚合函数 --->查询出的结果是一个单行单列的数据
-- count(非业务字段:比如id字段,在实际开发中非空并且唯一) 查询(统计)总条数
-- count(业务字段:比如 具体信息字段):可能是null值,不会统计
-- avg(字段名称):求平均分
-- max(字段名称):求这列的最大值
-- min(字段名称):求这列最小值
-- sum(字段名称):这列求和
-- 需求:统计student2表总记录数
/*
select
count(english) '总条数' -- english 业务字段,可能有null,不会统计这条数据
from
student2 ;
*/
SELECT
COUNT(id) '总条数'
FROM
student2 ;
-- 需求:查询数学成绩最高分
SELECT
MAX(math)
FROM
student2; -- 99分
-- 需求: 查询数学成绩最高分的学生信息
-- 1)查询数学最高分
-- select max(math) from student2;
-- 2)查询出数学成绩是99分的学生信息
-- select * from student2 where math = 99 ;
-- 类似于Java中 int a= 20 ; int b = 30 ; int x = a+b ;
SELECT
*
FROM
student2
WHERE math =
(SELECT MAX(math) FROM student2);
-- 需求:查询数学平均分
SELECT AVG(math) FROM student2;
-- 需求:查询出数学成绩大于数学平均成绩的学生信息;
-- 1)先查询出来数学平均分是多少
-- SELECT AVG(math) FROM student2;
-- 2) 查询数学成绩大于 79.5的学生所有信息
/*
select
*
from
student2
where
math > 79.5;
*/
SELECT
*
FROM
student2
WHERE math >
(SELECT
AVG(math)
FROM
student2) ;
-- select 嵌套 select 执行效率非常低!
-- 需求:求出 英语总成绩,(求和)
SELECT SUM(IFNULL(english,0)) '英语总成绩' FROM student2;
INSERT INTO student2 VALUES(9,'高圆圆',43,'女','西安',97,87) ;
4.排序查询
-- 4)排序查询 order by
-- select 字段列表 from order by 字段名称 排序规则;
-- 排序规则不写:默认asc 升序 ,desc 降序
-- 需求: 按照学生成绩的升序排序
SELECT
*
FROM
student2
ORDER BY
math DESC ; -- 字段名称后面没有携带排序规则,默认就是asc升序
-- 需求:数学成绩大于70的学生,按照学生成绩升序排序
-- 携带条件排序,where条件必须放在order by 前面,不能放后面
SELECT
*
FROM
student2
WHERE math > 70
ORDER BY
math ASC ;
-- 针对多个字段同时排序,前面字段如果它的值相同,应该按照后面的字段来进行排序
-- select 字段列表 from order by 字段名称 排序规则,字段名称2 排序规则2... ;
-- 需求:学生的数学成绩降序排序,英语成绩升序排序,查询所有学生信息
SELECT
*
FROM
student2
ORDER BY
math DESC , -- 数学降序排序
english ASC ; -- 英语升序排序
5.分组查询
-- 5)分组查询 group by
-- 基本语法: select 字段列表 from 表名 group by 分组字段;
-- 分组字段可以在select后面查询的 ,group by的后面不能使用聚合函数
-- 需求:按照性别分组,统计每个组的总人数
SELECT
sex '性别',
-- 查询分组字段
COUNT(id) '总人数'
FROM
student2
GROUP BY sex ;-- 按性别分组
-- 带条件进行分组查询, where关键字,还有group by 关键字
-- goup by 后面不能使用where条件,where 条件必须放在group by 前面,先满足条件,再参与分组
-- 需求:按照性别分组,统计每个组的总人数,条件:数学成绩不大于70分的不参与分组
-- 错误的写法
/*
select
sex '性别',
count(id) '总人数'
from
student2
group by sex
where math > 70 ;
*/
SELECT
sex '性别',
COUNT(id) '总人数'
FROM
student2
WHERE math > 70
GROUP BY sex ;
6.筛选查询
-- 6)筛选having
-- where,group by,having :先是满足条件,然后参与分组,在进行筛选!
-- 在上面的基础上
-- 需求
-- 按照性别分组,统计每个组的总人数,条件:数学成绩不大于70分的不参与分组, 筛选出总人数大于2的这一组
SELECT
sex '性别',
COUNT(id) '总人数'
FROM
student2
WHERE math > 70
GROUP BY sex
HAVING COUNT(id)>2 ;
-- 上面这个格式优化
SELECT
sex '性别',
COUNT(id) 总人数 -- 别名
FROM
student2
WHERE math > 70
GROUP BY sex
HAVING 总人数 > 2 ;
SELECT * FROM student2;
7.分页查询
-- 分页查询 limit mysql数据库的分页查询关键字limit
-- 语法格式:select 字段列表 from 表名 limit 起始行数,每页显示的条数;
-- 起始行数从0开始
-- 起始行数=(当前页码-1)*每页显示的条数
-- 现在每页显示两条, 查询第一页数据
SELECT * FROM student2 LIMIT 0,2;
-- 查询第二页数据
SELECT * FROM student2 LIMIT 2,2;
-- 查询第三页数据
SELECT * FROM student2 LIMIT 4,2;
-- 查询第四页数据
SELECT * FROM student2 LIMIT 6,2;
-- 查询第五页数据
SELECT * FROM student2 LIMIT 8,2 ;
-- 查询这个student2表中,限制查询4条数据
SELECT * FROM student2 LIMIT 4 ; -- select 字段列表 from 表名 limit 值;限制查询多少条记录
-- 查询student2表
SELECT * FROM student2;数据库备份与还原
-- 数据库的备份和还原
-- 两种方式
/**
方式1:图形界面化方式 直接sqlyog就行了
备份:
鼠标选中备份的库名, 右键-----> backup/export---->
选中sql脚本 backupdatabase as sql dump
最上面选中structure and data以及选中备份本地磁盘路径----执行即可
还原:
新建库---->use 库名----> 在库上面右键 ---->import导入---选择要执行的sql脚本文件
*/
CREATE DATABASE myee_2208;
USE myee_2208;
/*
方式2:命令行方式 去备份和还原
备份:
以管理员身份进入dos窗口,不需要登录mysql
输入指令mysqldump -uroot -p密码 要备份的库名 > 指定本地磁盘上的路径D:\EE_2208\day33\code\指定sql脚本文件名称.sql
还原:
在dos窗口,要登录mysql
mysql -uroot -p ---回车 输入密码
登录成功之后
将之前的库删除
新建库
使用库
source 本地磁盘上的备份的sql脚本文件,自动会执行!
*/
数据库的约束
约束操作数据库的一种行为,插入null,重复数据(非业务字段)等等---(不符合规范的数据)
-
默认约束 default
-- 1)默认约束default
CREATE TABLE stu(
id INT, -- 编号
NAME VARCHAR(10), -- 姓名
gender VARCHAR(3) DEFAULT '女' -- 性别 --加入默认约束
);
INSERT INTO stu VALUES(1,'张三丰','男'),(2,'文章','男') ;
-- 插入部分字段,没有插入的字段的值默认值就是null
INSERT INTO stu(id,NAME) VALUES(3,'高圆圆') ;
INSERT INTO stu VALUES(4,'张佳宁',NULL) ;
-- 注意事项
-- 默认约束对于直接插入null值不起作用的,只有在没有插入字段的值才起作用
-- null值在数据库中没有值,没有意义;不合法数据;这个时候默认约束起作用,
-- 当如果直接插入部分字段的时候,没有插入字段的值,默认约束
DROP TABLE stu ;
-- 通过sql语句将默认约束删除---修改的表的字段类型
ALTER TABLE stu MODIFY gender VARCHAR(3) ;
INSERT INTO stu(id,NAME) VALUES(4,'张建宁') ;
-- 通过sql语句加上默认约束
ALTER TABLE stu MODIFY gender VARCHAR(3) DEFAULT '女' ;-
非空约束 not null
-- 2)非空约束not null
-- 字段值不能为空,不能直接插入null值
CREATE TABLE stu(
id INT,
NAME VARCHAR(10) NOT NULL, -- 非空约束
age INT
);
INSERT INTO stu VALUES(1,'王宝',20),(2,'刘诗',25) ;
INSERT INTO stu VALUES(3,NULL,30) ;
-- 没有意义,非法数据----这个使用需要使用not null,姓名不能为null
-- 如果插入null值,Column 'name' cannot be null
DROP TABLE stu ;
-- 通过sql修改name字段类型
ALTER TABLE stu MODIFY NAME VARCHAR(10)
-- 通过sql语句加上默认约束
ALTER TABLE stu MODIFY NAME VARCHAR(10) NOT NULL ;-
唯一约束 unique
-- 实际注册业务中 邮箱,电话,身份证信息(真实有效的信息必须唯一的,通过真实有效的信息查询用户的信息)
-- 3)唯一约束
CREATE TABLE stu(
id INT,
NAME VARCHAR(10),
telephone VARCHAR(11) UNIQUE -- 加入唯一约束
) ;
INSERT INTO stu VALUES(1,'张三','13366668888'),(2,'王五','18733335555');
-- 电话号码:实际场景,必须唯一的 ,数据重复,非法数据!创建表的时候给字段加入唯一约束
INSERT INTO stu VALUES(3,'高圆圆','13366668888'); -- Duplicate entry '13366668888' for key 'telephone'重复数据
-- 通过sql语句:修改表,将唯一删除
-- alter table 表名 drop index 索引名称默认列名同名的 ;
ALTER TABLE stu DROP INDEX telephone ;
-- 通过sql:修改表,加入唯一约束 (加唯一索引)
-- alter table 表名 add constraint 唯一约束名 unique(列名就是字段名称);
ALTER TABLE stu ADD CONSTRAINT my_telepehone UNIQUE(telephone) ;
DROP TABLE stu ;-
主键约束 primary key
-- 4)主键约束 primary key , 一般情况都是作用在非业务字段上 (每一张表的id字段都主键)
-- 特点:非空且唯一
CREATE TABLE stu(
id INT PRIMARY KEY, -- 加入主键约束
NAME VARCHAR(10)
);
INSERT INTO stu VALUES(1,'高圆圆'),(2,'孙俪') ;
-- 插入id为null
INSERT INTO stu VALUES(NULL,'文文') ; -- Column 'id' cannot be null
-- 插入重复的id数据
INSERT INTO stu VALUES(2,'王宝') ; -- Duplicate entry '2' for key 'PRIMARY'
-- 非法行为,为了方便,数据库提供主键约束,非空且唯一
-- 通过sql语句删除键
-- alter table 表名 drop primary key ; -- 仅仅是删除了主键索引,非空约束还在
ALTER TABLE stu DROP PRIMARY KEY;
-- 通过sql语句修改表:添加主键约束
-- alter table 表名 add CONSTRAINT(这个声明) 主键约束名 PRIMARY KEY(字段名称) ;
-- CONSTRAINT(这个声明) 主键约束名 这个可以省略
ALTER TABLE stu ADD CONSTRAINT my_pri PRIMARY KEY(id) ;-
自增长约束 auto_increment
-- 5)自增长约束auto_increment, 它一般都是和主键一块用(非业务字段id,一定是非空且唯一,还自增)
-- id字段默认不插入值,那么从0开始一直逐次递增1
CREATE TABLE stu(
id INT PRIMARY KEY AUTO_INCREMENT,-- id字段主键并且是自增长的
NAME VARCHAR(10),
age INT
);
INSERT INTO stu (NAME,age) VALUES('霍宏运',23),('魏彤',25) ;
-- 插入指定id值
INSERT INTO stu VALUES(10,'李桑',20) ;
-- 下次不插入id值,记录上次插入数据id值
INSERT INTO stu(NAME,age) VALUES('文章',35) ;
-- mysql有一个函数: 查询到最后一次自增长的值是多少
SELECT LAST_INSERT_ID() ;
-- 自增长约束通过sql修改表的可以删除 :alter table 表名 modify 字段名称 字段类型 去掉auto_increment;
DROP TABLE stu ;
SELECT * FROM stu ;-
外键约束 foreign key
-- 6)外键约束 foreign key
-- 创建一张员工表employee,描述员工编号,员工姓名,员工性别,员工部门名称
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
NAME VARCHAR(10),
gender VARCHAR(3) ,
dept_name VARCHAR(10)
) ;
INSERT INTO employee (NAME,gender,dept_name)
VALUES('高圆圆','女','测试部'),
('张三丰','男','开发部'),
('文章','男','开发部'),
('马伊','女','运维部'),
('王宝','男','测试部'),
('张佳','女','运维部');
-- 问题: 查询员工编号,姓名以及的部分名称,部门名称信息存在"数据冗余"重复数据太多了
/*
1 高圆圆 测试部
2 张三丰 开发部
3 文章 开发布
4 马伊琍 运维部
5 xxx 测试部
如何解决呢:
一般情况下,
一张表描述一件事情,不能描述多个事情!
(打破了数据设计范式(规范))
将员工表拆分出来
员工表描述员工信息, id员工编号,name员工姓名,gender员工性别,dept_id 部门id号
部门表 ---描述部门信息 id部门编号,dept_name 部门名称
*/
DROP TABLE employee ;
-- 部门表---描述部门信息 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 -- 员工所在的部门的编号
);
INSERT INTO employee (NAME,gender,dept_id)
VALUES('高圆圆','女',2),
('张三丰','男',1),
('文章','男',1),
('马伊琍','女',3),
('王宝强','男',2),
('张佳宁','女',3);
-- 给员工表插入一个数据
INSERT INTO employee(NAME,gender,dept_id) VALUES('李帅','男',5) ;
-- 新的问题:插入的员工信息 没有这个5号部门,这种数据 非法数据!
-- 数据库---外键约束,将两张表进行关联!
-- 员工表的dept_id这个字段代表:员工的部门编号,应该关联部门表的id
-- 外键所在的表---从表---->外键作用的字段---主表(没有外键的)
-- 部门表:主表 员工表:从表
DROP TABLE employee ;
-- 直接创建表的时候添加外键约束
/*
constraint(声明) 外键约束名 -- 外键约束名:主表名_从表名_fk
foreign key (作用在从表的字段名称)
references(关联) 主表名(主键字段名);
*/
-- 创建员工表
-- 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) -- 主表名(主键字段名) ;
);-
级联操作cascade ,必须前提是存在外键
-
(级联删除on delete cascade,级联修改 on update cascade)
-- 给员工表插入一个数据
INSERT INTO employee(NAME,gender,dept_id) VALUES('李帅','男',5) ;--
-- Cannot add or update a child row :不能直接添加,或者修改从表信息
-- 加了外键约束之后,修改以及删除都很麻烦的
-- 直接删除主表:将3号部门删除
-- 1)先将在3号部门的员工删除
DELETE FROM employee WHERE id = 4 OR id = 6 ;
-- 2)再将3号部门删除
DELETE FROM dept WHERE id = 3 ; -- 不能直接删除主表,必须先将从表数据没有关联
-- 修改将测试部门的id该为4
-- 1)先将在2号部门的员工 修改了,让这个员工2号部门没有关联
UPDATE employee SET dept_id =1 WHERE id = 1 OR id=5;
-- 2)将部门表2号部门变成4号部门
UPDATE dept SET id = 4 WHERE id = 2 ;
UPDATE employee SET dept_id = 4 WHERE id =1 OR id = 5 ;
-- 跟外键相关联--- 级联操作(前提必须有外键)
-- cascade
DROP TABLE employee ;
-- 加入级联修改和级联删除
-- 在修改或者删除主表的时候,和主表相关联的从表数据随着改动!
-- 创建员工表
-- 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 ;
-- 创建表的添加外键
/*
CREATE TABLE 从表名(
字段名称1 字段类型1
字段名称2 字段类型2
外键作用的字段名称 字段类型,
CONSTRAINT -- 声明
外键约束名 -- 命名:主表名缩写_从表名缩写_fk
FOREIGN KEY (从表作用的字段名称) -- 作用在从表的指定字段上
REFERENCES -- 关联
主表名(主键字段名称) -- 主表名(主键字段名) ;
);
*/
-- 通过sql语句删除外键约束
-- alter table 表名 drop foreign key 外键约束的名称
ALTER TABLE employee DROP FOREIGN KEY dept_emp_fk ;
-- 插入不存在的部门编号数据
INSERT INTO employee (NAME,gender,dept_id) VALUES('高圆圆','女',3) ;
-- 通过sql语句添加外键约束并且同时级联修改和级联删除
/**
alter table 表名 add
CONSTRAINT -- 声明
外键约束名 -- 命名:主表名缩写_从表名缩写_fk
FOREIGN KEY (从表作用的字段名称) -- 作用在从表的指定字段上
REFERENCES -- 关联
主表名(主键字段名称) -- 主表名(主键字段名) ;
*/
ALTER TABLE employee ADD CONSTRAINT dept_emp_fk
FOREIGN KEY (dept_id) REFERENCES dept(id)ON UPDATE CASCADE ON DELETE CASCADE ;
SELECT * FROM employee ;
SELECT * FROM dept;
-- 数据库常见理论面试题
-- 数据库的范式(-----是设计数据库的一种规范要求
-- 标准规范:三大范式(规范):每个范式呈递次规范,范式级别越大,数据库中字段冗余度小
-- 1NF,2NF,3NF
-- 1NF:表中的每一列是不能再拆分原子数据项(最简单单独的一列,不能有复合列), 最基本的要求
-- 2NF:在1NF基础之上
-- 特点1)每一张表只能描述一件事情;不能一张表描述多个事情
-- 特点2)非主键字段必须完全依赖于主键字段
多表关系(理论的关系)
一对一
一种特例(多对一的一种特例)
人和身份证
一个人对应一张身份证
一张身份证从属于某个人的
一对多的关系 (开发中很频繁的)
用户和订单
一个用户可以下多个订单
一个订单(在某刻下订单节点)从属于某个用户的
部门和员工
一个部门有多个员工
一个员工从属于某个部门的
多对多的关系(比较频繁的)
商品和订单---- 中间表:订单项表
一个订单里面有多个商品
一个商品在多个订单中包含
学生和选课
USE ee_2208_01;
-- 创建部门表
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT, -- 部门编号
NAME VARCHAR(10) -- 部门名称
) ;
INSERT INTO dept(NAME) VALUES('销售部'),('市场部'),('财务部') ;
-- 创建员工表
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
NAME VARCHAR(10), -- 员工姓名
age INT, -- 员工年龄
salary INT, -- 员工工资
join_date DATE, -- 入职日期
dept_id INT, -- 员工所在的部门编号
-- 省略constraint 外键约束名
FOREIGN KEY (dept_id)
REFERENCES dept(id)
);
INSERT INTO employee(NAME,age,salary,join_date,dept_id)
VALUES
('高圆圆',43,15000,'2017-10-30',2),
('文文',35,8000,'2019-09-10',1),
('赵廷',45,12000,'2019-11-20',3),
('王强',30,9000,'2021-10-01',2),
('张宁',32,10000,'2016-12-30',1) ,
('王力',38,7000,'2017-11-20',2),
('张凡',30,8000,'2022-08-31',3);多表查询
内连接查询
-- 需求:要查询员工表和部门表的所有信息
SELECT
*
FROM
employee,dept;
-- 上面的写法不行的,这种情况---"笛卡尔乘积"
-- A表中有m条记录,B表中有n条记录,直接查询A和B表的数据---->结构 m*n 的总记录数
-- 笛卡尔乘积现象的出现,是因为表和表之间查询的时候,没有关系!
/*
查询多表的时候
1)查询哪些表 现在查询:部门表,员工表
2)查询这些表的哪些字段 全部数据:部门和员工全部
3)表和表之间的关系是什么(连接条件) 员工表所在部门dept_id 和部门表id必须一致
*/
-- 1)内连接查询
-- 1.1)隐式内连接(推荐:多去使用where条件,这个算sql优化的一种方式) ----使用select 字段列表 from 表名1,表名名2 where 连接条件;
-- 需求:要查询员工表和部门表的所有信息
SELECT
*
FROM
employee,
dept
WHERE
employee.dept_id = dept.id ;
-- 当表的名称比较长,为了方便,给表别名,通过别名访问表的字段
-- 查询指定的字段
SELECT
e.`name` '姓名',
e.`age` '年龄',
e.`salary` '工资',
e.`join_date` '入职日期',
d.`name` '部门名称'
FROM
employee e,
-- 员工表
dept d -- 部门表
WHERE e.dept_id = d.id ; -- 在有 并列条件and
-- 1.2)显示内连接
-- 语法:select 字段列表 from 表名1 (inner) join 表名2 on 连接条件; 如果有多个并列条件,后面and
-- 使用显示内连接:
-- 需求:查询员工表的编号,姓名,工资,入职日期,以及部门表的部门名称
SELECT
e.id '员工编号',
e.`name` '员工姓名',
e.`salary` '工资',
e.`join_date` '入职日期',
d.`name` '部门名称'
FROM
employee e
INNER JOIN dept d -- inner可以省略
ON e.`dept_id` = d.`id` ;外连接查询
-- 2) 外连接查询
-- 左外连接和右外连接(经常使用左外)
-
-- 插入一个员工:没有部门的员工
INSERT INTO employee (NAME,age,salary,join_date)
VALUES('李飞',35,12000,'2019-10-30') ;
-- 需求:查询员工表所有数据以及部门表的部门名称 --- 查出来的数据有问题的:没有部门的员工也得查出来
SELECT e.*,
-- 查询员工所有数据
d.`name` '部门名称' FROM employee e
JOIN dept d
ON e.`dept_id` = d.`id` ;
-- 左外连接(推荐:开发频繁使用的):将A表(左表)和B表的交集数据(有连接条件的数据)以及A表的所有全部查询
-- select 字段列表 from 表名1(左表) left outer join 表名2 on 连接条件;
-- outer可以省略不写
-- 右外连接:将A表和B表(右表)的交集数据以及B表的所有数据查询出来
-- select 字段列表 from 表名1 right outer join 表名2(右表) on 连接条件;
-- outer可以省略不写
-- 需求:查询员工表所有数据以及部门表的部门名称
SELECT
e.*,
d.`name` '部门名称'
FROM
employee e
LEFT OUTER JOIN dept d -- outer可以省略
ON e.`dept_id` = d.`id`
-- 右外
SELECT
e.*,
d.name '部门名称'
FROM
dept d
RIGHT OUTER JOIN
employee e
ON
e.`dept_id` = d.`id` ;子查询
-- 3)子查询:select嵌套select
-- 情况1:在where条件后面使用运算符之类去进行嵌套查询
-- 需求:查询最高工资的员工的姓名,年龄,工资以及部门名称信息
-- 1)查询出最高工资是多少
-- select max(salary) from employee ;-- 15000
-- 2) 查询员工工资是15000的员工姓名,年龄,工资以及部门信息
/*
select
e.`name` '姓名',
e.age '年龄',
e.`salary`,
d.`name` '部门名称'
from
employee e,
dept d
where e.`dept_id` = d.`id`
and e.`salary` = 15000 ;
*/
-- 一步走
SELECT
e.`name` '姓名',
e.age '年龄',
e.`salary`,
d.`name` '部门名称'
FROM
employee e,
dept d
WHERE e.`dept_id` = d.`id`
AND e.`salary` =
(SELECT
MAX(salary)
FROM
employee) ;
-- 需求:查询出大于平均工资员工信息(员工编号,姓名,工资,入职日期)以及对应的部门所有信息
-- 1)查询出平均工资是多少
-- select avg(salary) from employee ;-- 10125.0000
-- 2)查询出员工工资大于10125的员工的编号,姓名,工资,入职日期以及对应的所有部门信息
/*
select
e.id '员工编号',
e.`name` '姓名',
e.`salary` '工资',
e.`join_date` '入职日期',
d.id '部门编号',
d.`name` '部门名称'
from
employee e,
dept d
where e.`dept_id` = d.`id`
and e.`salary` > 10125.0000 ;
*/
-- 一步走
SELECT
e.id '员工编号',
e.`name` '姓名',
e.`salary` '工资',
e.`join_date` '入职日期',
d.id '部门编号',
d.`name` '部门名称'
FROM
employee e,
dept d
WHERE e.`dept_id` = d.`id`
AND e.`salary` >
(SELECT
AVG(salary)
FROM
employee) ;
-- 子查询情况2:使用in(值1,值2,值3...值n) in集合语句
-- 需求:查询出在市场部和财务部的员工所有信息以及部门所有信息
-- 1)查询出市场部和财务部的部门id号是多少
-- select id from dept where name = '财务部' or name= '市场部' ;
-- 2)查询在2号部门和3号部门的所有员工信息以及部门信息
/*
select
e.*,
d.*
from
employee e,
dept d
where
e.`dept_id` = d.`id`
and
e.`dept_id` in(2,3) ;
*/
-- 一步走
SELECT
e.*,
d.*
FROM
employee e,
dept d
WHERE e.`dept_id` = d.`id`
AND e.`dept_id` IN
(SELECT
id
FROM
dept
WHERE NAME = '财务部'
OR NAME = '市场部') ;
-- 3)子查询情况3
-- 将某条select语句查询的 结果当做一个"虚表",
-- 使用这个虚表和其他表关联查询
-- 需求:查询:入职日期大于'2017-12-30'的员工所有信息以及部门名称信息
-- 查询出入职日期大于'2017-12-30'的员工信息---->查出来的结果当做一个表(虚表),使用这个虚表和部门表关联查询
SELECT
t.id '员工编号',
t.name '员工姓名',
t.age '年龄',
t.salary '工资',
t.join_date '入职时间',
d.`name` '部门名称'
FROM
(SELECT
*
FROM
employee
WHERE employee.`join_date` > '2017-12-30') t
LEFT OUTER JOIN dept d -- 部门表
ON t.dept_id = d.id ;
-- 优化---纯使用where条件 隐式内连接
SELECT
e.*,
d.`name` '部门名称'
FROM
employee e,
dept d
WHERE e.`dept_id` = d.`id`
AND e.`join_date` > '2017-12-30' 
2084
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
