1, Database operation
1.1 create database
- Using sql statements to create
Syntax: create database database name charset utf8;
For example, create a database named test1
create database test1; - Create using the graphical interface Navicat
Select connection - right click to select "New Database", enter the database name, and click ok
1.2. View database
-
Using sql statements to view
Syntax: show databases; -
View the creation information of an existing database
Syntax: show create database database name; -
Viewing creation information using a graphical interface
Select the library name you want to view and right click Edit Database
1.3 modify database
- sql statement modification
Syntax: alter database database name character set new code;
For example, modify the code of test1 library to gbk - Graphical interface modification
Select the library name and right-click to select "Edit Database"
1.4 delete database
Note: once the database is deleted, the tables and data in the database will be deleted together
-
Delete using SQL statement
Syntax: drop database library name;
For example: delete test1 Library
drop database test1 -
Delete using graphical interface
Select the library name to delete - right click - select Delete Database
2, Basic operation of table
2.1 basic concept of table
-
Relationship between database and table
Each database contains N tables and is represented in the database -
Table (2D table)
Row and column composition: complex data can be saved in a simple table
Each row in the table is a complete record
The columns in the table are used to store information about the characteristics in each record
2.2 creating tables
- Using sql statements to create
Syntax:
create table Table name(
Field name 1 field type 1 [integrity constraint],
Field name 2 field type 2 [integrity constraint],
......
Field name n Field type n [integrity constraint]
)
be careful:
- Table names cannot use keywords, such as database select where
- Each group of field name field types is separated by "," but the last one does not need ","
For example, create a student information table students(id,name,gender,age,height,cls_id), etc
create table students(
id int unsigned primary key auto_increment not null,
name varchar(20) default '',
age tinyint unsigned default 0,
height decimal(5,2),
gender enum('male','female','secrecy') default 'secrecy',
cls_id int unsigned default 0,
is_delete bit default 0
);
b. Using a graphical interface
Click the selected library name, double-click and select "Table" right click – "New Table"
2.3 viewing table
-
View the basic structure of the table
Syntax: desc table name
For example: view the structure of the student tablemysql> desc students; +-----------+----------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+----------------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | | | | age | tinyint(3) unsigned | YES | | 0 | | | height | decimal(5,2) | YES | | NULL | | | gender | enum('male','female','secrecy') | YES | | secrecy | | | cls_id | int(10) unsigned | YES | | 0 | | | is_delete | bit(1) | YES | | b'0' | | +-----------+----------------------------+------+-----+---------+----------------+ -
View the detailed structure of the table (you can also view the storage engine and character set)
Syntax: show create table name;
For example: view the detailed structure of the student tablemysql> show create table students \G; *************************** 1. row *************************** Table: students Create Table: CREATE TABLE `students` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT '', `age` tinyint(3) unsigned DEFAULT '0', `height` decimal(5,2) DEFAULT NULL, `gender` enum('male','female','secrecy') DEFAULT 'secrecy', `cls_id` int(10) unsigned DEFAULT '0', `is_delete` bit(1) DEFAULT b'0', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
2.4 modify table structure
2.4.1 modify table name
Syntax: alter table old table name rename new table name
For example, change the students table name to student;
alter table students rename student;
Or rename table old table name to new table name
2.4.2 modifying field data types
Syntax: alter table name modify column name new field type
For example, change the data type of id in the student table to int(10)
mysql> alter table student modify id int(10);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> desc student;
+-----------+----------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------------------------+------+-----+---------+-------+
| id | int(10) | NO | PRI | NULL | |
| name | varchar(20) | YES | | | |
| age | tinyint(3) unsigned | YES | | 0 | |
| height | decimal(5,2) | YES | | NULL | |
| gender | enum('male','female','secrecy') | YES | | secrecy | |
| cls_id | int(10) unsigned | YES | | 0 | |
| is_delete | bit(1) | YES | | b'0' | |
+-----------+----------------------------+------+-----+---------+-------+
2.4.3 modifying data types of multiple fields
grammar:
alter table Table name modify Column name 1 new field type 1,
modify Column name 2 new field type 2,
.....
modify Listing n New field type n;
For example, modify id int (8) and name varchar (30) in the student table
mysql> alter table student modify id int(8),modify name varchar(30);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+-----------+----------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------------------------+------+-----+---------+-------+
| id | int(8) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | 0 | |
| height | decimal(5,2) | YES | | NULL | |
| gender | enum('male','female','secrecy') | YES | | secrecy | |
| cls_id | int(10) unsigned | YES | | 0 | |
| is_delete | bit(1) | YES | | b'0' | |
+-----------+----------------------------+------+-----+---------+-------+
2.4.4 modify field name
Syntax: alter table table name change old field name new field name old field type
For example, in the student table, gender is changed to sex
mysql> alter table student change gender sex enum('male','female','secrecy');
2.4.5 modify field name and field type
Syntax: alter table table name change old field name new field name new field type
For example, in the student table, sex is changed to gender data type varchar(2)
mysql> alter table student change sex gender varchar(2); mysql> desc student; +-----------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------------------+------+-----+---------+-------+ | id | int(8) | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | age | tinyint(3) unsigned | YES | | 0 | | | height | decimal(5,2) | YES | | NULL | | | gender | varchar(2) | YES | | NULL | | | cls_id | int(10) unsigned | YES | | 0 | | | is_delete | bit(1) | YES | | b'0' | | +-----------+---------------------+------+-----+---------+-------+
2.4.6. Add field
Syntax: alter table table name add field name field type [integrity constraint] [first |after field name]
Example 1: add the score field at the end of the student table
mysql> alter table student add score smallint(3) not null default 60; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from student; Empty set (0.01 sec) mysql> desc student; +-----------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------------------+------+-----+---------+-------+ | id | int(8) | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | age | tinyint(3) unsigned | YES | | 0 | | | height | decimal(5,2) | YES | | NULL | | | gender | varchar(2) | YES | | NULL | | | cls_id | int(10) unsigned | YES | | 0 | | | is_delete | bit(1) | YES | | b'0' | | | score | smallint(3) | NO | | 60 | | +-----------+---------------------+------+-----+---------+-------+
Example 2: add the phone field in the first position of the student table
mysql> alter table student add phone char(11) not null first; mysql> desc student; +-----------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------------------+------+-----+---------+-------+ | phone | char(11) | NO | | NULL | | | id | int(8) | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | age | tinyint(3) unsigned | YES | | 0 | |
Example 3: add the class field after age in the student table
mysql> alter table student add class varchar(6) after age;
2.4.7 delete field
Syntax: alter table name drop column name
Example: delete the phone in the student table
mysql> alter table student drop phone;
3, Delete table
- Delete using sql statement
drop table name;
mysql> create table test1(
-> id int(8)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+--------------------+
| Tables_in_students |
+--------------------+
| student |
| test1 |
+--------------------+
mysql> drop table test1;
mysql> show tables;
+--------------------+
| Tables_in_students |
+--------------------+
| student |
+--------------------+
Delete all fields in the table and keep the table structure. Syntax: truncate table table name
b. Delete using graphical interface
4, Insert data
1. Insert a piece of data for all fields
Syntax:
insert [into] table name [(field name 1, field name 2, field name 3,...)] values|value (value 1, value 2, value 3...);
For example, create a table student(sno primary key increases automatically, sname is not empty, age sex is unique by default)
mysql> create table students(
id int unsigned primary key auto_increment not null,
name varchar(20),
age smallint,
sex enum('male','female','secrecy') default 'secrecy',
email varchar(20) unique );
mysql> desc students;
+-------+----------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| age | smallint(6) | YES | | NULL | |
| sex | enum('male','female','secrecy') | YES | | secrecy | |
| email | varchar(20) | YES | UNI | NULL | |
+-------+----------------------------+------+-----+---------+----------------+
Insert data for all fields of the student table
Note: field names can be added or not added to the table. If field names are not added, the sequence of inserted values shall be the same as that of table structure fields
# Insert a single piece of data
mysql> insert into students (name,age,sex,email) value ("laowang",18,'male','sss@qq.com');
mysql> insert into students (name,age,sex,email) value ("laowang",18,'male','sss@qq.com');
# Insert multiple pieces of data at once
mysql> insert into students (name,age,sex,email) values ("laoyang",19,'female','sfaasss@qq.com'),
('laoli',20,'female','1dsf@qq.com');
mysql> select * from students;
+----+----------+------+------+----------------+
| id | name | age | sex | email |
+----+----------+------+------+----------------+
| 1 | laowang | 18 | male | sss@qq.com |
| 4 | laozhang | 19 | female | aasss@qq.com |
| 5 | laoyang | 19 | female | sfaasss@qq.com |
| 6 | laoli | 20 | female | 1dsf@qq.com |
+----+----------+------+------+----------------+
2. Insert a piece of data for the specified field
Syntax: insert [into] table name (field name 1, field name 2, field name 3,...) values|value (value 1, value 2, value 3...);
For example, insert data for sname and age in the table student
insert into students (name,age) value ('xiaomie',20)
select * from students
mysql> select * from students;
+----+----------+------+--------+----------------+
| id | name | age | sex | email |
+----+----------+------+--------+----------------+
| 1 | laowang | 18 | male | sss@qq.com |
| 4 | laozhang | 19 | female | aasss@qq.com |
| 5 | laoyang | 19 | female | sfaasss@qq.com |
| 6 | laoli | 20 | female | 1dsf@qq.com |
| 7 | xiaomie | 20 | secrecy | NULL |
+----+----------+------+--------+----------------+
3. Insert a piece of data in SET mode
Syntax:
insert [into] table name set field name 1 = field value [, field name 2 = field value 2...];
For example, use the set method to insert data into all fields of the student table
insert into students set name="Di Renjie",age=18,sex='male',email='fsdfs@qq.com';
4. Insert multiple pieces of data at the same time
Syntax:
insert [into] Table name[(Field name 1,Field name 2,Field name 3,....)] values|value(Value 1,Value 2,Value 3....),
(Value 1,Value 2,Value 3....),
.......
For example, insert multiple pieces of data into the table student at the same time
a. Insert multiple pieces of data for all fields
mysql> insert into students (name,age,sex,email) values ("laoyang",19,'female','sfaasss@qq.com'),
('laoli',20,'female','1dsf@qq.com');
b. Inserts multiple pieces of data into the specified field
insert into students (name,age) values ('bertwu',18),
('tom',20)
5. Insert query results
Syntax:
insert [into] table name (field name 1, field name 2..) select field name 1, field name 2... from table where condition
Note: the number and type of fields inserted into the table and queried should be consistent
For example, insert the data query results in the student table into t_student table
1. Create t_student table
create table t_student (
id int unsigned primary key auto_increment not null,
name varchar(20),
age smallint,
sex enum('male','female','secrecy') default 'secrecy',
email varchar(20) unique);
# Query table structure, consistent with student table
mysql> desc t_student;
+-------+----------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| age | smallint(6) | YES | | NULL | |
| sex | enum('male','female','secrecy') | YES | | secrecy | |
| email | varchar(20) | YES | UNI | NULL | |
+-------+----------------------------+------+-----+---------+----------------+
2. Insert the query result into t_student table
insert into t_student select * from students where sex ='female'; mysql> select * from t_student; +----+----------+------+------+----------------+ | id | name | age | sex | email | +----+----------+------+------+----------------+ | 4 | laozhang | 19 | female | aasss@qq.com | | 5 | laoyang | 19 | female | sfaasss@qq.com | | 6 | laoli | 20 | female | 1dsf@qq.com | +----+----------+------+------+----------------+ 3 rows in set (0.00 sec)
5, Update data
1. Update specified data
Syntax:
update table name set field name 1 = value 1 [, field name 2 = value 2...] where condition
For example, change the name of laowang in the students table to "Xiao Qiao"
update students set name="Little Joe" where name = 'laowang'
2. Update all data
Syntax:
update table name set field name 1 = value 1 [, field name 2 = value 2...]
For example, change the age in the student table to 18
update students set name="Little Joe" where name = 'laowang' mysql> select * from students; +----+-----------+------+--------+----------------+ | id | name | age | sex | email | +----+-----------+------+--------+----------------+ | 1 | Little Joe | 18 | male | sss@qq.com | | 4 | laozhang | 18 | female | aasss@qq.com | | 5 | laoyang | 18 | female | sfaasss@qq.com | | 6 | laoli | 18 | female | 1dsf@qq.com | | 7 | xiaomie | 18 | secrecy | NULL | | 8 | Di Renjie | 18 | male | fsdfs@qq.com | | 10 | tom | 18 | secrecy | NULL | +----+-----------+------+--------+----------------+
6, Delete data
1. Delete specified data
Syntax:
delete from table name where condition
For example, delete student information whose gender is confidential
mysql> delete from students where sex="secrecy";
2. Delete all data
Syntax: delete from table name
For example: delete all data in the student table
delete from students
3.truncate keyword delete data
Syntax: truncate table name
For example: delete t_ All data in student table
truncate t_student
The difference between truncate and delete to delete all records (fields)
-
delete is the data manipulation language DML and truncate is the Data Definition Language DDL
-
delete data is deleted one by one
truncate deleting data is to preserve the table structure and directly delete the data. The status of the deleted table is equivalent to that of a new table
truncate is more efficient than delete -
Delete to delete data, the number of deleted rows will be displayed
truncate is not displayed -
After deleting a record, add data to the table again. The self increment value will be + 1 at the original maximum value
truncate after deleting a record, it will automatically increase from 1 -
The delete operation can be rolled back
The truncate operation is implicitly committed and cannot be rolled back
Rollback is similar to the recycle bin of your computer. The delete d data can be recovered, while the truncate d data cannot be recovered.