Mysql Commands Colt-stee

 

MYSQL Cheat Sheet

connecting with mysql in cmd line 

to see list of dbs

show databases;

creating Databse 

CREATE DATABASE <Database_name>;

delete database

DROP DATABASE <Database_name>;

USE <Database_name>;

currently used db

SELECT database();

// tables 

CREATE TABLE tablename 

(

 colum_name datatype,

 colum_name datatype

);

// example

// table names are prural

CREATE TABLE cats 

(

 name VARCHAR(100),

 age INT

);

// show table 

SHOW TABLES <tablename>;

SHOW COLUMNS FROM <tablename>;

DESC <tablename>;

// delete table

DROP TABLE <tablename>;

-----------------------------

INSERT DATA

INSERT INTO <tablename>(colum_name1,colum_name2,...colum_name_n)

VALUES ('colum_value1','colum_value2',...colum_value_integer_n);

-------------------

select data

SELECT * FROM tablename;

Multiple INSERT DATA

INSERT INTO <tablename>(colum_name1,colum_name2,...colum_name_n)

VALUES 

('colum_value1','colum_value2',...colum_value_integer_n),

('colum_value1','colum_value2',...colum_value_integer_n),

('colum_value1','colum_value2',...colum_value_integer_n);

SHOW WARNINGS;

// NULL 

make a table colum not null

CREATE TABLE cats 

(

 name VARCHAR(100) NOT NULL,

 age INT NOT NULL

);

// Default value

CREATE TABLE cats 

(

 name VARCHAR(100) NOT NULL DEFAULT '<Any thing>',

 age INT NOT NULL DEFAULT <Any thing> 

);

// key 

primary key 

uniquely identify a row

CREATE TABLE cats 

(

 cat_id INT NOT NULL,

 name VARCHAR(100) NOT NULL DEFAULT '<Any thing>',

 age INT NOT NULL DEFAULT <Any thing>,

 PRIMARY KEY (cat_id);

);

----------

example -1

img employee with the following fields

CREATE TABLE employees

(

  id INT NOT NULL AUTO_INCREMENT,

  last_name TEXT(255) NOT_NULL,

  first_name TEXT(255) NOT_NULL,

  middle_name TEXT(255),

  age INT NOT_NULL,

  current_status TEXT(100) NOT_NULL DEFAULT 'employed';

  PRIMARY KEY (id);

);

-----------------------------------------------------------

read

SELECT * FROM cats;

SELECT <column name>,<column name> FROM cats;

SELECT * FROM cats WHERE age=4;

---------------------------------------------------

Aliases

SELECT id AS myId, FROM cats;

UPDATE <tablename> SET <column name>='Something' WHERE <column name>='Something';

-------------------------------------------------

example 2

UPDATE cats SET name='jack' WHERE name='jackson';

UPDATE cats SET breed='british shorthair' WHERE name='Ringo';

UPDATE cats SET ages=12 WHERE breed='Maine Coons';

-------------------------------------------

DELETE FROM <tablename>;

DELETE FROM <tablename> WHERE <column name>='<something>';

------------------------------------------------------------------

String Functions

run sql from a file 

source <filename.sql> 

take a look at the documentation

https://dev.mysql.com/doc/refman/8.0/en/string-functions.html

string functions

CONCAT()

CONCAT-WS()

SUBSTRING()

REPLACE()

REVERSE()

CHAR_LENGTH() 

UPPER()

LOWER()

------------------------------------------------------------------------

Refining Our Selections