数据库（Database）是按照数据结构来组织、存储和管理数据的仓库，
每个数据库都有一个或多个不同的API用于创建，访问，管理，搜索和复制所保存的数据。
—– 从删库，到跑路额！
- 数据库 —– database - 数据的仓库（集散地）
- 通过数据库可以实现数据的持久化
- 数据持久化：文件操作（读写文件）-文本文件 / Excel
- 使用数据库的原因：当我们做数据持久化操作时不仅仅是希望能够把数据长久的保存下来，更为重要的是我们很方便的管理数据，在需要数据的时候能够很方便的把需要的数据取出来
- 1907s IBM - 关系型数据库
- 理论基础：关系代数和集合论
- 具体表象：用二维表（行，列）来组织数据
- 行：记录（1212 电脑城 男） - 实体的具体记录
- 列：字段（学号，姓名，性别） - 实体的属性
- 关系型数据库自己的编程语言 - SQL（结构化查询语言）
- DDL（数据定义语言）：create / drop / alter(修改）
- DML（数据操作语言）: insert / delete /update / select
- DCL（数据控制语言）: grant(授权）/ revoke(撤销）
- 关系型数据库产品：
- SQL （Structred Query Language)
- DDL（数据定义语言）：create / drop / alter(修改）
- DML（数据操作语言）: insert / delete /update / select
- DCL（数据控制语言）: grant(授权）/ revoke(撤销)
- DDL(Data Default Language)
DML
创建数据库
create database school default charset utf8;
删除数据库
drop database if exists school;
切换到数据库school
use school;
删除表
drop table if exists tb_student;
创建二维表保存数据
- 列名 数据类型 约束条件
- 非空约束 —- not null
- 默认值约束 —– default
- 主键 ——- 表中能够唯一标识一条记录的列
- 主键约束 —— primary key
create table tb_student ( stuid int not null, stuname varchar(4) not null, gender bit default 1, brith date, addr varchar(50), primary key (stuid) ); - 修改表
alter table tb_student add column tel char(11) not null; alter table tb_student drop column brith; alter table tb_student add score int not null; - 插入数据
– DMl (Data Manipulation Language)insert into tb_student values (1001, '天明', 0, '四川', '17640141329', 90); insert into tb_student values (1002, '王大锤', 1, '四川', '17640141329',80); insert into tb_student (stuid, tel, stuname, score, addr) values (1003, '17640141329', '盖聂', 100, '四川成都'); insert into tb_student (stuid, tel, stuname, score) values (1004, '17640141329', '张三', 60), (1005, '17640141329', '豆丁', 85), (1006, '17640141329', '小明', 55); - 删除数据
delete from tb_student where stuid=1006; delete from tb_student where stuname='天明'; delete from tb_student where stuid in (1004, 1006, 2000); - 截断数据（删除全表）
truncate table tb_student; - 更新数据
update tb_student set addr='四川绵阳', gender=0 where stuid in (1003, 1004); - 查询数据
select * from tb_student; -- 投影 select stuname, gender from tb_student; -- 别名（alias --- as） select stuname as 姓名,gender 性别 from tb_student; select stuname as 姓名,if(gender, '男', '女') as 性别 from tb_student; select stuname as 姓名,case gender when 1 then '男' else '女' end as 性别 from tb_student; - 对列做运算
select concat(stuname, ‘: ‘, tel) as 信息 from tb_student; - 筛选
select * from tb_student where stuid=1001; - 不等号用<>表示
`mysql
select from tb_student where stuid<>1001;
select from tb_student where stuid in (1001, 1003, 1005);
select stuid, stuname, gender from tb_student where stuid>1002;
select stuid, stuname, gender from tb_student where stuid<=1002;
select stuid, stuname, gender from tb_student where gender=0;
select from tb_student where stuid between 1002 and 1004;
select from tb_student where stuid>1004 and gender=0;
select * from tb_student where stuid>1004 or gender=0; - 注意：判断一个字段是否为null不能用=和<>
select from tb_student where addr is null;
select from tb_student where addr is not null;` - %是一个通配符表示零个或任意多个字符
select * from tb_student where stuname like '张%'; select * from tb_student where stuname like '%三%'; - _也是一个通配符，它表示一个字符
select * from tb_student where stuname like '张_'; select * from tb_student where stuname like '张__'; - 排序——order by
- 默认升序（ascending）可以不写，降序descending必须写
select * from tb_student order by stuid desc; select * from tb_student order by tel asc; select * from tb_student order by gender asc, stuid desc; select * from tb_student where gender=0 order by stuid desc; - 分页（限制）
select * from tb_student limit 3; select * from tb_student limit 3 offset 3; 跳过6条信息，查3条信息
select * from tb_student limit 3 offset 6;跳过2条信息，查4条信息
select * from tb_student limit 2,4; select * from tb_student where gender=1 order by stuid desc limit 0, 3;
DCL
创建hellokitty用户并设置口令
create user 'hellokitty'@'%'identified by '123123';给hellokitty授权
grant select on srs.* to 'hellokitty'@'%'; grant insert, delete, update on srs.* to 'hellokitty'@'%'; grant create, drop, alter on srs.* to 'hellokitty'@'%';获得srs的所有权限，但不能授权给别人
grant all privileges on srs.* to 'hellokitty'@'%'; grant all privileges on *.* to 'hellokitty'@'%';将hellokitty自己得到的权限再授权给别人
grant all privileges on srs.* to 'hellokitty'@'%' with grant option;召回权限
revoke all privileges on srs.* from 'hellokitty'@'%';
事务控制
创建hellokitty用户并设置口令
create user 'hellokitty'@'%'identified by '123123';给hellokitty授权
grant select on srs.* to 'hellokitty'@'%'; grant insert, delete, update on srs.* to 'hellokitty'@'%'; grant create, drop, alter on srs.* to 'hellokitty'@'%';获得srs的所有权限，但不能授权给别人
grant all privileges on srs.* to 'hellokitty'@'%'; grant all privileges on *.* to 'hellokitty'@'%';将hellokitty自己得到的权限再授权给别人
grant all privileges on srs.* to 'hellokitty'@'%' with grant option;召回权限
revoke all privileges on srs.* from 'hellokitty'@'%';
事务控制
开启事务环境
begin -- start transaction update tb_score set mark=mark-2 where sid=1001 and mark is not null; update tb_score set mark=mark+2 where sid=1002 and mark is not null;事务提交
commit；- 事务回滚
rollback；
