Multi-table queries include the following types: Join query inner join outer join left outer join right outer join self-connection subquery Table creation statement emp table –Create table CREATE TABLE emp ( empnoINT, ename VARCHAR (50), job VARCHAR (50), mgrINT, hiredate DATE, sal DECIMAL (7, 2), comm DECIMAL (7, 2), deptnoINT ) ; –Insert data INSERT […]
Tag: mysql
MySQL master-slave replication, read-write separation
MySQL master-slave replication, read-write separation 1. MySQL master-slave replication 1.1 What is master-slave replication? 1.2 Why should reading and writing be separated? 1.3 When should reading and writing be separated? 1.4 Master-slave replication and separation of reading and writing 1.5 Replication types supported by mysql 1.6 Working process of master-slave replication 1.7 MySQL reading and […]
Mysql bin-log log recovery data and physical backup-xtrabackup
Focus on data backup and recovery binlog and xtarbackup bin-log log recovery Enable bin-log configuration bin-log log recovery Physical backup-xtrabackup Three backup methods Install xtrabackup backup Full backup incremental backup differential backup bin-log log recovery The bin-log log records the operations performed on the database. All additions, deletions, and modifications are in the bin-log log. […]
[Transfer] Practical combat of mysql triggers (if trigger execution fails, will SQL roll back?)
1 IntroductionMysql triggers and stored procedures are both programs embedded in mysql. Triggers are a new function of mysql5. Currently, the databases used by the online Fengchao system, Beidou system, and Columbus system are all mysql5.0.45 versions. Many programs such as fc-star management terminal, sfrd (das), and dorado will use triggers. The server program implements […]
[MySQL] Basic query (3) Aggregation function + group by
Article directory 1. Aggregation function 2. group by clause Conclusion Create the following table //Create table structure mysql> create table exam_result( -> id int unsigned primary key auto_increment, -> name varchar(20) not null comment ‘Classmate’s name’, -> chinese float default 0.0 comment ‘Chinese score’, -> math float default 0.0 comment ‘math score’, -> english float […]
MySqlC# database backup and restore
public static class SQLBackup { /// <summary> /// Execute Cmd command /// </summary> /// <param name=”workingDirectory”>Directory of the process to be started</param> /// <param name=”command”>Command to be executed</param> public static void StartCmd(String workingDirectory, String command) {<!– –> using (Process p = new Process()) { p.StartInfo.FileName = “cmd.exe”; p.StartInfo.WorkingDirectory = workingDirectory; p.StartInfo.UseShellExecute = false; p.StartInfo.RedirectStandardInput = […]
CentOSCentOS7.0 mysql and uninstallation
mysql installation: Using the command yum list mysql-server When installing mysql, I found that there is no mysql package. At this time, we need to download a Download package wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm Download is complete, install the mysql-community-release-el7-5.noarch.rpm package sudo rpm -ivh mysql-community-release-el7-5.noarch.rpm Install mysql sudo yum install mysql-server Wait for the installation to complete…. Then […]
In-depth exploration of geospatial query: how to elegantly implement accurate geographic data storage and retrieval techniques in MySQL, PostgreSQL and Redis
Blogger Maotouhu () takes you Go to New World? Blog homepage–Mao Touhu’s blog “Complete Column of Interview Questions” Articles with pictures and textsVivid imagesEasy to learn! Everyone is welcome to step in~ “IDEA Development Cheats Column” Learn the common operations of IDEA and double your work efficiency~ “Master Golang in 100 Days (Basic Introduction)” Learn […]
Mysql Waiting for table metadata lock problem handling
Waiting for table metadata lock problem handling When using mysql, we sometimes encounter lock waiting for table metadata lock. However, this lock wait is quite special and cannot be found in the innodb_lock_wait and show engine innodb status tables. The impact of this lock + ——- + —————— + ———– + —— + – ——– […]
mysql multi-instance deployment
mysql multi-instance deployment (One server, multiple databases) 1. Install mysql //Install dependency packages [root@localhost ~]# yum -y install ncurses-devel openssl-devel openssl make mariadb-devel libtool 2. Create users and groups [root@localhost ~]# groupadd -r -g 306 mysql [root@localhost ~]# useradd -r -M -s /sbin/nologin -g 306 -u 306 mysql 3. Unzip the source code package to […]