Query SQL variables show variables
Alter table name alter table name alter table name alter table name Alter table alter table name add field name data type; Alter table table_name add table_name data type FIRST; Alter table table_name add table_name data type after table_name; Alter table ALTER table name drop name; 4. Modify the data type. Alter TABLE Table name modify field name New data type. Alter table rename table name rename table name; The width of the numeric type is the display width, which is only used for select query. The display width is independent of the storage used. Zerofill is used to check the effect. Enum Type 1. Optional (enum) : Field name enum (Value 1, value 2…) ; 2. Multiple (set) : field name set (value 1, value 2…) ; Date and time type 1. Date: YYYY-MM-DD 2. Time: HH: MM: SS 3. Datetime: YYYY-MM-DD HH: MM: SS “4. Timestamp:” YYYY-MM-DD HH: MM: SS “5. Datetime: no value Null is returned by default
Curtime () returns the current date. Curtime () returns the current date. Year (date) Returns the year of the specified time. Returns the date at the specified time 6. Time (date) Returns the time at the specified time 4. 1. Syntax format SELECT * from table name where field name operator (time-interval unit); Interval unit: 1 day 2 hour | | 1 minute | 2 year | the month
1. Delete from table where id = 1; Select * from table_name where table_name = 1; update table_name set table_name = 1; Note: If the WHERE condition is not added, all records are changed by 3. 1. Value comparison: =! = > >= < <= 2. 1. Where id = 1 and id = 2 2. Where id = 1 and id = 2…. 3. Where not in (value 1, value 2…)
1. Null: Where name is null 2. Null: where name is not null 3. Note 1. NILL: null, can only use is or is not matching 2. 1. Where field name like expression 2. Expression 1._ : matches a single character. 2. % : matches 0 to multiple characters.
1 Grammar order:
3.select … 1. Where 2. Group by… 4. Having… 5. The order by… 6.limit … ;
2. Order by 1. Order by column name ascending: ASC descending: DESC 3. Limit 1 Usage limit n Displays n records limit m, n m indicates that n records are displayed from m+1 limit 2,3,4, 5 records 3. Page 1: limit 0,5 #1,2,3,4,5 page 2: limit 5,5 page 3: limit 10,5 page 4: limit 15 Limit (m-1) *n, n 4. Aggregate function AVG (field name) : Calculates the average value of the field sum (field name) : sums Max (field name) : maximum min (field name) : minimum count (field name) : statistics the number of the field
create database MOSHOU; use MOSHOU; Create table hero(id int, name char(15), sex enum(" male "," female "), country char(10))default charset=utf8; create table hero(id int, name char(15), sex enum(" male "," female "), Country char(10))default charset=utf8; Insert into hero values (1, "cao cao", "male", "wei"), (2, "Joe", "female" and "wu"), (3, "zhuge liang", "male" and "shu"), (4, "the sable cicada", "female", "han"), (5," Zhao Zilong "," Male "," Shu "), (6," Wei Yan "," male "," Shu "); use MOSHOU; Create table sanguo(id int, name char(20), gongji int, fangyu tinyint unsigned, sex enum country varchar(20) )default charset=utf8; Insert into sanguo values (1, 'zhuge liang, 120, 20,' male ', 'shu'), (2, 'sima yi, 119, 25,' male ', 'wei'), (3, "guan yu", 188, 60, 'male', 'shu'), (4, 'zhaoyun, 200,66,' male ', 'wei'), (5, 'sun quan, 110, 20,' male ', 'wu'), (6, "the sable cicada", 666, 10, 'female' and 'wei'), (7, null, 13, 1000 'male', 'shu'), (8, ",1005,88,' nu ',' shu ');Copy the code
Create library studb2
Select * from t1 where id = name, age, phnumber
3. View the table structure
Add an ID field to the first column of the table
5, change the phnumber data type to bigint
Add address to the last column of the table
Alter table age
8. Look at the table structure
The answer:
use studb2;
create table t1(
name char(20),
age tinyint unsigned,
phnumber char(11)
);
desc t1;
alter table t1 add id int first;
alter table t1 modify phnumber bigint;
alter table t1 add address varchar(50);
alter table t1 drop age;
desc t1;Copy the code
Insert three records into the table
2. Find out which users recharged on July 2, 2018
3. Search for the information of recharge in July 2018
4. Find the information of recharge from 10:00 to 12:00 on July 30
The answer:
Insert into t7 has values (3, "small zhao", 19000520300, 0201063000, 000), (4, "zhao", 19000521400, 0201070000, 000). (5, "week zhi if", 19010522350, 0201070100, 000); select * from t7 where date(shijian)="2018-07-02"; select * from t7 where date(shijian)>="2018-07-01" and date(shijian)<="2018-07-31"; select * from t7 where date(shijian)="2018-07-31" and time(shijian)>="10:00:00" and time(shijian)<="12:00:00";Copy the code1. Query the records within 1 day
2. Query the records generated one year ago
3. Query the records generated one day ago and within three days
The answer:
select * from t7
where shijian > (now()-interval 1 day);
select * from t7
where shijian < (now()-interval 1 year);
select * from t7
where
shijian < (now()-interval 1 day) and
shijian > (now()-interval 3 day);Copy the code1, find all the information of shu people
2. Find all heroines by name, gender, and country
3. Change the record with ID 2 to Dian Wei, male, country wei
4. Delete all shu heroes
5. Change the nationality of Diao Chan to wei
6, delete all table records
The answer:
Select * from hero where country=" 0 "; Select name,sex,country from hero where sex=" 男 "; Update hero set name=" cn ",sex=" cn ",country=" cn "; update hero set name=" cn ",sex=" cn ",country=" cn "where id=2; Delete from hero where country=" hero "; Update hero set country=" 中 国" where name=" 中 国"; delete from hero;Copy the code
1. Find out the name and attack power of shu heroes whose attack value is higher than 200
2. Change the attack value of heroes of Wu from 110 to 100 and their defense power to 60
3, find the hero information of Shu and Wei
The answer:
Select name as n,gongji as g from sanguo where gongji>200 and country=" shu "; Sanguo set sanguo =100,fangyu=60 where country=" wu "and gongji=110; Select * from sanguo where country=" sanguo "or country=" sanguo ";Copy the code
1. Find the hero information of Shu whose attack value is 100-200
2. Find information about heroines from countries other than Shu and Wu
3, find information about shu hero and Diao Chan whose ID is 1, 3 or 5
The answer:
Select * from sanguo where gongji between 100 and 200 and country=" sanguo "; Select * from sanguo where country not in(" sanguo "," sanguo ") and sex=" female "; Select * from sanguo where (id in(1,3,5) and country=" 0 ") or name=" 0 ";Copy the code1. In the heroes of Shu, find the records of the heroes whose defense value is from the last two to the last four
2. Search for the name, attack value, and country of the hero whose attack value is the top three and whose name is not NULL
The answer:
Select * from sanguo where country=" sanguo "order by fangyu asc limit 1,3; Select name,gongji,country from sanguo where country=" shu "and name is not NULL order by gongji DESC limit 3;Copy the code
1. How much is the strongest attack value
2. There are several records in the two fields of id and name respectively
## NULL will not be counted,”” will be counted
3. Calculate the total attack power of the heroes of Shu
4. Count the number of heroes whose attack value is greater than 200
The answer:
select max(gongji) from MOSHOU.sanguo; select count(id),count(name) from sanguo; Select sum(gongji) from MOSHOU. Sanguo where country=" sanguo "; Select count(*) from MOSHOU. Sanguo where gongji>200 and country=" shu ";Copy the code