MySql数据库的初步安装与数据表结构数据管理

---------------安装环境包-----------------
 
确认安装 gcc 、 gcc-c++ 、make、cmake 
ncurses-devel、（字符终端屏幕控制基本库）
bison、（语法分析器）
libaio-devel （支持同步I/O）的软件包
yum -y install gcc gcc-c++ make ncurses ncurses-devel bison cmake libaio-devel
yum install autoconf -y     ####安装5.6版本是需要安装autoconf库
 
---------------添 加mysql用户并加入到mysql组----------------
 
useradd  -s /sbin/nologin mysql
mkdir -p /usr/local/mysql
 
tar zxvf mysql-5.5.24.tar.gz -C /opt/
 
cd   mysql-5.5.24
 
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
 -DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_DATADIR=/home/mysql \
-DMYSQL_USER=mysql \
-DMYSQL_TCP_PORT=3306                               
 
指定安装路径
指定家目录  sock 数据库连接文件
指定字符集
指定字符编码
扩展性的字符集 支持其他的字符集
开启各种功能的存储引擎机
存储引擎机
存储引擎机
支持readline程序平台 读取数据按行读取，一行是一个对象
本地写入文件，方便导入导出
数据库的家目录  数据库存放的路径
指定管理用户和端口号
 
make -j 2 && make install  
 
------如果报错，清空缓存文件-如果正常可忽略-------
find -name CMakeCache.txt
rm -f ./CMakeCache.txt                          
---------------------------------
 
chown -R mysql:mysql /usr/local/mysql     
 
 
 
export PATH=$PATH:/usr/local/mysql/bin/   \*开机时刷新*\          
 
或者可选择vi /etc/profile 在最后一行加入后 运行source /etc/profile 
 
cp support-files/my-medium.cnf /etc/my.cnf                  
或
cp support-files/my-default.cnf /etc/my.cnf
 
cp support-files/mysql.server /etc/init.d/mysqld              
 
chmod 755 /etc/init.d/mysqld                             
chkconfig --add /etc/init.d/mysqld                      
chkconfig mysqld --level 35 on                                  
 
--------------初始化数据库------------------------------------------------
/usr/local/mysql/scripts/mysql_install_db \
--user=mysql \
--ldata=/var/lib/mysql \
--basedir=/usr/local/mysql \
--datadir=/home/mysql
 
ln -s /var/lib/mysql/mysql.sock /home/mysql/mysql.sock           /*直接建立软连接*/
 
vi /etc/init.d/mysqld      #####46行和47行添加
basedir=/usr/local/mysql
datadir=/home/mysql
 
service mysqld start
netstat -tnl 3306
mysql    #####初始登录mysql
grant all privileges on *.* to 'root'@'%' identified by 'abc123';    ### ##授予root用户可以在所有终端远程登录，使用的密码是abc123，并对所有数据库和所有表有操作权限。
 
 
#数据库密码设置
mysqladmin -u root password "abc123"
---------------------------------------------------------------
mysql -u root  -p  
abc123
mysql>show databases;

2）yum安装 mysql5.7

1. #创建mysql安装包的在线源
 cd /etc/yum.repos.d/
 vim mysql.repo
 [mysql]
 name=mysql5.7
 baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-x86_64/
 gpgcheck=0
  
 2. #安装服务mysql-community-server
 yum -y install mysql-community-server
  
 3. #启动服务
 systemctl start mysqld
 ss -ntap |grep 3306
  
 4. #此时无法使用mysql命令直接登入数据库。需要查看本地root用户的默认密码，使用密码登录。
 grep password /var/log/mysqld.log    #查看默认密码
 mysql -u root -p'34/Jeoiq0fZb'       #使用密码登录，特殊符号用单引号引起来
  
 5. #登入数据库后必须修改密码。
 mysql> alter user root@'localhost' identified by 'abc123';
 
 #为保证安全，密码的复杂性要足够，需要包含英文字母大小写、数字和特殊字符。
 
 #可以使用"\h;"查看帮助

数据库当前使用的端口查看：

show global variables like 'port';

三、mysql的数据类型

1）常用的数据库类型

类型名称	含义
tinyint(n)	1个字节，范围(-128~127)
smallint(n)	2个字节，范围(-32768~32767)
mediumint(n)	3个字节，范围(-8388608~8388607)
int(n)	4个字节(32个比特位)，整数型，范围(-2147483648~2147483647)
bigint(n)	*8个字节，整数型，范围(+-9.2210的18次方)**
float(m,d)	单精度浮点，8位精度，4字节32位。m数字总个数，d小数位
double(m,d)	双精度浮点，16位精度，8字节64位。m总个数，d小数位
char	固定长度的字符类型
varchar	可变长度的字符类型
text	文本
image	图片
decimal(5,2)	5个有效长度数字，小数点后面有2位（例如123.56）

2）char与varchar的区别

值	CHAR(4)	存储需求	VARCHAR(4)	存储需求
''	' '	4个字节	''	1个字节
'ab'	'ab '	4个字节	'ab '	3个字节
'abcd'	'abcd'	4个字节	'abcd'	5个字节
'abcdefgh'	'abcd'	4个字节	'abcd'	5个字节

CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

字节大小：

char无论是否有值，都会占用固定长度的字节大小，保存在磁盘上都是4字节
varchar在保存字符时，默认会加一个隐藏的结束符，因此结束符会多算一个字节

优劣比较：

varchar比char节省磁盘空间
但varchar类型的数据读写速度比char慢，因为char是连续的磁盘空间，e而varchar在多次增删改查中会产生一些磁盘空间碎片

四、查看数据库结构

1）查看当前服务器中的数据库

SHOW DATABASES;                        #大小写不区分，分号“;”表示结束

2）查看数据库中包含的表

方法一：

USE 数据库名;
SHOW TABLES;

方法二：

show tables  from 库名

3）查看表的结构（字段）

方式一：先切换库，在查看指定表的字段

 USE  数据库名;      #切换库
 DESCRIBE 表名;     #查看指定表的结构（  DESCRIBE 可以缩写为DESC）

由于屏幕占比问题与字段数量问题，会导致有些表结构的效果呈现并不好。（由于默认查看为横向的查看）

纵向查看方式：

desc  表名\G;

方式二：直接查看表结构

 DESCRIBE [数据库名.]表名;
 可缩写成：DESC 数据库名.表名;

四、SQL语句

SQL语句用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理等功能

1）SQL中的名词介绍

数据库：database
表：table，行：row 列：column
索引：index
视图：view
存储过程：procedure
存储函数：function
触发器：trigger
事件调度器：event scheduler，任务计划
用户：user
权限：privilege

2）SQL语言规范

在数据库系统中，SQL 语句不区分大小写，建议用大写
SQL语句可单行或多行书写，默认以 " ; " 结尾
关键词不能跨多行或简写
用空格和TAB 缩进来提高语句的可读性
子句通常位于独立行，便于编辑，提高可读性

数据库对象和命名：

数据库的组件(对象)：

数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等。

命名规则：

必须以字母开头，后续可以包括字母，数字和三个特殊字符（# _ $）
不要使用MySQL的保留字，如table、select、show、databases。
数据库名、表名、用户名区分大小

3）SQL语言分类

1、DDL：数据定义语言（Data Definition Language），用于创建数据库对象，如库、表、索引等

例如：CREATE，DROP，ALTER 等。

2、DML：数据操纵语言（Data Manipulation Language），用于对表中的数据进行管理

例如： SELECT、UPDATE、INSERT、DELETE 等。

3、DQL：数据查询语言（ Data Query Languag ），用于从数据表中查找符合条件的数据记录

例如： SELECT

4、DCL：数据控制语言（Data Control Language），用于设置或者更改数据库用户或角色权限

例如： GRANT，REVOKE

5、TCL：事务控制语言（Transaction Control Language），用于管理数据库中的事务。 TCL经常被用于快速原型开发、脚本编程、GUI和测试等方面

例如： COMMIT，ROLLBACK，SAVEPOINT

五、Mysql数据库基本的增删改查命令

1）创建数据库

CREATE DATABASE 数据库名;

2）创建表

 CREATE TABLE 表名(字段1 数据类型,字段2 数据类型[,...] [,PRIMARY KEY (主键名)]);
#主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键。

例：

CREATE DATABASE school;
USE school;
CREATE TABLE student (id int NOT NULL,name char(10) NOT NULL,score decimal(5,2),passwd char(48) DEFAULT '',PRIMARY KEY (id));

DESC student;

3）删除指定的数据表

DROP TABLE [数据库名.]表名;                #如不用USE进入库中，则需加上数据库名

4）删除指定的数据库

DROP DATABASE 数据库名;

六、管理表中数据记录

1）向数据表中插入新的数据记录

INSERT INTO 表名(字段1,字段2[,...]) VALUES(字段1的值,字段2的值,...);

例：

INSERT INTO student (id,name,score,passwd) values(1,'ggl',70.5,PASSWORD('123456'));
#PASSWORD('123456')：查询数据记录时，密码字串以加密形式显示；若不使用PASSWORD()，查询时以明文显示。

INSERT INTO student VALUES(2,'lisi',90.5,654321);

SELECT * FROM student;                    #查询表的数据记录

2）查询数据记录

SELECT 字段名1,字段名2[,...] FROM 表名 [WHERE 条件表达式];

例：

SELECT * FROM student;
SELECT id,name,score FROM student WHERE id=2;

select name from YYDS\G                    #以列表方式竖向显示
select * from YYDS limit 2;                #只显示头2行
select * from YYDS limit 2,3;              #显示第2行后的前3行

3）修改、更新数据表中的数据记录

UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 条件表达式];

例：

UPDATE YYDS SET passwd=PASSWORD('') WHERE name='zhangsan';
UPDATE YYDS SET name='qhw',passwd='' WHERE id=3;

4）在数据表中删除指定的数据记录

DELETE FROM 表名 [WHERE 条件表达式];

例：

DELETE FROM YYDS WHERE id=4;

七、修改表名和表结构

1）修改表名

ALTER TABLE 旧表名 RENAME 新表名;

例：

ALTER TABLE YYDS RENAME AWSL;
ALTER TABLE AWSL RENAME YYDS;

2）扩展表结构（增加字段）

ALTER TABLE 表名 ADD address varchar(50) default '地址不详';
#default '地址不详'：表示此字段设置默认值 地址不详；可与 NOT NULL 配合使用

3）修改字段（列）名，添加唯一键

ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [unique key];

例：

ALTER TABLE KY08 CHANGE name user_name varchar(10) unique key;
#CHANGE可修改字段名、数据类型、约束等所有项。

4）删除字段

ALTER TABLE 表名 DROP 字段名;

例：

ALTER TABLE YYDS DROP passwd;

案例扩展：

use kgc;
create table if not exists info (
id int(4) zerofill primary key auto_increment,                #指定主键的第二种方式
name varchar(10) not null default '匿名',
cardid int(18) not null unique key,
hobby varchar(50));

#if not exists：表示检测要创建的表是否已存在，如果不存在就继续创建
#int(4) zerofill：表示若数值不满4位数，则前面用“0”填充，例0001
#auto_increment：表示此字段为自增长字段，即每条记录自动递增1，默认从1开始递增；自增长字段数据不可以重复；自增长字段必须是主键；如添加的记录数据没有指定此字段的值且添加失败也会自动递增一次
#unique key：表示此字段唯一键约束，此字段数据不可以重复；一张表中只能有一个主键, 但是一张表中可以有多个唯一键
#not null：表示此字段不允许为NULL

创建方式与创建表完全一致，最大的区别是，临时表只在当前的数据库连接生效，当数据库连接断开或重新连接终端时，该临时表均失效，无法查看

5）复制表结构

方式一：创建新表，导入数据

 create table 新表名 like 旧表名;             #通过LIKE方法，复制旧表的结构生成新表
 
 insert into 新表名 select * from 旧表名;     #再将旧表数据导入新表

方式二：复制表

 create table 新表名 (select * from 旧表名);

6）临时表的创建

CREATE TEMPORARY TABLE 表名(字段1 数据类型,字段2 数据类型[, ...] [, PRIMARY KEY (主键名)]);

创建方式与创建表完全一致，最大的区别是，临时表只在当前的数据库连接生效，当数据库连接断开或重新连接终端时，该临时表均失效，无法查看

7）清除表的两种方式比较

方式一：delete清空

 delete from 表名;

方式二：truncate清空

 truncate table 表名;

两者区别：

delete清空属于按照行清空，一行一行进行，效率低，速度慢（对于表数据较多的情况） truncate相当于删除表数据，保留表结构，重新创建一个与原表结构相同的表
当数据表中有自增型约束字段时，delete清除表数据后，在插入数据时，自增字段的数值会接着被删除表数据的最后一个自增数，计数。（eg：被删除表数据最后一行id（自增），为10，再创建时，该id从11开始）。truncate清空后，自增型字段会从1开始计数

七、Mysql中常见的约束

主键约束（primary key） PK

主键具有唯一性，不可重复性，不可为空的特点。一般用于删除，查询时条件语句的条件。主键在一张表中只能有一个

自增长约束（auto_increment）

该约束针对于整数类型的字段，当插入该字段数据为空时，系统会根据上一个数，自动加1

非空约束（not null）

该约束到字段后，该字段中的数据插入不能为空

唯一性约束（unique）

该约束添加后，该字段的数据每行均为唯一的，不可重复。唯一性约束可以在一张表中有多个

默认值约束（default）

默认值约束，为该字段设置初始值，如果没有为该字段插入数据，就为初始值

零填充约束（zerofill）

零填充针对整数型的字段，例如int默认为11位，除了填写的数字以外其他用零填充

外键约束（foreign key）FK

保证数据的完整性和一致性

外键的定义：如果同一个属性字段x在表一中是主键，而在表二中不是主键，则字段x称为表二的外键

主键表与外键表的理解：

（1）以公共关键字作主键的表为主键表（父表、主表）

（2）以公共关键字作为外键的表为外键表（从表、外表）

与外键关联的子表的字段必须设置为主键。要求主表外键字段和子表的字段具备相同的数据类型、字符长度和约束

专业课程表

课程ID	课程名称
1	云计算
2	大数据
3	JAVA
4	前端
主键

学生信息表

学号	姓名	年龄	课程ID
1	zhangsan	18	1
2	lisi	20	2
3	wangwu	22	4
4	zhaoliu	23	1
5	zhuba	24	2
			外键

#为主表设置主键
mysql> alter table class add constraint PK_CID primary key(cid);

#主表插入记录
mysql> insert into class values(1,'云计算');
mysql> insert into class values(2,'大数据');
mysql> insert into class values(3,'java');
mysql> insert into class values(4,'前端');

#为从表设置外键
mysql> alter table student ADD constraint FK_CLASSID foreign key(classid) references class(cid);

#从表插入记录
mysql> insert into student values(1,'zhangsan',18,1);
mysql> insert into student values(2,'lisi',20,2);
mysql> insert into student values(3,'wangwu',22,4);
mysql> insert into student values(4,'zhaoliu',23,1);
mysql> insert into student values(5,'zhuba',24,2);

外键约束，至少将两张表进行关联。我们将这两张表分为主表和从表，主表的主键作为从表的外键。增加数据时，先进行主表操作，如果增加的数据外键值在主表中没有，就要先在主表中添加。
若进行删除操作，就要先进行从表操作，只要将从表有关主表（主键值）全部清除后，才能清除该主表的主键值

删除外键步骤：

1.查看外键别名
mysql> show create table student\G;
2.删除外键约束
mysql> alter table student drop foreign key FK_CLASSID;
3.删除别名
mysql> alter table student drop key FK_CLASSID;

做了外键约束后

要插入新的数据记录时，需要先在主键表中插入相关数据，才能在外键表插入相关数据，插入数据时，是先主后从

要删除数据记录时，要先删除外键表的相关数据，才能在主键表删除相关数据记录，删除数据时，是先从再主

总结

create database 库名;
use 库名;
create table 表名 (字段1 数据类型, 字段2 数据类型, ... [,primary key (字段)]);

drop table 表名;
drop database 库名;

show databases;
show tables [from 库名];
desc [库名.]表名;



DML 用于管理表数据

insert into 表名 values (字段1的值, 字段2的值, ....);    #要按照表结构的字段顺序设置值
insert into 表名 (字段1, 字段2, ...) values (字段1的值, 字段2的值, ...);

update 表名 set 字段=值, ... where 条件表达式;

delete from 表名 where 条件表达式;



DQL 用于根据条件查询表数据

select * from 表名 [where 条件表达式];
select 字段1,字段2,... from 表名 [where 条件表达式];

select * from 表名\G                #纵向查看每行记录字段的值

select * from 表名 limit N;       #显示表的前N行记录
select * from 表名 limit N,M;   #显示表的从第N行之后的连续M行记录（不包含第N行）


修改表结构

改表名      alter table 旧表名 rename 新表名;
增加字段   alter table 表名 add 新字段 数据类型 [字段属性];
修改字段   alter table 表名 change 旧字段 新字段 数据类型 [字段属性];
删除字段   alter table 表名 drop 字段;