Build table
1. Table building syntax
Table creation belongs to DDL statements, including create, drop and alter
create table Table name (field name 1 data type, field name 2 data type, field name 3 data type,)
2. Data type
varchar: variable length string, which is slow and saves space
char: fixed length string. Regardless of the actual data length, it allocates a fixed length to store data, which is fast and wastes space
int (11): integer in number
bigint: long integer in number
float: single precision floating point data
Double: double precision floating point data
Date: short date type
datetime: long date type
clob: a large character object that can store up to 4G strings and more than 255 characters
Blob: binary large object, which is specially used to store streaming media data such as pictures, sounds and videos. When inserting data into a blob type field, you need to use an IO stream
3. Table operation
3.1 create table:
create table t_student(
no int,
name varchar(32),
sex char(1),
age int(3),
email varchar(255)
);
mysql> create table t_student(
-> no int,
-> name varchar(32),
-> sex char(1),
-> age int(3),
-> email varchar(255)
-> );
Query OK, 0 rows affected (0.01 sec)
3.2 delete table:
drop table if exists t_student;
3.3 insert data:
Note: the field name and value should correspond one by one, the quantity should correspond, and the data type should correspond.
If the insert statement is executed successfully, there must be one more record. If no value is specified for other fields, the default value is null
mysql> insert into t_student (no,name,sex,age,email) values(1,'zhangsan','m',20,'zhangsan@163.com')
-> ;
Query OK, 1 row affected (0.01 sec)
mysql> select * from t_student;
+------+----------+------+------+------------------+
| no | name | sex | age | email |
+------+----------+------+------+------------------+
| 1 | zhangsan | m | 20 | zhangsan@163.com |
+------+----------+------+------+------------------+
1 row in set (0.00 sec)
3.3.1 specifying default values
create table t_student( no int, name varchar(32), sex char(1) default 'm', age int(3), email varchar(255) ); mysql> desc t_student; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | no | int(11) | YES | | NULL | | | name | varchar(32) | YES | | NULL | | | sex | char(1) | YES | | m | | | age | int(3) | YES | | NULL | | | email | varchar(255) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ insert into t_student(no) values(1); +------+------+------+------+-------+ | no | name | sex | age | email | +------+------+------+------+-------+ | 1 | NULL | m | NULL | NULL | +------+------+------+------+-------+
If the preceding field name is omitted, it is written! So write all the values
mysql> insert into t_student values(2,'lisi','f',13,'123456'); mysql> select * from t_student; +------+------+------+------+--------+ | no | name | sex | age | email | +------+------+------+------+--------+ | 1 | NULL | m | NULL | NULL | | 2 | lisi | f | 13 | 123456 | +------+------+------+------+--------+
3.3.2 insert date
str_to_date: converts the string varchar type to date type
date_format: converts the date type to a varchar string type with a certain format
drop table if exists t_user; create table t_user( id int, name varchar(32), birth date, ); mysql> desc t_user; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(32) | YES | | NULL | | | birth | date | YES | | NULL | | +-------+-------------+------+-----+---------+-------+
Note: all identifiers in the database are lowercase, and words are connected with underscores.
3.3.2.1 str_to_date
Insert data:
str_to_date: convert the string varchar type to date type. You can convert varchar to date type data. It is usually used in insert
If the date string provided is' 1990-01-01 ', this function is not required.
Syntax format:
str_to_date ('string date ',' date format ')
Date format of mysql:
% Y: year
% m: month
% d: day
% h: when
% i: minutes
% s: seconds
insert into t_user(id,name,birth) values(1,'zhangsan',str_to_date('01-10-1990','%d-%m-%Y'));
mysql> select * from t_user;
+------+----------+------------+
| id | name | birth |
+------+----------+------------+
| 1 | zhangsan | 1990-10-01 |
+------+----------+------------+
3.3.2.2 date_format
date_format: usually used in query date. Format the displayed date.
select id,name,date_format(birth,'%m/%d/%Y') as birth from t_user; +------+----------+------------+ | id | name | birth | +------+----------+------------+ | 1 | zhangsan | 10/01/1990 | +------+----------+------------+
3.3.3 date and datetime
drop table if exists t_user; create table t_user( id int, name varchar(32), birth date, create_time datetime );
mysql short date default format:% Y-%m-%d
mysql long date default format:% Y -% m -% d% H:% I:% s
insert into t_user(id,name,birth,create_time) values(1,'zhangsan','1990-01-01','2020-03-18 15:49:50'); mysql> select * from t_user; +------+----------+------------+---------------------+ | id | name | birth | create_time | +------+----------+------------+---------------------+ | 1 | zhangsan | 1990-01-01 | 2020-03-18 15:49:50 | +------+----------+------------+---------------------+
3.3.4 current time (now):
Use the now() function, and the obtained time has: hour, minute and second information, which is of type datetime
insert into t_user (id,name,birth,create_time) values(2,'lisi','1990-01-01',now()); mysql> select * from t_user; +------+----------+------------+---------------------+ | id | name | birth | create_time | +------+----------+------------+---------------------+ | 1 | zhangsan | 1990-01-01 | 2020-03-18 15:49:50 | | 2 | lisi | 1990-01-01 | 2021-09-18 11:01:44 | +------+----------+------------+---------------------+
3.4 modify update
Syntax format:
update Table name set Field name 1 = Value 1, field name 2 = Value 2, field name 3 = Value 3...where condition update t_user set name = 'jack',birth = '2000-10-11' where id = 2; mysql> select * from t_user; +------+----------+------------+---------------------+ | id | name | birth | create_time | +------+----------+------------+---------------------+ | 1 | zhangsan | 1990-01-01 | 2020-03-18 15:49:50 | | 2 | jack | 2000-10-11 | 2021-09-18 11:01:44 | +------+----------+------------+---------------------+
Update all without where!
mysql> update t_user set name = 'jack',birth = '2000-10-11'; mysql> select * from t_user; +------+------+------------+---------------------+ | id | name | birth | create_time | +------+------+------------+---------------------+ | 1 | jack | 2000-10-11 | 2020-03-18 15:49:50 | | 2 | jack | 2000-10-11 | 2021-09-18 11:01:44 | +------+------+------------+---------------------+
3.5 deleting data
Syntax format:
delete from Table name where condition delete from t_user where id = 2; +------+------+------------+---------------------+ | id | name | birth | create_time | +------+------+------------+---------------------+ | 1 | jack | 2000-10-11 | 2020-03-18 15:49:50 | +------+------+------------+---------------------+
Note: if there are no conditions, all the data in the whole table will be deleted