DATABASE MYDB;
USE MYDB;
CREATE TABLE IDENTITY (
NAME CHAR(
20)
PRIMARY KEY,
BIRTH DATETIME NOT NULL,
HOME CHAR(
20)
);
CREATE TABLE STUDENT (
NUMBER INT PRIMARY KEY AUTO_INCREMENT,
NAME CHAR(
20)
NOT NULL,
AGE TINYINT NOT NULL,
HOME CHAR(
30)
);
INSERT INTO STUDENT
VALUES (
30,
‘Curry‘,
10,
‘Golden State‘); #不指定字段名
INSERT INTO STUDENT (NAME, AGE, HOME)
VALUES (
‘Durant‘,
11,
‘Golden State‘); #指定字段名
INSERT INTO STUDENT (NAME, AGE, HOME)
VALUES
(‘Curry‘,
30,
‘Golden State‘),
(‘Durant‘,
29,
‘Golden State‘),
(‘James‘,
23,
‘Cleveland‘),
(‘Irving‘,
11,
‘Boston‘); #插入多条记录
INSERT INTO STUDENT (NAME, AGE, HOME)
SELECT
NAME,
TIMEDIFF(NOW(), BIRTH),
HOME
FROM IDENTITY; #插入查询结果
SELECT *
FROM STUDENT;
DROP DATABASE MYDB;
View Code
二、更新记录
INSERT INTO STUDENT VALUES (35, ‘Durant‘, 0, 29, ‘Oklahoma‘);
UPDATE STUDENT
SET HOME = ‘Golden State‘
WHERE NUMBER = 35;
View Code
三、删除记录
DELETE FROM STUDENT
WHERE HOME = ‘Golden State‘; #清除指定条件的数据
DELETE FROM STUDENT; #清空所有数据
View Code
MySQL笔记：数据插入
标签：identity stat font student data select ide home class
