MySQL入门随笔

最新推荐文章于 2024-10-10 15:31:49 发布

KratHuang

最新推荐文章于 2024-10-10 15:31:49 发布

阅读量148

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_59974749/article/details/120285499

版权

数据库的简单操作

1、安装
yum install mariadb mariadb-server -y

#查看本机安装了哪些包
rpm -qa |grep mariadb 查看本机安装了哪些包含mariadb名字的包

#查看命令属于哪个包
[root@localhost ~]# which mysql
/usr/bin/mysql
[root@localhost ~]# rpm -qf /usr/bin/mysql
mariadb-10.3.28-1.module_el8.3.0+757+d382997d.x86_64

2、启动服务
systemctl start mariadb

#mysqld的默认端口是3306

#查看服务状态
systemctl status mariadb

#常见错误：
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
#连接本地数据库的时候，是通过这个socket文件的，这里提示不能通过此socket文件连接，那基本都是由于服务没有启动

3、通过客户端去连接mariadb
#安装好之后连接mysql-server
#mysql -u root -p
#-u 是指定用户默认会创建root用户
#-p 是指定密码初始的时候敲回车即可

[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 31
Server version: 10.3.28-MariaDB MariaDB Server

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

MariaDB [(none)]>

#数据库的结构
库（database，文件夹） --> 表（table，二维表格，文件） -->字段（竖向），记录（横向）

#创建数据库sc
MariaDB [(none)]> create database sc
-> ;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> use sc;
Database changed
MariaDB [sc]>

#创建学生信息表
#学号、姓名、性别、年龄

#创建表格规律
# 字段名多个属性（用空格分割），
# int 表示整型
# primary key 表示主键
# auto_increment 表示自增
# varchar(128) 表示可变长字符串类型，最大长度为128字节
# not null 不为空
# default "fmale" 给它一个默认值

create table stuinfo(
stuid int primary key auto_increment,
stuname varchar(128) not null,
stusex varchar(64) default "fmale",
stuage int not null

);

create table stuinfo(
stu_xx int,
);

#查看表
MariaDB [sc]> show tables; #查看当前数据库有哪些表
+--------------+
| Tables_in_sc |
+--------------+
| stuinfo |
+--------------+
1 row in set (0.001 sec)

#新增插入语句
MariaDB [sc]> insert into stuinfo(stuname, stusex, stuage) values("huyuxuan","male",21);
Query OK, 1 row affected (0.002 sec)

MariaDB [sc]> insert into stuinfo(stuname, stusex, stuage) values("xiaoyanfei","fmale",18);
Query OK, 1 row affected (0.033 sec)

MariaDB [sc]> insert into stuinfo(stuname, stuage) values("maxueyan",20);
Query OK, 1 row affected (0.002 sec)

#查询语句
MariaDB [sc]> select * from stuinfo;
+-------+------------+--------+--------+
| stuid | stuname | stusex | stuage |
+-------+------------+--------+--------+
| 1 | huyuxuan | male | 21 |
| 2 | xiaoyanfei | fmale | 18 |
| 3 | huangzijie | male | 20 |
| 4 | wenlaoshi | male | 29 |
| 5 | fenglaoshi | male | 37 |
| 6 | shiyaling | fmale | 20 |
| 7 | maxueyan | fmale | 20 |
+-------+------------+--------+--------+
7 rows in set (0.001 sec)

MariaDB [sc]> select stuname,stuage from stuinfo;
+------------+--------+
| stuname | stuage |
+------------+--------+
| huyuxuan | 21 |
| xiaoyanfei | 18 |
| huangzijie | 20 |
| wenlaoshi | 29 |
| fenglaoshi | 37 |
| shiyaling | 20 |
| maxueyan | 20 |
+------------+--------+
7 rows in set (0.001 sec)

#输出为女性的平均年龄
MariaDB [sc]> select avg(stuage) from stuinfo where stusex="fmale";

#通过stusex这个做分类，分别计算每一类stusex对应的同学的最大年龄和平均年龄
MariaDB [sc]> select stusex,max(stuage),avg(stuage) from stuinfo group by stusex;

#过滤出最大年龄大于25的结果
MariaDB [sc]> select stusex,max(stuage) as max_stuage,avg(stuage) from stuinfo group by stusex having max_stuage>25;

#练习###########################################

#创建一张表
#产品表
farm_products
字段：
id 整型主键自增
type varchar(64) 水果，蔬菜，经济作物
name varchar(128) 苹果，梨子，棉花
price float
pro_date datetime "2021-09-13 14:00:00"

#往里面添加字段，可以重复

create table farm_products(
id int primary key auto_increment,
type varchar(64) ,
name varchar(128),
price float,
pro_date datetime default now()

) ;

MariaDB [sc]> insert into farm_products(type,name,price,pro_date) values("vegetable","potato",3.7,"2021-09-20 13:00");
Query OK, 1 row affected (0.001 sec)

MariaDB [sc]> select * from farm_products limit 5;
+----+-------+--------+-------+---------------------+
| id | type | name | price | pro_date |
+----+-------+--------+-------+---------------------+
| 1 | fruit | orange | 5.5 | 2021-08-11 12:00:00 |
| 2 | fruit | orange | 6.9 | 2021-07-11 12:00:00 |
| 3 | fruit | orange | 4.9 | 2021-09-11 12:00:00 |
| 4 | fruit | apple | 11.9 | 2021-09-11 13:00:00 |
| 5 | fruit | apple | 11.9 | 2021-08-21 13:00:00 |
+----+-------+--------+-------+---------------------+

#1、查询价格最高的5种产品（type，name， price）
select type,name,max(price) from farm_products group by name order by price desc limit 5;

#2、查询9月份之后录入的产品（type，name， price）
select type,name,price,pro_date from farm_products where pro_date >= "2021-09-01";

#3、查询每一类水果的平均价（type，name，和平均价）
select type,name,avg(price) from farm_products where type="fruit" group by name;
select type,name,avg(price) from farm_products group by name having type="fruit";

#4、查询8月份之后，每一类农产品的最大单价（name，price）
select type,name,max(price) from farm_products where pro_date >="2021-08-01" group by name;

#5、查询平均价最大的农产品的名字
select type,name,avg(price) as avg_price from farm_products group by name order by avg_price desc limit 1;

#6、查询8月份录入水果的均价
select avg(price) from farm_products where pro_date>="2021-08-01" and pro_date<"2021-09-01";

################################################

修改和删除
MariaDB [sc]> update stuinfo set stusex="fmale" where stuname="sc3";
Query OK, 1 row affected (0.002 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [sc]> delete from stuinfo where id <3;
ERROR 1054 (42S22): Unknown column 'id' in 'where clause'
MariaDB [sc]> delete from stuinfo where stuid <3;

##########权限管理

use mysql；

MariaDB [mysql]> select * from user \G;
*************************** 1. row ***************************
Host: localhost #客户端的ip地址
User: root
Password:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
Delete_history_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string:
password_expired: N
is_role: N
default_role:
max_statement_time: 0.000000