Alter:
更改表名
alter table 表名 rename 新表名;
alter table student rename student_info;
更改字段名,类型
alter table 表名 change 列名 新列名 数据类型;
alter table student_info change student_name student_name int;
添加字段
alter table 表名add 列名类型;
alter table student_info add student_age int;
删除字段
alter table 表名 drop 列名;
更改字段类型(尽量不要更改)
alter table 表名 modify 列名 新数据类型;
修改数据库字符集
alter database 数据库名 character set utf8
alter database _111_ character set utf8 注意:需要重启mysql服务,才能生效
修改表字符集
Alter table 表名 character set utf8 collate utf8_general_ci
约束
主键
第一种 : 创建表语句时,添加主键约束 create table person( id int , name varchar(100), income decimal(18,2), primary key (id,name) ); 上面代码设置了两个主键 create table person1( id int , name varchar(100), income decimal(18,2), primary key (id) ); 上面代码设置了一个主键 如果只有一列主键,也可以直接写在字段后面 create table person2( id int primary key, name varchar(100) , income decimal(18,2) ); 第二种 : 创建表完成之后,通过alter添加主键约束 语法 : alter table 表名 add primary key(列名,列名...); create table person3( id int , name varchar(100), income decimal(18,2) ); 比如要对person3表添加id列主键 alter table person3 add primary key(id);
主键自增
第一种 : 建表时,添加自增 create table person4( id int auto_increment , name varchar(200), primary key(id) ); 测试语句 : insert into person4(name)values('测试'); 并未输入id的值,但是可以自动填充 第二种 : 创建表之后,添加自增 语法 : alter table 表名modify 主键列名 类型 auto_increment; create table person5( id int , name varchar(200), primary key(id) ); alter table person5 modify id int auto_increment; 测试语句 : insert into person5 (name)values('测试'); 并未输入id的值,但是可以自动填充 设置自增的起始值 语法 : alter table 表名auto_increment=值; create table person6( id int auto_increment , name varchar(200), primary key(id) ); alter table person6 auto_increment=10000; 测试语句 : insert into person6 (name)values('测试'); Id值从10000开始
外键
第一种 : 创建表时添加外键约束 create table teacher( id int , name varchar(20), primary key (id) ); create table student ( id int , name varchar(20), teacher_id int , primary key (id), foreign key (teacher_id) references teacher(id) ); 注意 : 引用student中添加外键列,指向teacher表,所以必须先创建teacher表才行 测试语句 添加一个讲师 insert into teacher (id,name) values(1,'张老师'); 添加一个学生小明,学生通过teacher_id可以指向张老师 insert into student (id,name,teacher_id) values(1,'小明',1); 添加一个学生小红,teacher_id没有设置值 insert into student (id,name) values(2,'小红'); 添加一个小黑,teacher_id指向一个不存在的讲师,报错 insert into student (id,name,teacher_id) values(3,'小黑',2); 第二种 : 创建完表之后,添加外键约束 create table student1 ( id int , name varchar(20), teacher_id int, primary key (id) ); create table teacher1( id int , name varchar(20), primary key (id) ); 语法 : alter table 表名 add foreign key (外键列列名) references 指向的表名 (主键列列名); alter table student1 add foreign key (teacher_id) references teacher1 (id); 测试语句 添加一个讲师 insert into teacher1 (id,name) values(1,'张老师'); 添加一个学生小明,学生通过teacher_id可以指向张老师 insert into student1 (id,name,teacher_id) values(1,'小明',1); 添加一个学生小红,teacher_id没有设置值 insert into student1 (id,name) values(2,'小红'); 添加一个小黑,teacher_id指向一个不存在的讲师,报错 insert into student1 (id,name,teacher_id) values(3,'小黑',2);
unique
唯一约束是指定table的列或列组合不能重复,保证数据的唯一性。
唯一约束不允许出现重复的值,但是可以为多个null
第一种 : 创建表时,添加unique约束 create table temp ( id int , `name` varchar(20), unique(id) ); 或 create table temp ( id int unique , `name` varchar(20) ); 添加一条没有id的数据 insert into temp (name)values('张三'); 再添加一条没有id的数据,可以添加(唯一约束,又不是不为空约束) insert into temp (name)values('李四'); 添加一条id为1 的数据 insert into temp (id,name)values(1,'王五'); 再添加一条id为1的数据,报错,因为已经有了id为1了,不可重复 insert into temp (id,name)values(1,'赵六'); 第二种 : 创建表之后,添加unique约束 create table temp1 ( id int , `name` varchar(20) ); alter table temp1 add unique (id); 添加一条没有id的数据 insert into temp1 (name)values('张三'); 再添加一条没有id的数据,可以添加(唯一约束,又不是不为空约束) insert into temp1 (name)values('李四'); 添加一条id为1 的数据 insert into temp1 (id,name)values(1,'王五'); 再添加一条id为1的数据,报错,因为已经有了id为1了,不可重复 insert into temp1 (id,name)values(1,'赵六');
非空约束 not null与 默认值 default
第一种 : 创建表时,添加约束 create table temp2( id int not null, `name` varchar(30) default 'abc', sex varchar(10) not null default '男' ); 第二种 : 创建表之后,添加约束 语法 : alter table 表名 modify 列名 数据类型 not null default 默认值; create table temp3( id int, `name` varchar(30) , sex varchar(10) ); alter table temp3 modify id int not null ; alter table temp3 modify name varchar(30) default 'abc'; alter table temp3 modify sex varchar(10) not null default '男';
条件判断
如 : 查询学生表中,成绩大于90分的 select * from student where score > 90; 如 : 判断为空查询学生中,成绩为空的学生不能使用 = null ,应该使用 is null select * from student where score is null; 如 : 查询学生中,成绩不为空的学生 select * from student where score>90应该使用 is not null select * from student where score is not null;
between and
select 列限定 from 表限定 where 列名 between 值1 and 值2;
In
select 列限定 from 表限定 where 列名 in(值1,值2....);
模糊查询like
% 匹配任意个数的任意字符
_ 匹配单个任意字符
语法 :
select 列限定 from 表限定 where 列名 like '值' ;
如果想要查询 _ 或者 % 需要转义 \% \_
Order by 排序
语法 :
select 列限定 from 表限定 order by 列名 asc/desc;
Asc : 升序
Desc : 降序
Limit
限制条数,通常和order by一起使用,因为我们使用排序之后,再去获取前几条数据,比较有价值,比如成绩前三名
语法 :
select 列限定 from 表限定 limit 条数;
select 列限定 from 表限定 limit 开始值(不包含) ,条数;
count(*) : 总条数
max(字段名) : 最大值
min(字段名) : 最小值
avg(字段名) : 平均值
sum(字段名) : 总和
Group by
如 : 查询每个老师分别带了多少学生(显示老师id即可) select teacher_id, count(*) as stu_count from student group by teacher_id; 如 : 查询每个老师带的学生中的最高分数 select teacher_id, count(*) as stu_count,max(score) as stu_max_score from student group by teacher_id; 如 : 查询每个老师所带学生的总成绩与平均分 select teacher_id, sum(score) as sum,avg(score) as avg from student group by teacher_id;
Having
select teacher_id, avg(score) as avg from student group by teacher_id having avg > 60;
子查询
一.select后面 语法 : select 字段名,(查询语句) from 表名; 如 : 查询所有学生的信息并显示老师的名字 select *,( select name from teacher where id=teacher_id ) as teacher_name from student ; 如 : 查询每个老师的学生的 最大分数,最小分数,平均分数,分数总和,学生人数,老师名字 select max(score),min(score),sum(score),avg(score),count(*),( select name from teacher where id=teacher_id ) as teacher_name from student group by teacher_id ; 二. from后面 select *, case rank when 'A' then '优' when 'B' then '良' when 'C' then '差' end rank_ch from ( select *, case when score < 60 then 'C' when score >=60 and score <80 then 'B' when score >=80 then 'A' end as rank from student ) a; 三.where后面 如 : 在不知道teacher_id 和 老师名字的对应关系的情况下, 想查询出张老师下面的所有学生信息 select * from student where teacher_id in ( select id from teacher where name='张老师' );
Union与 union all
合并查询,合并查询的结果
Union 会去除重复项
Union all 不会去除重复项
常用函数
select version() ;显示当前MySQL软件的版本
select database();显示当前所处数据库是哪个
select char_length('中国');返回字符个数。
select length('中国');返回字符所占字节数,MySQL中,一个UTF8编码的汉字占3个字节
select concat( 'a', 'b', 'c', 'd');返回 'abcd'。字符串拼接函数
select concat_ws( '=', 'a', 'b', 'c');返回 'a=b=c'。字符串拼接函数,第一个是拼接间隔符
select upper('abcd');返回ABCD。将参数中所有小写字母转换为大写
select lower('ABCD');返回abcd。将参数中所有大写字母转换为小写
select substring( '系统信息类', 1, 3 );返回 系统信。第2个参数代表从1开始的第几个字符,第3个参数代表截取字符个数
select trim(' abc ');返回 abc。用于删去参数左右的所有空格
select curdate();返回当前日期
select curtime();返回当前时间
select now();返回当前日期时间
select unix_timestamp();返回当前日期时间对应的时间戳(单位秒)
select unix_timestamp('2018-05-24 20:00:00');返回参数指定的日期时间对应的时间戳(单位秒)
select from_unixtime(1527163397);返回参数指定时间戳(单位秒)对应的日期时间
select datediff( '2018-05-23', now() );返回两个参数对应日期相差的天数(用第一个参数减第二个参数)
select adddate( now(), -2 );返回指定天数前/后的日期时间(第一个参数是日期时间,第二个参数是天数,向后加是正数,向前减是负数)
select year('2019-02-24');返回2019 获得年份
select month('2019-02-24') 返回2 获得月份
select day('2019-02-24') 返回 24 获取日
select if( <判断条件>, <条件为真时的返回值>, <条件为假时的返回值> );相当于Java中的三目运算符<判断条件> ? <条件为真的返回值> : <条件为假的返回值>。
如select if(1=1, 2, 3);返回2。
select ifnull(<表达式或者字段>, <表达式或者字段为NULL时的返回值>);通常用于给有可能有NULL的情况下的提供默认值。
select ifnull(null,'无名氏') ; null这里可以写列名 就会把该列值为null的 以无名氏显示
select ifnull(name,'无名氏') from teacher ;