百木园-与人分享，
就是让自己快乐。
当前位置：百木园 > 野生技术 > 正文

47.MySQL数据库4

2022-02-22 分类：野生技术 阅读(172) 评论(0)

今日内容概要

  • 如何查询表
    • 前期表准备
    • 几个重要关键字的执行顺序
    • 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
标签：

相关推荐

  • 暂无文章

热门文章