MySQL
# 1、create database 数据库名; drop database 数据库名；
# 2、use 数据库名；
# 3、show tables;
# 4、create table 表名（各字段名及属性）；
# 5、insert into 表名（（字段）values （记录））；
# 6、show columns from 表名；
# 7、show index fron 表名；
# 8、select * from 表名；
# 9、update 表名 set 字段名=更新（修改）的记录 where 主键号=；
# 10、delete from 表名 where 主键号= ；
SHOW databases;#显示所有数据库
create database website;#创建新的数据库
drop database db_book;#删除数据数据库
use arpu;#打开数据库，所有操作都在打开数据库前提下进行的
show tables;#显示某数据库的所有表
show table status from arpu;#显示某数据库所有表的信息
show table status from arpu like 'new%';#显示某数据库下以new开头的表名的信息
show columns from arpu;#显示某表的所有列名及相关信息
show index from arpu;#显示某表的索引
use website;#打开website数据库
#创建web表，并写入字段（列名）及类型
create table if not exists `web`(
`web_id` int unsigned auto_increment,#值自己加1
`web_title` varchar(100) not null,
`web_author` varchar(40) not null,
`web-date` date,
primary key (`web_id`)
)engine=InnoDB default charset=utf8;
#web表中插入数据记录（列表值）
insert into `web`
( `web_title`,`web_author`)
values
("学习MySQL","mary"),("我的MySQL","joan"),("一起学py","赵四"),("乡村爱情10","刘能");
#显示web数据所有字段（列）下的所有记录（数据）或查询某列下数据
select * from web;
#更新、更改表中数据（记录）
update web set web_title='放心学' where web_id =3;
update web set web_title='我的sql' where web_id =2;
select web_title from web;#查询web_title列数据
#删除某行记录（表的结构还仍存在）
delete from web where web_id=2;
select * from web;
insert into `web`
( `web_title`,`web_author`)
values
("MySQL","刘英"),("我的谁","刘的华"),("一起学php","刘禅"),("乡村金竹","刘代表");
#筛选
select * from web where web_author like '刘%';
create table app(
`app_id` int unsigned auto_increment,
`web_title` varchar(100) not null,
`app_author` varchar(40) not null,
`web-date` date,
primary key (`app_id`)
)engine=InnoDB default charset=utf8;
show tables;
show columns from app;
insert into `app`
( `web_title`,`app_author`)
values
("MySQL","张英"),("我的谁","清华"),("一起学php","刘给"),("乡村金竹","史家杰");
select * from app;
#对两张表按条件筛选后的结果组合到一个结果集合中（并集|交集）
select web_title from web union select web_title from app order by web_title;
select web_title from web union all select web_title from app order by web_title;
#将表按某列升降序排列
select * from web order by web_id asc;#升序
select * from web order by web_id desc;#降序
use website;
show tables;
show columns from web;
select * from web;
select * from app;
#添加修改删除表字段
alter table app add app_date date;
alter table app add app_data int;#增加字段并定义类型
alter table app add app_first int first;#第一个位置增加字段
alter table app add app_t int after app_author;#在某列之后增加字段
alter table app drop app_first;#删除字段
show columns from app;
select * from app;
#同一字段增加多行数据
update app
set app_data = case app_id
when 1 then 10
when 2 then 14
when 3 then 34
when 4 then 20
end
where app_id in(1,2,3,4);
delete from app where app_id in (5,6,7,8);#删除多行数据
#单条件筛选
select app_data from app where app_data>20;
#多条件筛选(且条件)
select app_author, app_data from app where app_data<40 and app_author like '刘%' and app_id<5;
#多条件筛选(或条件)
select app_author, app_data from app where app_data>10 or app_data like '刘%' order by app_id;
#创建测试数据库
create database mytest;
#打开数据库
use mytest;
#创建成绩表汇总表
create table grade (
id int unsigned auto_increment,
name varchar(4) not null,
subject varchar(9) not null,
class varchar(5) not null,
score int not null,
primary key (id)
)engine=InnoDB default charset=utf8;
#创建学籍表1
create table roll(
id int unsigned auto_increment,
name varchar(4) not null,
class varchar(5) not null,
primary key (id)
)engine=InnoDB default charset=utf8;
#创建科目1成绩表
create table grade_1 (
id int unsigned auto_increment,
subject varchar(9) not null,
score int not null,
primary key (id)
)engine=InnoDB default charset=utf8;
#创建科目2成绩表
create table grade_2 (
id int unsigned auto_increment,
subject varchar(9) not null,
score int not null,
primary key (id)
)engine=InnoDB default charset=utf8;
#更改表名
ALTER TABLE grade_1 RENAME TO chinese;
alter table grade_2 rename to math;
show tables;
show columns from roll;
select * from roll;
#学籍表添加数据
insert into roll (id,name, class) values (101,'刘德华','高三一班'),
(102,'张学友','高三二班'),(103,'黎明','高三三班'),(104,'郭富城','高三');
insert into roll (id,name, class) values (105,'刘德华','高三一班'),
(106,'张友','高三二班'),(107,'小黎明','高三三班'),(108,'郭城','高三'),
(109,'张三','高三二班'),(1010,'小明','高三三班'),(1011,'陈框','高三'),
(1012,'朱伟','高三二班'),(1013,'刘畅','高三三班'),(1014,'王二','高三');
#语文成绩表
insert into chinese (id,subject,score) values (102,'语文',116),
(106,'张友',66),(107,'小黎明',130),(108,'郭城',79),
(109,'张三',110),(1010,'小明',59),(1011,'陈框',90),
(1012,'朱伟',104),(1013,'刘畅',108),(1014,'王二',121);
update chinese
set subject = case id
when 102 then '语文'
when 106 then '语文'
when 107 then '语文'
when 108 then '语文'
when 109 then '语文'
when 1010 then '语文'
when 1011 then '语文'
when 1012 then '语文'
when 1013 then '语文'
when 1014 then '语文'
end
where id in (102,106,107,108,109,1010,1011,1012,1013,1014);
select * from chinese;
#数学成绩表
insert into math (id,subject,score) values (101,'数学',134),
(102,'数学',141),(103,'数学',89),(104,'数学',101),
(105,'数学',65),(106,'数学',99),(107,'数学',122),(108,'数学',112),
(109,'数学',38),(1010,'数学',9),(1011,'数学',94),(1012,'数学',71);
select * from math;
#选择语文成绩及格同时数学成绩及格的学生
select chinese.score,math.score from chinese,math where chinese.score>90 and math.score>90 order by chinese.id or math.id;
#学籍表与语文表进行匹配
select a.id,a.subject,a.score,b.name,b.class from chinese a inner join roll b where a.id = b.id;
#学籍表与数学表表进行匹配
select a.id,a.subject,a.scosubjectre,b.name,b.class from math a inner join roll b where a.id = b.id;
#匹配后的语文、数学表进行合并
use arpu;
alter table arpu rename to bigdata;
show tables;
use mytest;
select * from chinese;
create database dd;
use dd;
create table goods(
title varchar(200) primary key,
link varchar(100) unique,
comment varchar(20)
)engine=InnoDB default charset=utf8;
select * from goods;
alter table goods convert to character set utf8mb4;
drop database dd;
use website;
show columns from web;
create database webdata;
create database web;
use web;
show tables from web;
select * from webmodel_site;
drop database web;
use dd;
drop database webdata;
create database webdata;
use webdata;
show tables from webdata;
select * from website_datamodel;
