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

用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;