数据库造神计划第五天---增删改查（CRUD）（1）

INSERT [INTO] table_name
    [(column [, column] ...)]
VALUES 
    (value_list) [, (value_list)] ...

value_list: value, [, value] ...

# 创建⼀个⽤于演⽰的表
create table users (
    id bigint,
    name varchar(20) comment '⽤户名'
);

# 插入第一条记录
insert into users values (1, '张三');

# 插入第二条记录
insert into users values (2, '李四');

# 指定了具体要插⼊的列
insert into users(id, name) values (3, '王五');

# 每个value_list表⽰⼀⾏数据
insert into users(id, name) values (4, '赵六'), (5, '钱七');

SELECT
   [DISTINCT]
   select_expr [, select_expr] ...
   [FROM table_references]
   [WHERE where_condition]
   [GROUP BY {col_name | expr}, ...]
   [HAVING where_condition]
   [ORDER BY {col_name | expr } [ASC | DESC], ... ]
   [LIMIT {[offset,] row_count | row_count OFFSET offset}]

#创建表结构
CREATE TABLE exam (
    id BIGINT,
    name VARCHAR(20) COMMENT '同学姓名',
    chinese float COMMENT '语⽂成绩',
    math float COMMENT '数学成绩',
    english float COMMENT '英语成绩'
);

#插⼊测试数据 
INSERT INTO exam (name, chinese, math, english) VALUES
    (1, '唐三藏', 67, 98, 56),
    (2, '孙悟空', 87, 78, 77),
    (3, '猪悟能', 88, 98, 90),
    (4, '曹孟德', 82, 84, 67),
    (5, '刘⽞德', 55, 85, 45),
    (6, '孙权', 70, 73, 78),
    (7, '宋公明', 75, 65, 30);

# 使⽤ * 可以查询表中所有列的值
select * from exam;

select id,name,chinese from exam;

# 表达式本⾝就是⼀个常数
select id,name,10 from exam;

# 也可以是常量的运算
select id,name,10+1 from exam;

# 表达式中包含⼀个字段
select id,name,chinese+10 from exam;

 # 表达式包含多个字段
select id, name, chinese + math + english  from exam;

 SELECT column [AS] alias_name [, ...] FROM table_name;

#为总分这⼀列指定别名
select id, name, chinese + math + english as 总分 from exam;

# 通过观察有两条98的记录
select math from exam;

# 去重查询
select distinct math from exam;

SELECT
 select_expr [, select_expr] ... [FROM table_references]
 WHERE where_condition

select name, english from exam where english < 60;

select name, chinese, english from exam where chinese > english;

select name, chinese + math + english as 总分 from exam where chinese + math + english < 200;

select * from exam where chinese > 80 and english > 80;

select * from exam where chinese > 80 OR english > 80;

select * from exam where chinese > 80 or math > 70 and english > 70;

select * from exam where (chinese > 80 or math > 70) and english > 70;

# 使⽤BETWEEN AND 实现
select name, chinese from exam where chinese between 80 and 90;
# 使⽤ AND 实现
select name, chinese from exam where chinese >= 80 and chinese <= 90;

# 使⽤IN实现
select name, math from exam where math in (78, 79, 98, 99);
# 使⽤OR实现
select name, math from exam where math = 78 or math = 79 or math = 98 or math = 99;

select * from exam where name like '孙%';

 select * from exam where name like '孙_';

 # 写⼊⼀条数据，英语成绩为NULL
 insert into exam values (8, '张⻜', 27, 0, NULL);

 # 使⽤is not null
select * from exam where english is not null;

 # 观察结果中的总分
select name, chinese + math + english as 总分 from exam;