Base MySQL Statement

#创建表
#create table stu (ID int primary key, name Char (ten), age int);

#删除表
#drop table Stu;

#增加
#insert into Stu (ID, age, name) values (1, 20, ' xiaoming ');
#insert into Stu (ID, name, age) VALUES (5, ' xiaoming 5 ', 40);

#删除
#全部删了
#delete from Stu;
#delete from Stu where Id>3 and age=40;
#delete from Stu where Name= ' Xiao Ming ' or age=21;

#改
#update Stu set name= ' MMMM ';
#update stu set name= ' xxx ' where id>3;
#update Stu set Name= ' yyy ', age= ' where name= ' mmmm ';

#查询
#select * from Stu;
#select ID, age from Stu;
#select ID, age, name from Stu where Id>2 and id<5;


base MySQL Statement#create table Stu (#StudyNo int primary key auto_increment, #IdCarNo char (a) is not null unique, #Name char (6) is not NULL, #Se x char not NULL, #Elective char (10));
#create Table Country (#name char (primary) key, #language char (10));
#create Table President (#name char (primary) key, #sex Char, #f_country_name char (unique);
#alter Table President Add Constraint foreign key (f_country_name) references country (name) on DELETE cascade; #设外键
#create Table Class (#classname char (primary key);
#alter table class Add column Headteacher char (n); #增加字段
#create table Stuclass (#no int primary key auto_increment, #name char (TEN), #age int, #f_classname char (10));
#alter table Stuclass Add constraint foreign key (F_classname) references class (classname) on DELETE cascade;
#create table Teacher (#teacher_no int primary key auto_increment, #name char (10));
#create table STU2 (#stu_no int primary key auto_increment, #name char (10));
#create table Middle (#id int primary key auto_increment, #f_teacher_no int, #f_stu_no int);
#alter table middle Add constraint foreign key (f_teacher_no) references teacher (teacher_no); #alter Table Middle Add constr Aint foreign KEY (f_stu_no) references stu2 (STU_NO);
#create table people (#id int primary key auto_increment, #name char (Ten), #sex Char, #myyear year, #mydate date, #mytime t IME);
#create table mytest (#id int primary key, #no1 int check (NO1&LT;20), #no2 int check (NO2&GT;20 and no2<30), #no3 int ch Eck (no3>20 or no3<30), #sex char (2) Check (sex= ' Male ', sex= ' female '));
#create table Stu (#id int primary key auto_increment, #Name char (6) NOT NULL, #Sex char NOT NULL check (sex= ' Male ', sex= ' female '), #age int Check (age>0 and age<120));
#insert into Stu (id,name,sex,age) VALUES (13, ' small One ', ' male ', 30); #insert into Stu (name,sex,age) VALUES (' Small two ', ' Male ', 31); #insert into Stu (id,name,sex,age) VALUES (15, ' small three ', ' Male ', 31); #insert into Stu (id,name,sex,age) VALUES (17, ' small Four ', ' female ', ' #insert into Stu (name,sex,age) VALUES (' Small ⑥ ', ' Male ', 33); #insert into Stu (name,sex,age) VALUES (' Small Five ', ' female ', ') #insert into Stu (name,age,sex) VALUES (' small seven ', 20, ' female ')
#delete from Stu where id=15 #delete from Stu where name= ' small one ' and sex= ' man ' #delete from Stu where name= ' small one ' and sex= ' man ' #de Lete from Stu where name= ' small seven ' and sex= ' women ' and age=20 #delete from Stu where age>30
#update Stu set sex= ' W ' where sex= ' m ' #update Stu set sex= ' m ' where id>20 and id<25 #update Stu set name= ' small Seven ', sex= ' W ', age=18 where age = 26
#select * from Stu #select id,name,age from Stu #select * from Stu ORDER by ID #select * FROM stu ORDER BY age Desc #selec T * from Stu where sex= ' M ' #select * from Stu where ID not in (19,25,23) #select * from Stu where ID not in (19,25,23) #se Lect * from Stu where ID =19 or ID =25 or ID =23 #select sex,count (*) as sex number from Stu Group by sex #select Sex,count (*) A s sex number from Stu where ID not in (19,30) group by sex

SELECT * into Newstu from Stu where Id>2 0====> There is a question in this sentence

MySQL is case insensitive

1. Delete from Stu; ====> emptying the table records

2, query: Ascending:select * from Stu ORDER by + field =====> by word orderby order

Order by + field + desc =====> in descending order by field

3.Select * from Stu where IDin(29,30,31,32)

====>where id=29 or id=30 or id=31 or id=32

1. Select Sex, COUNT (*) from Stu Group by sex;====> statistics on how many people are in different genders

Select Sex from Stu Group by sex;====> statistics of how many genders

Select Sex, COUNT (*) as gender number from Stu Group by sex;====> Two fields representing the number of different genders

Select Sex, AVG (stu.age) from Stu Group by sex;====> statistics on the average age of different genders

Select Sex, max (stu.age) from Stu Group by sex;====> statistics on the respective maximum age of different genders

2. alter  table class add column headteacher char (Ten);====> add a table field    ALTER&NBSP;TABLE&NBSP; table name   ADD&NBSP; list   varchar ( 20 )  
3. alter table id_name drop  column age,drop column address; ====> Delete two fields in table

1. Add a field
ALTER TABLE user add COLUMN new1 VARCHAR default null;//Add a field that defaults to null
ALTER TABLE user add COLUMN new2 VARCHAR (a) not NULL; Add a field that cannot be empty by default
 
2. Delete a field
ALTER TABLE user DROP COLUMN new2; Delete a field
 
3. Modify a field
ALTER TABLE user MODIFY New1 VARCHAR (10); Modify the type of a field
 
ALTER TABLE user change new1 new4 int; To modify the name of a field, be sure to re-specify the field's type

Base MySQL Statement