学习笔记

mysql 数据库

关系型数据库：存储数据的格式跟我们excel表格很像，存储数据非常友好

Oracle==》收费的、大型

非关系型数据库：

hbase==》大数据

Redis==》key

[size=14.0000pt] 安装数据库mysql

yum install==》在线下载并安装

yum install mysql==》下载mysql客户端

yum install mysql-server==》下载mysql服务端

rpm -qa|grep -i mysql==》产看是否安装了数据库

[root@192 /]# rpm -qa|grep -i mysql

perl-DBD-MySQL-4.013-3.el6.i686

mysql-libs-5.1.73-8.el6_8.i686

mysql-server-5.1.73-8.el6_8.i686

mysql-5.1.73-8.el6_8.i686

[root@192 /]# ==》正确安装

[size=14.0000pt] mysql服务启动关闭重启和状态查询

备注：mysql后面的这个d代表的是一个守护进程daemon

[size=10.5000pt] service mysqld start==》启动数据库，以后每次进入数据库都需要启动数据库

[root@192 /]# service mysqld start

Starting mysqld: [ OK ]

[root@192 /]#

[size=10.5000pt] service mysqld stop==》关闭数据库

[root@192 /]# service mysqld stop

Stopping mysqld: [ OK ]

[root@192 /]#

[size=10.5000pt] service mysqld status==》查询数据库状态

[root@192 /]# service mysqld status

mysqld is stopped

[root@192 /]#

[root@192 /]# service mysqld status

mysqld (pid 6757) is running...

[root@192 /]#

备注：pid 6757代表的是数据库的进程号

[size=10.5000pt] service mysqld restart==》重启数据库

[root@192 /]# service mysqld restart

Stopping mysqld: [ OK ]

Starting mysqld: [ OK ]

[root@192 /]#

[size=10.5000pt] netstat -nltp==》查看所有进程（重点记忆此命令！！！！！）

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 6757/mysqld

tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1856/sshd

tcp 0 0 127.0.0.1:631 0.0.0.0:* LISTEN 1740/cupsd

tcp 0 0 127.0.0.1:6010 0.0.0.0:* LISTEN 5596/sshd

tcp 0 0 127.0.0.1:6011 0.0.0.0:* LISTEN 6165/sshd

tcp 0 0 :::22 :::* LISTEN 1856/sshd

tcp 0 0 ::1:631 :::* LISTEN 1740/cupsd

tcp 0 0 ::1:6010 :::* LISTEN 5596/sshd

tcp 0 0 ::1:6011 :::* LISTEN 6165/sshd

[root@192 /]#

登录mysql数据库==》第一次登录时不需要密码

[size=10.5000pt] mysql -uroot -p==》登录进入mysql数据库

-u代表用户user的意思，root时用户

-p代表密码password的意思

我们默认mysql中有个用户时root

[root@192 /]# mysql -uroot -p

Enter password: （第一次安装数据库，不需要密码）

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

[root@192 /]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.1.73 Source distribution

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

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

mysql>

[size=10.5000pt] show databases;==》查看数据库中的所有库，里面有3个库

备注：数据库中所有的指令后面都需要加一个“；”，“；”是数据库的默认格式

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |（不能删除这三个系统库）

| test |

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

3 rows in set (0.00 sec)

mysql>

退出数据库==》①Ctrl+c退出②exit

mysql> exit

Bye

[root@192 /]#

mysqladmin -uroot password “123456”==》设置数据库账号和密码==（不是在mysql数据库里面输入）

[root@192 /]#mysqladmin -uroot password “123456”

mysql -uroot -p

数据库密码：

1）跳过权限不输入密码进入mysql

编辑vim /etc/my.cnf，在socket=/var下面这行输入

i模式输入：skip-grant-tables ：wq保存

service mysqld restart==》重启数据库

[size=14.0000pt] 创建数据库

[size=10.5000pt] create database dcs1;==》创建一个新的数据库名称为dcs1

备注：不能创建dcs1数据库，dcs1数据库已存在

[size=10.5000pt] drop database dcs1;==》删除数据库dcs1（千万不要删除默认的系统库）

[size=10.5000pt] use dcs1;==》切换和进入数据库

mysql> use dcs1

Database changed（显示进入成功）

mysql>

[size=10.5000pt] select database();==》查看当前已经进入到哪个数据库中

mysql> select database();

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

| database() |

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

| dcs1 |

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

1 row in set (0.00 sec)

mysql>

[size=10.5000pt] use mysql;==》从dcs1数据库进入到mysql数据库

mysql> use mysql;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select database();

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

| database() |

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

| mysql |

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

1 row in set (0.00 sec)

mysql>

create table test......==》进入dcs1数据库创建一个test表格

mysql> use dcs1

Database changed

mysql> create table test (id int(20)primary key auto_increment,score float(20,2)not null,name varchar(20),phone bigint(20)default 18611112222,time date);

Query OK, 0 rows affected (0.03 sec)

mysql>

[size=10.5000pt] desc test1;==》查看表结构

Field==》字段

type==》数据类型

null==》是否可以为空

key==》primary key主键

default==》默认值约束

Extra==》额外的备注，auto_increment自增长约束

数据库中常用的数据类型：

数据型：

int==》最大存储值2147483647（最大存储值为10位）

bigint==》手机号码都只能用bigint来存储（长整型）

float==》score|float（20，2），2是精度，代表的是几位数（符点型后面可以有小数）

文本型：【varchar和char存的数据需要加单引号或者双引号】

varchar==》存字符串，字符位数多一些（长字符型）

char==》存字符，单个字符

日期型：【date存的数据需要加单引号或者双引号】

date==》"2021-11-30"

数据库中常用的约束有哪些？

not null==》非空约束

primary key==》主键约束，里面的值必须是唯一的，不能重复

default==》默认值约束，若不输入值，字段永远是18611112222

auto_increment==》自增长约束

foreign key==》外键约束

【对于表结构的操作】

[size=10.5000pt] show tables;==》查看当前库dcs1中所有表格

mysql> select database();

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

| database() |

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

| dcs1 |

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

1 row in set (0.00 sec)

mysql> show tables;

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

| Tables_in_dcs1 |

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

| test |

| test1 |

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

2 rows in set (0.00 sec)

mysql>

[size=10.5000pt] alter table test rename test2;==》把test表格名称修改为test2

mysql> alter table test rename test2;

Query OK, 0 rows affected (0.00 sec)

mysql> show tables;

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

| Tables_in_dcs1 |

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

| test1 |

| test2 |

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

2 rows in set (0.00 sec)

mysql>

[size=10.5000pt] alter table test change id sid int(20);==》把id字段修改为sid，并且去掉了自增长

mysql> alter table test change id sid int(20);

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc test;

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

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

| sid | int(20) | NO | PRI | 0 | |

| phone | bigint(20) | YES | | 18611112222 | |

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

5 rows in set (0.00 sec)

mysql>

[size=10.5000pt] alter table test change sid id int(20) auto_increment;==》把sid字段修改为id，并且加上自增长约束

mysql> alter table test change sid id int(20) auto_increment;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc test;

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

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

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

| phone | bigint(20) | YES | | 18611112222 | |

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

5 rows in set (0.00 sec)

mysql>

[size=10.5000pt] alter table test add class int(20) first;==》添加一个字段在最前面

mysql> alter table test add class int(20) first;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc test;

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

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

| class | int(20) | YES | | NULL | |

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

| phone | bigint(20) | YES | | 18611112222 | |

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

6 rows in set (0.01 sec)

mysql>

[size=10.5000pt] alter table test add sex int(20) after id;==》添加sex字段到id后面

mysql> alter table test add sex int(20) after id;

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc test;

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

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

| class | int(20) | YES | | NULL | |

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

| sex | int(20) | YES | | NULL | |

| phone | bigint(20) | YES | | 18611112222 | |

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

7 rows in set (0.00 sec)

mysql>

[size=10.5000pt] alter table test add(age1 int(20),age2 int(20));==》添加两个字段，只能添加到最后面，不能添加到最前面

mysql> alter table test add(age1 int(20),age2 int(20));

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc test;

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

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

| class | int(20) | YES | | NULL | |

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

| sex | int(20) | YES | | NULL | |

| phone | bigint(20) | YES | | 18611112222 | |

| age1 | int(20) | YES | | NULL | |

| age2 | int(20) | YES | | NULL | |

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

9 rows in set (0.00 sec)

mysql>

[size=10.5000pt] alter table test modify class int(20) after id;==》调整class字段到id字段后面

mysql> alter table test modify class int(20) after id;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc test;

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

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

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

| class | int(20) | YES | | NULL | |

| sex | int(20) | YES | | NULL | |

| phone | bigint(20) | YES | | 18611112222 | |

| age1 | int(20) | YES | | NULL | |

| age2 | int(20) | YES | | NULL | |

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

9 rows in set (0.01 sec)

mysql>

[size=10.5000pt] alter table test drop sex;==》删除sex字段

mysql> alter table test drop sex;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc test;

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

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

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

| class | int(20) | YES | | NULL | |

| phone | bigint(20) | YES | | 18611112222 | |

| age1 | int(20) | YES | | NULL | |

| age2 | int(20) | YES | | NULL | |

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

8 rows in set (0.00 sec)

mysql>

[size=10.5000pt] alter table test drop age1,drop age2;==》同时删除age1和age2两个字段

mysql> alter table test drop age1,drop age2;

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc test;

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

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

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

| class | int(20) | YES | | NULL | |

| phone | bigint(20) | YES | | 18611112222 | |

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

6 rows in set (0.00 sec)

mysql>

[size=10.5000pt] alter table test change id id int(20);==》删除自增长（auto_increment）且不改变字段名称

mysql> alter table test change id id int(20);

Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc test;

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

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

| id | int(20) | NO | PRI | 0 | |

| class | int(20) | YES | | NULL | |

| phone | bigint(20) | YES | | 18611112222 | |

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

6 rows in set (0.00 sec)

mysql>

[size=10.5000pt] alter table test change id id int(20)auto_increment;==》不改变字段名称，增加自增长

mysql> alter table test change id id int(20)auto_increment;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc test;

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

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

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

| class | int(20) | YES | | NULL | |

| phone | bigint(20) | YES | | 18611112222 | |

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

6 rows in set (0.00 sec)

mysql>

[size=10.5000pt] alter table test drop primary key;==》删除主键（自增长约束时结合主键去使用的）

备注：当想要删除主键约束时，一定要先删除自增长约束后，再删除主键约束

【对于表中数据的操作】：

[size=10.5000pt] 单个数据插入：

方法1：insert into test(id,score,name,phone,time)values(1,89.99,"amy",18651887777,"2021-11-30");==》插入单个数据

mysql> insert into test(id,score,name,phone,time)values(1,89.99,"amy",18651887777,"2021-11-30");

Query OK, 1 row affected (0.00 sec)

mysql> select * from test;

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

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

| 1 | 89.99 | amy | 18651887777 | 2021-11-30 |

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

1 row in set (0.00 sec)

mysql>

方法2：insert into test values(2,78.66,"amy1",18851995678,"2021-11-30");==》插入单个数据

mysql> insert into test values(2,78.66,"amy1",18851995678,"2021-11-30");

Query OK, 1 row affected (0.00 sec)

mysql> select * from test;

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

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

| 1 | 89.99 | amy | 18651887777 | 2021-11-30 |

| 2 | 78.66 | amy1 | 18851995678 | 2021-11-30 |

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

2 rows in set (0.00 sec)

mysql>

多个数据的插入：

insert into test(id,score,name,phone,time)values(3,66.666,'xiaoliu2',13688889999,'2021-11-30'),(4,78.89,'xiaoliu3',13666668888,'2021-11-30'); ==》插入两条数据

insert into test values(5,100,'xiaoxie',default,'2021-11-30'),(6,99.99,'xiaoshi',13688889999,'2021-11-30'),(7,68.99,'xiaozhang',18966669999,'2021-11-30'); ==》插入三条数据

备注： 0 不等于null, null 指的是一个空的属性，0 代表的是一个值

指定字段插入数据

insert into test(id)values(8); ==》帮id字段插入值

insert into test(score)values(88.88); ==》把score插入值的时候id字段对应的值会自动加1，因为设置了自增长

int bigint

alter table test change phone phone int(20) not null; ==》把phone数据类型修改为int且加上not null约束

所有的手机号都改为：2147483647

insert into test(phone)values(2147483648); ==》还是显示2147483647

insert into test(phone)values(2147483646); ==》正常显示 2147483646