mysql 的指令， 增删改查....等等等

mysql 的指令 这是我的笔记

 show databases;
drop show databases;
database python;
create database pythonew charset=utf8;
use mysql;
select database();
 show tables;
 create table xxxx(id int, name varchar(30));
create table yyyy(id int primary key not null auto_increment, name varchar(30));
 desc xxxx;
'''
create table id_class(id int, name varchar(30);
create  table students(
id int unsigned not null auto_increment primary key,
name varchar(30),
age tinyint unsigned default 0,
high decimal(5,2),
gender enum("man","women","shuangxing","baomi") default "baomi",
cls_id int unsigned);
);
insert into students values(0,"wang",10,188.00,"man",0);
 select * from students;
INSERT INTO URLS (URL,CONTENT) VALUES ("WWW.SANTOSTANG.COM","SANTOS BOLG");
 INSERT INTO URLS (URL,CONTENT) VALUES ("WWW.BAIDU.COM","THIS IS CONTENT");
mysql> DELETE FROM URLS WHERE URL ='WWW.BAIDU.COM';

CREATE TABLE URLS(
    -> ID INT NOT NULL AUTO_INCREMENT,
    -> URL VARCHAR(4000) NOT NULL,
    -> CONTENT VARCHAR(4000) NOT NULL,
    -> CREATED_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -> PRIMARY KEY (ID));
Query OK, 0 rows affected (0.06 sec)
DESC URLS;
 SELECT * FROM URLS WHERE ID =1;
SELECT URL ,CONTENT FROM URLS WHERE ID=1;
	DELETE FROM URLS WHERE URL ='WWW.BAIDU.COM';
'''

create database python;
show create database python;
 select database();
use pyrhon;drop database python;
show create table; 表名字

修改表
alter table 表名  add 列名  类型
alter table classes add  birthday  datetime

alter table classes modify birthday date；

alter table classes change birthday birth date default "2001-01-01";
alter table classes drop high;

drop database;
drop table classes;


insert into  classes (name,age) values (12,21),(12,12);
insert into classes values (default,"python",10,"nv"1),(         )

updata students set gender where id = 9;
upddate student set age = 9  where name = "python";
upddate student set age = 9 where id = 9;

where 查询条件
select * from classes where name = "python";
select * from classes where id = 9;


select name,gender from student;

select name as 姓名 gender as 性别 from classes;

selecet id as 序号 from classes;


--创建有一个数据库
create database python_text charset = utf8;
--使用数据库
use python_text;
--显示使用的当前数据库是哪个
select database();
-- 创建一个数据表
---students表
create table url(id int  primary key auto_increment, url_list varchar(1000) not null)
create table students(
id int unsigned primary key auto_increment not null,
name varchar(20)  0,
hright decimal(5,2),
gender enum("男","女","中性","保密") default "保密",
is_delete bit default 0);
--classes表
create table classes(
id int unsigned auto_increment primary key not null,
name varchar(30) not null);
-- 创建数据表的方式
show create tables students;

--插入数据
insert into students values
(0,'xiaom',13,100.00,2,0),
(0,'xiaom',14,100.00,1,0),
(0,'xiaom',15,100.00,3,0),
(0,'xiaom',16,100.00,1,0),
(0,'xiaom',12,100.00,4,0),
(0,'xiaom',19,100.00,2,0);

insert into classes values(0,'a'),(0,'b)'),(0,'c');

查询  select * from students;
select * from classes;
select id, name from classes;

查询字段
select name,age from students;
select name as 名字 ,age as 年龄 from students;
select s.name,s.age from students as s;

select gender from students;

--消除重复行
select distinct gender from students;



--条件查询
select * from students where age >18;
select * from students where age=12;
select name,id from students where age>18 and age <20;
select name,id from students where age>18 or age <20;

--not 使用
select * from students where not age> 18 and gender = 1;
select * from students where not (age>18 and gender=1);


-- 模糊查询
--以xiaom开头的
select name from students where name like'xiaom%';

--查询姓名中有xiaom 的所有名字%
select name from students where name like'%xiaom%';
-- 查询有两个字的名字

select name from students where name like '__'

-- 查询至少有两个字的名字
select name from students where name like '__%'

-- rlike正则表达式
--以xiao开头
select name from students where name rlike "^xiao.*"

--以m结尾 m$"
select name from students where name rlike "^xiao.*m$"


--范围查询
--非连续使用（）
select age from students where age in (12,13,14);
select age from students where age in (12,13,14);

--使用连续
select age from students where age between 14 and 20;
select age name from students where age not between 14 and 20;
--select age from students where  age not ( between 15 and 20;)
select age from students where not age between 15 and 20;

select age from students where height is null;

select age from students where height is  not null;

--排序
select * from students where age between 1 and 18 and gender =1 order by age asc;
select * from students where (age between 1 and 18) and gender =1 order by age desc;-- 从大到小
--多列排序

select * from students where (age between 1 and 18) and gender =1 order by age desc, id desc ;-- 从大到小
select * from students order by age asc and height desc;

--聚合
--总数
select count(*) as 男性人数 from students where gender=1;
select count(*) as 女性人数 from students where gender=2;

--最大值
select max(age) from students;,,
--求和
select sum(age) from students;

--最小值
--平均值
select avg(age) from students;
select sum(age)/count(*) from students;
select round(sum(age) / count(*),2) from students;--保留两位小数
select round(sum(age) / count(*),3) from students;--四舍五入


-- 分组
select  from students group by

select gender,count(*) from students group by gender
select gender,avg(age) from students group by gender
select gender,group_concat(name) from students group by gender;

select gender ,group_concat(name,'  ',age,'  ',id) from students where gender = 1 group by gender;

select gender ,group_concat(name,'-',age,'-',id) from students where gender = 1 group by gender;


select gender,group_concat(name) ,avg(age) from students group by gender having avg(age)>2;

select gender ,group_concat(name) from students group by gender;


--分页
--limit 限制查询出的数据个数
--查询前五个
select * from students where gender =2 limit 0, 5;
--查询第n页
select * from students where gender =2 limit 5, 5; limit（第n页-1）* 每页的个数，每页的个数）
--select * from students  limit 1,3 order by age asc;

select * from students order by age asc limit 1,3;

select * from students where gender=2 order by age desc limit 0,2;

--第二组五个
select * from students where gender =2 limit 5, 5;


--内连接
---取交集


select * from students inner join classes;
--按要求显示姓名，班级

selecet students.name,classes.name from students inner join classes on students.cls_id= id_class;
--给数据表起名字
select s.name,c.name from students as s inner join classes as c on s.cls_id = c.id;

-- 查询 有能够对应班级的学生以及班级的信息，显示学生的所有信息，只显示班级名称
select students.*,classes.name from students inner join classes on students.cls_id = classes.id;
select c.name，s.name from students as s inner join classes as c on s.cls_id = c.id;

--排序
select  c.name，s.name from students as s inner join classes as c on s.cls_id = c.id order by c.name,s.id;


--外连接

select * from students as s left join classes as c on s.cls_id = c.id;

-- 查询没有对应班级信息的学生
select * from students as s left join classes as c on s.cls_id = c.id having  c.id is null;
select * from students as s left join classes as c on s.cls_id = c.id where  c.id is null;

mysql -uroot -pmysql

create table

--把数据插入mysql
source area.sql


select * from areas where pid = 37000;
select * from area where pid = 37000;

select * from area where id = 1;

select * from area where pid = 37000;


select province.atitle,city.atitle from areas as provice inner join areas as city on city.pid=province.aid having province.article= "山东省"

--子查询
select * from students where height=188
select * from students where height = (select max(height) from students);
mysql --uroot -pmysql;
 create database jin_dong charset=utf8;
 use jin_dondg;
 --drop database jin_dong;

create table goods(
id int unsigned primary key auto_increment not null,
name varchar(150) not null,
cate_name varchar(40) not null,
brandname varchar(30) not null,
price decimal(10,3) not null default 0,
is_show bit not null default 1,
is_saleoff bit not null default 0 );
删除表
drop table goods;

insert into goods values(0,"fag15.4英寸","笔记本","联想","3399",default,default);
insert into goods values(0,"fag15.1英寸","笔本","联想","3399",default,default);
insert into goods values(0,"fag15.3英寸","记本","联想","3399",default,default);
insert into goods values(0,"fag15.4英寸","记本","联想","3399",default,default);
insert into goods values(0,"fag14英寸","笔本","华硕","3399",default,default);
insert into goods values(0,"fag12.4英寸","笔记本","联想","399",default,default);
insert into goods values(0,"fag14.4英寸","笔本","华硕","339",default,default);
insert into goods values(0,"fa,15.4英寸","笔本","华硕","339",default,default);
insert into goods values(0,"fag17.4英寸","游戏本","华硕","399",default,default);
insert into goods values(0,"fa,18.4英寸","笔记本","华硕","39",default,default);
insert into goods values(0,"fa19.4英寸","游戏本","华为","99",default,default);
insert into goods values(0,"f150.4英寸","笔记本","华硕","9",default,default);
insert into goods values(0,"fag15.4英寸","超级本","华为","339",default,default);
insert into goods values(0,"fag15.4英寸","笔记本","华为","3399",default,default);
insert into goods values(0,"fa15.4英寸","超级本","华为","3329",default,default);
insert into goods values(0,"fag15.4英寸","超级本","联想","33929",default,default);
insert into goods values(0,"fag15.4英寸","笔记本","联想","3324",default,default);
insert into goods values(0,"fag15.4英寸","超级本","华为","3352",default,default);
insert into goods values(0,"fag15.4英寸","笔记本","华硕","3399253",defaultdefault);
insert into goods values(0,"fag15.4英寸","笔记本","华硕","339523",default,default);
insert into goods values(0,"fa15.4英寸","笔记本","华硕","3399256",default,default);
insert into goods values(0,"fag15.4英寸","笔记本","三星","3399",default,default);
insert into goods values(0,"fa15.4英寸","笔记本","小米","3399623",default,default);
insert into goods values(0,"fag15.4英寸","笔记本","外形热","3399523",default,0);

select * from goods where cate_name="笔本";

select name as 名称 ,price as 价格 from goods where  cate_name="笔本";


去重
select distinct cate_name from goods;

select cate_name from goods group by cate_name;
select cate_name, group_concat(price)  from goods group by cate_name;
select round(avg(price),2) from goods;
select cate_name  ,avg(price) from goods group by cate_name;
select cate_name  ,avg(price),max(price) , min(price),count(*) from goods group by cate_name;

select avg(price) from goods;
select * from goods where price>432;

select * from goods where price>(select avg(price) from goods);
select cate_name ,max(price) from goods group by cate_name;
select cate_name ,max(price) from goods;



select * from (select cate_name,max(price) as max_price from goods group by cate_name)  as g_new
left join goods as g
on g_new.cate_name=g.cate_name and g_new.max_price=g.price order by g_new.cate_name;


select g_new.cate_name,goods.name,goods.price from ( select cate_name,max(price) as max_price from goods group by cate_name) as g_new left join goods
on g_new.cate_name=goods.cate_name and g_new.max_price =goods.price order by g_new.cate_name;



create table if not exists goods_cates(
id int unsigned primary key auto_increment,
name varchar(40) not null)

select cate_name from goods group by cate_name;

insert into goods_cates(name) select cate_name from goods group by cate_name;


update goods set cate_name=401 where cate_name="笔记本"

把cate_name 改为另一张表的id
update goods as g inner join goods_cates as c on g.cate_name=c.name set cate_name=c.id;


改类型
alter table goods change cate_name cate_id int unsigned not null;


添加外键  确定两列数据的对比
alter table goods add foreign key  (cate_id) references goods_cates(id);




create table goods_brands (
id int unsigned primary key auto_increment,
name varchar(40) not null  ) select brandname as name from goods group by brandname;

同步数据


update goods as g inner join goods_brands as b     on g.brandname=b.name条件
     操作set g.brandname=b.id；
     修改表结构
     desc goods;
alter table goods
change cate_name cate_id int unsigned not null,
change brandname brand_id int unsigned not null;






























分割表