MYSQL入门笔记

科技2023-09-17 106

（1）你知道哪些数据库？

关系型数据库定义：关系型数据库以行和列的形式存储数据，这一系列的行和列被称为表，一组表组成了数据库。例子： MYSQL oracle postgresql MSSQL 非关系型数据库定义： key:value -->所有的数据存放的内存里，读取速度快–》缓存 json

{ age:36,name:cali,skills:{linux,python,mysql,network} }

列子： Redis MongoDB

(2)MYSQL在Linux内安装的三种方式

1.rpm包安装：

缺点：安装路径不可以指定，很多的参数不能指定 rpm方式安装MySQL 5.7.30 1.解压 [root@localhost ~]# tar xf mysql-5.7.30-1.el7.x86_64.rpm-bundle.tar

2.安装 [root@localhost ~]# yum install mysql-community-* -y 3.启动 mysql daemon ：守护进程就是一直在内存了运行的进程，日夜等待你的到来，像天使一样守护你 [root@localhost ~]# service mysqld start Redirecting to /bin/systemctl start mysqld.service [root@localhost ~]# [root@localhost ~]# systemctl restart mysqld 重启如何判断一个服务是启动了还是没有启动？ 1.看进程 [root@localhost ~]# ps aux|grep mysqld mysql 9814 0.7 4.6 1122100 181008 ? Sl 15:01 0:01 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid root 9844 0.0 0.0 112824 976 pts/0 S+ 15:03 0:00 grep --color=auto mysqld [root@localhost ~]# 2.看端口，3306是MySQL的端口号 [root@localhost ~]# netstat -anplut|grep mysqld tcp6 0 0 :::3306 ::😗 LISTEN 9814/mysqld [root@localhost ~]# [root@localhost ~]# lsof -i:3306 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME mysqld 9814 mysql 29u IPv6 43716 0t0 TCP *:mysql (LISTEN) [root@localhost ~]# 4.获得临时密码 [root@localhost ~]# cat /var/log/mysqld.log |grep temp 2020-10-05T06:58:17.189156Z 1 [Note] A temporary password is generated for root@localhost: >0U7Ag3nfjEu 5.登陆MySQL [root@localhost ~]# mysql -uroot -p’>0U7Ag3nfjEu’ mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.30

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> 6.重新设置root的密码 mysql> show databases; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. mysql> alter user ‘root’@‘localhost’ identified by ‘Sanchuang123#’; Query OK, 0 rows affected (0.00 sec)

mysql> ‘root’@‘localhost’ 用户的意思： root 代表是用户名 @是分隔符号 localhost代表root用户只能在本地登陆，不能在其他的电脑上登陆过来，进入MySQL identified by 是设置密码的 ; 是MySQL的命令结束符号 MySQL相关命令集合（1）启动MySQL服务

[root@localhost ~]# service mysqld start Redirecting to /bin/systemctl start mysqld.service

(2) MYSQL重启

[root@localhost ~]# systemctl restart mysqld

（3）MYSQL的日志文件放在哪里？

[root@localhost ~]# cat /var/log/mysqld.log

（4）MySQL查找临时密码

cat /var/log/mysqld.log|grep temp

结果：（5）用临时密码登录

[root@localhost ~]# mysql -uroot -p'*S#U_qE(q6il'

（6）MYSQL重新设置密码

mysql> alter user 'root'@'localhost' identified by 'Sanchuang123#'; Query OK, 0 rows affected (0.01 sec)

（7）显示数据库

（8）MYSQL存放数据的目录

[root@localhost ~]# cd /var/lib/mysql [root@localhost mysql]# ls auto.cnf ib_logfile0 private_key.pem ca-key.pem ib_logfile1 public_key.pem ca.pem ibtmp1 server-cert.pem client-cert.pem mysql server-key.pem client-key.pem mysql.sock sys ib_buffer_pool mysql.sock.lock ibdata1 performance_schema

（9）创建自己的数据库

（10）进入到三创数据库创建table

mysql> create table student(id int,name varchar(20),sex char(1),age int(3)); Query OK, 0 rows affected (0.02 sec) mysql> show tables -> ; +---------------------+ | Tables_in_Sanchuang | +---------------------+ | student | +---------------------+ 1 row in set (0.00 sec)

（11）查看table表的详细信息 --》查看表的结构 desc。 char 固定长度的字符串 character varchar 可变长度的字符串–》存放数据的时候更加节约空间，有多少个字符存放多少空间 variable character int 整形的数字

mysql> desc student -> ; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | age | int(3) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 4 rows in set (0.02 sec)

Processed: 0.016, SQL: 8