SQL drip 41-mysql Common SQL syntax

Source: SQL drip 41-mysql Common SQL syntax

ALTER table: Add, modify, delete table columns, constraints, and other table definitions.

• View column: Desc table name;
• Modify table name: ALTER TABLE T_book Rename to BBB;
• Add column: ALTER TABLE name add column name varchar (30);
• Add Annotated column:alter table directory add index_url varchar ($) default null comment ' chapter bibliography link ' After Dir_url;
• Delete column: ALTER TABLE table name drop column name;
• Modify Column name Mysql:alter table BBB change nnnnn hh int;
• Modify the column name Sqlserver:exec sp_rename ' t_student.name ', ' nn ', ' column ';
• Modify the Column name Oracle:lter table BBB Rename column nnnnn to HH int;
• Modify Column properties: ALTER TABLE T_book modify name varchar (22);

Sp_rename:sqlserver built-in stored procedures, with the definition of modified tables.

MySQL view constraints, add constraints, remove constraints add columns, modify columns, delete columns

• View field information for table: DESC table name;
• View all information about the table: Show create table table name;
• Add PRIMARY KEY constraint: ALTER TABLE name add constraint primary key (Shape: Pk_ table name) primary key table name (primary key field);
• Add FOREIGN KEY constraint: ALTER TABLE from TABLE ADD constraint foreign key (shape: Fk_ from Table _ Main Table) foreign key from table (foreign key field) references Main Table (primary key field);
• Delete PRIMARY KEY constraint: ALTER TABLE name drop PRIMARY key;
• Delete FOREIGN KEY constraint: ALTER TABLE name drop FOREIGN key foreign key (case sensitive);

• Modify table name: ALTER TABLE T_book Rename to BBB;
• Add column: ALTER TABLE name add column name varchar (30);
• Delete column: ALTER TABLE table name drop column name;
• Modify Column name Mysql:alter table BBB change nnnnn hh int;
• Modify the column name Sqlserver:exec sp_rename ' t_student.name ', ' nn ', ' column ';
• Modify the Column name Oracle:alter table BBB Rename column nnnnn to HH int;
• Modify Column properties: ALTER TABLE T_book modify name varchar (22);

• MySQL Modify field default value

  Solve

  ALTER TABLE topic ALTER COLUMN Cateid set default ' 2 ';

  Grammar

  ALTER TABLE name ALTER COLUMN field name drop default; (if default value exists, delete it first)

  ALTER TABLE name ALTER COLUMN field name set default value (can be set directly if it does not exist)

• Add a column with a default value with field Description: ALTER TABLE jz_order Add column Delay_delivery tinyint (3) DEFAULT 0 COMMENT ' deferred receipt, 0: No extension, 1: First extension 2: Second Deferred ' after C_userid;

Sp_rename:sqlserver built-in stored procedures, with the definition of modified tables.

The difference between insert IGNORE and insert into is that insert IGNORE ignores data that already exists in the database, inserts new data if the database has no data, and skips the data if there is data. This preserves the data that already exists in the database for the purpose of inserting data into the gap.
eg
Insert ignore into table (name) select name from Table2

MySQL related database, table, table structure and other display commands

1. Display the list of databases.
show databases;
2. Display the data table in the library:
Use MySQL;
Show tables;
3, display the structure of the data table:
describe table name;
4, build the library:
Create database name;
5, build the table:
Use library name;
CREATE TABLE table name (field settings list);
6. Deleting the library and deleting the table:
drop database name;
drop table name;
7. Empty the records in the table:
Delete from table name;

SQL drip 41-mysql Common SQL syntax