数据库的基本操作

数据库的操作笔记：加油！跳过授权登录：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.35insert,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_name2 操作文件（表）：增：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;