数据库sql

访问数据库

show databases;

显示当前账号可以看见的所有数据库。

use database(数据库名-mysql) ;

此命令成功后出现 Database changed

作用是进入 mysql数据库。

show tables；

显示当前的database所有表格table

desc table(表名);

显示表结构；

show create table student;

显示建表sql;

查询数据库character( 字符集)

select 列 from 表 where 数据库=名字

select schema_name,default_character_set_name from information_schema.schemata where schema_name =‘自己的数据库名’;

查询表信息

show table status from 数据库 like ‘表名’;

操作数据库的语言规范

SQl 结构化查询语言(Structured Query Language)

关键字不区分大小写 ，建议大家写大写的 风格统一

*DDL(Date Definition Language) 数据定义语言

用来创建，删除修改数据库中的对象(表格，用户，索引，视图，存储过程,触发器)

1. create(创建)
2. drop(删除)
3. alter(修改)

1.建数据库

create database （数据库名） 【default character set =’utf8‘】

2.建表

create table （

​     列名   数据类型（长度）,
     primary key (opinion_id)   //主键 
 ）character set utf8   collate utf8_general_ci;

character set utf8    设置表字符集

collate utf8_general_ci/ utf8_unicode_ci   设置排序规则

3.修改表

1.修改表名

alter table 愿表名 rename to 新表名;

2.修改原有列

alter table 原表名 change 原列名 新列名 新类型 新长度;

3.新增一个列

alter table 原表名 add 新列名 新类型 新长度;

4.删除一个原有的列

alter table 原表名 drop 原列名;

5.删除表,数据库

drop table 表格名字;

drop database 数据库名;

4.数据库中的数据类型

存储据的方式来分类

数值型

整数　tinyint　smallint　mediumint　int 4字节32bit　bigint

小数 　float 4 double 8 decimal numeric

字符串

char字符串　　varchar　　　　　　variable可变的

char(4)　　　　varchar(4) 数据库中的所有的字符串类型

a 4字节　　　　a 1字节

ab 4字节　　　ab 2字节

binary 二进制 　　varbinary可变二进制

blob二进制大文本　text正常字符大文本

日期/时间　定义是不需长度

date日期　time时间　datetime日期&时间　timestamp时间戳

1. create(创建)
2. drop(s)

5.约束

1.主键约束(Primary key)

每一个表格内 只能有一个列被设置为主键约束
主键约束通常是用来标记表格中数据的唯一存在
主键约束要求当前的列 不能为null值
主键约束要求当前的列 值是唯一存在的 不能重复
alter table 表名 add constraint 约束名字 约束类型 (列);
alter table myclass add constraint pk_myclass primary key (classid);
添加好主键约束后 可以通过如下语句查看
desc 表名;------>description描述
show keys from 表名；
发现自己定义的主键名字没有用上 可以简写为
alter table 表名 add primary key (classid);
添加主键之后若想要让主键自增 可以做相应的设计
alter table myclass modify classid int(4) auto_increment;
alter table myclass change classid classid int(4) auto_increment;
没有做起始值的说明 主键列的值会从1开始

alter table myclass auto_increment = 10;
删除主键约束
alter table myclass drop primary key;
注意:删除主键约束以后 不重复的特性取消了 非空特性还在
alter table myclass modify classid int (4) null;

2.唯一约束(Unique [Key])

可以为表格中的某一个列添加唯一约束 约束与主键类似
唯一约束表示的是列的值 不能重复 可以为空
唯一约束在表格中可以存在多个列
alter table 表名 add constraint 约束名 约束类型 (列);
alter table myclass add constraint uk_myclass unique [key] (loc);
可以简写为如下效果
alter table myclass add unique key(loc); 约束名默认的列名
删除唯一约束
alter table myclass drop index 约束名;

3.非空约束

在表格中的某一个列上添加非空约束
当前列的值不能为null
alter table 表名 modify 原列名 原类型 原长度 [not] null default xxx;
alter table 表名 change 原列名 原列名 原类型 原长度 [not] null default xxx;

4.外键约束

表格中可以有多个列被设置为外键约束
当前列的值可以为空 可以重复
当前列的值不能随便的填写 值去另外一张表格内寻找
外键是当前列的值受到另外一张表格某一个列的影响
另外一张表格的列 是唯一约束(主键 唯一)
1.添加外键约束
　alter table 表名字 add constraint fk_当前表_关联表 foreign key(列) references 另一个表(列);
2.通过show keys from 表; desc 表; show create table 表名;
　PRI UNI MUL---->multiple(多样 并联)
3.删除外键约束
　alter table 表名字 drop foreign key 约束名字;
注意:通过上述语句其实已经将外键约束删掉了
自动在当前表格内添加一个新的key
需要再次手动将这个生成的key删掉 外键约束才真的删除干净
alter table 表名字 drop key 约束名字;

​ alter table student add foreign key(列) references 另一个表(列);
​ 注意: 如果是简写的效果添加外键 外键的名字不是默认列名 student_ibfk_1
​ alter table student drop foreign key student_ibfk_1;
​ alter table student drop key classid;

5检查约束(Check)

mysql无效 orcal数据库有效
列在存值的时候做一个细致的检查 范围是否合理
alter table student add constraint ck_sage check(sage>15 and sage<30);

**DML(Date Manipulation Language)数据操作语言

用来操作数据库表格中的具体数据信息

1.写数据

1.insert新增

1. insert into 表名 （列名，列名，列名）values （值，值，值）；

2. 如果想要新增的记录是全部的表格的列，可以省略表名后面的括号内容，但要求values后面的值个数和类型都必须与表格中列顺序，类型，个数一致。insert into 表名 (值，值，值)；

3. 添加多条i

   nsert into 表名 (值，值，值)，(值，值，值)，(值，值，值)；

2.delete删除

1. 删除表全部数据

   delete from 表名 [where …]

3.update修改

1. 修改一个列全部值

   update 表名 set 列=值，列=值 [where … ]

***读取信息 DQL(Data Query Language)数据查询语言

select where条件 group-by分组 having条件 orrder-by排序

1.查询

select 列名 ，列名 from 表名；

2.条件查询

select 列名 ，列名 from 表名 where 条件；

where 后面具体使用 连接什么东西

1. 比较运算符>　>=　<　<=　!=　=

2. 算术运算符 +　-　*　/

3. 逻辑运算符　and　or　not

   如果and 和 or 同时出现 and比or 优先级高[]

4. [not] between　 值 and 值

   包含前后两个值

   physics 在75到90之间

   select sid ,sname,ssex,sage , sbirthday , chinese , english ,physics ,physical ,classid from student where physics between 75 and 90;

5. [not] in

   如下条件满足一个即可 chinese =92 或 chinese =99

   select * from student where chinese in (92,99);3.

6. 去重 distinct

数据库底层做的事

select sid ,sname,ssex,sage , sbirthday , chinese , english ,physics ,physical ,classid from student where chinese >=95 and english>=95;

1. 解析sql

2. 从表格中将全部数据读取出来　放到数据库缓存　集合List

3. 将list集合做一个遍历循环　每次拿到一个元素(一行记录) 条件

   假设我们的表格有七条数据

   如果我们只写一个where条件

   执行7次循环就可以比较出来

   如果有and连接不止一个条件

   先按照第一个条件先筛选

   7次循环　５条满足>95

   再按照第二个条件再筛选

   5次循环　2条满足

4. 如果以后在语句中使用了and

   尽量将条件苛刻的写在前面　提高执行效率

3.函数

函数------数据库定义好的

1. 可以理解为函数就是java中定义的方法

   MySQL数据库的函数都有返回值

   函数不一定都有参数列表 now();

2. 函数需要调用才能执行　　没有对象　函数直接放置在语句中相当与调用

3. 函数可以放置在什么位置上

   查询中用来显示的部分------->select 函数（列）from 表格

   条件筛选的后面 　　　------->select 列 from 表格 where sal>函数(值)

4. 参考手册

   mysql常用函数.pdf

   按照函数的功能来进行划分

   1. 比较函数

      ifnull(值)；是空返回1　不是空值返回0

   2. 数学函数(数值函数)-------->Math类

      abs绝对值　floor向下取整　mod(5,2)取余数　pow求次方　round()

   3. 日期和时间-------->date

      now()　year(date)　month(date)　day 　week

   4. 控制流程函数(转换函数)

      if(条件,值1，值2)

      if(值,value)

   5. 字符串函数---------->String类

      length()　　　　　length();

      concat()　　　　　concat();

      substr()　　　　　subString();

      instr()　　　　　　indexOf();

      replace()　　　　　replace();

      upper()　　　　　 toUpperCase();

      lower()　　　　　 toLowerCase();

      Itrim() rtrim()　　　trim();

      lpad() rpad()

      reverse()　　　　　StringBuffer() StringBuilder()

分组函数(聚合函数)

count()

max()　　min()　　avg()　　sum()

分组条件

group by

having关键字与where类似

where　group-by　having

分组条件＋分组函数的查询

1.在查询之前要考虑　到底是先分组　还是先筛选条件

先筛选　后分组　where　group-by

先分组　后筛选　group　having

2.在查询的时候一但分组了　行数会减少

想要展示的信息　个数需要与分组条件的个数一致

4.嵌套查询

​ 一个完整的SQL语句中 嵌套了另一个完整的SQL语句

*1.查询student表格中语文成绩最高的人
select * from student where chinese = (select max(chinese) from student);
嵌套可以将一个查询的结果当做条件
再次查询
2.查询在深圳班级上课的同学有哪些?
select * from student where classid = (select classid from myclass where classloc = ‘深圳’);
嵌套将某一个查询的结果当做条件的时候
表格可以不是一张
我们可以将另一张表格查询的信息 当做当前表格的条件
3.可以将查询的最终结果当做一张表格
在表格的基础上
再次查询
注意:在将结果当做表格时候 需要给表起别名

几个关键字

in　　满足查询子集中的某一个即可
in后面的自己内 可以是常量固定值
也可以是通过另一条sql语句查询出来的结果
in() not in ();

如下的三个使用起来与in类似 查询是否满足后面的子集中的条件
关键字的后面不允许写固定值 只允许写sql语句(只能通过嵌套来获取子集)
any 满足查询子集中的某一个即可 >any <any =any(结果与in) !=any
some 与any完全一致
all 满足查询子集中的全部才可以 >all <all =all !=all(结果与not in)

5.联合查询

等值连接

广义笛卡尔积---->进行条件筛选---->等值连接
　广义笛卡尔积将两张表格或多张表格 进行无条件的拼接
在拼接后的一张大表格的基础上进行了where的筛选
刚才我们的拼接 emp dept 一对多的对应关系
如果没有关系的两张表格 能否拼接呢? 任何表格都可以
select * from A , B where 条件

*外连接

select * from A left/right [outer] join B on 条件

1. 两张表格A和B 取决于谁的数据在左边显示

   A表格先出现 A左边显示

   B表格后出现 B右边显示

2. left和right来控制以哪一个表格的数据作为基准

   作为基准的表格数据必须全部显示出来

   非基准的表格按照on条件与之拼接

   若找到条件拼接 则正常显示 若找不到满足条件的则 null

内连接(自连接)

​ select * from A inner join B on 条件
​ 在当前表格中再次查询当前表格的信息
​ mgr = empno

6.集合操作

并集 (oracle数据库有–>交集intersect 差集minus)

union(结合 合并的意思 并集)

1. 要求前后两个查询子集的列数是一致

2. 类型没有要求

3. 拼接后显示的列名是前一个子集默认的列名

4. *** 注意 union和union all的区别**

   union合并后做去重复的处理 性能比较慢

   如果产生重复元素 记录的是第一次出现的那一行

   union all 将两个查询的字节直接做合并

   不做任何处理 性能比较快

   建议以后尽量使用union all来进行合并处理

７．分页查询

查询 limit a,b;　注意:与函数使用区分　　　instr(ename,‘A’); 1第一个出现 0没有
　a想要显示的起始行索引 　包括此行 　(偏移量) 从0开始
　b想要显示的行数
　(Oracle数据库分页使用伪列level rowid rownum)

DCL(Data Control Language)数据控制语言 用户权限

用户控制用户权限 SYSDBA数据库管理员

（赋予） grant 权限，权限 to用户

（回收）revoke 权限 ，权限 from 用户

1. 我们现在的身份是一个SYSDBA管理员 root账号
   管理员可以操作其他普通用户的权限
   通过root账号查看mysql数据库中的user表格
   记录着所有的用户信息
   user列 host列 password列 authentication_string列
2. 先创建一个新的用户
   database table user
   create user ‘用户名’@‘IP’ identified by ‘密码’;
   create user ‘zzt’@‘localhost’ identified by ‘123456’;
   用户被创建成功啦(只有一个默认的权限 Usage 只允许登录 不允许做其他事情)
   通过这个语句show grants for ‘用户名’@‘IP’;
3. 给新的用户赋予权限
   grant 权限 on 数据库名.表 to ‘用户名’@‘IP’;
   grant all on *.* to ‘zzt’@‘localhost’;
   赋予权限之后最好做一个刷新
   flush privileges;
4. 注销root 用新用户登录
5. 回收用户的权限
   Usage
   revoke 权限 on 数据库名.表名 from ‘用户名’@‘IP’;
6. 修改用户的密码
   update user表 set authentication_string = password(‘123’) where user = ‘zzt’;
7. 删除用户
   drop user ‘用户名’@‘IP’;
   drop user ‘zzt’@‘localhost’;

**TPL(Transcation Process Language)事务处理语言

事务Transcation

SQL语句是我们给数据库发送了指令，让数据库帮我们做事情

1. 事务可以理解为是让数据库做的事情

2. 有些时候事情之内不止一条sql,存在多个组成单元

   比如: 银行系统 转账 张三 100 —>李四

   update atm set abalance = 原来-100 where aname = 张三;

   update atm set abalance = 原来+100 where aname = 李四;

   登录 查询余额 存款 取款 转账 开户 销户
   .txt文件 I/O
   缓存Map commit(); MVC分层思想 IO 缓存

3. 一件事情中的所有操作应该是统一的
   要么都成功，要么都失败

4. 事务的本质

   可以理解为

   多线程并发操作同一张表格可能带来的安全问题

可以理解为多线程并发访问同一个文件资源 带来的安全问题

事务流程：

1.开启一个事务 begin Transcation
每一次执行的一条sql语句之前
mysql数据库都会默认的开启
begin; start transaction;
2.执行操作
insert update delete
select
可能不止一条语句
3.事务的处理
commit 提交/ rollback回滚/ save point A保存还原点
mysql数据库会默认的执行提交事务

mysql数据库事务管理默认的效果可以更改
autocommit变量 = on;
show variables like ‘%commit%’;
show variables like ‘autocommit’;
set autocommit = off; 设置自动提交关闭

事务的四大特性

A:Atomicity-------->原子性

一个事务中的所有操作是一个整体，不可分割

事务中的所有操作要么都成功，要么都失败

C:Consistency----->一致性

一个用户操作了数据，提交以后

另一个用户看到数据与之前用户看到的效果是一致的

I:Isolation----------->隔离性------>事务隔离级别

指的是多个用户并发访问数据库时

一个用户操作数据库，另一个用户不能有所干扰

多个用户之间的数据事务操作要互相隔离

D:Durability--------->持久性

指的是一个用户操作数据的事务一旦被提交(缓存—>文件)

他对数据库底层的真实的改变是永久性的　不可返回的

事务的隔离级别

级别	级别	说明
Serializable	最高	可以避免所有出现的问题 性能很慢
Repeatable Read	可重复读	(避免脏读，不可重复读) 在多次的情况下会出现幻读
Read Committed	读已提交	(避免脏读)
Read UnCommitted	读取未提交	(所有效果均无法保证)

可重复读说明

a，b两个事务读取相同表数据，b事务在此时修改表的数据，并已提交。a事务未结束前，同一条件下在b事务完成的前后 读取的数据都是相同的(即b事务未修改前的数据);

事务的隔离性可能会产生多线程并发操作同一个数据库表格的问题
会带来数据的安全隐患
1.脏读
一个人读到了另外一个人还没有提交的数据
A B在操作同一张表格
A修改了数据，还没有提交，B读取到了
A不提交了，回滚回来，B刚刚读取到的那些数据就是无用的----脏数据
2.不可重复读
A B在操作同一个表格
A先读取了一些数据，读完之后B此时将数据做了修改/删除 (以提交)
A再按照之前的条件重新读一遍，与第一次读取的不一致
3.幻读(虚读)
A B在操作同一个表格
A先读取了一些数据，读完之后B此时将数据做了新增
A再按照之前的条件重新读一遍，与第一次读取的不一致

MySQL数据库提供默认隔离级别 Repeatable Read
Oracle数据库提供默认隔离级别 Read Committed

可以修改数据库中的隔离级别
set session transaction isolation level xxx;
如果不放心可以查看
select @@tx_isolation; mysql5版本

select @@transaction_isolation; mysql8版本

数据库设计范式

1NF

​ 要求数据保证原子性
​ 每一个表格的没一个列都是不可分割 (行列交叉点的单元格内只存储一个数据)
​ 每一个表格必须有主键约束 (快速查询某一行记录)

2NF

​ 在满足第一范式的前提下
​ 不允许出现部分依赖性
​ (非主键列不能受到主键列或主键的一部分影响)

3NF

在满足前两个范式的前提下
不允许出现传递依赖性
(非主键列不能受到非主键列或非主键的一部分影响)

数据库设计的范式–表之间的关系(Java设计模式–类之间的关系)

设计数据库时，遵循的不同规范，这些规范统称为范式
范式的目的是为了减少数据库中的冗余，
管理表格的时候变得容易(修改 删除)
查询的时候可能就涉及到表格联合的问题(性能)

Normal Form(标准化形式–普通范式)
创建一张表格
用来记录学校里面不同楼宇项目的一些信息

项目表格Project
pid pname
项目编号 项目名称
A 教学楼
B 餐饮楼
C 宿舍楼

项目_工程师中间表project_engineer
pid eid
项目编号 工程师编号
A 1
A 2
A 3
A 4
B 1
B 5
B 6
C 2
C 7

工程师表格Engineer
eid ename
工程师编号 工程师名字 职称编号
1 赵一一 1
2 钱二二 1
3 孙三三 2
4 李四四 3
5 周五五 1
6 吴六六 2
7 郑七七 3

职称表格Ranks
rid rname hourly_pay
职称编号 职称名称 小时工资
1 高工 300
2 普工 200
3 助工 50

1.已经不遵循1NF 原子性可以保证 表格内没有主键
设定联合主键(项目编号–工程师编号)
2.产生了非主键列受到主键或主键的部分影响
----将数据拆开存储在两张表格里
3.产生了非主键列受到非主键列的影响
----将没有关系的数据拆开单独存放在表格里

常用的mysql权限

数据库/数据表/数据列权限：

权限	说明
Create	建立新的数据库或数据表
Alter	修改已存在的数据表(例如增加/删除列)
Drop	删除数据表或数据库
Insert	增加表的记录
Delete	删除表的记录
Update	修改表中已存在的记录
Select	显示/搜索表的记录
References	允许创建外键
Index	建立或删除索引
Create View	允许创建视图
Create Routine	允许创建存储过程和包
Execute	允许执行存储过程和包
Trigger	允许操作触发器
Create User	允许更改、创建、删除、重命名用户和收回所有权限
全局管理MySQL用户权限：
Grant Option	允许向其他用户授予或移除权限
Show View	允许执行SHOW CREATE VIEW语句
Show Databases	允许账户执行SHOW DATABASE语句来查看数据库
Lock Table	允许执行LOCK TABLES语句来锁定表
File	在MySQL服务器上读写文件
Process	显示或杀死属于其它用户的服务线程
Reload	重载访问控制表，刷新日志等
ShutDown	关闭MySQL服务
特别的权限：
All	允许做任何事(和root一样)
Usage	只允许登录，其它什么也不允许做

​

​
​