11. MySQL

Database DB storage, maintenance, and Management data collection (file system) database management system refers to a large-scale software that operates and manages databases, which is used to establish, use and maintain databases, to manage and control the database uniformly, and to ensure the security and integrity of the database. The data database software that the user accesses in the database through the database management system should be the database management system, the database is created and operated by the database management system Oracle: Large DB2: Can be large and small SQL Server:windowsmysql: Small and medium 1. Stop MySQL service net stop MySQL, if you encounter error 5, start cmd2 with administrator privileges, start MySQL service net start mysql3, login to MySQL mysql-u root-p abc4, change password (1) Stop my SQL Service (2) mysqld--skip-grant-tables boot server (3) new open cmd input mysql-u root-p No password required, use Mysql,update user set Password=password (' ABC ') where user= ' root ' (4) Close two cmd windows in Task Manager end MYSQLD Process (5) Restart MySQL service create a database for each app, create multiple tables in the database, and save data for entities in your program. The relationship between a row of records in a database and an object column: Word marked field: a record (entity)SQL Structured Query LanguageFourth generation language, Java third-generation language each database vendor supports the ISO SQL standard, the vendor has made its own extended classification DDL on the basis of the standard: data definition language, used to define database objects: libraries, tables, columns, etc. CREATE, ALTER, DROPDML: Data Manipulation language, Used to define database records (data), INSERT, UPDATE, DELETEDCL: Data Control Language, to define access rights and security level DQL: Data Query Language for querying records (data), select Note: SQL statements to end Operations database creation

1 Create Database mydb1; 2 Create Database character Set GBK; 3 Create Database character set gbk COLLATE gbk_chinese_ci;

Inquire

1 show databases;  // View all databases in the current database server 2 Create database mydb2;  // View the definition information for the MYDB2 database that you created earlier 3 Drop database mydb3;  // Delete the MYDB3 database created earlier

Modify

Alter Database character set UTF8;  // View the database in the server and modify the MYDB2 character set to UTF8

Delete

Drop database mydb3;  

View the database currently in use

Select Database ();

Switch database

 use MYDB2;

Operating Data Sheet

1 Create Table table name (2   1 field type, 3   2 field type, 4   ... 5   field N field type 6   );

Common data type int: integer 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; char (TEN) ' abc ', No strings, high performance varchar: Variable-length string type; varchar (TEN) ' abc ', commonly used text: string type; Large data, blob: byte type; picture video audio Date: Date type, format: YYYY-MM-DD; Time: Type: hh:mm: Sstimestamp: Timestamp type YYYY-MM-DD HH:MM:SS will be automatically assigned, not very practical, by 2023 can not be used datetime: DateTime type YYYY-MM-DD HH:MM:SS All tables in the current database

SHOW TABLES;

View field information for a table

DESC employee;

An image column is basically added to the employee table above. ALTER TABLE employee ADD image blob; Modify the job column so that it has a length of 60. ALTER TABLE employee MODIFY Job varchar (60); Delete the image column, only one column at a time. ALTER TABLE employee DROP image; Table name changed to User RENAME table employee to user; View the table creation details show create table user; Modify the table's character set to Gbkalter table user CHARACTER set GBK; Column Name name modified to usernamealter TABLE user change name username varchar (100);  Delete tables drop table user; DML operations (important) DML is an operation that increases, deletes, and changes data in a table. Do not confuse the DDL. INSERT, UPDATE, DELETE in MySQL, both the string type and the date type are enclosed in single quotation marks. ' Tom ' 2015-09-04 ' null value: null (does not account for memory) SELECT * from table name; Inserting insert insert into table name (column name 1, column Name 2 ...) VALUES (column value 1, column value 2 ...); If you insert a null value, use NULL to insert the date and character, using quotation marks around each column to give the value, the column value is not written sqlyog82 third-party Client interface Modification updateupdate table name set column Name 1 = column value 1, column Name 2 = column Value 2 ... where column name = value where is equal to if

11. MySQL