前言
对数据库的管理是非常重要的,本文基于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)
尾言
本文章以便之后个人查阅.