1 连接
mysql -u root -p 连接用户
2 数据库
create DATABASE testdb; 创建数据库
drop database testdb; 删除数据库
show databases; 查看所有数据库
show variables like ‘%max_connections%’; 查看数据库最大连接数
use mysql;使用数据库
select database(); 查看当前数据库
3 用户
create user tester01 identified by '12345678'; 创建用户
grant select,insert,update,delete,create on testdb to tester01;
grant all privileges on testdb to admin; 给用户授权
flush privileges; 刷新
show grants for 'tester01'; 查看用户授权信息
select distinct concat('user:',user,'@',host,';') as users from mysql.user; 查看数据库所有用户信息
delete from mysql.user where user='tester'; 删除用户
select user from mysql.user; 查看用户
4 表
show tables; 查看数据库表信息
创建表 PRIMARY KEY主键，ENGINE 设置存储引擎，CHARSET 设置编码
create table test01(
-> id INT NOT NULL AUTO_INCREMENT,
-> name VARCHAR(10) NOT NULL,
-> age INT,
-> date DATE,
-> PRIMARY KEY (id)
-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
表插入数据
insert into test01
-> (name, age, date)
-> values
-> ('张三', 22, now());
查询
select * from test01;
SELECT name, COUNT(*) FROM test01 GROUP BY name; 统计
更新
update test01 set age=33 where name like '张%';
