数据库相关操作

目录

一、 操作数据库：

三、 查询表中的记录

四、 约束

五、 数据库的设计

六、多表查询

七、事务

八、DCL

一、 操作数据库：

1 创建数据库，并判断是否存在和指定字符集

  create database if not exists db1 character set utf8;

2 查询某个数据库的字符集:查询某个数据库的创建语句

  show create database 数据库名称;

3 修改数据库的字符集

  alter database 数据库名称 character set 字符集名称;

4 删除数据库,并判断是否存在

  drop database if exists 数据库名称；

5 查询当前正在使用的数据库名称；

  select database();

二、 操作表

1 查询某个数据库中所有的表名称

  show tables;

2 创建表

  复制表：create table 新表名 like 要复制的表；

   create table 表名( ​ 列名1 数据类型 primary key, ​ 列名2 数据类型 ​ ); ​ 数据类型：

   timestamp 如果不给这个字段赋值，或赋值为null，则默认当前的系统时间

3 查看当前表中的信息

  desc 表名称;

4 删除表

  drop table if exists 表名；

5 修改表

• 修改表名：alter table 表名 rename to 新表名;

• 修改字符集：alter table 表名 character set 字符集名称;

• 添加一列：alter table 表名 add 列名 数据类型;

• 修改列名

  • alter table 表名 chage 列名 新列名 新数据类型；

  • alter table 表名 modify 列名 新数据类型;

• 删除列：alter table 表名 drop 列名;

2增删改表中的数据

1 添加数据

  insert into 表名(列名1，列名2...) values ();

 注意：只有数字类型不需要引号

2 删除表中数据

  delete from 表名 where 条件;

  注意：如果不加条件会删除所有记录

• delete from 表名； 不推荐使用。有多少条记录，执行多少次

• TRUNCATE TABLE 表名；推荐使用，效率高，先删除表，在创建一个一模一样的空表

3 修改数据

   update 表名 set 列名=值 , 列名 = 值 where 条件;

  注意：如果不加条件会删除所有记录

三、 查询表中的记录

1 查询并去除重复的结果集

  select DISTINCT 列名 FROM 表名；

2 查询并计算列之和

  select 列名1 + IFNULL（列名2 ，0） FROM 表名 ;

  select 列名1 + IFNULL（列名2 ，0） AS 列名 FROM 表名 ; ——起别名

  注意：

   如果有null参与的运算，计算结果都为null 。IFNULL ()判断

3 条件查询

  运算符：

• between and

• IN(集合)

• LIKE

  • _ ：单个任意字符

  • % ：多个任意字符

• IS NULL

• OR NOT

4 排序查询

  语法：order by 排序字段1 ASC(默认) ，排序字段2 DESC；

  注意：如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。

5 聚合函数

  count：计算个数

  注意：聚合函数的计算，排除null值。

6 分组查询

  语法：SELECt * FROM GROUP BY sex ;

  注意：

• 分组之后查询的字段：分组字段 、 聚合函数

• where 和 having 的区别 ？

  • where在分组之前进行限定，如果不满足，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来。

  • where后不可以跟聚合函数，having可以进行聚合函数的判断。

7 分页查询

  语法：LIMIT 开始的索引，每页查询的条数 ；

四、 约束

• 唯一约束（某一列的值不能重复）：unique

  添加唯一约束：alter table stu MODIFY 列名 VARCHAr(20) UNIQUE;

  删除唯一约束：alter table 表名 drop index 列名；

• 主键约束：primary key

  删除主键约束：alter table 表名 drop primary key；

  添加主键约束：ALTER TABLE 表名 MODIFY 列名 INT PRIMARY KEY;

• 外键约束：foreign key

  语法：constraint 外键名称 foreign key (外键列名称) references 主表名称(主键)

  删除外键约束：alter table student drop foreign key 外键名称;

  添加外键约束：alter table 表名 add constraint 外键名称 ......

级联操作(谨慎使用)：

1 添加级联操作:alter table 表名 add constraint 外键名称 ...... ON UPDATE CASCADE ON   DELETE CASCADE ;

  级联更新：ON UPDATE CASCADE

  级联删除：ON DELETE CASCADE

五、 数据库的设计

1多表之间的关系

• 一对多（多对一）：

  如：部门和员工

• 多对多（中间表）：

  学生和课程

2 数据库设计范式

1 第一范式

2 第二范式：在第一范式基础上消除非主属性对主码的部分函数依赖

3 第三范式：消除传递依赖

  完全依赖：依赖所有的属性值 例如：（学号，课程名称） ——> 分数

  部分依赖：只依赖于某个值 例如 ：（学号，课程名称） ——> 姓名

六、多表查询

1 内连接查询

1、隐式内连接：使用where条件消除无用的数据。

2、显示内连接：

  语法：select 字段列表 from 表名1 [inner]（可不写） join 表名2 on 条件

  注意：主键表连接外键表的数据若为null，那条数据将查询不到

• 从哪些表中查询数据

• 条件是什么

• 查询哪些字段

2 外连接查询

1、 左外连接

  语法：select 字段列表 from 表1 left [outer] join 表2 on 条件 ;

  * 查询的是左表所有数据以及其交集部分。

2、右外连接

  语法：select 字段列表 from 表1 right [outer] join 表2 on 条件 ;

  * 查询的是右表所有数据以及其交集部分。

3 子查询

1. 子查询的结果是单行单列的：

   子查询可以作为作为条件，使用运算符去判断。运算符 ：> >= <= =
   例子：查询员工工资小于平均工资的人

   select * from emp where emp.salary < (select AVG(salary) from emp);

2. 子查询的结果是多行单列的：

   • 子查询可以作为条件，使用运算符in来判断

3. 子查询的结果是多行多列的：

七、事务

1.事务的基本介绍

1. 概念：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

2. -- 开启事务 START TRANSACTION;

   UPDATe account SET balance = balance - 500 WHERe NAME = '张三'; UPDATE account SET balance = balance + 500 WHERe NAME = '李四';

3. -- 发现执行没有问题，提交事务 COMMIT;

4. -- 发现出问题了，回滚事务 ROLLBACK;

5. MYSQL数据库中事务默认自动提交

   • 查看食物的默认提交方式：select @@autocommit ; --1 代表自动提交

   • 修改事务的默认提交方式：select @@autocommit ；--0 代表手动提交

2.事务的四大特征

1. 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败

2. 持久性：当事务提交或回滚后，数据库会持久化的保存数据

3. 一致性：事务操作前后，事务总量不变

4. 隔离性：多个事务之间，相互独立

3.事务的隔离级别（了解）

• 概念：多个事务之间隔离的，相互独立的。但如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题

• 存在问题：

  1. 脏读：一个事务，读取到另一个事务中没有提交的数据

  2. 不可重复读（虚读）：在同一个事务中，两次读取到的数据不一样

  3. 幻读：一个事务操作（DML）数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改

• 隔离级别：

  1. read uncommitted:读未提交

     • 产生问题：脏读、不可重复读、幻读

  2. read committed:读已提交（oracle默认）

     • 产生问题：不可重复读、幻读

  3. repeatable read ：可重复读（mysql默认）

  4. serialICable：串行话

     • 解决所有问题

• 注意：隔离级别从小到大安全性越来越高，但是效率越来越低

• 数据库查询隔离级别：select @@tx_isolation;

• 数据库设置隔离级别：set global tranction ioslation level 级别字符串

八、DCL

• SQL分类：

  1. DDL：操作数据库 和表

  2. DML：增删改表中数据

  3. DQL：查询表中数据

  4. DCL：管理用户，授权

1. 用户管理

查询用户：

• 切换到mysql数据库 ： use mysql ;

• 查询user表 ：select * from user；

创建用户：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码' ;

删除用户：DROp USER '用户名'@'主机名' ;

修改密码：update user set password = password('新密码') where user= '用户名'

• 通配符 ：% 表示可以在任意主机使用用户登录数据库

1. 权限管理

查询权限：show grants for '用户名'@'主机名';

授予权限：grant 权限列表（* . *所有表所有权限） on 数据库名.表名 to '用户名'@'主机名' ;

撤销权限：revoke 权限列表 on 数据库.表名 from '用户名'@'主机名' ;