SQL基础语句

SQL语句

增
	创建用户：mysql>create user xxx@'ip' identified by 'passwd';
	创建数据库：mysql>create database web;
	创建数据表：mysql>create table a1 (id int,name char(30));
	插入数据：mysql>insert into a2 (id,name,age) values (1.'zhangsan',21);
	
删
	删除用户：mysql>drop user xxx@'ip';
	删除数据库：mysql>drop database web;
	删除数据表：mysql>drop table a1;
	删除数据：mysql>delete from a2 where id=5;
			  mysql>delete from a2 where age between 23 and 25;
			  
改
	修改表中数据：mysql>update a2 set age=21 where id=3;
	修改数据表的名称：mysql>alter table a2 rename a1;
	修改数据表的字段类型：mysql>describe a1;
						  mysql>alter table a1 modify name char(50);
						  mysql>describe a1;
						mysql>alter table a1 change name username char(50) not null default ' ';
						mysql>describe a1;
	添加删除字段：mysql>alter table a1 add time datetime;
				  mysql>alter table a1 drop time;

查
	查看所有数据库：mysql>show database;
	查看指定库内所有数据：mysql>show tables;
	查看指定数据表的字段结构：mysql>describe a1;
	查看所有MySQL用户密码及登陆信息：mysql>select User,Password,Host from mysql.user;

授权
	授权用户全部权限：mysql>grant all on aa.a1 to xxx@'ip';  #已存在用户
					  mysql>grant all on aa.a1 to abc@'ip' identified by 'passwd';  #创建用户授权
	取消abc用户的删除表、库、表中数据的权限：mysql>revoke drop,delete on aa.a1 from abc@'ip';
											 mysql>show grants for xxx@'ip';

启动关闭
	service mysqld start/stop
	/etc/init.d/mysqld start/stop
	mysqld_safe &   #源码安装
	mysqladmin -uroot -p123456 shutdown