實習已然兩個半月了,并沒有進入正軌,而明星程式員蕭井陌建議我辭職,确實在理。
是以在考慮中。這段時間,用到的sql語句,也是重新讓我覺得SQL也是一門大學問,并不是隻有簡單的查找删。下面,是一些覺得有必要記住的SQL語句。
drop procedure if exists insertt;#如果存在儲存過程則删除
delimiter $$ #建立一個儲存過程
create procedure insertt()
begin
set @a=12; #學号
set @b=1003; #班級編号
set @c=1014;
set @f="";
set @j=0;
set @k=2;
set @l=1; #手機号
while @a<256 do #如果@a<2000010001則傳回true,繼續執行
insert into tbl_resource_info values(@a,@b,@c,@d,@e,@f,@g,@h,@i,@j,@k,@l,@m,@n);
set @[email protected]+1;
set @[email protected]+1;
end while;
end $$
delimiter ;
#執行存儲過程插入資料
call insertt();
批量更新某一列
UPDATE tbl_resource_info
SET GROUPID = 1004
WHERE ID > 60 AND ID < 250
複制某一列到另一列
UPDATE tbl_resource_info
SET Content = NAME
WHERE ID > 62 AND ID < 214
sql語句優化:
一:SELECT studentId,studentName,studentCODE FROM tbl_student_info WHERE studentId IN (SELECT studentId FROM tbl_parents_student_map WHERE USERID = ?);
優化為:SELECT s.STUDENTID,s.STUDENTNAME,s.STUDENTCODE FROM tbl_student_info s,tbl_parents_student_map t WHERE s.STUDENTID=t.STUDENTID AND t.USERID='?';
二:SELECT studentId,studentName,studentCODE FROM tbl_student_info WHERE studentId IN (SELECT studentId FROM tbl_parents_student_map
WHERE USERID = (SELECT USERID FROM tbl_user_extend_info WHERE ECSHOPUSERID = '?'));
優化為:SELECT s.STUDENTID,s.STUDENTNAME,s.STUDENTCODE FROM tbl_student_info s,tbl_parents_student_map t,tbl_user_extend_info u
WHERE s.STUDENTID=t.STUDENTID AND t.USERID=u.USERID AND u.ECSHOPUSERID='?';
debug
觀察感覺出問題的變量與需要的變量之間的不同
這個語句是先查詢某個屬性,然後作為字段查詢。這句話,受益匪淺。
SELECT s.id,(select c.classname from tbl_class_info c where c.classid=s.classid) as classname,s.studentname,s.studentcode,s.sex from tbl_student_info s
where s.nurid= '11'
and (select c.classname from tbl_class_info c where c.classid=s.classid) like "%這個那個%";
連接配接字元串,表示在值的前後連接配接 ’%‘,
select * from t_sys_demo_tab1 where 1=1 and dm_str like concat('%',concat(#{demoTab1Query.dm_str,jdbcType= VARCHAR},'%'))
插入一條記錄,在mybatis,mysql
insert into t_sys_demo_tab1 (dm_uid,dm_str,dm_num,dm_int,dm_date,dm_time)
values (
#{dm_uid,jdbcType= VARCHAR},
#{dm_str,jdbcType= VARCHAR},
#{dm_num,jdbcType= NUMERIC},
#{dm_int,jdbcType= INTEGER},
#{dm_date,jdbcType= DATE},
#{dm_time,jdbcType= TIMESTAMP})
select @@identity 檢視這條記錄的主鍵編号
插入一條記錄,在mybatis,oracle
<selectKey resultType="Integer" keyProperty="auto_id" order="BEFORE">
select seq_sys_demo_tab1.nextVal from dual//擷取即将插入的記錄的主鍵id
</selectKey>
//插入記錄
insert into t_sys_demo_tab1 (auto_id,dm_uid,dm_str,dm_num,dm_int,dm_date,dm_time)
values (
#{auto_id,jdbcType= NUMERIC},
#{dm_uid,jdbcType= VARCHAR},
#{dm_str,jdbcType= VARCHAR},
#{dm_num,jdbcType= NUMERIC},
#{dm_int,jdbcType= INTEGER},
#{dm_date,jdbcType= DATE},
#{dm_time,jdbcType= TIMESTAMP})
</insert>
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIyVGduV2QvwVe0lmdhJ3ZvwFM38CXlZHbvN3cpR2Lc1TPB10QGtWUCpEMJ9CXsxWam9CXwADNvwVZ6l2c052bm9CXUJDT1wkNhVzLcRnbvZ2LcZXUYpVd1kmYr50MZV3YyI2cKJDT29GRjBjUIF2LcRHelR3LcJzLctmch1mclRXY39TO4EDO0YDN0ETOxQDM2EDMy8CX0Vmbu4GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.jpg)
根據上表,提取出來部分的需要的字段。<div>SELECT NAME JTGJSHOW,VALUE JTGJ FROM G_CODE WHERE PID=191428193265000503 AND VALUE IN (2,3) order by JTGJ
</div>
又更新在這裡了,好長一段時間沒有更新部落格,今天用到了想到了聯表删除/查詢。這裡記錄一下具體的目的和sql語句,給予oracle。具體内容如下:
這兩個表,我知道第二個表的login_name,那麼怎麼删除和查找第一個表對應的記錄呢。
具體方法如下:
連結查詢,當然可以删除 select * from t_sys_user_info a join t_sys_sp_users b on A.USER_ID = b.user_id where B.LOGIN_NAME like '%lin%';
内連結:select * from t_sys_user_info where user_id = (select user_id from t_sys_sp_users where login_name like 'lindi');"="号可以用“in”。