1. select * from emp; #注释
    2. #---------------------------
    3. #----命令行连接MySql---------
    4. #启动mysql服务器
    5. net start mysql
    6. #关闭
    7. net stop mysql
    8. #进入
    9. mysql -h 主机地址 -u 用户名 p 用户密码
    10. #退出
    11. exit/quit
    12. #---------------------------
    13. #----MySql用户管理---------
    14. #修改密码:首先在DOS 下进入mysql安装路径的bin目录下,然后键入以下命令:
    15. mysqladmin -uroot -p123 password 456;
    16. #增加用户
    17. #格式:grant 权限 on 数据库.* to 用户名@登录主机 identified by '密码'
    18. /*
    19. 如,增加一个用户user1密码为password1,让其可以在本机上登录, 并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入mysql,然后键入以下命令:
    20. grant select,insert,update,delete on *.* to user1@localhost Identified by "password1";
    21. 如果希望该用户能够在任何机器上登陆mysql,则将localhost改为"%"。
    22. 如果你不想user1有密码,可以再打一个命令将密码去掉。
    23. grant select,insert,update,delete on mydb.* to user1@localhost identified by "";
    24. */
    25. grant all privileges on wpj1105.* to sunxiao@localhost identified by '123'; #all privileges 所有权限
    26. #----------------------------
    1. #-----MySql数据库操作基础-----
    2. #显示数据库
    3. show databases;
    4. #判断是否存在数据库wpj1105,有的话先删除
    5. drop database if exists wpj1105;
    6. #创建数据库
    7. create database wpj1105;
    8. #删除数据库
    9. drop database wpj1105;
    10. #使用该数据库
    11. use wpj1105;
    12. #显示数据库中的表
    13. show tables;
    14. #先判断表是否存在,存在先删除
    15. drop table if exists student;
    16. #创建表
    17. create table student(
    18. id int auto_increment primary key,
    19. name varchar(50),
    20. sex varchar(20),
    21. date varchar(50),
    22. content varchar(100)
    23. )default charset=utf8;
    24. #删除表
    25. drop table student;
    26. #查看表的结构
    27. describe student; #可以简写为desc student;
    28. #插入数据
    29. insert into student values(null,'aa','男','1988-10-2','......');
    30. insert into student values(null,'bb','女','1889-03-6','......');
    31. insert into student values(null,'cc','男','1889-08-8','......');
    32. insert into student values(null,'dd','女','1889-12-8','......');
    33. insert into student values(null,'ee','女','1889-09-6','......');
    34. insert into student values(null,'ff','null','1889-09-6','......');
    35. #查询表中的数据
    36. select * from student;
    37. select id,name from student;
    38. #修改某一条数据
    39. update student set sex='男' where id=4;
    40. #删除数据
    41. delete from student where id=5;
    42. # and 且
    43. select * from student where date>'1988-1-2' and date<'1988-12-1';
    44. # or 或
    45. select * from student where date<'1988-11-2' or date>'1988-12-1';
    46. #between
    47. select * from student where date between '1988-1-2' and '1988-12-1';
    48. #in 查询制定集合内的数据
    49. select * from student where id in (1,3,5);
    50. #排序 asc 升序 desc 降序
    51. select * from student order by id asc;
    52. #分组查询 #聚合函数
    53. select max(id),name,sex from student group by sex;
    54. select min(date) from student;
    55. select avg(id) as '求平均' from student;
    56. select count(*) from student; #统计表中总数
    57. select count(sex) from student; #统计表中性别总数 若有一条数据中sex为空的话,就不予以统计~
    58. select sum(id) from student;
    59. #查询第i条以后到第j条的数据(不包括第i条)
    60. select * from student limit 2,5; #显示3-5条数据
    61. #巩固练习
    62. create table c(
    63. id int primary key auto_increment,
    64. name varchar(10) not null,
    65. sex varchar(50) , #DEFAULT '男' ,
    66. age int unsigned, #不能为负值(如为负值 则默认为0)
    67. sno int unique #不可重复
    68. );
    69. drop table c;
    70. desc c;
    71. insert into c (id,name,sex,age,sno) values (null,'涛哥','男',68,1);
    72. insert into c (id,name,sex,age,sno) values (null,'aa','男',68,2);
    73. insert into c (id,name,sex,age,sno) values (null,'平平','男',35,3);
    74. ...
    75. select * from c;
    76. #修改数据
    77. update c set age=66 where id=2;
    78. update c set name='花花',age=21,sex='女' where id=2
    79. delete from c where age=21;
    80. #常用查询语句
    81. select name,age ,id from c
    82. select * from c where age>40 and age<60; #and
    83. select * from c where age<40 or age<60; #or
    84. select * from c where age between 40 and 60 #between
    85. select * from c where age in (30,48,68,99); #in 查询指定集合内的数据
    86. select * from c order by age desc; #order by (asc升序 des降序)
    87. #分组查询
    88. select name,max(age) from c group by sex; #按性别分组查年龄最大值
    89. #聚合函数
    90. select min(age) from c;
    91. select avg(age) as '平均年龄 ' from c;
    92. select count(*) from c; #统计表中数据总数
    93. select sum(age) from c;
    94. #修改表的名字
    95. #格式:alter table tbl_name rename to new_name
    96. alter table c rename to a;
    97. #表结构修改
    98. create table test
    99. (
    100. id int not null auto_increment primary key, #设定主键
    101. name varchar(20) not null default 'NoName', #设定默认值
    102. department_id int not null,
    103. position_id int not null,
    104. unique (department_id,position_id) #设定唯一值
    105. );
    106. #修改表的名字
    107. #格式:alter table tbl_name rename to new_name
    108. alter table test rename to test_rename;
    109. #向表中增加一个字段(列)
    110. #格式:alter table tablename add columnname type;/alter table tablename add(columnname type);
    111. alter table test add columnname varchar(20);
    112. #修改表中某个字段的名字
    113. alter table tablename change columnname newcolumnname type; #修改一个表的字段名
    114. alter table test change name uname varchar(50);
    115. select * from test;
    116. #表position 增加列test
    117. alter table position add(test char(10));
    118. #表position 修改列test
    119. alter table position modify test char(20) not null;
    120. #表position 修改列test 默认值
    121. alter table position alter test set default 'system';
    122. #表position 去掉test 默认值
    123. alter table position alter test drop default;
    124. #表position 去掉列test
    125. alter table position drop column test;
    126. #表depart_pos 删除主键
    127. alter table depart_pos drop primary key;
    128. #表depart_pos 增加主键
    129. alter table depart_pos add primary key PK_depart_pos
    130. (department_id,position_id);
    131. #用文本方式将数据装入数据库表中(例如D:/mysql.txt)
    132. load data local infile "D:/mysql.txt" into table MYTABLE;
    133. #导入.sql文件命令(例如D:/mysql.sql)
    134. source d:/mysql.sql; #或者 /. d:/mysql.sql;

    原文地址:https://www.cnblogs.com/qinqinmeiren/archive/2011/05/21/2151693.html