常见的数据库单表练习题

第一题：

表结构

CREATE DATABASE zy;
USE zy;
CREATE TABLE student (
  id INT(11) NOT NULL AUTO_INCREMENT,
  NAME VARCHAR(255) DEFAULT NULL,
  score INT(11) DEFAULT NULL,
  address VARCHAR(255) DEFAULT NULL,
  useremail VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

表数据

INSERT  INTO student VALUES
 (1,'张三',98,'北京','111111111@qq.com'),
 (2,'李四',88,'上海','111111112@qq.com'),
 (3,'王五',78,'广州','111111113@qq.com'),
 (4,'赵六',68,'深圳','111111114@qq.com'),
 (5,'孙七',58,'杭州','111111115@qq.com'),
 (6,'小红',48,'北京','111111116@qq.com'),
 (7,'小黑',99,'上海','111111117@qq.com'),
 (8,'小绿',100,'杭州','111111118@qq.com'),
 (9,'小粉',60,'杭州','111111119@qq.com'),
 (10,'小紫',70,'黑龙江','111111110@qq.com');

查询语句

SELECT * FROM student;
SELECT id,NAME,score FROM student WHERE score>70;
ALTER TABLE student MODIFY COLUMN NAME VARCHAR(50);
DESC student;
ALTER TABLE student ADD pingjia VARCHAR(20);
UPDATE student SET score=88 WHERE NAME='张三';
SELECT * FROM student WHERE score>80;
SELECT * FROM student WHERE id IN(1,5,7);
SELECT * FROM student WHERE id BETWEEN 5 AND 8;
SELECT * FROM student WHERE NAME='小红'