MySQL 入门笔记（三)

MySQL 入门笔记（三)

创建

CREATE TABLE IF NOT EXISTS student ( 
	id INT,
	name VARCHAR(20),
	age INT,
	sex VARCHAR(10),
	english INT,
	math INT
);

修改

UPDATE student set id = 1 WHERE id = 10;
UPDATE student set english = NULL WHERE id = 1;

插入

INSERT INTO student (id,name,age,sex,english,math) VALUES (1,'黎明',33,'男',99,98);
INSERT INTO student (id,name,age,sex,english,math) VALUES (2,'刘德华',34,'男',91,93);
INSERT INTO student (id,name,age,sex,english,math) VALUES (3,'张学友',32,'男',99,99);
INSERT INTO student (id,name,age,sex,english,math) VALUES (4,'郭富城',30,'男',93,98);
INSERT INTO student (id,name,age,sex,english,math) VALUES (5,'张国荣',40,'男',100,100);
INSERT INTO student (id,name,age,sex,english,math) VALUES (6,'梅艳芳',37,'男',99,100);
INSERT INTO student (id,name,age,sex,english,math) VALUES (7,'邱淑贞',30,'女',90,90);

删除

1. 删除表

DROP TABLE stu;

2. 删除表中指定数据

DELETE FROM student  WHERE id = 1;

3. 清空表

DELETE FROM student
TRUNCATE TABLE student;

查询

1. 查询表

SELECT * FROM student;

2. 查询指定列

SELECT name,age FROM student;

3. 去重查询

SELECT DISTINCT english,math FROM student

4. 条件查询

SELECT * FROM student WHERE math > 90 ;
SELECT * FROM student WHERE math > 90 AND english > 98;
SELECT * FROM student WHERE math > 90 OR english > 98;
SELECT * FROM student WHERE math IN (90,100);

SELECT * FROM student WHERE math BETWEEN 95 AND 100;

SELECT * FROM student WHERE english IS NULL;

5. 模糊查询

SELECT * FROM  student WHERE name LIKE  '_学%';
SELECT * FROM  student WHERE name LIKE  '_学_';