关于mysql的安装等内容，稍后写博客详解，本篇主要在于mysql的使用
1.mysql安装后，统一编码
#1.my.cnf中
#mysql5.5以上：修改方式有所改动
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
#2. 重启服务
#3. 查看修改结果：
show variables like '%char%'
或
mysql> \s
--------------
/application/mysql/bin/mysql Ver 14.14 Distrib 5.6.34, for linux-glibc2.5 (x86_64) using EditLine wrapper
Connection id: 5
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.34 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 1 min 33 sec
Threads: 1 Questions: 5 Slow queries: 0 Opens: 67 Flush tables: 1 Open tables: 60 Queries per second avg: 0.053
--------------
2.SQL语句
2.1SQL语句分类
"DDL语句-数据定义语言："
数据库，表，视图，索引，存储过程，如create,drop,alter
"DML语句-数据操纵语言："
增加数据-insert，删除数据-delete，修改数据-update，查数据-select
"DCL语句-数据控制语言："
如用户的访问权限grant,revoke
2.2初识SQL语句
#1. 操作文件夹
增：create database db1 charset utf8;
查：show databases;
改：alter database db1 charset latin1;
删除: drop database db1;
#2. 操作文件
先切换到文件夹下：use db1
增：create table t1(id int,name char);
查：show tables
改：alter table t1 modify name char(3);
alter table t1 change name name1 char(2);
删：drop table t1;
#3. 操作文件中的内容/记录
增：insert into t1 values(1,'egon1'),(2,'egon2'),(3,'egon3');
查：select * from t1;
改：update t1 set name='sb' where id=2;
删：delete from t1 where id=1;
2.3数据库
2.3.1系统数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
information_schema:
虚拟库，不占用磁盘空间，存储的是数据库启动后的一些参数，如用户表信息，列信息，权限信息，字符信息
performance_schema:
MySQL5.5版本开始新增的数据库
主要用于手机数据库服务器性能参数，记录处理查询请求时发生的各种事件，锁等
mysql
授权库，主要存储系统用户的权限信息
test
mysql数据库系统自动创建的测试数据库，默认里面没有表
2.3.2数据库相关操作
"创建数据库"
create database 数据库名 charset utf8;
"数据库命名规则"
1.可以是字母，数字，下划线，@,#,$
2.区分大小写
3.唯一性
4.不能使用关键字，如select,drop
5.不能单独使用数据
6.最好命名和其功能相关
7.最长128位
"查看有哪些数据库"
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
"查看建库语句"
mysql> show create database db1;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
"select database()"
mysql> select database();
+--------------------+
| database() |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.01 sec)
"切换到某个数据库下，在该库下，select语句不需要加库名"
mysql> use db1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1 |
+---------------+
1 row in set (0.00 sec)
'"删除数据库'''
mysql> drop batabase db1;
"修改数据库"
mysql> alter database db1 charset utf8;
Query OK, 1 row affected (0.00 sec)
2.4存储引擎
"查看MySQL中有哪些存储引擎"
mysql> show engines\G
*************************** 1. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 2. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 9. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
9 rows in set (0.00 sec)
mysql>
"查看正在使用的存储引擎"
mysql> show variables like 'storage_engine%';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | InnoDB |
+----------------+--------+
1 row in set (0.00 sec)
mysql>
"InnoDB存储引擎"
支持事务，行级锁定，有表空间和索引
"MyISAM储存引擎"
不支持事务，表级别锁定，支持全文索引
"memery存储引擎"
数据库重启或崩溃，表中数据将会消失
"BLACKHOLE黑洞存储引擎"
数据放进去就没有了
"指定存储引擎"
1.建表时指定
MariaDB [db1]> create table innodb_t1(id int,name char)engine=innodb;
MariaDB [db1]> create table innodb_t2(id int)engine=innodb;
MariaDB [db1]> show create table innodb_t1;
MariaDB [db1]> show create table innodb_t2;
2.在配置文件中指定默认存储引擎
/etc/my.cnf
[mysqld]
default-storage-engine=INNODB
innodb_file_per_table=1
#"创建四个表，分别使用innodb，myisam，memory，blackhole存储引擎，进行插入数据测试"
mysql> use db1
Database changed
mysql> create table t1(id int)engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> create table t2(id int)engine=myisam;
Query OK, 0 rows affected (0.01 sec)
mysql> create table t3(id int)engine=memory;
Query OK, 0 rows affected (0.00 sec)
mysql> create table t4(id int)engine=blackhole;
Query OK, 0 rows affected (0.00 sec)
#"可以看到，后两种存储引擎只有表结构，无数据，因为只有.frm"
[root@m01 db1]# ll
total 152
-rw-rw---- 1 mysql mysql 61 May 22 17:49 db.opt
-rw-rw---- 1 mysql mysql 8556 May 22 18:52 t1.frm
-rw-rw---- 1 mysql mysql 98304 May 22 18:52 t1.ibd
-rw-rw---- 1 mysql mysql 8556 May 22 18:52 t2.frm
-rw-rw---- 1 mysql mysql 0 May 22 18:52 t2.MYD
-rw-rw---- 1 mysql mysql 1024 May 22 18:52 t2.MYI
-rw-rw---- 1 mysql mysql 8556 May 22 18:53 t3.frm
-rw-rw---- 1 mysql mysql 8556 May 22 18:53 t4.frm
[root@m01 db1]# pwd
/application/mysql/data/db1
#"memory，在重启mysq或重启机器后，表中数据清空"
mysql> insert into t3 values(2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t3;
+------+
| id |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
mysql>
[root@m01 db1]# service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL.. SUCCESS!
[root@m01 db1]#
mysql> select * from t3;
Empty set (0.00 sec)
#"blackhole,往表中插入任何数据，都相当于丢入黑洞，表中不保存记录"
mysql> insert into t4 values(2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t4;
Empty set (0.00 sec)
2.5表的增删改查
2.5.1创建表
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);
#注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的
mysql> create database db1 charset utf8;
Query OK, 1 row affected (0.00 sec)
mysql> use db1;
Database changed
mysql> create table t1(id int,name varchar(50),sex enum('male','female'),age int);
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1 |
+---------------+
1 row in set (0.01 sec)
mysql> desc t1;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> show create table t1\G;
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`sex` enum('male','female') DEFAULT NULL,
`age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
ERROR:
No query specified
mysql> select * from t1;
Empty set (0.00 sec)
mysql>
"插入数据"
mysql> insert into t1 values(1,'vita','male',18);
Query OK, 1 row affected (0.00 sec)
2.5.2查看表结构
#查看表中字段
mysql> desc t1;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
# 查看建表语句
mysql> show create table t1\G;
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`sex` enum('male','female') DEFAULT NULL,
`age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
2.5.3修改表结构
语法：
1. 修改表名
ALTER TABLE 表名
RENAME 新表名;
2. 增加字段
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…],
ADD 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
3. 删除字段
ALTER TABLE 表名
DROP 字段名;
4. 修改字段
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
"修改存储引擎"
mysql> alter table t2 engine=innodb;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
"添加字段"
mysql> alter table t2 add class varchar(10) not null;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t2 add school varchar(10) not null,add course varchar(10) not null;#添加多个字段
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t2;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| class | varchar(10) | NO | | NULL | |
| school | varchar(10) | NO | | NULL | |
| course | varchar(10) | NO | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table t2 add name varchar(10) not null after id;#id后添加一个字段
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t2;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | NO | | NULL | |
| class | varchar(10) | NO | | NULL | |
| school | varchar(10) | NO | | NULL | |
| course | varchar(10) | NO | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> alter table t2 add age int not null first;#设置为第一个字段
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t2;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| age | int(11) | NO | | NULL | |
| id | int(11) | YES | | NULL | |
| name | varchar(10) | NO | | NULL | |
| class | varchar(10) | NO | | NULL | |
| school | varchar(10) | NO | | NULL | |
| course | varchar(10) | NO | | NULL | |
+--------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
"删除字段"
mysql> alter table t2 drop school;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t2;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| age | int(11) | NO | | NULL | |
| id | int(11) | YES | | NULL | |
| name | varchar(10) | NO | | NULL | |
| class | varchar(10) | NO | | NULL | |
| course | varchar(10) | NO | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
"修改字段类型modify"
mysql> alter table t2 modify class char(10);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
"添加约束，设置auto_increment"
mysql> alter table t2 modify id int not null primary key auto_increment;#设置为主键，并自动增长
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t2;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| age | int(11) | NO | | NULL | |
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| class | char(10) | YES | | NULL | |
| course | varchar(10) | NO | | NULL | |
+--------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql>
"添加主键和自动增长"
mysql> alter table t2 modify id int not null primary key auto_increment;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
"添加主键"
mysql> alter table student1 modify name varchar(10) not null primary key;
mysql> desc t2;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| age | int(11) | NO | | NULL | |
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| class | char(10) | YES | | NULL | |
| course | varchar(10) | NO | | NULL | |
+--------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
"删除主键自增约束"
mysql> alter table t2 modify id int not null;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t2;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| age | int(11) | NO | | NULL | |
| id | int(11) | NO | PRI | NULL | |
| name | varchar(10) | NO | | NULL | |
| class | char(10) | YES | | NULL | |
| course | varchar(10) | NO | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql>
"删除主键"
mysql> alter table t2 drop primary key;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t2;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| age | int(11) | NO | | NULL | |
| id | int(11) | NO | | NULL | |
| name | varchar(10) | NO | | NULL | |
| class | char(10) | YES | | NULL | |
| course | varchar(10) | NO | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
2.5.4复制表
mysql> select * from t2;
+-----+----+------+--------+---------+
| age | id | name | class | course |
+-----+----+------+--------+---------+
| 12 | 1 | vita | python | python1 |
+-----+----+------+--------+---------+
1 row in set (0.00 sec)
"复制表结构和数据"
mysql> create table t4 select * from t2;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from t4;
+-----+----+------+--------+---------+
| age | id | name | class | course |
+-----+----+------+--------+---------+
| 12 | 1 | vita | python | python1 |
+-----+----+------+--------+---------+
1 row in set (0.00 sec)
"只复制表结构"
mysql> create table t5 select * from t2 where 1=2;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from t5;
Empty set (0.00 sec)
mysql> desc t5;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| age | int(11) | NO | | NULL | |
| id | int(11) | NO | | NULL | |
| name | varchar(10) | NO | | NULL | |
| class | char(10) | YES | | NULL | |
| course | varchar(10) | NO | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
2.5.5删除表
mysql> drop table t5;
Query OK, 0 rows affected (0.01 sec)
mysql> drop table t4;
Query OK, 0 rows affected (0.01 sec)
扫一扫
2288
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
