sql server cheat sheet

,inserted.hrd_collecttime) else1end, (Inserted.hrd_exporttemp+deleted.hrd_ EXPORTTEMP)/casewhendaTediff (hour,deleted.hrd_collecttime,inserted.hrd_collecttime) >0thendatediff (hour,deleted.hrd_ Collecttime,inserted.hrd_collecttime) else1end, (Inserted.hrd_cumflux-deleted.hrd_cumflux), 0fromdeletedinnerjoininsertedondeleted. Hrd_meternum=inserted. Hrd_meternumwheredatediff (hour,deleted.hrd_collecttime,inserted.hrd_collecttime) Add in the Code deleted data table and inserted data table, for the s

there is a database comprising four tables: Student table (Student), Course table (Course), score table (score) and teacher Information sheet (Teacher). The structure of the four tables is shown in table 1-1 table (a ~ table (four), as shown in table 1-2 table (i) ~ table (iv). Create four tables with SQL statements and complete related topics. Create DATABASE CeshiGoUse CeshiGoCREATE TABLE Student(Snochar

the day of the year,DW represents the day of the week and returns an integer default of 1 for SundayGETDATE No parametersSelect GETDATE ()Back to 2009-04-28 18:57:24.153Returns the current system date and time.DayDateSelect Day (' 2007-12-11 ') returns 11Returns an Integer that represents the part of the day of the specified date.Equivalent toDATEPART (dd, date)MonthDateSelect month (' 2007-12-11 ') returns 12Returns an Integer that represents the part of the month of the specified date.Equival

SQL Server queries record averages and sorted statements Query the student's average score and rank, SQL 2000 is completed with a subquery, when the average score is repeated, the position is left vacant and two kinds of positions are not retained. Select t1.*, px = (select count (1) from ( Select m.s# [student number], NBSP;NBSP;NBSP;NBSP;NBSP;N bsp; M.snam

Today, I accidentally uploaded the Excel Data to SQL Server. Yu Feng said that it is essential to keep a low profile to learn a technology. It cannot be known that it was just a meeting ~~~~~~~~ Hey !!! If (Fileupload1.hasfile) // If you have selected the Browse button of the upload control, the file is successfully browsed. { This . Fileupload1.saveas ( " D: \ temp \\ " + Fi

the day of the year,DW represents the day of the week and returns an integer default of 1 for SundayGETDATE No parametersSelect GETDATE ()Back to 2009-04-28 18:57:24.153Returns the current system date and time.DayDateSelect Day (' 2007-12-11 ') returns 11Returns an Integer that represents the part of the day of the specified date.Equivalent toDATEPART (dd, date)MonthDateSelect month (' 2007-12-11 ') returns 12Returns an Integer that represents the part of the month of the specified date.Equival

Power query and power map are the new Excel-oriented functions released by Microsoft on the WPC recently. With these two features, self-help BI makes it easier for you to discover and process data and enrich the data visualization functions. The predecessor of Power query and power map are data explorer and geoflow, both of which are mentioned in SQL Server 2014 ctp1. Combined with power intelligence and po

,state)User gas Consumption chart: Gas_consume (uno,time,gid,num,state)Employee Information Sheet: Employee (WNO,PASSWD)Permission table: Rights (Rightid,comment,function)Employee Permissions Table: Staffrignt (Wno,rightid)Analysis: Initial status information is automatically generated in the energy consumption record when the user registers The Permissions table design contains: Permission number, description and function, when adding pe

Label:There are 2 basic scenarios, all without the need to install OfficeOle DB Read + bulkcopyGet Excel Individual SheetName //Connection Stringstringstrconn ="provider=microsoft.jet.oledb.4.0;"+"Data source="+ Path +";"+"Extended Properties=excel 8.0;"; OleDbConnection Conn=NewOleDbConnection (strconn); Conn. Open (); //returns the schema for Excel, including the name, type, creation time, and modification time of each sheet tableDataTable dtsheetna

Tags: selectSimple Query SelectSimple data retrieval: SELECT * from T_employeeRetrieve only required columns: SELECT fname,fage from T_employeeColumn aliases: SELECT fnumber as number, FName as name, Fage as Age111 from T_employeeUse where to retrieve qualifying data: SELECT FName from T_employee WHERE fsalaryUse like fuzzy query SELECT FName from T_employee WHERE FName like ' sheet%Select can also retrieve data that is not associated with any table:

Tags: tab new create BSP ALT data SEL will not Ida CSV stands for comma-separated values, sometimes referred to as comma-separated values. and If the TXT file is loaded, then the file should have a comma-separated value. And the file should be like this Here is the script to create the table: CREATE TABLE Employee ( Id int, Name VARCHAR (100), Designation VARCHAR (100) ) I created the location "f a txt and CSV file: \ \ mypublis \ \ 's TXT file name is TestToInsert.txt Now run the following s

Management databaseGoInsert into incoming information table, select product number, purchase price, quantity of goods, purchase amount, arrival date from arrival information table where incoming quantity >300 -- modifying table data --Update the data table name to be modified set field name = new Value [, ...] [From data table list] [Where Condition expression] Cases:Use Commodity Management databaseGoUpdate customer Information table Set mailbox = ' [email protected] ' where customer number =

Tags: action single table res multiple table list set text table structureAA (Id,name) BB (ID. Address) A change 1 Multi-table change Change the address of name to Dawu; Update AA A INNER join BB B on a.id=b.id Set b.address= ' Dawu ' where a.name= ' name ' 2 single Table Update AA set aa.names= ' where XXX Second deletion 1 drop DROP TABLE TableName drop table AA Deletes the content and definition, freeing up space. In short, the whole table AA is removed. It is not possible to add data in the

Data sheet: Bus_visitorstatics1, open the window function realization:SELECT*FROM (SELECT*, Row_number () OVER (partitionbyvisitordateorderbycountdesc ) AS ' num ' FROM BUS_VisitorStatics) astb1where NBSP;NBSP;NBSP;TB1.NUMNBSP; SQL Server records how many pages to fetch top N data

First, create a practice database;Create a student information sheet studentThe column names were:Code (INT)Name (varchar ()) NOT NULLBirth (varchar ()) NOT NULLSex (char ()) NOT NULLScoore (Decimal (18,2))Insert Information:Zhang San, John Doe, Harry, Zhao Liu, Feng Qi, Liu Ba1) All inquiries2) query a column (name)3) query multiple columns (name, gender, birthday)4) query one row according to the criteria (code=2)5) Find a data based on the criteria

number and average salary;Select department_id department number, AVG (SALARY) Average salary from EMPLOYEES GROUP by DEPARTMENT_ID have avg (SALARY) >=7500--2) Check the staff table, the minimum wage of 8000 or more of the department number and minimum salary;Select department_id department number, min (SALARY) minimum salary from EMPLOYEES GROUP by department_id have MIN (SALARY) >8000Go--Vi. use of the INTO clause:---------------------------------------------------1 Job Information Table job

Tags: student data sheet attributes are not larger than share image technology share date 51ctoCreation of tables and use of T-SQL statements一，表的创建与基本概念 表是包含数据库中所有数据的数据库对象，表定义是一个集合。数据在表中组织方式与在电子表格中相似，都是按行和列的格式组织的。每一行代表一条唯一的记录，每一列代表记录中的一个字段。 **SQLserver 中的表包含下列主要组件 ** #列： 每一列代表由表的建模的对象的某个属性，列如，一个产品表有id 列，颜色列和重量列 #行

Tags: store pos ring database files Apt LTE using nbsp orderOne. SQL Server database commands 1. Database (1) Database creation Create DATABASE name On primary ( Name= ' database name ', Filename= ' Database full path and database name, database file suffix. mdf ', ) Log on ( Name= ', Filename= ' database log file full path and database log file name, database log file suffix. ldf ' ) (2) Database deletion

.--Trigger Management----Create a trigger(Create Trigger)--Note: In SQL Server, triggers can be created using the "organizer" and the T-SQL language two ways.executing a trigger is actually the trigger command (INSERT, update, and delete) when the trigger is defined。CREATE TRIGGER Trigger nameThe--create trigger command represents the creation of a trigger, which

Tags: oat dynamics--print tab UNP object_id for tar Row and column conversions: Name Course Score Zhang San language 74 Zhang San Mathematics 83 Zhang San Physics 93 John Doe Language 74 John Doe Mathematics 84 John Doe Physics 94 Want to become (get the following result): Name Chinese mathematics Physics ---------------- Lee 474 84 94 Sheet 374 83 93 Create Table SC (name varchar (10),