Database MySQL part1

Database Introduction

A database (DATABASE,DB) is a collection of organized, shareable data that is stored in a computer for a long period of time. The data in the database is organized, described and stored in a certain mathematical model, with small redundancy, high data independence and extensibility, and can be shared by various users.

Relational database: MYSQL, Oracle, SQL Server, DB2, SQLite,

Non-relational database: Redis MongoDB

MySQL Database

Installation

Installation on Windows is slightly

Linux installed on a slightly

Start

Slightly

Port number 3306

Set Password

1 mysqladmin-uroot password ' 123 '   #设置初始密码, the initial password is empty so the-P option does not use 2 mysqladmin-u root-p123 password ' 1234 ' #修改root用户密码

Log in to Database

1 MySQL               #本地登录, Default user root, empty password, user for [email protected]2 mysql-uroot-p1234 #本地登录, specify user name and password, user for [email protected]3 mysql- Uroot-p1234-h 192.168.31.95 #远程登录, user is [email protected]

SQL statements

SQL statements: Structured statements, SQL is a set of operational commands created specifically for the database, and is a fully functional database language. Almost all relational databases have support for SQL statements

SQL Statement Specification:
1, case-insensitive (the command suggests capitalization, because during SQL parsing, the database automatically converts lowercase commands to uppercase, and when there is a large number of SQL statements, the conversion generates some overhead)
2. Use a semicolon as the ending symbol
3, Comments (single line--, multi-line/* */)

SQL statement Action object: operation of database, operation of data table, operation of table record

Operation of the database
1. View all databases

mysql> SHOW databases;+--------------------+| Database           |+--------------------+| information_schema | | mysql              | | performance_schema | | sakila             | | sys                || World              |+--------------------+6 rows in Set (0.00 sec)

2. Create a database (that is, create a corresponding folder on disk)

Syntax: CREATE DATABASE [if not exists] db_name [character set XXX];

command selectable character encoding of database name database

mysql> CREATE DATABASE IF not EXISTS student CHARACTER SET UTF8; Query OK, 1 row affected (0.01 sec) mysql> SHOW databases;+--------------------+| Database           |+--------------------+| information_schema | | mysql              | | performance_schema | | Sakila             | | student            | | sys                | | World              |+--------------------+7 rows in Set (0.00 sec)

3. Query how a database is created (create command)

Syntax: show CREATE DATABASE db_name;

Command database name

mysql> SHOW CREATE DATABASE student;+----------+--------------------------------------------------------------- ---+| Database | Create Database                                                  |+----------+------------------------------------------------------------------+| Student  | CREATE DATABASE ' student '/*!40100 DEFAULT CHARACTER SET UTF8 */|+----------+----------------------------------------- -------------------------+1 Row in Set (0.04 sec)

4. Modify Database character encoding

Syntax: ALTER DATABASE db_name character set GBK;

Command database name Modify character encoding

mysql> ALTER DATABASE student CHARACTER SET GBK; Query OK, 1 row affected (0.01 sec) mysql> SHOW CREATE DATABASE student;+----------+---------------------------------- -------------------------------+| Database | Create Database                                                 |+----------+-----------------------------------------------------------------+| Student  | CREATE DATABASE ' student '/*!40100 DEFAULT CHARACTER SET GBK */|+----------+------------------------------------------ -----------------------+1 Row in Set (0.00 sec) #注: Modify the database name, directly locate the database folder, modify the folder name

5. Delete Database

mysql> DROP DATABASE student; Query OK, 0 rows Affected (0.00 sec) mysql> SHOW databases;+--------------------+| Database           |+--------------------+| information_schema | | mysql              | | performance_schema | | sakila             | | sys                || World              |+--------------------+6 rows in Set (0.00 sec)

6, using the database (that is, switching databases, into the database)

mysql> use mysql;database changedmysql> SELECT Database (); +------------+| Database () |+------------+| MySQL      |+------------+1 row in Set (0.00 sec)

--Note: There is no way to return to the previous state after entering a database, but you can switch by using the

View the database you are currently using Select Database ();

Operation of the data table

Columns within a table: Fields

Rows within a table: Table records

1. Create a table (enter the library): The last field does not end with a comma

Syntax: CREATE TABLE table_name (

field firstname data type [constraint],
field firstname data type [constraint],
field firstname data type [constraint],
....
Field name segment data type [constraint]) [character set XXX];

Constraint: (not NULL + unique equals primary key), more than three constraints
Primary key (primary key): Non-null and unique
NOT NULL: non-null constraint
Unique: Single constraint

mysql> CREATE DATABASE Teachers; Query OK, 1 row Affected (0.00 sec) mysql> use teachers;database changedmysql> CREATE TABLE teacher1 (          I d int primary key,          name VARCHAR (a) not null,--age          int,--          birth DATE); Query OK, 0 rows affected (0.51 sec)

2. View the table of the database

mysql> SHOW tables;+--------------------+| Tables_in_teachers |+--------------------+| Teacher1           |+--------------------+1 row in Set (0.00 sec)

3. View table structure

Mysql> DESC teacher1;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| ID    | int     | NO   | PRI | NULL    |       | | name  | varchar (20) | NO   |     | NULL    |       | | age   | int |     YES  |     | NULL    |       | | birth | date        | YES  |     | NULL    |       | +-------+-------------+------+-----+---------+-------+4 rows in Set (0.00 sec)

4. View CREATE TABLE command

mysql> SHOW CREATE TABLE teacher1;+----------+----------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ------------------------+| Table |                                                                                                                                                                                           Create Table |+----------+-------------------------------- --------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------+| Teacher1 |  CREATE TABLE ' teacher1 ' (' id ' int (one) ' is not null, ' name ' varchar () is not null, ' age ' int (one) DEFAULT NULL, ' birth ' date Default NULL, PRIMARY KEY (' id ')) engine=innodb default Charset=utf8 |+----------+------------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 Row in Set (0.00 Sec

5. Add column (field): You can add more than one column, similar to the format created by the table.

Syntax: ALTER TABLE TAB_NAME add [column] name type [integrity constraint] [first|after field name];

command table name add Column definition column location, first front, after in a field

mysql> ALTER TABLE teacher1 ADD COLUMN sex INT; Query OK, 0 rows affected (0.58 sec) records:0  duplicates:0  warnings:0mysql> DESC teacher1;+-------+-------- -----+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| ID    | int     | NO   | PRI | NULL    |       | | name  | varchar (20) | NO   |     | NULL    |       | | age   | int |     YES  |     | NULL    |       | | birth | date        | YES  |     | NULL    |       | | sex   | int |     YES  |     | NULL    |       | +-------+-------------+------+-----+---------+-------+

Note: column can be omitted

mysql>  ALTER TABLE teacher1 ADD addr VARCHAR (a) first; Query OK, 0 rows affected (0.53 sec) records:0  duplicates:0  warnings:0mysql> DESC teacher1;+-------+-------- -----+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| Addr  | varchar (20) | YES  |     | NULL    | |       | ID    | int     | NO   | PRI | NULL    |       | | name  | varchar (20) | NO   |     | NULL    |       | | age   | int |     YES  |     | NULL    |       | | birth | date        | YES  |     | NULL    |       | | sex   | int |     YES  |     | NULL    |       | +-------+-------------+------+-----+---------+-------+6 rows in Set (0.00 sec)

6. Modify a column type

Syntax: ALTER TABLE tab_name Modify column name type [integrity constraint] [first|after field name];

mysql>  ALTER TABLE teacher1 MODIFY sex CHAR after ID; Query OK, 0 rows affected (0.82 sec) records:0  duplicates:0  warnings:0mysql> DESC teacher1;+-------+-------- -----+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| Addr  | varchar (20) | YES  |     | NULL    | |       | ID    | int     | NO   | PRI | NULL    |       | | sex   | char (1)     | YES  |     | NULL    |       | | name  | varchar (20) | NO   |     | NULL    |       | | age   | int |     YES  |     | NULL    |       | | birth | date        | YES  |     | NULL    |       | +-------+-------------+------+-----+---------+-------+6 rows in Set (0.00 sec)

7. Modify column names

Syntax: ALTER TABLE TAB_NAME change [column] columns name new column name type [integrity constraint] [first|after field name];

mysql>  ALTER TABLE teacher1 change birth birthday DATE; Query OK, 0 rows affected (0.12 sec) records:0  duplicates:0  warnings:0mysql> DESC teacher1;+----------+----- --------+------+-----+---------+-------+| Field    | Type        | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| Addr     | varchar (20) | YES  |     | NULL    | |       | ID       | int     | NO   | PRI | NULL    |       | | sex      | char (1)     | YES  |     | NULL    |       | | name     | varchar (20) | NO   |     | NULL    |       | | age      | int |     YES  |     | NULL    |       | | birthday | date        | YES  |     | NULL    |       | +----------+-------------+------+-----+---------+-------+6 rows in Set (0.00 sec)

8. Delete a column
Syntax: ALTER TABLE TAB_NAME drop [column] name;

mysql>  ALTER TABLE teacher1 DROP sex; Query OK, 0 rows affected (0.50 sec) records:0  duplicates:0  warnings:0mysql> DESC teacher1;+----------+----- --------+------+-----+---------+-------+| Field    | Type        | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| Addr     | varchar (20) | YES  |     | NULL    | |       | ID       | int     | NO   | PRI | NULL    |       | | name     | varchar (20) | NO   |     | NULL    |       | | age      | int |     YES  |     | NULL    |       | | birthday | date        | YES  |     | NULL    |       | +----------+-------------+------+-----+---------+-------+5 rows in Set (0.00 sec)

9. Modify the table name
Syntax: Rename table name to a new name;

Mysql> RENAME TABLE teacher1 to teacher; Query OK, 0 rows affected (0.14 sec) mysql> DESC teacher1; ERROR 1146 (42S02): Table ' teachers.teacher1 ' doesn ' t existmysql> DESC teacher;+----------+-------------+------+--- --+---------+-------+| Field    | Type        | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| Addr     | varchar (20) | YES  |     | NULL    | |       | ID       | int     | NO   | PRI | NULL    |       | | name     | varchar (20) | NO   |     | NULL    |       | | age      | int |     YES  |     | NULL    |       | | birthday | date        | YES  |     | NULL    |       | +----------+-------------+------+-----+---------+-------+5 rows in Set (0.00 sec)

10. Fix the character set used in the table
Syntax: ALTER TABLE name character set code;

mysql> ALTER TABLE teacher CHARACTER SET GBK; Query OK, 0 rows affected (0.09 sec) records:0 duplicates:0 warnings:0mysql> SHOW CREATE TABLE teacher;+---------+- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ---------------------------------------------------+| Table |                                                                                                                                                                                                                                        Create Table |+---------+--------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------+| Teacher | CREATE TABLE ' teacher ' (' addr ' varchar) CHARACTER SET UTF8 DEFAULT NULL, ' id ' int (one-by-one) not NULL, ' name ' varchar (20) CHARACTER SET UTF8 NOT null, ' age ' int (one) default null, ' birthday ' date default null, PRIMARY KEY (' id ')) engine=innod B DEFAULT charset=gbk |+---------+------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------+1 Row in Set (0.00 sec)

11. Remove the table

Syntax: DROP TABLE table_name

mysql> DROP TABLE Teacher; Query OK, 0 rows affected (0.19 sec) mysql> SHOW TABLES; Empty Set (0.00 sec)

 

Supplement: MySQL table data type

MySQL supports a variety of data types and can be broadly divided into three categories: numeric, date/time, and string (character) types

Numeric type

Date/Time Type

The date and time types that represent time values are datetime, date, TIMESTAMP, hour, and year.

Each time type has a valid value range and a value of "0", and a value of "0" is used when specifying an illegal MySQL value that cannot be represented.

String type

The string type refers to Char, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, enum, and set.

Char and varchar types are similar, but they are saved and retrieved in different ways. They are also different in terms of their maximum length and whether trailing spaces are retained. No case conversions are made during the storage or retrieval process.

CHAR: Specify the number of characters

VARCHAR: Maximum length

Binary and varbinary classes are similar to char and varchar, but they contain binary strings rather than binary strings. That is, they contain a byte string instead of a character string.

A blob is a binary large object that can hold a variable amount of data. There are 4 types of blobs: Tinyblob, BLOBs, Mediumblob, and Longblob. They can only accommodate the maximum length of a value differently.

There are 4 types of text: Tinytext, text, Mediumtext, and Longtext. These correspond to 4 types of blobs, with the same maximum length and storage requirements.

Database MySQL part1