Summary of the basic usage of adding, deleting, modifying, and querying mysql, and summary of the usage of adding, deleting, and querying mysql

Summary of the basic usage of adding, deleting, modifying, and querying mysql, and summary of the usage of adding, deleting, and querying mysql

Directory:

1. Create a database
2. Create a data table
3. view the table structure
4. add, delete, modify, and query

 

Create a database students
Create a data table class1
The content includes:

Id Primary Key automatic id No. No sign bit SMALLINT type name VARCHAR (30) type non-null unique value school VARCHAR (30) non-null default value chengdu college

 

The Code is as follows:
1. Create a database

        mysql> CREATE DATABASE students;        Query OK, 1 row affected (0.07 sec)

2. Enter the database

        mysql> USE students;        Database changed

 

 

3. Create a table

        mysql> CREATE TABLE class1 (        -> id SMALLINT UNSIGNED AUTO_INCREMENT ,        -> name VARCHAR(30) NOT NULL UNIQUE KEY ,        -> school VARCHAR(30) DEFAULT 'chengdu_collage' ,        -> PRIMARY KEY(id)        -> );        Query OK, 0 rows affected (0.09 sec)

 

4. view the table structure

        mysql> DESC class1;        +--------+----------------------+------+-----+-----------------+----------------+        | Field  | Type                 | Null | Key | Default         | Extra          |        +--------+----------------------+------+-----+-----------------+----------------+        | id     | smallint(5) unsigned | NO   | PRI | NULL            | auto_increment |        | name   | varchar(30)          | NO   | UNI | NULL            |                |        | school | varchar(30)          | YES  |     | chengdu_collage |                |        +--------+----------------------+------+-----+-----------------+----------------+        3 rows in set (0.00 sec)

 

1. INSERT)

1. insert into tb_name [(col_name...)] {VALUES | VALUE} ({expr | DEFAULT },....), (...)... example: mysql> insert into class1 (name) VALUES ('john'); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO class1 VALUES (DEFAULT, 'job ', 'chengdu _ agricultural_college '); Query OK, 1 row affected (0.01 sec) 2. insert into tb_name SET col_name = {expr | DEFAULT },... example: mysql> insert into class1 SET name = 'Tom '; Query OK, 1 row affected (0.02 sec) mysql> INSERT INTO class1 SET name = 'lues ', school = 'chengdu _ agricultural_college '; Query OK, 1 row affected (0.01 sec)

 

2. UPDATE)

1. UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1 = {expr1 | DEFAULT} [, col_name2 = {expr2 | DEFAULT}]... example: mysql> UPDATE class1-> SET name = 'lues2' WHERE name = 'lues'; Query OK, 1 row affected (0.07 sec) Rows matched: 1 Changed: 1 Warnings: 0

 

3. DELETE)

1. DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [WHERE where_condition] example: mysql> delete from class1 WHERE name = 'lues2'; Query OK, 1 row affected (0.01 sec)

 

4. Query (SELECT)

1. simple query of mysql> SELECT * FROM class1; + ---- + ------ + ---------------------------- + | id | name | school | + ---- + ------ + upper + | 1 | john | chengdu_collage | 2 | jobs | chengdu_agricultural_college | 3 | tom | chengdu_collage | + ---- + ------ + ------------------------------ + 3 rows in set (0.00 sec) 2. simple conditional query mysql> SELECT * FROM class1 WHERE id> 1; + ---- + ------ + ---------------------------- + | id | name | school | + ---- + ------ + upper + | 2 | jobs | chengdu_agricultural_college | 3 | tom | chengdu_collage | + ---- + ------ + ------------------------------ + 2 rows in set (0.00 sec) 3. simple grouping query mysql> SELECT * FROM class1 group by school DESC; + ---- + ------ + ---------------------------- + | id | name | school | + ---- + ------ + upper + | 1 | john | chengdu_collage | 2 | jobs | chengdu_agricultural_college | + ---- + ------ + ------------------------------ + 2 rows in set (0.00 sec) 4. simple sorting query mysql> SELECT * FROM class1 order by id DESC; + ---- + ------ + ---------------------------- + | id | name | school | + ---- + ------ + bytes + | 3 | tom | chengdu_collage | 2 | jobs | chengdu_agricultural_college | 1 | john | chengdu_collage | + ---- + ------ + ------------------------------ + 3 rows in set (0.00 sec)