* Open the database in the terminal: Mode 1. Open the database in the administrator run CMD terminal: net start mysql57 (service name) ==> Connection database: Mysql-u username-P ==> Enter password
Mode 2. Open the database in mysql-client: Enter the password directly and you can enter it.
* Sign Out: Exit
* Remote Connection database: MYSQL-H IP address---User name-----==> Enter the other password
* Database type: relational database: MySQL, Oracle, etc. Non-relational database (NoSQL): MongoDB, Redis, etc.
* Database operation: show databases; View all databases # View database, detabase to add complex s
Create DATABASE demo Charset=utf8; Create a database Demo # database command with a semicolon at the end;
Drop database demo; Delete Database Demo
Use demo switch Database # to manipulate the database you have to select a database first
Select database; View the database currently in use
* Table operation: show tables; View all Tables # View table also add s
CREATE TABLE Demo (column 1, column 2,...); Create a Table Demo example: CREATE TABLE demo (id int auto_increment primary_key, name varchar (20))
drop table demo; Delete Table Demo
DESC demo; View table Structure
Show create table demo; View the statements used to create the table demo
Rename table demo to Newdemo; Modify the table name of the table demo to Newdemo
* Data manipulation: add: INSERT into table name values (...); Full column Insertion
Insert into table name VALUES (...),...; Insert more than one data in all columns
Insert into table name (column 1, column 2,...) VALUES (value 1, value 2,...) are inserted by default
Delete: Delete from table name where condition; Delete in-table data (warning: No where condition will delete all data in the table) # example: Delete from student where id=4;
Change: Update table name set column 1= value 1, column 2= value 2,... where condition; Modify in-table data (warning: All columns will be modified after no conditions) # example: Update student set name=tom,age=18 where id=7;
Check: SELECT * from table name; View all the data in a table (here * represents all columns, if you want to query only some of the columns can be separated by commas, example select name,age from table name;)
Conditional query: A. Syntax: SELECT * FROM table name where condition;
B. Comparison operator (=, <, >=, <=,! =): Example: SELECT * FROM student where id>=3;
C. Logical operators (and, or, not): Example: SELECT * from student where id>=3 and gender=0; Query id>=3 and gender-female students
D. Fuzzy query: Like:% means any number of any character, _ represents an arbitrary character
Example: SELECT * from student where name is like "Week%"; = To get results such as Jay Chou, Stephen Chow Chi ...
SELECT * from student where name is like "Week _"; And get results like Zhou Xun ...
E. Scope query: Example: SELECT * from student where ID in (2,7,12); # query for students with ID 2,7,12
SELECT * from student where ID between 5 and 10; # Query ID between 5-10 students
F. Empty query: Example: SELECT * FROM student where address is null; # Check for students with an empty address (not NULL for empty)
G. Precedence: parentheses > Not > Comparison operators > Logical operators; and > or + are available () to change the priority level
H. Sort: SELECT * from table name order BY column 1 asc/desc, column 2 Asc/desc, ...; # ASC Ascending desc Descending
MySQL database additions and deletions change