操作表中的数据

 一、SQL表达式和运算符

1. 表达式

   SQL命令中的表达式和其他语言中的表达式定义是一样的，都是指运算符将同类型的数据（如变量、常量、函数等）按照一定规则连接起来的具有特定意义的语句。

   表达式可以分为条件表达式和逻辑表达式，在MySQL中，这两种表达式只返回1和0。

   在数据库中的表达式一般指由常量、变量、函数和运算符组成。

   单个的常量、变量或函数也称作表达式。

   SQL中包括三种表达式：

   - 表名 后跟<字段名表达式>

   - select语句后的<目标表达式>

   - where语句后的<条件表达式>

2. 运算符

   运算符是保留字或主要用于SQL语句的WHERE子句中的字符，用于执行操作。

   一种符号，它是用来进行列间或者变量之间的比较和数学运算的，包括算术运算符、赋值运算符、比较运算符、逻辑运算符

   - 算术运算符：+ - * / %

   - 赋值运算符：=

   - 比较运算符：>   >=  <  <=  !=   <>  

     is null 是否为空

     is not null 是否不为空

     in  判断一个值是in列表中的任意各一个值

     not in  判断一个值不是in列表中的任意一个值

     like 通配符匹配

     between and  判断一个值是否在两个值之间

   - 逻辑运算符：and(或者&&)、or(或者||)和not(或者！)。

 二、向表中插入数据

1. 一次插入一行数据

   语法：

   insert  [ into ]  表名 [ ( 字段名1 , 字段名2 , ... ) ]   values  ( 值1 , 值2 , ... ) ;

   例：student表  （id，学号，姓名，性别，年龄）

   DROP TABLE IF EXISTS student;

   CREATE TABLE IF NOT EXISTS student(

       id int auto_increment PRIMARY KEY,

       stuno VARCHAR(15) NOT NULL UNIQUE,

       stuname VARCHAR(10) NOT NULL,

       gender char DEFAULT '男',

       age INT

   );

   DESC student;

   - 插入一行数据

     - INSERT INTO student(id,stuno,stuname,gender,age) VALUES(1,'19001','张三','女',20);

     - 省略into关键字

       INSERT  student(id,stuno,stuname,gender,age) VALUES(1,'19001','张三','女',20);

     - 省略所有字段名，必须按照字段顺序指定值。

       INSERT INTO student  VALUES(2,'19002','张三2','女',20);

   - 在指定列插入数据

     - id有自增约束，不建议指定值

       INSERT INTO student (stuno,stuname,gender,age)  VALUES ('19003','张三3','女',20)  ;

     - primary key约束和unique约束在添加值时，数据严禁重复。

       INSERT INTO student ( stuno , stuname , gender , age ) VALUES ('19003','张三3','女',20);

     - gender有默认值，在列名部分没有使用gender，会设为默认。

       INSERT  INTO  student ( stuno , stuname , age )  VALUES ( '19004' , '张三4' , 20 ) ;

     - gender有默认值，指定值时使用DEFAULT。

       INSERT INTO student(stuno,stuname,gender,age) VALUES('19005','张三5',DEFAULT,20);

     - 添加记录不指定所有字段值，不指定字段时，该字段必须是允许为空。

       INSERT INTO student(stuno,stuname,gender) VALUES('19005','张三5',DEFAULT);

2. 一次插入多行数据

   1）语法：

   INSERT  [INTO]  TABLE_NAME  [ ( 字段1 , 字段2 , 字段3 , …… .. ) ]

   VALUES  ( 值1 , 值2 , 值3 …… )  ,

   VALUES  ( 值1 , 值2 , 值3 …… )  ,  ...

   VALUES  ( 值1 , 值2 , 值3 …… ) ;

   注意：一次添加多条数据时MySQL特有的功能。

   2）通过INSERT SELECT语句将现有表中的数据添加到已存在的表中

   语法：

   INSERT INTO <表名>(列名)

   SELECT <列名>

   FROM <源表名> 

   例：

   INSERT INTO student2

   SELECT * FROM student;

   可以实现复制表的功能。

3. 总结

   添加记录但不指定所有字段值，不指定字段时，该字段必须是允许为空；

   自动增长列尽量不要指定值；

   有默认值约束的列不指定值时将使用默认值；

   主键列、唯一约束列的值不能重复；

   值与字段名必须一一匹配，但是可以不按照表中字段顺序指定

三、修改表中数据

语法：

UPDATE 表名

SET 要被修改的字段名1=新值1,字段名2=新值2,…..,字段名n=新值3

[WHERE 更新条件];

1. 修改表中全部数据

   修改表中的全部数据是一种不太常用的操作。

   例1）将所有学生的年龄+1

   update student

   SET age = age + 1;

2. 根据条件修改表中数据

   例：1）将学号是19003的学生年龄+1。

   update student

   SET age = age + 1

   WHERE stuno = '19003';

   2）修改学号是19006的学生姓名为李四，性别为女，年龄30。

   UPDATE student

   SET stuname = '李四',gender = '女',age = 30

   WHERE stuno = '19006';

四、删除表中数据

1. delete

   delete命令既能够删除整张表的数据又可以根据用户的筛选条件进行部分数据的删除操作。

   语法：

   DELETE FROM  表名  [ WHERE  筛选条件 ] ;

   1）删除所有信息

   DELETE  FROM  student ;

   注意：不要轻易使用DELETE命令，在使用前要慎重考虑。因为大多数数据库中的提交方式是自动的，在没有标识为事务的情况下是不能回滚的。DELETE命令是要记录到日志的操作，所以如果无意中使用DELETE命令，只能从备份的数据库中进行恢复。

   2）根据where条件删除数据

   例1：删除学号为19006的学生

   DELETE  FROM  student  WHERE  stuno  =  '19006' ;

   例2：删除学生编号为19002,19004的学生信息

   DELETE  FROM  student  WHERE  stuno  in ('19002','19004') ;

   错误写法

   DELETE  FROM  student  WHERE  stuno  =  '19002'  AND  stuno  =  '19004' ;

   以上命令运行后不会报错，受影响行0行，语法没错，只是未找到符合条件的学生信息。

   例3：删除性别为男，年龄为女，年龄21岁的学生信息

   DELETE  FROM  student  WHERE  gender  =  '女'  AND  age  =  21 ;

   例4：插入一个学生信息，年龄未设置

   INSERT  INTO  student (stuno,stuname)  VALUE ('19007','张三7') ;

   INSERT  INTO  student (stuno,stuname,age)  VALUE ('19008','张三8',NULL) ;

   DELETE  FROM  student  WHERE  age  =  null ;     无法删除

   正确写法：DELETE  FROM  student  WHERE  age  IS  NULL ;

   例5：删除班级表记录，在学生表中有关联数据，要先删除从表再删除主表。

   1）先创建班级表

   班级表

   CREATE  TABLE  IF  NOT  EXISTS  classinfo (

       Id  INT  auto_increment  PRIMARY  KEY ,

       classno  VARCHAR(20)  UNIQUE  NOT  NULL ,

       classname  VARCHAR(30)  UNIQUE  NOT  NULL ,

       Size  INT  班级人数

    ) ;

   DESC  classinfo ;

   INSERT  INTO  classinfo(classno,classname,size)  VALUES ('20201','软件191',50) ;

   INSERT  INTO  classinfo(classno,classname,size)  VALUES ('20202','软件192',45) ;

   SELECT   FROM  classinfo ;

   2）给student表添加字段班级编号，并设置约束。

   alter  TABLE  student  ADD  COLUMN  cid  INT  REFERENCES  classinfo(id) ;

   或者

   Alter  TABLE  student  ADD  COLUMN  cid  INT ;

   alter  TABLE  student  ADD  FOREIGN  KEY(cid)  REFERENCES  classinfo(id) ;

   外键允许为空，最好在插入数据前修改表结构或约束???？求证

   INSERT INTO student(stuno,stuname,age) VALUES('19007','张三7',25);        成功插入

   INSERT INTO student(stuno,stuname,age,cid) VALUES('19009','张三9',27,3);    成功插入

   INSERT INTO student(stuno,stuname,age,cid) VALUES('19010','张三10',28,4);   成功插入

   DELETE FROM student WHERE stuno = '19009';            删除成功

   删除主表classinfo

   DELETE FROM classinfo;删除失败，需要先删除从表中关联数据。

2. truncate

   语法：

   TRUNCATE    TABLE    表名 ; 

   1. 表结构、列、约束等不被改动

   2. 不能用于有外键约束引用的表

   3. 标识列重新开始编号

3. 总结

   TRUNCATE可以删除表中的所有数据，只留下一个表的结构定义。

   使用TRUNCATE语句执行操作是不记录日志操作的，因此其速度要比DELETE语句快的多。

   TRUNCATE语句将释放数据表的数据和索引所占据的所有空间，以及释放所有索引分配的空间。

   实际工作中，不建议使用TRUNCATE TABLE命令，因为使用它删除的数据不能恢复还原。

   TRUNCATE  TABLE 不能用于有外键约束引用的表，即主表中的数据，无论外键表中是否有相关数据。

   而Delete Table不同，必须先删除外键表的相关数据，再删除主表中数据。

   将学号19008、19010的学生cid设置为null，目的是删除与主表班级表的关联列数据

   UPDATE  student

   SET  cid  =  null

   WHERE  stuno  in ('19008','19010') ;

   TRUNCATE TABLE classinfo; 删除失败 ，有外键删除不了，即便没有相关数据。

   DELETE FROM classinfo;      删除成功，只要没有相关数据，就可以删除主表。

   delete删除后接着上次记录继续编号，truncate删除记录后重新编号。

   truncate语句只能用于清空表中的数据。

4. 复制表

   1）复制表的结构和数据

   create table 表名 select  from 表名;

   注意：不包含外键约束

   2）复制表的结构

   方式一：create table 表名 select  from 表名 where 1=2;

   方式二：create table 表名 like 表名;

   3）复制表的数据

   insert into 新表 select  from 表名;