MYSQL--command1

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;
-------------------------------------------------------------
+----------+--------+---------+------+------------+------------+
| name     | owner | species | sex | birth      | death      |
+----------+--------+---------+------+------------+------------+
| 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 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny | snake   | m    | 0996-04-29 | NULL       |
+----------+--------+---------+------+------------+------------+
-------------------------------------------------------------

8：更新表中名字为Bowser的生日
--》update pet set birth = '1995-07-02' where name = 'Bowser';

9：查询名字为Bowser的记录（Bowser不计较大小写）
mysql> select * from pet where name = 'Bowser';
+--------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1995-07-02 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+

12：优先执行括号中的逻辑
mysql> select * from pet where (species = 'cat' and sex = 'm')
    -> or (species = 'dog' and sex = 'f');
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth      | death |
+-------+--------+---------+------+------------+-------+
| 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 |
+----------+------------+

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;
+----------+------------+------------+------+
| name     | birth      | CURDATE() | age |
+----------+------------+------------+------+
| 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;
+--------+------------+------------+------+
| name   | birth      | death      | age |
+--------+------------+------------+------+
| Bowser | 1995-07-02 | 1995-07-29 |    0 |
+--------+------------+------------+------+

添加配置
[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

21：