2.bigint :数字比较大的时候,例如页面点击量
3.如果数据库中有100条数据
(1)select username from yii_blog where blogid<50 and blogid > 30;(正确的)
(2)select username from yii_blog where blogid>20 and blogid > 50;
两条数据那个执行的速度快?答案是第一条(sql语句是按顺序来执行的)
4. select * from yii_blog where content like '1%';(查出内容字段以1开始的所有信息)
select * from yii_blog where content like '%3';(查出内容字段以3结尾的所有信息)
select * from yii_blog where content like '%2%';(查出内容中包含2的所有信息)
5.找出正好名字包含3个字符的所有信息
select * from pet where name like "___";
6.找出blogid的最大值
select MAX(blogid) as num from yii_blog;
7.找出bolgid的总记录
select count(blogid) as num from yii_blog;
8. 计算几个值的和:select (4+2);
select sum(blogid) as num from yii_blog;
9. 根据blogid的范围查询出所有数据
select * from yii_blog where blogid between 30 and 34;
多表查询:
10.union(联合查询)查询出两个表中uid的集合(合并两个查询的结果)
select uid from yii_blog union select uid from yii_user;
11. 取出两张表中的uid相等的数据:(关联查询-左连接,以左边那张表为主)
select * from yii_user left join yii_blog where yii_user.uid= yii_blog.uid;
12.取出两张表中的uid相等的数据:(关联查询-右连接,以右边那张表为主)
select * from yii_user right join yii_blog where yii_user.uid = yii_blog.uid;
13. 两张表比较查询(当两张表中的uid相等时查询出yii_user表中的username)
select yii_user.username from yii_user left join yii_blog on yii_user.uid =yii_blog.uid;
14.添加新字段
alter table yii_user add email varchar(10) not Null;
alter table table1 add id int unsigned not Null auto_increment primary key;(自动递增,作为主键)
alter table yii_user add lk varchar(10) not Null after username;(在username字段后面添加新字段)
15.删除字段
ALTER TABLE yii_user DROP email;
16.修改字段
(1)修改字段名称:alter table yii_user change email myemail char(10) not null; (此处email 是原字段名字 myemail是修改后的新字段名字)
(2)修改字段类型:alter table yii_user MODIFY lk CHAR(10);