当前位置: 澳门新濠3559 > 数据库 > 正文

如果想获取查询语句中的一个字段值可以用sele

时间:2019-10-06 19:48来源:数据库

mysql中变量赋值,mysql变量赋值

 

sql server中变量要先申明后赋值:

局部变量用一个@标识,全局变量用两个@(常用的全局变量一般都是已经定义好的);

申明局部变量语法:declare @变量名 数据类型;例如:declare @num int;

赋值:有两种方法式(@num为变量名,value为值)

set @num=value;   或   select @num=value;

如果想获取查询语句中的一个字段值可以用select给变量赋值,sql server中变量要先申明后赋值。如果想获取查询语句中的一个字段值可以用select给变量赋值,如下:

select @num=字段名 from 表名 where ……

mysql中变量不用事前申明,在用的时候直接用“@变量名”使用就可以了。

第一种用法:set @num=1; 或set @num:=1; //这里要使用变量来保存数据,直接使用@num变量

第二种用法:select @num:=1; 或 select @num:=字段名 from 表名 where ……

注意上面两种赋值符号,使用set时可以用“=”或“:=”,但是使用select时必须用“:=赋值”

 

=================

 

Chapter 14. HQL: The Hibernate Query Language

14.1. Case Sensitivity

14.2. The from clause

14.3. Associations and joins

14.4. Forms of join syntax

14.5. Referring to identifier property

14.6. The select clause

14.7. Aggregate functions

14.8. Polymorphic queries

14.9. The where clause

14.10. Expressions

14.11. The order by clause

14.12. The group by clause

14.13. Subqueries

14.14. HQL examples

14.15. Bulk update and delete

14.16. Tips & Tricks

14.17. Components

14.18. Row value constructor syntax

Hibernate uses a powerful query language (HQL) that is similar in appearance to SQL. Compared with SQL, however, HQL is fully object-oriented and understands notions like inheritance, polymorphism and association.

 

User-Defined Variables

You can store a value in a user-defined variable in one statement and then refer to it later in another statement. This enables you to pass values from one statement to another.

User variables are written as @var_name, where the variable name var_name consists of alphanumeric characters, “.”, “_”, and “$”. A user variable name can contain other characters if you quote it as a string or identifier (for example, @'my-var',@"my-var", or @`my-var`).

User-defined variables are session-specific. A user variable defined by one client cannot be seen or used by other clients. (Exception: A user with access to the Performance Schema user_variables_by_thread table can see all user variables for all sessions.) All variables for a given client session are automatically freed when that client exits.

User variable names are not case sensitive. Names have a maximum length of 64 characters as of MySQL 5.7.5. (Length is not constrained before that.)

One way to set a user-defined variable is by issuing a SET statement:

SET @var_name = expr [, @var_name = expr] ...

For SET, either = or := can be used as the assignment operator.

You can also assign a value to a user variable in statements other than SET. In this case, the assignment operator must be := and not = because the latter is treated as the comparison operator = in non-SET statements:

mysql> SET @t1=1, @t2=2, @t3:=4;
mysql> SELECT @t1, @t2, @t3, @t4 := @[email protected][email protected];
 ------ ------ ------ -------------------- 
| @t1  | @t2  | @t3  | @t4 := @[email protected][email protected] |
 ------ ------ ------ -------------------- 
|    1 |    2 |    4 |                  7 |
 ------ ------ ------ -------------------- 

User variables can be assigned a value from a limited set of data types: integer, decimal, floating-point, binary or nonbinary string, or NULL value. Assignment of decimal and real values does not preserve the precision or scale of the value. A value of a type other than one of the permissible types is converted to a permissible type. For example, a value having a temporal or spatial data type is converted to a binary string. A value having the JSON data type is converted to a string with a character set of utf8mb4 and a collation of utf8mb4_bin.

If a user variable is assigned a nonbinary (character) string value, it has the same character set and collation as the string. The coercibility of user variables is implicit. (This is the same coercibility as for table column values.)

Bit values assigned to user variables are treated as binary strings. To assign a bit value as a number to a user variable, use CAST() or  0:

mysql> SET @v1 = b'1000001';
mysql> SET @v2 = CAST(b'1000001' AS UNSIGNED), @v3 = b'1000001' 0;
mysql> SELECT @v1, @v2, @v3;
 ------ ------ ------ 
| @v1  | @v2  | @v3  |
 ------ ------ ------ 
| A    |   65 |   65 |
 ------ ------ ------ 

If the value of a user variable is selected in a result set, it is returned to the client as a string.

If you refer to a variable that has not been initialized, it has a value of NULL and a type of string.

User variables may be used in most contexts where expressions are permitted. This does not currently include contexts that explicitly require a literal value, such as in the LIMIT clause of a SELECT statement, or the IGNORE N LINES clause of aLOAD DATA statement.

As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement. For example, to increment a variable, this is okay:

SET @a = @a   1;

For other statements, such as SELECT, you might get the results you expect, but this is not guaranteed. In the following statement, you might think that MySQL will evaluate @a first and then do an assignment second:

SELECT @a, @a:[email protected] 1, ...;

However, the order of evaluation for expressions involving user variables is undefined.

Another issue with assigning a value to a variable and reading the value within the same non-SET statement is that the default result type of a variable is based on its type at the start of the statement. The following example illustrates this:

mysql> SET @a='test';
mysql> SELECT @a,(@a:=20) FROM tbl_name;

For this SELECT statement, MySQL reports to the client that column one is a string and converts all accesses of @a to strings, even though @a is set to a number for the second row. After the SELECT statement executes, @a is regarded as a number for the next statement.

To avoid problems with this behavior, either do not assign a value to and read the value of the same variable within a single statement, or else set the variable to 00.0, or '' to define its type before you use it.

In a SELECT statement, each select expression is evaluated only when sent to the client. This means that in a HAVINGGROUP BY, or ORDER BY clause, referring to a variable that is assigned a value in the select expression list does not work as expected:

mysql> SELECT (@aa:=id) AS a, (@aa 3) AS b FROM tbl_name HAVING b=5;

The reference to b in the HAVING clause refers to an alias for an expression in the select list that uses @aa. This does not work as expected: @aa contains the value of id from the previous selected row, not from the current row.

User variables are intended to provide data values. They cannot be used directly in an SQL statement as an identifier or as part of an identifier, such as in contexts where a table or database name is expected, or as a reserved word such asSELECT. This is true even if the variable is quoted, as shown in the following example:

mysql> SELECT c1 FROM t;
 ---- 
| c1 |
 ---- 
|  0 |
 ---- 
|  1 |
 ---- 
2 rows in set (0.00 sec)

mysql> SET @col = "c1";
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @col FROM t;
 ------ 
| @col |
 ------ 
| c1   |
 ------ 
1 row in set (0.00 sec)

mysql> SELECT `@col` FROM t;
ERROR 1054 (42S22): Unknown column '@col' in 'field list'

mysql> SET @col = "`c1`";
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @col FROM t;
 ------ 
| @col |
 ------ 
| `c1` |
 ------ 
1 row in set (0.00 sec)

An exception to this principle that user variables cannot be used to provide identifiers, is when you are constructing a string for use as a prepared statement to execute later. In this case, user variables can be used to provide any part of the statement. The following example illustrates how this can be done:

mysql> SET @c = "c1";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @s = CONCAT("SELECT ", @c, " FROM t");
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt FROM @s;
Query OK, 0 rows affected (0.04 sec)
Statement prepared

mysql> EXECUTE stmt;
 ---- 
| c1 |
 ---- 
|  0 |
 ---- 
|  1 |
 ---- 
2 rows in set (0.00 sec)

mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)

sql server中变量要先申明后赋值: 局部变量用一个@标识,全...

14.1. Case Sensitivity

With the exception of names of Java classes and properties, queries are case-insensitive. So SeLeCT is the same as sELEct is the same as SELECT, but org.hibernate.eg.FOO is not org.hibernate.eg.Foo, and foo.barSetis not foo.BARSET.

This manual uses lowercase HQL keywords. Some users find queries with uppercase keywords more readable, but this convention is unsuitable for queries embedded in Java code.

sql server中变量要先申明后赋值:

14.2. The from clause

The simplest possible Hibernate query is of the form:

from eg.Cat

This returns all instances of the class eg.Cat. You do not usually need to qualify the class name, since auto-import is the default. For example:

from Cat

In order to refer to the Cat in other parts of the query, you will need to assign an alias. For example:

from Cat as cat

This query assigns the alias cat to Cat instances, so you can use that alias later in the query. The as keyword is optional. You could also write:

from Cat cat

Multiple classes can appear, resulting in a cartesian product or "cross" join.

from Formula, Parameter

from Formula as form, Parameter as param

It is good practice to name query aliases using an initial lowercase as this is consistent with Java naming standards for local variables (e.g. domesticCat).

局部变量用一个@标识,全局变量用两个@(常用的全局变量一般都是已经定义好的);

14.3. Associations and joins

You can also assign aliases to associated entities or to elements of a collection of values using a join. For example:

from Cat as cat
    inner join cat.mate as mate
    left outer join cat.kittens as kitten

from Cat as cat left join cat.mate.kittens as kittens

from Formula form full join form.parameter param

The supported join types are borrowed from ANSI SQL:

  • inner join

  • left outer join

  • right outer join

  • full join (not usually useful)

The inner joinleft outer join and right outer join constructs may be abbreviated.

from Cat as cat
    join cat.mate as mate
    left join cat.kittens as kitten

You may supply extra join conditions using the HQL with keyword.

from Cat as cat
    left join cat.kittens as kitten
        with kitten.bodyWeight > 10.0

A "fetch" join allows associations or collections of values to be initialized along with their parent objects using a single select. This is particularly useful in the case of a collection. It effectively overrides the outer join and lazy declarations of the mapping file for associations and collections. See Section 19.1, “Fetching strategies” for more information.

from Cat as cat
    inner join fetch cat.mate
    left join fetch cat.kittens

A fetch join does not usually need to assign an alias, because the associated objects should not be used in the where clause (or any other clause). The associated objects are also not returned directly in the query results. Instead, they may be accessed via the parent object. The only reason you might need an alias is if you are recursively join fetching a further collection:

from Cat as cat
    inner join fetch cat.mate
    left join fetch cat.kittens child
    left join fetch child.kittens

The fetch construct cannot be used in queries called using iterate() (though scroll() can be used). Fetch should be used together with setMaxResults() or setFirstResult(), as these operations are based on the result rows which usually contain duplicates for eager collection fetching, hence, the number of rows is not what you would expect. Fetch should also not be used together with impromptu with condition. It is possible to create a cartesian product by join fetching more than one collection in a query, so take care in this case. Join fetching multiple collection roles can produce unexpected results for bag mappings, so user discretion is advised when formulating queries in this case. Finally, note that full join fetch and right join fetch are not meaningful.

If you are using property-level lazy fetching (with bytecode instrumentation), it is possible to force Hibernate to fetch the lazy properties in the first query immediately using fetch all properties.

from Document fetch all properties order by name

from Document doc fetch all properties where lower(doc.name) like '
	

编辑:数据库 本文来源:如果想获取查询语句中的一个字段值可以用sele

关键词: 澳门新濠3559