sql 语句学习 1 -持续更新中
1.连接数据库
mysql -u用户名 -p密码
mysql - uroot -pli
Screen Shot 2016-05-15 at 3.43.37 PM.png
2.展示数据库
show databases;
Screen Shot 2016-05-15 at 3.45.16 PM.png
3.操作数据库
use user;
Screen Shot 2016-05-15 at 3.46.04 PM.png
4.展示数据库中的表
show tables;
Screen Shot 2016-05-15 at 3.48.16 PM.png
5.结构化查询语言-sql(structured query language)
1.DDL 数据定义语言
create:创建表
//table_name 表名 //id 主键 类型 int 自增长 //name 字段名 类型 varchar(20) //introduce 字段名 类型 text create table table_name ( id int unsigned auto_increment primary key, name varchar(20), introduce text); //主键索引和普通索引 create table t1( id int unsigned auto_increment ,name,primary key(id),index in_name(name)); //添加索引的目的是加快查询速度
drop:删除表
drop table table_name;
alter://修改表
//给已有的表添加字段 alter table table_name add age int; //删除字段 alter table table_name drop age; //修改表中已有的字段的属性 alter table table_name modify age int not null default 20; //修改表中已有的字段的名字 alter table table_name chanage age user_age int; //查看表中的索引 show index from table_name; show index from table_name\\G //修改表名 alter table table_old_name rename table_new_name; //给字段添加索引 alter table table_name index in_name(name); //删除表中索引 alter table table_name drop index in_name; //打印sql语句执行信息 desc sql语句;
2.DML 数据操作语言
insert:增
insert into table_name(字段名) values('字段值'); insert into table_name(字段名1,字段名2) values('字段值1','字段值2');
delete:删
//删除talbe_name中id=6的记录 delete from table_name where id = 6; //删除talbe_name中id=6或者id=7的记录 delete from table_name where id = 6 or id = 7; delete from table_name where id in (6,7); //删除表中id>6&&id<8 delete from table_name where id > 6 and id < 8; delete from table_name where id between 6 and 8;
update:改
//修改表中id等于6的name等于a update table_name set name = 'a' where id = 6; //修改表中id等于6的name等于a，pass等于lsb update table_name set name = 'a' ，pass = 'lsb' where id = 6;
3.DQL 数据查询语言
select:查
//查询所有字段 select * from table_name; //查询特定字段 select id from table_name; //查询特定字段然后取别名 select id uid from table-name; //取唯一值 select distinct from table_name; //查询字段是否为空 select * from table_name where pass is null; //查询地段不为空的 select * from table_name where pass i not null; //查询字段中包含c的 select * from table_name where name like '%c%'; select * from table_name where name regexp '.*c.*'; //查询字段中包含c或者包含b的 select * from table_name where name like '%c%' or name like '%b%'; select * from table_name where name regexp '(.*c.*)|(.*b.*)'; //排序 默认升序 续写或者写 asc select * from table_name order by id; select * from table_name order by id asc; //id字段按降序排 select * from table_name order by id desc; //查询id前三大的记录 select * from table_name order by id desc limit 3; select * from table_name order by id desc limit 0,3; //mysql 字符串连接符 select concat('a','b'); select concat(id,name) from table_name; //随机排序 select * from table_name order by rand(); //随机排序取前五个 select * from table_name order by rand() limit 5; select * from table_name order by rand() limit 0,5; //统计 select count(*) from table_name; select count(id) from table_name; select count(id) total from table_name;//取别名 //求和 sum select sum(id) from table_name; //求平均值 select avg(id) from table_name; //求最大值 select max(id) from table_name; //求最小值 select min(id) from table_name; //取最大值和最小值 select max(id) max_value,min(id) min_value from table_name; //分组聚合 //按table_name表中的name分组 select name ,count(id) from table_name group by name; //给分组取别名 select name,count(id) total from table_name group by name; //分组降序排列 select name ,count(id) total from table_name group by name order by total desc; //查询分组中大于5条的 select name ,count(id) total from table_name group by name having total > 5;
4.DCL 数据控制语言
数据控制语言（DCL）是用来设置或者更改数据库用户或角色权限的语句，这些语句包括GRANT、DENY、REVOKE等语句，在默认状态下，只有sysadmin、dbcreator、db_owner或db_securityadmin等角色的成员才有权利执行数据控制语言。
6.多表查询
1.普通多表查询
//查询t1中name和t2中uid相等的记录 select * from t1,t2 where t1.name = t2.uid; //分组查询 select t.name,t2.uid from t1,t2 where t1.name = t2.uid groupd by t2.uid
2.左连接多表查询
select t1.name ,t2.uid from t1 left join t2 on t1.name = t2.uid;
