mysql code snippets

• • Read more

  Create table with constraints MySQL query

  CREATE TABLE table_name(
      Id int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
      Column1 char(40) DEFAULT NULL,
      Column2 varchar(100) DEFAULT '',
      Date timestamp DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY(Id),
      UNIQUE (Column1)
  )

• • Read more

  Add columns, alter table SQL query

  ALTER TABLE 
      table_name 
  ADD (
      Column_name1 data_type, 
      Column_name2 data_type (size)
  );

• • Read more

  Select rows from table SQL query

  //select all rows from table
  SELECT * FROM table_name
  
  //select rows based on a condition
  SELECT * FROM table_name WHERE column_name='value'

• • Read more

  Get duplicate rows and its counts MySQL

  SELECT 
      column_name, 
      COUNT(column_name)
  FROM
      table_name
  GROUP BY column_name
  HAVING COUNT(column_name) > 1;

• • Read more

  Add foreign key in existing table Mysql

  ALTER TABLE 
  ContactDetails 
  ADD 
  EmployeeId INT UNSIGNED NOT NULL;
  
  ALTER TABLE 
  ContactDetails 
  ADD 
  CONSTRAINT fk_EMP_CONTACT FOREIGN KEY (EmployeeId) REFERENCES Employee(Id);

• • Read more

  Order by ascending and descending MySQL

  SELECT * FROM table_name FROM ORDER BY column_name ASC
  SELECT * FROM table_name FROM ORDER BY column_name DESC

• • Read more

  Get distinct records MySQL

  SELECT DISTINCT
      column_name1, column_name2
  FROM
      table_name;

• • Read more

  In Operator MySQL

  -- Match with list values
  SELECT 
      first_name, 
      last_name,
      city
  FROM
      Students
  WHERE
      city IN ('Delhi' , 'Newyork');
  
  
  --Match with subquery records
  SELECT    
  	first_name, 
      last_name,
      city
  FROM    
  	Students
  WHERE city IN
  (
      SELECT 
          city
      FROM 
          city_list
  );

• • Read more

  Create and select database MySQL

  -- To create database
  CREATE DATABASE db_name;
  
  --To select database
  USE db_name;

• • Read more

  Show all Databases MySQL

  SHOW DATABASES;

• • Read more

  Show all tables in a database MySQL

  SHOW TABLES:

• • Read more

  Add unique constraint MySQL

  -- While creating table (Single)
  CREATE TABLE table_name( colum_1 data_type,  column_2 data_type UNIQUE );
  
  -- While creating table (Combine multiple column)
  CREATE TABLE table_name(
     column_1 data_type,
     column_2 data_type,
     UNIQUE(column_1, column_2)
  );
  
  -- Add unique constraints on existing table
  ALTER TABLE table_name
  ADD CONSTRAINT constraint_name 
  UNIQUE (column_name);

• • Read more

  Rename table MySQL

  -- SINGLE TABLE
  RENAME TABLE courses TO subjects;
  
  -- MULTIPLE TABLES
  RENAME TABLE 
      courses TO subjects,
      topics TO subject_topics;

• • Read more

  Remove or drop table MySQL

  -- Remove single table
  DROP TABLE table_name;
  
  -- Remove multiple tables
  DROP TABLE table_name_1, table_name_2;

• • Read more

  murata mysqlにcomman lineからアクセスする方法

  mysql -u root -p test_db
  # test_dbはデータベース名

• • Read more

  Add comment in Sql or Mysql

  -- USE -- FOR SINGLE LINE COMMENT
  -- SELECT * FROM STUDENTS
  
  /* BLOCK LEVEL COMMENTS - COMMENT OUT A FULL BLOCK */
  /*
  SELECT NAME
  FROM USERS
  */

• • Read more

  Delete a column from Table MySQL

  ALTER TABLE Students
  DROP COLUMN Email;

• • Read more

  Order by Date where date in string format MySQL

  SELECT Id, FirstName, Lastname
  FROM Students 
  ORDER BY STR_TO_DATE(CreatedDate, '%d-%m-%Y') ASC;
  
  -- USE 'DESC' in place of 'ASC' if you want result in decending format