1.数据库的分类
数据库目前分为:关系型数据库和非关系型数据库(更加符合大数据时代需求)。
关系型数据库又包括超大型数据库(Oracle),企业型数据库(SqlServer,MYSQL,POSTSql)和轻量级文件数据库(Sqlite,Python3(内置了sqlite))。
非关系型数据库又包括文档型数据库(MongoDB)和内存型数据库(Redis)。
2.结构化查询语句
1.DDL:数据定义语言, 操作数据库与表。
2.DML:数据操作语言, 插入、删除、修改。
3.DQL:数据查询语言, 查询。
4.DCL:数据控制语言,用户 权限。
3.数据库相关指令
1.show databases: 展示当前数据库管理系统下都有哪些数据库。
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dy |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
这里的dy是博主自己创建的数据库,其他四个数据库是数据库自带的,大家不要去动它们哦。
2.create database 数据库名 charset=utf8:创建数据库。
mysql> create database dy222 charset=utf8;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dy |
| dy222 |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
大家注意创建数据库的时候一定要带上charset=utf8,不然数据库里无法接受汉字。
3.select database():查询当前数据库。
mysql> select database();
+------------+
| database() |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)
这里博主没有选择数据库,因此里面是null,如果选择了数据库,里面就是你选择的数据库的名字。
4.use 数据库名:选择数据库。
mysql> use dy222;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| dy222 |
+------------+
1 row in set (0.00 sec)
5.drop database 数据库名:删除数据库。
4.数据库的表的相关指令
1.create table 表名(列名 类型 约束列表,列名 类型 约束列表...)
注意点:1.列名后必须跟类型 。 2.约束不分先后顺序
primary key:主键约束,每个表中必须有主键 ,主键必须唯一,能够确定唯一一行 。
not null:非空约束
auto_increment:自增长
unique:唯一约束,不能重复
mysql> create table hh (id int not null primary key auto_increment,name varchar(20) not null unique,age int not null);
Query OK, 0 rows affected (0.02 sec)
2.show databases:展示当前数据库中都有哪些表。
mysql> show tables;
+-----------------+
| Tables_in_dy222 |
+-----------------+
| hh |
+-----------------+
1 row in set (0.00 sec)
3.desc 表名:查看表结构
mysql> desc hh;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | UNI | NULL | |
| age | int(11) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
4.alter table 表名 rename 新表名:重命名
mysql> show tables;
+-----------------+
| Tables_in_dy222 |
+-----------------+
| hh |
+-----------------+
1 row in set (0.00 sec)
mysql> alter table hh rename gg;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-----------------+
| Tables_in_dy222 |
+-----------------+
| gg |
+-----------------+
1 row in set (0.00 sec)
5.alter table 表名 add 列名 类型 约束:添加列
mysql> alter table gg add sex varchar(20)not null;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc gg;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | UNI | NULL | |
| age | int(11) | NO | | NULL | |
| sex | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
6.alter table 表名 change 原列名 新列名 类型 约束:修改列
mysql> alter table gg change sex length int not null;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc gg;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | UNI | NULL | |
| age | int(11) | NO | | NULL | |
| length | int(11) | NO | | NULL | |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
7.alter table 表名 drop 列名:删除列
mysql> alter table gg drop length;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings:
mysql> desc gg;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | UNI | NULL | |
| age | int(11) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
8.drop table 表名:删除表
5.CURD 增删改查
1.全列插入:insert into 表名 values(值1,值2,......),值的个数必须和列的个数一致。
缺点:有默认值的列也要给值
mysql> insert into gg values(0,"豆豆",15),(0,"倩倩",20);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
2.缺省插入:insert into 表名(列名1,列名2....)values (值1, 值2.....)
优点:有默认值的列可以不给值
mysql> insert into gg (name,age) values ("阿花",18);
Query OK, 1 row affected (0.00 sec)
3.select * from 表名:查看当前表内信息
mysql> select * from gg;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | 豆豆 | 15 |
| 2 | 倩倩 | 20 |
| 3 | 阿花 | 18 |
+----+------+-----+
3 rows in set (0.00 sec)
4. select 列名...from 表名:查询部分列
mysql> select name from gg;
+------+
| name |
+------+
| 倩倩 |
| 豆豆 |
| 阿花 |
+------+
3 rows in set (0.00 sec)
5.select 列名 as 别名,....from 表名:通过as给与别名
mysql> select name as name2 from gg;
+-------+
| name2 |
+-------+
| 倩倩 |
| 豆豆 |
| 阿花 |
+-------+
3 rows in set (0.00 sec)
6.select*from 表名 where 条件:通过条件语句查询部分行
where id = 101, where id > 10
mysql> select * from gg where id < 2;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | 豆豆 | 15 |
+----+------+-----+
1 row in set (0.00 sec)
and 并且
mysql> select * from gg where id < 3 and id > 1;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 2 | 倩倩 | 20 |
+----+------+-----+
1 row in set (0.00 sec)
or 或者
mysql> select * from gg where id < 2 or id > 2;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | 豆豆 | 15 |
| 3 | 阿花 | 18 |
+----+------+-----+
2 rows in set (0.00 sec)
in 与 not in :在多个选项中就符合
mysql> select * from gg where id in (1, 3, 5, 7, 9);
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | 豆豆 | 15 |
| 3 | 阿花 | 18 |
+----+------+-----+
2 rows in set (0.00 sec)
like:模糊查询
% 匹配多个, %所在位置允许不限个数其他汉字。
mysql> select * from gg where name like "%倩%";
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 2 | 倩倩 | 20 |
| 4 | 刘倩倩 | 15 |
+----+--------+-----+
2 rows in set (0.00 sec)
mysql> select * from gg where name like "倩%";
+----+------+-----+
| id | name | age |
+----+------+-----+
| 2 | 倩倩 | 20 |
+----+------+-----+
1 row in set (0.00 sec)
_ 匹配一个
mysql> select * from gg where name like "倩_";
+----+------+-----+
| id | name | age |
+----+------+-----+
| 2 | 倩倩 | 20 |
+----+------+-----+
1 row in set (0.00 sec)
mysql> select * from gg where name like "刘__";
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 4 | 刘倩倩 | 15 |
+----+--------+-----+
1 row in set (0.00 sec)
这里博主使用了两个_
7.select distinct 列名 from table2:去重重复列
mysql> select distinct age from gg;
+-----+
| age |
+-----+
| 15 |
| 20 |
| 18 |
+-----+
3 rows in set (0.00 sec)
8.order by:排序
升序asc
mysql> select * from gg order by age asc;
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 1 | 豆豆 | 15 |
| 4 | 刘倩倩 | 15 |
| 3 | 阿花 | 18 |
| 2 | 倩倩 | 20 |
+----+--------+-----+
4 rows in set (0.00 sec)
降序desc
mysql> select * from gg order by age desc;
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 2 | 倩倩 | 20 |
| 3 | 阿花 | 18 |
| 1 | 豆豆 | 15 |
| 4 | 刘倩倩 | 15 |
+----+--------+-----+
4 rows in set (0.00 sec)
9.group by:分组 count:计数
mysql> select age, count(*) from gg group by age;
+-----+----------+
| age | count(*) |
+-----+----------+
| 15 | 2 |
| 18 | 1 |
| 20 | 1 |
+-----+----------+
3 rows in set (0.00 sec)
10.delete from 表名 where 条件:删除
11.update 表名 set 列=值...:修改
mysql> update gg set age = 11;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> select * from gg;
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 1 | 豆豆 | 11 |
| 2 | 倩倩 | 11 |
| 3 | 阿花 | 11 |
| 4 | 刘倩倩 | 11 |
+----+--------+-----+
4 rows in set (0.00 sec)
6.用户与权限
1.create user 'dy'@'%' identified by'123456':创建用户
dy: 自定义用户名字
%:用户可以使用所有权限
123456:自定义密码
2.select user():当前用户
3.drop user'dy'@'%':删除用户
4.select host,user from user:查询所有用户