MySQL总结

mysql启动:mysqld --console

 

远程连接mysql:shell> mysql -h host -u user -p psw

 

查看当前数据库版本:mysql> select version();

 

查看当前系统日期:mysql> current_date;

 

mysql用作一个简单的计算器:mysql> select sin(pi()/4), (4+1)*5;

 

在一行上输入多条语句:mysql> select version(); select now();

 

当用多行输入一个sql语句的时候，如果决定不执行这个语句，可以输入/c

 

找出服务器上当前存的数据库:mysql> show databases;

 

创建数据库:mysql> create database dbname;

 

数据库中关于年龄的问题 可以当前日期和出生日期之间的差来计算它

 

查看某个数据库中的表: mysql> show tables;

 

查看某个表的字段信息: mysql> describe tablename ;

 

在mysql中如果要插入一些数据，可以先把这些数据写入到txt文本中，列段之间用tab间隔。输入完毕后，添加到数据库中的指令是：mysql> load data local infile '/path/*.txt' into table tablename;

 

插入信息:mysql> insert into tablename values ();

 

更新信息:mysql> update pet set birth = ' 1989-08-31 ' where name = 'bowser';

 

关于计算年龄:select name, birth, curdate(),(year(curdate())-year(birth))- (right(curdate(),5)<right(birth,5))as age from pet;

 

在mysql中，可以单独对一个data数据进行year( )、month( )和dayofmonth( )等进行查找

 

执行order by时，如果运行 order by ... asc，则null值出现在最前面，若运行order by ... desc，则null值出现在最后面。

 

sql模式匹配允许你使用"_"匹配任何单个字符，而"%"匹配任意数目字符(包括零字符)。

 

要想找出正好包含5个字符的名字，使用"_"模式字符：select * from pet where name like '_____';

 

auto_increment属性为新的行产生唯一的标识

 

mysqld是mysql服务器

 

mysqld_safe、mysql.server和mysqld_multi是服务器启动脚本

mysql_install_db初始化数据目录和初始数据库

 

mysql是一个命令行客户程序

 

mysqladmin是用于管理功能的客户程序

 

mysqlcheck执行表维护操作

 

mysqldump和mysqlhotcopy负责数据库备份

 

mysqlimport导入数据文件

 

mysqlshow显示信息数据库和表的相关信息

 

perror显示错误代码的含义

 

shell> mysql --help

 

shell> mysql -h host -u user -p

 

mysql> quit(/q)

 

mysql> select version(), current_date;

 

mysql> select sin(pi()/4), (4+1)*5;

 

mysql> select version(); select now();

 

mysql> select user(),current_date;

 

mysql> select user()  /c

 

mysql> select * from my_table where name = 'smith and age < 30;

 

mysql> show databases;

 

mysql> use test

 

mysql> grant all on menagerie.* to 'your_mysql_name'@'your_client_host';

 

mysql> create database menagerie;

 

mysql> show tables;

 

mysql> create table pet (name varchar(20), owner varchar(20),sex char(1), birth date, death date);

 

mysql> describe pet;

 

mysql> load data local infile '/path/pet.txt' into table pet;

mysql> insert into pet values ('puffball','diane','hamster','f',' 1999-03-30 ',null);

 

mysql> delete from pet;

 

mysql> update pet set birth = ' 1989-08-31 ' where name = 'bowser';

 

mysql> select * from pet where birth > ' 1998-1-1 ';

 

mysql> select * from pet where species = 'dog' and sex = 'f';

 

mysql> select * from pet where species = 'snake' or species = 'bird';

 

mysql> select * from pet where (species = 'cat' and sex = 'm') or (species = 'dog' and sex = 'f');

 

mysql> select distinct owner from pet;

 

mysql> select name, species, birth from pet where species = 'dog' or species = 'cat';

 

mysql> select name, birth from pet order by birth;

 

mysql> select name, birth from pet order by birth desc;

 

mysql> select name, species, birth from pet order by species, birth desc;

 

mysql> select name, birth, curdate(),

(year(curdate())-year(birth))- (right(curdate(),5)<right(birth,5))

as age from pet;

 

mysql> select name, birth, death,

(year(death)-year(birth)) - (right(death,5)<right(birth,5))

 as age from pet where death is not null order by age;

 

mysql> select name, birth, month(birth) from pet;

 

mysql> select name, birth from pet where month(birth) = 5;

 

mysql> select name, birth from pet where month(birth) = month(date_add(curdate(),interval 1 month));

 

mysql> select name, birth from pet where month(birth) = mod(month(curdate()), 12) + 1;

 

mysql> select 1 is null, 1 is not null;

 

mysql> select * from pet where name like 'b%';

 

mysql> select * from pet where name like '%fy';

 

mysql> select * from pet where name like '%w%';

 

mysql> select * from pet where name like '_____';

 

mysql> select count(*) from pet;

 

mysql> select owner, count(*) from pet group by owner;

 

mysql> select database();

 

mysql> create table shop (article int(4) unsigned zerofill default '0000' not null,dealer char(20)  default '' not null, price double(16,2) default '0.00' not null,primary key (article,dealer));

 

mysql> select max(article) as article from shop;

 

mysql> select article, dealer, price from shop where price=(select max(price) from shop);

 

mysql> select article, dealer, price from shop order by price desc limit 2;

 

mysql> select article, max(price) as price from shop group by article;

 

mysql> select article, dealer, price from shop s1 where price=(select max(s2.price) from shop s2 where s1.article = s2.article);

 

mysql> select * from shop where price=@min_price or price=@max_price;

 

mysql> create table t1 (year year(4), month int(2) unsigned zerofill,day int(2) unsigned zerofill);