《Mysql数据库基本语法》

阿里奶奶灰

已于 2024-05-31 00:07:06 修改

阅读量1.8k

点赞数 44

文章标签：数据库 mysql oracle

于 2024-05-30 23:12:39 首次发布

本文链接：https://blog.csdn.net/hui20021118/article/details/139336925

版权

概念

Data Base：数据存储的仓库

SQL：Structure Query Language，结构查询语言

Data Base Management：执行SQL语句，实现增删改查

DQL：查询语句、DML：操作语句、DDL：定义语句

语句

常用语句

注意：以；结束语句

SHOW DATABASES; -- 显示数据库

CREATE DATABASE a;	-- 创建数据库

USE a;	-- 使用数据库

show tables;	-- 查看所有表

select version(); -- 查看版本号

desc tech_student；-- 查看表的详细信息

查询语句

简单查询

注意：*不推荐使用，因为查询效率慢，且语义不明确

select * from tech_student

select id from tech_student

select id,name from tech_student

别名

注意：

别名可以省略，

如果中间有空格，用单引号

标准是单引号，因为单引号可以在orcal中使用

select id,name as n from tech_student

select id,name n from tech_student

select id,name 'n n' from tech_student

运算符

select id, name * 12 as 姓名 from tech_student;

条件查询

注意：字符串使用’'框起来

SELECT * FROM tech_student WHERE birthday = '2022-11-07'

null

注意：null不是一个值，所以不能用=

SELECT * FROM tech_student WHERE birthday is NULL

SELECT * FROM tech_student WHERE birthday is NOT NULL

in

注意：in里面是具体的值，不是范围，而且字符串用’'括起来

SELECT * FROM tech_student WHERE name in(1,'小黑')

SELECT * FROM tech_student WHERE name not in(1,'小黑')

like

注意：查询带有%或_可以使用转义符

SELECT * FROM tech_student WHERE name like '小%'

SELECT * FROM tech_student WHERE name like '%\_'

SELECT * FROM tech_student WHERE name like '%\%'

order by

SELECT * FROM tech_student ORDER BY id ASC;

SELECT * FROM tech_student ORDER BY state DESC, name DESC; -- 按状态降序排，相同按名字降序

SELECT * FROM tech_student ORDER BY 1 DESC; -- 不建议使用，健壮性差

其他

注意between and 左小右大

>= > = < <= between and

处理函数

单行处理：一行一个结果

多行处理：多行一个结果

单行处理

lower、upper、substr、length

round、rand

case … when … then …

fomat、date_format、str_to_date

注意：iffull（任何与空运算等于null）、trim（用户输入可用），case语法

-- 字符串函数
SELECT *, LOWER(name) FROM tech_student;
SELECT *, UPPER(name) FROM tech_student;
SELECT *, LENGTH(name) FROM tech_student; 
SELECT *, trim(name) FROM tech_student; 
-- 取string左边第1位置起，1字长的字符串。
-- substr(string, -3,3)：取string右边第3位置起，3字长的字符串。结果为: ing
SELECT *, substr(name,1,1) FROM tech_student; 

-- 数字函数 			
-- column_name 必需。要舍入的字段。
-- decimals	必需。规定要返回的小数位数。
SELECT ROUND(column_name,decimals) FROM table_name 


SELECT *, rand() FROM tech_student; 


SELECT 
	*, 
	case name when 'aa' then state * 3 when 'bb' then state * 4 else state end
FROM 
	tech_student; 

-- 如果不是NULL，则返回第一个参数。 否则，IFNULL函数返回第二个参数。
SELECT *, (sn + IFNULL(null,0)) FROM tech_student; 

-- 日期函数
SELECT *, DATE_FORMAT(birthday,"%Y=%m=%d") FROM tech_student; 
-- 使用了 DATE_ADD 函数来将当前时间（NOW()）增加 36 小时
SELECT *, DATE_FORMAT(DATE_ADD(NOW(),INTERVAL 36 HOUR),"%Y=%m=%d") FROM tech_student; 
-- 字符串转为datetime，结果为2020-10-11
SELECT *, STR_TO_DATE('2020,10,11',"%Y,%m,%d") FROM tech_student;

多行处理

分组函数

min、max、avg、count、sum

注意：

count（*）和cont（price）区别

count（*）：对于整个表的每行

cont（price）：对于当前字段的每行

where中不可使用分组函数，因为执行顺序为

from、where、group by、select、order by、having

null不会参与聚合函数的计算

分组之后，查询的字段只包含聚合函数字段和分组字段，其他的没有意义

SELECT *,SUM(price) FROM tech_student ;

SELECT *,count(price) FROM tech_student ;

SELECT *,count(*) FROM tech_student ;

-- 求每个部门最大工资
SELECT dept_no,max(price) FROM tech_student GROUP BY dept_no;


-- 求每个部门中，不同岗位的最大工资
SELECT dept_no,port,max(price) FROM tech_student GROUP BY dept_no,port ORDER BY dept_no;

-- 求每个部门中，不同岗位的最大工资,最大大于3000
SELECT 
	dept_no,port,max(price) 
FROM 
	tech_student
GROUP BY 
	dept_no,port
HAVING
	max(price) > 3000
ORDER BY 
	dept_no;
	
-- 求每个部门中，不同岗位的最大工资,最大大于3000
SELECT 
	dept_no,port,max(price) 
FROM 
	tech_student
WHERE
	price > 3000
GROUP BY 
	dept_no,port
ORDER BY 
	dept_no;
		
-- 求每个部门中，不同岗位的最大工资,平均大于3000(where不能使用场景)
SELECT 
	dept_no,port,max(price) 
FROM 
	tech_student
GROUP BY 
	dept_no,port
HAVING 
	avg(price) > 1000
ORDER BY 
	dept_no;

连接查询

distinct

不重复

SELECT 
	DISTINCT name
FROM 
	Sale
;

笛卡尔积

两张表从一张表与另一张表进行全部对应，表一条数*表二条数= 全条数

SELECT 
	*
FROM 
	sale, sale_grade

避免笛卡尔积

避免笛卡尔积，添加where条件

SELECT 
	*
FROM 
	sale, sale_grade
WHERE 
	sale.port = sale_grade.id;

提高效率

只添加where条件，与原本笛卡尔积效率相同，要把两张表单独的属性拿出来，用别名

SELECT 
	s.name , sg.name 
FROM 
	sale s, sale_grade sg
WHERE 
	s.port = sg.id;

避免笛卡尔积连接，提高效率

连接

内连接

等值连接

把连接和二次筛选连接分开，更清晰（推荐使用，两张表的id连接）

SELECT 
	s.name , sg.name 
FROM 
	sale s
INNER JOIN
	sale_grade sg
ON 
	s.port = sg.id
WHERE
	s.name != '豆腐';

非等值连接

筛选条件是一个范围

SELECT 
	s.name, s.price, sg.name
FROM 
	sale s
INNER JOIN
	sale_grade sg
ON 
	s.price BETWEEN sg.min_price AND sg.max_price

内连接（自连接）

一张表看成两张表（运用上级领导在同一张表）

SELECT 
	s1.name, s2.name
FROM 
	sale s1
INNER JOIN
	sale s2
ON 
	s1.p_id = s2.id

外连接

有左、右外连接，可以确定主次关系在左还是右，并且left和right可以相互转换

用法：查询所有员工所在的部门，或者所有部门所在的员工

SELECT 
	s1.name, s2.name
FROM 
	sale s1
RIGHT JOIN
	sale s2
ON 
	s1.p_id = s2.id

内接和外接的区别

内连接：无主次关系，都是平等的

外连接：有主次关系

多表连接

在原本连接的基础上再连接

SELECT 
	s.name, sp.name, sg.name, s2.name '上级'
FROM 
	sale s
JOIN
	sale_port sp
ON 
	s.port = sp.id
JOIN
	sale_grade sg
ON
	s.price BETWEEN sg.min_price AND sg.max_price
LEFT JOIN 
	sale s2
ON 
	s.p_id = s2.id

子查询

查询里面嵌套查询，可写在（select、from、where）

值子查询、行子查询、列子查询、表子查询

行子查询：a and b 更改为 (a,b)(a,b)

用法：查询的数据需要其他表中的数据时使用

where

求大于最低工资的员工

SELECT 
	*
FROM 
	sale s
WHERE 
 s.price > (SELECT MIN(price) FROM sale)
;

from

求平均值的等级

SELECT
	t.* ,sg.name
FROM
	(SELECT port,AVG(price) AS avg_price FROM sale s GROUP BY port) t
JOIN
	sale_grade sg
WHERE t.avg_price BETWEEN sg.min_price AND sg.max_price;

select

求员工对应的部门名，不常用，子select中只能有一个结果

SELECT 
	name,port,(SELECT p.name FROM sale_port p WHERE s.port = p.id)
FROM 
	sale s
;

UNION

结果集合并，把乘法变加法，减少表连接的次数 10 * 10 * 10 == 1000 和10 * 10 + 10 * 10 = 200

SELECT
	* 
FROM 
	sale
WHERE port = 1 or port = 2;

SELECT
	* 
FROM 
	sale
WHERE port = 1 
UNION
SELECT
	* 
FROM 
	sale
WHERE port = 2 ;

Limit

SELECT 
	*
FROM 
	`sale` 
LIMIT 0,5;
0：初始位置
5；长度

每页3条
第一页	limit 0，3
第二页	limit 3，3
第三页	limit 6，3
第四页	limit 9，3

pageSize=3
pageNo=3
limit (pageNo-1)*pageSize, pageSize

总结

语法顺序
select
	...
from	
	...
where
	...
group by
	...
having
	...
order by
	...
limit
	...
执行顺序
from
	...
where
	...
group by
	...
having
	...
select
	...
order by
	...
limit
	,,,

DDL语句

创建表

CREATE TABLE 表名(
	数据名 数据类型,
	数据名 数据类型,
	数据名 数据类型
);
常用数据类型
--varchar：可变字符串，动态分配空间(缺点速度慢)	--255，如：图像url，用户名，密码
--char：定长字符串，固定字符串(缺点不能动态分配)	--255
--bigint：大整形
--int：整形	--11
--tinyint：小整形，--255,如：性别，职位（1班主任，2系主任）
--float：浮点型	
--double：双浮点型
--date：日期，如：生日，入职日期
--datetime：大日期，如：更新时间
--blob：二进制大对象，存储视频，图片...
--clob：字符大对象，存储文章

删除表

DROP TABLE t_student;	--直接删除表，可能报错
DROP TABLE IF EXISTS t_student;	--如果存在删除
TRUNCATE TABLE t_student;	--截断表数据，速度快

复制表

CREATE TABLE t_copy AS SELECT * FROM t_student;	--复制表

DML语句

INSERT

日期转换

INSERT INTO t_student(id,name,create_time) VALUES(1,'校长',STR_TO_DATE('1990-10-10','%Y-%m-%d'))
SELECT *,DATE_FORMAT(create_time,'%Y-%m-%d') FROM t_student

简写：
INSERT INTO t_student(id,name,create_time) VALUES(1,'校长','1990-10-10')
SELECT *,create_time FROM t_student

STR_TO_DATE：字符串转日期，规定格式可自动转换
DATE_FORMAT：日期转字符串，规定格式个自动转换

date和datetime

INSERT INTO (id, name, birth, create_time) VALUES(1,'校长','1990-10-10','1990-10-10 15:10:10')

date：%Y-%m-%d
datetime：%Y-%m-%d %h:%i:%s

多行插入

INSERT INTO t_student(id,name,birth,create_time) VALUES
(1,'2',now(),now()),
(1,'2',now(),now()),
(1,'2',now(),now()),
(1,'2',now(),now());


--查询结果多行插入
INSERT INTO t_copy_student SELECT * FROM t_student

UPDATE

UPDATE t_student SET id = 100 WHERE id = 1

DELETE

DELETE FROM t_student WHERE id = 1

约束

CREATE TABLE t_student(
	id int PRIMARY KEY AUTO_INCREMENT,
	name varchar(100) NOT NULL,
    id_card	UNIQUE,
	cno int,
	FOREIGN KEY(cno) REFERENCES t_class(cno)
);

CREATE TABLE t_class(
	cno int KEY_PRIMARY AUTO_INCREMENT,
	cname varchar(100)
);

NOT NULL：不能为空
UNIQUE：唯一，可以为空
PRIMARY KEY：主键=NOT NULL + UNIQUE
FOREIGN 待绑定的属性 REFERENCES 引用外键

约束
--unsigned 无符号，如：性别

事务

概念：保证事务的一致性，要么全部成功，要么全部失败

过程：

start transaction \ begin ;	# 开启事务
commit ; # 提交
rollback ; # 回滚

四大特性：

原子性：不可再分的最小单位
一致性：要么全部成功，要么全部失败
隔离性：再提交之前无法看到数据的处理
持久性：当用户提交或者回滚时，数据改变时永久的

索引

作用：提高查找效率

结构：B+tree（矮胖树）一个节点可存放多个key，数据存放在叶子结点，叶子节点有序

分类：

主键索引：创建主键时，自动生成且效率最高
唯一索引：创建唯一索引时，自动生成
普通索引：手动创建

过程：

create index idx_user_name on tb_user(name);
show index from tb_user;
drop index idx_user_name on tb_user;

思路

先找到sql语句所需要的表，在看表与表之间的关系，最后在设计

概念