MySQL database, which has to be viewed (1)

MySQL databases are generally used in small and medium-sized enterprises. They are not only open-source, but also have good management features. Today we will introduce the related content of MySQL databases, starting from: net start mySql; The following is the main content of the article.

Start: net start mySql;

Enter: MySQL-u root-p/MySQL-h localhost-u root-p databaseName;

List databases: show databases;

Select Database: use databaseName;

List tables: show tables;

Show columns from tableName;

Create a database: source fileName.txt;

Matching character: wildcard _ can be used to represent any character, and % represents any string;

Add a field:

 
 

  
  
alter table tabelName add column fieldName dateType; 
 
 

Add multiple fields:

 
 

  
  
alter table tabelName add column fieldName1 dateType,add columns fieldName2 dateType; 
 
 

Multi-line command input: note that words cannot be broken; when data is inserted or changed, the field strings cannot be expanded into multiple rows; otherwise, the hard press enter will be stored in the data;

Add an Administrator Account:

 
 

  
  
grant all on *.* to user@localhost identified by "password"; 
 
 

After each statement is entered, enter the plus sign ';' At the end, or add '\ G;

Query time: select now ();

Query the current user: select user ();

Query the database version: select version ();

Query the currently used database: select database ();

1. Delete the students data table in the student_course database:

 
 

  
  
rm -f student_course/students.*  
 
 

2. Back up the database: (back up the database test)

 
 

  
  
mysqldump -u root -p test>c:\test.txt  
 
 

Backup table: (back up the mytable table under the test database)

 
 

  
  
mysqldump -u root -p test mytable>c:\test.txt  
 
 

Import the backup data to the database: (import back to the test database)

 
 

  
  
mysql -u root -p test  
 
 

3. Create a temporary table: (create a temporary table zengchao)

 
 

  
  
create temporary table zengchao(name varchar(10));  
 
 

4. To create a table, first determine whether the table exists.

 
 

  
  
create table if not exists students(……);  
 
 

5. Copy the table structure from an existing table

 
 

  
  
create table table2 select * from table1 where 1<>1;  
 
 

6. Copy a table

 
 

  
  
create table table2 select * from table1;  
 
 

7. Rename the table

 
 

  
  
alter table table1 rename as table2;  
 
 

8. Modify the column type

 
 

  
  
alter table table1 modify id int unsigned; 
 
 

Modify the column id type to int unsigned.

 
 

  
  
alter table table1 change id sid int unsigned; 
 
 

Modify the column id name to sid and the attribute to int unsigned.

9. Create an index

 
 

  
  
alter table table1 add index ind_id (id);   
  
  
create index ind_id on table1 (id);   
  
  
create unique index ind_id on table1 (id);  
 
 

Create a unique index

10. delete an index

 
 

  
  
drop index idx_id on table1;   
  
  
alter table table1 drop index ind_id;  
 
 

11. Combine characters or multiple columns (link the column id with ":" And column name and "=)

 
 

  
  
select concat(id,':',name,'=') from students;  
 
 

12. limit (10 to 20 records selected) <the number of the first record set is 0>

 
 

  
  
select * from students order by id limit 9,10;  
 
 

13. functions not supported by MySQL

Transaction, view, foreign key and reference integrity, stored procedure and trigger

14. MySQL uses the index operator symbol

<, <=, >=, >,=, Between, in, like without the start of % or _

15. disadvantages of using Indexes

1) slowing down the speed of adding, deleting, and modifying data;

2) occupied disk space;

3) increase the burden on the query optimizer;

When the query optimizer generates an execution plan, it will consider indexes. Too many indexes will increase the workload for the query optimizer, resulting in the inability to select the optimal query solution;

16. Analyze index Efficiency