MySQL CRUD操作

梁云亮 2023-03-28 原文


1.DDL（Data Definition Language）数据库定义语言statements are used to define the database structure or schema.

DDL是SQL语言的四大功能之一。
用于定义数据库的三级结构，包括外模式、概念模式、内模式及其相互之间的映像，定义数据的完整性、安全控制等约束
DDL不需要commit.
CREATE
ALTER
DROP
TRUNCATE
COMMENT
RENAME

2.DML（Data Manipulation Language）数据操纵语言statements are used for managing data within schema objects.

由DBMS提供，用于让用户或程序员使用，实现对数据库中数据的操作。
DML分成交互型DML和嵌入型DML两类。
依据语言的级别，DML又可分成过程性DML和非过程性DML两种。
需要commit.
SELECT
INSERT
UPDATE
DELETE
MERGE
CALL
EXPLAIN PLAN
LOCK TABLE

3.DCL（Data Control Language）数据库控制语言  授权，角色控制等
GRANT 授权
REVOKE 取消授权

4.TCL（Transaction Control Language）事务控制语言
SAVEPOINT 设置保存点
ROLLBACK  回滚
SET TRANSACTION

SQL主要分成四部分：
（1）数据定义。（SQL DDL）用于定义SQL模式、基本表、视图和索引的创建和撤消操作。
（2）数据操纵。（SQL DML）数据操纵分成数据查询和数据更新两类。数据更新又分成插入、删除、和修改三种操作。
（3）数据控制。包括对基本表和视图的授权，完整性规则的描述，事务控制等内容。
（4）嵌入式SQL的使用规定。涉及到SQL语句嵌入在宿主语言程序中使用的规则。

1. insert 数据插入

-- DML insert replace  数据插入
-- insert [into] 表名(字段列表...) value();
-- insert [into] 表名(字段列表...) values(),(),();
select * from teacher;
alter table teacher modify id int unsigned auto_increment;
alter table student drop constraint stfk;
alter table student add constraint stfk foreign key (tid) references teacher(id) on delete set null on update cascade ;
alter table student modify id int unsigned auto_increment;
-- id name
insert into teacher value(null,'赵丽');
insert teacher(name) value('李强');
insert teacher(name) select name from teacher;
replace teacher(name) values('jack'),('andy');
-- 如果有存在的主键，则此记录修改，没有就插入数据
replace into teacher values(668,'张丽丽');
insert teacher(name,id) value('aaaa',null);

select * from teacher;

2. update

select version(), database(), user();
show tables;
use wxdb;

-- sql crud update 数据更新修改  数据库 表 表结构 表数据记录 表字段
-- update 表名 set field1=value1,f2=v2...;  where 1   true 1=1
select *
from teacher;

update teacher
set name = '李强强'
where id = 115;
-- 给 teacher.name 增加唯一索引
alter table teacher
    modify name varchar(30) unique;

alter table teacher
    add score tinyint unsigned default 60;

update teacher
set name = 'lisi'
where id = 669;

update teacher
set score=score - 2
where name like '___';

update teacher
set id=110,
    name='赵二',
    score=88
where id = 120;

3. delete

-- 数据删除  drop database db;  drop table t1; drop table if exists t1,t2,t3;
-- 删除修改 需要控制好where
delete from teacher where id = 110;

begin;
delete from teacher where id = 669;

commit; --
rollback;

select * from teacher;

-- 删除所有，无条件   一条一条记录删除，删除效率，但可以加条件，会触发删除触发器
delete from teacher;
-- table = 结构 + 数据  效率高，不能有条件，自增主键，恢复默认
 truncate teacher;
 
 alter table teacher modify score tinyint unsigned default 80 after id;


alter table teacher modify score tinyint unsigned default 80 after name;

4. select

(1) 简单查询别名计算列 if() ifnull() concat()

-- crud select 查询语句
select * from teacher;
select id,name,score from teacher;
-- 列别名name 姓名  表别名 tt
select name 姓名,score as '成绩' from teacher tt;

alter table teacher add birthday date default '2000-12-20';
select year(curdate()),month(curdate()),dayofmonth(curdate());


select concat('姓名：','李四') 姓名,concat(18,'岁') 年龄;

select id,name,score+5,concat(year(now())-year(birthday),'岁') 年龄 from teacher;

alter table teacher add gender enum('男','女') default null after name;

select * from teacher;
-- ifnull()
select ifnull(null,'a'),ifnull('b','c');
-- if(a,b,c)
select if(true,'yes','no'),if(null,'yes','no'),if(2=3,'yes','no');
-- '' null 0 false
select if('',2,3);


select id 编号,concat(name,if(gender = '男','(先生)',if(gender='女','(女士)',''))) 姓名,ifnull(gender,'保密') 性别 from teacher;

（2）查询条件 where

-- select 查询条件
select * from teacher where  1=1 or 12=3
-- where id = 6     = >= < <= != <>
-- and or not

-- 模糊查询 like % 0个或任意个任意符号   _代表一个任何符号
select * from teacher where name like '%强%';
select * from teacher where name like '__强';
select * from teacher where name like '__%';

select * from teacher where name = '李%';

select * from teacher where name like '李%' or name like '张%';

-- not like
select * from teacher where name not like '李%'


-- 正则表达式条件  regexp
select 1=1,'ab'<>'c',1=3;

select * from teacher where name regexp '^a.*$';
select * from teacher where name regexp '^.*a.*$';
select * from teacher where name regexp 'a';

select * from teacher where name regexp '^[a-zA-Z]{2}$';

-- 查询只有两个汉字的。
select * from teacher where name regexp '^[\\u4e00-\\u9fa5]{2}$';
select * from teacher where name regexp '\\d{3}';

-- 检查正则表达式
select 'myjava' regexp '^.*java$';


-- select 查询条件
select * from teacher where  1=1 or 12=3
-- where id = 6     = >= < <= != <>
-- and or not

-- 模糊查询 like % 0个或任意个任意符号   _代表一个任何符号
select * from teacher where name like '%强%';
select * from teacher where name like '__强';
select * from teacher where name like '__%';

select * from teacher where name = '李%';

select * from teacher where name like '李%' or name like '张%';

-- not like
select * from teacher where name not like '李%'


-- 正则表达式条件  regexp
select 1=1,'ab'<>'c',1=3;

select * from teacher where name regexp '^a.*$';
select * from teacher where name regexp '^.*a.*$';
select * from teacher where name regexp 'a';

select * from teacher where name regexp '^[a-zA-Z]{2}$';

-- 查询只有两个汉字的。
select * from teacher where name regexp '^[\\u4e00-\\u9fa5]{2}$';
select * from teacher where name regexp '\\d{3}';

-- 检查正则表达式
select 'myjava' regexp '^.*java$';

-- in ()  not in()
select * from teacher where id in (112,114,116);

select * from teacher where id not in (112,114,116);



-- 效率低
select * from teacher where not (id in (112,114,116));

-- is null  is not null 判断空
select * from teacher where gender = null;
select * from teacher where gender is null;
select * from teacher where gender is not null;
-- 效率低
select * from teacher where not gender is null;

-- between and     not between and
select * from teacher where score between 70 and 80;
select * from teacher where score>=70 and score<=80;

-- sql and or not  && || !
select * from teacher where ! (score not between 70 and 80);
select * from teacher where score<70 || score >80

(3) 集合查询，count() sum() avg() max() min()

-- 集合查询 聚集函数
select count(0) 总人数, max(score) 最高分, min(score) 最低分, sum(score) 总和, round(avg(score), 1) 平均分
from teacher;


select round(1.5242424, 1), pi(), round(pi()), round(pi(), 2), ceil(1.1), floor(1.9);

select *
from teacher;
select count(0)
from teacher
where score < 60
  and gender = '男';

select avg(score)
from teacher;

select *
from teacher


-- 排号函数window function row_number() over()   asc 升序 desc 降序
select row_number() over (order by rand()) 行号, id ID, name 姓名
from teacher limit 3;

-- 不要掌握 查询不进行读取缓存，效率太低。
select row_number() over (order by rand()) 行号, id ID, name 姓名
from teacher limit 2;

-- 随机  rand()
select rand(),rand(),rand();
-- 排名次 rank() over()    dense_rank() over()
select id,name,score,concat('第',dense_rank() over(order by score desc),'名') 名次
from teacher order by score desc;

-- 子查询 集合函数不在条件上使用
select * from teacher where score = max(score);

 select * from teacher where gender = '男' and score = (select max(score) from teacher where gender = '男');
 
 show tables;
create table stu
(
    id     int unsigned auto_increment comment '学号',
    name   varchar(30) not null comment '姓名',
    gender enum ('男','女') default '女',
    score  tinyint unsigned comment '成绩',
    dept   varchar(100) comment '专业',
    primary key (id)
) engine = innodb
  character set = utf8
  auto_increment = 202101 comment '学生信息表';

show engines;

insert stu
values (null, '李四', '男', 80, '计算机科学'),
       (null, '赵勇', '男', 80, '计算机科学'),
       (null, '李丽', '女', 80, '会计');

select *
from stu;

-- 消除重复
select count(distinct dept)
from stu;

select distinct dept 专业
from stu;
select distinct *
from stu;

-- 常见函数
select left('mysql', 2), right('mysql', 1), mid('mysql', 2, 2);
--
select length('my中国'),char_length('my中国');

select concat(1,2,3),concat_ws('-',10,20,30,40,50,60),space(50),repeat('*',50);


select name from stu;

select GROUP_CONCAT(name order by name desc SEPARATOR '-') from stu;

select group_concat(id) from stu;

(4)限制结果行，分页

show databases;
use wxdb;
show tables;

select * from stu;

select id,name,gender from stu limit 2;
select * from stu limit 0,1;
insert into stu(name,gender,score,dept) select name,gender,score,dept from stu;
-- limit 在查询语句最后边
select * from stu  where name like '李%' limit 5;
select * from stu limit 5,5;

-- 数据分页,如何实现
-- 要求每页3条记录，显示第一页 1*3-3
select * from stu limit 0,3;

-- 显示第3页  3*3-3
select * from stu limit 6,3;


-- pagesize = 3   pageno = 1     pageno*pagesize-pagesize  limit pageno*pagesize-pagesize,pagesize
-- pagesize = 3   pageno = n     n*3-3,3

-- 每页显示5条，显示第三页
select * from stu limit 10,5;

-- 每页40条记录
select count(*) 总记录数,ceil(count(*)/40) 总页数 from stu;
-- 每页40条记录，显示第2页
select * from stu limit 40,40

(5)查询排序，分组查询分组条件分组排序

-- 排序 order by 
-- 分组 用于统计 group by  having 分组条件 分组排序 order by 如果最后限制行数  limit
-- select from 表名 where条件 


/* 排序 */
-- order by score [asc | desc] asc 升序  desc降序
select id,name,score from stu order by score desc;

select * from stu;
select id,name,score from stu order by score desc,name;

select id,name from stu order by name;

select * from stu where name like '赵%' order by score desc;

-- 分组 分组查询，分组统计
select dept,count(*),max(score),min(score),avg(score),sum(score) from stu group by dept;

select ifnull(gender,'未知') 性别,count(*) 人数 from stu group by gender;

select dept 专业,count(*) 人数
from stu
where gender = '男'
group by dept
having 人数>2
order by 人数 desc,dept asc
limit 2
;

作业：

查询老师的信息，要求显示id,name,score,leves from teacher; if()
90> 优秀
80> 良好
60> 及格
<60 补考
null旷考
统计学生表每个专业有多少人，男生多少人，女生多少人，优秀多少人，良好多少人。
select num level,count(*) 人数 from (select if(score>90,'优秀(>90)',if(score>80,'良好(>80)',if(score>60,'及格','补考'))) num from stu) ss group by num order by 人数 desc;

有关MySQL CRUD操作的更多相关文章

ruby - 如何使用 Selenium Webdriver 根据 div 的内容执行操作？ - 2
我有一个使用SeleniumWebdriver和Nokogiri的Ruby应用程序。我想选择一个类，然后对于那个类对应的每个div，我想根据div的内容执行一个Action。例如，我正在解析以下页面:https://www.google.com/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=puppies这是一个搜索结果页面，我正在寻找描述中包含“Adoption”一词的第一个结果。因此机器人应该寻找带有className:"result"的div，对于每个检查它的.descriptiondiv是否包含单词“adoption
ruby-on-rails - 如何处理 Grape 中特定操作的过滤器之前？ - 2
我正在我的Rails项目中安装Grape以构建RESTfulAPI。现在一些端点的操作需要身份验证，而另一些则不需要身份验证。例如，我有users端点，看起来像这样:moduleBackendmoduleV1classUsers现在如您所见，除了password/forget之外的所有操作都需要用户登录/验证。创建一个新的端点也没有意义，比如passwords并且只是删除password/forget从逻辑上讲，这个端点应该与用户资源。问题是Grapebefore过滤器没有像except,only这样的选项，我可以在其中说对某些操作应用过滤器。您通常如何干净利落地处理这种情况？
ruby-on-rails - 在 Ruby on Rails 中发送响应之前如何等待多个异步操作完成？ - 2
在我做的一些网络开发中，我有多个操作开始，比如对外部API的GET请求，我希望它们同时开始，因为一个不依赖另一个的结果。我希望事情能够在后台运行。我找到了concurrent-rubylibrary这似乎运作良好。通过将其混合到您创建的类中，该类的方法具有在后台线程上运行的异步版本。这导致我编写如下代码，其中FirstAsyncWorker和SecondAsyncWorker是我编写的类，我在其中混合了Concurrent::Async模块，并编写了一个名为“work”的方法来发送HTTP请求:defindexop1_result=FirstAsyncWorker.new.async.
ruby - 在 Ruby 中是否有一种惯用的方法来操作 2 个数组？ - 2
a=[3,4,7,8,3]b=[5,3,6,8,3]假设数组长度相同，是否有办法使用each或其他一些惯用方法从两个数组的每个元素中获取结果？不使用计数器？例如获取每个元素的乘积:[15,12,42,64,9](0..a.count-1).eachdo|i|太丑了...ruby1.9.3 最佳答案使用Array.zip怎么样？:>>a=[3,4,7,8,3]=>[3,4,7,8,3]>>b=[5,3,6,8,3]=>[5,3,6,8,3]>>c=[]=>[]>>a.zip(b)do|i,j|c[[3,5],[4,3],[7,6],
ruby-on-rails - 如何让 Rails View 返回其关联的操作名称？ - 2
我有一个非常简单的Controller来管理我的Rails应用程序中的静态页面:classPagesController我怎样才能让View模板返回它自己的名字，这样我就可以做这样的事情:#pricing.html.erb#-->"Pricing"感谢您的帮助。最佳答案 4.3RoutingParametersTheparamshashwillalwayscontainthe:controllerand:actionkeys,butyoushouldusethemethodscontroller_nameandaction_nam
Postman测试简单操作 - 2
1、接口请求基本操作1.1例子tips在view的选项可以zoomin调整窗口字帖大小。1、创建一个测试的workspace，并命名为test2、test后面新增一个addrequest3、选择发送GET，URL为一个开源的https://api.apiopen.top/api/sentences获取每日一句4、点击send查看内容Tips:如果提示出现Error:tunnelingsocketcouldnotbeestablished,statusCode=407错误，参照以下解决办法)关于tunnelingsocketcouldnotbeestablished,cause=getaddri
【Linux操作系统】——网络配置与SSH远程 - 2
Linux操作系统——网络配置与SSH远程安装完VMware与系统后，需要进行网络配置。第一个目标为进行SSH连接，可以从本机到VMware进行文件传送，首先需要进行网络配置。1.下载远程软件首先需要先下载安装一款远程软件：FinalShell或者xhell7FinalShellxhell7FinalShell下载：Windows下载http://www.hostbuf.com/downloads/finalshell_install.exemacOS下载http://www.hostbuf.com/downloads/finalshell_install.pkg2.配置CentOS网络安装好
ruby - Ruby 语言可以用来构建操作系统吗？ - 2
Ruby语言是否可以用于创建全新的移动操作系统或桌面操作系统，即是否可以用于系统编程？最佳答案嗯，现在有一些操作系统使用比C更高级的语言。基本上，ruby解释器本身需要用一些低级的东西来编写，并且需要一些引导加载代码将功能齐全的ruby解释器作为独立内核加载到内存中。一旦ruby解释器被引导并以内核模式(或innerrings之一)运行，就没有什么可以阻止您在其上构建整个操作系统。不幸的是，它可能会很慢。每个操作系统功能的垃圾收集可能会相当引人注目。ruby解释器将负责任务调度和网络堆栈等基本事情，使用垃圾收集框架会大大
ruby-on-rails - JSON 对象操作 - 2
假设我们有以下描述一个人的JSON对象:{"firstName":"John","lastName":"Smith","age":25,"address":{"streetAddress":"212ndStreet","city":"NewYork","state":"NY","postalCode":"10021"},"phoneNumber":[{"type":"home","number":"212555-1234"},{"type":"fax","number":"646555-4567"}]有人可以建议在Rails3中操作前一个对象的最优雅和最有效的方法吗？我希望能够:添加另
ruby - 用于简单数组操作的慢速 Ruby - 2
我按照Cormen的“算法导论”中的伪代码，在Ruby中创建了简单的插入排序实现:defsort_insert(array)(1...array.length).eachdo|item_index|key=array[item_index]i=item_index-1whilei>=0&&array[i]>keydoarray[i+1]=array[i]i-=1endarray[i+1]=keyendarrayend它有效，但执行速度非常慢。对于约20k个元素的数组array=((0..10_000).to_a*2).shuffle，排序大约需要20秒。我只测量这个方法调用的时间，没有

MySQL CRUD操作

1. insert 数据插入

2. update

3. delete

4. select

作业：

有关MySQL CRUD操作的更多相关文章

随机推荐