mysql数据库基础

mysql数据库设置远程连接权限

# 1.授权（全部数据库权限）
grant all privileges on *.* to root@'%' identified by "root账号的密码" with grant option;

# 2.刷新权限
flush privileges;

MySQL的语法规范

1.不区分大小写,但建议关键字大写，表名、列名小写
2.每条命令最好用分号结尾
3.每条命令根据需要，可以进行缩进 或换行
4.注释
	单行注释：#注释文字
	单行注释：-- 注释文字
	多行注释：/* 注释文字  */

数据库的操作

创建数据库
CREATE DATABASE book;
删除数据库
DROP DATABASE book;
查看所有数据库
SHOW DATABASES;
查看数据库字符集
SHOW CREATE DATABASE book;
修改数据库的字符集
ALTER DATABASE book CHARACTER SET utf8;
查看当前选择的数据库
SELECT DATABASE();

• 字符集：

latin1：支持西方国家语言，不支持中文
UTF-8：支持所有国家的语言，也叫万国码
ASCII：美国标准信息交换代码，仅支持128个字符
GBK：支持简体和繁体中文，汉语基本集
GB2312：支持简体中文
BIG5：支持繁体中文，适用港澳台

数值类型

• 1.整型：

类型	大小	范围	用途
tinyint	1bytes	(-128,127)	小整数值
smallint	2bytes	(-32 768，32 767)	大整数值
mediumint	3bytes	(-8 388 608，8 388 607)	大整数值
int	4bytes	(-2 147 483 648，2 147 483 647)	大整数值
bigint	8bytes	(-9 223 372 036 854 775 808，9 223 372 036 854 775 807)	极大整数值

• 2.浮点数：

类型	大小	范围	用途
float	1bytes	8位精度	单精度浮点数值
double	2bytes	16位精度	双精度浮点数值
decimal	对decimal(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	小数值

• 3.日期和时间类型：

类型	大小	范围	格式
date	3bytes	1000-01-01/9999-12-31	YYYY-MM-DD
time	3bytes	‘-838:59:59’/‘838:59:59’	HH:MM:SS
year	1bytes	1901/2155	YYYY
datetime	8bytes	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS
timestamp	4bytes	1970-01-01 00:00:00/2038	YYYYMMDD HHMMSS

• 4.字符串类型：

类型	大小	用途
char	0-255 bytes	定长字符串
varchar	0-65535 bytes	变长字符串
tinyblob	0-255 bytes	不超过 255 个字符的二进制字符串
tinytext	0-255 bytes	短文本字符串
blob	0-65 535 bytes	二进制形式的长文本数据
text	0-65 535 bytes	长文本数据
mediumblob	0-16 777 215 bytes	二进制形式的中等长度文本数据
mediumtext	0-16 777 215 bytes	中等长度文本数据
longblob	0-4 294 967 295 bytes	二进制形式的极大文本数据
longtext	0-4 294 967 295 bytes	极大文本数据

表的操作

1.表的创建 
语法：
create table 表名(
	列名 列的类型【(长度) 约束】,
	列名 列的类型【(长度) 约束】,
	...
)
2.删除表：drop table 表名;;
3.修改表名：alter table 表名 rename 新表名;
4.修改某个字段的名称和数据类型：alter table 表名 change 原列名 新列名 新列数据类型;
5.仅修改某个字段的数据类型：alter table 表名 modify 列名 新数据类型;
6.增加一列：alter table 表名 add 列名 数据类型;
7.删除一列：alter table 表名 drop 列名;
8.修改表的字符集 ：alter table 表名 convert to character set utf8;
9.查看表的字符集：show create table 表名;
10.查看表的数据结构：desc 表名;
11.查询所有表：show tables;

# 删除表的内容，保留表结构
方式1：delete from 表名;
方式2：truncate table 表名

# delete与truncate区别：
1.delete 可以加where 条件，truncate不能加
2.truncate删除，效率高一丢丢
3.假如要删除的表中有自增长列，如果用delete删除后，再插入数据，自增长列的值从断点开始，
而truncate删除后，再插入数据，自增长列的值从1开始。
4.truncate删除没有返回值，delete删除有返回值
5.truncate删除不能回滚，delete删除可以回滚.

# 例：创建students表，表中存放学号（id）、姓名（name）、性别（sex）、年龄（age）、联系电话（tel）
CREATE TABLE students
(
	id int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name char(8) NOT NULL,
	sex char(4) NOT NULL,
	age tinyint UNSIGNED NOT NULL,
	tel char(13) NULL DEFAULT "-"
);
# unsigned：表示为无符号型 
# NOT NULL：说明该列的值不能为空, 必填
# AUTO_INCREMENT：默认地，AUTO_INCREMENT 的开始值是 1，每条新记录递增 1，以可以其他的值起始
# PRIMARY KEY ：表示表的主键, 本列的值必须唯一
# default：默认值，在不设置值的情况下取该值

数据的增删改

插入：insert
修改：update
删除：delete

1.插入语句
	方式一：插入所有列的数据
	语法：insert into 表名 values（所有列对应的值）;
	注意：复制顺序要和列的顺序一致，且数据类型要符合列的定义
	方式二：插入指定列的数据：
	语法：insert into 表名（列1，列2...） values （列1的值，列2的值）;
	方式三：使用set
	语法：insert into 表名 set 列名=值,列名=值,...;
	方式四：批量插入
	语法：insert into 表名（列名） values （第一行数据）,（第二行数据）;
2.修改语句
	语法：update 表名 set 列1=新值,列2=新值... where 条件;
3.删除语句
	语法：delete from 表名 where 条件;

运算符

• 在 MySQL 中1（TRUE）、0（FALSE）和 NULL
• 算术运算符：

运算符	说明
+	加法运算
-	减法运算
*	乘法运算
/ 或 DIV	除法运算，返回商
% 或 MOD	求余运算，返回余数

• 注意：mysql中的+号，仅仅只有一个功能运算符

select 100+90; #两个操作数都为数值型，则做加法运算
select '123'+90;#只要其中一方为字符型，试图将字符型数值转换成数值型
				#如果转换成功，则继续做加法运算
select 'john'+90; #如果转换失败，则将字符型数值转换成0
select null+10;  #只要其中一方为null，则结果肯定为null

• 比较运算符：

运算符	说明
=	等于
<	小于
<=	小于等于
>	大于
>=	大于等于
<=>	安全的等于，不会返回 UNKNOWN
<> 或!=	不等于
IS NULL 或 ISNULL	判断一个值是否为 NULL
IS NOT NULL	判断一个值是否不为 NULL
LEAST	当有两个或多个参数时，返回最小值
GREATEST	当有两个或多个参数时，返回最大值
BETWEEN AND	判断一个值是否落在两个值之间
IN	判断一个值是IN列表中的任意一个值
NOT IN	判断一个值不是IN列表中的任意一个值
LIKE	通配符匹配
REGEXP	正则表达式匹配

• 逻辑运算符：

运算符	说明
NOT 或者 !	逻辑非
AND 或者 &&	逻辑与
OR 或者 ll	逻辑或
XOR	逻辑异或

• 位运算符：

运算符	说明
l	按位或
&	按位与
^	按位异或
<<	按位左移
>>	按位右移
~	按位取反，反转所有比特

• 运算符优先级：

优先级由低到高排列	运算符
1	=(赋值运算）、:=
2	II、OR
3	XOR
4	&&、AND
5	NOT
6	BETWEEN、CASE、WHEN、THEN、ELSE
7	=(比较运算）、<=>、>=、>、<=、<、<>、!=、 IS、LIKE、REGEXP、IN
8	l
9	&
10	<<、>>
11	-(减号）、+
12	*、/、%
13	^
14	-(负号）、〜（位反转）
15	!

• 可以看出，不同运算符的优先级是不同的。一般情况下，级别高的运算符优先进行计算，如果级别相同，MySQL 按表达式的顺序从左到右依次计算。
• 另外，在无法确定优先级的情况下，可以使用圆括号“()”来改变优先级，并且这样会使计算过程更加清晰。

起别名

• 1.便于理解
• 2.如果要查询的字段有重名的情况，使用别名可以区分开来

 #方式一：使用as
SELECT last_name AS 姓,first_name AS 名 FROM employees;

#方式二：使用空格
SELECT last_name 姓,first_name 名 FROM employees;

去重

#案例：查询员工表中涉及到的所有的部门编号
SELECT DISTINCT department_id FROM employees;

约束类型

约束类型	主键	外键	唯一	非空	自增	默认
关键字	primary key	foreign key	unique	not null	auto_increment	default

• 1.主键约束 primary key：
  主键约束相当亍： 唯一约束 + 非空约束 的组合，主键约束列不允许重复，也不允许出现空值。数据库的设计规范要求每张表都需要创建一个主键，一般第一列的值为主键，不为空。一定要赋值，并且是唯一的。每个表最多叧允许一个主键
  如果在工作当中，没有适合做主键的字段，需要自己定义一个 id 作为主键，因为通过主键查询速度快。
• 2.唯一约束 Unique：
  要求这一列的值唯一，但是可以允许为空(如：id_card 身份证号码)
• 3.非空约束 not null：
  该值不能为 null，一定要赋值
  这意味着，如果不向字段添加值，就无法插入新记录或者更新记彔
• 4.默认约束 default：
  当插入记录时，如果没有明确为字段赋值，则自动赋予默认值
• 5.外键约束 foreign key：
  从表中某一列的数据必须在主表中存在
  外键约束主要用亍 2 张表的关联，分为主表和从表。
  如：老师表（主表）和学生表（从表），从表要有主表的信息-从表中某一列的数据必须在主表中存在