Using SQL statements to show--will be constantly updated and perfected

This blog post is mainly for my own use of SQL statements to show, and then continue to improve, to form their own style of writing SQL statements, and improve the speed of their own SQL statements. I was not familiar with a series of SQL statements, such as the left connection, and learned some SQL statements after these days of study and the use of actual requirements in the project. Now I'm going to show the SQL statements I wrote. Mainly divided into two categories, one is through the YII framework of SQL statements, one class is the normal SQL statement.

Say the normal SQL statement first.
1. The first is a multi-table query, the first involves the Join method, the following connection, there are several points to note, one is the use of group by, the group by words, expressed in accordance with the following keyword sort, if encountered duplicate, will only take the first message.
And if there is a need or relationship in many and conditions, you can use parentheses around the same method, and then write the or statement.
Also have to notice, multi-table query words, must pay attention to two to indicate what key field to connect, as the following SQL statement, os.order_id = O.id This is the basis of their connection.

SELECT  os.city_id, o.id as  order_id, o.c_id From  order_ Social_detail os join  order  o where  os.order_id = o.id and  o.pay_status = 0  and  (social_type = 1  or  social_type = 5  OR  social_type = 8 ) group  by  o.id   

2. If you say the method of join, you must mention the left join, which is the main table on the left, which is the previous table.
As the following SQL statement, its essence and join is the same, his condition is to use on the keyword and then add us.city_id = cd.area_id Two kinds of tables associated with the properties of the connection.
It is also important to note that this place uses the keyword in this to indicate what you are searching for in your parentheses.

SELECT cd.deadline_day FROM {{user_socialmanage}} us LEFT JOIN {{city_data}} cd  ON us.city_id = cd.area_id WHERE us.is_delete = 0 AND us.social_security_status IN (‘0,1,3‘) AND us.c_id = {$c_id} GROUP BY us.city_name

3. The following SQL statement mainly describes how to use the equal sign and less than equals.

SELECT * FROM jy_order_social_detail WHERE last_feedback_content != ‘‘ AND c_id = {$c_id} and is_delete = 0 AND service_status <= 4

4. Now to talk about, get the data stored in the database table is not empty information, with is not NULL the keyword, if it is to be empty, is null. For this reason, it is best not to save the data as null and avoid ambiguity.

SELECT city_id From user_socialmanage where (social_security_current_month is not null) and social_security_status = 1 and is_delete = 0

the use of 5.count (). The following is how the most basic count () is used.

SELECT count(*) From user_socialmanage where (social_security_status=0 or social_security_status=1 or social_security_status=3)

6. If inside the YII framework, if you want to add a variable to the SQL statement, you can use a separate, and then use. Point, or you can wrap the variable in curly braces directly in the SQL statement.

 Public  function getservicestatusbycid($params) {        //Incoming $parmas are arrays, including $service_status and c_id        $c _id=$params[' c_id '];$service _status=$params[' Service_status '];$sql="Select id from {{order_social_detail}}" where Service_status = {$service _status} and c_id = {$c _id} and Is_delete = 0 " ;$command= Yii::app ()->db->createcommand ($sql);$service _status_data=$command->queryall ();//Adopt. The way to separate, this to 1 note that the SQL statement must use double quotation marks Oh.     $sql="Select id from {{order_social_detail}} where Service_status =".$service _status."and c_id =".$c _id."and Is_delete = 0";$command= Yii::app ()->db->createcommand ($sql);$service _status_data=$command->queryall ();return $service _status_data; }

Using SQL statements to show--will be constantly updated and perfected