mysql常用知识点总结

来源自热心的网友

SQL 常用语句积累：
一、 SQL 基本语句
SQL 分类：
DDL —数据定义语言 (Create ， Alter ， Drop ， DECLARE)
DML —数据操纵语言 (Select ， Delete ， Update ， Insert)
DCL —数据控制语言 (GRANT ， REVOKE ， COMMIT ， ROLLBACK)
 
首先 , 简要介绍基础语句：
1 、说明：创建数据库
Create DATABASE database-name
2 、说明：删除数据库
drop database dbname
3 、说明：备份 sql server
---  创建 备份数据的  device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
---  开始 备份
BACKUP DATABASE pubs TO testBack
4 、说明：创建新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
根据已有的表创建新表：
A ： create table tab_new like tab_old ( 使用旧表创建新表 )
B ： create table tab_new as select col1,col2 …  from tab_old definition only
5 、说明：删除新表 drop table tabname
6 、说明：增加一个列
Alter table tabname add column col type
注：列增加后将不能删除。 DB2 中列加上后数据类型也不能改变，唯一能改变的是增加 varchar 类型的长度。
7 、说明：添加主键：  Alter table tabname add primary key(col)
说明：删除主键：  Alter table tabname drop primary key(col)
8 、说明：创建索引： create [unique] index idxname on tabname(col … .)
删除索引： drop index idxname
注：索引是不可更改的，想更改必须删除重新建。
9 、说明：创建视图： create view viewname as select statement
删除视图： drop view viewname
10 、说明：几个简单的基本的 sql 语句
选择： select * from table1 where  范围
插入： insert into table1(field1,field2) values(value1,value2)
删除： delete from table1 where  范围
更新： update table1 set field1=value1 where  范围
查找： select * from table1 where field1 like  ’ %value1% ’  ---like 的语法很精妙，查资料 !
排序： select * from table1 order by field1,field2 [desc]
总数： select count * as totalcount from table1
求和： select sum(field1) as sumvalue from table1
平均： select avg(field1) as avgvalue from table1
最大： select max(field1) as maxvalue from table1
最小： select min(field1) as minvalue from table1
11 、说明：几个高级查询运算词
A ：  UNION  运算符
UNION  运算符通过组合其他两个结果表（例如  TABLE1  和  TABLE2 ）并消去表中任何重复行而派生出一个结果表。当  ALL  随  UNION  一起使用时（即  UNION ALL ），不消除重复行。两种情况下，派生表的每一行不是来自  TABLE1  就是来自  TABLE2 。
B ：  EXCEPT  运算符
EXCEPT  运算符通过包括所有在  TABLE1  中但不在  TABLE2  中的行并消除所有重复行而派生出一个结果表。当  ALL  随  EXCEPT  一起使用时 (EXCEPT ALL) ，不消除重复行。
C ：  INTERSECT  运算符
INTERSECT  运算符通过只包括  TABLE1  和  TABLE2  中都有的行并消除所有重复行而派生出一个结果表。当  ALL  随  INTERSECT  一起使用时 (INTERSECT ALL) ，不消除重复行。
注：使用运算词的几个查询结果行必须是一致的。
12 、说明：使用外连接
A 、 left outer join ：
左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
B ： right outer join:
右外连接 ( 右连接 ) ：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。
C ： full outer join ：
全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。

二、 SQL 子查询语句
1 、单行子查询
        select ename,deptno,sal
        from emp
        where deptno=(select deptno from dept where loc='NEW YORK') ；
2 、多行子查询
        SELECT ename,job,sal
        FROM EMP
        WHERE deptno in ( SELECT deptno FROM dept WHERE dname LIKE 'A%') ；
3 、多列子查询
        SELECT deptno,ename,job,sal
        FROM EMP
        WHERE (deptno,sal) IN (SELECT deptno,MAX(sal) FROM EMP GROUP BY deptno) ；
4 、内联视图子查询
       (1)SELECT ename,job,sal,rownum
          FROM (SELECT ename,job,sal FROM EMP ORDER BY sal) ；
       (2)SELECT ename,job,sal,rownum
          FROM ( SELECT ename,job,sal FROM EMP ORDER BY sal)
          WHERE rownum<=5 ；
 
5 、在 HAVING 子句中使用子查询
     SELECT deptno,job,AVG(sal) FROM EMP GROUP BY deptno,job HAVING AVG(sal)>(SELECT sal FROM EMP WHERE ename='MARTIN') ；
6 、内连接   左连接   右连接举例；
     select sys_user.user_id ,sys_user.user_code  from  sys_user inner join XZFW_BANJIE onsys_user.user_id=XZFW_BANJIE.userid
    小例子：
     select top 10 * from sys_user  where user_code not in (select  user_code  from sys_user where user_code like '%yzj%')
     select top 2 * from (select top 2 *  from td.users order by us_username desc) users order by us_username desc
 7 、删除约束语句：
     alter   table   dbo.XZFW_SYS_USER   drop   CONSTRAINT  FK1772E1891324F678
 8 、记录数查询
      select count(user_pass) from sys_user
      select count(*) from sys_user where user_code!='admin'
9 、在范围之间取值 ( between ... and .. 用法 )
      select sys_user.user_id,sys_user.user_name,xzfw_shoujian.caseid from sys_user inner join xzfw_shoujian on sys_user.user_id=xzfw_shoujian.userid where user_id between 5 and 100
      或 select * from sys_user  where user_id<10 and user_id>1
10 、 三表查询实例：（三张表为： USER_DETAILS ， Subject ， Score ）
select USER_DETAILS.USER_NAME,Subject.SubjectName,Score.Score from USER_DETAILS inner join Scoreon USER_DETAILS.USER_ID=Score.USER_ID inner join Subject on Score.SubjectID=Subject.SubjectIDwhere USER_DETAILS.USER_ID=1
  常用查询举例：
 
select * from dbo.USER_DETAILS where USER_NAME='Cheers Li' and USER_POSITION='SQE'
select * from dbo.USER_DEPT
select * from dbo.USER_DETAILS
select top 3* from dbo.USER_DETAILS inner join dbo.USER_DEPT onUSER_DETAILS.USER_DEPT_ID=dbo.USER_DEPT.USER_DEPT_ID
insert into dbo.USER_DEPT (USER_DEPT_ID,USER_DEPT_NAME)values('QE_01','Software quality engineer')
update USER_DEPT set USER_DEPT_ID='QE_02' where USER_DEPT_NAME='Quality Control'
delete from dbo.USER_DEPT where USER_DEPT_ID='QE_01'
select dbo.USER_DETAILS.USER_NAME,dbo.USER_DETAILS.USER_AGE,dbo.USER_DEPT.USER_DEPT_NAME,USER_DEPT.USER_DEPT_ID fromdbo.USER_DETAILS right join dbo.USER_DEPT onUSER_DETAILS.USER_DEPT_ID=dbo.USER_DEPT.USER_DEPT_ID
select count(USER_NAME)from dbo.USER_DETAILS where USER_NAME='Cheers Li'
alter table USER_DEPT add Testcolumn char
alter table USER_DEPT drop column Testcolumn
select top 3* from(select top 3* from dbo.USER_DETAILS where USER_DEPT_ID='DEV_01' order byUSER_AGE desc)aa order by USER_ID desc
 
select * from dbo.USER_DETAILS where USER_NAME=(select max(USER_NAME) fromdbo.USER_DETAILS)

三、补充常用语句。
1. select employees.employee_id,employees.first_name,employees.last_name,salary*(1+0.1) new_salary from hr.employees;
2. select employee_id,first_name from hr.employees where first_name like 'B%';
3. select count(*) from hr.employees where first_name like 'B%';
4. select job_id,avg(salary),sum(salary),max(salary),count(*) from hr.employees group by job_id;

 
其次，大家来看一些不错的 sql 语句
1 、说明：复制表 ( 只复制结构 , 源表名： a 新表名： b) (Access 可用 )
法一： select * into b from a where 1<>1
法二： select top 0 * into b from a
 
2 、说明：拷贝表 ( 拷贝数据 , 源表名： a 目标表名： b) (Access 可用 )
insert into b(a, b, c) select d,e,f from b;
 
3 、说明：跨数据库之间表的拷贝 ( 具体数据使用绝对路径 ) (Access 可用 )
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子： ..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..
 
4 、说明：子查询 ( 表名 1 ： a 表名 2 ： b)
select a,b,c from a where a IN (select d from b ) 或者 : select a,b,c from a where a IN (1,2,3)
 
5 、说明：显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
 
6 、说明：外连接查询 ( 表名 1 ： a 表名 2 ： b)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
 
7 、说明：在线视图查询 ( 表名 1 ： a )
select * from (Select a,b,c FROM a) T where t.a > 1;
 
8 、说明： between 的用法 ,between 限制查询数据范围时包括了边界值 ,not between 不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 数值 1 and 数值 2
 
9 、说明： in 的使用方法
select * from table1 where a [not] in ( ‘值 1 ’ , ’值 2 ’ , ’值 4 ’ , ’值 6 ’ )
 
10 、说明：两张关联表，删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
 
11 、说明：四表联查问题：
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
 
12 、说明：日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff('minute',f 开始时间 ,getdate())>5
 
13 、说明：一条 sql 语句搞定数据库分页
select top 10 b.* from (select top 20 主键字段 , 排序字段 from 表名 order by 排序字段 desc) a, 表名 b where b. 主键字段 = a. 主键字段 order by a. 排序字段
 
14 、说明：前 10 条记录
select top 10 * form table1 where 范围
 
15 、说明：选择在每一组 b 值相同的数据中对应的 a 最大的记录的所有信息 ( 类似这样的用法可以用于论坛每月排行榜 , 每月热销产品分析 , 按科目成绩排名 , 等等 .)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
 
16 、说明：包括所有在 TableA 中但不在 TableB 和 TableC 中的行并消除所有重复行而派生出一个结果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)
 
17 、说明：随机取出 10 条数据
select top 10 * from tablename order by newid()
 
18 、说明：随机选择记录
select newid()
 
19 、说明：删除重复记录
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
 
20 、说明：列出数据库里所有的表名
select name from sysobjects where type='U'
 
21 、说明：列出表里的所有的
select name from syscolumns where id=object_id('TableName')
 
22 、说明：列示 type 、 vender 、 pcs 字段，以 type 字段排列， case 可以方便地实现多重选择，类似 select 中的 case 。
select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type
显示结果：
type vender pcs
电脑 A 1
电脑 A 1
光盘 B 2
光盘 A 2
手机 B 3
手机 C 3
 
23 、说明：初始化表 table1
TRUNCATE TABLE table1
 
24 、说明：选择从 10 到 15 的记录
select top 5 * from (select top 15 * from table order by id asc) table_ 别名 order by id desc
数据库基本理论整理：
通俗地理解三个范式
通俗地理解三个范式，对于数据库设计大有好处。在数据库设计中，为了更好地应用三个范式，就必须通俗地理解三个范式 ( 通俗地理解是够用的理解，并不是最科学最准确的理解 ) ：
第一范式： 1NF 是对属性的原子性约束，要求属性具有原子性，不可再分解；
第二范式： 2NF 是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；
第三范式： 3NF 是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。
没有冗余的数据库设计可以做到。但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是：在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。
        基本表及其字段之间的关系 , 应尽量满足第三范式。但是，满足第三范式的数据库设计，往往不是最好的设计。为了提高数据库的运行效率，常常需要降低范式标准：适当增加冗余，达到以空间换时间的目的。
〖例 2 〗：有一张存放商品的基本表，如表 1 所示。“金额”这个字段的存在，表明该表的设计不满足第三范式，因为“金额”可以由“单价”乘以“数量”得到，说明“金额”是冗余字段。但是，增加“金额”这个冗余字段，可以提高查询统计的速度，这就是以空间换时间的作法。
在 Rose 中，规定列有两种类型：数据列和计算列。“金额”这样的列被称为“计算列”，而“单价”和“数量”这样的列被称为“数据列”。
表 1 商品表的表结构
商品名称 商品型号 单价 数量 金额
电视机 29 吋 2500 40 100,000
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 199,711评论 5赞 468
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 83,932评论 2赞 376
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 146,770评论 0赞 330
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 53,799评论 1赞 271
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 62,697评论 5赞 359
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,069评论 1赞 276
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,535评论 3赞 390
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,200评论 0赞 254
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,353评论 1赞 294
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,290评论 2赞 317
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,331评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,020评论 3赞 315
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,610评论 3赞 303
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,694评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,927评论 1赞 255
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,330评论 2赞 346
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 41,904评论 2赞 341

推荐阅读更多精彩内容