mysql中基本sql语句操作

建数据库：

-- 创建数据库db1  (选中要执行的SQL语句，如果没有选中运行所有的SQL语句)
create database db1;

-- 判断是否存在，如果不存在则创建数据库db2
create database if not exists db2;

-- 创建数据库并指定字符集为gbk，如果没有指定默认是utf-8，在安装的时候已经指定。按tab自动补全
create database db3 character set gbk;

-- 查看所有的数据库 
show databases;

-- 查看某个数据库的定义信息
show create database db1;

-- 将db3数据库的字符集改成utf8
alter database db3 character set utf8;

-- 删除db2数据库
drop database db2;

-- 查看正在使用的数据库
select database();

-- 使用db1数据库
use db1;

表的操作：

-- 创建数据库day14
create database day14;

use day14;

-- 创建student表包含id整数,name变长字符串长20,sex性别定长型1,birthday字段日期类型
create table student(
 -- 字段名 字段类型
   id int,
   name varchar(20),
   sex char(1),
   birthday date
);

-- 查看mysql数据库中的所有表
use mysql;
show tables;

-- 查看student表的结构
use day14;
desc student;

-- 查看student的创建表SQL语句
show create table student;

-- 创建s1表，s1表结构和student表结构相同
create table s1 like student;

desc s1;

-- 直接删除表s1表
drop table s1;

-- 判断表是否存在并删除s1表
drop table if exists s1;

-- 为学生表添加一个新的字段remark,类型为char(20)
desc student;
alter table student add remark char(20);

-- 将student表中的remark字段的改成varchar(100)
alter table student modify remark varchar(100);

-- 将student表中的remark字段名改成intro，类型varchar(30) 
alter table student change remark intro varchar(30);

-- 删除student表中的字段intro 
alter table student drop intro;

alter table student drop remark;

-- 将学生表student改名成student2
rename table student to student2;

增删改的操作：

-- 查询表中记录，* 表示所有的列，没有查询条件，查询的是所有的行
select * from student;

-- 向表中插入所有字段，不写字段名
-- sql中字符串使用单引号，在mysql中也可以使用双引号。
-- 日期也使用单引号
insert into student values(1, '牛魔王', '男', '2001-11-11' );
-- now() 函数表示现在的时间：同时包含日期和时间
insert into student values(2, '红孩儿', '女', now());

-- 插入部分数据，往学生表中添加id, name数据
insert into student (id,name) values (3,'太上老君');
-- 长度超出
insert into student (id,name) values (3,'太上老君23784239472397423742974294729472394723479823498');

-- 一条insert语句插入多条记录
insert into student values(4, '孙悟空', '男', '1999-11-11' ),(5, '猪肛裂', '男', '1999-11-11' ),(6, '沙悟净', '男', '1999-10-11' );

-- 不带条件修改数据，将所有的性别改成女
update student set sex='女';

select * from student;

-- 带条件修改数据，将id号为2的学生性别改成男
update student set sex='男' where id=2;

-- 一次修改多个列，把id为3的学生，修改性别为男，生日：1996-03-08
update student set sex='男', birthday='1996-03-08' where id=3;

-- 带条件删除数据，删除id为3的记录
delete from student where id=3;
select * from student;

-- 不带条件删除数据,删除表中的所有数据
delete from student;

-- 删除表结构再创建表
truncate student;

查询的操作：

-- 查询student表中的id 和name 列
select * from student;

select id,name from student;

-- 没有指定条件，查询的是所有的行。条件是对行进行过滤
select name, birthday from student;

-- 使用别名，定义别名关键字是：as
select id as 编号,name as 姓名 from student;
-- as关键字可以省略
select name 姓名, birthday 生日 from student;

select * from student;

-- 添加一个字段 address varchar(50)
alter table student add address varchar(50);

update student set address='广州';

-- 查询学生来至于哪些地方,并且去掉重复行
select address from student;

select distinct address from student;

-- 查询学生的姓名和地址,去掉重复行。必须几个列都相同，才会去除
select distinct name,address from student;

-- 修改student表结构,添加数学和英语成绩列
alter table student add math int, add english int;

select * from student;
-- 查询姓名、数学，将数学每个减10分
select name,math from student;
-- 只影响查询结果
select name,math-10 from student;

-- 查询所有列与math + english的和并使用别名”总成绩”
select *,(math+english) 总成绩 from student;

条件查询操作：

CREATE TABLE student3 (
  id int,  -- 编号
  name varchar(20), -- 姓名
  age int, -- 年龄
  sex varchar(5),  -- 性别
  address varchar(100),  -- 地址
  math int, -- 数学
  english int -- 英语
);

INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES
 (1,'马云',55,'男','杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),
 (4,'柳岩',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港',99,99),
 (7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);
 
 select * from student3;
 
 -- 查询math分数大于80分的学生
 select * from student3 where math > 80;

-- 查询english分数小于或等于78分的学生
select * from student3 where english <=78;
 
-- 查询age等于20岁的学生
select * from student3 where age = 20;

-- 查询age不等于20岁的学生，注：不等于有两种写法
select * from student3 where age <> 20;
select * from student3 where age != 20;

-- 查询age大于35且性别为男的学生(两个条件同时满足)
select * from student3 where age > 35 and sex='男';
select * from student3 where age > 35 && sex='男';

-- 查询age大于35或性别为男的学生(两个条件其中一个满足)
select * from student3 where age > 35 or sex='男';

-- 查询id是1或3或5的学生
select * from student3 where id=1 or id=3 or id=5;

-- 查询id是1或3或5的学生
select * from student3 where id in(1,3,5);

-- 查询id不是1或3或5的学生
select * from student3 where id not in (1,3,5);

-- 查询english成绩大于等于77，且小于等于87的学生
select * from student3 where english between 77 and 87;
select * from student3 where english >=77 and english<=87;
select * from student3 where english<=87 and english >=77;

-- 查询姓马的学生
select * from student3 where name like '马';
-- 相当于
select * from student3 where name = '马';

-- 模糊查询必须使用通配符
select * from student3 where name like '马%';

-- 查询姓名中包含'德'字的学生
select * from student3 where name like '%德%';

-- 查询姓马，且姓名有2个字的学生
select * from student3 where name like '马_';

-- 查询英文成绩为NULL的学生
select * from student3 where english is NULL;

-- 查询英语成绩不为NULL的学生
select * from student3 where english is not NULL;

-- 查询姓名和英语成绩，如果英语为null，则显示为0分
select name,english from student3;

-- IFNULL(列名, 默认值): 如果这一列有值，则显示它的值，如果为NULL，则显示后面默认值
select name,ifnull(english,0) 英语 from student3;

查询显示排序，分页：

-- 查询所有数据,使用年龄降序排序
select * from student3 order by age desc;

-- 查询所有数据大于20岁的学生,在年龄降序排序的基础上，如果年龄相同再以数学成绩降序排序
select * from student3 order by age desc, math desc;

-- 聚合函数针对列来操作
-- 查询学生总数
select count(id) from student3;  -- 8
select count(english) from student3;  -- 7
select count(*) from student3; -- 8

-- 查询年龄大于40的总数
select count(*) from student3 where age > 40; 

-- 查询数学成绩总分
select sum(math) 数学总分 from student3;

-- 查询数学成绩平均分
select avg(math) 数学平均分 from student3;

-- 查询数学成绩最高分
select max(math) 数学最高分 from student3;

-- 查询数学成绩最低分
select min(math) 数学最低分 from student3;

-- 分组查询：按性别进行分组
select * from student3 group by sex ;

-- 按性别进行分组，求男生和女生数学的平均分
select sex, avg(math) from student3 group by sex;
-- 按性别进行分组，求男生和女生数学的总和
select sex, sum(math) from student3 group by sex;

-- 先使用Where过滤掉一些数据，再进行分组
-- 查询年龄大于25岁的人,按性别分组,统计每组的人数
select sex, count(*) from student3 where age >25 group by sex;

-- 查询年龄大于25岁的人，按性别分组，统计每组的人数，并只显示性别人数大于2的数据。
select sex, count(*) from student3 where age >25 group by sex  where count(*) > 2;  -- 错误的
-- 如果要对分组后的结果再进行过滤，使用having
select sex, count(*) from student3 where age >25 group by sex  having count(*) > 2;  -- 正确


INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES 
(9,'唐僧',25,'男','长安',87,78),
(10,'孙悟空',18,'男','花果山',100,66),
(11,'猪八戒',22,'男','高老庄',58,78),
(12,'沙僧',50,'男','流沙河',77,88),
(13,'白骨精',22,'女','白虎岭',66,66),
(14,'蜘蛛精',23,'女','盘丝洞',88,88);

select * from student3;

-- 返回0到3这三条记录 (起始行，返回行数)
select * from student3 limit 0,3;

-- 查询学生表中数据，从第3条开始显示，显示6条。
select * from student3 limit 2,6;

-- 每页显示5条
-- 第一页：从0开始，显示5条
select * from student3 limit 0,5;

-- 第二页：从5开始，显示5条
select * from student3 limit 5,5;

-- 第三页：从10开始，显示5条
select * from student3 limit 10,5;

主键约束：

-- 通过主键查询
select * from student3 where id=1;
-- 也可以做为主键
select uuid();

-- 创建表学生表st1, 包含字段(id, name, age)将id做为主键
create table st1(
   id int primary key,  -- 在建表的时候添加主键
   name varchar(20),
   age int
);

desc st1;

-- 插入重复的主键值
select * from st1;
insert into st1 values(1,'张三',30);
insert into st1 values(1,'李四',20);

-- 插入NULL的主键值
insert into st1 values(NULL,'张三',30);

-- 删除st1表的主键
alter table st1 drop primary key;

-- 表存在的情况下，添加主键
alter table st1 add primary key(id);

-- 创建学生表st2, 包含字段(id, name, age)将id做为主键并自动增长
create table st2(
   id int primary key auto_increment,
   name varchar(20),
   age int
);

select * from st2;

-- 插入数据
insert into st2 (name,age)values('张三',10);
-- 如果不写前面保证列数匹配
insert into st2 values(null,'张三',10);

-- 将主键的起始值设置为1000
alter table st2 auto_increment = 1000;

-- 影响到自增长的值
delete from st2;

truncate st2;