중복 값 찾는 법

SELECT * FROM 테이블 GROUP BY 필드 HAVING COUNT(필드) > 1

중복값 갯수 알기

SELECT count(필드명)-count(distinct 필드명) FROM `gd_member` WHERE 1

출처:

http://blog.naver.com/PostView.nhn?blogId=lecardos&logNo=70123459399&viewDate=&currentPage=1&listtype=0

http://blog.naver.com/x1478?Redirect=Log&logNo=70127581041

출처: http://dev.mysql.com/tech-resources/articles/wizard/page4.html

MySQL에서 엑셀의 피벗테이블같이 사용하고 싶을 때 유용하다.

Some help from SQL itself

The Wizard smiled. "If you like to, you're welcome. However, you could get some help from the database engine itself, provided that you ask nicely." And while he was speaking he typed a very cryptic statement:

mysql> SELECT CONCAT(', SUM(IF(dept = "',dept,'", 1,0)) AS `',dept,'`')
    -> FROM departments;

"You know," he went on, "you can also use SQL to produce SQL code. This is one of the cases. You have in front of you the list of columns that you should include in your query. Now, with some cut-and-paste, we could get the result you want. Here."

mysql> SELECT location
    -> , SUM(IF(dept = "Development", 1,0)) AS `Development` 
    -> , SUM(IF(dept = "Personnel", 1,0)) AS `Personnel`
    -> , SUM(IF(dept = "Research", 1,0)) AS `Research`
    -> , SUM(IF(dept = "Sales", 1,0)) AS `Sales`
    -> , SUM(IF(dept = "Training", 1,0)) AS `Training`
    -> , COUNT(*) AS total
    -> FROM locations INNER JOIN employees USING (loc_code)
    -> INNER JOIN departments USING (dept_code)
    -> GROUP BY location;

The wizard was now unstoppable. He had reached the stage where he simply couldn't help giving away his knowledge.

"Before we go on," he said, taking possession of my keyboard, "let's see how this same method can do more than counting. Let's replace those 1s with a numeric field, and do real summing up."

mysql> SELECT location
    -> , SUM(IF(dept = "Development", salary,0)) AS `Development`
    -> , SUM(IF(dept = "Personnel", salary,0)) AS `Personnel`
    -> , SUM(IF(dept = "Research", salary,0)) AS `Research`
    -> , SUM(IF(dept = "Sales", salary,0)) AS `Sales`
    -> , SUM(IF(dept = "Training", salary,0)) AS `Training`
    -> , SUM(salary) AS total
    -> FROM locations INNER JOIN employees USING (loc_code)
    -> INNER JOIN departments USING (dept_code)
    -> GROUP BY location;

"Don't forget to change also the total field. A simple SUM without IF, and your total is ready."

I was looking at the screen, which was showing what seemed to be the complete solution to my problem, but the wizard was shaking his head. "As a matter of fact," he was saying, there is something that we can improve here. We have two queries, in which we are reading the departments table. So we are reading it twice twice. The whole process could be improved, by querying for department code the first time, and omitting the join with departments the second time."

mysql> SELECT CONCAT(', SUM(IF(dept_code = "',dept_code,'", 1,0)) AS `',dept,'`')
    -> FROM departments;

"Here. Let's get the columns once more. Good. And there it is. This one looks better."

mysql> SELECT location
    -> , SUM(IF(dept_code = "1", 1,0)) AS `Personnel`
    -> , SUM(IF(dept_code = "2", 1,0)) AS `Training`
    -> , SUM(IF(dept_code = "3", 1,0)) AS `Research`
    -> , SUM(IF(dept_code = "4", 1,0)) AS `Sales`
    -> , SUM(IF(dept_code = "5", 1,0)) AS `Development`
    -> , COUNT(*) AS total
    -> FROM locations INNER JOIN employees USING (loc_code)
    -> GROUP BY location;

He changed the previous two SQL statements, executed them, with some cut-and-pasting in the middle, and got exactly the same result. Now he was explaining me why he did it. "The first query is scanning all the departments table, and we know that its results will be used to build the second query containing the employees table, which has already a department code. Therefore, we can skip the join with departments, since the only purpose of that join was to get the names of the departments."

"I see" was the only comment I could offer, Since I was overwhelmed by his continuous insight. I got the idea, and I couldn't help thinking that he must have done that before. All those pieces of information were coming just too fast for me. Luckily, all logs were on, so I knew that I would be able to get all the statements back when he would leave. Which was not the case yet. The wizard was now ready to give me his philosophical view of cross tabulating.

Example

The Regex.Escape method is a static method on the Regex type that receives one parameter of string type. The string reference is received and internally the Escape method allocates a new string containing the escaped character sequence and returns its reference. This example shows the use of Regex.Escape on user input.

Program that uses Regex.Escape [C#]

using System;
using System.Text.RegularExpressions;

class Program
{
    static void Main()
    {
	// User specified to remove this string.
	string value1 = @"\123";

	// Escape the input.
	value1 = Regex.Escape(value1);

	// Write the escaped input.
	Console.WriteLine(value1);

	// The string we are changing.
	string input1 = @"This is\123a string";

	// Remove the user-specified pattern.
	string output1 = Regex.Replace(input1, value1, "");

	// Write the output.
	Console.WriteLine(output1);
    }
}

Output
    (The backslash character was replaced.)

\\123
This isa string

String variables used. The example shows three strings: the first string value1 is the input that the user specifies. The user in this example specified the exact string "\123". This value is then escaped with Regex.Escape and becomes "\\123". Finally the Regex.Replace method is called and replaces the pattern "\\123".

Regex.Replace call result. Because the escaped string has two backslashes and not just one, the backslash is treated as a character in the regular expression and not an escape code. The Replace method then can match the character group "\123" and remove it from the final result.

Uses

Here we note some of the uses the Regex.Escape method has in programs targeting the .NET Framework. Because you can always escape strings in the source code that you type in, you will not need to use it in most programs. If your program retrieves input from a user or file that has certain characters in it, you can use Escape to eliminate the chance that those characters will be used incorrectly.

Summary

We looked at an example of using the Regex.Escape method in the C# programming language. This is a powerful static method that can be useful when preparing dynamic input for use in a regular expression method such as Replace or Split. However, we noted that in most programs where the expressions are specified in the source, this method is not required or useful.

크리스탈 리포트에 연결하기 위해서 dataset을 이용하였다.

하지만 찾지 못해서 그냥 sql 문으로 사용했는데..

마침 내가 찾는 자료가..ㅎㅎ

출처: http://kojaedoo.tistory.com/640

새항목 추가에서 데이터집합을 만듭니다.

서버탐색기에서 엑세스하고자하는 DataSet1.xsd 에다가 테이블을 끌어옵니다.

DataSet1TableAdapters.CustomersTableAdapter adater = new DataSet1TableAdapters.CustomersTableAdapter();

DataSet1.CustomersDataTable dt = adater.GetData();
//foreach (var item in dt)
//{
// Response.Write(item.CompanyName+"<br>");

//}
this.DataList1.DataSource = dt;
this.DataList1.DataBind();

DataList에 바인딩해보기

잘나온다.!

특정 행만 가져오기

이름을 지정하고

/// <summary>
/// customerID 를 이용해서 하나의 Customers 불러옵니다.
/// </summary>
/// <param name="customerID"></param>
private void GetCustomersData(string customerID)
{
    DataSet1.CustomersDataTable dt = adater.GetDataByCustomers(customerID);
    if (dt.Count > 0)
    {
        Response.Write("ALFKI 회사의 이름은 :" + dt[0].CompanyName);
    }
}

수정

DataSet1.CustomersDataTable dt = adater.GetDataByCustomers(customerID);
if (dt.Count > 0)
{
    dt[0].CompanyName = "kojaedoo Company";
}
this.adater.Update(dt);

추가

DataSet1.CustomersDataTable dt = new DataSet1.CustomersDataTable();
DataSet1.CustomersRow row =  dt.NewCustomersRow();
row.CustomerID = "KJD";
row.CompanyName = "New kojaedoo Company";
row.ContactName = "kojaedoo";
row.ContactTitle = "고객센터";
// .. 등등삽입

dt.AddCustomersRow(row);

this.adater.Update(dt);

삭제

특정행 가져오기에서 처럼 쿼리구성 마법사를 실행합니다. 여기서 Delete 선택

Delete Query가 추가 되었습니다.

끗

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace WebApplication2
{
    public partial class _Default : System.Web.UI.Page
    {
        //ALFKI

        DataSet1TableAdapters.CustomersTableAdapter adater = new DataSet1TableAdapters.CustomersTableAdapter();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                GetCustomersData();
                //UpdateCustomers("ALFKI");
                //NewCustomers();
            }
        }

        /// <summary>
        /// Customers 리스트를 불러옵니다.
        /// </summary>
        private void GetCustomersData()
        {

            DataSet1.CustomersDataTable dt = adater.GetData();
            //foreach (var item in dt)
            //{
            //    Response.Write(item.CompanyName+"<br>");

            //}
            this.DataList1.DataSource = dt;
            this.DataList1.DataBind();
        }

        /// <summary>
        /// customerID 를 이용해서 하나의 Customers 불러옵니다.
        /// </summary>
        /// <param name="customerID"></param>
        private void GetCustomersData(string customerID)
        {
            DataSet1.CustomersDataTable dt = adater.GetDataByCustomers(customerID);
            if (dt.Count > 0)
            {
                Response.Write("ALFKI 회사의 이름은 :" + dt[0].CompanyName);
            }
        }

        /// <summary>
        /// Customers를 수정합니다.
        /// </summary>
        /// <param name="customerID"></param>
        private void UpdateCustomers(string customerID)
        {
            DataSet1.CustomersDataTable dt = adater.GetDataByCustomers(customerID);
            if (dt.Count > 0)
            {
                dt[0].CompanyName = "kojaedoo Company";
            }
            this.adater.Update(dt);
        }

        /// <summary>
        /// Customers를 삭제합니다.
        /// </summary>
        /// <param name="customerID"></param>
        private void DeleteCustomers(string customerID)
        {
            int i = this.adater.DeleteQueryCustomerID(customerID);
            Response.Write(i.ToString() + "개가 삭제되었습니다.");
        }

        /// <summary>
        /// 새로운 Customers 를 만듭니다.
        /// </summary>
        private void NewCustomers()
        {
            DataSet1.CustomersDataTable dt = new DataSet1.CustomersDataTable();
            DataSet1.CustomersRow row =  dt.NewCustomersRow();
            row.CustomerID = "KJD";
            row.CompanyName = "New kojaedoo Company";
            row.ContactName = "kojaedoo";
            row.ContactTitle = "고객센터";
            // .. 등등삽입
            
            dt.AddCustomersRow(row);

            this.adater.Update(dt);

        }
    }
}

CREATE TABLE POST (

  POST_IDX int(11) AUTO_INCREMENT,

  SUBJECT varchar(2000),

  NAME varchar(200),

  CONTENT text,

  WRITTEN datetime,

  PRIMARY KEY(POST_IDX)

)



CREATE TABLE ATTACHMENT (

  ATTACH_IDX int(11) AUTO_INCREMENT,

  POST_IDX int(11),

  FILE_NAME varchar(200),

  FILE_PATH varchar(2000),

  UPLOADED datetime,

  PRIMARY KEY(ATTACH_IDX ),

  FOREIGN KEY POST_IDX REFERENCES POST(POST_IDX)

)

INSERT INTO POST (

  SUBJECT, NAME, CONTENT, WRITTEN

) VALUES (

  "제목", "이름", "글내용", NOW()

);



INSERT INTO ATTACHMENT (

  POST_IDX, FILE_NAME, FILE_PATH, UPLOADED

) VALUES (

  LAST_INSERT_ID(), "file.jpg", "/tmp/file.jpg", NOW()

);

MySQL에서 데이터를 삽입 후 AUTO_INCREMENT 컬럼의 증가값을 가져오기 위해 last_insert_id()를 호출하는 경우, 걱정되는 점 중 하나는 멀티 쓰레드 환경에서 컨넥션들이 서로 race condition을 유발하지 않을까 하는 점이다. 즉, 두 컨넥션이 동시에 insert를 하고 last_insert_id()를 호출하였을 때, 우연히 두 번의 삽입 작업이 먼저 이루어지고 나서 last_insert_id()가 동작하여 같은 정수값을 가져오지 않을까 하는 우려이다. 하지만 이 점에 대해 MySQL은 분명히 명시하고 있다.

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.

즉, ID를 생성해서 유지하는 것은 컨넥션 별로 따로 관리되며, 각각의 클라이언트가 정확한 수치를 가져오기 위해 테이블에 락을 걸거나 트랜잭션을 사용 할 필요가 없다는 것이다. 고로, 그냥 연달아 쿼리 날려주면 된다.

MySQL 매뉴얼, LAST_INSERT_ID(), LAST_INSERT_ID(expr) 항목을 참조