Basic operation of database

Database operation notes: come on! Skip authorization login: 1, close mysql Mysqld-skip-grant-tables restart client can log in to the database remotely without a password: select user () check the current login user to set up a local account create user 'admin'@'localhost' identified by' 123456login; set up a remote account create user 'admin'@'%' identified by' 123456login; any host create user 'admin'@'192.168.20.%' identified by' 123456' Remote login of fixed network segment host: mysql-h (IP)-uname-p 192.168.20.35 insert admin'@'%' identified by _ update _ select user authorization: level 1: for all libraries, all tables below, all fields under grant select on *. * to 'tables' 123456fields; level 2: for library db, all tables below, all fields under grant select on db.* to 'admin'@'%' identified by' 123456entries; level 3 For table table, all fields under grant select on db.table to 'admin'@'%' identified by' 123456; level 4: refresh for fields under table table grant select (id,name) on db.table to 'admin'@'%' identified by' 123456 operations flush privileges; 1 operation folder (database): add: create database database_name charset utf8; check Show databases; view all databases show create database database_name view database_name creation information change: alter database database_name charset gbk; delete: drop database database_name;\ c cancel command execute enter folder operation file (enter library operation table) user database_name2 operation file (table): add: create table table_name (id int,name char) engine=innodb default utf8; check: show tables; (view all tables) show create table_name; (view information on creating tables) desc table_name (view table structure) change: alter table table_name add age int; (add field) alter table table_name modify name char (12); delete: drop table table_name;3 operation file line by line content (record): add: insert into table_name values (1), (2) insert into table_name () values (); check: select * from table_name; (view all) select name,id from table_name Change: update table_name set name='SB' where id=4; deletion: delete from table_name; (overall kill) delete from table_name where id=4; (delete ID=4) # recommended truncate deletion, fast, delete from table_name;truncate table_name; (kill all, delete fast when there is a large amount of data) self-increasing ID (ID increments on the basis of the previous one) create table table_name (id int primary key auto_increment,name char) Primary key = not null unique copy table (all content): create table new_table_name select * from table_name; copy table (no content): create table new_table_name select * from table_name where 1 copy 2; (condition is false, content is not copied) Job 1: build database create database db1 charset utf8; build table insert field 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 data students: insert into student (name,sex,age,lesson,clsses) values ('egon1','male',18,'pyhton','six'), (' egon2','male',18,'pyhton','six'), ('egon3','male',18,'pyhton','six') Teacher: 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'); Class: insert into class (name) values (' egon1'), ('egon2'), (' egon3') Course: insert into lesson (name,price,period) values ('pyhton0',18000,6), (' pyhton1',18000,6), ('pyhton2',18000,6); assignment 2: create a user lili, but open lili to query (select) and modify (update) the student table grant select,update on db1.student to' lili'@'%' identified by '123456users flush privileges Create user Jack, only open room Jack query permission of master table grant select on db1.teacher to 'jack'@'%' identified by' 123456creating flush privileges; create user Tom, only allow Tom to query and modify the name and period of the course schedule grant select (name,period), update (name,period) on db1.lesson to 'tom'@'%' identified by' 123456query flush privileges