初识SQL语句

SQL（Structured Query Language ）

即结构化查询语言

SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。SQL语言分为3种类型：

DDL语句	数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER
DML语句	数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT
DCL语句	数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE

1.如果你在cmd中输入命令的时候，输入错了就用\c跳出

2.如果你在cmd中输入命令的时候，输入错了，且带有引号，就用引号\c跳出

   3. \s查看配置信息

 

数据库操作

　即操作文件夹

数据库命名规则：

可以由字母、数字、下划线、＠、＃、＄
区分大小写
唯一性
不能使用关键字如 create select
不能单独使用数字
最长128位　　

数据库的操作

增：create database db1 charset utf8;
删：drop database db1;
改：alter database db1 charset gbk;
查：
show databases; #查看所有的数据库
show create database db1; #查看db1数据库

举例

mysql> create database db1 charset utf8
    -> ;
Query OK, 1 row affected (0.01 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)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> alter database db1 charset gbk;
Query OK, 1 row affected (0.00 sec)

mysql> show create database db1;
+----------+-------------------------------------------------------------+
| Database | Create Database                                             |
+----------+-------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> drop database db1;
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

 

表的操作

即操作文件

基础操作

切换到数据库（文件夹下）:use db1
增：create table t1(id int,name char(10)) engine=innodb;
删：drop table t1;
改：alter table t1 add age int;
   alter table t1 modify name char(12);
查：show tables; #查看所有表
    show create table t1; #查看t1表
    desc t1;#查看表结构

    show create table t1\G; #查看表详细结构，可加\G
    select * from t1; #查看所有的表数据

　举例

mysql> use db1;
Database changed
mysql> create table t2(id int,name char(10)) engine=innodb charset=utf
Query OK, 0 rows affected (0.03 sec)

mysql> show create table t2;
+-------+-------------------------------------------------------------
-----------------------------------------------+
| Table | Create Table
                                               |
+-------+-------------------------------------------------------------
-----------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL,
  `name` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------
-----------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table t2\G;
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL,
  `name` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>
mysql> desc t2;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> alter table t2 modify name char(20);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t2;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(20) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)


mysql> alter table t2 add age int;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t2\G;
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL,
  `name` char(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> select * from t2;
Empty set (0.00 sec)

 

复制表与删除表

show tables; #查看库中所有表
create table t5 like t2; #只复制表结构，包括key，index等
create table t5 select * from t2 where 1=2; #只复制表结构，不包括key，index等
create table t4 select * from t2; #复制表结构和数据，不包括key，index等
drop table t4; 删除表　　

举例

mysql> create table t4 select * from t3 where 1=2;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> create table t5 like t2;
Query OK, 0 rows affected (0.01 sec)

mysql> desc t5;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | PRI | NULL    |       |
| name  | char(20) | NO   | PRI |         |       |
| grade | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql>
mysql> drop table t3;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
| t2            |
| t4            |
| t5            |
+---------------+
4 rows in set (0.00 sec)

mysql>
mysql>
mysql> insert into t2 values(1,'egon',99),(2,'alex',98);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t2;
+----+------+-------+
| id | name | grade |
+----+------+-------+
|  1 | egon |    99 |
|  2 | alex |    98 |
+----+------+-------+
2 rows in set (0.00 sec)

mysql> drop table t4;
Query OK, 0 rows affected (0.01 sec)

mysql> drop table t5;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> create table t4 select * from t2;
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc t2;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | PRI | NULL    |       |
| name  | char(20) | NO   | PRI |         |       |
| grade | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> desc t4;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   |     | NULL    |       |
| name  | char(20) | NO   |     |         |       |
| grade | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> select * from t4;
+----+------+-------+
| id | name | grade |
+----+------+-------+
|  1 | egon |    99 |
|  2 | alex |    98 |
+----+------+-------+
2 rows in set (0.00 sec)

mysql> create table t5 select * from t2 where 1=2;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from t5;
Empty set (0.00 sec)

mysql>
mysql> create table t6 like t2;
Query OK, 0 rows affected (0.01 sec)

mysql> desc t6;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | PRI | NULL    |       |
| name  | char(20) | NO   | PRI |         |       |
| grade | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> select * from t6;
Empty set (0.00 sec)

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
| t2            |
| t4            |
| t5            |
| t6            |
+---------------+
5 rows in set (0.00 sec)

mysql> drop table t5;
Query OK, 0 rows affected (0.01 sec)

mysql> drop table t4;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
| t2            |
| t6            |
+---------------+
3 rows in set (0.00 sec)

操作文件中的内容/记录

增：insert into db1.t2 values(1,'egon',18),(2,'alex',20),(3,'eva',21); #如果t2不给参数，默认按照位置参数依次传参
删：delete from t2 where id = 2;
    #对于清空记录有两种方式，但是推荐后者
    delete from t2;
    truncate t2; #当数据量比较大的情况下，使用这种方式，删除速度快
改：update t2 set name = 'ww' where id=3;
    update t2 set name= 'ww'  where name = 'alex';
    alter table t2 modify id int primary key auto_increment;  修改id为主键并且自增
查：select * from t2; #查看t2里所有的数据
    select name from t2;  #查看t2里所有的name
    select id,name from t2; #查看t2里所有的id,name
	
添加字段：alter table t2 add grade int;
修改存储引擎 ：alter table t2 engine=myisam;
删除字段: alter table t2 drop age;
删除自增约束: alter table t2 modify id int not null; 
删除主键: alter table t2 drop primary key;
增加主键:alter table t2 modify name varchar(10) not null primary key;
增加复合主键：alter table t2 add primary key(id,name);

　　

举例

mysql> insert into db1.t2 values(1,'egon',18),(2,'alex',20),(3,'eva',21);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t2\G;
*************************** 1. row ***************************
  id: 1
name: egon
 age: 18
*************************** 2. row ***************************
  id: 2
name: alex
 age: 20
*************************** 3. row ***************************
  id: 3
name: eva
 age: 21
3 rows in set (0.00 sec)


mysql> select * from t2;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | egon |   18 |
|    2 | alex |   20 |
|    3 | eva  |   21 |
+------+------+------+
3 rows in set (0.00 sec)

mysql> alter table t2 modify id int primary key auto_increment;
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show create table t2\G;
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


mysql> select id,name from t2;
+----+------+
| id | name |
+----+------+
|  1 | wy   |
|  2 | ww   |
|  3 | eva  |
+----+------+
3 rows in set (0.00 sec)

mysql> delete from t2 where id=1;
Query OK, 1 row affected (0.00 sec)

mysql> select id,name from t2;
+----+------+
| id | name |
+----+------+
|  2 | ww   |
|  3 | eva  |
+----+------+
2 rows in set (0.00 sec)

mysql> truncate table t2;
Query OK, 0 rows affected (0.03 sec)

mysql> select id,name from t2;
Empty set (0.00 sec)

mysql> alter table t2 drop age;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t2;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | YES  |     | NULL    |                |
| grade | int(11)  | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> alter table t2 add primary key(id,name);
ERROR 1068 (42000): Multiple primary key defined
mysql>
mysql> alter table t2 modify id int not null;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t2;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | PRI | NULL    |       |
| name  | char(20) | YES  |     | NULL    |       |
| grade | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> alter table t2 drop primary key;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t2;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   |     | NULL    |       |
| name  | char(20) | YES  |     | NULL    |       |
| grade | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> alter table t2 add primary key(id,name);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

　

查看当前用户和当前数据库

mysql> select user();
mysql> select database();

 

字符编码utf8和utf8mb4

一、简介

   MySQL在5.5.3之后增加了这个utf8mb4的编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode。好在utf8mb4是utf8的超集，除了将编码改为utf8mb4外不需要做其他转换。当然，为了节省空间，一般情况下使用utf8也就够了。

   二、内容描述

   那上面说了既然utf8能够存下大部分中文汉字,那为什么还要使用utf8mb4呢? 原来mysql支持的 utf8 编码最大字符长度为 3 字节，如果遇到 4 字节的宽字符就会插入异常了。三个字节的 UTF-8 最大能编码的 Unicode 字符是 0xffff，也就是 Unicode 中的基本多文种平面(BMP)。也就是说，任何不在基本多文本平面的 Unicode字符，都无法使用 Mysql 的 utf8 字符集存储。包括 Emoji 表情(Emoji 是一种特殊的 Unicode 编码，常见于 ios 和 android 手机上)，和很多不常用的汉字，以及任何新增的 Unicode 字符等等(utf8的缺点)。