command

Start close remote link exit skip start permission modify password

\#Start database
net start mysql


\#close database
net stop mysql


\#Remote linked database
mysql -h Host address -u user name -p User password
mysql -u root -stop
/* Connecting and disconnecting servers */
mysql -h address -P port -u user name -p password
\#Exit database
exit;      quit;          \q;


/* Skip permission verification and log in to MySQL */
mysqld --skip-grant-tables
-- modify root password
 Cryptographic function password()
update mysql.user set password=password('root');

Show Library - create Library - delete Library

\#show database

show databases;

\#Create database
create database Library name;

\#Delete database

drop database if exists Library name;
drop database learnsql；

Addition, deletion, modification and query of database  

\#Displays the tables in the database

show databases；


\#Create data table


create table user(
   id int auto_increment primary key,
   name varchar(50),
   sex varchar(20),
   date varchar(50),
   dream varchar(50)
)default charset=utf8;


\#Delete table


drop table user;
drop table if exists user;


\#View table structure


desc user;


\#Modify table


create table users(
   id int auto_increment primary key,
   name varchar(50) not null,
   sex varchar(20) not null,
   date varchar(50) not null,
   dream varchar(50) not null,
   unique (name)#Set unique value
)default charset=utf8;


\#Modify table name


alter table user rename to users;


\#Add fields to the table


alter table users add future varchar(60);


\#Delete a field in the table


alter table users drop column tomorrow;


\#Modify a field in a table


alter table users change future tomorrow varchar(100);


\#Add column test to table position


alter table position add(test char(10));

\#Modify column test in table position

alter table position modify test char(20) not null;

\#Modify the default value of column test in table position

alter table position alter test set default 'system';


\#Remove the default value of position test from the table

alter table position alter test drop default;


\#Remove column test from table position


alter table position drop column test;


\#Table Department_ POS delete primary key


alter table depart_pos drop primary key;


\#Table Department_ POS add primary key


alter table depart_pos add primary key PK_depart_pos
(department_id,position_id);


\#Load data into database tables in text mode (e.g. D:/mysql.txt)

load data local infile "D:/mysql.txt" into table MYTABLE;


\#Import. sql file command (for example, D:/mysql.sql)


source d:/mysql.sql;  #Or /. d:/mysql.sql;
```

Want to see the text version   , He's running towards you

 

 

 

\1. Addition, deletion and modification of table

\1. Create table

create table table name(

Column name data type [constraint type] [comment 'remarks'],

Constraint constraint name constraint type (column name)) engine = InnoDB default charset = utf8;

Query several columns of data from other tables to generate a new table

create table name 1 as select column 1, column 2 from table name 2

\2. Add data to the table

Add a row of data by column name

insert into table name [(column name 1, column name 2...)] values (column 1 data, column 2 data...);

Copy data from other tables

insert into table name 1 select column name from table name 2

\3. Modify the data in the table

Modify data conditionally

update table name set column name = column value, column 2 name = column 2 value... where to select criteria

Assign the subquery result to the data in the table

update table name set column name = (subquery)

\4. Delete data in the table

Delete specified data by conditions

delete from table name where selection criteria

Destroy the entire table or constraint

drop table name;

drop index constraint name;

\5. Modify the structure of the table

Add column

alter table name add column name data type;

Add constraint

alter table name add [constraint constraint name] constraint type (column name);

Constraint name add syntax undo syntax

Foreign key constraint alter table table name add [constraint constraint name] foreign key (foreign key column) references primary key table name (primary key column); Alter table name drop foreign key constraint name

Default constraint alter table name alter column name set default 'default value' alter table name alter column name drop default

Check constraint alter table name add [CONSTRAINT constraint constraint name] check (column name 10) alter table name drop check constraint name

Unique constraint alter table table name add [CONSTRAINT constraint constraint name] unique (column name) alter table name drop index constraint name

Primary key constraint alter table table name add [CONSTRAINT constraint name] primary key (column name) alter table name drop primary key

\3. Modify table name

alter table name rename new table name

\4. Modify the field name of the column

alter table name change column name new column name new column data type

\5. Modify the data type of the column

alter table name alter column column name data type;

\6. Add a column to the table

alter table name add column name data type;

\7. Delete a column in the table

alter table name drop column name

\7. View the structure of the table

desc table name

\2. Table query

\1. Basic syntax of query

select column name 1 [as] [column alias], column Name2

from table 1 [as] [table alias]

[left] join table 2 on connection conditions

[left] join table 3 on connection conditions

where search criteria (statistics function not available)

group by group column 1, column 2

having search conditions (available statistical functions min,max,sum,avg)

order by [desc descending]

limit starting line number, showing the number of lines