Mysql数据库02-MySQL数据库操作

一、SQL语句概述

       SQL语言是（Structured Query Language的缩写，即结构化查询语言），是关系型数据库的标准语言，用于维护管理数据库，如数据查询、数据更新、访问控制、对象管理等功能。

       SQL分类：

       ·DDL：数据定义语言，用于增删改数据库的对象，如库、表、索引等

       ·DML：数据操纵语言，用于对表中的数据进行增删改

       ·DQL：数据查询语言，用于数据查询

       ·DCL：数据控制语言

二、MySQL数据库表的管理操作

注：大部分命令不区分大小写

1、查看数据库结构

       查看数据库列表信息

格式：show databases;

示例：

[root@mysql ~]# netstat -anpt |grep 3306

tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      20605/mysqld       

[root@mysql ~]# mysql -uroot -p

Enter password:123123

       查看数据库中的数据表信息

格式：use 数据库名；

         show tables；

示例：

       显示数据表的结构（字段）

格式：describe [数据库.]表名

示例：

2、DDL语句操作

数据定义语言，用于增删改数据库的对象，如库、表、索引等

create创建新库、创建新表

格式：create database 数据库名;

         create table 表名 (定义字段);

示例：

mysql> create database auth;       //新建一个名为auth的数据库

Query OK, 1 row affected (0.00 sec)

mysql> use auth;       //使用auth这个数据库

Database changed

mysql> create table users (user_name char(20) not null, user_passwd char(30) default '', primary key (user_name));       //在auth库中，创建名为users的表，表内两个字段为（user_name 最多20个字节 不能为空 ，user_passwd 最多30个字节 默认为空，索引关键字 user_name）

Query OK, 0 rows affected (0.10 sec)

drop删除库、删除表

格式：drop table [数据库名.]表名;

         drop database 数据库名;

示例：

mysql> drop table auth.users;       //删除auth库的users表

Query OK, 0 rows affected (0.04 sec)

mysql> drop database auth;       //删除auth数据库

Query OK, 0 rows affected (0.03 sec)

3、DML语句操作

数据操纵语言，用于对表中的数据进行增删改

insert插入新数据

格式：insert into 表名(字段1,字段2,……) values(‘字段1的值’,’字段2的值’……);

示例：

mysql> use auth;

Database changed

mysql> desc users;

2 rows in set (0.00 sec)

mysql> insert into users(user_name,user_passwd) values('zhangsan',password('123123'));

//在users表的（字段user_name，字段user_passwd）中添加（‘zhangsan’，调用函数password（‘123123’））；

Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> insert into users values('lisi',password('123456'));       //字段内容可省略

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from users;

2 rows in set (0.00 sec)

update更改原有数据

格式：update 表名 set 字段名1=值1 [,字段2=值2] where 条件表达式;

示例：

mysql> update auth.users set user_passwd=password('') where user_name='lisi';       //清空lisi的密码

Query OK, 1 row affected (0.04 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from auth.users;

2 rows in set (0.00 sec)

mysql> update mysql.user set password=password('123123') where user='root';       //修改mysql登录root用户密码为123123

Query OK, 3 rows affected (0.00 sec)

Rows matched: 4  Changed: 3  Warnings: 0

mysql> flush privileges;              //刷新权限

Query OK, 0 rows affected (0.00 sec)

delete删除不需要的数据

格式：delete from 表名 where 条件表达式;

示例：

mysql> delete from auth.users where user_name='lisi';       //在auth库中的users表中，删除用户名为lisi的记录

Query OK, 1 row affected (0.04 sec)

mysql> select * from auth.users;

1 row in set (0.00 sec)

mysql> delete from mysql.user where user='';       //在mysql库中的user表中，删除空的用户名的记录

Query OK, 2 rows affected (0.03 sec)

4、DQL语句操作

数据查询语言，用于数据查询

select查询语句

格式：select 字段名1,字段名2,……from 表名;

         select 字段名1,字段名2,……from 表名 where 条件表达式;

示例：

三、案例：建立公司IT运营部工资数据表

       需求描述：为公司建立员工工资数据库imployee_salary，在imployee_salary数据库中，建立IT_salary数据表，以保存IT运营部员工的工资信息，如下表所示：

建立数据库imployee_salary

mysql> show databases;

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

| Database           |

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

| information_schema |

| auth               |

| mysql              |

| performance_schema |

| test               |

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

5 rows in set (0.00 sec)

mysql> create database imployee_salary;

Query OK, 1 row affected (0.00 sec)

mysql> use imployee_salary;

Database changed

建立数据表IT_salary

mysql> create table IT_salary (岗位类别 char(20) not null,姓名 char(20) not null, 年龄 int, 员工ID int not null , 学历 char(6), 年限 int, 薪资 int not null,primary key (员工ID));

//int 数字类型、char 字符串类型、not null 不能为空、char() 指定最多字节个数、primary key() 指定索引字段

将IT运营部的员工工资信息插入到IT_salary

mysql> insert into IT_salary(岗位类别,姓名,年龄,员工ID,学历,年限,薪资) values('网络工程师','孙空武',27,011,'本科',3,4800);

Query OK, 1 row affected (0.01 sec)

在记事本中编辑好如下内容，复制粘贴进数据库：

insert into IT_salary(岗位类别,姓名,年龄,员工ID,学历,年限,薪资) values('Windows工程师','蓝凌',19,012,'中专',2,3500);

insert into IT_salary(岗位类别,姓名,年龄,员工ID,学历,年限,薪资) values('Linux工程师','姜纹',32,013,'本科',8,15000);

insert into IT_salary(岗位类别,姓名,年龄,员工ID,学历,年限,薪资) values('Java软件工程师','关园',38,014,'大专',10,16000);

insert into IT_salary(岗位类别,姓名,年龄,员工ID,学历,年限,薪资) values('硬件驱动工程师','罗中昆',29,015,'大专',9,16500);

查看核对信息

四、用户权限设置 grand

1、设置用户权限（用户不存在是，则新建用户）

mysql> grant all on *.* to 'root'@'192.168.1.108' IDENTIFIED BY '123456';

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT select ON imployee_salary.* TO 'amber'@'localhost' IDENTIFIED BY '123456';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

[root@mysql ~]# mysql -uamber -p123456

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

Your MySQL connection id is 15

Server version: 5.5.22-log Source distribution

……

mysql> delete from imployee_salary.IT_salary where 姓名='关园';

ERROR 1142 (42000): DELETE command denied to user 'amber'@'localhost' for table 'IT_salary'

2、查看用户权限

mysql> show grants;

mysql> show grants for 'amber'@'localhost';

mysql> show grants for 'admin'@'192.168.1.108';

3、撤销用户权限

mysql> revoke select on imployee_salary.* from 'amber'@'localhost';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

在宿主机安装Navicat_for_MySQL_10.1.7_XiaZaiBa.exe

MySQL通配符：

_:任意单个字符

%：任意长度的任意字符

mysql> grant all on *.* to 'amber'@'%' identified by '123123';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

若宿主机连接报错，查看权限设置是否正确，可尝试如下命令：

mysql> GRANT ALL PRIVILEGES ON *.* TO 'amber'@'%' IDENTIFIED BY '123123' WITH GRANT OPTION;

mysql> FLUSH PRIVILEGES;

课外补充，MySQL的其他常用命令

用于显示广泛的服务器状态信息

show status;

显示创建特定数据库或表

help create database；

help create tables；

显示授权用户的安全权限

show grants；

显示服务器错误或警告信息

show errors；

show warnings；

显示当前连接用户

mysql> select user();

显示当前时间

mysql> select now();

显示当前用户及时间

mysql> select CURRENT_USER(),CURRENT_TIMESTAMP;

mysql> select user(),now();

显示当前数据库

mysql> select database();

显示服务器状态

mysql> status;

数据库的备份与恢复

方法1：可直接备份目录 /var/local/mysql/var

方法2：使用专用备份工具 mysqldump

备份操作

mysqldump -u 用户名 -p [密码] [选项] [数据库名] [表名] > /备份路径/备份文件名

常见选项：

--all-databases

--opt

[root@www ~]# mysqldump -u root -p mysql user > mysql-user.sql

Enter password:

[root@www ~]# mysqldump -u root -p --database auth > auth.sql

Enter password:

[root@www ~]# mysqldump -u root -p --opt --all-databases > all-data.sql

Enter password:

恢复操作

mysql -u root -p [数据库名] < /备份路径/备份文件名

[root@www ~]# mysql -u root -p test < mysql-user.sql

Enter password: