3.4 检查进程，并关闭

[root@localhost 桌面]# ps -aux|grep mysql

Warning: bad syntax, perhaps a bogus '-'? See /usr/share/doc/procps-3.2.8/FAQ

root      2189  0.0  0.1   5124  1396 ?        S    17:24   0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/localhost.localdomain.pid

mysql     2389  0.1  3.1 332068 32420 ?        Sl   17:24   0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/lib/mysql/localhost.localdomain.pid --socket=/var/lib/mysql/mysql.sock

root      3066  0.0  0.0   6056   796 pts/0    S+   17:28   0:00 grep mysql

[root@localhost 桌面]# kill -9 2389

[root@localhost 桌面]# service mysql status

SUCCESS! MySQL running (3112)

清理mysql->取消勾选->应用

rm -rf /var/lib/mysql

4. 数据库DML操作

[stu@localhost ~]$ su

密码：

[root@localhost stu]# mysql -u root -p

Enter password: 123456

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.5.48 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

4.1 查看数据库

mysql> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

| test               |

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

4 rows in set (0.02 sec)

4.2 创建数据库

mysql> use mydb1;

Database changed

mysql> show tables;

Empty set (0.00 sec)

create table student (

id int,

name varchar(20),

chinese double,

english double,

math double);

mysql> create table student(

   -> id int,

   -> name varchar(20),

   -> chinese double,

   -> english double,

   -> math double

   -> );

Query OK, 0 rows affected (0.04 sec)

insert into student(id,name,chinese,english,math)

values(1,'张三',78.8,98,66);

insert into student(id,name,chinese,english,math)

values(2,'李四',88.5,68,96);

4.3 添加表列

mysql> alter table student add age varchar(5);

Query OK, 2 rows affected (0.06 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from student;

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

| id   | name   | chinese | english | math | age  |

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

|    1 | 张三   |    78.8 |      98 |   66 | NULL |

|    2 | 李四   |    88.5 |      68 |   96 | NULL |

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

2 rows in set (0.00 sec)

4.4 修改列类型

把varhcar(5)改成int类型

mysql> alter table student modify age int;

Query OK, 2 rows affected (0.02 sec)

Records: 2  Duplicates: 0  Warnings: 0

4.5 修改列名

mysql> alter table student change age username varchar(20);

Query OK, 2 rows affected (0.23 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from student;

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

| id   | name   | chinese | english | math | username |

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

|    1 | 张三   |    78.8 |      98 |   66 | NULL     |

|    2 | 李四   |    88.5 |      68 |   96 | NULL     |

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

4.6 修改表名

mysql> rename table student to student1;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from student1;

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

| id   | name   | chinese | english | math | username |

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

|    1 | 张三   |    78.8 |      98 |   66 | NULL     |

|    2 | 李四   |    88.5 |      68 |   96 | NULL     |

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

2 rows in set (0.00 sec)

4.7 把student1的表结构和数据保存到新表student

mysql> create table student as select * from student1;

Query OK, 2 rows affected (0.01 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from student;

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

| id   | name   | chinese | english | math | username |

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

|    1 | 张三   |    78.8 |      98 |   66 | NULL     |

|    2 | 李四   |    88.5 |      68 |   96 | NULL     |

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

2 rows in set (0.00 sec)

4.8 表结构student添加id主键

mysql> alter table student add constraint primary key(id);

Query OK, 2 rows affected (0.03 sec)

Records: 2  Duplicates: 0  Warnings: 0

4.9 表student1删除id=1的行

mysql> delete from student1 where id=1;

Query OK, 1 row affected (0.00 sec)

mysql> select * from student1;

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

| id   | name   | chinese | english | math | username |

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

|    2 | 李四   |    88.5 |      68 |   96 | NULL     |

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

1 row in set (0.00 sec)

4.10 表student1删除全部数据

mysql> truncate table student1;

Query OK, 0 rows affected (0.01 sec)

mysql> select * from student1;

Empty set (0.00 sec)

4.11 表student1删除

mysql> drop table student1;

5.  数据库用户操作

[stu@localhost ~]$ su

密码：

[root@localhost stu]# mysql -u root -p

Enter password: 123456

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.5.48 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

5.1 创建用户（@本机用户，%网络用户）

mysql> create user mysql@localhost identified by '123456';

Query OK, 0 rows affected (0.00 sec)

mysql> grant select on *.* to mysql@localhost;

Query OK, 0 rows affected (0.00 sec)

5.2 查看用户权限

mysql> show grants for mysql@localhost;

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

| Grants for mysql@localhost                                                                                    |

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

| GRANT SELECT ON *.* TO 'mysql'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |

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

1 row in set (0.00 sec)

5.3 撤消用户权限

mysql> revoke select on *.* from mysql@localhost;

Query OK, 0 rows affected (0.02 sec)

5.4 修改密码

mysql> update mysql.user set password=password('234567') where user='mysql';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

5.5 打开新终端

[root@localhost mysql]# mysql -u mysql -p

Enter password: 123456

ERROR 1045 (28000): Access denied for user 'mysql'@'localhost' (using password: YES)

[root@localhost mysql]# mysql -u mysql -p

Enter password: 234567

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 5

Server version: 5.5.48 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

5.6 要回去用root账户删除mysql账户

mysql> drop user mysql;

6. 数据库查询操作

6.1 进入数据库

[root@localhost mysql]# mysql -u root -p

mysql> user mydb1;

mysql> select * from student where chinese<80;

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

| id | name   | chinese | english | math | username |

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

|  1 | 张三   |    78.8 |      98 |   66 | NULL     |

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

mysql> select * from student where chinese>80 and chinese<90;

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

| id | name   | chinese | english | math | username |

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

|  2 | 李四   |    88.5 |      68 |   96 | NULL     |

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

1 row in set (0.00 sec)

mysql> select * from student where chinese between 80 and 90;

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

| id | name   | chinese | english | math | username |

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

|  2 | 李四   |    88.5 |      68 |   96 | NULL     |

|  3 | Li     |      90 |      90 |   47 | NULL     |

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

2 rows in set (0.00 sec)

mysql> select * from student order by chinese desc;

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

| id | name   | chinese | english | math | username |

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

|  3 | Li     |      90 |      90 |   47 | NULL     |

|  2 | 李四   |    88.5 |      68 |   96 | NULL     |

|  1 | 张三   |    78.8 |      98 |   66 | NULL     |

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

3 rows in set (0.00 sec)

mysql> select id,name,(chinese+english+math) score from student;

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

| id | name   | score |

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

|  1 | 张三   | 242.8 |

|  2 | 李四   | 252.5 |

|  3 | Li     |   227 |

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

3 rows in set (0.00 sec)

mysql> select * from (select id,name,(chinese+english+math) score from student) vw order by vw.score;

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

| id | name   | score |

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

|  3 | Li     |   227 |

|  1 | 张三   | 242.8 |

|  2 | 李四   | 252.5 |

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

3 rows in set (0.00 sec)

一键三连！

一键三连！