Mysql基础

学习实验楼中MySQL基础课程过程中的笔记link
SQL= Structured Query Language

打开及查看

打开MySQL

sudo service mysql start  # 启动 MySQL 服务
mysql -u root   # 使用 root 用户登录

查看数据库

show databases;

连接数据库并查看表

use 某个数据库的名字；
show tables; 查看数据库中有哪些表

退出

使用quit或者exit

创建数据库并插入数据

新建数据库

create database 数据库名字；

删除数据库

drop table 表名

新建数据表

CREATE TABLE 表的名字
(
列名a 数据类型(数据长度),
列名b 数据类型(数据长度)，
列名c 数据类型(数据长度)
);

CREATE TABLE employee
(
 id int(10),
 name char(20),
 phone int(12)
 );

删除数据表

drop table 表名

重命名表

RENAME TABLE 原名 TO 新名字;

ALTER TABLE 原名 RENAME 新名;

ALTER TABLE 原名 RENAME TO 新名;

数据类型的介绍

link1
link2

查看表中的内容

select * from 表的名字；

插入数据

INSERT INTO 表的名字(列名a,列名b,列名c) VALUES(值1,值2,值3);

INSERT INTO employee(id,name,phone) VALUES(01,'Tom',110110110);

INSERT INTO employee VALUES(02,'Jack',119119119);

INSERT INTO employee(id,name) VALUES(03,'Rose');

SQL的约束

约束类型

主键 primary key
默认值 default
唯一 unique
外键 foreign key
非空 not null

主键

用于约束表中的一行，作为这一行的唯一标识符，在一张表中通过主键就能准确定位到一行，因此主键十分重要，不能有重复记录且不能为空

id int(10） PRIMARY KEY  #一种方法
CONSTRAINT 主键名（自定义）PRIMARY KEY(id) #另一种
PRIMARY KEY(id) #另一种
CONSTRAINT 主键名（自定义）PRIMARY KEY(id,name) #复合主键

默认值

有DEFULT约束的列，插入数据为空时，将使用默认值

唯一约束

UNIQUE约束的列不能有重复值

外键

相当于时一个参考的链接，确保了数据的完整性，也能体现表之间的关系
一个表可以有多个外键，每个外键必须参考另一个表的主键，被外键约束的列，取值必须在他参考的列中有对应的值

CONSTRAINT 外键名字（自定义） FOREIGN KEY age REFERENCES department(name)

FOREIGN KEY(age） REFERENCES department(name) #另一种

非空约束

NOT NULL

ID自增

AUTO_INCREMENT

select 语句

基本语句

select 要查询的列名1，列名2 from 表名字 where 限制条件；
select * from table_name；

数学符号条件

用=,<,>,>=,<=

AND 与 OR

age>25 AND age<30
或者
age BETWEEN 25 AND 30

IN 和 NOT IN

in_dpt IN (‘dpt3’,‘dpt4’)

通配符

关键字LIKE用于实现模糊查询，和通配符连用
SQL中的通配符是_（一个未指定字符）和%（不定个未指定字符）
phone like ‘1101__’ 共八位

对结果排序

ORDER BY 列名 ASC/DESC/默认升序

SQL内置函数和计算

1.COUNT 一般和group by 配合使用
2.SUM
3.AVG
4.MAX
5.MIN
eg:

SELECT MAX(salary) AS max_salary,MIN(salary) FROM employee;

子查询

查询多个表才能获取信息

SELECT of_dpt,COUNT(proj_name) AS count_project FROM project GROUP BY of_dpt
HAVING of_dpt IN
(SELECT in_dpt FROM employee WHERE name='Tom');

HAVING关键字和where是一样的
第二个select语句将返回一个集合的数据形式，然后被第一个select语句用in进行判断

连接查询

子查询只有在结果来自一个表或多个表时才有用
如多需要多个表，需要用连接操作

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;

对表的修改

增加一列

ALTER TABLE 表名字 ADD COLUMN 列名字 数据类型 约束 位置;
或：
ALTER TABLE 表名字 ADD 列名字 数据类型 约束 位置;

after 。。。或 first

删除一列

ALTER TABLE 表名字 DROP COLUMN 列名字;
或： 
ALTER TABLE 表名字 DROP 列名字;

重命名，修改数据类型和约束一列

ALTER TABLE employee CHANGE height shengao INT(4) DEFAULT 170;

改变数据类型

ALTER TABLE 表名字 MODIFY 列名字 新数据类型;

修改表中某个值

UPDATE 表名字 SET 列1=值1,列2=值2 WHERE 条件;

删除一行记录

DELETE FROM 表名字 WHERE 条件;

索引

创建索引

ALTER TABLE 表名字 ADD INDEX 索引名 (列名);

CREATE INDEX 索引名 ON 表名字 (列名);

显示索引

show index from 表名字

视图

视图是从一个或多个表中导出来的表，是一种虚拟存在的表。
1.数据库中只存放了视图的定义，而没有存放视图中的数据，这些数据参访在原来的表中
2.视图中的数据依赖于原来表中的数据，一旦表中数据发生该表，显示在视图中的数据也会改变；
3.在使用视图时可以把它当作一张表

CREATE VIEW 视图名(列a,列b,列c) AS SELECT 列1,列2,列3 FROM 表名字;

导入导出

导入导出都要在指定的路径下进行，在mysql终端查看路径变量
要导入数据文件，需要将该文件移动到安全路径下
导入

sudo cp -a /home/shiyanlou/Desktop/SQL6 /var/lib/mysql-files/
sudo vim /var/lib/mysql-files/SQL6/in.txt

mysql -uroot;
use mysql_shiyan;
load data infile '/var/lib/mysql-files/SQL6/in.txt' into table employee;

导出

SELECT 列1，列2 INTO OUTFILE '文件路径和文件名' FROM 表名字;

备份与恢复

备份与导出的区别：导出的文件知识保存数据库中的数据；而备份，则是把数据库的结构，包括数据、约束、索引、视图等全部另存为一个文件

mysqldump会产生一个sql脚本文件，其中包括从头重新创建数据库所需的命令 creat table insert等
在终端输入的命令

mysqldump -u root 数据库名>备份文件名;   #备份整个数据库

mysqldump -u root 数据库名 表名字>备份文件名;  #备份整个表

用备份的文件可以恢复 数据库

source /tmp/SQL6/MySQL-06.sql

另一种方式
新建一个空的数据库

mysql -u root          #因为在上一步已经退出了MySQL，现在需要重新登录

CREATE DATABASE test;  #新建一个名为test的数据库

退出mysql ，输入语句进行恢复

mysql -u root test < bak.sql

mysql -u root          #因为在上一步已经退出了MySQL，现在需要重新登录

use test               #连接数据库test

SHOW TABLES;           #查看test数据库的表