mysql学习总结-CSDN博客

本文链接：https://blog.csdn.net/qqasww/article/details/119063792

Sql语言分类

DDL (数据定义语言) 用来创建删除存储数据用的数据库以及数据库中的表等对象，包括

CREATE　创建数据库和表等对象

DROP　删除数据库和表等对象

ALTER 　修改数据库和表等对象

DML (数据库操作语言) 用来查询或变更表中的记录包含

SELECT 查询表中的数据

INSERT 向表中插入新的数据

UPDATE 变更表中的数据

DELETE 删除表中的数据

DCL(数据库控制语言) 用来确认或者取消对数据库中数据的变更,除此之外,还可以对RDBMS的用户是否有权限操作数据库中的对象(数据库表) 进行设定, 包括

COMMIT 确认对数据库中数据的变更

ROLLBACK 取消度数据库中数据的变更

DDL

创建数据库: create database 数据库名
创建表格 : create table 表名(字段)
删除数据库 : drop database 数据库名
删除表格对象 : drop table 表名     (不是删除表格中的数据)

修改表格

增删改
alter table 表名 add|drop|modify|changde 列...

增
alter table 表名 add 列名 类型
ALTER TABLE test ADD sex VARCHAR(50);
删
alter table 表名 drop 列名
ALTER TABLE test DROP sex
改
-- 修改表的类型
alter table 表名 modify 列名 新的数据类型
ALTER TABLE test MODIFY tname VARCHAR(100) 
-- 修改列名和列类型
alter table 表名 change 旧列名 新列名 列类型
ALTER TABLE test CHANGE sex tsex CHAR(5) 
-- 修改表名
alter table 表名 rename to 新表名
ALTER TABLE test RENAME TO test1

约束

1) 主键约束 :一行记录的唯一表示 primary key      (主键约束 = 唯一约束 + 非空约束
2) 默认约束 :设置一个默认值  default
3) 唯一约束 : 该字段的值具有唯一性  unique  (可以有多个空值)
4) 非空约束 : 该字段的值不能为空 not null 
5) 外键约束 : foreign key ()

设定约束
1) 在创建表格时设置
create table 表名(
	id int primary key, -- 主键约束
	sex varchar(10) not null, -- 非空约束
	age int default 20, -- 默认约束
	num int unique -- 唯一约束
)

CREATE TABLE test1(
	id INT PRIMARY KEY, -- 主键约束
	sex VARCHAR(10) NOT NULL, -- 非空约束
	age INT DEFAULT 20, -- 默认约束
	num INT UNIQUE -- 唯一约束  
)

2) 追加约束

 -- 追加主键
 
 alter table 表名 add constraint 主键名 primary key(主键列名)
 alter table 表名 modify 列名 数据类型 primary key
 
  -- 删除主键
  alter table 表名 drop primary key

主键自增长
关键词 auto_increment
作用 : 如果添加主键值 ,系统会默认使得主键每次设置自动增加一个值

-- 创建表格时设置

create table test(
	id int primary key auto_increment
	...
);
-- 追加主键自增长
	alter table 表名 modify 列名 数据类型 primary key auto_increment

外键

外键约束

-- 追加约束
alter table 表名 add constraint 外键名 foreign key(外键列名) references 主表表名(主键列名)

1) 追加外键必须主表设置主键  
2) 从表的外键列数据必须符合主表的要求

ALTER TABLE stu ADD CONSTRAINT fk_tid FOREIGN KEY(tea_id) REFERENCES teacher(teacherid)

-- 删除外键约束
alter table 表名 drop foreign key 外键名

-- 创建表格是设置
create table 表名 (
	id int primary key,
    ...
    t_id int,
    constraint foreign key(t_id) references 主表名(tid)   
)

DML 语言

增

insert into 表名 (列1,列2...) values(值1,值2...)
如果表中所有的列都添加数据,并且添加顺序和表中列的顺序一致可以简化为
insert into 表名 values(值1,值2...)
添加的数据是另一张表的查询结果 
insert into 表(列1,列2,....) select ... from 表2...
注意: 插入的表和查询的结果需要一致

-- 创建表并且给新建的表添加数据 创建的表和另一个查询结果是一样的 
create table 表名 as select ... from ... 
-- 查询1班的学生信息，并且将查询结果存储到一个新创建的表格中
CREATE TABLE newStu AS SELECT * FROM stu WHERE class= 1

删

delete from 表名 (清空表中所有的数据 表还在)
drop table 表名 表不存在

-- 删除其中一些数据
delete from 表 where 条件

表中的列是有顺序的, 可以修改表的顺序

alter table 表名 modify 列 类型 after|first

-- 设置某列为第一列
alter table 表名 modify 列 类型 first

-- 修改某列在另一列后面

alter table 表名 modify 列1 类型 after 列2

改

update 表名 set 列1 = 值,列2 = 值 ... where 条件

-- 修改  将所有身高修改为 180 

update stu set height = 180.00
-- 将所有男生的语文成绩+2分，数学成绩-2 
update stu set ch_score= ch_score+2 ,ma_score= ma_score-2 where sex='男'

查

select 列1,列2 ... from 表名

--查询学生的年龄和姓名
select name,age from student

如果想要查询所有列, 可以使用 * 通配符 来简化命令

select * from student

条件查询

select … from 表名 where 条件筛选

条件:

比较 > < >= <= =(判断条件是否相同) 不等于判断: 有两个: != <>

空值判断 is null is not null

逻辑判断: 与and 或or 非not

-- 	a. 查询主演不是NULL的电影
SELECT * FROM fiml WHERE actor IS NOT NULL	
-- 	b. 查询导演是NULL的电影
SELECT * FROM fiml WHERE director IS NULL
-- 	c. 查询上映时间在'2015-11-11'后的的电影	
SELECT * FROM fiml WHERE showtime>2015-11-11 
-- 	d. 查询上映时间在'2015-10-10'到'2015-11-30'之间的电影
SELECT * FROM fiml WHERE showtime>'2015-10-10' AND showtime < '2015-11-30' 
-- 	f. 查询没有王宝强参与演出的电影
SELECT * FROM fiml WHERE actor != '王宝强' 
-- 	g. 查询名字是刘德华的电影
SELECT * FROM fiml WHERE NAME='刘德华'
-- 查询身高大于160的女生和身高大于175的男生
SELECT * FROM stu WHERE (height>160 AND sex='女') OR (height>175 AND sex='男')

聚合函数

在查询结果的基础上进行计算 , 计算结果进行显示
count(列名) 计算记录的条数
sum(列名)  计算该列中所有列值的和
max(列名) 最大值
min(列名) 最小值
avg(列名) 平均值

-- 2. 查询所有用户的平均年龄
SELECT AVG(age) FROM users
-- 3. 查询所有电影中的最低价
SELECT MIN(like_price) FROM users
-- 4. 查询男用户中的最高年龄
SELECT MAX(age) FROM users WHERE sex = '男'
-- 5. 查询女用户有多少人
SELECT COUNT(*) FROM users WHERE sex = '女'
-- 6. 查询在的北大街用户有哪些
SELECT * FROM users WHERE adress = '北大街'
-- 7. 查询在西大街的用户有多少人
SELECT COUNT(*) FROM users WHERE adress = '西大街'
-- 8. 查询喜欢三国演义的电影有多少人
SELECT COUNT(*) FROM users WHERE likes = '三国演义'
-- 9. 查询不在北大街的用户有多少人
SELECT COUNT(*) FROM users WHERE adress != '北大街'
-- 10.查询喜欢电影价钱少于200的用户有多少人
SELECT COUNT(*) FROM users WHERE like_price <=200

别名

1) 查询结果的显示 , 可以使用别名
2) 可以给一个查询结果起别名 相当于将查询结果作为一张虚拟的表, 表名就是别名 再次对虚拟表进行查询
3) 如果一张表的表名特别麻烦, 可以起个别名

select 列1 as 别名 ,列2 as 别名 ...from 表 as 表的别名

SELECT MIN(age) AS '最小年龄',COUNT(*) AS '个数',MAX(age),MAX(ch_score) FROM stu WHERE sex='女' AND class = 1

-- 列其实全程是表名.列名
select stu.name as '姓名' from stu
-- 可以使用表的别名来设置列名
select s.name as '姓名' from stu as s

注:

as 可以省略

select s.name ‘姓名’ from stu s

通常情况下表别名的as是省略的, 但是列的别名as不省略

去重

关键词 distinct

-- 查询所有的班级号
select distinct class from stu
-- 查询一班的性别 
SELECT DISTINCT sex FROM stu WHERE class = 1

分组查询

group by having
按照某列去进行分组计算,将计算结果显示

select ... from 表名 where 条件 group by 分组依据1,分组依据2 ... having 条件

-- 查询男女生分别有多少个
SELECT COUNT(*),sex FROM stu GROUP BY sex
-- 查询年龄在30岁以上的男女分别有多少个 
SELECT COUNT(*),sex FROM stu WHERE age >= 30 GROUP BY sex
-- 查询每个班的语文及格人数
select count(*),class from stu where score >= 60 group class


-- 分组结果进行筛选
-- 查询班级号>=2 的每个班的人数
select count(*) ,class from stu group by class having class>=2
-- 班级中最高成绩大于80分的所在的班级人数以及班级号 
SELECT COUNT(*),class FROM stu GROUP BY class HAVING MAX(ch_score) >= 90

数据类型

整数: int
小数: float double decimal
		decimal 的格式需要定义长度DECIMAL(M,D) M代表所有位数 整数位+小数位   D:代表小数位
        
日期和时间类型:
		DATE 默认格式 YYYY-MM-DD
		TIME 默认格式  HH:MM:SS
		DATETIME 年月日时分秒 YYYY-MM-DD HH:MM:SS
		
日期的储存

insert into stu(id,sname,birthday) values(1,'a','2000-10-10')

-- 插入当前时间 SYDATE()
insert into stu(id,sname,birthday) values(2,'a',SYDATE())

字符串类型
		char(长度): 固定长度的字符串数据类型 无论存储的数据长度是多少, 都按照定义的长度去占内存存储
        varchar(长度): 不定长的字符串  存储数据本身的长度去占内存, 存储的数据最大长度不超过设置

排序查询

order by 列1,列2...

select ... from 表名 
where 条件
group by 列1,列2... having
order by 列1,列2... [desc]

执行顺序

from>>where>>group by>>having>>select>>order by

-- 查询学生的语文成绩，按照顺序排列 null值是按最小的排序的 
SELECT * FROM stu ORDER BY ch_score

SELECT * FROM stu ORDER BY ch_score DESC

-- 查询学生的成绩，按照成绩、班级,身高顺序排列
SELECT name,score FROM stu ORDER by score,class,height
-- 查询学生的成绩，按照成绩（降序）、班级（升序）,身高（降序）顺序排列 
SELECT name,score FROM stu ORDER BY ch_score DESC,class,height DESC

三大范式

如何定义关系型数据库
第一范式: 针对于字段来说,每个字段,不能含有多重含义 不能含有多个数据
第二范式: 在第一范式的基础上,一个表格必须设有主键,主键必须是一行记录的唯一标记
第三范式: 在第二范式的基础上,如果一个表中,多行记录有相同的数据,需要将相同的数据存储到另一张表中,使用外键进行关联,减少数据的冗余,后期维护方便

事务

事务就是需要再同一个处理单元执行一系列更新(增删改)的集合

开启事务:
	start transaction:
结束事务
	提交事务 commit;   数据库的数据为执行了事务中操作的状态
	回滚事务 rollback; 数据库的数据回到事务开启前的状态

start transaction;

update account set momey = money - 500 where id = 1;
update account set momey = money + 500 where id = 2;

commit;
rollback;

事务的特性(ACID)

1）原子性：事务中的增删改语句，要么全部执行，要么全部不执行 
2）一致性：数据库中的数据在事务开启前和事务结束后的两种状态要保持一致 
3）隔离性：在同一时间内，多个用户使用事务对同一表中的数据进行操作，多个用户之间有一定的隔离，不会造成数据的混乱
4）持久性：在事务结束之后，数据库中的数据是永久保存的状态，即所有用户访问到一样的数据

子查询

在查询结果的基础上,在进行查询,即将查询结果当做一张虚拟表去查询

1)作为查询条件   一般查询结果是一个值
-- 查询成绩最高的学生信息
select * from stu where score > (select max(score) from stu)
-- 查询比1班的平均成绩高的所有2班的学生信息 
SELECT * FROM stu WHERE class= 2 AND ch_score>(SELECT AVG(ch_score) FROM stu WHERE class=1)

2)作为一张表去查询
-- 人数最多的班级人数 
SELECT MAX(coun) FROM (SELECT COUNT(*) AS coun,class FROM stu GROUP BY class) c1

3）子查询作为一个结果显示 -- 查询学生信息以及所有的老师人数
SELECT stu.*,(SELECT COUNT(*) FROM teacher) AS '老师人数' FROM stu

谓词

in: 主要用于等值条件判断 格式 in(值1,值2...)
-- 查询年龄为20,30,40，22的学生信息
SELECT * FROM stu WHERE age IN(20,30,40,22)

-- 查询所有男老师所带的学生 
SELECT * FROM stu WHERE teaid IN(SELECT teacherid FROM teacher WHERE sex='男')

between:在值1和值2 之间
格式: between 值1 and 值2
-- 查询年龄在20-30之间的学生 
SELECT * FROM stu WHERE age BETWEEN 20 AND 30

3）like

like '%_' 
查询有某些词的查询 
占位符：% 代表占位多个字符 张%
	   _ 代表占位一个字符 张__
-- 查询名字中有l的学生 
SELECT * FROM stu WHERE NAME LIKE '%l%'

4）*any(子查询) all(子查询)

-- 查询所有男老师所带的学生 
select * from stu where teaid = any(SELECT teacherid FROM teacher WHERE sex='男')
-- 查询比1班所有学生成绩都高的2班学生信息 
-- 一班的所有学生成绩 
SELECT * FROM stu WHERE class= 2 AND ch_score > ALL(SELECT ch_score FROM stu WHERE class=1)

5 ) case

select ... 
case 列 
when 值1 then ..
when 值2 then ..
...
else ...
end as 别名
,列n from 表


SELECT NAME,age,class,sex, 
CASE class 
WHEN 1 THEN 'A班'
WHEN 2 THEN 'B班'
else '其他'
end as '班级名'
t_name from stu


范围判断显示 
select 列1，列2... , 
case when 列n>值1 then 显示数据1 
  when 列n>值2 then 显示数据2  
  else 其他 end as 别名
  ,列n+1 from 表

6 分页

limit 关键词进行分页 是mysql特有的分页，oracle没有该关键词的分页 
格式 limit 值1，值2 
值1为条的序号：条数的序号是从0开始的
值2为一页的记录总个数：如果最后一页剩余的个数不够一页，则剩余显示即可


SELECT * FROM stu LIMIT 0,4 
SELECT * FROM stu LIMIT 4,4
SELECT * FROM stu LIMIT 8,4

级联

多表查询
select .. from 表1,表2 ... where 条件



-- 查询比lucy年龄大的学生信息 
SELECT s2.* FROM stu s1, stu s2 WHERE s1.name='lucy' AND s1.age<s2.age

-- 查询学生的信息和班级名称 
SELECT stu.*,class.classname FROM stu,class WHERE stu.classnum=class.classnum




内级联 inner join 
内级联的inner关键词可以省略 一般就是用于等值的判断
外级联 	--》左外级联 left outer join on
		  --》右外级联 right outer join on