MySQL_03_bj

MySQL-Day02回顾
1、表记录的管理
1、删除：delete from 表名 where 条件;
## 不加where条件全部删除
2、更新 ：update 表名 set 字段名=值,… where 条件;
## 不加where条件全部更新
2、运算符
1、数值比较&字符比较
数值比较：> >= < <= != =
字符比较：= !=
2、逻辑比较
and or
3、范围内比较
字段名 between 值1 and 值2
字段名 in(值1,值2,…)
字段名 not in(值1,值2,…)
4、匹配空、非空
is null
is not null
null:空值,用 is 或者 is not 去匹配
“” :空字符串,用 = 或者 != 去匹配
5、模糊比较
字段名 like 表达式
_ : 匹配单个字符
% : 匹配0到多个字符
3、SQL查询
1、order by 字段名 ASC/DESC
2、limit(分页)
1、limit n
2、limit m,n ## 从 m+1 条记录开始 显示 n 条记录
3、聚合函数
avg(字段名) max(字段名) min(字段名) sum(字段名)
count(字段名) ## 空值NULL不会被统计
4、group by(给查询结果分组)
先分组 - 再聚合 - 再去重
如果select后的字段名和group by之后的字段不匹配,则必须对select后的字段名做聚合处理(聚合函数)
5、having
1、having与group by语句联合使用,过滤由group by语句返回的记录集
2、弥补了where关键字不能与聚合函数联合使用的不足
3、where只能操作表中实际存在的字段,having可操作聚合函数生成显示列
6、distinct(不显示字段的重复值)
select distinct 字段名1,字段名2,… from 表名;
7、查询时做数学运算
+ - * / %
8、执行顺序
3、select …聚合函数 from 表名
1、where …
2、group by …
4、having …
5、order by …
6、limit …
4、约束
1、默认约束(default)
2、非空约束(not null)
字段名 数据类型 not null default 值
MySQL-Day03笔记
1、索引(index)
1、定义
对数据库中表的一列或者多列的值进行排序的一种结
构(MySQL中索引用BTREE方式)
2、优点
可以加快数据的检索速度
3、缺点
1、当对表中的数据进行增加、删除和修改的时候,索
引需要动态维护,降低了数据的维护速度
2、索引需要占用物理空间
4、索引示例
1、开启运行时间检测
show variables like “%pro%”;
set profiling=1;
2、执行查询语句
select name from t1 where name=“lucy1009999”;
3、查看执行时间
show profiles;
4、在name字段创建索引
create index index_name on t1(name);
5、执行查询语句
select name from t1 where name=“lucy1009998”;
6、查看执行时间
show profiles;
5、索引类型
1、普通索引(index)
1、使用规则
1、一张表中可以有多个index字段
2、字段的值可以重复,且可以为NULL
3、经常把做查询条件的字段设置为index字段
4、index字段的key标志为 MUL
2、创建index
1、创建表时创建
… index(字段名),index(字段名));

	    mysql> create table t4(
	        -> id int,
	        -> name char(20),
	        -> age tinyint unsigned,
		-> index(id),
		-> index(name)
	        -> );
	    mysql> desc t4;

	2、在已有表中创建index
	    1、语法
	       create index 索引名 on 表名(字段名);
	    2、注意
	       索引名一般和字段名一样
	       mysql> create index age on t3(age);
	       mysql> show tables;
	       mysql> create index age on t4(age);
	       mysql> desc t4;
    3、查看索引
	1、desc 表名; -->查看key标志
	2、show index from 表名;
	       mysql> show index from t4;
	       mysql> show index from t4\G;
    4、删除index
	drop index 索引名 on 表名;
	## 删除index只能一个一个删
	       mysql> drop index id on t4;
	       mysql> desc t4;
2、唯一索引(unique)
    1、使用规则
	1、一个表中可以有多个unique字段
	2、对应unique字段的值不允许重复,但可以为NULL
	3、unique的key标志是：UNI
    2、创建唯一索引unique
	1、创建表时创建
	    1、... unique(字段名1),unique(字段名2));
	       ... unique(phnumber),unique(cardnumber));
	    2、... 字段名 数据类型 unique);
	    mysql> create table t5(
	        -> id int,
	        -> name varchar(15),
	        -> number char(11) unique
		-> );
	    mysql> desc t5;
	2、在已有表中创建
	    create unique index 索引名 on 表名(字段名);
	    mysql> create unique index id on t5(id);
	    mysql> desc t5;
	3、查看/删除唯一索引(同index)
	    drop index 索引名 on 表名;
	    mysql> drop index from t5;
	    mysql> show index from t5;
	    mysql> show index from t5\G;
	    mysql> desc t5;
	    mysql> drop index id on t5;
	    mysql> desc t5;
	    注意：index，unique在删除时只能一个一个删除
3、主键索引(primary key)
   &&
   自增长属性(auto_increment)配合主键一起使用
    1、使用规则
	1、一个表中只能有一个主键(primary)字段
	2、对应字段值不允许重复,且不能为空(NULL)
	3、主键字段的key标志是：PRI
	4、把表中能够唯一标识一条记录的字段设置为主键,
	   通常把记录编号的字段设置为主键
    2、创建主键(primary key)
	1、创建表时创建
		... id int primary key auto_increment,
		... ...)[auto_increment=10000];
		... id int auto_increment,
		... primary key(id))[auto_increment=1000];
	    1、字段名 数据类型 primary key,
            mysql> create table t1( 
                -> id int primary key,
                -> name varchar(20) not null,
		-> sex enum("boy","girl") default "boy"
	        -> ); 
            mysql> desc t1;
	    mysql> insert into t1 values(1,"张三丰","boy");
	    mysql> insert into t1 values(1,"张无忌","boy");
	           对应字段值不允许重复,且不能为空(NULL)
	    2、primary key(字段名),
            mysql> create table t2( 
                -> id int,
                -> name char(20),
		-> likes set("boy","girl","study"),
		-> primary key(id)   #(id,name)复合主键
	        -> ); 
            mysql> desc t2;

	2、在已有表中创建
	    alter table 表名 add primary key(字段名);
	3、删除主键及自增长属性
	    1、先删除自增长属性(auto_increment)
		alter table 表名 modify 字段名 数据类型 primary key;
	    2、再删除主键
		alter table 表名 drop primary key;
                mysql> alter table t2 drop primary key;		            
	        mysql> desc t2;

                mysql> alter table t2 add primary key(id);
	        mysql> desc t2;
	4、在已有表中添加自增长属性(auto_increment)并指定起始值
	    1、作用：通常和主键字段一起配合使用
	    2、创建
	       1、创建表时创建自增长属性
	          字段名 数据类型 key auto_increment,
                   mysql> create table t3( 
                       -> id int primary key auto_increment,
                       -> name char(15),
		       -> age tinyint unsigned
	               -> ); 
                   mysql> desc t3;
		   mysql> insert into t3 values(0,"赵敏",30);
		   mysql> select * from t3;
		   mysql> insert into t3 values(0,"小昭",30);
		   mysql> insert into t3 values(0,"周芷若",25);
		   mysql> select * from t3;
		   mysql> delete from t3 where id=3;
		   mysql> select * from t3;
		   mysql> insert into t3 values(0,"张无忌",28);
	       2、在已有表中添加自增长属性
		  alter table 表名 modify 字段名 数据类型 auto_increment;
	    3、指定起始值
		alter table 表名 auto_increment=值;
4、外键索引(foreign key)
    1、定义
       让当前表的字段值在另一个表的范围内选择
    2、语法格式
	foreign key(参考字段名)
	references 被参考表名(被参考字段名)
	on delete 级联动作
	on update 级联动作
    3、示例
	表1：缴费信息表(财务)
		学号	姓名	班级	金额
		1	唐伯虎	二班	200
		2	点秋香	二班	300
		3	祝枝山	二班	500

	表2：班级信息表(班主任)
		学号	姓名	金额
		1	唐伯虎	200
		2	点秋香	300
	表1：
		create table jftab(
		id int primary key,
		name varchar(20),
		class varchar(7),
		money int)character set utf8;
		insert into jftab values
		(1,"唐伯虎","AID04",200),
		(2,"点秋香","AID04",300),
		(3,"祝枝山","AID04",500);
	表2：
		create table bjtab(
		stu_id int,
		name varchar(20),
		money int,
		foreign key(stu_id) references jftab(id) on delete cascade on update cascade
		)character set utf8;
		insert into bjtab values
		(1,"唐伯虎",200),
		(2,"点秋香",300);
    4、级联动作
	1、cascade ：数据级联更新
	   1、当主表删除记录时，如果从表有相关联记录则级联删除
	   2、当主表删除记录或更改被参考字段值时,从表级联更新
	2、restrict(检查外键限制，默认)
	   1、当主表删除记录时，如果从表有相关联记录则不允许主表删除
	   2、当主表删除记录或更改被参考字段值时,从表中有相关联记录则不允许主表操作
	   3、update同1	              
	      mysql> alter table bjtab
                  -> foreign key(stu_id) references jftab(id)
                  -> ;
	      mysql> select * from jftab;
	      mysql> select * from bftab;		          
	3、set null
	   1、当主表删除记录或更改被参考字段值时,从表中相关联记录的字段值设置为 NULL
	   2、update同1
	      mysql> alter table bjtab
                  -> add foreign key(stu_id) references jftab(id)
                  -> on delete set null
		  -> on update set null
		  -> ;
	      mysql> select * from jftab;
	      mysql> select * from bftab;
	4、no action
	   同 restrict,都是立即检查外键限制
    5、删除外键限制
       1、语法格式
	  alter table 表名 drop foreign key 外键名;
           2、注意
	  外键名查看方式：show create table 表名;
    6、在已有表中添加外键限制
       1、语法格式
	  alter table 表名 add
	  foreign key(参考字段名) references 被参考表
	  名(被参考字段名) 
	  on delete 级联动作
	  on update 级联动作
           2、注意
          在已有表中添加外键时，会受到表中原有数据的限制
    7、使用规则
	1、两张表参考字段和被参考字段数据类型要一致
	2、被参考字段必须为key的一种,通常primary key

2、数据导入
1、作用 ：把文件系统的内容导入到数据库中
2、语法格式
load data infile “文件名”
into table 表名
fields terminated by “分隔符”
lines terminated by “\n”

   示例
   cookie
   查看所有的用户信息
           cat /etc/passwd | head
   查看某个用户信息
           cat /etc/passwd | grep "tarena"
1、把 /ect/passwd 文件中的内容导入到 db3下的userinfo表
	tarena:x:1000:1000:tarena,,,:/home/tarena:/bin/bash
	用户名:密码:UID:GID:用户描述:主目录:登录权限
3、把 /ect/passwd 导入到MySQL数据库中				
4、操作步骤
1、在数据库中创建对应的表
   tarena:x:1000:1000:tarena,,,:/home/tarena:/bin/bash

   mysql> create table userinfo(
       -> username varchar(50),
       -> password char(1),
       -> uid int,
       -> gid int,
       -> comment varchar(50),
       -> homedir varchar(50),
       -> shell varchar(50)
       -> )character set utf8;
2、查看数据库默认搜索路径
       mysql> show variables like "secure_file_priv";
3、将系统文件拷贝到数据库的默认搜索路径中
       新建终端
       tarena@tedu:~$ sudo cp /etc/passwd /var/lib/mysql-files/
                          sudo -i
		      cd /var/lib/mysql-files/
		      ls
		      成功显示passwd文件
		      exit
4、执行数据导入语句
	mysql> load data infile "/var/lib/mysql-files/passwd"
	    -> into table userinfo
	    -> fields terminated by ":"
	    -> lines terminated by "\n";

	mysql> select * from userinfo;

【【【创建成功删除重新创建
    mysql> delete from userinfo;
    mysql> select * from userinfo;
    mysql> desc userinfo;
不是7个字段需要添加
    mysql> alter tables userinfo add 字段名 数据类型 after...;
	
将系统文件拷贝到数据库的默认搜索路径中
    新建终端
    tarena@tedu:~$ sudo cp /etc/passwd /var/lib/mysql-files/
                       sudo -i
		   cd /var/lib/mysql-files/
		   ls
		   成功显示passwd文件 
		   exit

    mysql> desc userinfo;
执行数据导入语句
        mysql> load data infile "/var/lib/mysql-files/passwd"
        -> into table userinfo
	-> fields terminated by ":"
	-> lines terminated by "\n";

        mysql> select * from userinfo;】】】

5、练习
1、将AID1709.csv文件导入到 aid1709 表中

1、创建表
	create table aid1709(
	id int,
	name varchar(20),
	score float(5,2),
	phone bigint,
	class char(7)
	)character set utf8;
2、拷贝文件
	$ : sudo cp ~/AID1709.csv /var/lib/mysql-files/
3、执行数据导入语句
	load data infile "/var/lib/mysql-files/AID1709.csv"
	into table aid1709
	fields terminated by ","
	lines terminated by "\n";

3、数据导出
1、作用
将数据库中表记录保存到系统文件里
2、语法格式
select … from 表名
into outfile “文件名”
fields terminated “分隔符”
lines terminated by “\n”
3、示例
1、把userinfo表中的用户名、密码和uid号三个字段的值给
导出到userinfo.txt中
mysql> select username,password,uid from userinfo
-> into outfile “/var/lib/mysql-files/userinfo.txt”
-> fields terminated by " "
-> lines terminated by “\n”
-> ;
tarena@tedu:~$ sudo -i
cd /var/lib/mysql-files/
ls
cat userinfo.txt

    2、把 mysql库下的user表中 User、Host的值导出来,user2.txt
   mysql> select User,Host from mysql.user
           -> into outfile "/var/lib/mysql-files/user2.txt"
           -> fields terminated by "  "
           -> lines terminated by "\n"
       -> ;
  
  tarena@tedu:~$ sudo -i
		 cd /var/lib/mysql-files/
		 ls
		 cat user2.txt
		 exit
4、注意
1、导出的内容由SQL查询语句决定
2、执行导出命令时路径必须指定对应的数据库目录下
   ### chmod 777 文件名
   ### chmod +rw 文件名
3、不确定数据库默认搜索路径是否更改，查看默认搜索路径
       show variables like "secure_file_priv";
   或
       模糊查询
       show variables like "%secure%";

5、表的复制
表的重命名：alter table 表名 rename 新表名;
1、表的复制
1、语法格式
create table 表名 select … from 表名 …;
mysql> show tables;
mysql> select * from userinfo;
mysql> create table userinfo2
-> select * from userinfo;
mysql> show tables;
mysql> select * from userinfo2;
2、练习
1、复制userinfo表的前10行，userinfo3
mysql> create table userinfo3
-> select * from userinfo limit 10;
2、复制userinfo表的用户名、密码、uid三个字段
2-10条记录，userinfo4
mysql> create table userinfo4
-> select username,password,uid from userinfo
-> limit 1,9
-> ;
2、示例
1、复制 aid1709 表的全部记录和字段,aid17092
create table aid17092 select * from aid1709;
2、复制 aid1709 表中成绩低于65分的学生到表next
create table next select * from aid1709 where socre<65;
3、只复制表结构
create table 表名 select * from 表名 where false;
4、注意
复制表的时候不会把原有表的 键 属性复制过来
6、嵌套查询(子查询)
1、定义 ：把内层的查询结果作为外层查询的条件
2、语法
select查询语句 where 条件(select查询语句);
3、示例
1、把uid的值小于这个平均值的用户名和uid号显示出来
mysql> select username,uid from userinfo
-> where
-> uid < (select avg(uid) from userinfo);
2、查找userinfo表中用户名在mysql库下的user表Host值
为localhost 并且User值是root的用户名
mysql> select username from userinfo
-> where username in
-> (select User from mysql.user where Host=“localhost”
and User=“root”);

3、找出每个国家攻击力最高的英雄的名字和攻击值
    mysql> select name,gongji from sanguo
	-> where gongji in(
	-> select max(gongji) from sanguo group by country);