2 Database Installation Application

[Email protected]:~$ mysql-uroot-pmysql

CREATE DATABASE: Create database name Charset=utf-8; creating table Table        name (field type constraint);              not Insert data inserts into  table name (...) value (...); Modify Data   Update table name set field name = value; Delete Data    from   table name;        Tombstone: Alter into table name add Isdelete bit default 0;                  Update table name set Isdelete=1 from table name

1 remote connection to MySQL

• Installation

sudo apt-get install mysql-server mysql-Client then follow the prompts to enter

• Management Services

Start service MySQL start stop service MySQL stop restart service mysql restart

• Allow remote connections

# Locate the MySQL configuration file and modify the sudo vi/etc/mysql/mysql.conf.d/mysqld.cnf bind-address=127.0. 0.1 comments  # Log in to MySQL, Run command 'root'@ '% ' MySQL ' With grant Option;flush privileges; # Restart MySQL

　　　　

2. Add a table

3. Logical deletion

• For important data, do not want physical deletion, once deleted, the data can not be retrieved
• Typically for important data, a isdelete column is set, type bit, which represents the tombstone
• Non-critical data that is larger than a large amount of growth can be physically deleted
• The importance of data should be based on actual development decisions

　　　　

4. Command operation

[email protected]:~$ mysql--help   # view command [email protected]:~$ mysql-uroot-pmysql    #连接

# View version mysql> select version ();         +-------------------------+| Version () in                Set (0.00 sec) View versions: select version (); Show current time: Select Now ();

5. Remote Connection

• Generally in the company development, the database may be unified on a single server, all developers share a database, rather than the configuration of a database on their own computer
• Run command

Mysql-hip Address-uroot-p

• -H Write the host IP address to connect to
• -U back write user name of the connection
• -P Write password after enter

6. Database Operations

# CREATE DATABASE database name charset=UTF8; # Deleting a database drop database name; # Switch Database Use database name; # View the currently selected databases Select Database ();

7. Table Operations

• Create a table

Auto_increment indicates that the CREATE TABLE table name (column and type) is automatically grown;

Mysql> CREATE TABLE School (    not  null,    not  null,     Gender bit default 1,    birthday datetime);

• Modify Table

ALTER TABLE name add|change| Drop Column name type: mysql> ALTER TABLE school add iddelete bit default 0;

table name ;

Mysql> Show create table school; -----+| School | CREATE TABLE ' School ' (  ' id ' int (one) not null auto_increment,  ' name ' varchar () is notnull,  ' gender ' bit (1) Default b'1',  ' birthday ' datetime DEFAULT NULL,  ' iddelete ' bit (1) Default b'0',  PRIMARY KEY (' id ')) ENGINE=innodb default charset= UTF8 |+--------+-----------------------------

8. Data manipulation

• Inquire

 from table name

• Increase

1. Full column insert: INSERT into table name values (...) MySQL> INSERT into students values (0," Tengxu ", 1,"1999-9-9"  , 0); +----+--------+--------+---------------------+----------+| ID | Name   | gender | Birthday            | isdelete |+----+--------+--------+---------------------+----------+|  1 | Tengxu   | |       1999-09-09 00:00:00 |          |

2. Default insert: INSERT into table name (column 1,...) VALUES (value 1,...) MySQL> INSERT into students (name) VALUES (' netease ');

Mysql> INSERT into students (Gender,name) VALUES (1,' millet ');

3.mysql Unique inserts multiple data at the same time: INSERT into table name values (...), (...) ...; or INSERT into table name (column 1,...) VALUES (value 1,...), (value 1,...) ...;

Mysql> INSERT into students (name) VALUES (' Baidu '), (' cool dog '), ( ' QQ ' ); +----+--------+--------+---------------------+----------+| ID | Name   | gender | Birthday            | isdelete | |  5 | Baidu   |       | NULL                |          | |  6 | Cool Dog   |       | NULL                |          | |  7 | QQ     |       | NULL                |          | +----+--------+--------+---------------------+----------+

• Modify

Update table name set column 1= value 1,... Where condition

' 1990-2-2 ' where id=2; MySQL> Update students set gender=0,birthday='2017-2-13' where id=6;

• Remove physical

 from students where id=5;

• Tombstone is essentially a modification operation update

ALTER TABLE Students add Isdelete bit default 0, update students isdelete =1 where if need to delete...;

Mysql> Update students set isdelete=1 where id=6; MySQL from students where isdelete=0;

9. Backup and Recovery data backup

# go to Super admin sudo-s# into MySQL library directory cd/var/lib/mysql#  Run the mysqldump command  > ~/desktop/ backup file. sql; Enter the MySQL password as prompted

[Email protected]:/var/lib/mysql#  mysqldump-uroot-p python3 > ~/desktop/bak.sql[email] Protected]:~/desktop$ vim bak.sql A series of SQL statements

Data recovery

-uroot–p database name < ~/desktop/ backup file. SQL enter MySQL password as prompted

Mysql> CREATE DATABASE py31 charset=utf8;[ Email protected]:

2 Database Installation Application