《必背的mysql语句》由会员分享，可在线阅读，更多相关《必背的mysql语句（6页珍藏版）》请在金锄头文库上搜索。
1、必背的mysql语句/* set names gbk;交互字符集 1-交互字符集要和我的sql语句的编码保持一致出现乱码问题： mysql_connect(localhost,root,printf); mysql_query(set names utf8); mysql_query(insert into.); insert into use (id, name) values(1,小小帅); 1-数据库的编码是utf8 alter database psd1312 default character set utf8; 2-数据表的编码是utf8 alter table user deaf
2、ault character set utf8; 3-表中中文字段是否为utf8 alter table user modify username char(10) default charset=utf8 not null; */00001:建带有默认编码的数据库(psd1305.默认编码为：gbk)create database psd1308 default character set gbk;00002：先判断是否存在的建表方式(course表 id int(4) ,非空自增长)drop table course;create table course(id int(4) auto_i
3、ncrement not null);或者create table if not exists course(id int(4) auto_increment not null);00003：增删改增增加字段(demo表 uId int) alter table demo add uId int; 增加记录 (demo表 id ,name ,sex ,age) insert into demo(id ,name ,sex , age)values(1 , lanol , female, 18);增加唯一索引(demo表 unique) alter table demo modify id in
4、t unique;增加主键(demo表 primary key) alter table demo modify id int primary key; alter table tb change id id int(10) not null auto_increment=1;增加缺省(demo表 not null default 0) alter table demo modify age int not null default 0;增加外键(t1表 t2表 foreign key references )alter table t1 add foreign key(typeId) ref
5、erences t2(typeId);删删除字段(demo表 删id) alter table demo drop id;删除记录(demo表 id=2) delete from demo where id = 2;删除表(demo表) drop table demo;删数据库(数据库psd1305) drop database psd1308;删除唯一索引(show demo表+id为索引名) show index from demo; alter table demo drop index id;-id为索引名删除主键:-先删除自增长再删除主键alter table tb change i
6、d id int(10);/删除自增长alter table tb drop primary key;/删除主建改改字段名(demo表中的id改为uId int) alter table demo change id uId int;改记录内容(books表 name=wooght_1,sex=male-id=1) update books set name=lanol_1,sex=male where uId=1;改字段类型(改demo表 编码格式为utf8 not null) alter table demo modify uId varchar(5) charset utf8 not n
7、ull;*改表名：(demo表-demo1表) alter table dmeo rename demo1;*改数据库的字符集alter database pad1308 default character set utf8;/修改数据库的字符集改表的字符集alter table psd1308 default character set utf8;/修改表的字符集00004：查单表a:截断:(limit 0 ,3)select bId , publishing , bName , bTypeId from books limit 0 ,3;一个参数就为长度b:排序:(order by pub
8、Date desc;)select bId , bName , pubDate from books order by pubDate desc;降序c:where子句:between.and:select bId , bName , price from books where price between 50 and 60;(包括50和60)替代：select bId , bName , price from books where price=50 and price=60;in:select bId , bName , price from books where price in(
9、50 , 60);替代:select bId , bName , price from books where price=50 and price=60; 关键字查找：select bId , bName from books where bName like %网站%;d:分组：(bId , bName , bTypeId group by )select bId , bName , bTypeId from books group by bTypeId;多表子查询:select name , age from demo where uId=(select uId from cousr w
10、here score=100); 内连接:select e.empno 编号,e.ename 姓名,d.dname 部门名称from emp e join dept dwhere e.deptno = d.deptno;外左连接:select e.empno 编号,e.ename 姓名,d.dname 部门名称from emp e left join dept don e.deptno = d.deptno; 外右连接:select e.empno 编号,e.ename 姓名,d.dname 部门名称from emp e right join dept don e.deptno = d.deptno; /缺了哪个部分？select id,title,count from bbs_post where id in (select id from bbs_post where title like %刘欢%);
