Raspberry Pi_Eng_24.2.5 Database SQL


Published Book on Amazon


All of IOT Starting with the Latest Raspberry Pi from Beginner to Advanced – Volume 1
All of IOT Starting with the Latest Raspberry Pi from Beginner to Advanced – Volume 2


출판된 한글판 도서


최신 라즈베리파이(Raspberry Pi)로 시작하는 사물인터넷(IOT)의 모든 것 – 초보에서 고급까지 (상)
최신 라즈베리파이(Raspberry Pi)로 시작하는 사물인터넷(IOT)의 모든 것 – 초보에서 고급까지 (하)


Original Book Contents


24.2.5  Database SQL

 

24.2.5.1    Introduction of Database SQL

 

All relational databases use special instruction called SQL (Structured Query Language) to manage their internal data. By using SQL, you can define the type and content of data in the database, to enter data into the database, to modify the data entered, and to view the data entered.

 

The main functions of SQL that are necessary for database management are as follows:

    Query database        -- SHOW

    Define database       -- CREATE/DROP DATABASE

    Manage Privilege      -- GRANT

    Define table             -- CREATE/CHANGE/DROP TABLE

    Query table info.      -- DESCRIBE/EXPLAIN TABLE

    Enter table data       -- INSERT TABLE

    Modify table data     -- UPDATE TABLE

    Delete table data      -- DELETE TABLE

    Query table data      -- SELECT TABLE

 

We will not describe all the features of the SQL here, and we will only introduce the functionality to the minimum extent necessary to explain how it works. Additional learning should be done in a separate way.


 

24.2.5.2    Querying Database Information "SHOW" SQL

 

In MySQL, you can use "SHOW" command to query information about server state, database, and table

 

SHOW  <database-information-object>   ;

 

The following are the main items that can be processed by "SHOW" command.

    SHOW DATABASES [like_or_where]                             -- MySQL database list

    SHOW GRANTS FOR user

    SHOW TABLE STATUS [FROM db_name] [like_or_where]  -- status info. of tables

    SHOW [FULL] TABLES [FROM db_name] [like_or_where]  -- table list

 

Next is the command to check which database is in MySQL.

 

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

+--------------------+

3 rows in set (0.00 sec)

 

The following is a command to check what tables are in the current database "mysql".

 

mysql> show tables;

+---------------------------+

| Tables_in_mysql           |

+---------------------------+

| columns_priv              |

| db                        |

| event                     |

~ Skip

| time_zone_transition_type |

| user                      |

+---------------------------+

24 rows in set (0.01 sec)


 

24.2.5.3    Creating Database "CREATE DATABASE" SQL

 

All data managed by MySQL is stored inside the database. Therefore, if you want to save specific data, a database must be created in advance to store the data. In MySQL database system, multiple databases can be defined and operated concurrently.

 

To do this, you must have "CREATE" privilege on the database.

 

Use "CREATE DATABASE" command as follows to create a new database.

 

CREATE  DATABASE    [IF NOT EXISTS]   <database-name>    [option]

 

"IF NOT EXISTS" statement ensures that errors do not occur by creating database only if it does not exist, and doing no operation if the database does exist.

 

Next is creating a new database "raspi_db" in MySQL and look up the results again.

 

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

+--------------------+

3 rows in set (0.00 sec)

mysql> create database raspi_db ;

Query OK, 1 row affected (0.00 sec)

 

mysql> show databases ;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| raspi_db           |

+--------------------+

4 rows in set (0.00 sec)


 

24.2.5.4    Deleting Database "DROP DATABASE" SQL

 

To completely remove unused data, you can delete the previously defined database. The command to use in this case is "DROP DATABASE" command.

 

This command not only deletes database, but also deletes all the tables and all the data stored in those table.

 

To do this, you must have "DROP" privilege on the database.

 

DROP  DATABASE   [IF EXISTS]    <database-name>   

 

"IF NOT EXISTS" statement ensures that errors do not occur by deleting database only if it exists, and doing no operation if it does not exist.

 

Next is deleting the database "raspi_db" defined in MySQL and checking the result.

 

mysql> show databases ;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| raspi_db           |

+--------------------+

4 rows in set (0.00 sec)

mysql> drop database raspi_db ;

Query OK, 0 rows affected (0.02 sec)

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

+--------------------+

3 rows in set (0.00 sec)

 


 

24.2.5.5    Creating DB User"CREATE USER"/"DROP USER" SQL

 

All users accessing MySQL must be created in advance and given appropriate privilege.

 

A database user is a user that is distinct from operating system user and is self-defined in database, independent of the operating system.

 

To create a database user, use "CREATE USER" command.

 

CREATE USER   

             < 'user-id'@'host-name'  [  IDENTIFIED BY 'password'  ]     >

[ ,< 'user-id'@'host-name'  [  IDENTIFIED BY 'password'  ]     > ] ...

 

When a database user is created, its contents are stored in table "user" of database "mysql".

 

If "IDENTIFIED BY" is specified for a newly created user-id, a password can be specified together with the user-id. If no password is specified, the user-id is created without a password.

 

To delete an existing database user, use "DROP USER" command.

 

DROP USER

             < 'user-id'@'host-name' > 

           [ ,< 'user-id'@'host-name' > ]...

 

The following is checking information about table "user" in database "mysql".

 

mysql> use mysql ;

Database changed

mysql> describe user ;

+------------------------+----------------------------------+------+-----+---------+-------+

| Field                  | Type                             | Null | Key | Default | Extra |

+------------------------+----------------------------------+------+-----+---------+-------+

| Host                   | char(60)                         | NO   | PRI |         |       |

| User                   | char(16)                         | NO   | PRI |         |       |

| Password               | char(41)                         | NO   |     |         |       |

| Select_priv            | enum('N','Y')                    | NO   |     | N       |       |

| Insert_priv            | enum('N','Y')                    | NO   |     | N       |       |

| Update_priv            | enum('N','Y')                    | NO   |     | N       |       |

| Delete_priv            | enum('N','Y')                    | NO   |     | N       |       |

| Create_priv            | enum('N','Y')                    | NO   |     | N       |       |

| Drop_priv              | enum('N','Y')                    | NO   |     | N       |       |

~ Skip

~ Skip

| max_user_connections   | int(11) unsigned                 | NO   |     | 0       |       |

| plugin                 | char(64)                         | YES  |     |         |       |

| authentication_string  | text                             | YES  |     | NULL    |       |

+------------------------+----------------------------------+------+-----+---------+-------+

42 rows in set (0.03 sec)

 

Next is creating 'admin'@'localhost' in database "raspi_db" and checking the user-id information stored in table "user" of database "mysql". You can see that the corresponding user-id is created.

 

mysql> create user  'admin'@'localhost identified by  'xxxxxx'   ;

Query OK, 0 rows affected (0.00 sec)

mysql> use mysql ;

Database changed

mysql> select Host, User from user ;

+-----------------+------------------+

| Host            | User             |

+-----------------+------------------+

| 127.0.0.1       | root             |

| ::1             | root             |

| localhost       | admin            |

| localhost       | debian-sys-maint |

| localhost       | pi               |

| localhost       | root             |

+-----------------+------------------+

7 rows in set (0.01 sec)


 

24.2.5.6    Managing User Privilege "GRANT"/"REVOKE" SQL

 

For all operations that are performed in MySQL, the particular privileges must be granted beforehand. All privileges are pre-granted to user "root".

 

Use "GRANT" command to grant a new privilege and "REVOKE" command to revoke privilege.

 

GRANT

       priviage_type [(column_list)]        

[ ,priviage_type [(column_list)]] ...

    ON [ TABLE  | FUNCTION  | PROCEDURE ]   

< *  | *.*  | db_name.*  | db_name.tbl_name  | tbl_name  | db_name.routine_name  >

    TO    < 'user-id'@'host-name'  [ IDENTIFIED BY 'password'  ]  >

  [ ,< 'user-id'@'host-name'  [ IDENTIFIED BY 'password'  ]  > ] ...

REVOKE

             priviage_type [(column_list)]

      [ ,priviage_type [(column_list)]] ...

    ON [ TABLE  |  FUNCTION  | PROCEDURE ]

< *  | *.*  | db_name.*  | db_name.tbl_name  | tbl_name  | db_name.routine_name  >

    FROM   < 'user-id'@'host-name'

               [ ,< 'user-id'@'host-name' > ]...

 

Wildcard (*) can be used to specify the database or table to which the privilege applies. If wildcard is specified, it means that it applies to all objects that satisfy the specified condition.

 

When the user-id specified in "GRANT" command is not a user-id already defined in the database, if there is no special restriction on creating a user arbitrarily, a new user-id is created and the user-id is granted the privilege. By specifying "IDENTIFIED BY" for a newly created user-id, a password can be specified together with the user-id. If no password is specified, a user-id is created without a password.

 

If you use wildcard (%) as part of the name when you specify "host_name", it means that the privileges apply to all cases where satisfies the condition. Refer the following example.

    %.com                  -- applies to all hosts of form 'xxxx.com'.

    %.example.net        -- applies to all hosts of form 'xxxx.example.net'.

 

Privilege types available in "GRANT" and "REVOKE" command are as follows.

 

Privilege

Meaning and Grantable Levels

ALL [PRIVILEGES]

Grant all privileges at specified access level except GRANT OPTION

ALTER

Enable use of ALTER TABLE. Levels: Global, database, table.

ALTER ROUTINE

Enable stored routines to be altered or dropped. Levels: Global, database, procedure.

CREATE

Enable database and table creation. Levels: Global, database, table.

CREATE ROUTINE

Enable stored routine creation. Levels: Global, database.

CREATE TEMPORARY TABLES

Enable use of CREATE TEMPORARY TABLE. Levels: Global, database.

CREATE USER

Enable use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES. Level: Global.

CREATE VIEW

Enable views to be created or altered. Levels: Global, database, table.

DELETE

Enable use of DELETE. Level: Global, database, table.

DROP

Enable databases, tables, and views to be dropped. Levels: Global, database, table.

EXECUTE

Enable the user to execute stored routines. Levels: Global, database, table.

GRANT OPTION

Enable privileges to be granted to or removed from other accounts. Levels: Global, database, table, procedure, proxy.

INDEX

Enable indexes to be created or dropped. Levels: Global, database, table.

INSERT

Enable use of INSERT. Levels: Global, database, table, column.

LOCK TABLES

Enable use of LOCK TABLES on tables for which you have the SELECT privilege. Levels: Global, database.

PROCESS

Enable the user to see all processes with SHOW PROCESSLIST. Level: Global.

REFERENCES

Enable foreign key creation. Levels: Global, database, table, column.

RELOAD

Enable use of FLUSH operations. Level: Global.

SELECT

Enable use of SELECT. Levels: Global, database, table, column.

SHOW DATABASES

Enable SHOW DATABASES to show all databases. Level: Global.

SHOW VIEW

Enable use of SHOW CREATE VIEW. Levels: Global, database, table.

SHUTDOWN

Enable use of mysqladmin shutdown. Level: Global.

SUPER

Enable use of other administrative operations such as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin debug command. Level: Global.

TRIGGER

Enable trigger operations. Levels: Global, database, table.

UPDATE

Enable use of UPDATE. Levels: Global, database, table, column.

USAGE

Synonym for "no privileges"

 

Next is seeing the status before executing "grant" command.

 

mysql> show grants for 'pi'@'localhost' ;

ERROR 1141 (42000): There is no such grant defined for user 'pi' on host 'localhost'

 

Next is running "GRANT" command to grant privilege and specify password to "pi@localhost" account at once, and then checking privilege status again. You can see the specified privilege.

 

mysql> grant all on  *.*  to 'pi'@'localhost' identified by 'xxxxxx' ;

Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'pi'@'localhost' ;

+--------------------------------------------------------------------------------+

| Grants for pi@localhost                                                                                            |

+--------------------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'pi'@'localhost' IDENTIFIED BY PASSWORD '*9817606F8D7B598221D07445C7A03B71A6E83239' |

+--------------------------------------------------------------------------------+

1 row in set (0.01 sec)


 

24.2.5.7    Creating Table "CREATE TABLE" SQL

 

All data managed by MySQL is stored in a particular table inside a database. Therefore, if you want to save data, a specific table must be created in a database for storing data in advance.

 

Before creating a table, you must specify database for use with "use" command. To do this requires "CREATE" privilege on the table.

 

To create a new table, use "CREATE TABLE" command as follows.

 

CREATE  TABLE     [IF NOT EXISTS]   <table-name> 

(

<col_name>  <data_type>  [NOT NULL  |  NULL]  [DEFAULT default_value]

             [AUTO_INCREMENT]  [UNIQUE [KEY]  |  [PRIMARY] KEY]    ,

<col_name>  <data_type> [NOT NULL  |  NULL]  [DEFAULT default_value]

             [AUTO_INCREMENT]  [UNIQUE [KEY]  |  [PRIMARY] KEY]    ,

<col_name>  <data_type> [NOT NULL  |  NULL]  [DEFAULT default_value]

             [AUTO_INCREMENT]  [UNIQUE [KEY]  |  [PRIMARY] KEY]

)

 

"IF NOT EXISTS" statement ensures that errors do not occur by creating table only if it does not exist, and doing no operation if the table does exist.

 

The major data types that can be used in each column are as follows:

    BIT[(length)]

    INT[(length)] [UNSIGNED] [ZEROFILL]

    INTEGER[(length)] [UNSIGNED] [ZEROFILL]

    REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]

    FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]

    DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]

    NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]

    DATE

    TIME

    TIMESTAMP

    DATETIME

    YEAR

    CHAR[(length)]

    VARCHAR(length)

    BLOB

 

Next, a new table "employee" will be created in database "raspi_db". The following are data types for each column.

    id                          -- INT                          -- PRIMARY KEY, AUTO_INCREMENT

    name                     -- VARCHAR(50)

    birth                      -- DATE

    position_code           -- CHAR(10)

    dept_code               -- CHAR(10)

    manager_name        -- VARCHAR(50)

    sales                      -- DECIMAL(10,0)

    salary_rate               -- NUMERIC(6,2)

 

Next is creating the table and confirming the result by "show" command.

 

mysql> create table  employee 

(  id            INT PRIMARY KEY  AUTO_INCREMENT ,   

   name           VARCHAR(50)                 ,   

   birth         DATE                        ,   

   position_code  CHAR(04)                    ,   

   dept_code     CHAR(04)                     ,   

   manager_name         VARCHAR(50)                  ,   

   sales         DECIMAL(10,0)                ,   

   salary        NUMERIC(5,0)

 );

Query OK, 0 rows affected (0.03 sec)

mysql> show tables ;

+--------------------+

| Tables_in_raspi_db |

+--------------------+

| employee           |

+--------------------+

1 row in set (0.00 sec)


 

24.2.5.8    Deleting Table "DROP TABLE" SQL

 

To completely remove unused data, you can delete the previously defined table. The command to use in this case is "DROP TABLE" command.

 

This command deletes table and deletes all the data contained in it as well. To do this requires "DROP" privilege on the table.

 

DROP   TABLE  [IF EXISTS]    < table -name>   

 

"IF EXISTS" statement ensures that errors do not occur by performing a delete operation only when a table exists, and not performing processing when there is no table.

 

The following is deleting the table "employee_new" defined in database "raspi_db" and querying the result again.

 

mysql> show tables ;

+--------------------+

| Tables_in_raspi_db |

+--------------------+

| employee           |

| employee_new       |

+--------------------+

2 rows in set (0.00 sec)

 

mysql> drop table employee_new ;

Query OK, 0 rows affected (0.02 sec)

 

mysql> show tables ;

+--------------------+

| Tables_in_raspi_db |

+--------------------+

| employee           |

+--------------------+

1 row in set (0.01 sec)

 


 

24.2.5.9    Querying Table Information "EXPLAIN"/"DESCRIBE" SQL

 

You can use "DESCRIBE" or "EXPLAIN" command to look up information about tables in a particular database.

 

<DESCRIBE | DESC | EXPLAIN>     <table-name>  [col_name]

 

You can query table related information with "SHOW" command. Refer to the followings.

    SHOW CREATE TABLE

    SHOW TABLE STATUS

    SHOW INDEX

 

The following is the result of checking the contents of "employee" table defined in "raspi_db" database. You can see that detailed information about each field defined in the table is displayed.

 

mysql> desc employee ;

+---------------+---------------+------+-----+---------+----------------+

| Field         | Type          | Null | Key | Default | Extra          |

+---------------+---------------+------+-----+---------+----------------+

| id            | int(11)       | NO   | PRI | NULL    | auto_increment |

| name          | varchar(50)   | YES  |     | NULL    |                |

| birth         | date          | YES  |     | NULL    |                |

| position_code | char(4)       | YES  |     | NULL    |                |

| dept_code     | char(4)       | YES  |     | NULL    |                |

| manager_name  | varchar(50)   | YES  |     | NULL    |                |

| sales         | decimal(10,0) | YES  |     | NULL    |                |

| salary        | decimal(5,0)  | YES  |     | NULL    |                |

+---------------+---------------+------+-----+---------+----------------+

8 rows in set (0.01 sec)

 

mysql>

 

 

 

 


 

24.2.5.10  Inserting Table Data "INSERT" SQL

 

Use "INSERT" command to enter data into a specific table in a database.

 

INSERT  INTO  <table-name> 

(   < col_name >     ,   < col_name >       ,   < col_name >          )

VALUES

(  {expr | DEFAULT}   ,   {expr | DEFAULT}     ,   {expr | DEFAULT}        ) 

INSERT  INTO  <table-name> 

SET  < col_name >  = {expr | DEFAULT}                      ,

      < col_name >  = {expr | DEFAULT}          ,

       < col_name >  = {expr | DEFAULT}  

 

Next, we will insert data into "employee" table defined in "raspi_db".

 

mysql> INSERT  INTO  employee  

   SET   name            = "John"   ,

         birth           = "1960-05-17",

         position_code   = "11111",

         dept_code       = "E1000",

         manager_name    = "Mike",

         sales           = "300000",

       salary          = "600000" ;

Query OK, 1 row affected, 3 warnings (0.01 sec)

mysql> select * from employee ;

+----+------+------------+---------------+-----------+--------------+--------+--------+

| id | name | birth      | position_code | dept_code | manager_name | sales  | salary |

+----+------+------------+---------------+-----------+--------------+--------+--------+

|  1 | John | 1960-05-17 | 1111          | E100      | Mike         | 300000 |  99999 |

|  2 | John | 1960-05-17 | 1111          | E100      | Mike         | 300000 |  99999 |

|  3 | John | 1960-05-17 | 1111          | E100      | Mike         | 300000 |  99999 |

+----+------+------------+---------------+-----------+--------------+--------+--------+

1 rows in set (0.00 sec)

mysql>  

 

The above example is the result of executing inserting operation three times and executing "select" command to inquire data in the table. You can see that all three data are entered, and if you look at "id" column for each data, you can see that they are automatically numbered sequentially, although no special value was specified at inserting operation.

24.2.5.11  Quering Table Data "SELECT" SQL

 

Use "SELECT" command to query data in a specific table in a database.

 

SELECT

    [ ALL | DISTINCT | DISTINCTROW ] 

< col_name | expression >  [, < col_name | expression > ...]

[ FROM        < tbl_name [[AS] alias]    [ table_join_reference  ] >

 [ WHERE         < col_name  < comparison-operator > expression >  [ < logical-operator > 

                    < col_name  < comparison-operator > expression >  ] ]

 

    [ GROUP BY { col_name | expr | position }    [ASC | DESC], ...]

 [ HAVING      < col_name  < comparison-operator > expression >  [ < logical-operator > 

                    < col_name  < comparison-operator > expression >  ] ]

 

    [ ORDER BY { col_name | expr | position }    [ASC | DESC], ...]  ]

 

    SELECT column

This specifies the column you want to query.

"*" means all columns.

If there are multiple columns, separate them with ",".

 

    FROM

This specifies table you want to query.

 

    WHERE

This specifies conditions of the data you want to query.

This normally specifies condition for column.

ex) name  = "Mike"

      sales  < "30000"

 

    GROUP BY

If the specified column combination has the same key value, it is processed as one. This can specify multiple columns.

 

    HAVING

This specifies condition for GROUP when using "GROUP BY".

 

    ORDER BY

This specifies the sort order of the queried data.

 

    table_join_ reference in FROM

    [ INNER | CROSS] JOIN               tbl_name [[AS] alias]       < join_expr>

    STRAIGHT_JOIN                       tbl_name [[AS] alias]       < join_expr> 

    {LEFT|RIGHT} [OUTER] JOIN         tbl_name [[AS] alias]       < join_expr>  

 

Here, <join_expr> has the format of <ON conditional_expr | USING (column_list)>.

 

    logical-operator

 

Name

Description

AND, &&

Logical AND

NOT, !

Negates value

||, OR

Logical OR

XOR

Logical XOR

 

    comparison-operator

 

Name

Description

<=>

NULL-safe equal to operator

=

Equal operator

>=

Greater than or equal operator

Greater than operator

<=

Less than or equal operator

Less than operator

!=, <>

Not equal operator

IS NULL

NULL value test

IS NOT NULL

NOT NULL value test

ISNULL( )

Test whether the argument is NULL

IS

Test a value against a boolean

IS NOT

Test a value against a boolean

BETWEEN ... AND ...

Check whether a value is within a range of values

NOT BETWEEN ... AND ...

Check whether a value is not within a range of values

LIKE

Simple pattern matching

NOT LIKE

Negation of simple pattern matching

IN( )

Check whether a value is within a set of values

NOT IN( )

Check whether a value is not within a set of values

INTERVAL( )

Return the index of the argument that is less than the first argument

GREATEST( )

Return the largest argument

LEAST( )

Return the smallest argument

 

The following is querying data for all columns in table "employee". "*" is used to display all columns.

 

mysql> select * from employee ;

+----+-------+------------+---------------+-----------+--------------+--------+--------+

| id | name  | birth      | position_code | dept_code | manager_name | sales  | salary |

+----+-------+------------+---------------+-----------+--------------+--------+--------+

|  1 | John  | 1960-05-17 | 1111          | E100      | Mike         | 300000 |  99999 |

|  2 | John  | 1960-05-17 | 1111          | E100      | Mike         | 300000 |  99999 |

|  3 | John  | 1960-05-17 | 1111          | E100      | Mike         | 300000 |  99999 |

|  4 | John  | 1960-05-17 | 1111          | E100      | Mike         | 300000 |  99999 |

|  5 | Bill  | 1960-05-17 | 4444          | E400      | Anderson     | 500000 |  99999 |

|  6 | James | 1970-05-17 | 6666          | D100      | Anderson     | 300000 |  99999 |

+----+-------+------------+---------------+-----------+--------------+--------+--------+

6 rows in set (0.01 sec)

mysql>  

 

The following is a processing result by selecting only some columns of table "employee" and specifying condition for one column in "WHERE" condition.

 

mysql> select id, name, sales from employee where  id >= 3;

+----+-------+--------+

| id | name  | sales  |

+----+-------+--------+

|  3 | John  | 300000 |

|  4 | John  | 300000 |

|  5 | Bill  | 500000 |

|  6 | James | 300000 |

+----+-------+--------+

4 rows in set (0.00 sec)

mysql>

 

The following is a processing result by selecting only some columns of table "employee" and specifying conditions for two columns in "WHERE" condition.

 

mysql> select id, name, sales from employee where  id >= 3 and sales=300000 ;

+----+-------+--------+

| id | name  | sales  |

+----+-------+--------+

|  3 | John  | 300000 |

|  4 | John  | 300000 |

|  6 | James | 300000 |

+----+-------+--------+

3 rows in set (0.01 sec)

mysql>


 

24.2.5.12  Changing Table Data "UPDATE" SQL

 

Use "UPDATE" command to change data of a specific table in a database.

 

UPDATE  table_name

SET  < col_name >  = {expr | DEFAULT}                     ,

     < col_name >  = {expr | DEFAULT}         ,

     < col_name >  = {expr | DEFAULT}  

[ WHERE  < col_name  < comparison-operator > expression >  [ < logical-operator > 

           < col_name  < comparison-operator > expression >  ] ]    

 

    SET column

This specifies the column you want to modify.

If there are multiple columns, separate them with ",".

If "DEFAULT" is specified, default value is assigned according to data type of the corresponding column.

 

    comparison-operator of WHERE          -- Refer to the descriptions of "SELECT".

    logical-operator of WHERE                -- Refer to the descriptions of "SELECT".

 

Next is querying some data from table "employee"

 

mysql> select * from employee where id <= 3 ;

+----+----------+------------+---------------+-----------+--------------+--------+--------+

| id | name     | birth      | position_code | dept_code | manager_name | sales  | salary |

+----+----------+------------+---------------+-----------+--------------+--------+--------+

|  1 | John     | 1960-05-17 | 1111          | E100      | Mike         | 300000 |  99999 |

|  2 | John     | 1960-05-17 | 1111          | E100      | Mike         | 300000 |  99999 |

|  3 | John     | 1960-05-17 | 1111          | E100      | Mike         | 300000 |  99999 |

+----+----------+------------+---------------+-----------+--------------+--------+--------+

 

 

The following is modifying the value for "name" of the data of which "id" column is "1" in table "employee", and then querying the above data again. You can see that the value for "name" in the data of which "id" column is "1" has been changed.

 

mysql> update employee set name = "Davinchi" where id = 1 ;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from employee where id <= 3 ;

+----+----------+------------+---------------+-----------+--------------+--------+--------+

| id | name     | birth      | position_code | dept_code | manager_name | sales  | salary |

+----+----------+------------+---------------+-----------+--------------+--------+--------+

|  1 | Davinchi | 1960-05-17 | 1111          | E100      | Mike         | 300000 |  99999 |

|  2 | John     | 1960-05-17 | 1111          | E100      | Mike         | 300000 |  99999 |

|  3 | John     | 1960-05-17 | 1111          | E100      | Mike         | 300000 |  99999 |

+----+----------+------------+---------------+-----------+--------------+--------+--------+


 

24.2.5.13  Deleting Table Data "DELETE" SQL

 

Use "DELETE" command to delete data from a specific table in a database.

 

DELETE  FROM   table_name

[ WHERE  < col_name  < comparison-operator > expression > [ < logical-operator > 

< col_name  < comparison-operator > expression >  ] ]    

 

    comparison-operator of WHERE          -- REFER to the descriptions of "SELECT".

    logical-operator of WHERE                -- REFER to the descriptions of "SELECT".

 

The following is querying some data from table "employee".

 

mysql> select * from employee where id <= 3 ;

+----+----------+------------+---------------+-----------+--------------+--------+--------+

| id | name     | birth      | position_code | dept_code | manager_name | sales  | salary |

+----+----------+------------+---------------+-----------+--------------+--------+--------+

|  1 | Davinchi | 1960-05-17 | 1111          | E100      | Mike         | 300000 |  99999 |

|  2 | John     | 1960-05-17 | 1111          | E100      | Mike         | 300000 |  99999 |

|  3 | John     | 1960-05-17 | 1111          | E100      | Mike         | 300000 |  99999 |

+----+----------+------------+---------------+-----------+--------------+--------+--------+

3 rows in set (0.01 sec)

 

The following is deleting data of which "id" column is "3" from table "employee", and then querying the above data again. You can see that the data of which "id" column is "3" has been deleted.

 

mysql> delete employee where id = 3 ;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from employee where id <= 3 ;

+----+----------+------------+---------------+-----------+--------------+--------+--------+

| id | name     | birth      | position_code | dept_code | manager_name | sales  | salary |

+----+----------+------------+---------------+-----------+--------------+--------+--------+

|  1 | Davinchi | 1960-05-17 | 1111          | E100      | Mike         | 300000 |  99999 |

|  2 | John     | 1960-05-17 | 1111          | E100      | Mike         | 300000 |  99999 |

+----+----------+------------+---------------+-----------+--------------+--------+--------+

2 rows in set (0.01 sec)