网站设计制作花多少钱,seo排名优化方式方法,平面设计公司企业文化,服务外包下的网站开发postgresql中基础sql查询 创建表插入数据创建索引删除表postgresql命令速查简单查询计算查询结果 利用查询条件过滤数据模糊查询 创建表
-- 部门信息表
CREATE TABLE departments( department_id INTEGER NOT NULL -- 部门编号#xff0c;主键, department_name CHARACTE… postgresql中基础sql查询 创建表插入数据创建索引删除表postgresql命令速查简单查询计算查询结果 利用查询条件过滤数据模糊查询 创建表
-- 部门信息表
CREATE TABLE departments( department_id INTEGER NOT NULL -- 部门编号主键, department_name CHARACTER VARYING(30) NOT NULL -- 部门名称, manager_id INTEGER -- 部门经理的员工编号, location_id INTEGER -- 部门所在位置编号, CONSTRAINT dept_id_pk PRIMARY KEY (department_id)) ;-- 职位信息表
CREATE TABLE jobs( job_id CHARACTER VARYING(10) NOT NULL -- 职位编号主键, job_title CHARACTER VARYING(35) NOT NULL -- 职位名称, min_salary INTEGER -- 最低月薪, max_salary INTEGER -- 最高月薪, CONSTRAINT job_id_pk PRIMARY KEY(job_id)) ;-- 员工信息表
CREATE TABLE employees( employee_id INTEGER NOT NULL -- 员工编号主键, first_name CHARACTER VARYING(20) -- 名字, last_name CHARACTER VARYING(25) NOT NULL -- 姓氏, email CHARACTER VARYING(25) NOT NULL -- 邮箱, phone_number CHARACTER VARYING(20) -- 电话号码, hire_date DATE NOT NULL -- 入职日期, job_id CHARACTER VARYING(10) NOT NULL -- 职位编号, salary NUMERIC(8,2) -- 月薪, commission_pct NUMERIC(2,2) -- 销售提成百分比, manager_id INTEGER -- 经理, department_id INTEGER -- 部门编号, CONSTRAINT emp_emp_id_pk PRIMARY KEY (employee_id), CONSTRAINT emp_salary_min CHECK (salary 0) , CONSTRAINT emp_email_uk UNIQUE (email), CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id), CONSTRAINT emp_job_fk FOREIGN KEY (job_id) REFERENCES jobs(job_id), CONSTRAINT emp_manager_fk FOREIGN KEY (manager_id) REFERENCES employees(employee_id)) ;ALTER TABLE departments ADD CONSTRAINT dept_mgr_fk FOREIGN KEY (manager_id) REFERENCES employees(employee_id);-- 员工详细信息视图
CREATE VIEW emp_details_view(employee_id,job_id,manager_id,department_id,location_id,first_name,last_name,salary,commission_pct,department_name,job_title)
AS SELECTe.employee_id, e.job_id, e.manager_id, e.department_id,d.location_id,e.first_name,e.last_name,e.salary,e.commission_pct,d.department_name,j.job_title
FROMemployees e,departments d,jobs j
WHERE e.department_id d.department_idAND j.job_id e.job_id;插入数据
ALTER TABLE departments ALTER CONSTRAINT dept_mgr_fk DEFERRABLE INITIALLY DEFERRED;BEGIN;
INSERT INTO departments VALUES ( 10, Administration, 200, 1700);INSERT INTO departments VALUES ( 20, Marketing, 201, 1800);INSERT INTO departments VALUES ( 30, Purchasing, 114, 1700);INSERT INTO departments VALUES ( 40, Human Resources, 203, 2400);INSERT INTO departments VALUES ( 50, Shipping, 121, 1500);INSERT INTO departments VALUES ( 60 , IT, 103, 1400);INSERT INTO departments VALUES ( 70 , Public Relations, 204, 2700);INSERT INTO departments VALUES ( 80 , Sales, 145, 2500);INSERT INTO departments VALUES ( 90 , Executive, 100, 1700);INSERT INTO departments VALUES ( 100 , Finance, 108, 1700);INSERT INTO departments VALUES ( 110 , Accounting, 205, 1700);INSERT INTO departments VALUES ( 120 , Treasury, NULL, 1700);INSERT INTO departments VALUES ( 130 , Corporate Tax, NULL, 1700);INSERT INTO departments VALUES ( 140 , Control And Credit, NULL, 1700);INSERT INTO departments VALUES ( 150 , Shareholder Services, NULL, 1700);INSERT INTO departments VALUES ( 160 , Benefits, NULL, 1700);INSERT INTO departments VALUES ( 170 , Manufacturing, NULL, 1700);INSERT INTO departments VALUES ( 180 , Construction, NULL, 1700);INSERT INTO departments VALUES ( 190 , Contracting, NULL, 1700);INSERT INTO departments VALUES ( 200 , Operations, NULL, 1700);INSERT INTO departments VALUES ( 210 , IT Support, NULL, 1700);INSERT INTO departments VALUES ( 220 , NOC, NULL, 1700);INSERT INTO departments VALUES ( 230 , IT Helpdesk, NULL, 1700);INSERT INTO departments VALUES ( 240 , Government Sales, NULL, 1700);INSERT INTO departments VALUES ( 250 , Retail Sales, NULL, 1700);INSERT INTO departments VALUES ( 260 , Recruiting, NULL, 1700);INSERT INTO departments VALUES ( 270 , Payroll, NULL, 1700);INSERT INTO jobs VALUES ( AD_PRES, President, 20080, 40000);
INSERT INTO jobs VALUES ( AD_VP, Administration Vice President, 15000, 30000);INSERT INTO jobs VALUES ( AD_ASST, Administration Assistant, 3000, 6000);INSERT INTO jobs VALUES ( FI_MGR, Finance Manager, 8200, 16000);INSERT INTO jobs VALUES ( FI_ACCOUNT, Accountant, 4200, 9000);INSERT INTO jobs VALUES ( AC_MGR, Accounting Manager, 8200, 16000);INSERT INTO jobs VALUES ( AC_ACCOUNT, Public Accountant, 4200, 9000);
INSERT INTO jobs VALUES ( SA_MAN, Sales Manager, 10000, 20080);INSERT INTO jobs VALUES ( SA_REP, Sales Representative, 6000, 12008);INSERT INTO jobs VALUES ( PU_MAN, Purchasing Manager, 8000, 15000);INSERT INTO jobs VALUES ( PU_CLERK, Purchasing Clerk, 2500, 5500);INSERT INTO jobs VALUES ( ST_MAN, Stock Manager, 5500, 8500);
INSERT INTO jobs VALUES ( ST_CLERK, Stock Clerk, 2008, 5000);INSERT INTO jobs VALUES ( SH_CLERK, Shipping Clerk, 2500, 5500);INSERT INTO jobs VALUES ( IT_PROG, Programmer, 4000, 10000);INSERT INTO jobs VALUES ( MK_MAN, Marketing Manager, 9000, 15000);INSERT INTO jobs VALUES ( MK_REP, Marketing Representative, 4000, 9000);INSERT INTO jobs VALUES ( HR_REP, Human Resources Representative, 4000, 9000);INSERT INTO jobs VALUES ( PR_REP, Public Relations Representative, 4500, 10500);INSERT INTO employees VALUES ( 100, Steven, King, SKING, 515.123.4567, TO_DATE(17-06-2003, dd-MM-yyyy), AD_PRES, 24000, NULL, NULL, 90);INSERT INTO employees VALUES ( 101, Neena, Kochhar, NKOCHHAR, 515.123.4568, TO_DATE(21-09-2005, dd-MM-yyyy), AD_VP, 17000, NULL, 100, 90);INSERT INTO employees VALUES ( 102, Lex, De Haan, LDEHAAN, 515.123.4569, TO_DATE(13-01-2001, dd-MM-yyyy), AD_VP, 17000, NULL, 100, 90);INSERT INTO employees VALUES ( 103, Alexander, Hunold, AHUNOLD, 590.423.4567, TO_DATE(03-01-2006, dd-MM-yyyy), IT_PROG, 9000, NULL, 102, 60);INSERT INTO employees VALUES ( 104, Bruce, Ernst, BERNST, 590.423.4568, TO_DATE(21-05-2007, dd-MM-yyyy), IT_PROG, 6000, NULL, 103, 60);INSERT INTO employees VALUES ( 105, David, Austin, DAUSTIN, 590.423.4569, TO_DATE(25-06-2005, dd-MM-yyyy), IT_PROG, 4800, NULL, 103, 60);INSERT INTO employees VALUES ( 106, Valli, Pataballa, VPATABAL, 590.423.4560, TO_DATE(05-02-2006, dd-MM-yyyy), IT_PROG, 4800, NULL, 103, 60);INSERT INTO employees VALUES ( 107, Diana, Lorentz, DLORENTZ, 590.423.5567, TO_DATE(07-02-2007, dd-MM-yyyy), IT_PROG, 4200, NULL, 103, 60);INSERT INTO employees VALUES ( 108, Nancy, Greenberg, NGREENBE, 515.124.4569, TO_DATE(17-08-2002, dd-MM-yyyy), FI_MGR, 12008, NULL, 101, 100);INSERT INTO employees VALUES ( 109, Daniel, Faviet, DFAVIET, 515.124.4169, TO_DATE(16-08-2002, dd-MM-yyyy), FI_ACCOUNT, 9000, NULL, 108, 100);INSERT INTO employees VALUES ( 110, John, Chen, JCHEN, 515.124.4269, TO_DATE(28-09-2005, dd-MM-yyyy), FI_ACCOUNT, 8200, NULL, 108, 100);INSERT INTO employees VALUES ( 111, Ismael, Sciarra, ISCIARRA, 515.124.4369, TO_DATE(30-09-2005, dd-MM-yyyy), FI_ACCOUNT, 7700, NULL, 108, 100);INSERT INTO employees VALUES ( 112, Jose Manuel, Urman, JMURMAN, 515.124.4469, TO_DATE(07-03-2006, dd-MM-yyyy), FI_ACCOUNT, 7800, NULL, 108, 100);INSERT INTO employees VALUES ( 113, Luis, Popp, LPOPP, 515.124.4567, TO_DATE(07-12-2007, dd-MM-yyyy), FI_ACCOUNT, 6900, NULL, 108, 100);INSERT INTO employees VALUES ( 114, Den, Raphaely, DRAPHEAL, 515.127.4561, TO_DATE(07-12-2002, dd-MM-yyyy), PU_MAN, 11000, NULL, 100, 30);INSERT INTO employees VALUES ( 115, Alexander, Khoo, AKHOO, 515.127.4562, TO_DATE(18-05-2003, dd-MM-yyyy), PU_CLERK, 3100, NULL, 114, 30);INSERT INTO employees VALUES ( 116, Shelli, Baida, SBAIDA, 515.127.4563, TO_DATE(24-12-2005, dd-MM-yyyy), PU_CLERK, 2900, NULL, 114, 30);INSERT INTO employees VALUES ( 117, Sigal, Tobias, STOBIAS, 515.127.4564, TO_DATE(24-07-2005, dd-MM-yyyy), PU_CLERK, 2800, NULL, 114, 30);INSERT INTO employees VALUES ( 118, Guy, Himuro, GHIMURO, 515.127.4565, TO_DATE(15-11-2006, dd-MM-yyyy), PU_CLERK, 2600, NULL, 114, 30);INSERT INTO employees VALUES ( 119, Karen, Colmenares, KCOLMENA, 515.127.4566, TO_DATE(10-08-2007, dd-MM-yyyy), PU_CLERK, 2500, NULL, 114, 30);INSERT INTO employees VALUES ( 120, Matthew, Weiss, MWEISS, 650.123.1234, TO_DATE(18-07-2004, dd-MM-yyyy), ST_MAN, 8000, NULL, 100, 50);INSERT INTO employees VALUES ( 121, Adam, Fripp, AFRIPP, 650.123.2234, TO_DATE(10-04-2005, dd-MM-yyyy), ST_MAN, 8200, NULL, 100, 50);INSERT INTO employees VALUES ( 122, Payam, Kaufling, PKAUFLIN, 650.123.3234, TO_DATE(01-05-2003, dd-MM-yyyy), ST_MAN, 7900, NULL, 100, 50);INSERT INTO employees VALUES ( 123, Shanta, Vollman, SVOLLMAN, 650.123.4234, TO_DATE(10-10-2005, dd-MM-yyyy), ST_MAN, 6500, NULL, 100, 50);INSERT INTO employees VALUES ( 124, Kevin, Mourgos, KMOURGOS, 650.123.5234, TO_DATE(16-11-2007, dd-MM-yyyy), ST_MAN, 5800, NULL, 100, 50);INSERT INTO employees VALUES ( 125, Julia, Nayer, JNAYER, 650.124.1214, TO_DATE(16-07-2005, dd-MM-yyyy), ST_CLERK, 3200, NULL, 120, 50);INSERT INTO employees VALUES ( 126, Irene, Mikkilineni, IMIKKILI, 650.124.1224, TO_DATE(28-09-2006, dd-MM-yyyy), ST_CLERK, 2700, NULL, 120, 50);INSERT INTO employees VALUES ( 127, James, Landry, JLANDRY, 650.124.1334, TO_DATE(14-01-2007, dd-MM-yyyy), ST_CLERK, 2400, NULL, 120, 50);INSERT INTO employees VALUES ( 128, Steven, Markle, SMARKLE, 650.124.1434, TO_DATE(08-03-2008, dd-MM-yyyy), ST_CLERK, 2200, NULL, 120, 50);INSERT INTO employees VALUES ( 129, Laura, Bissot, LBISSOT, 650.124.5234, TO_DATE(20-08-2005, dd-MM-yyyy), ST_CLERK, 3300, NULL, 121, 50);INSERT INTO employees VALUES ( 130, Mozhe, Atkinson, MATKINSO, 650.124.6234, TO_DATE(30-10-2005, dd-MM-yyyy), ST_CLERK, 2800, NULL, 121, 50);INSERT INTO employees VALUES ( 131, James, Marlow, JAMRLOW, 650.124.7234, TO_DATE(16-02-2005, dd-MM-yyyy), ST_CLERK, 2500, NULL, 121, 50);INSERT INTO employees VALUES ( 132, TJ, Olson, TJOLSON, 650.124.8234, TO_DATE(10-04-2007, dd-MM-yyyy), ST_CLERK, 2100, NULL, 121, 50);INSERT INTO employees VALUES ( 133, Jason, Mallin, JMALLIN, 650.127.1934, TO_DATE(14-06-2004, dd-MM-yyyy), ST_CLERK, 3300, NULL, 122, 50);INSERT INTO employees VALUES ( 134, Michael, Rogers, MROGERS, 650.127.1834, TO_DATE(26-08-2006, dd-MM-yyyy), ST_CLERK, 2900, NULL, 122, 50);INSERT INTO employees VALUES ( 135, Ki, Gee, KGEE, 650.127.1734, TO_DATE(12-12-2007, dd-MM-yyyy), ST_CLERK, 2400, NULL, 122, 50);INSERT INTO employees VALUES ( 136, Hazel, Philtanker, HPHILTAN, 650.127.1634, TO_DATE(06-02-2008, dd-MM-yyyy), ST_CLERK, 2200, NULL, 122, 50);INSERT INTO employees VALUES ( 137, Renske, Ladwig, RLADWIG, 650.121.1234, TO_DATE(14-07-2003, dd-MM-yyyy), ST_CLERK, 3600, NULL, 123, 50);INSERT INTO employees VALUES ( 138, Stephen, Stiles, SSTILES, 650.121.2034, TO_DATE(26-10-2005, dd-MM-yyyy), ST_CLERK, 3200, NULL, 123, 50);INSERT INTO employees VALUES ( 139, John, Seo, JSEO, 650.121.2019, TO_DATE(12-02-2006, dd-MM-yyyy), ST_CLERK, 2700, NULL, 123, 50);INSERT INTO employees VALUES ( 140, Joshua, Patel, JPATEL, 650.121.1834, TO_DATE(06-04-2006, dd-MM-yyyy), ST_CLERK, 2500, NULL, 123, 50);INSERT INTO employees VALUES ( 141, Trenna, Rajs, TRAJS, 650.121.8009, TO_DATE(17-10-2003, dd-MM-yyyy), ST_CLERK, 3500, NULL, 124, 50);INSERT INTO employees VALUES ( 142, Curtis, Davies, CDAVIES, 650.121.2994, TO_DATE(29-01-2005, dd-MM-yyyy), ST_CLERK, 3100, NULL, 124, 50);INSERT INTO employees VALUES ( 143, Randall, Matos, RMATOS, 650.121.2874, TO_DATE(15-03-2006, dd-MM-yyyy), ST_CLERK, 2600, NULL, 124, 50);INSERT INTO employees VALUES ( 144, Peter, Vargas, PVARGAS, 650.121.2004, TO_DATE(09-07-2006, dd-MM-yyyy), ST_CLERK, 2500, NULL, 124, 50);INSERT INTO employees VALUES ( 145, John, Russell, JRUSSEL, 011.44.1344.429268, TO_DATE(01-10-2004, dd-MM-yyyy), SA_MAN, 14000, .4, 100, 80);INSERT INTO employees VALUES ( 146, Karen, Partners, KPARTNER, 011.44.1344.467268, TO_DATE(05-01-2005, dd-MM-yyyy), SA_MAN, 13500, .3, 100, 80);INSERT INTO employees VALUES ( 147, Alberto, Errazuriz, AERRAZUR, 011.44.1344.429278, TO_DATE(10-03-2005, dd-MM-yyyy), SA_MAN, 12000, .3, 100, 80);INSERT INTO employees VALUES ( 148, Gerald, Cambrault, GCAMBRAU, 011.44.1344.619268, TO_DATE(15-10-2007, dd-MM-yyyy), SA_MAN, 11000, .3, 100, 80);INSERT INTO employees VALUES ( 149, Eleni, Zlotkey, EZLOTKEY, 011.44.1344.429018, TO_DATE(29-01-2008, dd-MM-yyyy), SA_MAN, 10500, .2, 100, 80);INSERT INTO employees VALUES ( 150, Peter, Tucker, PTUCKER, 011.44.1344.129268, TO_DATE(30-01-2005, dd-MM-yyyy), SA_REP, 10000, .3, 145, 80);INSERT INTO employees VALUES ( 151, David, Bernstein, DBERNSTE, 011.44.1344.345268, TO_DATE(24-03-2005, dd-MM-yyyy), SA_REP, 9500, .25, 145, 80);INSERT INTO employees VALUES ( 152, Peter, Hall, PHALL, 011.44.1344.478968, TO_DATE(20-08-2005, dd-MM-yyyy), SA_REP, 9000, .25, 145, 80);INSERT INTO employees VALUES ( 153, Christopher, Olsen, COLSEN, 011.44.1344.498718, TO_DATE(30-03-2006, dd-MM-yyyy), SA_REP, 8000, .2, 145, 80);INSERT INTO employees VALUES ( 154, Nanette, Cambrault, NCAMBRAU, 011.44.1344.987668, TO_DATE(09-12-2006, dd-MM-yyyy), SA_REP, 7500, .2, 145, 80);INSERT INTO employees VALUES ( 155, Oliver, Tuvault, OTUVAULT, 011.44.1344.486508, TO_DATE(23-11-2007, dd-MM-yyyy), SA_REP, 7000, .15, 145, 80);INSERT INTO employees VALUES ( 156, Janette, King, JKING, 011.44.1345.429268, TO_DATE(30-01-2004, dd-MM-yyyy), SA_REP, 10000, .35, 146, 80);INSERT INTO employees VALUES ( 157, Patrick, Sully, PSULLY, 011.44.1345.929268, TO_DATE(04-03-2004, dd-MM-yyyy), SA_REP, 9500, .35, 146, 80);INSERT INTO employees VALUES ( 158, Allan, McEwen, AMCEWEN, 011.44.1345.829268, TO_DATE(01-08-2004, dd-MM-yyyy), SA_REP, 9000, .35, 146, 80);INSERT INTO employees VALUES ( 159, Lindsey, Smith, LSMITH, 011.44.1345.729268, TO_DATE(10-03-2005, dd-MM-yyyy), SA_REP, 8000, .3, 146, 80);INSERT INTO employees VALUES ( 160, Louise, Doran, LDORAN, 011.44.1345.629268, TO_DATE(15-12-2005, dd-MM-yyyy), SA_REP, 7500, .3, 146, 80);INSERT INTO employees VALUES ( 161, Sarath, Sewall, SSEWALL, 011.44.1345.529268, TO_DATE(03-11-2006, dd-MM-yyyy), SA_REP, 7000, .25, 146, 80);INSERT INTO employees VALUES ( 162, Clara, Vishney, CVISHNEY, 011.44.1346.129268, TO_DATE(11-11-2005, dd-MM-yyyy), SA_REP, 10500, .25, 147, 80);INSERT INTO employees VALUES ( 163, Danielle, Greene, DGREENE, 011.44.1346.229268, TO_DATE(19-03-2007, dd-MM-yyyy), SA_REP, 9500, .15, 147, 80);INSERT INTO employees VALUES ( 164, Mattea, Marvins, MMARVINS, 011.44.1346.329268, TO_DATE(24-01-2008, dd-MM-yyyy), SA_REP, 7200, .10, 147, 80);INSERT INTO employees VALUES ( 165, David, Lee, DLEE, 011.44.1346.529268, TO_DATE(23-02-2008, dd-MM-yyyy), SA_REP, 6800, .1, 147, 80);INSERT INTO employees VALUES ( 166, Sundar, Ande, SANDE, 011.44.1346.629268, TO_DATE(24-03-2008, dd-MM-yyyy), SA_REP, 6400, .10, 147, 80);INSERT INTO employees VALUES ( 167, Amit, Banda, ABANDA, 011.44.1346.729268, TO_DATE(21-04-2008, dd-MM-yyyy), SA_REP, 6200, .10, 147, 80);INSERT INTO employees VALUES ( 168, Lisa, Ozer, LOZER, 011.44.1343.929268, TO_DATE(11-03-2005, dd-MM-yyyy), SA_REP, 11500, .25, 148, 80);INSERT INTO employees VALUES ( 169 , Harrison, Bloom, HBLOOM, 011.44.1343.829268, TO_DATE(23-03-2006, dd-MM-yyyy), SA_REP, 10000, .20, 148, 80);INSERT INTO employees VALUES ( 170, Tayler, Fox, TFOX, 011.44.1343.729268, TO_DATE(24-01-2006, dd-MM-yyyy), SA_REP, 9600, .20, 148, 80);INSERT INTO employees VALUES ( 171, William, Smith, WSMITH, 011.44.1343.629268, TO_DATE(23-02-2007, dd-MM-yyyy), SA_REP, 7400, .15, 148, 80);INSERT INTO employees VALUES ( 172, Elizabeth, Bates, EBATES, 011.44.1343.529268, TO_DATE(24-03-2007, dd-MM-yyyy), SA_REP, 7300, .15, 148, 80);INSERT INTO employees VALUES ( 173, Sundita, Kumar, SKUMAR, 011.44.1343.329268, TO_DATE(21-04-2008, dd-MM-yyyy), SA_REP, 6100, .10, 148, 80);INSERT INTO employees VALUES ( 174, Ellen, Abel, EABEL, 011.44.1644.429267, TO_DATE(11-05-2004, dd-MM-yyyy), SA_REP, 11000, .30, 149, 80);INSERT INTO employees VALUES ( 175, Alyssa, Hutton, AHUTTON, 011.44.1644.429266, TO_DATE(19-03-2005, dd-MM-yyyy), SA_REP, 8800, .25, 149, 80);INSERT INTO employees VALUES ( 176, Jonathon, Taylor, JTAYLOR, 011.44.1644.429265, TO_DATE(24-03-2006, dd-MM-yyyy), SA_REP, 8600, .20, 149, 80);INSERT INTO employees VALUES ( 177, Jack, Livingston, JLIVINGS, 011.44.1644.429264, TO_DATE(23-04-2006, dd-MM-yyyy), SA_REP, 8400, .20, 149, 80);INSERT INTO employees VALUES ( 178, Kimberely, Grant, KGRANT, 011.44.1644.429263, TO_DATE(24-05-2007, dd-MM-yyyy), SA_REP, 7000, .15, 149, NULL);INSERT INTO employees VALUES ( 179, Charles, Johnson, CJOHNSON, 011.44.1644.429262, TO_DATE(04-01-2008, dd-MM-yyyy), SA_REP, 6200, .10, 149, 80);INSERT INTO employees VALUES ( 180, Winston, Taylor, WTAYLOR, 650.507.9876, TO_DATE(24-01-2006, dd-MM-yyyy), SH_CLERK, 3200, NULL, 120, 50);INSERT INTO employees VALUES ( 181, Jean, Fleaur, JFLEAUR, 650.507.9877, TO_DATE(23-02-2006, dd-MM-yyyy), SH_CLERK, 3100, NULL, 120, 50);INSERT INTO employees VALUES ( 182, Martha, Sullivan, MSULLIVA, 650.507.9878, TO_DATE(21-06-2007, dd-MM-yyyy), SH_CLERK, 2500, NULL, 120, 50);INSERT INTO employees VALUES ( 183, Girard, Geoni, GGEONI, 650.507.9879, TO_DATE(03-02-2008, dd-MM-yyyy), SH_CLERK, 2800, NULL, 120, 50);INSERT INTO employees VALUES ( 184, Nandita, Sarchand, NSARCHAN, 650.509.1876, TO_DATE(27-01-2004, dd-MM-yyyy), SH_CLERK, 4200, NULL, 121, 50);INSERT INTO employees VALUES ( 185, Alexis, Bull, ABULL, 650.509.2876, TO_DATE(20-02-2005, dd-MM-yyyy), SH_CLERK, 4100, NULL, 121, 50);INSERT INTO employees VALUES ( 186, Julia, Dellinger, JDELLING, 650.509.3876, TO_DATE(24-06-2006, dd-MM-yyyy), SH_CLERK, 3400, NULL, 121, 50);INSERT INTO employees VALUES ( 187, Anthony, Cabrio, ACABRIO, 650.509.4876, TO_DATE(07-02-2007, dd-MM-yyyy), SH_CLERK, 3000, NULL, 121, 50);INSERT INTO employees VALUES ( 188, Kelly, Chung, KCHUNG, 650.505.1876, TO_DATE(14-06-2005, dd-MM-yyyy), SH_CLERK, 3800, NULL, 122, 50);INSERT INTO employees VALUES ( 189, Jennifer, Dilly, JDILLY, 650.505.2876, TO_DATE(13-08-2005, dd-MM-yyyy), SH_CLERK, 3600, NULL, 122, 50);INSERT INTO employees VALUES ( 190, Timothy, Gates, TGATES, 650.505.3876, TO_DATE(11-07-2006, dd-MM-yyyy), SH_CLERK, 2900, NULL, 122, 50);INSERT INTO employees VALUES ( 191, Randall, Perkins, RPERKINS, 650.505.4876, TO_DATE(19-12-2007, dd-MM-yyyy), SH_CLERK, 2500, NULL, 122, 50);INSERT INTO employees VALUES ( 192, Sarah, Bell, SBELL, 650.501.1876, TO_DATE(04-02-2004, dd-MM-yyyy), SH_CLERK, 4000, NULL, 123, 50);INSERT INTO employees VALUES ( 193, Britney, Everett, BEVERETT, 650.501.2876, TO_DATE(03-03-2005, dd-MM-yyyy), SH_CLERK, 3900, NULL, 123, 50);INSERT INTO employees VALUES ( 194, Samuel, McCain, SMCCAIN, 650.501.3876, TO_DATE(01-07-2006, dd-MM-yyyy), SH_CLERK, 3200, NULL, 123, 50);INSERT INTO employees VALUES ( 195, Vance, Jones, VJONES, 650.501.4876, TO_DATE(17-03-2007, dd-MM-yyyy), SH_CLERK, 2800, NULL, 123, 50);INSERT INTO employees VALUES ( 196, Alana, Walsh, AWALSH, 650.507.9811, TO_DATE(24-04-2006, dd-MM-yyyy), SH_CLERK, 3100, NULL, 124, 50);INSERT INTO employees VALUES ( 197, Kevin, Feeney, KFEENEY, 650.507.9822, TO_DATE(23-05-2006, dd-MM-yyyy), SH_CLERK, 3000, NULL, 124, 50);INSERT INTO employees VALUES ( 198, Donald, OConnell, DOCONNEL, 650.507.9833, TO_DATE(21-06-2007, dd-MM-yyyy), SH_CLERK, 2600, NULL, 124, 50);INSERT INTO employees VALUES ( 199, Douglas, Grant, DGRANT, 650.507.9844, TO_DATE(13-01-2008, dd-MM-yyyy), SH_CLERK, 2600, NULL, 124, 50);INSERT INTO employees VALUES ( 200, Jennifer, Whalen, JWHALEN, 515.123.4444, TO_DATE(17-09-2003, dd-MM-yyyy), AD_ASST, 4400, NULL, 101, 10);INSERT INTO employees VALUES ( 201, Michael, Hartstein, MHARTSTE, 515.123.5555, TO_DATE(17-02-2004, dd-MM-yyyy), MK_MAN, 13000, NULL, 100, 20);INSERT INTO employees VALUES ( 202, Pat, Fay, PFAY, 603.123.6666, TO_DATE(17-08-2005, dd-MM-yyyy), MK_REP, 6000, NULL, 201, 20);INSERT INTO employees VALUES ( 203, Susan, Mavris, SMAVRIS, 515.123.7777, TO_DATE(07-06-2002, dd-MM-yyyy), HR_REP, 6500, NULL, 101, 40);INSERT INTO employees VALUES ( 204, Hermann, Baer, HBAER, 515.123.8888, TO_DATE(07-06-2002, dd-MM-yyyy), PR_REP, 10000, NULL, 101, 70);INSERT INTO employees VALUES ( 205, Shelley, Higgins, SHIGGINS, 515.123.8080, TO_DATE(07-06-2002, dd-MM-yyyy), AC_MGR, 12008, NULL, 101, 110);INSERT INTO employees VALUES ( 206, William, Gietz, WGIETZ, 515.123.8181, TO_DATE(07-06-2002, dd-MM-yyyy), AC_ACCOUNT, 8300, NULL, 205, 110);
COMMIT;ALTER TABLE departments ALTER CONSTRAINT dept_mgr_fk NOT DEFERRABLE INITIALLY IMMEDIATE;创建索引
CREATE INDEX emp_department_ix ON employees (department_id);CREATE INDEX emp_job_ix ON employees (job_id);CREATE INDEX emp_manager_ix ON employees (manager_id);CREATE INDEX emp_name_ix ON employees (last_name, first_name);CREATE INDEX dept_location_ix ON departments (location_id);删除表
DROP VIEW emp_details_view;
DROP TABLE departments CASCADE;
DROP TABLE jobs CASCADE;
DROP TABLE employees CASCADE;postgresql命令速查
postgresql命令速查
简单查询
/*
注释作者 jane
时间 2023/08/17
*/
select first_name,last_name
from employees ;计算查询结果
select 11 as sumResult;SQL使用SELECT和FROM查询表中的数据 *表示查询全部字段 PostgreSQL提供了无表查询的扩展使用AS指定别名 使用DISTINCT去除查询结果中的重复值 PostgreSQL支持两种格式的代码注释。
利用查询条件过滤数据
PostgreSQL使用WHERE子句过滤表中的数据 PostgreSQL提供了以下比较运算符、、 BETWEEN、IN等。
模糊查询 -- sh sa-- %任意多个字符-- _任意一个字符select first_namefrom employeeswhere first_name like S_%;create table t_like (v TeXT);insert into t_like(v) values (进度 25%已完成);
insert into t_like(v) values (日期2022年6月25日。);select * from t_like;-- 查询包含25%的字符串
-- escape转义#后的字符
select * from t_like where v like %25#%% escape #;
-- 也可以使用\进行转义效果和escape一样
select * from t_like where v like %25\%%;
