面试阶段大家基本都会问一些mysql的题,具体的高深理论以后再慢慢补充,但是刷题是不可避免的,下面直接上货
创建/删除表和索引系列
创建表
CREATE TABLE if not exists `test_date` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date` date DEFAULT NULL,
`temp` int(11) NOT NULL,
`updateTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
删除表
drop table if exists person;
清空表(delete不重置自增键,truncate重置,truncate不写日志速度更快)
delete from person;
truncate table person;
truncate person;
增加索引
#alter table添加方式
1.添加PRIMARY KEY(主键索引)
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
2.添加UNIQUE(唯一索引)
ALTER TABLE `table_name` ADD UNIQUE ( `column` )
3.添加INDEX(普通索引)
ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
4.添加FULLTEXT(全文索引)
ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
5.添加多列索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
#create方式只能添加这两种索引;
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
删除索引
drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;
账户相关/权限分配
查看已经存在的用户
SELECT USER,HOST FROM MYSQL.USER;
创建mysql 用户
格式:CREATE USER 'USERNAME'@'HOST' IDENTIFIED BY 'PASSWORD';
CREATE USER 'vinter'@'%' IDENTIFIED BY '123456';
CREATE USER 'jerry'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'Tom'@'126.96.10.26' IDENTIFIED BY '123456';
解析:
USERNAME 用户名
HOST 主机
PASSWORD 密码
localhost 只可以本地登陆
% 本地登陆,远程登陆
126.96.10.26 指定登陆的ip
删除mysql 用户:
格式:DROP USER 'USERNAME'