Mysql基础语句汇总

表结构与表数据的基础操作

• 新增一个学生表

//方式一
CREATE TABLE t_student (
	sno INT ( 10 ) PRIMARY KEY auto_increment,
	sname VARCHAR ( 20 ) NOT NULL,
	sex VARCHAR ( 1 ) DEFAULT '男',
	age INT ( 3 ),
	enterdate date,
	classname VARCHAR ( 10 ),
	email VARCHAR ( 15 ) UNIQUE 
);

//方式二
CREATE TABLE t_student (
	sno INT ( 10 ) auto_increment,
	sname VARCHAR ( 20 ) not null,
	sex VARCHAR ( 1 ) DEFAULT '男',
	age INT ( 3 ),
	enterdate date,
	classname VARCHAR ( 10 ),
	email VARCHAR ( 15 ),
	constraint pk_stu primary key(sno),
	constraint uq_stu_email unique(email)
);

• 查询学生表
  select * from t_student where 1=1;

• 向学生表插入数据
  INSERT INTO t_student VALUES(1,'张三','男',15,'2020-02-20','火箭一班','123@qq.com');

• 修改学生表数据(不带条件)
  UPDATE t_student SET sex = '女' ;

• 删除学生表数据(不带条件 )
  delete from t_student;
truncate table t_student;
  delete是一条一条删除,truncate是重新创建这个表,因此后者效率更高,delete操作支持回滚,而truncate是隐式提交,不能回滚,delete操作后再次新增记录时,自增字段会继续从删除前的最大值加1,truncate则从1开始;

-----------------------------------------------------------------------------------------

• 在创建表之后添加主键约束
  alter table t_student add constraint pk_stu primary key (sno);

• 给学生表添加一个字段(放在最前面)
  alter table t_student add score double(5,2) first;

• 删除一个学生表字段
  alter table t_student drop score;

• 修改字段名和类型
  modify 是修改字段的类型和定义;
  alter table t_student modify score float(4,1);
  change 可以修改字段名,类型和定义;
  alter table t_student change score fenshu double(5,3);

• 删除整表
  drop table t_student;

查看表结构

desc 表名;

查看建表语句

show create table 表名;

添加外键约束

//创建一个班级表
CREATE TABLE t_class(
	cno int(10) primary key auto_increment,--班级id
	cname VARCHAR(10) not null --班级名称
);

//创建学生表并添加班级表外键约束
CREATE TABLE t_student (
	sno INT ( 10 ) primary key auto_increment, --学生id
	sname VARCHAR ( 20 ) not null, --学生名称
	classno INT ( 10 ) --班级id,跟班级表cno字段对应,类型和长度最好一致
);

• 添加外键约束
  alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno);

• 外键策略

  1. no action 不允许操作(默认)

  2. cascade 级联操作,操作主表时候影响从表的外键信息;
     alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update cascade on delete cascade;

  3. set null 置空操作
     alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update set null on delete set null;

快速复制表

• 复制表结构和数据;
  create table t_copy as select * from t_student;
• 复制表结构;
  create table t_copy as select * from t_student where 1=2;

常用函数

多行函数

• max() 最大值;
• min() 最小值;
• count() 总条数;
• sum() 值的总和;
• avg() 平均值;

单行函数

字符函数

• lower() 转换成小写;
• upper() 转换成大写;
• length() 获取传入字符的长度;
• substring() 字符串截取,下标从1开始;

数值函数

• abs() 取绝对值;
• ceil() 向上取整;
• floor() 向下取整;
• round() 四舍五入;
• mod() 取余;

日期与时间函数

• curdate() 当前年月日;
• curtime() 当前时分秒;
• now() 当前时年月日时分秒;
• sysdate() 该函数执行时的年月日时分秒;

流程函数

• if(condition,t,f) 如果条件condition为真,返回t,否则返回f;
• ifnull(value1,value2) 如果value1不为null,则返回value1,否则返回value2;
• nullif(value1,value2) 如果value1等于value2,则返回null,否则返回value1;
• case 字段名
  when ‘teacher’ then ‘教师’
  when ‘student’ then ‘学生’
  else ‘其他’
  end;
• case 字段名
  when money<=100 then ‘小康’
  when money<=500 then ‘富裕’
  else ‘贫穷’
  end;

其他函数

• database() 返回当前数据库;
• user() 返回当前用户;
• version() 返回当前数据库版本;

单表执行顺序

select - from - where - group by - having - order by

多表连接查询类型

• cross join 交叉连接,匹配出所有可能的结果,笛卡尔乘积,没有实际意义,cross在mysql中可以不写;

• natural join 自然连接,自动匹配所有的同名列,同名列只展示一次,但是查询字段时如果没有指定字段所属的数据库表,就会效率低;

• inner join using() 内连接using子句,缺点:必须要两个表的列同名;

• inner join on() 内连接on子句,用的最多,缺点:其中某表没有的数据,另一表对应数据也不展示;

• left join on 左外连接,左边的表数据即使不匹配也展示;

• right join on 右外连接,右边的表数据即使不匹配也展示;

• full outer join on 全外连接,仅在orcale数据库有用,即使左右表不匹配的数据也展示;

• union 并集,左外链接+union+右外连接,达到了全外连接的效果;