MySQL语句练习

#创建表
CREATE TABLE student(
id INT(10) PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(20),
sex TINYINT(1) DEFAULT 1,
phone VARCHAR(50),
role INT(4),
score INT(10)
)
#查询表结构
DESC student;


#插入表数据
INSERT INTO student(`name`,sex,phone,role,score)
VALUES('tom',1,'15999999999',1,54),('rose',2,'158888888888',2,90),
('jack',1,'15666666666',3,94),('make',1,'15555555555',3,82);

#删除表中不需要的数据
DELETE FROM student WHERE id=2;

#查看表内容
SELECT * FROM student;

#修改rose的电话号码
UPDATE student SET phone = '158000000000' WHERE `name`='rose';

#查询不及格学员信息
SELECT * FROM student
WHERE score<60;

#查询name 是j开头的人数据
SELECT `name` FROM student
WHERE `name` LIKE '%j%';

#查询为1和3的数据
SELECT * FROM student WHERE role IN(1,3);

#删除role为1的数据
DELETE FROM student WHERE role=1;

#删除表数据
DELETE FROM student;

TRUNCATE student;
#删除表
DROP TABLE student;