MySQL数据库管理

创建新的表

mysql> create database 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(40) default 'ky11',primary key(id));

mysql> desc ky11;

Field：字段名称
Type：数据类型
Null：是否允许为空
Key：主键
Default：默认值
Extra：扩展属性

数据库类型：关系型数据库、非关系型数据库

关系型数据库(SQL):存储的往往是字符、字符串、数值、布尔值等
非关系型数据库(NosQL）:存储的往往是图片、视频、语音等
时序数据库():心电图，持续性，根据时间点进行变化的一组连续性的数据(时序数据八)
关系型数据库:
Mysql (oracle公司)、 sQL server（微软) 、access(微软公司office产品)oracle（税务局)、DB2(IBM公司，国电），sybase (sybase）等等
非关系型数据库(NosQL):
MongoDB 、Redis （内存数据库/缓存数据库）K-v键值对、与之类似的Memcache ,K-v键值对redis-memecache对比:
相同点:存储高热数据（在内存中高速运行)
不同点: redis可以做持久化保存，可以存储对象

关系型数据库

实体-关系 E-R
记录（行）表示一个实体（的属性）
主键特性：唯一、非空

插入新数据

mysql> insert into ky11 (id,name,score,age,address) values(1,'zhangsan',70.133,30,'杭州');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from ky11

mysql> create table ky13 (id int not null,name char(10) not null,score decimal(5,2),passwd char(49) default'',primary key(id));
mysql> desc ky13;

mysql> insert into ky13 values(1,'lisi',20,'abc123');                                      
Query OK, 1 row affected (0.00 sec)
mysql> select * from ky13;

mysql> insert into ky13 values(2,'lisi',20,password('abc123'));
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from ky13;

更新原有数据

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

删除不需要的数据

mysql> delete from ky13 where id='2';
Query OK, 1 row affected (0.00 sec)
mysql> select * from ky13;

修改表名和表结构

mysql> alter table ky13 rename ky13_new;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;

扩展表结构（增加字段）

mysql> alter table ky11 add hobid int(5);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc ky11;

mysql> select * from ky11;

mysql> alter table ky11 change address address_new varchar(50) unique key default '地址不详';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> select * from ky11;

mysql> desc ky11;

删除字段

mysql> alter table ky11 drop hobid;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> select * from ky11;

清空表

mysql> delete from ky12;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from ky12;
Empty set (0.00 sec)

MySQL中6种常见的约束

主键约束(primary key)
外键约束（foreign key）
非空约束（not null）
唯一性约束（unique [key | index]）
默认值约束（default）
自增约束（auto_increment）
外键的定义:如果同一个属性字段x在表一中是主键，而在表二中不是主键，则字段x称为表二的外键。
(1)以公共关键字作主键的表为主键表(父表、主表)
(2)以公共关键字作外键的表为外键表(从表、外表)
注意:与外键关联的主表的字段必须设置为主键。要求从表不能是临时表，主表外键字段和从表的字段具备相同的数据类型、字符长度和约束。

创建主表和从表（test04/test05）

mysql> create table test04 (hobid int(4),hobname varchar(50));
Query OK, 0 rows affected (0.01 sec)
mysql> create table test05 (id int(4) primary key auto_increment,name varchar(10),age int(3),hobid int(4));
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;

mysql> alter table test04 add constraint PK_hobid primary key(hobid);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table test05 add constraint PK_hobid foreign key(hobid) references test04(hobid);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table test05;

mysql> desc test04;

mysql> desc test05;

插入新的数据记录，要先主表在从表

mysql> insert into test04 values(1,'runing');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test05 values(1,'lisi',20,1);
Query OK, 1 row affected (0.01 sec)

删除数据记录

mysql> drop tables test05;
Query OK, 0 rows affected (0.00 sec)
mysql> drop tables test04;
Query OK, 0 rows affected (0.00 sec)

新建用户

mysql> create user 'zhangsan'@'localhost' identified by '123123';
Query OK, 0 rows affected (0.01 sec)

mysql> select password('123123');

mysql> create user 'lisi'@'localhost' identified by password '*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1';
Query OK, 0 rows affected, 1 warning (0.00 sec)

查看用户信息

mysql> use mysql
Database changed
mysql> select user,authentication_string,host from user;

重命名指定

mysql> rename user 'zhangsan'@'localhost' to 'zhaoliu'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,authentication_string,host from user;

删除用户

mysql> drop user 'lisi'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,authentication_string,host from user;

修改当前登陆的用户密码

mysql> set password = password('1234');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> exit
Bye
[root@localhost ~]# mysql -uroot -p1234

修改其他用户密码

mysql> set password for 'zhaoliu'@'localhost' = password('12345');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select user,authentication_string,host from mysql.user;

数据库用户授权

mysql> grant select on school.* to 'zhangsan'@'%' identified by 'abc123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select user,authentication_string,host from mysql.user;

刷新权限

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> quit
Bye
[root@localhost ~]# mysql -u zhangsan -pabc123
mysql> show databases;

查看权限

mysql> show grants for 'zhangsan'@'%';

撤销权限

mysql> revoke all on *.* from 'zhangsan'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'zhangsan'@'%';

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'zhangsan'@'%';