数据库

发表于 | 分类于 | 阅读次数: |

登录数据库

1
2
C:\Users\h>mysql -u root -p
Enter password: ******

创建一个数据库

1
mysql> create database bill;

展示数据库

1
mysql> show databases;

切换数据库

1
mysql> use bill

创建一个表

1
2
3
4
5
6
7
8
9
mysql> create table 2020_3(
    -> date int unsigned auto_increment,
    -> breakfast int null,
    -> lunch int null,
    -> dinner int null,
    -> other int null,
    -> last_change date,
    -> primary key (date)
    -> )engine=innodb;

删除表

1
mysql> drop table 2020_3

插入数据

1
2
3
4
5
6
7
8
mysql> insert into 2020_3
    -> (breakfast,lunch,dinner,other,last_change)
    -> values
    -> (0,0,0,15,NOW());
mysql> insert into 2020_3
    -> (other,last_change)
    -> values
    -> (50,NOW());

查询数据

1
2
3
mysql> select *
    -> from 2020_3
    -> where(other>0);

更新数据

1
2
3
4
5
6
mysql> update 2020_3
    -> set
    -> breakfast=0,
    -> lunch=0,
    -> dinner=0
    -> where date=2;

删除数据

1
2
3
mysql> delete
    -> from 2020_3
    -> where date=2;

LIKE子句

1
2
3
mysql> select *
    -> from 2020_3
    -> where other like '5%';

UNION 操作符

1
2
3
4
5
mysql> select date
    -> from 2020_3
    -> union
    -> select other
    -> from 2020_3;

这个自动会去重。下面这个不会去重

1
2
3
4
5
mysql> select date
    -> from 2020_3
    -> union all
    -> select other
    -> from 2020_3;

加上where

1
2
3
4
5
6
7
mysql> select date
    -> from 2020_3
    -> where date=1
    -> union all
    -> select dinner
    -> from 2020_3
    -> where dinner=4;

排序

DESC降序ABS升序

1
2
3
4
mysql> select *
    -> from 2020_3
    -> order by
    -> date desc;

从新插入一个表

分组

按名字分组，统计每个人有多少条记录。

1
2
3
mysql> select name,count(*)
    -> from employee_tbl
    -> group by name;

连接的使用

1
2
3
4
5
6
7
mysql> select a.date,b.singin
    -> from 2020_3 a inner join employee_tbl b
    -> on a.date=b.singin;
等价于
mysql> select a.date,b.singin
    -> from 2020_3 a,employee_tbl b
    -> where a.date=b.singin;

NULL的使用

不能使用算数运算符，NULL不管和谁比较都返回NULL，只能用is NULL 和is not NULL

正则表达式

ALTER

1
2
3
4
5
6
7
8
mysql> alter table
    -> 2020_3
    -> add
    -> name char(10);
mysql> alter table
    -> 2020_3
    -> drop
    -> name;