用MySQL创建数据库和数据库表
帐户luowei505050的专栏
类别数据库
#用MySQL创建数据库和数据库表
#1、使用SHOW语句找出在服务器上当前存在什么数据库：SHOW DATABASES;
#2、创建一个数据库db1
create database db1;
#3、选择你所创建的数据库
use db1;
#use test;
#4、创建一个数据库表
create table t_person (FNamevarchar(20),Fageint);
# 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);
updatet_person set fage=30;
updatet_person set fage=20 where fname='tom';
updatet_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 (fnumbervarchar(10),fnamevarchar(20),fageint,fsalaryint);
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;
selectavg(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 FSalaryasc; #升序
select * from t_employee order by FSalarydesc; #降序
select * from t_employee;
#========================================================= use test;
drop database db1;。