澳门赌场

法式开辟 > Myql > 注释

mysql语句大全

亮术网 2012-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语句大全