sql

来源：互联网发布：汽车一定要贴膜吗知乎编辑：程序博客网时间：2024/06/16 14:28

一、SQL语言
结构化查询语言
操作关系型数据库

二、操作数据库
1.创建数据库
  CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [, create_specification] ...]
  create_specification:
      [DEFAULT] CHARACTER SET charset_name |   [DEFAULT] COLLATE collation_name
  ~创建一个名称为mydb1的数据库。
   CREATE DATABASE mydb1;
  ~创建一个使用gbk字符集的mydb2数据库。
   create database mydb2 character set gbk;
  ~创建一个使用utf8字符集，并带校对规则的mydb3数据库。
   create database mydb3 character set utf8 collate utf8_bin;
2.查看数据库
  查询所有数据库:
   show databases;
  查看数据库的创建语句:
   show create database db_name;
3.删除数据库
  drop database db_name;
  ~删除前面创建的mydb1数据库
   drop database mydb1;
4.修改数据库
  ALTER DATABASE [IF NOT EXISTS] db_name [alter_specification [, alter_specification] ...]
  alter_specification:
      [DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name
  ~ 查看服务器中的数据库，并把其中某一个库的字符集修改为utf8;
   alter database mydb2 characer set utf8;
5.选择数据库
  选择数据库:use db_name;
  查看当前所在的数据库:select database();
三、操作数据库表
1.创建表
  CREATE TABLE table_name
  (
   field1 datatype,
   field2 datatype,
   field3 datatype,
   ...
  )character set 字符集 collate 校对规则
  field：指定列名　datatype：指定列类型

  ~创建employee表
   create table employee(
    id int primary key auto_increment ,
    name varchar(20) unique,
    gender bit not null,
    birthday date,
    entry_date date,
    job varchar(50),
    salary double,
    resume text
   );

  约束:可以为字段定义一些限制
   主键约束 primary key 可选的 auto_increment 主键自动增长
   非空约束 not null
   唯一约束 unique
   外键约束

2.查看表
  查询当前数据库中所有的表 show tables;
  查看表结构 desc tab_name;
  查看建表语句 show create table tab_name;

3.修改表
ALTER TABLE table ADD/MODIFY/DROP/change/character set (column datatype [DEFAULT expr] [, column datatype]...);
rename table 表名 to 新表名;

  ~在上面员工表的基本上增加一个image列。
   alter table employee add image blob;
  ~修改job列，使其长度为60。
   alter table employee modify job varchar(60);
  ~删除gender列。
   alter table employee drop gender;
  ~表名改为user。
   rename table employee to user;
  ~修改表的字符集为gbk
   alter table user character set gbk;
  ~列名name修改为username
   alter table user change name username varchar(20);
4.删除表
  drop table tabName;
  ~删除之前的user表
   drop table user;


四、操作数据记录 - 增删改查 - CRUD

注意：

create table exam(
id int primary key auto_increment,
name varchar(20) not null,
chinese double,
math double,
english double
);

insert into exam values(null,'关羽',85,76,70);
insert into exam values(null,'张飞',70,75,70);
insert into exam values(null,'赵云',90,65,95);

create table cust(
id int primary key auto_increment,
name varchar(20)
);
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');

  create table dept(
   id int primary key auto_increment,
   name varchar(20)
  );
  insert into dept values (null,'财务部'),(null,'人事部'),(null,'科技部'),(null,'销售部');


  create table emp(
   id int primary key auto_increment,
   name varchar(20),
   dept_id int
  );
  insert into emp values (null,'张飞',1),(null,'关羽',2),(null,'刘备',3),(null,'赵云',4);

1.Insert语句
  INSERT INTO table [(column [, column...])] VALUES (value [, value...]);

  ~使用insert语句向表中插入三个员工的信息。
   insert into employee (id,name,gender,birthday,entry_date,job,salary,resume)
   values(null,'张飞',0,'1990-09-09','1990-10-01','打手',998.0,'真的很能打...');

insert into employee values (null,'关羽',1,'1988-08-08','1989-09-09','财神',9999999.9,'公司挣钱都指着他了....');

   insert into employee values
   (null,'刘备',1,'1980-09-09','1980-10-01','ceo',10.0,'公司的老大')
   ,(null,'赵云',0,'1999-09-09','2000-01-01','保安队长',10000.0,'公司老大的贴身保镖,关系很亲密...');

**mysql的乱码:

方案1:set names gbk; -- 通知服务器和当前客户端交互时应该使用的编码.
案例2:修改mysql服务器的配置,指定服务器默认认为的客户端编码集找到mysql的安装目录下的my.ini,修改[mysql] default-character-set=gbk 修改即可

2.Update语句
UPDATE tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]

  ~将所有员工薪水修改为5000元。
   update employee set salary = 5000.0;
  ~将姓名为’张飞’的员工薪水修改为3000元。
   update employee set salary=3000 where name='张飞';
  ~将姓名为’赵云’的员工薪水修改为4000元,job改为ccc。
   update employee set salary=4000,job='ccc' where name='赵云';
  ~将刘备的薪水在原有基础上增加1000元。
   update employee set salary=salary+1000 where name='刘备';

3.Delete语句
delete from tbl_name[WHERE where_definition]

  ~删除表中名称为’张飞’的记录。
   delete from employee where name='张飞';
  ~删除表中所有记录。
   delete from employee;
  ~使用truncate删除表中记录。
   truncate table employee;

4.Select语句
(1)基本查询
SELECT [DISTINCT] *|{column1, column2. column3..} FROM table;

   ~查询表中所有学生的信息。
    select * from exam;
   ~查询表中所有学生的姓名和对应的英语成绩。
    select name,english from exam;
   ~过滤表中重复数据。
    select distinct english from exam;
   ~在所有学生分数上加10分特长分显示。
    select name,english+10,math+10,chinese+10 from exam;
   ~统计每个学生的总分。
    select name,math+english+chinese from exam;
   ~使用别名表示学生总分。
    select name as 姓名 ,math+english+chinese as 总成绩 from exam;
    select name 姓名 ,math+english+chinese 总成绩 from exam;

  (2)使用Where子句进行过滤查询
   ~查询姓名为XXX的学生成绩
    select * from exam where name='张飞';
   ~查询英语成绩大于90分的同学
    select * from exam where english>90;
   ~查询总分大于230分的所有同学
    select name 姓名,math+english+chinese 总分 from exam where math+english+chinese>230;
   ~查询英语分数在 80－100之间的同学。
    select name,english from exam where english between 80 and 100;
   ~查询数学分数为75,76,77的同学。
    select name,math from exam where math in(75,76,77);
   ~查询所有姓张的学生成绩。
    select * from exam where name like '张%';
    select * from exam where name like '张_';
    select * from exam where name like '张__';
   ~查询数学分>70，语文分>80的同学。
    select * from exam where math>70 and chinese>80;

(3)排序查询
SELECT column1, column2. column3.. FROM table order by column [asc|desc]

   ~对语文成绩排序后输出。
    select name ,chinese from exam order by chinese desc;
   ~对总分排序按从高到低的顺序输出
    select name 姓名,math+english+chinese 总分 from exam order by 总分 desc;
   ~对姓张的学生成绩排序输出
    select name 姓名,ifnull(math,0)+ifnull(english,0)+ifnull(chinese,0) 总分 from exam where name like '张%' order by 总分 desc;

  (4)聚合函数
   ~1.count() -- 计数
    ~统计一个班级共有多少学生？
     select count(*) from exam;
    ~统计数学成绩大于70的学生有多少个？
     select count(name) from exam where math>70;
    ~统计总分大于230的人数有多少？
     select count(math+english+chinese) from exam where math+english+chinese>230;

   ~2.sum() -- 求和
    ~统计一个班级数学总成绩？
     select sum(math) from exam;
    ~统计一个班级语文、英语、数学各科的总成绩
     select sum(math) ,sum(english) ,sum(chinese) from exam;
    ~统计一个班级语文、英语、数学的成绩总和
     select sum(ifnull(math,0)+ifnull(english,0)+ifnull(chinese,0)) from exam;
    ~统计一个班级语文成绩平均分
     select sum(chinese)/count(*) 语文平均分 from exam;

   ~3.avg() -- 求平均
    ~求一个班级数学平均分？
     select avg(ifnull(math,0)) from exam;
    ~求一个班级总分平均分？
     select avg(ifnull(math,0)+ifnull(english,0)+ifnull(chinese,0)) from exam;
   ~4.Max/Min -- 最大/最小
    ~求班级最高分和最低分（数值范围在统计中特别有用）
    select max(ifnull(math,0)+ifnull(english,0)+ifnull(chinese,0)) from exam;
    select min(ifnull(math,0)+ifnull(english,0)+ifnull(chinese,0)) from exam;

  (5)分组查询
   SELECT column1, column2. column3.. FROM table group by column having ...
   ~对订单表中商品归类后，显示每一类商品的总价
    select product,sum(price) from orders group by product;
   ~查询购买了几类商品，并且每类总价大于100的商品
    select product ,sum(price) from orders group by product having sum(price)>100;
   ~查询单价小于100总价大于100的商品的名称.
    select product,sum(price) from orders where price<100 group by product having sum(price)>100;
   **where 和 having都可以实现过滤,where是在分组之前进行过滤,having是在分组之后进行过滤,where中不可以使用聚合函数,having中可以使用聚合函数.

***sql的编写顺序:select from where groupby having orderby
***sql的执行顺序:from where select group by having order by

===================================================================================================================================================
五、备份和恢复数据库
备份：
  在cmd窗口中：mysqldump -u 用户名 -p 数据库名>文件名.sql
恢复:
  方法1:在cmd窗口中 mysql -u 用户名 -p 数据库名<文件名.sql -- 只能恢复数据,不能恢复数据库本身!
  方法2:在mysql命令行下执行 source 文件名.sql -- 可以在当前位置执行sql文件从而恢复数据,但是仍然只能恢复数据,不能恢复数据库本身!
===================================================================================================================================================
六、外键:用来声明表和表之间的关系的键,可以使数据库维系这种关系,一旦某个操作执行时会破坏这种关系,数据库会进行阻止.

foreign key(ordersid) references orders(id)

create table dept(
  id int primary key auto_increment,
  name varchar(20)
);
insert into dept values (null,'人事部'),(null,'财务部'),(null,'销售部'),(null,'科技部');

create table emp(
  id int primary key auto_increment,
  name varchar(20),
  dept_id int,
  foreign key (dept_id) references dept(id)

);
insert into emp values (null,'张无忌','1'),(null,'奥巴马',2),(null,'哈利波特',3),(null,'金正恩',4),(null,'朴乾',4);
===================================================================================================================================================
七、多表设计、多表查询

1.多表设计
  1-1:
  1-*:
  *-*:

图解：

2.多表查询
  笛卡尔积查询:这种查询,查出来的结果是两张表相乘的结果,如果左边表有m条记录右边表有n条记录,最终查处m*n条记录.但是这种查询其中往往包含大量的错误的数据,所以笛卡尔积查询通常我们不使用.
   select * from dept ,emp;
  内连接查询:只查寻出左边表有记录且右边表也有对应记录的数据
   select * from dept,emp where dept.id = emp.dept_id;
   select * from dept inner join emp on dept.id=emp.dept_id;
  外连接查询:
   左外连接查询:在内连接的基础上增加上左边表有而右边表没有的记录
    select * from dept left join emp on dept.id=emp.dept_id;
   右外连接查询:在内连接的基础上增加上右边表有而左边表没有的记录
    select * from dept right join emp on dept.id=emp.dept_id;
   全外连接查询:在内连接的基础上增加上左边表有而右边表没有的记录和右边表有而左边表没有的记录
    select * from dept full join emp on dept.id=emp.dept_id; # mysql 不支持全外链接!!!
    select * from dept left join emp on dept.id=emp.dept_id
    union
    select * from dept right join emp on dept.id=emp.dept_id; #可以通过union拼接左外和右外的结果模拟全外连接

0 0