mysql 用DML操作数据库(二)

待我称王封你为后i 2023-10-18 15:36 117阅读 0赞

### 约束种类 ###

*  非空约束(not null)：字段不能为null值，必须给定具体的数据
 *  唯一性约束(unique) ：具有唯一性，不可重复，但可以为null
 *  主键约束(primary key) PK：该字段不能重复也不能为空
 *  外键约束(foreign key) FK：主要用来维护两个表之间数据的一致性。
 *  检查约束(目前MySQL不支持、Oracle支持)

**数据表添加相应的约束：**

创建表的同时，添加约束
        -- 创建班级表
    	  create table if not exists grade(
    	   gradeId int(11) primary key auto_increment,  -- PK，自动标识列
    	   gradeName varchar(20) default null
    	  )engine=InnoDB default charset=utf8;
    
     -- 删除表的语法
       drop table if  exists grades
    
       select * from grade
    
       -- 自动标识列 必须添加0或null哦！
       insert into grade VALUES(0,'T01');
       insert into grade VALUES(0,'T02');
    
       
      -- 创建学生表
    	 create table if not exists student(
    	  studentNo int(4) PRIMARY key not null comment '学号',  -- Pk(唯一，但不能为空)
    	  Loginpwd varchar(20) UNIQUE ,    -- 唯一约束（允许有一个为空）
    	  StudentName varchar(20) default null ,  
        sex ENUM('男','女')  NOT NULL DEFAULT '男', -- 约束 ENUM('男','女') 不需要数据类型哦！！
        address VARCHAR(100) default '杭州市西湖区',    -- 默认值（杭州市西湖区）
        tel  VARCHAR(20) default null,
        email VARCHAR(20)  not null,   -- 非空约束
        id   VARCHAR(18)  not null,
        gid   int(11) not null,
        FOREIGN KEY (`gid`) REFERENCES grade(gradeId)  -- FK 
    	 )ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
    
    -- [Err] 1215 - Cannot add foreign key constraint  
       -- 解决：ENGINE=InnoDB 这两张表必须相同,gid数据类型一致
    
      --表已创建可以单独添加约束
      语法： alter table 表名 add constraint 约束名 foreign key(字段名) references 主表名(字段 名)
    
     ALTER TABLE `student` ADD CONSTRAINT `fk_gids` FOREIGN KEY (`gid`) REFERENCES 
     grade(gradeId);
    
      -- 删除外键
     语法：alter table 表名 drop foreign key 约束名
    
    -- ALTER TABLE article DROP FOREIGN KEY fk_1

**小结:**

有了外键引用之后，表分为父表和子表 
        班级表：父表
        学生表：子表
        创建先创建父表
        删除先删除子表数据
        插入先插入父表数据

**DML操作数据:**

发现mysql 竟然不支持select *  into  新表名 from  源user,
    运行sql 一直报错 ，错误代码：1327 Undeclared variable  备份表名，
    查询资料才发现 原来mysql 不支持 select into form 这种语法
    
    可用如下方法去替换：
          Create table 新表名(Select * from 源user);

-- INSERT INTO  表名  [ ( 字段1, 字段2, 字段3, … ) ]  VALUES  (  '值1', '值2', '值3', …)
    
    select * from  grade;
    
    -- ctrl+shift+R 选中SQL语句执行
    
    insert into grade VALUES(4,'大一');
    insert into grade VALUES(5,'大二');
    
    
    select * from student;
    
    
          -- 创建学生表
    	 create table if not exists student(
    	  studentNo int(4) not null comment '学号',
    	  Loginpwd varchar(20) default null ,
    	  StudentName varchar(20) default null ,
         sex char(2) not null,
        address VARCHAR(100) default null,
        tel  VARCHAR(20) default null,
        email VARCHAR(20)  not null,
        id   VARCHAR(18)  not null,
        gid   int(4) not null
    	 )ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
    
    -- 不写时，默认所有的字段从左到右
    -- 1011	郭靖	男	1	13500000001	北京海淀区中关村大街1号 	guojing@bdqn.cn	450323198612111000
     insert INTO student values(1011,'aaa','郭靖','男',	'北京海淀区中关村大街1号','13500000001','guojing@bdqn.cn','450323198612111000',1);
    
    -- 部分字段 不允许为空，必填.
    -- 1012	李文才	男	2	13500000002	河南洛阳	liwencai@bdqn.cn	450323198112311000
     insert into student(studentno,studentName,sex,email,id,gid) values(1012,'李文才','男','liwencai@bdqn.cn','450323198112311000',2);
    
    
    select  * from `subject`;
    -- 1	高等数学-1	120	1
    
    insert into `subject` values(4,'高等数学-1',120,4);
    
    
    
    -- UPDATE   表名   SET    column_name = value  [ ,  column_name2 = value2, …. ]  [ WHERE   condition ];
    
    -- select * from  grade;
    select * from mygrade;
    
    -- 批量添加数据 insert into mygrade新表名（已存在） select * from grade(原表数据);
    insert into mygrade select * from grade;
    
    
    -- 批量添加多条数据
    -- INSERT INTO 
    -- [表名]([列名],[列名]) 
    --  VALUES
    -- ([列值],[列值])),
    -- 
    -- ([列值],[列值])),
    -- 
    -- ([列值],[列值]));
    
     -- 自动标示列，可以用0或null;
    insert into mygrade VALUES (0,'boy'),(0,'grile'),(0,'back'),(0,'my');
    
    
    
    -- (1)没有条件的更新 (整张表的数据)
    update mygrade set gradeName='JAVA26';
    
    -- (2) 根据条件更新
     update mygrade set gradeName='T01' where gradeId=1;
    
    
    -- (3)多个字段更新有条件
    -- 更新学生表密码123456，地址杭州市西湖区,电话110 根据条件是学号1012；
     select * from  student;
     
       update student set loginpwd='123456',address='杭州市西湖区', tel='110' where studentno=1012;
       
    
    -- 将数据表subject中ClassHour大于110且GradeID为1的课时都减少10
      select * from `subject`;
    
       -- and ==>&& 
       update `subject` set ClassHour=ClassHour-10 where ClassHour>110 and GradeID=1;
    
      -- or  ==>||
      update `subject` set ClassHour=ClassHour+10 where ClassHour<60 || GradeID=4;
    
    --   ClassHour>=60 and  ClassHour<=110  等同于==>BETWEEN 60  and 110 
      update `subject` set ClassHour=ClassHour+100 where ClassHour>=60 and  ClassHour<=110;
    
      update `subject` set ClassHour=ClassHour-100 where ClassHour BETWEEN 160 and 210; 
    
    
    --   TRUNCATE命令   
         -- 用于完全清空表数据，但表结构、索引、约束等不变
      -- truncate table 表名
       select * from mygrade;
      
       -- 语法  truncate table mygrade;
    
      -- 删除语法 delete from 表名 [where CONDITION]
    
        delete from mygrade where gradeid>3;
    
         delete from mygrade;
    
    
    -- 相同
    -- 都能删除数据、不删除表结构，但TRUNCATE 速度更快
    -- 不同
    -- 使用TRUNCATE TABLE重新设置AUTO_INCREMENT计数器
    -- 使用TRUNCATE TABLE不会对事务有影响
      
       insert into mygrade VALUES(0,'aaa');
       insert into mygrade VALUES(0,'cccc');
    
    
    -- 存放的路径： X:\360MoveData\Users\Administrator\Documents\Navicat\MySQL\servers\hlx\myschool

总结：

添加
            insert into 表名[(字段名,,,,)] values(值,,,,);
            insert into 表名[(字段名,,,,)] values(值,,,,),(值,,,,),(值,,,,);
            insert into 新表名(必须存在) select * from 源表
    
    --     修改
              update 表名 set 字段名=值[,字段名=值,字段名=值] where [CONDITION]
    --     删除
             delete from 表名  where [CONDITION]
    
            -- truncate table 表名