sqyog
#DDL数据库操作
/*
创建数据库
create database if not exists 库名;
创建数据库指定字符集
create database 数据库名 character set 字符集;
创建数据库并且指定排序方式
create database 数据库名 collate 排序方式;
创建数据库并且指定字符集和排序方式
create database 数据库名 character set 字符集 collate 排序方式;
查询数据库字符集和排序方式
show variables like 'character_set_database';
show variables like 'collation_database';
*/
CREATE DATABASE IF NOT EXISTS lianxi_db1 CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs;
SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';
/*
1.2数据库查看
查看所有库
show databases;
查看当前使用库
select database();
查看库下所有表
show tables from 库名;
查看创建库信息和语句
show create database 库名;
选中切换库
use 库名;
细节:对数据操作之前一定要选中库
*/
SHOW DATABASES;
USE lianxi_db1;
SELECT DATABASE();
SHOW TABLES FROM mysql;
SHOW CREATE DATABASE mysql;
/*
1.3修改库
修改字符集
alter database 库名 character set 字符集;
修改排序方式
alter database 库名 collate 排序方式;
修改字符集和排序方式
alter database 库名 character set 字符集 collate 排序方式;
注意:数据库没有修改名字指令,要备份数据,删除旧库,创建新库,恢复数据
*/
ALTER DATABASE lianxi_db1 CHARACTER SET utf8;
ALTER DATABASE lianxi_db1 COLLATE utf8mb4_0900_as_cs;
ALTER DATABASE lianxi_db1 CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs;
/*
1.4删除数据库
直接删除
drop database 库名;
判断存在删除
drop database if exists 库名;
*/
/*
1.5 库管理练习
1.创建一个名为blog_platform的数据库
2.查看数据库字符集和排序方式
3.将排序方式改为utf8mb4_0900_as_cs,以实现大小写敏感
4.查看数据库字符集和排序方式
5.删除库跑路
*/
CREATE DATABASE IF NOT EXISTS blog_platform;
USE blog_platform;
SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';
ALTER DATABASE blog_platform COLLATE utf8mb4_0900_as_cs;
SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';
DROP DATABASE blog_platform;
SHOW DATABASES;
#2.DDL之数据表操作
/*
创建表语法
create table [if not exists] 表名(
列名 类型 [列的约束条件] [列的注释],
列名 类型 [列的约束条件] [列的注释],
.....
列名 类型 [列的约束条件] [列的注释]
)[描述] [注释];
建表事项
1.表名 列名 列类型必须填写
2.推荐使用if not exists
3.注释很有必要
4.列之间使用,隔开,最后一列没有,
*/
/*
2.2 建表实战
1.创建名为book_libs的库,并且使用utf8mb4,utf8mb4_0900_as_cs
2.创建一个图书表books
图书名称book_name列,类型varchar(20),添加注释
图书价格book_price列,类型为double(4,1),添加注释
图书数量book_num列,类型为int,添加注释
*/
CREATE DATABASE IF NOT EXISTS book_libs CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs;
USE book_libs;
SELECT DATABASE();
CREATE TABLE IF NOT EXISTS books(
book_name VARCHAR(20) COMMENT '图书名字',
book_price DOUBLE(4,1) COMMENT '价格',
book_num INT COMMENT '数量'
)CHARSET = utf8mb4 COMMENT '图书表';
SHOW TABLES FROM book_libs;
/*
2.3创建表的类型(整数)
tinyint
smallint
mediumint
int
bigint
无符号要用unsigned修饰,没有负值
*/
#练习
#创建一个t1表,包含年龄,学号
USE lianxi_db1;
SELECT DATABASE();
CREATE TABLE IF NOT EXISTS t1(
t1_age TINYINT UNSIGNED COMMENT '年龄无负值',
t1_id INT UNSIGNED COMMENT '学号无负值'
);
SHOW TABLES FROM lianxi_db1;
/*
2.4创建表的类型(小数)
float(m,d) m:小数加整数位数(最大为24);d:小数位数(最大为8);
double(m,d) m:最大为53,d:最大为30;
decimal(m,d) m:最大为65,d:最大为30; (不会精度丢失)
无符号要用unsigned修饰,没有负值
*/
/*
2.5创建表的类型(字符串)
char(m) 固定长度 m:最大为255;如果不指定m默认为1;
varchar(m) 可变长度 m:最大为65535/4;必须指定m;
text 大文本类型,不用指明长度;
细节:
char中小于m会用空格补全,读取的时候忽略空格;
varchar会读取空格;
*/
/*
2.6创建表的类型(时间)
year
time
date
datetime
timestamp
演示:创建t2表,
注册日期 字段插入自动添加时间,更新数据时间不变(自动维护时间,后续修改数据时间不变)
更新日期 字段插入自动添加时间,更新数据时间改变(插入数据自动维护时间,修改数据时间改变)
*/
USE lianxi_db1;
CREATE TABLE t2(
NAME VARCHAR(20),
register_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '插入自动维护时间',
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '插入维护时间,修改数据自动更新时间'
);
SHOW TABLES FROM lianxi_db1;
/*
练习:
创建学生表student存储借书学员信息,包括姓名,年龄,身高,生日以及注册时间和更新时间;
*/
CREATE TABLE student(
student_name VARCHAR(20) COMMENT '姓名',
student_age TINYINT UNSIGNED COMMENT '年龄',
student_high DOUBLE(4,1) UNSIGNED COMMENT '身高',
student_born DATE COMMENT '生日',
student_register DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
student_update DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
);
SHOW TABLES FROM lianxi_db1;
/*
2.7修改表
添加列
alter table 表名 add 列名 类型 [first | after 原列名]
修改列名
alter table 表名 change 原列名 新列名 新类型 [first | after 原列名]
修改列类型
alter table 表名 modify 列名 新类型 [first | after 原列名]
删除列
alter table 表名 drop 列名;
修改表名
alter table 表名 rename [to] 新表名;
删除表
drop table [if exists] 表名;
清空表数据
truncate table 表名; #删除表中数据+删除表的关联记录
*/
/*
练习:
1.创建表employees
2.将表中的mobile字段修改到code字段后面
3.将birth字段改为birthday
4.修改sex字段,数据类型char(1)
5.删除字段note
6.增加字段favoriate_activity,数据类型varchar(100)
7.将表名称改为employee_info
*/
CREATE TABLE employees(
emp_num INT(11),
last_name VARCHAR(50),
first_name VARCHAR(50),
mobile VARCHAR(25),
`code` INT,
job_title VARCHAR(50),
birth DATE,
note VARCHAR(255),
sex VARCHAR(5)
);
SHOW TABLES FROM lianxi_db1;
DESC employees;
ALTER TABLE employees MODIFY mobile VARCHAR(25) AFTER `code`;#2
ALTER TABLE employees CHANGE birth birthday DATE;#3
ALTER TABLE employees MODIFY sex CHAR; #4
ALTER TABLE employees DROP note;#5
ALTER TABLE employees ADD favoriate_activity VARCHAR(100);#6
ALTER TABLE employees RENAME TO employee_info;#7
DESC employee_info;
#3 DML数据操作语言
/*
3.1插入数据
1.全列插入
insert into 表名 values(值1,值2....);
2.指定列插入
insert into 表名 (列名1,列名2..) values(值1,值2....);
3.多行插入
insert into 表名 values(值1,值2....),(值1,值2....)....; #一个括号代表一行
insert into 表名 (列名1,列名2..) values(值1,值2....),(值1,值2....)....;
*/
/*
练习
1.插入一名学生的全部信息
2.插入一名学生的学号,名字,年龄,其他默认
3.插入两名学生的信息,包括学号,名字,年龄,生日和身高
4.插入一名学生大的信息,只包括学号,名字,年龄,其他为空
*/
CREATE DATABASE IF NOT EXISTS dml_db1;
USE dml_db1;
CREATE TABLE students(
stu_id INT COMMENT '学号',
stu_name VARCHAR(100) COMMENT '姓名',
stu_age TINYINT UNSIGNED COMMENT '年龄',
stu_birthday DATE COMMENT '生日',
stu_height DECIMAL(4,1) DEFAULT 200 COMMENT'身高'
);
INSERT INTO students VALUES (1, 'haha' , 18 , '2007-02-15' , 163);
INSERT INTO students (stu_id,stu_name,stu_age) VALUES (2,'xiangxiang',18);
INSERT INTO students VALUES (3,'haha',18,'2007-02-15',163),(4,'haha',18,'2007-02-15',163);
INSERT INTO students (stu_id,stu_name,stu_age,stu_birthday,stu_height) VALUES (5,'xiangxiang',18,NULL,NULL),(6,'xiangxiang',18,NULL,NULL);
DESC students;
/*
3.2修改数据
全表修改
update 表名 set 列名 = 新值 , 列名 = 值 , 列名 = 值...
条件修改(条件筛选行)
update 表名 set 列名 = 新值 , 列名 = 值 , 列名 = 值... where 条件
*/
/*
练习:
1.把学号为3的名字改为'xixi'
2.将年龄小于20的身高加上2
3.将所有人年龄降1
*/
UPDATE students SET stu_name = 'xixi' WHERE stu_id = 3;
UPDATE students SET stu_height = stu_height + 3 WHERE stu_age <= 20;
UPDATE students SET stu_age = stu_age - 1;
/*
3.3删除数据
全表删除
delete from 表名;
条件删除
delete from 表名 where 条件;
*/
/*
练习
1.删除身高为203的
2.将身高为166的且年龄小于20的数据移除
3.将所有学生全部移除
*/
DELETE FROM students WHERE stu_height = 203;
DELETE FROM students WHERE stu_height = 166 AND stu_age <20;#或是or
DELETE FROM students;
#4.DQL数据查询语句
/*
4.1基本语法
1.非表查询
select关键字,快速输出一个运算结果或函数
2.指定表查询
查询结果来自一张表或多张表
select 列名,列名,... from 表名
select 表名.列名,列名,... from 表名 #表名.列名 多表的时候要这么用,多表列名可能重复
select * , 表名.* | from 表名; * 就是所有列
3.查询列并起别名
select 列名 as 别名 , 列名 别名 ... from 表;
4.去除重复行数据
select distinct 列名 , 列名 , 列名 from 表名;
5.查询常数列
select 列 , 列名 , '值' as 列名 from 表;
*/
SELECT NOW();#1 #查询当前时间
SELECT * FROM students;#2 #查询全部信息
SELECT stu_id,stu_name FROM students;#2 #查询学号和姓名
SELECT stu_name AS NAME , stu_age FROM students;#3 #查询名字和年龄,将名字显示为name而不是stu_name
SELECT DISTINCT stu_name , stu_age FROM students;#4 查询名字年龄并且去重
SELECT stu_name , stu_age , 'zjnu' university FROM students; #5 #查询名字年龄并且添加常数列学校'zjnu';
/*
4.2显示表结构
使用命令查看表的列和列的特征
describe 表名;
desc 表名;
*/
DESC students;
/*
4.3 条件查询
select 列 from 表 where 条件 [and][or] [条件];
*/
SELECT DISTINCT stu_id,stu_name FROM students WHERE stu_id = 3;
/*
4.4 运算符使用
1.1 计算运算符
+ - * /(浮点除法) div % mod
细节: /0 不会报错 是null
1.2 比较运算符
等于 = <=>(安全等于) 例:null=null是false , null<=>null是true
判空: is null is not null
不等于对比: <> !=(方言)
区间比较: between min and max, not between min and max
范围比较: in(a,b) not in(a,b) 不是判断范围,而是判断两个值; a=x 并且 b=y 才能是true
模式匹配: like , not like ; _表示一个字符, %表示零个或多个字符; '张伟' like '张_' 是true;
*/
/*
练习:
1.查询学号在2,3之间的;
2.查询身高为1开头的;
3.查询生日是空的
*/
SELECT * FROM students WHERE stu_id BETWEEN 2 AND 4;
SELECT * FROM students WHERE stu_height LIKE '1%';
SELECT * FROM students WHERE stu_birthday IS NULL;
/*
逻辑运算符
且:and , &&
或: or , ||
否: ! , not
异或: xor(相同为0,不同为1)
细节:可以将多个条件拼接,非0就是true
*/
/*
4.5 函数
abs()绝对值,ceil() 向上取整 floor()向下取整 rand()随机数 rand(x)种子随机数 round(x)对x四舍五入 round(x,y)指定小数位y位 truncate(x,y)将x截取为y位小数
*/
SELECT ABS(-3) , CEIL(3.3) , FLOOR(3.3) , RAND() , ROUND(4.444,2) , TRUNCATE(4.444,1) ,RAND(3) , RAND(3);
/*
字符串函数
char_length(x)返回x字符的长度 , concat(x,x,x,x)多个字符拼接 , find_in_set(x,y)在y中找x出现的位置 ,
*/
SELECT CHAR_LENGTH('xiangxiang') , CONCAT('haha','ai','xiangxiang') , FIND_IN_SET('bb','aa,bb,cc');
/*
时间函数
获取当前时间:now() , curdate() , curtime() 考虑当前时区 | utc_date() , utc_time() 不考虑当前时区
时间部分提取:year(时间) , month(时间) , week(时间)第几周 , day(时间) , weekday(时间)周几(0是星期一) , dayofweek(时间)1是星期天 2是星期一
*/
SELECT NOW() , CURDATE() , CURTIME() , WEEK(NOW()) , WEEKDAY(NOW()) , DAYOFWEEK(NOW()) , YEAR(NOW()) , MONTH(NOW()) ,DAY(NOW());
/*
时间运算
date_add | adddate(时间锚点,interval +-值 运算时间单位的英文 year month ...)加时间 ,date_sub | subdate(时间锚点,interval +-值 运算时间单位的英文 year month ...)减时间
addtime(时间,秒) 时间的加减秒的运算
datediff(日期,日期) 算两个日期间隔的天
timediff(时间,时间) 算两个时间间隔的时分秒
*/
SELECT ADDDATE(NOW(),INTERVAL 1 YEAR) , ADDDATE(NOW(),INTERVAL -1 MONTH) , ADDTIME('10:10:10',30) , DATEDIFF(CURDATE(),'2025-11-11') , TIMEDIFF(CURTIME(),'12:00:00');
/*
时间格式化函数
date_format(时间,'格式化字符串') , time_format(时间,'格式化字符串') 格式化生成一个自定义时间字符串
str_to_date('非标准的时间字符串','非标准时间字符串对应的格式') 非标准时间字符串->标准时间
*/
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') , TIME_FORMAT(NOW(),'%H:%i:%s') , STR_TO_DATE('2006-08-11','%Y-%m-%d');
/*
练习:
1.查询今天过生日的学生
*/
USE dml_db1;
SELECT * FROM students WHERE MONTH(stu_birthday) = MONTH(NOW()) AND DAY(stu_birthday) = DAY(NOW());
SELECT * FROM students WHERE STR_TO_DATE(stu_birthday,'%m-%d') = STR_TO_DATE(NOW(),'%m-%d');
/*
流程控制函数
if(condtion,true_value,false_value)类似于三目运算符 , ifnull(column,null_value)判断是否为空并赋空值
case关键字:处理多流程结果
第一种语法: case when 表达式 then result when 表达式2 then result ...else default_value end from 表名;
第二种语法: case 列名 | 表达式 when 值 then result when 值 then result2 ...else default_value end from 表名;
*/
#练习:查询学生身高,身高伪163的加上3,否则减去3;
SELECT stu_height , IF(stu_height=163,stu_height+3,stu_height-3) AS new_height FROM students;
#练习:查询学生编号,大于3的加1否则减1
SELECT stu_id ,
CASE
WHEN stu_id>3 THEN stu_id+1
WHEN stu_id<=3 THEN stu_id-1
ELSE 0 END FROM students;
/*
#2.多行函数(聚合函数)
avg(列名)->数值类型 ,
sum(列名)->数值类型 ,
min|max(列名)->任意类型 ,
count(列名/ * /1)计算总条数 ->任意类型
*/
USE dml_db1;
SELECT COUNT(*) , SUM(stu_id) , AVG(stu_height) FROM students;
/*
4.6分组查询
将数据行分成若干小组,最终统计的是组的特性和数据
group by 分组列,分组列...having 分组后的比较;
细节:1.分组查询只能查询分组字段和聚合函数
2.having是分组后的条件 | where是分组前的条件
*/
#查询不同编号学生的身高平均值
SELECT stu_id , AVG(stu_height) FROM students GROUP BY stu_id;
#查询身高高于170的不同编号的学生的平均身高
SELECT stu_id , AVG(stu_height) FROM students WHERE stu_height >170 GROUP BY stu_id;
/*
排序查询
order by 列名 asc | desc , 列名 asc | desc;
细节:1.多列排序只有上一列相同,下一列才生效;
2.asc正序,desc倒序
3.asc是默认的,可以不写
*/
#按学号正序排序,查询学生信息
SELECT * FROM students ORDER BY stu_id ASC;
/*
数据切割(分页查询)
select语句 limit [offset偏移量 , ] number ;
偏移量不写默认为0, number是查多少行;
*/
SELECT *FROM students LIMIT 3;
SELECT * FROM students ORDER BY stu_id LIMIT 4 , 4;
#select语句关键字顺序
#select...from...where...group by...having...order by...limit...;
#执行顺序:from->where->group by->having->select->order by->limit;
#细节:where后不能用select产生的列(别名);
/*
总结练习
*/
CREATE DATABASE IF NOT EXISTS test04_lib;
USE test04_lib;
CREATE TABLE IF NOT EXISTS books(
id INT COMMENT '书编号',
`name` VARCHAR(50) COMMENT '书名',
`authors` VARCHAR(100) COMMENT '作者',
price FLOAT COMMENT '价格',
pubdate YEAR COMMENT '出版日期',
note VARCHAR(100) COMMENT '说明',
num INT COMMENT '库存'
);
DESC books;
INSERT INTO books VALUES(1,'tal of AA','Dickes',23,1995,'novel',11);
INSERT INTO books VALUES(2,'EmmaT','Jane Lura',35,1993,'joke',22);
INSERT INTO books VALUES(3,'Story of Jane','Jane Tim',40,2001,'novel',0),
(4,'Lovey Day','George Byron',20,2005,'novel',30),
(5,'Old Land','Honore Blade',30,2010,'law',0),
(6,'The Battle','Upton Sara',30,1999,'medicine',40),
(7,'Rose Hood','Richard haggard',28,2008,'cartoon',28);
UPDATE books SET price=price+5 WHERE note = 'novel';
UPDATE books SET price=40 , note='drama' WHERE `name`='EmmaT';
DELETE FROM books WHERE num=0;
SELECT * FROM books WHERE `name` LIKE '%a%';
SELECT COUNT(*) , SUM(num) FROM books WHERE `name` LIKE '%a%';
SELECT * FROM books WHERE note = 'novel' ORDER BY price DESC;
SELECT * FROM books ORDER BY num DESC , note;#根据num降序排列,相同则按note升序排列
SELECT note,COUNT(*) FROM books GROUP BY note;#根据note分组统计种类数量
SELECT note , SUM(num) FROM books GROUP BY note HAVING SUM(num)>30; #根据note分组统计库存大于30的库存数量
SELECT * FROM books LIMIT 4,4; #查询所有书,每页显示四本,查询第二页
SELECT note , SUM(num) FROM books GROUP BY note ORDER BY SUM(num) DESC LIMIT 1;#显示库存最多的
SELECT * FROM books WHERE CHAR_LENGTH(REPLACE(NAME,' ',''))>6;#统计书名长度大于6的书
SELECT NAME,note,
CASE
WHEN note='novel' THEN '小说'
WHEN note='law' THEN '法律'
WHEN note='medicine' THEN'医药'
WHEN note='cartoon' THEN'卡通'
WHEN note='joke' THEN'笑话'
ELSE '其他'
END AS TYPE FROM books;
SELECT IFNULL(note,'总量') , SUM(num) FROM books GROUP BY note WITH ROLLUP;#统计每种类型书数量并统计总量
#5.约束
/*
5.1非空约束
列 not null;
限制某一个列不为null
*/
CREATE TABLE emp1(
`name` VARCHAR(20) NOT NULL,
age INT
);
DESC emp1;
ALTER TABLE emp1 MODIFY age INT NOT NULL;
/*
删除非空约束
*/
ALTER TABLE emp1 MODIFY age INT NULL;
ALTER TABLE emp1 MODIFY `name` VARCHAR(20);
/*
5.2默认约束
default 默认值
不能添加到主键和唯一约束的列上
*/
CREATE TABLE emp2(
`name` VARCHAR(20) NOT NULL DEFAULT '',
age INT DEFAULT 0
);
DESC emp2;
ALTER TABLE emp2 MODIFY age INT NOT NULL DEFAULT 0;
ALTER TABLE emp2 MODIFY NAME VARCHAR(20) NOT NULL DEFAULT 'haha';
#删除默认约束
ALTER TABLE emp2 MODIFY NAME VARCHAR(20) NOT NULL;
/*
5.3检查约束
check(检查表达式)
检查约束属于表级别的约束,不属于任一列
*/
CREATE TABLE emp3(
`name` VARCHAR(20) ,
CHECK(CHAR_LENGTH(NAME)>5),
age INT,
CHECK(age>0)
);
DESC emp3;
#添加约束
#alter table 表名 add constraint 约束名 check(表达式);
ALTER TABLE emp3 ADD CONSTRAINT age_ck CHECK(age>10);
#查看约束
SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS#固定不变的
WHERE TABLE_SCHEMA ='test04_lib'#库名
AND TABLE_NAME = 'emp3';#表名
#删除约束
#alter table 表名 drop constraint 约束名;
ALTER TABLE emp3 DROP CONSTRAINT age;
/*
5.4唯一约束
unique; 可以为空,但是有值必须唯一
可以单列唯一,也可以多列组合唯一
约束名默认是列名字
*/
CREATE TABLE emp4(
`name` VARCHAR(20) UNIQUE,
age INT UNIQUE
);
CREATE TABLE emp5(
`name` VARCHAR(20),
age INT,
UNIQUE KEY(NAME,age)#组合唯一
);
DESC emp4;
DESC emp5;
SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS#固定不变的
WHERE TABLE_SCHEMA ='test04_lib'#库名
AND TABLE_NAME = 'emp4';#表名
SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS#固定不变的
WHERE TABLE_SCHEMA ='test04_lib'#库名
AND TABLE_NAME = 'emp5';#表名
#修改约束
ALTER TABLE emp4 ADD CONSTRAINT emp4_name UNIQUE KEY(NAME,age);
#删除约束
ALTER TABLE emp5 DROP CONSTRAINT NAME;
/*
5.5主键约束
主键是区分一行不重复的列
自定义主键(人为创造的一列):
自然主键(自带属性的列):
primary key;
主键唯一
*/
CREATE TABLE emp6(
id INT PRIMARY KEY,
NAME VARCHAR(20),
age TINYINT UNSIGNED
);#或者加一行primary key(id,name)作为组合主键
ALTER TABLE emp6 ADD PRIMARY KEY(id);
#删除主键
ALTER TABLE emp6 DROP PRIMARY KEY;#不需要写主键约束名,因为表只有一个主键
/*
5.6自增长约束
建议:主键列+数字类型+自增长约束
auto_increment
*/
CREATE TABLE emp7(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
DESC emp7;
ALTER TABLE emp7 MODIFY id INT AUTO_INCREMENT;#不需要再次指定主键,主键不会被覆盖
#删除自增长约束
ALTER TABLE emp7 MODIFY id INT;
#插入自增长的数据
INSERT INTO emp7 (NAME) VALUES('haha'),('xiangxiang');
INSERT INTO emp7 (NAME) VALUES('xixi'),('nono');
INSERT INTO emp7 (id,NAME) VALUES(7,'haha'),(8,'xiangxiang');
/*
5.7外键约束
外键就是子表中的一列,这一列引用主表主键列的值
外键的值应保证与主键范围一致
一个表中可能有多个外键,外键是跨表引用其他表的主键
外键类型要与主键相同,命名最好相同
删除主表的前提是没有子表引用数据
constraint 外键约束名 foreign key (子表外键名) references 主表 (主键名) [级联配置]
#添加
alter table 表名 add constraint 外键约束名 foreign key (子表外键名) references 主表 (主键名) [级联配置]
#删除
先删除约束,再删除外键索引
查询约束名字
alter table 表名 drop foreign key 外键约束名;
show index from 子表;
alter table 子表 drop index 索引名;
*/
#主表
CREATE TABLE student1(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20)
);
#子表
CREATE TABLE score1(
cid INT PRIMARY KEY AUTO_INCREMENT,
NUMBER INT,
sid INT,
CONSTRAINT s_s_l_kf FOREIGN KEY (sid) REFERENCES student1(sid)
);
SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS#固定不变的
WHERE TABLE_SCHEMA ='test04_lib'#库名
AND TABLE_NAME = 'score2';
#主表
CREATE TABLE student2(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20)
);
#子表
CREATE TABLE score2(
cid INT PRIMARY KEY AUTO_INCREMENT,
NUMBER INT,
sid INT
#CONSTRAINT s_s_l_kf FOREIGN KEY (sid) REFERENCES student1(sid)
);
#添加外键约束
ALTER TABLE score2 ADD CONSTRAINT s_s_2_kf FOREIGN KEY (sid) REFERENCES student2(sid);
#删除外键约束
ALTER TABLE score2 DROP FOREIGN KEY s_s_2_kf;
SHOW INDEX FROM score2;
ALTER TABLE score2 DROP INDEX s_s_2_kf;
/*
约束级联设置
alter table 表名 add constraint 外键约束名 foreign key (子表外键名) references 主表 (主键名) on delete cascade on update cascade;
*/
#6.数据库多表关系
#一对一,一对多,多对多
#一对一:
CREATE TABLE empll(
e_id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
CREATE TABLE profile1(
p_id INT PRIMARY KEY AUTO_INCREMENT,
address VARCHAR(100),
e_id INT UNIQUE ,
CONSTRAINT s_s_k FOREIGN KEY (e_id) REFERENCES empll (e_id)
);
DESC profile1;
#一对多:
CREATE TABLE author(
e_id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
CREATE TABLE blog(
p_id INT PRIMARY KEY AUTO_INCREMENT,
address VARCHAR(100),
e_id INT,#外键
CONSTRAINT s_s_k FOREIGN KEY (e_id) REFERENCES author (e_id)
);
#多对多:课程和学生
CREATE TABLE student(
s_id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
CREATE TABLE course(
c_id INT PRIMARY KEY AUTO_INCREMENT,
c_name VARCHAR(10) NOT NULL,
c_teacher VARCHAR(10)
);
CREATE TABLE student_course(
sc_id INT PRIMARY KEY AUTO_INCREMENT,
s_id INT,
c_id INT,
CONSTRAINT s_s_k1 FOREIGN KEY (s_id) REFERENCES student (s_id),
CONSTRAINT s_s_k2 FOREIGN KEY (c_id) REFERENCES course (c_id)
);
#7.DQL多表查询
/*
7.1 垂直合并
union(合并且去除重复数据)
union all(合并不去除重复数据)
*/
CREATE TABLE a(
aid INT,
aname VARCHAR(10)
);
CREATE TABLE b(
bid INT,
bname VARCHAR(10)
);
INSERT INTO a VALUES(1,'aaaa'),(2,'bbbb'),(3,'cccc');
INSERT INTO b VALUES(4,'aaaa'),(5,'bbbb'),(6,'cccc');
INSERT INTO b VALUES(4,'aaaa'),(5,'bbbb'),(9,'cccc');
#去除重复合并
SELECT aid , aname FROM a
UNION
SELECT bid , bname FROM b;
#不去除重复合并
SELECT aid , aname FROM a
UNION ALL
SELECT bid , bname FROM b;
/*
7.2 水平合并
*/
/*
内连接查询
select 列 from 表1 as 别名 [inner] join 表2 别名 on 表一别名.主键=表2别名.外键;
可以将多个表的数据行水平拼接到一起
*/
-- 创建学生表
CREATE TABLE students (
id INT PRIMARY KEY,
NAME VARCHAR(50),
class_id INT,
FOREIGN KEY (class_id) REFERENCES classes(id)
);
-- 创建班级表
CREATE TABLE classes (
id INT PRIMARY KEY,
class_name VARCHAR(50)
);
-- 插入学生数据
INSERT INTO students (id, NAME, class_id) VALUES
(1, 'Alice', 101),
(2, 'Bob', 102),
(3, 'Charlie', 101);
-- 插入班级数据
INSERT INTO classes (id, class_name) VALUES
(101, 'Math'),
(102, 'English');
SELECT students.id, students.name, classes.class_name
FROM students
INNER JOIN classes ON students.class_id = classes.id
#如果有多个表就继续inner join;
/*
外链接查询
表1 别名 left|right [outer] join 表2 别名 on 主=外键
作用和内链接一致
外链接而可以通过left或者right来指定逻辑主表,逻辑主表一定会显示全;
*/
INSERT INTO classes (id, class_name) VALUES
(103, NULL);
SELECT students.id, students.name, classes.class_name
FROM students
LEFT OUTER JOIN classes ON students.class_id = classes.id;
#多表查询顺序
#select...from...left|right join on ... where ...group by... having...order by...asc|desc ...limit;
/*
自然链接
就是内连接外连接升级版
*/
/*
自链接(一种场景,特殊用法)
自连接不是新语法,是一种特殊场景(同一个表连接多次查询多表数据)
一个表中的数据,引用自身另一列的数据
*/
CREATE TABLE employees (
id INT PRIMARY KEY,
NAME VARCHAR(50),
manager_id INT #外键，引用本表的id
);
INSERT INTO employees (id, NAME, manager_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2);
#两次服用自连接
SELECT
e1.name AS Employee,
e2.name AS Manager
FROM
employees e1
LEFT JOIN
employees e2 ON e1.manager_id = e2.id;
#多次复用自连接
SELECT
e1.name AS Employee,
e2.name AS Manager,
e3.name AS Managersmanger
FROM
employees e1
LEFT JOIN
employees e2 ON e1.manager_id = e2.id
LEFT JOIN
employees e3 ON e2.manager_id = e3.id;
/*
7.3子查询
一个sql语句中嵌套一个或多个查询语句
1.标量子查询:返回一行一列,单个值
2.行子子查询:返回一行多列,一般用于插入数据值或者整体对比
3.列子子查询:返回多行单列一般用于条件对比,需要配合in any all等关键字
4.表子子查询:返回多行多列,不能用于条件,一般用于查询的虚拟的中间表
*/
CREATE TABLE employees1 (
id INT PRIMARY KEY,
NAME VARCHAR(50),
department_id INT,
salary INT
);
CREATE TABLE departments1 (
id INT PRIMARY KEY,
dept_name VARCHAR(50)
);
-- 插入部门数据
INSERT INTO departments1 (id, dept_name) VALUES
(1, 'HR'),
(2, 'IT'),
(3, 'Finance');
-- 插入员工数据
INSERT INTO employees1 (id, NAME, department_id, salary) VALUES
(1, 'Alice', 1, 6000),
(2, 'Bob', 2, 8000),
(3, 'Charlie', 2, 9000),
(4, 'David', 3, 7000),
(5, 'Eva', 2, 9000);
#标量子查询
#查出工资最高的员工姓名
SELECT NAME
FROM employees1
WHERE salary = (
SELECT MAX(salary)
FROM employees1
);
#行子子查询
#查找工资和部门与 Charlie 一样的员工
SELECT NAME
FROM employees1
WHERE (salary, department_id) = (
SELECT salary, department_id
FROM employees
WHERE NAME = 'Charlie'
);#或者把=改成 in 也行
#列子子查询
#查找工资比 IT 部门任意一个人高的员工
SELECT NAME, salary
FROM employees1
WHERE salary > ALL (
SELECT salary
FROM employees1
WHERE department_id = 2
);
#表子子查询
#找出工资最高的员工，并显示其部门名称
SELECT e.name, e.salary, d.dept_name
FROM (
SELECT *
FROM employees1
WHERE salary = (SELECT MAX(salary) FROM employees1)
) AS e
JOIN departments1 d ON e.department_id = d.id;
#1.复制表
#创建表复制表结构
CREATE TABLE emp LIKE employees1;
#使用insert语句+子查询,复制数据,此时insert不用写values
INSERT INTO emp (SELECT * FROM employees1);#也可以用where条件插入部分数据
#2.同时复制
CREATE TABLE empl1 AS(SELECT * FROM employees1);
#综合大练习:
CREATE DATABASE IF NOT EXISTS lianxi_db2;
CREATE TABLE students(
stu_id INT PRIMARY KEY,
NAME VARCHAR(50),
age INT,
gender VARCHAR(10)
);
INSERT INTO students (stu_id,NAME,age,gender)VALUES
(1,'爱丽丝',20,'女'),
(2,'鲍勃',22,'男'),
(3,'查理',21,'男');
CREATE TABLE courses(
course_id INT PRIMARY KEY,
course_name VARCHAR(50)
);
INSERT INTO courses (course_id,course_name)VALUES
(101,'数学'),
(102,'物理'),
(103,'化学');
CREATE TABLE scores(
stu_id INT,
course_id INT,
score INT,
FOREIGN KEY (stu_id) REFERENCES students(stu_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
INSERT INTO scores (stu_id,course_id ,score) VALUES
(1,101,85),
(1,102,78),
(2,101,90),
(2,102,85),
(3,101,92),
(3,102,88),
(3,103,85);
#1.查询每位学生的姓名,年龄,所选课程名称以及对应的成绩
SELECT s1.name , s1.age ,c1.course_name ,sc1.score FROM students AS s1
LEFT JOIN scores sc1 ON s1.stu_id=sc1.stu_id
LEFT JOIN courses c1 ON c1.course_id=sc1.course_id;
#2.查询没有选择任何课程的学生姓名
SELECT * FROM students WHERE stu_id NOT IN (SELECT DISTINCT stu_id FROM scores);
#3.查询每门课程平均成绩,并列出平均成绩高于所有学生平均成绩的课程
SELECT course_id , AVG(score) AS sc
FROM scores
GROUP BY course_id
HAVING sc>(SELECT AVG(score) FROM scores);
#4.查询每位学生所选课程的平均成绩,并按照平均成绩降序排列
SELECT s1.name, AVG(sc1.score) AS avg_score
FROM students AS s1
JOIN scores sc1 ON s1.stu_id = sc1.stu_id
GROUP BY s1.stu_id
ORDER BY avg_score DESC;
#8 数据库事务
#8.1
#手动提交
SET
#自动提交
SET autocommit = 1 | TRUE;
#查看是否自动提交
SHOW VARIABLES LIKE 'autocommit';
/*
set autocommit = 0| FALSE;
sql
sql
sql
sql
commit/rollback;(提交或者回滚)
*/
#8.2开启独立事物
#前提:不管是自动提交还是手动提交,都可以使用
/*start transaction ;
sql
sql
sql
commit\rollback;
#开启第二次
start transaction ;
sql
sql
sql
commit\rollback;
说明:DDL语句不支持事务,无法回滚
*/
#8.3事务的隔离性
#隔离作用:并发事务之间避免干扰
#隔离级别:事物之间隔离性的强弱,由具体隔离级别决定
/*
read-uncommited 最弱级别,
read-commited (oracle默认)
repeatable-read (mysql默认)
serializable 不会发生任何并发问题
隔离级别越高,越安全,性能越差
查看隔离级别:select @@transaction_isolation;
修改隔离级别set transaction_isolation = 隔离级别;
*/
CREATE TABLE bank(
ACCOUNT VARCHAR(20) NOT NULL UNIQUE COMMENT '账号',
money DECIMAL(30,2) COMMENT '银行金额'
);
INSERT INTO bank VALUES('zhangsan',10000),('lisi',10000);
#lisi欠zhangsan 1000元 还钱演示
#8.4用户控制权限管理
/*
1.创建用户
create user '账号' @ '访问主机地址 localhost | ip地址 | % ' identified by '密码'
2.赋予权限
grant 权限,权限,权限 on 数据库名.表名(* 表示任何库和表) to '账号'@'主机地址'
3.回收权限
revoke 权限,权限,权限 on 数据库名.表名 from '账号'@'主机地址'
4.删除用户
drop user '用户名'
5.查看用户和权限
select user,host from mysql.user;
show grants for '账号'@'主机地址'
*/
/*
8.5数据备份和还原
1.数据全量备份(备份单库所有表)
mysqldump -u username -p database_name > backup.sql
例如:mysqldump -u root -p studb > d:/studb.sql
mysql -u root -p(密码) studb1(库) < d:/studb.sql(还原)
以上都是在终端进行的不是在sql内部
*/
/*
binlog操作
*/
#1.准备原有的日志文件
RESET MASTER;
#2.准备数据,插入数据 ->000001日志文件
CREATE DATABASE lianxi_binlog;
USE lianxi_binlog;
CREATE TABLE table_0(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(40) NOT NULL
);
INSERT INTO table_0 (NAME) VALUES('二狗子'),('驴弹弹');
#3.重启一个新的日志文件->000002日志文件
FLUSH LOGS;
#4.将删除数据和插入数据植入第二个日志文件
DELETE FROM table_0 WHERE id = 2;#删除驴弹弹
INSERT INTO table_0 (NAME) VALUES('狗剩子');
SELECT * FROM table_0;
#1.查看日志文件和命令清单
#查看有哪些日志文件
SHOW BINARY LOGS;
#查看日志文件命令清单
SHOW BINLOG EVENTS;#查看第一个日志的清单
SHOW BINLOG EVENTS IN '清单文件名' (FROM pos LIMIT OFFSET , NUMBER);#from从哪个位置开始查询
#练习:
SHOW BINARY LOGS;
SHOW BINLOG EVENTS;
SHOW BINLOG EVENTS IN 'LAPTOP-BA8TSI01-bin.000002';
SHOW BINLOG EVENTS IN 'LAPTOP-BA8TSI01-bin.000002' FROM 320 LIMIT 0 , 5;
#2.查看详细内容
#mysqlbinlog -v binlog日志文件(例如:LAPTOP-BA8TSI01-bin.000001)(这个命令在cmd中执行,不是在MySQL中执行)
#跳过步骤找回数据
mysqlbinlog LAPTOP-BA8TSI01-bin.000001 > d:/my_binlog.000001.sql#将其他日志完整导出
mysqlbinlog --stop-POSITION=删除命令开始的pos LAPTOP-BA8TSI01-bin.000002 > d:/my_binlog.390.sql#02日志删除之前
mysqlbinlog --start-POSITION=删除命令下一个命令开始pos LAPTOP-BA8TSI01-bin.000002 > d:/my_binlog.441.sql#02日志删除之后
#练习:
SHOW BINLOG EVENTS IN 'LAPTOP-BA8TSI01-bin.000002';
mysqlbinlog LAPTOP-BA8TSI01-bin.000001 > d:/bin001.sql
mysqlbinlog --stop-POSITION=390 LAPTOP-BA8TSI01-bin.000002 > d:\bin002.sql
mysqlbinlog --start-POSITION=440 LAPTOP-BA8TSI01-bin.000002 > d:\bin003.sql
mysql -u root -p(密码) <d:\bin001.sql
mysql -u root -p(密码) <d:\bin002.sql
mysql -u root -p(密码) <d:\bin003.sql
#8.6 窗口函数
/*
多行函数 over (partition by 列名 order by 列名 desc|asc)
*/
#例子:
USE lianxi_db2;
SELECT stu_id , course_id,score,AVG(score) OVER() FROM scores;
#查询不同id的平均分数
SELECT stu_id , course_id ,score, AVG(score) OVER(PARTITION BY stu_id)FROM scores;
#1.序号函数:row_number()
#按id,按分数从高到低排序
SELECT ROW_NUMBER() OVER(PARTITION BY stu_id ORDER BY score DESC) AS num , stu_id , course_id ,score FROM scores;
#2.序号函数:rank() 并列排序会跳过重复值 例:1,2,2,4
#使用rank()函数获取course_id为'101'的分数最高的两款商品
SELECT * FROM (
SELECT RANK() OVER (PARTITION BY course_id ORDER BY score DESC) AS row_num,
stu_id,course_id ,score FROM scores) t
WHERE course_id=101 AND row_num<=2;
#3.分布函数:percent_rank()
#4.前后函数:lag(expr,n)
#5.首位函数:first_value(expr)
#6.
#9.实战练习:
CREATE DATABASE test09_sql;
USE test09_sql;
CREATE TABLE employees(
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department_id INT
);
CREATE TABLE departments(
department_id INT PRIMARY KEY,
department_name VARCHAR(50)
);
CREATE TABLE salaries(
employee_id INT PRIMARY KEY,
salary DECIMAL(10,2)
);
CREATE TABLE managers(
manager_id INT PRIMARY KEY,
department_id INT
);
INSERT INTO employees VALUES
(1,'John','Doe',1),
(2,'Jane','Smith',2),
(3,'Michael','Johnson',1),
(4,'Emily','Brown',NULL),
(5,'David','Williams',3),
(6,'Sarah','Jones',1);
INSERT INTO departments VALUES
(1,'HR'),
(2,'Finance'),
(3,'IT');
INSERT INTO salaries VALUES
(1,50000),
(2,60000),
(3,55000),
(4,48000),
(5,70000),
(6,52000);
INSERT INTO managers VALUES
(1,1),
(2,2),
(3,3);
#1.找出每个部门平均工资
SELECT e1.department_id ,AVG(salary) FROM employees AS e1
LEFT JOIN
salaries AS s1 ON e1.employee_id = s1.employee_id
GROUP BY e1.department_id;
#2.列出每个部门的经理姓名; 和他们管理的员工数目
SELECT CONCAT(e.first_name ,e.last_name)FROM employees AS e
LEFT JOIN
managers AS m ON e.department_id = m.department_id
WHERE e.employee_id IN (SELECT manager_id FROM managers);
#正确:
SELECT d.department_name , CONCAT(e1.first_name,e1.last_name) AS manager_name ,COUNT(1) ct FROM departments d
LEFT JOIN managers m ON d.department_id=m.department_id
LEFT JOIN employees e1 ON e1.employee_id=m.manager_id
LEFT JOIN employees e2 ON e2.department_id=d.department_id
GROUP BY manager_name ,d.department_name;
#3.列出没有分配到部门的员工
SELECT CONCAT(e1.first_name,e1.last_name) FROM employees AS e1 WHERE e1.department_id<=>NULL;
#4.列出每个部门的员工数目以及该部门总工资
SELECT d.department_name,SUM(salary) FROM employees AS e LEFT JOIN salaries AS s ON e.employee_id=s.employee_id
LEFT JOIN departments AS d ON e.department_id=d.department_id
GROUP BY department_name ;
#5.列出每个员工的名字和薪资等级(低于平均工资为低等,高于平均工资为高等)
SELECT CONCAT(e.first_name,e.last_name),s.salary,
CASE
WHEN s.salary < AVG(salary) OVER() THEN '低级别'
ELSE '高级别'
END AS salary_level
FROM employees AS e
LEFT JOIN salaries AS s ON e.employee_id=s.employee_id;
#6.查出薪资排名前4的员工
SELECT CONCAT(e.first_name,e.last_name) , s.salary FROM employees AS e LEFT JOIN salaries AS s ON e.employee_id = s.employee_id ORDER BY s.salary DESC LIMIT 0,4;
#7.找出至少有两名员工的部门
SELECT department_id , COUNT(1) num FROM employees GROUP BY department_id HAVING num>2;
#8.找出每个部门的平均工资,但是要排除经理
SELECT e.department_id , AVG(s.salary) FROM employees AS e
LEFT JOIN salaries AS s ON e.employee_id = s.employee_id
WHERE e.employee_id NOT IN (SELECT manager_id FROM managers)
GROUP BY e.department_id;
#9.列出每个部门的员工姓名,薪资,以及该部门内薪资排名
SELECT CONCAT(e.first_name,e.last_name) , s.salary , e.department_id ,
RANK() OVER(PARTITION BY e.department_id ORDER BY s.salary DESC)
FROM employees AS e
LEFT JOIN salaries AS s ON e.employee_id = s.employee_id ;
#10.找出每个部门薪资最低的员工
SELECT * FROM (SELECT CONCAT(e.first_name,e.last_name) , s.salary , e.department_id ,
RANK() OVER(PARTITION BY e.department_id ORDER BY s.salary ASC) num
FROM employees AS e
LEFT JOIN salaries AS s ON e.employee_id = s.employee_id) temp WHERE temp.num = 1;#当作一个临时表
本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来源 Hexo！
相关推荐
2024-10-31
文章标题
cc
2025-05-08
java小知识
子类可以调用本类的有参构造父类的构造方法不能调用要用super();set和get方法不属于构造方法,可以调用
2025-05-08
java小知识1(多态中调用成员方法和变量的区别)
多态中调用成员方法和变量的区别:public class Test { public static void main(String[] args) { Animal a=new Dog(); //调用成员变量:编译看左边,运行看左边 //如果左边的类中没有,就会向上找,直到找到为止 //如果没有找到,就会报错 System.out.println(a.name);//动物 //调用成员方法:编译看左边,运行看右边 //如果左边的类中没有,就会向上找,直到找到为止 //如果没有找到,就会报错 //但是运行时调用的是右边的方法 a.show();//Dog—show }}class Animal{ String...
2025-05-09
java小知识2(接口)
public abstract class Animal { private String name; private int age; public Animal() { } public Animal(String name, int age) { this.name = name; this.age = age; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } public abstract void eat(); }接口:public...
2025-05-09
java小知识3(多态)
多态不能调用子类的特有方法只能调用重写的方法；方法编译也是看左边，但运行看右边 ------------------------------------ 想用子类特有功能需要强转，但不能乱转，可以用instanceof判断
2025-05-09
IDEA小技巧
1.ctrl+d;(向下复制一行)2.ctrl+alt+l;(格式化代码,让代码变整齐)3.alt+insert;(类中构造有参无参构造方法,set,get方法)4.ctrl+p;(查看新建对象有参构造的参数及顺序)5.alt+enter(报错时鼠标放在红色波浪线上可以生成解决方法)6.ctrl+alt+m;(选中,提取方法)7.ctrl+n(查看API帮助文档)8.shift+f6(更改循环中变量名,例如把i全变为j)9.ctrl+b(查看不认识的系统的方法)10.ctrl+f12(ctrl+n后用于查看类中所有方法)11.ctrl+b(跟进方法)12.ctrl+alt+t(包围添加while(true))
