MySQL 操作语句大全

DDL

概念  Data Definition Language  数据定义语言作用  定义数据库或表结构用 关键词 create alter drop

数据库语句操作

创建一个名称为mydb1的数据库 create database mydb1查看数据库的创建细节      show create database mydb1;查看目前所有的数据库      show databases;创建一个使用gbk字符集的数据库    create database mydb2 set gbk;修改字符集   alter database mydb2 character set utf8;删除创建的某个数据库  drop database mydb1; 

表结构语句操作

* 创建表之前选择数据库 use mydb2;     * 创建一个员工信息表 create table employee(_id int,name varchar(100),gender varchar(20),birthday date,entry_day date,job varchar(100),salary float (8,2),resume text);* 查看当前数据库中的所有表 show tables;* 查看表的创建细节  show create table employee;* 在员工表上边添加image一列   alter table employee add image blob;* 修改job列，使其长度为60    alter table employee modify job varchar(60);* 删除image列 alter table employee drop image;* 表名字修改为user rename table employee to user;* 修改表的字符集为utf-8; alter table user character set gbk;* 列名name修改为username alter table user change name username varchar(100);

DML

*   概念 Data Manipulation Language 数据操作语言*  关键词，insert update delete *  MySQL： *  字符串类型 使用单引号 'ajdjddj’*  日期时间 ‘2017-11-11’*  特殊值 null* 数据库表内容增删改查* 插入数据    * 指定列插入数据   insert into user(_id,username,gender,birthday,entry_date,job,salary,resume) values(1,'zhangsan','mail','1999-10-09','2017-03-21','cto',10000,"这个人很厉害的啊");       * 不指定列插入数据insert into user  values(4,'王五','mail','1999-10-09','2017-03-21','cto',10000,"这个人很厉害的啊"); * 编码     * 告知服务器客户端用的编码是gbk        set character_set_client=utf8;    * 告知 服务器客户端查看结果集使用的编码为gbk        set character_set_results=utf8;* 修改数据：    *   将员工薪水修改为5000元 update user set salery=5000;    *  将姓名为’张三‘的员工薪水修改为3000元 update user set salery=3000 where username=’张三‘    *  将姓名为”李四“ 的员工薪水修改为4000，job修改为ccc update user set salery=4000,jpb='ccc' where username="李四"    *  将张三的薪水在原有基础上增加1000元 update user set salery=salery+1000 where username='张三'* 删除数据    * delete from user where username='张三'    *   delete from user;       * truncate table user;

DCL

* 概念 Data Control Language 数据控制语言

DQL

概念

Data Query Language 数据查询语言

查询数据

先创建一个学生表create table student(id int primary key auto_increment,name varchar(20) unique not null,chinese float,english float,math float);往学生表中添加一些信息:insert into student (name,chinese,english,math) values('wangba',93,22,77);insert into student (name,chinese,english,math) values('lisi',80,90,29);insert into student (name,chinese,english,math) values('wangwu',55,99,98);insert into student (name,chinese,english,math) values('zhaoliu',99,30,57);insert into student (name,chinese,english,math) values('zhouqi',78,22,77);查询所有学生信息    select * from user 查询学生员工的姓名和对应的英语成绩   select name,english from student 过滤表中的重复数据  select distinct english from student 在所有学生数学分数上加10分特长分  select math as 原来的数学成绩,math+10 as 现在的数学成绩 from student； 统计每个学生的总分  select name ,chinese +math+english from student;  使用别名表示学生分数  select name,chinese+math+english 总分 from student; 查询wangwu的学生成绩  select * from student where name='wangwu'; 查询英语成绩大于90分的同学  select * from student where english>90; 查询总分大于200分的所有同学  select * from student where(chinese+english+math)>200; 查询数学成绩在70-80之间的同学  select * from student where math between 70 and 80; 查询数学成绩的范围在98,97,90的同学  select * from student where math in(98,97,90); 查询所有姓li的同学成绩  select * from student where name like 'li%'; 查询语文>90 数学大于70的同学  select * from student where math>70 and chinese>90; 对数学成绩排序后输出      select name，math from student order by math; 对总分进行排序后输出，然后按照从高到底的顺序输出  select name,chinese+english+math （as 总分） from student order by chinese+english+math desc;

报表查询（使用数据库提供的函数）

统计一个班级共有多少学生select count(*) from student;统计数学成绩大于80的学生select count(*) from student where math>80;统计总分大于250的人数select count(*) from student where (chinese+english+math)>250;统计一下班级数学总成绩select sum(math) from student;统计一下班级语文、英语、数学各科的总成绩select sum(chinese),sum(english),sum(math) from student;统计所有成绩的总和select sum(chinese+english+math)from student;统计班级语文的平均分select sum(chinese)/count(*) from student;select avg(chinese) from student;统计一个班级总分的平均分select avg(chinese+math+english) from student;统计班级语文最高分和数学最低分select max(chinese)from student;select min(chinese) from student; 分组查询  group by 按照什么进行分组 having 判断条件 创建一个订单表 create table orders(id int ,product varchar(20),price float); insert into orders values(1,'苹果笔记本',10000); insert into orders values(2,'联想笔记本',5000); insert into orders values(3,'dell笔记本',5000); insert into orders values(4,'iphone7',7000); insert into orders values(5,'苹果笔记本',10000); 对订单表中商品归类后，显示没一类商品的总价 select product ,sum(price) from orders group by product; 查询购买了几类商品，并且每类商品总价大于7000的商品 select product,sum(price) from orders group by product having sum(price) >6000; 分页查询: limit  startIndex,size select * from orders limit  0,1;

多表设计

一对多设计原则：使用外键约束案例演示：部门与员工关系create table department(id int primary key,name varchar(100));create table employee(id int primary key,name varchar(100),salery float(8,2),department_id int,constraint department_id_fk foreign key (department_id) references department(id));insert into department values(1,'人事部');insert into department values(2,'教学部');insert into department values(3,'会计部');insert into employee values(1,'zhangsan',1000.01,1);insert into employee values(2,'lisi',1000.03,1);insert into employee values(3,'wangwu',1000.02,2);insert into employee values(4,'zhaoliu',1000.03,3);多对多设计原则：引入中间表，联合主键，primary key(字段1,字段2);案例演示:一个老师可以教多个学生，一个学生也可以被多个老师教；create table teacher(id int primary key,name varchar(100));create table student (id int primary key,name varchar(100));create table teacher_student(t_id int,s_id int,primary key(t_id,s_id),constraint t_id_fk foreign key(t_id) references teacher(id),constraint s_id_fk foreign key (s_id) references student(id));一对一 尽量保证一张表按照外键关联+唯一约束create table person(id int primary key,name varchar(100));create table id_card(id int primary key,num varchar(20),person_id int unique,constraint person_id_fk foreign key(person_id) references person(id));根据主键关联create table person(id int primary key,name varchar(100));create table id_card(id int primary key,num varchar(20),constraint person_id_fk foreign key(id) references person(id));

连接查询

初始定义表结构

定义一个用户表 一个订单表create table customer(id int primary key auto_increment,name varchar(20),city varchar(20));create table orders(id int primary key auto_increment,good_name varchar(30),price float(8,2),customer_id int);insert into customer (name,city) values('李晨','北京');insert into customer (name,city) values('范冰冰','山东');insert into customer (name,city) values('李冰冰','天津');insert into customer (name,city) values('李易峰','上海');insert into orders values(1,'最新版儿童纸尿裤',100,1);insert into orders values(2,'最新版儿童小车',1000,2);insert into orders values(3,'最新版婚纱',10000,2);insert into orders values(4,'lv手包',100000,3);insert into orders values(5,'香奈儿',100000,8);分析：上述表示一对多关系，用户表为左表 订单表为右表

交叉连接：cross join

返回左表和右表的笛卡尔积（3*4）select * from customer,orders;select * from customer cross join orders;

内连接：inner join

返回满足条件的所有记录隐式内连接    select c.*,o.* from customer c,orders o where  c.id =o.customer_id;显示内连接    select * from customer inner join orders on customer.id=orders.customer_id;    select * from customer c inner join orders o on c.id=o.customer_id;

外连接：outer join

• 左外连接 left outer join 返回满足条件的所有记录，同时返回左表中剩余的其他记录

• select * from customer c left outer join orders o on c.id=o.customer_id;

• 右外连接 返回满足条件的所有记录，同时返回游标中剩余的其他记录。

• select * from customer c right outer join orders o on c.id=o.customer_id;

子查询

概念：嵌套c查询，是指在select 子据或者where 子句中又嵌入select 查询语句，比如查询范冰冰的所有订单信息select id from customer where name='范冰冰';select * from orders where customer_id=id;子查询 :select * from orders where customer_id=(select id from customer where name='范冰冰');

联合查询

union 关键字联合查询能合并两条查询语句的查询结果，去掉其中的重复数据行，然后返回没有重复的数据进行显示查询结果。select * from orders where price>1000 union select * from orders where customer_id =2;

定义表的约束

primary key: 不允许重复 ，不允许为空删除主键：alter table 表名 drop primary key定义主键自动增长 auto_increment定义唯一约束 unique定义非空约束 not null定义外键约束 constraint ordersid_FK foreign key(ordersid) refrerence orders(id);

TPL：事务处理语言

数据库备份与还原

数据库备份

    windows/mac 命令行 mysql -u root -p 数据库名称 >盘符/xx.sql

数据库还原

    windows/mac 命令             mysql - u root -p 数据库名 <sql文件所在位置    mysql 命令        1：确保数据库存在        2：source 盘符下的sql;