Tag：SQL statement
-
Explanation of the use example of MSSQL function datename (obtain the SQL statement of the current year, month and day / the day of the year)
MSSQL functiondatename Copy codeThe code is as follows: grammar DATENAME ( datepart , date )Parametersdatepart Is part of the date returned. The following table lists all valid datepart parameters. The user-defined variable equivalent is invalid. dateIs an expression that can be resolved to time, date, smalldatetime, datetime, datetime2, or DateTimeOffset values. Date can be an […]
-
T-SQL / MSSQL SQL statement example of importing data script with command line
Simple usage of OSQL: it is used to execute local scripts. It is suitable for larger SQL scripts and is more convenient to execute Copy codeThe code is as follows: osql -S serverIP -U sa -P 123 -i C:\script.sql IP address or server name of the ServerIP database instance SA is the user 123 is […]
-
MySQL SQL statement operations for adding or modifying primary keys
Add table field alter table table1 add transactor varchar(10) not Null; alter table table1 add id int unsigned not Null auto_increment primary key Modify the field type of a table and specify whether it is empty or non empty Alter table table name change field name field name field type [allow non blank]; Alter table […]
-
The SQL statement insertion result is a mixed example of select and value
Copy codeThe code is as follows: String slctpsql=”select id ,”+uid+”,”+ddd+”,”+score+”,’”+mark+”‘ ,”+markid+” ,”+exam.getId()+” from Test_Paper where testBaseId=(select id from Test_Base where baseTestId=”+judgemap.get(i).getId()+” and baseTestType=1) and paperId=”+paperbaseinfo.getId(); String insertsql=”insert into Test_Paper_Record (t_pid,userId,answer,score,mark,markerId,examid )”+slctpsql;
-
Sample code for creating and deleting constraints using SQL statements
Create and delete constraints using SQL statements Constraint type Primary key constraint: the primary key column data is required to be unique and cannot be empty. Unique constraint: the column is required to be unique and can be null, but only one null value can appear. Check constraint: limit the value range and format of […]
-
Implementation of modifying table name using SQL statement in MySQL
In mysql, you can use the SQL statement rename table to modify the table name. The basic syntax of the SQL statement rename table to modify the table name is: Rename table < old table name > to < new table name >; Let’s change the test table to test1 table. 1. First, check which […]
-
Handling method of error reporting with multiplication sign in SQL statement
In ADO, we need to use multiplication in SQL statements, but after adding ‘*’, the execution program will always make errors. This is because ‘*’ coincides with the ‘*’ keyword in SQL, so compilation will make errors. Solution: put the multiplication operation outside the SQL statement, put the result into a variable, and then reference […]
-
Generate SQL statements of 300 different random numbers
–Generate 300 8-bit non repeating pure digital random numbers DECLARE @i INT=0; DECLARE @j INT; DECLARE @qnum INT=300; — Number of generated random numbers SET NOCOUNT ON CREATE TABLE #temp_Table(num INT) WHILE(@i<@qnum) BEGIN SELECT @j = cast( floor(rand()*(99999999-10000000)+10000000) as int) IF(NOT EXISTS(SELECT num FROM #temp_Table WHERE [email protected] )) BEGIN INSERT #temp_Table (num) VALUES (@j) SET […]
-
Nesting of single quotation marks in SQL statements
In SQL statements, we will inevitably use single quotation mark nesting, but direct nesting is certainly not OK. It is also not OK to use backslash as escape character in Java, and single quotation mark is used as escape character in SQL. For example, the following example is an example of a query in a […]
-
SQL Sever uses SQL statements to merge duplicate row data into one row and separate them with commas
1、 Define table variables Copy codeThe code is as follows: DECLARE @T1 table ( UserID int , UserName nvarchar(50), CityName nvarchar(50) ); Insert into @ T1 (user ID, user name, city name) values (1, ‘a’,’shanghai ‘)Insert into @ T1 (user ID, user name, city name) values (2, ‘B’,’Beijing ‘)Insert into @ T1 (user ID, user […]
-
C # realize the conversion of string string to in followed by parameters in SQL statement
After converting string string to in, you can use parameter code: public string StringToList(string aa) { string bb1 = “(“; if (!string.IsNullOrEmpty(aa.Trim())) { string[] bb = aa.Split(new string[] { “\r\n”, “,”, “;”, “* ” }, StringSplitOptions.RemoveEmptyEntries); for (int i = 0; i < bb.Length; i++) { If (! BB1. Contains (BB [i])) // remove duplicate […]
-
SQL statement for viewing object definition in SQL Server
In addition to viewing the definitions of view and stored procedure in SSMS, you can also use the following statements to query directly: Copy codeThe code is as follows: SELECT object_definition(object_id(‘sys.tables’)); go sp_helptext ‘sys.tables’ go select * from sys.system_sql_modules whereobject_id = object_id(‘sys.tables’) For the following object types:·C = check constraint·D = default (constrained or independent)·P […]