MySQL common commands on Linux servers

1. Execute the SQL file:

Source/usr/local/crazy/file/test.sql

Show tables; --Show all tables in the database

Note: You need to put the SQL file in a folder on the server, you can not have a Chinese directory, or do not

Drop database name; --delete database MyDatabase

Exit Exit

2. Connect to the database (except for previous blog methods):

(1) New file under Server: mysql.sh

(2) Contents of the mysql.sh file:

(3) Enter the directory where the mysql.sh file is located: Enter the command

./mysql.sh

3. Create a new database:

Create database name;

show databases; --Show list of all databases

4. Select the database:

Use database name;

5. Create a table

CREATE TABLE ' Table_seq ' (

' Table_type ' varchar (4) Not NULL,
' VALUE ' varchar (+) DEFAULT NULL,
' REMARK ' varchar (+) DEFAULT NULL,
PRIMARY KEY (' Table_type ')
) Engine=innodb DEFAULT Charset=utf8;

Note: MYSQL creates a self-growing field

1. Add when creating table: CREATE TABLE tablename (ID int auto_increment PRIMARY key,...)

2. Add after creating the table: ALTER TABLE tablename add ID int auto_increment PRIMARY key add self-increment to the primary key ID: ALTER TABLE test MODIFY ID INT UNSIGNED auto_increment; 3. Set the PRIMARY key: ALTER TABLE TableName add primary key (Field_name); 4. Rename table: ALTER TABLE table_old_name rename Table_new_name; 5. Change the type of field: ALTER TABLE tableName modify Field_name field_type; 6. Rename field: ALTER TABLE tableName change Old_field_name new_field_name new_field_type; 7. Delete field: ALTER TABLE tableName drop column field_name; 8. Add a new field: ALTER TABLE tableName add New_field_name field_type; ALTER TABLE TableName add New_field_name field_type not null default ' 0 '; 9, add a field, the default value is 0, non-empty, autogrow, PRIMARY key: Alter TABLE Tabelname add New_field_name field_type default 0 NOT NULL Auto_increment , add primary key (New_field_name);

Create a table CREATE TABLE pre_common_usernotes (ID int (8) NOT null primary key auto_increment, user_id char (a) not NULL, order_id char () not NULL, pub_id char (a) is not NULL, Ad_name char (8), ad_id int., device Char, System_name Char, Chan Nel int (8), Price double (16,2), point Int (8), TS Int (TEN) NOT null default ' 0 ', sign char (+));   Create a database and set the database Default field encoding format CREATE DATABASE database_name default charset UTF8 collate utf8_unicode_ci;   Set auto_increment Field the minimum valueAleter TABLE table_name auto_increment=100000 or ALTER TABLE album Auto_increment=1; Note: Auto_increment can only be used with the type int and primary key. 6. Operation on the primary key :

Add Federated Primary key : ALTER TABLE pub_device_property ADD CONSTRAINT pk_property PRIMARY key (IMEI,ICCID);

Delete primary key: ALTER TABLE pub_device_property DROP PRIMARY key;

Add primary key (including Federated primary Key): ALTER TABLE pub_device_property ADD PRIMARY key (IMEI,ICCID)

Add constraint (not recommended): ALTER TABLE pub_device_property ADD UNIQUE (IMEI);

You can view the various constraints established in the table:Show create table Pub_device_property;

ALTER TABLE pub_device_property DROP KEY constraint name;

Remove UNIQUE Constraint

#alter table 表名 drop key 约束名;alter table conferenceinfo drop key id;

Example:

Show CREATE TABLE Conferenceinfo;
Results after execution:
| Conferenceinfo | CREATE TABLEconferenceinfo(
idbigint (unsigned) not NULL auto_increment,
cnnamevarchar (+) DEFAULT NULL,
ennamevarchar (+) DEFAULT NULL,
tagvarchar (+) DEFAULT NULL,
locationvarchar (+) DEFAULT NULL,
sponsorvarchar (+) DEFAULT NULL,
startdateDate DEFAULT NULL,
enddateDate DEFAULT NULL,
deadlineDate DEFAULT NULL,
acceptanceDate DEFAULT NULL,
websitevarchar (+) is not NULL,
PRIMARY KEY (id),
UNIQUE KEYid(id,website),
KEYconference_tag_index(id,tag) USING BTREE,
KEYconference_startdate_index(startdate) USING BTREE
) engine=innodb auto_increment=3 DEFAULT Charset=utf8 |

7. Operations on the fields in the table:

Add a field , default is empty
ALTER TABLE USER ADD COLUMN new1 VARCHAR () DEFAULT NULL;
Add a field that cannot be empty by default
ALTER TABLE USER ADD COLUMN new2 VARCHAR (a) not NULL

Add a field

ALTER TABLE jw_user_role ADD zk_env VARCHAR (+);

The modifier field is not NULL, and the original type is also written out

ALTER TABLE jw_user_role MODIFY zk_env VARCHAR (+) not NULL;

Delete a field
ALTER TABLE user DROP COLUMN new2

Modify the type of a field
ALTER TABLE user MODIFY New1 VARCHAR (10);
To modify the name of a field, be sure to re-specify the field's type
ALTER TABLE user change new1 new4 int;

Change Column Name

Alter table student change Physics Physisc char (Ten) not null;

You can add comments to a table
ALTER TABLE ' table_name ' COMMENT ' notes ';
Adding comments to a field also applies to modifying
ALTER TABLE ' table_name ' change ' column_name ' column_name ' type (longth) UNSIGNED null DEFAULT null COMMENT ' comment '

To adjust the order of fields:

ALTER TABLE table name

Change field name new field FirstName type default after field name (after which field is skipped)

Example:

ALTER TABLE appstore_souapp_app_androidmarket;
Change getpricecurrency getpricecurrency varchar (+) default NULL after GetPrice

MySQL common commands on Linux servers