Explain the use of Mysql basic syntax in detail

Introduction to MYSQL

MySQL is a relational database management system, developed by MySQL AB Company of Sweden, and currently belongs to Oracle products. MySQL is one of the most popular relational database management systems. In the application of WEB, MySQL is the best application software of RDBMS (Relational Database Management System, relational database management system).

Why use MYSQL

Because of its small size, fast speed, low total cost of ownership, and most importantly, it is free, which saves the development cost for many small and medium-sized enterprises.

I believe that many code friends are unfamiliar with grammar when they get started, and they don't know how to use it. Below, I will give you a collective analysis of the actual operation of 1 basic grammar.

1. Create a database

CREATE DATABASE 数据库名称

2. Create Tables-Here I created an user table for testing (id we use here as primary key, username user name, userpass user password, job job position, department department, name real name) This is a basic user table

CREATE TABLE `user` (
 `id` int(11) NOT NULL,
 `username` varchar(11) DEFAULT NULL,
 `userpass` varchar(50) DEFAULT NULL,
 `job` varchar(11) DEFAULT NULL,
 `department` varchar(11) DEFAULT NULL,
 `name` varchar(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Create an signin sign-in table (id primary key, signindate sign-in time, uid user name, name name)

DROP TABLE IF EXISTS `signin`;
CREATE TABLE `signin` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `signindate` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 `uid` varchar(50) DEFAULT NULL,
 `name` varchar(50) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

3. Let's talk about some specific operation syntax

a. Insert Data--insert Syntax

INSERT INTO `user` VALUES ('1', 'admin', '21232f297a57a5a743894a0e4a801fc3', ' System maintenance ', ' System administrator ', ' System administrator ');
INSERT INTO `signin` VALUES ('1', '2018-05-10 16:42:32', 'admin', ' Xiao Ming ');

b. Modify data-update syntax (modify name value to Xiaoming according to username)

update user set name=' Xiao Ming ' where username='admin'

c. Delete Data-delete Syntax (Delete data with ID of 1)

delete from user where id=1

d. Query data-select syntax (according to the condition query, here is the query username value admin or name value Xiaoming data, when one of the two conditions is met)

select id,username,job,department,name from user where username='admin' or name=' Xiao Ming '

e. Query data-select syntax (count how much data is in the admin table)

select count(*) from user

f. Query Syntax--select Syntax (paged query, querying page 110 condition data)

select id,username,job,department,name from user order by id desc limit 10,1

g. Query syntax-select syntax (left join left outer join, associated query user sign-in record, if there is no sign-in record will return the data in user table)

select u.id,u.username,s.signindate from user u left join signin s on u.username=s.uid

Summarize