MYSQL--command1-白红宇

MYSQL--command1

阅读量：5051 次

发布时间：2019-06-12

本文共 8802 字，大约阅读时间需要 29 分钟。

1：查询mysql版本号

//select version();

//status;

2:mysql出现下列情况的解决法子

service mysql start

Starting MySQL.................................................

.....................................................The server quit without updating PID file (/var/lib/mysql/localhost.localdomain.pid). [失败]

mysql -uroot -p

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

---------------------------------------

[root@localhost ~]# ps aux |grep mysq*

root 1562 0.0 0.0 108328 48 ? S 18:25 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/localhost.localdomain.pid

mysql 1692 0.0 9.8 1078000 100220 ? Sl 18:25 0:04 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/localhost.localdomain.err --pid-file=/var/lib/mysql/localhost.localdomain.pid

root 3691 0.0 0.0 103248 768 pts/2 D+ 19:48 0:00 grep mysq*

[root@localhost ~]# kill 1562

[root@localhost ~]# kill 1692

[root@localhost ~]# kill 3691

分析MySQL重复启动了

----------------------------------------

3：写了一半又不想执行了语句结尾加上 \c

mysql> select user()

-> \c

4:创建表

mysql> create table pet (

-> name varchar(20),owner varchar(20),

-> species varchar(20),sex char(1),birth Date, death Date

-> );

5：展现创建表的语句

--》show create table pet;

6：将指定的文件插入到表中

--》load data local infile '/export/pratice/pet.xt' into table pet;

7：删除表中全部数据（表结构不变）

--》delete from pet;

-------------------------------------------------------------

+----------+--------+---------+------+------------+------------+

+----------+--------+---------+------+------------+------------+

| Fluffy | Harold | cat | f | 1993-02-04 | NULL |

| Claws | Gwen | cat | m | 1994-03-17 | NULL |

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

| Fang | Benny | dog | m | 1990-08-27 | NULL |

| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |

+----------+--------+---------+------+------------+------------+

-------------------------------------------------------------

8：更新表中名字为Bowser的生日

--》update pet set birth = '1995-07-02' where name = 'Bowser';

9：查询名字为Bowser的记录（Bowser不计较大小写）

mysql> select * from pet where name = 'Bowser';

+--------+-------+---------+------+------------+------------+

+--------+-------+---------+------+------------+------------+

| Bowser | Diane | dog | m | 1995-07-02 | 1995-07-29 |

+--------+-------+---------+------+------------+------------+

10：查找生日在1998年以后的特定查询

mysql> select * from pet where birth >= '1998-1-1';

+--------+-------+---------+------+------------+-------+

+--------+-------+---------+------+------------+-------+

+--------+-------+---------+------+------------+-------+

11：多条件查询

mysql> select * from pet where species = 'dog' and sex ='f';

+-------+--------+---------+------+------------+-------+

+-------+--------+---------+------+------------+-------+

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

+-------+--------+---------+------+------------+-------+

mysql> select * from pet where species = 'snake' or species ='bird';

+----------+-------+---------+------+------------+-------+

+----------+-------+---------+------+------------+-------+

+----------+-------+---------+------+------------+-------+

12：优先执行括号中的逻辑

mysql> select * from pet where (species = 'cat' and sex = 'm')

-> or (species = 'dog' and sex = 'f');

+-------+--------+---------+------+------------+-------+

+-------+--------+---------+------+------------+-------+

| Claws | Gwen | cat | m | 1994-03-17 | NULL |

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

+-------+--------+---------+------+------------+-------+

13：检索特定的列

mysql> select name,birth from pet;

+----------+------------+

| name | birth |

+----------+------------+

| Fluffy | 1993-02-04 |

| Claws | 1994-03-17 |

| Buffy | 1989-05-13 |

| Fang | 1990-08-27 |

| Bowser | 1995-07-02 |

| Chirpy | 1998-09-11 |

| Whistler | 1997-12-09 |

| Slim | 0996-04-29 |

+----------+------------+

14：查询不复的字段--关键词DISTINCT

mysql> select owner from pet;

+--------+

| owner |

+--------+

| Harold |

| Gwen |

| Harold |

| Benny |

| Diane |

| Gwen |

| Benny |

+--------+

mysql> select distinct owner from pet;

+--------+

| owner |

+--------+

| Harold |

| Gwen |

| Benny |

| Diane |

+--------+

15:使用组合条件查询特定的列

mysql> select name,species,birth from pet

-> where species = 'dog' or

-> species = 'cat'

-> ;

+--------+---------+------------+

| name | species | birth |

+--------+---------+------------+

| Fluffy | cat | 1993-02-04 |

| Claws | cat | 1994-03-17 |

| Buffy | dog | 1989-05-13 |

| Fang | dog | 1990-08-27 |

| Bowser | dog | 1995-07-02 |

+--------+---------+------------+

16：根据某个字段进行排序（默认为升序）

mysql> select name,birth from pet order by birth;

+----------+------------+

| name | birth |

+----------+------------+

| Slim | 0996-04-29 |

| Buffy | 1989-05-13 |

| Fang | 1990-08-27 |

| Fluffy | 1993-02-04 |

| Claws | 1994-03-17 |

| Bowser | 1995-07-02 |

| Whistler | 1997-12-09 |

| Chirpy | 1998-09-11 |

+----------+------------+

升降序排列

select name,birth from pet order by birth desc;

select name,birth from pet order by birth asc;

17：日期计算

--查询当前日期

mysql> select curdate() from pet;

--获取当年的年

mysql> select year('1995-07-02') as years from pet;

18：根据出生日期和现在时间算出年龄

mysql>

SELECT name, birth, CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE())

-> AS age FROM pet;

+----------+------------+------------+------+

+----------+------------+------------+------+

| Fluffy | 1993-02-04 | 2019-05-05 | 26 |

| Claws | 1994-03-17 | 2019-05-05 | 25 |

| Buffy | 1989-05-13 | 2019-05-05 | 29 |

| Fang | 1990-08-27 | 2019-05-05 | 28 |

| Bowser | 1995-07-02 | 2019-05-05 | 23 |

| Chirpy | 1998-09-11 | 2019-05-05 | 20 |

| Whistler | 1997-12-09 | 2019-05-05 | 21 |

| Slim | 0996-04-29 | 2019-05-05 | 1023 |

+----------+------------+------------+------+

19：NULL与not NULL

mysql> select name,birth,death,

-> TIMESTAMPDIFF(YEAR,birth,death)

-> as age

-> from pet where death is not null

-> order by age;

+--------+------------+------------+------+

+--------+------------+------------+------+

| Bowser | 1995-07-02 | 1995-07-29 | 0 |

+--------+------------+------------+------+

mysql> select name,birth,death, TIMESTAMPDIFF(YEAR,birth,death) as age from pet where death is null order by age;

+----------+------------+-------+------+

+----------+------------+-------+------+

+----------+------------+-------+------+

20：查看mysql字符集

show variables like 'character%';

mysql> show variables like 'character%';

+--------------------------+----------------------------+

| Variable_name | Value |

+--------------------------+----------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | latin1 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | latin1 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

添加配置

[root@localhost etc]# vim /etc/my.cnf

[mysqld]

character-set-server=utf8

collation-server=utf8_general_ci

sql_mode='NO_ENGINE_SUBSTITUTION'

[mysql]

default-character-set = utf8

[mysql.server]

default-character-set = utf8

[mysqld_safe]

default-character-set = utf8

[client]

default-character-set = utf8

重启mysql

service MySQL restart

mysql> show variables like 'character%';

+--------------------------+----------------------------+

| Variable_name | Value |

+--------------------------+----------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

21：

转载于:https://www.cnblogs.com/xlhlx/p/10816780.html

你可能感兴趣的文章

Ubuntu 编译出现 ISO C++ 2011 不支持的解决办法

查看>>

Linux 常用命令——cat, tac, nl, more, less, head, tail, od

查看>>

VueJS ElementUI el-table 的 formatter 和 scope template 不能同时存在

HDU 1028 Ignatius and the Princess III(母函数)

查看>>

(转)面向对象最核心的机制——动态绑定(多态)

poi操作oracle数据库导出excel文件

查看>>

(转)Intent的基本使用方法总结

查看>>

Windows Phone开发（24）：启动器与选择器之发送短信

查看>>

JS截取字符串常用方法

查看>>

Google非官方的Text To Speech和Speech Recognition的API

查看>>

stdext - A C++ STL Extensions Libary

查看>>

Django 内建中间件组件

查看>>

bootstrap-Table服务端分页，获取到的数据怎么再页面的表格里显示

查看>>