List of MySQL sentences for programming beginners * Personal memo
List of MySQL sentences for programming beginners * Personal memo
Describe the memo currently being studied
Please comment if you have any corrections or advice.
setup
| action |
SQL statement |
| Connect to MySQL |
mysql -u root -p |
| MySQL disconnect |
exit |
| change Password |
ALTER USER 'root'@'localhost' IDENTIFIED BY 'New password'; |
Database operation
| action |
SQL statement |
| Creating a database |
CREATE DATABASE database name default character set utf8; |
| Delete database |
DROP DATABASE database name; |
| Check the list of databases |
show databases; |
| Select database to operate |
USE database name; |
Table operation
| action |
SQL statement |
| Creating a table |
CREATE TABLE database name.Table name you want to create(●); |
| Delete table |
DROP TABLE database name.Table name you want to create; |
| Check the list of tables |
show tables; |
| Confirmation of table design |
describe books; |
- Example of column creation contents in the above table creation (●)
| action |
SQL statement |
| ID column |
ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY |
| String column |
Column name VARCHAR(Maximum number of characters) |
| Integer column |
Column name INT |
| Registration date and time automatic registration column |
Column name TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP |
CRUD operation
| action |
SQL statement |
| Data creation |
INSERT INTO table name(Column 1,Column 2, ...) VALUES (Value 1,Value 2, ...); |
| Data acquisition |
SELECT column name FROM table name; |
| Data update |
UPDATE table name SET column name 1=Value 1,Column name 2=Value 2, …; |
| Delete data |
DELETE FROM table name; |
Other
| Operation example |
SQL statement |
| WHERE clause example |
CRUD statement WHERE column name=value; |
| ORDER BY clause example |
CRUD statement ORDER BY Sorting criteria column |
| Analysis function |
SELECT function(Column name)FROM table name; |
| Join table |
INNER JOIN table name ON condition |