MySQL Basic use

Open mysql:mysql-hlocalhost-uroot-p Password

Next is the high energy, sometimes because of the MySQL version of the problem, the problem of the field, the problem of the space is often caused by errors, so some of the following code in your machine error please do not take offense, do not understand to ask Baidu.

Show Start:

Create a list first:

Mysql> CREATE TABLE Student (

-stu_id int Auto_increment,

-Name CHAR (+) not NULL,

-Age INT is not NULL,

-Register_date date NOT NULL,

Primary key (ID));

ERROR 1046 (3d000): No Database selected

What's wrong, and what's the reason?

We're going to create a database first.

mysql> CREATE DATABASE xsphpdb;

Query OK, 1 row Affected (0.00 sec)

OK, and then this creates a list

Mysql> CREATE TABLE Xsphpdb.users (

ID int,

, name char (30),

-Age Int,sex char (3));

Query OK, 0 rows affected (0.08 sec)

Let's look at this list again.

Mysql> Show tables;

ERROR 1046 (3d000): No Database selected

What's wrong?

Because you create a database, but do not enter it, so error.

mysql> use xsphpdb;

Database changed

Mysql> Show tables;

+-------------------+

| tables_in_xsphpdb |

+-------------------+

| Users |

+-------------------+

1 row in Set (0.02 sec)

This is OK, so what do we do next?

View the details of a list

mysql> desc users;

+-------+----------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

| ID | Int (11) |     YES | |       NULL | |

| name | char (30) |     YES | |       NULL | |

| Age | Int (11) |     YES | |       NULL | |

| sex | CHAR (3) |     YES | |       NULL | |

+-------+----------+------+-----+---------+-------+

Ok

Then create one:

Mysql> CREATE TABLE Studen (

-stu_id INT not NULL auto_increment,

-Name CHAR (+) not NULL,

-Age INT is not NULL,

-Register_date date NOT NULL,

PRIMARY KEY (stu_id)

);

mysql> desc Studen;

+---------------+----------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+---------------+----------+------+-----+---------+----------------+

| stu_id | Int (11) | NO | PRI | NULL | auto_increment |

| name | char (32) |     NO | |                NULL | |

| Age | Int (11) |     NO | |                NULL | |

| Register_date | Date |     NO | |                NULL | |

+---------------+----------+------+-----+---------+----------------+

What's the difference between this watch and the table above?

Is null

Remember to create a list in the future and make sure

-stu_id INT not NULL auto_increment,

-Name CHAR (+) not NULL,

-Age INT is not NULL,

-Register_date date NOT NULL,

Not NULL, otherwise you will usher in a variety of errors

Insert data: INSERT INTO Studen (name,age,register_date) VALUES ("Alex Li", 22, "2016-03-4");

SELECT * from Studen;

viewing elements

+--------+---------+-----+---------------+

| stu_id | name | Age | Register_date |

+--------+---------+-----+---------------+

| 1 |  Alex Li | 22 | 2016-03-04 |

+--------+---------+-----+---------------+

1 row in Set (0.00 sec)

So repeat it a few times.

Mysql> select * from Studen;

+--------+---------+-----+---------------+

| stu_id | name | Age | Register_date |

+--------+---------+-----+---------------+

| 1 |  Alex Li | 22 | 2016-03-04 |

| 2 |  Alex Li | 22 | 2016-03-04 |

| 3 |  Alex Li | 22 | 2016-03-04 |

| 4 |  Alex Li | 22 | 2016-03-04 |

| 5 |  Alex Li | 22 | 2016-03-04 |

| 6 |  Alex Li | 22 | 2016-03-04 |

| 7 |  Alex Li | 22 | 2016-03-04 |

+--------+---------+-----+---------------+

How do I check the data?

Mysql> select * FROM Studen limit 3 offset 2;

+--------+---------+-----+---------------+

| stu_id | name | Age | Register_date |

+--------+---------+-----+---------------+

| 3 |  Alex Li | 22 | 2016-03-04 |

| 4 |  Alex Li | 22 | 2016-03-04 |

| 5 |  Alex Li | 22 | 2016-03-04 |

+--------+---------+-----+---------------+

Mysql> select * from Studen limit 1 offset 2;

+--------+---------+-----+---------------+

| stu_id | name | Age | Register_date |

+--------+---------+-----+---------------+

| 3 |  Alex Li | 22 | 2016-03-04 |

+--------+---------+-----+---------------+

Mysql> SELECT * from Studen where stu_id>3 and age=22;

+--------+---------+-----+---------------+

| stu_id | name | Age | Register_date |

+--------+---------+-----+---------------+

| 4 |  Alex Li | 22 | 2016-03-04 |

| 5 |  Alex Li | 22 | 2016-03-04 |

| 6 |  Alex Li | 22 | 2016-03-04 |

| 7 |  Alex Li | 22 | 2016-03-04 |

+--------+---------+-----+---------------+

4 rows in Set (0.00 sec)

Here, there is a special concept:

Fuzzy Search:

Mysql> SELECT * from Studen where register_date like "2016-03%";

+--------+---------+-----+---------------+

| stu_id | name | Age | Register_date |

+--------+---------+-----+---------------+

| 1 |  Alex Li | 22 | 2016-03-04 |

| 2 |  Alex Li | 22 | 2016-03-04 |

| 3 |  Alex Li | 22 | 2016-03-04 |

| 4 |  Alex Li | 22 | 2016-03-04 |

| 5 |  Alex Li | 22 | 2016-03-04 |

| 6 |  Alex Li | 22 | 2016-03-04 |

| 7 |  Alex Li | 22 | 2016-03-04 |

+--------+---------+-----+---------------+

7 rows in Set, 1 Warning (0.00 sec)

increase, check, learn, we come to learn how to modify:

Update Studen set name= "Chenronghua", age=33 where stu_id=4;

Ok

Update Studen set name= "Chenronghua", age=33 where stu_id>6;

Ok

Mysql> SELECT * from Studen where register_date like "2016-03%";

+--------+-------------+-----+---------------+

| stu_id | name | Age | Register_date |

+--------+-------------+-----+---------------+

| 1 |  Alex Li | 22 | 2016-03-04 |

| 2 |  Alex Li | 22 | 2016-03-04 |

| 3 |  Alex Li | 22 | 2016-03-04 |

| 4 |  Chenronghua | 33 | 2016-03-04 |

| 5 |  Alex Li | 22 | 2016-03-04 |

| 6 |  Alex Li | 22 | 2016-03-04 |

| 7 |  Chenronghua | 33 | 2016-03-04 |

+--------+-------------+-----+---------------+

The last one is to delete

Delete from Studen where name= "Chenronghua";

There's one more question of how to sort.

Being sorted, with a sort of reverse

Mysql> SELECT * from Studen order by stu_id;

+--------+---------+-----+---------------+

| stu_id | name | Age | Register_date |

+--------+---------+-----+---------------+

| 1 |  Alex Li | 22 | 2016-03-04 |

| 2 |  Alex Li | 22 | 2016-03-04 |

| 3 |  Alex Li | 22 | 2016-03-04 |

| 5 |  Alex Li | 22 | 2016-03-04 |

| 6 |  Alex Li | 22 | 2016-03-04 |

+--------+---------+-----+---------------+

5 rows in Set (0.00 sec)

Mysql> SELECT * from Studen ORDER by stu_id Desc;

+--------+---------+-----+---------------+

| stu_id | name | Age | Register_date |

+--------+---------+-----+---------------+

| 6 |  Alex Li | 22 | 2016-03-04 |

| 5 |  Alex Li | 22 | 2016-03-04 |

| 3 |  Alex Li | 22 | 2016-03-04 |

| 2 |  Alex Li | 22 | 2016-03-04 |

| 1 |  Alex Li | 22 | 2016-03-04 |

+--------+---------+-----+---------------+

Add two more data

mysql> INSERT INTO Studen (name,age,register_date) VALUES ("Wngd", 23, "2016-06-4");

Query OK, 1 row Affected (0.00 sec)

mysql> INSERT INTO Studen (name,age,register_date) VALUES ("Wn45", 2324, "2016-07-4");

Query OK, 1 row Affected (0.00 sec)

Mysql> select * from Studen;

+--------+---------+------+---------------+

| stu_id | name | Age | Register_date |

+--------+---------+------+---------------+

| 1 |   Alex Li | 22 | 2016-03-04 |

| 2 |   Alex Li | 22 | 2016-03-04 |

| 3 |   Alex Li | 22 | 2016-05-31 |

| 5 |   Alex Li | 22 | 2016-03-04 |

| 6 |   Alex Li | 22 | 2016-03-04 |

| 8 |   Wngd | 23 | 2016-06-04 |

| 9 | Wn45 | 2324 | 2016-07-04 |

+--------+---------+------+---------------+

7 Rows in Set (0.00 sec)

Statistics of the data

Mysql> Select Name,count (*) as Stu_num from Studen Group by Register_date;

+---------+---------+

| name | Stu_num |

+---------+---------+

|       Alex Li | 4 |

|       Alex Li | 1 |

|       Wngd | 1 |

|       Wn45 | 1 |

+---------+---------+

4 rows in Set (0.00 sec)

Mysql> Select Name,sum (age) from Studen;

+---------+----------+

| name | SUM (age) |

+---------+----------+

|     Alex Li | 2457 |

+---------+----------+

1 row in Set (0.00 sec)

Find sum

Mysql> Select Name,sum (age) from Studen group by name;

+---------+----------+

| name | SUM (age) |

+---------+----------+

|      Alex Li | 110 |

|     Wn45 | 2324 |

|       Wngd | 23 |

+---------+----------+

Mysql> Select Name,sum (age) from Studen Group by name with rollup;

+---------+----------+

| name | SUM (age) |

+---------+----------+

|      Alex Li | 110 |

|     Wn45 | 2324 |

|       Wngd | 23 |

|     NULL | 2457 |

+---------+----------+

4 rows in Set (0.00 sec)

Mysql> Select COALESCE (Name, "Total Age"), sum (age) from Studen Group by name with rollup;

+----------------------------+----------+

| COALESCE (Name, "Total Age") | SUM (age) |

+----------------------------+----------+

|      Alex Li | 110 |

|     Wn45 | 2324 |

|       Wngd | 23 |

|     Total Age | 2457 |

+----------------------------+----------+

Let me introduce you to:

mysql> ALTER TABLE studen ADD zk_en VARCHAR (+) not null; #加一列一定要加not NULL, otherwise the operation is difficult to do, due to different versions, the solution is not the same

Query OK, 0 rows affected (0.07 sec)

records:0 duplicates:0 warnings:0

mysql> desc Studen;

+---------------+---------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+---------------+---------------+------+-----+---------+----------------+

| stu_id | Int (11) | NO | PRI | NULL | auto_increment |

| name | char (32) |     NO | |                NULL | |

| Age | Int (11) |     NO | |                NULL | |

| Register_date | Date |     NO | |                NULL | |

| sex | Enum (' M ', ' F ') |     YES | |                NULL | |

| Phone | Int (11) |     NO | |                NULL | |

| zk_env | varchar (16) |     YES | |                NULL | |

| Zk_en | varchar (16) |     NO | |                NULL | |

+---------------+---------------+------+-----+---------+----------------+

mysql> ALTER TABLE Studen change zk_en Gender char (+) NOT null default "X"; #修改类型

Query OK, 7 rows affected (0.07 sec)

Records:7 duplicates:0 warnings:0

mysql> desc Studen;

+---------------+---------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+---------------+---------------+------+-----+---------+----------------+

| stu_id | Int (11) | NO | PRI | NULL | auto_increment |

| name | char (32) |     NO | |                NULL | |

| Age | Int (11) |     NO | |                NULL | |

| Register_date | Date |     NO | |                NULL | |

| sex | Enum (' M ', ' F ') |     YES | |                NULL | |

| Phone | Int (11) |     NO | |                NULL | |

| zk_env | varchar (16) |     YES | |                NULL | |

| Gender | char (32) |     NO | |                X | |

+---------------+---------------+------+-----+---------+----------------+

But what if it's not enough?

We learn how to change zk_env null.

Update Studen set zk_ens=0 where zk_ens is null;

ALTER TABLE Studen MODIFY COLUMN zk_ens Int (one) not NULL DEFAULT ' 0 ';

This is OK

MySQL Basic use