The basic operation of mysql statement 04/07 Update SLTechnology News&Howtos

The basic operation of mysql statement

2025-04-07 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

Practice environment: operate directly in the production environment

OS:CentOS6.8

The specific operation procedure is as follows:

Last login: Wed Aug 10 08:07:15 2016 from *

Welcome to * CentOS server!

[sky@sky9896] $mysql-u root-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 114094

Server version: 5.5.49-cll-lve MySQL Community Server (GPL) by Atomicorp

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > create database a # create database a

Query OK, 1 row affected (0.08 sec)

Mysql > show databases; # shows all databases

+-+

| | Database |

+-+

| | information_schema |

| | a |

| | back20150625ultrax |

| | cacti |

| | cacti20151220 |

| | cacti20160104 |

| | feifeicms |

| | mysql |

| | performance_schema |

| | phpcom |

| | study |

| | syslog |

| | test |

| | test1 |

| | tt |

| | ultrax |

+-+

16 rows in set (0.16 sec)

Mysql > use a # Open the database

Database changed

Mysql > show tables

Empty set (0.00 sec)

Mysql > create table A1 (id int); # create A1 table

Query OK, 0 rows affected (0.22 sec)

Mysql > show tables; # shows all tables

+-+

| | Tables_in_a |

+-+

| | A1 |

+-+

1 row in set (0.00 sec)

Mysql > describe A1 # display table structure

+-+ +

+-+ +

| | id | int (11) | YES | | NULL |

+-+ +

1 row in set (0.00 sec)

Mysql > show engines; # View engine

+-- +

+-- +

| | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | |

| | CSV | YES | CSV storage engine | NO | NO | NO | |

| | MyISAM | YES | MyISAM storage engine | NO | NO | NO | |

| | BLACKHOLE | YES | / dev/null storage engine (anything you write to it disappears) | NO | NO | NO |

| | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| | ARCHIVE | YES | Archive storage engine | NO | NO | NO | |

| | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | |

+-- +

9 rows in set (0.00 sec)

[sky@sky9896 ~] $mysql-u root-p # Connect to the database

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 114209

Server version: 5.5.49-cll-lve MySQL Community Server (GPL) by Atomicorp

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > exit # exit

Bye

Mysql > use a

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql > create table a2 (

-> id int unsigned not null auto_increment, # cannot be empty. Increase the value automatically.

-> name char (40) not null default', # cannot be empty. Default is empty.

-> info char (200) null

-> primary key (id); # set id as the primary key

Query OK, 0 rows affected (0.12 sec)

Mysql > describe a2

+-+ +

+-+ +

| | id | int (10) unsigned | NO | PRI | NULL | auto_increment |

+-+ +

3 rows in set (0.00 sec)

Mysql > show tables

+-+

| | Tables_in_a |

+-+

| | A1 |

| | a2 |

+-+

2 rows in set (0.00 sec)

Mysql > insert into a2 (id,name,info) values

Query OK, 1 row affected, 1 warning (0.07 sec)

Mysql > select * from a2

+-- +

+-- +

| | 1 | wuhaiming |? |

+-- +

1 row in set (0.00 sec)

Mysql > select id from a2

+-- +

| | id |

+-- +

| | 1 |

+-- +

1 row in set (0.00 sec)

Mysql > insert into a2 values (2)

Query OK, 1 row affected (0.04 sec)

Mysql > select * from a2

+-- +

+-- +

| | 1 | wuhaiming |? |

| | 2 | a2 | sky9890 |

+-- +

2 rows in set (0.00 sec)

Mysql > insert into a2 (id,name) values (', 'whm')

Query OK, 1 row affected, 1 warning (0.06 sec)

Mysql > select * from a2

+-- +

+-- +

| | 1 | wuhaiming |? |

| | 2 | a2 | sky9890 |

| | 3 | whm | NULL |

+-- +

3 rows in set (0.00 sec)

Mysql > describe a2

+-+ +

+-+ +

| | id | int (10) unsigned | NO | PRI | NULL | auto_increment |

+-+ +

3 rows in set (0.00 sec)

Mysql > insert into a2 values ('','a6 recording, recording sky9890'), (','a7 recording,); # insert a record

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near')'at line 1

Mysql > insert into a2 values ('','a6 records memory sky9890'), (','a7 records values); # insert two records in a row

Query OK, 2 rows affected, 2 warnings (0.17 sec)

Records: 2 Duplicates: 0 Warnings: 2

Mysql > select * from a2

+-- +

+-- +

| | 1 | wuhaiming |? |

| | 2 | a2 | sky9890 |

| | 3 | whm | NULL |

| | 4 | A6 | sky9890 |

| | 5 | A7 | sky |

+-- +

5 rows in set (0.00 sec)

Mysql > describe A1

+-+ +

+-+ +

| | id | int (11) | YES | | NULL |

+-+ +

1 row in set (0.00 sec)

Mysql > describe a2

+-+ +

+-+ +

| | id | int (10) unsigned | NO | PRI | NULL | auto_increment |

+-+ +

3 rows in set (0.00 sec)

Mysql > select * from A1

Empty set (0.00 sec)

Mysql > insert into A1 (id) select id from a2

Query OK, 5 rows affected (0.06 sec)

Records: 5 Duplicates: 0 Warnings: 0

Mysql > select * from A1

+-+

| | id |

+-+

| | 1 |

| | 2 |

| | 3 |

| | 4 |

| | 5 |

+-+

5 rows in set (0.00 sec)

Mysql > select * from a2

+-- +

+-- +

| | 1 | wuhaiming |? |

| | 2 | a2 | sky9890 |

| | 3 | whm | NULL |

| | 4 | A6 | sky9890 |

| | 5 | A7 | sky |

+-- +

5 rows in set (0.00 sec)

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | a |

| | back20150625ultrax |

| | cacti |

| | cacti20151220 |

| | cacti20160104 |

| | feifeicms |

| | mysql |

| | performance_schema |

| | phpcom |

| | study |

| | syslog |

| | test |

| | test1 |

| | tt |

| | ultrax |

+-+

16 rows in set (0.00 sec)

Mysql > drop database b; # Delete b database

ERROR 1008 (HY000): Can't drop database 'b'; database doesn't exist

Mysql > show tables

+-+

| | Tables_in_a |

+-+

| | A1 |

| | a2 |

+-+

2 rows in set (0.00 sec)

Mysql > drop tables A1 position # Delete A1 table

Query OK, 0 rows affected (0.09 sec)

Mysql > show tables

+-+

| | Tables_in_a |

+-+

| | a2 |

+-+

1 row in set (0.00 sec)

Mysql > delete from a2 where id=5 or info='sky'; # Delete a record

Query OK, 1 row affected (0.05sec)

Mysql > select * from a2

+-- +

+-- +

| | 1 | wuhaiming |? |

| | 2 | a2 | sky9890 |

| | 3 | whm | NULL |

| | 4 | A6 | sky9890 |

+-- +

4 rows in set (0.00 sec)

Mysql > update a2 set info='sky' where id=1; # update a field value

Query OK, 1 row affected (0.07 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > select * from a2

+-- +

+-- +

| | 1 | wuhaiming | sky |

| | 2 | a2 | sky9890 |

| | 3 | whm | NULL |

| | 4 | A6 | sky9890 |

+-- +

4 rows in set (0.00 sec)

Mysql > SHOW VARIABLES LIKE 'character%'

+-+

| | Variable_name | Value |

+-+

| | character_set_client | utf8 |

| | character_set_connection | utf8 |

| | character_set_database | latin1 |

| | character_set_filesystem | binary |

| | character_set_results | utf8 |

| | character_set_server | latin1 |

| | character_set_system | utf8 |

| | character_sets_dir | / usr/share/mysql/charsets/ |

+-+

8 rows in set (0.00 sec)

Mysql > SET collation_database = utf8

ERROR 1273 (HY000): Unknown collation: 'utf8'

Mysql > set character_set_database=utf8

Query OK, 0 rows affected (0.00 sec)

Mysql > update a2 set info=' Wu Haiming 'where id=1

Query OK, 1 row affected, 1 warning (0.06 sec)

Rows matched: 1 Changed: 1 Warnings: 1

Mysql > select * from a2

+-- +

+-- +

| | 1 | wuhaiming |? |

| | 2 | a2 | sky9890 |

| | 3 | whm | NULL |

| | 4 | A6 | sky9890 |

+-- +

4 rows in set (0.00 sec)

Mysql > alter table a2 rename A1 # modify the table name

Query OK, 0 rows affected (0.06 sec)

Mysql > show tables

+-+

| | Tables_in_a |

+-+

| | A1 |

+-+

1 row in set (0.00 sec)

Mysql > select * from a2

ERROR 1146 (42S02): Table 'a.a2' doesn't exist

Mysql > select * from a2

ERROR 1146 (42S02): Table 'a.a2' doesn't exist

Mysql > select * from A1

+-- +

+-- +

| | 1 | wuhaiming |? |

| | 2 | a2 | sky9890 |

| | 3 | whm | NULL |

| | 4 | A6 | sky9890 |

+-- +

4 rows in set (0.00 sec)

Mysql > alter table A1 change info information char; # change the field name

Query OK, 4 rows affected (0.26 sec)

Records: 4 Duplicates: 0 Warnings: 0

Mysql > describe A1

+-+ +

+-+ +

| | id | int (10) unsigned | NO | PRI | NULL | auto_increment |

+-+ +

3 rows in set (0.00 sec)

Mysql > alter table A1 add time date;# add columns at the end

Query OK, 4 rows affected (0.26 sec)

Records: 4 Duplicates: 0 Warnings: 0

Mysql > describe A1

+-+ +

+-+ +

| | id | int (10) unsigned | NO | PRI | NULL | auto_increment |

+-+ +

4 rows in set (0.00 sec)

Mysql > alter table A1 drop time; # delete column

Query OK, 4 rows affected (0.26 sec)

Records: 4 Duplicates: 0 Warnings: 0

Mysql > describe A1

+-+ +

+-+ +

| | id | int (10) unsigned | NO | PRI | NULL | auto_increment |

+-+ +

3 rows in set (0.00 sec)

Mysql > alter table A1 add time date first;# is inserted into the first column

Query OK, 4 rows affected (0.24 sec)

Records: 4 Duplicates: 0 Warnings: 0

Mysql > describe A1

+-+ +

+-+ +

| | id | int (10) unsigned | NO | PRI | NULL | auto_increment |

+-+ +

4 rows in set (0.00 sec)

Mysql > select * from A1

+-+

+-+

| | NULL | 1 | wuhaiming |? |

| | NULL | 2 | a2 | sky9890 |

| | NULL | 3 | whm | NULL | |

| | NULL | 4 | A6 | sky9890 | |

+-+

4 rows in set (0.00 sec)

Mysql > alter table A1 add nian year after time;# is inserted into the specified column

Query OK, 4 rows affected (0.25 sec)

Records: 4 Duplicates: 0 Warnings: 0

Mysql > describe A1

+-+ +

+-+ +

| | id | int (10) unsigned | NO | PRI | NULL | auto_increment |

+-+ +

5 rows in set (0.00 sec)

Mysql > grant all on cacti.* to 'a1 license license localhost 'identified by' 123456 authorization

Query OK, 0 rows affected (0.02 sec)

Mysql > exit

Bye

Mysql > grant create,select on *. * to 'a2 licensed localhost 'identified by' 123; # authorization

Query OK, 0 rows affected (0.00 sec)

Mysql > revoke select on *. * from 'a2permission withdrawing permission

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.