MySQL 增删改查

一、操作表记录（增删改查，Create,Read,Update,Delete）

1.Insert

INSERT INTO table_name [(column [,column.....])] VALUES (value [,value......]);

INSERT INTO employee (id,name,gender,birthday,entry_data,job,salay,resume)
VALUES (null,'赖泽铵','男','1996-7-7','2018-12-8','python工程师',5000,'啥都会');

insert into employee values (null,'吴志雄','男','2000-1-1','2001-2-2','董事长','1','全靠他了'),(null,'赖泽铵','男','1996-7-7','2018-12-8','python工程师',5000,'啥都会'); #所有列都写入可以不写 ，插入多条

2.Update

UPDATE tbl_name SET col_name=exprl [,col_name2=expr2......]
（1）将所有员工薪水改为5000

update employee set salay= 5000;

(2)将赖泽铵薪水改为3000

update employee set salay = 3000 where name='赖泽铵';

(3)将侯文泽薪水改为4000，job改为c++

update employee set salay = 4000,job = 'c++' where name = '侯文泽';

(4)将郑嘉颖薪水增加1000

update employee set salay = salay+1000 where name = '郑嘉颖';

3.Delet (truncate) #truncate整表摧毁

delete from table_name [WHERE where_definition]

(1)删除表中名为侯文泽的记录

delete from employee where name = '侯文泽';

（2）删除表中所有记录

delete from employee;

(3)使用truncate删除表中记录

truncate employee;

4.Select

SELECT [DISTINCT] * | {column1,column2,......} FROM table;

(1)查询表中所有员工的信息

select * from employee;

（2）查询表中所有员工的姓名和薪水

select name,salay from employee;

（3）过滤表中重复数据

select distinct salay from employee;

（4）在所有员工薪水上+1000

select name,salay+1000 from employee;

（5）统计每个学生的总分

create table grade (
id INT primary key AUTO_INCREMENT,
name varchar(20),
chinese INT,
math INT,
english INT
);

insert into grade values (null,'侯文泽',70,80,90),(null,'郑嘉颖',79,85,92),(null,'赖泽铵',90,90,40);

select name,chinese+math+english from grade;

（6）使用别名表示学生的总分

select name as 姓名 ,chinese+math+english as 总成绩 from grade;

select name 姓名 ,chinese+math+english 总成绩 from grade;

5.带有where的查询

（1）查询姓名为xxx的学生成绩

select * from grade where name = '赖泽铵';

(2)查询英语成绩大于90的同学

select * from grade where english>90;

(3)查询总分大于230分的所有同学

select * from grade where chinese+math+english>230;

(4)where 常用运算符

~：> < <= >= = <>#不等于

select * from grade where chinese<>80;

~：between ... and .... #在之间

select * from grade where math between 70 and 85;

~：in(set) #括号内为集合

select * from grade where english in (80,85,90);

~：like #像，通配符% ，‘_’ 表示一个字符

select * from grade where name like '赖%';

select * from grade where name like '文';

~：and or not #逻辑运算符

转载于:https://blog.51cto.com/13742773/2327974