SQL学习笔记

1、primary key 主键
foreign key 外键（在另外一个表格里是主键）
2、结束标记  ；（分号）或者/g.
3、显示有几个数据库 show databases;
4、登录数据库： mysql -u localhost -p (此处输入密码)
5、创建数据库: create database (此处输入数据库名字)
6、选中数据库：use (此处输入数据库名字)
7、查看表： show tables;
8、删除数据库：drop database test;
9、创建一张表 create table class(//(class是表名)
stu int,
name varchar(20),
age int
);
10、给表重命名 rename table oldname to newname ;
11、删除表：drop table test（test为表名）;
12、描述表 description class(desc class);
13、写错语句可以用\c退出
14、设定字符集 set names gbk;
15、整型  smallint 2字节 mediumint 3字节 int4字节 bigint 8字节 tinyint 1字节（数据默认有符号）
tinyint(M) unsigned zerofill  : M 代表数据宽度，只有在zerofill时才有意义 zerofill代表0填充（默认无符号的）
16、浮点型 float(M,D) M代表总位数，不包含点 D表示精度（小数位）
    定点型同float相同，但是decimal更精确(不进行四舍五入)
17、字符型 char 定长 varchar 变长 （不会丢失尾部的空格，因为尾部有计数位）   char(M)   M代表宽度（0~255），可容纳的字符数
    定长比变长运行速度快，so文章这种可以用varchar（范围0-65535,2W到6W字符），用户名这种用char（0-255）
    text 文本字符串（范围2W-6W）
18、时间类型
    year 类型    1个字节 1901-2155 （0000年表示错误年份或者不输入年份）
                 如果输入2位 '00-69' 2000-2069
                             '70-99' 1970-1999    
    date 类型    典型格式 1992-08-12  日期类型 1000-01-01 --- 9999-12-31
    time 类型    典型格式 hh:mm:ss    时间类型 '-838:59:59'->'838:59:59'
    datetime 类型
19、精确到秒的时间表示法，不是datetime，而是用int，因为计算方便，而且可以格式化不同的显示样式
20、select 的五种子句
    where carid in(4,5);  表示查询carid等于4或者等于5的行---------（in）
    where carid between(1,6); 表示carid的值在1-6之间的的数值（允许等于两个边界值 ）----------(between)
    where carid >=30 and carid <=50; 表示carid的值在30-50之间----------（and 或 &&）
    where carid >=30 and carid <=50 or carid >=300 and carid <=500; 表示carid的值在30-50或者在300-500之间----------(or 或 ||)
    where carid !=30 and carid NOT）50; 表示carid的值不等于30并且不等于50----------（!= 或 NOT）
    where carid not in(40,50); 表示carid的值不在40-50之间 -----------（not in）    
    where carname like '奥迪%'; % 表示通配任意字符-----------（%）
    where carname like '奥迪_';_ 表示通配单个字符-----------（%）
21、统计函数 max min sum avg(求平均) count(求总行数)
select goods_id,max(price) from goods; max求物品最大值 -----(max)
select goods_id,max(price) from goods group by goods_id; 以goods_id为组，查询每组里的最大值  ------（group by）
select count(*) from goods; 查询所有商品的数量（总行数）--------(count)
selecrt sum(price1*price2) as sumprice from goods group by goods_id;  将price1*price2用as取别名为sumprice ------(as)
22、select 的语句的使用顺序必须是where 、group 、having  、order by 、limit

23、# 注释（不执行）        
24、having 和where 在功能上是相同的，区别是having可以用as重命名的变量继续查询，where不可以（如果两者同时使用，则where放在前面）。
原因如下：
select和where 都是从数据库里查询数据，不可以键as重命名的变量作为查询条件，因为重命名的变量是不存在数据库里的
eg：select salary+salary2 as sumsalary from goods where goods_id=2 having sumsalary>200;
eg：select salary+salary2 as sumsalary from goods group by goods_id having sumsalary>200;
25、order by price asc排序价格 递增
    order by goods_id,price desc 递减排序(优先以good_id排序升序，然后再以价格排序降序)
26、limit[偏移量（可选，默认为0）][取出的条目]  eg:limit 3,3;从第四个开始的三个数 4,5,6
    order by price asc limit 3,3；
27、查询的结果可以当成一个表（子查询）
28、子查询（where 、from、exists）
where：select goods_is,goodname from goods where goods_id =(select mac(goods_id) from goods);---(where)查询最新添加的产品
把内层查询的结果作为外查询的条件
from： select * from (select goods_id, gooddsname from goods order by good_id desc) as tmp group by cat_id;---(from)查询每个栏目下最新的商品
把内层查询的结果当成临时表，供外层SQL再次查询
exists:Select goods_id, goods_name from CATEGORY where (select * from goods where goods.catid=CATEGORY.carid);---(exists)
把外层的查询结果，拿到内层，看内层的查询是否成立
29、union的用法：合并2次或者多次询结果（查询的列一致（以第一次查询的列为准），可以同时查询多张表）
    如果不同语句中取出的行，有完全相同（列值相同），那么查询的时候将去除重复的 select * from ta union select * from tb
    如果不取出重复，可以用all来指定。 select * from ta union all select * from tb;
30、在查询的时候，如果希望看见显示的列名，则在from前些，而筛选显示的行的数据则在from之后限定。
31、如果子句中有 order by，limit，须加（），推荐放到所有子句之后，即对最终合并后给结果排序
    在子句中，order by与limit要配合使用才有意义。
32、    连接查询：左连接、右连接、内连接
左连接：select 列1，列2，列N from LeftTable  left join RightTable on LeftTable=RightTable where ......(from 和where之间的数据作为一张表看)
右链接：select 列1，列2，列N from LeftTable  right join RightTable on LeftTable=RightTable where ......(from 和where之间的数据作为一张表看)   
内连接：select 列1，列2，列N from LeftTable  inner join RightTable on LeftTable=RightTable where ......(from 和where之间的数据作为一张表看)
 a、 左连接、右连接：以左表为准，去找右表找匹配数据，或右表没有匹配的数据，右表对应的数据设置成null，
     不管是左连接还是右连接，都以左表为准。eg: A left join B ==> B right join A;(都代表A是左表)
 b、 内连接：查询左右表都有的数据，即，不要左右表里 null的数据（左右连接的交集） 
     外连接：求并集（SQL不支持，但是可以用union来达到目的）
  
   列的增加与删除、修改
33、两个集合相乘，叫笛卡尔积，就是两个集合的完全组合。
    select * from ta,tb; ta里有四行，tb里有四行，执行之后的结果为16行（先将ta*tb，再查询，相乘是将ta
    里的每行数据依次和tb里给每行相乘）
34、视图：是由查询结果形成一张虚拟表。
创建语法：create view 视图名  as Select 语句；
create view shitu as select * from goods;
删除视图：drop view
35、视图增删该，视图与表一一对应的时候，可以修改视图影响表，
但是对于视图insert还应该注意，视图中必须包含表中没有默认值的列。
36、altorerhm = temptable 是创建一张临时的表 merage是连接两张表.
37、字符转换
a、告诉服务器,发送给服务器什么编码 character_set_client
b、告诉服务器上的转换器,转换成什么编码 character_set_connection
c、服务器查询的结果显示成什么编码 character_set_resutls
 如果以上三者都为一样的N ，则可以简写set names N
38、truncate 表名；清空表
39、show collation;显示校对集
40、创建触发器的语法： 
create trigger triggerName 
After/Before insert/update/delete on 表名 
for each row
begin
SQL语句
end;

eg: 
create trigger g1
after insert on otable
for each row 
begin
undate gtable set num = num -3 where id = 2;
end$

41、设定结束符 delimiter $;
42、truncate Stable;清空Stable表
43、触发器里，对于insert而言，新增的行，用new来表示。
    行中的每一列的值，new、列名来表示
a、添加订单，库存减少
create trigger tg2
after insert on otable
for each row
begin 
undate gtable set num = num-new.gnum where id=new.gid;
end$
b、删除订单，库存相应增加
create trigger tg3
after delete on otable
for each row
begin
undate gtable set num = num+old.gnum where id=old.gid;
end$
c、修改订单的数量时， 库存相应改变,id也发生修改
create trigger tg4
after delete on otable
for each row
begin
undate gtable set num = num+old.gnum where id=old.gid;
undate gtable set num = num-new.gnum where id=new.gid;
end$
44、常用的表的引擎
myisam 批量插入速度快， 不支持事务，锁表,不支持外键，默认不释放，所以快，需要定时清理（optimize table 表名）
innodb 批量出入速度慢，支持事务，锁行，支持外键  默认是这个引擎
45、事务：（ACID特性）只一组操作，要么都成功执行，要么都不执行,事务前后应保持一致，并且对其他事务不可见
，事务处理完成之后，其影响应该保留下来，不能撤销
原子性（atomicity）、一致性(xonsistency)、隔离性(isolation)、持久性(durability)

msg eg：
1、a增加行
insert into msg
(id,title,name,content)
values
(2,'出来','李三','双马电子'),
(4,'出来','李四','双马电子'),
(5,'出来','李五','双马电子');
   b增加列
alter table class add age2 tinyint unsigned;    
alter table class add age2 tinyint not null default 0;//如果不设置值，默认为0    
2、查询整个表
select * from msg;
3、更新
update msg set
id = 3,
content = '修改留言内容'
where
name = '张三';
4、删除(必须删除一行)
delete from msg
where id = 2;
5、查询特定列id title
select id,title from msg;
6、查询特定行 
select *from msg where id>2;//查询id>2d的行
select *from msg where content='双马电子';//查询content='双马电子'
7、查询特定行和特定列
select id,content from msg where id>2;
(查询所有表 select * from msg where 1;)
8、建有主键且自增长的表
create table class(
id int primary key auto_increment,
name varchar(10),
age tinyint
)charset utf8;
9、建立一个有浮点数的表
create table goods(
name varchar(10) not null default '',
price float(6,2) not null default 0.00
)charset utf8;

10、创建时间表
create table y(
ya year(4)
);

create table da(
title varchar(30),
dt date
)charset utf8;

create table ti(
tm time);

create table dati(
name varchar(20) not null default '',
regtime datetime not null default '1000-01-01 00:00:00'
)charset utf8;

11、创建多类型表
create table all9(
id int primary key auto_increment,
name char(3) not null default '',
age tinyint unsigned not null default 0,
email varchar(30) not null default '',
tel char(11) not null default '',
salary decimal(7,2) not null default '2000',
riqi date not null default '2012-03-12'
)charset utf8;
12、增加列
a、alter table goods add price tinyint unsigned not null default 20 after cat;在商品的表里添加价格在种类的后面。
(alter table 表名 add 列声明 after (在哪列的后面))
b、新增列默认放在表的最后一列
c、alter table goods add id int primary key auto_increment first;新增表放在表的最前面
（alter table 表名 add 列声明 first）
13、修改列
alter table 表名 change 被改变的列名 列声明
14、删除列：
alter table 表名 drop 列名；alter table goods drop price;删除商品中的价格

数据库优化
1、数据库结构、sql语句、数据的配置、适当的硬件配置和操作系统。
2、sql分类：ddl(数据定义语言)[create alter drop]
           dml(数据操作语言)[insert delete update]
           select
           dtl(数据事务语言)[commit（提交事务） rollback（回滚事务） savepoint]
           dcl(数据控制语言)[grant revoke(权限相关)]
3、show status 命令：显示SQL数据库当前状态，主要关心‘com’开头的  com_select com_updata com_delete com_insert
show status like 'Com%'  相当于 show session status  like 'com%'   显示当前控制台的查询情况
show global status  like 'com%'  显示数据库从启动到现在的查询次数
4、显示连接数据库次数 show status like  'connections'
5、show status like  'uptime':服务器的工作的时间（单位秒）
6、show status like 'show_queries':慢查询的次数（默认是10秒）优化重点
查看慢查询的情况： show variables like 'long_query_time'
7、自定义函数 (产生n个随机字符串)  调用示例： Select rand_string(6)
create function rand_string(n int)
returns varchar(255)
begin 
 declare chars_str varchar(100) default
 'abcdefghigklmnopqrstuvwsyzABCDEFGHIGKLMNOPQRSTUVWSYZ';
 declare return_str varchar(255) default'';
 declare i int default 0;
 while i < n do
 set return_str = concat(return_str,substring(chars_str,floor(1+rand()*52),1));
 set i = i+1;
 end while;
 return return_str;
 end
8、删除自定义函数 drop function rand_string
9、自定义函数（产生随机数字）  调用示例：select rand_num();
create function rand_num()
return int(5)
begin
declare i int default 0;
set = floor(10+rand()*500);
return i;
end
10、设置慢查询的默认定义时间
11、加   \G 可以纵向排列显示
12、show indexes from 表名  查询表的索引
13、将表中数据全部重新添加到自己是数据库里（复制自己再填充自己）select * from select * from a;
14、定点数decimal比浮点数float精确
15、

1、数据库约束 
主键约束，str_id int primary key
外键约束 
check约束 stu_sal int check (stu_sal>=1000 and stu_sal<=8000)
default约束 stu_sex nchar(1) default '男'
唯一约束  stu_name nvarchar(200) unique(允许为空)

2、删除表 用drop table 表名
3、sql中NULL不参与<> !=运算，但是可以用is  not is实现
4、count(字段名)返回字段值为非空的记录的个数，重复的记录也会被当作有效值
   count(distinct 字段名)返回字段不重复并且非空的记录的个数