mysql基础
搭建mysql服务
安装mysql
[root@192 ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm //配置mysqlyum源
--2022-07-26 18:21:28-- http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
Resolving dev.mysql.com (dev.mysql.com)... 23.13.191.247, 2600:140e:6:79d::2e31, 2600:140e:6:7a7::2e31
Connecting to dev.mysql.com (dev.mysql.com)|23.13.191.247|:80... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm [following]
--2022-07-26 18:21:28-- https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
Connecting to dev.mysql.com (dev.mysql.com)|23.13.191.247|:443... connected.
HTTP request sent, awaiting response... 302 Moved Temporarily
Location: https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm [following]
--2022-07-26 18:21:28-- https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm
Resolving repo.mysql.com (repo.mysql.com)... 2.16.232.230
Connecting to repo.mysql.com (repo.mysql.com)|2.16.232.230|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 25680 (25K) [application/x-redhat-package-manager]
Saving to: ‘mysql57-community-release-el7-11.noarch.rpm’
mysql57-community-releas 100%[==================================>] 25.08K 46.0KB/s in 0.5s
2022-07-26 18:21:30 (46.0 KB/s) - ‘mysql57-community-release-el7-11.noarch.rpm’ saved [25680/25680]
[root@192 ~]# rpm -Uvh mysql57-community-release-el7-11.noarch.rpm
warning: mysql57-community-release-el7-11.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Verifying... ################################# [100%]
Preparing... ################################# [100%]
Updating / installing...
1:mysql57-community-release-el7-11 ################################# [100%]
[root@192 ~]# yum module disable mysql
MySQL Connectors Community 48 kB/s | 51 kB 00:01
MySQL Tools Community 325 kB/s | 711 kB 00:02
MySQL 5.7 Community Server 1.2 MB/s | 2.7 MB 00:02
Dependencies resolved.
====================================================================================================
Package Architecture Version Repository Size
====================================================================================================
Disabling modules:
mysql
Transaction Summary
====================================================================================================
Is this ok [y/N]: y
Complete!
[root@192 ~]# dnf -y install mysql-community-server mysql-community-client mysql-community-common mysql-community-devel --nogpgcheck
Last metadata expiration check: 0:00:35 ago on Tue 26 Jul 2022 06:22:39 PM CST.
Dependencies resolved.
====================================================================================================
Package Arch Version Repository Size
====================================================================================================
Installing:
mysql-community-client x86_64 5.7.39-1.el7 mysql57-community 28 M
mysql-community-common x86_64 5.7.39-1.el7 mysql57-community 311 k
mysql-community-devel x86_64 5.7.39-1.el7 mysql57-community 4.2 M
mysql-community-server x86_64 5.7.39-1.el7 mysql57-community 178 M
Installing dependencies:
libpkgconf x86_64 1.4.2-1.el8 baseos 35 k
mysql-community-libs x86_64 5.7.39-1.el7 mysql57-community 2.6 M
ncurses-compat-libs x86_64 6.1-9.20180224.el8 baseos 328 k
net-tools x86_64 2.0-0.52.20160912git.el8 baseos 322 k
perl-Carp noarch 1.42-396.el8 baseos 30 k
perl-Data-Dumper x86_64 2.167-399.el8 baseos 58 k
perl-Digest noarch 1.17-395.el8 appstream 27 k
perl-Digest-MD5 x86_64 2.55-396.el8 appstream 37 k
perl-Encode x86_64 4:2.97-3.el8 baseos 1.5 M
perl-Errno x86_64 1.28-421.el8 baseos 76 k
perl-Exporter noarch 5.72-396.el8 baseos 34 k
perl-File-Path noarch 2.15-2.el8 baseos 38
......
配置mysql
//启动mysql并且设置开机自启
[root@192 ~]# systemctl restart mysqld.service
[root@192 ~]# systemctl enable mysqld
[root@192 ~]# systemctl status mysqld.service
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2022-07-26 18:26:01 CST; 2min 12s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Main PID: 12465 (mysqld)
Tasks: 27 (limit: 24688)
Memory: 305.0M
CGroup: /system.slice/mysqld.service
└─12465 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
Jul 26 18:25:58 192.168.253.130 systemd[1]: Starting MySQL Server...
Jul 26 18:26:01 192.168.253.130 systemd[1]: Started MySQL Server.
[root@192 ~]# ss -anltup | grep mysql //查看mysql端口是否可用
tcp LISTEN 0 80 *:3306 *:* users:(("mysqld",pid=12465,fd=21))
[root@192 ~]# grep "password" /var/log/mysqld.log //在日志文件中找出mysql初始密码
2022-07-26T10:25:59.415236Z 1 [Note] A temporary password is generated for root@localhost: :b<<Cvu&K00F
[root@192 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.39
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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>
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
mysql> alter user 'root'@'localhost' identified by 'ray123!';
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[root@192 ~]# rpm -e mysql57-community-release //避免mysql自动升级,卸载yum源
[root@192 ~]# rpm -e mysql57-community-release
error: package mysql57-community-release is not installed
DML操作
DML操作包括增(INSERT)、删(DELETE)、改(UPDATE)、查(SELECT),均属针对表的操作。
insert语句
mysql> use ray;
Database changed
mysql> insert light(id,name,age) values(1,'zhangsan',5),(2,'lisi',6),(3,'wangwu',6),(4,'shuaige',8),(5,'shabi',7);
Query OK, 5 rows affected (0.03 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from light;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 5 |
| 2 | lisi | 6 |
| 3 | wangwu | 6 |
| 4 | shuaige | 8 |
| 5 | shabi | 7 |
+----+----------+------+
5 rows in set (0.00 sec)
select语句
字段column表示法
表示符 | 代表什么? |
---|---|
* | 所有字段 |
as | 字段别名,如col1 AS alias1 当表名很长时用别名代替 |
条件判断语句WHERE
操作类型 | 常用操作符 |
---|---|
操作符 | >,<,>=,<=,=,!= BETWEEN column# AND column# LIKE:模糊匹配 RLIKE:基于正则表达式进行模式匹配 IS NOT NULL:非空 IS NULL:空 |
条件逻辑操作 | AND OR NOT |
ORDER BY:排序,默认为升序(ASC)
ORDER BY语句 | 意义 |
---|---|
ORDER BY ‘column_name’ | 根据column_name进行升序排序 |
ORDER BY ‘column_name’ DESC | 根据column_name进行降序排序 |
ORDER BY ’column_name’ LIMIT 2 | 根据column_name进行升序排序 并只取前2个结果 |
ORDER BY ‘column_name’ LIMIT 1,2 | 根据column_name进行升序排序 并且略过第1个结果取后面的2个结果 |
mysql> select * from light; //查询所有字段
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 5 |
| 2 | lisi | 6 |
| 3 | wangwu | 6 |
| 4 | shuaige | 8 |
| 5 | shabi | 7 |
+----+----------+------+
5 rows in set (0.00 sec)
mysql> select name from light; //查询名字
+----------+
| name |
+----------+
| zhangsan |
| lisi |
| wangwu |
| shuaige |
| shabi |
+----------+
5 rows in set (0.00 sec)
mysql> select * from light order by id; //id升序
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 5 |
| 2 | lisi | 6 |
| 3 | wangwu | 6 |
| 4 | shuaige | 8 |
| 5 | shabi | 7 |
+----+----------+------+
5 rows in set (0.00 sec)
mysql> select * from light order by id desc; //id降序
+----+----------+------+
| id | name | age |
+----+----------+------+
| 5 | shabi | 7 |
| 4 | shuaige | 8 |
| 3 | wangwu | 6 |
| 2 | lisi | 6 |
| 1 | zhangsan | 5 |
+----+----------+------+
5 rows in set (0.00 sec)
mysql> select * from light order by id limit 2; //升序取前两个
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 5 |
| 2 | lisi | 6 |
+----+----------+------+
2 rows in set (0.00 sec)
mysql> select * from light order by id limit 1,2; //升序跳过第一个取下面两个
+----+--------+------+
| id | name | age |
+----+--------+------+
| 2 | lisi | 6 |
| 3 | wangwu | 6 |
+----+--------+------+
2 rows in set (0.00 sec)
mysql> select * from light where age > 5; //大于5
+----+---------+------+
| id | name | age |
+----+---------+------+
| 2 | lisi | 6 |
| 3 | wangwu | 6 |
| 4 | shuaige | 8 |
| 5 | shabi | 7 |
+----+---------+------+
4 rows in set (0.00 sec)
mysql> select * from light where age >=6; 大于等于6
+----+---------+------+
| id | name | age |
+----+---------+------+
| 2 | lisi | 6 |
| 3 | wangwu | 6 |
| 4 | shuaige | 8 |
| 5 | shabi | 7 |
+----+---------+------+
4 rows in set (0.00 sec)
mysql> select * from light where age =6 and name = 'lisi'; //查找年龄等于6 名字为lisa
+----+------+------+
| id | name | age |
+----+------+------+
| 2 | lisi | 6 |
+----+------+------+
1 row in set (0.00 sec)
mysql> select * from light where age between 6 and 8; 查询年龄在6和8之间的
+----+---------+------+
| id | name | age |
+----+---------+------+
| 2 | lisi | 6 |
| 3 | wangwu | 6 |
| 4 | shuaige | 8 |
| 5 | shabi | 7 |
+----+---------+------+
4 rows in set (0.00 sec)
mysql> select * from light where age is not null; 查询年龄不为空
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 5 |
| 2 | lisi | 6 |
| 3 | wangwu | 6 |
| 4 | shuaige | 8 |
| 5 | shabi | 7 |
+----+----------+------+
5 rows in set (0.00 sec)
mysql> select * from light where age is null; //查询年龄为空
Empty set (0.00 sec)
update
mysql> select * from light;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 5 |
| 2 | lisi | 6 |
| 3 | wangwu | 6 |
| 4 | shuaige | 8 |
| 5 | shabi | 7 |
+----+----------+------+
5 rows in set (0.00 sec)
mysql> update light set age = 10 where name = 'lisi'; //将lisi年龄修改为10
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from light;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 5 |
| 2 | lisi | 10 |
| 3 | wangwu | 6 |
| 4 | shuaige | 8 |
| 5 | shabi | 7 |
+----+----------+------+
5 rows in set (0.00 sec)
delete
mysql> select * from light;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 5 |
| 2 | lisi | 10 |
| 3 | wangwu | 6 |
| 4 | shuaige | 8 |
| 5 | shabi | 7 |
+----+----------+------+
5 rows in set (0.00 sec)
mysql> delete from light where id = 5; //将id等于6的删除
Query OK, 1 row affected (0.00 sec)
mysql> select * from light;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 5 |
| 2 | lisi | 10 |
| 3 | wangwu | 6 |
| 4 | shuaige | 8 |
+----+----------+------+
4 rows in set (0.00 sec)
mysql> delete from light; //删除整张表的内容
Query OK, 4 rows affected (0.00 sec)
mysql> select * from light;
Empty set (0.00 sec)
mysql> desc light;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
truncate
truncate与delete的区别:
语句类型 | 特点 |
---|---|
delete | DELETE删除表内容时仅删除内容,但会保留表结构 DELETE语句每次删除一行,并在事务日志中为所删除的每行记录一项 可以通过回滚事务日志恢复数据 非常占用空间 |
truncate | 删除表中所有数据,且无法恢复 表结构、约束和索引等保持不变,新添加的行计数值重置为初始值 执行速度比DELETE快,且使用的系统和事务日志资源少 通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放 对于有外键约束引用的表,不能使用TRUNCATE TABLE删除数据 不能用于加入了索引视图的表 |
mysql> select * from light;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 5 |
| 2 | lisi | 6 |
| 3 | wangwu | 6 |
| 4 | shuaige | 8 |
| 5 | shabi | 7 |
+----+----------+------+
5 rows in set (0.00 sec)
mysql> truncate light;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from light;
Empty set (0.00 sec)
mysql> desc light;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
DCL操作
创建授权grant
权限类型(priv_type)
权限类型 | 代表什么? |
---|---|
ALL | 所有权限 |
SELECT | 读取内容的权限 |
INSERT | 插入内容的权限 |
UPDATE | 更新内容的权限 |
DELETE | 删除内容的权限 |
指定要操作的对象db_name.table_name
表示方式 | 意义 |
---|---|
. | 所有库的所有表 |
db_name | 指定库的所有表 |
db_name.table_name | 指定库的指定表 |
WITH GRANT OPTION: 被授权的用户可以将自己的权限副本转增给其他用户,说白点就是将自己的权限完全复制给另外一个用户。不建议使用
创建授权grant
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| ray |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> grant all on *.* to 'ray'@'localhost' identified by 'ray123!'; //授权ray在数据库本机登录访问所有数据库
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all on *.* to 'ray'@'127.0.0.1' identified by 'ray123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all on ray.* to 'ray'@'192.168.253.130' identified by 'ray123!';//授权ray在192.168.253.130上远程登录访问ray数据库
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all on *.* to 'ray'@'%' identified by 'ray123!';//授权ray在所有位置上远程登录访问ray数据库
Query OK, 0 rows affected, 1 warning (0.00 sec)
查看权限
mysql> show grants; //查看当前用户权限
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show grants for 'ray'@'192.168.253.130'; //查看ray权限
+------------------------------------------------------------+
| Grants for ray@192.168.253.130 |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ray'@'192.168.253.130' |
| GRANT ALL PRIVILEGES ON `ray`.* TO 'ray'@'192.168.253.130' |
+------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
取消授权revoke
mysql> revoke all on *.* from 'ray'@'192.168.253.130';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
实战案例
创建库与表
创建一个以你名字为名的数据库,并创建一张表student,该表包含三个字段(id,name,age)
mysql> create database wenjincheng;
Query OK, 1 row affected (0.00 sec)
mysql> use wenjincheng;
Database changed
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| ray |
| sys |
| wenjincheng |
+--------------------+
6 rows in set (0.00 sec)
mysql> use wenjincheng;
Database changed
mysql> create table student(id int(11) primary key auto_increment,name varchar(100) not null,age tinyint(4));
Query OK, 0 rows affected (0.01 sec)
mysql> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
查看表内容
查看下该新建的表有无内容(用select语句)
mysql> select * from student;
Empty set (0.00 sec)
键入数据
往新建的student表中插入数据(用insert语句)
mysql> insert into student (name,age)
-> values('tom',20),
-> ('jerry',23),('wangqing',25),('sean',28),
-> ('zhangshan',26),('zhangsan',20),('lisi',null),
-> ('chenshuo',10),('wangwu',3),('qiuyi',15),('qiuxiaotian',20);
Query OK, 11 rows affected (0.01 sec)
Records: 11 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangsan | 20 |
| 7 | lisi | NULL |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
11 rows in set (0.00 sec)
mysql>
修改表数据
修改lisi年龄为50
mysql> update student set age = 50 where name = 'lisi';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student
-> where name = 'lisi';
+----+------+------+
| id | name | age |
+----+------+------+
| 7 | lisi | 50 |
+----+------+------+
1 row in set (0.01 sec)
以降序排序
以age字段降序排序
mysql> select * from student
-> order by age desc;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 7 | lisi | 50 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 3 | wangqing | 25 |
| 2 | jerry | 23 |
| 1 | tom | 20 |
| 6 | zhangsan | 20 |
| 11 | qiuxiaotian | 20 |
| 10 | qiuyi | 15 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
+----+-------------+------+
11 rows in set (0.00 sec)
以升序查询表内容且跳过前几位
查询student年龄最小的三维同学跳过前两位
mysql> select * from student
-> order by age
-> limit 2,3;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 10 | qiuyi | 15 |
| 1 | tom | 20 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
3 rows in set (0.00 sec)
以降序查询表内容前几位
查询student表中年龄最大的四位同学
mysql> select * from student
-> order by age desc
-> limit 4;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 7 | lisi | 50 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 3 | wangqing | 25 |
+----+-----------+------+
4 rows in set (0.00 sec)
以条件查询表内容
查询student表中名字叫做zhangshan的记录
mysql> select * from student
-> where name = 'zhangshan';
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 5 | zhangshan | 26 |
+----+-----------+------+
1 row in set (0.00 sec)
满足多条件查询表内容
查询student表中名字叫做zhangshan且年龄大于二十岁的记录
mysql> select * from student
-> where name = 'zhangshan' and age > 20;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 5 | zhangshan | 26 |
+----+-----------+------+
1 row in set (0.00 sec)
以范围区间的条件查询表内容
查询student表中年龄在二十三到三十之间的记录
mysql> select * from student
-> where age
-> between 23 and 30;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
+----+-----------+------+
4 rows in set (0.01 sec)
修改表数据
修改wangwu的年龄为100
mysql> update student
-> set age = 100
-> where name = 'wangwu';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student
-> where name = 'wangwu';
+----+--------+------+
| id | name | age |
+----+--------+------+
| 9 | wangwu | 100 |
+----+--------+------+
1 row in set (0.00 sec)
以条件删除表数据
删除student中名字叫做zhangshan且年龄小于等于20的记录
mysql> delete from student
-> where name = 'zhangshan' and age >= 20;
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 6 | zhangsan | 20 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 100 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
10 rows in set (0.00 sec)