CRUD operations in SQL

CRUD acronym

CRUD is an acronym that represents four basic functions of persistent storage:

Code Completion

Operation	SQL
Create	INSERT
Read (Retrieve)	SELECT
Update (Modify)	UPDATE
Delete (Destroy)	DELETE

These operations in SQL are described below via using MYSQL dbms: ---- ##MySQL syntax * **Under Unix, database names are case sensitive (unlike SQL keywords)** * **All text commands must be first on line and end with `;`** ---- ## Creating & Using Database `SHOW DATABASES;` - shows all available databases `CREATE DATABASE db_name` - creates database with the particular name. > Creating a database does not select it for use; must do that explicitly: mysql> CREATE DATABASE handmade_db; `USE db_name` - to make the particular db current. > Alternatively, you can select the database on the command line when you invoke `mysql`. > Just specify its name after any connection parameters that you might need to provide: mysql -h host -u user -p db_name Enter password:***** > You can see at any time which database is currently selected using `SELECT DATABASE();`. ---- ## Manipulating the Tables `SHOW TABLES;` - shows available tables in the used db. Use `CREATE TABLE table_name` syntax to specify layout of your table: mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE); mysql> SHOW TABLES; +-----------------------+ | Tables_in_handmade_db | +-----------------------+ | pet | +-----------------------+ 1 row in set (0.00 sec) > To change table structure u can use `ALTER TABLE` syntax. Use `DESCRIBE table_name;` command to check your table structure: mysql> DESCRIBE pet; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | owner | varchar(20) | YES | | NULL | | | species | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | | death | date | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 6 rows in set (0.02 sec) ---- ### Loading Data into Table U can populate your table from local text file via using `LOAD DATA LOCAL INFILE 'path/to/table_input.txt' INTO TABLE table_name;` However the most useful is `INSERT INTO table_name VALUES(...);` command, which allows to u to add new records one at time in order in which the columns were created or shown: mysql> INSERT INTO pet VALUES ('Martin','Joe','cat','m','2010-01-30',NULL); Query OK, 1 row affected (0.01 sec) ---- ### Retrieving Data from Table `SELECT what_to_select FROM which_table WHERE conditions_to_satisfy;` *what_to_select* indicates what you want to see. This can be a list of columns, or * to indicate “all columns.” #### Retrieve All To show **all** data from particular table use the following syntax `SELECT * FROM table_name`: mysql> SELECT * FROM pet; +--------+-------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+-------+---------+------+------------+-------+ | Martin | Joe | cat | m | 2010-01-30 | NULL | +--------+-------+---------+------+------------+-------+ 1 row in set (0.00 sec) #### Retrieve Rows Select *particular row* from your table via following syntax `SELECT * FROM table_name WHERE condition;`: mysql> SELECT * FROM pet WHERE species = 'cat'; +--------+---------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+---------+---------+------+------------+-------+ | Martin | Joe | cat | m | 2010-01-30 | NULL | | Crumpy | Simpson | cat | m | 2011-02-20 | NULL | +--------+---------+---------+------+------------+-------+ 2 rows in set (0.00 sec) You can use also `AND`, `OR`, to define multiple conditions e.g. `SELECT * FROM table_name WHERE condition1 AND condition2;`: mysql> SELECT * FROM pet WHERE species = 'cat' AND birth >= '2011-01-01'; +--------+---------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+---------+---------+------+------------+-------+ | Crumpy | Simpson | cat | m | 2011-02-20 | NULL | +--------+---------+---------+------+------------+-------+ 1 row in set (0.00 sec) #### Retrieve Columns Select *particular columns* from your table via following syntax `SELECT column1, column3 FROM table_name WHERE condition;`: mysql> SELECT name, species FROM pet; +--------+---------+ | name | species | +--------+---------+ | Martin | cat | | Crumpy | cat | | Snoopy | dog | +--------+---------+ 3 rows in set (0.00 sec) > To minimize the output, retrieve each unique output record just once > by adding the keyword `DISTINCT` : `SELECT DISTINCT owner FROM pet;` U can also use `WHERE` clause here as well. #### Sort Rows Use `ORDER BY column_name` clause or `ORDER BY column_name DESC` for descending order. Use `ORDER BY BINARY column_name` for case-sensitive sorting. mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC; +--------+---------+------------+ | name | species | birth | +--------+---------+------------+ | Crumpy | cat | 2011-02-20 | | Martin | cat | 2010-01-30 | | Snoopy | dog | 2012-03-30 | +--------+---------+------------+ 3 rows in set (0.00 sec) #### Counting Rows Use `SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;` to calculate number of rows of each group. mysql> SELECT name, species, COUNT(*) FROM pet GROUP BY species; +--------+---------+----------+ | name | species | COUNT(*) | +--------+---------+----------+ | Martin | cat | 2 | | Snoopy | dog | 1 | +--------+---------+----------+ 2 rows in set (0.00 sec) #### Pattern Matching SQL pattern matching enables you to use `_` to match any single character and `%` to match an arbitrary number of characters (including zero characters). You do not use `=` or `<>` when you use SQL patterns; use the `LIKE` or `NOT LIKE` comparison operators instead. mysql> SELECT name, birth FROM pet WHERE species LIKE 'c%'; +--------+------------+ | name | birth | +--------+------------+ | Martin | 2010-01-30 | | Crumpy | 2011-02-20 | +--------+------------+ 2 rows in set (0.00 sec) U can use `REGEXP` & `NOT_REGEXP`(or `RLIKE` & `NOT_RLIKE`) to match Regular Expression Patterns: * `.` - any char;`[A-Za-z]` - any char within brackets; `[^abc]` - any char except ones*("abc")* within brackets; `( )` - marked subexpression. * `*` - zero or more instances; `?` - zero or one time; `+` - one or more times. * `\n` - matches *nTh* match; `{m,n}` - matches at least *m* and not more than *n* times. * `|` - alternation, matcher either the expression before or after operator. `cat|dog` matches "cat" or "dog". * `^` - start position within string/line; `$` - end position within string/line. * `\s` - whitespace; `\S` - anything but whitespace; `\d`/`\D` - digit/not digit. * `\w`/`\W` alphabetic char & `_` symbol / excluding any alphabetic char & `_` symbol. *Select all species starting from `c`:* mysql> SELECT name, birth FROM pet WHERE species REGEXP '^c'; +--------+------------+ | name | birth | +--------+------------+ | Martin | 2010-01-30 | | Crumpy | 2011-02-20 | +--------+------------+ 2 rows in set (0.00 sec) #### NULL values To check values for `NULL` use `IS NULL` and `IS NOT NULL`: mysql> SELECT name, birth FROM pet WHERE species IS NOT NULL; +--------+------------+ | name | birth | +--------+------------+ | Martin | 2010-01-30 | | Crumpy | 2011-02-20 | | Snoopy | 2012-03-30 | +--------+------------+ 3 rows in set (0.00 sec) #### Date Calculations To determine elapsed time from some date use `CURDATE()` & `TIMESTAMPDIFF()` clauses: mysql> SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet; +--------+------------+------------+------+ | name | birth | CURDATE() | age | +--------+------------+------------+------+ | Martin | 2010-01-30 | 2015-02-25 | 5 | | Crumpy | 2011-02-20 | 2015-02-25 | 4 | | Snoopy | 2012-03-30 | 2015-02-25 | 2 | +--------+------------+------------+------+ 3 rows in set (0.00 sec) To extract parts of dates use `YEAR()`,`MONTH()` & `DAYMONTH()`: mysql> SELECT name, birth, MONTH(birth) FROM pet ORDER BY MONTH(birth); +--------+------------+--------------+ | name | birth | MONTH(birth) | +--------+------------+--------------+ | Martin | 2010-01-30 | 1 | | Crumpy | 2011-02-20 | 2 | | Snoopy | 2012-03-30 | 3 | +--------+------------+--------------+ 3 rows in set (0.00 sec) #### Using Few Tables at once U can use `INNER JOIN` to show info from few tables or to compare few records from the same table: mysql> SELECT p1.name, p1.sex, p2.name, p2.sex -> FROM pet AS p1 INNER JOIN pet as p2 -> WHERE p1.sex = 'f' AND p2.sex = 'm'; +--------+------+--------+------+ | name | sex | name | sex | +--------+------+--------+------+ | Snoopy | f | Martin | m | | Snoopy | f | Crumpy | m | +--------+------+--------+------+ 2 rows in set (0.00 sec) ### Update Data record in Table Use `UPDATE table_name SET column1 = value1, column2 = value2....,` `columnN = valueN WHERE [condition];` > **If u won't use WHERE clause it will update all rows of the specified columns!** *Select needed record at first:* mysql> SELECT * FROM pet WHERE name LIKE 'M%'; +--------+-------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+-------+---------+------+------------+-------+ | Martin | Joe | cat | m | 2010-01-30 | NULL | +--------+-------+---------+------+------------+-------+ 1 row in set (0.00 sec) *Update the record more precisely:* mysql> UPDATE pet SET species = 'dog' WHERE name = 'Martin'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 *Ensure that its done correctly:* mysql> SELECT * FROM pet WHERE name LIKE 'M%'; +--------+-------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+-------+---------+------+------------+-------+ | Martin | Joe | dog | m | 2010-01-30 | NULL | +--------+-------+---------+------+------------+-------+ 1 row in set (0.00 sec) ### Delete Data record from Table Use `DELETE FROM table_name WHERE condition;` > **If u won't use WHERE clause it will delete all records from the specified table!** > e.g. `DELETE FROM pet;` *Select needed record at first:* mysql> SELECT * FROM pet WHERE birth IS NULL; +------+----------+---------+------+-------+-------+ | name | owner | species | sex | birth | death | +------+----------+---------+------+-------+-------+ | Blah | BlahBlah | animal | f | NULL | NULL | +------+----------+---------+------+-------+-------+ 1 row in set (0.00 sec) *Delete the record:* mysql> DELETE FROM pet WHERE birth IS NULL; Query OK, 1 row affected (0.00 sec) *Ensure that its done correctly:* mysql> SELECT * FROM pet; +--------+---------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+---------+---------+------+------------+-------+ | Martin | Joe | dog | m | 2010-01-30 | NULL | | Crumpy | Simpson | cat | m | 2011-02-20 | NULL | | Snoopy | Bob | dog | f | 2012-03-30 | NULL | +--------+---------+---------+------+------------+-------+ 3 rows in set (0.00 sec) ---- ## Delete DataBase * `DROP DATABASE db_name;` - **Be careful with it, it will delete all your data.** * Once db is dropped, u can check it via `SHOW DATABASES;` ---- ## see Also * [SQL Tutorial](http://www.tutorialspoint.com/sql/) * [MySQL 5.7 Reference Manual](http://dev.mysql.com/doc/refman/5.7/en/index.html)