SQL syntax for the database

--Create a database
CREATE DATABASE mytest;

--Create a table
CREATE TABLE T_user (
--primary key defines the column as the primary key
--Auto_increment represents the value of the column, which is automatically generated by the DBMS and is the auto-grow column (Auto_+tab key).
ID INT PRIMARY KEY auto_increment,

UserName VARCHAR (20),--Name
Money FLOAT,--wages
Birthday Date--date of birth

) DEFAULT Charset=utf8;

--Add Index

CREATE INDEX index_name on T_user (username);

--Delete Index

ALTER TABLE t_user DROP INDEX index_name;

--Add record

INSERT into T_user (username,money,birthday) VALUES (' Zhang San ', 3000, ' 1992-09-03 ');

--add more than one record

INSERT into T_user (username,money,birthday) VALUES (' Zhang San ', 3000, ' 1992-09-03 '), (' Qinshubao ', 6666, ' 1984-06-02 '), (' Luo Cheng ', 7777, ' 1985-03-02 ');

--Querying all data in a table

Select*from T_user;

--Query all users ' names and birthdays

SELECT Username,birthday from T_user;

--delete the record with ID 8

DELETE from T_user Where id=8;

--Delete records below Payroll 5000

DELETE from T_user Where money<5000;

--Revise Artop's salary to 7000

UPDATE t_user SET money=7000 WHERE username= ' Luo Cheng ';

--all employees have a 500 rise in wages

UPDATE t_user SET money=money+500 WHERE birthday>= ' 1990-01-01 ';

--Change the name of the person with ID 2 to Li Shimin

UPDATE t_user SET username= ' li Shimin ' WHERE id=2;

--Delete all the records of the two methods (delete records the directory log, a location after the deleted data can also be restored, but inefficient, truncate will not log, deleted data can not be recovered, but high efficiency)

DELETE from T_user;

TRUNCATE TABLE T_user;

--Record with ID 2, change name to Li Zongrui, change salary to 4500

UPDATE from T_user SET username= ' Li Zongrui ', money=4500 WHERE id=2;

--inquire about people who have wages between 3000-6000

Select*from t_user WHERE money>=3000 and money<=6000;

--Show all members of Gen Y

Select*from t_user WHERE betwen ' 1980-01-01 ' and ' 1990-01-01 ';

--show third to sixth records

Select*from t_user LIMIT 2, 4;

--Add 200 to employee's salary with ID 3 and ID 6

UPDATE t_user SET money=money+200 WHERE id=3 or id=6;

UPDATE t_user SET money=money+200 WHERE in (3,6);

--Query all members of the surname Zhang

Select*from t_user WHERE username like ' Zhang% ';

--Query all names containing the members of the sheet

Select*from t_user WHERE username like '% zhang% ';

--Query name only two words and surname Zhang

Select*from t_user WHERE username like ' Zhang _ ';

--Show all members by salary sort

Select*from t_user ORDER by Money DESC; --The default is ASC ascending, desc descending

--Display all records, query columns require display in Chinese

SELECT ID number, username name, money salary, birthday birthday from T_user;

--Removal of duplicate records

SELECT DISTINCT birthday from T_user;

--Query for employees with an empty or empty salary

INSERT into T_user (username,birthday) VAlUES (' Qiao ', ' 1998-09-09 ');

Select*from T_user WHERE money is NULL;

Select*from T_user WHERE money was not NULL;

SQL syntax for the database