MySQL基础【子查询、创建和管理表、数据的修改】

1、子查询

        查询的结果是一个虚表，现基于虚表进行额外的操作，此虚表称为子查询。子查询可以进行进一步的查询。     

        需要进行多步执行的简单查询。

        查询国土面积最小的国家。

select
    *
from
    country
where
    surfacearea = (select min(surfacearea) from country);

        子查询要包括在括号内。先执行子查询，后进行主查询。

        查询人均寿命最高和最低的国家。

select
    name,
    lifeexpectancy
from
    country
where
    lifeexpectancy in (
                        (select max(lifeexpectancy) from country),
                        (select min(lifeexpectancy) from country)
                      );

        查询中国城市人口大于人口平均值的城市。

select 
	name,
	population
from 
	city 
where 
		population > (select avg(population) from city where countrycode = 'chn')
	and 
		countrycode = 'chn';

2、创建和管理表

        针对表中的数据进行的操作, 这样的语言称DML(数据操纵语句)
                select         R
                update        U 
                delete         D 
                insert          C

        针对数据库中的对象的操作, 这样的语言称DDL(数据定义语言)
                数据库 
                表
                列 
                约束 
                索引 
                预编译
                函数
                存储过程 
                触发器
                事件.....

（1）创建数据库：create database 数据库名 charset 字符集;

        在创建数据库时，如果创建同名数据库会出现语句执行错误的情况，当不希望这一句的执行错误引起整个sql脚本执行失败可以加上 if not exists，如 create database if not exists 数据库名charset 字符集; 这时会将原来的执行错误转化成warning。

（2）删除数据库：drop database 数据库名;

        同（1），drop database if exists 数据库名; 删除数据库会将数据库中的所有内容全部丢弃，慎重执行此语句。

（3）修改数据库：alter database 数据库名 charset 新字符集;

（4）查看数据库或表

        show create database(table) 数据库名或表名;

                SQL语言分类
                    1) DML 数据操纵语言, 主要处理数据
                        insert select update delete 
        
                    2) DDL 数据定义语言, 主要处理数据库对象
                        create show alter drop 
        
                    3) DCL 数据控制语句, 主要用于控制事务
                        commit rollback

（5）创建表：

        方法一：全新的建表方式

create table if not exists 表名(
    列1 数据类型1(长度) 其他选项,
    列2 数据类型2(长度) 其他选项,
    ......,
    primary key(列) -- 表级主键
)engine 数据库引擎 charset 字符集;

        数据库引擎 : 
                    InnoDB : 缺省引擎, 支持事务, 外键等高级特性, 速度慢
                    MyIsam : 速度快, 早期的缺省引擎, 不支持事务,外键等高级特性

        其他选项 : auto_increment, default 缺省值, not null, unique.

        常用数据类型
                int                                   4字节整数
                bigint                              8字节整数
                char(长度)                      定长字符串
                varchar(字符数)             可变长字符串，最多65535字节
                double                            8字节双精度浮点
                decimal                          定点数
                date                               日期
                datetime                        日期时间
                longtext                         长文本        

       创建school数据库，在school下创建teacher，classes，student三张表。

create database school;

        创建teacher表

create table if not exists teacher(
    --id int primary key auto_increment, 列级主键，不推荐使用
	id int auto_increment,    --auto_increment，实现id自增
	name varchar(20),
	age int,
	phone varchar(20),
	address varchar(100),
	gender enum('男', '女') default '男',
	primary key(id)        --表级主键，推荐使用
) engine innodb charset gbk;

        创建classes表。

create table if not exists classes(
	id int auto_increment,
	name varchar(30),
	student_count int,
	room char(3),
    gender enum('男', '女') default '男',
	master int,        -- 班主任
	begindate date,    --开课日期
	primary key(id)
);

        创建学生表student, id, name, age, classes, phone。

create table if not exists student(
    id int auto_increment,
    name varchar(20) not null,
    age int,
    classes int,
    phone varchar(20) unique,
    primary key(id)
);

        在teacher表中添加一条数据。

insert into teacher(
    name,
    age,
    phone,
    address
) values (
    '张三',
    '25',
    '13888888888',
    '山东'
);

        方法二：基于子查询，不能复制各种约束（将虚表变为实表）。

create table if not exists 表名 子查询 

create table country select * from world.country where continent = 'asia';
--跨库使用表使用限定条件 数据库名.表名
create table country1 select * from world.country;

        完全复制表结构：create table if not exists 表名 like 已有表名;  此种方式只复制表结构，没有数据。没有一种技术能既复制表结构又复制数据。

（7）修改表结构：alter table 表名 
                                子句；

        添加新列：alter table 表名

                           add 新列名 数据类型 其它选项;

        给teacher表添加性别属性。

alter table teacher 
add gender enum('男', '女') default '男';

        修改一列：alter table 表名 
                          modify 列名 新数据类型 新其他选项;  

        将teacher变长20字符的phone属性修改为定长11字符且非空。

alter table teacher 
modify phone char(11) not null;

        给学生表添加新列address, 非空

alter table student
add address varchar(50) not null;

        把student表phone改为char(11)且唯一

alter table student
modify phone char(11) unique;

        再添加新列gender

alter table student 
add gender enum('男','女') default '男';

        重命名一列：alter table 表名 
                              change 老列名 新列名 新数据类型 新其他选项;

        将teacher表的phone改为mobile

alter table teacher
change phone mobile char(11);

        把student表classes换成 class_id , phone也换成mobile, 要求之前的选项还保留

alter table student 
change classes class_id int;

alter table student
change phone mobile char(11) unique;

        desc 表名：查看表结构

        丢弃一个列：alter table 表名
                             drop column 列名;

                执行此操作, 此列对应的所有数据都会删除。

        删除teacher表中address属性

alter table teacher
drop column address;

        丢弃表：drop table if exists 表名1, 表名2, ....;

        此操作不可以回滚，谨慎！        

        清空表数据：
                truncate table 表名; -- 它是一个DDL语句, 一旦清除,就不能回滚，效率高.

                delete from 表名; -- 它是一个DML语句, 意味着是可以回滚的，效率低.

        修改表名：alter table 表名
                          rename to 新表名

        将teacher表修改为teachers，student表改为students。

alter table teacher 
rename to teachers;

alter table student 
rename to students;

3、对数据的增删改查（DML）

（1）插入数据

        方法一：全新方式插入

insert into 表名 (
	列1,
	列2,
	列3,
	.....
) values (
	值1,
	值2,
	值3,
	....
)

        向teachers表中添加数据

insert into teachers(
    name,
    age,
    mobile
) values (
    '张三',
    32,
    '12333333333'
);

insert into teachers(
	name,
	age,
	gender,
	mobile
) value (
	'李四',
	20,
	'女',
	'12344444444'
);

        一次添加多条数据

insert into teachers(
	name,
	age,
	gender,
	mobile
) values (
	'王老师',
	25,
	'男',
	'1233333333'
), (
	'赵老师',
	28,
	'女',
	'1233333333'
), (
	'周老师',
	37,
	null,
	null
);

        方法二：使用子查询插入

        将张三、李四变为学生

insert into students(
	name,
	age,
	mobile,
	gender,
	address
) select 
	name,
	age, 
	mobile,
	gender,
	'北京'
from 
	teachers 
where 
	id in (1, 2, 3);

（2）克隆表：

create table 新表 like 旧表;
insert into 新表 select * from 旧表 

        将world库下的中国城市克隆schcool库新表chinaCity中 

create table if not exists chinaCity like world.city;
insert into chinaCity select * from world.city where countrycode = 'chn';

        插入一条数据：insert into 表名 set 属性 = 具体值

insert into teachers set
    name = '张老师',
    age = 36,
    mobile = '15666666666',
    gender = '女';

（3）修改数据

update 表名 set 
	列1 = 值1,
	列2 = 值2,
	列3 = 值3,
	....
where 
	行过滤

update teachers set 
    age = 50,
    mobile = '11111111111'
where
    id = 3;

（4）删除数据：delete from 表名  where 行过滤

delete from teachers
where id > 5;