mysql数据库项目

mysql数据库项目

如果对运维课程感兴趣，可以在b站上、csdn或微信视频号 上搜索我的账号： 运维实战课程，可以关注我，学习更多免费的运维实战技术视频

项目1  

安装系统自带mysql服务,修改登录mysql数据库的默认登录密码，并在登录mysql数据库后对库的操作：查看当前已有库、查看当前所在库、创建库、切换库、删除库

1）安装mysql服务mysql登录命令和启动mysql服务：

[root@localhost 桌面]# yum -y install mysql-server mysql

[root@localhost 桌面]# /etc/init.d/mysqld start

2）修改登录mysql数据库服务的默认登录密码：

[root@localhost 桌面]# mysql

mysql> set password for root@"localhost"=password("123");

mysql> quit;

3登录mysql数据库服务器并对库进行操作：（查看、创建、切换、删除）

[root@localhost 桌面]# mysql -uroot -p123

mysql> show databases;               //查看当前已有的库

mysql> create database ku;           //创建数据库，库名为(ku)

mysql> use ku;                       //切换到这个库（ku）

mysql> select database();            //查看当前所在哪个库里面

mysql> drop database ku;             //删除这个数据库（ku）

mysql> quit;                         //退出数据库

项目2 mysql数据库 字符类型 和 数值类型 赋值的建表

安装系统自带mysql服务,修改登录mysql数据库的默认登录密码，登录mysql数据库后创建一个数据库(库名：ku)，在该数据库（ku）中，进行如下表的操作：查看库中的所有表、创建表、查看表结构、向表里添加插入数据、查看表中所有内容、查看表中指定字段的内容、删除表中指定字段内容、删除表中所有字段内容、删除表。

1）安装mysql服务mysql登录命令和启动mysql服务：

[root@localhost 桌面]# yum -y install mysql-server mysql

[root@localhost 桌面]# /etc/init.d/mysqld start

2）修改登录mysql数据库服务的默认登录密码：

[root@localhost 桌面]# mysql

mysql> set password for root@"localhost"=password("123");

mysql> quit;

3登录mysql数据库服务器并创建数据库（库名：ku）

[root@localhost 桌面]# mysql -uroot -p123

mysql> show databases;               //查看当前已有的库

mysql> create database ku;           //创建数据库，库名为(ku)

mysql> use ku;                       //切换到这个库（ku）

mysql> show tables;                  //查看该库中所有的表名（现在为空）

mysql> create table biao (name varchar(5),age int(2),sex enum("boy","girl"));  //建一个3个字段的表

mysql> desc biao;                                                    //查看表结构

mysql> insert into biao values ("tom",25,"boy");                     //向表里插入数据1

mysql> insert into biao values ("lucy",25,"girl"); //向表里插入数据2

mysql> select * from biao;                                           //查看表中所有内容

mysql> select * from biao where name="lucy";                         //查看表中指定字段的内容

mysql> delete from biao where name="tom";                            //删除表中指定字段的内容

mysql> delete from biao;                                             //删除表中所有字段的内容

mysql> drop table biao;                                              //删除表

项目3  mysql数据库日期类型赋值的建表

mysql> create database ku;

mysql> use ku;

mysql> create table biao (

    -> name varchar(5),

    -> start_day year,

    -> birthday date,

    -> up_class time,

    -> meeting datetime

-> );

mysql> desc biao;

mysql> insert into biao values ("shi",1988,19881009,090000,20160112170000);   //给各字段赋值

mysql> select * from biao;

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

| name | start_day | birthday   | up_class | meeting             |

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

| shi  |      1988 | 1988-10-09 | 09:00:00 | 2016-01-12 17:00:00 |

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

项目4  mysql数据库枚举类型赋值的建表

mysql> create table biao (

    -> name varchar(5),

    -> sex enum("girl","boy","no"),                               // enum():选择列举范围内一个

    -> loves set("game","film","book","music")                   //set():选择列举范围内一个或多个

    -> );

mysql> insert into biao values ("tom","boy","game,film,music");

mysql> select * from biao;

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

| name | sex  | loves           |

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

| tom  | boy  | game,film,music |

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

项目5  登录mysql数据库服务器中获取系统的时间

mysql> select (now());                                                   //获取最全的时间和日期

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

| (now())             |

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

| 2016-01-16 04:28:10 |

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

mysql> select year(now());                                               //仅获取年

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

| year(now()) |

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

|        2016 |

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

mysql> select month(now());                                              //仅获取月

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

| month(now()) |

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

|            1 |

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

mysql> select day(now());                                                //仅获取日

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

| day(now()) |

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

|         16 |

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

mysql> select time(now());                                               //仅获取时间

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

| time(now()) |

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

| 04:29:13    |

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

项目6  索引主键与auto_increment 连用，使字段自动增长，每次都自加1。

（字段自动增长，前提是必须是主键且是整数类型）

mysql> create table biao (

    -> id int(2) primary key auto_increment,

    -> age int(3),

    -> name varchar(5)

-> );

mysql> desc biao;

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

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

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

| id    | int(2)     | NO   | PRI | NULL    | auto_increment |

| age   | int(3)     | YES  |     | NULL    |                |

| name  | varchar(5) | YES  |     | NULL    |                |

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

mysql> insert into biao (age,name) values(26,"tom");

mysql> select * from biao;

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

| id | age  | name |

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

|  1 |   26 | tom  |

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

mysql> insert into biao (age,name) values(26,"jim");

mysql> select * from biao;

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

| id | age  | name |

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

|  1 |   26 | tom  |

|  2 |   26 | jim  |

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

mysql> insert into biao (age,name) values(25,"lucy");

mysql> select * from biao;

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

| id | age  | name |

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

|  1 |   26 | tom  |

|  2 |   26 | jim  |

|  3 |   25 | lucy |

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

项目7 统计函数: 参数和括号中间不能有空格

将/etc/passwd文件内容到数据库中的ku.biao中，然后统计uid字段各记录的集合中的平均值、求和值、最小值、最大值，统计uid字段和username字段各记录的值的总个数，并查看表时按uid升序、或降序排列查看。

mysql> create table ku.biao (

    -> username varchar(5),

    -> passwd_wei varchar(5),

    -> uid int(2),

    -> gid int(2),

    -> comment varchar(5),

    -> jiamulu varchar(5),

    -> shell varchar(5)

-> );

mysql> load data infile "/etc/passwd" into table biao fields terminated by ":" lines terminated by "\n";

mysql> select avg(uid) from ku.biao;      //uid字段各记录的集合中的平均值

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

| avg(uid) |

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

|  88.8065 |

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

mysql> select sum(uid) from ku.biao      // uid字段各记录的集合中的求和值

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

| sum(uid) |

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

|     2753 |

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

mysql> select min(uid) from ku.biao     // uid字段各记录的集合中的最小值

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

| min(uid) |

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

|        0 |

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

mysql> select max(uid) from ku.biao   // uid字段各记录的集合中的最大值

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

| max(uid) |

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

|      499 |

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

mysql> select count(username),count(uid) from ku.biao  //统计uid字段和username字段各记录的值的总个数.

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

| count(username) | count(uid) |

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

|              31 |         31 |

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

mysql> select * from ku.biao  order by uid asc;             //按照uid升序排列查看

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

| username | passwd_wei | uid  | gid  | comment | jiamulu | shell |

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

| root     | x          |    0 |    0 | root    | /root   | /bin/ |

| bin      | x          |    1 |    1 | bin     | /bin    | /sbin |

| daemo    | x          |    2 |    2 | daemo   | /sbin   | /sbin |

| adm      | x          |    3 |    4 | adm     | /var/   | /sbin |

| lp       | x          |    4 |    7 | lp      | /var/   | /sbin |

| sync     | x          |    5 |    0 | sync    | /sbin   | /bin/ |

| shutd    | x          |    6 |    0 | shutd   | /sbin   | /sbin |

| halt     | x          |    7 |    0 | halt    | /sbin   | /sbin |