- CREATE DATABASE IF NOT EXISTS Testing;
- USE Testing;
- CREATE TABLE WeightCalculation (WeightCalculationID INT NOT NULL, Weight DECIMAL(10,5) NOT NULL);
- INSERT INTO WeightCalculation (WeightCalculationID, Weight) VALUES (1,54.3445);
- INSERT INTO WeightCalculation (WeightCalculationID, Weight) VALUES (2,928.23017);
- SET @a = (SELECT SUM(WEIGHT) FROM WeightCalculation);
- INSERT INTO WeightCalculation (WeightCalculationID, Weight) VALUES (3,@a);
- SELECT * FROM WeightCalculation;
- DROP DATABASE Testing;
mysql 字段操作添删改
- 添 alter table field_test add decimal_test decimal(3,2) after text_test;
- 删 alter table icecream drop column flavor ;
- 改 alter table icecream change taste flavor varchar (10) ;
- 改 alter table field_test modify decimal_test decimal(20,5);
mysql enum的测试学习
ENUM是一个字符串对象,其值来自表创建时在列规定中显式枚举的一列值。
在某些情况下,ENUM值也可以为空字符串('')或NULL:
· 如果你将一个非法值插入ENUM(也就是说,允许的值列之外的字符串),将插入空字符串以作为特殊错误值。该字符串与“普通”空字符串不同,该字符串有数值值0。后面有详细讨论。
· 如果将ENUM列声明为允许NULL,NULL值则为该列的一个有效值,并且 默认值为NULL。如果ENUM列被声明为NOT NULL,其默认值为允许的值列的第1个元素。
每个枚举值有一个索引:
· 来自列规定的允许的值列中的值从1开始编号。
· 空字符串错误值的索引值是0。这说明你可以使用下面的SELECT语句来找出分配了非法ENUM值的行:
· mysql> SELECT * FROM tbl_name WHERE enum_col=0;
· NULL值的索引是NULL。
例如,定义为ENUM的列('one','two','three')可以有下面所示任何值。还显示了每个值的索引:
值 | 索引 |
NULL | NULL |
'' | 0 |
'one' | 1 |
'two' | 2 |
'three' | 3 |
枚举最多可以有65,535个元素。
当创建表时,ENUM成员值的尾部空格将自动被删除。
当检索时,保存在ENUM列的值使用列定义中所使用的大小写来显示。请注意可以为ENUM列分配字符集和 校对规则。对于二进制或大小写敏感的校对规则,当为列分配值时应考虑大小写。
如果在数值上下文中检索一个ENUM值,将返回列值的索引。例如,你可以这样从ENUM列搜索数值值:
mysql> SELECT enum_col+0 FROM tbl_name;
如果将一个数字保存到ENUM列,数字被视为索引,并且保存的值是该索引对应的枚举成员。(但是,这不适合LOAD DATA,它将所有输入视为字符串)。不建议使用类似数字的枚举值来定义一个ENUM列,因为这很容易引起混淆。例如,下面的列含有字符串值'0'、'1'和'2'的枚举成员,但数值索引值为1、2和3:
numbers ENUM('0','1','2')
根据枚举成员在列定义中列出的顺序对ENUM值进行排序。(换句话说,ENUM值根据索引编号进行排序)。例如,对于ENUM('a','b'),'a'排在'b'前面,但对于ENUM('b','a'),'b'排在'a'前面。空字符串排在非空字符串前面,并且NULL值排在所有其它枚举值前面。要想防止意想不到的结果,按字母顺序规定ENUM列。还可以使用GROUP BY CAST(col AS CHAR)或GROUP BY CONCAT(col)来确保按照词汇对列进行排序而不是用索引数字。
如果你想要确定一个ENUM列的所有可能的值,使用SHOW COLUMNS FROM tbl_name LIKE enum_col,并解析输出中第2列的ENUM定义。
- CREATE TABLE employee_person (
- id int unsigned not null primary key,
- address varchar(60),
- phone int,
- email varchar(60),
- birthday DATE,
- sex ENUM('M', 'F'),
- m_status ENUM('Y','N'),
- s_name varchar(40),
- children int
- );#test for enum
- INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name) values (1, '200, Regina Street', 7176666, 'net@hotmail.com', '1971-04-26', 'M', 'Y', 'Ane Regina');
- INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name) values (2, '200, Regina Street', 7176666, 'net@hotmail.com', '1971-04-26', 'F', 'N', 'Ane Regina');
- INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name) values (3, '200, Regina Street', 7176666, 'net@hotmail.com', '1971-04-26', 'X', 'N', 'Ane Regina');#error input ,it will be set null
- select * from employee_person;
- select * from employee_person where sex="M";
- select * from employee_person where sex=0;
- select * from employee_person where sex=1;
- select * from employee_person where sex=2;
我个人认为enum类似一个数组,数组的第0个元素是null或者空,当输入有问题的时候默认就是这个,前提你没有设置default value。
字段类型总表
Type {storage} | Name | Range | Attributes | Default |
---|---|---|---|---|
Numeric {1 byte} | TINYINT[(M)] | -128 TO 127 [0 to 255 if UNSIGNED] | AUTO_INCREMENT UNSIGNED, ZEROFILL, SERIAL DEFAULT VALUE | NULL [0 if NOT NULL] |
Numeric {2 bytes} | SMALLINT[(M)] | -32,768 to 32,767 [0 to 65,535] | AUTO_INCREMENT, UNSIGNED, ZEROFILL, SERIAL DEFAULT VALUE | NULL [0 if NOT NULL] |
Numeric {3 bytes} | MEDIUMINT[(M)] | -8,388,608 to 8,388,607 [0 to 16,777,215] | AUTO_INCREMENT, UNSIGNED, ZEROFILL, SERIAL DEFAULT VALUE | NULL [0 if NOT NULL] |
Numeric {4 bytes} | INT[(M)] | -/+2.147E+9 [0 to 4.294E+9] | AUTO_INCREMENT, UNSIGNED, ZEROFILL, SERIAL DEFAULT VALUE | NULL [0 if NOT NULL] |
Numeric {8 bytes} | BIGINT[(M)] | -/+9.223E+18 [0 to 18.45E+18] | AUTO_INCREMENT, UNSIGNED, ZEROFILL, SERIAL DEFAULT VALUE | NULL [0 if NOT NULL] |
Numeric {4 or 8} | FLOAT(p) | p=0-24 --> "FLOAT" p=25-53 --> "DOUBLE" | UNSIGNED, ZEROFILL | NULL [0 if NOT NULL] |
Numeric {4 bytes} | FLOAT[(M,D)] | Min=+/-1.175E-38 Max=+/-3.403E+38 | UNSIGNED, ZEROFILL | NULL [0 if NOT NULL] |
Numeric {8 bytes} | DOUBLE[(M,D)] | Min=+/-2.225E-308 Max=+/-1.798E+308 | UNSIGNED, ZEROFILL | NULL [0 if NOT NULL] |
Numeric {M+2} | DECIMAL[(M,[D])] Stored as string | Max Range = DOUBLE range Fixed point vs. DOUBLE float | UNSIGNED, ZEROFILL | NULL [0 if NOT NULL] |
Bit {8 bytes} | BIT[(M)] | Binary. Display by [add zero or converting with BIN()]. M=1-64 | Prior to 5.03 TINYINT(1) Synonym | NULL [0 if NOT NULL] |
String {M char's} | CHAR[(M)] | M=0-255 Characters, FIXED. Right padded with spaces. | BINARY, CHARACTER SET | NULL ["" if NOT NULL] |
String {M char's1} | VARCHAR(M) | M=0-65,535 Characters M=0-255 <v5.0.3 | BINARY, CHARACTER SET | NULL ["" if NOT NULL] |
String {#char's1} | TINYTEXT2 | 0-255 Characters | BINARY, CHARACTER SET | NULL ["" if NOT NULL] |
String {#char's1} | TEXT2 | 0-65,535 Char's | BINARY, CHARACTER SET | NULL ["" if NOT NULL] |
String {#char's1} | MEDIUMTEXT2 | 0-16,777,215 Char's | BINARY, CHARACTER SET | NULL ["" if NOT NULL] |
String {#char's1} | LONGTEXT2 | 0-4,294,967,295 Char's | BINARY, CHARACTER SET | NULL ["" if NOT NULL] |
String {M bytes} | BINARY[(M)] | M=0-255 bytes, FIXED. | Global Only (case sensitive) | NULL ["" if NOT NULL] |
String {M bytes} | VARBINARY(M) | 0-65,535 bytes M=0-255 <v5.0.3 | Global Only (case sensitive) | NULL ["" if NOT NULL] |
String {#bytes1} | TINYBLOB | 0-255 bytes | Global Only (case sensitive) | NULL ["" if NOT NULL] |
String {#bytes1} | BLOB | 0-65,535 bytes | Global Only (case sensitive) | NULL ["" if NOT NULL] |
String {#bytes1} | MEDIUMBLOB | 0-16,777,215 bytes | Global Only (case sensitive) | NULL ["" if NOT NULL] |
String {#bytes1} | LONGBLOB | 0-4,294,967,295 bytes | Global Only (case sensitive) | NULL ["" if NOT NULL] |
String {1-2 bytes} | ENUM2 ("A1","A2",...) | Column is exactly 1 of 1-65,535 values | CHARACTER SET | NULL [1st value if NOT NULL] |
String {1-8 bytes} | SET2 ("A1","A2",...) | Column is 0 or more values in list of 1-64 members | CHARACTER SET | NULL ["" if NOT NULL] |
Date & Time {3 bytes} | DATE | "1000-01-01" - "9999-12-31" | Global Only (YYYY-MM-DD) | NULL ["0000-00-00" if NOT NULL] |
Date & Time {8 bytes} | DATETIME | "1000-01-01 00:00:00" - "9999-12-31 23:59:59" | Global Only (YYYY-MM-DD hh:mm:ss) | NULL ["0000-00-00 00:00:00" if NOT NULL] |
Date & Time {3 bytes} | TIME | "-838:59:59" - "838:59:59" | Global Only (hh:mm:ss) | NULL ["00:00:00" if NOT NULL] |
Date & Time {4 bytes} | TIMESTAMP | 19700101000000 - 2037+ | Global Only (YYYYMMDDhhmmss) | Current Date & Time |
Date & Time {1 bytes} | YEAR | 1900 - 2155 | Global Only (YYYY) | NULL ["0000" if NOT NULL] |