学习MySQL笔记
此文记录我阶段学习的总结,用于个人复习。因初次学习,难免有不恰当的地方,仅供各位读者参考。
一、常用命令
1.show databases(显示所有数据库)
例子:
mysql>show databases; -- 显示所有数据库 --
+--------------------+
| Database |
+--------------------+
| information_schema | -- 目前有4个数据库 --
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
2.create database stu_db(创建数据库)
例子:
mysql>create database stu_db; -- 创建数据库 --
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| stu_db | -- stu_db是刚创建的 --
| sys |
+--------------------+
5 rows in set (0.00 sec)
3.drop database stu_db(删除数据库)
例子:
mysql>drop database stu_db; -- 删除数据库stu_db --
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema | -- stu_db没了 --
| sys |
+--------------------+
4 rows in set (0.00 sec)
4.use stu_db(选择数据库)
例子:
mysql>use stu_db; -- 选择数据库stu_db --
Database changed
5.create table(字段名1 type(类型值1),字段名2 type(类型值2),……) (创建数据表)
例子:
mysql>create table book_tab(title varchar(20),price double(8,2)); -- 创建数据表 --
Query OK, 0 rows affected, 1 warning (0.02 sec)
6.desc book_tab(显示表结构)
例子:
mysql> desc book_tab; -- 显示表结构 --
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| title | varchar(20) | YES | | NULL | |
| price | double(8,2) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
再来一例子
mysql> create table car_tab( -- 创建数据表 --
-> chepai varchar(20),
-> color varchar(10),
-> price double(10,2),
-> seat int(2),
-> company varchar(50)
-> );
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> show tables; -- 显示所有数据表 --
+------------------+
| Tables_in_stu_db |
+------------------+
| book_tab |
| car_tab |
+------------------+
2 rows in set (0.00 sec)
mysql> desc car_tab; -- 显示表结构 --
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| chepai | varchar(20) | YES | | NULL | |
| color | varchar(10) | YES | | NULL | |
| price | double(10,2) | YES | | NULL | |
| seat | int | YES | | NULL | |
| company | varchar(50) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
7.insert(插入数据)
例子:
mysql> INSERT INTO car_tab VALUES( -- 插入数据 --
-> '陕A123456',
-> '白色',
-> 500000.00,
-> 6,
-> '宝马'
-> );
Query OK, 1 row affected (0.00 sec)
mysql> select * from car_tab;
+-----------+-------+-----------+------+---------+
| chepai | color | price | seat | company |
+-----------+-------+-----------+------+---------+
| 陕A123456 | 白色 | 500000.00 | 6 | 宝马 |
+-----------+-------+-----------+------+---------+
1 row in set (0.00 sec)
8.select(查询数据)
例子:
mysql> INSERT INTO car_tab(
-> chepai,
-> color,
-> company
-> ) VALUES(
-> '陕A12138',
-> '黑色',
-> '奔驰'
-> );
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM car_tab; -- 查询表的数据 --
+-----------+-------+-----------+------+---------+
| chepai | color | price | seat | company |
+-----------+-------+-----------+------+---------+
| 陕A123456 | 白色 | 500000.00 | 6 | 宝马 |
| 陕A12138 | 黑色 | NULL | NULL | 奔驰 |
+-----------+-------+-----------+------+---------+
9.自增,主键
mysql> CREATE TABLE book_tab(
-> id int(8) auto_increment, -- id的值会自动增加,不会重复 --
-> title varchar(20),
-> author varchar(20),
-> isbn varchar(20),
-> price double(8,2),
-> PRIMARY KEY(id) -- 将id作为主键,唯一确定一行 --
-> );
Query OK, 0 rows affected, 2 warnings (0.02 sec)
10.delete(删除数据)
例子:
mysql> INSERT INTO book_tab(
-> title,
-> author,
-> isbn,
-> price
-> )VALUES(
-> '三国演义',
-> '罗贯中',
-> 'abcd0001',
-> 33.33
-> );
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO book_tab(
-> title,
-> author,
-> isbn,
-> price
-> )VALUES(
-> '海底两万里',
-> '凡尔纳',
-> 'abcd0002',
-> 35.33
-> );
Query OK, 1 row affected (0.00 sec)
mysql> DELETE FROM book_tab WHERE id = 2; -- 删除id为2的那一行 --
Query OK, 1 row affected (0.00 sec)
mysql> select * from book_tab;
+----+----------+--------+----------+-------+
| id | title | author | isbn | price |
+----+----------+--------+----------+-------+
| 1 | 三国演义 | 罗贯中 | abcd0001 | 33.33 |
+----+----------+--------+----------+-------+
1 row in set (0.00 sec)
再来一例子
mysql> select * from book_tab;
+----+------------+----------+----------+-------+
| id | title | author | isbn | price |
+----+------------+----------+----------+-------+
| 3 | 海底两万里 | 凡尔纳 | abcd0002 | 35.33 |
| 4 | 百万英镑 | 马克吐温 | abcd0003 | 38.33 |
+----+------------+----------+----------+-------+
2 rows in set (0.00 sec)
mysql> DELETE FROM book_tab WHERE price between 35 and 36; -- 删除price在35到36之间的那一行 --
Query OK, 1 row affected (0.00 sec)
mysql> select * from book_tab;
+----+----------+----------+----------+-------+
| id | title | author | isbn | price |
+----+----------+----------+----------+-------+
| 4 | 百万英镑 | 马克吐温 | abcd0003 | 38.33 |
+----+----------+----------+----------+-------+
1 row in set (0.00 sec)
再来一例子
mysql> INSERT INTO book_tab(
-> title ,
-> author ,
-> isbn ,
-> price
-> )
-> VALUES(
-> '红楼梦',
-> '曹雪芹',
-> 'hfzy00003',
-> 66.33
-> );
Query OK, 1 row affected (0.00 sec)
mysql> select * from book_tab;
+----+----------+----------+-----------+-------+
| id | title | author | isbn | price |
+----+----------+----------+-----------+-------+
| 4 | 百万英镑 | 马克吐温 | abcd0003 | 38.33 |
| 5 | 红楼梦 | 曹雪芹 | hfzy00003 | 66.33 |
+----+----------+----------+-----------+-------+
2 rows in set (0.00 sec)
mysql> DELETE FROM book_tab WHERE author = '曹雪芹'; -- 删除author为曹雪芹的那一行 --
Query OK, 1 row affected (0.00 sec)
mysql> select * from book_tab;
+----+----------+----------+----------+-------+
| id | title | author | isbn | price |
+----+----------+----------+----------+-------+
| 4 | 百万英镑 | 马克吐温 | abcd0003 | 38.33 |
+----+----------+----------+----------+-------+
1 row in set (0.00 sec)
再来一例子
mysql> select * from book_tab;
+----+----------+----------+-----------+-------+
| id | title | author | isbn | price |
+----+----------+----------+-----------+-------+
| 4 | 百万英镑 | 马克吐温 | abcd0003 | 38.33 |
| 6 | 西游记 | 吴承恩 | hfzy00002 | 36.33 |
| 7 | 水浒 | 施耐庵 | hfzy00001 | 33.33 |
| 8 | NULL | NULL | NULL | NULL |
+----+----------+----------+-----------+-------+
4 rows in set (0.00 sec)
mysql> delete from book_tab where title is NULL; -- 删除title为NULL的那一行 --
Query OK, 1 row affected (0.00 sec)
mysql> select * from book_tab;
+----+----------+----------+-----------+-------+
| id | title | author | isbn | price |
+----+----------+----------+-----------+-------+
| 4 | 百万英镑 | 马克吐温 | abcd0003 | 38.33 |
| 6 | 西游记 | 吴承恩 | hfzy00002 | 36.33 |
| 7 | 水浒 | 施耐庵 | hfzy00001 | 33.33 |
+----+----------+----------+-----------+-------+
3 rows in set (0.00 sec)
11.update(更新数据)
例子:
mysql> select * from book_tab;
+----+------------+--------+----------+-------+
| id | title | author | isbn | price |
+----+------------+--------+----------+-------+
| 10 | 海底两万里 | 凡尔纳 | hfzy0001 | 40.43 |
+----+------------+--------+----------+-------+
1 row in set (0.00 sec)
mysql> update book_tab set price=55.55 where author='凡尔纳'; -- 更新author为凡尔纳的那一行的price为55.55 --
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from book_tab;
+----+------------+--------+----------+-------+
| id | title | author | isbn | price |
+----+------------+--------+----------+-------+
| 10 | 海底两万里 | 凡尔纳 | hfzy0001 | 55.55 |
+----+------------+--------+----------+-------+
1 row in set (0.00 sec)
二、三大范式
1.每个字段不可拆分
2.一个表必须描述一件事情
3.每个字段必须直接依赖主键,不可传递依赖
三、补充
-- 插入多条数据 --
-- 方式1 --
insert int 表名(字段列表) values(值列表),(值列表),(值列表)...
-- 方式2 --
insert int 表名 values(全值列表),(全值列表),(全值列表)...
-- 快速插入数据
INSERT INTO book_tab(
book_title,
book_author,
book_price,
book_publisher
) SELECT book_title,book_author,book_price,book_publisher FROM book_tab;-- 读取book_tab表中的book_title,book_author,book_price,book_publisher数据再插入book_tab中
-- 删除数据
delete FROM book_tab;-- 每删除一条,就往日志中写入一条,这种方法删除的可以恢复
TRUNCATE table book_tab;
drop table book_tab;
-- 查询
SELECT emp_num,emp_name,emp_phone FROM emp_tab;-- 查询emp_tab表,结果只显示emp_num,emp_name,emp_phone这三列
SELECT emp_num AS 编号,emp_name 姓名,emp_phone 电话 FROM emp_tab;-- 查询,显示编号,姓名,电话
SELECT * FROM emp_tab;
SELECT e.* e.emp_birth FROM emp_tab e;-- 表名别名方式
-- 条件查询
SELECT * FROM emp_tab WHERE emp_dept='市场部'; -- 查询部门是市场部的那些数据行
SELECT emp_name 姓名 FROM emp_tab WHERE emp_dept='市场部' AND emp_gender='女';-- 多条件查询,查询既部门是市场部,性别是女的数据行
SELECT * FROM emp_tab WHERE emp_dept='销售部' OR emp_dept='人事部';-- 查询部门是销售部或者是人事部的数据行
SELECT * FROM emp_tab WHERE emp_dept='aaa' AND emp_gender='ff' OR 1='1'-- SQL注入漏洞
-- 查询
SELECT emp_name 姓名 FROM emp_tab WHERE emp_dept in('市场部','销售部','教学部');-- 查询在市场部、销售部和教学部的人
SELECT emp_name 姓名 FROM emp_tab WHERE emp_dept not in('市场部','销售部','教学部');-- 查询不在市场部、销售部和教学部的人
-- 模糊查询
SELECT * FROM emp_tab WHERE emp_name LIKE '刘%';-- %表示任意个任意字符
SELECT * FROM emp_tab WHERE emp_name LIKE '刘_';-- _表示一个任意字符
SELECT * FROM emp_tab WHERE emp_name LIKE '%刘%';-- 查询姓名中带刘的人
SELECT * FROM emp_tab WHERE emp_name LIKE '%李%' OR emp_manager LIKE '%王%';-- 查询姓名中带李的人
SELECT * FROM emp_tab WHERE emp_address is NULL;-- 查询地址为空的人