数据库的简单操作

一、数据库的默认端口及连接工具

• oracle 11g：默认端口1521，连接工具：plsql，navicat
• MySQL 5.7： 默认端口3306，连接工具:navicat，sqlyog（小海豚）
• sql server 2008：默认端口1433

二、创建数据库对象（DDL:Data Definition Language）

规范与约束

1. 命名规则：字母+数字+下划线：不要数字开头、不要中文、不要和关键字重叠

2. 约束：
   主键(primary key)：唯一且非空， 相当于身份证号码
   外键(references): 来源于主键
   唯一约束(unique)：值不可以重复，但可以为null
   非空约束(not null): 值不能为空
   检查约束(check): 检查值是否满足某个条件
   默认值(default)

3. 时间表达式：
   1. ‘1/9月/2020’或’1-9月-2020’
   2. to_date函数：把字符串类型转换为date类型
   to_date('2020-9-27 9:00:00', 'yyyy-mm-dd hh24:mi:ss')
   3. to_char函数：把date类型转换为字符串类型
   to_char('2020-9-27', 'yyyy-mm-dd)
   4. 获取当前时间(sysdate)

DDL：数据库定义语句（create， alter， drop， truncate）

1. 创建表结构的格式：
   create table 表名(列名1 数据类型,列名2 数据类型,...列名N 数据类型);

   create table 表名(
       列名1 数据类型 [primary key],
       列名2 数据类型 [unique],
       列名1 数据类型 [not null | null],
       列名2 数据类型 [check(条件)],
       列名1 数据类型 [references 表名(列名)],
       ...
       列名2 数据类型 [default 默认值] [各种约束]
   );
   

   常用数据类型：
   varchar(n) ,int(n) ，date ， datetime ，text

2. 表的修改
   1. 查看表结构：
   在command window（命令窗口）输入： desc 表名 + 回车
   2. 修改表： 增加列
   alter table 表名 add(字段名1 数据类型 [约束], 字段名2 数据类型 [约束]...)
   3. 修改列： 修改数据类型、约束
   alter table 表名 modify(列名1 数据类型 [约束], 列名1 数据类型 [约束], )
   4. 删除列
   alter table 表名 drop column 列名;
alter table 表名 drop(列名1， 列名2...)

3. 表结构的删除 drop table 表名

三、数据库数据操作（DML:data manipulation language）

规范与约束

1. 单值比较运算符：> , < , = , !=或<> , <= , >=

2. 多值比较运算符： (not) between 值1 and 值2， not in(指定的值1, 值2...)

3. 多条件过滤之逻辑运算符：and、 or

4. 去重 distinct

5. 模糊查询：like

   1. %表示匹配任意长度的字符串
   2. %test%:表示匹配中间字符串为test的任意长度的字符串
   3. _表示仅能匹配一个字符
   4. o_a:表示匹配长度为3，以字母o开头，且以字母a结尾的字符串，中间一个字符为任意字符。

6. is [not] null 为空/不为空

7. 事务：通俗理解就是做一件事情的过程，这个过程有两种结果：成功、失败
   在数据库中发出一条DML语句即开启一个事务，开启事务就一定要结束事务。
   结束事务的两种方式：
   commit：提交事务，所有的操作都生效，操作的结果直会永久保存在数据库中
   rollback：回滚事务，所有的操作都不会生效，操作的结果直接扔掉，不会永久保存到数据库中

DML：数据库操作语句（insert, delete, update, select）

1. 查询数据

   select 列1, 列2, 列3...列N /*
   from 表名/(表达式)  	--数据池
   [where 过滤条件]
   [group by 分组内容]
   [having 组内条件]
   [order by 排序方式];
   

   1. 查询执行顺序： from --> where --> group by（分组函数这里产生）--> having（分组函数这里才能用）--> select --> order by

   2. where 针对整张表进行过滤；
      having针对分组后每个组的内容进行过滤，有having一定有group by

   3. 在使用group by进行分组时， select子句中可以出现分组函数和group by里用到的字段，不能出现其他字段（即：select 后面的所有字段，必须出现在 group by 后面）

2. 新增数据

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

3. 修改数据

   update 表名
   set 列1 = 值1， 列2  = 值2...列n = 值n
   [where 过滤条件];
   

4. 删除数据

   delete from 表名 [where 过滤条件];
   

   删除全表数据：
   delete from 表名
truncate table 表名 --不会发起事务!!慎用

四、单表复杂查询

1. 统计函数：

   SELECT COUNR(*) 总行数,
   	count(comm) comm的非空行数, 
   	sum(sal) 总工资 ,
   	avg(sal) 平均工资 ,
   	max(sal) 工资最大值 ,
   	min(sal) 最小值 
   FROM EMP
   

   1. count(*) --统计表中所有行的行数
   2. count(字段) --统计表中指定字段的非空行数
   3. sum(字段) --统计指定字段的和
   4. avg(字段) --统计指定字段的平均值
   5. max(字段) --统计指定字段的最大值
   6. min(字段) --统计指定字段的最小值

2. 分组函数

   group by 字段1, 字段2 [having 分组函数的比较表达式]；
   

   having 用于限制选中的组
   select 后面的所有字段，必须出现在 group by 后面

   例1，计算每个部门中每个工种各有多少教职工

   SELECT JOB, DEPTNO, COUNT(*) FROM EMP GROUP BY DEPTNO, JOB;
   

   例2， 统计各部门中分析员的人数

   SELECT JOB, DEPTNO, COUNT(*) FROM EMP GROUP BY DEPTNO, JOB HAVING JOB='ANALYST';
   

3. 排序语句：order by 字段1 ASC/DESC, 字段2 ASC/DESC...
   字段名，指出查询出来的结果按该字段排序；
   ASC表示按升序排序，
   DESC表示按降序排序；默认是按升序排序。

   SELECT 字段名 FROM 表名  
   	[WHERE 过滤条件]
   	[GROUP BY 字段1, 字段2]
   	[having 分组函数的比较表达式]
   	[order by 字段1 ASC/DESC, 字段2 ASC/DESC...]
   

   例：查询出至少有两名秘书的所有部门号， 并按人数降序排列
   1. 先找出秘书， 然后把秘书按部门分组
   SELECT DEPTNO, COUNT(*) FROM EMP WHERE JOB='CLERK' GROUP BY DEPTNO HAVING COUNT(*) >= 2 ORDER BY COUNT(*) DESC;
   2. 先把所有员工按部门分组（按职位分组），然后找出里面的秘书
   SELECT DEPTNO, COUNT(*) FROM EMP GROUP BY DEPTNO, JOB HAVING JOB='ANALYST' AND COUNT(*) >= 2 ORDER BY COUNT(*) DESC;

4. round(m, n) 函数：
   –当 n > 0, 将m四舍五入到小数点右边n位
   –当 n < 0, 将m四舍五入到小数点左边n位

5. 空值函数：nvl(m, n)
   当m不为空时，取m
   当m为空时，取n

   计算30号部门中每个职工的每日总报酬（一个月22个工作日/保留2位小数）
   SELECT ENAME, ROUND((SAL + NVL(COMM, 0))/22, 2) FROM EMP WHERE DEPTNO = 30;

6. 伪列伪行( oracle数据库特有 )

   1. 伪列
      rowid：属于表中的列，是由数据库自动增加的列，是真实存在的一个列，唯一标识一条数据的物理位置（查询语句首先得到数据的rowid，然后根据rowid到数据文件中唯一定位数据）
      因此，根据rowid查询的效率是最高的，数据在入库时(即成功插入数据)，Oracle数据就给这条数据分配一个唯一的rowid

      根据rowid查询的效率是最高的，但通常情况下不知道rowid的值，rowid是整个数据库唯一(在不同的数据库中不一定唯一)

   2. 伪行：
      rownum：是给结果集增加的一个序号，不属于任何的表，先有结果集，才能有rownum，始终是从1开始编号(不能跨过1而存在)
      select st.*, rownum from emp st;

      –"索引"就是一本书的目录，提升查询数据的效率
      –视图就是一张临时表，不能往视图中插入、修改、删除数据，只能查询数据

五、多表查询

• 等值连接

  select 表别名1.列名1, 表别名2.列名2...
  from 表名1 表别名1, 表名2 表别名2...
  where 等值连接条件
  

  • 分析题目的思路：
    • 如果有多个表，先找到这些表的相同字段；
    • 确定主从表。

• 外连接查询（左右连接）

  select 列名
  from 表名1 表别名1, 表名2 表别名2
  where 表别名1.列名=表别名2.列名(+) 或 表别名1.列名(+)=表别名2.列名;
  

  主键所在的表为主表，外键所在的表为从表，把操作符"(+)"加到外键上即可
  select ... from table1 t1,table2 t2 where t1.id = t2.id(+); --左连接( oracle数据库特有 )
  select ... from table1 t1,table2 t2 where t1.id(+) = t2.id; --右连接( oracle数据库特有 )

  左右连接的另一种写法：
  select * from A2 left JOIN B2 ON A2.id=B2.bid
select * from A2 right JOIN B2 ON A2.id=B2.bid
select * from A1 left join B2 on A1.id = B2.bid inner B2 left join C3 on B2.bid = C3.cid;

• 子查询

  SELECT <列名表>
  FROM 表名
  WHERE 列表或表达式 比较运算符(SELECT 列名 FROM 表名 WHERE 条件);
  

  单行值子查询：是指只返回一行（或 一个记录）的子查询

  对于该子查询命令，系统执行时先执行子查询，返回一个值(即SMITH的部门号)作为主要查询(或外层查询)的条件,然后再根据该条件执行主查询选择出预期的结果，即与“SMITH”同一部门的所有职工。通常，主查询与子查询之间用"=“、”>"等比较运算符进行连接。

  例：
  查找出与“SMITH”在同一个部门工作的所有职工姓名及工资

  select e.ename, e.sal from emp e where deptno = (select deptno from emp where ename = 'SMITH');
  

  多行值子查询：是指子查询会返回多个值，此时，需要用到多值比较运算符：[NOT]IN

• 在from子句中使用子查询、from 表名

  例：列出工资大于本部门平均工资的员工姓名和部门号及统计此部门的人数

  SELECT E.ENAME, AV, E.DEPTNO, GE 
  FROM EMP E ,(SELECT AVG(SAL) AV, DEPTNO, COUNT(*) GE FROM EMP GROUP BY DEPTNO) A 
  WHERE E.DEPTNO = A.DEPTNO AND E.SAL > AV;
  

六、数据的备份

• 导出表数据
  步骤：

  1. 选择PL/SQL菜单栏的Tools下面的Export Tables
  2. 切换到"SQL Inserts"窗口
  3. 选择"Create tables"
  4. 在Output file处，选择输出的地址，并自定义好以 .sql 结尾的文件名字，如 emp.sql
  5. 点击Output file最右边的Export按钮即可。

• 导入表数据
  步骤：

  1. 选择PL/SQL菜单栏的Tools下面的Import Tables
  2. 切换到"SQL Inserts"
  3. 选择“Use Command Window”
  4. 在Import file中选择要导入的sql文件
  5. 点击Import file最右边的Import按钮即可。

七、MYSQL数据库搭建

MYSQL下载地址：https://dev.mysql.com/downloads/mysql/

搭建Windows本地数据库：

1. 系统变量path配置/bin
2. 编写配置文件my.ini(可不配置)
3. 初始化数据库：mysqld --initialize-insecure --user=mysql (执行完包里面多了个data)
4. 安装服务器：mysqld --install mysql
5. 启动sql：net start mysql
6. 进入sql服务：mysql -u root -p（-u 后为用户名（root），首次安装没有设置密码，回车直接进入 ）
7. 设置用户密码：ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘admin’; (by引号里面的是密码)
8. 刷新：flush privileges
9. 退出sql：exit
10. 关闭服务：net stop mysql

最后

以上就是怡然小懒猪为你收集整理的数据库的简单操作数据库的简单操作的全部内容，希望文章能够帮你解决数据库的简单操作数据库的简单操作所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。