Basic operation of database
In MySQL database, for a MySQL example, multiple databases can be included.
After connecting to MySQL, we can view the existing databases through show databases. There are already some libraries, among which information ﹣ u schema, auth, MySQL and performance ﹣ schema are built-in (different versions are slightly different).
How to create your own database? Use the create database database database name; OK.
Create database database name;
Here we have successfully created the MySQL database, and then we show databases; again, you can see that MySQL is already in the list.
After building this database, I don’t think it’s good to get a name. I want to change it to MySQL study, because there is no table in it, so I want to delete and rebuild it (if there is a table, it’s still a little complicated to change the database name, so it’s necessary to be careful when naming it).
Drop database database database name is used to delete database; statement
Drop database database name;
Of course, deleting a library is a dangerous action. In order to avoid running away from deleting a library, two things must be done before deleting a library: one is to confirm that you have no fever, and the other is to confirm that you are right. My library has just been built here, so it’s confirmed.
Then we created the MySQL study database. Now we want to use this database. What should we do?
We need to switch to this database first and use the use database name; statement.
Use database name;
After switching to the corresponding library, we can use show tables; to view the tables in the library. Because the one just created is still hot, there is nothing in it.
Basic operation of table
Let’s create the first table. Use the CREATE TABLE statement, and of course, you need to define the column properties of the table.
Create table table name（ Column name 1 data type [column's properties], Column name 2 data type [column's properties], ... Column name n data type [column properties] );
For example, we create the simplest user table, which contains ID of type int and name column of varchar (5).
CREATE TABLE user ( id INT, name VARCHAR(5) );
For the created table, we can use the following command to view the details.
DESC table name; Description table name; Explain table name; Show columns from table name; Show fields from table name;
For the name of the database, it is not convenient to modify. What about the table? It is easy to change the table name. Use the alter table table table name rename to the new table name; just
Alter table table name rename to new table name;
This changes the table name to user? Info. What if you want to delete the table? Use drop table table table name;
Drop table table name;
Basic operation of columns
After deletion, I silently created the user table back, because I still need to use it.
If we want to add column properties to the user table, what do we do?
Alter table table name add column column column name data type [column properties];
Add a new age column.
If you want to modify a column, you can use the following statement. Modify only supports modifying column properties, while change supports modifying column names and column properties.
#Modify column properties Alter table table name modify column name new data type [new attribute]; #Modify column name Alter table name change old column name new column name new data type [new property];
For example, age, I want to change it to user u age, and the length only needs to be defined as 2 bits.
To delete a column, since there are add, modify and change, there should be drop. That’s right.
Alter table table name DROP column name;
Basic operation of data
With a table, you can also operate the basic columns, but what we usually call “add, delete, modify and query” is that there is no data, so we need to have some data.
Using insert to insert data, when a column is not specified, it is inserted in the order of the columns, and the value of the column must contain the full number of columns, while the declaration column is much freer
Insert into table name values (value of column 1, value of column 2,...); Insert into table name (column 1, column 2,...) values (column 1 value, column 2 value,...);
For example, insert data in the user table.
INSERT INTO user VALUES(1,'zs'); INSERT INTO user(id,name) VALUES(2,'ls'); INSERT INTO user(name,id) VALUES('ww',3);
As for the query, I’m sure you’ve seen that using the select statement, select * from table name is the most basic query, * means to view all column properties. Generally, the query needs to be filtered with the conditions such as where, and the operation is more complex. Later, we will go further.
Select column 1, column 2,... From table name [where condition];
To modify the data, we use the update statement. The essence of update is to modify the data that meets the conditions, that is, to query out the corresponding data first, and then make changes to it. Therefore, general queries also need to be combined with where statements. Statements without where will change all the data in the table, which should be paid attention to in daily life.
Update table name set column 1 = new value 1 of column, column 2 = new value 2 of column,... [where condition]
As for deletion, use the delete statement, which is also a dangerous statement. Pay attention to take where with you, unless you really want to delete all the data, or you have a fever for a while.
Delete from table name [where condition];
At this point, the basic use of MySQL is almost OK. Of course, there are some complex statements. We will continue to learn later.