mysql学习笔记心得_id int(6)unsigned zerofill primary key

mysql 安装：官网下载mysql安装包，解压后，在当前bin目录下打开cmd 执行mysqld --isntall

mysql -uroot  windows登录本机mysql （windows下默认没有密码)
mysql --install 安装
net start mysql 启动服务

mysql -h -u -p    登录mysql
常用函数：
select version();'
select database();
select current_user();---当前用户
select curdate();
select curtime();
show tables;  显示所有表
show databases  显示所用库

use test  使用数据库
show tables   显示test下面的所有的表
show  engines 显是支持的引擎 innodb支持事务 myisam不支持事务 优点：速度快

show charset  显示字符集

数据类型：
数字
tinyint  samllint mediumint int bigit  decimal(p,s)小数 s代表表示几位小数
字符串动态长度  后面的长度是指支持的最大长度
varchar(20)
日期
date time  datetime
二进制

Mysql难以优化引用可空列查询，它会使索引、索引统计和值更加复杂。可空列需要更多的存储空间，还需要mysql内部进行特殊处理。可空列被索引后，每条记录都需要一个额外的字节，还能导致MYisam 中固定大小的索引变成可变大小的索引
建表：--这里int(6)中数字配合zerofill使用，不然没有意义
create table dd(
    id int(6) unsigned zerofill -- 宽度不够补零 not null--非空约束不能为空，插入的时候不填报错  auto_increment
    sname varchar(20)
    sgender enum('男'，'女','保密') --枚举
    primary_key(id)  --指定id为主键
)engines = myisam auto_increment =5 default charset = utf8;

drop table dd；  删除表

--判断数据库aa是不是存在，如果不存在，就建立此数据库，并且设定新数据库的字符编码为uft8
create database if not exists aa default character set uft8
--更改数据db的默认编码集为gbk
alter database db default charset gbk

create table 'aaa' select from tablename   根据表名建表  
rename table t1 to student   修改表名

desc student   查看表结构
alter table student change tid sno int   修改列名

增加一列
alter table  studtn  add sscore  varchar(20);
alter table  studtn  add sscore  varchar(20) after snme  在某一列后面增加
 删除列：
 alter table student drop column sscore  
 
 -------------------------------------------------------数据的insert-update -delete-------------------------------------------------------
 insert into tableName values(,,,,),values(,,,,),values(,,,,)..........插入多列
 插入的时候替换 主键存在的情况下，替换原先对应已有的内容
 replace into student (sname,sage) values('张三',18),('李四',22)
 修改数据
 update student set sscore =85 ,age = 22 where sanme='李四'
 删除数据
 delete form student where name = '李四'
 无条件删除所有记录
 delete from student 
 truncate table student --直接清空数据表记录，auto_increment 自动从1开始  效率高于delete
 
 数据的简单查询
 只显示一列 或多lie
 select sanme sscroe from student
 select *from student
 成绩小于80的学生信息
 select *form student where sscore<80;
 --查询显示出成绩不是80的学生信息
 select *fronm student where sscore != 80
 select *fronm student where sscore <> 80
 select *fronm student where not sscore = 80
 --查询条件between and 范围   范围之间
 select *fronm student where  sscore between  80  and 90
 select *fronm student where  sscore not between  80  and 90
 
 查询条件like 条件%零个或者多个  _代表1个任意字符 这两个符号必须配合like使用才有前面所说的意义
 查询姓名是两个字的学生信息
 select *from student where sname like '__';
 select *from student where sanme like '张_'
 基本条件 确定范围 IN(，，，，)这里in里面有的
 select *from student where sno in(201401,202051,202041)  查询学号在这三个中的学生信息
 
 基本条件null 查询
 select *from student where sscoree is null  成绩为空 或者is not null 不能是 =null  这里是NUll是没填 不是0
 
--------------------------------------------------------集合函数与分组查询---------------------------------------------------------------------------
avg()平均数   cout()统计个数  sum()求和  max()最大值 where后面不能直接跟集合函数 采用子查询的方式使用集合函数
select avg(score) from student  显示平均分  
查询最高分的学生信息
select *form student where sscore in(select max(score) from stduet)

--统计人数
select count(*) form student
--统计不及格的人数
select count(*) form student where sscore<60

求总分 平均年龄 并起别名
select sum(sscore) 总成绩，avg(age) 平均年龄 from student

分组在查询中 group by
统计各个城市的学生人数-------注意 分组查询中 查询条件只能出现 分组字段 和集合函数
selelct sadresss 地区 ，count(*) 人数 from student group by sadresss

selelct adresss 地区 ，count(*) 人数 from student group by saadress order by count(*) desc  按人数排序
selelct adresss 地区 ，count(*) 人数 from student where adress is not null group by saadress order by count(*) desc  按人数升降排序 地区没填的不统计
selelct adresss 地区 ，count(*) 人数 from student where adress is not null group by saadress HANVING count(*)>2 order by count(*)desc  分组条件地区人数大于2的

 ------------------------------------------------------select 高级查询 两个表  一个学生表 一个老师表  ------------------------------
 学生表里有老师的编号
 查询学生的信息以及老师的名字  ---多表查询 这种方式无法查出不存在老师的学生
 select s.sid s.name,t.tname  from s ,t  where s.tid  = t.tid 
 --学生不存在老师的情况呢 用下面的方式查询
 --连接查询 join  left  join  on / rignt join on
 select s.sid,s.name,t.tname from s left join t on s.tid = t.tid;
 老师为Null 查出来结果是没有安排老师，不为null的查出来是什么就是什么
 select s.sid,s.name,t.tname，if(t.name is null,'没有安排老师',t.name) from s left join t on s.tid = t.tid;
 
一个员工表 员工表里有领导的id (领导也在这个表里) 采用自连接查询你呢方式  
查询出员工的信息和员工领导的名字
select j1.jid,j1.name,j1.gender, j2.name from job j1 ,job j2 where j1.jid = j2.jid ;

----------------------------------------------------------------表约束-------------------------------------------------------------------------------------------
1.主键约束  primary_key（字段名） 不能为空，不能重复 主键自带索引，关系数据库 一个表只有一个主键 可以是复核主键--主键代表的就是唯一 可以primary_key(tid,tname)复核主键 ：id重了 姓名不能重 姓名重了 id不能重
  主键的作用：指的是一个列或多列的组合，其值能唯一地标识表中的每一行，主要确定该数据的唯一性。
create table t1(
 tid int unsigned not null auto_increment,
 tname varchar(30),--30变长
 tdate datatime default now(),  --默认值 可使用函数
 tage tinyint ,
 --primary_key(tname,tid) --复核主键
 constranint mypk primary_key(tid,tname)--给主键起别名  oracle用的比较多
 check(tage>18)  --检测年龄是否大于18  数据库忽略该约束没有用
)engine = myisam auto_increment =1 default charset = utf8;

 

2.默认约束  default '男'  default 18  default now()
3.非空约束    NOT NULL 
4.外健约束  FOREIGN KEY REFERENCE  外健约束 ：关联几个表  要使用外健的表必须是innodb存储引擎
外键的使用：foreign key references
两个表 学生表 老师表  引擎innodb
create table teacher(
    tid int unsigned not null auto_increment,
    tname varchar(20) not null,
    key(tname) --建立表的同时 建立索引
    primary_key(tid)
)engine = innodb auto_increment = 1 default charset = uft8;

create table student(
    sid int unsigned not null auto_increment,
    sname varchar(20) not null,
    tid int unsigned,
    primary_key(sid),
    constraint fk foregin key(tid) references techaer(tid) --创建外键 关联学生表的tid和老师表的tid  关联到老师表的主键字段
    constraint fk foregin key(tid) references techaer(tid) on delete cascade --创建外键 关联学生表的tid和老师表的tid (可以再删除记录的时候，同时把相关联的记录删除 学生表和老师表对应的记录都删除，比如张三的老师是李老师，在删除李老师这条记录的时候，学生表的张三的记录也会被删除，但是也不可以先删除教师表)
    constraint fk foregin key(tid) references techaer(tid) on delete set null --不像上面一样 区别就是 删除老师信息的时候 学生表对应关联记录的老师会设为空 而不会删除学生表的记录
)engine = innodb auto_increment = 1 default charset = uft8;
A、现在直接 drop tabe teacher  不让删除，因为还有学生表关联着老师表  可以先删除学生表在删除老师表
 

5 check约束 检测插入的数据是否符合件 但数据库忽略该约束
6 unique约束 唯一约束 和主键的区别是 主键不能为空，unique可以有一个为空

--增加主键 在表已建好且没设置主键的情况下：
alter table tt add constraint  primary key(sno);

--------------------------------------------------------------------常用函数----------------------------------------------------------------------------------------
select length（’abc‘）；长度字节数
select char_length('avc是')  返回字符个数4
返回名字为两个字的学生信息
select*from student where char_length(sname)=2;
select*from student where sname like '__';
limit 限制结果的行数 只显示两行记录
select *from student limit 2;
随机显示两行信息
select rand()  0-1随机小数
select *from student order by rand() limit 2;  效率很低
时间函数 date_add(now(),interval -10 day)10天前的日期 、date_add(now(),interval 10 day)十天后日期、date_add(now(),interval 10 week) 十周后日期
查询10天前注册的会员的人数
select count(*) from member where regdate between  data_add(now(),interval -10 day) and now();
查询两个日期之间相差的天数
select datediff(curdate(),'1981-1-1');
格式化日期函数
select date_format(now(),'%Y年%M月%d日')
slelect weekyear（now（））;现在是今年的第几周
select from_unixtime(137655100)将数字表达日期 格式化成标准 yyyy-mm-dd h:i:s

面试容易问的问题：

1、区分主键/外键  ：总结  外键约束是多个表的关联关系 删除表的时候存在先后关系，删除记录的时候后取决于外键约束后面的条件可以设空，可以级联删除，一个表的外键，一般指向另外一个表的主键或唯一约束健，并且类型一致。而主键是指单个表内的唯一性，设置为主键的列不能存在重复，主键可以是复核主键。
2、索引 ：索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息 和书籍的检索目录功能相识 ，主键和唯一约束都自带索引 优点：检索速度特别快  缺点 降低了数据插入，修改速度，索引占用一部分硬盘空间
   --建立索引 create index 别名 on tableName （字段 + 排序）
   create INDEX iia on teacher (tnanme desc); 为teacher表字段tname建立索引 别名为 iia
   --删除索引 
   drop index iia on teacher
   自带索引  unque  primary key 
   create table aaa(
    abkook varchar(20) unique,--建立唯一约束 自动建立索引
    name varchar(20),
    address varchar(50),
    key(name desc),    --索引
    key(adress asc),
    sno int unsigned not null 
    primary key(sno)  主键 也是索引
   );
   
3、事务：是指作为单个逻辑工作单元执行的一系列操作（对数据库的相关增删改查的操作），要么完全地执行，要么完全地不执行。
四大特性：
 3.1、原子性：一个事务中的所有操作，要不操作全部成功，要不全部失败，不能存在中间态。
 3.2、一致性：事务必须使得数据库从一个一致性状态转变到另一个一致性状态。比如银行转账，A账户转到B账户，不管转几次，A和B账户的总额不能变。
 3.3、隔离性：是指多个用户同时请求数据库，开启多个事务同时处理某个数据库，隔离性保证了各个事务之间均不受干扰，每个事务都感觉不到其他事务的存在。
 3.4、持久性：对数据库的修改是持久性的，一旦修改，就算数据库系统出现故障，这种修改也不会丢失，这点是数据库数据存放到硬盘中，并有redo log 和 binlog 一起保证的

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理 insert,update,delete 语句
一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

 

BEGIN 或 START TRANSACTION 显式地开启一个事务；

COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；

ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；

RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

ROLLBACK TO identifier 把事务回滚到标记点；

SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

 

mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb;  # 创建数据表
Query OK, 0 rows affected (0.04 sec)
 
mysql> select * from runoob_transaction_test;
Empty set (0.01 sec)
 
mysql> begin;  # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into runoob_transaction_test value(5);
Query OK, 1 rows affected (0.01 sec)
 
mysql> insert into runoob_transaction_test value(6);
Query OK, 1 rows affected (0.00 sec)
 
mysql> commit; # 提交事务
Query OK, 0 rows affected (0.01 sec)
 
mysql>  select * from runoob_transaction_test;
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)
 
mysql> begin;    # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql>  insert into runoob_transaction_test values(7);
Query OK, 1 rows affected (0.00 sec)
 
mysql> rollback;   # 回滚
Query OK, 0 rows affected (0.00 sec)
 
mysql>   select * from runoob_transaction_test;   # 因为回滚所以数据没有插入
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)

事务回滚对已提交的修改无法生效。 

 

4、存储过程 ：概念：存储过程是在大型数据库系统中，一组为了完成特定功能的sql语句集。经过编译后存储在数据库中，用户通过指定存储过程的名字并给出参数来执行它
   存储过程是编译后再服务器执行，速度快 类似于函数 函数有返回值
   存储过程就是具有名字的一段代码，用来完成一个特定的功能。---具体了解 -看书签
   
   delimiter  --定义申明存储过程
   create procedure My(out cc int)--out 出参
   begin
        declare l_int int unsigned default 4000000;  --申明局部变量
        select count(*) into cc from aaa;
   end
   delimiter
   --调用储存过程
   set @nn = 1
   call My(@nn)这里赋不进去，是出参 
   call My(@nn);  -调用触发查询结果
   select @nn  --这样可以看到出参的结果
   变量定义：
   DECLARE l_int int unsigned default 4000000;
   建立储存过程
   delimiter //
   create procedure 过程名（in out参数名 参数类型） 
   bgein 
        存储过程要执行的语句；
   end
   delimiter;
 --查看当前db数据的所有储存过程
 show procedure status where db = ’db‘；
 储存过程中使用的语句
    if for while case when then
    

5、函数
相比储存过程有返回值 
成绩登记判断将查询的结果放入 select  sscore,sname,dj(sscore) from student 
delimiter
create functional dj(score tinyint)
returns varchar(30)  --先确定返回类型
begin
declare lev varchar(30);
case
when lev>90  then  set lev = '优秀';
when lev>=70 then  set lev = '良好';
when kev>60  then  set lev = '及格';
else
    set lev = '补考';
end case
return lev
end
delimiter;

6、触发器
自动执行的存储过程 在执行相关操作时候，自动触发  删除的时候产生old 插入的时候产生new 修改产生old和new

触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。

举个例子，比如你现在有两个表【用户表】和【日志表】，当一个用户被创建的时候，就需要在日志表中插入创建的log日志，如果在不使用触发器的情况下，你需要编写程序语言逻辑才能实现，但是如果你定义了一个触发器，触发器的作用就是当你在用户表中插入一条数据的之后帮你在日志表中插入一条日志信息。当然触发器并不是只能进行插入操作，还能执行修改，删除。

CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt
trigger_name：触发器的名称
tirgger_time：触发时机，为BEFORE或者AFTER
trigger_event：触发事件，为INSERT、DELETE或者UPDATE
tb_name：表示建立触发器的表明，就是在哪张表上建立触发器
trigger_stmt：触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句
所以可以说MySQL创建以下六种触发器：
BEFORE INSERT,BEFORE DELETE,BEFORE UPDATE
AFTER INSERT,AFTER DELETE,AFTER UPDATE

列子：两个表 一个学生信息表sss，一个学生补考表sssbk，在插入学生信息表的同时，不及格的同学信息也会自动进入学生补考表
--建立触发器
插入触发器
delimiter
create trigger tt1 --别名 after insert on sss for each row  --在插入sss的时候对每一行都执行下面的操作
declare ss tinyint;
set ss = new.sscore;   --借助new 新成绩 这里的new代表刚插入的信息
if ss<60 then
   insert into sssbk values(new.id,new.sname，new.sscore,new.sadress,new.stel);
end if
delimiter;

现在在删除学生信息的时候，补考表中的学生信息也应该删除 
建立删除触发器
delimiter
create trigger tt2 --别名 after delete on sss for each row  --在插入sss的时候对每一行都执行下面的操作
delete from sssbak where sid  = OLD.sid  --借助OLD 这里old代表删除前的信息
delimiter;

现在学生成绩补考成绩更新后成绩合格 ，补考成绩表的记录应该删除
建立更新触发器
delimiter
create trigger tt3 after update on sss for each row
begin
declare newss = new.sscore;--新成绩 更新后的成绩
declare oldss = old.sscore;--旧成绩 更新前的成绩
if newss>=60 then
   delete from sssbk where sid = new.sid
else
    relpace into sssbk values(new.sid,new.sscore,new.sname,new.saadress);
 end if
end
delimiter;

--删除触发器
drop trigger tt1;

 

示例2：

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
  `add_time` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`(250)) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1;

CREATE TABLE `logs` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `log` varchar(255) DEFAULT NULL COMMENT '日志说明',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='日志表';

DELIMITER $
CREATE TRIGGER user_log AFTER INSERT ON users FOR EACH ROW
BEGIN
DECLARE s1 VARCHAR(40)character set utf8;
DECLARE s2 VARCHAR(20) character set utf8;#后面发现中文字符编码出现乱码，这里设置字符集
SET s2 = " is created";
SET s1 = CONCAT(NEW.name,s2);     #函数CONCAT可以将字符串连接
INSERT INTO logs(log) values(s1);
END $
DELIMITER ;

 

7、事件 ：事件称事件调度器 主要是定期或指定时间执行一条命令  类似于定时器

--检查你的额mysql是否开启事件功能，一般是关闭的
show variables like 'event_scheduler'
--开始事件
set global event_scheduler =1
--关闭事件
set global event_scheduler = off;

--创建事件
create event myevent1 on schedule every 1 second --每1秒
do  insert into eee(t2) values(10);  --做什么事

--禁用某一个事件
alter event myevent1 disable;

8、视图 ： 对sql命令操作封装，外部通过授权给视图对象，而不是整个表的权限来控制访问者查看的信息。简化复杂的查询语句，提高数据的安全性

9 、数据库管理(权限管理账户管理)：

--查看当前服务器上的所有账号及密码 主机  -账号是localhost的代表本机 外部电脑有账号和密码也不能使用
select user,password,host from mysql.user;
--设置当前账号密码
在当前用户下执行  set password = password('aaa'); 修改当前用户的密码
set password = password('');设置空 取消密码
--设置某个账号的密码 前提是登录账号又要修改其他账号的权限
select user() 查看当前登录账号的名称及主机名
set password for root@localhost = password('aa'); 修改root的账户密码
set password for root@localhost = password(''); 取消root的账户密码
--创建账号 建立一个和root账号权限一样的超级管理员密码111，任意主机都可以使用此账号登录
grant all on *.* 'admin'@'%' identified by '111';  --%的意思任意一台主机可登录 账号admin 密码111。 现在登录方式 mysql -h192.168.1.2 -uadmin -p111;
--建立没有相关权限的账号
create user aa 建立aa账号 没有密码 相当于 aa@% 默认任意主机可登录
create user bb indentified by 'bb' 建立bb账号，密码bb 相当于 bb@% 默认任意主机可登录
--授权数据库hnstdb给user账户， user可对hnstdb做任何操作 没有建其他库 删除其他库的权限
grant all on hnstdb.* to 'user'@'%' identified by user;
--删除账号
drop user admin
flush privileges  立即刷新权限

权限管理(二)
--指定只有ip为192.168.1.5可以登录 但没有相关权限
create user 'yy'@'192.168.1.5' indentified by '123';
--授管理员权 登录root账户执行下面语句
grant all on *.* to 'yy'@'192.168.1.5';
--收回所有权限
 revoke all privileges on *.* from 'yy'@'192.168.1.5';
--给用户test库学生表的查询权限
grant select on test.student to 'yy'@'192.168.1.5';
--收回给用户test库学生表的查询权限
 revoke select  on test.student from 'yy'@'192.168.1.5';
--视图 涉及敏感信息查看  给视图授权 这样不用给表的权限，访问者只能看到 学生表里的名字和账号，看不到其他信息。
create view vv as select name account from students
grant select on test.vv to  'yy'@'192.168.1.5';

selecet from vv

--删除视图
deop veiw vv

修改用户名  修改 'yy'@%用户为 'admin'@%
rename user yy to admin 

10、数据库导出 导入 备份
--导出db数据库 -q 代表快速导出，-d只导出表结构 -t只备份表数据
mysqldump -uroot -p111  -q -d -t db>c:/db.sql;
--导入db数据库 前提是这个库存在 可以create database db
mysql -uroot -p111 -q db<c:/db.sql;
--只导出数据库中的stu表数据 表备份
mysqldump -uroot -p111  -q  db stu >c:/db.sql;
--从txt中导入数据到表中
load data infile 'c"/d.txt' into table stu fields terminated by '\,' lines terminated by'\r\n'
--将表数据导入到txt文件中
select sname,sage into outfile 'c:/dd.txt' fields terminated by'\,' lines terminated by '\r\n' from stu where id<4