PS:TINYINT取值范围(-128-127,0-255);auto_increment自动编号
USE db;
CREATE TABLE student
(
sno INT UNSIGNED NOT NULL AUTO_INCREMENT,
sname VARCHAR(20) NOT NULL,
sage TINYINT UNSIGNED NOT NULL,
sbirthday DATETIME,
saddress VARCHAR(50),
sscore DECIMAL(4,1),/*999.9*/
PRIMARY KEY(sno),
)ENGINE=MYISAM AUTO_INCREMENT=201401 DEFAULT CHARSET=utf8;
DESC student;
-------------------------------------------------------------------------------------------------------------------
1)INSERT 操作 插入数据
INSERT INTO student VALUES(NULL,'李四',18,'2000-1-8','安徽',82);
INSERT student VALUES(NULL,'张三',19,'1999-7-18','北京',90);
INSERT INTO STUDENT VALUES(NULL,'LILI',20,'1998-2-9','HUBIE',89),(NULL,'LICI',21,'Hong Kong',70 );
---把表中信息查询出来,并插入(insert into select * from t)
INSERT INTO student (sname,sage,sbirthday,saddress,ssore) SELECT sname,sgae,sbirthday,saddress,sscore FROM student;
INSERT INTO student (sname,sage) VALUES ('jack',18),('Amy',22);
----如果id相同替换插入:
REPLACE INTO student (sname,sage) VALUES ('张默默',18),('李丽丽',19);
REPLACE INTO student VALUES(201436,'AAA',22,'2001-2-3','SHANGHAI',78);
--查看查询数据显示
SELECT * FORM student;
2)修改数据
UPDATE student SET sscore=89,saddress='北京' WHERE sno=201401;
3)删除数据
DELECT FORM student WHERE sno=201401;
DELECT FORM student;/*无条件删除所有数据*/
TRUNCATE TABLE student;/*直接清空数据表记录,auto_increment自动编号从1重新分配*/
