Mysql learning note 1, installation environment: windows xp mysql 4.0.17 Download EMS mysql query 1.6.0.1 Native IP: 172.5.1.1832 from http://www.mysql.com, install mysql, use the default installation to select C: / mysql, do not modify the default directory 3, start mysql installation as the service: c: / mysql / bin / mysqld --install launched mysql: net start mysql stop mysql: net stop mysql4, the database created by MySQL, Test MySQL Save system data TEST database 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 is in mysql.user table In the system default, there are 4 data for use mysql select host, user, password from user; --------- ---- ---------- | Host | User | Password | --------- ---- ---------- | Localhost | root | | | | LocalHost | | | |% | | | ----------- ---- ---------- These data representatives: User name ROOT, the user can log in to any user name from this unit and any remote host, the password is empty, can log in the user from the machine, the password is empty, the password is not available from the remote login (User The field is n, so it is not allowed to log in to the remote login after version 4.0.17, so% display may be similar to Build or localhost.localdomain, if you want to reach the purpose of the remote login, you can C: / Mysql / bin / mysql -uroot mysql update user set host = '%', password = password ('root') where user = 'root' and host in ('build', 'localhost.localdomain "); this can be root / root You can log in in the remote, if you modify the local root password, you can update User set password = password ("root") where user = 'root' and host = 'localhost' Next, you need to use mysql -uroot -Proot to log in Remote or local machines can log in with mysql -h 172.5.1.183 -uroot, which determines the right to modify according to the second row of policies:
1) NET STOP MYSQL NET START MYSQL 2) C: / MySQL / BIN / MYSQLADMIN FLUSH-Privileges 3) After logging in to MySQL, other increasing users authorized by the Flush Privileges statement 1) In addition to the record USER table in the user table In addition to HOST Outside the user, there are many permissions fields, but the number of fields is different in different versions, you need to pay attention to the number of columns, so if you want to increase users, you need c: / mysql / bin / mysql -uroot mysql insert INTO User Values ('172.5.1.201', 'root', Password ("test"), 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', ' Y ',' Y ',' Y ',' Y ',' Y ',' Y ',' Y ',' Y ',' Y ',' Y ',' Y ',' Y ',' Y ' , 'Y', 'Y', 'Y', 'Y', 'Y') Because the permission field is default to 'n', so I have to write it all, so this It is better to use Grant 2) Use a GRANT statement, such as adding a full permissions, user grant all privilegs on *. * To shiyq @ "172.5.1.201" Identified by 'Stone' with grant option; Reclaim Authorization 1) Operation USER Table # root @ localhost recovery of Shutdown_priv update user set Shutdown_priv = 'N' where user = 'shiyq' and host = '172.5.1.201'; deleted shiyq @ "172.5.1.201" delete from user where user = 'root' and host = ' 172.5.1.201 '2) Use the revoke command # Delete user shiyq @ "172.5.1.201" mysql> revoke all privileges on *. * From shiyq@'172.5.1.201'; # 用户 用户 用户Q @ " 172.5.1.201 "SHUTDOWN Permissions MySQL> Revoke Shutdown on *. * From Shiyq @" 172.5.1.201 "; Grant and Revoke operations also affect the USER table, so they are the same, see what you like: The USER table saves all the databases for this machine. If you have to get more accurate permission control, then you need to operate Host, DB, Table_Priv table,
MySQL permissions are the panegivers of 3 table permissions, which can also be operated with Grant, Revoke operation, interested in further study below has a simple case C: / mysql / bin / mysql -uroot -Proot MySQL> CREATE DATABASE shiyq; use shiyq; create table test (id int not null auto_increment primary key, name varchar (20) not null default 'test'); grant drop, alter, create on shiyq * to test @ localhost identified by "test".; Grant Insert, Update, Delete on test to test @ localhost; you can check the record of User, Host, DB, Tables_PRIV, you can see the result of your operation, # Because these tables have more columns, It is easy to use EMS mysql query 1.6.0.1, #non the user who has just created, try to see C: / mysql / bin / mysql -utest -ptest mysql> show databases; # # 看 不同 不同 uq; show Tables; CREATE TABLE TEST1 (ID INT Not Null Primary Key); Select * from test1; # system prompts you no permissions, select * from test; #, you can perform the following command INTO TEST (Name) VALUES ('Hello'); UDPATE TEST '; delete from test; SHIYQ.TEST SELECT permission Grant Select on test to test @ localhost; this is used in ShiyQ / S TONE login can execute Select * from test; 6, create a database Staffer Create Database Staffer; 7, the following statement is in the MySQL environment in executing the database Show DatabasesSSSSSSSSSSSSSS, switching to the Staffer Database; showing the current database Table Show Tables; Display Table Staffer Structure DESC Staffer; 8 ; mysql> exit; c: / mysql / bin / mysql -ushiyq -pstone staffer 2) create table 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 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 Department', # setting default 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 complex and the primary key); create table staffer (id int not null auto_increment primary key, # sets the master key name varchar (20) not null default 'Anonymous', # default value is set department_id int not null, position_id int not null, unique (department_id, position_id) # Set a unique value); 3) Delete mysql> Drop Table Depart_pos; Drop Table Department; Drop Table S_Position; Drop Table S_Position; Drop Table Staffer; Drop Database Staffer; 9, Modify Structure MySQL> # Table S_Position Increase Test ALTER TABLE S_POSITION a DD (Test Char (10)); # Test ALTER TABLE S_POSITION MODIFY TEST ALTER TABLE S_POSITION MODIFY TEST ALTER TABLE S_POSITION MODIFY TEST CHAR (20) Not Null; # Table S_Position Modify Column Test Default ALTER TABLE S_POSITION ALTER TEST SET Default 'System'; # Table S_Position Remove test default alter table s_position alter test drop default; # table s_position removing column test alter table s_position drop column test; # table depart_pos drop primary alter table depart_pos drop primary key; # table depart_pos increase the master key alter table depart_pos add primary key PK_depart_pos (department_id , POSITION_ID); 10, Operation Data # Insert Table DEPARTMENT INTO Department (Name, Description) VALUES ('System Department,' System Department); Insert Into Department (Name, Description) VALUES ('public relations department', 'public relations unit');
INSERT INTO Department (Name, Description) VALUES ('customer service department', 'customer service "); INSERT INTO Department (Name, Description) VALUES (Finance", Finance'); Insert Into Department (Name, Description) VALUES ('Test Department', 'Test Department'); # Insert Table 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 ( 'normal employee', 'general staff'); # 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 Table Staffer Insert Into Staffer (Name, Department_ID, POSITION_ID) VALUES ('Chen Dak Cecre ", 1); Insert Into Staffer (Name, Department_ID, POSITION_ID) Values (' Levinbin ', 1, 2) Insert Into Staffer (Name, Department_ID, POSITION_ID) VALUES ('Ma Jia', 1, 3); Insert Into Staffer (Name, Department_ID, POSITION_ID) VALUES ('志强', 5, 1); Insert Into Staffer (Name, DEPARTMENT_ID, POSITION_ID) VALUES ('Yang Yuru', 4, 1); 11, Query and Delete Operation # Display System Divasters and Jobs Select A.Name, B.Name Department_name, C.Name Positio n_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 part'; # Display system part SELECT COUNT (*) from Staffer A , department b where a.department_id = B.ID and b.Name = 'system part' # Display the number of people in each department Select Count (*) Coun, B.Name from Staffer A, Department B Where a.Department_ID = B.ID Group by b.name; # Delete customer service department delete from department where name = 'customer service department'; # # 财 财 部 部 财 财 财 一 一 一;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
12, backup and recovery backup database Staffer C: / mysql / bin / mysqldump -uroot -Proot Staffer> E: /staffer.sql getting Staffer.sql is a SQL script, not including the statement of the library, so you need manual creation The database can be imported into the restore database Staffer, you need to create a space Staffer C: / mysql / bin / mysql -uroot -Proot Staffer
13. Import data from text 1) Use tools C: / mysql / bin / mysqlimport's role to import files into and remove file extension names, such as Staffer.txt, Staffer is imported The common options and functions in the Staffer table and the following -D or -dete new data import data table in previous delete data data tables all information in the data table --f or --force does not encounter an error, MySQLIMPORT will force to continue inserting data -i or OR --ignore MySQLIMPORT Skip or ignores those rows with the same unique keyword, the data in the import file will be ignored. -L or -lock-tables data is inserted before locking the table, which prevents the user's query and update when updating the database. -R or-replace This option is reversed to the role of the -i option; this option will replace the same unique key record in the table. --Fields-enclosed- by = CHAR specifies what is included in the record of data in the text file, and the data is enclosed in double quotes. By default, the data is not caced by the character. --Fields-terminated- by = char specifies the separator between the values of each data, in the file separated by the file, the separator is the period. You can use this option to specify a separator between the data. The default separator is a jumper (Tab) --Lines-Terminated- by = STR This option specifies the partition string or character of the line between the line between the line between the line. By default, mysqlimport is separated by newline. You can choose to replace a single character with a string: a new line or a carriage return. The options commonly used by mysqlimport commands include the -v display version (Versice), the -p prompt input password (Password).
This tool has a problem, which cannot ignore some columns, which has a lot of trouble for our data import, although it can be manually set up, but there will be inexplicable results, we do a simple example we define the following department_no. TXT, saved in the E disk, interval to Tabs / T 10 10 11 11 12 24 Perform the following command C: / mysql / bin / mysqlimport -uroot -Proot Staffer E: /DEPART_POS.TXT here is here no listing symbols The division uses the default / t because other symbols will have problems. I don't know if it is Windows. 2) Load data infile file_name INTO TABLE_NAME (Column1_name, column2_name) This command is used in MySQL> prompt, the advantage is Specify list import, examples 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 Question, I don't know if Windows is still Chinese, and the columns that do not specify will have a null value. It is obviously not what we want, so use these tools 14, MySQL management tools Mysql is a relatively simple system, Most client tools with MySQL can meet our requirements, but under specific conditions, you can use some tools to make your management, development, and debugging work easier. Here, I will introduce the software I organized 1) phpmyadmin It is a management tool based on the B / S structure. The current version is 2.6.0-PL2, which can find the latest version and download link from http://sourceforge.net/projects/phpmyadmin/, Because this is a web tool, the configuration process is a bit trouble, now I will explain, the structure I use is Windows XP Apache 2.0.52 php 4.3.9 mysql 4.0.22 (Mysql 4.0 version is big With a small difference, if other versions encounter problems, we can discuss it together) 1) Installing AAPACHE from http://httpd.apache.org/download.cgi to download the latest Apache version Click Setup.exe installation to D: / Apache GROUP / APACHE2, the installer will install a name Apache2, if any abnormal situation, we can manually install, d: / apache group / apache2 / bin / apache -k install net start apache2 net stop apache2 2) Installation PHP can download the latest PHP version from http://www.php.net, it is best to use version 4.xx. It has two versions of installation and zip. Since the installation version cannot fully support Apache, there is no many extension components, I Using ZIP package, a) open the ZIP package,
Copying the file in the root directory to C: / php b) The configuration process can be done according to install.txt in the directory, I simply explain the process of installing the PHP installed as a module of Apache, copy php.ini-dist to C Under WINDOWS, modify the name to PHP.INI copy php4ts.dll to c: / windows / system32 in d: / apache group / apache2 / conf / httpd.conf plus loadModule PHP4_Module C: / PHP / SAPI / PHP4APache2 .dll addtype application / x-httpd-php .php c) Add a file info.php under D: / Apache Group / Apache / HTDOCS, content is Enter http in the browser: / /localhost/info.php If the output is seen, the PHP configuration is successful 3) Configure phpMyAdmin to extract phpMyAdmin to E: / homPage, the PHPMYADMIN's work directory is E: /HomePage/phpmyadmin-2.6.0-pl2 in d: / Apache group / apache2 / conf / http.conf Sets virtual directory and permissions, adding the following alias / phpadmin "e: /Homepage/phpmyadmin-2.6.0-pl2"
