MySQL 基础入门(学习笔记)
1.SQL通用语法
1.SQL语句可以单行或者多行书写,以分号结尾。
2.MySQL数据库的 SQL 语句不区分大小写,关键字建议使用大写
3.注释:
- 单行注释:--注释内容 或 #注释内容(MySQL 特有)
- 多行注释:/*注释*/
2.SQL分类
1.DDL(Data Definition Language)
数据定义语言用来定义数据库对象:数据库,表,列等
2.DML(Data Manipulation Language)
数据操作语言,用来对数据库中表的数据进行增删改
3.DQL(Data Query Language)
数据查询语言,用来查询数据库中表的记录(数据)
4.DCL(Data Control Language)
数据控制语言,用来定义数据库的访问权限和安全级别,及创建用户
3.数据类型
#DDL数据类型
MySQL支持多种类型,可以分为三类
1.数值类型
2.日期和时间类型
3.字符串类型
age int
scoer double(总长度,小数点后保留的位数)
birthday data
"张三"
name char(10) 占 10 个字符空间,后面 8 个用空格补齐 存储性能高,浪费空间
name vachar(10) 占 2 个字符空间 存储性能低,节约空间
创建表案例
#需求:设计一张学生表,请注重数据类型、长度的合理性
1.编号
2.姓名,姓名最长不超过10个汉字
3.性别,因为取值只有两种可能,因此最多一个汉字
4.生日,取值为年月日
5.入学成绩,小数点后保留两位
6.邮件地址,最大长度不超过64
7.家庭联系电话,不一定是手机号码,可能会出现–等字符
8.学生状态(用数字表示,正常、休学、毕业..)
create table student(
id int,
name varchar(10),
gender char(1),
birthday date,
score double(5,2),
email varchar(64),
tel varchar(15),
status tinyint
);
1.DDL
DDL(Data Definition Language)
数据定义语言用来定义数据库对象:数据库,表,列等
#DDL操作数据库
1.查询所有的数据库:
show databases;
2.创建数据库,名字为db1:
create database db1;
create database if not exists db1; #如果不存在就创建这个db1数据库
3.删除数据库 db1:
drop database db1;
drop database if exists db1; #如果存在db1数据库,就删除
4.使用数据库 s1:
use s1;
5.查看当前使用的数据库:
select database();
#DDL操作表
1.创建(create)
创建一个tb_user表
create table tb_user(
id int,
username varchar(20),
password varchar(32)
);
#注意:最后一行末尾,不能加逗号
2.查询(retrieve)
1.查询当前数据库下所有表名:
show tables;
2.查询表结构:
desc 表名称;
3.修改(update)
1.修改表名
alter table
表名 rename to 新表名;
2.添加一列
alter table
表名 add 列名 数据类型;
3.修改数据类型
alter table
表名 modify 列名 新数据类型;
4.修改列名和数据类型
alter table
表名 change 列名 新列名 新数据类型;
5.删除列
alter table
表名 drop 列名;
4.删除(delete)
1.删除表
drop table 表名;
2.删除表时判断表是否存在
drop table if exists 表名;
2.DML
DML(Data Manipulation Language)
数据操作语言,用来对数据库中表的数据进行增删改
#添加数据
1.给指定列添加数据
insert into 表名(列名1,列名2,...)
values(值1,值2,...)
2.给全部列添加数据
insert into 表名
values(值1,值2,...)
3.批量添加数据
insert into 表名(列名1,列名2,...) values(值1,值2,...),(值1,值2,...),(值1,值2,...)...
insert into 表名 values(值1,值2,...),(值1,值2,...),(值1,值2,...)...
#修改数据
1.修改数据
update 表名
set 列名1=值1,列名2=值2,...
[where 条件]
#注意:如果update语句后面没有加where条件,则会将表中的所有数据全部更改!
#删除数据
delete from 表名
[where 条件]
#注意:如果delete语句后面没有加where条件,则会将表中的所有数据全部删除!
3.DQL
DQL(Data Query Language)
数据查询语言,用来查询数据库中表的记录(数据)
#查询语法
select
字段列表
FROM
表名列表
where
条件列表
GROUP BY
分组字段
HAVING
分组后条件
ORDER BY
排序字段
LIMIT
分页限定
基础查询
条件查询(WHERE)
分组查询(GROUP BY)
排序查询(ORDER BY)
分页查询(LIMIT)
1.基础查询
1.查询多个字段
select 字段列表 from 表名;
select * from 表名; #查询所有数据
2.去除重复记录
select distinct 字段列表 from 表名;
3.起别名
AS: AS 也可以省略
select name,math as 数学成绩,english as 英语成绩
from student;
2.条件查询(WHERE)
1.条件查询语法
select 字段列表
from 表名
where 条件列表;
条件
3.分组查询(GROUP BY)
#聚合函数
1.概念:将一行数据作为一个整体,进行纵向计算。
2.聚合函数分类:
函数名 功能
count(列名) 统计数量(一般选用不为null的列) 推荐用* count(*)
max(列名) 最大值
min(列名) 最小值
sum(列名) 求和
avg(列名) 平均值
3.聚合函数语法:
select 聚合函数名(列名) from 表;
注意:null不参与所有聚合函数运算
1.分组查询语法
select 字段列表
from 表名
[where 分组前条件限制]
GROUP BY 分组字段名
[HAVING 分组后条件过滤]
注意:分组子后,查询的字段为聚合函数和分组字段,查询其他字段无任何意义
where 和 having 区别:
- 执行时机不一样: where 是分组之前进行限定,不满足 where 条件,则不参与分组
而 having 是分组之后对结果进行过滤
- 可判断的条件不一样: where 不能对聚合函数进行判断, having 可以。
执行顺序: where > 聚合函数 > having
4.排序查询(ORDER BY)
1.排序查询语法
select 字段列表
from 表名
ORDER BY 排序字段名1 [排序方式1],排序字段名2 [排序方式2]...;
排序方式:
- ASC:升序排序(默认值)
- DESC:降序排序
注意:如果有多个排序条件,当前边的条件值一样时,才会根据第二条件进行排序
5.分页查询(LIMIT)
1.分页查询语法:
select 字段列表 from 表名 limit 起始索引,查询条目数;
- 起始索引:从 0 开始
计算公式: 起始索引 = (当前页码-1)*每页显示的条数
tips:
- 分页查询 limit 是 MySQL 数据库的方言
- Oracle 分页查询使用 rownumber
- SQL Server 分页查询使用 top
4.约束
1.概念和分类
1.约束的概念:
- 约束是作用于表中列上的规则,用于限制加入表的数据
- 约束的存在保证了数据库中的数据的正确性,有效性和完整性
2.约束的分类
#Tips: MySQL 不支持检查约束
create table emp(
id int primary key auto_increment, #员工id,主键且自动增长
ename varchar(50) not null unique , #员工姓名,非空且唯一
joindate DATE not null, #入职日期,非空
salary DOUBLE(7,2) not null, #工资,非空
bonus DOUBLE(7,2) default 0 #奖金,如果没有奖金默认为0
);
#自动增长:auto_increment
1.建完表之后添加非空约束
alter table 表名 modify 字段名 数据类型 not null;
删除约束
alter table 表名 modify 字段名 数据类型;
2.建完表之后添加唯一约束
alter table 表名 modify 字段名 数据类型 unique;
删除约束
alter table 表名 drop index 字段名;
3.建完表之后添加主键约束
alter table 表名 add primary key(字段名);
删除约束
alter table 表名 drop primary key;
4.建完表之后添加默认约束
alter table 表名 alter 列名 set default 默认值;
删除约束
alter table 表名 alter 列名 drop default;
#外键约束
1.概念:外键用来让两个表的数据之间建立连接,保证数据的一致性和完整性
2.语法:
添加约束:
创建表时添加外键约束
[constraint] [外键名称] foreign key (外键列名) references 主表(主表列名);
建完之后添加外键约束
alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称);
删除约束
alter table 表名 drop foreign key 外键名称;
5.数据库设计
#数据库设计设计什么
- 有哪些表
- 表里有哪些字段
- 表和表之间是什么关系
#表关系有哪几种
1.一对一
例如:用户和用户详情
一对一关系多用于表拆分,将一个实体中经常使用的字段放一张表,不经常使用的字段放另一张表,用于提升查询性能
实现方式:在任意一方加入外键,关联另一方主键,并且设置外键唯一(UNIQUE)
2.一对多
例如:部门和员工
一个部门对应多个员工,一个员工对应一个部门
实现方式:在多的一方建立外键,指向一的一方的主键
3.多对多
例如:商品和订单
一个商品对应多个订单,一个订单对应多个商品
实现方式:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
6.多表查询
1.笛卡尔积:取A,B集合所有组合情况
2.多表查询:从多张表查询数据
1.连接查询
1.内连接:相当于查询A,B交集数据
2.外连接:
1.左外连接:相当于查询A表所有数据和交集部分数据
2.右外连接:相当于查询B表所有数据和交集部分数据
2.子查询
1.内连接查询语法
--隐式内连接
select 字段列表 from 表1,表2... where 条件;
--显示内连接
select 字段列表 from 表1[inner] join 表2 on 条件;
2.外连接查询语法
--左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件;
--右外连接
select 字段列表 from 表1 right [outer] join 表2 on 条件;
3.子查询
1.子查询概念:查询中嵌套查询,称嵌套查询为子查询
2.子查询根据查询结果不同,作用不同:
- 单行单列:作为条件值,使用 = != > <等进行条件判断
select 字段列表 from 表 where 字段名 = (子查询);
- 多行单列:作为条件值,使用 in 等关键字进行条件判断
select 字段列表 from 表 where 字段名 in (子查询);
- 多行多列:作为虚拟表
select 字段列表 from (子查询) where 条件;
7.事务查询
#事务简介
·数据库的事务( Transaction )是一种机制、一个操作序列,包含了一组数据库操作命令
·事务把所有的命令作为一个整体一起向系统提交或撤销操作请求,即这一组数据库命令要么同时成功,要么同时失败
·事务是一个不可分割的工作逻辑单元
#开启事务
start Transaction;
或者 begin;
#提交事务
commit;
#回滚事务
rollback;
事务四大特征:
·原子性(Atomicity):事务是不可分割的最小操作单位,要么同时成功,要么同时失败
·一致性(Consistency):事务完成时,必须使所有的数据都保持─致状态
·隔离性(Isolation) :多个事务之间,操作的可见性
·持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的
MySQL事务默认自动提交
-- 查看事务的默认提交方式
select @@autocommit;
-- 1 自动提交 0 手动提交
-- 修改事务提交方式
set @@autocommit = 0;