CREATE TABLE tb_user(
id INT,
NAME VARCHAR(30),
gender CHAR(3),
birthday DATETIME,
salary DOUBLE(7,2)
);
#插入数据
# insert into 表名(列名,列名...列名)values(值1,值2...值n);
INSERT INTO tb_user(id,NAME,gender,birthday,salary)VALUES
(1,'张三','男','1990-9-19 12:11:11',9000.00);
INSERT INTO tb_user(id,NAME,gender,birthday,salary)VALUES
(2,'李四','男','1991-9-19 12:10:31',9200.00);
#修改数据
# update 表名 set 列名=值,列名=值2,列名=值3 where 条件=xx;
UPDATE tb_user SET salary=9999.00,birthday='1989-9-19 12:11:11' WHERE id=1;
#删除语句 删除单条或多条
# delete from 表名 where 条件
DELETE FROM tb_user WHERE id = 2;
DELETE FROM tb_user;#全表删除
#查询
#select * from tb_user; 全表查询 * :代表所有列
SELECT * FROM tb_user;
SELECT * FROM tb_user WHERE id = 1;
#指定的列 只要id和name两个列的数据
SELECT id,NAME FROM tb_user;
#模糊查询 like
SELECT * FROM tb_user WHERE NAME LIKE '%三%';
# 张是第一个字的姓
SELECT * FROM tb_user WHERE NAME LIKE '张%'
# _ 一个下划线代表一个字母
SELECT * FROM tb_user WHERE NAME LIKE '_三'
SELECT * FROM tb_user WHERE NAME LIKE '__来'
SELECT * FROM tb_user WHERE NAME LIKE '%来'
INSERT INTO tb_user (id, NAME, gender, birthday, salary,age)
VALUES (7,'张飞','男','2000-6-21',15000.00,20),
(8,'关羽','男','1999-5-21',16000.00,21),
(4,'刘备','男','1990-4-21',55000.00,30),
(5,NULL,'男','2000-5-21',5000.00,20),
(6,'刘梅','女','2000-5-21',11000.00,20);
#1.查询性别为男，并且年龄为20的学生记录
SELECT * FROM tb_user WHERE gender = '男' AND age = 20;
#2.查询学号是1或者名字是关羽的记录
SELECT * FROM tb_user WHERE id=1 OR NAME='关羽';
#3.查询学号是1,2,3的记录
SELECT * FROM tb_user WHERE id = 1 OR id =2 OR id=3;
SELECT * FROM tb_user WHERE id IN(1,2,3);
#4.查询姓名为null的记录
SELECT * FROM tb_user WHERE NAME IS NULL;
#5.查询姓名不为null的学生记录
SELECT * FROM tb_user WHERE NAME IS NOT NULL;
#6.查询年龄在18-20之间的学生记录
SELECT * FROM tb_user WHERE age > 17 AND age < 21;
SELECT * FROM tb_user WHERE age BETWEEN 18 AND 20;
#7.查询性别非男的学生记录
SELECT * FROM tb_user WHERE gender = '女';
