A collection of SQL statements related to the Mysql primary key and a collection of SQL statements related to the mysql primary key

A collection of SQL statements related to the Mysql primary key and a collection of SQL statements related to the mysql primary key

Add Table Fields

Alter table table1 add transactor varchar (10) not Null;

Alter table table1 add id int unsigned not Null auto_increment primary key

Modify the field type of a table and specify it as null or not

Alter table name change field Name field type [whether to allow non-null];

Alter table name modify Field Name field type [whether to allow non-null];

Alter table name modify Field Name field type [whether to allow non-null];

Modify the field name of a table and specify it as null or not

Alter table name change field Original Name field New Name field type [whether to allow non-null

Delete A Field

Alter table mytable DROP field name;

Add unique key

Alter table 'test2' add unique ('userid ')

Modify primary key

Alter table 'test2' drop primary key, add primary key ('id ')

Add Index

Alter table 'test2' add index ('id ')

Alter table 'category 'modify COLUMN 'id' int (11) not null AUTO_INCREMENT FIRST, add primary key ('id ');

The SQL statement block for modifying the primary key is as follows:

22 declare @ defname varchar (100)
Declare @ cmd varchar (500)
Declare @ tablename varchar (100)
Declare @ keyname varchar (100)
Set @ tablename = 'temp1'
Set @ keyname = 'id' -- key to be Set, separated
Select @ defname = name
FROM sysobjects so
JOIN sysconstraints SC
ON so. id = SC. constid
WHERE object_name (so. parent_obj) = @ tablename
And xtype = 'pk'
If @ defname is not null
Begin
Select @ cmd = 'alter table' + @ tablename + 'drop constraint' + @ defname
-- Print @ cmd
Exec (@ cmd)
End
Else
Set @ defname = 'pk _ '+ @ keyname
Select @ cmd = 'alter table' + @ tablename + 'add constraint' + @ defname + 'Primary KEY tertered ('+ @ keyname + ')'
Exec (@ cmd)

How to get the primary key field name and field type -- get the primary key field name

1:
SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

WHERE TABLE_NAME <> 'dtproperties'

2:
EXEC sp_pkeys @ table_name = 'table name'

3:
Select o. name as table name, c. name as field name, k. colid as field serial number, k. keyno as index order, t. name as type

From sysindexes I

Join sysindexkeys k on I. id = k. id and I. indid = k. indid

Join sysobjects o on I. id = o. id

Join syscolumns c on I. id = c. id and k. colid = c. colid

Join policypes t on c. xusertype = t. xusertype

Where o. xtype = 'U' and o. name = 'name of the table to be queried'

And exists (select 1 from sysobjects where xtype = 'pk' and parent_obj = I. id and name = I. name)

Order by o. name, k. colid

How to obtain the primary key or unique field of a table in mysql database

Describe table name;
The field names and attributes are retrieved cyclically to determine which primary Key is Key = 'pri '.

Mysql> describe cdb_videos;
+ -------------- + --------------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ -------------- + --------------------- + ------ + ----- + --------- + ------- +
| Vid | varchar (16) | NO | PRI |
| Uid | mediumint (8) unsigned | NO | 0 |
| Dateline | int (10) unsigned | NO | MUL | 0 |
| Displayorder | tinyint (3) | NO | MUL | 0 |
| Tid | mediumint (8) unsigned | NO | 0 |
| Pid | int (10) unsigned | NO | 0 |
| Vtype | tinyint (1) unsigned | NO | 0 |
| Vview | mediumint (8) unsigned | NO | 0 |
| Vtime | smallint (6) unsigned | NO | 0 |
| Visup | tinyint (1) unsigned | NO | 0 |
| Vthumb | varchar (128) | NO |
| Vtitle | varchar (64) | NO |
| Vclass | varchar (32) | NO |
| Vautoplay | tinyint (1) | NO | 0 |
+ -------------- + ----------------------- + ------ + ----- + --------- + ------- +... The remaining full text>

The primary key of the MYSQL query table.

SELECT
T. TABLE_NAME,
T. CONSTRAINT_TYPE,
C. COLUMN_NAME,
C. ORDINAL_POSITION
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c
WHERE
T. TABLE_NAME = c. TABLE_NAME
AND t. TABLE_SCHEMA = 'test'
AND t. CONSTRAINT_TYPE = 'Primary key ';

The test result is as follows:

Mysql> SELECT
-> T. TABLE_NAME,
-> T. CONSTRAINT_TYPE,
-> C. COLUMN_NAME,
-> C. ORDINAL_POSITION
-> FROM
-> INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,
-> INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c
-> WHERE
-> T. TABLE_NAME = c. TABLE_NAME
-> AND t. TABLE_SCHEMA = 'test'
-> AND t. CONSTRAINT_TYPE = 'Primary key'
-> LIMIT 3;
+ ------------ + ----------------- + ------------- + ------------------ +
| TABLE_NAME | CONSTRAINT_TYPE | COLUMN_NAME | ORDINAL_POSITION |
+ ------------ + ----------------- + ------------- + ------------------ +
| Mr_dept | primary key | dept_id | 1 |
| Order | primary key | id | 1 |
| Tab | primary key | id | 1 |
+ ------------ + ----------------- + ------------- + ------------------ +
3 rows in set (0.06 sec)