CMD operation MySQL

1. Introduction to the Database

• Database classification
  • DDL (data definition Language): Definition Language for database objects: libraries, tables, columns, etc. CREATE, ALTER, DROP
  • DML (Data manipulation Language): Database manipulation language, which is used to define the records (data) of the databases; INSERT, UPDATE, DELETE
  • DCL (Data Control Language): The language used to define access rights and security levels;
  • DQL (data query Language): The Language for Querying records (data). Select query
• Common Types of data:
  • int: integral type
  • Double: floating-point type, for example double (5,2), represents up to 5 bits, which must have 2 decimal places, i.e. the maximum value is 999.99
  • Char: Fixed length string type, if the defined length is 3,char (3), the input value is ' ABCD ', the length exceeds 3, will error
  • VARCHAR: variable length string type, defined varchar (10), the value ' abc ' length is 3, the system will automatically compress the length, save space. But the performance of varchar is not as high as Char
  • Text: String type
  • BLOB: Byte type, storing multimedia files (picture, audio, video)
  • Date: Day type in the format: YYYY-MM-DD
  • Time: The format of the type: HH:MM:SS
  • Timestamp: Timestamp type, YYYY-MM-DD hh:mm:ss, automatically assigned
  • DateTime: DateTime type YYYY-MM-DD HH:MM:SS

2.CMD Operation MySQL

• Basic Database operations:
  • CMD start MySQL service: net start MySQL
  • cmd stop MySQL service: net stop MySQL
  • CMD login mysql:mysql-u user name-p, if you want to link the database of other computers, you need to specify the IP address of the computer with-H: mysql-h IP address-u user name-P
  • CMD exit mysql:quit;
• Change Password:
  • 1. Stop the MySQL service first
  • 2. Enter Mysqld--skip-grant-tables under CMD to start the server, do not move the cursor (do not close the window)
  • 3. Open a CMD window, enter Mysql-u root-p do not need a password, enter and then press ENTER again
  • 4. Using MySQL's own database, enter use MySQL in the first cmd;
  • 5. Then enter the SQL statement that changed the password: Update user set Password=password (' abc ') WHERE user= ' root ';
  • 6. Close the two CMD window and end the MYSQLD process in Task Manager
  • 7. Restart the MySQL service on the Service Management page and the password modification is complete.

3.DDL: Manipulating databases, tables, columns

• manipulating databases
  • See what databases are in the current account: show databases;
  • Creating database: Create database name;
  • Specify a character set when creating a database: Create database name character Set GBK;
  • View the definition information for the created data: show create database name;
  • View supported character sets: show character set;
  • Delete the database: drop database name;
  • Modify database charset: ALTER DATABASE name character set GBK;
  • View the database currently in use: Show ();
  • Switch database: use database name;
• Operational Data Sheet:
  • Creating Table: Create table table name (Field 1 field type, field 2 field type, field N field type);
  • View all tables in the database: show TABLES;
  • View the table's fields: DESC table name;
  • Basically add a column to the table: ALTER table name Add field name fields type
  • Modify the job column so that its length is 60:alter table table name MODIFY job varchar (60);
  • Delete the image column, only one column at a time: ALTER TABLE employee DROP image;
  • Modify table name: RENAME table name to new table name;
  • View the table's creation details: Show create table table name;
  • Modify the table's character set to Gbk:alter table user CHARACTER set GBK;
  • Modify Column Name: ALTER TABLE User Change original column name new column field type;
  • Delete tables: drop table user;

4.DML Operating table Data

• Insert
• Update
• Delete: Deletes the data in the table, the structure of the table is still there, and the deleted data can be retrieved.
• Truncate: Deletes data from the table and clears the table data. Truncate DELETE is to drop the table directly and then create the same new table. Deleted data cannot be retrieved, execution speed is faster than delete

CMD operation MySQL