插入一条记录
insert into user(ITCode, UserName, Password, Salt, province) values('name', 8000000, 31, 1, '河北')
插入多条记录
INSERT INTO record (name) VALUES ('a'),('b'),('c');
insert into pj_tester(ProjectID,InProject,Tester,JoinDate,LeaveDate) values('1','1','1','1','1'),('1','1','1','1','1')
删除多条记录
DELETE FROM record WHERE name IN ('a', 'b');
DELETE FROM record WHERE name = 'a' or name = 'b';
更新一条记录
update user set ITCode = \"{uf.ITCode}\", UserName = \"{uf.UserName}\", EmailAddress= \"{uf.EmailAddress}\",Gender = \"{uf.Gender}\"," +
//$"Location = \"{uf.Location}\",Department = \"{uf.Department}\",Labour = \"{uf.Labour}\",Role = \"{uf.Role}\" where ITCode=\"{itCode}\";
获取搜索个数
select count(*) from user where UserName like '%a%'
时间段查询,一定要加DATE_FORMAT
select * from project where Brand like '%%' and ProjectMode like '%%' and Status like '%%' and TestSite like '%%' and DATE_FORMAT(StartDate,'%Y-%m-%d') >= '2020-06-01' and DATE_FORMAT(EndDate,'%Y-%m-%d') <= '2020-06-19' order by Status Asc LIMIT 0,10
批量更新一条记录中的单个字段
UPDATE user
SET name = CASE id
WHEN 1 THEN 'update_name1'
WHEN 2 THEN 'update_name2'
END
WHERE id IN (1, 2);
# 批量更新一条记录中的多个字段
UPDATE user
SET name = CASE id
WHEN 3 THEN 'update_name3'
WHEN 4 THEN 'update_name4'
END,
pwd = CASE id
WHEN 5 THEN 'update_pwd5'
END
WHERE id IN (3, 4, 5);
update machine set Status='Waiting', Owner='wuxx10' Where MachineID IN('Knowckout2-1','MOFUSHI_M710TS_B250_KT1-1','Pippen1-1')
添加字段:
默认在最后添加:`alter table user add age int(3);`
在指定字段后添加:`alter table user add email varchar(60) after password;`
在开头添加字段:`alter table user add id int(11) first;
删除字段:`alter table user drop age;
如何修改密码(V8.0.18)
控制台输入指令:
mysql -uroot -p
输入旧密码
Enter password: *********
输入语句NewPassword处为你要修改的密码
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';
Query OK, 0 rows affected (0.02 sec)
联合查询
这种场景下得到的是A的所有数据,和满足某一条件的B的数据;B中补存在数据则自动填充null
select a.*, b.* from user a left join pj_tester b on a.ITCode = b.Tester
一些需要注意的坑:
Window下查询语句不区分大小写,需要加binary来区分大小写
string strQueryCmd = $"select * from {strTable} where ITCode=\"{itCode}\""; //不区分大小写
string strQueryCmd = $"select * from {strTable} where binary ITCode=\"{itCode}\"";//区分大小写