数据库的安装初始化及管理

1. 官网下载或者 wget

[root@mysql ~] # ls

anaconda-ks.cfg

initserver.sh

mysql-8.0.33-1.el7.x86_64.rpm-bundle.tar

mysql-community-client-8.0.33-1.el7.x86_64.rpm

mysql-community-client-plugins-8.0.33-1.el7.x86_64.rpm

mysql-community-common-8.0.33-1.el7.x86_64.rpm

mysql-community-debuginfo-8.0.33-1.el7.x86_64.rpm

mysql-community-devel-8.0.33-1.el7.x86_64.rpm

mysql-community-embedded-compat-8.0.33-1.el7.x86_64.rpm

mysql-community-icu-data-files-8.0.33-1.el7.x86_64.rpm

mysql-community-libs-8.0.33-1.el7.x86_64.rpm

mysql-community-libs-compat-8.0.33-1.el7.x86_64.rpm

mysql-community-server-8.0.33-1.el7.x86_64.rpm

mysql-community-server-debug-8.0.33-1.el7.x86_64.rpm

mysql-community-test-8.0.33-1.el7.x86_64.rpm

2. 确定 mysql-community-server 正常安装之后就可以开始配置

3. 初始化 mysqld 服务 mysqld initeialize

4. 启动服务 systemctl start mysqld

5. 添加开机启动列表 systecmctrl enable mysqld

6. 在 /var/log/mysql.log 中找到初始密码，前后复制空格 7. 登录管理 mysql -uroot -p

将复制的密码粘贴到输入密码的区

域，实现登录

8. 设置新的密码

9. 退出登录

1. 数据库的对象

1. 数据库系统 DBMS

2. 数据库

DB

3. 表

table

4. 记录

record

5. 字段

feild

1. 数据库操作

删除库

创建库

mysql > create datababse test charset utf8;

Query OK, 1 row affected, 1 warning ( 0.01 sec )

mysql > show databases ; 查看库

2. 表的操作

1. 选择库

+--------------------+

mysql > create datababse test charset utf8;

Query OK, 1 row affected, 1 warning ( 0.01 sec )

mysql > show databases ; 查看库

2. 表的操作

1. 选择库

2. 查看表

3. 创建表

mysql > CREATE TABLE `test` . `user` (

-> `id` INT NOT NULL AUTO_INCREMENT ,

-> `username` VARCHAR ( 45 ) NOT NULL ,

-> `password` VARCHAR ( 45 ) NOT NULL ,

-> PRIMARY KEY ( `id` ) ,

-> UNIQUE INDEX `username_UNIQUE` ( `username` ASC )

VISIBLE )

-> COMMENT = 'us' ;

mysql > show tables ;

+----------------+

| Tables_in_test |

+----------------+

| user |

| user0 |

4. 删除表

新增列

mysql > show tables ;

+----------------+

| Tables_in_test |

+----------------+

| user |

| user0 |

+----------------+

2 rows in set ( 0.00 sec )

mysql > drop table user0;

Query OK, 0 rows affected ( 0.01 sec )

mysql > show tables ;

+----------------+

| Tables_in_test |

+----------------+

| user |

+----------------+

1 row in set ( 0.01 sec )

ALTER TABLE `test`.`user`

ADD COLUMN `realname` VARCHAR(45) NULL AFTER `password`;

mysql> desc user;

+----------+-------------+------+-----+---------+---------

-------+

| Field | Type | Null | Key | Default | Extra

|

+----------+-------------+------+-----+---------+---------

-------+

| id | int | NO | PRI | NULL |

auto_increment |

| username | varchar(45) | NO | UNI | NULL |

| 修改列名

| password | varchar(45) | NO | | NULL |

|

| realname | varchar(45) | YES | | NULL |

|

+----------+-------------+------+-----+---------+---------

-------+

4 rows in set (0.01 sec)

mysql> ALTER TABLE `test`.`user`

-> CHANGE COLUMN `realname` `zsxm` VARCHAR(45) NULL

DEFAULT NULL ;

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc user;

+----------+-------------+------+-----+---------+---------

-------+

| Field | Type | Null | Key | Default | Extra

|

+----------+-------------+------+-----+---------+---------

-------+

| id | int | NO | PRI | NULL |

auto_increment |

| username | varchar(45) | NO | UNI | NULL |

|

| password | varchar(45) | NO | | NULL |

|

| zsxm | varchar(45) | YES | | NULL |

|

+----------+-------------+------+-----+---------+---------

-------+ 删除列

5. 其他

3. 记录的操作

1. 新增

mysql > ALTER TABLE `test` . `user`

-> DROP COLUMN `zsxm` ;

Query OK, 0 rows affected ( 0.01 sec )

Records: 0 Duplicates: 0 Warnings : 0

mysql > desc user ;

+----------+-------------+------+-----+---------+---------

-------+

| Field | Type | Null | Key | Default | Extra

|

+----------+-------------+------+-----+---------+---------

-------+

| id | int | NO | PRI | NULL |

auto_increment |

| username | varchar ( 45 ) | NO | UNI | NULL |

|

| password | varchar ( 45 ) | NO | | NULL |

|

+----------+-------------+------+-----+---------+---------

-------+

3 rows in set ( 0.00 sec )

#insert into 表名 ( 字段名列表 ) values ( 字段值列表 )

mysql > select * from user ;

Empty set ( 0.00 sec )

mysql > insert into user ( id,username, password ) values

( 3 , 'zhangsan' , 'zhangsan' ) ; Query OK, 1 row affected ( 0.01 sec )

mysql > select * from user ;

+----+----------+----------+

| id | username | password |

+----+----------+----------+

| 3 | zhangsan | zhangsan |

+----+----------+----------+

1 row in set ( 0.00 sec )

mysql > select * from user ;

+----+----------+----------+

| id | username | password |

+----+----------+----------+

| 3 | zhangsan | zhangsan |

+----+----------+----------+

1 row in set ( 0.00 sec )

mysql > insert into

user ( username, password ) values ( 'lisi' , 'lisi' ) ;

Query OK, 1 row affected ( 0.00 sec )

mysql > select * from user ;

+----+----------+----------+

| id | username | password |

+----+----------+----------+

| 3 | zhangsan | zhangsan |

| 4 | lisi | lisi |

+----+----------+----------+

2 rows in set ( 0.00 sec )

mysql > desc user ;

+----------+-------------+------+-----+---------+------

----------+

| Field | Type | Null | Key | Default | Extra

| +----------+-------------+------+-----+---------+------

----------+

| id | int | NO | PRI | NULL |

auto_increment |

| username | varchar ( 45 ) | NO | UNI | NULL |

|

| password | varchar ( 45 ) | NO | | NULL |

|

+----------+-------------+------+-----+---------+------

----------+

3 rows in set ( 0.00 sec )

2. 删除

delte from user where 条件 ;

mysql > delete from user where username = 'zhangsan' ;

Query OK, 1 row affected ( 0.00 sec )

mysql > select * from user ;

+----+----------+----------+

| id | username | password |

+----+----------+----------+

| 4 | lisi | lisi |

+----+----------+----------+

1 row in set ( 0.00 sec )

mysql > delete from user ;

Query OK, 1 row affected ( 0.00 sec ) mysql > select * from user ;

Empty set ( 0.00 sec )

3. 修改

mysql >

mysql > insert into user values

( 1 , 'zhangsan' , 'zhangsan' ) ;

Query OK, 1 row affected ( 0.00 sec )

mysql > insert into user values ( 2 , 'lisi' , 'lisi' ) ;

Query OK, 1 row affected ( 0.00 sec )

mysql > insert into user values ( 3 , 'wangwu' , 'wangwu' ) ;

Query OK, 1 row affected ( 0.00 sec )

mysql > select * from user ;

+----+----------+----------+

| id | username | password |

+----+----------+----------+

| 1 | zhangsan | zhangsan |

| 2 | lisi | lisi |

| 3 | wangwu | wangwu |

+----+----------+----------+

3 rows in set ( 0.00 sec )

mysql > update user set password = 123 where

username = 'zhangsan' ;

Query OK, 1 row affected ( 0.01 sec )

Rows matched: 1 Changed : 1 Warnings : 0

mysql > select * from user ;

+----+----------+----------+

| id | username | password |

+----+----------+----------+

| 1 | zhangsan | 123 | | 2 | lisi | lisi |

| 3 | wangwu | wangwu |

+----+----------+----------+

3 rows in set ( 0.00 sec )

4. 查询