mysql

--Mysql  的数据类型分类
数值型：
tinyint      非常小的数据         有符值： -27 ~ 27-1       无符号值：0 ~ 28-1 
smallint     较小的数据           有符值：  -215 ~ 215-1    无符号值：  0 ~ 216-1 
mediumint    中等大小的数据        有符值：  -223 ~ 223-1     无符号值：  0 ~ 224-1       
int          标准整数             有符值： -231 ~ 231-1     无符号值：0 ~ 232-1       
bigint       较大的整数           有符值： -263 ~263-1       无符号值：0 ~264-1       
float        单精度浮点数         ±1.1754351e -38
double       双精度浮点数         ±2.2250738585072014e -308
decimal      字符串形式的浮点数    decimal(m, d)

字符串类型
char[(M)]    固定长字符串，检索快但费空间， 0 <=  M  <=   255
varchar[(M)] 可变字符串0 <=  M <= 65535
tinytext     微型文本串
text         文本串

日期和时间型数值类型
DATE         YYYY-MM-DD，日期格式             1000-01-01~ 9999-12-31
TIME         Hh:mm:ss ，时间格式              -838:59:59~838:59:59
DATETIME     YY-MM-DD hh:mm:ss               1000-01-01 00:00:00  至 9999-12-31 23:59:59
TIMESTAMP    YYYYMMDDhhmmss格式表示的时间戳    197010101000000 ~2037年的某个时刻
YEAR         YYYY格式的年份                    1901~2155
集合类
ENUM（'','','',''）         最多设置65535个选项
SET('','','','')          最多设置64个选项

NULL值
理解为“没有值”或“未知值”，不要用NULL进行算术运算，结果仍为NULL

建表语句：
create database kj20501q_db character set utf8mb4;
show databases;
use kj20501q_db;
drop database if exists kj20501q_db;
create table sys_student(
	-- 对于数据类型后面所增加的小括号的数字，表示这个整数类型由该数字的位数组成，
	-- 例如 no 最多是由一个 10 位的整数组成
	no int(10) not null comment '学号',
	name varchar(255) not null comment '学生姓名',
	score decimal(10, 1) comment '考试总成绩',
	cellphone varchar(100) unique comment '手机号码',
	birthday date comment '生日',
	createTime timestamp default CURRENT_TIMESTAMP comment '插入时间',
	updateTime timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP comment '修改时间',
	-- 以下表明该 sys_student 表的主键是 no 这个字段
	primary key(no)
)Engine=InnoDB charset=utf8mb4 comment='学生信息表';

 CREATE   TABLE [ IF NOT EXISTS ]    `表名`   (
    
	`字段名1`    列类型 [ 属性 ]  [ 索引 ] [注释] ,
	 `字段名2`   列类型 [ 属性 ]  [ 索引 ] [注释] , 
	… …    
         `字段名n`   列类型 [ 属性 ]  [ 索引 ] [注释] 

)  [  表类型 ] [ 表字符集 ] [注释] ;

修改表
 ALTER TABLE 旧表名  RENAME AS  新表名
添加字段
 ALTER TABLE 表名   ADD 字段名   列类型 [ 属性 ]   
添加字段
 ALTER TABLE 表名   MODIFY 字段名   列类型 [ 属性 ]  
添加字段
 ALTER TABLE 表名   CHANGE 旧字段名  新字段名   列类型 [ 属性 ]
删除字段
 ALTER TABLE 表名   DROP  字段名


 

ACCESSIBLE (R),ACCOUNT[a],ACTION
ADD (R),AFTER,AGAINST
AGGREGATE,ALGORITHM,ALL (R)
ALTER (R),ALWAYS[b],ANALYSE
ANALYZE (R),AND (R),ANY
AS (R),ASC (R),ASCII
ASENSITIVE (R),AT,AUTOEXTEND_SIZE
AUTO_INCREMENT,AVG,AVG_ROW_LENGTH
BACKUP,BEFORE (R),BEGIN
BETWEEN (R),BIGINT (R),BINARY (R)
BINLOG,BIT,BLOB (R)
BLOCK,BOOL,BOOLEAN
BOTH (R),BTREE,BY (R)
BYTE,CACHE,CALL (R)
CASCADE (R),CASCADED,CASE (R)
CATALOG_NAME,CHAIN,CHANGE (R)
CHANGED,CHANNEL[c],CHAR (R)
CHARACTER (R),CHARSET,CHECK (R)
CHECKSUM,CIPHER,CLASS_ORIGIN
CLIENT,CLOSE,COALESCE
CODE,COLLATE (R),COLLATION
COLUMN (R),COLUMNS,COLUMN_FORMAT
COLUMN_NAME,COMMENT,COMMIT
COMMITTED,COMPACT,COMPLETION
COMPRESSED,COMPRESSION[d],CONCURRENT
CONDITION (R),CONNECTION,CONSISTENT
CONSTRAINT (R),CONSTRAINT_CATALOG,CONSTRAINT_NAME
CONSTRAINT_SCHEMA,CONTAINS,CONTEXT
CONTINUE (R),CONVERT (R),CPU
CREATE (R),CROSS (R),CUBE
CURRENT,CURRENT_DATE (R),CURRENT_TIME (R)
CURRENT_TIMESTAMP (R),CURRENT_USER (R),CURSOR (R)
CURSOR_NAME,DATA,DATABASE (R)
DATABASES (R),DATAFILE,DATE
DATETIME,DAY,DAY_HOUR (R)
DAY_MICROSECOND (R),DAY_MINUTE (R),DAY_SECOND (R)
DEALLOCATE,DEC (R),DECIMAL (R)
DECLARE (R),DEFAULT (R),DEFAULT_AUTH
DEFINER,DELAYED (R),DELAY_KEY_WRITE
DELETE (R),DESC (R),DESCRIBE (R)
DES_KEY_FILE,DETERMINISTIC (R),DIAGNOSTICS
DIRECTORY,DISABLE,DISCARD
DISK,DISTINCT (R),DISTINCTROW (R)
DIV (R),DO,DOUBLE (R)
DROP (R),DUAL (R),DUMPFILE
DUPLICATE,DYNAMIC,EACH (R)
ELSE (R),ELSEIF (R),ENABLE
ENCLOSED (R),ENCRYPTION[e],END
ENDS,ENGINE,ENGINES
ENUM,ERROR,ERRORS
ESCAPE,ESCAPED (R),EVENT
EVENTS,EVERY,EXCHANGE
EXECUTE,EXISTS (R),EXIT (R)
EXPANSION,EXPIRE,EXPLAIN (R)
EXPORT,EXTENDED,EXTENT_SIZE
FALSE (R),FAST,FAULTS
FETCH (R),FIELDS,FILE
FILE_BLOCK_SIZE[f],FILTER[g],FIRST
FIXED,FLOAT (R),FLOAT4 (R)
FLOAT8 (R),FLUSH,FOLLOWS[h]
FOR (R),FORCE (R),FOREIGN (R)
FORMAT,FOUND,FROM (R)
FULL,FULLTEXT (R),FUNCTION
GENERAL,GENERATED[i] (R),GEOMETRY
GEOMETRYCOLLECTION,GET (R),GET_FORMAT
GLOBAL,GRANT (R),GRANTS
GROUP (R),GROUP_REPLICATION[j],HANDLER
HASH,HAVING (R),HELP
HIGH_PRIORITY (R),HOST,HOSTS
HOUR,HOUR_MICROSECOND (R),HOUR_MINUTE (R)
HOUR_SECOND (R),IDENTIFIED,IF (R)
IGNORE (R),IGNORE_SERVER_IDS,IMPORT
IN (R),INDEX (R),INDEXES
INFILE (R),INITIAL_SIZE,INNER (R)
INOUT (R),INSENSITIVE (R),INSERT (R)
INSERT_METHOD,INSTALL,INSTANCE[k]
INT (R),INT1 (R),INT2 (R)
INT3 (R),INT4 (R),INT8 (R)
INTEGER (R),INTERVAL (R),INTO (R)
INVOKER,IO,IO_AFTER_GTIDS (R)
IO_BEFORE_GTIDS (R),IO_THREAD,IPC
IS (R),ISOLATION,ISSUER
ITERATE (R),JOIN (R),JSON[l]
KEY (R),KEYS (R),KEY_BLOCK_SIZE
KILL (R),LANGUAGE,LAST
LEADING (R),LEAVE (R),LEAVES
LEFT (R),LESS,LEVEL
LIKE (R),LIMIT (R),LINEAR (R)
LINES (R),LINESTRING,LIST
LOAD (R),LOCAL,LOCALTIME (R)
LOCALTIMESTAMP (R),LOCK (R),LOCKS
LOGFILE,LOGS,LONG (R)
LONGBLOB (R),LONGTEXT (R),LOOP (R)
LOW_PRIORITY (R),MASTER,MASTER_AUTO_POSITION
MASTER_BIND (R),MASTER_CONNECT_RETRY,MASTER_DELAY
MASTER_HEARTBEAT_PERIOD,MASTER_HOST,MASTER_LOG_FILE
MASTER_LOG_POS,MASTER_PASSWORD,MASTER_PORT
MASTER_RETRY_COUNT,MASTER_SERVER_ID,MASTER_SSL
MASTER_SSL_CA,MASTER_SSL_CAPATH,MASTER_SSL_CERT
MASTER_SSL_CIPHER,MASTER_SSL_CRL,MASTER_SSL_CRLPATH
MASTER_SSL_KEY,MASTER_SSL_VERIFY_SERVER_CERT (R),MASTER_TLS_VERSION[m]
MASTER_USER,MATCH (R),MAXVALUE (R)
MAX_CONNECTIONS_PER_HOUR,MAX_QUERIES_PER_HOUR,MAX_ROWS
MAX_SIZE,MAX_STATEMENT_TIME[n],MAX_UPDATES_PER_HOUR
MAX_USER_CONNECTIONS,MEDIUM,MEDIUMBLOB (R)
MEDIUMINT (R),MEDIUMTEXT (R),MEMORY
MERGE,MESSAGE_TEXT,MICROSECOND
MIDDLEINT (R),MIGRATE,MINUTE
MINUTE_MICROSECOND (R),MINUTE_SECOND (R),MIN_ROWS
MOD (R),MODE,MODIFIES (R)
MODIFY,MONTH,MULTILINESTRING
MULTIPOINT,MULTIPOLYGON,MUTEX
MYSQL_ERRNO,NAME,NAMES
NATIONAL,NATURAL (R),NCHAR
NDB,NDBCLUSTER,NEVER[o]
NEW,NEXT,NO
NODEGROUP,NONBLOCKING[p],NONE
NOT (R),NO_WAIT,NO_WRITE_TO_BINLOG (R)
NULL (R),NUMBER,NUMERIC (R)
NVARCHAR,OFFSET,OLD_PASSWORD[q]
ON (R),ONE,ONLY
OPEN,OPTIMIZE (R),OPTIMIZER_COSTS[r] (R)
OPTION (R),OPTIONALLY (R),OPTIONS
OR (R),ORDER (R),OUT (R)
OUTER (R),OUTFILE (R),OWNER
PACK_KEYS,PAGE,PARSER
PARSE_GCOL_EXPR[s],PARTIAL,PARTITION (R)
PARTITIONING,PARTITIONS,PASSWORD
PHASE,PLUGIN,PLUGINS
PLUGIN_DIR,POINT,POLYGON
PORT,PRECEDES[t],PRECISION (R)
PREPARE,PRESERVE,PREV
PRIMARY (R),PRIVILEGES,PROCEDURE (R)
PROCESSLIST,PROFILE,PROFILES
PROXY,PURGE (R),QUARTER
QUERY,QUICK,RANGE (R)
READ (R),READS (R),READ_ONLY
READ_WRITE (R),REAL (R),REBUILD
RECOVER,REDOFILE,REDO_BUFFER_SIZE
REDUNDANT,REFERENCES (R),REGEXP (R)
RELAY,RELAYLOG,RELAY_LOG_FILE
RELAY_LOG_POS,RELAY_THREAD,RELEASE (R)
RELOAD,REMOVE,RENAME (R)
REORGANIZE,REPAIR,REPEAT (R)
REPEATABLE,REPLACE (R),REPLICATE_DO_DB[u]
REPLICATE_DO_TABLE[v],REPLICATE_IGNORE_DB[w],REPLICATE_IGNORE_TABLE[x]
REPLICATE_REWRITE_DB[y],REPLICATE_WILD_DO_TABLE[z],REPLICATE_WILD_IGNORE_TABLE[aa]
REPLICATION,REQUIRE (R),RESET
RESIGNAL (R),RESTORE,RESTRICT (R)
RESUME,RETURN (R),RETURNED_SQLSTATE
RETURNS,REVERSE,REVOKE (R)
RIGHT (R),RLIKE (R),ROLLBACK
ROLLUP,ROTATE[ab],ROUTINE
ROW,ROWS,ROW_COUNT
ROW_FORMAT,RTREE,SAVEPOINT
SCHEDULE,SCHEMA (R),SCHEMAS (R)
SCHEMA_NAME,SECOND,SECOND_MICROSECOND (R)
SECURITY,SELECT (R),SENSITIVE (R)
SEPARATOR (R),SERIAL,SERIALIZABLE
SERVER,SESSION,SET (R)
SHARE,SHOW (R),SHUTDOWN
SIGNAL (R),SIGNED,SIMPLE
SLAVE,SLOW,SMALLINT (R)
SNAPSHOT,SOCKET,SOME
SONAME,SOUNDS,SOURCE
SPATIAL (R),SPECIFIC (R),SQL (R)
SQLEXCEPTION (R),SQLSTATE (R),SQLWARNING (R)
SQL_AFTER_GTIDS,SQL_AFTER_MTS_GAPS,SQL_BEFORE_GTIDS
SQL_BIG_RESULT (R),SQL_BUFFER_RESULT,SQL_CACHE
SQL_CALC_FOUND_ROWS (R),SQL_NO_CACHE,SQL_SMALL_RESULT (R)
SQL_THREAD,SQL_TSI_DAY,SQL_TSI_HOUR
SQL_TSI_MINUTE,SQL_TSI_MONTH,SQL_TSI_QUARTER
SQL_TSI_SECOND,SQL_TSI_WEEK,SQL_TSI_YEAR
SSL (R),STACKED,START
STARTING (R),STARTS,STATS_AUTO_RECALC
STATS_PERSISTENT,STATS_SAMPLE_PAGES,STATUS
STOP,STORAGE,STORED[ac] (R)
STRAIGHT_JOIN (R),STRING,SUBCLASS_ORIGIN
SUBJECT,SUBPARTITION,SUBPARTITIONS
SUPER,SUSPEND,SWAPS
SWITCHES,TABLE (R),TABLES
TABLESPACE,TABLE_CHECKSUM,TABLE_NAME
TEMPORARY,TEMPTABLE,TERMINATED (R)
TEXT,THAN,THEN (R)
TIME,TIMESTAMP,TIMESTAMPADD
TIMESTAMPDIFF,TINYBLOB (R),TINYINT (R)
TINYTEXT (R),TO (R),TRAILING (R)
TRANSACTION,TRIGGER (R),TRIGGERS
TRUE (R),TRUNCATE,TYPE
TYPES,UNCOMMITTED,UNDEFINED
UNDO (R),UNDOFILE,UNDO_BUFFER_SIZE
UNICODE,UNINSTALL,UNION (R)
UNIQUE (R),UNKNOWN,UNLOCK (R)
UNSIGNED (R),UNTIL,UPDATE (R)
UPGRADE,USAGE (R),USE (R)
USER,USER_RESOURCES,USE_FRM
USING (R),UTC_DATE (R),UTC_TIME (R)
UTC_TIMESTAMP (R),VALIDATION[ad],VALUE
VALUES (R),VARBINARY (R),VARCHAR (R)
VARCHARACTER (R),VARIABLES,VARYING (R)
VIEW,VIRTUAL[ae] (R),WAIT
WARNINGS,WEEK,WEIGHT_STRING
WHEN (R),WHERE (R),WHILE (R)
WITH (R),WITHOUT[af],WORK
WRAPPER,WRITE (R),X509
XA,XID[ag],XML
XOR (R),YEAR,YEAR_MONTH (R)
ZEROFILL (R),,