Basic syntax of SQL statement

the Chinese code capable of bee displayed is first written

set character_set_client=gbk;set character_set_results=gbk;

Or just lose one sentence.

set names gbk;

create a database

create database name;

Enquiry of existing data bureau

show databases;

delete database

drop database + library name

using the library

use library name;

create tables

create table Student( id int, name varchar(20) not null, age int, sex char(2) not null; major varchar(20));

Take the student table as an example, create a primary key self-increasing table

creat table student( id int primary key auto_increment,//Note: auto_increment can only be used if int is primary key. name varchar(20) not null, banji varchar(20) not null, banji integer default(1), //Set default to 1 );

Add settings or change defaults after creating a table

For example:

alter table student modify score int;alter table student modify score int default '1';

primary key constraint

Add primary key constraints when creating tables

creat table person( id int not null, name varchar(20) not null, adress varchar(50), primary key(id));

Add primary key constraints after creating a table

alter table person add primary key(id);

foreign key constraint

create table Score( p_id int, name varchar(20) not null, age int, sex char(2) not null; major varchar(20), foreign key(p_id) reference persons(id));

Add foreign key constraints after creating a table:

alter table name add foreign key (p_id) references main table name (id)

Creating Primary Foreign Key Relationship Constraints

alter table score add foreign key(p_id) reference person(id);checkbanji int (banji between 1 and 3)

lookup table

show tables;

Adding fields to the table (i.e. adding various desired attributes)(e.g. adding score to the student table here)

alter table student add score double;

i.e. alter table name add field type;

After adding fields, you can query the table structure

desc Student;

describe table name; or desc table name;

modify the table name

alter table rename to new table name;

Modify field name (attribute name)

alter table table name change original field name new field name original data type;alter table student change physics char(10) not null;

//Note: auto_increment can only be used if int type is primary key.

Modify attributes (i.e. modify the data type that qualifies this field)

alter table student modify score int;

alter table name modify field name data type;

remove a column

alter table name drop field name

Delete a record

delete from student where score