mysql数据库配置和表的创建

一、 配置mysql和创建表
1、配置mysql
[[email protected] ~]# groupadd mysql
创建管理mysql账户
[[email protected] ~]# useradd -M -s /sbin/nologin -g mysql mysql
安装nginx依赖软件
[[email protected] ~]# yum -y install ncurses-devel
解压cmake依赖程序
[[email protected] ~]# tar zxvf /mnt/cmake-2.8.6.tar.gz -C /usr/src
配置安装cmake
[[email protected] cmake-2.8.6]# ./configure && make && make install
[[email protected] ~]# tar zxvf mysql-5.6.36.tar.gz -C /usr/src/
进入mysql目录
[[email protected] ~]# cd /usr/src/mysql-5.6.36/
配置mysql
[[email protected] mysql-5.6.36]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DSYSCONFDIR=/etc
安装mysql
[[email protected]生成mysql配置文件ntos01 mysql-5.6.36]# make && make install
生成mysql配置文件
[[email protected] mysql-5.6.36]# cp support-files/my-default.cnf /etc/my.cnf
配置服务控制文件
[[email protected] mysql-5.6.36]# cp support-files/mysql.server /etc/init.d/mysqld
[[email protected] ~]# chmod +x /etc/init.d/mysqld
[[email protected] ~]# chkconfig --add mysqld
[[email protected] ~]# chkconfig --level 35 mysqld on
优化命令
[[email protected] ~]# vim /etc/profile
PATH=$PATH://usr/local/mysql/bin
[[email protected] ~]# source /etc/profile
初始化mysql
[[email protected] ~]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
[[email protected] ~]# chown -R mysql:mysql /usr/local/mysql/
启动和设置开机自启服务
[[email protected] ~]# systemctl start mysqld
[[email protected] ~]# systemctl enable mysqld
设置密码
[[email protected] ~]# mysqladmin -uroot password
New password:
Confirm new password:
进入mysql数据库
[[email protected] ~]# mysql -uroot [email protected]
查看mysql数据库状态

创建数据库
mysql> create database benet;
查看数据库

切换数据库
mysql> use benet;
Database changed
创建表的结构
create table benet.IT_salary (岗位类别 char(32) not null,姓名 char(16) not null,年龄 tinyint not null,员工ID smallint not null,学历 char(16) default ‘大专’,年限 tinyint not null,薪资 int not null,primary key (员工ID));
查看表的结构

插入数据
mysql> insert into benet.IT_salary (岗位类别,姓名,年龄,员工ID,学历,年限,薪资) values (‘网络工程师’,‘孙 空武’,‘2
Query OK, 1 row affected (0.03 sec)
mysql> insert into benet.IT_salary (岗位类别,姓名,年龄,员工ID,学历,年限,薪资) values (‘Windows工程师’,‘蓝凌’,‘19’,‘012’,’ 中专’,‘2’,‘3500’);
Query OK, 1 row affected (0.01 sec
mysql> insert into benet.IT_salary (岗位类别,姓名,年龄,员工ID,学历,年限,薪资) values (‘liunx工程师’,‘姜纹’,‘32’,‘013’,’ 本科’,‘8’,‘15000’);
Query OK, 1 row affected (0.01 sec
mysql> insert into benet.IT_salary (岗位类别,姓名,年龄,员工ID,学历,年限,薪资) values (‘java工程师’,‘关园’,‘38’,‘014’,’ 大专’,‘10’,‘16000’);
Query OK, 1 row affected (0.01 sec
mysql> insert into benet.IT_salary (岗位类别,姓名,年龄,员工ID,学历,年限,薪资) values (‘硬件驱动工程师’,‘罗中昆’,‘29’,‘015’,’ 大专’,‘9’,‘16500’);
Query OK, 1 row affected (0.01 sec
查看表

创建表
mysql> create table benet.users (姓名 char(16) not null,密码 char(48) default ‘’,primary key (姓名));
Query OK, 0 rows affected (0.01 sec)
查看表的结构

插入数据第二列设置为密文密码
mysql> insert into benet.users values(‘aaa’,password(‘123’));
Query OK, 1 row affected (0.01 sec)
查看表

创建明文用户密码
mysql> insert into benet.users (姓名,密码) values (‘张三’,‘123’);
Query OK, 1 row affected (0.00 sec)
查看表

修改user表中姓名张三的密码
mysql> update benet.users set 密码=password(‘abc’) where 姓名=‘张三’;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
查看

修改用户张三为明文密码
mysql> update benet.users set 密码=(‘abc’) where 姓名=‘张三’;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0