[Database 02] database data type engine type

Basic database operation

Relational database SQL non relational database NoSQL (Not noly sql) common: redis, MongDB

1. Basic command line operation of MySQL

Each line must be accompanied by; ending

-- get into
mysql -uroot -p
-- Modify user name and password (actually modify the table where the password is stored)
update mysql.user set authentication_string=password('') where user = 'root' and Host = 'localhost';
-- Refresh permissions
flush privileges;
-- Show all databases
show databases;
-- Switch database
mysql> use school
Database changed
-- Show all tables
show tables;
-- Describe information such as rows and columns in the table
desc student;-- student Is the table name

DDL, DML, DQL, DCL D... L: Database... Language

Definition, management, query, control

2. Operation database

Operation database, operation table, operation data

1. Operation database

  1. establish

    create database Database name ;
    -- Add judgment
    create database if not exists Database name ;
    
  2. delete

    drop database Database name ;
    drop database if exists Database name ;
    
  3. use

    -- Switch database
    use Database name ;
    -- If the object name is a special character or field (and sql If there are duplicates in the system field, it will be added``)
    select `user` from student; -- user yes student An attribute in the table
    
  4. view the database

    show databases;
    

2. Database data type

Common:

Int corresponds to int in java

varchar corresponds to String

Textused to store long text

Timestamp is the timestamp,

datetime common

For int type in the database, the set Length is only related to Zerofill in the field attribute (fill in a few zeros). After it is set to int, the maximum amount of data is 4 bytes, which has nothing to do with the set Length.

  1. numerical value

    • tinyint 1 byte, smallint 2 bytes, mediumint 3 bytes, int 4 bytes; = = bigint 8 bytes;
    • float 4 bytes
    • double 8 bytes
    • Floating point number in decimal string (for financial calculation)
  2. character string

    • char fixed long string, 0 ~ 255
    • varchar variable length string 0 ~ 65535
    • Tiny text 2 ^ 8 - 1, enough blog information
    • **Text text string, large article 2 ^ 16 - 1**
  3. Time date

    java.util.Date

    • Date yyyy-mm-dd date
    • Time HH: mm: SS time date
    • datetime is the most commonly used time format
    • Timestamp timestamp, milliseconds from 1970.1.1 to now
    • Year means year
  4. null

    • No value, unknown

3. Field properties of database

  1. Unsigned: unsigned integer. Declaration cannot be negative

  2. Zerofill: 0 is used to fill in the insufficient digits, and 0 is used to fill in

  3. Auto Incr: Auto increment, which automatically adds 1 to the previous record. It is used to set the unique primary key. Understood as index, it must be of integer type,

    You can customize the initial value and increment of the primary key (set in advanced of sqlyog table creation)

  4. Not null: when it is set to not null, an error will be reported if the property is not assigned a value.

  5. Default: sets the default value

In each table, there must be 5 attributes (project phase): indicating the meaning of a record

id Primary key
`version` Optimistic lock
is_delete Pseudo deletion
gmt_create Creation time
gmt_update Modification time

4. Create database tables

-- Use single quotes for Strings
-- All statements are followed by commas, and the last one is not
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT 'Student number',
`name` VARCHAR(30) NOT NULL DEFAULT 'anonymous' COMMENT 'full name',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT 'password',
`sex` VARCHAR(2) NOT NULL DEFAULT 'male' COMMENT 'Gender',
`birthday` DATETIME DEFAULT NULL COMMENT 'date of birth',
`address` VARCHAR(100) DEFAULT NULL COMMENT 'address',
`email` VARCHAR(50) DEFAULT NULL COMMENT 'mailbox',

PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

Format:

create table Table name(
Property name type declaration(Type length) Field properties,

primary key (Attribute name)    -- The last one didn't,number
)Table type character encoding

View the definition of the built database show cteat table

3. Database engine type

InnoDB MyISAM
Transaction support support I won't support it
Foreign key support support I won't support it
Row level lock support Not supported (table lock)
Clustered index support I won't support it
Full text index I won't support it support
Tablespace Large, twice as large as MyISAM Small

Full text indexing: you can index content in an article
MyISAM: save space and speed

InnoDB: high security, transaction processing, support multi table and multi-user processing (support row lock and foreign key)

All databases exist in the data directory, which is essentially file storage

Storage differences:

InnoDB: generate *. frm files and ibdata1 files in the upper folder.

MyISAM:

  • *Definition file of. frm table structure
  • *. MYD data file
  • *. MYI index file index

If charset is not set, mysql defaults to character set encoding.

Modification method: 1. Select character set = utf8 when creating a table; 2. Configure in my.ini and add character set server = utf8

Tags: Database

Posted on Mon, 29 Nov 2021 17:00:40 -0500 by Alex C