Mysql Study Notes
Mysql Study Notes
1. installation environment:
Windows 7
Mysql 5.5 download from http://www.mysql.com
EMS Mysql Query 1.6.0.1
Local IP Address: 172.5.1.183
2. Install Mysql by default.
The directory is in c: \ mysql. do not modify the default directory.
3. Start Mysql
Install as a service: c: \ mysql \ bin \ mysqld -- install
Start Mysql: net start mysql
Stop Mysql: net stop mysql
4. The databases created by the system include mysql and test.
Mysql saves System Data
Test Database for testing
5. Default logon Mode
On the local machine
C: \ mysql \ bin \ mysql-uroot
C: \ mysql \ bin \ mysql
Remote
Mysql-h 172.5.1.183-uroot
These are in the mysql. user table. The system has four data entries by default.
Use mysql
Select host, user, password from user;
+ ----------- + ------ + ---------- +
| Host | user | password |
+ ----------- + ------ + ---------- +
| Localhost | root |
| % | Root |
| Localhost |
| % |
+ ----------- + ------ + ---------- +
The meaning of these data:
If the username is root and the password is empty, users can log in from the local host and any remote host.
Users with blank usernames and passwords can log in from the local machine.
If the user name is blank and the password is empty, remote login is not allowed (the field following the user is N, so login is not allowed)
Change root Password
Mysql-uroot
Use mysql
Update user set password = PASSWORD ("root") where user = 'root' and host = 'localhost'
Next time, you need to use mysql-uroot-proot to log on.
You can use mysql-h 172.5.1.183-uroot to log on remotely or locally. This is determined based on the policy of the second line.
The permission modification takes effect:
1)net stop mysql net start mysql 2)c:\mysql\bin\mysqladmin flush-privileges
3) use the flush privileges statement after logging on to mysql.
6. Create a database staffer
Create database staffer;
7. The following statement is executed in the mysql Environment
Show databases;
Switch to the staffer database use staffer;
Show tables with permissions in the current database;
The desc staffer structure of the staffer table is displayed;
8. Create a test environment
1) Create a database staffer
Mysql> create database staffer
2) create tables staffer, department, position, depart_pos
Create table s_position (id int not null auto_increment, name varchar (20) not null default 'manager', # Set the default value description varchar (100), primary key PK_positon (id) # Set the primary key); create table department (id int not null auto_increment, name varchar (20) not null default 'System amount', # Set the default value description varchar (100 ), primary key PK_department (id) # Set the primary key); create table depart_pos (department_id int not null, position_id int not null, primary key PK_depart_pos (department_id, position_id) # Set the primary and Secondary keys ); create table staffer (id int not null auto_increment primary key, # Set primary key name varchar (20) not null default 'Anonymous ', # Set default Value department_id int not null, position_id int not null, unique (department_id, position_id) # sets a unique value );
3) Delete
Mysql>
Drop table depart_pos;
Drop table department;
Drop table s_position;
Drop table staffer;
Drop database staffer;
9. modify the structure
Mysql>
# Add column test to table position
Alter table position add (test char (10 ));
# Test
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 test from table position
Alter table position alter test drop default;
# Remove column test from table position
Alter table position drop column test;
# Table depart_pos Delete primary keys
Alter table depart_pos drop primary key;
# Add a primary key for table depart_pos
Alter table depart_pos add primary key PK_depart_pos (department_id, position_id );
10. operation data
# Insert table department
Insert into department (name, description) values ('System amount', 'System amount ');
Insert into department (name, description) values ('Public relations ', 'public relations ');
Insert into department (name, description) values ('Customer Service department ', 'Customer Service department ');
Insert into department (name, description) values ('finance department ', 'Finance department ');
Insert into department (name, description) values ('test part', 'test part ');
# Insert table s_position
Insert into s_position (name, description) values ('directory', 'directory ');
Insert into s_position (name, description) values ('manager', 'manager ');
Insert into s_position (name, description) values ('ordinary employees', 'ordinary employees ');
# Insert table depart_pos
Insert into depart_pos (department_id, position_id)
Select a. id department_id, B. id postion_id
From department a, s_position B;
# Insert a table staffer
Insert into staffer (name, department_id, position_id) values ('chen dazhi ', 1, 1 );
Insert into staffer (name, department_id, position_id) values ('Li wenbin', 1, 2 );
Insert into staffer (name, department_id, position_id) values ('majia', 1, 3 );
Insert into staffer (name, department_id, position_id) values ('Kang Zhiqiang ', 5, 1 );
Insert into staffer (name, department_id, position_id) values ('yang Yuru );
11. query and delete operations
# Display System Department personnel and positions
Select a. name, B. name department_name, c. name position_name
From staffer a, department B, s_position c
Where a. department_id = B. id and a. position_id = c. id and B. name = 'System amount ';
# Display the number of people in the System Department
Select count (*) from staffer a, department B
Where a. department_id = B. id and B. name = 'System amount'
# Display the number of people in each department
Select count (*) cou, B. name
From staffer a, department B
Where a. department_id = B. id
Group by B. name;
# Delete the customer service department
Delete from department where name = 'Customer Service department ';
# Change finance department to finance department
Update department set name = 'finance department 'where name = 'finance department ';
12. backup and recovery
Backup database staffer
C: \ mysql \ bin \ mysqldump-uroot-proot staffer> e: \ staffer. SQL
The obtained staffer. SQL is an SQL script that does not contain database creation statements, so you need to manually
Database creation before importing
To restore the database staffer, you must create an empty database staffer.
C: \ mysql \ bin \ mysql-uroot-proot staffer <staffer. SQL
If you do not want to create staffer manually later, you can
C: \ mysql \ bin \ mysqldump-uroot-proot -- databases staffer> e: \ staffer. SQL
Mysql-uroot-proot> e: \ staffer. SQL
In this case, the staffer library cannot exist in the system and cannot be imported to databases with other names,
Of course, you can manually modify the staffer. SQL file.
13. import data from text to database
1) use the tool c: \ mysql \ bin \ mysqlimport
This tool is used to import files to and remove tables with the same file extension name, as shown in
Staffer.txt and staffer are all imported to the staffer table.
Common options and functions are as follows:
-D or -- delete all information in the data table before the new data is imported into the data table.
-F or -- force: No matter whether an error occurs or not, mysqlimport will force data insertion to continue.
-I or -- ignore mysqlimport skip or ignore those with the same unique
The data in the imported file is ignored.
-L or-lock-tables: The table is locked before data is inserted. This prevents,
When you update a database, user queries and updates are affected.
-R or-replace is opposite to-I.
The table contains records with the same unique keywords.
-- Fields-enclosed-by = char
Specify What records are included in a text file. In many cases
The data is enclosed in double quotation marks. By default, data is not enclosed by characters.
-- Fields-terminated-by = char
Specifies the delimiter between values of each data. In a file separated by periods,
The Delimiter is a period. You can use this option to specify the delimiter between data.
The default Delimiter is a Tab)
-- Lines-terminated-by = str
This option specifies the delimiter between rows and rows in a text file.
Or character. By default, mysqlimport uses newline as the line separator.
You can use a string to replace a single character:
A new line or a carriage return.
Common options of the mysqlimport command include-v display version ),
-P indicates the password.
This tool has a problem and cannot ignore certain columns, which makes it very difficult for us to import data, although
You can set this field manually, but it will produce inexplicable results. Let's make a simple example.
We define the following depart_no.txt, Which is saved on the E disk. The interval is tab \ t.
10 10
11
12 24
Execute the following command:
C: \ mysql \ bin \ mysqlimport-uroot-proot staffer e: \ depart_pos.txt
The column's surrounding symbol is not used here, And the Default \ t is used for segmentation, because other symbols may cause problems,
I don't know if this is the reason for windows.
2) Load Data INFILE file_name into table_name (column1_name, column2_name)
This command is used at the mysql> prompt. The advantage is that you can specify column import. The example is as follows:
C: \ mysql \ bin \ mysql-uroot-proot staffer
Mysql> load data infile "e:/depart_no.txt" into depart_no (department_id, position_id );
These two tools have problems in use in Windows. I don't know if they are caused by Windows or Chinese,
In addition, unspecified columns produce null values, which is obviously not what we want, so use these tools with caution.
