MySQL学习一

mysql -uroot -p;
use hd；
create table t(id smallint);
create database hd default character set utf8;
source d:/houdunwang.sql
整形 十进制 十六进制 八进制


浮点数 223232.22323


字符串型 "houdunwang" "" 由0个或多个字符串组成的数据


null 不确定的值


类型 大小 范围（有符号）范围（无符号）用途
tinyint 1字节   （-128~127） （0-1255）小政府整数
smallint 2字节 (-32768-32767)(0,65535)大整型
mediumint 3字节 (-8388608-8388607) (0,16777215) 大整型
int      4字节  (~) 大整型
bigint   8字节   大整形


浮点数据类型
单精度浮点数 float 4字节 
双精度浮点型 double 8字节

mysql> create table stu(id int(10) primary key auto_increment ,sname char(30) not null,sex tinyint(1) default 0 ,qq varchar(255)) default character set utf8 engine myisam;Query OK, 0 rows affected (0.33 sec)mysql> desc stu;+-------+--------------+------+-----+---------+----------------+| Field | Type         | Null | Key | Default | Extra          |+-------+--------------+------+-----+---------+----------------+| id    | int(10)      | NO   | PRI | NULL    | auto_increment || sname | char(30)     | NO   |     | NULL    |                || sex   | tinyint(1)   | YES  |     | 0       |                || qq    | varchar(255) | YES  |     | NULL    |                |+-------+--------------+------+-----+---------+----------------+4 rows in set (0.08 sec)mysql> insert into stu(sname,sex,qq) values("wangwu",0,35443353);Query OK, 1 row affected (0.06 sec)mysql> insert into stu(sname,sex,qq) values("zhangsan",0,3587843353);Query OK, 1 row affected (0.00 sec)mysql> insert into stu(sname,sex,qq) values("zhaosan",0,343353);Query OK, 1 row affected (0.00 sec)mysql> insert into stu(sname,sex,qq) values("lihua",1,344743353);Query OK, 1 row affected (0.00 sec)mysql> insert into stu(sname,sex,qq) values("zhangmei",0,94743353);Query OK, 1 row affected (0.00 sec)mysql> insert into stu(sname,sex,qq) values("yudan",1,84743353);Query OK, 1 row affected (0.00 sec)mysql> select * from stu;+----+----------+------+------------+| id | sname    | sex  | qq         |+----+----------+------+------------+|  1 | lisi     |    1 | 323353     ||  2 | wangwu   |    0 | 35443353   ||  3 | zhangsan |    0 | 3587843353 ||  4 | zhaosan  |    0 | 343353     ||  5 | lihua    |    1 | 344743353  ||  6 | zhangmei |    0 | 94743353   ||  7 | yudan    |    1 | 84743353   |+----+----------+------+------------+7 rows in set (0.00 sec)mysql> select sname,sex from stu;+----------+------+| sname    | sex  |+----------+------+| lisi     |    1 || wangwu   |    0 || zhangsan |    0 || zhaosan  |    0 || lihua    |    1 || zhangmei |    0 || yudan    |    1 |+----------+------+7 rows in set (0.00 sec)mysql> select * from stu where sname="lisi";+----+-------+------+--------+| id | sname | sex  | qq     |+----+-------+------+--------+|  1 | lisi  |    1 | 323353 |+----+-------+------+--------+1 row in set (0.00 sec)mysql> select * from stu where sname like "li%";+----+-------+------+-----------+| id | sname | sex  | qq        |+----+-------+------+-----------+|  1 | lisi  |    1 | 323353    ||  5 | lihua |    1 | 344743353 |+----+-------+------+-----------+2 rows in set (0.00 sec)mysql> select id,sname,sex from stu;+----+----------+------+| id | sname    | sex  |+----+----------+------+|  1 | lisi     |    1 ||  2 | wangwu   |    0 ||  3 | zhangsan |    0 ||  4 | zhaosan  |    0 ||  5 | lihua    |    1 ||  6 | zhangmei |    0 ||  7 | yudan    |    1 |+----+----------+------+7 rows in set (0.00 sec)mysql> select if(sex,"男生","女生"),sname,sex from stu;+-----------------------+----------+------+| if(sex,"男生","女生") | sname    | sex  |+-----------------------+----------+------+| 男生                  | lisi     |    1 || 女生                  | wangwu   |    0 || 女生                  | zhangsan |    0 || 女生                  | zhaosan  |    0 || 男生                  | lihua    |    1 || 女生                  | zhangmei |    0 || 男生                  | yudan    |    1 |+-----------------------+----------+------+7 rows in set (0.08 sec)mysql> select if(sex,"男生","女生") as stusex,sname from stu;+--------+----------+| stusex | sname    |+--------+----------+| 男生   | lisi     || 女生   | wangwu   || 女生   | zhangsan || 女生   | zhaosan  || 男生   | lihua    || 女生   | zhangmei || 男生   | yudan    |+--------+----------+7 rows in set (0.00 sec)mysql> select sname,sex from stu where sname like "li%" and sex=1;+-------+------+| sname | sex  |+-------+------+| lisi  |    1 || lihua |    1 |+-------+------+mysql> select sname,sex from stu where sname like "li%" or sex=0;+----------+------+| sname    | sex  |+----------+------+| lisi     |    1 || wangwu   |    0 || zhangsan |    0 || zhaosan  |    0 || lihua    |    1 || zhangmei |    0 |+----------+------+6 rows in set (0.00 sec)select concat("姓名:",sname,"性别:",if(sex,"男","女"),"QQ:"qq) as stuinfo from stu;

mysql> alter table stu add birday date;Query OK, 7 rows affected (0.26 sec)Records: 7  Duplicates: 0  Warnings: 0mysql> select * from stu;+----+----------+------+------------+--------+| id | sname    | sex  | qq         | birday |+----+----------+------+------------+--------+|  1 | lisi     |    1 | 323353     | NULL   ||  2 | wangwu   |    0 | 35443353   | NULL   ||  3 | zhangsan |    0 | 3587843353 | NULL   ||  4 | zhaosan  |    0 | 343353     | NULL   ||  5 | lihua    |    1 | 344743353  | NULL   ||  6 | zhangmei |    0 | 94743353   | NULL   ||  7 | yudan    |    1 | 84743353   | NULL   |+----+----------+------+------------+--------+7 rows in set (0.03 sec)mysql> update stu set birday="1990/2/23";Query OK, 7 rows affected (0.06 sec)Rows matched: 7  Changed: 7  Warnings: 0mysql> select * from stu;+----+----------+------+------------+------------+| id | sname    | sex  | qq         | birday     |+----+----------+------+------------+------------+|  1 | lisi     |    1 | 323353     | 1990-02-23 ||  2 | wangwu   |    0 | 35443353   | 1990-02-23 ||  3 | zhangsan |    0 | 3587843353 | 1990-02-23 ||  4 | zhaosan  |    0 | 343353     | 1990-02-23 ||  5 | lihua    |    1 | 344743353  | 1990-02-23 ||  6 | zhangmei |    0 | 94743353   | 1990-02-23 ||  7 | yudan    |    1 | 84743353   | 1990-02-23 |+----+----------+------+------------+------------+7 rows in set (0.00 sec)mysql> update stu set birday="1990/2/23" where id=1;Query OK, 0 rows affected (0.05 sec)Rows matched: 1  Changed: 0  Warnings: 0mysql> update stu set birday="1990/2/23" where id=1Query OK, 0 rows affected (0.05 sec)Rows matched: 1  Changed: 0  Warnings: 0mysql> update stu set birday="1995/12/14" where id=Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> update stu set birday="1985/2/14" where id=3Query OK, 1 row affected (0.02 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> update stu set birday="1995/12/4" where id=4Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> update stu set birday="1965/2/21" where id=5Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> update stu set birday="1975/7/21" where id=6Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> update stu set birday="1979/7/1" where id=7;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from stu limit 2;+----+--------+------+----------+------------+| id | sname  | sex  | qq       | birday     |+----+--------+------+----------+------------+|  1 | lisi   |    1 | 323353   | 1990-02-23 ||  2 | wangwu |    0 | 35443353 | 1995-12-14 |+----+--------+------+----------+------------+2 rows in set (0.00 sec)mysql> select * from stu order by id desc;+----+----------+------+------------+------------+| id | sname    | sex  | qq         | birday     |+----+----------+------+------------+------------+|  7 | yudan    |    1 | 84743353   | 1979-07-01 ||  6 | zhangmei |    0 | 94743353   | 1975-07-21 ||  5 | lihua    |    1 | 344743353  | 1965-02-21 ||  4 | zhaosan  |    0 | 343353     | 1995-12-04 ||  3 | zhangsan |    0 | 3587843353 | 1985-02-14 ||  2 | wangwu   |    0 | 35443353   | 1995-12-14 ||  1 | lisi     |    1 | 323353     | 1990-02-23 |+----+----------+------+------------+------------+7 rows in set (0.11 sec)mysql> select * from stu order by id asc;+----+----------+------+------------+------------+| id | sname    | sex  | qq         | birday     |+----+----------+------+------------+------------+|  1 | lisi     |    1 | 323353     | 1990-02-23 ||  2 | wangwu   |    0 | 35443353   | 1995-12-14 ||  3 | zhangsan |    0 | 3587843353 | 1985-02-14 ||  4 | zhaosan  |    0 | 343353     | 1995-12-04 ||  5 | lihua    |    1 | 344743353  | 1965-02-21 ||  6 | zhangmei |    0 | 94743353   | 1975-07-21 ||  7 | yudan    |    1 | 84743353   | 1979-07-01 |+----+----------+------+------------+------------+7 rows in set (0.00 sec)mysql> select * from stu order by birday asc limit 1;+----+-------+------+-----------+------------+| id | sname | sex  | qq        | birday     |+----+-------+------+-----------+------------+|  5 | lihua |    1 | 344743353 | 1965-02-21 |+----+-------+------+-----------+------------+1 row in set (0.00 sec)mysql> select * from stu order by birday desc limit 1;+----+--------+------+----------+------------+| id | sname  | sex  | qq       | birday     |+----+--------+------+----------+------------+|  2 | wangwu |    0 | 35443353 | 1995-12-14 |+----+--------+------+----------+------------+1 row in set (0.06 sec)查找年龄第二大：mysql> select sname,birday from stu order by birday asc;+----------+------------+| sname    | birday     |+----------+------------+| lihua    | 1965-02-21 || zhangmei | 1975-07-21 || yudan    | 1979-07-01 || zhangsan | 1985-02-14 || lisi     | 1990-02-23 || zhaosan  | 1995-12-04 || wangwu   | 1995-12-14 |+----------+------------+7 rows in set (0.00 sec)mysql> select sname,birday from stu order by birday limit 1,1;+----------+------------+| sname    | birday     |+----------+------------+| zhangmei | 1975-07-21 |+----------+------------+1 row in set (0.00 sec)mysql> update stu set birday="1979/07/21" where id=3;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from stu order by birday;+----+----------+------+------------+------------+| id | sname    | sex  | qq         | birday     |+----+----------+------+------------+------------+|  5 | lihua    |    1 | 344743353  | 1965-02-21 ||  6 | zhangmei |    0 | 94743353   | 1975-07-21 ||  7 | yudan    |    1 | 84743353   | 1979-07-01 ||  3 | zhangsan |    0 | 3587843353 | 1979-07-21 ||  1 | lisi     |    1 | 323353     | 1990-02-23 ||  4 | zhaosan  |    0 | 343353     | 1995-12-04 ||  2 | wangwu   |    0 | 35443353   | 1995-12-14 |+----+----------+------+------------+------------+7 rows in set (0.00 sec)mysql> 查找年龄最大的二个人\cmysql> select * from stu where birday <=(select birday from stu order by birday asc limit 1,1);+----+----------+------+-----------+------------+| id | sname    | sex  | qq        | birday     |+----+----------+------+-----------+------------+|  5 | lihua    |    1 | 344743353 | 1965-02-21 ||  6 | zhangmei |    0 | 94743353  | 1975-07-21 |+----+----------+------+-----------+------------+2 rows in set (0.07 sec)mysql> 得到学生那年出生？\cmysql> select distinct year(birday) as "学生出生年份" from stu;+--------------+| 学生出生年份 |+--------------+|         1990 ||         1995 ||         1979 ||         1965 ||         1975 |+--------------+5 rows in set (0.11 sec)