MySQL Database Learning "The third" additions and deletions change operation

Note :
1. If you command in cmd, enter the wrong one and use \c to jump out

2.\s Viewing configuration information

I. Operation folder (library)

Add: Create Database db1 charset UTF8; Delete: drop database db1; change: Alter DATABASE DB1 charset GBK; check: show databases; #查看所有的数据库    Show Create database db1; #查看db1数据库

Ii. Operating documents (table)

Switch to Folder: Use DB1 add: Create TABLE T1 (IDintchar()) engine=   int;     Char check: show tables; #查看所有表    show create table t1; #查看t1表    desc t1; #查看表结构    Show CREATE table T1\g; #查看表详细结构, can be added    \gSelect * from T1; #查看所有的表数据

Third, the operation of the file line of content (record)

Add: INSERT into DB1.T1 values (1, ' Haiyan '), (2, ' yaling '), (3, ' Xiaoxiao '); #如果t1不给参数, by default, the parameter is deleted by the location parameters: Delete from t1 where id = 2;    #对于清空记录有两种方式, but recommend the latter    delete from T1;    Truncate T1; #当数据量比较大的情况下, use this method to delete the fast change: Update t1 set name = ' SB ' where id=3;    Update T1 set name= ' SB '  where name = ' Xiaoxiao ';    ALTER TABLE T7 modify ID int primary key auto_increment;  Modify ID primary key and self-search: SELECT * from T1; #查看t1里所有的数据    select name from T1;  #查看t1里所有的name    Select id,name from T1; #查看t1里所有的id, name

Iv. method of self-increment ID

CREATE TABLE t5 (ID int primary key auto_increment,name char), #create table t4 (id int not NULL unique Auto_increment,na Me char (Ten));  (not empty and unique) #这个和上面的是一回事insert into xx (' Haiyan1 '), (' Haiyan2 '), (' Haiyan3 '), ('                                haiyan4 '),                                (' Haiyan5 ');

V. Copy table structure

CREATE TABLE T7 (ID int,name char); CREATE TABLE T8 select * from T7;  #拷贝表结果 (Copy the data if you have data) CREATE TABLE T8 SELECT * from T5 where 1=2; #拷贝表结构, the table data is not copied (no records are found when the condition is false) ALTER TABLE T7 modify ID int primary key auto_increment;  Modify ID as primary key and increment insert into T7 (name) VALUES (' Egon1 '), (' Egon1 '), ('                              egon1 '),                              (' Egon1 ');

6.delete from t7 where id = 1; #删记录 (just delete a row when id=1) 7.update T7 Set name = "; #修改字段对应的值

Modify ID as primary key and increment

VI. Create an account

8.select User () #查看当前用户
  SELECT * from Mysql.user; See all the Users
9. Create account identifitycreate user ' Haiyan ' @ ' localhost ' identified by ' 147852 ' # native account named Haiyan create user ' alex ' @ '% ' identified By ' 123 ' #代表只要ip地址能拼通, then all users can telnet alexcreate user ' Susan ' @ ' 192.168.20.% ' identified by ' 123 ' #创建远程账号, as long as it is 192.168.20.? The IP at the beginning can be logged in susan# if you want to telnet to Alex's account, the client will have to log in like this  : mysql-h192.168.20.97-ualex-p123

Vii. permissions operation of the database

#insert, select, Update,delete #有这么几个可以设置权限的操作, let's take select for example. Four levels: Level 1: For all the libraries, under all the tables, under all the Fields ' ' *. * * stands for all the tables under all libraries ' ' agree to select permission Open, Open is *. * 's SELECT permission open to user grant SELECT on * * to ' Zhang ' @ ' localhost ' identified by ' 123 ';  #让创建用户的时候赋予权限级别2: On the DB1 Library, under all the tables, under All the fields grant select on db1.* to ' Wang ' @ ' localhost ' identified by ' 123 '; Level 3: Multiple fields under Table Db1.t1, grant Select on Db1.t1 to ' li ' @ ' localhost ' identified by ' 123 '; Level 4: To Table Db1.t1, under Id,name, field grant Select (ID, name)  On db1.t1 to ' Zhao ' @ ' localhost ' identifitied by ' 123 '; Grant Select (ID, name), update (name) on Db1.t1 to ' Zhao ' @ ' localhost ' Identifitied by ' 123 '; After modifying the permissions, remember to refresh the permissions flush privileges; Delete permissions: Revoke SELECT On *. * from ' Zhang ' @ ' localhost ' revoke select on DB 1.* from ' Wang ' @ ' localhost ' revoke select on db1.t1 from ' li ' @ ' localhost ' revoke select (ID, name), update (name) on Db1.t1 fr Om ' zhao ' @ ' localhost '

1. Create a local user and give permissions

Users log on locally, no IP address is required

2. Create a user as long as the IP is able to pass, all users can log in

Customer Login

The rest is the same, not one of them said

Eight, solve garbled problem

#1. Modify configuration file [Mysqld]default-character-set=utf8 [Client]default-character-set=utf8 [mysql]default-character-set=utf8# mysql5.5 above: Modification method changed    [mysqld]    Character-set-server=utf8    collation-server=utf8_general_ci    [ Client]    Default-character-set=utf8    [MySQL]    default-character-set=utf8#2. Restart Service # # #. View the results of the modification: \sshow Variables like '%char% ' permanently solves coding problems

Show variables like ' char% '; View encoding

MySQL Database Learning "The third" additions and deletions change operation