MySql数据库总结

MySql数据库知识点总结

1.sql语言：结构化的查询语言。

2.sql语法特点:

（1）不区分大小写

（2）相等于赋值符号相同”=”

（3）无双引号，字符串用单引号括起来

（4）无bool值，但是在视图中可以输入true/false

（5）类型不严格：任何类型数据都可以包含在单引号内

（6）有逻辑运算符： ！(not) &&(and) ||(or)

（7）有关系运算符：> < >= <= = <> != ,它返回一个bool值

3.创建数据表：

Create table 表名称

(

字段名称 字段类型 字段特征(是否为null,默认值 标识列 主键 唯一键 外键 check约束),

......

)

代码：

创建老师表Teacher :Id、Name、Gender、Age、Salary、Birthday:

if exists(select * from sysobjects where name='teacher')

drop table teacher

create table Teacher

(

        Id int identity(1,1),//可以同时创建多个特征，用空格 分隔开。

  // identity是标识列，第一个参数是种子，第二个是增量

Name nvarchar(50) not null,-- not null标记它的值不能为null--不能不填写

ClassId int not null,

 Gender bit not null,

Age int  ,

Salary money, --如果不标记为 not null.那么就相当于标记了null

Birthday datetime

)

4.连接:

         主机名 端口  一个合法用户 用户口令

         Mysql -u root -p -h myserver -P 9999

5.选择数据库:使用USE关键字，mysql语言组成部分的一个关键字：USE dataname;

6.注意：绝不要使用关键字命名一个表或列

7. 数据库三大范式：

第一范式：每一数据项不可以分割；

第二范式：有主键；

第三范式：数据项不能依赖于除主键之外的字段；

8.INSERT语句：

insert into 表名称(列表项1,2,3...)values(对应列表项的值1),(对应列表项的值2)，(对应列表项的值3)...

例如：

insert into emp

(

emp_name,emp_job,emp_mgr,emp_hiredate,emp_sal,emp_comm,dept_no

)

values

('高洪','经理',2,'2012-11-25',6000,3000,2),

    ('佳佳','老板',null,'2010-11-25',8000,5000,3),

('baby','总监',1,'2016-11-25',6000,3000,1);

9.修改表结构：

（1）增加列：alter table 表名称 add （列名称1 类型1，列名称2 类型2...）若仅为一列，则可以不要括号

例如：

增加一列：alter table dept add dept_age int;

增加多列：alter table dept add (

  dept_sex int ,

  detp_weight int);

（2）修改列定义：

a.改变列类型：alter table 表名称 midify 列名称 列类型

把dept表中的dept_age列的类型int 变成varcher类型：

alter table dept modify dept_age varchar(30);

b.改变列名：alter table 表名称 change 原来列名称 新的列名称 类型 ;

把dept表中的dept_age列名变成age：

alter table dept change dept_age age int ;

（3）重新命名表名:alter table 表名称 rename to 新的表名称 ;

把dept表的名字变成dept_tb:

alter table dept rename to dept_tb;

（4）删除列：alter table 表名称 drop 列名称

把dept表的dept_age列删除；

alter table dept drop dept_age;

（5）删除主键：alter table 表名称 drop primary key;

把 dept 表的主键列dept_no 删除；

alter table dept drop primary key;

（6）添加主键：alter table 表名称 add primary key (主键名称)

给test表添加主键：

alter table test add primary key (test_no);#没有定义主键；

（7）删除表：drop table 表名称;

删除dept表；

drop table dept;

10. 插入数据（insert into ），更新数据（update .. set ..）和删除数据(delete from ...)

（1）(更新数据)：update dept（表名称） set dept_loc='新安县' where dept_no=2;

（2）(删除数据列)：delete from dept where dept_no=4;

（3）(将字段变成非空) ：alter table test modify test_name varchar(40) not null;

（4）(将字段变成空)：alter table test modify test_name varchar(20) null;

（5）(将字段变成唯一)：alter table test modify test_name varchar(20) unique;

（6）(删除字段唯一限制)： alter table test drop key test_name;#唯一的字段不用加 （），主键需要加（）；

（7）（增加外键）：alter table emp add constraint fk_1 foreign key (dept_no) references dept(dept_no);

（8）(删除外键)： 通过 show create table emp;查出系统生成的 constraint (约束名称) emp_ibfk_1;

第一步：alter table emp drop foreign key emp_ibfk_1;

第二步：alter table emp drop key dept_no;#创建外键时，没有指定外键名，[constraint fk_1];

11.show语句（显示信息）：

语句 功能

SHOW  DATABASES;    显示数据库列表：

SHOW TABLES; 显示一个数据库内的表的列表（USE进入数据库的情况下）

SHOW COLUMNS FROM column;    SHOW也可以用来显示表列： DESCRIBE 是SHOW COLUMNS的一种快捷方式；DESCRIBE cust;

SHOW STATUS             显示服务器状态信息

SHOW CREATE DATABASE    显示创建特定数据库的MYSQL语句

SHOW CREATE TABLE       显示创建特定表的MYSQL语句

SHOW GRANTS             显示授权用户的安全权限

SHOW ERRORS             显示服务器的错误信息

SHOW WARNINGS           显示服务器的警告信息

12.select语句（检索）：

语句 功能

SELECT columnOne FROM table; 检索单列

SELECT columnOne,columnTw FORM table; 检索多列

SELECT * FROM products; 检索所有列

13.DISTINCT关键字：保证检索出来的数据不重复

 SELECT DISTINCT columnOne FROM table;   //检索出来的columnOne没有重复值

注意：DISTINCT关键字应用于所有列而不仅是前置它的列：

SELECT DISTINCT vend_id，prod_price......要求vend_id，prod_price这两列都不出现重复的值

14.LIMIT关键字：限制结果条数：

 sql语句后面加入下面sql语句

        LIMIT 5          显示结果的前5条

        LIMIT 3,4        从行3开始的后4行

        LIMIT 4 OFFSET 3 从行3开始的后4行

15.排序：ORDER BY+列名 +ASC(升序，默认)/DESC（降序）

例如：SELECT columnOne,columnTwo,columnThire FROM table ORDER BY columnOne ASC, columnTwo  DESC;    //先按columnOne正排序 再按columnTwo倒序排序

16.过滤数据：WHERE语句+关系运算符/逻辑运算

17.BETWEEN 在指定的两个指之间 必须指定2个值。这两个值必须使用AND连接

例如：SELECT columnOne,columnTwo, FROM table WHERE columnOne BETWEEN 3 AND 8;   // 检索columnOne值为3到8之间的行     

18.空值检查：IS NULL子句：

例如：SELECT columnOne FROM table WHERE columnOne IS NULL;

   //检索 columnOne值为NULL的行

19.注意：在数据库中NULL是不能被匹配和不匹配的语句找出来，所以一定要注意表中是否存在NULL值。

20.AND组合：必须满足所有条件

例如：SELECT columnOne,columnTwo,columnThire FROM table WHERE columnOne = 2009 AND columnTwo <= 10;     //检索columnOne =2009 且 columnTwo <= 10 的行

21.OR组合：满足任意一个条件

例如：SELECT columnOne,columnTwo,columnThire FROM table WHERE columnOne = 2009 OR columnTwo <= 10;//检索columnOne =2009 或 columnTwo <= 10 的行

22.IN操作符：IN操作符用来指定范围，范围中的每个条件进行匹配。IN取合法值的逗号分隔的清单。IN操作符完成与OR相同的功能，优点如下：

        （1）IN操作符更清楚且直观

        （2）使用IN时，计算的次序更容易管理

        （3）IN操作符一般比OR操作符执行更快

        （4）IN操作符可以包含其他SELECT语句，使得能够更动态的创建WHERE子句

例如：SELECT columnOne,columnTwo FROM table WHERE columnOne IN (1002,1005,1006) ORDER BY columnTwo;    //检索columnOne为1002或1005或1006的行并且按columnTwo分组

23.NOT操作符:WHERE子句中的NOT操作符有且只有一个功能，那就是否定它之后所跟的任何条件

例如：SELECT columnOne,columnTwo FROM table WHERE columnOne NOT IN (1002,1005,1006) ORDER BY columnTwo;     //检索columnOne不为1002或1005或1006的行并且按columnTwo分组

24.通配符过滤：

（1）LINK操作符：

 %   通配在任何位置出现的任何字符，但是需要注意：该字符或者字符串区分大小写

        //检索以jet开头的词或句子

        SELECT columnOne,columnTwo FROM table WHERE columnOne LINK ' jet% ';

        //检索以jet结尾的词或句子

        SELECT columnOne,columnTwo FROM table WHERE columnOne LINK ' %jet ';

        //检索以jet包含的词或句子

        SELECT columnOne,columnTwo FROM table WHERE columnOne LINK ' %jet% ';

        //检索以e开头，以u 结尾的词或句子

        // SELECT columnOne,columnTwo FROM table WHERE columnOne LINK' e%u ';

%还可以匹配0字符，注意尾空格会影响搜索模式的结果。

 _    匹配单个字符

        SELECT columnOne,columnTwo FROM table WHERE columnOne LINK '_abc'

        //匹配aabc eabc  eabc  等前面一个字母的词

（2）LIKE操作符：该操作符可用来区分大小写，同时也可以进行模糊查找

（3）通配符使用的注意事项：不要过度的使用通配符，除非是必要的，否则通配符不要用在搜索模式的开始处，仔细注意通配符的位置。不要放错位置

25.数据处理函数：

（1）常用的文本处理函数：

函数名实现功能  

 Upper()将文本转换为大写

 Left()             返回串左边的字符

    Length()         返回串的长度

    Locate()         找出串的一个子串

    Lower()         将串转换为小写

Right()           返回右边的字符

Soundex()      返回串的SOUNDEX值       

SubString()     返回串的字符

 RTrim()，LTrim()，Trim()  依次是删除右边  左边  两边的空格

 例如：

SELECT vend_name, Upper(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;

 SELECT Concat(RTrim(vend_name), '( ' ,RTrim(vend_country), ') FROM vendors ORDER BY vend_name;     

（2）日期和时间的处理函数：

函数名实现功能  

AddDate()      增加一个日期  天、周等   

        AddTime()      增加一个时间  时、分等

        CurDate()      返回当前日期

        CurTime()      返回当前时间

        Date()         返回日期时间的日期部分

        DateDiff()     计算两个日期之差      

        Date_Add()     高度灵活的日期或时间串   

        Date_Format()  返回一个格式的日期或时间串   

        Day()          返回一个日期的天数部分

        DayOfWeek()    对于一个日期，返回对于星期几

        Hour()         返回一个时间的小时部分

        Minute()       返回一个时间的分钟部分

        Month()        返回一个日期的月份部分

        Now()          返回当前的日期和时间

        Second()       返回一个时间的秒部分

        Time()         返回一个日期时间的时间部分

        Year()         返回一个日期的年份部分

注意：MySQl日期格式必须为yyyy-mm-dd

（3）数值处理函数

函数名实现功能

  Abs()             返回一个数的绝对值

        Cos()             返回一个角度的余弦

        Exp()             返回一个数的指数值

        Mod()             求余

        Pi()              返回圆周率

        Rand()            返回一个随机数

        Sin()             返回一个角度的正弦值

        Sqrt()            返回一个数的平方根

        Tan()             返回一角度的正切

（4）汇总函数

函数名   实现功能

AVG()            返回某列的平均值

COUNT()          返回某列的行数

MAX()            返回某列的最大值

MIN()            返回某列的最小值

SUM()            返回某列值之和

注意：

（1）COUNT(*) 对表中的数目（所有列）进行计数，不管表列中包含的是NULL值还是非空值；COUNT(column) 对特定的列中具有值的进行计数，忽略NULL值

（2）DISTINCT  参数，作用是返回不相同的值；下面是返回供应商提供的产品的平均值，它与上面的SELECT 语句相同，但使用了DISTICT参数，因此平均值只考虑各个不同价格合起来的平均值

 SELECT AVG(DISTINCT prod_price)  AS avg_price FROM products WHERE vend_id=10003；

 如果指定了列名，DISTINCT参数只能用于COUNT(),DISTINCT参数不能用于COUNT(*)应为DISTINCT必须使用列名。

26.分组：GROUP BY

27.HAVING子句，在分组后进行过滤，HWERE在分组前进行过滤

例如:

如想要得到大于3的不同产品订单

  SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >３;

检索总计订单价格大于50的订单号和总计订单

    SELECT order_num , SUM(quantity* item_price)  AS ordertotal  FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >= 50;

28.SELECT语句使用顺序:

SELECT要返回的列或表达式->FROM从中检索数据的表->WHERE行级过滤->GROUNP BY分组说明->HAVING组级过滤->ORDER BY输出排序顺序->LIMIT要检索的行数

29.子查询：嵌套在其他查询中的查询：

（1）利用子查询进行过滤：

        SELECT cust_id FROM orders WHERE order_num IN

（

         SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'

        ）

（2）可以把一条SELECT语句返回的结果用于另一条SELECT语句的WHERE 子句

格式化sql，包含子查询的sql语句难以阅读，可以使用适当的缩进。

例如：得到了订购物品TNT2的所有客户ID，下一步是检索这些客户的信息：

SELECT cust_name , cust_contact  FROM  customers WHERE cust_id IN

(

     SELECT cust_id FROM orders WHERE order_num IN

 (

     SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'

     )

    )

    注意：子查询使用中，列必须匹配 在WHERE子句中使用子查询，应该保证SELECT语句具有WHERE 子句中相同数目的列。通常子查询将返回的单个列于单个列匹配，但如果需要也可以多个列。虽然子查询一般与IN操作符结婚使用，但也可以用于测试等于、不等于等符号

（3）作为计算字段使用子查询，使用子查询的另一个方法是创建计算字段。

例如：假设需要显示sustomers表中每个客户的订单总数。

        SELECT cust_name ，cust_state,

(

         SELECT COUNT(*) FROM orders WHERE orders,coust_id = customers . Cust_id

        ) AS orders FROM customers ORDER BY cust_name;

30.表连接：

（1）内连接：inner join ，能够找到两个表中相等字段值的记录

--查询学员信息显示班级名称

select Student.StudentNo,Student.StudentName,grade.classname

from Student

inner join grade on Student.ClassId=grade.ClassId

（2）左连接: 关键字join前面的表是左表，后面的表是右表，显示左表所有数据，如果建立关联的字段值在右表中不存在，那么右表的数据就以null值替换

select PhoneNum.*,PhoneType.*

from   PhoneNum

left join  PhoneType on PhoneNum.pTypeId=PhoneType.ptId

（2）右连接: 关键字join前面的表是左表，后面的表是右表,右连接可以得到右表所有数据，如果建立关联的字段值在右左表中不存在，那么左表的数据就以null值替换

select PhoneNum.*,PhoneType.*

from   PhoneNum

right join  PhoneType on PhoneNum.pTypeId=PhoneType.ptId

（3）full join :可以得到左右连接的综合结果--去重复

select PhoneNum.*,PhoneType.*

from   PhoneNum

full join  PhoneType on PhoneNum.pTypeId=PhoneType.ptId

31.事务：一种处理机制。以事务处理的操作，要么都能成功执行，要么都不执行。

（1）特点 ACID：

A：原子性：操作不可拆分，它的操作要么全都执行，要么全都不执行。是一个整体

C：一致性：事务在完成时，数据都保持一种某种程度的一致状态

I：隔离性：事务中隔离，每一个事务请求将被单独处理，与其它事务没有关系

D：持久性：如果事务一旦提交，就对数据的修改永久保留

（2）使用事务：将你需要操作的sql命令包含在事务中。在事务的开启和事务的提交之间

或者在事务的开启和事务的回滚之间

（3）三个关键语句：

开启事务：begin transaction

提交事务：commit transaction

回滚事务：rollback transaction

（4）注意：

    --事务一旦开启，就必须提交或者回滚

--事务如果有提交或者回滚，必须保证它已经开启

28.视图：

视图就是一张虚拟表，可以像使用子查询做为结果集一样使用视图。

（1）创建视图：create view vw_自定义名称

（2）删除视图：drop view vw_自定义名称

（3）注意：

a.视图中不能使用order by

b.对视图进行增加删除和修改操作--可以对视图进行增加删除和修改操作,只是建议不要这么做,如果操作针对单个表就可以成功，但是如果 多张的数据就会报错

c.不可更新，因为修改会影响多个基表

32.触发器：

33.存储过程：

34.完整性约束：