Mysql-mysql data types, manipulating data tables, recording operations

One, data type

Start time to end time for each date type:

1, year:1970 to 20692, time:-838:59:59 to 838:59:593, date:1000-1-1 to 9999-12-314, datetime:1000-1-1 00:00:00 to 9999-12-31 23:59:595, timestamp storage range: 1970-1-1 00:00:00 to 2037-12-31 23:59:59

Character 1, char (m): fixed length type, such as char (5), if we only assign ABC, then the system will automatically fill in the following two spaces 0--2552, VARCHAR (m): variable length type, l+1 bytes, where l<=m and 0<=m0-- 655353. ENUM (' value1 ', ' value2 ' ... ) enumeration values, which provide several options, such as: gender (' male ', ' female ', ' confidential ') 4, SET (' value1 ', ' value2 ' ...) up to 64 members, any of which can be selected, any combination

Second, the data sheet

Data table 1, data table (or table) is one of the most important components of the database, is the basis of other objects 2, the table is a two-dimensional table, the row is called "Record", the column is called "field" to create data table 1, first open the database (database to exist): Use database name 2, create data table: [IF not EXISTS] table_name (column_name (column name) data_type (column type),....) Example--create TABLE tb1 (username VARCHAR), userage TINYINT unsigned,salary FLOAT (8,2) UNSIGNED,); UNSIGNED unsigned

View datasheet: (View a table of selected databases) SHOW TABLES [from db_name] [like ' pattern ' | where expr]; (can be used to view a table list of other databases) Example: Show tables;//View all tables for the currently selected database show TABLES from test;//View all tables in the TEST database, the current selection database location is unchanged. Select database ();//View the currently selected databases

View data table list show TABLES [from db_name] [like ' pattern ' | WHERE expr]show TABLES; View tables in the current database show TABLES from MySQL; From, the current database does not change show COLUMNS from TB1; View the data table structure in TB1

Insert record (insert row in table) Insert Tbl_name [Field (Colume),...] VALUES (Val,...); When you omit a field, values (the record must be separated by a comma in the order of the table column, the field order, and the inserted content should not be less); If you do not omit the field (partially omitted, omit some of the records automatically empty), values (record content insert content by field, write several fields to insert several content); Record lookup (Cartesian product of multiple-table-check principle): SELECT ECPR (expression),... from Tbl_name;

The asterisk is the filter for the field: SELECT * from TB1; * Filter of fields, all records

NULL and non-null:
NULL, the field value can be empty    
Not NULL, the field value is forbidden to be empty and no assignment will cause an error

AutoNumber Auto_increment is automatically numbered and must be used in conjunction with the primary key 1, AutoNumber: Guaranteed record uniqueness 2, the type must be integer (can be float (5,0), must be 0 after the decimal point), must be combined with the primary key primary key 3, by default, The starting value is 1, the increment for each time is 1CREATE TABLE tb3 (id SMALLINT UNSIGNED auto_increment,username VARCHAR () not NULL); Error, auto increment field must be set as primary key

PRIMARY KEY constraint 1, uniqueness (can be assigned but cannot assign the same value to two records) there is only one primary key 2, non-null for each table, and is inserted in the same way as NOT NULL 3, the AutoNumber (auto_increment) must be used in conjunction with the primary key (PRIMARY key). Primary keys can not be used with automatic numbering

1.primary key A PRIMARY KEY constraint can have only one 2 in a table. Unique KEY Uniqueness Constraint (1) Unique constraint guarantees record uniqueness (2) The field of a unique constraint can be a null value (NULL)  (but there can be only one null) (3) Multiple unique constraints can exist per data table

Default defaults (default constraint) automatically assigns default values when inserting records without explicitly assigning a value to the field

Mysql-mysql data types, manipulating data tables, recording operations