Open database: sudo service mysql start using root login: mysql-u root to create database: creation databases Mysql_shiyan; query database: show databases; connect database: Use Mysql_shiyan; query table: show tables; creating tables: Create TABLE employee (ID int, name char), phone (12)); Delete table: Drop DATABASE Mysql_ Insert Table data: INSERT into employee (ID, name,phone) VALUES (Shiyan, ' TOM ', 18868830303);
1. Data type:int 4 integer FLOAT 4 single-precision floating-point number double 8 double-precision floating-point numberenum radio, such as Gender enum (' A ', ' B ', ' C ')set Multi-select Set (' 1 ', ' 2 ', ' 3 ')Date 3rd Yyyy-mm-ddtime 3 point-in-time or duration HH:MM:SSYEAR 1-year value YYYYCHAR 0~255 Fixed-length stringVARCHAR 0~255 variable length stringText 0~65535 long Text data 2,
in MySQL, there are usually these kinds of constraints: Constraint type: PRIMARY key default unique foreign key non-null keyword: PRIMARY key The default unique FOREIGN key not a NULL primary key (PRIMARY key) is used to constrain a row in a table as a unique identifier for this row, the value constraint (DEF Ault) specifies that the default value is used when the inserted data is empty when there is a column with the default constraint. A unique constraint is simple, which specifies that the value of a column specified in a table must not have duplicate values, that is, each value in this column is unique. FOREIGN KEY constraints, a table can have multiple foreign keys, each foreign key must REFERENCES (reference) The primary key of another table, the column that is constrained by the foreign key, the value must have a corresponding value in the column it references. A non-null constraint (NOT NULL), which can be understood by a name, is a non-null constrained column that must not be empty when inserting a value. CREATE TABLE Employee (ID INT (10)PRIMARY KEY, name CHAE (20)DEFAULT' Tom ',UNIQUE(phone), salary INT (10)Not NULL,);
3. Where limit conditionsmathematical Symbols (=,<,>,>=,<=)or (or) and and (and) and between (inclusive)Select Name,age from employee where age>25 or age>30;In and not inSELECT NAME,AGE,PHONE,IN_DPT from the employee WHERE IN_DPT not in (' dpt1 ', ' DPT3 ');a wildcard character likeWhere _ represents an unspecified character, and% indicates an unspecified character. SELECT Name,age,phone from the employee WHERE phone like ' 1101__ '; SELECT Name,age,phone from the employee WHERE name like ' j% ';ORDER by SortASC and DESC Ascending or descending select Name,age,salary,phone from the employee ORDER by salary DESC;
4. SQL built-in functions and calculationsFunction name: Count sum AVG max min effect: count sum averaging maximum minimum value SELECT max (Salary) as max_salary,min (salary) from employee;use as keyword to rename values
5, sub-queryWork with multiple tables to get the information you need. For example: Want to know the department of the employee named "Tom" has done several projects. Employee information is stored in the employee table, but the project information is stored in the project table. Select Of_dpt,count (proj_name) as Count_project from Projectwhere of_dpt in (SELECT IN_DPT from employee WHERE name= ' Tom ') ;
6. Connection QueryWhen working with multiple tables, subqueries are only useful when the results come from a table. However, if you need to display data from two or more tables, you must use a JOIN operation. The basic idea of a connection is to manipulate two or more tables as a new table, as follows: SELECT id,name,people_numfrom employee,departmentwhere EMPLOYEE.IN_DPT = Department.dpt_nameorder by ID; SELECT Id,name,people_numfrom EmployeeJOINDepartment onEMPLOYEE.IN_DPT = Department.dpt_nameorder by ID;
7. Rename a tableRename a tableThe statement has many forms, the following 3 format effect is the same: RENAME TABLE original to the new name; ALTER TABLE formerly known as RENAME new name; ALTER TABLE formerly known as RENAME to new name;Delete a table:DROP table name;Add a column:ALTER table table name ADD column name data type constraint; ALTER table table name ADD column name data type constraint after column name; ("After column 1" means that the new column is placed after "column 1") ALTER TABLE employee ADD Test INT (Ten) DEFAULT one first; (Put in the position of column one, use primary)Delete a column:ALTER TABLE name drop column name (no data type, constraint or location information required after statement) OR: ALTER TABLE table name drop list name;Rename a column:This statement actually not only can be used to rename a column, exactly, it is to modify a column (change): ALTER table name changes the original column name of the new column name data type constraints;To change the data type:To modify the data type of a column, in addition to using the change statement just now, you can also use the MODIFY statement: ALTER table table name MODIFY column name new data type;To modify a value in a table:Most of the time we need to make changes not the entire database or the entire table, but one or a few data in the table, this requires us to use this command to achieve precise modification: UPDATE table name SET column 1= value 1, column 2= value 2 WHERE condition;To delete a row of records:Deleting a row of data in a table must also be accompanied by a where condition, otherwise the entire column of data will be deleted. Delete statement: Delete from table name WHERE condition;
MySQL Common statements
