百木园-与人分享,
就是让自己快乐。

47.MySQL数据库4

今日内容概要

  • 如何查询表
    • 前期表准备
    • 几个重要关键字的执行顺序
    • where筛选条件
    • group by 分组
    • 分组注意事项
    • having分组之后的筛选条件
    • distinct去重
    • order by排序
    • limit限制展示条数
    • 正则
  • 联表操作理论
    • 前期表准备
    • 表查询
    • 子查询
    • 总结

今日内容详细

如何查询表

  前期表准备

create table emp(
id int
not null unique auto_increment,
name varchar(
20) not null,
sex enum(
\'male\',\'female\') not null default \'male\',
age int(
3) unsigned not null default 28,
hire_date date
not null,
post varchar(
50),
post_comment varchar(
100),
salary double(
15,2),
office int,
depart_id int
);

#插入数据
#
三个部门:教学部,销售部,运营部

insert into
emp(name,sex,age,hire_date,post,salary,office,depart_id) values
(
\'jason\',\'male\',18,\'20170301\',\'张江第一帅形象\',7300.33,401,1),
(
\'tom\',\'male\',78,\'20150302\',\'teacher\',1000000.31,401,1),
(
\'kevin\',\'male\',81,\'20130305\',\'teacher\',8300,401,1),
(
\'tony\',\'male\',73,\'20140701\',\'teacher\',3500,401,1),
(
\'owen\',\'male\',28,\'20121101\',\'teacher\',2100,401,1),
(
\'jack\',\'female\',18,\'20110211\',\'teacher\',9000,401,1),
(
\'jenny\',\'male\',18,\'19000301\',\'teacher\',30000,401,1),
(
\'sank\',\'male\',48,\'20101111\',\'teacher\',10000,401,1),
(
\'哈哈\',\'female\',48,\'20150311\',\'sale\',3000.13,402,2), #以下是销售部门
(\'呵呵\',\'female\',48,\'20101101\',\'sale\',2000.35,402,2),
(
\'西西\',\'female\',38,\'20110312\',\'sale\',1000.37,402,2),
(
\'乐乐\',\'female\',18,\'20160513\',\'sale\',3000.29,402,2),
(
\'啦啦\',\'female\',18,\'20170127\',\'sale\',4000.33,402,2),
(
\'僧龙\',\'male\',28,\'20160311\',\'operation\',10000.13,403,3), #以下是运营部门
(\'程咬金\',\'male\',18,\'19970312\',\'operation\',20000,403,3),
(
\'程咬银\',\'female\',18,\'20130311\',\'operation\',18000,403,3),
(
\'程咬铜\',\'male\',18,\'20150411\',\'operation\',19000,403,3),
(
\'程咬铁\',\'female\',18,\'20140512\',\'operation\',17000,403,3);

mysql> select * from emp;
+----+-----------+--------+-----+------------+-----------------------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------------------+--------------+------------+--------+-----------+
| 1 | jason | male | 18 | 2017-03-01 | 张江第一帅形象 | NULL | 7300.33 | 401 | 1 |
| 2 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 4 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 6 | jack | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 7 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 8 | sank | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 9 | 哈哈 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 10 | 呵呵 | female | 48 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
| 11 | 西西 | female | 38 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 12 | 乐乐 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 13 | 啦啦 | female | 18 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 14 | 僧龙 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 18000.00 | 403 | 3 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 19000.00 | 403 | 3 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
+----+-----------+--------+-----+------------+-----------------------+--------------+------------+--------+-----------+
18 rows in set (0.00 sec)

当表的字段很多的时候,命令窗口不够宽,感觉数据错乱,怎么办???
只需要在select * from emp后面加上 \\G
即:select
* from emp \\G;

个别同学的电脑在插入中文的时候还是会出现乱码或者空白的现象
你可以将字符编码统一设置成gbk

select * from emp \\G;

mysql> select * from emp \\G;
*************************** 1. row ***************************
id:
1
name: jason
sex: male
age:
18
hire_date:
2017-03-01
post: 张江第一帅形象
post_comment: NULL
salary:
7300.33
office:
401
depart_id:
1
*************************** 2. row ***************************
id:
2
name: tom
sex: male
age:
78
hire_date:
2015-03-02
post: teacher
post_comment: NULL
salary:
1000000.31
office:
401
depart_id:
1
*************************** 3. row ***************************
id:
3
name: kevin
sex: male
age:
81
hire_date:
2013-03-05
post: teacher
post_comment: NULL
salary:
8300.00
office:
401
depart_id:
1
*************************** 4. row ***************************
id:
4
name: tony
sex: male
age:
73
hire_date:
2014-07-01
post: teacher
post_comment: NULL
salary:
3500.00
office:
401
depart_id:
1
*************************** 5. row ***************************
id:
5
name: owen
sex: male
age:
28
hire_date:
2012-11-01
post: teacher
post_comment: NULL
salary:
2100.00
office:
401
depart_id:
1
*************************** 6. row ***************************
id:
6
name: jack
sex: female
age:
18
hire_date:
2011-02-11
post: teacher
post_comment: NULL
salary:
9000.00
office:
401
depart_id:
1
*************************** 7. row ***************************
id:
7
name: jenny
sex: male
age:
18
hire_date:
1900-03-01
post: teacher
post_comment: NULL
salary:
30000.00
office:
401
depart_id:
1
*************************** 8. row ***************************
id:
8
name: sank
sex: male
age:
48
hire_date:
2010-11-11
post: teacher
post_comment: NULL
salary:
10000.00
office:
401
depart_id:
1
*************************** 9. row ***************************
id:
9
name: 哈哈
sex: female
age:
48
hire_date:
2015-03-11
post: sale
post_comment: NULL
salary:
3000.13
office:
402
depart_id:
2
*************************** 10. row ***************************
id:
10
name: 呵呵
sex: female
age:
48
hire_date:
2010-11-01
post: sale
post_comment: NULL
salary:
2000.35
office:
402
depart_id:
2
*************************** 11. row ***************************
id:
11
name: 西西
sex: female
age:
38
hire_date:
2011-03-12
post: sale
post_comment: NULL
salary:
1000.37
office:
402
depart_id:
2
*************************** 12. row ***************************
id:
12
name: 乐乐
sex: female
age:
18
hire_date:
2016-05-13
post: sale
post_comment: NULL
salary:
3000.29
office:
402
depart_id:
2
*************************** 13. row ***************************
id:
13
name: 啦啦
sex: female
age:
18
hire_date:
2017-01-27
post: sale
post_comment: NULL
salary:
4000.33
office:
402
depart_id:
2
*************************** 14. row ***************************
id:
14
name: 僧龙
sex: male
age:
28
hire_date:
2016-03-11
post: operation
post_comment: NULL
salary:
10000.13
office:
403
depart_id:
3
*************************** 15. row ***************************
id:
15
name: 程咬金
sex: male
age:
18
hire_date:
1997-03-12
post: operation
post_comment: NULL
salary:
20000.00
office:
403
depart_id:
3
*************************** 16. row ***************************
id:
16
name: 程咬银
sex: female
age:
18
hire_date:
2013-03-11
post: operation
post_comment: NULL
salary:
18000.00
office:
403
depart_id:
3
*************************** 17. row ***************************
id:
17
name: 程咬铜
sex: male
age:
18
hire_date:
2015-04-11
post: operation
post_comment: NULL
salary:
19000.00
office:
403
depart_id:
3
*************************** 18. row ***************************
id:
18
name: 程咬铁
sex: female
age:
18
hire_date:
2014-05-12
post: operation
post_comment: NULL
salary:
17000.00
office:
403
depart_id:
3
18 rows in set (0.00 sec)

来源:https://www.cnblogs.com/MRPython/p/15229131.html
图文来源于网络,如有侵权请联系删除。

未经允许不得转载:百木园 » 47.MySQL数据库4

相关推荐

  • 暂无文章