MySQL基础

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
 );

2. R(Retrieve)：查询
   show tables; 查询某个数据库中所有的表名称
   desc 表名; 查询表结构
3. U(Update):修改
   ● 修改表名
   alter table 表名 rename to 新的表名;
   ● 修改表的字符集
   alter table 表名 character set 字符集名称;
   ● 添加一列
   alter table 表名 add 列名 数据类型;
   ● 修改列名称 类型
   alter table 表名 change 列名 新列别 新数据类型;
alter table 表名 modify 列名 新数据类型;
   ● 删除列
   alter table 表名 drop 列名;
4. 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 区别：

1. where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来
2. 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> 数据库的设计——多表操作

多表之间的实现关系

1. 一对一
   在多的一方建立外键，指向一的一方的主键
2. 一对多
   借助第三张中间表，中间表至少包含两个字段，分别指向两张表的主键
3. 多对多
   在任意一方添加唯一外键，指向另一方的主键；或让两个表的主键相同

<5> 数据库的三大范式

1. 第一范式（1NF）：每一列都是不可分割的原子数据项

2. 第二范式（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. 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码(例如：该表中码为：(学号，课程名称)
   * 主属性：码属性组中的所有属性
   * 非主属性：除过码属性组的属性

3. 第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

<6> 备份和还原

CMD 命令行：

备份：mysqldump -uroot -proot > 路径
还原：

1. 登录MYSQL
2. 建立数据库：create database wtf02;
3. 使用数据库：use wtf02;
4. 导入数据库：source 路径(D:/…***.sql)

Sqlyog 图形界面：

<7> 多表查询

(1) 内连接查询

1. 隐式：

select 
      f01.mane,
      f01.age
from
      why f01, -- 将 why 表定义为 f01
      what f02
where
      f01.'id'=f02.'id2' -- 判断条件，只显示id和id2相同的项

2. 显式：

select 字段列表 from 表名1 [inner] join 表名2 on 条件

(2) 外链接查询

1. 左外连接
   select 字段列表 from 表1 left [outer] join 表2 on 条件;
   查询左表所有数据以及和右表的交集部分
2. 右外连接
   同左外连接，将left改为right，查询右表所有数据以及和右表的交集部分

(3) 子查询

在查询中嵌套查询；Where语句后可接运算式 (聚合函数和> < >= = <=)

1. 子查询的结果是单行单列时，该子查询可以作为条件
2. 子查询的结果是多行单列时，该子查询可以作为条件使用 in 来判断
3. 子查询的结果是多行多列时，该子查询可以作为一张虚拟表

-- 查询'财务部'和'市场部'所有的员工信息
	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)操作：

1. 开启事务：start transation;
2. 回滚：rollback;
3. 提交：commit;

START TRANSACTION--开启事务
UPDATE account SET balance =balance-500 WHERE NAME='ErCong';
UPDATE account SET balance =balance+500 WHERE NAME='GouDan';
COMMIT;--提交事务
ROLLBACK;--发现错误，回滚事务，回到事务开头

4. 事务提交方式：
   @@autocommit：1 代表自动提交，0 代表手动提交
   查询事务: SELECT @@autocommit;
   SET @@autocommit =0;
   自动提交(MYSQL默认)：
   一条DML(增删改)语句会自动提交一次事务
   手动提交(ORACLE默认)：
   需要先开启事务，再提交

(3) 事务的四大特征：

1. 原子性：不可分割的最小单位
2. 持久性：当事务提交或回滚后，数据库会持久化的保存数据
3. 隔离性：多个事务之间相互独立
4. 一致性：事务操作前后，数据总量保持不变

(4) 事务的隔离级别：

多个事务之间相互独立，若多个事务同时操作同一批数据，则会引发问题，设置不同的隔离级别就可解决这一问题。
存在问题：

1. 脏读：一个事务，读取到另一个事务中没有提交的数据
2. 不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样。
3. 幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。

隔离级别：
1.read uncommitted：读未提交

2.read committed：读已提交
3.repeatable read：可重复读
4.serializable：串行化

(5)管理用户

1. 添加用户：
   CREATE USER ‘用户名’@‘主机名’ IDENTIFIED BY ‘密码’;
2. 删除用户：
   DROP USER ‘用户名’@‘主机名’;
3. 修改密码：
   UPDATE USER SET PASSWORD(‘新密码’) WHERE USER =‘用户名’;
   SET PASSWORD FOR ‘用户名’@‘主机名’ = PASSWORD(‘新密码’);
4. 查询用户：
   1.切到MYSQL数据库，
   USE mysql;
   2.查询user表
   SELECT * FROM USER;
   通配符：%表示可以在任意主机使用用户登录数据库

(6)权限管理

1.查询权限：
SHOW GRANTS FOR ‘用户名’@‘主机名’;
2.授予权限：
grant 权限列表 on 数据库名.表名 to ‘用户名’@‘主机名’;
3.撤销权限：
revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’；