Mysql基本语法大全

Mysql和Mssql的语法非常相似,理解了其中一门另一门也就十分容易上手。 以下是Mysql语句用法介绍:
Mysql基本语法大全

  1. 登录 mysql
    语法如下:mysql -u用户名 -p用户密码
    例:
    1)登录本机:

    1
    mysql -uroot -p123456

    2)远程登录:

    1
    mysql -h192.123.456.78 -uroot -p123456 /*-h后是IP地址*/
  2. 启动和停止 mysql 服务
    1
    2
    net start mysql/*启动*/
    net stop mysql/*停止*/
  3. 创建与删除数据库
    1
    2
    CREATE DATABASE 数据库名 #创建
    drop database 数据库名 #删除
  4. 打开(使用)数据库
    1
    use 数据库名
  5. 创建表
    1
    create table tablename(column1 type1 [not null] [primary key],column2 type2 [not null],)

    例:

    1
    create table employee(id int not null primary, ename varchar(30))

    根据已有表创建新表:

    1
    2
    create table newtable like oldtable #使用旧表创建新表
    create table newtable as select column1,column2,from oldtable definition only
  6. 删除表
    1
    drop table tablename
  7. 查询
    1
    select column1,column2,from tablename where 条件 order by field1,[field2,] [ASC | DESC]

    例:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    select ename,age from employee where age > 25 order by age; #按年龄升序排列
    select distinct ename,age from employee order by age desc; #按年龄降序排列
    select ename,age from employee where ename like '%%'; #查找包含“王”的记录
    select max(age) from employee; #最大值
    select min(age) from employee; #最小值
    select count as totalRecord from employee; #统计总数
    select sum(age) from employee where age> 25;  #求和
    select avg(age) from employee where age > 25; #求平均值
    select ename from employee where ename in(select ename from users); #子查询
  8. 插入
    1
    insert into tablename(field1,field2,)values(value1,value2,)

    例:

    1
    insert into employee(id,ename,age) values(1,'王小明',26);
  9. 更新
    1
    update tablename set field1=value1,field2=value2,where 条件

    例:

    1
    update employee set age=28 where ename='王小明';
  10. 删除
    1
    delete from tablename where 条件

    例:

    1
    delete from employee where id > 100
  11. 增加删除列
    增加列:

    1
    Alter table tablename add column columnname type [[after|before] column]

    例:

    1
    2
    Alter table employee add column address varchar(50) before age;
    Alter table employee add column address varchar(50) after age;

    删除列:

    1
    Alter table tablename drop column columnname

    例:

    1
    Alter table employee drop column address;

    重命名列:

    1
    Alter table tablename change oldcolumnname newcolumnname type

    例:

    1
    Alter table employee change address useraddr varchar(50);

    修改列类型:

    1
    Alter table tablename modify columnname type

    或:

    1
    Alter table tablename change columnname columnname type

    例:

    1
    2
    Alter table employee modify address char(100);
    Alter table employee change address address char(100);
  12. 重命名表
    1
    rename table oldtablename to newtablename

    例:

    1
    rename employee to user;
  13. 创建删除索引
    创建索引:

    1
    create [UNIQUE|FULLTEXT|SPATIAL] index indexname on tablename(column1,column2,)

    例:

    1
    create unique index emp_ename on employee(ename);

    删除索引:

    1
    drop index indexname

    例:

    1
    drop index emp_ename;
  14. 添加删除主键
    添加主键:

    1
    Alter table tablename add primary key(column1,column2,)

    例:

    1
    Alter table employee add primary key(ename,id);

    删除主键:

    1
    Alter table tablename drop primary key(column1,column2,)

    例:

    1
    Alter table employee drop primary key(ename,id)
  15. 创建删除视图
    创建视图:

    1
    create view viewname as select statement

    例:

    1
    create view v-employee as select ename,age from employee;

    删除视图:

    1
    drop view viewname

    例:

    1
    drop view v-employee;
6

发表评论