mysql5.0基础语句_mysql 基础语句

话不多说

mysql -uroot -p

show databases; //查看数据库

show tables; // 查看表

create database HA; //创建数据库

ceate table xixi(id int primary key auto__increment nut null,name varchar(20),age varcahr(20),sex enum('m','f') default m,salary flost(5,2)); //创建表

insert into xixi values(1,'hehe','20',m,'4000.00'),(2,'haha','22',m,'6000.00'),(3,'yezhu','38',m,'15000.00');向表中插入数据

insert into xixi (name,age,sex....)values(1,'hehe','20',m,'4000.00'),(2,'haha','22',m,'6000.00'),(3,'yezhu','38',m,'15000.00');向表指定中插入数据

desc xixi;查看表中结构

explain xixi; 同上

show fields from mysql.user;

show create table xixi\G;

select now();查看当前的时间

select database();

select status;

select user();

select version();

update students set id=2;  所有的都变为2

update students set sex='M' where id=2；

alter table xixi add head varchar(20);   ///插入在最后面

alter table xixi add head varchar(20) after age; //插入在age后面

alter table xixi add head varchar(20) first;  //插入在最前面

alter table xixi drop  sex; 删除sex字段

alter table xixi add modify sex tianyun; //修改sex字段类型

alter table xixi change sex to tianyun varcahr(20);修改sex字段的名字和类型

alter table 表名 rename 新表名;

drop database HA if exists;

create database HA if not exists;

create table huhu select * from xixi; //复制xixi表的结构

create user 'xixi'@'localhost' indetified with mysql_native_password by '密码';  //创建本地用户

create user 'xixi'@'%' indetified with mysql_native_password by '密码';  //创建远程用户

select user,host from mysql.user; //可在user表中查看结构

update mysql.user set host='%' name='xixi'; 更改本地用户为远程用户

update mysql.user set host='192.168.0.%' name='xixi'; 更改用户为内网网段访问

alter user 'root'@'localhost' identified with mysql_native_password by '新密码';更改用户的密码

alter user 'root'@'%' identified with mysql_native_password by '新密码';

//create database xixi;use xixi;

删除表

delete from mysql.user;

delete from students where id=3;

delete from students where age is null;

去重

select distinst name,age from xixi;

select  distinct id,name,age from xixi where id=3;

select * from user where age >20 and salary>5000;

or和and 同时存在时，先算and的两边值,逻辑与先执行

select id,name,age from xixi where id>3 and age>25;

别名

select name,salary,salary*14 from haha;创建别名

select name,salary,salary*14 AS gongzi from haha;别名

select CONCAT(name,'annual 'salary:',salary*14) AS annual_salary from example;

where salary BETWEEN 5000 AND 15000;　　//BETWEEN and 多少到多少

select name,salary from employee5 where comment is null;空

select name,salary from employee5 where comment is not null;非空

select name,salary from employee5 where salary=4000 or salary=5000 or salary=20000;

查找工资是4000或者五千或者两万的

IN关键字

select name,sakary example where salary  in (4000,5000,20000); 查找工资是4000或者五千或者两万的

select name,sakary example where salary  not in (4000,5000,20000);查找工资不是是4000或者五千或者两万的

关键字like模糊查找

select * from example where name like 'al___'; 一个下划线代表任意一个字符

select * from example where name like 'al%'; %代表任意多个字符

ASC升序  DESC降序

select name,salary example where order by salary DESC,comment ASC;

grant select,create,insert,update on xixi.* to 'xixi'@'10.0.0.%' identified by '123';

grant * on *.* to 'xixi'@'%' identified by '密码'；

revoke：回收权限

REVOKE INSERT ON *.* FROM clsn@localhost;

revoke drop,delete on *.* from sys@localhost;

select * from example order by name, salary ASC limit 2,2;从第二个取值

select * from example order by name, salary ASC limit 2;取值

mysql> select max(sex) from server where age=10;

+----------+

| max(sex) |

+----------+

| 7        |

+----------+

1 row in set (0.00 sec)

mysql> select count(*) from server where age=10;

+----------+

| count(*) |

+----------+

|        3 |

+----------+

1 row in set (0.00 sec)

mysql> select max(sex) from server where age=10;

+----------+

| max(sex) |

+----------+

| 7        |

+----------+

1 row in set (0.00 sec)

mysql> select min(sex) from server where age=10;

+----------+

| min(sex) |

+----------+

| 6        |

+----------+

1 row in set (0.00 sec)

mysql> select avg(sex) from server where age=10;

+-------------------+

| avg(sex)          |

+-------------------+

| 6.333333333333333 |

+-------------------+

1 row in set (0.00 sec)

mysql> select sum(sex) from server where age=10;

+----------+

| sum(sex) |

+----------+

|       19 |

+----------+

mysql> select sex,GROUP_CONCAT(rnname) from server group by sex;

+------+----------------------+

| sex  | GROUP_CONCAT(rnname) |

+------+----------------------+

| 6    | lanzi,gouzi          |

| 7    | daqiang              |

+------+----------------------+