ch2-数据管理

前言

​ 对数据库的管理是非常重要的,本文基于mysql8.0.

正文

帐号管理

• 创建新的账号

  create user ‘root’@‘localhost’ identified by ‘123456’;
  create user ‘root’@‘%’ identified by ‘123456’;
  

  "localhost"是指用户只能在本地登录, 远程登录是 “%”

• 账号设置新的密码

  alter user 'root'@'localhost' identified by '设置的新密码'；
  

• 查看账号信息

  use mysql
  select user,host from user;
  

• 删除账号 drop user ‘账号名’;

• 用户授权

#语法
GRANT privileges ON databasename.tablename TO 'username'@'host';

#privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL
#databasename：数据库名
#tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*

#授予用户指定数据库的指定表的所有权限
grant all privileges on 数据库名.表名 to '用户名'@'主机IP';

#授予用户服务器中所有数据库所有表的所有权限
grant all privileges on *.* to '用户名'@'主机IP'; 

• mysql 新设置用户或更改密码后需用flush privileges刷新MySQL的系统权限相关表，否则会出现拒绝访问

数据库管理

• show databases; 显示数据库列表
• create database ‘库名’；建立数据库
• drop database ‘库名’； 删除数据库
• use ‘库名’ ; 接下来操作对该库进行

数据表

• show tables; 查看该数据库里的所有表名

• create table [表名]（字段名 数据类型，-- – ，-- – ）；建立表格

  mysql> create table account(
  -> id int,
  -> name varchar(255)
  -> );
  

• 建立表格还可以用检索出来的数据作为数据源

• 数据类型(有的一定要标明size)

  

  

  

• drop table ‘表名’ ； 删除表格

• alter table ‘旧表名’ rename ‘新表名’ ； 修改表名

• describe ‘表名’ 或 desc ‘表名’ ； 显示数据表的结构,不检索数据

• alter table ‘表名’ add ‘字段名’ ‘数据类型’ ‘[not null]’ ‘[default]’ （后俩项为非必要项） 增加一列

  alter table account add weixin varchar(255);
  

• alter table ‘表名’ drop ‘字段名’； 删除一列

• alter table ‘表名’ change ‘旧字段名’ ‘新字段名’ ‘数据类型’ ； 修改字段名的信息

  alter table account change weixin qq varchar(255);
  

数据行

• insert into [表名] values(值1,值2,…) ；往表中插入数据( 值个数与字段个数相同)

  mysql> desc account;
  +-------+--------------+------+-----+---------+-------+
  | Field | Type | Null | Key | Default | Extra |
  +-------+--------------+------+-----+---------+-------+
  | id | int | YES | | NULL | |
  | name | varchar(255) | YES | | NULL | |
  | qq | varchar(255) | YES | | NULL | |
  +-------+--------------+------+-----+---------+-------+
  mysql> insert into account(id,
                             name,
                             qq)
      						
  values(1,
        'yuwenlong',
        '1589292300');
  

  最好明确指明列,给出列,即使表结构改变,也能继续发生作用, 还有不能插入同一条记录两次

• insert into [表名] （列1，列2…) values(值1,值2…) ;只往表中几列插入数据

  mysql> select * from account;
  +------+-----------+------------+
  | id | name | qq |
  +------+-----------+------------+
  | 1 | yuwenlong | 1589292300 |
  +------+-----------+------------+
  mysql> insert into account (id,name) values(2,'yyy');
  mysql> select * from account;
  +------+-----------+------------+
  | id | name | qq |
  +------+-----------+------------+
  | 1 | yuwenlong | 1589292300 |
  | 2 | yyy | NULL |
  +------+-----------+------------+
  

• update [表名] set [字段名] = xxx [where子句]；修改单列的数据信息，全靠where控制

• update [表名] set [字段名1] = xxx ,[字段名2] =xxx … [where子句]；修改多列的数据信息

  mysql> alter table account add mm int ;
  mysql> select * from account;
  +------+-------------+-------------+--------+------+
  | id | name | qq | gender | mm |
  +------+-------------+-------------+--------+------+
  | 1 | yuwenlong | 1589292300 | 1 | NULL |
  | 2 | wuchengxiao | 12345678 | 1 | NULL |
  | 3 | genziyue | 987654321 | 0 | NULL |
  | 3 | zhl | 123213123 | 0 | NULL |
  | 4 | zhouyang | 37427837847 | 1 | NULL |
  | 5 | ssss | 36261736 | 0 | NULL |
  | 6 | erw | 762377278 | 0 | NULL |
  | 7 | hsdhf | 3728338 | 1 | NULL |
  | 8 | sdfsd | 32477278 | 1 | NULL |
  | 9 | jsjdsj | 378282832 | 0 | NULL |
  | 10 | sdfs3we | 273878278 | 0 | NULL |
  | 11 | ehjrh32 | 636467 | 0 | NULL |
  | 12 | 3jjsjd | 37274728372 | 1 | NULL |
  | 13 | eyrwyu | 2367476237 | 1 | NULL |
  +------+-------------+-------------+--------+------+
  mysql> update account 
  		set mm = 1 
  		where gender=0;
  mysql> update account 
  		set mm =737 
  		where id>=6;
  mysql> select * from account ;
  +------+-------------+-------------+--------+------+
  | id | name | qq | gender | mm |
  +------+-------------+-------------+--------+------+
  | 1 | yuwenlong | 1589292300 | 1 | NULL |
  | 2 | wuchengxiao | 12345678 | 1 | NULL |
  | 3 | genziyue | 987654321 | 0 | 1 |
  | 3 | zhl | 123213123 | 0 | 1 |
  | 4 | zhouyang | 37427837847 | 1 | NULL |
  | 5 | ssss | 36261736 | 0 | 1 |
  | 6 | erw | 762377278 | 0 | 737 |
  | 7 | hsdhf | 3728338 | 1 | 737 |
  | 8 | sdfsd | 32477278 | 1 | 737 |
  | 9 | jsjdsj | 378282832 | 0 | 737 |
  | 10 | sdfs3we | 273878278 | 0 | 737 |
  | 11 | ehjrh32 | 636467 | 0 | 737 |
  | 12 | 3jjsjd | 37274728372 | 1 | 737 |
  | 13 | eyrwyu | 2367476237 | 1 | 737 |
  +------+-------------+-------------+--------+------+
  mysql> update account 
  		set mm =33 
  		where mm is NULL;
  mysql> select * from account;
  +------+-------------+-------------+--------+------+
  | id | name | qq | gender | mm |
  +------+-------------+-------------+--------+------+
  | 1 | yuwenlong | 1589292300 | 1 | 33 |
  | 2 | wuchengxiao | 12345678 | 1 | 33 |
  | 3 | genziyue | 987654321 | 0 | 1 |
  | 3 | zhl | 123213123 | 0 | 1 |
  | 4 | zhouyang | 37427837847 | 1 | 33 |
  | 5 | ssss | 36261736 | 0 | 1 |
  | 6 | erw | 762377278 | 0 | 737 |
  | 7 | hsdhf | 3728338 | 1 | 737 |
  | 8 | sdfsd | 32477278 | 1 | 737 |
  | 9 | jsjdsj | 378282832 | 0 | 737 |
  | 10 | sdfs3we | 273878278 | 0 | 737 |
  | 11 | ehjrh32 | 636467 | 0 | 737 |
  | 12 | 3jjsjd | 37274728372 | 1 | 737 |
  | 13 | eyrwyu | 2367476237 | 1 | 737 |
  +------+-------------+-------------+--------+------+
  

• 插入和更新可以用检索出来的数据作为数据源(子查询)

• delete from [表名] [where语句] 从 MySQL 数据表中删除数据

  mysql> select * from book2;
  +------+---------+---------+-------+
  | id | title | content | pages |
  +------+---------+---------+-------+
  | 1 | dfsd | fdsdf | 23 |
  | 2 | sdfdsz | dhfhjsd | 12 |
  | 3 | sjhdfj | shjdfhj | 29 |
  | 4 | hgdfh | hwsdh | 45 |
  | NULL | fdsdf | NULL | NULL |
  | NULL | dhfhjsd | NULL | NULL |
  | NULL | shjdfhj | NULL | NULL |
  | NULL | hwsdh | NULL | NULL |
  +------+---------+---------+-------+
  mysql> delete from book2 where id is NULL;
  Query OK, 4 rows affected (0.02 sec)
  mysql> select * from book2;
  +------+--------+---------+-------+
  | id | title | content | pages |
  +------+--------+---------+-------+
  | 1 | dfsd | fdsdf | 23 |
  | 2 | sdfdsz | dhfhjsd | 12 |
  | 3 | sjhdfj | shjdfhj | 29 |
  | 4 | hgdfh | hwsdh | 45 |
  +------+--------+---------+-------+
  mysql> delete from book2 ;
  Query OK, 4 rows affected (0.01 sec)
  mysql> select * from book2;
  Empty set (0.00 sec)
  

尾言

​ 本文章以便之后个人查阅.