MySQL基础1-查询

最新推荐文章于 2024-04-02 08:55:17 发布
最新推荐文章于 2024-04-02 08:55:17 发布
阅读量311 收藏
点赞数
分类专栏： SQL基础 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u014215131/article/details/111406054
版权

1. SQL(Structured Query Language)常见命令

  1. 查看当前所有的数据库
show databases;
  1. 打开指定的库
use 库名;
  1. 查看当前库的所有表
show tables;
  1. 查看其它库的所有表
show tables from 库名;
  1. 创建表
create table 表名(
列名 列类型,
列名 列类型
);
  1. 查看表结构
desc 表名;
  1. 查看服务器版本
select version();

MySQL的语法规范
1.不区分大小写，但建议关键字大写，表明、列名小写
2.每条命令最好用分号结尾
3.单行注释 # 注释文字 多行注释 /* 注释文字 */

2. DQL(Data Query Language)语言

/*
SQLyog Ultimate v10.00 Beta1
MySQL - 5.5.15 : Database - myemployees
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`myemployees` /*!40100 DEFAULT CHARACTER SET gb2312 */;

USE `myemployees`;

/*Table structure for table `departments` */

DROP TABLE IF EXISTS `departments`;

CREATE TABLE `departments` (
  `department_id` int(4) NOT NULL AUTO_INCREMENT,
  `department_name` varchar(3) DEFAULT NULL,
  `manager_id` int(6) DEFAULT NULL,
  `location_id` int(4) DEFAULT NULL,
  PRIMARY KEY (`department_id`),
  KEY `loc_id_fk` (`location_id`),
  CONSTRAINT `loc_id_fk` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=271 DEFAULT CHARSET=gb2312;

/*Data for the table `departments` */

insert  into `departments`(`department_id`,`department_name`,`manager_id`,`location_id`) values (10,'Adm',200,1700),(20,'Mar',201,1800),(30,'Pur',114,1700),(40,'Hum',203,2400),(50,'Shi',121,1500),(60,'IT',103,1400),(70,'Pub',204,2700),(80,'Sal',145,2500),(90,'Exe',100,1700),(100,'Fin',108,1700),(110,'Acc',205,1700),(120,'Tre',NULL,1700),(130,'Cor',NULL,1700),(140,'Con',NULL,1700),(150,'Sha',NULL,1700),(160,'Ben',NULL,1700),(170,'Man',NULL,1700),(180,'Con',NULL,1700),(190,'Con',NULL,1700),(200,'Ope',NULL,1700),(210,'IT ',NULL,1700),(220,'NOC',NULL,1700),(230,'IT ',NULL,1700),(240,'Gov',NULL,1700),(250,'Ret',NULL,1700),(260,'Rec',NULL,1700),(270,'Pay',NULL,1700);

/*Table structure for table `employees` */

DROP TABLE IF EXISTS `employees`;

CREATE TABLE `employees` (
  `employee_id` int(6) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(20) DEFAULT NULL,
  `last_name` varchar(25) DEFAULT NULL,
  `email` varchar(25) DEFAULT NULL,
  `phone_number` varchar(20) DEFAULT NULL,
  `job_id` varchar(10) DEFAULT NULL,
  `salary` double(10,2) DEFAULT NULL,
  `commission_pct` double(4,2) DEFAULT NULL,
  `manager_id` int(6) DEFAULT NULL,
  `department_id` int(4) DEFAULT NULL,
  `hiredate` datetime DEFAULT NULL,
  PRIMARY KEY (`employee_id`),
  KEY `dept_id_fk` (`department_id`),
  KEY `job_id_fk` (`job_id`),
  CONSTRAINT `dept_id_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`),
  CONSTRAINT `job_id_fk` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`)
) ENGINE=InnoDB AUTO_INCREMENT=207 DEFAULT CHARSET=gb2312;

/*Data for the table `employees` */

insert  into `employees`(`employee_id`,`first_name`,`last_name`,`email`,`phone_number`,`job_id`,`salary`,`commission_pct`,`manager_id`,`department_id`,`hiredate`) values (100,'Steven','K_ing','SKING','515.123.4567','AD_PRES',24000.00,NULL,NULL,90,'1992-04-03 00:00:00'),(101,'Neena','Kochhar','NKOCHHAR','515.123.4568','AD_VP',17000.00,NULL,100,90,'1992-04-03 00:00:00'),(102,'Lex','De Haan','LDEHAAN','515.123.4569','AD_VP',17000.00,NULL,100,90,'1992-04-03 00:00:00'),(103,'Alexander','Hunold','AHUNOLD','590.423.4567','IT_PROG',9000.00,NULL,102,60,'1992-04-03 00:00:00'),(104,'Bruce','Ernst','BERNST','590.423.4568','IT_PROG',6000.00,NULL,103,60,'1992-04-03 00:00:00'),(105,'David','Austin','DAUSTIN','590.423.4569','IT_PROG',4800.00,NULL,103,60,'1998-03-03 00:00:00'),(106,'Valli','Pataballa','VPATABAL','590.423.4560','IT_PROG',4800.00,NULL,103,60,'1998-03-03 00:00:00'),(107,'Diana','Lorentz','DLORENTZ','590.423.5567','IT_PROG',4200.00,NULL,103,60,'1998-03-03 00:00:00'),(108,'Nancy','Greenberg','NGREENBE','515.124.4569','FI_MGR',12000.00,NULL,101,100,'1998-03-03 00:00:00'),(109,'Daniel','Faviet','DFAVIET','515.124.4169','FI_ACCOUNT',9000.00,NULL,108,100,'1998-03-03 00:00:00'),(110,'John','Chen','JCHEN','515.124.4269','FI_ACCOUNT',8200.00,NULL,108,100,'2000-09-09 00:00:00'),(111,'Ismael','Sciarra','ISCIARRA','515.124.4369','FI_ACCOUNT',7700.00,NULL,108,100,'2000-09-09 00:00:00'),(112,'Jose Manuel','Urman','JMURMAN','515.124.4469','FI_ACCOUNT',7800.00,NULL,108,100,'2000-09-09 00:00:00'),(113,'Luis','Popp','LPOPP','515.124.4567','FI_ACCOUNT',6900.00,NULL,108,100,'2000-09-09 00:00:00'),(114,'Den','Raphaely','DRAPHEAL','515.127.4561','PU_MAN',11000.00,NULL,100,30,'2000-09-09 00:00:00'),(115,'Alexander','Khoo','AKHOO','515.127.4562','PU_CLERK',3100.00,NULL,114,30,'2000-09-09 00:00:00'),(116,'Shelli','Baida','SBAIDA','515.127.4563','PU_CLERK',2900.00,NULL,114,30,'2000-09-09 00:00:00'),(117,'Sigal','Tobias','STOBIAS','515.127.4564','PU_CLERK',2800.00,NULL,114,30,'2000-09-09 00:00:00'),(118,'Guy','Himuro','GHIMURO','515.127.4565','PU_CLERK',2600.00,NULL,114,30,'2000-09-09 00:00:00'),(119,'Karen','Colmenares','KCOLMENA','515.127.4566','PU_CLERK',2500.00,NULL,114,30,'2000-09-09 00:00:00'),(120,'Matthew','Weiss','MWEISS','650.123.1234','ST_MAN',8000.00,NULL,100,50,'2004-02-06 00:00:00'),(121,'Adam','Fripp','AFRIPP','650.123.2234','ST_MAN',8200.00,NULL,100,50,'2004-02-06 00:00:00'),(122,'Payam','Kaufling','PKAUFLIN','650.123.3234','ST_MAN',7900.00,NULL,100,50,'2004-02-06 00:00:00'),(123,'Shanta','Vollman','SVOLLMAN','650.123.4234','ST_MAN',6500.00,NULL,100,50,'2004-02-06 00:00:00'),(124,'Kevin','Mourgos','KMOURGOS','650.123.5234','ST_MAN',5800.00,NULL,100,50,'2004-02-06 00:00:00'),(125,'Julia','Nayer','JNAYER','650.124.1214','ST_CLERK',3200.00,NULL,120,50,'2004-02-06 00:00:00'),(126,'Irene','Mikkilineni','IMIKKILI','650.124.1224','ST_CLERK',2700.00,NULL,120,50,'2004-02-06 00:00:00'),(127,'James','Landry','JLANDRY','650.124.1334','ST_CLERK',2400.00,NULL,120,50,'2004-02-06 00:00:00'),(128,'Steven','Markle','SMARKLE','650.124.1434','ST_CLERK',2200.00,NULL,120,50,'2004-02-06 00:00:00'),(129,'Laura','Bissot','LBISSOT','650.124.5234','ST_CLERK',3300.00,NULL,121,50,'2004-02-06 00:00:00'),(130,'Mozhe','Atkinson','MATKINSO','650.124.6234','ST_CLERK',2800.00,NULL,121,50,'2004-02-06 00:00:00'),(131,'James','Marlow','JAMRLOW','650.124.7234','ST_CLERK',2500.00,NULL,121,50,'2004-02-06 00:00:00'),(132,'TJ','Olson','TJOLSON','650.124.8234','ST_CLERK',2100.00,NULL,121,50,'2004-02-06 00:00:00'),(133,'Jason','Mallin','JMALLIN','650.127.1934','ST_CLERK',3300.00,NULL,122,50,'2004-02-06 00:00:00'),(134,'Michael','Rogers','MROGERS','650.127.1834','ST_CLERK',2900.00,NULL,122,50,'2002-12-23 00:00:00'),(135,'Ki','Gee','KGEE','650.127.1734','ST_CLERK',2400.00,NULL,122,50,'2002-12-23 00:00:00'),(136,'Hazel','Philtanker','HPHILTAN','650.127.1634','ST_CLERK',2200.00,NULL,122,50,'2002-12-23 00:00:00'),(137,'Renske','Ladwig','RLADWIG','650.121.1234','ST_CLERK',3600.00,NULL,123,50,'2002-12-23 00:00:00'),(138,'Stephen','Stiles','SSTILES','650.121.2034','ST_CLERK',3200.00,NULL,123,50,'2002-12-23 00:00:00'),(139,'John','Seo','JSEO','650.121.2019','ST_CLERK',2700.00,NULL,123,50,'2002-12-23 00:00:00'),(140,'Joshua','Patel','JPATEL','650.121.1834','ST_CLERK',2500.00,NULL,123,50,'2002-12-23 00:00:00'),(141,'Trenna','Rajs','TRAJS','650.121.8009','ST_CLERK',3500.00,NULL,124,50,'2002-12-23 00:00:00'),(142,'Curtis','Davies','CDAVIES','650.121.2994','ST_CLERK',3100.00,NULL,124,50,'2002-12-23 00:00:00'),(143,'Randall','Matos','RMATOS','650.121.2874','ST_CLERK',2600.00,NULL,124,50,'2002-12-23 00:00:00'),(144,'Peter','Vargas','PVARGAS','650.121.2004','ST_CLERK',2500.00,NULL,124,50,'2002-12-23 00:00:00'),(145,'John','Russell','JRUSSEL','011.44.1344.429268','SA_MAN',14000.00,0.40,100,80,'2002-12-23 00:00:00'),(146,'Karen','Partners','KPARTNER','011.44.1344.467268','SA_MAN',13500.00,0.30,100,80,'2002-12-23 00:00:00'),(147,'Alberto','Errazuriz','AERRAZUR','011.44.1344.429278','SA_MAN',12000.00,0.30,100,80,'2002-12-23 00:00:00'),(148,'Gerald','Cambrault','GCAMBRAU','011.44.1344.619268','SA_MAN',11000.00,0.30,100,80,'2002-12-23 00:00:00'),(149,'Eleni','Zlotkey','EZLOTKEY','011.44.1344.429018','SA_MAN',10500.00,0.20,100,80,'2002-12-23 00:00:00'),(150,'Peter','Tucker','PTUCKER','011.44.1344.129268','SA_REP',10000.00,0.30,145,80,'2014-03-05 00:00:00'),(151,'David','Bernstein','DBERNSTE','011.44.1344.345268','SA_REP',9500.00,0.25,145,80,'2014-03-05 00:00:00'),(152,'Peter','Hall','PHALL','011.44.1344.478968','SA_REP',9000.00,0.25,145,80,'2014-03-05 00:00:00'),(153,'Christopher','Olsen','COLSEN','011.44.1344.498718','SA_REP',8000.00,0.20,145,80,'2014-03-05 00:00:00'),(154,'Nanette','Cambrault','NCAMBRAU','011.44.1344.987668','SA_REP',7500.00,0.20,145,80,'2014-03-05 00:00:00'),(155,'Oliver','Tuvault','OTUVAULT','011.44.1344.486508','SA_REP',7000.00,0.15,145,80,'2014-03-05 00:00:00'),(156,'Janette','K_ing','JKING','011.44.1345.429268','SA_REP',10000.00,0.35,146,80,'2014-03-05 00:00:00'),(157,'Patrick','Sully','PSULLY','011.44.1345.929268','SA_REP',9500.00,0.35,146,80,'2014-03-05 00:00:00'),(158,'Allan','McEwen','AMCEWEN','011.44.1345.829268','SA_REP',9000.00,0.35,146,80,'2014-03-05 00:00:00'),(159,'Lindsey','Smith','LSMITH','011.44.1345.729268','SA_REP',8000.00,0.30,146,80,'2014-03-05 00:00:00'),(160,'Louise','Doran','LDORAN','011.44.1345.629268','SA_REP',7500.00,0.30,146,80,'2014-03-05 00:00:00'),(161,'Sarath','Sewall','SSEWALL','011.44.1345.529268','SA_REP',7000.00,0.25,146,80,'2014-03-05 00:00:00'),(162,'Clara','Vishney','CVISHNEY','011.44.1346.129268','SA_REP',10500.00,0.25,147,80,'2014-03-05 00:00:00'),(163,'Danielle','Greene','DGREENE','011.44.1346.229268','SA_REP',9500.00,0.15,147,80,'2014-03-05 00:00:00'),(164,'Mattea','Marvins','MMARVINS','011.44.1346.329268','SA_REP',7200.00,0.10,147,80,'2014-03-05 00:00:00'),(165,'David','Lee','DLEE','011.44.1346.529268','SA_REP',6800.00,0.10,147,80,'2014-03-05 00:00:00'),(166,'Sundar','Ande','SANDE','011.44.1346.629268','SA_REP',6400.00,0.10,147,80,'2014-03-05 00:00:00'),(167,'Amit','Banda','ABANDA','011.44.1346.729268','SA_REP',6200.00,0.10,147,80,'2014-03-05 00:00:00'),(168,'Lisa','Ozer','LOZER','011.44.1343.929268','SA_REP',11500.00,0.25,148,80,'2014-03-05 00:00:00'),(169,'Harrison','Bloom','HBLOOM','011.44.1343.829268','SA_REP',10000.00,0.20,148,80,'2014-03-05 00:00:00'),(170,'Tayler','Fox','TFOX','011.44.1343.729268','SA_REP',9600.00,0.20,148,80,'2014-03-05 00:00:00'),(171,'William','Smith','WSMITH','011.44.1343.629268','SA_REP',7400.00,0.15,148,80,'2014-03-05 00:00:00'),(172,'Elizabeth','Bates','EBATES','011.44.1343.529268','SA_REP',7300.00,0.15,148,80,'2014-03-05 00:00:00'),(173,'Sundita','Kumar','SKUMAR','011.44.1343.329268','SA_REP',6100.00,0.10,148,80,'2014-03-05 00:00:00'),(174,'Ellen','Abel','EABEL','011.44.1644.429267','SA_REP',11000.00,0.30,149,80,'2014-03-05 00:00:00'),(175,'Alyssa','Hutton','AHUTTON','011.44.1644.429266','SA_REP',8800.00,0.25,149,80,'2014-03-05 00:00:00'),(176,'Jonathon','Taylor','JTAYLOR','011.44.1644.429265','SA_REP',8600.00,0.20,149,80,'2014-03-05 00:00:00'),(177,'Jack','Livingston','JLIVINGS','011.44.1644.429264','SA_REP',8400.00,0.20,149,80,'2014-03-05 00:00:00'),(178,'Kimberely','Grant','KGRANT','011.44.1644.429263','SA_REP',7000.00,0.15,149,NULL,'2014-03-05 00:00:00'),(179,'Charles','Johnson','CJOHNSON','011.44.1644.429262','SA_REP',6200.00,0.10,149,80,'2014-03-05 00:00:00'),(180,'Winston','Taylor','WTAYLOR','650.507.9876','SH_CLERK',3200.00,NULL,120,50,'2014-03-05 00:00:00'),(181,'Jean','Fleaur','JFLEAUR','650.507.9877','SH_CLERK',3100.00,NULL,120,50,'2014-03-05 00:00:00'),(182,'Martha','Sullivan','MSULLIVA','650.507.9878','SH_CLERK',2500.00,NULL,120,50,'2014-03-05 00:00:00'),(183,'Girard','Geoni','GGEONI','650.507.9879','SH_CLERK',2800.00,NULL,120,50,'2014-03-05 00:00:00'),(184,'Nandita','Sarchand','NSARCHAN','650.509.1876','SH_CLERK',4200.00,NULL,121,50,'2014-03-05 00:00:00'),(185,'Alexis','Bull','ABULL','650.509.2876','SH_CLERK',4100.00,NULL,121,50,'2014-03-05 00:00:00'),(186,'Julia','Dellinger','JDELLING','650.509.3876','SH_CLERK',3400.00,NULL,121,50,'2014-03-05 00:00:00'),(187,'Anthony','Cabrio','ACABRIO','650.509.4876','SH_CLERK',3000.00,NULL,121,50,'2014-03-05 00:00:00'),(188,'Kelly','Chung','KCHUNG','650.505.1876','SH_CLERK',3800.00,NULL,122,50,'2014-03-05 00:00:00'),(189,'Jennifer','Dilly','JDILLY','650.505.2876','SH_CLERK',3600.00,NULL,122,50,'2014-03-05 00:00:00'),(190,'Timothy','Gates','TGATES','650.505.3876','SH_CLERK',2900.00,NULL,122,50,'2014-03-05 00:00:00'),(191,'Randall','Perkins','RPERKINS','650.505.4876','SH_CLERK',2500.00,NULL,122,50,'2014-03-05 00:00:00'),(192,'Sarah','Bell','SBELL','650.501.1876','SH_CLERK',4000.00,NULL,123,50,'2014-03-05 00:00:00'),(193,'Britney','Everett','BEVERETT','650.501.2876','SH_CLERK',3900.00,NULL,123,50,'2014-03-05 00:00:00'),(194,'Samuel','McCain','SMCCAIN','650.501.3876','SH_CLERK',3200.00,NULL,123,50,'2014-03-05 00:00:00'),(195,'Vance','Jones','VJONES','650.501.4876','SH_CLERK',2800.00,NULL,123,50,'2014-03-05 00:00:00'),(196,'Alana','Walsh','AWALSH','650.507.9811','SH_CLERK',3100.00,NULL,124,50,'2014-03-05 00:00:00'),(197,'Kevin','Feeney','KFEENEY','650.507.9822','SH_CLERK',3000.00,NULL,124,50,'2014-03-05 00:00:00'),(198,'Donald','OConnell','DOCONNEL','650.507.9833','SH_CLERK',2600.00,NULL,124,50,'2014-03-05 00:00:00'),(199,'Douglas','Grant','DGRANT','650.507.9844','SH_CLERK',2600.00,NULL,124,50,'2014-03-05 00:00:00'),(200,'Jennifer','Whalen','JWHALEN','515.123.4444','AD_ASST',4400.00,NULL,101,10,'2016-03-03 00:00:00'),(201,'Michael','Hartstein','MHARTSTE','515.123.5555','MK_MAN',13000.00,NULL,100,20,'2016-03-03 00:00:00'),(202,'Pat','Fay','PFAY','603.123.6666','MK_REP',6000.00,NULL,201,20,'2016-03-03 00:00:00'),(203,'Susan','Mavris','SMAVRIS','515.123.7777','HR_REP',6500.00,NULL,101,40,'2016-03-03 00:00:00'),(204,'Hermann','Baer','HBAER','515.123.8888','PR_REP',10000.00,NULL,101,70,'2016-03-03 00:00:00'),(205,'Shelley','Higgins','SHIGGINS','515.123.8080','AC_MGR',12000.00,NULL,101,110,'2016-03-03 00:00:00'),(206,'William','Gietz','WGIETZ','515.123.8181','AC_ACCOUNT',8300.00,NULL,205,110,'2016-03-03 00:00:00');

/*Table structure for table `jobs` */

DROP TABLE IF EXISTS `jobs`;

CREATE TABLE `jobs` (
  `job_id` varchar(10) NOT NULL,
  `job_title` varchar(35) DEFAULT NULL,
  `min_salary` int(6) DEFAULT NULL,
  `max_salary` int(6) DEFAULT NULL,
  PRIMARY KEY (`job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

/*Data for the table `jobs` */

insert  into `jobs`(`job_id`,`job_title`,`min_salary`,`max_salary`) values ('AC_ACCOUNT','Public Accountant',4200,9000),('AC_MGR','Accounting Manager',8200,16000),('AD_ASST','Administration Assistant',3000,6000),('AD_PRES','President',20000,40000),('AD_VP','Administration Vice President',15000,30000),('FI_ACCOUNT','Accountant',4200,9000),('FI_MGR','Finance Manager',8200,16000),('HR_REP','Human Resources Representative',4000,9000),('IT_PROG','Programmer',4000,10000),('MK_MAN','Marketing Manager',9000,15000),('MK_REP','Marketing Representative',4000,9000),('PR_REP','Public Relations Representative',4500,10500),('PU_CLERK','Purchasing Clerk',2500,5500),('PU_MAN','Purchasing Manager',8000,15000),('SA_MAN','Sales Manager',10000,20000),('SA_REP','Sales Representative',6000,12000),('SH_CLERK','Shipping Clerk',2500,5500),('ST_CLERK','Stock Clerk',2000,5000),('ST_MAN','Stock Manager',5500,8500);

/*Table structure for table `locations` */

DROP TABLE IF EXISTS `locations`;

CREATE TABLE `locations` (
  `location_id` int(11) NOT NULL AUTO_INCREMENT,
  `street_address` varchar(40) DEFAULT NULL,
  `postal_code` varchar(12) DEFAULT NULL,
  `city` varchar(30) DEFAULT NULL,
  `state_province` varchar(25) DEFAULT NULL,
  `country_id` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3201 DEFAULT CHARSET=gb2312;

/*Data for the table `locations` */

insert  into `locations`(`location_id`,`street_address`,`postal_code`,`city`,`state_province`,`country_id`) values (1000,'1297 Via Cola di Rie','00989','Roma',NULL,'IT'),(1100,'93091 Calle della Testa','10934','Venice',NULL,'IT'),(1200,'2017 Shinjuku-ku','1689','Tokyo','Tokyo Prefecture','JP'),(1300,'9450 Kamiya-cho','6823','Hiroshima',NULL,'JP'),(1400,'2014 Jabberwocky Rd','26192','Southlake','Texas','US'),(1500,'2011 Interiors Blvd','99236','South San Francisco','California','US'),(1600,'2007 Zagora St','50090','South Brunswick','New Jersey','US'),(1700,'2004 Charade Rd','98199','Seattle','Washington','US'),(1800,'147 Spadina Ave','M5V 2L7','Toronto','Ontario','CA'),(1900,'6092 Boxwood St','YSW 9T2','Whitehorse','Yukon','CA'),(2000,'40-5-12 Laogianggen','190518','Beijing',NULL,'CN'),(2100,'1298 Vileparle (E)','490231','Bombay','Maharashtra','IN'),(2200,'12-98 Victoria Street','2901','Sydney','New South Wales','AU'),(2300,'198 Clementi North','540198','Singapore',NULL,'SG'),(2400,'8204 Arthur St',NULL,'London',NULL,'UK'),(2500,'Magdalen Centre, The Oxford Science Park','OX9 9ZB','Oxford','Oxford','UK'),(2600,'9702 Chester Road','09629850293','Stretford','Manchester','UK'),(2700,'Schwanthalerstr. 7031','80925','Munich','Bavaria','DE'),(2800,'Rua Frei Caneca 1360 ','01307-002','Sao Paulo','Sao Paulo','BR'),(2900,'20 Rue des Corps-Saints','1730','Geneva','Geneve','CH'),(3000,'Murtenstrasse 921','3095','Bern','BE','CH'),(3100,'Pieter Breughelstraat 837','3029SK','Utrecht','Utrecht','NL'),(3200,'Mariano Escobedo 9991','11932','Mexico City','Distrito Federal,','MX');

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

2.1 基础查询

# 基础查询
/*
语法:
select 查询列表
from 表名;
查询列表可以是表中的字段、常量值、表达式、函数
*/
USE myemployees;
#1.查询表中的某个字段
SELECT last_name,salary FROM employees;
SELECT `first_name`,`email` FROM employees;

#2.查询所有字段
SELECT * FROM employees;

#3.查询常量值
SELECT 100;
SELECT 'john';

#4.查询表达式
SELECT 100-96;

#5.查询函数
SELECT VERSION();

#6.起别名，如果要查询的字段有聪明的情况，可以区分
SELECT 100%98 AS 结果;
SELECT last_name "姓",first_name 名 FROM employees;

#7.去重
SELECT DISTINCT department_id FROM employees;

#8.+号的作用，运算符
SELECT last_name+first_name AS 姓名 FROM employees;
SELECT CONCAT(last_name," ",first_name) FROM employees;
SELECT CONCAT(`first_name`,",",IFNULL(`commission_pct`,0)) FROM employees;

2.2 条件查询

#条件查询
/*
语法：
select 查询列表  ③
from 表名   ①
where 筛选条件;  ②
1.按条件表达式筛选 条件运算符(< > <> >= <=)
2.逻辑表达式筛选 逻辑运算符 and or not
3.模糊查询 like, between and, in, is null, is not null
like和通配符一起使用,% 任意多个字符 _ 任意单个字符

*/

#1.
SELECT * FROM employees WHERE salary>12000;
SELECT last_name,department_id FROM employees WHERE department_id <> 90;

#2.
SELECT last_name,salary,commission_pct FROM employees WHERE salary>10000 AND salary<20000;
SELECT * FROM employees WHERE NOT(department_id >=90 AND department_id <=110) OR salary >15000;

#3.
SELECT * FROM employees WHERE last_name LIKE '%a%';
SELECT last_name,salary FROM employees WHERE last_name LIKE '__n_l%';
SELECT * FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';

SELECT * FROM employees WHERE employee_id BETWEEN 80 AND 120;

SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL;
SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NOT NULL;

#安全等于<=>
SELECT * FROM employees WHERE salary <=> 12000;

2.3 排序查询

# 排序查询
/*
语法：
select 查询列表  ③
from 表  ①
[where 筛选条件]  ②
order by 排序列表[asc|desc]  ④
order by字句中可以支持单个字段、多个字段、表达式、函数、别名
order by一般是放在查询语句的最后，limit除外
*/
SELECT * FROM employees ORDER BY salary ASC;

SELECT * FROM employees WHERE department_id >= 90 ORDER BY hiredate ASC;

SELECT last_name,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees 
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) ASC; 

SELECT last_name,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees 
ORDER BY 年薪 ASC; 

#按姓名长度排序
SELECT LENGTH(last_name) 长度,last_name FROM employees ORDER BY 长度 DESC;

SELECT * FROM employees ORDER BY salary DESC,employee_id ASC;

SELECT last_name,salary FROM employees 
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;

2.4 常见函数

#常见函数
/*
select 函数名(实参列表) [from 表];
分类：1.单行函数，concat、length、ifnull
2.分组函数(统计函数)，做统计使用
*/

#1.单行函数-字符函数
#length
SELECT LENGTH('张hahaha'); 
SHOW VARIABLES LIKE '%char%';

#concat 拼接字符串
SELECT CONCAT(last_name,'_',first_name) FROM employees;

#upper、lower
SELECT UPPER('abcd');
SELECT CONCAT(UPPER(last_name),' ',LOWER(first_name)) FROM employees;

#substr/substring,索引从1开始
SELECT SUBSTR('我爱中国爱',3,2);

SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) 
FROM employees;

#instr返回字串第一次出现的索引
SELECT INSTR('我中国爱中国爱','中国');

#trim去前后空格
SELECT TRIM('a' FROM 'abaaac    ');

# lpad，rpad指定的字符左、右填充指定长度
SELECT LPAD('该大大',10,'*');
SELECT RPAD('dddd',12,'c');

#replace 替换
SELECT REPLACE('百度百度百度','百','千');

#2.数学函数
#round 四舍五入
SELECT ROUND(-1.356,2);

#ceil 向上取整
SELECT CEIL(-1.0001);

#floor向下取整
SELECT FLOOR(-9.999);

#truncate 截断
SELECT TRUNCATE(1.699,1);

#mod取模
SELECT MOD(-10,-3);

#3.日期函数
#now
SELECT NOW();

#curdate/curtime
SELECT CURDATE();
SELECT YEAR(NOW()); 
SELECT MONTHNAME(NOW())

#str_to_date 
SELECT STR_TO_DATE('1996-7-4','%Y-%c-%d');
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');

#date_format日期转换为字符
SELECT DATE_FORMAT(NOW(),'%y-%m%d');
SELECT last_name,DATE_FORMAT(hiredate,'%m/%d %Y')
FROM employees
WHERE commission_pct IS NOT NULL;

#4.其他函数
SELECT DATABASE();
SELECT USER();

#5.流程控制函数
#if
SELECT IF(10>5,'大','小');
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金','有奖金')
FROM employees;

#case
SELECT salary,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END
FROM employees;

SELECT salary,
CASE
WHEN salary>20000 THEN 'a'
WHEN salary>10000 THEN 'b'
ELSE 'c'
END
FROM employees;

2.5 分组函数

/*分组函数，用作统计使用，又称为聚合函数或组函数
分类:sum 、avg、max、min、count
1.sum、avg处理数值型
max、min、count可以处理任何类型
2.以上分组函数都忽略null值
3.可以和distinct搭配去重
和分组函数一同查询的字段要求是group by后的字段
*/
//1.
SELECT SUM(salary) FROM employees;
SELECT AVG(salary),MAX(last_name),MIN(last_name) FROM employees;
SELECT COUNT(commission_pct) FROM employees;

//2.
SELECT SUM(commission_pct),AVG(commission_pct),SUM(commission_pct)
/35,SUM(commission_pct)/107 FROM employees;

//3.
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary) FROM employees;

//4.count的使用
SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees;
/*效率：
MYISAM引擎下，*的效率高
INNODB下，1和*差不多 
*/

2.6 分组查询

# 分组查询
/*
语法：
select 分组函数，列（出现在group by的后面）
from 表
[where 筛选条件]
group by 分组的列表
[order by 字句]
查询列表比较特殊
分组查询中的筛选条件分为两类：
               数据源      位置
分组前筛选     原始表      where
分组后筛选     结果集      having
①分组函数做条件肯定是放在having子句中
②能用where的优先考虑where
*/
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;

SELECT location_id,COUNT(*)
FROM departments
GROUP BY location_id;

SELECT AVG(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

#分组后的筛选
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;

SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary) > 12000;

SELECT manager_id,MIN(salary)
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000

SELECT COUNT(*),LENGTH(last_name)
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*) > 5;

#按多个字段分组
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id;

#添加排序
SELECT department_id,job_id,AVG(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id
HAVING AVG(salary)>10000
ORDER BY AVG(salary) DESC;

2.7 连接查询

#连接查询
/*
又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积线程：表1 m行，表2 n行，结果m*n行
分类：
按年代分类：
	sql92 仅支持内连接
	sql99（推荐） 仅不支持全外连接
按功能分类：
	内连接：
		等值连接
		非等值连接
		自连接
	外连接：
		左外连接
		右外连接
		全外连接
	交叉连接
*/

# sql92标准
#1.等值连接
#①连接的结果为多表的交集部分
#②n表连接需要n-1个连接条件
#③多表的顺序没有要求，一般要为表起别名 
SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;

#为表起别名，起了别名，就不能用原来的表明去限定
SELECT last_name,e.job_id,job_title
FROM employees AS e,jobs j
WHERE e.`job_id`=j.`job_id`;

#加筛选
SELECT last_name,department_name,commission_pct
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`
AND employees.`commission_pct` IS NOT NULL;

SELECT department_name,city
FROM departments d,locations l
WHERE d.`location_id` = l.`location_id`
AND city LIKE '_o%'

#加分组
SELECT city,COUNT(*)
FROM departments,locations
WHERE departments.`location_id`=locations.`location_id`
GROUP BY city;

SELECT department_name,d.`manager_id`,MIN(salary)
FROM departments d,employees e
WHERE d.`department_id`=e.`department_id`
AND commission_pct IS NOT NULL
GROUP BY department_name,d.manager_id;

#可以加排序
SELECT job_title,COUNT(*) c
FROM employees,jobs
WHERE employees.`job_id`=jobs.`job_id`
GROUP BY job_title
ORDER BY c DESC;

#三表连接
SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`;

#2.非等值连接
CREATE TABLE job_grades
(grade_level VARCHAR(3),
lowest_sal INT,
highest_sal INT);
INSERT INTO job_grades
VALUES ('A',1000,2999);
INSERT INTO job_grades
VALUES ('B',3000,5999);
INSERT INTO job_grades
VALUES ('C',6000,9999);
INSERT INTO job_grades
VALUES ('D',10000,14999);
INSERT INTO job_grades
VALUES ('E',15000,24999);
INSERT INTO job_grades
VALUES ('F',25000,40000);

#SELECT * from job_grades;

SELECT salary,grade_level
FROM employees e,job_grades j
WHERE salary BETWEEN j.lowest_sal AND j.highest_sal;

#3.自连接，自己连接自己
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;

#sql99标准
/*
语法：select 查询列表
from 表1 别名 [连接类型inner left right fll cross] 
join 表2 别名
on 连接条件
where 筛选条件
[where/group/having/order by]
*/
# 内连接inner
# 1.等值连接
#①inner可以省略
#②inner join连接和sql92的等价连接效果是一样的，都是查询交集
SELECT last_name,department_name
FROM employees INNER JOIN departments
ON employees.`department_id`=departments.`department_id`;

#加筛选
SELECT last_name,job_title
FROM employees e 
INNER JOIN jobs j
ON e.`job_id`=j.`job_id`
WHERE e.`last_name` LIKE '%e%';

#加分组
SELECT city,COUNT(*) 部门个数
FROM departments d INNER JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*) >3;

#加排序
SELECT department_name,COUNT(*) 员工个数
FROM departments d INNER JOIN employees e
ON d.`department_id`=e.`department_id`
GROUP BY department_name
HAVING COUNT(*) > 3
ORDER BY COUNT(*) DESC;

#三表连接
SELECT last_name,department_name,job_title
FROM employees e INNER JOIN departments d
ON e.`department_id`=d.`department_id`
INNER JOIN jobs j ON e.`job_id`=j.`job_id`;

#2.非等值连接
SELECT salary,grade_level
FROM employees e 
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

#3.自连接
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.`manager_id`=m.`employee_id`;

#外连接
/*
应用场景：用于查询一个表有，另一个表没有的记录
① 外连接的查询结果为主表中的所有记录，从表没有匹配的，显示null
② 左外连接，left左边是主表
  右外连接，reight右边的是主表
*/
#查询哪个部门没有员工
#左外
SELECT d.*,e.employee_id
FROM departments d
LEFT JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE e.employee_id IS NULL;
#右外
SELECT d.*,e.employee_id
FROM employees e 
RIGHT JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE e.`employee_id` IS NULL;

#交叉连接，就是个笛卡尔积

2.8 子查询

#子查询，出现在其他语句中的select语句，或称为内查询
/*
外部的查询，称为主查询或外查询
分类：
按子查询出现的位置；
	SELECT后 + 标量
	FROM后 + 表子查询
	WHERE或HAVING后 + 标量/列/行，重点
	EXISTS后 + 表
按结果集的行列数不同：
	标量子查询（结果集只有一行一列）
	列子查询（结果集只有一列多行）IN/ANY/SOME/ALL
	行子查询（结果集一行多列）
	表子查询（多行多列）
子查询都放在小括号内，一般放在条件的右侧
*/

#1.where或having后

#标量子查询（单行子查询）

SELECT *
FROM employees
WHERE salary>(
	SELECT salary
	FROM employees
	WHERE last_name='Abel');

SELECT last_name,job_id,salary
FROM employees
WHERE salary>(
	SELECT salary
	FROM employees
	WHERE employee_id=143
)AND job_id =(
	SELECT job_id
	FROM employees
	WHERE employee_id=141);

SELECT last_name,job_id,MIN(salary)
FROM employees
WHERE salary=(
	SELECT MIN(salary)
	FROM employees);



SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT MIN(salary)
	FROM employees
	WHERE department_id=50);
#列子查询（多行子查询）

SELECT last_name,department_id
FROM employees
WHERE department_id IN(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN (1400,1700));
	
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ANY(
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
#行子查询（90多列多行，使用较少）
SELECT *
FROM employees
WHERE (employee_id,salary)=(
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
);

 #2.select后
SELECT d.*,(
	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id = d.`department_id`
) 个数 FROM departments d;

#3.from后，将子查询的结果充当一张表，必须起别名
SELECT avg_dep.*,g.`grade_level`
FROM (
	SELECT department_id,AVG(salary) ag
	FROM employees
	GROUP BY department_id	
)avg_dep
INNER JOIN job_grades g
ON avg_dep.ag BETWEEN g.`lowest_sal` AND g.`highest_sal`;

#4.exists后，相关子查询结果1或0
SELECT department_name 
FROM departments d
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE d.`department_id`=e.`department_id`
);

2.9 分页查询

#分页查询
/*
语法：
select 查询列表
from 表名
[join type join 表2
on 连接条件
where筛选条件
group by分组字段
having分组后的筛选
order by 排序的字段]
limit [offset起始索引(从0开始),]size;
①limit放在最后，limit (page-1)*size,size;
*/

SELECT * FROM employees
LIMIT 5;

SELECT * FROM employees
LIMIT 10,15;

SELECT * FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10;

2.10 union联合查询

#联合查询union
/*
将多条查询语句的结果合并成一个结果
语法：
查询语句1 union[all] 查询语句2....
要查询的结果来自于多个表，多个表没有直接的连接关系
①多表列数必须一致
②UNION默认去重union all包含重复项
*/

SELECT * FROM employees WHERE email LIKE '%a%' 
UNION
SELECT * FROM employees WHERE department_id>90;
盖大大
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
《PostgreSQL技术内幕-查询优化深度探索》节选之《查询树》
Greenplum中文社区
09-07 659
...
job历史执行记录查询 oracle_Oraclejob情况的查看处理
weixin_31948907的博客
02-26 3736
注意：当一个job的执行周期是3分钟执行一次，而你设参数job_queue_interval=600(10分钟)，而你只有一个SNP进程的话，你的这个JOBexec dbms_job.broken(23,false);commit;select t.*, t.rowid from dba_jobs t;select t.*, t.rowid from sys.user_jobs t;select ...
my sql四舍五入_SQL查询四舍五入 解决方法
weixin_35794280的博客
12-24 213
方法1：SELECT CAST('123.456' as decimal) 将会得到 123(小数点后面的将会被省略掉)。如果希望得到小数点后面的两位。则需要把上面的改为SELECT CAST('123.456' as decimal(38, 2)) ===>123.46说明1:这里的decimal英文为: 小数, 十进制decimal(38,2)这里的38是这个小数的位数有多少位, 一般最...
job历史执行记录查询 oracle_Oracle诊断案例-Job任务停止执行
weixin_39799646的博客
12-22 439
Oracle诊断案例-Job任务停止执行昨天接到研发人员报告，数据库定时任务未正常执行，导致某些操作失败。开始介入处理该事故.系统环境:SunOS DB 5.8 Generic_108528-21 sun4u sparc SUNW,Ultra-4Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production1.首先介入检查数据库任务$ sqlp...
job历史执行记录查询 oracle_Oracle数据库定时任务配置和日志执行情况查询
weixin_32419609的博客
01-13 2677
基础配置：/******************************************************************* 移动抵扣券快到期推送提醒* 首次执行 : 2016-09-19 10:00:00* 定期执行 : 每天10:00:00*******************************************************************...
mysql-connector-java-8.0.20.jar
08-13
总之，`mysql-connector-java-8.0.20.jar`是Java开发者连接MySQL 8.0数据库的重要工具，它提供了高效、稳定的数据库访问能力，支持最新的MySQL特性和优化，为Java应用程序与MySQL数据库之间的交互奠定了坚实的基础。
mysql-connector-j-8.0.31
12-23
1. **MySQL Connector/J介绍** MySQL Connector/J是MySQL公司开发的开源JDBC驱动，符合JDBC 4.2规范。它提供了与Java应用程序交互的接口，使开发者可以利用Java语言进行数据库操作，如查询、插入、更新和删除数据。...
mysql-connector-java-5.1.49.jar
01-21
MySQL Connector/J 5.1.49 是一个用于Java应用程序的数据库驱动程序，它使得Java开发者能够连接到MySQL数据库服务器并...在实际项目中，正确配置和使用这个驱动是保证Java应用与MySQL数据库之间高效、安全通信的基础。
mysql-connector-java-8.0.16.jar
01-20
MySQL Connector/J 8.0.16 是MySQL数据库与Java应用程序之间的重要桥梁，它是一个用于连接Java应用程序到MySQL服务器的JDBC驱动程序。这个jar文件是MySQL官方提供的，确保了与MySQL数据库的高效、安全和可靠的连接。...
mysql-8.0.21-winx64
09-21
MySQL是世界上最受欢迎的开源数据库系统之一，尤其在Web应用程序中被广泛应用。`mysql-8.0.21-winx64` 是一个针对Windows 64位系统的...在实际应用中，了解如何安装、配置和管理MySQL是每个IT专业人员的基础技能之一。
查看Job执行的历史记录
weixin_33877092的博客
08-17 2793
查看Job执行的历史记录 SQL Server将Job的信息存放在msdb中，Schema是dbo，表名以“sysjob”开头。 一，基础表 1, 查看Job和Step，Step_ID 是从1 开始的。 select j.job_id,j.name,j.enabled,j.description, j.start_step_id,j...
job历史执行记录查询 oracle_ORACLE查询JOB信息及JOB创建
weixin_36485139的博客
02-26 4263
1、查询系统JOBselect job,what,to_char(next_date, 'yyyy/mm/dd hh24:mi:ss'),broken,failures,schema_user,log_user,priv_userfrom dba_jobs;2、查询某个JOB的详细信息(JOB名称如果有前缀用户名的要去掉才能查到)select *from user_scheduler_job_lo...
job历史执行记录查询 oracle_oracle job 查询 存储过程
weixin_39938269的博客
01-27 2040
Oracle开发与使用文章收藏收集一些Oracle开发与使用文章,以便于后续查找.【基础类】1.ORACLE函数大全2.Orace常用函数3.oracle 日期函数介绍4.Oracle中常用的to_Char用法详解(有FMT的详细列表)5.ORACLE中日期和时间函数汇总(转载)6.SQL Server和Or...文章长征2号2017-03-061232浏览量Oracle开发与使用文章收藏收集一些...
job历史执行记录查询 oracle_监控Oracle Job运行情况
weixin_39793813的博客
12-31 856
监控Oracle Job运行情况2018-01-02 Python 宅必备关于监控系统我们前面介绍了很多学会了如何使用Django新建网站以及获取数据监控数据至MySQL或redis然后将获得的数据库处理后再前端显示往期可以到我的个人网页查看这期讲如何使用Django批量监控Oracle Job运行情况开发环境操作系统:CentOS 7.3Python版本 :2.7Django版本: 1.10.5...
写一个sql实现以下查询结果_常用SQL系列之（三）:记录叠加、匹配、外连接及笛卡尔等...
weixin_39580041的博客
11-20 350
本系统为@牛旦教育IT课堂在微头条上的内容，为便于查阅，特辑录于此，都是常用SQL基本用法。。前两篇连接：(一)：SQL点滴(查询篇)：数据库基础查询案例实战(二)：SQL点滴(排序篇)：数据常规排序查询实战示例(17)如何实现多表记录的叠加返回？也就是如何将来自多个表的数据组织到一起，把各个表的结果叠加起来。这些表不必要有相同的关键字，但各表对应的列的数据类型应相同。例如我们现实部门编号为2的所...
my sql四舍五入_关于SQL 四舍五入函数的用法讲解
weixin_42109732的博客
12-24 187
--ROUND--返回数字表达式并四舍五入为指定的长度或精度。----语法--ROUND(numeric_e-xpression,length[,function]----参数--numeric_e-xpression----精确数字或近似数字数据类型类别的表达式(bit数据类型除外)。----length----是numeric_e-xpression将要四舍五入的精度。leng...
Oracle定时任务运行记录表,oracle 创办 定时任务job记录
weixin_39963341的博客
04-04 370
oracle 创建 定时任务job记录说明全部用命令行执行1、先查看 job queue的启动参数，SQL> show parameter job;显示：NAME TYPE VALUE------------------------------------ ----------- -------job_queue...
linux 压缩日志文件log.gz直接查看
热门推荐
会哭的雨@的博客
09-09 1万+
直接用zcat就可以查看 zgrep就可以直接查找
【SQLSERVER】查询Job作业基本信息及执行情况
最新发布
德仔
04-02 413
SELECT[job].[job_id] AS '作业唯一标示符',[job].[name] AS '作业名称'END AS '最后执行时间'WHEN 0 THEN '失败'WHEN 1 THEN '成功'WHEN 2 THEN '重试'WHEN 3 THEN '取消'WHEN 4 THEN '正在运行'END AS '最后执行状态'3 , 0 , ':' ), 6 , 0 , ':' ) AS '最后运行持续时间',[jobh].[message] AS '最后运行状态信息'
mysql having count( 1 ) >1_mysql 基础篇-数据查询(1)
06-06
这是一个关于 MySQL 数据库查询中的 HAVING 子句的问题。HAVING 子句用于在 GROUP BY 子句分组后对结果进行过滤。在这个问题中，使用了 COUNT(1) 函数，它用于计算指定列中非 NULL 值的数量。当 COUNT(1) > 1 时，...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值