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
[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)
项目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
[root@localhost 桌面]# mysql
mysql> set password for root@"localhost"=password("123");
mysql> quit;
[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; //删除表
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 |
+------+-----------+------------+----------+---------------------+
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 |