MySql数据库 基本命令

-- 建库
create database tt;
drop database tt;
create database if not exists tt;
alter database tt default CHARACTER set 'utf8';
show create database tt;
-- 建立学生表
use tt
create table if not exists `stu`(
sid int(11) UNSIGNED PRIMARY KEY auto_increment,
`name` VARCHAR(20) not null,
age int,
sex enum('m','f','mid') DEFAULT 'mid',
birth datetime not null default now(),
email VARCHAR(50),
addr VARCHAR(100) default '北京',
tel VARCHAR(11)
)

-- 创建课程表
create table if not exists course( 
cid int(11) UNSIGNED PRIMARY KEY auto_increment,
cname varchar(20) not null
)

-- 创建成绩表
create table if not exists score(
sid int(11) UNSIGNED not null,
cid int(11) UNSIGNED not null,
cj int(11) UNSIGNED not null,
PRIMARY key(sid,cid),
FOREIGN key(sid) REFERENCES stu(sid) on delete CASCADE on UPDATE CASCADE,
FOREIGN KEY(cid) REFERENCES course(cid) on DELETE CASCADE on update CASCADE
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 删除数据表
drop table stu 
drop table course 
drop table score

-- 查看表结构
show tables;
desc stu;
-- 修改表结构
alter table stubak add beizhu varchar(200) COMMENT'备注';
-- 开头增加一个字段
alter table stu add xxx varchar(20) FIRST;
-- 中间位置增加一个字段
alter TABLE stubak add mr varchar(1) not null DEFAULT'否' after birth;
-- 删除字段 xxx
alter table stu drop xxx
-- 修改字段  change MODIFY
alter table stubak CHANGE beizhu bz VARCHAR(100);
select * FROM stu;
alter table stubak MODIFY mr bit(1) not null DEFAULT 0;
desc stu
select * FROM stu;
-- 部分字段增加记录
insert into stu(name,age,sex,birth,email,tel) VALUES('张三',20,'m','2002-1-1','zs@abc.com','15512687946');
insert into stu(name,age,sex,birth,addr,tel) VALUES('李四',30,'m','1992-1-1','上海','15512687947');
-- 全部字段增加记录
insert into stu VALUES(3,'王武',32,'f','1999-4-3',1,'ww@bcd.com','上海','123486545','王武')
insert into stu VALUES(4,'老毕灯',80,'f','1942-8-2',1,'lbd@bcd.com','越南','12348545','谢正阳')
insert into stu VALUES(5,'李六',90,'f','1932-8-18',1,'ll@bcd.com','上海','1234865045','李六')
insert into stu VALUES(6,'詹姆斯',40,'f','1982-5-13',1,'zms@bcd.com','武汉','123476545','詹姆斯')
insert into stu VALUES(7,'库里',38,'f','1980-5-6',1,'kl@bcd.com','上海','123486565','kuli')

select * FROM stu;
select * FROM score;
select * FROM course;
-- 插入课程表
insert into course(cname) values('数据库'),('数据结构')
insert into course(cname) values('安全运维'),('完全开发'),('安服');
-- 插入成绩表
insert into score values(1,1,89),(1,2,56),(2,3,66),(2,2,34),(2,5,56),(4,1,90),(4,4,67),(4,2,56)
-- 删除记录
delete from stu where sid=5
-- 修改记录
use tt
update stu set name='张三1' where sid=1;
update stu set age='1000' where sid=4;
-- 设置别名
select * FROM stu where sid=4;
select * FROM stu as tt where tt.sid=4;
select sid as 学号,name as 姓名 from stu;

-- 查询
-- 查询所有姓名为张开头的学生
select * from stu where name like '老%';
-- 查询年龄在 25 岁以上的学生
select * from stu where age>500;
-- 查询家住北京和上海的学生
select * from stu where addr='上海' or addr='北京';
select * from stu where addr in('上海','北京');
-- 没有留下邮箱的人
select * from stu where email is null;
-- 又留下邮箱的人
SELECT * from stu where email is not null;
-- 查询张三的信息
select * from stu where name='张三1';
-- 查新sid为1号的学生信息
select * from stu where sid=1;
-- 查询已婚学生的信息
select * from stu where mr=1; 
-- 查询邮箱发给为def的学生信息
select * from stu where email like '%bcd%';
-- 查询邮箱首字母为a或b的学生信息
select * from stu where email like 'a%' or email like 'b%';
-- 查询成绩，按照降序排列 默认是升序
select * from score order by cj desc;
-- 考第一名的学生成绩 limit 0,1 从位置 0 开始，取一个
select * from score order by cj desc limit 0,1;
select * from score order by cj desc limit 0,2;
select * from score order by cj desc limit 1,1;

-- 聚合函数
-- 总共多少学生
SELECT count(*) as 学生总数 from stu;
-- 求总分
select sum(cj) as 总分 from score;
-- 平均分
SELECT avg(cj) as 平均分 from score;
-- 找最高分
select max(cj) as 最高分 from score;
-- 找最低分
SELECT min(cj) as 最低分 from score;
-- 男生多少人 女生多少人 
select sex as 性别,count(*) as 人数  from stu GROUP BY sex; 
-- 男生人数
select sex as 性别,count(*) as  人数 from stu group by sex having sex='m';
select sex as 性别,count(*) as  人数 from stu where sex='m';
-- 每个学生的平均成绩
select sid as 学号,avg(cj) as 平均分 from score
GROUP BY sid;
-- 求学号为 2 的学生的平均成绩
select sid,avg(cj) from score 
group by sid 
having sid=2;
-- 每门课程的最高分是多少分
select cid,max(cj) from score 
group by cid
-- 学生分组显示方式 GROUP_CONCAT()
SELECT sid,GROUP_CONCAT(cid),GROUP_CONCAT(cj order by  cj desc SEPARATOR '') from score
group by sid
-- 每个人平均分，从大到小排
select sid,avg(cj)from score
group by sid
order by avg(cj) desc
-- 函数
-- 时间函数
select now()
select sysdate();
select curtime()
select curtime(2)
-- curtime 当前时间
select curtime()
select curtime(2)
-- curdate 当前日期
select curdate()
select curdate()+2;
select CURRENT_TIME;
select CURRENT_USER;
SELECT now(),date(now());
SELECT now(),time(now());
SELECT now(),quarter(now());
SELECT now(),month(now()); -- 月
select now(),WEEK(now());
SELECT now(),weekday(now()); 
SELECT now(),day(now()); -- 日
SELECT now(),hour(now()); -- 小时 
SELECT now(),minute(now()); -- 分钟 
SELECT now(),second(now()); -- 秒 
SELECT now(),microsecond(now()); -- 微秒
-- 找出出生在 1982 年的学生信息
select * from stu where year(birth)='1982';
-- 找出所有的 90 后
select * from stu where year(birth) BETWEEN 1990 and 1999
select * from stu where year(birth) >=1990 and year(birth) <=1999 

select length('sql课程') -- 返回 9
select CHAR_LENGTH('sql课程') -- 返回 5
select right('买买提,土尔松',3)
select left('买买提,土尔松',3)
-- concat 字符串连接
select CONCAT('买买提',',','土尔松')
-- 将数据库中所有姓老的改成姓谢的
update stu set name=concat('谢',right(name,CHAR_LENGTH(name)-1)) where name like '老%';
select ORD('2')
select ord('abc')
select mid('abcd',2,2)
select sleep(2);
-- union 结果集列数一样
select 1,2 union select * from course

-- 备份 load data outfile 
select * from stu into OUTFILE '/tmp/stubak.txt'
show DATABASES

create table if not exists `stubak`(
sid int(11) UNSIGNED PRIMARY KEY auto_increment,//
`name` VARCHAR(20) not null,
age int,
sex enum('m','f','mid') DEFAULT 'mid',
birth datetime not null default now(),
email VARCHAR(50),
addr VARCHAR(100) default '北京',
tel VARCHAR(11)
)

load data infile '/tmp/stubak.txt' into table stubak;
select * from stubak