查看所有的數(shù)據庫:show databases;
創(chuàng)建數(shù)據庫 create database [if not exists] studb;
切換數(shù)據: use studb;
展示所有的表格:show tables;
創(chuàng)建表格:
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;
查看表的結構(創(chuàng)建表的語句): show create table stuTable;
查找表的:select * from stuTable;
表重命名:rename table 舊名字to 新名字;
刪除表: drop table newstutable;
刪除數(shù)據庫: 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';
根據條件查找記錄的個數(shù):
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;(降序)
最高分數(shù):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;
//函數(shù)
count() 統(tǒng)計個數(shù)
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 ;
