database table operations, data manipulation

Note the points:
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)

# View all databases    # View the DB1 database

Ii. Operating documents (table)

Switch to Folder: Use DB1: CREATE TABLE t1 (id int,name char) engine=innodb; Delete: drop table T1; change: ALTER TABLE T1 add age int;    ALTER TABLE T1 modify name char (# # View all Tables    # view T1 table    desc t1;  # View table structure

    Show CREATE TABLE t1\g; #查看表详细结构, can add \g

     from # View all the table data

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

Add: INSERT into DB1.T1 values (1,'Haiyan'), (2,'yaling'), (3,'Xiaoxiao');#If the T1 does not give the parameter, the default is to pass the parameter by position parameterDelete: Delete fromT1 WHERE id = 2; #There are two ways to clear records, but the latter is recommendedDelete fromT1; Truncate T1; #when the amount of data is larger, the deletion speed is used in this wayChange: 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 as primary key and self-search: Select* fromT1;#See all the data in T1Select Name fromT1;#See all the name in T1Select Id,name fromT1;#See all the id,name in T1

Iv. method of self-increment ID

CREATE TABLE t5 (ID int primary key auto_increment,name char (10)); #create table t4 (id int notNull unique Auto_increment,name char (10)); (not empty and unique)#This is one thing with the above.Insert into XX (name) VALUES ('Haiyan1'),                                ('haiyan2'),                                ('haiyan3'),                                ('Haiyan4'),                                ('Haiyan5');

V. Copy table structure

CREATE TABLE T7 (ID int,name char (10) ); CREATE TABLE T8 Select* fromT7;#Copy table results (copy the data if you have data)CREATE TABLE T8 SELECT * fromT5 where 1=2;#copy table structure without copying table data (no records are found when the condition is false)ALTER TABLE T7 modify ID int primary key auto_increment; Modify the ID 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 ()#View Current User9. Create account identifitycreate User'Haiyan'@'localhost'Identified by'147852' #A native account named HaiyanCreate user'Alex'@'%'Identified by'123' #means that all users can telnet to Alex as long as the IP address can be spelled.Create user'Susan'@'192.168.20.%'Identified by'123' #Create a remote account, as long as it is 192.168.20. You can sign in to Susan at the beginning of the IP#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: all the tables under all of the libraries, all the fields below" "*. * represents all the tables under all libraries" "agree that select permission is open and open is*.*The SELECT permission is open to the user grant Select on*. * To'Zhang'@'localhost'Identifitied by'123';#Giving permissions when creating a userLevel 2: On the DB1 Library, under all the tables, under All the fields grant select on DB1.* To'Wang'@'localhost'Identifitied by'123'; Level 3: Multiple fields under Table Db1.t1, grant Select on Db1.t1 to'Li'@'localhost'Identifitied 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 DB1.* from 'Wang'@'localhost'Revoke select on Db1.t1 from 'Li'@'localhost'REVOKE Select (ID, name), update (name) on Db1.t1 from 'Zhao'@'localhost'

database table operations, data manipulation