MYSQL Basic 20 command for Web development by ocec

  here are examples of SQL commands for creating a database, creating a table, deleting a table, adding a column, deleting a column, and inserting data:

1. Create Database: Used to create a new database.

   CREATE DATABASE my_database;

2. Create Table: Used to create a new table in a database.

   CREATE TABLE employees (

       id INT PRIMARY KEY,

       name VARCHAR(50),

       age INT

   );

3. Delete Table: Used to delete an existing table from the database.

   DROP TABLE employees;

4. Add Column: Used to add a new column to an existing table.

   ALTER TABLE employees ADD COLUMN department VARCHAR(50);

5. Delete Column: Used to delete an existing column from a table.

   ALTER TABLE table_name DROP COLUMN column_name;

6. Delete multiple columns from a table:

ALTER TABLE table_name
DROP COLUMN column_name1,
DROP COLUMN column_name2;
 

7. Insert Data: Used to insert new records into a table.

   INSERT INTO table_name(id, name, age) VALUES (1, 'John Doe', 30);

 8. Add a column to a specific position in a table:

ALTER TABLE table_name ADD column_name AFTER existing_column;
 

9. Add multiple columns to specific positions in a table:

ALTER TABLE table_name
ADD column_name1 AFTER existing_column,
ADD column_name2 AFTER existing_column,
ADD column_name3 AFTER existing_column;
 

10. Rename a column in a table:

ALTER TABLE table_name CHANGE previous_column_name new_column_name;
 

11.View the data type of columns in a table:

DESC table_name;
 

12.Change the data type of a column:

ALTER TABLE table_name CHANGE column_name column_name CHAR(12);
 

13. Change the data type of multiple columns:

ALTER TABLE table_name
CHANGE column_name1 column_name1 CHAR(12),
CHANGE column_name2 column_name2 CHAR(12);
 

14.Update data in a table:

UPDATE table_name SET column_name1 = 'value', column_name2 = 'value' WHERE id = 3;
 

15.Delete rows from a table based on a condition:

DELETE FROM table_name WHERE column_name = 'EXISTING_VALUE';
 

16.Set a column to be NOT NULL:

ALTER TABLE table_name MODIFY column_name CHAR(12) NOT NULL;

17. Make a column unique:

ALTER TABLE table_name ADD UNIQUE (column_name);
 

18. Delete a unique key from a table:

ALTER TABLE table_name DROP INDEX column_name;
 

19. Set a default value for a column:

ALTER TABLE table_name CHANGE column_name column_name CHAR(15) DEFAULT 'ENTER_VALUE';
 

20.Remove a default value from a column:

ALTER TABLE table_name ALTER column_name DROP  DEFAULT;
 

21.Add auto-increment to a column:

ALTER TABLE table_name CHANGE ID ID INT(6) NOT NULL AUTO_INCREMENT;
 

22.Create a new table with constraints:

CREATE TABLE table_name (
Id INT(11) NOT NULL UNIQUE AUTO_INCREMENT,
Column_name1 CHAR(30) NOT NULL,
Column_name2 CHAR(5) NOT NULL DEFAULT 'Enter value'
);