mysql基础一

SQL语句分类

1. 数据定义语言：简称DDL(Data Definition Language)，用来定义数据库对象：数据库，表，列等。关键字：create，alter，drop等。
2. 数据操作语言：简称DML(Data Manipulation Language)，用来对数据库中表的记录进行更新。关键字：insert，delete，update等。
3. 数据控制语言：简称DCL(Data Control Language)，用来定义数据库的访问权限和安全级别，及创建用户。
4. 数据查询语言：简称DQL(Data Query Language)，用来查询数据库中表的记录。关键字：select，from，where等。

注意事项：

1. SQL语句可以单行或多行书写，以分号结尾。
2. 可使用空格和缩进来增强语句的可读性。
3. MySQL数据库的SQL语句不区分大小写。
4. 同样可以使用/* */的方式完成注释，或者–、#。

MySQL中的我们常使用的数据类型如下

1. int 整型。
2. double 浮点型。
3. varchar 字符串型。
4. date 日期类型，格式为yyyy-MM-dd。

mysql详细的数据类型

分类	类型名称	字节数	说明
整数类型	tinyInt	1B	很小的整数。带符号的范围是-128到127。无符号的范围是0到255。
	smallint	2B	小的整数。带符号的范围是-32768到32767。无符号的范围是0到65535。
	mediumint	3B	中等大小的整数,带符号的范围是-8388608到8388607。无符号的范围是0到16777215。
	int(integer)	4B	普通大小的整数。带符号的范围是-2147483648到2147483647。无符号的范围是0到4294967295。
	bigint	8B	大整数。带符号的范围是-9223372036854775808到9223372036854775807。无符号的范围是0到18446744073709551615。
布尔类型	boolean	1B	表示布尔值，但真实上是使用tinyint(1)表示的。当值为0时表示假，当值为非0时表示真。
小数类型	float	4B	单精度浮点数。-3.403E38～3.403E38
	double	8B	双精度浮点数。-1.798E308～1.798E308
	decimal(M,D)	(65位)最大精度38	压缩严格的定点数。其中M表示十进制数字总的个数，D表示小数点后面数字的位数-1038~1038。 不能超过38个有效数字
日期类型	year	1	YYYY 1901~2155
	time	3	HH:MM:SS。范围：-838:59:59~838:59:59
	date	3	YYYY-MM-DD。范围：1000-01-01~9999-12-31
	datetime	8	YYYY-MM-DD HH:MM:SS。范围：1000-01-01 00:00:00~ 9999-12-31 23:59:59
	timestamp	8	YYYY-MM-DD HH:MM:SS。范围：19700101 00:00:01~2038-01-19 03:14:07
文本	char(M)	0-255B	M为0~255个字符之间的整数定长。定长字符串。
	varchar(M)	0-65535B	M为0~65535个字节之间的整数变长。变长字符串。
	tinytext	2^8–1B	允许长度0~255字节。短文本字符串。
	text	2^16-1B	允许长度0~65535字节,64KB。长文本数据。
	mediumtext	2^24-1B	允许长度0~167772150字节,16MB。中等长度文本数据。
	longtext	2^32-1B	允许长度0~4294967295字节,4个G。极大文本数据。
二进制类型	binary(M)	255B	允许长度0~M个字节的定长字节字符串
	varbinary(M)	255B	允许长度0~M个字节的变长字节字符串
	tinyblob	2^8–1B（256B）	允许长度0~255字节。不超过 255 个字符的二进制数据。
	blob	2^16-1B（64K）	允许长度0~65535字节,64KB。二进制形式的长数据。
	mediumblob	2^24-1B（16M）	允许长度0~167772150字节,16MB。二进制形式的中等长度数据。
	longblob	2^32-1B（4G）	允许长度0~4294967295字节,4G。二进制形式的极大数据。

• 登陆数据库

  • 格式1：> mysql -u用户名 -p密码 例：mysql -uroot -proot
  • 格式2：> mysql --host ip地址 --user用户名 --password密码 例：mysql -h 127.0.0.1 -uroot -proot

DDL数据定义语言之数据库操作

• 创建数据库采用安装数据库时指定的默认编码

  create database 数据库名;
  

• 创建数据库并指定编码

  create database 数据库名 character set 字符集;
  

• 查看数据库MySQL服务器中的所有的数据库:

  show databases;
  

• 查看某个数据库的定义的信息:

  show create database 数据库名;
  

• 删除数据库

  drop database 数据库名称;
  

• 查看正在使用的数据库:

  select database();
  

• 切换数据库：

  use 数据库名;
  

DDL数据定义语言之表操作

• 创建表

  create table 表名 (
      字段名 类型(长度) [约束],
      字段名 类型(长度) [约束],
  	...
  );
  

• 类型：

  varchar(n) 字符串
  int        整形
  double     浮点
  date       日期
  timestamp  时间戳
  

• 约束：

  primary key 主键，被主键修饰字段中的数据，不能重复、不能为null。
  

• 查看数据库中的所有表：

  show tables;
  

• 查看表结构：

  desc 表名;
  

• 删除表

  drop table 表名;
  

• 修改表结构格式：了解

  alter table 表名 add 列名 类型(长度) [约束]; -- 添加列。
  alter table 表名 modify 列名 类型 (长度) [约束]; -- 修改列的类型长度及约束。
  alter table 表名 change 旧列名 新列名 类型(长度) [约束]; -- 修改列名。
  alter table 表名 drop 列名; -- 删除列。
  rename table 表名 to 新表名; -- 修改表名。
  alter table 表名 character set 字符集; 修改表的字符集
  

DML数据操作语言

• 插入表记录：insert

  • 语法：

    • 向表中插入某些字段

      insert into 表 (字段1,字段2,字段3..) values (值1,值2,值3..); 
      

    • 向表中插入所有字段,字段的顺序为创建表时的顺序

      insert into 表 values (值1,值2,值3..);
      

  • 注意：

    • 值与字段必须对应，个数相同，类型相同。
    • 值的数据大小必须在字段的长度范围内。
    • 除了数值类型外，其它的字段类型的值必须使用引号引起（建议单引号）。
    • 如果要插入空值，可以不写字段，或者插入 null。

• 更新表记录：update

  • 语法：

    update 表名 set 字段名=值,字段名=值,... where 条件;
    

  • 注意：
    • 列名的类型与修改的值要一致。
    • 修改值得时候不能超过最大长度。
    • 除了数值类型外，其它的字段类型的值必须使用引号引起（建议单引号）。

• 删除记录：delete

  • 语法：

    delete from 表名 [where 条件];
    truncate table 表名;
    

    面试题：

    delete -- 一条一条删除，不清空auto_increment记录数。
    truncate -- 直接将表删除，重新建表，auto_increment将置为零，从新开始。
    

DOS操作数据乱码解决

解决方案1：在cmd命令窗口中输入命令，此操作当前窗口有效，为临时方案。
set names gbk;
解决方案2：安装目录下修改my.ini文件，重启服务所有地方生效。[不建议]
乱码原因:因为mysql的客户端设置编码是utf8,而系统的cmd窗口编码是gbk

DQL数据查询语言

• 语法：

  select [distinct] *|列名,列名 from 表 where 条件
  

• 简单查询

1. 查询所有的商品.

   select * from product;
   

2. 查询商品名和商品价格.

   select pname,price from product;
   

3. 别名查询.使用的关键字是as（as可以省略的，建议不省略）.

• 表别名:

  select * from product as p;
  

• 列别名：

  select pname as pn from product;
  

4. 去掉重复值.

   select distinct price from product;
   

5. 查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示.

   select pname,price+10 from product;
   

• 比较运算符

>		<	<=		>=	=	<>	!=     
大于、小于、大于(小于)等于、不等于

BETWEEN ...AND...	显示在某一区间的值(含头含尾)
IN(set)	显示在in列表中的值，例：in(100,200)
LIKE '张pattern'    模糊查询，Like语句中，%代表零个或多个任意字符，_代表一个字符，
例如：first_name like '_a%';
IS NULL	判断是否为空；非空：IS NOT NULL

• 逻辑运算符

and   多个条件同时成立
or    多个条件任一成立
not   不成立，例：where not (salary>100);

• 排序

select * from 表名 order by 排序字段 asc|desc;

asc 升序 (默认)
desc 降序

显示商品的价格(去重复)，并排序(降序)

select distinct price from product order by price desc;

• 聚合函数

count()：统计指定列不为null的记录行数；
sum()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
max()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
min()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
avg()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

• 分组

select 字段1,字段2… from 表名 where 条件 group by 分组字段 having 条件;

having与where的区别:

having是在分组后对数据进行过滤，后面可以使用分组函数(统计函数)。
where是在分组前对数据进行过滤，后面不可以使用分组函数。

• 分页查询

select 字段1，字段2... from 表名 limit m,n

m: 整数，表示从第几条索引开始，计算方式 （当前页-1）*每页显示条数
n: 整数，表示查询多少条数据

• 主键约束

主键必须包含唯一的值。
主键列不能包含 null 值。
每个表都应该有且只能有一个主键。

• 添加主键约束

  • 方式一：创建表时，在字段描述处，声明指定字段为主键

    create table persons
    (
        id int(11) primary key
    );
    

  • 方式二：创建表时，在constraint约束区域，声明指定字段为主键

    • 格式：[constraint 名称] primary key (字段列表)

      • 关键字constraint可以省略，如果需要为主键命名，constraint不能省略，主键名称一般没用。

      • 字段列表需要使用小括号括住，如果有多字段需要使用逗号分隔。
        注:声明两个以上字段为主键，我们称为联合主键。

        create table persons
        (
            firstname varchar(255),
            lastname  varchar(255),
            address   varchar(255),
            constraint personid primary key (firstname, lastname)
        );
        

        create table persons
        (
            firstname varchar(255),
            lastname  varchar(255),
            primary key (firstname)
        );
        

  • 方式三：创建表之后，通过修改表结构，声明指定字段为主键：

    alter table persons add [constraint 名称] primary key (字段列表);
    

• 删除主键约束

  alter table persons drop primary key;
  

• 自动增长列类型必须是整形，自动增长列必须为键(一般是主键)。

  create table persons(id int primary key auto_increment);
  

  默认地，auto_increment 的开始值是 1，如果希望修改起始值

  alter table persons auto_increment=100;
  

• 唯一约束

unique 约束唯一。
unique 和 primary key 约束均为列或列集合提供了唯一性的保证。
primary key 拥有自动定义的 unique 约束。

请注意，每个表可以有多个 unique 约束，但是每个表只能有一个 primary key 约束。

如需删除约束，请使用下面的 sql：

alter table persons drop index名称

如果添加唯一约束时，没有设置约束名称，默认是当前字段的字段名。

唯一约束与主键约束的区别：
唯一：唯一、可以有空值，但只能有一个空值。一个表可以有多个唯一约束。
主键：唯一、不能为空、一个表只能有一个主键，非业务数据

• 多表查询

1. 交叉连接查询(基本不会使用-得到的是两个表的乘积) [了解]

select * from A,B;

AxB所形成的集合就叫笛卡尔积

2. 内连接查询(使用的关键字 inner join – inner可以省略)

   -- 隐式内连接
   select * from A,B where 条件;
   -- 显示内连接
   select * from A inner join B on 条件;
   

3. 外连接查询(使用的关键字 outer join – outer可以省略)

   -- 左外连接：left outer join
   select * from A left outer join B on 条件;
   -- 右外连接：right outer join
   select * from A right outer join B on 条件;
   

4. 子查询：一条select语句结果作为另一条select语法一部分（查询条件，查询结果，表等）。

   select ...查询字段... from ...表.. where ... 查询条件