Contents
1.MySQL数据库管理命令之查询数据
1.1查询数据
（1）命令语法
select <字段1,字段2,....>from<表名> where <表达式> 其中 select，from，where是不能随便改的，是关键字，支持大小写
（2）查看test表的所有数据
mysql> use john Database changed mysql> mysql> select * from test; ##select * from john.test; +------+------+--------+ | id | age | name | +------+------+--------+ | 1 | NULL | john01 | | 2 | NULL | 卓恩 | | 3 | NULL | kaka | +------+------+--------+ 3 rows in set (0.01 sec) 查询用户主机密码 mysql> select user,host,password from mysql.user; +---------+--------------+-------------------------------------------+ | user | host | password | +---------+--------------+-------------------------------------------+ | root | localhost | *84AAC12F54AB666ECFC2A83C676908C8BBC381B1 | | root | node.mysql01 | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | node.mysql01 | | | test | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | jeffrey | localhost | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 | | john | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +---------+--------------+-------------------------------------------+ 9 rows in set (0.01 sec)
1.1.1指定查询部分数据
查询前两行数据 mysql> select * from test limit 2; +------+------+--------+ | id | age | name | +------+------+--------+ | 1 | NULL | john01 | | 2 | NULL | 卓恩 | +------+------+--------+ 2 rows in set (0.00 sec) mysql> insert into test(id,name) values(4,'john'),(4,'fafa'),(5,'haha'),(6,'dada'); mysql> select * from test limit 2,4; ##偏移量 +------+------+------+ | id | age | name | +------+------+------+ | 3 | NULL | kaka | | 4 | NULL | john | | 4 | NULL | fafa | | 5 | NULL | haha | +------+------+------+ 4 rows in set (0.00 sec)
1.1.2按照条件查询数据
mysql> select * from test where id=3 and name='kaka';#数字加引号可能不使用索引
+------+------+------+
| id | age | name |
+------+------+------+
| 3 | NULL | kaka |
+------+------+------+
1 row in set (0.00 sec)
1.1.3指定范围查询
mysql> select * from test where id>6 and id<8; Empty set (0.00 sec)
1.1.4排序方式查询
正序 mysql> select * from test order by id asc; 反序 mysql> select * from test order by id desc; 反序选取2行 mysql> select * from test order by name desc limit 2;
1.1.5以条件查询导出数据
mysql> select id,name from test where id>1 and id<5 order by id desc INTO OUTFILE'/tmp/sql.txt'; Query OK, 6 rows affected (0.01 sec) mysql> system cat /tmp/sql.txt 4 john 4 fafa 4 john 4 fafa 3 kaka 2 卓恩 mysql>
1.1.6多表连表查询
1.建立几个关联表
要实现多表连表查询，就需要有关联表及数据
学生表：Studet(Sno,Sname,Ssex,Sage,Sdet)
(学号-主键，姓名，性别，年龄，所在系)
课程表：Course(Cno,Cname,Cpno,Ccredit)
(课程号-主键，课程名，选课，学分)
选课表：Sc(Sno,Cno,Grade)
(学号-主键，课程号-主键，成绩)
其中SC表主键参照Student，Course表
2.创建关联表语句
drop table student; 学生表： create table student( Sno int(10) NOT NULL COMMENT '学号', Sname varchar(16) NOT NULL COMMENT '姓名', Ssex char(2) NOT NULL COMMENT '性别', Sage tinyint(2) NOT NULL default '0' COMMENT '学生年龄', Sdept varchar(16) default NULL COMMENT '学生所在系别', PRIMARY KEY (Sno), key index_Sname (Sname) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 插入数据 insert into student values(0001,'宏志','男',30,'计算机网络'); insert into student values(0002,'王硕','男',30,'computer application'); insert into student values(0003,'zhangsan','男',28,'物流管理'); insert into student values(0004,'脉动','男',29,'computer application'); insert into student values(0005,'wanger','女',26,'计算机科学与技术'); insert into student values(0006,'莹莹','女',22,'护士');
课程表： create table course( Cno int(10) NOT NULL COMMENT '课程号', Cname varchar(64) NOT NULL COMMENT '课程名', Ccredit tinyint(2) NOT NULL COMMENT '学分', PRIMARY KEY (Cno) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 插入数据： insert into course values(1001,'linux中高级运维','3'); insert into course values(1002,'linux中高级架构师','5'); insert into course values(1003,'mysql高级Dba','4'); insert into course values(1004,'python运维开发','4'); insert into course values(1005,'java web开发','3');
选课表： create table `SC`( SCid int(12) NOT NULL auto_increment COMMENT '主键', `Cno` int(10) NOT NULL COMMENT '课程号', `Sno` int(10) NOT NULL COMMENT '学号', `Grade` tinyint(2) NOT NULL COMMENT '学生成绩', PRIMARY KEY (SCid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 插入数据 insert into sc(Sno,Cno,Grade) values(0001,1001,4); insert into sc(Sno,Cno,Grade) values(0001,1002,3); insert into sc(Sno,Cno,Grade) values(0001,1003,1); insert into sc(Sno,Cno,Grade) values(0001,1004,6); insert into sc(Sno,Cno,Grade) values(0002,1001,3); insert into sc(Sno,Cno,Grade) values(0002,1002,2); insert into sc(Sno,Cno,Grade) values(0002,1003,2); insert into sc(Sno,Cno,Grade) values(0002,1004,8); insert into sc(Sno,Cno,Grade) values(0003,1001,4); insert into sc(Sno,Cno,Grade) values(0003,1002,4); insert into sc(Sno,Cno,Grade) values(0003,1003,2); insert into sc(Sno,Cno,Grade) values(0003,1004,8); insert into sc(Sno,Cno,Grade) values(0004,1001,1); insert into sc(Sno,Cno,Grade) values(0004,1002,1); insert into sc(Sno,Cno,Grade) values(0004,1003,2); insert into sc(Sno,Cno,Grade) values(0004,1004,3); insert into sc(Sno,Cno,Grade) values(0005,1001,5); insert into sc(Sno,Cno,Grade) values(0005,1002,3); insert into sc(Sno,Cno,Grade) values(0005,1003,2); insert into sc(Sno,Cno,Grade) values(0005,1004,9);
3.统计学号，姓名，选的课程，学分
mysql> select student.Sno,student.Sname,course.Cname,sc.Grade from student,course,sc where student.Sno=sc.Sno and course.Cno=sc.Cno order by Sno; +-----+---------+-------------------------+-------+ | Sno | Sname | Cname | Grade | +-----+---------+-------------------------+-------+ | 1 | 宏志 | linux中高级运维 | 4 | | 1 | 宏志 | linux中高级架构师 | 3 | | 1 | 宏志 | mysql高级Dba | 1 | | 1 | 宏志 | python运维开发 | 6 | | 2 | 王硕 | linux中高级运维 | 3 | | 2 | 王硕 | linux中高级架构师 | 2 | | 2 | 王硕 | mysql高级Dba | 2 | | 2 | 王硕 | python运维开发 | 8 | | 3 | zhangsan| linux中高级运维 | 4 | | 3 | zhangsan| linux中高级架构师 | 4 | | 3 | zhangsan| mysql高级Dba | 2 | | 3 | zhangsan| python运维开发 | 8 | | 4 | 脉动 | linux中高级运维 | 1 | | 4 | 脉动 | linux中高级架构师 | 1 | | 4 | 脉动 | mysql高级Dba | 2 | | 4 | 脉动 | python运维开发 | 3 | | 5 | wanger | linux中高级运维 | 5 | | 5 | wanger | linux中高级架构师 | 3 | | 5 | wanger | mysql高级Dba | 2 | | 5 | wanger | python运维开发 | 9 | +-----+---------+-------------------------+-------+ 20 rows in set (0.01 sec)
1.1.7使用exlain查看select语句的执行计划
用查询语句查看是否使用索引情况
mysql> explain select * from test where name='john'\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 11 Extra: Using where 1 row in set (0.00 sec)
1.1.8使用explain优化SQL语句（select语句）的基本流程
1.用命令抓取慢SQL语句，然后用explain命令查看查询语句是否走的索引查询
1 在数据库命令行里面操作
mysql> show full processlist; +----+------+-----------+--------+---------+------+-------+-----------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+--------+---------+------+-------+-----------------------+ | 13 | root | localhost | john | Query | 0 | NULL | show full processlist | +----+------+-----------+--------+---------+------+-------+-----------------------+ 1 row in set (0.00 sec) 现在没有 当有大量语句执行的时候 会有显示 2 在linux命令行操作 [root@mysql 3306]# mysql -u root -ppassword -S /mysqldata/3306/mysql.sock -e "show full processlist"|grep -vi "sleep" Id User Host db Command Time State Info 15 root localhost NULL Query 0 NULL show full processlist
2 设置配置参数记录慢查询语句
log_query_time = 2 <==查询时间超过2秒，记录到log里 log_queries_not_using_indexs <==没有走索引的语句，记录到log里 log-slow-queries = /data/3306/slow.log
3 对抓取到的慢查询语句用explain命令检查索引执行情况
例如： explain select * from test where name='john'\G;
4 对需要建索引的条件列建立索引
大表不能高峰值建立索引，300万记录
5 切割慢查询日志，去重分析后发给大家
关于mysql的配置文件参数设置（my.cnf） [mysqld] long_query_time = 1 log-slow-queries = /data/3306/slow.log log_queries_not_using_indexs 关于日志切割脚本 mv /data/3306/slow.log /opt/$(date +%F)_slow.log mysqladmin –uroot –ppcwangjixuan –S /data/3306/mysql.sock flush-logs
1.1.9使用profile功能优化MySQL查询
MariaDB [(none)]> show profiles; Empty set (0.00 sec) 查看帮助 MariaDB [(none)]> help show profile 查看profile生效情况 MariaDB [(none)]> SELECT @@profiling; +-------------+ | @@profiling | +-------------+ | 0 | +-------------+ 1 row in set (0.00 sec) 开启profile功能 MariaDB [(none)]> SET profiling = 1; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SELECT @@profiling; +-------------+ | @@profiling | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec) MariaDB [(none)]> show profiles; +----------+------------+--------------------+ | Query_ID | Duration | Query | +----------+------------+--------------------+ | 1 | 0.00011027 | SELECT @@profiling | +----------+------------+--------------------+ 1 row in set (0.00 sec) 测试准备 MariaDB [(none)]> use wordpress Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [wordpress]> MariaDB [wordpress]> desc wp_postmeta; +------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------------+------+-----+---------+----------------+ | meta_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment | | post_id | bigint(20) unsigned | NO | MUL | 0 | | | meta_key | varchar(255) | YES | MUL | NULL | | | meta_value | longtext | YES | | NULL | | +------------+---------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) MariaDB [wordpress]> select SQL_NO_CACHE * from wp_postmeta where post_id=10; +---------+---------+----------------------+------------------+ | meta_id | post_id | meta_key | meta_value | +---------+---------+----------------------+------------------+ | 52 | 10 | description_value | 准备工作： | 53 | 10 | keywords_value | | | 54 | 10 | _edit_last | 1 | | 55 | 10 | _edit_lock | 1528704688:1 | | 56 | 10 | _format_audio_embed | | | 57 | 10 | _format_video_embed | | | 58 | 10 | _format_link_demourl | | | 59 | 10 | _format_link_dowurl | | | 60 | 10 | _format_audio_type | xiamiplayer | | 61 | 10 | views | 169 | | 128 | 10 | specs_zan | 3 | +---------+---------+----------------------+------------------+ 11 rows in set (0.00 sec) MariaDB [wordpress]> show profiles; +----------+------------+---------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+---------------------------------------------------------+ | 1 | 0.00011027 | SELECT @@profiling | | 2 | 0.00005145 | show tables | | 3 | 0.04226747 | show databases | | 4 | 0.00089363 | SELECT DATABASE() | | 5 | 0.00022481 | show databases | | 6 | 0.00014418 | show tables | | 7 | 0.00029921 | show tables | | 8 | 0.00056613 | desc wp_postmeta | | 9 | 0.00038219 | select SQL_NO_CACHE * from wp_postmeta where post_id=10 | +----------+------------+---------------------------------------------------------+ 9 rows in set (0.00 sec) MariaDB [wordpress]> SHOW PROFILE FOR QUERY 9; ##查看id为9的这条命令在数据库中执行的详细过程 +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000079 | ##开始用了多长时间 | checking permissions | 0.000009 | ##检查权限用了多久 | Opening tables | 0.000018 | ##打开表用了多少时间 | After opening tables | 0.000006 | | System lock | 0.000004 | | Table lock | 0.000003 | | After table lock | 0.000006 | | init | 0.000024 | | optimizing | 0.000016 | | statistics | 0.000075 | | preparing | 0.000032 | | executing | 0.000005 | | Sending data | 0.000072 | | end | 0.000005 | | query end | 0.000005 | | closing tables | 0.000007 | | freeing items | 0.000006 | | updating status | 0.000008 | | cleaning up | 0.000003 | +----------------------+----------+ 19 rows in set (0.00 sec) 说明： 此时可以查看谁占用的时间较多，可以逐一优化
Get busy living or get busy dying. 努力活出精彩的人生，否则便如行尸走肉
如无特殊说明，文章均为本站原创，转载请注明出处
- 转载请注明来源：MySQL数据库管理之查询数据
- 本文永久链接地址：https://www.xionghaier.cn/archives/446.html
该文章由 John 发布
