加载中...

（21）基本概念

基本概念

  • 每个表由多个行和列组成
  • 每行包含一个单独实体的数据, 称为记录
  • 每一列包含与该记录相关的一项数据, 称为属性

安装

本博文中所有的SQL语句遵循小写书写风格, 不喜勿喷

  1. $ brew install mysql
  2. $ mysql -u root mysql
  3. #设置开机启动
  4. $ ln -sfv /usr/local/opt/mysql/*.plist ~/Library/LaunchAgents 
  5. #加载mysql
  6. $ launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
  7. #启动mysql服务器
  8. $ mysql.server start
  9. #关闭mysql服务器
  10. $ mysql.server stop
  11. #使用根用户
  12. $ mysql -u root 
  13. #创建用户密码
  14. mysql> set password=password('123456');
  15. #创建数据库 
  16. mysql> create database firstdb;
  17. #添加用户和密码, 并赋予firstdb的完全访问权限, 账户名为amdin, 密码为123456
  18. mysql> grant all on firstdb.* to admin@localhost identified by '123456';  
  19. #退出
  20. mysql> exit

初试数据库

  1. #使用非根用户登陆数据库, 并使用firstdb
  2. mysql> mysql -u admin -p123456 firstdb

创建表

  1. #创建
  2. mysql> create table sales_rep(
  3.     -> employee_number int,
  4.     -> surname varchar(40),
  5.     -> first_name varchar(30),
  6.     -> commission tinyint
  7.     -> );
  8.  
  9. #显示已有表
  10. mysql> show tables;
  11. #describe来检查表结构
  12. mysql> describe sales_rep;

sales_rep为表名, employee_number, surname, first_name, commission为属性, int表示整型, varchar表示变长字符, tinyint表示小整数

删除表和数据库

  1. #创建一个表
  2. mysql> create table com(id int);
  3. #删除表使用drop关键字
  4. mysql> drop table com;
  5. #切换root用户, 创建数据库com
  6. mysql> create database com;
  7. #删除数据库
  8. mysql> drop database com;

插入/删除/修改记录

  1. #插入数据 insert into 表名(要插入的属性名) values(属性值), 字符串使用单引号
  2. mysql> insert into sales_rep values(1, 'Rive', 'Sol', 10);
  3. mysql> insert into sales_rep values(2, 'Gordimer', 'Charlens', 15);
  4. mysql> insert into sales_rep values(3, 'Serote', 'Mike', 10);
  5. #一行添加数据
  6. mysql> insert into sales_rep values
  7.      >(1, 'Rive', 'Sol', 10),
  8.      >(2, 'Gordimer', 'Charlens', 15),
  9.      >(3, 'Serote', 'Mike', 10);
  10. #从文件中加载数据, 格式load data local infile "文件名" into table 表名;
  11. mysql> load data local infile "sales_rep.sql" into table sales_rep;

删除记录

  1. # 删除employee_number为5的记录
  2. mysql> delete from sales_rep where employee_number = 5;

修改记录

  1. #修改employee_number的记录的commission为12
  2. mysql> update sales_rep set commission = 12 where employee_number = 1;

数据检索

  1. #检索所有插入的数据
  2. mysql> select * from sales_rep;
  3. #检索surname为'Gordimer'的记录
  4. mysql> select * from sales_rep where surname='Gordimer';

模式匹配

like和%进行模糊查找

  1. # 输出已surname已R开头的数据
  2. mysql> select * from sales_rep where surname like 'R%';

排序

order by

  1. #数据按照surname排序输出, 当surname相同时, 使用first_name排序
  2. mysql> select * from sales_rep order by surname, first_name;
  3. #递减排序使用关键字desc, 默认使用升序asc
  4. mysql> select * from sales_rep order by surname desc;

多列排序时, 使用逗号隔开排序规则, order by排序优先次序为从左到右

  1. mysql> select ename, job, sal from emp  order by deptno asc, sal desc;

按照字符串部分子串排序

  1. #按照job中最后两个字符进行排序
  2. mysql> select ename, job from emp order by substr(job, length(job) - 1);

书中说: 找到字符串末尾(字符串长度)并减2, 则其实诶之就是字符串中倒数第二个字符

但在我测试过程用应该是建1, 则是对最后两个字符排序(疑问?)

根据数据项的键排序

使用case语句

  1. 如果job是salesman, 按照comm, 否则, 按照sal排序
  2. mysql> select ename, sal, job, comm from emp    -> order by case when job = 'salesman' then comm else sal end;

限制数据数量

limit

  1. #按surname降序输出两行
  2. mysql> select * from sales_rep order by surname desc limit 2;

从表中随机返回n条记录

  • order by可以接受函数的返回值, 并使用它来改变结果集的顺序
  1. select ename, job from emp order by rand() limit 5;

最大值/最小值/计数/平均/综合

  1. #查询commission的最大值
  2. mysql> select max(commission) from sales_rep;
  3. #查询最小值
  4. mysql> select min(commission) from sales_rep;
  5. #表中不重复surname的个数
  6. mysql> select count(distinct surname) from sales_rep;
  7. #commission的平均值
  8. mysql> select avg(commission) from sales_rep;
  9. #commission的总和
  10. mysql> select sum(commission) from sales_rep;
  11. #right()从字符串右端算起, 按位返回字符串
  12. mysql> select right(date_joined, 5), right(birthday, 5) from sales_rep;

去重

  1. #使用distinct, 去掉查询字段相同的记录

改变表结构

添加列

  1. #给表添加一列名为data_joined, 类型为date
  2. mysql> alter table sales_rep add date_joined date;
  3. #添加一类名为year_born, 类型为year
  4. alter table sales_rep add year_born year;

修改列定义

  1. 将year_born改为 列名为birthday, 类型为data
  2. mysql> alter table sales_rep change year_born birthday date;

重命名表

  1. mysql> alter table sales_rep rename cash_flow;
  2. #恢复原来表名
  3. mysql> alter table cash_flow rename to sales_rep;

删除列

  1. #删除列名为enhancement_value的一列
  2. mysql> alter table sales_rep drop enhancement_value;

日期函数

  1. #给date类型设置日期
  2. mysql> update sales_rep set date_joined = '2014-02-15', birthday = '2000-02-14' where employee_number = 1;
  3. #使用日期函数, 设置显示日期格式
  4. mysql> select date_format(date_joined, '%m/%d/%y') from sales_rep;
  5. # 使用year()输出年, month()输出月, dayofmonth()输出一个月的第几日
  6. mysql> select year(birthday), month(birthday), dayofmonth(birthday) from sales_rep;

高级查找(别名, concat, 多表查询, case表达式)

as起别名(类似pytho中import包时用as起别名)

  1. mysql> select year(birthday) as year, month(birthday) as month, dayofmonth(birthday) as day from sales_rep;

在别名的时候用别名做限定条件

from语句是在where之前完成的

  1. #将查询结果作为内敛视图
  2. mysql> select * from (select sal as salary, comm as commission from emp) x where salary < 5000;

concat连接列

将多列作为一列进行输出

  1. #将first_name, 一个空格, surname连接在一起输出
  2. mysql> select concat(first_name, ' ', surname) as name, month(birthday) as month from sales_rep order by month;
  1. mysql> select concat(ename, ' works as a ', job) as msg from emp where deptno = 10;

多表查询

创建两个表并插入数据

  1. mysql> create table client(
  2.     -> id int,
  3.     -> first_name varchar(40),
  4.     -> surname varchar(30)
  5.     -> );
  6. mysql> create table sales(
  7.     -> code int,
  8.     -> sales_rep int,
  9.     -> customer int,
  10.     -> value int
  11.     -> );
  12.  
  13. mysql> insert into customer values
  14.     -> (1, 'Yvaonne', 'Clegg'),
  15.     -> (2, 'Johnny', 'Chaka'),
  16.     -> (3, 'Winston', 'Powers'),
  17.     -> (4, 'Patricia', 'Mankunku');
  18. mysql> insert into sales values
  19.     -> (1, 1, 1, 2000),
  20.     -> (2, 4, 3, 250),
  21.     -> (3, 2, 3, 500),
  22.     -> (4, 1, 4, 450),
  23.     -> (5, 3, 1, 3800),
  24.     -> (6, 1, 2, 500);
  1. code为1, 且两表中employee_number和sales_rep的记录输出, select后面部分列出要返回的字段
  2. mysql> select sales_rep, customer, value, first_name, surname from sales, sales_rep where code = 1  and sales_rep.employee_number= sales.sales_rep;

case表达式

对select中的列值执行if-else操作

  1. mysql> select ename, sal,
  2.     -> case when sal <= 2000 then 'underpaid'
  3.     -> when sal >= 4000 then 'overpaid'
  4.     -> else 'ok'        #else语句是可选的
  5.     -> end as status    #对case语句返回的列取别名
  6.     -> from emp;

查询中分组(不懂)

group by指的是按照某个属性分组, 与其他组互不干扰

  1. #查询每个sales_rep的value值的和
  2. mysql> select sales_rep, sum(value) as sum from sales group by sales_rep;

常用类型

数字类型

  • int(整型), 表示整数
  • float/double分别表示单精度和双精度浮点数

字符类型

  • char(M) 固定长度为M的字符串, 字符串长度不够会补上空格 , 搜索时大小写无关
  • varchar(M), 可变长字符串(相比char一般比较节省内存), 搜索时大小写无关
  • text, 最大65535个字符, 搜索时大小写无关
  • blob, 最大65535个字符, 搜索时大小写相关

日期和时间类型

  • date, 默认格式YYYY-MM-DD, 可以使用date_format()函数更改输出方式
  • timestamp(M), 时间戳, YYYYMMDDHHMMSS, 可以指定不同长度的时间戳(M只影响显示)
  • time, 格式HH:MM:SS

表类型

表类型优点缺点
静态表速度快, 易缓存要求更多的磁盘空间
动态表占磁盘空间小需要维护, 不易出问题后重建
压缩表只读表类型, 占用很少磁盘空间每条记录分开压缩, 不能同时访问
merge表表尺寸小, 某些情况下速度快eq_ref搜索慢, replace不能工作
heap表散列索引, 最快数据存在内存, 出现问题易丢失
还没有评论.