数据库基础学习

数据库基础指令

结构化查询语言

DDL //数据定义语言 create drop alter
DML//数据操作语言 insert update delete
DQL //数据查询语言select
DCL //数据控制语言grant commit rollback

数据表三部分组成

1. 表结构 （列信息）
2. 表数据 （行信息）
3. 表索引 （把列中的行加到索引中【一般情况下一个表一定要把id这一列的所有数据都加到主键索引中】）

mysql基本命令

1. 关闭mysql
   net stop mysql
2. 开启 mysql
   net start mysql
3. 登录 mysql
   mysql -uroot -p123456
4. 查看数据库
   show databases;
5. use test
   切换到test数据库
6. 查看所有表
   show tables;
7. 查看table1 表中的所有的数据
   select * from table1;
8. 查看t1表列结构
   desc t1；
9. 退出mysql客户端
   exit；

数据库操作

1. 查看数据库
   show databases；
2. 创建数据库
   create databases db1；
3. 切换数据库
   use db1；
4. 删除数据库
   drop database db1；

数据表操作

 1. 查看表      show tables； 2. 创建表     create table 表名； 3. 修改表名    rename table 表名 to 表名2； 4. 删除表     drop table 表名； 5. 查看表字段    desc 表名 6. 查看表数据     select * from 表名;

数据库表设计

数据表概念

数据值和列类型（字段类型）

数值型    int    float字符型char(n) //255字节,比varchar快,存n个字节varchar(n) //65535个字节 比char 节约空间，存多少，占多少，如果超过n,截取到ntext //65535个字节日期型    date    datetime    time    year    timestamp    极少用

数据字段属性

1. unsigned //无符号，正数
2. zerofill //0填充，int(5) ,不够3位补0；
3. auto_increment //自动累加
4. null
5. not null //存储数据时不能为null
6. default

数据表对象管理

创建表 create table

修改表 alter table

删除表 drop table

数据表类型和存储位置

常用的数据表类型

MyIASM

InnoDB

MyISAM 和 InnoDB的适用场景

MyISAM适合：(1)做很多count 的计算；(2)插入不频繁，查询非常频繁；(3)没有事务。

InnoDB适合：(1)可靠性要求比较高，或者要求事务；(2)表更新和查询都相当的频繁，并且表锁定的机会比较大的情况。

MyISAM 和 InnoDB的区别

1)MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持

2)mysiam表不支持外键

3)在执行数据库写入的操作（insert,update,delete）的时候，mysiam表会锁表，而innodb表会锁行

4)当你的数据库有大量的写入、更新操作而查询比较少或者数据完整性要求比较高的时候就选择innodb表。当你的数据库主要以查询为主，相比较而言更新和写 入比较少，并且业务方面数据完整性要求不那么严格，就选择mysiam表。因为mysiam表的查询操作效率和速度都比innodb要快

存储位置

一般在mysql/data/目录下

数据表的字符集

四种字符集

 1. 服务器字符集 server characterset 2. 数据字符集  db characterset 3. 客户端字符集 client characterset 4. 客户端连接字符集 conn. characterset

查看数据库字符集

    show create database dbName

查看表字符集

     show create table tableName

设置client characterset和conn. characterset字符集为utf8

    set names urf8

索引

主键索引

primary key

添加主键索引
alter table table_name add primary key (column name);

普通索引

index in_named

普通索引一般建表之后添加
create index 索引名 on table_name(column1,column2);
alter table table_name add index 索引名(column1,column2);
删除
alter table table_name drop index 索引名;

查看表中的所有索引

show index from tableName

后期维护数据表

1. 添加字段
   alter table tableName add 字段名 类型
2. 修改字段
   alter table tableName modify 字段名 类型 [,not null default 默认]
3. 删除字段
   alter table tableName drop 字段名
4. 修改字段名
   alter table tableName change 原字段名 新字段名 类型；

帮助手段

？ show;
? create;
? 后面跟语句。

sql语句设计

CREATE TABLE `tab1` (  `id` int(2) unsigned primary key auto_increment,  `name` varchar(4) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8

增 insert

insert into tab1(id) values(3);

删 delete

delete from tab1 where id=2;
//注意，mysql里没有 “”==“”，只有‘’=‘’；既可以表示赋值又可以表示相等；清空数据使用truncate；

改 update

update table set username=’g’ where id=3;

查 select（最重要）

1. 选择特定的字段
   select id,name from tab1;
2. as 别名
   select id as a,name as b from tab1;
   select id a,name b from tab1;
3. distinct
   去掉重复项
   select distinct name from tab1;
4. where条件
   select id,name from tab1 where id>5;
5. null
   select * from tab1 where name is null;
   select * from tab1 where name is not null;
6. between and
   select * from tab1 where id between 2 and 6;
7. in
   select * from tab1 where id in (1,5,6);
8. like
   模糊查询
   select * from tab1 where name like ‘%4%’;
   select * from tab1 where name like ‘%4%’;
   select * from tab1 where name like ‘%4%’ or name like ‘%5%’;
   select * from tab1 where name like ‘___4%’;
   正则，比较慢
   select * from tab1 where name regexp ‘4’;

9. order by
   排序
   select * from tab1 order by name desc;

   desc 倒序
   asc 升序

10. limit
    限定输出个数
    select * from tab1 order by name asc limit 3;
    select * from tab1 order by id desc limit 3,4;
    select * from tab1 order by id asc limit 0,3;

11. 常用函数

    • concat() //连接
      select concat(‘a’,’b’);
    • rand() //随机排序
      select * from tab1 order by rand();
    • count() //统计
      select count(*) from tab1;
    • sum() //求和
    • avg() //求平均
    • max() // 最大值
    • min() //最小值
12. 分组聚合
    group by 在 order by 前

group by 后面还有条件使用 having

select name,count(id) num from tab1 group by name having num>2 order by num desc;

1. 多表查询
   1。多表查询（优先）

设计两张表，一张表记录个人信息，另一张表记录用户发表的信息，使用个人信息的id作为关系纽带。查看用户发表了几次信息

mysql> create table people(    -> id int unsigned primary key auto_increment,    -> name varchar(20),    -> age int    -> );

mysql> select * from people;+----+-------+------+| id | name  | age  |+----+-------+------+|  1 | jim   |   20 ||  2 | tom   |   23 ||  3 | alis  |   23 ||  4 | linda |   27 |+----+-------+------+

mysql> create table text(    -> id int unsigned primary key auto_increment,    -> uid int,    -> title varchar(200),    -> content text    -> );

mysql> select * from text;+----+------+---------+--------------------+| id | uid  | title   | content            |+----+------+---------+--------------------+|  1 |    1 | title1  | this is content    ||  2 |    1 | title1  | this is content2   ||  3 |    1 | title33 | this is content3   ||  4 |    1 | title4  | this is content4   ||  5 |    2 | title2  | this is content222 |+----+------+---------+--------------------+

mysql> select people.name,text.uid,text.title,text.content from people,text where text.uid=people.id;+------+------+---------+--------------------+| name | uid  | title   | content            |+------+------+---------+--------------------+| jim  |    1 | title1  | this is content    || jim  |    1 | title1  | this is content2   || jim  |    1 | title33 | this is content3   || jim  |    1 | title4  | this is content4   || tom  |    2 | title2  | this is content222 |+------+------+---------+--------------------+mysql> select people.name,count(people.name) from people,text where text.uid=people.id group by text.uid;+------+--------------------+| name | count(people.name) |+------+--------------------+| jim  |                  4 || tom  |                  1 |+------+--------------------+//由上可知有两人发表了信息，其中jim发表了四条信息，tom发表了一条信息

2。左链接查询
假设现在需要查询没有发表信息的人

mysql> select p.id,p.name from people p left join text t on p.id=t.uid where t.id is null;+----+-------+| id | name  |+----+-------+|  3 | alis  ||  4 | linda |+----+-------+2 rows in set (0.00 sec)

3。嵌套查询
需求和上一样

mysql>  select p.id,p.name  from people p where id not in (select uid from text group by uid);+----+-------+| id | name  |+----+-------+|  3 | alis  ||  4 | linda |+----+-------+2 rows in set (0.00 sec)