每天学一点之MySQL的基本操作

MySQL的基本操作

一、MySQL的语法规范和要求

（1）mysql的sql语法不区分大小写

MySQL的关键字和函数名等不区分大小写，但是对于数据值是否区分大小写，和字符集与校对规则有关。

ci（大小写不敏感），cs（大小写敏感），_bin（二元，即比较是基于字符编码的值而与language无关，区分大小写）

（2）命名时：尽量使用26个英文字母大小写，数字0-9，下划线，不要使用其他符号

studentName–>student_name

（3）建议不要使用mysql的关键字等来作为表名、字段名等，如果不小心使用，请在SQL语句中使用`（飘号）引起来

（4）数据库和表名、字段名等对象名中间不要包含空格

（5）同一个mysql软件中，数据库不能同名，同一个库中，表不能重名，同一个表中，字段不能重名

（6）标点符号：

必须成对

必须英文状态下半角输入方式

字符串和日期类型必须使用单引号’ ’

列的别名可以使用双引号" "，给表名取别名不要使用双引号。取别名时as可以省略

如果列的别名没有包含空格，可以省略双引号，如果有空格双引号不能省略。

（7）SQL脚本中如何加注释

单行注释：#注释内容

单行注释：–空格注释内容 其中–后面的空格必须有

多行注释：/* 注释内容 */

#以下两句是一样的，不区分大小写
show databases;
SHOW DATABASES;

#创建表格
#create table student info(...); #表名错误，因为表名有空格
create table student_info(...); 

#其中name使用``飘号，因为name和系统关键字或系统函数名等预定义标识符重名了。
CREATE TABLE t_stu(
    id INT,
    `name` VARCHAR(20)
);

select id as "编号", `name` as "姓名" from t_stu; #起别名时，as都可以省略
select id as 编号, `name` as 姓名 from t_stu; #如果字段别名中没有空格，那么可以省略""
select id as 编 号, `name` as 姓 名 from t_stu; #错误，如果字段别名中有空格，那么不能省略""

二、SQL分类

DQL（数据查询语言）: 查询语句，凡是select语句都是DQL。
DML（数据操作语言）：insert delete update，对表当中的数据进行增删改。
DDL（数据定义语言）：create drop alter，对表结构的增删改。
TCL（事务控制语言）：commit提交事务，rollback回滚事务。(TCL中的T是Transaction)
DCL（数据控制语言）: grant授权、revoke撤销权限等。

三、MySQL数据类型

• 整型系列：xxxInt

  int(5)，必须和zerofill一起使用才有意义，单独的写范围没有意义。
  unsigned：无符号字符，将整形的范围变为从0开始，同时所对应的范围也会发生变化
  zerofill：0填充，当设置了整形的宽度时，若为该字段所设置的值不够宽度的要求，则会在数据之前补0，以达到宽度的要求

• 浮点型系列：float,double

  double(M,D)：表示最长为M位，其中小数点后D位
  算术运算时会损失精度
  例如：double(5,2)表示的数据范围[-999.99,999.99]，如果超过这个范围会报错。

• 定点型系列：decimal

  decimal(M,D)：表示最长为M位，其中小数点后D位

• 字符串类型：char,varchar(M),text

  char如果没有指定宽度，默认为1个字符
  varchar(M)，必须指定宽度

• 日期时间类型：year, date, datetime, timestamp
  

• 其他类型：bit（0或1）, xxBlob, 枚举，集合等

  TinyBlob 最大 255B
  Blob 最大 65K
  MediumBlob 最大 16M
  LongBlob 最大 4G

四、MySQL运算符

/*一、运算符
1、算术运算符
+：加
-：减
*：乘
/：除   可以保留小数部分
div：除  如果整数与整数相除只保留整数部分
%：求余数
mod：求余数
*/
select 1+1; #2
select 1/2; #0.5
select 1 div 2; #0
#查询员工的年薪？（字段可以参与数学运算。）
		select ename,sal * 12 from emp;
		+--------+----------+
		| ename  | sal * 12 |
		+--------+----------+
		| SMITH  |  9600.00 |
		| ALLEN  | 19200.00 |
		| WARD   | 15000.00 |
		| JONES  | 35700.00 |
		+--------+----------+

/*
2、比较运算符
>：大于
<：小于
=：等于  注意区别，Java中是==,mysql中是=
>=：大于等于
<=：小于等于
!=：不等于
<>：不等于
<=>：安全等于  用于判断null值的比较运算符
		null值的判断，习惯上我们用is null 和is not null
*/

/*条件查询。
	语法格式：
		select 
			字段,字段...
		from
			表名
		where
			条件;

	执行顺序：先from，然后where，最后select
*/

#查询薪资大于20000元的员工
select * from t_employee where salary > 20000; #实际开发中不建议使用*，效率较低。

#查询所有男员工
select * from t_employee where gender = '男';
select * from t_employee where gender != '女';
select * from t_employee where gender <> '女';

#查询奖金比例commision_pct是null的员工
select  * from t_employee where commission_pct <=> null;
select  * from t_employee where commission_pct is null;

/*
3、逻辑运算符
&&和and：逻辑与
	两个条件同时满足
||和or：逻辑或
	两个条件满足任意一个
^和xor：逻辑异或
	两个条件只能满足其中一个
!和not：
	不满足xx条件
	*/
#查询薪资大于20000元的女员工	
select * from t_employee where salary > 20000 && gender = '女';
select * from t_employee where salary > 20000 and gender = '女';
#and和or联合起来用：找出薪资大于1000的并且部门编号是20或30部门的员工。
select ename,sal,deptno from emp where sal > 1000 and (deptno = 20 or deptno = 30);
#注意：当运算符的优先级不确定的时候加小括号。

#查询男员工
select * from t_employee where not gender = '女';
select * from t_employee where !(gender = '女');

#查询薪资大于10000  异或 性别是男的，即它俩只能满足一个
#即查询薪资大于10000的女的或薪资低于10000的男的
select * from t_employee where salary>10000 ^ gender ='男';
select * from t_employee where salary>10000 xor gender ='男';
/*
4、范围
（1）区间范围：
	在[a,b]之间，between a and b 在使用的时候必须左小右大,还可以使用在字符串方面。
	不在[a,b]之间，not between a and b
（2）集合范围
	in(...)  in等同于or in后面的值不是区间，是具体的值。
	not in(...) 不在这几个值当中。
*/
#查询薪资在[15000,20000]之间的员工
select * from t_employee where salary between 15000 and 20000;
select * from t_employee where salary >= 15000 and salary <=20000;

#查询薪资为9000,10000,12000的员工
select * from t_employee where salary in(9000,10000,12000);
select * from t_employee where salary =9000 || salary =10000 || salary =12000;
 /*
5、模糊查询
like '%x%' x代表确定的字符 %表示不确定的0~n个字符
     '_x%'  x代表确定的字符 _表示确定的1个字符
*/
#查询，名字ename中包含“冰”这个字的员工
select * from t_employee where ename like '%冰%';

#查询，名字ename是张xx，三个字
select * from t_employee where ename like '张__';

#查询，名字ename是第二个字是'冰'
select * from t_employee where ename like '_冰%';

#找出名字中有下划线的？ *使用转译字符
select name from t_user where name like '%\_%';

/*
 6、位运算符（很少使用）
 左移：<<
 右移：>>
 按位与：&
 按位或：|
 按位异或：^
*/

/*
7、特殊的null值处理
 #（1）判断时
在数据库当中NULL不是一个值，代表什么也没有，为空。
空不是一个值，不能用等号衡量。
必须使用 is null或者is not null
 xx <=> null

 #(2)计算时
 ifnull(xx,代替值)  当xx是null时，用代替值计算
*/
select eid,ename,salary*(1+ifnull(kpi,0)) as salary from t_employee;
+-----+--------+--------------------+
| eid | ename  | salary             |
+-----+--------+--------------------+
|   1 | 张三   |               1227 |
|   2 | 李四   |            25369.2 |
|   3 | 王五   |            11181.6 |
|   4 | 赵六   |           34319.74 |
|   5 | 田七   |            9310.64 |
|   6 | 崔八   |           36926.28 |
|   7 | 杨九   |               2425 |
|   8 | 杨迪   |               2595 |

五、DDL

#1、查看所有数据库
show databases;

#2、指定使用某个数据库
use 数据库名;

#3、创建数据库
create database 数据库名;
create database 数据库名 charset 'utf8';#在MySQL中字符集名称不要使用utf-8
create database 数据库名 charset 'gbk';

#4、删除数据库
drop database 数据库名;

#5、查看某库下的所有表格
show tables;#前提是前面有use 数据库名;的语句
show tables from 数据库名;

#6、创建表格
create table [数据库名.] 表名(  #中括号内在没有明确在哪个数据库的情况下指定创建表
       字段名1 数据类型,
       字段名2 数据类型,
       ...
);
#7、删除表格
drop table [数据库名.]表名;

#8、查看某个表结构
describe [数据库名.]表名;
desc [数据库名.]表名;

#9、增加一列   []内的内容表示可选
alter table [数据库名.]表名 add [column] 字段名 数据类型;
alter table [数据库名.]表名 add [column] 字段名 数据类型 first;
alter table [数据库名.]表名 add [column] 字段名 数据类型 after 另一个字段;

#10、删除一列
alter table [数据库名.]表名称 drop [column] 字段名;

#11、修改列数据类型
alter table [数据库名.]表名称 modify [column] 字段名 新数据类型;

#12、修改列名
alter table [数据库名.]表名称 change[column] 旧字段名 新字段名 新数据类型;

#13、修改列的位置
alter table [数据库名.]表名称 modify [column]字段名 数据类型 first;
alter table [数据库名.]表名称 modify [column]字段名 数据类型 after 另一个字段;

#14、修改表名称
alter table 旧表名 rename 新表名;
rename table 旧表名 to 新表名;

六、DML

• 1、添加数据

insert into [数据库名.]表名称 values(值列表);
#要求值列表的顺序、个数、类型，要与表格中的字段的顺序、个数、类型一一匹配

insert into [数据库名.]表名称 (部分字段列表) values(值列表);
#要求列表的顺序、个数、类型，要与前面的(部分字段列表)的顺序、个数、类型一一匹配

#批量添加
insert into 表名 values(值,值,...),(值,值,...),(值,值,...),...
insert into 表名(字段,字段,...) values(值,值,...),(值,值,...),(值,值,...),...

注意：SQL语句中的字符串类型和日期类型需要使用单引号

• 2、修改数据

update 表名 set 字段=值,... where 条件;
#将薪资小于10000的员工的薪资加上10000
update t_employee set salary=salary+10000 where salary<10000; 

• 3、删除数据

delete from 表名 where 条件;
#将eid为1的员工删除
delete from t_employee  where eid=1;

注意：
清空表的两种方式：
delete from 表名;
truncate 表名; -->本质是在底层先将表删除，再重新创建
因此truncate清空表的操作是无法通过事务进行回滚的，因为事务只针对于DML语句

七、数据库的导入和导出

1、导入
先登录mysql，然后执行如下命令：

mysql> source sql脚本路径名.sql

注意：
若在命令行中导入数据出现乱码问题，是因为数据库编码为UTF8，但是dos目录窗口所使用的编码为GBK，此时可以在命令行中使用"set names GBK"解决此问题

2、导出

C:\Windows\System32> mysqldump -h主机地址 -P端口号 -u用户名 -p密码 数据库名 > 文件路径/文件名.sql