MySQL数据库——增、删、改、查
数据库的基本概念
数据:描述事物的符号记录,以记录形式按统一格式进行存储数据(数字,文字,图形图像,声音,档案记录等)
表:由不同的记录和字段组成,用来存储具体的数据(数据库中记录为行,字段为列)
数据库:表的集合组成数据库,以一定的组织方式存储的相互有关的数据集合
数据库管理系统(DBMS):是实现对数据库资源的有效组织、管理和存取的系统软件
数据库系统:是一个人机系统,有硬件、OS、数据库、DBMS、应用软件和数据库用户组成,用户可以通过DBMS或应用程序操作数据库
数据库的特点:
(1)可以结构化存储大量数据信息,方便用户进行有效的检索和访问
(2)可以有效地保持数据信息的一致性、完整性,降低数据冗余
(3)可以满足应用的共享和安全方面的要求
数据库发展:20世纪60年代IBM公司研制大的第一代数据库——20世纪70年代第二代数据库关系型数据库,如:DB2,MySQL,——20世纪80年代三代数据库“关系-对象”数据库,如:Oracle
主流数据库
SQL Server(微软公司产品):面向Windows操作系统,简单、易用
Oracle(甲骨文公司产品):面向所有主流平台,安全、完善、操作复杂
DB2(IBM公司产品):面向所有主流平台,大型、安全、完善
MySQL(甲骨文公司收购):免费、开源、体积小
关系数据库
关系型数据库是基于关系模型的数据库系统,关系模型的数据结构使用简单易懂的二维数据表,其中主要包含了实体、关系、属性三要数
实体:对应现实世界中的“事件”或“事物”
关系;实体之间的对应联系
属性:描述一个实体的详细信息
非关系型数据库
非关系型数据库也称为nosql,它存储数据不以关系模型为依据,不需要固定的表格式,常用的有:Redis,mongoDB等
优点:
(1)数据库可高并发读写
(2)对海量数据高效率存储与访问
(3)数据库具有扩展性和高可用性
数据库日志
数据库日志文件用于备份,恢复,是最核心的部分
MySQL与oracle日志有所区别
MySQL写一条数据,同步到日志中一条
Oracle重做日志组;一个组中至少3个日志成员,轮流存储日志,还会有另一个组与之同步/备份
比如:
阿里云对象存储:存储大都是一些静态文件,图片、音频、视频,不能直接在对象存储中修改数据
飞天平台:使用盘古系统,钟馗:安全;女娲:选举/调度;盘古一式三份
MySQL:存储引擎;分类
myisam和innodb 两种引擎
myisam:快速读取,不支持事务
innodb:更注重写,支持事务
安装MySQL
[root@mysql opt]# tar zxf mysql-boost-5.7.20.tar.gz
[root@mysql opt]# yum install -y install ncurses ncurses-devel bison cmake
[root@mysql opt]# cd mysql-5.7.20/
[root@mysql mysql-5.7.20]# cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DSYSCONFDIR=/etc \
-DSYSTEMD_PID_DIR=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DWITH_BOOST=boost \
-DWITH_SYSTEMD=1
[root@mysql mysql-5.7.20]# useradd -s /sbin/nologin mysql
[root@mysql mysql-5.7.20]# make && make install
[root@mysql ~]# chown -R mysql:mysql /usr/local/mysql/
[root@mysql ~]# vim /etc/my.cnf
[client]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock
[mysql]
port = 3306 #对应的端口
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock
[mysqld] #mysql服务配置
user = mysql
basedir = /usr/local/mysql #工作目录
datadir = /usr/local/mysql/data #数据文件目录
port = 3306
character_set_server=utf8 #对应中文字符集
pid-file = /usr/local/mysql/mysqld.pid #PID文件位置
socket = /usr/local/mysql/mysql.sock #通讯文件
server-id = 1 #服务id,在之后的mysql集群中用于标识mysql服务器
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES
[root@mysql ~]# chown mysql:mysql /etc/my.cnf
[root@mysql ~]# echo "PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH" >> /etc/profile
[root@mysql ~]# echo "export PATH" >> /etc/profile
[root@mysql ~]# source /etc/profile
[root@mysql ~]# cd /usr/local/mysql
[root@mysql mysql]# bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data #初始化数据库
[root@mysql mysql]# cp usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/
[root@mysql mysql]# systemctl start mysqld.service
[root@mysql mysql]# netstat -antp | grep 3306
tcp6 0 0 :::3306 :::* LISTEN 106734/mysqld
[root@mysql mysql]# mysqladmin -u root -p password #创建root用户数据库密码
Enter password: #第一次没有密码直接回车
New password: #输入新的密码
Confirm new password: #再次输入新密码
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety
进入数据库
[root@mysql mysql]# mysqladmin -uroot -p123456
mysql> show databases; #查看当前MySQL中包含额库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> use mysql; #进入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> show tables; #查看数据库中的表
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
mysql> describe user; #查看user表中的结构
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
……………………………………
基本命令
use mysql; ##进入数据库
show tables; ##查看表
describe [数据库名.]表名 ##查询非当前数据库内的表的结构
describe 表名 ##查询当前数据库内的表的结构
describe可以简写desc
字段解析
Field:字段名称
Type:数据类型
Null:是否允许为空
Key:主键
Default:默认值
Extra:拓展属性,如:标志弗列(表示了种子,增量/步长)
常用的数据类型Type
int :整型, 用于定义整数类型的数据
float :单精度浮点4字节32位 用于表示小数点后六位
double :双精度浮点8字节64位
char :固定长度的字符类型 用于定义字符类型数据, 如手机号码11位,char(11)
varchar :可变长度的字符类型
text :文本
image :图片
decimal(5,2) :5个有效长度数字,小数点后面2位
SQL是目前数据库的主要语法
DDL:数据定义语言,用于创建数据库对象,如库、表、索引等
DML:数据操纵语言,用于对表中的数据进行管理
DQL:数据查询语言,用于从数据表中查找符合条件的数据记录
DCL:数据控制语言,用于设置或者更改数据库用户或角色权限
创建新的数据库
mysql> create database zsx ; #新建数据库
Query OK, 1 row affected (0.01 sec)
mysql> show databases; #查看库是否创建成功过
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zsx |
+--------------------+
5 rows in set (0.00 sec)
mysql> use whd;
mysql> show tables; #新建的库中没有表
Empty set (0.00 sec)
mysql> create table zsx1 (id int not null,name varchar(20) not null,score decimal(5,2) not null,age int(4),address varchar(50) default 'nanjing',primary key(id));
#### 添加表
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
Empty set (0.00 sec)
+---------------+
| Tables_in_zsx |
+---------------+
| zsx1 |
+---------------+
1 row in set (0.00 sec)
mysql> desc zsx1; #查看新建表
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
| score | decimal(5,2) | NO | | NULL | |
| age | int(4) | YES | | NULL | |
| address | varchar(50) | YES | | nanjing | |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> drop table zsx1; #删除表
Query OK, 0 rows affected (0.01 sec)
mysql> show tables; #查看结果
新表插入数据
mysql> create table zsx1 (id int not null,name varchar(20) not null,score decimal(5,2) not null,age int(4),address varchar(50) default 'nanjing',primary key(id));
Query OK, 0 rows affected (0.00 sec)
mysql> select * from zsx1; #查看表
Empty set (0.00 sec)
mysql> insert into zsx1 (id,name,score,age,address) values(1,'lisi',87.222,20,'shanghai');
####在表中插入数据,注意插入中主键有唯一性,不可设置重复冲突
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from zsx1;
+----+------+-------+------+----------+
| id | name | score | age | address |
+----+------+-------+------+----------+
| 1 | lisi | 87.22 | 20 | shanghai |
+----+------+-------+------+----------+
1 row in set (0.00 sec)
修改数据记录
mysql> create table zsx1 (id int not null,name varchar(10) not null,score int(4),address varchar(40),primary key(id));
mysql> desc zsx1;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(10) | NO | | NULL | |
| score | int(4) | YES | | NULL | |
| address | varchar(40) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> insert into zsx1 values(1,'lisi',40,'上海'),(2,'wangwu',50,'杭州'); #可同时插入两条记录
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from zsx1;
+----+--------+-------+---------+
| id | name | score | address |
+----+--------+-------+---------+
| 1 | lisi | 40 | 上海 |
| 2 | wangwu | 50 | 杭州 |
+----+--------+-------+---------+
2 rows in set (0.00 sec)
mysql> update zsx1 set score=99 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from zsx1; #查看验证结果
+----+--------+-------+---------+
| id | name | score | address |
+----+--------+-------+---------+
| 1 | lisi | 99 | 上海 |
| 2 | wangwu | 50 | 杭州 |
+----+--------+-------+---------+
2 rows in set (0.00 sec)
UPDATE 表明 set 字段名=新字段值 【,字段名2=新字段值】 where 条件表达式
删除数据记录
DELETE FROM 表名 WHERE 条件表达式
truncate table 表名; #清除表
mysql> delete from zsx1 where id=2; #删除whd1表中id为2的记录
Query OK, 1 row affected (0.00 sec)
mysql> select * from whd1;
+----+------+-------+---------+
| id | name | score | address |
+----+------+-------+---------+
| 1 | lisi | 99 | 上海 |
+----+------+-------+---------+
1 row in set (0.00 sec)
查看表名
SELECT * FROM 表名;
SELECT 字段名 from 表名; #查看单独字段
SELECT 字段名,字段名 from 表名; #查看多个字段
SELECT name from 表名\G; #以列表方式竖向显示
SELECT * from 表名 info limit 2; #显示前三行,字段名的那一行为第一行
SELECT * from 表名 info limit 2,3; #显示从第二行往下的第三行
show create table test02\G #查看数据表的表机构、索引等消息
mysql> insert into zsx1 values (2,'zhansan',20,'杭州'),(3,'wangwu',20,'杭州'),(4,'xuyi',40,'南京'),(5,'whd',50,'湖北');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from zsx1;
+----+---------+-------+---------+
| id | name | score | address |
+----+---------+-------+---------+
| 1 | lisi | 99 | 上海 |
| 2 | zhansan | 20 | 杭州 |
| 3 | wangwu | 20 | 杭州 |
| 4 | xuyi | 40 | 南京 |
| 5 | whd | 50 | 湖北 |
+----+---------+-------+---------+
5 rows in set (0.00 sec)
mysql> select * from zsx1 info limit 1,2;
+----+---------+-------+---------+
| id | name | score | address |
+----+---------+-------+---------+
| 2 | zhansan | 20 | 杭州 |
| 3 | wangwu | 20 | 杭州 |
+----+---------+-------+---------+
2 rows in set (0.00 sec)
修改表名和表结构
ALTER TABLE 旧表名 RENAME 新表名; #修改表名
ALTER TABLE 表名 ADD 字段名 数据类型; #新增字段
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型 unique key; #修改字段名、数据类型、约束等
ALTER TABLE 表名 DROP 字段名 ; #删除字段
mysql> show tables;
+---------------+
| Tables_in_zsx |
+---------------+
| zsx1 |
| |
+---------------+
2 rows in set (0.00 sec)
mysql> alter table zsx1 rename zsx2; #修改表名
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_zsx |
+---------------+
| zsx2 |
| |
+---------------+
2 rows in set (0.00 sec)
mysql> alter table zsx6 add age int(2); #新增字段
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from zsx2;
+----+---------+-------+---------+------+
| id | name | score | address | age |
+----+---------+-------+---------+------+
| 1 | lisi | 99 | 上海 | NULL |
| 2 | zhansan | 50 | 杭州 | NULL |
| 3 | wangwu | 20 | 杭州 | NULL |
| 4 | xuyi | 40 | 南京 | NULL |
| 5 | zsx | 50 | 湖北 | NULL |
+----+---------+-------+---------+------+
5 rows in set (0.00 sec)
mysql> alter table zsx2 change score score_new varchar(3); #修改字段名
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from zsx2;
+----+---------+-----------+---------+------+
| id | name | score_new | address | age |
+----+---------+-----------+---------+------+
| 1 | lisi | 99 | 上海 | 20 |
| 2 | zhansan | 50 | 杭州 | NULL |
| 3 | wangwu | 20 | 杭州 | NULL |
| 4 | xuyi | 40 | 南京 | NULL |
| 5 | zsx | 50 | 湖北 | NULL |
+----+---------+-----------+---------+------+
5 rows in set (0.00 sec)
mysql> alter table zsx2 drop age; #删除字段
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from zsx2;
+----+---------+-----------+---------+
| id | name | score_new | address |
+----+---------+-----------+---------+
| 1 | lisi | 99 | 上海 |
| 2 | zhansan | 50 | 杭州 |
| 3 | wangwu | 20 | 杭州 |
| 4 | xuyi | 40 | 南京 |
| 5 | zsx | 50 | 湖北 |
+----+---------+-----------+---------+
5 rows in set (0.00 sec)
拓展
mysql> create table if not exists zsx6 (id int(4) zerofill primary key auto_increment,name varchar(10) not null,cardid int(40) not null unique key,hobby varchar(50));
Query OK, 0 rows affected (0.00 sec)
mysql> describe zsx6;
+--------+--------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------------+------+-----+---------+----------------+
| id | int(4) unsigned zerofill | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| cardid | int(40) | NO | UNI | NULL | |
| hobby | varchar(50) | YES | | NULL | |
+--------+--------------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
if not exists:表示检查创建的表是否存在,不存在就创建
int(4) zerofill :表示若数值不满4位数,则前面用0填充,如:0001
auto_increment :表示此字段为自增长值字段,每新增一条记录,自动递增1,自增长字段必须是主键
unique key :表示唯一键,一张表中只能有一个主键,但是可以有多个唯一键
删除类型小结
drop table table_name
- 属于DDL
- 不可回滚
- 不可带where
- 表内容和结构删除
- 删除速度快
truncate table table_name - 属于DDL
- 不可回滚
- 不可带where
- 表内容删除
- 删除速度快
delete from table_name
1、属于DML
2、可回滚(可恢复)
3、可带where
4、表结构在,表内容要看where执行的情况
5、删除速度慢,需要逐行删除