MySQL installation and basic use

Basic use of MySQL database
Service side
1. Installation: sudo apt-get install Mysql-service
2. Start: sudo service MySQL start
3. See if there is a MySQL service in the process: PS ajx|grep MySQL (/usr/sbin/mysqld)
4. Stop service: sudo service MySQL stop
5. Restart Service: sudo service mysql restart
6. View configuration file: Profile directory is/etc/mysql/mysql.conf.d/mysqld.cnf

Client
1. Installation: sudo apt-get install mysql-client
2. Connection server: Mysql-u root (username)-p MySQL (password)
3. Exit: Ctrl+d or Exit or quit
4. Modify input prompt: Prompt prompt (\d full date, \d database name, \u user, \h Host)
5. Clear screen: ctrl+shift+l

Database operations
1. View all databases: show databases;
2. Creating database: Create Database name charset = Utf-8;
3. Using the database: Use database name;
4. View the database currently in use: select databases ();
5. Delete the database: drop database name;

Data Table Operations
1. View all data sheets: show tables;
2. Create a table:
CREATE TABLE table_name (
column1 datatype constraint,
Column2 datatype,
... ..
columen datatype,
PRIMARY KEY (one or more columns)
);
Example:
CREATE TABLE students (
ID int unsigned primary key auto_increment not NULL,
name varchar () is not NULL,
Age tinyint unsigned default 0,
height Decimal (5,2),
gender enum ("Male", "female", "confidential") default "Confidential "
);
3. View the creation statement for the table: Show create table table name;
4. View table structure: DESC table name;
5. Modify the table structure
5.1. Add field: ALTER TABLE name add field name type;
ALTER TABLE students add birthday datetime;
5.2. Modify the field type: ALTER TABLE indicates the new type and constraint of the Modify field name;
ALTER TABLE students modify birthday date not null;
5.3. Field renaming: ALTER TABLE name change old name new name type and constraint;
ALTER TABLE students change birthday birth date not null;
6. Delete table: droptable table name;

Data Manipulation
1. Basic Enquiry
1.1. Query all data in the table: SELECT * from table name;
SELECT * from students;
1.2. Query the data for the specified column: Select Column1, Column2,... from table name;
select name, height from students;
2. Add Data
2.1. Full column insert: INSERT into table name values (...) [,(...) ...];
INSERT into students values (0, "Goku", 40,178.50, "Male", "1980-10-01");
INSERT into students values (0, "Wu Rice", 20,180.00, "Male", "2000-10-2"), (0, "Default,190,default ", "1950-1-1");
2.2. Partial insert: INSERT into table name (column 1, column 2, ...) VALUES (value 1, value 2, ...) [, (value 1, value 2, ...)];
INSERT into students (Name,age,birth) VALUES ("Beta Tower", "1970-1-1");
INSERT into students (Name,birth) VALUES ("Trunks-kun", "2008-12-1"), ("Wu Tian", "2009-12-2");
3. Modify the Data
Update table name set column 1= value 1, column 2= value 2...where condition;
Update students set age=12 where name= "trunks-kun";
4. Delete Data
4.1. Physical deletion
Delete from table name where condition;
Delete from students where name= "Wu Tian";
4.2. Tombstone (Set Isdelete field, type bit, default value is 0, modify isdelete=1 when data is deleted)
(ALTER TABLE students add Isdelete bit default 0 NOT null;)
Update students set isdelete=1 where condition;

Database backup
1. Backup: Run the mysqldump command
Mysqldump-u root-p Database name > Python.sql (saved to *.sql file)
# Follow the prompts to enter your password
2. Recovery
2.1. Connect to the MySQL server, create a new database, log out
2.2.mysql-u root-p New database name < Python.sql
# Follow the prompts to enter your password

Data Integrity
a database is a complete business unit that can contain more than one table and the data is stored in a table. In order to store data more accurately in the table, the data is guaranteed to be complete and effective,
You can add some mandatory validation to the table when you create the table, including the types and constraints of the data fields.
1. Data type
the principle of using data types is: Suffice it, try to use a small range of values, instead of large, so that you can save more storage space.
common data types are as follows:
integer: Int,bit
decimal: Decimal
string: Varchar,char
DateTime : Date, time, DateTime
enum type (enum)
the types of special instructions are as follows:
decimal indicates floating-point numbers, such as decimal (5,2), which represent 5-digit coexistence, with decimals being 2 bits
Char represents a fixed-length string, such as char (3), and a space is ' ab ' if it fills ' ab '
varchar represents a variable-length string, such as varchar (3), which stores ' ab ' when populated with ' Ab '
The string text indicates that large text is stored and is recommended when the character is greater than 4000
for pictures, audio, video and other files, not stored in the database, but upload to a server, and then in the table to store the file's save path
2. Constraints
PRIMARY KEY PRIMARY key: The order of physical storage
NonEmpty NOT null: This field does not allow null values to be filled
Unique : The value of this field does not allow duplicates
Default: Default value is used when this value is not filled, and if filled in as
foreign key Foreign key: the relationship field is constrained, when the value is filled in for the relationship field, it is queried for the existence of the value in the associated table, if it exists, the fill fails and throws an exception if it does not exist .
Note: Although foreign KEY constraints can guarantee the validity of data, in the CRUD (add, modify, delete, query) of the data,
will reduce the performance of the database, so it is not recommended to use, then the validity of the data how to guarantee it? A: Can be controlled at the logical level

MySQL installation and basic use