数据库的操作笔记:加油! 跳过授权登录:1,关闭mysql;mysqld --skip-grant-tables 重新启动客户端不用密码就可以登录 远程登录数据库: select user() 查看当前登录用户 建立本机账号 create user 'admin'@'localhost' identified by '123456'; 建立远程账号 create user 'admin'@'%' identified by '123456'; 任意主机 create user 'admin'@'192.168.20.%' identified by '123456'; 固定网段主机 远程登录:mysql -h(IP) -uname -p 192.168.20.35 insert,delete,update,select 用户授权: 级别1:对所有库,下的所有表,下的所有字段 grant select on *.* to 'admin'@'%' identified by '123456'; 级别2:对库db,下的所有表,下的所有字段 grant select on db.* to 'admin'@'%' identified by '123456'; 级别3;对表table,下的所有字段 grant select on db.table to 'admin'@'%' identified by '123456'; 级别4: 对表table 下的字段 grant select(id,name) on db.table to 'admin'@'%' identified by '123456'; flush privileges; 刷新 1 操作文件夹(数据库): 增: create database database_name charset utf8; 查; show databases;查看所有的数据库 show create database database_name 查看database_name 创建信息 改: alter database database_name charset gbk; 删: drop database database_name; \c取消命令执行 进入文件夹操作文件(进入库操作表)user database_name 2 操作文件(表): 增: create table table_name(id int,name char)engine=innodb default utf8; 查: show tables;(查看所有表) show create table_name;(查看创建表的信息) desc table_name;(查看表结构) 改: alter table table_name add age int;(增加字段) alter table table_name modify name char(12); 删: drop table table_name; 3 操作文件的一行行内容(记录): 增: insert into table_name values(1,'egon'),(2,'alex'); insert into table_name() values(); 查: select * from table_name; (查看所有) select name,id from table_name; 改: update table_name set name='SB' where id=4; 删: delete from table_name;(整体干掉) delete from table_name where id =4;(删除ID=4的) #推荐truncate删除,速度快, delete from table_name; truncate table_name;(干掉所有,数据量大的时候删除速度快) 自增ID (ID递增在上一个的基础上递增) create table table_name(id int primary key auto_increment,name char); primary key =not null unique 复制表(所有内容):create table new_table_name select * from table_name; 复制表(不要内容):create table new_table_name select * from table_name where 1=2;(条件为假,内容不拷贝) 作业一: 建库 create database db1 charset utf8; 建表插入字段 create table student(id int primary key auto_increment,name char,sex char,age int,lesson char,clsses char); create table teacher(id int primary key auto_increment,name char,sex char,age int,profess char,lesson char,clsses char); create table class(id int primary key auto_increment,name char); create table lesson(id int primary key auto_increment,name char,price int,period int); 插入数据 学生: insert into student(name,sex,age,lesson,clsses) values ('egon1','male',18,'pyhton','six'), ('egon2','male',18,'pyhton','six'), ('egon3','male',18,'pyhton','six'); 老师: insert into teacher(name,sex,age,profess,lesson,clsses) values ('egon1','male',18,'teachering','pyhton','six'), ('egon2','male',18,'teachering','pyhton','six'), ('egon3','male',18,'teachering','pyhton','six'); 班级: insert into class(name) values ('egon1'), ('egon2'), ('egon3'); 课程: insert into lesson(name,price,period)values ('pyhton0',18000,6), ('pyhton1',18000,6), ('pyhton2',18000,6); 作业二: 创建用户lili,只是开放lili对学生表的查询(select)与修改(update)权限 grant select,update on db1.student to 'lili'@'%' identified by '123456'; flush privileges; 创建用户Jack,只开房Jack对老师表的查询权限 grant select on db1.teacher to 'jack'@'%' identified by '123456'; flush privileges; 创建用户Tom,只允许Tom查询和修改课程表的名字和周期 grant select(name,period),update(name,period) on db1.lesson to 'tom'@'%' identified by '123456'; flush privileges;
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。