Mysql学习<->

mysql study:登陆mysql doc 命令：mysql -h localhost -u root -p-h:表示服务器名(localhost=127.0.0.1);-u:username(默认的是:root);-p:password;\h:help(帮助);\c:clear(清除输入);exit:退出;整数型：#创建一个intdata数据库：mysql> CREATE TABLE intdata(    -> a TINYINT(5),    -> b SMALLINT(6),    -> c MEDIUMINT(9),    -> d INT(10),    -> e BIGINT(13)    -> );Query OK, 0 rows affected (0.00 sec)mysql> DESC intdata;+-------+--------------+------+-----+---------+-------+| Field | Type         | Null | Key | Default | Extra |+-------+--------------+------+-----+---------+-------+| a     | tinyint(5)   | YES  |     | NULL    |       || b     | smallint(6)  | YES  |     | NULL    |       || c     | mediumint(9) | YES  |     | NULL    |       || d     | int(10)      | YES  |     | NULL    |       || e     | bigint(13)   | YES  |     | NULL    |       |+-------+--------------+------+-----+---------+-------+#给没一列插入一个值：mysql> INSERT INTO intdata VALUES(1,1,1,1,1);Query OK, 1 row affected (0.00 sec)#查询显示结果：mysql> SELECT * FROM intdata;+------+------+------+------+------+| a    | b    | c    | d    | e    |+------+------+------+------+------+|    1 |    1 |    1 |    1 |    1 |+------+------+------+------+------+1 row in set (0.00 sec) line 4#创建一个整数型的数据库intdata1：mysql> CREATE TABLE intdata1(    -> a TINYINT(5) UNSIGNED ZEROFILL,    -> b SMALLINT(8) UNSIGNED ZEROFILL,    -> C MEDIUMINT(9) UNSIGNED ZEROFILL,    -> d INT(10) UNSIGNED ZEROFILL,    -> e BIGINT(20) UNSIGNED ZEROFILL    -> );Query OK, 0 rows affected (0.00 sec)mysql> DESC intdata1;+-------+--------------------------------+------+-----+---------+-------+| Field | Type                           | Null | Key | Default | Extra |+-------+--------------------------------+------+-----+---------+-------+| a     | tinyint(5) unsigned zerofill   | YES  |     | NULL    |       || b     | smallint(8) unsigned zerofill  | YES  |     | NULL    |       || C     | mediumint(9) unsigned zerofill | YES  |     | NULL    |       || d     | int(10) unsigned zerofill      | YES  |     | NULL    |       || e     | bigint(20) unsigned zerofill   | YES  |     | NULL    |       |+-------+--------------------------------+------+-----+---------+-------+5 rows in set (0.01 sec)#插入一行值：mysql> INSERT INTO intdata1 VALUES(1,1,1,1,1);Query OK, 1 row affected (0.00 sec)#查看表中的记录,不足位直接补零。mysql> SELECT * FROM intdata1;+-------+----------+-----------+------------+----------------------+| a     | b        | C         | d          | e                    |+-------+----------+-----------+------------+----------------------+| 00001 | 00000001 | 000000001 | 0000000001 | 00000000000000000001 |+-------+----------+-----------+------------+----------------------+mysql> CREATE TABLE intdata2(    -> a INT(4),    -> b BIGINT(10)    -> );Query OK, 0 rows affected (0.00 sec)mysql> DESC intdata2;+-------+------------+------+-----+---------+-------+| Field | Type       | Null | Key | Default | Extra |+-------+------------+------+-----+---------+-------+| a     | int(4)     | YES  |     | NULL    |       || b     | bigint(10) | YES  |     | NULL    |       |+-------+------------+------+-----+---------+-------+2 rows in set (0.01 sec)mysql> INSERT INTO intdata2 VALUES(111111,22222222222222);Query OK, 1 row affected (0.00 sec)mysql> SELECT * FROM intdata2;+--------+----------------+| a      | b              |+--------+----------------+| 111111 | 22222222222222 |+--------+----------------+1 row in set (0.00 sec)#注意如果插入的数据长度大于设置的长度并且小于默认长度的时候是可以插入的。#----------------------------------------------------------------------------------------------------------浮点型和定点型：数据类型(M,D);eg:FLOAT(6,2)的含义为:数据是FLOAT型,数据的长度为6,小数点后保留2位,1234.56是正确的表示。如果插入值的精度高于实际定义的精度，系统会自动进行四舍五入处理，使值的精度达到要求，不同的是，FLOAT型和DOUBLE型在四舍五入时不会报错，而DECIMAL型会有警告；mysql> CREATE TABLE float1(    -> a FLOAT(6,2),    -> b DOUBLE(6,2),    -> c DECIMAL(6,2)    -> );Query OK, 0 rows affected (0.00 sec)mysql> DESC float1;+-------+--------------+------+-----+---------+-------+| Field | Type         | Null | Key | Default | Extra |+-------+--------------+------+-----+---------+-------+| a     | float(6,2)   | YES  |     | NULL    |       || b     | double(6,2)  | YES  |     | NULL    |       || c     | decimal(6,2) | YES  |     | NULL    |       |+-------+--------------+------+-----+---------+-------+3 rows in set (0.00 sec)mysql> INSERT INTO float1 VALUES (3.141,3.1415,3.1415);Query OK, 1 row affected, 1 warning (0.01 sec)mysql> SELECT * FROM float1;+------+------+------+| a    | b    | c    |+------+------+------+| 3.14 | 3.14 | 3.14 |+------+------+------+1 row in set (0.00 sec)mysql> INSERT INTO float1 VALUES(3.156,3.157,3.178);Query OK, 1 row affected, 1 warning (0.00 sec)mysql> SELECT * FROM float1;+------+------+------+| a    | b    | c    |+------+------+------+| 3.14 | 3.14 | 3.14 || 3.16 | 3.16 | 3.18 |+------+------+------+2 rows in set (0.00 sec)注意：1.如果不指定精度，浮点和定点数有其默认的精度。FLOAT 型和DOUBLE型默认会保存实际精度，、但这与操作系统和硬件的精度有关。DECIMAL型默认整数位为10，小数位为0，即默认为整数。2.在mysql中，定点数以字符串形式存储，因此精度要比浮点高。而且浮点数会出现误差，这事浮点数存在的缺陷，如果对数据的要求比较高，选择定点数DECIMAL比较安全。mysql error:1.ERROR 1064 (42000): You have an error in your SQL syntax(语法,句法); check the manual(手册,指南) thatcorresponds(相当于,符合于) to your MySQL server version for the right syntax to use near '(test)意思是mysql的语法问题,检查mysql语句是否有问题。mysql error:2.No Database Selected:意思是没有选择数据库： create database （database name）use （database name）即可。