增
- 创建用户
1)创建用户,仅允许本地登录
create user 'bmanager'@'localhost' identified by 'MyNewPass0!';
2)创建用户,允许远程主机登录mysql
create user 'bmanager'@'%' identified by 'MyNewPass0!';
- 创建库
CREATE DATABASE books default character set utf8;;
- 授权
1)给用户授权,指定库
grant all privileges on books.* to 'bmanager'@'%' identified by "MyNewPass0!";
2)用户可以访问所有数据库和表
grant all privileges on *.* to 'bmanager'@'%' identified by "MyNewPass0!";
- 刷新
flush privileges;
查
- 查看库:show databases;
- 使用库:use books;
- 查询表:show tables;
- 查看数据表结构: desc booksModel_books_info;
- 查看表生成的DDL:
show create table booksModel_books_info\G;
- 查询表所有数据:
select * from booksModel_books_info\G;
- 查询表中一共多少记录:
select count(*) from booksModel_books_info;
- 查询表中某个字段:
select id from booksModel_books_info;
- 查询表中某两个字段
select id,name from booksModel_books_info;
- 查询表中前 10 条数据
select * from booksModel_books_info limit 10\G;
- 查询表中前 10 条数据
select * from booksModel_books_info where price='88' limit 0,10\G;
- 查询表中 第6条 后面的10条记录,不包括第6条
select * from booksModel_books_info where price='88' limit 6,10\G;
- 查询表中后 10 条数据
select * from booksModel_books_info order by id desc limit 10\G;
- 查询表中包含某字段的数据
select * from booksModel_books_info where price='88'\G;
- 查询表中包含某字段的数据前十条
select * from booksModel_books_info where price='88' limit 10\G;
- 查询表中包含某字段的数据后十条
select * from booksModel_books_info where price='88' order by id desc limit 10\G;
- 查询表中包含某字段的数据,输出指定字段信息
select id,name,price from booksModel_books_info where price='88'\G;
删
- 删除一个库:DROP DATABASE books;
- 删除表某条数据:delete from booksModel_books_info where name=‘现代通信网’;
- 删除一张表:drop table booksModel_books_info;
- 清空表内数据
清空表信息的方式有两种 :
truncate table booksModel_books_info;
delete from booksModel_books_info;
truncate、delete 清空表数据的区别 :
- truncate 是整体删除 (速度较快),delete是逐条删除 (速度较慢)
- truncate 不写服务器 log,delete 写服务器 log,也就是 truncate 效率比 delete高的原因
- truncate 不激活trigger (触发器),但是会重置Identity (标识列、自增字段),相当于自增列会被置为初始值,又重新从1开始记录,而不是接着原来的 ID数。而 delete 删除以后,identity 依旧是接着被删除的最近的那一条记录ID加1后进行记录。如果只需删除表中的部分记录,只能使用 DELETE语句配合 where条件
改
- 更改表名
rename table booksModel_books_info to booksModel_books_info_get;