常用MySql语句

数据库

  • 连接 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