MySQL语句笔记

MySQL语句笔记

数据库操作

	show 库名;  打开数据库
	use 库名；   打开库
	create database 库名 character
	create database 库名 character set utf8；
	create database 库名 character set utf8 collate utf8_general_ci;创建数据库
	show create database 库名; 查看数据库使用的字符集
	alter database 库名 character set (gbk);修改数据库编码格式
	drop database 库名；删除库
	show engines;  mysql支持的存储引擎
	show variables like '%engine%' ;   查看默认存储引擎
	set default_storage_engine=innodb; 修改默认存储引擎为innodb

导入与导出

	mysqldump -uroot -p    库名>表名.sql；  导出
	mysql -uroot -p 库名<表名.sql；          导入

数据表操作

	--创建数据表

	create table if not exists(如果不存在就创建) +表名(
		字段名 char(10) primary key--(主键) auto_increment--(自增),
		stuname char(20),
		stuage tinyint,
		stusex char(2)
	)
	
	--查看数据表
	desc + 表名;     
	或
	show create table + 表名;
	
	
	--修改数据表
	1、删除列
	alter table 表名 drop column 字段名
	
	2、添加新字段
	alter table 表名 add 字段名 类型
	
	3、添加新字段到指定位置 指定字段后面
	alter table 表名 add 字段名 char(20) after 字段名
	
	4、添加新的字段到第一列
	alter table 表名 add 字段名 数据类型 first
	
	5、修改字段的数据类型
	alter table 表名 modify 字段名 数据类型
	
	6、修改字段名
	alter table 表名 change 原字段名 新字段名 字段类型
	
	7、修改数据表名
	alter table 表名 rename to 新表名
	
	--删除数据表
	drop table 表名

数据表约束

--主键、唯一键、非空、外键约束
	
	主键 primary key
		1、主键列数据值不能重复
		2、一个表只能有一个主键
		3、主键列不允许为空
		
	自增列 auto_increment
	组合主键
	
	添加主键 stuid+subjectname 组合主键
	altler table 表名 add constraint pk1 primary key(字段1，字段2)
	
	非空 not null
	default 默认为……  
		例 varchar(20)    <--字符型-- not null default "687621354"
			int              <--整型-- not null default 6   不加引号
			枚举类型enum("男","女")          --只能输入男或女
	添加非空
    alert table 表名 change 字段 字段 类型 not null;

	唯一键 unique
		1、唯一列不能有重复值
		2、一个表可以有多个唯一键
		3、唯一可以是空
	
	外键 foreign key
		参照另一张表，保证插入数据的有效性，数据完整性
		保证在子表中插入有效的数据（在主表中存在的数据），
			1、不能在从表中插入主表中没有的主键字段的值
			2、不能在主表中删除从表中引用的数据
		      一定想删除（先删除从表中的引用，再删除主表中的）
			外键字段在主表中必须是唯一键或者主键
			
		--例：
		主表 stu2 stuid
		从表or子表 score stuid
		添加外键 从表score的字段stuid 主表名stu2(stuid)
		alter table score add constraint 外键名 foreign key(stuid) 
		references stu2(stuid)
		
		解除外键约束
		alter table 子表名 drop foreign key 外键名;

表数据操作（增删改查）

	表中插入数据
		1、自增列可以不插入，也可以插入一个null
		2、自增列也可以手动插入，保证不是重复值
		
		insert into 表名(字段1，字段2，……) values(数据1，数据2，……)
		insert into 表名 values(数据1，2，3，……)
		省略字段列表
			1、有几个字段就要传递几个值
			2、传递值的次序和表定义字段的次序要一致
			3、字段列表怎么写顺序就怎么写
		多条插入
		insert into 表名(字段1，字段2，……) values
		(数据1，数据2，……),
		(数据1，数据2，……),
		(数据1，数据2，……),
		(数据1，数据2，……);
		字符型(char、varchar)、日期型（time、datetime）必须带引号
		最好使用单引号
	
	
	删除表数据
		有外键约束不能被删除
		delete from 表名 [where 条件]
	
	
	修改表数据
		update 表名 set 字段名='修改值'[,字段2='修改值']  [where 条件]   
			如果为空(null)  where 字段 is null
			加学分……
			update 表名 set 字段=字段+1 where 条件
	
	数据表查询
		select 字段列表/* from 表名
			where 语句	<>不等于
			order by 字段	--排序
				desc 降序 | asc (默认)	
				
		select * from 字段 order by 字段(主)，字段2,…… desc;
		
	
		group by 字段	--组合
			分组前筛选用where  分组后用having
	
		在显示结果中给字段起别名 as写不写都可以
			select 字段名 [as] 别名,字段 别名,…… from 表名 where 字段="";  
			
		查询同字段多内容  用in代替or
			select * from 表名 where 字段 in ('数据1','花果山','天宫');
			
		查询什么什么之间用 between …… and……  日期型也可以用
			select * from 表名 where 字段 between 0 and 10;
			
		模糊查询%任意多位   _任意一位 
			--例 (姓孙的)
			select * from 表名 where 字段 like '孙%';
			
			--(手机号1开头，第二位是3或者5)正则表达式
			select * from 表名 where 字段 regexp '^1[35]';
			
			--(手机号1开头，第二位不是3或者5)
			select * from 表名 where 字段 regexp '^1[^35]'
		
		distinct --去掉重复关键字
			select distinct 字段 from 表名;
		
		limit    --限制取出多少条记录
			select * from 表名 limit 0(从第几个开始取),10(取多少个)    页数（页数-1）*8
	
		聚合函数
			avg()、min()、max()、sum()、count(计算的是字段不为空值的条数)
	
	
	//	连接查询
		*内连接
			两表查询
				1、select * from 表名 a [inner] join 第二个表 b on a.相同字段=b.相同字段;
				2、select * from 第一个表 a,第二个表 b where a.相同字段=b.相同字段;
			三表查询
				select * from 第一个表 a,第二个表 b,第二个表 c where a.相同字段=b.相同字段 and a.字段=c.字段;
	
		*外连接
			select * from 表 a left / right outer join 第二个表 b on a.相同字段=b.stuid
				--查询没有参见过考试的学生信息
				select a.* from stuinfo a left outer join scoreinfo b on a.stuid=b.stuid
				where id is null
		
		*交叉连接
			select * from 表 a cross join score 表2 b;
	
		子查询
			select * from 表名 where 字段 运算符(select 字段 from 表 where 条件)
			--如果子查询返回单个值，单列，可以使用= <> > < 来导入子查询
			--如果返回多个值，多列，使用in 来导入子查询