The difference between Mysql and Oracle
1. Instance difference
Mysql is a lightweight database, open source and free. Oracle is charged and the price is very high.
One instance of Mysql can operate multiple libraries, while one instance of Oracle can only correspond to one library.
After Mysql is installed, 300M and Oracle have about 3G.
2. Operation difference
Primary key: Mysql generally uses auto-growth type, while Oracle needs to use sequence object.
Single quotation mark processing: double quotation marks can be used to wrap strings in mysql, and single quotation marks can only be used to wrap strings in oracle.
paging SQL statement: MySQL uses limit, while Oracle uses built-in views and rownum pseudo-columns.
Transaction processing: mysql defaults to auto-commit, while oracle defaults not to auto-commit, and the user’s CTL language is required for transaction submission.
Operating Mysql
The difference between mysql and oracle in the physical isolation mechanism of data:
after the oracle is installed, it will There is an oracle instance and a library, which contains data files, which are called tablespaces in oracle.
So after Oracle is installed, we must first create a permanent tablespace, and then create users. The permanent table space is then allocated to this user.
Then go to create a user, and then allocate a table space to him. Physical isolation is achieved through table space.
So it is enough to have one database in Oracle, and then we will create a table space for him.
mysql is an instance that can correspond to multiple libraries. There is no concept of table space in mysql, so we You can create different libraries, and then users can directly operate the different libraries. There are different data files in each library.
1. Create and delete a database
1.1.1 Use commands to create a database
< p>create database database name default character set character encoding;
1.1.1.1 example
Create a test database, and view the database and its encoding.
create database test default character set utf-8;
Create database:
create database name;
View database
show databases;
View database code:
select schema_name ,default_character_set_name from information_schema.schemata where schema_name=’test’;
1.2 Delete database
drop database database name;
1.2.1.1 example
drop database test;
2. Select a database
In which database you need to create a table, you need to select the database first.
Use the name of the library that needs to be selected;
2.1 Example 1
Create a database named test, coded as utf-8;
< p> create database test default character set utf8;
2.2 Example 2
Select the database;
use test;
3. Data types in Mysql
3.1 Numerical Types
Mysql supports all prepared sql numerical data types.
As an extension of the sql standard, Mysql also supports the integer types tinyint, mediumint and bigint.
Mysql data type
tinyint(m) Byte range (-32768- 32767)
mediumint(m) 3 byte range (-8388608- 8388687)
int(m) 2147483648- 2147483647)
bigint(m) 8 byte range (+-9.22*10 to the 18th power)
Length in numeric type m refers to the display length, not the storage length. It is only useful when the field specifies zerofill.
For example: int(3), if the actual value is 2, if the column specifies zerofill, the query result is 002, Fill it with 0 on the left.
3.2 Floating Point
float(m,d) Single-precision floating point 8-bit precision (4 bytes) m total number, d decimal place
double(m,d) double precision floating point 16-bit precision (8 digits) m total number, d decimal places
、
3.3 string type< /h2>
char(n) fixed length, up to 255 characters
varchar(n) variable length, up to 65535 characters
tinytext variable length, up to 255 Characters
text variable length, up to 65535 characters
mediumtext variable length, up to 2 to the 24th power-1 character
longtext is variable Length, up to 2 to the 32nd power-1 character
3.3.1char and varchar
1.char(n ) If the number of stored characters is less than n, fill it with a space, and then remove the space when inquiring. Therefore, there can be no spaces at the end of the string stored in the char type, and varchar is not limited to this.
2.Char type string retrieval is faster than varchar type.
3.3.2 varchar and text
1. varchar can be specified n, text cannot be specified, the internal storage varchar is the actual number of characters stored + 1 Byte (n<=255), text is the actual number of characters + 2 bytes.
2.text type cannot have a default value.
3. Varchar can directly create an index, and text creates an index to specify the first multiple characters. Varchar query speed is faster than text. In the case of creating indexes, the index of text does not seem to work.
3.4 Date type
mysql data type
-2
Time ’12:25:36′
datetime date and time’2008-12-2 22: 06:44′
automatically record time
3.5 Binary Data
1. BLOB and TEXT storage methods are different, text is stored in text mode, English storage is case-sensitive, and Blob is binary Stored in a way, regardless of case.
2. The data stored in BLOB can only be read out as a whole.
3.text can specify the character set, and BLOB is different from the specified character set.
DDL language
4. Create table and delete table
4.1 Create table
create table employees (employee_id int,last_name varchar(30), salary float(8,3))
4.2 View table
show tables;
4.3 Delete table
p>
drop table employees;
5. Modify the table
5.1 Use the ddl statement to modify the table name
p>
alter table old table name rename new table name
5.1.1 example one
modify the employees table name to emp.
alter table employees rename emp;
5.2 Modify column name
Alt table name change column Old column name New column name Type
5.2.1 Example
Change the last_name in the emp table to name
alter table employees change column< /strong> last_name name varchar(30)
5.3 Use ddl to modify column type
alter table table name modifity column name new type
5.3.1 example
Specify the length of name in emp as 49;
alter table employees MODIFY name varchar(40);
5.4 Use ddl statement to add columns
alter table table name add column new column name type
5.4.1 example
Add a new lie to the emp table as commission_pct
alter table employees add column commission_pct float(4,2)
5.5 Use ddl to delete columns
alter table Name drop column Column name
5.5.1 example
Delete commission_pct in the emp table
alter table emp drop column commsission_pct;
Query table constraint information
show keys from table;
< /p>