MySQL Learning notes

Mysql Learning Notes
1. Installation Environment:
Windows 7
Mysql 5.5 Download from http://www.mysql.com
EMS Mysql Query 1.6.0.1
Native ip:172.5.1.183
2, install MySQL. With the default installation, you can
Folder selection in C:\mysql, do not change the default folder
3. Start MySQL
Installation as a service: C:\mysql\bin\mysqld--install
Start mysql:net start MySQL
Stop mysql:net stop MySQL
4, the system created database has Mysql,test
MySQL Save system data
Test database is used to test
5. Default login mode
In this 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 4 data by default
Use MySQL
Select Host,user,password from user;
+-----------+------+----------+
| Host | user | password |
+-----------+------+----------+
| localhost |          Root | |
| %         |          Root | |
|      localhost |          | |
|      %         |          | |
+-----------+------+----------+

These data represent the meaning of:
Username is root. Password users are able to log on from native and remote hosts, regardless of the
No matter what username. Password users are able to log on from this machine
Username is empty, password is empty and the user is not able to log on remotely (the field after user is n. So I can't log in)
Change Rootpassword
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 talent to land
You can use mysql-h 172.5.1.183-uroot to log in remotely or natively. This is based on the strategy of the second row.
Permission changes take effect:

  1) net stop MySQL    net start MySQL  2) c:\mysql\bin\mysqladmin flush-privileges

3) After logging in to MySQL, use the flush privileges statement
6. Create DATABASE staffer
Create DATABASE staffer;
7, the following statements in the MySQL environment in the run
Displays the database for which the user has permissions show databases;
Switch to staffer database use staffer;
Displays a table with permissions in the current database show tables;
Shows the structure of table staffer DESC staffer;
8. Create test environment
1) Create a database staffer
Mysql> CREATE DATABASE staffer
2) Create a table Staffer,department,position,depart_pos

CREATE TABLE S_position     (         ID int not NULL auto_increment,         name varchar (a) NOT null default ' manager ', #设定默认值         D Escription varchar (+),         primary key Pk_positon (ID)  #设定主键     );         CREATE TABLE Department     (         ID int not NULL auto_increment,         name varchar (a) NOT NULL default ' system department ', #设定默认值 
   description varchar (+),         primary key pk_department (ID)  #设定主键     );     CREATE TABLE Depart_pos     (         department_id int not NULL,         position_id int not NULL,         primary key Pk_ Depart_pos (department_id,position_id)  #设定复和主键     );     CREATE TABLE staffer     (         ID int not NULL auto_increment primary key,  #设定主键         name varchar (a) NOT null Def Ault ' John Doe ',  #设定默认值         department_id int not NULL,         position_id int not NULL,         unique (department_id, position_id)  #设定唯一值     );

3) Delete
Mysql>
drop table Depart_pos;
drop TABLE Department;
drop table s_position;
drop table staffer;
Drop database staffer;
9, change the structure
Mysql>
#表position添加列test
ALTER TABLE position Add (test char (10));
#表position改动列test
ALTER TABLE position Modify Test char (a) not null;
#表position改动列test默认值
ALTER TABLE position ALTER TEST set default ' system ';
#表position去掉test默认值
ALTER TABLE position ALTER test drop default;
#表position去掉列test
ALTER TABLE position drop column test;
#表depart_pos删除主键
ALTER TABLE Depart_pos drop PRIMARY key;
#表depart_pos添加主键
ALTER TABLE Depart_pos add primary key Pk_depart_pos (department_id,position_id);
10. Operation Data
#插入表department
INSERT INTO department (name,description) VALUES (' System department ', ' System Department ');
INSERT INTO department (name,description) VALUES (' PR ', ' PR ');
INSERT INTO department (name,description) VALUES (' Customer service ', ' service department ');
INSERT INTO department (name,description) VALUES (' financial department ', ' financial department ');
INSERT INTO department (name,description) VALUES (' Test department ', ' Test Department ');
#插入表s_position
Insert into s_position (name,description) VALUES (' director ', ' director ');
Insert into s_position (name,description) VALUES (' manager ', ' manager ');
Insert into s_position (name,description) VALUES (' Ordinary employees ', ' ordinary employees ');
#插入表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;
#插入表staffer
INSERT INTO staffer (name,department_id,position_id) VALUES (' Chanda ', 1, 1);
INSERT INTO staffer (name,department_id,position_id) VALUES (' Li Wenbin ', for each);
INSERT INTO staffer (name,department_id,position_id) VALUES (' Ma Jia ', 1, 3);
INSERT INTO staffer (name,department_id,position_id) VALUES (' Zhiqiang ', 5, 1);
INSERT INTO staffer (name,department_id,position_id) VALUES (' Yang Yuju ', 4, 1);
11, Query and delete operations
#显示系统部的人员和职位
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 department ';
#显示系统部的人数
Select COUNT (*) from staffer A,department b
where A.department_id=b.id and B.name= ' System Department '
#显示各部门的人数
Select COUNT (*) cou,b.name
From staffer A,department b
where a.department_id=b.id
Group BY B.name;
#删除客服部
Delete from department where name= ' customer service department ';
#将財务部改动为財务一部
Update department set name= ' finance a ' where name= ' financial department ';
12. Backup and Recovery
Backing up the database staffer
C:\mysql\bin\mysqldump-uroot-proot Staffer>e:\staffer.sql
The resulting staffer.sql is an SQL script. Does not contain a built-in statement, so you need to manually
Create database talent enough to import
To restore the database staffer, you need to create an empty library staffer
C:\mysql\bin\mysql-uroot-proot Staffer<staffer.sql
Assuming that 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
But this kind of word system can not exist staffer library. and cannot import a database of other names,
Of course you can manually change the Staffer.sql file
13. Import data from text to database
1) using the tool C:\mysql\bin\mysqlimport
The purpose of this tool is to import the file into the same table as the file extension name. Such as
Staffer.txt,staffer are imported into the staffer table
Frequently used options and features such as the following
-D or--delete all information in the data table before the new data is imported into the datasheet
-F or--force whether or not an error is encountered. Mysqlimport will force the data to continue to be inserted
-I or--ignore mysqlimport skip or ignore those with the same unique
Keyword rows, the data in the import file is ignored.
-L or-lock-tables data is inserted before the table is locked. This prevents,
When you update a database, users ' queries and updates are affected.
-R or-replace This option is the opposite of the-I option; this option overrides
The table has the same unique keyword record.
--fields-enclosed-by= Char
Specifies what the data in the text file is recorded in, in very many cases
The data is enclosed in double quotes. By default, the data is not surround by the word.
--fields-terminated-by=char
Specifies the delimiter between the values of individual data. In a period-delimited file,
The delimiter is a period. You can use this option to specify a delimiter between data.
The default delimiter is Jump Geff (Tab)
--lines-terminated-by=str
This option specifies a delimited string of data between rows and rows in a text file
or character. By default, Mysqlimport with newline as the line delimiter.
You can choose to replace a single character with a string:
A new line or a carriage return.
The Mysqlimport command frequently uses options that have a-V display version number,
-P Prompt for password (password) and so on.
There is a problem with this tool. Some columns cannot be ignored, which can be a big hassle for our data import. Although
This field can be set manually. But there will be inexplicable results. Let's do a simple demo sample
We define for example the following depart_no.txt, which are saved in the E-drive, spaced as tabs \ t
10 10
11 11
12 24
Execute such as the following command
C:\mysql\bin\mysqlimport-uroot-proot staffer E:\depart_pos.txt
There is no enclosing symbol for the column used here. Cut using the default \ t, because the use of other symbols will have problems,
Don't know why it's not 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 the ability to specify column imports. Scale as shown below
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 are used under Windows, do not know whether it is the cause of windows or the problem of Chinese,
And does not specify the column it produces a null value, which is obviously not what we want, so use these tools cautiously

MySQL Learning notes