This article brings you a summary of the basic knowledge of mysql. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.
1. Startup and Exit
1. Enter MySQL:
Start MySQL Command Line Client (MySQL DOS interface), directly Just enter the password you used during installation. The prompt at this time is: mysql>
or open the terminal and enter the SQL statement:
mysql –uroot –p123
2. Exit MySQL:
quit or exit
2. Library operations
1. Create database
Command: create database For example : Create a database named xhkdb mysql> create database xhkdb;
2. Display all databases
Command: show databases (note: there is an s at the end) mysql> show databases;
3. Delete database Command: drop database For example: delete the database named xhkdb mysql> drop database xhkdb;
4. Connect to the database Command: use For example: if the xhkdb database exists, try to access it: mysql> use xhkdb; Screen prompt: Database changed
5. The currently selected (connected) database mysql> select database();
6. The table information contained in the current database: mysql> show tables; ( Note: there is an s at the end
3. Table operation, you should connect to a database before the operation 1. Create table Command: create table
[,..]);
mysql> create table MyClass(
> id int(4) not null primary key auto_increment,
> name char(20) not null,
> sex int(4) not null default '0',
> degree double(16,2));
Copy after login
2. Get table structure command : desc table name, or show columns from table name
mysql> desc MyClass;
mysql> show columns from MyClass;
Copy after login
3. Delete table Command: drop table
For example: delete the table named MyClass mysql> drop table MyClass;
4. Insert data Command: insert into
[( [,..For example, insert two records into the table MyClass. These two records represent: the score of Tom named No. 1 is 96.45, and the score of Tom No. 2 is 96.45. The score of Joan, numbered 3, is 82.99, and the score of Wang, numbered 3, is 96.5.
mysql> insert into MyClass values(1,'Tom',96.45),(2,'Joan',82.99), (2,'Wang', 96.59);
Copy after login
5. Query the data in the table 1), Query all rows command : select from < table name> where < expression> For example: view all data in table MyClass mysql> select * from MyClass; 2), Query the first few rows of data For example: View the first 2 rows of data in the table MyClass mysql> select * from MyClass order by id limit 0,2;
6. Delete Data in the table Command: delete from table name where expression For example: delete the record numbered 1 in the table MyClass mysql> delete from MyClass where id=1;
7, Modify the data in the table: update table name set field = new value,... where condition mysql> update MyClass set name='Mary' where id=1;
8. Add to the table Field: Command: alter table table name add field type other; For example: a field passtest is added to the table MyClass, the type is int(4), the default value is 0 mysql> alter table MyClass add passtest int(4) default '0'
9. Change the table name: Command: rename table original table name to new table name; For example: change the name of table MyClass to YouClass mysql> rename table MyClass to YouClass; Update field content update table name set field name = new content update table name set field name = replace(field name,'old content','new Content'); Add 4 spaces in front of the article update article set content=concat(' ',content);
4. Introduction to field types 1 ． INT[(M)] type: normal size integer type 2． DOUBLE[(M,D)] [ZEROFILL] type: Normal size (double precision) floating point number type 3. DATE date type: The supported range is 1000-01-01 to 9999-12-31. MySQL displays DATE values in YYYY-MM-DD format, but allows you to use strings or numbers to assign values to DATE columns 4. CHAR(M) type: fixed-length string type. When stored, the right side is always filled with spaces to the specified length 5. BLOB TEXT type, the maximum length is 65535 (2^16-1) characters. 6. VARCHAR type: variable length string type
5. Database backup 1. Export the entire database mysqldump -u username-p --default-character-set= latin1 database name> Exported file name (the default encoding of the database is latin1) mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql
2. Export a table mysqldump -u user name -p database name table name> exported file name mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql
3. Export a database structure mysqldump -u wcnc -p -d – add-drop-table smgp_apps_wcnc >d:wcnc_db.sql -d No data –add-drop-table Add a drop table before each create statement
4. Import database Commonly used source commands Enter the mysql database console, For example, mysql -u root -p mysql>use database Then use the source command, the following parameters are Script file (such as the .sql used here) mysql>source d:wcnc_db.sql
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
MySQL is an open source relational database management system, mainly used to store and retrieve data quickly and reliably. Its working principle includes client requests, query resolution, execution of queries and return results. Examples of usage include creating tables, inserting and querying data, and advanced features such as JOIN operations. Common errors involve SQL syntax, data types, and permissions, and optimization suggestions include the use of indexes, optimized queries, and partitioning of tables.
MySQL's position in databases and programming is very important. It is an open source relational database management system that is widely used in various application scenarios. 1) MySQL provides efficient data storage, organization and retrieval functions, supporting Web, mobile and enterprise-level systems. 2) It uses a client-server architecture, supports multiple storage engines and index optimization. 3) Basic usages include creating tables and inserting data, and advanced usages involve multi-table JOINs and complex queries. 4) Frequently asked questions such as SQL syntax errors and performance issues can be debugged through the EXPLAIN command and slow query log. 5) Performance optimization methods include rational use of indexes, optimized query and use of caches. Best practices include using transactions and PreparedStatemen
Apache connects to a database requires the following steps: Install the database driver. Configure the web.xml file to create a connection pool. Create a JDBC data source and specify the connection settings. Use the JDBC API to access the database from Java code, including getting connections, creating statements, binding parameters, executing queries or updates, and processing results.
MySQL is chosen for its performance, reliability, ease of use, and community support. 1.MySQL provides efficient data storage and retrieval functions, supporting multiple data types and advanced query operations. 2. Adopt client-server architecture and multiple storage engines to support transaction and query optimization. 3. Easy to use, supports a variety of operating systems and programming languages. 4. Have strong community support and provide rich resources and solutions.
The process of starting MySQL in Docker consists of the following steps: Pull the MySQL image to create and start the container, set the root user password, and map the port verification connection Create the database and the user grants all permissions to the database
The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.
Laravel is a PHP framework for easy building of web applications. It provides a range of powerful features including: Installation: Install the Laravel CLI globally with Composer and create applications in the project directory. Routing: Define the relationship between the URL and the handler in routes/web.php. View: Create a view in resources/views to render the application's interface. Database Integration: Provides out-of-the-box integration with databases such as MySQL and uses migration to create and modify tables. Model and Controller: The model represents the database entity and the controller processes HTTP requests.
The key to installing MySQL elegantly is to add the official MySQL repository. The specific steps are as follows: Download the MySQL official GPG key to prevent phishing attacks. Add MySQL repository file: rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm Update yum repository cache: yum update installation MySQL: yum install mysql-server startup MySQL service: systemctl start mysqld set up booting
