SQL 基础操作

目录

 创建数据库

导入数据库

创建表

操作和修改表

事务 

 

SQL方案语句：用于创建数据库对象(表、索引、约束等)的语句

SQL数据语句：用于创键、操作、检索保存在数据库中的数据的语句

SQL事务语句：用于开始、结束、回滚事务

SQL专业术语

实体	数据库用户所关注的对象，如顾客、部门等
列	存储在表中的独立数据片段
行	完整的描述一个实体，也称为记录
表	行的结果，既可以保存在内存中，也可以保存在存储设备中
结果集	一般为SQL查询的结果
主键	用以唯一标识表中每个行的一个或多个列
外键	一个或多个用于识别其他表中某一行的列

 创建数据库

• mysql客户端程序允许你通过用户账户和密码连到网络中任何地方的MySQL数据库服务器。默认情况下，如果你在命令行上输入mysql，且不加任何参数，它会试图用Linux登录用户名连接运行在同一Linux系统上的MySQL服务器。可以使用-u参数指定登录用户名。-p参数告诉mysql程序提示输入登录用户输入密码.用root用户登录MySQL：

 $ mysql -u root -p （database）

• MySQL服务器将数据组织成数据库。数据库通常保存着单个应用程序的数据，与用这个数据库服务器的其他应用互不相关。应该为每个应用程序CREATE一个单独的数据库。创建示例数据库：

 CREATE DATABASE bank；
 CREATE DATABASE IF NOT EXISTS bank DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

• 用root管理员账户连接到MySQL服务器。这个账户可以完全控制所有的MySQL服务器对象，但同样是是极其危险的。明智的做法是在MySQL上创建一个仅对应用中所涉及的数据库有权限的独立用户账户。可以用GRANT语句来完成分配权限操作。创建用户user并赋予数据库权限：新版的的mysql版本已经将创建账户和赋予权限的方式分开了

CREATE user 'user'@ 'localhost' IDENTIFIED BY '123';

GRANT ALL PRIVILEGES ON bank.* TO 'user'@ 'localhost';

GRANT SELECT,INSERT,DELETE,UPDATE    #赋予查询、插入、删除、更新权限
-> ON mytest.*                            #权限作用的数据库和表 database.table
-> TO 'user'@ 'localhost';                           

• 用SQL命令SHOW来显示当前在MySQL服务器上配置过的数据库，然后用SQL命令USE来连接到单个数据库。mysql会话一次只能连一个数据库。连接bank数据库：

>USE bank；
Database changed

MySQL的数据类型

char	定长字符串值(使用空格向右填充)	为数据列指定非默认的字符集：varchar(20) character set utf8 改变整个数据集的默认字符集：create database foreign_sales character set utf8
varchar	变长字符串值
int	整数值
float、double	浮点值
boolean	布尔类型true/false
date	YYYY-MM-DD	1000-01-01 ~ 9999-12-31
time	HHH：mm：ss	-838:59:59 ~ 838:59:59
datetime	YYYY-MM-DD HH：MI：SS	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
timestamp	YYYY-MM-DD HH：MI：SS	1970-01-01 00:00:00 ~ 2037-12-31 23:59:59
text	长字符串值	更大存储空间的有mediumtext和longtext
BLOB	大的二进制值	比如图片或视频

导入数据库

source将外部的数据库文件(*.sql)导入到本地mysql服务器中：

1. 创建空的数据库：mysql > CREATE DATABASE world； 
2. 关联数据库： mysql > USE world;
3. 利用source命令读入文件：mysql > SOURCE D:\mysql-8.0.13-winx64\data\world.sql (切记此处不要加分号；)

创建表

• 第一步：设计

我们从几个方面来描述个人的信息类型：姓名；性别；出生日期；地址；最喜爱的食物。

• 其中性别只允许单个字母M或F。
• 为了确保没有重复的列，我们将信息进一步精细划分划分
• 可能存在名字相同，需要有个主键保证唯一性
• 最喜爱的食物可能包含更多条目的列表，最好为此数据创建一个独立的表，其中包含一个指向person表的外键。以便为每一种食物指明所归属的人员

person表

列	类型	允许值
person_id	int(unsigned)
first_name	varchar(20)
last_name	varchar(20)
gender	char(1)	M,F
birth_date	date
street	varchar(30)
city	varchar(20)
state	varchar(20)
country	varchar(20)
postal_code	varchar(20)

person表已经有了主键(person_id)来保证唯一性，下一步建立favorite_food表，其中包含一个指向person表的外键

favorite_food表

person_id	int（unsigned）
food	varchar(20)

•  第二步：创建SQL方案语句

person表的创建语句如下：

CREATE TABLE person (
    -> person_id int not null,
    -> fname varchar(20),
    -> lname varchar(20),
    -> gender ENUM('M','F'),
    -> birth_date date,
    -> street varchar(20),
    -> city  varchar(20),
    -> state  varchar(20),
    -> country  varchar(20),
    -> postal_code varchar(20),
    -> constraint pk_person primary key (person_id));

如果想确认person表是否被创建，那么可以使用describe命令(简写desc)检查表定义。在创建表时，可以允许

DESCRIBE person;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| person_id   | int(11)       | NO   | PRI | NULL    |       |
| fname       | varchar(20)   | YES  |     | NULL    |       |
| lname       | varchar(20)   | YES  |     | NULL    |       |
| gender      | enum('M','F') | YES  |     | NULL    |       |
| birth_date  | date          | YES  |     | NULL    |       |
| street      | varchar(20)   | YES  |     | NULL    |       |
| city        | varchar(20)   | YES  |     | NULL    |       |
| state       | varchar(20)   | YES  |     | NULL    |       |
| country     | varchar(20)   | YES  |     | NULL    |       |
| postal_code | varchar(20)   | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+

创建favorite_food表：

• 一个人有多种喜爱的食物，仅靠person_id不能保证唯一性，本表的主键包含两列：person_id 和food。
• 本表包含另一个约束，外键约束，它限制了favorite表中的person_id列的值只能来自person表。

CREATE TABLE favorite_food(
    -> person_id int not null,
    -> food varchar(20),
    -> constraint pk_favorite_food primary key (person_id,food),
    -> constraint fk_fav_food_person_id foreign key (person_id) references person (person_id));
DESCRIBE favorite_food;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| person_id | int(11)     | NO   | PRI | NULL    |       |
| food      | varchar(20) | NO   | PRI | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

操作和修改表

• 插入

使用SQL命令INSERT向表中插入新的记录。每条INSERT命令都必须指定数据字段值来供MySQL服务器接受该记录。每个数据字段的值都用逗号分开。

INSERT INTO person
    -> (person_id,fname,lname,gender,birth_date)
    -> VALUES(1,'William','Turner','M','1972-05-27');        字符串转为日期类型

SELECT person_id,fname,lname,birth_dat
    -> FROM person;

SELECT person_id,fname,lname,birth_dat
    -> FROM person
    -> WHERE lname="Turner";
+-----------+---------+--------+------------+
| person_id | fname   | lname  | birth_date |
+-----------+---------+--------+------------+
|         1 | William | Turner | 1972-05-27 |
+-----------+---------+--------+------------+

如果你试图添加另外一条包含相同的empid数据字段值的记录，就会得到一条错误消息 。但如果你将empid的值改成唯一的值，那就没问题了

INSERT INTO employees VALUES(1,'blum','barbara',25000.00);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

INSERT INTO employees VALUES(2,'blum','barbara',25000.00);
Query OK, 1 row affected (0.00 sec)

同时我们向William插入三种喜爱的食物偏好。利用order by语句对查询返回结果进行排序

INSERT INTO favorite_food (person_id,food) VALUES (1,'pizza');
Query OK, 1 row affected (0.11 sec)
INSERT INTO favorite_food (person_id,food) VALUES (1,'cookies');
Query OK, 1 row affected (0.12 sec)
INSERT INTO favorite_food (person_id,food) VALUES (1,'nachos');
Query OK, 1 row affected (0.10 sec)

SELECT food FROM favorite_food WHERE person_id =1 ORDER BY food;
+---------+
| food    |
+---------+
| cookies |
| nachos  |
| pizza   |
+---------+

SELECT * FROM favorite_food;
+-----------+---------+
| person_id | food    |
+-----------+---------+
|         1 | cookies |
|         1 | nachos  |
|         1 | pizza   |
+-----------+---------+

•  删除

从表中删除数据，可以用DELETE，其中table指定了要从中删除记录的表。默认情况下它会删除该表中所有记录。

DELETE FROM table;        删除所有记录
DELETE FROM person WHERE person_id=2

使用drop语句完全清理掉表。包括本地存储文件。

DROP TABLE favorite_food
DROP TABLE person

• 更新数据

通过update语句补全William的缺失值

UPDATE person SET street = '1225 tremont St.',city='Boston',state='MA',country='USA',postal_code='02138' 
    -> WHERE person_id=1;
Query OK, 1 row affected (0.34 sec)
Rows matched: 1  Changed: 1  Warnings: 0    匹配了一行数据；修改了一行数据

• 表格属性修改

数字型主键生成机制

在MySQL中，有一种安全的产生数字型主键的方法，自增特性。一般情况下，在表格创建时就进行此项工作，但我们可以利用alter Table 来修改已经存在的表定义：

ALTER TABLE person MODIFY person_id int auto_increment

当向person表插入数据时，可以简单的将person_id设为null(设置为可以为null)，mysql会向该列自动提供下一个可用的主键数字(从1开始自增)

事务

将多条SQL语句聚集在一起，当所有语句都正确执行，则发出commit命令结束事务；如果其中任何地方有意外发生，就发出rollback命令撤销服务器自事务开始时的所有变化。

• 锁

锁是数据库服务器用来控制数据资源被并行使用的一种机制，当数据库的一些内容被锁定时，任何打算修改这个数据的用户必须等到锁被释放。大部分数据库使用下列两种锁策略之一：

1. 数据库的写操作必须向服务器申请并获得写锁才能修改数据；读操作必须申请和获得读锁才能查询数据。多用户可以同时读取数据，而一个表一次只能分配一个写锁，并且拒绝读请求直至写锁释放。
2. 数据库的写操作必须向服务器申请并获得写锁才能修改数据；读操作不需要任何类型的锁就能能查询数据。服务器要保证从查询开始到结束读操作看到一个一致的数据视图。这个方法称为版本控制

• 锁的粒度

• 表锁：阻止多用户同时修改同一个表数据
• 页锁：阻止多用户同时修改某表中同一页的数据
• 行锁： 阻止多用户同时修改某表中同一行的数据