【MySQL】学习笔记之MySQL基础

最新推荐文章于 2024-01-05 22:39:12 发布

Kafka_Hive_Flink

最新推荐文章于 2024-01-05 22:39:12 发布

阅读量455

点赞数

分类专栏： # MySQL 文章标签： mysql sql 数据库

本文链接：https://blog.youkuaiyun.com/qq_43771096/article/details/105888615

版权

MySQL 专栏收录该内容

5 篇文章

订阅专栏

文章目录

> 数据库概述
- -- 数据库的特点和好处
- -- 数据库软件
> DML——数据查询
> DDL数据定义语言
> DCL数据控制语言
- -- 数据库事务

> 数据库概述

– 数据库的特点和好处

永久性存储数据，借助数据库软件，最终将数据存储到本地
方便管理和查询
共享
安全

– 数据库软件

Oracle、db2、sybase、sqlserver、mysql、access、baseX、sqlite

Mysql优点：体积小、开源代码、节约成本

> DML——数据查询

– 基本查询

语法：

select  *  from 表名  where 条件

– 条件查询

— between……and关键字

查询employee_id在100到120（含100和120）之间的

select * from employees where employee_id between 100 and 120;

— or关键字

查询job_id 为 ‘IT_PROG’或’AD_PU’

select * from employees where job_id ='IT_PROG'or job_id = 'AD_PU';

— and关键字

查询姓名第二个字符为’a’ 和第三个字符为’a’

select * from employees where first_name like '_a%' and  first_name like '__a%';

— in关键字

查询job_id 为 ‘IT_PROG’或’AD_PU’

select * from employees where job_id in ('IT_PROG','AD_PU');

— like关键字

查询姓名第二个字符为’a’ 和第三个字符为’a’

select * from employees where first_name like '_a%' and  first_name like '__a%';

— 转义：escape关键字

查询姓名第二个字符为_的员工信息(包含转义字符)

SELECT * FROM employees WHERE first_name LIKE '_$_%' ESCAPE '$';

— not关键字

判断某列为空

select * from employees where commission_pac is null; select * from employees where commission_pac is not  null;

– 查询排序

关键字：order by

ASC 升序排序

DESC 降序排序

— 按列排序

SELECT * FROM employees  WHERE salary>2000  ORDER BY department_id DESC,salary desc;

— 按表达式或函数排序

SELECT * FROM employees ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC

— 按别名排序

SELECT salary*12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees ORDER BY 年薪 DESC;

注：起别名的格式

①字段名别名

②字段名 ‘别名’

③字段名 “别名”

– 多表查询

查询的字段来自不同的表
笛卡尔积出现原因：两个表的连接，缺少连接条件，，结果为两个表记录条数的乘积

— 等值连接|非等值连接

等值连接：

select 字段名，字段名…… 
from 表1 别名，表2 别名…… 
where 表1的别名.字段=表2的别名.字段 
and 表1的别名.字段=表3的别名.字段 ……

非等值连接：

SELECT last_name,salary,grade_level 
FROM employees e,job_grades g 
WHERE salary BETWEEN lowest_sal AND highest_sal;

— 内连接|外链接

内连接：

Inner  join ... On..

注意：inner 和 outer 可以省略

表没有顺序关系

外链接：
外连接结果：内连接的结果 + 主表中有但从表中没有的字段

left  outer  join ... on ... 
right  outer  join ... On ...

注意： Mysql没有全外连接：full join … On

1. 两个表的顺序不能调换
2. Outer可以省略
3. 一般用外连接查询一个表有但另一个表中没有的字段

— 自连接|非自连接

自连接：查询一张表中含义一样的两个字段
案例：查询员工姓名、领导的姓名

SELECT a.employee_id,a.first_name 员工姓名,a.manager_id,b.first_name领导名称 
FROM employees a 
INNER JOIN employees b
ON a.`manager_id`=b.`employee_id`;

– 单行函数

给一个值，返回一个结果

— 日期函数

select  now();   //获取当前时间（日期+时间）

— 字符函数

①转换大小写：upper lower

SELECT UPPER('SQL Course'); SELECT LOWER('SQL Course');

②拼接字符串：concat

SELECT  CONCAT('Hello', 'World');

③截取子串（sql中索引从1开始）：substr

SELECT SUBSTR('HelloWorld',1,5);

④获取字节个数：length

SELECT  LENGTH('Hello,光头强');

⑤获取字符第一次出现的索引: instr

SELECT  INSTR('HelloWorlWod', 'Wo');

⑥用指定的字符，填充指定的位数：lpad rpad

SELECT  LPAD(salary,10,'a' ) FROM employees; SELECT  RPAD(salary, 20, '*') FROM employees;

⑦去前后指定的字符：trim

SELECT  TRIM(' ' FROM '  HelloHHHHW  oHHHrldHHHHH   ');

⑧替换：replace

SELECT REPLACE('abcdbbb','b','xxx');

— 数学函数

①四舍五入：round

SELECT ROUND(45.926, -1);

②截短：truncate

SELECT TRUNCATE(123.56,-1);

③求余：mod

SELECT MOD(-1600, 300)

— 条件表达式

①case语句

case 条件表达式 when 常量1 then 值1 when 常量2 then 值2 …… else 值3 End

【案例】查询员工表的新工资、原始工资和部门，其中部门编号为90，工资为原来 1.1倍；部门编号为100，工资为原来的1.2倍；其他部门，保持原值

SELECT department_id,salary 原始工资, 
CASE department_id    
	WHEN 90 THEN salary*1.1    
	WHEN 100 THEN salary*1.2    
	ELSE salary END 新工资 
FROM employees;

②if语句

If(条件表达式，值1，值2) ：如果条件表达式成立，返回值1；否则，返回值2

【案例】查询员工的编号、奖金、以及备注

SELECT employee_id,commission_pct, 
IF(commission_pct IS NULL,'bad','good') 备注 
FROM employees;

– 分组函数

① max() 最大值

min() 最小值

sum() 和

avg() 平均值

count() 计数

②忽略null值

③count(*) 统计行数

④分组函数可以嵌套单行函数

⑤分组函数不可以嵌套分组函数

⑥分组函数查询一般不和其他字段一起

– 分组查询

关键字：group by
关键字：having 分组后的条件
【案例】查看每个部门的人数,部门人数大于1

SELECT department_id 部门号 ,COUNT(employee_id) 人数 
FROM employees 
GROUP BY 部门号 
HAVING 人数>1;

– 子查询

特点：
①子查询要先于外查询执行，外查询用到了子查询的结果
②子查询的语句最好用小括号括起来
③子查询代替了多条sql语句的执行效果
单行子查询：子查询的结果集为一个值
多行子查询：子查询的结构集为一组值

— 单行子查询

where子句用上子查询

【案例】谁的工资比Abel高

SELECT employee_id,last_name,salary 
FROM employees WHERE salary > (    
	SELECT salary     
	FROM employees     
	WHERE last_name = 'Abel' 
);

having子句用到了子查询

【案例】查询最低工资大于50号部门最低工资的部门id和其最低工资

SELECT MIN(salary),department_id 
FROM employees 
GROUP BY department_id 
HAVING MIN(salary)>(  
	SELECT MIN(salary)      
	FROM employees     
	WHERE department_id = 50 
);

子查询用到了分组函数

【案例】查询公司最低工资

SELECT last_name,job_id,salary 
FROM employees 
WHERE salary=( 
	SELECT MIN(salary) 
	FROM employees 
);

子查询非法使用情况：子查询结果集为多个；子查询结果集为null

— 多行子查询

关键字：in、Any、All
【案例】返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工

SELECT employee_id,last_name,job_id,salary 
FROM employees 
WHERE salary<ANY( 
	SELECT salary 
	FROM employees 
	WHERE job_id = 'IT_PROG' 
 );

– 数据分页查询

语法：

select 列 from 表 limit 起始索引,行数

注意：

1、索引从 0开始

2、limit子句永远放在最后

3、分页查询

步骤1：规定每页的条目数：5
步骤2：指定的页数的起始索引
limit （页数-1）*条目数,条目数
【案例】显示第三页的所有的记录

SELECT * FROM employees LIMIT 10,5;

– 数据增加（插入）

语法

insert  into 表名【(字段名,字段名,……)】  values (值1,值2,……)

注意：
①非空列必须插入值
②字段的个数、类型、约束和值的个数、类型、约束必须一致
③字段名可以省略，但默认全部字段
④插入数据可以导入数据（复制数据）：

insert  into 表1  select  *  from表2

【案例】

INSERT INTO stuinfo(id,stuname,sex,borndate,majorId) VALUES(1,'张翠山','男','2000-9-9',2);

– 数据修改

语法

updata 表名 set 列名 = 新值  where 条件

【案例】

Update  stuinfo  set  sex =’女’ , borndate = now() Where  stuname =’周芷若’;

– 数据删除

语法：

①delete  from  表名 where 条件 
②truncate  table  表名

二者的区别：

①方式一可以加where条件，方式二不可以加

②方式二效率高

③方式二连着日志文件一起删除

> DDL数据定义语言

– 操作数据库

创建库：create database 数据库名; 删除库：drop database 数据库名;

– 操作表

创建表： create table 表名( 字段名数据类型【约束】字段名数据类型【约束】 …… 字段名数据类型【约束】 );

【案例】

CREATE TABLE student( 
id INT NOT NULL, 
sname VARCHAR(20) NOT NULL, 
sex CHAR(1) NOT NULL,  
majorId INT  
);

– 删除表结构

drop  table  表名;

– 修改表结构：

—添加字段

Alter  table 表名 add column 字段名 类型;

— 修改字段

Alter  table 表名 modify column 字段名 类型;

— 删除字段

Alter  table 表名  drop  column 字段名;

— 重命名字段

Alter  table 表名  change  列名 新列名 类型;

— 重命名表

Alter  table 表名 rename 新表名;

— 复制表（结构/数据）

只复制表中的结构

Create  table  新表 like 旧表;

既复制结构也复制数据

Create  table  新表  【as】 select  *  from  旧表;

– 数据库约束

— not null 非空约束

规定某个字段不能为空

— Unique 唯一约束（可以为空）

规定某个字段在整个表中是唯一的

— Primary key 主键约束（非空且唯一）

可以添加组合主键，但只能加表级约束

— Foreign key 外键约束

1、在从表中设置外键关系列

2、引用的列在主表中必须是主键（非空唯一）

3、引用的列和外键列的类型必须是一致，名称可以不一样

4、插入数据先插入主表，删除数据先删除从表

— Check 检查约束

check(列<100)

1、语法不报错

2、mysql不支持此约束，也就是没效果

— Default 默认值

default 值;
插入时可以遵循以下语法

INSERT INTO score(id,stuid,majorId) VALUES(3,1,1); 
INSERT INTO score VALUES(4,1,2,DEFAULT); 
INSERT INTO score VALUES(4,1,2);

— Auto_increment 自增长

自增长列必须是数值类型
一个表至多有一个
在MySQL里自增列，必须是一个键，建议和主键搭配

【案例】

CREATE TABLE test( 
	id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
	name VARCHAR(10)  DEFAULT '大力'NOT NULL 
);

— 创建表时添加约束（列、表）

列级约束
设计列的时候后面追加对应的约束

create table 表( id int not null primary key 列级约束的写法 )

语法：

列名 类型 not null 列名 类型 unique 列名 类型 primary key 列名 类型 check(条件) 列名 类型 default 值

表级约束
设计表的时候，对所有列的添加约束
语法：

CONSTRAINT pk PRIMARY KEY(id),    主键 
constraint uq unique(stuid),  唯一 
CONSTRAINT ck CHECK(score>=0 AND score<=100), 检查 
CONSTRAINT fk FOREIGN KEY(stuid) REFERENCES stuinfo(id)  外键

注意：非空、默认只能添加列级约束,外键只能添加表级约束

【表级约束案例】

CREATE TABLE score( 
 	 id INT , 
	 stuid INT  ,
	 majorId INT , 
	 score FLOAT DEFAULT 100, 
	 CONSTRAINT pk PRIMARY KEY(id), 
	 CONSTRAINT uq UNIQUE(stuid), 
	 CONSTRAINT ck CHECK(score>=0 AND score<=100), 
	 CONSTRAINT fk FOREIGN KEY(stuid) REFERENCES stuinfo(id)
  );

> DCL数据控制语言

关键字： commit 提交

rollback回滚

savepoint设置保存点

– 数据库事务

每条增删改(查不是)语句都是自动开启的事务
事务是数据库的特有概念，目的是为了让数据从一种状态到另一种状态

— 事务特点：ACID

原子性：一个事务是不可分割的！要么一起执行，要么都不执行
一致性：数据的准确性
隔离性：一个事务和另外的事务之间隔离开来，每一个事务是独立，互不影响
持久性：一个事务一旦提交，将永久更新到本地

— 事务分类

隐式事务：

每条 insert、update、delete语句都可以看做一个独立的事务
没有明显的事务开启和结束的标记，所以称为隐式事务

显式事务：

多条更新语句合并成为一个事务，需要有明显的事务开启和结束标记

— 用法

①取消每条增删改的自动提交

set  autocommit =0;

②开启一个新事务

Start  transaction; 
UPDATE stuinfo SET sex='男' WHERE majorId=2; 
UPDATE stuinfo SET sex='女' WHERE majorId = 3;

③结束事务

Rollback;  #回滚 Commit;  #提交