MySQL/Lesson01

ISO SQL：2003 standard data types

• Character String Types
• National Character String Types
• Binary Large Object String Types
• Numeric Types
• Boolean Types
• Datetime Types
• Interval Types

SQL:2003-string types

• Fixed-width strings:
  • CHARACTER(length)
  • CHAR(length)
• Variable-length strings:
  • CHARACTER VARYING(length)
  • CHAR VARYING(length)
  • VARCHAR(length)
• Character objects:
  • CHARACTER LARGE OBJECT
  • CHAR LARGE OBJECT
  • CLOB

String Data Types in mysql

• CHAR and VARCHAR

BINARY and VARBINARY

• BLOB and TEXT
• ENUM
• SET

CHAR and VARCHAR

• Declare
  • Char(N) 　　     0 to 255
  • Varchar(N)        0 to 65,535
• Difference
  • Stored
  • Retrieved
  • maximum length

sql_mode

• SET sql_mode = ''; --default
• SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
• SET sql_mode = 'STRICT_ALL_TABLES';
• SET sql_mode = 'STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';
• SET sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE';
• SET sql_mode = 'ALLOW_INVALID_DATES';
• SET sql_mode = 'STRICT_ALL_TABLES,ALLOW_INVALID_DATES';

 

查看sql_mode:

①show variables like 'sql_mode';

设置sql_mode:

①set sql_mode = '';

求长度

①select length(col1),length(col2) from table_name;

查看表结构的命令

①desc table_name;

②explain table_name;

③show columns from table_name;

查看表的确切定义

①show create table table_name;

删除某一列

①alter table table_name drop column_name;