mysq基础笔记整理

运算符
= --------------------表示等于
<> --------------------表示不等于
between … and … —显示在某一区间的值（含头尾）
like --------------------模糊查询，_ 代表一个字符 ，%代表任意多个字符
in
and , or ,not

登录数据库
启动: net start mysql;
停止: net stop mysql;

 登录
    方式一：mysql -u用户名 -p密码
	方式二：mysql --host=数据库服务器地址 --prot=端口号 -u用户名 -p密码
	       比如：  mysql --host=localhost --prot=3306 -uroot -p123456                
	方式三：mysql -h服务器地址 -p端口号 -p密码	
           比如：mysql -h127.0.0.1 -p3306 -uroot -p123456

 退出数据库  quit/exit	

source命令备份与还原
备份格式： mysqldump -u用户名 -p密码 数据库 > 文件的路路径
还原格式： source 导入文件的路径;

\\\
DDL(Data Define Language)
创建数据和库数据表，删除数据库和数据表 create/drop
修改数据库和表（影响结构） alter

   创建数据库
      方式一: create database 数据库名;
	  方式二: create database if not exists 数据库名;   --防止报错
	  方式三: create database 数据库名 character set gbk;   --使用指定字节码创建数据库
	  
   创建表 
        create table 表名（
		     字段名1  数据类型,
			 字段名2  数据类型,
			 ...
			 字段名n  数据类型

         );			
	  
   快速创建一个表结构相同的表
     create table 新表名 like 旧表名;
	   
	  
	  
   修改数据库
        *修改数据库的默认字节码
          alter database 数据库名 default character set 字节码;   --gbk/utf8

        *删除数据库
         drop database 数据库名;
		 
		
   修改表结构
        *直接删除表drop
		  drop table 表名;
		*判断表是否存在，存在就删除
          drop table if exists 表名;

        *添加列ADD·	
           alter table 表名 add 列名 数据类型;
		   alter table 表名 add 列名 数据类型 after 列名;   --------添加某列到指定列的后面
		   alter table 表名 add 列名 数据类型 comment 备注;  -------添加列并且添加备注

        *修改列表的数据类型modify
           alter table 表名 modify 列名 新的数据类型;

        *修改列名和数据类型chang
           alter table 表名 chang 列名 新列名 数据类型;

        *删除表中的列drop
           alter table 表名 drop 列名;

        *修改表名 rename
		   rename table 表名 to新表名
		   
		*修改表的字符集编码
		   alter table 表名 default character set utf8;		   
		   
		   
数据库约束
     
	主键约束    primary kay
    唯一约束    unique
    非空约束    not null
    默认约束    default 默认值
    外键约束    constraint foreign key(外键列) references  主表名(主键);
	自增长      auto_increment

    删除主键 ：alter table 表名 drop primary key;
	
级联操作
    在修改和删除主表的主键时，同时更更新或删除副表的外键值，称为级联操作
    on update cascade  级联更新
    on delete cascade  级联删除		
	
零填充：字段名 int(n) zerofill;	

\\\
DML(Data Manipulation Language)
对表进行进行增删改 insert/update/delete

    插入数据insert
	     方式一：insert into 表名 （字段名1,字段名2,......） values(值1,值2);
		 方式二: insert into 表名 values(值1,值2,......);
		 
	更新表的记录update
         方式一：update 表名 set 字段名 = 值;   --修改这一列的所有数据
         方式二: update 表名 set 字段名1=值1,字段名2=值2,...where 条件;  --修改满足条件的记录

	删除记录delete
          删除表中所有记录
			  方式一:delete from 表名;
			  方式二:truncate 表名;
				区别:delete是一条条数据删除，删除效率慢，不改变原来的自增长。
					 truncate是直接摧毁表，然后新建一张一样的新表,会重置表的自增长为1开始;
				 
		  删除满足条件的记录
            delete from 表名 where 条件;
			
	数据类型转换cast()或convert()
		相同点：都是进行数据类型转换，实现的功能基本等同
		不同点：两者的语法不同，cast(value as type) 、 convert(value,type)		
			
	字符串拼接CONCAT(x,y)
		CONCAT函数会将所有参数按照参数的顺序拼接成一个字符串做为返回值。
		例：UPDATE `aicube`.`sys_class` SET class_alias_name = CONCAT(gradeId,classNum)
		
	有关联关系的批量更新:
		UPDATE sp_user_sub_course_tbl usc 
		SET sub_course_id = aa.sub_course_id 
		FROM
			(
			SELECT
				usc.user_sub_course_id,
				sc.sub_course_id 
			FROM
				sp_user_sub_course_tbl usc
				LEFT JOIN sp_sub_course_tbl sc ON usc.sub_course_sequences = sc.sub_course_sequences 
				AND sc.course_id = usc.course_id 
			WHERE
				usc.user_id = 549329 
				AND usc.course_id = 3009 
			) aa 
		WHERE
			aa.user_sub_course_id = usc.user_sub_course_id		

\\\\
DQL(Data Query Language)
查询数据 show/select

   查看数据库
      show databases;      --------------查看所用数据库
	  show create database 数据库名;  --查看数据库的定义信息 
	
   查看表		
	  use 数据库名;
	  show tables;  --查看数据库中所有的表
	  desc 表名;    --查看表结构
	  
	  select 字段名1,... from 表名;         ---------------------查看指定列信息
      select * from 表名;                   ---------------------查看所有列信息
	  select 字段名1 as 别名,字段名2 as 别名,... from 表名;    --对列指定别名查询，as可以省略
	  select distinct 字段名 from 表名;     ---------------------指定列查询不重复的结果（distinct）
	  select 列名1+固定值 from 表名;        ---------------------查询某列每一个值+固定值的结果。结果列可以指定别名
	  select 列名1+列名2 from 表名;         ---------------------查询列1和列2相加后的结果。结果列可以指定别名
	  select * from 表名 where 列名 is not null;    -------------查询某列不为空的所有信息
	  select * from 表名 where 列名 between  值1 and 值2     ----查询某列在值1和值2之间的所有信息
	  select * from 表名 where 列名 in(值1,值2);             ----查询某列有值1，值2的信息
	     
	  
	  
  约束====
	  select 列名 from 表名 where 条件 order by 列名 asc/desc;      ----------------------------排序order by
	  升序:asc 可以省略默认按升序
	  降序:desc 
	  select * from 表名 order by convert(列名 using gbk) asc       ----------------------------按拼音排序convert
	  
	  查询所有数据大于20岁的学生,在年龄降序排序的基础上，如果年龄相同再以数学成绩升序排序
	  select * from student3 where age > 20 order by age desc, math asc ;        ----------------组合排序
	  
	  查询所有数据,按性别分组。
	  select * from student3 group by sex;      -------------------------------------------------分组group by
	  
	  limit限制查询记录的条数
      select * from student3 limit 2, 6;	    --------------------查询学生表中数据，从第3条开始显示，显示6条。
	  
五个聚合函数
      count(列名)   统计这一列共有多少个
      sum(列名)     求这一列的总和，通常只对数值类型操作
	  avg(列名)     求这一列的平均值(average)
	  max(列名)     求这一列的最大值，字符串也有最大和最小值。
	  min(列名)     求这一列的最小值
	  
	  语法：        select 聚合函数(列名)from 表名where 条件

where 和 having的区别
      where和having语句都是用来添加过滤条件
	  where语句是对行数据进行过滤，having对组数据进行过滤
	  where语句后面不能使用聚合函数，having后面可以使用聚合函数	
	  
多表查询
    select * from 左表,右边 where 从表.外键=主表.主键;      ---------------------------隐式内连接
    select * from 左表 inner join 右表 on 条件;           -----------------------------显示内连接
	select * from 左表 left join 右表 on 条件;           ------------------------------左外连接
	select * from 左表 right join 右表 on 条件;           -----------------------------右外连接
	
	子查询
	   select * from 表名 where 列名 in (子查询结果，多个值);          ----------------只查询结果是多行单列
	   select * from 左表, (子查询结果 右表) where 从表.外键=主表.主键    -------------子查询结果是多行多列

\\\\
函数
concat()拼接函数

replace( str, from_str, to_str)替换函数
		str        需要进行替换的字符串
		from_str   需要被替换的字符串
		to_str     需要替换的字符串	
	   
union 数据合并查询
	要求：两张表有相同列数（少的列可以null代替），列别名一样
    select 
	  'student' 表名,
	  t.`sid` 学号,
	  t.`sName` 姓名,
	  t.`sAge` 年龄,
	  t.`sSex` 性别 
	from
	  student t 
	union
	select 
	  'sc' 表名,
	  sc.`sid` 学号,
	  sc.`cid` 姓名,
	  sc.`score` 年龄,
	  null 性别 
	from
	  sc 		

case  判断查询
     select 
	  (
		case
		  when 语文 >= 80 
		  then '优秀' 
		  when 语文 >= 60 
		  then '及格' 
		  else '不及格' 
		end
	  ) as 语文,
	  ...from table 	
	
if 判断	查询
     IF( 表达式 , true , false )
     select *,if(book_name='java','已卖完','有货') as product_status from book where price =50
 
对11位的数字进行脱敏查询（电话）
	CASE
	WHEN (
		CHAR_LENGTH (apv.login_id) = 11
	) THEN
		REPLACE (
			apv.login_id,
			SUBSTR(apv.login_id, 4, 4),
			'****'
		)
	ELSE
		apv.login_id
	END AS login_id,
	
to_char('2019-12-07'::date,'IYYY_IW')   ---------获取一年中第几周

to_char(now(),'hh24:mi:ss') < '23:00:00'  ---------是否在当天23点之前

to_char(now() - INTERVAL '1 MONTH','IYYY-MM')   ----------获取上个月日期

to_char(now() - INTERVAL '1 day', 'yyyy-MM-dd')   ----------获取当前天的前一天

round()  ----------- 第一个参数是取值的数据，第二个参数是精度，第三个参数是数据取值模式（四舍五入还是截断），其中第三个参数是可选参数，默认是四舍五入模式。

sum()  -------------函数返回数值列的总数

EXTRACT(epoch from now()-w.create_timestamp)>60*60*18 -------提取秒数（大于18小时）

w.create_timestamp + INTERVAL '14 hour' <= now( )  -----------时间计算（14小时内）
Minutes

COUNT(DISTINCT CASE WHEN 条件 THEN 去重字段 END) xxx GROUP BY 分组字段   --------count去重
COUNT(DISTINCT 去重字段 ) xxx GROUP BY 分组字段      --------count去重

\\\\
DCL(Data Control Language)
创建用户，删除用户，分配权限等 grant/revoke
创建用户：
create user ‘用户名’@‘机主名’ identified by ‘密码’; --------通配符%,表示任意主机名
例如： create ‘user2’@‘localhost’ identified by ‘123’;

	给用户授权:
	   grant 权限 on 数据库名.表名 to '用户名'@'机主名';
	   
	撤销授权:
       revoke 权限 on 数据库名.表名 from '用户名'@'主机名';

    删除用户:
        drop user '用户名'@'主机名';

    修改管理员密码:
        mysqladmin -uroot -p password 新密码;
        输入老密码			

\\\\\
事务
事务特性:原子性,一致性,隔离性,持久性
start transaction 开启事务
commit 提交事务
rollback 回滚事务

select @@autocommit;  查看是否自动提交事务，1=自动提交，0=手动提交
set @@autocommit = 0;

savepoint 名字      设置回滚点
rollback to 名字    回到回滚点

四种隔离级别
serializable(串行化): 可以解决所有并发访问问题
repeatable read(可重复读): 只有幻读的问题
read committed(读已提交): 只能解决脏读问题
read uncommitted(读未提交): 所有并发访问的问题都会出现

\\\\
数据库优化之创建索引

 创建普通索引：CREATE INDEX username ON mytable(username);
               create index 列 on 表（列）

 创建唯一索引：CREATE UNIQUE INDEX age ON mytable(age);
               create unique index 列 on 表（列）
 
 创建主键索引：ALTER TABLE mytable ADD PRIMARY KEY (id);
               alter table 表 add primary key (列)

 组合索引：ALTER TABLE mytable ADD INDEX name_city_age (username,city,age);
           alter table 表 add index 新列名(列,列,列)	 
 
 删除索引：DROP INDEX 索引的名字 ON 索引的表;
           drop index 索引列名 on 索引的表