MySQL数据库简介和增删改查1

一、数据库的基本概念
二、操作MySQL

1.1 基本概念：数据、表、数据库
数据：
●描述事物的符号记录
●包括数字，文字、图形、图像、声音、档案记录等
●以“记录”形式按统一的格式进行存储
表：
●将不同的记录组织在一起
●用来存储具体数据
数据库：
●表的集合，是存储数据的仓库
●以一定的组织方式存储的相互有关的数据集合
数据库管理系统(DBMS)：
●是实现对数据库资源有效组织、管理和存取的系统软件
数据库系统：
●是一个人机系统，由硬件、oS、数据库、DBMS、应用软件和数据库用户组成
●用户可以通过DBMS或应用程序操作数据库

1.2 关系型数据库：
●关系数据库系统是基于关系模型的数据库系统
●关系模型的数据结构使用简单易懂的二维数据表
●关系模型可用简单的“实体-关系”(E-R)图来表示
●E-R图中包含了实体(数据对象)、关系和属性三个要素

1.3 关系型数据库：
●关系数据库的存储结构是二维表格
●在每个二维表中：
每一行称为一条记录，用来描述一个对象的信息
每一列称为一个字段，用来描述对象的一个属性

1.4 非关系型数据库：
●非关系数据库也被称作NosQL (Not Only[SQL)
●存储数据不以关系模型为依据，不需要固定的表格式
●非关系型数据库的优点
（1）数据库可高并发读写
（2）对海量数据高效率存储与访问
（3）数据库具有高扩展性与高可用性

1.5 常见数据库：
●关系型：MySQL、Oracle（例如淘宝账号，银行用户账号）
●非关系型：Redis(内存、缓存型数据库，可以做持久化保存)、maogodb(芒果)、memcached、postgresql(PG)

1.6 mysql和oracle日志区别：
mysql：写一条数据,同步到日志中一条
oracle :重做日志组
一个组中至少3个日志成员，轮流存储日志还会有另一个组与之同步/备份

1.7 Mysql存储引擎
myisam 和innodb
myisam:快速读取,不支持事务
innodb:更注重写,支持事务

2.1 操作数据库：

数据库基本操作：
[root@www mysql]# mysql -u root -p123    #登录数据库p后面接密码
mysql> show databases;    #显示所有数据库
mysql> use mysql;       #进入某数据库
mysql> show tables;     #进入某数据库后查看表
mysql> describe user;    #查看usr该表的结构（在当前数据库）
DESCRIBE [数据库名.] 表名    #查看某个表的结构（不在当前数据库）

数据表的结构解释：
Filed    字段名称
Type     数据类型
Null     是否允许为空
Key      主键
Default  默认值
Extra    扩展属性，例如标志符列（标识了种子，增量/步长）

常用的数据类型解释：
int       整形（定义整数类型的数据）
float     单精度浮点4字节32位（准确表示到小数点后6位）
double    双精度浮点8字节64位（准确表示到小数点后12位）
char      固定长度的字符类型（用于定义字符类型数据），例如手机号char(11)
varchar   可变长度的字符类型
test      文本
image     图片
decimal(5,2)  5个有效长度数字，小数点后两位 #指定长度数组(小数点后遵循四舍五入)
#Char如果存入数据的实际长度比指定长度要小，会补空格至指定长度，如果存入的数据的实际长度大于指定长度，低版本会被截取,高版本会报错

SQL语句概述：
SQL语言：
●Structured Query Language的缩写，即结构化查询语言
●关系型数据库的标准语言
●用于维护管理数据库
（1）包括数据查询、数据更新、访问控制、对象管理等功能（增删改查 ）
SQL分类：
●DDL:数据定义语言
●DML:数据操纵语言
●DQL:数据查询语言
●DCL:数据控制语言

DDL:数据定义语言：创建删除库，表，索引

模拟创建一个数据库，数据表，及表内容
mysql> create database school;   #创建新的数据库school

mysql> create table ky11 (id int not null,name varchar(20) not null,score decimal(5,2)  not null,age int(5),address varchar(50) default 'ky11',primary key(id));

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| ky11             |
+------------------+
1 row in set (0.00 sec)

mysql> desc ky11;
+---------+--------------+------+-----+---------+-------+
| 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(5)       | YES  |     | NULL    |       |
| address | varchar(50)  | YES  |     | ky11    |       |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> drop table ky11;    #删除此表
mysql> drop table 库名.表名;   #不进入此库删除某数据表

DML:管理表中的数据记录：
insert:插入新数据
update: 更新原有数据
delete :删除不需要的数据

insert插入数据示例：
mysql> insert into ky11 (id,name,score,age,address) values(1,'lisi',89.122,20,'江苏');     #在表中插入数据
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from ky11;       #查看插入表中的数据
+----+------+-------+------+---------+
| id | name | score | age  | address |
+----+------+-------+------+---------+
|  1 | lisi | 89.12 |   20 | 江苏    |
+----+------+-------+------+---------+
1 row in set (0.00 sec)
mysql> insert into ky11 values(2,'zhangsan',29,20,'上海'),(3,'wangwu',30,28,'北京');    #在表中连续插入多条数据
mysql> select * from ky11;

+----+----------+-------+------+---------+
| id | name     | score | age  | address |
+----+----------+-------+------+---------+
|  1 | lisi     | 89.12 |   20 | 江苏    |
|  2 | zhangsan | 29.00 |   20 | 上海    |
|  3 | wangwu   | 30.00 |   28 | 北京    |
+----+----------+-------+------+---------+
3 rows in set (0.00 sec)

插入密码示例方法1：
mysql> create table ky12 (id int not null,name varchar(10) not null,score decimal(5,2))  not null,passwd char(48) DEFAULT'',primary key(id));   #建表加入密码选项
mysql> desc ky12;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id     | int(11)      | NO   | PRI | NULL    |       |
| name   | varchar(10)  | NO   |     | NULL    |       |
| score  | decimal(5,2) | NO   |     | NULL    |       |
| passwd | char(48)     | YES  |     |         |       |
+--------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> insert into ky12 values(1,'zhaoliu',20,'123');
Query OK, 1 row affected (0.00 sec)    #表中插入一条数据
mysql> select * from ky12;      
+----+---------+-------+--------+
| id | name    | score | passwd |
+----+---------+-------+--------+
|  1 | zhaoliu | 20.00 | 123    |
+----+---------+-------+--------+
1 row in set (0.01 sec)
插入密码示例方法2：
mysql> insert into ky12 values(2,'zhaoliu',20,password('123'));
Query OK, 1 row affected, 1 warning (0.00 sec)    #密码加密
mysql> desc ky12;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id     | int(11)      | NO   | PRI | NULL    |       |
| name   | varchar(10)  | NO   |     | NULL    |       |
| score  | decimal(5,2) | NO   |     | NULL    |       |
| passwd | char(48)     | YES  |     |         |       |
+--------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> select * from ky12;
+----+---------+-------+-------------------------------------------+
| id | name    | score | passwd                                    |
+----+---------+-------+-------------------------------------------+
|  1 | zhaoliu | 20.00 | 123                                       |
|  2 | zhaoliu | 20.00 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |   #加密密码可作为原设置密码使用
+----+---------+-------+-------------------------------------------+
2 rows in set (0.00 sec)

update更新原有数据示例：
update 表名 set 字段名1='字段值1',字段名2='字段值2' [where 条件表达式];
mysql> select * from ky12;
+----+---------+-------+-------------------------------------------+
| id | name    | score | passwd                                    |
+----+---------+-------+-------------------------------------------+
|  1 | zhaoliu | 20.00 | 123                                       |
|  2 | zhaoliu | 20.00 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+----+---------+-------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql> update ky12 set name='zhangsan' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from ky12;
+----+----------+-------+-------------------------------------------+
| id | name     | score | passwd                                    |
+----+----------+-------+-------------------------------------------+
|  1 | zhangsan | 20.00 | 123                                       |
|  2 | zhaoliu  | 20.00 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+----+----------+-------+-------------------------------------------+
2 rows in set (0.00 sec)

delete:删除不需要的数据（表内容）示例：
格式：delete from 表名 [where 条件表达式]
mysql> select * from ky12;
+----+----------+-------+-------------------------------------------+
| id | name     | score | passwd                                    |
+----+----------+-------+-------------------------------------------+
|  1 | zhangsan | 20.00 | 123                                       |
|  2 | zhaoliu  | 20.00 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+----+----------+-------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql> delete from ky12 where id='2';   #删除该行
Query OK, 1 row affected (0.00 sec)

mysql> select * from ky12;
+----+----------+-------+--------+
| id | name     | score | passwd |
+----+----------+-------+--------+
|  1 | zhangsan | 20.00 | 123    |
+----+----------+-------+--------+
1 row in set (0.00 sec)

DQL查询数据记录：

 格式：select 字段名1，字段名2，..... from 表名; 
 mysql> select * from ky11;
+----+----------+-------+------+---------+
| id | name     | score | age  | address |
+----+----------+-------+------+---------+
|  1 | lisi     | 89.12 |   20 | 江苏    |
|  2 | zhangsan | 29.00 |   20 | 上海    |
|  3 | wangwu   | 30.00 |   28 | 北京    |
+----+----------+-------+------+---------+
3 rows in set (0.00 sec)
mysql> select id,name from ky11;    #只查看id和name字段
mysql> select id,name from ky11 where score>=30; #查看id和name字段，且要求分数大于等于30
mysql> select *  from ky11 limit 2;  #查看含字段名的行共三行
mysql> select *  from ky11 limit 1,2; #查看id为1的行的下面两行

DCL:数据控制语言

alter:修改表名和表结构
修改表名格式：alter table 旧表名 rename 新表名;
mysql> alter table ky11 rename ky1111;   #改成新名字ky1111
增加表结构字段格式：alter table 表名 add 需要增加的字段 (数据类型及其他);

修改字段(列)名格式：alter table 表名 change 旧列名 新列名 数据类型 [unique key];
unique key:唯一键(特点：唯一，可以为空，空值只允许出现一次)
change可以修改字段名，数据类型，约束等所有项
例:
mysql> alter table ky11 change address address111 varchar(50) unique key default '地址不详';

删除字段格式：alter table 表名 drop 字段名;
mysql> alter table ky11 drop address;

扩展：检测要创建的表是否己存在，如果不存在就继续创建
mysql> create table if not exists info(id int(4) zerofill primary key  auto_increment not null,name varchar(20),cardid int(18) not null unique key,hobby varchar(50) not nuull);
Query OK, 0 rows affected (0.02 sec)

mysql> desc info
+--------+--------------------------+------+-----+---------+----------------+
| Field  | Type                     | Null | Key | Default | Extra          |
+--------+--------------------------+------+-----+---------+----------------+
| id     | int(4) unsigned zerofill | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)              | YES  |     | NULL    |                |
| cardid | int(18)                  | NO   | UNI | NULL    |                |
| hobby  | varchar(50)              | NO   |     | NULL    |                |
+--------+--------------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)


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

复制克隆等操作：
create table 新表名 like 旧表名   #复制数据表格式
insert into 新表名 select * 旧表名;    #备份内容
create table 新表名 (select * from 旧表名) #克隆数据表及内容不包含主键等信息
show create table 表名\G   #获取数据表的结构，索引等信息

删除数据操作：
方法1：delete from 表名
#DELETE清空表后，返回的结果内有删除的记录条目; DELETE 工作时是一行一行的删除记录数据的;如果表中有自增长字段，使用DELETE FROM删除所有记录后，再次新添加的记录会从原来最大的记录ID后面继续自增写入记录。
方法2：truncate table 表名
#TRUNCATE清空表后，没有返回被删除的条目:TRUNCATE 工作时是将表结构按原样重新建立,因此在速度上TRUNCATE会比DELETE清空表快;使用TRUNCATE TABLE清空表内数据后，id会从1开始重新记录

删除符号drop，truncate，delete区别总结：
drop table table_name
1)属于DDL
2)不可回滚（无法恢复)
3)不可带where
4)表内容和结构删除
5)删除速度快
truncate table table_name
1)属于DDL
2)不可回滚
3)不可带where
4)表内容删除
5)删除速度快
delete from table_name
1)属于DML
2)可回滚
3)可带where
4)表结构在,表内容要看where执行的情况
5)删除速度慢，需要逐行删除

小结：不再需要一张表的时候，用drop想删除部分数据行时候，用delete，并且带上where子句保留表而删除所有数据的时候用truncate
速度：drop>truncate>delete
安全性：delete最好

创建临时表：create temporary table 表名
临时表创建成功之后，使用SHOwTABEES命令是看不到创建的临时表的，临时表会在连接退出后被销毁。
如果在退出连接之前，也可以可执行增删改查等操作，比如使用DROP TABLE语句手动直接删除临时表。