MySQL学习笔记（一）：MySQL基础入门

1、Sql语言概述

　　a) ddl语句（数据定义语言）：create，alter，drop

　　b) dml语句（数据操作语言）：update，insert，delete，select

　　c) dcl语句（数据控制语言）：是数据库控制功能。用来设置或更改数据库用户或角色权限的语句，包括（grant，deny，revoke等）语句

2、连接和登录数据库

　　a) Mysql -h host -P3306 -uuser -ppassword

　　b) -h：当连接MySQL服务器不在同台主机时，填写主机名或IP地址，默认是localhost

　　c) -P:访问数据库的端口，默认是3306

　　d) -u：登录MySQL的用户名

　　e) -p：登录MySQL的密码

3、存储引擎

　　a) Show engines：可以查看MySQL服务实例支持的存储引擎

　　b) InnoDB（行级锁）：支持外键（foreign key），支持事务（transaction），在修改数据的时候只修改一条数据，在操作其他数据的时候还可以正常操作

　　c) MyISAM（表锁）：不支持事务、外键，指定表的存储引擎eg：create table

4、常见的唯一性约束

　　a) Primary key：主键约束

　　b) Unique：唯一性约束

　　c) Not null：非空值约束

　　d) Auto_increment：用于整列默认递增

　　e) Default default_value：默认值约束

5、查看表结构

　　a) Desc 表名：查看表结构 

　　

　　b) Show create table 表名：查看建表的语句

6、DDL语句

　　a) Alter table 表名：修改表名 eg：alter table hyn_test rename hyn

　　b) 修改字段名

　　　　1.alter table hyn modify age varchar(5);  #把hyn表中的age字段的数据类型改成varchar类型

　　　　2.alter table hyn change age ag varchar(5);  #同时修改类型和字段名字

　　　　3.选中某个表，右键设计表也可以修改表中的字段

　　c) 增加字段：alter table 表名add 属性名 数据类型[完整性约束条件] [first | after 属性名2]

　　d) 删除字段：alter table 表名 drop age;

　　e) 删除表

　　　　1.truncate table 表名;  #删除之后无法恢复，自增长id从1开始

　　　　2.delete from 表名;  #删除之后可以回滚，自增长id从上一个id之后开始增加

　　　　3.drop 表名;  #删除之后无法回滚

　　f) 增加外键：alter table 表名 add constraint FK_ID foreign key（外键字段名） references 外表表名（对应的表的主键字段名）;

　　g) 删除标的外键约束：alter table 表名 drop foreign key 外键别名;

7、DML语句

　　a) 添加数据

　　　　1.insert into 表名（要插入的表的字段） values();  #指定字段

　　　　2.insert into 表名 values();  #必须要填写完所有的列名

　　　　3.set autocommit=0;  #关闭自动提交

　　　　4.commit  #开启自动提交

　　b) 修改表数据

　　　　1.update 表名 set stu_name='张山' where stu_id=1;  #修改id为1的用户名

　　　　2.update 表名 set stu_name='张山',cn='JP' where stu_id=1;  #修改多个字段

　　　　3.update 表名 set stu_name='张山',cn='JP' limit 5,10;  #修改第5到10行的数据，（基本上没有多大意义，只需要了解怎么用即可）

　　c) 查询语句

　　　　1.select * from 表名 where age is null or age='';  #查询空数据

　　　　2.select * from 表名 where stu_name like '张%';  #查询以张开头的用户

　　　　3.select * from 表名 where stu_name like '张_';   #匹配任意一个字符

　　d) 使用聚合函数查询

　　　　1.avg，sum，max，min，count;   eg：select avg(sarlary) from 表名;   #单独使用聚合函数

　　　　2.group by； eg：select count(*),sex from 表名 group by sex;   #聚合函数和分组函数一起使用

　　　　3.分组之后有条件的话不能直接加where，用having，eg：select stu_id from score group by stu_id having count(*) < 2

　　e) 备份表

　　　　1.create table 表名 like 表名（数据库中已有的表）;  #创建和数据库中已存在的表相同表结构的表

　　　　2.create table 表名_bak as select * from 表名;  #先创建一个表，再把这个表的数据全部加入到新建的表中

　　f) 查询和修改

　　　　1.left join：查询出左表的所有成绩，右表中没有成绩的数据不会查询出来

　　　　2.right join：查询出右表的所有成绩，左表中没有成绩的不会查询出来

　　　　3.inner join：查询连个表的所有数据

　　　　4.update 表1，表2 set 字段1=，字段2= where 表1.id=表2.id and...    #多表修改

　　g) 去重

　　　　1.select distinct 字段名 from 表名;  #去掉重复的字段名

　　　　2.union，有重复的直接去重，条件比较多，不知道怎么关联的时候用， eg：select 字段1，字段2 from 表名 where 条件 union select 字段1，字段2 from 表名 条件;

　　　　3.union all，有重复的数据不会去重

　　h) 子查询

　　　　1.select * from 表名 where id=(select id from 表名 where 条件);

　　　　2.update 表名 set 字段名=‘’ where id=(select ........);

　　　　3.把子查询的结果当成一个表来操作，如下图

　　　　

8、DCL语句

　　a) Grant all on *.* to ‘root’@’localhost’identified by ‘123456’with grant option; #增加一个超级用户

　　b) Grant select,insert,update on bugfree.* to ‘test’@’%’ identified by ‘123456’ #增加一个普通用户

　　c) Revoke 权限 on 数据库对象 from 用户  #取消权限

　　d) 即允许本机连接又运行远程连接需要加localhost和%

　　

9、数据库备份

　　a) Mysqldump，eg：mysqldump -uroot -p123456 数据库名 [表名]> 数据库名.sql;

　　

　　b) 备份整个数据库，eg：mysqldump -uroot -p123456 -A > all.sql

　　c) 恢复数据库备份，eg：mysql -uroot -p123456 数据库名 < xxx.sql

10、写一个存储过程，实现向某个表中自动添加500条数据

　　#默认情况下，MySQL一遇到“;”就会自动执行，在这样的情况下就需要事先把delimiter后面的符号换成“$$”或“//”；加$$的目的就是为了让整个语句写完之后在执行　　

　　delimiter $$;   
　　create procedure test_student(count int)  
　　　　begin   
　　　　declare name varchar(20);  #声明变量
　　　　declare sex varchar(10);
　　　　declare addr varchar(50);
　　　　declare class varchar(20);
　　　　declare i int;
　　　　set i = 0;   #设置变量的值
　　　　set sex= 'F';
　　　　set addr='beijingchaoyang';
　　　　set class='wusuopu';
　　　　while i<count do    #开始循环
　　　　set name=CONCAT('difeng',i);  #连接字符串函数
　　　　insert into students (name,sex,class,addr) values (name,sex,class,addr);   #插入数据操作
　　　　set i=i+1;
　　　　end while;    #结束循环
　　　　end
　　$$;
　　delimiter;

　　call test_student(500);   #调用存储过程
　　select count(*) from students;  #查询students表的总数据