Database 问题

1. long string inserted into sql server.

insert into tbtest (text) values ('abc\'ff\r\ndef')

 

2. district 与 orderby 连用

sql="select distinct id from test order by otherfield desc"  

需要找到不同的id,同时又想让记录按fbsj排序。但是这样一定会出现错误,出现distinct与order by冲突问题。如何来得到完美的解决呢,看下面的方法,依然用一条sql语句

sql="select id from test group by id order by max(otherfield) desc" 

用group by 代替disitnct实现找不同id的功能。

 

3. T-SQL

exec sp_columns CUSTOMERS

DROP TABLE table_name;

Exec sp_columns CUSTOMERS;

INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]   
VALUES (value1, value2, value3,...valueN); 

SELECT ID, NAME, AGE, AMOUNT 
   FROM CUSTOMERS, ORDERS 
   WHERE  CUSTOMERS.ID = ORDERS.CUSTOMER_ID 
OR 
SELECT A.ID, A.NAME, A.AGE, B.AMOUNT 
   FROM CUSTOMERS A inner join  ORDERS B on A.ID = B.Customer_ID

DELETE FROM CUSTOMERS 
   WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP WHERE AGE >=27 )

CREATE PROCEDURE SelectCustomerstabledata 
AS 
SELECT * FROM Testdb.Customers 
GO//?

Select ABS(-22)

SELECT salary from customers where salary between 2000 and 8500


4. distinct 当dataType 类型是text时， 不可用。
如email为text类型，可以改成：select distinct customerId, '' as email from customerDB where customername='jane'

5. stored procedure（sp）在JAva中可以用

mysql > DELIMITER //   
mysql > CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int)   
-> BEGIN 
-> SELECT p_inout;  
-> SET p_inout=2;  
-> SELECT p_inout;   
-> END;  
-> //   
mysql > DELIMITER ; 

DECLARE l_int int unsigned default 4000000;  
DECLARE l_numeric number(8,2) DEFAULT 9.95;  
DECLARE l_date date DEFAULT '1999-12-31';  
DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';  
DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded'

SET @y='Goodbye Cruel World';  

SELECT 'Hello World' into @x;  #
https://blog.csdn.net/weixin_34248258/article/details/93056135