MySQL基础
文章目录
SQL简介
结构化查询语言:通用于所有数据库,定义了所有数据库的规则。
基本语法
语法要求
1.可单行或多行书写,以分号结尾
2.可使用空格和缩进
3.SQL语句不区分大小写,但关键字建议使用大写
基本语句
<1>注释
单行注释:#注释内容… 或 – 注释内容…(–要加空格)
多行注释:/注释内容/
从客户端操作服务器:
<2>数据库和表基本操作
- DDL:操作数据库、表
- DML:增删改表中的数据
- DQL:查询表中的数据
- DCL:授权客户端操作,权限
(1) DDL操作数据库:
- 操作数据库:CRUD
1.C(Create)
:创建
2.R(Retrieve)
:查询
show databases;
:查询所有数据库
show create databases 数据库名称;
:查询某个数据库
create database 数据库名称;
:创建数据库**(若已存在会报错)**
create database if not exists 数据库名称;
:不存在则创建,存在也不会报错
create database 数据库名称 character set gbk;
:创建数据库字符集为GBK的数据库
3.U(Update)
:修改
alter database 数据库名称 character set 字符集名称;
:更改数据库字符集**(GBK/UTF8…)**
4.D(Delete)
:删除
drop database 数据库名称;
:删除数据库
drop database if exists 数据库名称;
:删除数据库
5.使用数据库
select database();
:查询当前正在使用的数据库名称
use 数据库名称;
:使用数据库
(2) DDL操作表:
1.C(Create)
:创建
create table 表名();
:创建表
列名1 数据类型1,
列名2 数据类型2 ,
…
列名n 数据类型n(最后不需要写",")
数据类型:
1.int 整数类型:
列名1 int,
2.double 小数类型
列名2 double(5,2)
,👉最多有5位数字,保留两位小数
3.date 日期(只包含年月日yyyy-MM-dd):
4.datetime 日期(包含时分秒yyyy-MM-dd HH:mm:ss)
5.timestamp 时间戳类型(若不赋值或添加NULL则自动添加当前时间)
6.varchar 字符串类型:
列名3 varchar(20)
👉20是最大字符长度
use wtf02;--使用wtf02中的表
create talble what(
id int,
name varchar(20),
age int,
birthday date,
time timestamp
);
- R(Retrieve):查询
show tables; 查询某个数据库中所有的表名称
desc 表名; 查询表结构 - U(Update):修改
● 修改表名
alter table 表名 rename to 新的表名;
● 修改表的字符集
alter table 表名 character set 字符集名称;
● 添加一列
alter table 表名 add 列名 数据类型;
● 修改列名称 类型
alter table 表名 change 列名 新列别 新数据类型;
alter table 表名 modify 列名 新数据类型;
● 删除列
alter table 表名 drop 列名;
- D(Delete):删除
drop table 表名;
drop table if exists 表名 ;
(3) DML 增删改表中数据
1.添加数据:
insert into 表名(列名1,列名2...列名n) values(值1,值2...值n);
(省略列名定义所有列;除数字类型外其他用引号引起来)
2.删除数据:
delete from 表名 [where 条件];
(若不加条件则删除表中所有数据,相当于 **truncate table**
)
truncate table 表名;
:删除表中的所有数据,效率更高
3.修改数据:
update 表名 set 列名1=值1,列名2=值2...,...[where 条件];
(4) DQL 查询表中数据
① 基础查询
1.select 字段列表 from 表名列表;
查询多个字段**(若查询所有字段,可用 * 替代列表)**
2.select distinct 列名 from 表名;
去除重复的表**(仅去除所有字段均相同的)**
3.计算列:
用四则运算计算列的值
ifnull(字段名称,替换值)
:判断某字段是否位Null,该字段数值位null时替换为某数值
4.起别名
… as
…(可省略)
②条件查询
1.where + 条件判断符
2.运算符
- <、>、<=、=、!=(<>)
- between…and
- in(集合)
- like
- is null
- and(&&)
- or(||)
- not(或!,在is后面)
3.Like 模糊查询
**例:**查询第二个字是“化”的人:
select * from student where name like "_化%";
注:_表示一个字符位的占位符,%表示多个(或0个)字符位的占位符
③ 排序查询
语法:order by 排序字段1 排序方式1,…排序字段n 排序方式n;(多排序条件时当第一条件相同时按照第二条件排序)
排序方式:ASC升序(默认) DESC降序
④ 聚合函数
对一列数据进行纵向计算 (聚合函数排除null值)
1.count:计算个数
一般选择非空列,
方法:count(*)
2.max:计算最大值
3.min:计算最小值
4.sum :计算和
5.avg:计算平均值
select sum(math) from student;
⑤ 分组查询
group by 分组字段;
select
where 和 having 区别:
- where 在分组之前进行限定,如果不满足条件,则不参与分组。having在分组之后进行限定,如果不满足结果,则不会被查询出来
- where 后不可以跟聚合函数,having可以进行聚合函数的判断。
例:
按照性别分组。分别查询男、女同学的平均分,人数 要求:分数低于70分的人,不参与分组,分组之后。人数要大于2个人
SELECT sex , AVG(math),COUNT(id) 人数 FROM student WHERE math > 70 GROUP BY sex HAVING 人数 > 2;
⑥ 分页查询
limit 开始的索引,每页查询的条数;
(SQL方言)
开始的索引=(当前页码-1)*每页显示的条数
例:每页显示4条记录:
SELECT * FROM student LIMIT 0,4 --第一页
SELECT * FROM student LIMIT 4,8 --第二页
SELECT * FROM student LIMIT 8,12 --第三页
<3> 表的约束
(1) 非空约束(not null)
1 . 创建表时添加约束
CREATE TABLE stu(id INT,NAME VARCHAR(20) NOT NULL);
2 . 创建表完后,添加非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
3 . 删除name的非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20);
(2) 唯一约束(unique)
1 .创建表时添加
CREATE TABLE stu(id INT,phonenumber VARCHAR(11) UNIQUE);
2 .创建表完后,添加非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(11) NOT NULL;
3 .删除name的非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(11);
注:MYSQL中唯一约束限定的列允许多个NULL元素
(3) 主键约束(primary key)
注:
① 主键同时拥有以上两种功能——非空且唯一
② 一张表只能有一个字段为主键
③ 主键就是表中记录的唯一标识
1.创建表时添加主键约束
create table stu(id int primary key,-- 给id添加主键
name varchar(20));
2.删除主键约束
ALTER TABLE stu DROP PRIMARY KEY;
3.创建完表后,添加主键约束
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
*4.自动增长
auto_increment
可使为数值类型的某一列自动增长
创建表时候添加主键和自动增长:id int primary key auto_increment
删除自动增长
ALTER TABLE stu MODIFY id INT;
添加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
(4) 外键约束(foreign key)
外键约束:让表产生联系
1.创建表时添加
constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
2.删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
3.创建表之后,添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
4 级联操作
添加级联操作 (在主表后面添加):
主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE ;
级联更新:on update cascade
级联删除:on delete cascade (删除与之关联的所有表)
<4> 数据库的设计——多表操作
多表之间的实现关系
- 一对一
在多的一方建立外键,指向一的一方的主键 - 一对多
借助第三张中间表,中间表至少包含两个字段,分别指向两张表的主键 - 多对多
在任意一方添加唯一外键,指向另一方的主键;或让两个表的主键相同
<5> 数据库的三大范式
-
第一范式(1NF):每一列都是不可分割的原子数据项
-
第二范式(2NF):在1NF的基础上,非码属性必须完全依赖于码(在1NF基础上消除非主属性对主码的部分函数依赖)
* 几个概念:
1. 函数依赖:A–>B,如果通过A属性(属性组)的值,可以确定唯一B属性的值。则称B依赖于A (学号–>姓名)
2. 完全函数依赖:A–>B, 如果A是一个属性组,则B属性值得确定需要依赖于A属性组中所有的属性值。(学号,课程名称) --> 分数
3. 部分函数依赖:A–>B, 如果A是一个属性组,则B属性值得确定只需要依赖于A属性组中某一些值即可。(学号,课程名称) – > 姓名
4. 传递函数依赖:A–>B, B – >C . 如果通过A属性(属性组)的值,可以确定唯一B属性的值,在通过B属性(属性组)的值可以确定唯一C属性的值,则称 C 传递函数依赖于A (学号–>系名,系名–>系主任)
5. 码:如果在一张表中,一个属性或属性组,被其他所有属性所完全依赖,则称这个属性(属性组)为该表的码(例如:该表中码为:(学号,课程名称)
* 主属性:码属性组中的所有属性
* 非主属性:除过码属性组的属性 -
第三范式(3NF):在2NF基础上,任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)
<6> 备份和还原
CMD 命令行:
备份:mysqldump -uroot -proot > 路径
还原:
- 登录MYSQL
- 建立数据库:create database wtf02;
- 使用数据库:use wtf02;
- 导入数据库:source 路径(D:/…***.sql)
Sqlyog 图形界面:
<7> 多表查询
(1) 内连接查询
- 隐式:
select
f01.mane,
f01.age
from
why f01, -- 将 why 表定义为 f01
what f02
where
f01.'id'=f02.'id2' -- 判断条件,只显示id和id2相同的项
- 显式:
select 字段列表 from 表名1 [inner] join 表名2 on 条件
(2) 外链接查询
- 左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件;
查询左表所有数据以及和右表的交集部分 - 右外连接
同左外连接,将left改为right,查询右表所有数据以及和右表的交集部分
(3) 子查询
在查询中嵌套查询;Where语句后可接运算式 (聚合函数和> < >= = <=)
- 子查询的结果是单行单列时,该子查询可以作为条件
- 子查询的结果是多行单列时,该子查询可以作为条件使用 in 来判断
- 子查询的结果是多行多列时,该子查询可以作为一张虚拟表
-- 查询'财务部'和'市场部'所有的员工信息
SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';
SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;
-- 子查询
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');
<8> 事务
(1)概念:
将一大坨步骤的操作进行事务管理,使之要么同时成功,要么同时失败 (防止别人给我转账一个小目标但是我没收到)。
(2)操作:
- 开启事务:start transation;
- 回滚:rollback;
- 提交:commit;
START TRANSACTION--开启事务
UPDATE account SET balance =balance-500 WHERE NAME='ErCong';
UPDATE account SET balance =balance+500 WHERE NAME='GouDan';
COMMIT;--提交事务
ROLLBACK;--发现错误,回滚事务,回到事务开头
- 事务提交方式:
@@autocommit:1 代表自动提交,0 代表手动提交
查询事务: SELECT @@autocommit;
SET @@autocommit =0;
自动提交(MYSQL默认):
一条DML(增删改)语句会自动提交一次事务
手动提交(ORACLE默认):
需要先开启事务,再提交
(3) 事务的四大特征:
- 原子性:不可分割的最小单位
- 持久性:当事务提交或回滚后,数据库会持久化的保存数据
- 隔离性:多个事务之间相互独立
- 一致性:事务操作前后,数据总量保持不变
(4) 事务的隔离级别:
多个事务之间相互独立,若多个事务同时操作同一批数据,则会引发问题,设置不同的隔离级别就可解决这一问题。
存在问题:
- 脏读:一个事务,读取到另一个事务中没有提交的数据
- 不可重复读(虚读):在同一个事务中,两次读取到的数据不一样。
- 幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改。
隔离级别:
1.read uncommitted:读未提交
2.read committed:读已提交
3.repeatable read:可重复读
4.serializable:串行化
(5)管理用户
- 添加用户:
CREATE USER ‘用户名’@‘主机名’ IDENTIFIED BY ‘密码’; - 删除用户:
DROP USER ‘用户名’@‘主机名’; - 修改密码:
UPDATE USER SET PASSWORD(‘新密码’) WHERE USER =‘用户名’;
SET PASSWORD FOR ‘用户名’@‘主机名’ = PASSWORD(‘新密码’); - 查询用户:
1.切到MYSQL数据库,
USE mysql;
2.查询user表
SELECT * FROM USER;
通配符:%表示可以在任意主机使用用户登录数据库
(6)权限管理
1.查询权限:
SHOW GRANTS FOR ‘用户名’@‘主机名’;
2.授予权限:
grant 权限列表 on 数据库名.表名 to ‘用户名’@‘主机名’;
3.撤销权限:
revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’;