#Create table
MariaDB [abc]> create table if not exists abd(id int,name varchar(30)); #Syntax: create table [if not exists] indicates (field 1 type, field 2 type); when creating a table, you need at least one field, otherwise you will get an error
#View Table Creation Process
MariaDB [abc]> show create table abd; #The first three tables in the grammar show create table name are fixed (you can understand the whole table creation process by this command, you can understand the table better with this command)
+-------+--------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------+
| abd | CREATE TABLE `abd` (
`id` int(11) DEFAULT NULL,
`name` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------+
#See which tables are under the current library
MariaDB [abc]> show tables;
#View table structure
MariaDB [abc]> desc abd; #Syntax: desc table name; (without library name, the default is to query the table structure under the current library)
+-------+-------------+------ +-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------ +-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
//Note: By looking at the table structure, you can see clearly how many fields are in the table (the vertical row is the field) and the type of the table (the horizontal row is the type).
MariaDB [mysql]> desc abc.abd; #Implementing cross-database queries (but you have to pinpoint which library the table is in) grammar: desc library name. table name;
MariaDB [mysql]> explain abc.abd; #explain can also be used to query table structures in the same way as desc.
#Delete table
MariaDB [abc]> drop table if exists abd; #Grammar: drop table [if exists] table name where: the first two are mandatory [optional] table names (you need to delete), the command to be careful, confirm that the correct situation is in operation.
#The attributes of tables are redefined in alter grammar; the attributes are modified by alter table name keywords. There are still a lot of keywords.
MariaDB [abc]> alter table abc2 rename abc; #Rename (keyword) rename purpose: rename tables.
MariaDB [abc]> alter table abc modify id int(60); #Modify (keyword) to modify a field: modify field properties
MariaDB [abc]> alter table abc change id id1 int(30); #Change (keyword) Re-commands a field and modifies the use of attributes: change original field new field field type purpose: modify field name and field type
MariaDB [abc]> alter table abc add sex enum("male","female"); #Add (keyword) add a new field usage: add field name field attribute purpose: add table structure
MariaDB [abc]> alter table abc add uuid int first; #It's still the use of add, but at the end it adds a first, meaning to add a field and put it first, and by default it's at the end. Usage: add field name field attribute first
MariaDB [abc]> alter table abc add address varchar(50) alter id1; #Or add usage, but add a parameter after which field to place a new field after: add (add) field name field attribute after field name (existing)
MariaDB [abc]> alter table abc drop id1; # Drop (keyword) usage: drop field name purpose: which field to kill
http://More detailed usage of dev.mysql.com/doc/refman/5.5/en/alter-table.html can be found in this document.
#Insert grammar for insert data into tables: insert into table name values (field value 1, field value 2, field value 3...) effect: add records
MariaDB [abc]> insert into abc values(1,"cs","ahuang","male");
MariaDB [abc]> insert into abc values(2,"xt","Ah Zhong","male"),(3,"Xie","Fang que","female"),(4,"beij","mit","female");
MariaDB [abc]> insert into abc values(null,"sc","lower-middle","male"); #Can add null value
#View Table Content Select [option] Field Name from Table Name Function: View Records
MariaDB [abc]> select * from abc; # * all
MariaDB [abc]> select id from abc; #To view a single field, simply add the name of the field you want.
MariaDB [abc]> select id,name from abc; #View the contents of multiple fields, fields in the middle of 2 or more, separated by numbers
MariaDB [abc]> select distinct id,name from abc; #Distinct means eliminating duplication
##Where judgement statement logical operator: and and and or or not arithmetic operator: <>!= (the first two are not equal to) > < >= <= (these four are familiar meanings). In where matching rule, h supports multiple choices, not in (not in is not notin) on the contrary.
MariaDB [abc]> select * from abc where id=2 and sex="male"; # where is a conditional judgment statement, which is used a lot in many sql, usually at the end of the usage: where specific judgment purpose: matching the required data
MariaDB [abc]> select id from abc where name="mit" or name="square yue" or name="ssd"; # or as long as one of them is satisfied, it will show up.
MariaDB [abc]> select * from abc where id=4 and name="ssd"; #If one of the conditions is not met, but the implementation will be successful, but there is no specific output of the results.
MariaDB [abc]> select * from abc where id<>4; #Display all records with id not equal to 4 (for example, other uses are similar)
MariaDB [abc]> select * from abc where id in(23,9,4); #The same is true of not in.
MariaDB [abc]> select * from abc where id not in(23,9) order by id desc;
##Order by sort mysql database defaults to ascending order (from small to large), so you only need to remember one of its parameters: desc (descending order: from big to small) usage: order by field (sorted field) desc.
MariaDB [abc]> select * from abc order by id desc; # Sort in descending order by id column
MariaDB [abc]> select * from abc order by id,address,name desc; #Multiple field sorting, default to the first field
##Range operations between... and... can be replaced by arithmetic operators
MariaDB [abc]> select * from abc where id between 9 and 23 order by id desc; #Display the contents of id 9 - 23 in descending order
MariaDB [abc]> select * from abc where id>=9 and id<=23 order by id desc; #The same result
##Usage of Fuzzy Matching Query: Where Field Name [not] like Matches Content where not like is almost the opposite meaning
MariaDB [abc]> select * from abc where id like "9"; #Match only data with id 9
MariaDB [abc]> select * from abc where id not like "9"; #The reverse is to match all the data at id=9.
##Select sub-queries are simply collected by nesting a select query inside the select
MariaDB [abc]> select * from abc where id=(select id from abc where id=4); #When nesting the select loop in select, the select statement in () will be executed first, and then the result will be assigned to the external select statement (i.e. assigned to id) in the way of variable. It can be understood as variable assignment, which is generally used for cross table query. I did not use a table to demonstrate this.
##The join query uses a common field to find the qualified union of the two tables. Connect the two tables by common fields.
//But the inner connection and the outer connection (the left connection and the outer connection)
//Internal join: select field from table 1 (alias), Table 2 (alias) where union set has another method: select field from table 1 (alias) inner join table 2 (alias) on table 1. field = table 2. field
select bname,btypename from books a,category b where a.btypeid=b.btypeid;
//External Connection - Left External Connection select Field from Table 1 (Alias) left jion Table 2 (Alias) on Connection Conditions where table a is the main table, table b is the slave table, the main table has all contents, and null is not displayed in the slave table.
select bname,btypename,price from books a left join category b on a.btypeid=b.btypeid;
//Outer join-right outer join select field from a table right join b table on condition where a table is from a table, B table is the main table (both show), right join can represent multi-table join
select bname,btypename,price from books a right join category b on a.btypeid=b.btypeid;
##Code blocks that aggregate functions to perform specific functions
sum() Sum usage: select sum(The field of summation can only be a number) from Table name;
MariaDB [abc]> select sum(price) from abe;
avg()Average Value Usage:select avg(The average field can only be a number.) from Table name;
MariaDB [abc]> select avg(price) from abe;
max() Maximum usage:select max(Ditto) from Table name;
MariaDB [abc]> select max(price) from abe;
MariaDB [abc]> select * from abe where price=(select max(price) from abe); #Comprehensive usage
min() Ibid. after the minimum
MariaDB [abc]> select min(price) from abe;
MariaDB [abc]> select * from abe where price=(select min(price) from abe);
count() Usage of Statistical Row Number: select count(*) from Table name;
MariaDB [abc]> select count(name) from abe;
##String function
substr(string,start,len) Interception mode:from start(The default is to start at 1.)Start, intercept len Long acting:Intercept a piece of data
MariaDB [abc]> select substr(name,1,3) from abe; #Starting with the first character, intercept three characters
MariaDB [abc]> select substr(name,-2,3) from abe; #Starting with the penultimate character, intercepting two characters with a minus sign means intercepting backwards.
concat(str1,str2,str3) Stitching effect;Put multiple fields together into one field output
MariaDB [abc]> select concat(name,id) from abe;
MariaDB [abc]> select concat(name,"#######",id) from abe; #The middle can be separated by special symbols, but a "" sign is needed.
##toggle case
upper() Convert all English to uppercase output.:select upper(Only for English lowercase fields) from Table name [condition]
MariaDB [abc]> select upper(address) from abc where id=4 or id=233; #English capitalization output
lower() Follow upper On the contrary
MariaDB [abc]> select lower(address) from abc where id=4 or id=233; #In English lowercase output
#Delete table content delete usage: delete from table name where specifies condition effect: delete record
MariaDB [abc]> delete from abc where id=3; #Delete the record with id=3
MariaDB [abc]> delete from abc where id is null; #Delete null values (you cannot write id=null directly, otherwise you will report an error)
MariaDB [abc]> delete from abc where id=2 and address="cs"; # and is two conditions must be met before deletion operation, although not deleted successfully, but will prompt the operation success.
MariaDB [abc]> delete from abc where id=2 or address="cs"; # or, if only one of the conditions is met, the operation can be carried out. If both conditions are successfully met, both records will be destroyed.
#Update table record update table name set (row) specifies conditional role; update record
MariaDB [abc]> update abc set sex="female" where id =4; #If you don't specify a specific condition without where, the default is to replace the entire segment of the sex field (caution must be exercised)
MariaDB [abc]> update abc set id=233 where id =10;
## Arithmetic + + * / the following tests are in support of normal addition, subtraction, multiplication and division.
MariaDB [abc]> update abe set price=price+6 where price<60; #As long as the book is under 60 yuan, the price of each book is increased by 6 yuan + 6, that is to say, +6 on the original price value.
MariaDB [abc]> update abe set price=price-10 where price>100; #Every book above 100 yuan is reduced by 10 yuan.
MariaDB [abc]> update abe set price=(price+(price*0.1)) where price<40; #Books under 40 yuan are up 1.1 times