Java cultivation program -- the 73rd day of learning punch in

Java (72nd day of punch in)

I've been busy with digital experiment recently. If you have any questions about verilog, please exchange and learn

First, simply query the previous content. Query the top 200 cities in the city table. After grouping according to the Countrycode, the groups with an average population of more than 400000 in each group will output the top 5 in descending order

mysql> SELECT
    -> CountryCode,AVG(Population)  AS 'Avgpop'
    -> FROM
    -> city
    -> WHERE
    -> ID <= 200
    -> GROUP BY
    -> CountryCode
    -> HAVING
    -> AVG(Population) > 400000\
    -> ORDER BY
    ->  Avgpop DESC
    -> LIMIT
    -> 5;
+-------------+-------------+
| CountryCode | Avgpop      |
+-------------+-------------+
| AUS         | 808119.0000 |
| AZE         | 616000.0000 |
| AFG         | 583025.0000 |
| ARM         | 544366.6667 |
| AGO         | 512320.0000 |
+-------------+-------------+
5 rows in set (0.00 sec)

At the same time, we will not repeat the modified UPDATE, deleted DELETE, inserted INSERT and table structure deletion. Next, we will continue to look at the constraints

mysql> CREATE TABLE t_student(
    -> name VARCHAR(25) NOT NULL,
    -> sex CHAR(1)    NOT NULL,
    -> idno  INT  DEFAULT 45  UNIQUE
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO
    -> t_student
    -> (name,sex,idno)
    -> VALUES
    -> ('Linda','male',1);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO
    -> t_student
    -> (sex,idno)
    -> VALUES
    -> ('male',1);
ERROR 1364 (HY000): Field 'name' doesn't have a default value
mysql> INSERT INTO
    -> t_student
    -> (name,sex)
    -> VALUES
    -> ('LI','female');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO
    -> t_student
    -> (name,sex)
    -> VALUES
    -> ('Wng','male');
ERROR 1062 (23000): Duplicate entry '45' for key 't_student.idno'
mysql> SELECT * FROM t_student;
+-------+-----+------+
| name  | sex | idno |
+-------+-----+------+
| Linda | male  |    1 |
| LI    | female  |   45 |
+-------+-----+------+
2 rows in set (0.00 sec)

The non NULL constraint and uniqueness constraint have been completely tested above, so the fields with uniqueness constraint will no longer use DEFAULT because they cannot be repeated

Null represents null and does not represent a specific value, so it can be null at the same time

Joint unique table level constraint for two fields

For example, the above requires that the name and idno be united and unique. Note that the United uniqueness is different from that when each field has uniqueness constraints. If one field is duplicate and the other field is not duplicate, it can be, but if both fields are unique, they cannot be duplicate

At this time, the syntax format is different from that of individual UNIQUE or other operations. Before, you added UNIQUE or NULL after each column, that is, each field

In creating the last item of the table, a separate column is used to represent the constraint and fields are added

mysql> CREATE TABLE t_student(
    -> name VARCHAR(25) NOT NULL,
    -> sex CHAR(1)  NOT NULL,
    -> idno INT NOT NULL,
    -> UNIQUE(name,idno)
    -> );
Query OK, 0 rows affected (0.04 sec)

Here is the unique combination of name and idno. You can experiment

mysql> INSERT INTO t_student VALUES ('Linda','female');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> INSERT INTO t_student VALUES ('Linda','female',1);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t_student VALUES ('Linda','female',2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_student VALUES ('Linda','female',2);
ERROR 1062 (23000): Duplicate entry 'Linda-2' for key 't_student.name'

ERROR 1062 (23000): Duplicate entry 'Linda-2' for key 't_student.name'

All errors reported here are joint errors, not a single field

A constraint directly after a column is called a column level constraint, and a constraint added to the last row is called a table level constraint

When you need to combine multiple fields to add a constraint, you need to use table level constraints, and you can use column level constraints for others

NOT NULL only has column level constraints, but no table level constraints

Union of constraints

The above shows that multiple fields use the same constraint at the same time. If table level constraints are used at this time, multiple constraints can also be used for the same field. For example, add unique and not null to the same field. The format is

col_name  Constraint 1, constraint 2,

Here, you can add two constraints to name to see if the creation is successful

ysql> CREATE TABLE t_student(
    -> name VARCHAR(25) NOT NULL UNIQUE,
    ->  sex CHAR(1) DEFAULT 'male',
    -> idno INT NOT NULL
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> DESC t_student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(25) | NO   | PRI | NULL    |       |
| sex   | char(1)     | YES  |     | male      |       |
| idno  | int         | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

It can be found that when name is jointly constrained by NOT NULL and UNIQUE, it is used as the primary key constraint by default

In MySQL, when a field is constrained by NOT NULL and UNIQUE at the same time, the field will automatically become a field constrained by primary key. Note: it is different in Oracle!)

Therefore, there are few unions. You can directly use the primary key constraint

Primary key constraint primary key PK

The primary key constraint mentioned above, so what is a primary key constraint

Primary key constraint: a constraint

Primary key field: the field to which the primary key constraint is added is the primary key field

Primary key value: each value in the primary key field is called the primary key value

• What is the primary key?

  The primary key is the unique identification of each row, which is equivalent to the ID number of each row. One row records a corresponding data, and the basis of identifying the data is the primary key. The main primary keys are different, and other data are exactly the same, that is, the data is different

• Any table should have a primary key. Without a primary key, the table is invalid

• Characteristics of primary key: NOT NULL UNIQUE ------------ [cannot be empty and cannot be repeated]

How to add a primary key to a field

col_name data type  PRIMARY KEY,

Here you can try the effect

mysql>  CREATE TABLE t_student(
    -> name VARCHAR(25) PRIMARY KEY,
    -> sex CHAR(1) DEFAULT 'male',
    -> idno INT NOT NULL
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> DESC t_student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(25) | NO   | PRI | NULL    |       |
| sex   | char(1)     | YES  |     | male      |       |
| idno  | int         | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

The effect is the combination of NOT NULL and UNIQUE

mysql> INSERT INTO t_student VALUES ('Lin','male',1);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO t_student VALUES ('Lin','male',1);
ERROR 1062 (23000): Duplicate entry 'Lin' for key 't_student.PRIMARY'
mysql> INSERT INTO t_student (sex,idno) VALUES ('male',1);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

ERROR 1062 (23000):, ERROR 1364 (HY000):

Duplicate entry ‘Lin’ for key ‘t_student.PRIMARY '----- here is the directly recognized key PRIMARY

• Besides directly using column level constraints, primary key constraints can also use table level constraints

mysql> CREATE TABLE t_student(
    -> name VARCHAR(25),
    -> idno INT NOT NULL,
    -> PRIMARY KEY(name)
    -> );
Query OK, 0 rows affected (0.03 sec)