/*
2017.8.7(yi)~2017.8.15(er)
--注释
COMMENT ON COLUMN public.kq_users.user_display_name IS '显示名称';
--权限
GRANT ALL ON TABLE public.kq_users TO xfacedata;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE public.kq_users TO r_xfacedata_qry;
GRANT SELECT ON TABLE public.kq_users TO xfacektl;
--建表
drop table users;
CREATE TABLE users (
user_id character varying(32) NOT NULL,
user_name character varying(200) NOT NULL,
user_display_name character varying(200) NOT NULL,
password character varying(200) NOT NULL,
salt_value character varying(50) NOT NULL,
PRIMARY KEY (user_id)
)
-- 更新字段值
update kq_users set user_display_name = '120' where user_id = '120';
*/
--CRUD 表中字段
alter table kq_users add COLUMN user_display_name varchar(200);
-- false
-- alter table kq_users add COLUMN userDisplayName varying(200);
alter table kq_users rename column userDisplayName to user_display_name;
alter table kq_users add COLUMN salt_value varchar(100);
alter table kq_users rename column id to user_id;
alter table kq_users rename column user_id to id;
alter table kq_users add COLUMN id varchar(32);
-- 删除字段
alter table Test drop COLUMN BazaarType;
alter table kq_users drop COLUMN id;
alter table kq_users drop COLUMN salt_value;
alter table kq_users drop COLUMN user_display_name;
/*
MD5 加密，盐值；
--salt_value 1440345394530213
select md5('admin'||'1440345394530213');
"151ea22fe214a4b37783a3c2fcf393e7"
select md5('sys'||'1440345394530213');
"9ba01cc258bc33d0f50177553d905f73"
insert into kq_users(user_id, user_name, user_display_name, user_pwd, salt_value) values('11','admin1','管理员1','151ea22fe214a4b37783a3c2fcf393e7','1440345394530213');
insert into kq_users(user_id, user_name, user_display_name, user_pwd, salt_value) values('22','sys1','系统管理员1','9ba01cc258bc33d0f50177553d905f73','1440345394530213');
delete from kq_users where user_id='11';
delete from kq_users where user_id='22';
--ok
select t.user_id userId, t.user_name userName,t.user_pwd userPwd, t.user_display_name userDisplayName,t.salt_value salt from kq_users as t where
t.user_pwd=md5(('admin'||'1440345394530213')) and
t.user_name='admin1';
--ok
select t.user_id userId, t.user_name userName,t.user_pwd userPwd, t.user_display_name userDisplayName,t.salt_value salt from kq_users as t where
t.user_pwd=md5(('sys'||'1440345394530213')) and
t.user_name='sys1';
*/
--加注释
COMMENT ON COLUMN public.kq_users.salt_value IS 'md5加密盐值';
limit 1 offset 0;
select * from kq_users where user
select * from kq_users;
