©2020-2024 ioDraw All rights reserved
<>mysql Basic syntax of database
<>DDL operation
* Create database
grammar ：create database Database name ;
* View all databases
grammar ：show databases;
* switch （ use ） database
grammar ：use + Database name ;
* Create a table
grammar ：create table Table name （
Field name 1 Field type ,
Field name 2 Field type ,
Field name 3 Field type ,
…… ……
）;
* View all tables in the database
grammar ： Show tables;
* View the structure of the table
grammar ：desc Table name ;
* Add field
grammar ：alter table Table name add Field name Field type
* Delete field
grammar ：alter table Table name drop Field name
* Modify table name
grammar ：rename table Old table name to New table name
* Modify field type
grammar ：alter table Table name modify Field name Field of new type
* Field rename
grammar ：alter table Table name change Old field name New field name Field type
<>DML operation
* insert data
grammar ：insert into Table name （ Field name ） value（ content ）
* Delete data
grammar ：Delete from Table name where condition
* Modify data
grammar ：update Table name set field = Revised content where （ Restrictions ）
* View data
grammar ：select Field name from Table name where condition
* Delete table
grammar ：drop table Table name ;
truncate table Table name ;
* Delete database
drop database Library name ;
notes ：
（1）Delete Only deleting the records inserted in the table does not delete them
（2）Truncate Delete data and records , Equivalent to first drop Where is this form create This is a list
<> constraint
* Characteristics of primary key constraints ： Uniqueness , Non emptiness
Set primary key and auto grow
Primary key ： When creating a table , After the field you want to set as the primary key Add primary key （primary key）
Self growth ： When creating a table , After the field to be set to self growing Add on auto_increment
* Nonempty constraint ： Cannot be empty
Add the not null
* Foreign key constraints ：
To create a table based on the primary key of the main table
After field definition Add foreign key
Constraint （ Foreign key name ） foreign key（ Constrained fields ） references Primary table name （ The outside world constrains its fields ）
The main table as a constrained field needs to be the primary key of the table
<>DQL operation
* Basic query
Query all ：
select * from Table name
Query the data of the specified column ：
Select Listing 1, Listing 2…… from Table name Where to write （ What time ） Which column do you want to check
* View tables in other databases in the current database
Show tables in Database name
* View the data of the table below the non current database
Select Listing from bank.user;
* Where query criteria
Relational operators ：> < = != >= <=
section ：between A and B（A and B between ）【A,B】
AND && and also
or || : or
not： wrong Negation
Is null： It's empty
is not null : Not empty
in In what contain
* Fuzzy query
Like image
wildcard
_： Any character
%： Any number of
Technology