1.連接數(shù)據(jù)庫(kù)
mysql -u用戶名 -p密碼
mysql - uroot -pli
2.展示數(shù)據(jù)庫(kù)
show databases;
3.操作數(shù)據(jù)庫(kù)
use user;
4.展示數(shù)據(jù)庫(kù)中的表
show tables;
5.結(jié)構(gòu)化查詢語(yǔ)言-sql(structured query language)
1.DDL 數(shù)據(jù)定義語(yǔ)言
create:創(chuàng)建表
//table_name 表名 //id 主鍵 類型 int 自增長(zhǎng) //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語(yǔ)句執(zhí)行信息 desc sql語(yǔ)句;
2.DML 數(shù)據(jù)操作語(yǔ)言
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 數(shù)據(jù)查詢語(yǔ)言
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.*)'; //排序 默認(rèn)升序 續(xù)寫或者寫 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; //隨機(jī)排序 select * from table_name order by rand(); //隨機(jī)排序取前五個(gè) select * from table_name order by rand() limit 5; select * from table_name order by rand() limit 0,5; //統(tǒng)計(jì) 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 數(shù)據(jù)控制語(yǔ)言
數(shù)據(jù)控制語(yǔ)言（DCL）是用來設(shè)置或者更改數(shù)據(jù)庫(kù)用戶或角色權(quán)限的語(yǔ)句睡腿，這些語(yǔ)句包括GRANT峻贮、DENY、REVOKE等語(yǔ)句纤控，在默認(rèn)狀態(tài)下，只有sysadmin细层、dbcreator唬涧、db_owner或db_securityadmin等角色的成員才有權(quán)利執(zhí)行數(shù)據(jù)控制語(yǔ)言。
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;
