数据库基本操作

# DML操作

**查找数据**

```sql

SELECT COUNT(*) AS 数量

FROM `book`

WHERE `author` = '刘刚';

```

```sql

SELECT *

FROM `emp`

WHERE `uid`='1';

```

```sql

SELECT COUNT( DISTINCT `user_id`)

FROM `book_borrow`

WHERE YEAR(`borrow_time`)= '2024' AND MONTH(`borrow_time`)= '5';

```

**添加数据**

```sql

INSERT INTO`book`VALUES

('5','数据库高级','XX出版社',NOW(),NULL);

```

**删除数据**

```sql

delete from emp

where name = 'dony';

```

```sql

delete from `emp` where `id` like '%XX%';

```

**更新数据**

```sql

UPDATE `user`

SET `dept_id`= 3

WHERE `user_name`='小刚';

```

```sql

UPDATE `user`

SET `dept_id`= (SELECT `id` FROM `dept` WHERE `dept_name`='运维部')

WHERE `user_name`='小刚';

```

**调用函数**

```sql

SELECT fn()

```

**调用存储过程**

```sql

CALL sp()

```

# DDL操作

**删除数据库**

```sql

Drop database emp;

```

**删除触发器**

```sql

drop trigger trig_test;

```

**设置外键**

```sql

foreign key (uid) references emp(uid);

```

**表中添加列**

```sql

ALTER TABLE S ADD(CN CHAR(8));

```

**表中删除列**

```sql

ALTER TABLE emp DROP Age;

```

```sql

alter table emp drop column Age;

```

**查询表结构**

```sql

DESC emp;

```

**查找数据库中的所有数据表**

```sql

show tables;

```

**给表重命名**

```sql

alter table old_name rename to new_name;

```

**创建索引**

```sql

CREATE INDEX index_name ON `book`(`book_name`);

```

```sql

CREATE INDEX index_name ON `book`(`publish`,`book_name`);

```

# DQL操作

# DCL操作