第六章 2.Mysql数据库练习

数据库安装

1.直接通过Yum 源，安装MariaDB。

[root@localhost ~]# yum install mariadb-server.x86_64 -y
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
Resolving Dependencies
                                             

Complete!
[root@localhost ~]# 

2.服务管理

[root@localhost ~]# systemctl list-unit-files | grep mariadb.service
mariadb.service                               disabled
[root@localhost ~]# systemctl start mariadb.service 
[root@localhost ~]# systemctl enable mariadb.service
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
[root@localhost ~]# netstat -anptul | grep 3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      3470/mysqld         
[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> 

3.密码修改MySQL 数据库用户root 密码为root

[root@localhost ~]# mysqladmin -u root -p password "root"
Enter password:         // 直接回车，不需要输入内容
[root@localhost ~]#

4.登录MySQL

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

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> 

mysql 数据库操作

1.显示所有数据库

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> 

2.创建一个名为bbs 的数据库，编码方式为utf8

MariaDB [(none)]> create database bbs default character set utf8;
Query OK, 1 row affected (0.00 sec)

3.使用bbs 数据库

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

4.查看库中所有表名

MariaDB [bbs]> show tables;
Empty set (0.00 sec)

MariaDB [bbs]> 

5.创建一个名为user 的表，

其中包含id、username、password、image_path 列，id 为主键

MariaDB [bbs]> create table user(
    -> id int(10) not null auto_increment primary key, 
    -> username varchar(255) not null, 
    -> password varchar(255) not null, 
    -> image_path varchar(255)
    -> );
Query OK, 0 rows affected (0.00 sec)

MariaDB [bbs]>

6.查看user 表的定义

MariaDB [bbs]> desc user;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(10)      | NO   | PRI | NULL    | auto_increment |
| username   | varchar(255) | NO   |     | NULL    |                |
| password   | varchar(255) | NO   |     | NULL    |                |
| image_path | varchar(255) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

MariaDB [bbs]>

7.创建一个名为message 的表，

其中包含id、uid、title、content，id 为主键

create table message (
id int(10) not null auto_increment primary key, 
uid int(10) not null, 
title varchar(255) not null, 
content varchar(255) not null);

MariaDB [bbs]> create table message (
    -> id int(10) not null auto_increment primary key, 
    -> uid int(10) not null, 
    -> title varchar(255) not null, 
    -> content varchar(255) not null);
Query OK, 0 rows affected (0.00 sec)

MariaDB [bbs]> 

8.创建第一个用户ajest，密码123456

MariaDB [bbs]> insert into user( username, password) values( 'ajest', '123456');
Query OK, 1 row affected (0.00 sec)

9.查询user 表中用户

MariaDB [bbs]> select * from user;
+----+----------+----------+------------+
| id | username | password | image_path |
+----+----------+----------+------------+
|  1 | ajest    | 123456   | NULL       |
+----+----------+----------+------------+
1 row in set (0.00 sec)

MariaDB [bbs]> 

10.批量创建用户root、admin 和你的名字，密码统一为123456

MariaDB [bbs]> insert into user( username, password) values( 'root', '123456'), ( 'admin', '123456');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [bbs]> 

插入自己的名字

MariaDB [bbs]> insert into user( username, password) values( 'wj', '123456');
Query OK, 1 row affected (0.00 sec)

【拓展】批量插入留言

insert into message(uid,title,content)values
(1,"Hello","My Name is AJEST"),
(2,"OK?","Hi,AJEST!How are you?"),
(1,"ok!","I'm fine!Thank you!"),
(3,"Hello","My Name is admin!");

MariaDB [bbs]> insert into message(uid,title,content)values
    -> (1,"Hello","My Name is AJEST"),
    -> (2,"OK?","Hi,AJEST!How are you?"),
    -> (1,"ok!","I'm fine!Thank you!"),
    -> (3,"Hello","My Name is admin!");
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [bbs]>