用MySQL创建数据库和数据库表

#用MySQL创建数据库和数据库表

 

#1、使用SHOW语句找出在服务器上当前存在什么数据库：

SHOW DATABASES;

 

#2、创建一个数据库db1

create database db1;

 

#3、选择你所创建的数据库

use db1;

#use test;

 

#4、 创建一个数据库表

create table t_person (FName varchar(20),Fage int);

 

# 5、显示表的结构：

SHOW TABLES;

 

#查看表中数据

select * from t_person;

 

# 6、 往表中加入记录

Insert Into t_person(FName,FAge) values('Jim',25);

Insert Into t_person(FName,FAge) values('green',38);

Insert Into t_person(FName,FAge) values('kate',20);

Insert Into t_person(FName,FAge) values('john',23);

Insert Into t_person(FName,FAge) values('tom',28);

Insert Into t_person(FName,FAge) values('daviy',30);

 

#查看表中数据

select * from t_person;

 

# 7、用文本方式将数据装入一个数据库表

LOAD DATA LOCAL INFILE "mytable.txt" INTO TABLE pet;

 

#其它操作

insert into t_person(fname) values('lily');

 

insert into t_person values('poly',22);

 

update t_person set fage=30;

 

update t_person set fage=20 where fname='tom';

 

update t_person set fage=50 where fname='tom' or fname='jim';

 

delete from t_person where fname='jim';

 

delete from t_person where fage>30;

 

delete from t_person;

 

drop table t_person;

 

 

#===========================================================

 

CREATE TABLE t_employee (fnumber varchar(10),fname varchar(20),fage int,fsalary int);

 

insert into t_employee(fnumber,fname,fage,fsalary) values('001','tom',20,5000);

insert into t_employee(fnumber,fname,fage,fsalary) values('002','jim',23,3000);

insert into t_employee(fnumber,fname,fage,fsalary) values('003','lily',30,6000);

insert into t_employee(fnumber,fname,fage,fsalary) values('004','lucy',19,2000);

insert into t_employee(fnumber,fname,fage,fsalary) values('004','tomjim',19,2000);

 

select * from t_employee where fname like '%m%';

select * from t_employee where fname like 'j%';

select * from t_employee where fsalary>4000 and fsalary<8000;

select * from t_employee where fsalary between 4000 and 8000;

 

select sum(FSalary) from t_employee;

select avg(FSalary) from t_employee;

select max(FSalary) from t_employee;

select min(FSalary) from t_employee;

 

select sum(FSalary),avg(FSalary),max(FSalary),min(FSalary) from t_employee;

select count(*) from t_employee where FSalary>5000;

 

select * from t_employee order by FSalary asc; #升序

select * from t_employee order by FSalary desc; #降序

 

select * from t_employee;

 

 

#=========================================================

use test;

 

drop database db1;