数据库基本操作

数据库的操作笔记:加油!
跳过授权登录: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

  

用户授权:
grant all on *.* to 'admin'@'%' identified by '123456';         最高远程权限
revoke select on *.* from 'zhang'@'localhost'       #删除权限
级别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);
alter table table_name add primary key (id); # 修改id为table_name表的主键
删:
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;(条件为假,内容不拷贝)

  

 1 建库
 2 create database db1 charset utf8;
 3 
 4 
 5 建表插入字段
 6 create table student(id int primary key auto_increment,name char,sex char,age int,lesson char,clsses char);
 7 create table teacher(id int primary key auto_increment,name char,sex char,age int,profess char,lesson char,clsses char);
 8 create table class(id int primary key auto_increment,name char);
 9 create table lesson(id int primary key auto_increment,name char,price int,period int);
10 
11 插入数据
12 学生:
13 insert into student(name,sex,age,lesson,clsses) values 
14 ('egon1','male',18,'pyhton','six'),
15 ('egon2','male',18,'pyhton','six'),
16 ('egon3','male',18,'pyhton','six');
17 老师:
18 insert into teacher(name,sex,age,profess,lesson,clsses) values 
19 ('egon1','male',18,'teachering','pyhton','six'),
20 ('egon2','male',18,'teachering','pyhton','six'),
21 ('egon3','male',18,'teachering','pyhton','six');
22 班级:
23 insert into class(name) values
24 ('egon1'),
25 ('egon2'),
26 ('egon3');
27 课程:
28 insert into lesson(name,price,period)values
29 ('pyhton0',18000,6),
30 ('pyhton1',18000,6),
31 ('pyhton2',18000,6);
作业1
 1 创建用户lili,只是开放lili对学生表的查询(select)与修改(update)权限
 2 grant select,update on db1.student to 'lili'@'%' identified by '123456';
 3 
 4 flush privileges;
 5 创建用户Jack,只开房Jack对老师表的查询权限
 6 grant select on db1.teacher to 'jack'@'%' identified by '123456';
 7 
 8 flush privileges;
 9 创建用户Tom,只允许Tom查询和修改课程表的名字和周期
10 grant select(name,period),update(name,period) on db1.lesson to 'tom'@'%' identified by '123456';
11 
12 flush privileges;
作业2

 

posted @ 2017-09-05 17:26 前方、有光 阅读(...) 评论(...) 编辑 收藏