MySQL to create a database and simple addition, deletion and modification
We can use the create command to create a database after logging in to the
MySQL service. The syntax is as follows:
CREATE DATABASE database name;
Sign in:
Enter password: ***********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.22-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>mysql> CREATE DATABASE IF NOT EXISTS CODES DEFAULT CHARSET utf8 COLLATE utf8_general_ci;//Create a database
Output result:
mysql> CREATE DATABASE IF NOT EXISTS CODES DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql>
Create a database, the role of this command:
- If the database does not exist, it is created, and if it exists, it is not created.
- Create a CODES database and set the code set to utf8
MySQL delete database
To log in to the
MySQL server as a normal user, you may need specific permissions to create or delete the MySQL database, so we use the root user to log in, and the root user has the highest permissions.
In the process of deleting the database, you must be very careful, because after executing the delete command, all data will disappear.
drop command to delete the database
Drop command format:
drop database <database name>;
For example, delete the database named RUNOOB:
mysql> drop database CODES;
Results of the:
mysql> drop database CODES;
Query OK, 0 rows affected (0.01 sec)
mysql>
MySQL select database
After you connect to the
MySQL database, there may be multiple operational databases, so you need to select the database you want to operate.
Select the MySQL database from the command prompt window
You can easily select a specific database in the mysql> prompt window. You can use SQL commands to select the specified database.
Examples
The following example selects the database CODES:
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| codes |
| mysql |
| performance_schema |
| sakila |
| sys |
| tuberonion |
| world |
+--------------------+
8 rows in set (0.00 sec)
mysql> USE CODES;
Database changed
mysql>
After executing the above command, you have successfully selected the RUNOOB database, which will be executed in the RUNOOB database in subsequent operations.
Note: All database names, table names, and table fields are case sensitive. So you need to enter the correct name when using SQL commands.
MySQL data type
The types of data fields defined in MySQL are very important for the optimization of your database.
MySQL supports multiple types, which can be roughly divided into three categories: numeric, date/time, and string (character) types.
Numeric type
MySQL supports all standard SQL numeric data types.
These types include strict numeric data types (INTEGER, SMALLINT, DECIMAL, and NUMERIC), and approximate numeric data types (FLOAT, REAL, and DOUBLE PRECISION).
The keyword INT is a synonym for INTEGER, and the keyword DEC is a synonym for DECIMAL.
The BIT data type holds bit field values and supports MyISAM, MEMORY, InnoDB, and BDB tables.
As an extension of the SQL standard, MySQL also supports the integer types TINYINT, MEDIUMINT, and BIGINT.
Date and time type
Date and time types representing time values are DATETIME, DATE, TIMESTAMP, TIME, and YEAR.
Each time type has a valid value range and a "zero" value, and the "zero" value is used when specifying an illegal value that MySQL cannot represent.
The TIMESTAMP type has a proprietary automatic update feature, which will be described later.
String type
The string type refers to CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET. This section describes how these types work and how to use them in queries.
CHAR and VARCHAR types are similar, but they are stored and retrieved differently. Their maximum length and whether trailing spaces are reserved are also different. No case conversion is performed during storage or retrieval.
BINARY and VARBINARY are similar to CHAR and VARCHAR, except that they contain binary strings rather than non-binary strings. That is, they contain byte strings instead of character strings. This means that they have no character set, and sorting and comparison are based on the numeric values of the column value bytes.
BLOB is a binary large object that can hold a variable amount of data. There are 4 BLOB types: TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. They differ in the storage range that can be accommodated.
There are 4 types of TEXT: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. Corresponding to these 4 BLOB types, the maximum length that can be stored is different, which can be selected according to the actual situation.
MySQL 5.0 and above:
1. The length of a Chinese character depends on the encoding:
UTF-8: one Chinese character = 3 bytes
GBK: one Chinese character = 2 bytes
2. varchar(n) means n characters, regardless of Chinese characters and English, Mysql can store n characters, only the actual byte length is different
3. MySQL check the length, you can use SQL language to view:
select LENGTH(fieldname) from tablename
For example, I query my own database (this database, I did not give):
mysql> SELECT * FROM ht_app;
+----------------------+---------------------+---------------------+-------------+----------------------------------+--------------------------------------------------------+----------------+---------------------------------------------------+------------------+---------------------------------------------------+
| app_id | gmt_create | gmt_modified | app_version | app_md5 | app_url | app_picture_id | app_picture_url | login_picture_id | login_picture_url |
+----------------------+---------------------+---------------------+-------------+----------------------------------+--------------------------------------------------------+----------------+---------------------------------------------------+------------------+---------------------------------------------------+
| com.hengtong.henchat | 2018-06-14 13:53:50 | 2018-06-28 13:53:54 | 1.0.1 | 29F85D405C47544B69CDC8948878BB5E | http://oo94hcyew.bkt.clouddn.com/app-version-1.0.1.apk | 20180622.png | G:\git\TransactionHT\src\main\resources\pictures\ | 20180621.png | G:\git\TransactionHT\src\main\resources\pictures\ |
+----------------------+---------------------+---------------------+-------------+----------------------------------+--------------------------------------------------------+----------------+---------------------------------------------------+------------------+---------------------------------------------------+
1 row in set (0.01 sec)
mysql> SELECT LENGTH(app_id) FROM ht_app;
+----------------+
| LENGTH(app_id) |
+----------------+
| 20 |
+----------------+
1 row in set (0.00 sec)
mysql>
MySQL create data table
The following information is required to create a MySQL data table:
Table Name
Table field name
Define each table field
grammar
The following is the general SQL syntax for creating a MySQL data table:
In the following example, we will create the data table runoob_tbl in the CODES database:
Example analysis:
If you don't want the field to be NULL, you can set the field's attribute to NOT NULL. If you enter the field's data as NULL when operating the database, you will get an error.
AUTO_INCREMENT defines the column as a self-increasing attribute, generally used for the primary key, the value will automatically increase by 1.
The PRIMARY KEY keyword is used to define the column as the primary key. You can use multiple columns to define the primary key, separated by commas.
ENGINE sets the storage engine, and CHARSET sets the encoding.
Create a table from the command prompt
The MySQL data table can be easily created through the mysql> command window. You can use the SQL statement CREATE TABLE to create a data table.
Examples
The following is an example of creating a data table codes_user:
mysql> USE CODES;
Database changed
mysql> SHOW TABLES;
Empty set (0.00 sec)
mysql> CREATE TABLE IF NOT EXISTS `codes_user`(
-> `user_id` INT UNSIGNED AUTO_INCREMENT,
-> `user_title` VARCHAR(100) NOT NULL,
-> `user_author` VARCHAR(40) NOT NULL,
-> `submission_date` DATE,
-> PRIMARY KEY ( `user_id` )
-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)
mysql> SHOW TABLES;
+-----------------+
| Tables_in_codes |
+-----------------+
| codes_user |
+-----------------+
1 row in set (0.00 sec)
mysql>
Note: The MySQL command terminator is a semicolon (;).
MySQL delete data table
Deleting a data table in MySQL is very easy to operate, but you should be very careful when performing a delete table operation, because all data will disappear after the delete command is executed.
grammar
The following is the general syntax for deleting MySQL data tables:
CREATE TABLE table_name (column_name column_type);
Delete the data table in the command prompt window
In the mysql> command prompt window, delete the data table SQL statement is DROP TABLE:
CREATE TABLE IF NOT EXISTS `codes_user`(
`user_id` INT UNSIGNED AUTO_INCREMENT,
`user_title` VARCHAR(100) NOT NULL,
`user_author` VARCHAR(40) NOT NULL,
`submission_date` DATE,
PRIMARY KEY ( `user_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
