MySQL插入更新删除数据

关注微信公共号：小程在线

关注CSDN博客：程志伟的博客

 

CREATE TABLE person
(
  id     INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name   CHAR(40) NOT NULL DEFAULT '',
  age    INT NOT NULL DEFAULT 0,
  info   CHAR(50) NULL,
  PRIMARY KEY (id)
);


select * from person;
insert into person (id,name,age,info) values (1,'Green',21,'Lawyer');
select * from person;


insert into person (id,name,age,info) values (2,'Suse',2,'dance');
select * from person;


INSERT INTO person  VALUES (3,'Mary', 24, 'Musician');
select * from person;


INSERT INTO person (name, age,info)
    VALUES('Willam', 20, 'sports man');
select * from person;


INSERT INTO person (name, age ) VALUES ('Laura', 25);
select * from person;


INSERT INTO person(name, age, info)
VALUES ('Evans',27, 'secretary'),
('Dale',22, 'cook'),
('Edison',28, 'singer');
select * from person;


INSERT INTO person 
VALUES (9,'Harry',21, 'magician'), 
(NULL,'Harriet',19, 'pianist');



CREATE TABLE person_old
(
  id     INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name   CHAR(40) NOT NULL DEFAULT '',
  age    INT NOT NULL DEFAULT 0,
  info   CHAR(50) NULL,
  PRIMARY KEY (id)
);


INSERT INTO person_old VALUES (10,'Harry',20, 'student'), (11,'Beckham',31, 'police');
select * from person_old;


INSERT INTO person(id, name, age, info)
 SELECT id, name, age, info FROM person_old;
select * from person;


更新数据
select * from person where id=10; 
UPDATE person SET age = 15, name='LiMing'  WHERE id = 10;
select * from person where id=10; 


update person set info='student' where age between 19 and 22;
select * from person where age between 19  and 22;


删除数据
select * from person where id=10;
delete from person where id=10;


delete from person where age between 19 and 22;
select * from person where age between 19  and 22;


delete from person;

select * from person;

 

CREATE TABLE person
(
  id     INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name   CHAR(40) NOT NULL DEFAULT '',
  age    INT NOT NULL DEFAULT 0,
  info   CHAR(50) NULL,
  PRIMARY KEY (id)
);


【例.1】在person表中，插入一条新记录，id值为3，name值为Green，age值为21，SQL语句如下：
INSERT INTO person (id ,name, age , info) VALUES (1,'Green', 21, 'Lawyer');


【例.2】在person表中，插入一条新记录，id值为4，name值为Suse，age值为22，info值为dancer，SQL语句如下：
INSERT INTO person (age ,name, id , info)
VALUES (22, 'Suse', 2, 'dancer');


【例.3】在person表中，插入一条新记录，name值为Mary，age值为24，SQL语句如下：
INSERT INTO person 
VALUES (3,'Mary', 24, 'Musician');


【例.4】在person表中，插入一条新记录，name值为Willam，age值为20，info值为sports man，SQL语句如下：
INSERT INTO person (name, age,info)
    VALUES('Willam', 20, 'sports man');


【例.5】在person表中，插入一条新记录，name值为laura，age值为25，SQL语句如下：
INSERT INTO person (name, age ) VALUES ('Laura', 25);


【例.6】在person表中，在name、age和info字段指定插入值，同时插入3条新记录，SQL语句如下：
INSERT INTO person(name, age, info)
VALUES ('Evans',27, 'secretary'),
('Dale',22, 'cook'),
('Edison',28, 'singer');


【例.7】在person表中，不指定插入列表，同时插入2条新记录，SQL语句如下：
INSERT INTO person 
VALUES (9,'Harry',21, 'magician'), 
(NULL,'Harriet',19, 'pianist');


【例.8】从person_old表中查询所有的记录，并将其插入到person表中，过程如下：
首先，创建一个名为person_old的数据表，
CREATE TABLE person_old
(
  id     INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name   CHAR(40) NOT NULL DEFAULT '',
  age    INT NOT NULL DEFAULT 0,
  info   CHAR(50) NULL,
  PRIMARY KEY (id)
);
向person_old表中添加两条记录：
INSERT INTO person_old
VALUES (10,'Harry',20, 'student'), (11,'Beckham',31, 'police');
将查询结果插入到表中
INSERT INTO person(id, name, age, info)
 SELECT id, name, age, info FROM person_old;


【例.9】在person表中，更新id值为10的记录，将age字段值改为15，将name字段值改为LiMing，SQL语句如下：
UPDATE person SET age = 15, name=’LiMing’ WHERE id = 10;


【例.10】在person表中，更新age值为19到22的记录，将info字段值都改为student，SQL语句如下：
UPDATE person SET info=’student’ WHERE id BETWEEN 19 AND 22;


【例.11】在person表中，删除id等于10的记录，SQL语句如下：
DELETE FROM person WHERE id = 10;


【例.12】在person表中，使用DELETE语句同时删除多条记录，在前面UPDATE语句中将age字段值在19到22之间的记录的info字段值修改为student，在这里删除这些记录，SQL语句如下：
DELETE FROM person WHERE age BETWEEN 19 AND 22;


【例.13】删除person表中所有记录，SQL语句如下：
DELETE from person;