数据库--数据操作

‘,‘男‘); INSERT into info(id,NAMES) values(2,‘田硕‘); INSERT into info(id,NAMES) values(3,‘田硕‘),(4,‘田硕‘),(5,‘田硕‘); NSERT into info(names,sex) select nameS,sex from info ;

三、更新操作UPDATE

update info set sex = ‘女‘, names = ‘田硕‘ where id=5;

四、删除操作DELETE

DELETE from info where id = ‘11‘; -- 删除指定数据
 
DELETE from info; -- 删除整张表中所有的数据（一行一行删）
        
TRUNCATE info; -- 清空整张表（直接都删，比delete快）

五、查询操作DELECT

　　5.1  简单查询

select * from person; -- 查询所有

select name,SEX from person; -- 按指定字段查询

select name,SEX as‘性别‘ from person; -- as 表示为字段起别名

select salary+200 from person; -- 可以进行数据列运算

select DISTINCT age,name FROM person;  -- 去重复查询

　　5.2  条件查询

　　　　1.运算符

select * FROM person WHERE age >20;
select * FROM person WHERE age <=20;

select * FROM person WHERE age <>20;（也是不等于）
select * FROM person WHERE age !=20;

　　　　2.关键字

select * FROM person where dept_id is null;

select * FROM person where dept_id is not null;

select * FROM person where name = ‘abc‘;

　　　　3.逻辑运算符 and or

select * from person where age = 28 and salary =53000;

select * from person where age = 23 or salary =2000;
                
select * from person where not(age = 28 and salary =53000);

　　5.3  区间查询

select * from person where age BETWEEN 18 and 20;

ps: between...and 前后包含所指定的值
等价于 select * from person where salary >= 4000 and salary <= 8000;

　　5.4  集合查询

select * from person where id = 1 or id = 3 or id = 5;

select * from person where id not in(1,3,5);

　　5.5  模糊查询

select * from person where name like ‘%e%‘; -- 包含指定参数
select * from person where name like ‘%e‘; -- 以什么结尾
select * from person where name like ‘e%‘; -- 以什么开头

select * from person where name like ‘__e%‘; -- _表示单个字符站位符

select * from person where name like ‘__‘;

　　5.6 排序查询

select * from person where age >30 ORDER BY salary desc; -- ASC正序 DESC倒序
            
select * from person ORDER BY CONVERT(name USING gbk);-- 中文排序

 

数据库--数据操作

标签：esc   upd   into   post   数据库   mysq   int   去重   参数