Linux——Mysql数据库操作
数据定义语言。
目录
一，mysql数据库介绍
1，SQL语言的分类
- DDL(Data Definition Language，数据定义语言):用来建立数据库、数据库对象和定义字段，
| 语句 | 说明 |
| create | 创建数据库 |
| alter | 往数据表中增添列 |
| drop | 删除数据表 |
- DML(Data Manipulation Language，数据操作语言)，用来插入，删除和修改数据库中的数据，如INSERT、UPDATE、DELETE。
| 语句 | 说明 |
| update | 更新数据 |
| INSERT | 插入数据 |
| delete | 删除数据 |
- DQL(Data Query Language，数据查询语言):用来查询数据库中的数据如 SELECT。
| 语句 | 说明 |
| SELECT DISTINCT column1 FROM table_name; | 从数据表中选取特定列的唯一值。 |
| SELECT * FROM table_name; | 从数据表中选取所有列的数据 |
| SELECT * FROM table_name WHERE condition; | 根据条件从数据表中筛选数据。 |
- DCL(Data Control Language，数据控制语言):用来控制数据库组件的存取许可、存取权限等，如COMMIT、ROLLBACK、GRANT、REVOKE。
| 语句 | 说明 |
| COMMIT | 提交当前事务。 |
| ROLLBACK | 回滚当前事务。 |
| GRANT | 为用户授予特定权限。 |
| REVOKE | 撤销用户的特定权限。 |
2，系统数据库
初始化后的mysql服务器默认建立了四个数据库：
- information_schema：虚拟库，不占用磁盘空间，存储的是数据库启动后的一information schema:些参数，如用户表信息、列信息、权限信息、字符信息等
- mysql：授权库，主要存储系统用户的权限信息
- performance_schema：MySQL 5.5开始新增一个数据库:主要用于收集数据库服务器性能参数，记录处理查询请求时发生的各种事件、锁等现象
- sys：主要用于存储系统性能信息和监控数据,对数据库的性能优化和故障排除具有关键作用
3，表的数据类型
Mysql支持多种类型，大致可以分为三类：数值，日期/时间和字符串类型，
1，数值：
| 类型 | 字节 | 范围 | 说明 |
| TINYINT | 1 | 0-255 | 最小的整数数据类型 |
| SMALLINT | 2 | 0-65535 | 小整数类型 |
| MEDIUMINT | 3 | 0-16777215 | 中等大小的整数类型 |
| INT/INTEGER | 4 | 0-4294967295 | 最常用的整数类型 |
| BIGINT | 8 | 0-2^64-1 | 大整数类型 |
| FLOAT | 4 | 0-4294967295 | 单精度浮点类型 |
| DOUBLE | 8 | 0-2^64-1 | 双精度浮点类型 |
| DECIMAL(M,D) | 最大14 字节 | 精确小数，M是总位数(1-65)，D是小数位数 |
2，日期/时间：
| 类型 | 格式 | 范围 | 说明 |
| DATE | YYYY-MM-DD | 1000-01-01-9999-12-31 | 年月日 |
| TIME | HH:MM:SS | -838:59:59-838:59:59 | 分时秒 |
| DATETIME | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 | 日期时间值 |
| TIMESTAMP | YYYY-MM-DD HH:MM:SS | 1970-01-01 00:00:01 ~ 2038-01-19 03:14:07 UTC | 时间戳，自动转换时区 |
| YEAR | YYYY | 1901 ~ 2155 | 年份值 |
3，字符串：
| 类型 | 最大长度 | 说明 |
| CHAR(n) | 255 | 定长字符串，n为字符数 |
| VARCHAR(n) | 65,535 | 变长字符串，n为最大字符数 |
| TINYTEXT | 255字节 | 短文本字符串 |
| TEXT | 65,535字节 | 常规文本字符串 |
| MEDIUMTEXT | 16,777,215字节 | 中等长度文本字符串 |
| LONGTEXT | 4GB | 长文本字符串 |
| BINARY(n) | 255 | 定长二进制数据 |
| VARBINARY(n) | 65,535 | 变长二进制数据 |
| BLOB | 65,535 | 二进制大对象 |
| LONGBLOB | 4GB | 超大的二进制数据 |
二，mysql数据库操作
1，数据库的操作
- 创建数据库
语法：
create database <数据库名>;
- 选择要进入的数据库
语法：
use 数据库名;
- 查看数据库
show databases; #查看当前数据库中有哪些表
show create database db1; #显示创建名为db1的数据库时使用的sql语句
select database(); #查看当前所在数据库名称
- 删除数据库
语法：
drop database 数据库名;
2，mysql表的操作
- 查看表
mysql> use mysql; ##进入到数据库中
Database changed
mysql> show tables; ##查看库中的所有表
- 创建表
语法：
create table 表名（
字段名1 类型[（宽度）约束条件]
字段名1 类型[（宽度）约束条件]
字段名1 类型[（宽度）约束条件]
）;
- 创建一个完整的表
mysql>use db1;
mysql> create table t1 (id int,name varchar(50),age int(3),sex enum('male','female'));
mysql>show tables;
- 查看表结构
mysql> desc t1;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)- 修改表名
语法：
alter table 表名 rename 新表名;
mysql> alter table t1 rename t2;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t2 |
+---------------+
1 row in set (0.00 sec)
- 往表里面增加字段
语法:
ALTER TABLE 表名ADD 字段名 数据类型完整性约束条件…]
mysql> alter table t2 add class int(3);
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> desc t2;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| class | int | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
- 删除字段
语法：
alter table 表名 drop 字段名;
mysql> alter table t2 drop class;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t2;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
- 修改字段
语句:
alter table 表明 modify 字段名 [完整性约束条件];
mysql> alter table t2 modify name char(50);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t2 change name user_name varchar(50);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t2;
+-----------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| user_name | varchar(50) | YES | | NULL | |
| age | int | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
+-----------+-----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
- 复制表
语法：
create table 新表 like 旧表; ##只复制表结构不负责表中的数据
mysql> create table t3 like t2;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t2 |
| t3 |
+---------------+
2 rows in set (0.00 sec)
create table t4 select * from t2; ##复制表结构+记录（不会复制 主键，外键和索引）- 删除表
语句：清空表内的内容
truncate <表名>;
delete from <表名>;
语句：删除整个表
drop table t2;3，mysql数据操作
- 插入数据
按照顺序插入数据：
mysql> insert into t2 values(1,'zhangsan',22,'male');
Query OK, 1 row affected (0.00 sec)
指定字段插入数据
mysql> insert into t2(id,user_name,age,sex) values(2,'lisi',21,'female');
Query OK, 1 row affected (0.01 sec)
插入多行数据
mysql> insert into t2 values(3,'wangwu',24,'male'),(4,'zaholiu',19,'male');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
- 更新数据
语法：
update 表名 set 字段名=字段值 where 条件表达式
##修改wangwu的年龄为30
mysql> update t2 set age=30 where user_name='wangwu';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
- 删除数据
语法：
delete from 表名 where 条件表达式
mysql> delete from t2 where id=1;
Query OK, 1 row affected (0.00 sec)
三：查询数据insert
SELECT 语句:用于从指定的表中查找符合条件的数据记录。MySQL数据库支持标准的 SQL 查询语句。
1，单表查询的语法：
select 字段1，字段2...... from 表名
where 条件
group by 字段
having 筛选
order by 字段
limit 限制条数
关键字执行的优先级
from
wheregroup by
having
select
order by
limit
准备数据
-
简单查询
语法：select 字段1，字段2...... from表名
例子：select id,name,office from renyuan;
表示所有字段时，可以也使用通配符<*>,显示所有的数据记录
-
where条件
WHERE 是 SQL 中用于筛选数据的核心子句，其作用是通过指定条件从表中过滤出符合条件的记录。它可应用于SELECT、UPDATE、DELETE等语句中，控制操作的范围。
where子句中可以使用：
1，比较运算符：> < >= <= <> !=
2，between 80 and 100 值在10到20之间
3，in(80,90,100)值是10或20或30
4，like 'egon&'
pattern可也是%或_
%表示任意多字符
_表示一个字符
5，逻辑运算符：在多个条件直接可以使用逻辑运算符and or not
比较运算符：
##薪资大于10000的人的姓名
mysql> select name,salary from renyuan where salary > 1000;
##不是403办公室的人姓名
mysql> select name,office from renyuan where office!=403;
##薪资在10000至15000之间的人的姓名
mysql> select name,salary from renyuan where salary between 10000 and 15000;
##薪资是9000或10000或30000的人的姓名
mysql> select name,salary from renyuan where salary in(9000,10000,3000);
##Like:模糊匹配
_:任意单个字符
%：任意多个字符
名字以 程 开头的人的信息
mysql> select * from renyuan where name like "程咬_";
mysql> select * from renyuan where name like "程%";
逻辑运算符:and or not
##薪资17000并且办公室是403的人
select * from renyuan where office=403 and salary=17000；
##薪资 17000 或者办公室是 403的人
select * from renyuan where office=403 or salary=17000;
##薪资不是 9000 或 10000 或 30000 的人姓名
select name, salary from renyuan where salary not in(9000, 10000, 30000);
2，group by 分组
分组指的是:将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等
分组的好处
取每个部门的最高工资
取每个部门的员工数
取男人数和女人数 #这个需求没有“每”字，但可以把需求格式化为:取每个性别的人数
##sex列进行分类
mysql> select sex from renyuan group by sex;
+--------+
| sex |
+--------+
| male |
| female |
+--------+
2 rows in set (0.00 sec)
聚合函数：conut() avg() max() min() sum()
count():计数 计算男生和女生的人数
mysql>select sex,count(*)from renyuan group by sex;
avg():平均值 每个岗位的平均薪资
mysql>select post,avg(salary)from renyuan group by post;
max():最大值每个岗位的最高薪资
mysql>select post,max(salary)from renyuan group by post;
min():最小值每个岗位的最低薪资
mysql>select post,min(salary)from renyuan group by post;
sum():总和每个岗位的薪资总和
mysql>select post,sum(salary)from renyuan group by post;3，having过滤
HAVING与WHERE不一样的地方
执行优先级从高到低:where>groupby>having
Where 发生在分组 group by 之前，因而 Where 中可以有任意字段，但是绝对不能使用聚合函数。Having 发生在分组 group by 之后，因而 Having 中可以使用分组的字段，无法直接取到其他字段,但可以使用聚合函数
有哪些岗位是平均工资大于 10000 的
mysql>select post,avg(salary) from renyuan group by post having avg(salary)>10000:
-
order by 排序
使用ORDERBY语句来完成排序，并最终将排序后的结果返回给用户
mysql>select *from renyuan order by id desc;#倒叙
mysql>select *from renyuan order by id asc;#默认是正序 asc 可以省略-
limit限制结果条目
在使用 MySQL SELECT 语句进行查询时，结果集返回的是所有匹配的记录。有时候仅需要返回第一行或者前几行，这时候就需要用到 LIMIT 子句
倒叙排列后仅显示第一行
mysql>select * from renyuan order by id desc limit 1;
-
正则匹配
MySQL 正则表达式通常是在检索数据库记录的时候，根据指定的匹配模式匹配记录中符合要求的特殊字符串。MySQL的正则表达式使用 REGEXP 这个关键字来指定正则表达式的匹配模式
Name 是1开头的姓名
mysql>select *from renyuan where name regexp 1;
Name 是u结尾的姓名
mysql>select * from renyuan where name regexp "u$";
Name 是 wan 和wu 之间至少1个g的姓名
mysql>select * from renyuan where name regexp "wang+wu";-
子查询
子查询也被称作内查询或者嵌套查询，是指在一个查询语句里面还嵌套着另一个查询语句。子查询语句是先于主查询语句被执行的，其结果作为外层的条件返回给主查询进行下一步的查询过滤。子查询不仅可以在 SELECT 语句中使用，在 INERT、UPDATE、DELETE 中也同样适用。在嵌套的时候，子查询内部还可以再次嵌套新的子查询，也就是说可以多层嵌套。
语法：<表达式> [NOT] IN <子查询>
##查询平均年龄在25岁以上的部门名
mysql> select id,name from bumen where id in (select dep_id from renyuan group by dep_id having avg(age) > 25);
+------+--------------+
| id | name |
+------+--------------+
| 201 | 人力资源 |
| 202 | 销售 |
+------+--------------+
2 rows in set (0.00 sec)
##查看技术部员工姓名
mysql> select name from renyuan where dep_id in (select id from bumen where name='技术');
+----------+
| name |
+----------+
| zhangsan |
| sunqi |
+----------+
2 rows in set (0.00 sec)
##查看不足一人的部门名（子查询得到的是有人的部门id）
mysql> select name from bumen where id not in (select distinct dep_id from renyuan);
+--------+
| name |
+--------+
| 运营 |
+--------+
1 row in set (0.00 sec)
4，多表连接查询
MySQL 的连接查询，通常都是将来自两个或多个表的行结合起来，基于这些表之间的共同字段，进行数据的拼接。首先，要确定一个主表作为结果集，然后将其他表的行有选择性的连接到选定的主表结果集上。使用较多的连接查询包括:内连接、左连接和右连接。
-
内连接（只连接匹配的行）
MySQL 中的内连接就是两张或多张表中同时符合某种条件的数据记录的组合。通常在 FROM 子句中使用关键字INNERJOIN 来连接多张表，并使用 ON 子句设置连接条件。
mysql> select renyuan.id,renyuan.name,renyuan.age,renyuan.sex,bumen.name from renyuan inner join buumen on renyuan.dep_id=bumen.id;
+----+----------+------+--------+--------------+
| id | name | age | sex | name |
+----+----------+------+--------+--------------+
| 1 | zhangsan | 18 | male | 技术 |
| 2 | lisi | 48 | female | 人力资源 |
| 3 | wangwu | 38 | male | 人力资源 |
| 4 | zhaoliu | 28 | female | 销售 |
| 5 | sunqi | 18 | male | 技术 |
+----+----------+------+--------+--------------+
5 rows in set (0.00 sec)
-
左连接
左连接也可以被称为左外连接，在FROM子句中使用LEFTJOIN或者 LEFTOUTER JOIN 关键字来表示。左连接以左侧表为基础表，接收左表的所有行，并用这些行与右侧参考表中的记录进行匹配,也就是说匹配左表中的所有行。
mysql> select renyuan.id,renyuan.name,bumen.name as depart_name from renyuan left join bumen on renyuan.dep_id=bumen.id;
+----+----------+--------------+
| id | name | depart_name |
+----+----------+--------------+
| 1 | zhangsan | 技术 |
| 2 | lisi | 人力资源 |
| 3 | wangwu | 人力资源 |
| 4 | zhaoliu | 销售 |
| 5 | sunqi | 技术 |
| 6 | zhuba | NULL |
+----+----------+--------------+
6 rows in set (0.00 sec)
-
右连接
右连接也被称为右外连接，在FROM子句中使用RIGHT JOIN 或者 RIGHTOUTER JOIN 关键字来表示。右连接跟左连接正好相反，它是以右表为基础表，用于接收右表中的所有行，并用这些记录与左表中的行进行匹配。也就是说匹配右表中的每一行及左表中符合条件的记录。
mysql> select renyuan.id,renyuan.name,bumen.name as depart_name from renyuan right join bumen on renyuan.dep_id=bumen.id;
+------+----------+--------------+
| id | name | depart_name |
+------+----------+--------------+
| 5 | sunqi | 技术 |
| 1 | zhangsan | 技术 |
| 3 | wangwu | 人力资源 |
| 2 | lisi | 人力资源 |
| 4 | zhaoliu | 销售 |
| NULL | NULL | 运营 |
+------+----------+--------------+
6 rows in set (0.00 sec)
四：mysql数据库用户授权
MySQL 数据库的 root 用户账号拥有对所有数据库、表的全部权限，频繁使用 root 账号会给数据库服务器带来一定的安全风险。实际工作中，通常会建立一些低权限的用户，只负责一部分数据库、表的管理和维护操作，甚至可以对查询、修改、删除记录等各种操作做进一步的细化限制，从而将数据库的风险降至最低。
1，创建用户
语法：create user 用户名@来源地址 identified by '密码'
- 用户名@来源地址: 用于指定用户名称和允许访问的客户机地址,即谁能连接能从哪里连接。来源地址可以是域名、IP 地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址，如“%.bdqn.com”，“192.168.1.%”等。
- IDENTIFIED BY: 用于设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略“IDENTIFIED BY”部分，则用户的密码将为空
##创建一个用户并允许本机访问
mysql> create user 'zhangsan'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
2，授权操作
语法：grant 权限列表 on 数据库名.表名 to 用户名@来源地址
- 权限列表:用于列出授权使用的各种数据库操作，以逗号进行分隔，如select,insert,update”。使用“a11”表示所有权限，可授权执行任何操作。
- 数据库名.表名:用于指定授权操作的数据库和表的名称，其中可以使用通’配符“*”。例如，使用“auth.*”表示授权操作的对象为 auth
##授予zhangsan用户对db1数据库有select权限
mysql> grant select on db1.* to 'zhangsan'@'localhost';
Query OK, 0 rows affected, 1 warning (0.01 sec)
##使用此命令查看zhangsan用户的权限
mysql> show grants for 'zhangsan'@'localhost';
+---------------------------------------------------+
| Grants for zhangsan@localhost |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO `zhangsan`@`localhost` |
| GRANT SELECT ON `db1`.* TO `zhangsan`@`localhost` |
+---------------------------------------------------+
2 rows in set (0.00 sec)
3，查看权限
语法：show grants for 用户名@来源地址
查看用户zhangsan所拥有的权限
mysql> show grants for 'zhangsan'@'localhost';
+---------------------------------------------------+
| Grants for zhangsan@localhost |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO `zhangsan`@`localhost` |
| GRANT SELECT ON `db1`.* TO `zhangsan`@`localhost` |
+---------------------------------------------------+
2 rows in set (0.00 sec)
4，撤销权限
语法：revoke 权限列表 on 数据库名.表名 from 用户名@来源地址
撤销zhagnsan用户的权限
mysql> revoke all on db1.*from 'zhangsan'@'localhost';
Query OK, 0 rows affected, 1 warning (0.01 sec)
##查看zhangsan用户的权限
mysql> show grants for 'zhangsan'@'localhost';
+----------------------------------------------+
| Grants for zhangsan@localhost |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `zhangsan`@`localhost` |
+----------------------------------------------+
1 row in set (0.00 sec)
DAMO开发者矩阵，由阿里巴巴达摩院和中国互联网协会联合发起，致力于探讨最前沿的技术趋势与应用成果，搭建高质量的交流与分享平台，推动技术创新与产业应用链接，围绕“人工智能与新型计算”构建开放共享的开发者生态。
更多推荐
38
0- 0
已为社区贡献4条内容
所有评论(0)