centos 7.6 —— MySQL数据库基本操作

SQL语句概述

SQL语言

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

SQL分类

• DDL：(Definition)数据定义语言
• DML：数据操纵语言
• DQL（data query language）：数据查询语言
• DCL：数据控制语言

1.登录MySQL

– 进入数据库的方法一

mysql -uroot -pmysql # mysql 数据库密码(显示)

– 进入数据库的方法二

mysql -uroot -p # 隐藏密码输入

2.数据库的基本操作

– 显示数据库版本(记得加;

select version();

– 显示当前的时间

select now();

– 查看所有数据库

show databases;

– 创建数据库

create database 数据库名 charset=utf8;

– 创建淘宝数据库

create database taobao;

– 创建淘宝数据库并指定编码

create database taobao charset=utf8;

– 查看创建数据库的语句

show create database school

3.使用数据库taobao数据库

– 使用数据库

use school;

– 显示数据库中所有的表

show tables;

–删除数据库

drop database school;

4.数据表的基本操作

• auto_increment :自动增长
• not null :表示不为空
• primary key :表示主键
• default :默认值

查看当前的数据库中所有的表

– show tables;

– 创建students数据表

create table students(
id int unsigned not null auto_increment primary key,
name varchar(50) not null default ‘张三’,
age tinyint unsigned not null default 18,
high decimal(5,2) not null,
gender enum(‘男’, ‘女’, ‘保密’)default ‘保密’,
cls_id int unsigned not null
);

– 插入一条数据到students表中

insert into students values(0, ‘mike’, 18, 145,‘保密’,2)

– 查询students表中的所有的数据

select * from students;

– 查看创建表的语句

show create table students;

– 删除表

drop table students;

– 查看表的字段

desc students;

– 添加表的字段

alter table students add birth datetime;

– 修改字段:不改变字段名字

alter table students modify birth date;

– 修改字段:不重命名版

alter table students change birth birthday date default ‘2020-01-01’;

– 删除字段

alter table students drop cls_id;

– 插入数据 insert into 表明 value(…)

– 主键可以用 0 null default来占位

insert into students values(null, ‘lily’, 22, 168, 2, ‘1990-01-01’);

– 部分插入

insert into students(high) values(172);

– 多行插入

insert into students(name, high) values(‘李四’, 178),(‘老王’, 1.44);

– 多行插入全部数据

insert into students values(null, ‘lily’, 23, 173, 2, ‘1990-01-01’), (null, ‘xiao’, 22, 189, 2, ‘1990-02-03’);

– 修改表
– 修改全部年龄

update students set age= 30;

– 修改指定id的年龄

update students set age=28 where id=1;

–查询表的内容

select * from students;

– 定条件查询

select * from students where id=2;
select * from students where id>=1 and id<=3;

–查询指定的列

select id, name from students where id>=1 and id<=3;
select name, id from students where id>=1 and id<=3;

– 可以用as来为列表指定别名(显示出来的名字就是指定的名字)

select name as 姓名, id as 学号 from students where id>=1 and id<=3;

– 物理删除 (删除表里的内容)

delete from student where id=6;

–逻辑删除(用新的字段作为条件限制显示信息)

alter table students add is_delete bit default 0;
– 把id=1的is_delete改为1

update students set is_delete=1 where id=1;

– 查询然后条件限制为is_delete=0 就可以隐藏数据

select * from students where is_delete=0;

5.数据表的查询操作

– 查询所有字段

select * from students;

– 查询指定字段

select name, age from students;

– 给字段起别名(用别名显示)

select name as 姓名, age as 年龄 from students;

– 从指定的表中寻找指定的字段

select students.name, students.age from students;

– 用as起别名再用别名调用字段

select s.name, s.age from students as s;

– 利用distinct字段消除重复行

select distinct gender from students;

– 条件查询(比较运算符)

select * from students where age>19;
select * from students where age<19;
select * from students where age!=18;

– 条件查询(逻辑运算符)

select * from students where age>=17 and age<=27;
select * from students where age>=13 or high>=159;
select * from students where not(age<=17 and gender=2);

– 模糊查询

– 查询以"李"开头的所有名字

select * from students where name like “李%”;

– 查询以"王"字结尾的所有名字

select * from students where name like “%王”;

– 查询有"三"的所有名字

select * from students where name like “%三%”;

– 查询有两个字的名字

select * from students where name like “__”;

– 查询有三个字的名字

select * from students where name like “___”;

– 查询至少有两个的名字

select * from students where name like “%__%”;

– 空判断is null

select * from students where high is null;

6.清空表

清空表

只是清空表中信息，但是表还是存在。drop是删除表。

• delete from tablename
• truncate table tablename

7.新建临时表

临时建立的表，用于保存一些临时数据，不会长期存在

连接断开，临时表被删除

mysql>  CREATE TEMPORARY TABLE `mytmp` (
       `id` int(10) NOT NULL AUTO_INCREMENT,
       `NAME` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
       `level` int(10) NOT NULL,
       PRIMARY KEY (id)
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec) mysql> insert into mytmp(name,level) values('aa',10); //插入数据 Query OK, 1 row affected (0.01 sec) 
mysql>select * from mytmp;
 +----+------+-------+
 | id | NAME | level |
 +----+------+-------+ 
 | 1 | aa | 10 | 
 +----+------+-------+ 
 1 row in set (0.00 sec) 
 mysql>quit //退出当前连接
 Bye 
 mysql>select * from mytmp;//重新连接 MySQL 之后查看临时表状态 
 ERROR 1146 (42S02): Table 'dzm.mytmp' doesn't exist

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22

临时表创建成功之后，使用 SHOW TABLES 命令是看不到创建的临时表的，临时表会 在连接退出后被销毁。如果在退出连接之前，也可以手动直接删除，使用 DROP TABLE 语 句，具体操作如下所示。

    

mysql>drop table mytmp;
Query OK, 0 rows affected (0.00 sec)

• 1
• 2
• 3
• 4

7.克隆表

筛选身高大于174的数据存放到新表tmp 中

mysql> create table tmp as select* from students where high>174;

克隆表方法

AS 这里是作为连接语句的操作符，更多的是被用来设置别名

方法一：like (只能复制表的结构属性，数据无法复制)

mysql>create table test like students;  //like 方法

mysql> show create table test\G   like方法复制表结构，不复制数
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE "test" (
  "id" int(3) NOT NULL DEFAULT '0',
  "birthday" date DEFAULT NULL,
  "name" varchar(50) NOT NULL,
  "gender" enum('男','女','保密') DEFAULT '保密',
  "high" decimal(5,2) NOT NULL,
  "age" tinyint(3) unsigned NOT NULL DEFAULT '18',
  "is_delete" bit(1) DEFAULT b'0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into test select* from tmp;   //将tmp表的数据写入test表中
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select* from test;  据
+----+------------+--------+--------+--------+-----+-----------+
| id | birthday   | name   | gender | high   | age | is_delete |
+----+------------+--------+--------+--------+-----+


    
-----------+
|  1 | 2020-01-03 | 张三   | 男     | 175.00 |  34 |          |
|  2 | NULL       | 李四   | 保密   | 178.00 |  18 |           |
+----+------------+--------+--------+--------+-----+-----------+
2 rows in set (0.00 sec)

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29

方法一：通过创建表的方式克隆表

mysql>show create table mytmp\G *************************** 1. row *************************** 
Table: mytmp 
Create Table: CREATE TABLE `mytmp` (   //不加单引号也可以实现
`id` int(10) NOT NULL AUTO_INCREMENT, 
`NAME` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, 
`level` int(10) NOT NULL, 
PRIMARY KEY (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) 

mysql>CREATE TABLE `test` (//改名后创建新表 
->`id` int(10) NOT NULL AUTO_INCREMENT, 
->`NAME` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, 
->`level` int(10) NOT NULL, 
->PRIMARY KEY (id) 
->) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
Query OK, 0 rows affected (0.01


    
 sec)

mysql> insert into test select * from mytmp;//导入原表数据 
Query OK, 1 rows affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from mytmp; 
+----+------+-------+ 
| id | NAME | level | 
+----+------+-------+ 
| 1 | aa | 10 | 
+----+------+-------+ 
3 rows in set (0.01 sec)

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29

8.用户权限设置

1．授予权限

GRANT 语句：专门用来设置数据库用户的访问权限。当指定的用户名不存在时， GRANT 语句将会创建新的用户；当指定的用户名存在时，GRANT 语句用于修改用户信息。 语句格式如下所示。

GRANT 权限列表 ON 数据库名.表名 TO 用户名@来源地址 [ IDENTIFIED BY ‘密码’ ]

• 权限列表：用于列出授权使用的各种数据库操作，以逗号进行分隔，如“select，insert，
  update”。使用“all”表示所有权限，可授权执行任何操作。
• 数据库名.表名：用于指定授权操作的数据库和表的名称，其中可以使用通配符“ ”。例 如，使用“auth. ”表示授权操作的对象为 auth数据库中的所有表。
• 用户名@来源地址：用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。来源地址可以是域名、IP 地址，还可以使用“%”通配符，表示某个区域或网段 内的所有地址，如“%.bdqn.com”“192.168.1.%”等。
• IDENTIFIED BY：用于设置用户连接数据库时所使用的密码字符串。在新建用户时， 若省略“IDENTIFIED BY”部分，则用户的密码将为空。 执行以下操作可以添加一个名为“xiaoqi”的数据库用户，并允许其从本机访问，对 auth 数据库中的所有表具有查询权限，验证密码为“123456”。使用 GRANT 语句授权的用户 记录，会保存到 mysql 库的 user、db、host、tables_priv 等相关表中，无须刷新即可生效。

mysql>grant select on students.* to 'xiaoqi'@'localhost' identified by '123';

 Query OK, 0 rows affected (0.00 sec)
 

• 1
• 2
• 3
• 4
• 5

[root@www ~]# mysql -u xiaoqi -p …… //省略部分内容
mysql>select * from auth.users; //验证授权的访问操作
±----------±------------------------------------------+
| user_name| user_passwd |
±----------±------------------------------------------+
| zhangsan | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9|
±----------±------------------------------------------+ 1
row in set (0.00 sec)
mysql>select * from mysql.user; //验证非授权的访问操作
ERROR 1142 (42000): SELECT command denied to user ‘xiaoqi’@‘localhost’ for table ‘user’

在企业服务器的应用中，数据库与网站服务器有时候是相互独立的。因此在 MySQL 服 务器中，应根据实际情况创建新的用户授权，允许授权用户从网站服务器访问数据库。通常 的做法是，创建一个或几个网站专用的数据库，并授予所有权限，限制访问的来源 IP 地址。 例如，执行以下操作可以新建 bdqn 数据库，并授权从 IP 地址为 192.168.4.19 的主机连接， 用户名为“dbuser”，密码为“pwd@123”，允许在 bdqn 数据库中执行所有操作。

mysql>CREATE DATABASE bdqn; 
Query OK, 1 row affected (0.00 sec) 
mysql>GRANT all ON bdqn.* TO 'dbuser'@'192.168.4.19' IDENTIFIED BY 'pwd@123'; 
Query OK, 0 rows affected (0.00 sec)

• 1
• 2
• 3
• 4

2．查看权限 SHOW GRANTS 语句

：专门用来查看数据库用户的授权信息，通过 FOR 子句可指定 查看的用户对象（必须与授权时使用的对象名称一致），语句格式如下所示。

SHOW GRANTS FOR 用户名@来源地址
执行以下操作可以查看用户 dbuser 从主机 192.168.4.19 访问数据库时的授权信息。

mysql>SHOW GRANTS FOR 'dbuser'@'192.168.4.19'; 
+------------------------------


    
-------------------------------+ | 
Grants for dbuser@192.168.4.19 | 
+-------------------------------------------------------------+ | 
GRANT USAGE ON *.* TO 'dbuser'@'192.168.4.19' |
| GRANT ALL PRIVILEGES ON `bdqn`.* TO 'dbuser'@'192.168.4.19' | +-------------------------------------------------------------+ 2 
rows in set (0.00 sec)

• 1
• 2
• 3
• 4
• 5
• 6
• 7

3．撤销权限 REVOKE 语句

：用于撤销指定用户的数据库权限，撤销权限后的用户仍然可以连接到 MySQL 服务器，但将被禁止执行对应的数据库操作，语句格式如下所示。

REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@来源地址

执行以下操作可以撤销用户 xiaoqi 从本机访问数据库 auth 的所有权限。

mysql>REVOKE all ON auth.* FROM 'xiaoqi'@'localhost'; 
Query OK, 0 rows affected (0.01 sec) 
mysql>SHOW GRANTS FOR 'xiaoqi'@'localhost'; //确认已撤销对 auth 库的权限 +-------------------------------------------------------------------+ | Grants for xiaoqi@localhost | +-------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'xiaoqi'@'localhost' | +-------------------------------------------------------------------+ 1 row in set (0.00 sec)

• 1
• 2
• 3