mysql sql语句集合

1、创建数据库，显示所有数据库，修改数据库，删除数据库，显示创建数据库的语句，连接数据库

create database if not exists stu; --创建数据库
show databases      --显示所有数据库
alter database stu charset=utf8;--修改数据库
drop database if exists stu;  --删除数据库
show create database stu;
use stu; --连接数据库

2、表结构操作

--创建表
 create table stu(
    id int auto_increment primary key comment '主键',
    name varchar(20) not null comment '姓名',
    `add` varchar(50) not null default '地址不详' comment '地址',
    score int comment '成绩,可以为空'
    )engine=innodb charset=utf8;

----无符号整数
create table stu2(
    id tinyint unsigned    # 无符号整数
    );
----唯一键
create table stu2(
     id int auto_increment primary key,
     name varchar(20) unique    -- 唯一键
     );

create table stu27(
    id int primary key,
    name varchar(20),
    unique(name)
    );

----有外键
create table stuscore(
       sid tinyint primary key,
       score tinyint unsigned,
       foreign key(sid) references stu(id)   -- 创建外键
)engine=innodb;
----外键 删除时置空，更新时级联。
create table stuscore(
       id int auto_increment primary key comment '主键',
       sid tinyint comment '学号，外键',
       score tinyint unsigned comment '成绩',
       foreign key(sid) references stuinfo(id) on delete set null on update cascade
)engine=innodb;

--显示创建表语句
show create stu \G

--查询表结构
describe stu;  --desc stu;

--复制表
----语法一：create table 新表 select 字段 from 旧表
-------特点：不能复制父表的键，能够复制父表的数据
----语法二：create table 新表 like 旧表
----特点：只能复制表结构，不能复制表数据

--删除表
drop table if exists stu4;

--修改表
alter table stu add `add` varchar(20);	-- 默认添加字段放在最后
 alter table stu add sex char(1) after name;  -- 在name之后添加sex字段
alter table stu add age int first;    -- age放在最前面
alter table stu drop age;   -- 删除age字段
alter table stu change name stuname varchar(10);  -- 将name字段更改为stuname varchar(10)
alter table stu modify `add` varchar(20) default '地址不详';--将add字段更改为varchar(20) 默认值是‘地址不详’
----修改表引擎
alter table stu engine=myisam;
----修改表名
alter table stu rename to student;
----将表移到其他数据库
alter table stu rename to phpdata.stu1;   -- 将当前数据库中的stu表移动到phpdata数据库中改名为stu1

----添加主键
alter table stu add primary key(id);
----删除主键
alter table stu23 drop primary key;

----添加外键
alter table stuscore add foreign key (sid) references stu(id);

----删除外键
-----首先查看外链名称
show  create table stuscore\G
alter table stuscore drop foreign key `stuscore_ibfk_1`;


----添加唯一键
alter table stu add unique(name),add unique(addr);
----删除唯一键
alter table stu drop index name;

3、表数据操作

--插入数据
insert into stu (id,stuname,sex,`add`) values (null,'tom','男','江苏');--第一个字段为自增长字段
insert into stu values (6,'李白','男','四川'),(7,'杜甫','男','湖北');
--更新数据
update stu set sex='女',`add`='上海' where id=1;
--删除数据
delete from stu where id=1;

表查询

--在插入查询有中文时
set names gbk;

----简单查询语句
select * from stu;
----as取别名
select ch,math,ch+math as '总分' from stuscore;
----where后面跟的是条件，在数据源中进行筛选。返回条件为真记录
-- 查询语文和数学都及格的学生
select * from stu where ch>=60 and math>=60;
-- 年龄在20与25之间的
select * from stu where stuage between 20 and 25;
-- 查找没有缺考的学生
select * from stu where ch is not null and math is not null;
--group by 将查询的结果分组，分组查询目的在于统计数据。
-- 查询男生和女生的各自语文平均分
select sex,avg(ch) '平均分' from stu group by sex;
--order by 排序 desc降序  asc升序
select *,ch+math '总分' from stu order by ch+math desc;
--having：是在结果集上进行条件筛选
select stuname from stu having sex='女';
--limit分页显示多少条 语法： limit [起始位置]，显示长度
-----找出班级总分前三名
select *,ch+math total from stu order by (ch+math) desc limit 0,3;
--模糊查询 通配符 _ [下划线] 表示任意一个字符       % 表示任意字符
select * from stu where stuname like 'T_m';
--union 将多个select语句结果集纵向联合起来  all：显示所有数据     distinct：去除重复的数据【默认】
select name from stu union all select name from emp;

如果插入的主键重复会报错

解决方法：如果插入的主键重复就执行替换

---方法1
replace into stu values('s002','ketty');
---方法二
nsert into stu values ('s002','李白') on duplicate key update name='李白';