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 Field is N, so you can't log in) Modify root password mysql -uroot use mysql update user set password = password ("root") Where use R = 'root' and host = 'localhost' Next time you need to use mysql -uroot -Proot to log in to remote or locally to log in with mysql -h 172.5.1.1.183 -uroot, this policy determines permission based on the second line of policies Modification Effective: 1) Net Stop MySQL Net Start MySQL 2) C: / MySQL / BIN / MYSQLADMIN FLUSH-privileges 3) After logging in to MySQL, use Flush Privileges statement 6, create database Staffer Create Database Staffer; 7, the following statement in MySQL The environment is executing the database show databases of the user owns privilege; switch to the Staffer Database Use Staffer; Display Table Show Tables in the current database; Display Table Staffer DESC STAFFER; 8, create a test environment 1) Create a database Staffer MySQL>
create database 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', # default value is set 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) # setting 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) # multiplexed and set the primary key); create table staffer (id int not null auto_increment primary key, # set Master key varchar (20) Not null default '无 名', # set default value department_id int not null, position_id int not null, unique (department_id, position_id) # Set a unique value); 3) Delete mysql> Drop Table de Part_pos; drop table s_position; drop table staffer; drop database staffer; 9, modify structure mysql> # Table Position Add Column ALTER TABLE POSION TEST ALTER TABLE TEST ALTER TABLE Position Modify Test Char (20) Not NULL; # Test Thermal Test Set Default 'System'; # Test 's Remove Test; # Test Drop default; # Test Drop default; # Test ALTER TEST DROP DEFAULT; ALTER TABLE POSITION DROP Column Test; # Table DEPART_POS Remove Primary Keys Alter Table DEPART_POS DROP Primary Key;
10. Operation data # Insert Table Department Insert INTO Department (Name, Description) VALUES ('System Department ",' System Department '); Insert Into Department (Name, Description) VALUES (' Public Relation Department ',' Public Relation Department); 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 # Displays the personnel and positions of the system part. Select A.Name, B.Name Department_name, C. Name position_name from stape a, divartment 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 Department' # Displays 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 the customer service department delete from department where name = 'customer service department'; # # 财 财 部 为 财 财 财 一 一 一 财 财 数据 数据 数据 数据 数据 数据 数据 数据 数据 数据 数据 数据 数据 数据 数据 数据 数据 数据 数据 数据 数据 数据 数据 数据C: / mysql / bin / mysqldump -uroot -Proot Staffer> E: The staffer.sql gets STAFFFER.SQL is a SQL script that does not include the statement of the library, so you need to manually create a database to import recovery database Staffer, Need to create a space Staffer C: / mysql / bin / mysql -uroot -Proot Staffer
