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