查看所有的数据库:show databases;
创建数据库 create database [if not exists] studb;
切换数据: use studb;
展示所有的表格:show tables;
创建表格:
create table if not exists stuTable(id int,name varchar(20),gender varchar(2))ENGINE='innoDB' default charset='utf8' ;
插入: insert into stuTable(id,name,gender)values(1,yangshaofeng,'M');
更新: update stuTable set name='ziji'where id=1;
删除一个记录:delete from stuTable where id=2;
查看表的结构(创建表的语句): show create table stuTable;
查找表的:select * from stuTable;
表重命名:rename table 旧名字to 新名字;
删除表: drop table newstutable;
删除数据库: drop database studb;
Alter:增加字段 删除一个字段(给字段扩充长度) 修改字段或者表的编码,修改字段的名字.
id不为空: id int not null
id自增: id int not null auto_increment
主键:id int not null auto_increment primary key
修改字段(长度):
alter table stutable change name name varchar(20) not null;
alter table [表名字] change [旧字段] [新字段] [新字段的类型] [新字段的约束]
新增字段:新增字段 alter table [表名字] add ([字段] [类型] [约束], [字段] [类型] [约束])
alter table stutable add(beiyong text);
删除字段: alter table [表名] drop 字段
alter table stutable drop beiyong;
查询:先插入
insert into stutable (name,phone) values('xiaoling ','999');
查询所有
select * from stutable;
根据子句查找
select * from stutable where id=4;
通过两个条件查找人:
select * from stutable where name = 'xiaozhao'and phone = '10086'; //&&
通过电话查找姓名
select name from stutable where phone ='119' or phone = '120'; // ||
select name,phone from stutable where phone ='119' or phone = '120';
根据条件查找记录的个数:
select count(name) from stutable where phone ='119' or phone = '120';
分页:向上取整(网页)
select count(name) from stutable where name like '%xiao%';(包含多少含xiao的字符)
//模糊查找
like % 模糊查找
//排序默认是asc
select score from scoretable order by score;(升序)
select * from scoretable order by score desc;(降序)
最高分数:select max(score) from scoretable;
select min(score) as 'Minnnn' from scoretable;(最低分---as 重命名)
select avg(score) as 'avggggg' from scoretable;(平均值)
大于70: select score from scoretable where score>90 order by score;
查几条: select avg(score) as 'avggggg' from scoretable limit 1,2;select score from scoretable where score>90 order by score limit 1,2;
limit begin,pageCount;
//函数
count() 统计个数
max() 最大值
min()最小值
avg()平均值
子查询:
select score from scoreTable where stuId =(select id from stutable where name = 'zhangxiao');
(分页)
select * from stutable where name like '%xiao%' limit 1,3 ;
