SQL练习1

-- 第一天

INSERT INTO stu(id,name,age) VALUES(001,'张三',18);
INSERT INTO stu(id,name,age) VALUES(002,'李四',22);
CREATE TABLE person(
xingming VARCHAR(20),
six VARCHAR(3),
habby VARCHAR(30),
beizhu char(50)
);
SELECT *FROM stu;

INSERT INTO stu VALUES(004,'张无忌',21,90.5,"1997-12-1","2020-2-15");
desc stu;

-- 删除大于21岁的
DELETE FROM stu WHERE age>21;

-- 删除表中所有数据
DELETE from stu;

TRUNCATE TABLE stu;

-- 修改数据
UPDATE stu set age=26 WHERE id=1;
update stu set age=23,score=99 WHERE name='李四';

CREATE TABLE student3 (
id int, -- 编号
name varchar(20), -- 姓名
age int, -- 年龄
sex varchar(5), -- 性别
address varchar(100), -- 地址
math int, -- 数学
english int -- 英语
);

INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES (1,'马云',55,'男','
杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),(4,'柳岩
',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港',99,99),(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);

SELECT *FROM student;
-- 复制表
CREATE TABLE student LIKE student3;

-- 删除student3
drop table student3;

-- 1.查询student表下的姓名和年龄
SELECT name,age from student;

-- 2.查询年龄大于20岁
SELECT * FROM student WHERE age>=20;

-- 3.去重复的结果集
SELECT distinct address from student;

-- 4.计算math和English分数之和
-- 起别名
-- 如果有数据为null，那么相加之后结果为null，用ifnull解决
SELECT NAME,math,english,math+IFNULL(english,0) as 总分 from student;

-- 5.查询年龄为22,18,25岁的所有信息
SELECT * FROM student where age=18 or age=22 or age=25;

SELECT * from student where age IN (18,22,25);

-- 查询年龄不等于20岁
SELECT * from student where age!=20;

SELECT * from student where age<>20;

-- 查询年龄大于等于20，小于等于30的信息
SELECT * from student where age >=20 and age <=30;
-- 不推荐
SELECT * from student where age >=20 && age <=30;

SELECT * from student where age BETWEEN 20 and 30;

-- 查询英语成绩为null
SELECT *from student where english IS null;

-- 查询英语成绩不为空
SELECT *from student where english is not null;

-- 查询姓马的人
-- 模糊查询
-- %任意字符 _单个字符
SELECT *FROM student where name LIKE '马%';

-- 查询第二个字是化的人
select *from student where name LIKE '_化%';

-- 查询姓名是三个字的人
select *from student where name like '___';

-- 查询姓名中包含德的人
SELECT *from student where name like '%德%';