mysqlcrud mysqlcrud是什么意思啊

一、介绍

CRUD即增加(Create)、查询(Retrieve)、更新(Update)、删除(Delete)四个单词的首字母缩写。

In computing, CRUD is an acronym for create, Retrieve, update, and delete. It is used to refer to the basic functions of a database or persistence layer in a software system.

C reate new records

R etrieve existing records

U pdate existing records

D elete existing records.

平常我们说的系统开发中的增改查删可以用CURD来表示增加：Create 修改：Update查找：Read删除：Delete外国人喜欢CRUD，表示增查改删：增加：Create读取查询：Retrieve更新：Update删除：Delete

二、insert和repleace 

repleace 与insert基本上一样

不一样的是：insert 如果指定主键，唯一约束存在，则报错，replace则修改

 

insert into stu value(null,'李四',22,'女','郑州');

 

insert stu(name,address) values('李平','北京'),('刘阳','上海'),('赵强','广州');

 

replace into stu values(null,'王五',25,'女','河南省郑州市');

 

 

 三、delet删除

drop database 数据库
drop table 表白
alter table stu drop column 行
删除表中的数据记录用：delete from 表名 where 条件
truncate table  stu//截断数据 删除数据
-- 删除数据 delete 和 truncate 区别
/*
delete 一条记录一条记录删除，删除所有的时候，效率低，但它灵活 可以加上条件
会触发删除触发器
truncate 直接截断数据，auto_increment 恢复默认 , 不触发删除触发器，
也没有影响行，删除所有的时候，效率高，只能删除截断数据
*/

 

四、update修改update zcx set sage=18;

-- 修改数据
select * from stu;
-- update 表名 set 列名=值,列名2=值2 ... where 条件表达式 ;

update stu set age = 20,gender = '男' where name = '王五';

update stu set age = 22,gender = '男' where id = 4;

-- 修改郑州地址的 人员年龄增加一岁
update stu set age = age + 1 where address like '%郑州%';

like '%李'李结尾的
like '李%'李开头的
like '%李%'包含李的

 

 

五、select查询

查询一个段里所有属性长度
select *from length(trim(addr)) from stu;
select *from student;
alter table student add ssort int unsigned default 1;
--基本查询
select *from student;
select sno,sname,ssort fromstudent order by ssort asc;
asc//降序
desc//升序
limit 限制结果
--查询限制结果行
select sno,sname from student limit 3000;select sno,sname from student limit 0,3;--查询别名
select sno,sname from student stu;select sno,sname from student as stu;select sno as 年龄,sname 姓名 from student as stu;
--0-10select sno as 年龄,sname 姓名,round(rand()*10)from student as stu;select sno,sname from student order by rand() limt 2;//效率低
select sno,sname from student where sno in (201201,212333);--where 条件 基本 => < >= <= != <>
--and  or not
select * from student where snam='李勇';

select * from student where sid!=55;

select * from student where sid<>55;

--null 条件  is null    not is null     is not null

select *from student where stid is null;

select *from student where stid is not null;--in 条件 not in查询某个范围
select *from student where sid in(1,23,45,6);在这个范围

select *from student where sid  not in(1,23,45,6);不在这个范围-- between and  (not between and)范围 数字 日期
select * from student wherer sscore between 80 and 90;

select * from student wherer sscore>=80 between  and sscore<= 90;

select *from student where not sscore between 80 and 90;

select *from student where sscore<80 or sscore>90;

select *from sthudent where sscore<80 or ssore>90;
select now(),sysdate(),current_timestamp();select dagt(now);

-- dateadd()

select date_add(now(),interval + day(now())+1 day );

--21 天前日期

select addate(now(),-21);

--字符串转换为日期时间

select concat(date_add(curdate(),interval -day(now())+1 ady),'00:00:00')<now();--union  union all
union联合两个表，union all联合两个表所有
--子查询
--模糊查询
select  *fromstudent where sname like '李%'；

select  *fromstudent where sname like '%李%'；

select  *fromstudent where sname like '李_'；

select  *fromstudent where sname like '__'；

select  *fromstudent where sname like '李%'or sname like'张%'；

select  *fromstudent where sname like '%李%%' escape '$'；//查询姓名有%的-- 正则表达式查询
select 'abcd' from regexp '[a-z]{4}';//正则，是否包含abcd

select  '132201032023' regexp '^1[3,5,8]\\d{9}$';//检查手机号

select instr('abc123','z');

select *from student where instr(sname inster(nsme,''%)!=0

select *from student where sname regexp '\\d';//查询名字包含数字

select * from student where sname regexp '[a-z]';//查询全英小写 子查询
CREATE TABLE s1(
id INT UNSIGNED AUTO_INCREMENT,
NAME VARCHAR(30),
gender ENUM('男','女'),
brith DATE,
score TINYINT UNSIGNED,
dept VARCHAR(100),
PRIMARY KEY(id)
);
SELECT *FROM s1;
INSERT INTO s1 SET NAME ='李四',gender='男',brith='2004-6-25',score=80,dept='计算机科学';
INSERT INTO s1 SET NAME ='张三',gender='女',brith='2014-7-15',score=80,dept='Java基础';
INSERT INTO s1 SET NAME ='王五',gender='男',brith='2004-4-05',score=80,dept='计算机科学';
INSERT INTO s1 SET NAME ='赵六',gender='男',brith='1999-6-25',score=99,dept='Hadoop';
INSERT INTO s1 SET NAME ='七九',gender='女',brith='1998-6-25',score=100,dept='Hadoop';
INSERT INTO s1 SET NAME ='琪琪',gender='女',brith='200-6-25',score=99,dept='php';

SELECT DISTINCT dept FROM s1;
-- 统计有多少人
SELECT dept 专业,COUNT(*) 人数,MAX(score) 最高分,MIN(score)最低分,ROUND(AVG(score),2) 平均分,SUM(score)总和 FROM s1
WHERE score>90
GROUP BY dept HAVING COUNT(*)>1 ORDER BY COUNT(*) LIMIT 2;
SELECT RAND(),ROUND(RAND(),2);
-- 查询显示各专业的人数，最高分，最低分
SELECT dept ,COUNT(*) 人数,MAX(score) 最高分,MIN(score)最低分 FROM s1 GROUP BY dept;
SELECT gender,COUNT(*) 人数 FROM s1 GROUP BY gender HAVING COUNT(*)>2;
-- 子查询
SELECT *FROM s1 WHERE score IN (SELECT MAX(score) FROM s1);
-- 学生成绩划分等级>=90 优秀有  >=80 良好 >=60 及格 补考
SELECT ss.level 等级,COUNT(*) 人数 FROM(SELECT id ,NAME,score ,IF(score>=90,'优秀',IF(score>=80,'良好',IF(score>=60,'及格','补考'))) LEVEL FROM s1) ss GROUP BY ss.level

-- 根据月份将学生成绩求和
SELECT SUM(score) 分数,MONTH(brith) 月份 FROM s1 GROUP BY brith;select *from score 
-- 分页 每页3条记录，显示第二页
select id,name from stu limit 0,3; -- 第一页
select id,name from stu limit 3,3; -- 第二页select id ,name from stu limit 9,3;
-- 第n页 limit n*3-3,3
当前页*每页条数-每页条数，每页条数
show colums from student;
desc studnt; 

insert 15:12:38