MySQL数据操作DML

DML（Data Manipulation Language 数据操纵语言）

DML是用来对数据库里的数据进行操作的语言。DML需要 commit。
当使用 mysql 客户端操作数据库时，这些语句都会自动提交，不用执行 commit。

MySQL数据库查询

结构化查询语言SQL介绍

sql是用于访问和处理数据库的标准的计算机语言

什么是SQL

SQL 指结构化查询语言
SQL 使我们有能力访问数据库
SQL 是一种 ANSI 的标准计算机语言
注：ANSI，美国国家标准化组织

sql能做什么？

SQL 面向数据库执行查询
SQL 可从数据库取回数据
SQL 可在数据库中插入新的记录
SQL 可更新数据库中的数据
SQL 可从数据库删除记录
SQL 可创建新数据库
SQL 可在数据库中创建新表
SQL 可在数据库中创建存储过程
SQL 可在数据库中创建视图
SQL 可以设置表、存储过程和视图的权限

sql是一种标准吗，但是....

SQL 是一门 ANSI 的标准计算机语言，用来访问和操作数据库系统。SQL 语句用于取回和更新数据库中的数据。SQL 可与数据库程序协同工作，比如 MS Access、DB2、Informix、MS SQL Server、Oracle、Sybase 以及其他数据库系统。

不幸地是，存在着很多不同版本的 SQL 语言，但是为了与 ANSI 标准相兼容，它们必须以相似的方式共同地来支持一些主要的关键词（比如 SELECT、UPDATE、DELETE、INSERT、WHERE 等等）。

注释：除了 SQL 标准之外，大部分 SQL 数据库程序都拥有它们自己的私有扩展

创建表

create table student(
      id    int    auto_increament    primary key,
      name varchar（10），
      age   int（4），
      phone  int(32)
);

插入数据 （增）

insert into .... values

/*插入一条数据*/
insert into 表 (列名,列名...) values (值,值,值...)

/*一次插入多条数据 */ 
insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)

/*以下为扩展*/
/*方式三*/
insert into 表1 (列名,列名...) select  列名,列名... from 表2

注意

这个插入是按行插入的 ；
每个字段对应一个值，非常严格的一一对应；
形式：数字可以直接写，字符串和时间加单引号，但如果是函数值，则不能加引号；
auto_increment，timestamp等字段无需插入

image.png

上面这个表用sql语句来写

insert into student (name, age, phone) values('王麻子', 32, '13141098899');
$ 插入一行数据
insert into student (
    id,name, age, phone
) values(
    2,'九筒', 32, '13721096541'),
    (3,'六子', 18, '13139192657'),
    (4,'马邦德', 37, '15855996688');     $插入多行数据

修改数据 （改）

update ...set...

update 表名 set 字段名1=值表达式1，字段名2=值表达式2，....[where条件] [order排序] [limit限定];

update  student set name='李四' where id=2  
 将id=2的那条数据中的九筒换成name是李四的

中间的字段名可以修改多个 条件一定要是可以唯一标识的你要修改的那条记录
执行updata语句的时候一定不能没有wherer条件 执行没有where的更新语句一定要慎重

查询 （查）

select .... from 表名

select * from 表   -这个是全表查询，建议不要这么查会加重cpu
select * from 表 where id > 2  -加上条件的查询id大于2的信息
select id,name,age as gg from 表 where id > 2  -里面的as是给前面要查询那个字段换个名字来显示 仅作用于显示 原表中的数据不会改变

高级查询

a、条件
    select * from 表 where id > 1 and name != '王麻子' and age = 18;
    -查询表中id大于1的数据并且name不等于王麻子且age等于18的信息
    select * from 表 where id between 5 and 16;
    -查询表中id在5到16 之间的信息
    select * from 表 where id in (11,22,33)   -查询id是11，22，33的1
    select * from 表 where id not in (11,22,33)   -查询id不是11，22，33的1
    select * from 表 where id in (select id from 表)  -查询的id是从另一长表中查询出来的id
 
b、通配符
    select * from 表 where name like 'sha%'  - sha开头的所有（多个字符串）
    select * from 表 where name like 'shar_'  - sha开头的所有（一个字符）
 
c、限制
    select * from 表 limit 5;            - 获取前 5 行
    select * from 表 limit 0,2;          - 从第 1 行开始, 取出 2 行, 包含第 1 行
    select * from 表 limit 2 offset 0    - 从第 1 行开始, 取出 2 行, 包含第 1 行
 
d、排序
    select * from 表 order by 列 asc              - 根据 “列” 从小到大排列
    select * from 表 order by 列 desc             - 根据 “列” 从大到小排列
    select * from 表 order by 列1 desc,列2 asc    - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序
 
e、分组
    select age from 表 group by age   -相当于给age单独分成一个小组
    select age,id from 表 group by age,id
    select age,id from 表  where id > 10 group by age,id order by id desc
    select age,id,count(*),sum(age),max(age),min(age) from 表 group by age,id
 
    select age from 表 group by age having max(id) > 10
    - having和where一样都是筛选 但筛选不同 having是对group by 对象进行筛选的
    特别的：group by 必须在where之后，order by之前,order by 是最后进行的是对group by分的组进行排序
    
f、嵌套查询
select * from  (select name from t1 where age>18 and age < 25 order by id desc limit 2 ) as tt  order by id; 
- 首先先执行括号中的select语句将t1表中的年龄在18到25之间的name 根据id从大到小排序只取两行
  的数据结果 然后根据这个结果查询所有结果别名为tt 并根据id进行排序

多表查询

image.png

删除 （删）

delete from ...

后面一定加where条件 字段唯一可以标识整行数据的 id

删之前用select查查是否是要删除的那个

慎重删除

清空表的内容，表本身还在

delete from 表名    /*自增列的值继续递增，可以加 where 子句*/
truncate table 表名  /*自增列的值重新从 1 开始*/
/*删除表中的所有数据，自增列的值继续递增*/
delete from tb1;

/*删除表中的某些数据，被删除的数据的自增列的值将不会再次出现，自增列的值继续递增*/
delete  from  tb1   where id < 20;

删除表 表会消失

drop table  表名     /*整个表将不复存在*/

注意
“以行为单位”删除
删除语句中，where条件如果不写，则就删除了所有数据
order排序子句用于设定删除数据的先后顺序
limit限定子句用于限定在设定的顺序情况下删除指定的某些行

drop delete truncate的区别

drop 直接删掉表
truncate 删除表中数据，再插入时自增长id又从1开始
delete 删除表中数据，可以加where字句, 增长 id 会继续增长。
（1）DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。TRUNCATE TABLE则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
（2） 表和索引所占空间。当表被TRUNCATE后，这个表和索引所占用的空间会恢复到初始大小，而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。
（3） 一般而言，drop > truncate > delete
（4） 应用范围。TRUNCATE只能对TABLE；DELETE可以是table和view(视图)
（5）TRUNCATE和DELETE只删除数据，而DROP则删除整个表（结构和数据）。
（6）truncate与不带where的delete：只删除数据，而不删除表的结构（定义）drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。
（7）delete语句为DML（data maintain Language),这个操作会被放到rollback segment中,事务提交后才生效。如果有相应的tigger,执行的时候将被触发。
（8）truncate、drop是DLL（data define language),操作立即生效，原数据不放到 rollback segment中，不能回滚
（9） 在没有备份情况下，谨慎使用drop与truncate。要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。要删除表用drop;若想保留表而将表中数据删除，如果于事务无关，用truncate即可实现。如果和事务有关，或老师想触发trigger,还是用delete。
（10）Truncate table表名 速度快,而且效率高,因为:
truncate table 在功能上与不带 WHERE 子句的DELETE语句相同：二者均删除表中的全部行。但TRUNCATE TABLE比DELETE速度快，且使用的系统和事务日志资源少。DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
（11）TRUNCATE TABLE删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用DELETE。如果要删除表定义及其数据，请使用DROP TABLE 语句。
（12） 对于由FOREIGN KEY约束引用的表，不能使用TRUNCATE TABLE，而应使用不带WHERE子句的DELETE语句。由于TRUNCATE TABLE不记录在日志中，所以它不能激活触发器。

复制表的结构

-- 清空表数据
    TRUNCATE [TABLE] 表名

-- 复制表结构
    CREATE TABLE 表名 LIKE 要复制的表名

-- 复制表结构和数据
    CREATE TABLE 表名 [AS] SELECT * FROM 要复制的表名

-- 检查表是否有错误
    CHECK TABLE tbl_name [, tbl_name] ... [option] ...

-- 优化表
   OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

-- 修复表
   REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]

-- 分析表
   ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] 

image.png

创建有外键的表

create table class(
    id int not null auto_increment primary key,
    class_name varchar(8),
    create_date DATE
);

create table teacher(
    id int not null auto_increment primary key,
    name varchar(12) not null,
    class_id int(2) not null,
    foreign key (class_id) references class(id) - mysql 自动给外键起名字teacher_ibfk_1*/
    
    /*给外键命名*/
    /*constraint fk_class foreign key (class_id) references class(id)*/
);

创建完表就可以执行增、删、改、查这些语句了 首先的是往表里插入数据,后面的根据要求来进行操作

mysql> insert into class ( class_name, create_date ) values ( '云计算1805', '2018-05-16' );
mysql> insert into class ( class_name, create_date) values ( '云计算1806', '2018.06.16' );
mysql> insert into class ( class_name, create_date ) values ( '云计算1807', '2018_07_16' ),('云计算1808','20180816');

连表

企业通用方法

-- 查询到 杨哥 多负责的班级的班级名
select teacher.name, class.class_name
from teacher, class
where 
teacher.class_id = class.id
and
teacher.name = "杨哥";


-- 查询到 每个老师负责的班级名称

select teacher.name, class.class_name
from teacher, class
where 
teacher.class_id = class.id;

-- 查询到 云计算1806 班都有那些老师负责

select teacher.name, class.class_name
from teacher, class
where 
teacher.class_id = class.id
and
class.class_name = "云计算1806";

/*无对应关系则不显示*/
select  A.class_name, B.name
from class as A, teacher as B
Where A.id = B.class_id
 
/* 内连接   无对应关系则不显示*/
select A.class_name, B.name
from class as A inner join teacher as B
on A.id = B.class_id
 
/* 左连接   左边的表(A)所有显示，如果右边的表(B)中无对应关系，则值为null*/
select A.class_name, B.name
from class as A left join teacher as B
on A.id = B.class_id
 
/* 右连接 右边的(B)表所有显示，如果左边的表(A)中无对应关系，则值为 NULL*/
select A.name, B.name
from class as A right join teacher as B
on A.id = B.class_id