Mysql基础 01 数据与sql

一、基本概念

数据库(database,DB)：存储数据的仓库。

数据库管理系统软件(Database Management System,DBMS)：是一种操作和管理数据库的大型软件。常见的DBMS有oracle、sql server、Mysql

数据库系统(Data Base System,DBS)：有软件、数据库和数据管理员组成。

二、mysql的常用命令

1、登录：mysql -h 服务器IP -P 端口号 -u 用户名 -p 密码 --prompt 命令提示符 --delimiter 指定分隔符
  例：mysql -uroot -p

2、退出：quit exit \q

三、sql规范

SQL是专门为数据库而建立的操作命令集，现在几乎所有的数据库均支持sql

1、SQL语句不区分大小写，但字符串常量区分大小写。建议命令大写，表明库名小写。

2、SQL语句课单行或多行写，以分号“;”结尾。

3、可用空格和缩进来提高语句的可读性。

4、注释：单行注释：--   多行注释：/* */

5、SQL语句可以折行操作。

四、数据类型

数值类型

日期和时间类型
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

字符串类型

此外，还有一个BIT类型，占一个二进制位长度，可以用来表示布尔类型。

五、SQL语句

1、显示当前的日期和时间：select now();

2、查看所有数据库：show databases;

3、创建数据库（在磁盘上创建一个对应的文件夹）：create database [if not exists] db_name [character set xxx]
  注：①写if not exists，当创建的数据库已存在，也不会报错。
    ②character set xxx表示创建xxx字符集。
  例：create database s3;

4、显示警告信息：show warnings;

5、显示数据库s3的创建信息：show create database s3

6、删除数据库s4：drop database [if exists] s4;

7、修改数据库字符集为gbk(默认为utf8)：alter database s3 character set gbk;

8、切换数据库到s4：use s4;

9、查看当前使用的数据库：select database();

10、创建表(类似一个excel表)：
create table tab_name(
  field1 type [约束条件],
  field2 type [约束条件],
  …
  fieldn type [约束条件]
)

约束：
  primary key (非空且唯一) :能够唯一区分出当前记录的字段称为主键。主键字段必须是数字类型。一张表只有一个主键。
  unique
  not null 该字段值非空
  auto_increment 字段的值自增1
  foreign key 外键约束

例：

CREATE TABLE employee(
    id TINYINT PRIMARY KEY auto_increment,
    name VARCHAR(4),
    gender TINYINT(1),
    age INT,
    department VARCHAR(20),
    salary DOUBLE(7,2)
);

注：TINYINT(1)表示1个位宽。VARCHAR(4)表示4个字符。DOUBLE(7,2)表示总共7位数字，其中2位为小数。

11、查看当前库下的表名：show tables;

12、查看employee表结构：desc employee; 或 show columns from employee;

13、查看表的创建信息：show create table employee;

14、添加一个表字段，例：
alter table employee add is_married tinyint(1);
alter table employee add entry_date not null;
alter table employee add A INT, add B VARCHAR(10);

15、删除表字段：alter table emplyee drop A,drop entry_date;

16、修改一列类型：alter table tab_name modify 列名 类型 [完整性约束条件]［first｜after 字段名］;
其中first表示排字段第一个，after表示排某个字段后面。
例：alter table employee modify age smallint not null default 18 alter id;

17、修改列名：alter table tab_name change [column] 列名 新列名 类型 [完整性约束条件]［first｜after 字段名］;
例：alter table employee change department depart varchar(20) after salary;

18、修改表名：rename table 表名 to 新表名;

19、删除表：drop table 表名;

20、插入一条表记录：
①insert ［into］ tab_name (field1,filed2,…) values (value1,value2,…);
②insert ［into］ tab_name values (所有字段值);
③insert ［into］ tab_name set 字段名=值;

21、插入多条表记录：
insert ［into］ tab_name (field1,filed2,…) values
  (value1,value2,…),
  (value3,value4,…);

22、修改表记录：
update tab_name set field1=value1,field2=value2,…[where 语句];
注：没有WHERE子句，则更新所有的行。
例：update employee set salary=salary+200 where name=“xixi”;

23、删除表纪录：如果不跟where语句则删除整张表中的数据。delete语句只能删除表记录，不能删除表结构，想要一同删除表记录和表结构，用drop。delete语句是逐条删除记录，而TRUNCATE也可以删除表中的所有数据，它是首先摧毁表，再新建表。TRUNCATE删除的数据不能在事务中恢复。
delete from tab_name [where …]；
truncate table tab_name;

24、查询表的所有记录：SELECT * FROM tab_name;

25、查询表的某些字段的所有记录：SELECT field1,filed2 … FROM tab_name;

26、查询表的所有不重复的记录：select [distinct] *|field1，field2，… from tab_name；

27、select 也可以使用表达式，并且可以使用: 字段 as 别名或者:字段 别名：
select name as na,age+10 from emp;

28、select语句使用where子句，进行过滤查询。例：
select name na,age+10 as a from emp where age>=20;
如果字段值为null，使用 is，而不是 =。
例：select * from emp where id=3 and gender is null;

where字句中可以使用：
  比较运算符：
  > < >= <= <> !=
  between 80 and 100 值在10到20之间
  in(80,90,100) 值是10或20或30
  like ‘xi%’

  pattern可以是%或者_，
  如果是%则表示任意多字符，此例如唐僧,唐国强
  如果是_则表示一个字符唐_，只有唐僧符合。两个_则表示两个字符：__

在多个条件直接可以使用逻辑运算符 and or not

29、select语句使用Order by 指定排序的列，排序的列即可是表中的列名，也可以是select 语句后指定的别名。
select *|field1,field2… from tab_name order by field [Asc|Desc]
Asc 升序、Desc 降序，其中asc为默认值 ORDER BY 子句应位于SELECT语句的结尾。

30、使用正则表达式查询，例：
select * from order_menu where product_name regexp “香蕉$”;

31、Mysql在执行sql语句时的执行顺序：from  where  select  group by  having  order by  limit
having和where作用相同，只是执行顺序不同。
分析：
select JS as JS成绩 from ExamResult where JS成绩 >70; ---- 不成功
select JS as JS成绩 from ExamResult having JS成绩 >90; — 成功

32、group by 分组查询：
select * from order_menu group by 4,5;
以第4和5列为基准分组，相同的合并，只显示每组的第一行。也可以写字段名：
select * from order_menu group by born_date,class;
注：禁用 ONLY_FULL_GROUP_BY 模式：SET sql_mode = ‘’;

33、使用聚合函数，例：
select class,sum(price) from order_menu group by class;

  count(列名)：统计行的个数。注意:count(*)统计所有行，而 count(字段)用来统计某一列中非 NULL 的行数。

  SUM(列名)：统计满足条件的行的内容和

  AVG(列名)：会计算列中所有非 NULL 值的平均值

  Max()、Min()：最大、最小值

34、加having过滤行
select class,sum(price) from order_menu group by class having sum(price)>200;

35、ifnull(price,0)：如果price的值为null，则设置为0
select class,min(ifnull(price,0)) from order_menu group by class;

36、limit：显示行数
select * from order_menu limit 3; 显示前3行
select * from order_menu limit 3,3; 跳过前3行，显示接下来的3行。