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) |