linux-Database 1

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

So it doesn't matter ...

MySQL Database

Installation

Installation on Windows is slightly

Linux installed on a slightly

Start

Slightly

Port number 3306

Set Password

1 ' 123 '   # set initial password, empty initial password so the-P option is not used 2 ' 1234 ' # To modify the root user password

Log in to Database

1 mysql               # Local login, Default user root, blank password, user for [email protected]2# Local login, Specify user name and password, user is [email protected]3# telnet, user is [email protected]

Forgotten password 1: From the deletion of the library to the escape

Slightly

Forgot password 2: Cross the Authorization library

Slightly

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 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>1 row affected (0.01 sec) MySQL> SHOW DATABASES; +--------------------+| Database           |+--------------------+| information_schema | | mysql              | | performance_schema | | Sakila             | | student            | | sys                | | World 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 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>1 row affected (0.01 sec) MySQL> SHOW CREATE DATABASE student; +----------+-----------------------------------------------------------------+| Database | Create Database                                                 |+----------+-----------------------------------------------------------------+| Student   in Set (0.00 sec)# Note: 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 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 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>1 row Affected (0.00 sec) MySQL> Use teachers;database changedmysql>  CREATE TABLE teacher1 (    -          ID INT primary KEY,    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 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    |        in Set (0.00 sec)

4. View CREATE TABLE command

Mysql>SHOW CREATE TABLE teacher1;+----------+--------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------------------------+| Table |                                                                                                                                                                                           Create Table |+----------+-------------------------------- --------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------+| Teacher1 |CREATE TABLE ' teacher1 ' (' id ' int (11) not NULL, ' name ' varchar (20) Not NULL, ' age ' int (11default NULL, ' birth ' date default NULL, PRIMARY KEY (' id ')) ENGINE=innodb DEFAULT Charset=utf8 |+----------+----------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ------------------+1 RowinchSet (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    |        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    |        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    |        in Set (0.00 sec)

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

mysql> 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    |        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.14sec) MySQL>DESC Teacher1; ERROR1146 (42S02): Table'Teachers.teacher1'doesn'T existMysql>DESC teacher;+----------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| Addr | varchar (20) |     YES | |       NULL | || ID | Int (11) | NO | PRI |       NULL | || name | varchar (20) |     NO | |       NULL | || Age | Int (11) |     YES | |       NULL | || Birthday | Date |     YES | |       NULL | |+----------+-------------+------+-----+---------+-------+5 rowsinchSet (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.09sec) records:0 duplicates:0 Warnings:0mysql>SHOW CREATE TABLE teacher;+---------+---------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------+| Table |                                                                                                                                                                                                                                        Create Table |+---------+--------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------+| Teacher |CREATE TABLE ' teacher ' (' addr ' varchar (20) CHARACTER SET UTF8 DEFAULT NULL, ' id ' int (11) not NULL, ' name ' varchar (20) CHARACTER SET UTF8 not NULL, ' age ' int (11default NULL, ' birthday ' date default null, PRIMARY KEY (' id ')) ENGINE=innodb DEFAULT charset=gbk |+---------+------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------+1 RowinchSet (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.

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.

Table record actions: Next day

linux-Database 1