MySQL Database basic command statement

MySQL mariadb

Client connections
Mysql-uroot-p;

Client exit
Exit or \q

Show all databases
show databases;
Show schemas;

Create a database
Create DATABASE DB1
CharSet UTF8;

Deleting a database
Drop database db1;
Drop database if exists db1;

View Table
Show tables;
Desc TB1;-------View the table structure of TB1
Show CREATE TABLE tb1\g; --View the Build table statement

Table additions and deletions to change

Create
CREATE TABLE TB1 (

ID int primary KEY auto_increment,
Name varchar () is not NULL,
num int not NULL unique,
XID int,
Foreign KEY (XID) references TB2 (ID),
) Engine=innodb Charset=utf8;

Modify
ALTER TABLE TB1 add gender char (1) after name;

ALTER TABLE TB1 modify num int null;

ALTER TABLE TB1 modify ID int;

ALTER TABLE TB1 modify ID int auto_increment;

ALTER TABLE TB1 drop PRIMARY key;

ALTER TABLE TB1 drop FOREIGN KEY (foreign key constraint name);

ALTER TABLE TB1 DROP INDEX constraint name;
ALTER TABLE TB1 modify ID int;

Delete a table
drop table if exists tb1;

Constraints

Primary health non-null only external health check

Default value

num int default 1;

A sql_mod variable for MySQL

Linux installed Mysql,sql_mod variable by default is a null value, indicating that running in ' not strict ' mode, non-empty fields will be inserted,
The length of the character segment will be truncated

。。。。。。。。。。。。。。。。。。

You can modify this variable to make MySQL run in strict mode

--View the value of the Sql_mode variable
Show variables like ' Sql_mode ';

--Modification
Set global sql_mode= ' Strict_trans_tables ';

------Exit, re-enter MySQL, and view the variables
Show variables like ' Sql_mode ';

* * Structured Query Language
Structured Query Language

* SQL standard syntax
* Each database vendor has its own extended syntax
*) MySQL Extension
*) Oracle Plsql
*) SQL Server T-SQL

*sql classification
*) DDL-----Definition language, database build table modification table
*) DML-----Data Manipulation language, additions and deletions
*) DQL-----Data Query Language, select
Inserting data Insert

*insert into TB1 values (5, ' abc ');
All fields Insert values by field order

* * INSERT INTO TB1 (gender, name) VALUES (' M ', ' Zhang San ');
Inserts a value into the specified field

* * INSERT INTO TB1 (gender, name)
VALUES (' M ', ' Zhang San '),
(' F ', ' John Doe '),
(' M ', ' Harry ');
Inserting multiple data (non-standard SQL) into a table at one time

* INSERT INTO TB1 select * from TB2
Insert into TB1 (name, gender) select name, Gender from TB2
Inserting data from a TB2 table to a TB1

Modify Data Update
===========================================================
*) Update tb1 set name= ' abc ', Age=23,gender=null
where id=43;

Deleting data Delete
===========================================================
*) Delete from tb1 where .....

Querying data
=========================================================
* WHERE clause
= equals
<> Not equal to
> Greater than
< less than
>= greater than or equal to
<= less than or equal to

Between small value and large value range
In specifies several fixed values

Like fuzzy queries usually look only at strings
%-matches 0 to any number of characters
_-matches a single arbitrary character

Escape ' \ ': Specify the transfer operator

\_ Normal Underline
\% Normal% character

Is null

Not
------------------------------
Not between and
Not in
is NOT NULL

and
Or

MySQL Database basic command statement