Basic MySQL statements

Basic MySQL statements
# Creating a table
# Create table stu (id int primary key, name char (10), age int );

# Deleting a table
# Drop table stu;

# Add
# Insert into stu (id, age, name) values (1, 20, 'xiaoming ');
# Insert into stu (id, name, age) values (5, 'xiaoming 5', 40 );

# Delete
# All deleted
# Delete from stu;
# Delete from stu where id> 3 and age = 40;
# Delete from stu where name = 'xiaoming 'or age = 21;

# Change
# Update stu set name = 'mmmm ';
# Update stu set name = 'xxx' where id> 3;
# Update stu set name = 'yyy', age = '000000' where name = 'mmmm ';

# Query
# Select * from stu;
# Select id, age from stu;
# Select id, age, name from stu where id> 2 and id <5;


Basic MySQL statements# Create table stu (# StudyNo int primary key auto_increment, # IdCarNo char (20) not null unique, # Name char (6) not null, # Sex char not null, # Elective char (10 ));
# Create table country (# name char (10) primary key, # language char (10 ));
# Create table president (# name char (10) primary key, # sex char, # f_country_name char (10) unique );
# Alter table president add constraint foreign key (f_country_name) references country (name) on delete cascade; # Set a foreign key
# Create table class (# classname char (10) primary key );
# Alter table class add column headteacher char (10); # add a field
# Create table stuclass (# no int primary key auto_increment, # name char (10), # 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 constraint foreign key (f_stu_no) references stu2 (stu_no );
# Create table people (# id int primary key auto_increment, # name char (10), # sex char, # myyear year, # mydate date, # mytime time );
# Create table mytest (# id int primary key, # no1 int check (no1 <20), # no2 int check (no2> 20 and no2 <30 ), # no3 int check (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, 'xiaoyi', 'male', 30); # insert into stu (Name, Sex, age) values ('小', 'male', 31); # insert into stu (id, Name, Sex, age) values (15, 'three', 'male ', 31); # insert into stu (id, Name, Sex, age) values (17, '4', 'female, 33) # insert into stu (Name, Sex, age) values ('small 6', 'male', 33); # insert into stu (Name, Sex, age) values ('small 5', 'female, 20) # insert into stu (Name, age, Sex) values ('7', 20, 'female ')
# Delete from stu where id = 15 # delete from stu where name = 'xiaoyi' and sex = 'male' # delete from stu where name = 'xiaoyi' and sex =' male' # delete from stu where name = 'small 7' and sex = 'female '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 = 'xiao Qi ', 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 # select * 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) # select * from stu where id = 19 or id = 25 or id = 23 # select sex, count (*) as gender count from stu group by sex # select sex, count (*) as gender count from stu where id not in (19,30) group by sex




Select * into newstu from stu where id> 20 ==> this sentence has doubts


Case Insensitive MySQL
Delete from stu ;===> clear table records

2. Query: in ascending order: select * from stu order by + field ====> sort by field in ascending order

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

3. select * from stu where id in (29,30, 31,32)

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

Select sex, count (*) from stu group by sex; ==> count the number of people with different genders

Select sex from stu group by sex ;==> count the Gender

Select sex, count (*) as gender count from stu group by sex; ===> two fields are displayed, indicating the number of people with different gender

Select sex, avg (stu. age) from stu group by sex ;==> calculate the average age of different gender

Select sex, max (stu. age) from stu group by sex; ====> maximum age of each gender

Alter table class add column headteacher char (10) ;==> add the alter table name add column name varchar (20) alter table id_name drop column age, drop column address; ====> Delete two fields in the table

 

 

1. Add a field

Alter table user add COLUMN new1 VARCHAR (20) default null; // add a field, empty by DEFAULT

Alter table user add COLUMN new2 VARCHAR (20) not null; // add a field, which cannot be blank 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; // modify the name of a field. You must specify the field type again.