Database data types, constraints, table operations

--sept. 28th, Mon.15:30 PM

first, the data type (1) Integral typeTINYINT 1 bytes//tinyint UNSIGNEDSMALLINT 2 bytesmediumint 3 bytesINT 4 bytesBIGINT 8 bytes  (2) floating-point type float[(M, D)] double[(M, D)]m is the total number of digits, D is the number of digits after the decimal point, and if M and D are omitted, the value is saved according to the hardware allowable limit, and the single-precision floating-point number is accurate to approximately 7 decimal places. Float can be used generally. (3) Date and time typeYear 1 Range: 70-69 (date between 1970-2069)Time 3 838-5959Date 3 date range: 1000.1.1-9999.12.31datetime 8th Period time Reasonable range: 1000.1.1.00:00:00-9999.12.31.23:59:59TIMESTAMP 4 (timestamp) 1970.1.1.00:00:00-2037 ...in the development process, the date time is generally not used, because the cross-time zones are taken into account, but numeric types are adopted.   (4) Character type CHAR (m) m bytes, 0<=m<=255, fixed length type VARCHAR (M) l+1 bytes, l<=m and 0<=m<=65535, variable length typetinytext l+1 Bytes, l<2 8-Time SquareTEXT l+2 Bytes, l<2 16-Time Squaremediumtext l+3 Bytes, l<2 24-Time Squarelongtext l+4 Bytes, l<2 32-Time Square ENUM (' value1 ', ' value2 ', ...) 1 or 2 bytes, depending on the number of enumeration values, up to a maximum of 65,535 values SET (' value1 ', ' value2 ', ...) 1,2,3,4 or 8 bytes, depending on the number of set members (up to 64 members) (collection)  Second, data table operationA data table is one of the most important components of a database and is the basis for other objects. (1) Open the database

Use database_name

(2) Creating a data table CREATE TABLE [IF not EXISTS] table_name (column _name data_type,     ...);such as:> CREATE TABLE T1 (> Username VARCHAR (),> Age TINYINT UNSIGNED,> Salary FLOAT (8, 2) UNSIGNED>); Field name-type-constraint Delete data table: DROP table table_name;(3) Query data table listSHOW TABLES [from db_name] [like ' pattern ' | WHERE Expr]query the entire MySQL table: SHOW TABLES from MySQL; The current database does not change. (4) View data table structureSHOW COLUMNS from tbl_name to table processing(5) insertion and search of recordsLine: RecordInsert record: Insert [into] tbl_name [(Col_name, ...)]    VALUES (Val, ...) You can assign a value to a specified col_name field, or omit it, but you must assign a value to all fields at this point> INSERT tb1 VALUES (' Tom ', ' 9998.78 ');> INSERT tb1 (username, salary) VALUES (' John ', 5000.00) Find records: SELECT expr, ... From tbl_name//expr expressionsuch as: SELECT * from TB1;//* for filtering of fields, not records  Third, Constraints constraints ensure the integrity and consistency of the data. classification: table-level constraints, column-level constraints  This is categorized according to the number of constraint fields: If only one field is constrained, it is called a column-level constraint; If the constraint is for more than one field, it is called a table-level constraint.   Column-level constraints can be declared either when the column is defined, or after the definition. table-level constraints can only be declared after a column definition. Not NULL, default can exist only at the column-level constraint, and the others may exist with both.  type: non-null constraint (NOT NULL)PRIMARY KEY constraint (PRIMARY key)Unique constraint (unique KEY)default constraint (defaults)FOREIGN KEY constraint (FOREIGN key)Check Constraint (1) non-null and null valuesnull field value can be nullNot null field value is forbidden to be empty ( for example, some items are required when filling out a form)> CREATE TABLE tb2 (> Username VARCHAR () not NULL,> Age TINYINT UNSIGNED NULL>);You can view show COLUMNS from TB2;(2) automatic numberingauto_increment automatic numbering must be used in conjunction with the primary key. The uniqueness of the record can be guaranteed. by default, the starting value is 1, and each increment is 1. should be integral type, if floating point type, then the decimal point must be 0such as:> CREATE TABLE tb3 (> ID SMALLINT UNSIGNED auto_increment,> Username VARCHAR (+) not NULL>);ERROR 1075 (42000): Incorrect table definition; there can is only one auto column and it must is defined as a key. (3) PRIMARY KEY constraintPRIMARY Key Primary key is used to guarantee the uniqueness of the record, only one primary key can exist per table and is not NULL automatically.> CREATE TABLE TB4 (> ID SMALLINT UNSIGNED auto_incrementPRIMARY KEY,> Username VARCHAR (+) not NULL>);You can view show COLUMNS from TB4; (4) Unique constraintThe unique KEY uniqueness constraint guarantees the uniqueness of the record, its fields can be empty (null), and multiple unique constraints can exist for each data table. If you create a data table that has both a primary KEY constraint and a unique constraint:> CREATE TABLE tb5 (> ID SMALLINT UNSIGNED auto_increment PRIMARY KEY,> Username VARCHAR () not NULL UNIQUE KEY,> Age TINYINT UNSIGNED>);You can view show COLUMNS from Tb5;(5) Default ConstraintsDefault when you insert a record, it is automatically assigned a value if it is not explicitly assigned to a field. > CREATE TABLE tb6 (> ID SMALLINT UNSIGNED auto_increment PRIMARY KEY,> Username VARCHAR () not NULL UNIQUE KEY,> Sex ENUM (' 1 ', ' 2 ', ' 3 ') DEFAULT ' 3 ' >);     

Database data types, constraints, table operations