MySQL (1)

Time:2020-9-29

MySQL service:

  • Start: Net start nysql
  • Stop: net stop MySQLNote:Generally, the service is started by default. Only when the service is started, it is necessary to run CMD in administrator mode.

Operation database:

  • Log in to the database: MySQL – urot – proot u represents the user name, P represents the password (this will expose the password) / – – > so MySQL – uroot – P will not be exposed if it is written in this way. It will let you enter the password: enter password: Enter
  • Show all databases: show databases; remember to add the following command after writingsemicolon;   
  • Create database: create database database name; for example: create database db1; create db1 database
  • Create database 2: create database if not exists
  • Database setting code: create database db3 character set GBK; create database db3 and set the code as GBK. The default database code is set by yourself during installation
  • View the information of a single database: show create database db1; view the information of db1
  • Drop database: drop database db1; drop database db1
  • Using database: use DB2; using database DB2
  • To view the database in use:select database();   

Database code:

  • Modify database code: alter database db3 default character set utf8; modify db3 code to UTF-8 format
  • View database code: Show variables like ‘character%’; view database code
  • Set character_ set_ client=gbk;  set character_ set_ connection=gbk;  set character_ set_ results=gbk;
  • Set code: set names GBK; set the above three at a time

Exit: to exit the database connection, press Ctrl + C

Operation table:

The premise of operation table is: use database name;

  • Create table: format create table table table name (field name 1, field type 1, field name 2, field type 2 ); as shown below:
create table stu(id int,name varchar(100),age int);

Common MySQL field types:

classification Type name explain
Integer type tinyInt Very small integer (used when selecting two integers, such as gender)
  smallint Small integer
  mediumint Medium sized integer
  int(integer) Normal size integers (most commonly used)
Decimal type float Single-precision floating-point
  double Double precision floating point number (most commonly used)
  decimal(m,d) Compact strict fixed point number
Date type year YYYY 1901~2155
  time HH:MM:SS -838:59:59~838:59:59
  date YYYY-MM-DD 1000-01-01~9999-12-3
  datetime YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~ 9999-12-31 23:59:59
  timestamp YYYY-MM-DD HH:MM:SS 1970~01~01 00:00:01 UTC~2038-01-19 03:14:07UTC
  • View tables: show tables; view all tables in the database
  • View table structure: desc stu; view stu’s table structure
  • To view the statement of creating a table: show create table stu; to view the statement of creating stu
  • Create a table with the same table structure: create table stu1 like stu; create ST1, stu1 table structure and stu structure are the same

Delete table:

  • Drop table stu1: drop table stu1
  • To determine whether there is a deletion: drop table if exists stu1; to see whether stu1 exists, delete exists; if not, delete fails

Modification table:

  • Add field to table: alter table stu add field type; eg: alter table stu add Sorc int; add field Sorc to table stu and set type to int
  • Modify field type: alter table stu modify Sorc varchar (199); modify field Sorc type to varchar (199)
  • Modify the field name: alter table stu change Sorc price varchar (10); change Sorc field to price field type: varchar (10);
  • Modify the table name: rename table stu to stu1; change the table stu to stu1

Delete table:

  • To delete a field in a table: alter table stu drop price; to delete a field price
  • Delete all data in the table: delete from stu1; delete stu1 table
  • Destroy all table data: truncate table stu1; destroy table stu1

Personal learning, the content is brief