Database-MySQL

MySQL

One login to create an operation query

1. Start the database

net start mysql;

2. Log in to the database

mysql -uroot -p;

3. Query all databases in the database server

show databases;

4. Operate a certain database

use + 数据库名字;

5. Query the data table information in the database

use + 数据库名字;
select * from 数据表名

6. Exit the database

exit;

7. Create a database

create database test;

8. Create a data table

use test;
CREATE TABLE pet(name VARCHAR(20),owner VARCHAR(20),species VARCHAR(20),sex CHAR(1),birth DATE,death DATE);

9. View all data tables in a database

use test;
show tables;

10. Insert data into the data table

mysql> INSERT INTO pet
    -> VALUES('狗','周星驰','狗','公','1990-01-01',NULL);
  1. View inserted data
mysql> select * from pet;

Two data type and type selection

mysql commonly used data types

(1) Value

mysql> create table testType(number TINYINT);
Query OK, 0 rows affected (1.22 sec)

View testType

mysql> describe testType;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| number | tinyint | YES  |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
1 row in set (0.11 sec)

Insert data into the table

INSERT INTO testType VALUES(127);
Insert picture description here


Insert picture description here

(2) Date/time
(3) String
Date format selection is based on format
Numerical value and string selection is based on size

Three data records add, delete, modify, and check operations

1. Delete data

Insert picture description here


Insert picture description here

2. Modify the data

update pet set name='旺旺财' where owner='周星驰';
Insert picture description here


Additions, deletions, and modifications: INSERT DELETE UPDATE SELECT

Four MySQL table building constraints

1. Primary key constraints

Insert picture description here


Keywords: primary key
Method 1:

mysql> create table user(
    -> id int primary key,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.11 sec)
Insert picture description here


Insert picture description here
mysql> insert into user values(1,'张三');
Query OK, 1 row affected (0.03 sec)

mysql> select * from user
    -> ;
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
+----+------+
1 row in set (0.02 sec)

mysql>
mysql> insert into user values(1,'张三');
ERROR 1062 (23000): Duplicate entry '1' for key 'user.PRIMARY'
mysql>
mysql> insert into user values(2,'张三');
Query OK, 1 row affected (0.02 sec)
mysql> select * from user;
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
|  2 | 张三 |
+----+------+
2 rows in set (0.00 sec)

mysql>
mysql> insert into user values(NULL,'张三');
ERROR 1048 (23000): Column 'id' cannot be null
mysql>

Method 2: The combination of joint formation can not be repeated.
Keyword: primary key (id, name)
The combination of id and name is constrained to be unique

mysql> insert into user values(NULL,'张三');
ERROR 1048 (23000): Column 'id' cannot be null
mysql> create table user2(
    -> id int,
    -> name varchar(20),
    -> password varchar(20),
    -> primary key(id,name)
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql> select * from user2;
Empty set (0.01 sec)

mysql> describe user2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(20) | NO   | PRI | NULL    |       |
| password | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql>
mysql> insert into user2 values(1,'张三');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into user2 values(1,'张三','123');
Query OK, 1 row affected (0.88 sec)

mysql> insert into user2 values(1,'张三','123');
ERROR 1062 (23000): Duplicate entry '1-张三' for key 'user2.PRIMARY'
mysql> insert into user2 values(2,'张三','123');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user2 values(1,'李四','123');
Query OK, 1 row affected (0.03 sec)

mysql> select * from user2;
+----+------+----------+
| id | name | password |
+----+------+----------+
|  1 | 张三 | 123      |
|  1 | 李四 | 123      |
|  2 | 张三 | 123      |
+----+------+----------+
3 rows in set (0.00 sec)

2. Auto-increment constraint: automatically generate id

Keyword: auto_increment

mysql> create table user3(
    -> id int primary key auto_increment,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.10 sec)
mysql> insert into user3(name) values('李四');
Query OK, 1 row affected (0.06 sec)

mysql> select * from user3;
+----+------+
| id | name |
+----+------+
|  1 | 李四 |
+----+------+
1 row in set (0.00 sec)

Add again

mysql> insert into user3(name) values('李四');
Query OK, 1 row affected (0.01 sec)

mysql> select * from user3;
+----+------+
| id | name |
+----+------+
|  1 | 李四 |
|  2 | 李四 |
+----+------+
2 rows in set (0.00 sec)

3. Forget to create a primary key constraint

mysql> create table user4(
    -> id int,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> desc user4
    -> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

Use Add Constraint Statement

mysql> alter table user4 add primary key(id);
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user4
    -> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

Delete constraint statement

mysql> alter table user4 drop primary key;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user4
    -> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

Use modify to add fields and modify constraints

mysql> alter table user4 modify id int primary key;
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user4
    -> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)