sql操作

1、root用户登录mysql -uroot -pxxx2、创建字符集为utf8的数据库create database recmt character set utf8;3、创建用户并分配指定数据库的相关权限（.*是所有权限）,密码要有一定强度grant all on recmt.* to scott identified by ‘xxx‘;4、退出mysqlexit;5、换用户登录mysqlmysql -uscott -pxxx6、使用指定的数据库use recmt;7、设置使用的字符集set names utf8;8、创建带主键的表create table urllist (url varchar(512) not null primary key);

 9、创建带主键的表，主键自动增长

create table recruit (id INT auto_increment primary key, company_name varchar(256));10、创建对应表的唯一索引（不强制用唯一的，普通的也可以）create unique index name on urllist (url);11、显示创建数据库的语句，表的显示一样，就是数据库对应替换成表就行mysql> show create database recmt;+----------+----------------------------------------------------------------+| Database | Create Database                                                |+----------+----------------------------------------------------------------+| recmt    | CREATE DATABASE `recmt` /*!40100 DEFAULT CHARACTER SET utf8 */ |+----------+----------------------------------------------------------------+12、显示表结构mysql> desc urllist;+-------+--------------+------+-----+---------+-------+| Field | Type         | Null | Key | Default | Extra |+-------+--------------+------+-----+---------+-------+| url   | varchar(512) | NO   | PRI | NULL    |       |+-------+--------------+------+-----+---------+-------+13、插入数据mysql> insert into urllist (url) values ("123");14、查询表的数据mysql> select * from urllist;+-----+| url |+-----+| 123 |+-----+因为定义了主键，不能重复所以再次插入会错误insert into urllist (url) values ("123");ERROR 1062 (23000): Duplicate entry ‘123‘ for key ‘PRIMARY‘15、查询有多少行mysql> select count(*) from urllist;+----------+| count(*) |+----------+|        2 |+----------+16、删除表mysql> drop table urllist;17、查看当前数据库有多少表mysql> show tables;+----------------+| Tables_in_wurl |+----------------+| recruit        || urllist        |+----------------+18、清空表中的数据mysql> insert into urllist (url) values ("123");mysql> select * from urllist;+-----+| url |+-----+| 123 |+-----+mysql> delete from urllist;mysql>  select * from urllist;Empty set (0.00 sec)19、用一个表过滤另外一个表创建表create table aa (id INT, name varchar(10));create table bb (name varchar(10));插入数据insert into aa values(1, ‘hao‘);insert into aa values(2, ‘old‘);insert into aa values(3, ‘are‘);insert into aa values(4, ‘your‘);insert into bb values(‘your‘);insert into bb values(‘hao‘);insert into bb values(‘more‘);通过表bb过滤表aa,删除表aa中和表bb中相同的数据delete from aa where aa.name in (select name from bb);20、修改字段名称, job_site 是原来的名称alter table rt change job_site locate varchar(16); 21、修改字段类型alter table rt modify column rdate varchar(16);alter table rt modify column job_name varchar(80);alter table rt modify column company_name varchar(80);alter table rt modify column locate varchar(16);alter table rt modify column salary varchar(16);alter table rt modify column rdate varchar(16);22、垂直显示，每一个字段一行******不是以‘;‘结尾********select * from rt\G23、将查询结果重定向到文件******每次都要重定向*******tee filename;24、用查询结构的集合创建新表create table ft asselect * from rt where company_name not in (select name from company);

 

sql操作

标签：alter   自动   插入   any   ***   not   index   mysq   数据库