数据库

• 连接 mysql -u [user] -p
• 查看数据库 show databases;
• 选择数据库 use [db];
• 创建数据库 create database [db] CHARACTER SET utf8 COLLATE utf8_general_ci;
• 删除数据库 drop schema [db];

数据表

• 查看数据表 show tables;
• 查看数据表结构 desc [table];
• 复制表 CREATE TABLE [table] * FROM [oldtable];
• 查看创建表的SQL语句 SHOW CREATE TABLE [table];
• 创建数据表 CREATE TABLE student_score (id INT AUTO_INCREMENT NOT NULL,name varchar(255),sex varchar(10),number INT, score INT, PRIMARY KEY(id), UNIQUE(name));
• 更改数据表名 ALTER TABLE [table] RENAME TO [table];
• 新增字段 ALTER TABLE [table] ADD [field] [type] NOT NULL Default 0;
• 修改字段 ALTER TABLE [table] CHANGE [field] [field] [type];
• 删除字段 ALTER TABLE [table] DROP COLUMN [field];

用户

• 创建用户 CREATE USER [user];
• 设置密码 SET PASSWORD FOR [user]= PASSWORD(“[password]”);
• 权限 GRANT ALL PRIVILEGES ON [db].* TO [user] IDENTIFIED BY “[password]”;
• 生效权限 FLUSH PRIVILEGES;

增删改查

• 查询
  • 限制显示数量
    SELECT [fiele] AS [name] FROM [table] WHERE [filed]=[value] LIMIT10,15;
  • 模糊查询
    SELECT * FROM [table] WHERE [filed] like '%[value]%';
  • 连接查询
    SELECT [table2].[field] FROM [table1] JOIN [table2] ON [table2].[field] = [table1].[field] WHERE [table2].[filed]=[value];
• 插入
  INSERT INTO [table]( [field], [field] ) VALUES ( [value], [value] );
• 更改
  UPDATE [table] SET [field] = [value] WHERE [filed]=[value];
• 删除
  DELETE FROM [table] WHERE [filed]=[value];

触发器和约束

• 查看约束
  SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_NAME = ‘[table]’;

• 触发器
  CREATE TRIGGER solves_abb1 AFTER INSERT ON solves FOR EACH ROW
  BEGIN
  UPDATE teams SET teams.solves_count=solves_count+1 WHERE
  teams.id=new.teamid;
  END;

备份与还原

• 备份
  • 备份数据库
    mysqldump -u root -p --databases [db] > bak.sql
  • 备份数据表
    mysqldump -u root -p [db] [table] > bak.sql
• 还原
  • 还原全部数据库
    mysql -u root -p < bak.sql
  • 还原单个数据库
    mysql -u root -p [db] < bak.sql