Mysql基本查询、视图、索引、触发器、序列
基本查询
修改
String sql="update smbms_user set userCode='"+code+"' where id='"+user_id+"' ";
删除用户
String sql="delete from smbms_user where id=?";
登录
select * from user where userName=#{userName} and userPassword=#{userPassword}
模糊查询
select * from smbms_bill WHERE productName like "%米%"
处理年龄
SELECT floor(DATEDIFF(NOW(),birthday)/365) as age, u.*,r.roleName as userRoleName from smbms_user as u,smbms_role as r WHERE u.userRole = r.id
查news表格,要求按主题生活(topic表格,有生活,娱乐等)分类,按创建时间降序查询,并取前5条
SELECT * from news WHERE ntid=1 ORDER BY ncreateDate ASC LIMIT 0,6;
查全部新闻按降序排序,并取前5条
SELECT * from easybuy_news ORDER BY createTime DESC LIMIT 0,5;
通过用户的id来查地址
select * from easybuy_user_address where userId=#{id}
插入
INSERT INTO ebook_category(id,name) VALUES(2,"oracle");
INSERT INTO ebook_entry(id,categoryId,title,summary,uploaduser,createdate) VALUES(2,1,"美食","美食摘要","tome",NOW());
创表
create table product(
pid int(4) primary key auto_increment,
name VARCHAR(20),
catalog_name varchar(20),
price int(10),
picture varchar(20) );
聚合函数:取平均值 AVG() :求sal平均值
select avg(sal) as avg_sal from emp;
--求和 SUM() 对sal求和
select sum(sal) as sum_sal from emp;
--求最大值 MAX()、最小值MIN() 对sal进行操作
select max(sal) as max_sal,min(sal) as min_sal from emp;
--求行数 count() 求emp表中的行数
select count(*) as avg_sal from emp;
注:聚合函数中除了count()函数外,其余的跳过空值去处理非空的值;
给已创建的表增加字段
alter table personalInfo add column numberId varchar(20)
//给已有字段设主键
alter table personalInfo add primary key(numberId)
//删除表中某个字段
alter table personalInfo drop column age
//给表中某个字段设置默认值
alter table `user` alter is_delete set default 0;
修改字段中某部分内容
update tod_arrange set date = replace(date, '2021','2020')
给表中某个字段加1
update nian_xian set work_nianxian=cast(work_nianxian as int)+1
登录mysql后创建新用户,@后面的表示可以在哪里登录,%可以在任意地方登录,用不同的用户登录数据库,表不一样
create user 'srabc'@'locallost' identified by 'usrabc';
聚合函数字符串转数字
select sum(cast(vacation_day as floot)) vacation from tod_dutyoff where user_name="aaa"
查询每个部门的转正人数,对于转正人数小于2个人的不显示,having与group by连用
select (select d.dept_name from department d where d.dept_no=e.dept_no) as 部门,sum(e.work) as 转正人数 from emp e where e.work='1' group by e.dept_no HAVING sum(e.work)>=2
查询每个部门的人数,HAVING count(*)>=3理解为对每种分组结果的条件筛选,having后面多条件情况如下
SELECT dept_no as 部门名称,count(*) as 人数 from emp group by dept_no HAVING count(*)>=3 and SUM(work)>1
to_char 和 to_date转换
Oracle
select to_char(sysdate,'yyyy-mm-dd') today from dual;
select * from emp where dates between to_date('2007-06-12 10:00:00', 'yyyy-mm-dd hh24:mi:ss') and to_date('2007-06-12 10:00:00', 'yyyy-mm-dd hh24:mi:ss')
--假设hiredate字段储存的格式为2020-06-03 ,现在只取年份,如下
select to_char(to_date('hiredate','yyyy'),'yyyy') from emp
-- 查询创建时间大于2020-06-01日的申请信息
select * from apply where create_date>to_date('2020-06-01','yyyy-mm-dd')
-- 查雇佣时间大于2020-0702号员工信息
SELECT * FROM EMP WHERE to_date(hiredate,'yyyy-mm-dd') >to_date('2020-07-02','yyyy-mm')
--表中字段为date类型模糊查询
select * from YYCGD2020 where to_char(cjtime,'yyyy-MM-dd') like '%2020-09-02%'
Mysql
select date_format(procedure_startTime,'%Y-%m-%d') from blood_analyse_lbjk
select str_to_date(procedure_startTime,'%Y-%m-%d') from blood_analyse_lbjk
order by字段不生效,在后面+0即可
SELECT * from blood_analyse_lbjk ORDER BY end_weigth+0 asc
MySQL向数据库表的某字段追加数据
使用CONCAT()函数
mysql向表中某字段后追加一段字符串(field为字段名):
update table_name set field=CONCAT(field,'str',)
mysql 向表中某字段前加字符串
update table_name set field=CONCAT('str',field)
SELECT *, CONCAT(date_format(process_start,'%Y-%m-%d')," ",operator_id)AS 产品信息 FROM blood_analyse;
追加数据时取时间列支取年月日,并追加到其他列
UPDATE blood_analyse set eye_end_person=CONCAT(date_format(process_start,'%Y-%m-%d')," ",eye_end_person)
MySQL添加索引
1.添加PRIMARY KEY(主键索引)
alter table `table_name` add primary key(`column`);
2.添加UNIQUE(唯一索引)
alter table `table_name` add unique(`column`);
3.添加普通索引
alter table `table_name` add index index_name(`column`);
4.添加全文索引
alter table `table_name` add fulltext(`column`);
5.添加多列索引
alter table `table_name` add index index_name(`column1`,`column2`,`column3`);
删除关联表数据操作
第一种增加增减层叠关系, ON DELETE CASCADE在删除主表的同时,拿相关联的从表的数据都干掉
第二种层叠关系,ON DELETE SET NULL在删除主表的同时,把相关联的数据都设置null
开发常用
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX; -- 当前运行的所有事务
SELECT * FROM INFORMATION_SCHEMA.innodb_locks; -- 当前出现的锁
SELECT * FROM INFORMATION_SCHEMA.innodb_lock_waits; -- 锁等待的对应关系
show processlist --查看进程列表
kill 进程号
查看触发器: show triggers
查看数据库版本号 select version()
查看锁 select processList
sql语句-视图
#视图。就是一个虚拟表,便于我们对数据进行处理。对视图的DML操作会改变基表数据,但不能改变基表结构,如加字段
为什么要使用视图:
<1>安全原因:限制数据的访问(如:社会保险基金表,可以使用视图只显示姓名和地址,而不显示社会的保险号和工资)
<2>减少复杂的slq语句查询,一般是用查询结果集返回作为视图
例:
create view empnew_view as select eid,ename,salary from emp //创建视图
select * from empnew_view; //查询视图
insert into empnew_view(eid,ename,salary) values(99,'qi1qi',66) //DML操作
update emp set ename="qiqi1" where eid=88 //DML操作
delete from emp where eid=88 //DML操作
DESC v;
#索引,方便我们的快速查找,方便我们快速查找,对SQL非常有帮助,并不是所有字段都用索引,
//前提,这个字段经常被当做查询对象操作,是一个表里面不是索引越多越好,根据你的具体,项目来设置,一张表里面与其他表有关系重合的,不适合加索引
#查看所有所有 index
SHOW INDEX FROM result;
TABLE 表 第二个字段0不可以重复,1代表可以重复
第三个参数:索引名字 第四个是索引下标,从1开始 第五个:字段名 第六个:排序规则, 默认是a
sql语句-触发器
//创建一个空部门表
CREATE TABLE emp(
eid INT(4),
ename VARCHAR(20),
epwd VARCHAR(20),
edate DATE
);
ALTER TABLE emp ADD salary DOUBLE(5,2);
INSERT INTO emp(eid,ename,epwd) VALUES (1,"aa","123");
#创建绩效表
CREATE TABLE jixiao(
jname VARCHAR(20),
jmoney DOUBLE(5,2)
);
#创建触发器:就相当于你的闹钟,在插入数据之前执行,当emp表插入数据的时候,另一个jixiao表也同时插入数据
CREATE TRIGGER t_money BEFORE INSERT ON emp FOR EACH ROW
INSERT INTO jixiao VALUES(new.ename,new.salary*0.5);
#创建触发器2:
CREATE TRIGGER t_mo BEFORE INSERT ON emp FOR EACH ROW
UPDATE emp SET money=new.money;
INSERT INTO emp(eid,ename,salary) VALUES(11,"ee",20);
UPDATE jixiao SET money=1;
SELECT * FROM jixiao;
#删除触发器
DROP TRIGGER t_money;
#展示触发器
SHOW TRIGGERS;
sql语句---索引
//创建一个主键索引(唯一,不能为空,不一定是INT)
ALTER TABLE emp ADD PRIMARY KEY(eid);
SHOW INDEX FROM emp;
//唯一索引(主键索引在一张表里只能有一个,唯一索引可以有多个);
#创建唯一索引,#在添加唯一索引时,乱码添加不成功
ALTER TABLE emp ADD UNIQUE(ename);
#删除索引
ALTER TABLE emp DROP INDEX ename;
#全局索引作用:便于我们快速查找到某一行
ALTER TABLE emp ADD INDEX(epwd);
序 列
由于mysql和oracle不太一样,不支持直接的sequence,所以需要创建一张table来模拟sequence的功能
第一步:创建 sequence 管理表
DROP TABLE IF EXISTS sequence; CREATE TABLE sequence ( name VARCHAR(50) NOT NULL, current_value INT NOT NULL, increment INT NOT NULL DEFAULT 1, PRIMARY KEY (name) ) ENGINE=InnoDB;
第二步:创建取当前值的函数
DROP FUNCTION IF EXISTS currval; DELIMITER $ CREATE FUNCTION currval (seq_name VARCHAR(50)) RETURNS INTEGER LANGUAGE SQL DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN DECLARE value INTEGER; SET value = 0; SELECT current_value INTO value FROM sequence WHERE name = seq_name; RETURN value; END $ DELIMITER ;
第三步:创建取下一个值的函数
DROP FUNCTION IF EXISTS nextval; DELIMITER $ CREATE FUNCTION nextval (seq_name VARCHAR(50)) RETURNS INTEGER LANGUAGE SQL DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN UPDATE sequence SET current_value = current_value + increment WHERE name = seq_name; RETURN currval(seq_name); END $ DELIMITER ;
第四步:创建更新当前值的函数
DROP FUNCTION IF EXISTS setval; DELIMITER $ CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER) RETURNS INTEGER LANGUAGE SQL DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN UPDATE sequence SET current_value = value WHERE name = seq_name; RETURN currval(seq_name); END $ DELIMITER ;
第五步:测试函数功能
当上述四步完成后,可以用以下数据设置需要创建的sequence名称以及设置初始值和获取当前值和下一个值。
-
添加一个sequence名称和初始值,以及自增幅度:INSERT INTO sequence VALUES ('seq1', 0, 1);
-
设置指定sequence的初始值:SELECT SETVAL('seq1', 10);
-
查询指定sequence某一记录的当前值:SELECT CURRVAL('seq1') as number;
-
查询指定sequence的下一个值:SELECT NEXTVAL('seq1') as nextNumber;