程序开发 > Myql > 正文

mysql语句大全

亮术网 2013-04-24 本网原创

  mysql 与 mssql 语法十分相似,懂一门另一门也就差不多了,下面来总结 mysql 都有哪些语句。

  1、登录 mysql

  语法如下:mysql -u用户名 -p用户密码

  例:

  1)登录本机:mysql -uroot -p123456

  2)远程登录:mysql -h192.186.210.69 -uroot -p123456 /*-h后是IP地址*/

?

  2、启动和停止 mysql 服务

  启动:net start mysql

  停止:net stop mysql

?

  3、创建与删除数据库

  创建:CREATE DATABASE 数据库名

  删除:drop database 数据库名

?

  4、打开(使用)数据库

  use 数据库名

?

  5、创建表

  create table tablename(column1 type1 [not null] [primary key],column2 type2 [not null],…)

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

  根据已有表创建新表:

  1)create table newtable like oldtable (使用旧表创建新表)

  2)create table newtable as select column1,column2,… from oldtable definition only

?

?

  6、删除表

  drop table tablename

?

  7、查询

  select column1,column2,… from tablename where 条件 order by field1,[field2,…] [ASC | DESC]

  例:

  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、插入

  insert into tablename(field1,field2,…)values(value1,value2,…)

  例:insert into employee(id,ename,age) values(1,'李大海',26);

?

  9、更新

  update tablename set field1=value1,field2=value2,… where 条件

  例:update employee set age=28 where ename='李大海';

?

  10、删除

  delete from tablename where 条件

  例:delete from employee where id > 100

?

?

  11、增加删除列

  增加列:Alter table tablename add column columnname type [[after|before] column]

  例:Alter table employee add column address varchar(50) before age;

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

?

  删除列:Alter table tablename drop column columnname

  例:Alter table employee drop column address;

?

  重命名列:Alter table tablename change oldcolumnname newcolumnname type

  例:Alter table employee change address useraddr varchar(50);

?

  修改列类型:Alter table tablename modify columnname type

  或:Alter table tablename change columnname columnname type

?

  例:Alter table employee modify address char(100);

  Alter table employee change address address char(100);

?

  12、重命名表

  rename table oldtablename to newtablename

  例:rename employee to user;

?

  13、创建删除索引

  创建:create [UNIQUE|FULLTEXT|SPATIAL] index indexname on tablename(column1,column2,…)

  例:create unique index emp_ename on employee(ename);

?

  删除:drop index indexname

  例:drop index emp_ename;

?

  14、添加删除主键

  添加:Alter table tablename add primary key(column1,column2,…)

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

?

  删除:Alter table tablename drop primary key(column1,column2,…)

  例:Alter table employee drop primary key(ename,id)

?

  15、创建删除视图

  创建:create view viewname as select statement

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

?

  删除:drop view viewname

  例:drop view v-employee;

本文浓缩标签:mysql语句大全