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, using the default installation can
Directory selection in C:\mysql, do not modify the default directory
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 for testing
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:
User name is root, password is empty user can log in from the local and any remote host
Any user name, password is empty user can log in from the local
User name is empty, password is empty user can not log in from remote (the field after user is n, so cannot log in)
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 in.
The remote or native can use the Mysql-h 172.5.1.183-uroot login, which is determined based on the strategy of the second row
Permission modification takes 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 statement in the MySQL environment in the execution
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 a 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 ', #设定默认值 des Cription varchar (+), PRIMARY key Pk_positon (ID) #设定主键); CREATE TABLE department (ID int not NULL auto_increment, name varchar (a) NOT NULL default ' system department ', #设定 The default value is 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 de Fault ' John Doe ', #设定默认值 department_id int not NULL, position_id int NOT null, unique (department_id,p osition_id) #设定唯一值);
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>
#表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 (' Finance department ', ' finance 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= ' finance 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 a SQL script that does not include the built-in statements, so you need to manually
Create a database before you can import
To restore the database staffer, you need to create an empty library 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
However, the system can not exist staffer library, and can not import other names of the database,
Of course you can manually modify 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 files into and out of the table with the same name as the file extension, as
Staffer.txt,staffer are imported into 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 datasheet
-F or--force Mysqlimport will force the data to be inserted regardless of whether or not an error is encountered
-I or--ignore mysqlimport skip or ignore those with the same unique
keyword, the data in the import file is ignored.
-L or-lock-tables data is inserted before locking the table, which prevents the
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
A record with the same unique keyword in the table.
--fields-enclosed-by= Char
Specifies what the data in the text file is to be recorded in, in many cases
The data is enclosed in double quotation marks. By default, the data is not surround by the word.
--fields-terminated-by=char
Specifies the delimiter between the values of each 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 in a text file and lines
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 common options for the Mysqlimport command are the-v Display versions (version),
-P Prompt for password (password) and so on.
There is a problem with this tool and it is not possible to ignore some of the columns, which is a big hassle for our data import, although
This field can be set manually, but there will be inexplicable results, we do a simple example
We define the following depart_no.txt, which are saved in the E-drive, spaced in tabs \ t
10 10
11 11
12 24
Execute the following command
C:\mysql\bin\mysqlimport-uroot-proot staffer E:\depart_pos.txt
There is no enclosing symbol for the column, and the partition uses the default \ t, because there are problems with other symbols,
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, and the advantage is that you can specify the column import, 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 with caution
MySQL Learning notes
