MySQL基础

MySQL基础

SQL

• SQL通用语法

1. SQL语句可以单行或者多行书写,以分号结尾。
2. SQL语句可以使用空格/缩进来增强语句的可读性。
3. MySQL数据库的SQL语句不区分大小写,关键字建议使用大写。

---

• SQL分类

分类	全称	说明
DDL	Data Definition Language	数据定义语言,用来定义数据库对象
DML	Data Manipulation Language	数据操作语言,用来对数据库对象
DQL	Data Query Language	数据查询语言,用来查询数据库中表的记录
DCL	Data Control Language	数据控制语言,用来创建数据库用户、控制数据库的访问权限

---

• DDL

  • DDL-数据库操作

  SHOW DATABASES;---查询所以数据库---
  SELECT DATABASE();---()不能忘,本质是一个函数,查询当前数据库---
  CREATE DATABASE 数据库名;---创建---
  DROP DATABASE 数据库名;---删除---
  USE 数据库名;---使用---
  

  • DDL-表操作

  SHOW TABLES;
  CREATE TABLE 表名;
  DESC 表名;
  SHOW CREATE TABLE;
  ALTER TABLE 表名 ADD 字段名 类型(长度)[COMMENT 注释][约束] /*例子:为emp表增加一个新的字段"昵称"为nickname,类型为varchar(20);
  alert table emp add nickname varchar(20) comment'昵称';*/
  ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);---修改数据类型---
  AlTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释][约束]---修改字段名和字段类型---
  DROP TABLE 表名;---删除表---
  

---

• DML

  INSERT INTO 表名(字段1,字段2...) VALUES(值1,值2...);---插入数据,表名后的字段可以省略---
  UPDATE 表名 SET 字段名1=值1,字段名2=值2[WHERE 条件];---修改数据,如:UPDATE emp SET name ="heliang" WHERE id=1;可以不写where条件,会修改整张表的所以数据---
  DELETE FORM 表名[WHERE 条件] ---where条件可以没有,但是会删除整张表的数据,DELETE语句不能删除某一个字段的值(可以用UPDATE)---
  

---

• DQL

  • 基础查询

  SELECT 字段1,字段2,字段3...FROM 表名
  SELECT *FROM 表名;---查询表格所有信息---
  SELECT DISTINCT 字段列表 FROM 表名;---SELECT DISTINCT workaddress FROM emp;---
  

  • 条件查询

  SELECT 字段列表 FROM WHERE 条件列表;/*查询姓名为两个字的员工信息:SELECT *FROM emp WHERE name LIKE'__';
  查询身份证最后一位为X的员工信息* SELECT *FROM emp WHERE idcard LIKE '%X';/
  

  特殊比较运算符	功能
  <>或!=	不等于
  BETWEEN…AND…	在某个范围内
  IN(…)	在in之后的列表中的值,多选一
  LIKE 占位符	模糊匹配(_匹配单个字符,%匹配任意个字符)是NULL模糊匹配

  • 聚合函数

  函数	功能
  count	统计数量
  max	最大值
  min	最小值
  avg	平均值
  sum	求和

  SELECT 聚合函数(字段列表) FROM 表名;/* SELECT max(age) FROM emp;*/
  

  • 分组查询

  SELECT 字段列表 FROM [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
  /*查询年龄小于45的员工,并根据工作地址分组,获取员工数量大于等于3的工作地址:
  SELECT workaddress,count(*) address_count FROM emp WHERE age<45 GROUP BY workaddress HAVING address_count>=3;/
  

  where和having的区别:
  
  1.执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组,而having是分组之后对结果进行过滤。
  
  2.判断条件不同:where不能对聚合函数进行判断,而having可以。
  
  3.执行顺序:where > 聚合函数 > having

  • 排序查询

  SELECT 字段列表 FROM ORDER BY 字段1 排序方式1,字段2 排序方式2;
  /*根据年龄对公司的员工进行升序排序,年龄相同,再按照入职时间进行降序排序,如:SELECT *FROM emp ORDER BY age ASC,entrydate DESC;*/
  

  排序方式:

  1. ASC:升序(默认值)
  2. DESC:降序
     注意：如果多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序
  • 分页查询

  SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数/* SELECT * FROM emp LIMIT 0,10;查询第一页的员工数据,每页显示十条
  SELECT * FROM emp LIMIT 10,1O;查询第二页员工数据,每页展示10条记录  */
  
  

  注意:
  

  1. 起始索引从0开始,起始索引=(查询页码-1)*每页显示记录数。
     
  2. 分页查询是数据库方言。
     
  3. 如果查询的是第一页数据,起始索引可以省略,可以简写。
     
     

  *执行顺序
  书写顺序:SELECT,FROM,WHERE,GROUP BY,HAVING,ORDER BY,LIMIT
  执行顺序:FROM,WHERE,GROUP BY,HAVING,SELECT,ORDER BY,LIMIT

---

• DCL

1. 用户管理
2. 权限控制

---

约束

1. 概念:约束是作用于表中字段上的规则,用于限制存储在表中的数据

2. 目的:保证数据库中数据的正确、有效性和完整性

3. 分类:

   约束	描述	关键字
   非空约束	限制该字段的数据不能为null	NOT NULL
   唯一约束	保证该字段的所以数据都是唯一、不重复的	UNIQUE
   主键约束	主键是一行数据的唯一标识,要求非空且唯一	PRIMARY KEY
   默认约束	保存数据时,如果未指定该字段的值,则采用默认值	DEFAULT
   外键约束	用来让两张表的数据之间建立连接,保证数据的一致性和完整性	FOREIGN KEY

4. 例子:

   CREATE TABLE user(
      id int PRIMARY KEY auto_increment comment'主键',name VARCHAR(10) not null UNIQUE comment'姓名',
      status CHAR(1) default '1' comment'状态',gender CHAR(1) comment '性别') comment '用户表';

5. 外键约束:外键用来让两张表的数据之间的建立联系,从而保证数据的一致性和完整性。

---添加外键---
CREATE TABLE 表名(
   字段名 数据类型
   ....
   [CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);
/*ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY(dept_id) REFERENCES dept(id);*/
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段表) REFERENCES 主表(主表列名) 
---删除外键---
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称

---

多表查询

1. 概述:指从多张表中查询数据
2. 要加where关键字,不然会出现笛卡尔积现象

• 内连接

  • 隐式内连接

  SELECT 字段列表 FROM 表1,表2 WHERE 条件...;
  /*SELECT emp.name,dept.name FROM emp,dept WHERE emp.dept_id = dept.id*/
  

  • 显式内连接

  SELECT 字段列表 FROM 表1[INNER]JOIN 表2 ON 连接条件...;
  /*SELECT e.name,d.name FROM emp e inner join dept d on e.dept_id =d.id;*/
  

• 外连接

  • 左外连接:

  SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...---相当于查询表1(左表)的所以数据包含表1和表2交集部分的数据---
  /*SELECT e.*,d.name FROM emp e left OUTER JOIN dept d on e.dept_id=d.id;*/
  

  • 右外连接

  SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...---相当于查询表(右表)的所以数据包含表1和表2交集部分的数据---
  

• 子查询

  • 介绍:SQL语句中嵌套的select语句,称为嵌套查询,又称子查询。
  • 形式:

  SELECT * FROM t1 WHERE column1 =(SELECT column1 FROM t2...); /* SELECT id FROM tb_dept WHERE name='教育部';*/
  
  SELECT * FROM tb_emp WHERE entrydata ='2007-01-01' and job=2;
  /*查询与其入职日期 及 职位都相同的员工信息*/
  
  SELECT * FROM tb_emp WHERE entrydate > '2006-01-01';/*查询入职日期是2006-01-01之后的员工信息*/
  

  ---

事务

1. 事务是一组操作的集合,它是一个不可分割的工作单位。事务会把所以的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
2. 事务控制:

   /*开启事务:start transaction;/begin
   提交事务:commit;
   回滚事务:rollback;*/
   begin;
   delete from tb_dept where id=2;
   delete from tb_emp where dept_id=2;
   commit;
   rollback;
   

3. 四大特征(ACID):
   • 原子性:事务是不可分割的最小单元,要么全部成功,要么全部失败。
   • 一致性:事务完成时,必须使所有的数据都保持一致状态。
   • 隔离性:数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。
   • 持久性:事务一旦提交或者回滚,它对数据库中的数据改变就是永久的。