Basic MySQL Data Table operation 3: A comprehensive example of basic mysql operations

Basic MySQL Data Table operation 3: A comprehensive example of basic mysql operations

1. Create a database

mysql> create database company;mysql> use company;

Ii. Create a table

1. Create an offices table

mysql> create table offices    -> (    -> officeCode int(10) NOT NULL UNIQUE,    -> city varchar(50) NOT NULL,    -> address varchar(50) NOT NULL,    -> country varchar(50) NOT NULL,    -> postalCode varchar(15) NOT NULL,    -> PRIMARY KEY (officeCode)    -> );

2. Create a table employees

mysql> create table employees    -> (    -> employeeNumber int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,    -> lastName VARCHAR(50) NOT NULL,    -> firstName VARCHAR(50) NOT NULL,    -> mobile VARCHAR(25) NOT NULL,    -> officeCode int(10) NOT NULL,    -> jobTitle VARCHAR(50) NOT NULL,    -> birth DATETIME,    -> note VARCHAR(255),    -> sex VARCHAR(5),    -> CONSTRAINT office_fk FOREIGN KEY (officeCode) REFERENCES offices(officeCode)    -> );

3. view the tables created in the database

mysql> show tables;+-------------------+| Tables_in_company |+-------------------+| employees         || offices           |+-------------------+

mysql> desc offices;+------------+-------------+------+-----+---------+-------+| Field      | Type        | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+-------+| officeCode | int(10)     | NO   | PRI | NULL    |       || city       | varchar(50) | NO   |     | NULL    |       || address    | varchar(50) | NO   |     | NULL    |       || country    | varchar(50) | NO   |     | NULL    |       || postalCode | varchar(15) | NO   |     | NULL    |       |+------------+-------------+------+-----+---------+-------+

mysql> desc employees;+----------------+--------------+------+-----+---------+----------------+| Field          | Type         | Null | Key | Default | Extra          |+----------------+--------------+------+-----+---------+----------------+| employeeNumber | int(11)      | NO   | PRI | NULL    | auto_increment || lastName       | varchar(50)  | NO   |     | NULL    |                || firstName      | varchar(50)  | NO   |     | NULL    |                || mobile         | varchar(25)  | NO   |     | NULL    |                || officeCode     | int(10)      | NO   | MUL | NULL    |                || jobTitle       | varchar(50)  | NO   |     | NULL    |                || birth          | datetime     | YES  |     | NULL    |                || note           | varchar(255) | YES  |     | NULL    |                || sex            | varchar(5)   | YES  |     | NULL    |                |+----------------+--------------+------+-----+---------+----------------+

III. Basic table operations

1. Modify the mobile field of the table employees to the end of the officeCode field.

mysql> alter table employees MODIFY mobile varchar(25) after officeCode;mysql> desc employees;+----------------+--------------+------+-----+---------+----------------+| Field          | Type         | Null | Key | Default | Extra          |+----------------+--------------+------+-----+---------+----------------+| employeeNumber | int(11)      | NO   | PRI | NULL    | auto_increment || lastName       | varchar(50)  | NO   |     | NULL    |                || firstName      | varchar(50)  | NO   |     | NULL    |                || officeCode     | int(10)      | NO   | MUL | NULL    |                || mobile         | varchar(25)  | YES  |     | NULL    |                || jobTitle       | varchar(50)  | NO   |     | NULL    |                || birth          | datetime     | YES  |     | NULL    |                || note           | varchar(255) | YES  |     | NULL    |                || sex            | varchar(5)   | YES  |     | NULL    |                |+----------------+--------------+------+-----+---------+----------------+

2. Rename the birth field of employees to employee_birth.

mysql> alter table employees CHANGE birth employee_birth DATETIME;mysql> desc employees;+----------------+--------------+------+-----+---------+----------------+| Field          | Type         | Null | Key | Default | Extra          |+----------------+--------------+------+-----+---------+----------------+| employeeNumber | int(11)      | NO   | PRI | NULL    | auto_increment || lastName       | varchar(50)  | NO   |     | NULL    |                || firstName      | varchar(50)  | NO   |     | NULL    |                || officeCode     | int(10)      | NO   | MUL | NULL    |                || mobile         | varchar(25)  | YES  |     | NULL    |                || jobTitle       | varchar(50)  | NO   |     | NULL    |                || employee_birth | datetime     | YES  |     | NULL    |                || note           | varchar(255) | YES  |     | NULL    |                || sex            | varchar(5)   | YES  |     | NULL    |                |+----------------+--------------+------+-----+---------+----------------+

3. Modify the sex field. The data type is CHAR (1). It is not empty.

mysql> alter table employees MODIFY sex CHAR(1) NOT NULL;mysql> desc employees;+----------------+--------------+------+-----+---------+----------------+| Field          | Type         | Null | Key | Default | Extra          |+----------------+--------------+------+-----+---------+----------------+| employeeNumber | int(11)      | NO   | PRI | NULL    | auto_increment || lastName       | varchar(50)  | NO   |     | NULL    |                || firstName      | varchar(50)  | NO   |     | NULL    |                || officeCode     | int(10)      | NO   | MUL | NULL    |                || mobile         | varchar(25)  | YES  |     | NULL    |                || jobTitle       | varchar(50)  | NO   |     | NULL    |                || employee_birth | datetime     | YES  |     | NULL    |                || note           | varchar(255) | YES  |     | NULL    |                || sex            | char(1)      | NO   |     | NULL    |                |+----------------+--------------+------+-----+---------+----------------+

4. Delete Field note

mysql> alter table employees DROP note;mysql> desc employees;+----------------+-------------+------+-----+---------+----------------+| Field          | Type        | Null | Key | Default | Extra          |+----------------+-------------+------+-----+---------+----------------+| employeeNumber | int(11)     | NO   | PRI | NULL    | auto_increment || lastName       | varchar(50) | NO   |     | NULL    |                || firstName      | varchar(50) | NO   |     | NULL    |                || officeCode     | int(10)     | NO   | MUL | NULL    |                || mobile         | varchar(25) | YES  |     | NULL    |                || jobTitle       | varchar(50) | NO   |     | NULL    |                || employee_birth | datetime    | YES  |     | NULL    |                || sex            | char(1)     | NO   |     | NULL    |                |+----------------+-------------+------+-----+---------+----------------+

5. Add the field name favoriate_activity. The data type is VARCHAR (100)

mysql> alter table employees ADD favoriate_activity varchar(100);mysql> desc employees;+--------------------+--------------+------+-----+---------+----------------+| Field              | Type         | Null | Key | Default | Extra          |+--------------------+--------------+------+-----+---------+----------------+| employeeNumber     | int(11)      | NO   | PRI | NULL    | auto_increment || lastName           | varchar(50)  | NO   |     | NULL    |                || firstName          | varchar(50)  | NO   |     | NULL    |                || officeCode         | int(10)      | NO   | MUL | NULL    |                || mobile             | varchar(25)  | YES  |     | NULL    |                || jobTitle           | varchar(50)  | NO   |     | NULL    |                || employee_birth     | datetime     | YES  |     | NULL    |                || sex                | char(1)      | NO   |     | NULL    |                || favoriate_activity | varchar(100) | YES  |     | NULL    |                |+--------------------+--------------+------+-----+---------+----------------+

6. Delete the table offices

1) The foreign key of the table is set during table creation, so it cannot be deleted directly.

mysql> drop table offices;ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

2) Delete the foreign key constraint of the employees table

mysql> alter table employees drop foreign key office_fk;

3) Delete the offices table

mysql> drop table offices;Query OK, 0 rows affected (0.03 sec)

mysql> show tables;+-------------------+| Tables_in_company |+-------------------+| employees         |+-------------------+

7. Modify the storage engine of the employees table to MyISAM.

mysql> alter table employees ENGINE=MyISAM;Query OK, 0 rows affected (0.12 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show create table employees\G;*************************** 1. row ***************************       Table: employeesCreate Table: CREATE TABLE `employees` (  `employeeNumber` int(11) NOT NULL AUTO_INCREMENT,  `lastName` varchar(50) NOT NULL,  `firstName` varchar(50) NOT NULL,  `officeCode` int(10) NOT NULL,  `mobile` varchar(25) DEFAULT NULL,  `jobTitle` varchar(50) NOT NULL,  `employee_birth` datetime DEFAULT NULL,  `sex` char(1) NOT NULL,  `favoriate_activity` varchar(100) DEFAULT NULL,  PRIMARY KEY (`employeeNumber`),  KEY `office_fk` (`officeCode`)) ENGINE=MyISAM DEFAULT CHARSET=latin11 row in set (0.01 sec)

8. Change the employees table name to employees_info.

mysql> alter table employees rename employees_info;Query OK, 0 rows affected (0.00 sec)mysql> show tables;+-------------------+| Tables_in_company |+-------------------+| employees_info    |+-------------------+1 row in set (0.00 sec)

If you encounter any problems during your attempt or my code is incorrect, please correct me. Thank you very much!

Contact: david.louis.tian@outlook.com

Copyright @: reprinted, please indicate the source!

Help: mapreduce operates mysql Databases on instances and uses new APIs?

1. Check the basic concepts of map/reduce and the basic knowledge about hadoop.
2. map/reduce is a computing architecture and has no direct relationship with mysql operations. If you want to operate mysql, you can use jdbc or Hive to pass values to it. Map/reduce does not operate mysql instances, and does not comply with the map/reduce design principles.
3. It is not absolutely impossible to operate, but it is not so designed and programmed. Check the information again.

Help me with a job for MySQL database operations

I. Database Operation example:
1. create databases and tables
Drop database if exists mysqlDB;
Create database mysqlDB;
Use mysqlDB;
Create table dept
(
Deptno int primary key auto_increment,
Dname varchar (14) unique not null,
Loc varchar (10)
) ENGINE = innodb CHARSET = gbk; create table emp
(
Empno int primary key auto_increment,
Ename varchar (10) unique,
Job varchar (9) not null,
Mgr int,
Hiredate datetime,
Sal float (7,2) not null,
Comm float (7,2 ),
Deptno int,
Index par_ind (deptno ),
Foreign key (deptno) references dept (deptno)
) ENGINE = innodb CHARSET = gbk; 2. Insert data: (Chinese Garbled text: MY. ini file)
Insert into dept (dname, loc) values ('finance Department ', 'u.s.'), ('business Department ', 'China ');
Insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values
(1001, 'xiaoming ', 'employee', 1002, now (), 1200,200, 1 ),
(1002, 'Heaven, 'supervisor', 1002, now (), 1500,500, 1 ),
(1003, 'da', 'manager', 1002, now (), 2 ),
(1004, 'silent', 'secretarial ', 1002, now (), 1200,200, 2 );
Or:
Insert into tablename (list ....)
Select <list...> from tablename3. Execute MYSQL script batch processing:
Write the preceding command into a text file. Assume It is DBscript. SQL. Run the following command on the command line:
Mysql-u root-p password <c:/DBscript. SQL;
Or use
Mysql> source c:/DBscript. SQL ;. 4. Batch import data:
The following data is the contents of the dept and emp tables, which are copied to the corresponding file.