MySQL学习笔记之四：插入、更新与删除数据

MySQL学习笔记之四：插入、更新与删除数据

李小艺 (2014-4-01)

插入、更新与删除数据

【Insert数据】

INSERT  INTO 表名  VALUES(值1,值2,值3,…,值n);

INSERT INTO  product  VALUES(1001, ‘OTC’, ‘Headache’,’beijing’);

INSERT  INTO  product (id, name, function, company, address)  VALUES(1002, ‘BCD’, ‘Headache’, ‘MSI’, ‘shenzhen’);

INSERT INTO product (id, function, name, address, company)

SELECT id, function, name, address, company

FROM medicine;

【Update数据】

UPDATE 表名  SET  属性名1=值1, 属性名2=值2,…,属性名n=值n

WHERE  条件表达式;

UPDATE product  SET  name=’aaa’, address=’shenzhen’ 

WHERE  id=10001;

【Delete数据】

DELETE  FROM 表名  WHERE  条件表达式;

DELETE  FROM  product;

DELETE  FROM  product  WHERE  id=10001;

 

其它查询命令

【两表连接查询】

SELECT  num, name, employee.d_id, age, sex, d_name, function

FROM  employee ,  department

WHERE  employee.d_id = department.d_id;

【左JOIN查询】可以查出左边表的全部数据，而只能查询右边表的指定记录

SELECT  num, name, employee.d_id, age, sex, d_name, function

FROM  employee, LEFT  JOINdepartment

ON  employee.d_id = department.d_id;

【右JOIN查询】可以查出右边表的全部数据，而只能查询左边表的指定记录

SELECT  num, name, employee.d_id, age, sex, d_name, function

FROM  employee, RIGHT  JOINdepartment

ON  employee.d_id = department.d_id;

【带IN的子查询】

SELECT  *  FROM  employee

WHERE  d_id   IN  (SELECT  d_id  FROM  department);

SELECT  *  FROM  employee

WHERE  d_id  NOT IN   (SELECT  d_id  FROM  department);

【带比较运算符的子查询】

SELECT  id, name, score

FROM   computer_stu

WHERE  score >= (SELECT  score  FROM  scholarship  WHERE  level=1);

SELECT  d_id, d_name

FROM  department

WHERE  d_id <> (SELECT  d_id  FROM  employee  WHERE  age=28);

【带EXISTS的子查询】

SELECT  *  FROM  employee

WHERE  EXISTS  (SELECT  d_name   FROM   department

                              WHERE   d_id = 1003);

SELECT  *  FROM  employee

WHERE  age>24   AND  EXISTS

                          (SELECT  d_name  FROM  department

                           WHERE  d_id=1003);

【带ANY和ALL的子查询】

SELECT  *  FROM   computer_stu

WHERE  score >= ANY

             (SELECT  score  FROM  scholarship);

SELECT  *  FROM   computer_stu

WHERE  score >= ALL

             (SELECT  score  FROM  scholarship);