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