1.MySQL数据库服务器的开启与关闭:
net start mysql
net stop mysql
2.退出命令行客户端:
quit
3.创建数据库
CREATE DATABASE 数据库名称;
4.显示已经有的数据库
SHOW DATABASES;
5.打开数据库
USE 数据库名称;
6.在数据库中建立表
先必须打开需要建立表的数据库,然后执行如下命令:
CREATE TABLE 表名称
(
列名1 数据类型 【列表项】,
列名2 数据类型 【列表项】,
列名3 数据类型 【列表项】,
。。。
);
create table admins(
username char(20) not null,
password varchar(20) not null
)ENGINE=MyISAM DEFAULT CHARSET=gb2312;//加上可使用中文
7.显示数据库中的表
SHOW TABLES;
8.往表中插入数据
INSERT INTO admins(username,password) VALUES(‘王军霞‘,‘123456‘);
INSERT INTO admins VALUES(‘王军祥’,‘012345’);
Int、float、double等类型的数据不需要单引号修饰,varchar、text、date、time、enum、等类型的数据需要单引号修饰。
9.查看表中的记录
SELECT username,password FROM admins where username=‘王军霞‘;
SELECT username FROM admins;
SELECT * FROM admins;
10.查看表的结构
EXPLAIN admins;
DESCRIBE admins;
11.修改表中记录的数据值
UPDATE admins SET password=‘654321‘ WHERE username=‘王军霞‘;
12.删除数据库表中的记录
DELETE FROM admins WHERE username=‘王军霞‘;
Delete删除单个数据
13.删除表
DROP TABLE admins;
14.删除数据库
DROP DATABASE tushuguanli;
15.恢复数据库;
恢复数据库:找到所在目录 进入cmd备份
mysqldump -uroot -p123456 --opt test1>d:\test1.sql
进入数据库;
SOURCE 数据库目录;
16.SQL的约束
(1) 主键 (PRIMARY KEY)是用于约束表中的一行,作为这一行的唯一标识符,在一张表中通过主键就能准确定位到一行,因此主键十分重要,主键不能有重复记录且不能为空。
CREATE TABLE admins(
id int PRIMARY KEY,
);
复合主键
CREATE TABLE admins(
id int,
name char(20),
PRIMARY KEY(id,name)
);
创建后再设置主键
CREATE TABLE admins(
id int not null,
name char(20)
);
ALTER TABLE admins ADD PRIMARY KEY(id);
(2) 默认值约束 (DEFAULT) 规定,当有 DEFAULT 约束的列,插入数据为空时,将使用默认值。
height int(10) DEFAULT’180’,
(3) 唯一约束 (UNIQUE) 比较简单,它规定一张表中指定的一列的值必须不能有重复值,即这一列每个值都是唯一的。
UNIQUE (phone),
(4) 外键 (FOREIGN KEY) 既能确保数据完整性,也能表现表之间的关系。一个表可以有多个外键,每个外键必须 REFERENCES (参考) 另一个表的主键,被外键约束的列,取值必须在它参考的列中有对应值。
CONSTRAINT emp_fk FOREIGN KEY(in_dpt) REFERENCES department(dpt_name);
(5) 非空约束 (NOT NULL)被非空约束的列,在插入值时必须非空。
age int(10) NOT NULL,
17.SELECT语句详解
WHERE语句可以使用数学符号(=,<,>,<=,>=),还可以用AND和OR 连接
SELECT name,age FROM employee WHERE age<25 OR age>30;
SELECT name,age FROM employee WHERE age>25 AND age<30;
SELECT name,age FROM employee WHERE age BETWEEN 25 AND 30;
关键词 IN 和 NOT IN 的作用和它们的名字一样明显,用于筛选“在”或“不在”某个范围内的结果,比如说我们要查询在 dpt3 或 dpt4 的人:
SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt IN (‘dpt3’,’dpt4’);
SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt NOT IN (‘dpt2’,’dpt1’);
关键字 LIKE 可用于实现模糊查询,常见于搜索功能中。和 LIKE 联用的通常还有通配符,代表未知字符。SQL中的通配符是 _ 和 %。其中 _ 代表一个未指定字符,% 代表不定个未指定字符
SELECT name,age,phone FROM employee WHERE phone LIKE ‘1101__’;
SELECT name,age,phone FROM employee WHERE name LIKE ‘J%’;
默认情况下,ORDER BY 的结果是升序排列,而使用关键词 ASC 和 DESC 可指定升序或降序排序。
SELECT name,age,salary,phone FROM employee ORDER BY salary DESC;
SQL 允许对表中的数据进行计算。对此,SQL 有 5 个内置函数,这些函数都对 SELECT 的结果做操作:
其中 COUNT 函数可用于任何数据类型(因为它只是计数),而 SUM 、AVG 函数都只能对数字类数据类型做计算,MAX 和 MIN 可用于数值、字符串或是日期时间数据类型
SELECT MAX(salary) AS max_salary, MIN(salary) FROM employee;
AS 关键词可以给显示的值重命名
上面讨论的 SELECT 语句都仅涉及一个表中的数据,然而有时必须处理多个表才能获得所需的信息。例如:想要知道名为 "Tom" 的员工所在部门做了几个工程。员工信息储存在 employee 表中,但工程信息储存在 project 表中。
对于这样的情况,我们可以用子查询:
SELECT of_dpt,COUNT(proj_name) AS count_project FROM project GROUP BY of_dpt
HAVING of_dpt IN
(SELECT id_dpt FROM employee WHERE name=’Tom’);
在处理多个表时,子查询只有在结果来自一个表时才有用。但如果需要显示两个表或多个表中的数据,这时就必须使用连接 (join) 操作。 连接的基本思想是把两个或多个表当作一个新的表来操作,如下:
SELECT id,name,people_num
FROM employee,department
WHERE employee.in_dpt = department.dpt_name
ORDER BY id;
用JOIN ON 语法
SELECT id,name,people_num
FROM employee JOIN department
ON employee.in_dpt = department.dpt_name
ORDER BY id;
SELECT dpt_name,people_num,COUNT(proj_name) AS count_project
FROM project,department
WHERE dpt_name = of _dpt GROUP BY of_dpt;
18.重命名表格
RENAME TABLE 原名 TO 新名字;
ALTER TABLE 原名 RENAME 新名字;
ALTER TABLE 原名 RENAME TO 新名字;
19.对一列的修改
增加一列
ALTER TABLE 表名字 ADD COLUMN 列名字 数据类型 约束;
ALTER TAVLE 表名字 ADD 列名字 数据类型 约束;
可以使用AFTER、FIRST等关键字把列放置在指定的位置
删除一列
ALTER TABLE 表名字 DROP COLUMN 列名字;
ALTER TABLE 表名字 DROP 列名字;
重命名一列(修改)
ALTER TABLE 表名字 CHANGE 原列名 新列名 数据类型 约束;
改变数据类型
ALTER TABLE 表名字 MODIFY 列名字 新数据类型;
20.对表内容的修改
修改表中某个值
UPDATA 表名字 SET 列1=值1,列2=值2 WHERE 条件;
UPDATA employee SET age=19,salary=3000 WHERE name=’Tom’;
一定要加WHERE条件
删除一行记录
DELECT FROM 表名字 WHERE 条件;
21.其他基本操作
索引
ALTER TABLE 表名字 ADD INDEX 索引名(列名);
CREATE INDEX 索引名 ON 表名字(列名);
ALTER TABLE employee ADD INDEX idx_id(id);
CREATE INDEX idx_name ON employee(name);
查看索引 SHOW INDEX FROM 表名字;
索引可以加快查询速度
视图
CREATE VIEW 视图名(列a,列b,列c) AS SELECT 列1,列2,列3 FROM 表名字;
CREATE VIEW v_emp (v_name,v_age,v_phone ) AS SELECT name,age,phone FROM employee;
视图是一种虚拟存在的表
导入
导入导出的文件都必须在指定的 路径下进行,在mysql终端中查看路径变量:
show variables like ‘%secure%’;
使用Xfce终端将要导入的数据文件移动到安全路径下:
sudo cp -a /home/shiyanlou/Desktop/SQL6 /var/lib/mysql-files/
使用命令sudo vim 文件路径和文件名 可以查看文件中的内容
最后使用导入语句:
LOAD DATA INFILE ‘文件路径和文件名’ INTO TABLE 表名字;
导出
SELECT 导出数据 INTO OUTFILE ‘文件路径和文件名’ FROM 表名字;
注意:语句中 “文件路径” 之下不能已经有同名文件。
备份
mysqldump 是 MySQL 用于备份数据库的实用程序。它主要产生一个 SQL 脚本文件,其中包含从头重新创建数据库所必需的命令 CREATE TABLE INSERT 等。mysqldump 是一个备份工具,因此该命令是在终端中执行的,而不是在 mysql 交互环境下
mysqldump -u root 数据库名>备份文件名; //备份整个数据库
mysqldump -u root 数据库名 表名字>备份文件名; //备份整个表
备份与导出的区别:导出的文件只是保存数据库中的数据;而备份,则是把数据库的结构,包括数据、约束、索引、视图等全部另存为一个文件。
恢复数据库
除了SOURCE 语句还有另一种方法恢复数据库
先新建一个空的数据库test
退出再输入语句进行恢复
Mysql -u root test < 文件名.sql
进阶篇
1.输入查询
版本和当前日期
SELECT VERSION(), CURRENT_DATE;
简单计算
SELECT SIN(PI()/4), (4+1)*5;
MySQL提示符
输入错误时可以使用 \c 取消命令,当出现缺失单引号或双引号时使用 ‘\c 取消命令
2.日期计算
使用TIMESTAMPDIFF函数可以计算当前日期和出生日期之间的差,也可以直接使用语句(YEAR(CURDATE())-YEAR(birth))计算。
TIMESTAMPDIFF:
直接使用语句:
还可以使用类似语句查询死亡年龄,用death IS NOT NULL 排除没有死亡日期的对象
2. 常见函数
概念:类似于java的方法,将一组逻辑语句封装在方法体中,对外暴露方法名。
好处:1、隐藏了实现细节
2、提高代码的重用性
调用:select 函数名(实参列表) from 表;
特点:1、叫什么(函数名)
2、干什么(函数功能)
分类:1、单行函数
字符函数
数学函数
日期函数
其他函数【补充】
流程控制函数【补充】
如:concat、length、ifnull等
3、分组函数
功能:做统计使用,又称为统计函数、聚合函数、组函数
字符函数:
#1.length 获取参数值的字节个数
SELECT LENGTH(‘john’); 4
SELECT LENGTH(‘张三丰hahaha’) 15(一个中文字符算三个字节)
SHOW VARIABLES LIKE’%char%’
#2.concat 拼接字符串
Concatenate 级联
SELECT CONCAT(last_name,’_’,first_name) 姓名 FROM employee;
#3.upper、lower
SELECT UPPER(‘john’); JOHN
SELECT LOWER(‘joHn’); john
#4.substr、substring
注意:索引从1开始
#截取从指定索引处开始后面所有字符
SELECT SUBSTR(‘李莫愁爱上了陆展元’,7) out_put; 陆展元
#截取从指定索引处到指定字符长度的字符
SELECT SUBSTR(‘李莫愁爱上了陆展元’,1,3) out_put; 李莫愁
#5.instr 返回子串第一次出现的索引,如果找不到返回0
SELECT INSTR(‘杨不悔爱上了殷六侠’,’殷六侠’) out_put; 7
SELECT INSTR(‘杨不悔殷六侠爱上了殷六侠’,’殷六侠’) out_put; 4
#6.trim
SELECT TRIM(‘ 张翠山 ’) out_put; 张翠山
SELECT TRIM(‘a’ FROM ‘aaaaa张aaa翠山aaaaaaa’) out_put; 张aaa翠山
#7.lpad 用指定的字符实现左填充指定长度
SELECT LPAD(‘殷素素’,4,’*’) out_put; *殷素素
#8.Rpad 用指定的字符实现右填充指定长度
SELECT RPAD(‘殷素素’,4,’*’) out_put; 殷素素*
#9.replace 替换
SELECT REPLACE(‘张无忌爱上了周芷若’,‘周芷若’,‘赵敏’) out_put; 张无忌爱上了赵敏
3.变量
系统变量:
全局变量 作用域:针对所有会话(连接),但不能跨重启。
会话变量 作用域:仅仅针对于当前会话(连接)有效。
自定义变量:
用户变量 作用域:仅仅针对于当前会话(连接)有效。
局部变量 作用域:仅仅在定义它的BEGIN END 中有效。
#一.系统变量:变量由系统提供,不是用户定义的,属于服务器层面。
- 查看所有的系统变量
SHOW GLOBAL | SESSION VARIABLES;
- 查看满足条件的部分系统变量(模糊查询)
SHOW GLOBAL | SESSION VARIABLES LIKE’%char%’;
- 查看指定的某个系统变量的值
SELECT @@GLOBAL | SESSION . 系统变量名;
- 为某个系统变量赋值
方式一:
SET GLOBAL | SESSION 系统变量名 = 值;
方式二:
SET @@GLOBAL | SESSION . 系统变量名 = 值;
注意:如果是全局级别,则需要加GLOBAL,如果是会话级别,则需要加SEESION ,如果不写,则默认SESSION使用的语法。
#二、自定义变量:变量是用户自定义的,不是由系统设置的。
使用步骤: 1.声明、2.赋值、3.使用(查看、比较、运算等)
用户变量
- 声明并初始化
SET @用户变量名 =值;
SET @用户变量名 :=值;
SELECT @用户变量名 :=值;
- 赋值(更新用户变量的值)
方法一:通过SET 或SELECT
SET @用户变量名 =值;
SET @用户变量名 :=值;
SELECT @用户变量名 :=值;
方法二:通过SELECT INTO
SELECT 字段 INTO @用户变量名 FROM 表;
- 使用(查看用户变量的值)
SELECT @用户变量名;
局部变量
- 声明
DECLARE 局部变量名 类型;
DECLARE 局部变量名 类型 DEFAULT 值;
- 赋值
方法一:通过SET 或SELECT
SET 局部变量名 =值;
SET 局部变量名 :=值;
SELECT @局部变量名 :=值;
方法二:通过SELECT INTO
SELECT 字段 INTO @局部变量名 FROM 表;
- 使用
SELECT 用户变量名;
定义和使用的位置 语法
用户变量 会话中的任何地方 必须加@符号,不用限定类型
局部变量 只能在BEGIN END 中,且为第一句 一般不用加@符号,需要限定类型