#创建数据库相关代码
CREATE DATABASE test;  -- 创建名为test的数据库
show CREATE DATABASE test; -- 查看创建好的数据库
show DATABASES;   -- 查看所有的数据库列表
use test;  -- 使用创建好的数据库
drop DATABASE test; -- 删除数据库

-- 使用test数据库
use test;
-- 创建员工信息表
CREATE TABLE emp(
			depid char(3),
			depname varchar(20),
			peoplecount int);			
-- 查看表是否创建成功
show tables
-- 删除数据表
drop table emp

# 主键约束
-- 单字段主键
CREATE TABLE emp(
			depid char(3) PRIMARY KEY,
			depname varchar(20),
			peoplecount int);
-- drop table emp		
-- 多字段主键
CREATE TABLE emp(
			depid char(3) PRIMARY KEY,
			depname varchar(20),
			peoplecount int,
			primary key (depid,depname));

CREATE TABLE emp(
			depid char(3) PRIMARY KEY,
			depname varchar(20) not null ,
			peoplecount int);

CREATE TABLE emp(
			depid char(3) PRIMARY KEY,
			depname varchar(20) not null ,
			peoplecount int unique
			);

CREATE TABLE emp(
			depid char(3) PRIMARY KEY,
			depname varchar(20) not null DEFAULT '-' ,
			peoplecount int unique
			);

# 创建一个含有各种约束条件的数据表
create table exp (id int primary key auto_increment,
				  fname varchar(4) not null,
				  math int default 0,
				  minmax float unique);

create table fruits(
					f_id char(10) not null,
					s_id int not null default 0,
					f_name char(255),
					f_price decimal(8,2) not null,
					primary key(f_id));
insert into fruits(f_id,s_id,f_name,f_price) values 
				("a1",101,"apple",5.2),
				("a2",102,"orange",10.2);				

select <select_list>
from <table_list>
[where <where_condition>]
[group by <group_by_list>]
[having <having_condition>]
[order by <order_by_list> asc|desc]
[limit <limit_number>]

create table second_hand_houses(
							frame char(10) not null,
							ftype char(255),
							size int,
							region char(10) not null,
							floor int,
							drection char(10) not null,
							bulit_data char(10) not null,
							price_unit int)

#查询北京所有2室1厅的二手房信息
select * from second_hand_houses where ftype = '2室1厅'

#查询朝阳区房价单价在10000以上的二手房名称，户型，面积，朝向，楼层
select frame,ftype,size,drection,floor
from second_hand_houses
where region ='朝阳区'
and price_unit>50000

#查询各地区下二手房数量、总的可居住面积、最大单价
select region,count(*) as counts,sum(size) as tot_size,max(price_unit) as max_price
from second_hand_houses
group by region

#查询各地区下每年二手房数量、总的可居住面积、最大单价
select region,bulit_data,count(*) as counts,sum(size) as tot_size,max(price_unit) as max_price
from second_hand_houses
group by region,bulit_data

#按照地区、户型、楼层和阳台朝向分组统计东城区、朝阳区二手房的平均单价和总数量
select region,ftype,floor,drection,avg(price_unit) as avg_price,count(*) as counts
from second_hand_houses
where region in ("东城区","朝阳区")
group by region,ftype,floor,drection
having avg_price >50000;

#按面积降序，单价升序的方式查询所有2室1厅的二手房信息
select * from second_hand_houses
where ftype ="2室1厅"
order by size desc,price_unit

#查询建筑时间最悠久的前5套二手房
select * from second_hand_houses
order by bulit_data
limit 5

#查询出东城区，1980年建的二手房，并且单价在2-3名
select * from second_hand_houses
where region ="丰台区" and bulit_data = "2008"
order by price_unit desc
limit 1,2   

create database <database_name>

create table table_name (column_name column_type)

#创建学生信息表
CREATE TABLE stu_info (
    id INT AUTO_INCREMENT PRIMARY KEY, -- 学生ID，自增主键
    iname VARCHAR(20),                 -- 学生姓名
    gender CHAR(1),                    -- 性别（M: 男, F: 女）
    department VARCHAR(10),            -- 所属院系
    age INT,                           -- 年龄
    province VARCHAR(10),              -- 省份
    email VARCHAR(50),                 -- 邮箱
    mobilephone CHAR(11)               -- 手机号（11位）
);
# create table stu_order like stu_info 创建相似的表
INSERT INTO stu_info (iname, gender, department, age, province, email, mobilephone)
VALUES
('周八', 'M', '计算机', 21, '北京', 'zhouba@example.com', '13800138005'),
('武勇', 'M', '生物', 21, '浙江', 'wuyong@example.com', '13800138049');

alter table stu_info add column hobby char(20) default 0

drop database <databsae_name>

drop table <table_name>

#删除学生表在，名为“张三“和”李四”的记录
delete from stu_info  #保留自增字段记录；如果需要删除部分数据或需要事务支持，使用 DELETE。
where iname in("张三","李四") 

#删除学生表中email 字段
alter table stu_info drop email

#truncate #彻底清除字段表；如果需要快速清空整个表，使用 TRUNCATE
truncate table stu_info

alter table stu_info rename to stu_summary

alter table stu_info rename to stu_summary

alter table stu_summary change age age varchar(10)
alter table stu_summary change gender sex varchar(10)

alter table stu_summary modify column age varchar(10)
desc stu_summary #查看表字段类型

update stu_summary set email='zhangyong@163.com' where iname ='张勇'